<commit_before><?php

require_once 'YASS/DataStore.php';
require_once 'YASS/Replica.php';
require_once 'YASS/SyncStore/GenericSQL.php';

class YASS_SyncStore_ARMS extends YASS_SyncStore_GenericSQL {

	/**
	 * 
	 */
	public function __construct(YASS_Replica $replica) {
		parent::__construct($replica);
		$this->disableCache = TRUE;
	}
	
	/**
	 * Implementation of hook_arms_trigger
	 */
	function onCreateSqlProcedures(YASS_Replica $replica) {
		$result = array();
		foreach ($this->replica->schema->getEntityTypes() as $entityType) {
		// foreach (array('civicrm_activity') as $entityType) {
			$result += $this->_createSqlProcedure($replica, $entityType);
		}
		return $result;
	}
	
	protected function _createSqlProcedure(YASS_Replica $replica, $table) {
		$fks = $replica->schema->getIncomingForeignKeys($table);
		$staticArgs = array(
			'@yass_replicaId' => $replica->id,
			'@yass_effectiveReplicaId' => $replica->getEffectiveId(),
			'@entityType' => $table,
			'@entityIdColumn' => 'id',
		);
		$proc = array(
			'declare' => array(),
			'cmds' => array(),
		);
		
		// exclude FKs from unsync'd tables
		foreach (array_keys($fks) as $fkName) {
			if (!in_array($fks[$fkName]['fromTable'], $this->replica->schema->getEntityTypes())) {
				unset($fks[$fkName]);
			}
		}
		
		if (!empty($fks)) {
			foreach ($fks as $id => $fk) {
				$fks[$id]['vars'] = array(
					'@fkFromTable' => $fk['fromTable'],
					'@fkFromCol' => $fk['fromCol'],
					'@fkToTable' => $fk['toTable'],
					'@fkToCol' => $fk['toCol'],
					'@fkCursor' => 'cur_' . $fk['fromTable'] . '__' . $fk['fromCol'],
					'@fkLoop' => 'loop_' . $fk['fromTable'] . '__' . $fk['fromCol'],
				);
			}
			
			$proc['declare'][] = 'DECLARE relId INT;';
			$proc['declare'][] = 'DECLARE relGuid VARCHAR(36);';
			$proc['declare'][] = 'DECLARE done INT DEFAULT FALSE;';
			foreach ($fks as $fk) {
				// INNER JOIN is better than LEFT JOIN b/c we don not need to propagate deletions for unmapped entities
				$proc['declare'][] = strtr('DECLARE @fkCursor CURSOR FOR
					SELECT relEntity.id, map.guid
					FROM @fkFromTable relEntity
					INNER JOIN yass_guidmap map ON (map.entity_type="@fkFromTable" AND map.lid = relEntity.id)
					WHERE relEntity.@fkFromCol = deleteId;
				', $fk['vars']);
			}
			$proc['declare'][] = 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;';
			$proc['cmds'][] = '
				IF @yass_disableTrigger IS NULL OR @yass_disableTrigger = 0 THEN
			
				';
			foreach ($fks as $fk) {
				$proc['cmds'][] = strtr('
					OPEN @fkCursor;
					SET done = FALSE;
					@fkLoop: LOOP
						FETCH @fkCursor INTO relId, relGuid;
						IF done THEN
							LEAVE @fkLoop;
						END IF;
						UPDATE yass_syncstore_state state SET u_replica_id = @yass_effectiveReplicaId, u_tick = yass_thisTick
							WHERE state.replica_id = @yass_replicaId AND state.entity_id = relGuid;
						CALL yass_cscd_@fkFromTable(relId, yass_thisTick);
					END LOOP;
					CLOSE @fkCursor;
				', $fk['vars']);
			}
			$proc['cmds'][] = '
				END IF;
			';
		}
		$result = array();
		$result['yass_cscd_' . $table] = array(
			'full_sql' => strtr(''
				. "CREATE PROCEDURE yass_cscd_@entityType (IN deleteId INT, IN yass_thisTick INT)\n"
				. "BEGIN\n"
				. implode("\n", $proc['declare'])
				. implode("\n", $proc['cmds'])
				. "END;\n",
			$staticArgs),
		);
		return $result;
	}

	/**
	 * Implementation of hook_arms_trigger
	 */
	function onCreateSqlTriggers(YASS_Replica $replica) {
		// make sure that there's a last-seen value for the trigger to read
		db_query('INSERT IGNORE INTO {yass_syncstore_seen} (replica_id,r_replica_id,r_tick) VALUES (%d,%d,%d)',
			$replica->id, $replica->id, 0);
	
		$template = '
			IF @yass_disableTrigger IS NULL OR @yass_disableTrigger = 0 THEN
				SET yass_nextTick = 1+(SELECT max(r_tick) FROM yass_syncstore_seen
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId LIMIT 1);
				UPDATE yass_syncstore_seen SET r_tick = yass_nextTick
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId;
				SET yass_guid = (SELECT guid FROM yass_guidmap
					WHERE replica_id = @yass_replicaId AND entity_type = "@entityType" AND lid = {ACTIVE}.@entityIdColumn);
				IF yass_guid IS NULL OR yass_guid = "" THEN
					SET yass_guid = uuid();
					INSERT DELAYED INTO {yass_guidmap} (replica_id,entity_type,lid,guid)
						VALUES (@yass_replicaId, "@entityType", {ACTIVE}.@entityIdColumn, yass_guid);
				END IF;

				INSERT DELAYED INTO yass_syncstore_state (replica_id, entity_type, entity_id, u_replica_id, u_tick, c_replica_id, c_tick) 
				VALUES (@yass_replicaId, "@entityType", yass_guid, @yass_effectiveReplicaId, yass_nextTick, @yass_effectiveReplicaId, yass_nextTick)
				ON DUPLICATE KEY UPDATE u_replica_id = @yass_effectiveReplicaId, u_tick = yass_nextTick;
			END IF
		';
		$beforeDelTemplate = '
			IF @yass_disableTrigger IS NULL OR @yass_disableTrigger = 0 THEN
				SET yass_nextTick = 1+(SELECT max(r_tick) FROM yass_syncstore_seen
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId LIMIT 1);
				UPDATE yass_syncstore_seen SET r_tick = yass_nextTick
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId;
				SET max_sp_recursion_depth = 10;
				CALL yass_cscd_@entityType(OLD.@entityIdColumn, yass_nextTick);
			END IF
		';
		
		foreach ($this->replica->schema->getEntityTypes() as $table) {
			$staticArgs = array(
				'@yass_replicaId' => $replica->id,
				'@yass_effectiveReplicaId' => $replica->getEffectiveId(),
				'@entityType' => $table,
				'@entityIdColumn' => 'id',
			);
			$result[] = array(
				'table' => array($table),
				'when' => array('after'),
				'event' => array('insert', 'update','delete'),
				'declare' => array('yass_nextTick' => 'NUMERIC', 'yass_guid' => 'VARCHAR(36)'),
				'sql' => strtr($template, $staticArgs),
			);
			$result[] = array(
				'table' => array($table),
				'when' => array('before'),
				'event' => array('delete'),
				'declare' => array('yass_nextTick' => 'NUMERIC'),
				'sql' => strtr($beforeDelTemplate, $staticArgs),
			);
		}
		
		return $result;
	}
	
	/**
	 * Find any unmapped entities and... map them...
	 */
	function onValidateGuids(YASS_Replica $replica) {
		$engine = YASS_Engine::singleton();
		$this->lastSeen = FALSE; // flush cache
		
		// create GUIDs for any unmapped entities
		foreach ($this->replica->schema->getEntityTypes() as $type) {
			$args = array(
				'@yass_replicaId' => $replica->id,
				'@entityType' => $type,
				'@entityIdColumn' => 'id',
			);
			$q = db_query(strtr('
				SELECT 
					"@entityType" AS entity_type,
					entity.@entityIdColumn lid
				FROM {@entityType} entity
				LEFT JOIN {yass_guidmap} map ON (
					map.replica_id = @yass_replicaId 
					AND map.entity_type = "@entityType" 
					AND map.lid = entity.@entityIdColumn
				)
				WHERE map.guid IS NULL
			', $args));
			while ($row = db_fetch_object($q)) {
				$replica->mapper->addMappings(array(
					$type => array($row->lid => $engine->createGuid()),
				));
			}
			
			$q = db_query(strtr('
				SELECT
					map.guid AS guid
				FROM {@entityType} entity
				INNER JOIN {yass_guidmap} map ON (
					map.replica_id = @yass_replicaId
					AND map.entity_type = "@entityType"
					AND map.lid = entity.@entityIdColumn
				)
				LEFT JOIN {yass_syncstore_state} state ON (
					state.replica_id = @yass_replicaId
					AND state.entity_type = "@entityType"
					AND state.entity_id = map.guid
				)
				WHERE state.u_replica_id IS NULL
			', $args));
			while ($row = db_fetch_object($q)) {
				$this->onUpdateEntity($row->guid);
			}
		}
	}

}<commit_msg>refs #1242, CAP-94 - Revise stored-procedure template used in cascading deletes:  * Provide better behavior with combinations syncable/unsyncable-entities and cascade/set-null strategies  * Generate valid code (but with suitable redflags) for unsupported cascades<commit_after><?php

require_once 'YASS/DataStore.php';
require_once 'YASS/Replica.php';
require_once 'YASS/SyncStore/GenericSQL.php';

class YASS_SyncStore_ARMS extends YASS_SyncStore_GenericSQL {

	/**
	 * 
	 */
	public function __construct(YASS_Replica $replica) {
		parent::__construct($replica);
		$this->disableCache = TRUE;
	}
	
	/**
	 * Implementation of hook_arms_trigger
	 */
	function onCreateSqlProcedures(YASS_Replica $replica) {
		$result = array();
		foreach ($this->replica->schema->getEntityTypes() as $entityType) {
		// foreach (array('civicrm_activity') as $entityType) {
			$result += $this->_createSqlProcedure($replica, $entityType);
		}
		return $result;
	}
	
	/**
	 * Generate a store procedure to update sync-states in a cascading manner which parallels SQL's cascade during deletion.
	 */
	protected function _createSqlProcedure(YASS_Replica $replica, $table) {
		$fks = $replica->schema->getIncomingForeignKeys($table);
		$staticArgs = array(
			'@yass_replicaId' => $replica->id,
			'@yass_effectiveReplicaId' => $replica->getEffectiveId(),
			'@entityType' => $table,
			'@entityIdColumn' => 'id',
		);
		$proc = array(
			'declare' => array(),
			'cmds' => array(),
		);
		
		if (!empty($fks)) {
			foreach ($fks as $id => $fk) {
				$fks[$id]['vars'] = array(
					'@fkFromTable' => $fk['fromTable'],
					'@fkFromCol' => $fk['fromCol'],
					'@fkToTable' => $fk['toTable'],
					'@fkToCol' => $fk['toCol'],
					'@fkOnDelete' => $fk['onDelete'],
					'@fkCursor' => 'cur_' . $fk['fromTable'] . '__' . $fk['fromCol'],
					'@fkLoop' => 'loop_' . $fk['fromTable'] . '__' . $fk['fromCol'],
				);
				
				// build list of tasks that need to be done to the related entity
				
				$fks[$id]['cmds'] = array(); // array(sqlString)
				if (in_array($fk['fromTable'], $this->replica->schema->getEntityTypes())) {
					$fks[$id]['cmds'][] = '
							UPDATE yass_syncstore_state state SET u_replica_id = @yass_effectiveReplicaId, u_tick = yass_thisTick
								WHERE state.replica_id = @yass_replicaId AND state.entity_id = relGuid;
					';
				}
				// CASCADE means: "if @toTable is deleted, then delete @fromTable"
				// SET NULL means: "if @toTable is deleted, then set @fromTable.@fromCol to null"
				if ($fk['onDelete'] == 'CASCADE') {
					// FIXME: The in_array() test should be removed, but the cmd depends on having an appropriate stored-procedure.
					// We don't currently have stored-procedures for all entities (syncable-entities: yes; unsyncable-entities: no).
					// As it stands, our stored-procs don't necessarily match SQL cascading because we're missing stored-procs
					// for unsyncable-entities.
					if (in_array($fk['fromTable'], $this->replica->schema->getEntityTypes())) {
						$fks[$id]['cmds'][] = '
							CALL yass_cscd_@fkFromTable(relId, yass_thisTick);
						';
					} else {
						$proc['cmds'][] = strtr('-- UNSUPPORTED: Cascade deletion to unsyncable entity (@fkToTable.@fkToCol <=> @fkFromTable.@fkFromCol)',
							$fks[$id]['vars']);
					}
				}
			}
			
			$proc['declare'][] = 'DECLARE relId INT;';
			$proc['declare'][] = 'DECLARE relGuid VARCHAR(36);';
			$proc['declare'][] = 'DECLARE done INT DEFAULT FALSE;';
			foreach ($fks as $fk) {
				if (empty($fk['cmds'])) {
					$proc['declare'][] = strtr('-- UNNECESSARY: DECLARE @fkCursor CURSOR FOR...', $fk['vars']);
				} else {
					$proc['declare'][] = strtr('DECLARE @fkCursor CURSOR FOR
						SELECT relEntity.id, map.guid
						FROM @fkFromTable relEntity
						LEFT JOIN yass_guidmap map ON (map.entity_type="@fkFromTable" AND map.lid = relEntity.id)
						WHERE relEntity.@fkFromCol = deleteId;
					', $fk['vars']);
				}
			}
			$proc['declare'][] = 'DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;';
			foreach ($fks as $fk) {
				if (empty($fk['cmds'])) {
					$proc['cmds'][] = strtr('
						-- IGNORE: relation (@fkToTable.@fkToCol <=> @fkFromTable.@fkFromCol ; onDelete=@fkOnDelete) has no cascading commands
					', $fk['vars']);
				} else {
					$proc['cmds'][] = strtr('
						OPEN @fkCursor;
						SET done = FALSE;
						@fkLoop: LOOP
							FETCH @fkCursor INTO relId, relGuid;
							IF done THEN
								LEAVE @fkLoop;
							END IF;
							'.implode('', $fk['cmds']).'
						END LOOP;
						CLOSE @fkCursor;
					', $fk['vars']);
				}
			}
		}
		$result = array();
		$result['yass_cscd_' . $table] = array(
			'full_sql' => strtr(''
				. "CREATE PROCEDURE yass_cscd_@entityType (IN deleteId INT, IN yass_thisTick INT)\n"
				. "BEGIN\n"
				. implode("\n", $proc['declare'])
				. "\n"
				. implode("\n", $proc['cmds'])
				. "END;\n",
			$staticArgs),
		);
		return $result;
	}

	/**
	 * Implementation of hook_arms_trigger
	 */
	function onCreateSqlTriggers(YASS_Replica $replica) {
		// make sure that there's a last-seen value for the trigger to read
		db_query('INSERT IGNORE INTO {yass_syncstore_seen} (replica_id,r_replica_id,r_tick) VALUES (%d,%d,%d)',
			$replica->id, $replica->id, 0);
	
		$template = '
			IF @yass_disableTrigger IS NULL OR @yass_disableTrigger = 0 THEN
				SET yass_nextTick = 1+(SELECT max(r_tick) FROM yass_syncstore_seen
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId LIMIT 1);
				UPDATE yass_syncstore_seen SET r_tick = yass_nextTick
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId;
				SET yass_guid = (SELECT guid FROM yass_guidmap
					WHERE replica_id = @yass_replicaId AND entity_type = "@entityType" AND lid = {ACTIVE}.@entityIdColumn);
				IF yass_guid IS NULL OR yass_guid = "" THEN
					SET yass_guid = uuid();
					INSERT DELAYED INTO {yass_guidmap} (replica_id,entity_type,lid,guid)
						VALUES (@yass_replicaId, "@entityType", {ACTIVE}.@entityIdColumn, yass_guid);
				END IF;

				INSERT DELAYED INTO yass_syncstore_state (replica_id, entity_type, entity_id, u_replica_id, u_tick, c_replica_id, c_tick) 
				VALUES (@yass_replicaId, "@entityType", yass_guid, @yass_effectiveReplicaId, yass_nextTick, @yass_effectiveReplicaId, yass_nextTick)
				ON DUPLICATE KEY UPDATE u_replica_id = @yass_effectiveReplicaId, u_tick = yass_nextTick;
			END IF
		';
		$beforeDelTemplate = '
			IF @yass_disableTrigger IS NULL OR @yass_disableTrigger = 0 THEN
				SET yass_nextTick = 1+(SELECT max(r_tick) FROM yass_syncstore_seen
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId LIMIT 1);
				UPDATE yass_syncstore_seen SET r_tick = yass_nextTick
					WHERE replica_id = @yass_replicaId AND r_replica_id = @yass_effectiveReplicaId;
				SET max_sp_recursion_depth = 10;
				CALL yass_cscd_@entityType(OLD.@entityIdColumn, yass_nextTick);
			END IF
		';
		
		foreach ($this->replica->schema->getEntityTypes() as $table) {
			$staticArgs = array(
				'@yass_replicaId' => $replica->id,
				'@yass_effectiveReplicaId' => $replica->getEffectiveId(),
				'@entityType' => $table,
				'@entityIdColumn' => 'id',
			);
			$result[] = array(
				'table' => array($table),
				'when' => array('after'),
				'event' => array('insert', 'update','delete'),
				'declare' => array('yass_nextTick' => 'NUMERIC', 'yass_guid' => 'VARCHAR(36)'),
				'sql' => strtr($template, $staticArgs),
			);
			$result[] = array(
				'table' => array($table),
				'when' => array('before'),
				'event' => array('delete'),
				'declare' => array('yass_nextTick' => 'NUMERIC'),
				'sql' => strtr($beforeDelTemplate, $staticArgs),
			);
		}
		
		return $result;
	}
	
	/**
	 * Find any unmapped entities and... map them...
	 */
	function onValidateGuids(YASS_Replica $replica) {
		$engine = YASS_Engine::singleton();
		$this->lastSeen = FALSE; // flush cache
		
		// create GUIDs for any unmapped entities
		foreach ($this->replica->schema->getEntityTypes() as $type) {
			$args = array(
				'@yass_replicaId' => $replica->id,
				'@entityType' => $type,
				'@entityIdColumn' => 'id',
			);
			$q = db_query(strtr('
				SELECT 
					"@entityType" AS entity_type,
					entity.@entityIdColumn lid
				FROM {@entityType} entity
				LEFT JOIN {yass_guidmap} map ON (
					map.replica_id = @yass_replicaId 
					AND map.entity_type = "@entityType" 
					AND map.lid = entity.@entityIdColumn
				)
				WHERE map.guid IS NULL
			', $args));
			while ($row = db_fetch_object($q)) {
				$replica->mapper->addMappings(array(
					$type => array($row->lid => $engine->createGuid()),
				));
			}
			
			$q = db_query(strtr('
				SELECT
					map.guid AS guid
				FROM {@entityType} entity
				INNER JOIN {yass_guidmap} map ON (
					map.replica_id = @yass_replicaId
					AND map.entity_type = "@entityType"
					AND map.lid = entity.@entityIdColumn
				)
				LEFT JOIN {yass_syncstore_state} state ON (
					state.replica_id = @yass_replicaId
					AND state.entity_type = "@entityType"
					AND state.entity_id = map.guid
				)
				WHERE state.u_replica_id IS NULL
			', $args));
			while ($row = db_fetch_object($q)) {
				$this->onUpdateEntity($row->guid);
			}
		}
	}

}
{'fraction_non_alphanumeric': 0.15235720223251395, 'fraction_numerical': 0.0019593872461702885, 'mean_word_length': 3.604428649535265, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 141, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7623273', 'n_tokens_mistral': 7405, 'n_tokens_neox': 6198, 'n_words': 1307}