install/update functions and procedures for cache lists & list watching
***************************************************************************/
// 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.)
sql_dropProcedure('sp_updateall_cachelist_counts');
sql("CREATE PROCEDURE sp_updateall_cachelist_counts (OUT nModified INT)\n\t BEGIN\n\t\t\t\tUPDATE `cache_lists` SET `entries`=\n\t\t\t\t\t(SELECT COUNT(*) from `cache_list_items` WHERE `cache_list_items`.`cache_list_id`=`cache_lists`.`id`); \n\t\t\t\tSET nModified = ROW_COUNT();\n\t\t\t\tUPDATE `cache_lists` SET `watchers`=\n\t\t\t\t\t(SELECT COUNT(*) from `cache_list_watches` WHERE `cache_list_watches`.`cache_list_id`=`cache_lists`.`id`); \n\t\t\t\tSET nModified = nModified + ROW_COUNT();\n\t END;");
// re-calculate stat_caches.watch for one cache
sql_dropProcedure('sp_update_watchstat');
sql("CREATE PROCEDURE sp_update_watchstat (IN nCacheId INT)\n\t BEGIN\n\t\t\t DECLARE nWatches INT DEFAULT 0;\n\t\t\t\t SET nWatches =\n\t\t\t\t\t(SELECT COUNT(*) FROM\n\t\t\t\t\t\t(SELECT `cache_list_watches`.`user_id` \n\t\t\t\t\t\t FROM `cache_list_watches`, `cache_lists`, `cache_list_items`\n\t\t\t\t\t\t WHERE `cache_list_items`.`cache_id`=nCacheId AND `cache_lists`.`id`=`cache_list_items`.`cache_list_id` AND `cache_list_watches`.`cache_list_id`=`cache_lists`.`id`\n\t\t\t\t\t\t UNION /* UNION discards duplicates */\n\t\t\t\t\t\t SELECT `user_id` FROM `cache_watches` WHERE `cache_id`=nCacheId) AS `wu`); \n\t\t\t UPDATE `stat_caches` SET `stat_caches`.`watch` = nWatches WHERE `cache_id`=nCacheId;\n\t\t\t IF ROW_COUNT() = 0 THEN\n\t\t\t INSERT IGNORE INTO `stat_caches` (`cache_id`, `watch`) VALUES (nCacheId, nWatches);\n\t\t\t END IF;\n\t END;");
// re-calculate stat_caches.watch for all entries of a cache list
sql_dropProcedure('sp_update_list_watchstat');
sql("CREATE PROCEDURE sp_update_list_watchstat (IN nCachelistId INT)\n\t BEGIN\n\t\t\t\t\tDECLARE done INT DEFAULT 0;\n\t\t\t\t\tDECLARE cacheid INT DEFAULT 0;\n\t\t\t\t\tDECLARE cur1 CURSOR FOR SELECT `cache_id` FROM `cache_list_items` WHERE `cache_list_id` = nCachelistId;\n\t\t\t\t\tDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n\t\t\t\t\tOPEN cur1;\n\t\t\t\t\tREPEAT\n\t\t\t\t\t\tFETCH cur1 INTO cacheid;\n\t\t\t\t\t\tIF NOT done THEN\n\t\t\t\t\t\t\tCALL sp_update_watchstat(cacheid); \n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tUNTIL done END REPEAT;\n\t\t\t\t\tCLOSE cur1;\n\t END;");
// 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;");
// recalc picture of cache_logs for all entries
sql_dropProcedure('sp_updateall_cachelog_picturestat');
sql("CREATE PROCEDURE sp_updateall_cachelog_picturestat (OUT nModified INT)\n\t BEGIN\n\t SET nModified=0;\n\n\t /* cache_logs.picture */\n\t UPDATE `cache_logs`, (SELECT `object_id` AS `log_id`, COUNT(*) AS `count` FROM `pictures` WHERE `object_type`=1 GROUP BY `object_type`, `object_id`) AS `tblPictures` SET `cache_logs`.`picture`=`tblPictures`.`count` WHERE `cache_logs`.`id`=`tblPictures`.`log_id`;\n\t SET nModified=nModified+ROW_COUNT();\n\t END;");
// Update out-of-sync rating dates. These probably were caused by rating-related
// bugs when deleting one of multiple found logs and when changing the log type
// (9 mismatches within ~9 months up to June 2013).
sql_dropProcedure('sp_updateall_rating_dates');
sql("CREATE PROCEDURE sp_updateall_rating_dates (OUT nModified INT)\n\t BEGIN\n\t UPDATE `cache_rating` SET `rating_date` =\n\t (SELECT `date` FROM `cache_logs` WHERE `cache_logs`.`cache_id`=`cache_rating`.`cache_id` AND `cache_logs`.`user_id`=`cache_rating`.`user_id` AND `cache_logs`.`type` IN (1,7) ORDER BY `date` LIMIT 1)\n\t WHERE (SELECT COUNT(*) FROM `cache_logs` WHERE `cache_logs`.`cache_id`=`cache_rating`.`cache_id` AND `cache_logs`.`user_id`=`cache_rating`.`user_id` AND `cache_logs`.`date`=`cache_rating`.`rating_date` AND `type` IN (1,7))=0;\n\t /* will set rating_date to 0000-00...:00 for orphan records */\n\t SET nModified=ROW_COUNT();\n\t END;");
// notify users with matching watch radius about this cache
sql_dropProcedure('sp_notify_new_cache');
sql("CREATE PROCEDURE sp_notify_new_cache (IN nCacheId INT(10) UNSIGNED, IN nLongitude DOUBLE, IN nLatitude DOUBLE)\n\t BEGIN\n\t INSERT IGNORE INTO `notify_waiting` (`id`, `cache_id`, `user_id`, `type`)\n\t SELECT NULL, nCacheId, `user`.`user_id`, 1 /* notify_new_cache */\n\t FROM `user`\n /* Throttle email sending after undeliverable mails. See also runwatch.php. */\n\t WHERE (`email_problems` = 0 OR DATEDIFF(NOW(),`last_email_problem`) > 1+DATEDIFF(`last_email_problem`,`first_email_problem`))\n\t AND NOT ISNULL(`user`.`latitude`)\n\t AND NOT ISNULL(`user`.`longitude`)\n\t AND `user`.`notify_radius`>0\n\t AND (acos(cos((90-nLatitude) * 3.14159 / 180) * cos((90-`user`.`latitude`) * 3.14159 / 180) + sin((90-nLatitude) * 3.14159 / 180) * sin((90-`user`.`latitude`) * 3.14159 / 180) * cos((nLongitude-`user`.`longitude`) * 3.14159 / 180)) * 6370) <= `user`.`notify_radius`;\n\t END;");
// recreate the user statpic on next request
sql_dropProcedure('sp_refresh_statpic');
sql("CREATE PROCEDURE sp_refresh_statpic (IN nUserId INT(10) UNSIGNED)\n\t BEGIN\n\t\t\t DELETE FROM `user_statpic` WHERE `user_id`=nUserId;\n\t END;");
// recreate all user statpic on next request
sql_dropProcedure('sp_refreshall_statpic');
sql("CREATE PROCEDURE sp_refreshall_statpic ()\n\t BEGIN\n\t\t\t DELETE FROM `user_statpic`;\n\t END;");
/* Triggers
*/
sql_dropTrigger('cachesBeforeInsert');
sql("CREATE TRIGGER `cachesBeforeInsert` BEFORE INSERT ON `caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tSET @dont_update_listingdate=1;\n\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\t\tSET NEW.`listing_last_modified`=NOW();\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF NEW.`status` <> 5 THEN\n\t\t\t\t\t\t\tSET NEW.`is_publishdate`=1;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF SUBSTR(TRIM(NEW.`wp_gc`),1,2)='GC' THEN\n\t\t\t\t\t\t\tSET NEW.`wp_gc_maintained`=UCASE(TRIM(NEW.`wp_gc`));\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tSET NEW.`need_npa_recalc`=1;\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\n\t\t\t\t\t\t/* reserve and set cache waypoint\n\t\t\t\t\t\t *\n\t\t\t\t\t\t * Table cache_waypoint_pool is used to prevent race conditions\n\t\t\t\t\t\t * when 2 caches will be inserted simultaneously\n\t\t\t\t\t\t */\n\t\t\t\t\t\tIF ISNULL(NEW.`wp_oc`) OR NEW.`wp_oc`='' THEN\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t/* cleanup previous assignments failures /*\n\t\t\t\t\t\t\tDELETE FROM `cache_waypoint_pool` WHERE `uuid`=NEW.`uuid`;\n\n\t\t\t\t\t\t\t/* reserve a waypoint */\n\t\t\t\t\t\t\tUPDATE `cache_waypoint_pool` SET `uuid`=NEW.`uuid` WHERE `uuid` IS NULL ORDER BY WPTODEC(`wp_oc`, '&1') ASC LIMIT 1;\n\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tIF (SELECT COUNT(*) FROM `cache_waypoint_pool` WHERE `uuid`=NEW.`uuid`) = 0 THEN\n\n\t\t\t\t\t\t\t\t/* waypoint reservation was not successfull. Maybe we are on a development machine, where cronjob for waypoint pool\n\t\t\t\t\t\t\t\t * generation did not run or the pool is empty. To get a valid waypoint, we simply increment the highest used waypoint by one.\n\t\t\t\t\t\t\t\t * NOTE: This ignores the setting of {$opt['logic']}[waypoint_pool][fill_gaps]\n\t\t\t\t\t\t\t\t * CAUTION: This statement is realy slow and you should always keep your waypoint pool filled with some waypoint on a production server\n\t\t\t\t\t\t\t\t */\n\t\t\t\t\t\t\t\tINSERT INTO `cache_waypoint_pool` (`wp_oc`, `uuid`)\n\t\t\t\t\t\t\t\t\tSELECT DECTOWP(MAX(`dec_wp`)+1, '&1'), NEW.`uuid` AS `uuid`\n\t\t\t\t\t\t\t\t\t\tFROM (\n\t\t\t\t\t\t\t\t\t\t\t\t SELECT MAX(WPTODEC(`wp_oc`, '&1')) AS dec_wp FROM `caches` WHERE `wp_oc` REGEXP '&2'\n\t\t\t\t\t\t\t\t\t\t\tUNION SELECT MAX(WPTODEC(`wp_oc`, '&1')) AS dec_wp FROM `cache_waypoint_pool`\n\t\t\t\t\t\t\t\t\t\t) AS `tbl`;\n\n\t\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\t\t/* query and assign the reserved waypoint */\n\t\t\t\t\t\t\tSET NEW.`wp_oc` = (SELECT `wp_oc` FROM `cache_waypoint_pool` WHERE `uuid`=`NEW`.`uuid`);\n\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tSET @dont_update_listingdate=0;\n\t\t\t\t\tEND;", $opt['logic']['waypoint_pool']['prefix'], '^' . $opt['logic']['waypoint_pool']['prefix'] . '[' . $opt['logic']['waypoint_pool']['valid_chars'] . ']{1,}$');
sql_dropTrigger('cachesAfterInsert');
sql("CREATE TRIGGER `cachesAfterInsert` AFTER INSERT ON `caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tSET @dont_update_listingdate=1;\n\n\t\t\t\t\t\tINSERT IGNORE INTO `cache_coordinates` (`cache_id`, `date_created`, `longitude`, `latitude`)\n\t\t\t\t\t\t VALUES (NEW.`cache_id`, NOW(), NEW.`longitude`, NEW.`latitude`);\n\t\t\t\t\t\tINSERT IGNORE INTO `cache_countries` (`cache_id`, `date_created`, `country`)\n\t\t\t\t\t\t VALUES (NEW.`cache_id`, NOW(), NEW.`country`);\n\n\t\t\t\t\t\tCALL sp_update_hiddenstat(NEW.`user_id`, NEW.`status`, FALSE);\n\n\t\t\t\t\t\tIF NEW.`status`=1 THEN\n\t\t\t\t\t\t CALL sp_notify_new_cache(NEW.`cache_id`, NEW.`longitude`, NEW.`latitude`);\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\t/* cleanup/delete reserved waypoint */\n\t\t\t\t\t\tDELETE FROM `cache_waypoint_pool` WHERE `uuid`=NEW.`uuid`;\n\n\t\t\t\t\t\tSET @dont_update_listingdate=0;\n\t\t\t\t\tEND;");
sql_dropTrigger('cachesBeforeUpdate');
sql("CREATE TRIGGER `cachesBeforeUpdate` BEFORE UPDATE ON `caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tSET @dont_update_listingdate=1;\n\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 OLD.`cache_id`!=NEW.`cache_id` OR\n\t\t\t\t\t\t\t OLD.`uuid`!=NEW.`uuid` OR\n\t\t\t\t\t\t\t OLD.`node`!=NEW.`node` OR\n\t\t\t\t\t\t\t OLD.`date_created`!=NEW.`date_created` OR\n\t\t\t\t\t\t\t OLD.`is_publishdate`!=NEW.`is_publishdate` OR\n\t\t\t\t\t\t\t OLD.`user_id`!=NEW.`user_id` OR\n\t\t\t\t\t\t\t OLD.`name`!=NEW.`name` OR\n\t\t\t\t\t\t\t OLD.`longitude`!=NEW.`longitude` OR\n\t\t\t\t\t\t\t OLD.`latitude`!=NEW.`latitude` OR\n\t\t\t\t\t\t\t OLD.`type`!=NEW.`type` OR\n\t\t\t\t\t\t\t OLD.`status`!=NEW.`status` OR\n\t\t\t\t\t\t\t OLD.`country`!=NEW.`country` OR\n\t\t\t\t\t\t\t OLD.`date_hidden`!=NEW.`date_hidden` OR\n\t\t\t\t\t\t\t OLD.`size`!=NEW.`size` OR\n\t\t\t\t\t\t\t OLD.`difficulty`!=NEW.`difficulty` OR\n\t\t\t\t\t\t\t OLD.`terrain`!=NEW.`terrain` OR\n\t\t\t\t\t\t\t OLD.`logpw`!=NEW.`logpw` OR\n\t\t\t\t\t\t\t OLD.`search_time`!=NEW.`search_time` OR\n\t\t\t\t\t\t\t OLD.`way_length`!=NEW.`way_length` OR\n\t\t\t\t\t\t\t OLD.`wp_gc`!=NEW.`wp_gc` OR\n\t\t\t\t\t\t\t\t /* See notes on wp_gc_maintained in modification-dates.txt. */\n\t\t\t\t\t\t\t OLD.`wp_nc`!=NEW.`wp_nc` OR\n\t\t\t\t\t\t\t OLD.`wp_oc`!=NEW.`wp_oc` OR\n\t\t\t\t\t\t\t OLD.`default_desclang`!=NEW.`default_desclang` OR\n\t\t\t\t\t\t\t OLD.`date_activate`!=NEW.`date_activate` THEN\n\n\t\t\t\t\t\t\t\tSET NEW.`last_modified`=NOW();\n\t\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\t\tIF NEW.`last_modified` != OLD.`last_modified` THEN\n\t\t\t\t\t\t\t\tSET NEW.`listing_last_modified`=NOW();\n\t\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\t\tIF OLD.`status`!=NEW.`status` THEN\n\t\t\t\t\t\t\t\tCALL sp_touch_cache(OLD.`cache_id`, FALSE);\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tIF NEW.`wp_gc`<>OLD.`wp_gc` AND\n\t\t\t\t\t\t (SUBSTR(TRIM(NEW.`wp_gc`),1,2)='GC' OR TRIM(NEW.`wp_gc`)='') THEN\n\t\t\t\t\t\t\tSET NEW.`wp_gc_maintained`=UCASE(TRIM(NEW.`wp_gc`));\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tIF OLD.`longitude`!=NEW.`longitude` OR\n\t\t\t\t\t\t OLD.`latitude`!=NEW.`latitude` THEN\n\t\t\t\t\t\t\tSET NEW.`need_npa_recalc`=1;\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tIF OLD.`status`=5 AND NEW.`status`<>5 THEN\n\t\t\t\t\t\t\tSET NEW.`date_created`=NOW();\n\t\t\t\t\t\t\tSET NEW.`is_publishdate`=1;\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tSET @dont_update_listingdate=0;\n\t\t\t\t\tEND;");
sql_dropTrigger('cachesAfterUpdate');
sql("CREATE TRIGGER `cachesAfterUpdate` AFTER UPDATE ON `caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tSET @dont_update_listingdate=1;\n\n\t\t\t\t\t\tIF NEW.`longitude` != OLD.`longitude` OR NEW.`latitude` != OLD.`latitude` THEN\n\t\t\t\t\t\t\tINSERT IGNORE INTO `cache_coordinates` (`cache_id`, `date_created`, `longitude`, `latitude`, `restored_by`)\n\t\t\t\t\t\t\t\tVALUES (NEW.`cache_id`, NOW(), NEW.`longitude`, NEW.`latitude`, IFNULL(@restoredby,0));\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF NEW.`country` != OLD.`country` THEN\n\t\t\t\t\t\t\tINSERT IGNORE INTO `cache_countries` (`cache_id`, `date_created`, `country`, `restored_by`)\n\t\t\t\t\t\t\t\tVALUES (NEW.`cache_id`, NOW(), NEW.`country`, IFNULL(@restoredby,0));\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF NEW.`cache_id` = OLD.`cache_id` AND\n\t\t\t\t\t\t OLD.`status` <> 5 AND\n\t\t\t\t\t\t\t OLD.`date_created` < LEFT(NOW(),10) AND\n\t\t\t\t\t\t\t (NEW.`name` != OLD.`name` OR NEW.`type` != OLD.`type` OR NEW.`date_hidden` != OLD.`date_hidden` OR NEW.`size` != OLD.`size` OR NEW.`difficulty` != OLD.`difficulty` OR NEW.`terrain` != OLD.`terrain` OR NEW.`search_time` != OLD.`search_time` OR NEW.`way_length` != OLD.`way_length` OR NEW.`wp_gc` != OLD.`wp_gc` OR NEW.`wp_nc` != OLD.`wp_nc`)\n\t\t\t\t\t\t\t THEN\n\t\t\t\t\t\t\tINSERT IGNORE INTO `caches_modified` (`cache_id`, `date_modified`, `name`, `type`, `date_hidden`, `size`, `difficulty`, `terrain`, `search_time`, `way_length`, `wp_gc`, `wp_nc`, `restored_by`) VALUES (OLD.`cache_id`, NOW(), OLD.`name`, OLD.`type`, OLD.`date_hidden`, OLD.`size`, OLD.`difficulty`, OLD.`terrain`, OLD.`search_time`, OLD.`way_length`, OLD.`wp_gc`, OLD.`wp_nc`, IFNULL(@restoredby,0));\n\t\t\t\t\t\t\t/* logpw needs not to be saved */\n\t\t\t\t\t\t\t/* for further explanation see restorecaches.php */\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF NEW.`user_id`!=OLD.`user_id` THEN\n\t\t\t\t\t\t\tINSERT INTO `cache_adoptions` (`cache_id`,`date`,`from_user_id`,`to_user_id`)\n\t\t\t\t\t\t\t\tVALUES (NEW.`cache_id`, NEW.`last_modified`, OLD.`user_id`, NEW.`user_id`);\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tIF NEW.`user_id`!=OLD.`user_id` OR NEW.`status`!=OLD.`status` THEN\n\t\t\t\t\t\t\tCALL sp_update_hiddenstat(OLD.`user_id`, OLD.`status`, TRUE);\n\t\t\t\t\t\t\tCALL sp_update_hiddenstat(NEW.`user_id`, NEW.`status`, FALSE);\n\t\t\t\t\t\tEND IF;\n IF OLD.`status`=5 AND NEW.`status`=1 THEN\n CALL sp_notify_new_cache(NEW.`cache_id`, NEW.`longitude`, NEW.`latitude`);\n END IF;\n IF NEW.`status`<>OLD.`status` THEN\n \tINSERT INTO `cache_status_modified` (`cache_id`, `date_modified`, `old_state`, `new_state`, `user_id`) VALUES (NEW.`cache_id`, NOW(), OLD.`status`, NEW.`status`, IFNULL(@STATUS_CHANGE_USER_ID,0));\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tSET @dont_update_listingdate=0;\n\t\t\t\t\tEND;");
sql_dropTrigger('cachesAfterDelete');
sql("CREATE TRIGGER `cachesAfterDelete` AFTER DELETE ON `caches`\n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tSET @dont_update_listingdate=1;\n\n\t\t\t\t\t\t/* lots of things are missing here - descs, logs, pictures ...\n\t\t\t\t\t\t also, the depending deletions should be done BEFORE deleting from caches! */\n\n\t\t\t\t\t\tDELETE FROM `cache_coordinates` WHERE `cache_id`=OLD.`cache_id`;\n\t\t\t\t\t\tDELETE FROM `cache_countries` WHERE `cache_id`=OLD.`cache_id`;\n\t\t\t\t\t\tDELETE FROM `cache_npa_areas` WHERE `cache_id`=OLD.`cache_id`;\n\t\t\t\t\t\tDELETE FROM `caches_modified` WHERE `cache_id`=OLD.`cache_id`;\n\t\t\t\t\t\tCALL sp_update_hiddenstat(OLD.`user_id`, OLD.`status`, TRUE);\n\t\t\t\t\t\tINSERT IGNORE INTO `removed_objects` (`localId`, `uuid`, `type`, `node`) VALUES (OLD.`cache_id`, OLD.`uuid`, 2, OLD.`node`);\n\n\t\t\t\t\t\tSET @dont_update_listingdate=0;\n\t\t\t\t\tEND;");
sql_dropTrigger('cacheDescBeforeInsert');
sql("CREATE TRIGGER `cacheDescBeforeInsert` BEFORE INSERT ON `cache_desc`\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\t\t\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;");
<?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.)
sql_dropProcedure('sp_updateall_cachelist_counts');
sql("CREATE PROCEDURE sp_updateall_cachelist_counts (OUT nModified INT)\n\t BEGIN\n\t\t\t\tUPDATE `stat_cache_lists` SET `entries`=\n\t\t\t\t\t(SELECT COUNT(*) from `cache_list_items` WHERE `cache_list_items`.`cache_list_id`=`stat_cache_lists`.`cache_list_id`); \n\t\t\t\tSET nModified = ROW_COUNT();\n\t\t\t\tUPDATE `stat_cache_lists` SET `watchers`=\n\t\t\t\t\t(SELECT COUNT(*) from `cache_list_watches` WHERE `cache_list_watches`.`cache_list_id`=`stat_cache_lists`.`cache_list_id`); \n\t\t\t\tSET nModified = nModified + ROW_COUNT();\n\t END;");
sql_dropTrigger('cacheListsAfterInsert');
sql("CREATE TRIGGER `cacheListsAfterInsert` AFTER INSERT ON `cache_lists` \n\t\t\t\tFOR EACH ROW\n\t\t\t\t\tBEGIN\n\t\t\t\t\t\tINSERT INTO `stat_cache_lists` (`cache_list_id`) VALUES (NEW.`id`);\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 `stat_cache_lists` WHERE `cache_list_id`=OLD.`id`;\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() WHERE `cache_lists`.`id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\tUPDATE `stat_cache_lists` SET `entries`=`entries`+1 WHERE `stat_cache_lists`.`cache_list_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 `stat_cache_lists` SET `entries`=`entries`-1 WHERE `stat_cache_lists`.`cache_list_id`=OLD.`cache_list_id`; \n\t\t\t\t\t\t\t\tUPDATE `stat_cache_lists` SET `entries`=`entries`+1 WHERE `stat_cache_lists`.`cache_list_id`=NEW.`cache_list_id`;\n\t\t\t\t\t\t\t\tUPDATE `cache_lists` SET `last_modified`=NOW(), `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; optimization */\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 `stat_cache_lists` SET `entries`=`entries`-1 WHERE `stat_cache_lists`.`cache_list_id`=OLD.`cache_list_id`;\n\t\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\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\t\tCALL sp_update_watchstat(OLD.`cache_id`);\n\t\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 `stat_cache_lists` SET `watchers`=`watchers`+1 WHERE `stat_cache_lists`.`cache_list_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 `stat_cache_lists` SET `watchers`=`watchers`-1 WHERE `stat_cache_lists`.`cache_list_id`=OLD.`cache_list_id`; \n\t\t\t\t\t\t\t\tUPDATE `stat_cache_lists` SET `watchers`=`watchers`+1 WHERE `stat_cache_lists`.`cache_list_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; optimization */\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 `stat_cache_lists` SET `watchers`=`watchers`-1 WHERE `stat_cache_lists`.`cache_list_id`=OLD.`cache_list_id`;\n\t\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;");
