sql("CREATE TRIGGER `cacheAttributesAfterDelete` AFTER DELETE ON `caches_attributes`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=OLD.`cache_id`;\n\t\t\t\t\t\t\tCALL sp_update_cache_listingdate(OLD.`cache_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF (SELECT `status` FROM `caches` WHERE `cache_id`=OLD.`cache_id`) != 5 AND\n\t\t\t\t\t\t (SELECT `date_created` FROM `caches` WHERE `cache_id`=OLD.`cache_id`) < LEFT(NOW(),10) THEN\n\t\t\t\t\t\t\tINSERT IGNORE INTO `caches_attributes_modified` (`cache_id`, `attrib_id`, `date_modified`, `was_set`, `restored_by`) VALUES (OLD.`cache_id`, OLD.`attrib_id`, NOW(), 1, IFNULL(@restoredby,0));\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('map2resultAfterDelete');
sql("CREATE TRIGGER `map2resultAfterDelete` AFTER DELETE ON `map2_result`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tDELETE FROM `map2_data` WHERE `result_id`=OLD.`result_id`;\n\t\t\t\t\tEND;");
sql_dropTrigger('coordinatesBeforeInsert');
sql("CREATE TRIGGER `coordinatesBeforeInsert` BEFORE INSERT ON `coordinates`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tSET NEW.`date_created`=NOW();\n\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('coordinatesAfterInsert');
sql("CREATE TRIGGER `coordinatesAfterInsert` AFTER INSERT ON `coordinates`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tIF NEW.`type`=1 THEN\n\t\t\t\t\t\t\tIF ((ISNULL(@XMLSYNC) OR @XMLSYNC!=1) AND IFNULL(@dont_update_listingdate,0)=0) THEN\n\t\t\t\t\t\t\t /* update caches modification date for XML interface handling */\n\t\t\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NEW.`last_modified` WHERE `cache_id`=NEW.`cache_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tCALL sp_update_cache_listingdate(NEW.`cache_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('coordinatesBeforeUpdate');
sql("CREATE TRIGGER `coordinatesBeforeUpdate` BEFORE UPDATE ON `coordinates`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite `last_modified` while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('coordinatesAfterUpdate');
sql("CREATE TRIGGER `coordinatesAfterUpdate` AFTER UPDATE ON `coordinates`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tIF NEW.`type`=1 THEN\n\t\t\t\t\t\t\tIF ((ISNULL(@XMLSYNC) OR @XMLSYNC!=1) AND IFNULL(@dont_update_listingdate,0)=0) THEN\n\t\t\t\t\t\t\t /* update caches modification date for XML interface handling */\n\t\t\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NEW.`last_modified` WHERE `cache_id`=NEW.`cache_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tCALL sp_update_cache_listingdate(NEW.`cache_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF OLD.`cache_id`!=NEW.`cache_id` AND OLD.`type`=1 THEN\n\t\t\t\t\t\t\tCALL sp_update_cache_listingdate(OLD.`cache_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('coordinatesAfterDelete');
sql("CREATE TRIGGER `coordinatesAfterDelete` AFTER DELETE ON `coordinates`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tIF OLD.`type`=1 THEN\n\t\t\t\t\t\t\tIF (ISNULL(@XMLSYNC) OR @XMLSYNC!=1) THEN\n\t\t\t\t\t\t\t /* update caches modification date for XML interface handling */\n\t\t\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=OLD.`cache_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tCALL sp_update_cache_listingdate(OLD.`cache_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('savedTextsBeforeInsert');
sql("CREATE TRIGGER `savedTextsBeforeInsert` BEFORE INSERT ON `saved_texts`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite creation date while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tSET NEW.`date_created`=NOW();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheReportsBeforeInsert');
sql("CREATE TRIGGER `cacheReportsBeforeInsert` BEFORE INSERT ON `cache_reports`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite creation date while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tSET NEW.`date_created`=NOW();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('statCachesAfterInsert');
sql("CREATE TRIGGER `statCachesAfterInsert` AFTER INSERT ON `stat_caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* meta_last_modified=NOW() is used to trigger an update of okapi_syncbase,\n\t\t\t\t\t\t if OKAPI is installed. */\n\t\t\t\t\t\tUPDATE caches SET meta_last_modified=NOW() WHERE caches.cache_id=NEW.cache_id;\n\t\t\t\t\tEND;");
sql_dropTrigger('statCachesAfterUpdate');
sql("CREATE TRIGGER `statCachesAfterUpdate` AFTER UPDATE ON `stat_caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tIF NEW.found<>OLD.found OR NEW.notfound<>OLD.notfound OR NEW.note<>OLD.note OR\n\t\t\t\t\t\t NEW.will_attend<>OLD.will_attend OR NEW.last_found<>OLD.last_found OR\n\t\t\t\t\t\t NEW.watch<>OLD.watch OR NEW.ignore<>OLD.ignore OR NEW.toprating<>OLD.toprating THEN\n\t\t\t\t\t\t\t/* meta_last_modified=NOW() is used to trigger an update of okapi_syncbase,\n\t\t\t\t\t\t\t if OKAPI is installed. */\n\t\t\t\t\t\t\tUPDATE caches SET meta_last_modified=NOW() WHERE caches.cache_id=NEW.cache_id;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('gkItemWaypointAfterInsert');
sql("CREATE TRIGGER `gkItemWaypointAfterInsert` AFTER INSERT ON `gk_item_waypoint`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* this triggers an update of okapi_syncbase, if OKAPI is installed */\n\t\t\t\t\t\tUPDATE caches SET meta_last_modified=NOW() WHERE caches.wp_oc=NEW.wp;\n\t\t\t\t\tEND;");
sql_dropTrigger('gkItemWaypointAfterUpdate');
sql("CREATE TRIGGER `gkItemWaypointAfterUpdate` AFTER UPDATE ON `gk_item_waypoint`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* this triggers an update of okapi_syncbase, if OKAPI is installed */\n\t\t\t\t\t\tUPDATE caches SET meta_last_modified=NOW() WHERE caches.wp_oc=OLD.wp;\n\t\t\t\t\t\tUPDATE caches SET meta_last_modified=NOW() WHERE caches.wp_oc=NEW.wp;\n\t\t\t\t\tEND;");
sql_dropTrigger('gkItemWaypointAfterDelete');
sql("CREATE TRIGGER `gkItemWaypointAfterDelete` AFTER DELETE ON `gk_item_waypoint`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* this triggers an update of okapi_syncbase, if OKAPI is installed */\n\t\t\t\t\t\tUPDATE caches SET meta_last_modified=NOW() WHERE caches.wp_oc=OLD.wp;\n\t\t\t\t\tEND;");
// Update trigger version.
// Keep this at the end of this file.
sql_dropFunction('dbsvTriggerVersion');
sql("\n\t\tCREATE FUNCTION `dbsvTriggerVersion` () RETURNS INT\n\t\tRETURN 113");
// re-calculate stat_caches.watch for all entries
sql_dropProcedure('sp_updateall_watchstat');
sql("CREATE PROCEDURE sp_updateall_watchstat (OUT nModified INT)\n\t BEGIN\n\t SET nModified=0;\n\n\t INSERT IGNORE INTO `stat_caches` (`cache_id`) \n\t\t\t\t \t SELECT DISTINCT `cache_id` FROM `cache_watches` \n\t\t\t\t\t UNION \n\t\t\t\t\t SELECT DISTINCT `cache_id` FROM `cache_list_items` \n\t\t\t\t\t WHERE `cache_list_items`.`cache_list_id` IN\n\t\t\t\t\t (SELECT `cache_list_id` FROM `cache_list_watches`); \n\n\t\t\t\t /* initialize temp watch stats with 0 */\n\t\t\t\t DROP TEMPORARY TABLE IF EXISTS `tmp_watchstat`;\n\t\t\t\t CREATE TEMPORARY TABLE `tmp_watchstat` ENGINE=MEMORY (SELECT `cache_id`, 0 AS `watch` FROM `stat_caches`);\n\t\t\t\t ALTER TABLE `tmp_watchstat` ADD PRIMARY KEY (`cache_id`); \n\n\t /* calculate temp stats for all watches caches (no effect for unwatched) */\n\t\t\t\t UPDATE `tmp_watchstat`, \n\t\t\t\t\t\t\t\t(SELECT `cache_id`, COUNT(*) AS `count` FROM \n\t\t\t\t\t\t\t\t\t(SELECT `cache_id`, `user_id` FROM `cache_watches` \n\t\t\t\t\t\t\t\t\t UNION\n\t\t\t\t\t\t\t\t\t SELECT `cache_id`, `user_id` FROM `cache_list_items`, `cache_list_watches`\n\t\t\t\t\t\t\t\t\t WHERE `cache_list_items`.`cache_list_id` = `cache_list_watches`.`cache_list_id`\n\t\t\t\t\t\t\t\t\t) `ws` \n\t\t\t\t\t\t\t\t GROUP BY `cache_id`) `users_watching_caches`\n\t\t\t\t SET `tmp_watchstat`.`watch` = `users_watching_caches`.`count` \n\t\t\t\t WHERE `tmp_watchstat`.`cache_id` = `users_watching_caches`.`cache_id`;\n\n\t\t\t\t /* transfer temp data to stat_caches */\n\t\t\t\t UPDATE `stat_caches`, (SELECT * FROM `tmp_watchstat`) AS `ws`\n\t\t\t\t SET `stat_caches`.`watch` = `ws`.`watch`\n\t\t\t\t WHERE `stat_caches`.`cache_id` = `ws`.`cache_id`;\n\t SET nModified=nModified+ROW_COUNT();\n\n\t\t\t\t DROP TEMPORARY TABLE `tmp_watchstat`;\n\t END;");
sql_dropTrigger('cacheListsBeforeInsert');
sql("CREATE TRIGGER `cacheListsBeforeInsert` BEFORE INSERT ON `cache_lists` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tSET NEW.`date_created`=NOW();\n\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tIF ISNULL(NEW.`uuid`) OR NEW.`uuid`='' THEN\n\t\t\t\t\t\t\tSET NEW.`uuid`=CREATE_UUID();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListsBeforeUpdate');
sql("CREATE TRIGGER `cacheListsBeforeUpdate` BEFORE UPDATE ON `cache_lists` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t IF NEW.`id` != OLD.`id` OR\n\t\t\t\t\t NEW.`uuid` != OLD.`uuid` OR\n\t\t\t\t\t\t NEW.`user_id` != OLD.`user_id` OR\n\t\t\t\t\t\t NEW.`name` != OLD.`name` OR \n\t\t\t\t\t\t\t NEW.`is_public` != OLD.`is_public` THEN\n\t\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF OLD.`is_public` AND NOT NEW.`is_public` THEN\n\t\t\t\t\t\t\tDELETE FROM `cache_list_watches` WHERE `cache_list_watches`.`cache_list_id`=NEW.`id` AND `cache_list_watches`.`user_id` != NEW.`user_id`;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListsBeforeDelete');
sql("CREATE TRIGGER `cacheListsBeforeDelete` BEFORE DELETE ON `cache_lists` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t\tSET @DELETING_CACHELIST=TRUE;\n\t\t\t\t\t\tDELETE FROM `cache_list_watches` WHERE `cache_list_watches`.`cache_list_id`=OLD.`id`;\n\t\t\t\t\t\tDELETE FROM `cache_list_items` WHERE `cache_list_items`.`cache_list_id`=OLD.`id`;\n\t\t\t\t\t\tSET @DELETING_CACHELIST=FALSE;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListItemsAfterInsert');
sql("CREATE TRIGGER `cacheListItemsAfterInsert` AFTER INSERT ON `cache_list_items`\n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tUPDATE `cache_lists` SET `last_modified`=NOW(), `last_added`=NOW(), `entries`=`entries`+1 WHERE `cache_lists`.`id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\tIF (SELECT `user_id` FROM `cache_list_watches` `clw` WHERE `clw`.`cache_list_id`=NEW.`cache_list_id` LIMIT 1) IS NOT NULL THEN\n\t\t\t\t\t\t\t\tCALL sp_update_watchstat(NEW.`cache_id`);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF; \n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListItemsAfterUpdate');
sql("CREATE TRIGGER `cacheListItemsAfterUpdate` AFTER UPDATE ON `cache_list_items` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tUPDATE `cache_lists` SET `last_modified`=NOW() WHERE `cache_lists`.`id`=OLD.`cache_list_id`;\n\t\t\t\t\t\t\tIF NEW.`cache_list_id` != OLD.`cache_list_id` THEN\n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `entries`=`entries`-1 WHERE `cache_lists`.`id`=OLD.`cache_list_id`; \n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `last_modified`=NOW(), `last_added`=NOW(), `entries`=`entries`+1 WHERE `cache_lists`.`id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tIF NEW.`cache_id` != OLD.`cache_id` THEN\n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `last_added`=NOW() WHERE `cache_lists`.`id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tIF (SELECT `user_id` FROM `cache_list_watches` `clw` WHERE `clw`.`cache_list_id`=OLD.`cache_list_id` LIMIT 1) IS NOT NULL THEN\n\t\t\t\t\t\t\t\tCALL sp_update_watchstat(OLD.`cache_id`);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tIF (SELECT `user_id` FROM `cache_list_watches` `clw` WHERE `clw`.`cache_list_id`=NEW.`cache_list_id` LIMIT 1) IS NOT NULL THEN\n\t\t\t\t\t\t\t\tCALL sp_update_watchstat(NEW.`cache_id`);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListItemsAfterDelete');
sql("CREATE TRIGGER `cacheListItemsAfterDelete` AFTER DELETE ON `cache_list_items` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* avoid recursive access to cache_lists */\n\t\t\t\t\t\tIF NOT IFNULL(@DELETING_CACHELIST,FALSE) THEN\n\t\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `last_modified`=NOW(), `entries`=`entries`-1 WHERE `cache_lists`.`id`=OLD.`cache_list_id`;\n\t\t\t\t\t\t\tIF (SELECT `user_id` FROM `cache_list_watches` `clw` WHERE `clw`.`cache_list_id`=OLD.`cache_list_id` LIMIT 1) IS NOT NULL THEN\n\t\t\t\t\t\t\t\tCALL sp_update_watchstat(OLD.`cache_id`);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListWatchesAfterInsert');
sql("CREATE TRIGGER `cacheListWatchesAfterInsert` AFTER INSERT ON `cache_list_watches` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tUPDATE `cache_lists` SET `watchers`=`watchers`+1 WHERE `cache_lists`.`id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\tCALL sp_update_list_watchstat(NEW.`cache_list_id`);\n\t\t\t\t\t\tEND IF; \n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListWatchesAfterUpdate');
sql("CREATE TRIGGER `cacheListWatchesAfterUpdate` AFTER UPDATE ON `cache_list_watches` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\tIF NEW.`cache_list_id` != OLD.`cache_list_id` THEN\n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `watchers`=`watchers`-1 WHERE `cache_lists`.`id`=OLD.`cache_list_id`; \n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `watchers`=`watchers`+1 WHERE `cache_lists`.`id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\t\tCALL sp_update_list_watchstat(OLD.`cache_list_id`);\n\t\t\t\t\t\t\tCALL sp_update_list_watchstat(NEW.`cache_list_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheListWatchesAfterDelete');
sql("CREATE TRIGGER `cacheListWatchesAfterDelete` AFTER DELETE ON `cache_list_watches` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\t/* avoid recursive access to cache_lists */\n\t\t\t\t\t\tIF NOT IFNULL(@DELETING_CACHELIST,FALSE) THEN\n\t\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `watchers`=`watchers`-1 WHERE `cache_lists`.`id`=OLD.`cache_list_id`;\n\t\t\t\t\t\t\tCALL sp_update_list_watchstat(OLD.`cache_list_id`);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheWatchesAfterInsert');
sql("CREATE TRIGGER `cacheWatchesAfterInsert` AFTER INSERT ON `cache_watches` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t\tCALL sp_update_watchstat(NEW.`cache_id`);\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheWatchesAfterUpdate');
sql("CREATE TRIGGER `cacheWatchesAfterUpdate` AFTER UPDATE ON `cache_watches` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t\tIF NEW.`cache_id`!=OLD.`cache_id` THEN\n\t\t\t\t\t\t\tCALL sp_update_watchstat(OLD.`cache_id`);\n\t\t\t\t\t\t\tCALL sp_update_watchstat(NEW.`cache_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheWatchesAfterDelete');
sql("CREATE TRIGGER `cacheWatchesAfterDelete` AFTER DELETE ON `cache_watches` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t\tCALL sp_update_watchstat(OLD.`cache_id`);\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheLogsAfterInsert');
sql("CREATE TRIGGER `cacheLogsAfterInsert` AFTER INSERT ON `cache_logs` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t\tDECLARE done INT DEFAULT 0;\n\t\t\t\t\t\tDECLARE notify_user_id INT;\n\t\t\t\t\t\tDECLARE cur1 CURSOR FOR\n\t\t\t\t\t\t\t/* watches from `cache_watches` */ \n\t\t\t\t\t\t\tSELECT `cache_watches`.`user_id` \n\t\t\t\t\t\t\tFROM `cache_watches` \n\t\t\t\t\t\t\tINNER JOIN `caches` ON `cache_watches`.`cache_id`=`caches`.`cache_id` \n\t\t\t\t\t\t\tINNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` \n\t\t\t\t\t\t\tWHERE `cache_watches`.`cache_id`=NEW.cache_id AND `cache_status`.`allow_user_view`=1\n\t\t\t\t\t\t\tUNION /* UNION discards duplicates */\n\t\t\t\t\t\t\t/* watches from `cache_list_watches` */\n\t\t\t\t\t\t\tSELECT `clw`.`user_id` FROM `cache_list_watches` `clw` \n\t\t\t\t\t\t\tINNER JOIN `cache_list_items` `cli` ON `clw`.`cache_list_id`=`cli`.`cache_list_id`\n\t\t\t\t\t\t\tINNER JOIN `caches` ON `cli`.`cache_id`=`caches`.`cache_id` \n\t\t\t\t\t\t\tINNER JOIN `cache_status` ON `caches`.`status`=`cache_status`.`id` \n\t\t\t\t\t\t\tWHERE `cli`.`cache_id`=NEW.cache_id AND `cache_status`.`allow_user_view`=1;\n\t\t\t\t\t\tDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n\n\t\t\t\t\t\tCALL sp_update_logstat(NEW.`cache_id`, NEW.`user_id`, NEW.`type`, FALSE);\n\n\t\t\t\t\t\tOPEN cur1;\n\t\t\t\t\t\tREPEAT\n\t\t\t\t\t\t\tFETCH cur1 INTO notify_user_id;\n\t\t\t\t\t\t\tIF NOT done THEN\n\t\t\t\t\t\t\t\tINSERT INTO `watches_logqueue` (`log_id`, `user_id`) VALUES (NEW.id, notify_user_id);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tUNTIL done END REPEAT;\n\t\t\t\t\t\tCLOSE cur1;\n\t\t\t\t\tEND;");
sql_dropTrigger('userBeforeDelete');
sql("CREATE TRIGGER `userBeforeDelete` BEFORE DELETE ON `user` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tDELETE FROM `cache_adoption` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `cache_ignore` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `cache_rating` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `cache_watches` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `cache_lists` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `stat_user` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `user_options` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `user_statpic` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `watches_waiting` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\t\tDELETE FROM `notify_waiting` WHERE `user_id`=OLD.user_id;\n\t\t\t\t\tEND;");
<?php
/***************************************************************************
Unicode Reminder メモ
update functions and procedures for cache lists
***************************************************************************/
// We run this via maintain.php instead of dbsv-update.php because the
// latter one has no sufficient privileges yet for updating functions
// (should be changed / may have been changed when you are reading this.)
// added node, description, desc_htmledit and last_to_private fields
sql_dropTrigger('cacheListsBeforeUpdate');
sql("CREATE TRIGGER `cacheListsBeforeUpdate` BEFORE UPDATE ON `cache_lists` \n\t\t\t\tFOR EACH ROW \n\t\t\t\t\tBEGIN \n\t\t\t\t\t IF NEW.`id` != OLD.`id` THEN\n\t\t\t\t\t\t\tCALL error_cache_list_id_must_not_be_changed();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF NEW.`uuid` != OLD.`uuid` OR\n\t\t\t\t\t\t NEW.`node` != OLD.`node` OR\n\t\t\t\t\t\t NEW.`user_id` != OLD.`user_id` OR\n\t\t\t\t\t\t NEW.`name` != OLD.`name` OR \n\t\t\t\t\t\t\t NEW.`is_public` != OLD.`is_public` OR\n\t\t\t\t\t\t\t NEW.`description` != OLD.`description` OR\n\t\t\t\t\t\t\t NEW.`desc_htmledit` != OLD.`desc_htmledit` THEN\n\t\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\n\t\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\n\t\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF OLD.`is_public` != NEW.`is_public` THEN\n\t\t\t\t\t\t\tSET NEW.`last_state_change`=NOW(); /* for XML interface */\n\t\t\t\t\t\t\tIF NOT NEW.`is_public` THEN\n\t\t\t\t\t\t\t\tDELETE FROM `cache_list_watches` WHERE `cache_list_watches`.`cache_list_id`=NEW.`id` AND `cache_list_watches`.`user_id` != NEW.`user_id`;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND;");
// new trigger
sql_dropTrigger('cacheListsAfterDelete');
sql("CREATE TRIGGER `cacheListsAfterDelete` AFTER DELETE ON `cache_lists` \n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tINSERT IGNORE INTO `removed_objects` (`localId`, `uuid`, `type`, `node`) VALUES (OLD.`id`, OLD.`uuid`, 8, OLD.`node`);\n\t\t\t\t\tEND;");
sql("CREATE TRIGGER `userBeforeInsert` BEFORE INSERT ON `user` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\r\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\r\n\t\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\r\n\t\t\t\t\t\tEND IF;\r\n\t\t\t\t\tEND;");
sql_dropTrigger('userBeforeUpdate');
sql("CREATE TRIGGER `userBeforeUpdate` BEFORE UPDATE ON `user` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\t/* dont overwrite date values while XML client is running */\r\n\t\t\t\t\t\tIF ISNULL(@XMLSYNC) OR @XMLSYNC!=1 THEN\r\n\t\t\t\t\t\t\tIF NEW.`user_id`!=OLD.`user_id` OR \r\n\t\t\t\t\t\t\t NEW.`uuid`!=OLD.`uuid` OR \r\n\t\t\t\t\t\t\t NEW.`node`!=OLD.`node` OR \r\n\t\t\t\t\t\t\t NEW.`date_created`!=OLD.`date_created` OR \r\n\t\t\t\t\t\t\t NEW.`username`!=OLD.`username` OR \r\n\t\t\t\t\t\t\t NEW.`pmr_flag`!=OLD.`pmr_flag` THEN\r\n\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\r\n\t\t\t\t\t\t\tEND IF;\r\n\t\t\t\t\t\tEND IF;\r\n\t\t\t\t\tEND;");
sql_dropTrigger('userAfterDelete');
sql("CREATE TRIGGER `userAfterDelete` AFTER DELETE ON `user` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tINSERT IGNORE INTO `removed_objects` (`localId`, `uuid`, `type`, `node`) VALUES (OLD.`user_id`, OLD.`uuid`, 4, OLD.`node`);\r\n\t\t\t\t\tEND;");
sql_dropTrigger('userDelegatesBeforeInsert');
sql("CREATE TRIGGER `userDelegatesBeforeInsert` BEFORE INSERT ON `user_delegates` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('userDelegatesBeforeUpdate');
sql("CREATE TRIGGER `userDelegatesBeforeUpdate` BEFORE UPDATE ON `user_delegates` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('watchesNotifiedBeforeInsert');
sql("CREATE TRIGGER `watchesNotifiedBeforeInsert` BEFORE INSERT ON `watches_notified` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('watchesWaitingBeforeInsert');
sql("CREATE TRIGGER `watchesWaitingBeforeInsert` BEFORE INSERT ON `watches_waiting` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('xmlsessionBeforeInsert');
sql("CREATE TRIGGER `xmlsessionBeforeInsert` BEFORE INSERT ON `xmlsession` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheAdoptionBeforeInsert');
sql("CREATE TRIGGER `cacheAdoptionBeforeInsert` BEFORE INSERT ON `cache_adoption` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheAdoptionBeforeUpdate');
sql("CREATE TRIGGER `cacheAdoptionBeforeUpdate` BEFORE UPDATE ON `cache_adoption` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('userStatpicBeforeInsert');
sql("CREATE TRIGGER `userStatpicBeforeInsert` BEFORE INSERT ON `user_statpic` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tSET NEW.`date_created`=NOW();\r\n\t\t\t\t\tEND;");
sql_dropTrigger('sysSessionsAfterInsert');
sql("CREATE TRIGGER `sysSessionsAfterInsert` AFTER INSERT ON `sys_sessions` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tUPDATE `user` SET `user`.`last_login`=NEW.`last_login` WHERE `user`.`user_id`=NEW.`user_id`;\r\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheAttributesAfterInsert');
sql("CREATE TRIGGER `cacheAttributesAfterInsert` AFTER INSERT ON `caches_attributes` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=NEW.`cache_id`;\r\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheAttributesAfterUpdate');
sql("CREATE TRIGGER `cacheAttributesAfterUpdate` AFTER UPDATE ON `caches_attributes` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=NEW.`cache_id`;\r\n\t\t\t\t\t\tIF OLD.`cache_id`!=NEW.`cache_id` THEN\r\n\t\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=OLD.`cache_id`;\r\n\t\t\t\t\t\tEND IF;\r\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheAttributesAfterDelete');
sql("CREATE TRIGGER `cacheAttributesAfterDelete` AFTER DELETE ON `caches_attributes` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tUPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=OLD.`cache_id`;\r\n\t\t\t\t\tEND;");
sql_dropTrigger('map2resultAfterDelete');
sql("CREATE TRIGGER `map2resultAfterDelete` AFTER DELETE ON `map2_result` \r\n\t\t\t\tFOR EACH ROW \r\n\t\t\t\t\tBEGIN \r\n\t\t\t\t\t\tDELETE FROM `map2_data` WHERE `result_id`=OLD.`result_id`;\r\n\t\t\t\t\tEND;");