Unicode Reminder メモ
Ggf. muss die Location des php-Binaries angepasst werden.
Erstellt stored procedures.
***************************************************************************/
$rootpath = '../../../';
require_once $rootpath . 'lib/clicompatbase.inc.php';
if (!file_exists($rootpath . 'util/mysql_root/sql_root.inc.php')) {
die("\n" . 'install util/mysql_root/sql_root.inc.php' . "\n\n");
}
require_once $rootpath . 'util/mysql_root/sql_root.inc.php';
/* begin db connect */
db_root_connect();
if ($dblink === false) {
echo 'Unable to connect to database';
exit;
}
/* end db connect */
sql_dropFunction('distance');
sql("CREATE FUNCTION `distance` (lat1 DOUBLE, lon1 DOUBLE, lat2 DOUBLE, lon2 DOUBLE) RETURNS DOUBLE DETERMINISTIC \n\t BEGIN\n\t RETURN ACOS(COS((90-lat1) * 3.14159 / 180) * COS((90-lat2)* 3.14159 / 180) + SIN((90-lat1) * 3.14159 / 180) * SIN((90-lat2) * 3.14159 / 180) * COS((lon1-lon2) * 3.14159 / 180)) * 6370;\n\t\t\t END;");
sql_dropFunction('projLon');
sql("CREATE FUNCTION `projLon` (nLat DOUBLE, nLon DOUBLE, nDistance DOUBLE, nAngle DOUBLE) RETURNS DOUBLE DETERMINISTIC \n\t BEGIN\n\t\t\t DECLARE nLatProj DOUBLE DEFAULT 0;\n\t\t\t DECLARE nDeltaLon DOUBLE DEFAULT 0;\n\t\t\t DECLARE nLonProj DOUBLE DEFAULT 0;\n\n\t SET nLat = nLat * 3.141592654 / 180;\n\t SET nLon = nLon * 3.141592654 / 180;\n\t SET nAngle = nAngle * 3.141592654 / 180;\n\t SET nDistance = (3.141592654/ (180 * 60)) * nDistance / 1.852;\n\n\t SET nLatProj = asin(sin(nLat) * cos(nDistance) + cos(nLat) * sin(nDistance) * cos(nAngle));\n\t SET nDeltaLon = -1 * (atan2(sin(nAngle) * sin(nDistance) * cos(nLat), cos(nDistance) - sin(nLat) * sin(nLatProj)));\n\t SET nLonProj = (nLon - nDeltaLon + 3.141592654) - floor((nLon - nDeltaLon + 3.141592654) / 2 / 3.141592654) - 3.141592654;\n\n\t return nLonProj * 180 / 3.141592654;\n\t\t\t END;");
sql_dropFunction('projLat');
sql("CREATE FUNCTION `projLat` (nLat DOUBLE, nLon DOUBLE, nDistance DOUBLE, nAngle DOUBLE) RETURNS DOUBLE DETERMINISTIC \n\t BEGIN\n\t\t\t\t\tDECLARE nLatProj DOUBLE DEFAULT 0;\n\n\t\t\t\t\tSET nLat = nLat * 3.141592654 / 180;\n\t\t\t\t\tSET nLon = nLon * 3.141592654 / 180;\n\t\t\t\t\tSET nAngle = nAngle * 3.141592654 / 180;\n\t\t\t\t\tSET nDistance = (3.141592654 / (180 * 60)) * nDistance / 1.852;\n\n\t\t\t\t\tSET nLatProj = asin(sin(nLat) * cos(nDistance) + cos(nLat) * sin(nDistance) * cos(nAngle));\n\n\t\t\t\t\treturn nLatProj * 180 / 3.141592654;\n\t\t\t END;");
sql_dropFunction('angle');
sql("CREATE FUNCTION `angle` (nLat1 DOUBLE, nLon1 DOUBLE, nLat2 DOUBLE, nLon2 DOUBLE) RETURNS DOUBLE DETERMINISTIC \n\t BEGIN\n\t\t\t\t\tDECLARE nDegCorrection DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nEntfernungsWinkel DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nArccosEntfernungsWinkel DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE n DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAngle DOUBLE DEFAULT 0;\n\n\t\t\t\t\tSET nLat1 = nLat1 * 3.141592654 / 180;\n\t\t\t\t\tSET nLon1 = nLon1 * 3.141592654 / 180;\n\t\t\t\t\tSET nLat2 = nLat2 * 3.141592654 / 180;\n\t\t\t\t\tSET nLon2 = nLon2 * 3.141592654 / 180;\n\n\t\t\t\t\tSET nDegCorrection = IF(nLon1 < nLon2, 360, 0);\n\t\t\t\t\tSET nEntfernungsWinkel = sin(nLat1) * sin(nLat2) + cos(nLat1) * cos(nLat2) * cos(nLon1 - nLon2);\n\n\t\t\t\t\tIF ((nEntfernungsWinkel < -1.0) OR (nEntfernungsWinkel >= 1.0)) THEN\n\t\t\t\t\t\tRETURN 0;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tSET nArccosEntfernungsWinkel = acos(nEntfernungsWinkel);\n\t\t\t\t\tSET n = sin(nLat2) / sin(nArccosEntfernungsWinkel) / cos(nLat1) - tan(nLat1) / tan(nArccosEntfernungsWinkel);\n\n\t\t\t\t\tIF (n < -1.0) OR (n > 1.0) THEN\n\t\t\t\t\t\tIF nLon1 = nLon2 THEN\n\t\t\t\t\t\t\tIF nLat1 > nLat2 THEN\n\t\t\t\t\t\t\t\tRETURN 90.0;\n\t\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\t\tRETURN 270.0;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND IF;\n\n\t\t\t\t\t\tRETURN 0.0;\n\t\t\t\t\tELSE\n\t\t\t\t\t\tSET nAngle = acos(n) * 180.0 / 3.141592654 - nDegCorrection;\n\t\t\t\t\t\tIF nAngle < 0.0 THEN\n\t\t\t\t\t\t\tRETURN 360 + nAngle;\n\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\tRETURN 360 - nAngle;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tRETURN 0;\n\t\t\t END;");
sql_dropFunction('ptonline');
sql("CREATE FUNCTION `ptonline` (nLat DOUBLE, nLon DOUBLE, nLatPt1 DOUBLE, nLonPt1 DOUBLE, nLatPt2 DOUBLE, nLonPt2 DOUBLE, nMaxDistance DOUBLE) RETURNS DOUBLE DETERMINISTIC \n\t BEGIN\n\t\t\t\t\tDECLARE nTmpLon DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nTmpLat DOUBLE DEFAULT 0;\n\n\t\t\t\t\tDECLARE nAnglePt1Pt2 DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAnglePt1 DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAngleLinePt1 DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAnglePt2 DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAngleLinePt2 DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nDistancePt1 DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nDistancePt2 DOUBLE DEFAULT 0;\n\n\t\t\t\t\tDECLARE nProjLat DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nProjLon DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nProjAngle DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAngleProj DOUBLE DEFAULT 0;\n\t\t\t\t\tDECLARE nAnglePt1Proj DOUBLE DEFAULT 0;\n\t\t\t\t\t\n\t\t\t\t\tIF nLonPt2 < nLonPt1 THEN\n\t\t\t\t\t\tSET nTmpLon = nLonPt1; \n\t\t\t\t\t\tSET nTmpLat = nLatPt1;\n\t\t\t\t\t\tSET nLonPt1 = nLonPt2; \n\t\t\t\t\t\tSET nLatPt1 = nLatPt2;\n\t\t\t\t\t\tSET nLonPt2 = nTmpLon; \n\t\t\t\t\t\tSET nLatPt2 = nTmpLat;\n\t\t\t\t\tEND IF;\n\t\t\t\t\t\n\t\t\t\t IF nLonPt1 = nLonPt2 THEN \n\t\t\t\t\t\tSET nLonPt2 = nLonPt2 + 0.000001;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tSET nAnglePt1Pt2 = angle(nLatPt1, nLonPt1, nLatPt2, nLonPt2);\n\t\t\t\t\tSET nAnglePt1 = angle(nLatPt1, nLonPt1, nLat, nLon);\n\t\t\t\t\tSET nAngleLinePt1 = nAnglePt1Pt2 - nAnglePt1;\n\n\t\t\t\t\tIF nAngleLinePt1 > 180 THEN\n\t\t\t\t\t\tSET nAngleLinePt1 = 360 - nAngleLinePt1;\n\t\t\t\t\tEND IF;\n\t\t\t\t\tIF nAngleLinePt1 < -180 THEN\n\t\t\t\t\t\tSET nAngleLinePt1 = nAngleLinePt1 + 360;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tSET nAnglePt2 = angle(nLat, nLon, nLatPt2, nLonPt2);\n\t\t\t\t\tSET nAngleLinePt2 = nAnglePt1Pt2 - nAnglePt2;\n\n\t\t\t\t\tIF nAngleLinePt2 > 180 THEN\n\t\t\t\t\t\tSET nAngleLinePt2 = 360 - nAngleLinePt2;\n\t\t\t\t\tEND IF;\n\t\t\t\t\tIF nAngleLinePt2 < -180 THEN\n\t\t\t\t\t\tSET nAngleLinePt2 = nAngleLinePt2 + 360;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tIF (nAngleLinePt1 > 90) OR (nAngleLinePt1 < -90) THEN\n\t\t\t\t\t\tSET nDistancePt1 = distance(nLat, nLon, nLatPt1, nLonPt1);\n\t\t\t\t\t\tIF nDistancePt1 < nMaxDistance THEN\n\t\t\t\t\t\t\tRETURN 1;\n\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\tRETURN 0;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND IF;\n\t\t\t\t \n\t\t\t\t\tIF (nAngleLinePt2 > 90) OR (nAngleLinePt2 < -90) THEN\n\t\t\t\t\t\tSET nDistancePt2 = distance(nLat, nLon, nLatPt2, nLonPt2);\n\t\t\t\t\t\tIF nDistancePt2 < nMaxDistance THEN\n\t\t\t\t\t\t\tRETURN 1;\n\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\tRETURN 0;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tIF nAngleLinePt1 > 0 THEN\n\t\t\t\t\t\tIF nAnglePt1Pt2 > 270 THEN\n\t\t\t\t\t\t\tSET nProjAngle = nAnglePt1Pt2 - 270;\n\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\tSET nProjAngle = nAnglePt1Pt2 + 90;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tELSE\n\t\t\t\t\t\tIF nAnglePt1Pt2 > 90 THEN\n\t\t\t\t\t\t\tSET nProjAngle = nAnglePt1Pt2 - 90;\n\t\t\t\t\t\tELSE\n\t\t\t\t\t\t\tSET nProjAngle = nAnglePt1Pt2 + 270;\n\t\t\t\t\t\tEND IF;\n\t\t\t\t\tEND IF;\n\n\t\t\t\t\tSET nProjLat = projLat(nLat, nLon, nMaxDistance, nProjAngle);\n\t\t\t\t\tSET nProjLon = projLon(nLat, nLon, nMaxDistance, nProjAngle);\n\n\t\t\t\t\tSET nAnglePt1Proj = angle(nLatPt1, nLonPt1, nProjLat, nProjLon);\n\t\t\t\t\tSET nAngleProj = nAnglePt1Pt2 - nAnglePt1Proj;\n\t\t\t\t\tIF nAngleProj > 180 THEN\n\t\t\t\t\t\tSET nAngleProj = 360 - nAngleProj;\n\t\t\t\t\tEND IF;\n\t\t\t\t\tIF nAngleProj < -180 THEN\n\t\t\t\t\t\tSET nAngleProj = nAngleProj + 360;\n\t\t\t\t\tEND IF;\n\t\t\t\t \n\t\t\t\t\tIF (nAngleLinePt1 >= 0) AND (nAngleProj < 0) THEN\n\t\t\t\t\t\tRETURN 1;\n\t\t\t\t\tELSEIF (nAngleLinePt1 < 0) AND (nAngleProj >= 0) THEN\n\t\t\t\t\t\tRETURN 1;\n\t\t\t\t\tELSE\n\t\t\t\t\t\tRETURN 0;\n\t\t\t\t\tEND IF;\n\t\t\t END;");
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");
$rootpath = '../../../';
require_once $rootpath . 'lib/clicompatbase.inc.php';
if (!file_exists($rootpath . 'util/mysql_root/sql_root.inc.php')) {
die("\n" . 'install util/mysql_root/sql_root.inc.php' . "\n\n");
}
require_once $rootpath . 'util/mysql_root/sql_root.inc.php';
/* begin db connect */
db_root_connect();
if ($dblink === false) {
echo 'Unable to connect to database';
exit;
}
/* end db connect */
/* get prefered language from string
*/
sql_dropFunction('PREFERED_LANG');
sql("CREATE FUNCTION `PREFERED_LANG` (sExistingTokens VARCHAR(60), sPreferedTokens VARCHAR(60)) RETURNS CHAR(2) DETERMINISTIC SQL SECURITY INVOKER\r\n\t BEGIN\r\n\t\t\t DECLARE nPreferedIndex INT DEFAULT 1;\r\n\t\t\t DECLARE sPrefered CHAR(2) DEFAULT '';\r\n\t\t\t DECLARE sLastPrefered CHAR(2) DEFAULT '';\r\n\t\t\t DECLARE nPos INT DEFAULT 0;\r\n\r\n\t IF ISNULL(sExistingTokens) THEN\r\n\t\t\t\t RETURN NULL;\r\n\t END IF;\r\n\r\n\t\t\t SET sExistingTokens = CONCAT(',', sExistingTokens, ',');\r\n\r\n\t SET sPrefered = SUBSTRING_INDEX(SUBSTRING_INDEX(sPreferedTokens, ',', nPreferedIndex), ',', -1);\r\n\t pl: LOOP\r\n\t\t\t\t IF sPrefered = sLastPrefered THEN\r\n\t\t\t\t LEAVE pl;\r\n\t\t\t\t END IF;\r\n\r\n SET nPos = INSTR(sExistingTokens, CONCAT(',', sPrefered, ','));\r\n IF nPos!=0 THEN\r\n\t\t\t\t\t RETURN sPrefered;\r\n END IF;\r\n\r\n\t SET sLastPrefered = sPrefered;\r\n SET nPreferedIndex = nPreferedIndex + 1;\r\n SET sPrefered = SUBSTRING_INDEX(SUBSTRING_INDEX(sPreferedTokens, ',', nPreferedIndex), ',', -1);\r\n\t END LOOP pl;\r\n\r\n SET sPrefered = SUBSTRING_INDEX(SUBSTRING_INDEX(sExistingTokens, ',', 2), ',', -1);\r\n IF sPrefered = '' THEN\r\n\t RETURN NULL;\r\n ELSE\r\n\t RETURN sPrefered;\r\n END IF;\r\n\t\t\t END;");
/* Stored procedures containing database logic
*/
sql_dropProcedure('sp_touch_cache');
sql("CREATE PROCEDURE sp_touch_cache (IN nCacheId INT(10) UNSIGNED, IN bUpdateCacheRecord BOOL)\r\n\t COMMENT 'update all last_modified dates of related records'\r\n\t BEGIN\r\n\t\t\t\t IF bUpdateCacheRecord = TRUE THEN\r\n\t\t\t\t\t UPDATE `caches` SET `last_modified`=NOW() WHERE `cache_id`=nCacheId;\r\n\t\t\t\t END IF;\r\n\r\n\t\t\t\t UPDATE `cache_desc` SET `last_modified`=NOW() WHERE `cache_id`=nCacheId;\r\n\t\t\t\t UPDATE `cache_logs` SET `last_modified`=NOW() WHERE `cache_id`=nCacheId;\r\n\t\t\t\t UPDATE `pictures` SET `last_modified`=NOW() WHERE `object_type`=2 AND `object_id`=nCacheId;\r\n\t\t\t\t UPDATE `pictures`, `cache_logs` SET `pictures`.`last_modified`=NOW() WHERE `pictures`.`object_type`=1 AND `pictures`.`object_id`=`cache_logs`.`id` AND `cache_logs`.`cache_id`=nCacheId;\r\n\t\t\t\t UPDATE `mp3` SET `last_modified`=NOW() WHERE `object_id`=nCacheId;\r\n\t END;");
sql_dropProcedure('sp_update_caches_descLanguages');
sql("CREATE PROCEDURE sp_update_caches_descLanguages (IN nCacheId INT(10) UNSIGNED)\r\n\t -- COMMENT 'set caches.desc_languages of given cacheid and fill cache_desc_prefered'\r\n\t BEGIN\r\n\t DECLARE dl VARCHAR(60);\r\n\r\n\t SELECT GROUP_CONCAT(DISTINCT `language` ORDER BY `language` SEPARATOR ',') INTO dl FROM `cache_desc` WHERE `cache_id`=nCacheId GROUP BY `cache_id` ;\r\n\t UPDATE `caches` SET `desc_languages`=dl, default_desclang=PREFERED_LANG(dl, '&1') WHERE `cache_id`=nCacheId LIMIT 1;\r\n\t END;", strtoupper($lang . ',EN'));
sql_dropProcedure('sp_updateall_caches_descLanguages');
sql("CREATE PROCEDURE sp_updateall_caches_descLanguages (OUT nModified INT)\r\n\t -- COMMENT 'set caches.desc_languages of all caches, fill cache_desc_prefered and return number of modified rows'\r\n\t BEGIN\r\n\t\t\t SET nModified = 0;\r\n\t UPDATE `caches`, (SELECT `cache_id`, GROUP_CONCAT(DISTINCT `language` ORDER BY `language` SEPARATOR ',') AS `dl` FROM `cache_desc` GROUP BY `cache_id`) AS `tbl` SET `caches`.`desc_languages`=`tbl`.`dl`, `caches`.`default_desclang`=PREFERED_LANG(`tbl`.`dl`, '&1') WHERE `caches`.`cache_id`=`tbl`.`cache_id`;\r\n\t SET nModified = nModified + ROW_COUNT() ;\r\n\t END;", strtoupper($lang . ',EN'));
sql_dropProcedure('sp_update_logstat');
sql("CREATE PROCEDURE sp_update_logstat (IN nCacheId INT(10) UNSIGNED, IN nUserId INT(10) UNSIGNED, IN nLogType INT, IN bLogRemoved BOOLEAN)\r\n\t -- COMMENT 'update found, last_found, notfound and note of stat_cache_logs, stat_caches and stat_user'\r\n\t BEGIN\r\n\t DECLARE nFound INT DEFAULT 0;\r\n\t DECLARE nNotFound INT DEFAULT 0;\r\n\t DECLARE nNote INT DEFAULT 0;\r\n\t DECLARE nWillAttend INT DEFAULT 0;\r\n\t DECLARE nDate DATE DEFAULT NULL;\r\n\r\n\t IF nLogType = 1 THEN SET nFound=1; END IF;\r\n\t IF nLogType = 2 THEN SET nNotFound=1; END IF;\r\n\t IF nLogType = 3 THEN SET nNote=1; END IF;\r\n\t IF nLogType = 7 THEN SET nFound=1; END IF;\r\n\t IF nLogType = 8 THEN SET nWillAttend=1; END IF;\r\n\r\n\t IF bLogRemoved = TRUE THEN\r\n\t\t\t\t SET nFound = -nFound;\r\n\t\t\t\t SET nNotFound = -nNotFound;\r\n\t\t\t\t SET nNote = -nNote;\r\n\t\t\t\t SET nWillAttend = -nWillAttend;\r\n\t END IF;\r\n\r\n\t UPDATE `stat_cache_logs` SET `found`=IF(`found`+nFound>0, `found`+nFound, 0), `notfound`=IF(`notfound`+nNotFound>0, `notfound`+nNotFound, 0), `note`=IF(`note`+nNote>0, `note`+nNote, 0), `will_attend`=IF(`will_attend`+nWillAttend>0, `will_attend`+nWillAttend, 0) WHERE `cache_id`=nCacheId AND `user_id`=nUserId;\r\n\t IF ROW_COUNT() = 0 THEN\r\n\t\t\t\t INSERT IGNORE INTO `stat_cache_logs` (`cache_id`, `user_id`, `found`, `notfound`, `note`, `will_attend`) VALUES (nCacheId, nUserId, IF(nFound>0, nFound, 0), IF(nNotFound>0, nNotFound, 0), IF(nNote>0, nNote, 0), IF(nWillAttend>0, nWillAttend, 0));\r\n\t END IF;\r\n\r\n\t UPDATE `stat_caches` SET `found`=IF(`found`+nFound>0, `found`+nFound, 0), `notfound`=IF(`notfound`+nNotFound>0, `notfound`+nNotFound, 0), `note`=IF(`note`+nNote>0, `note`+nNote, 0), `will_attend`=IF(`will_attend`+nWillAttend>0, `will_attend`+nWillAttend, 0) WHERE `cache_id`=nCacheId;\r\n\t IF ROW_COUNT() = 0 THEN\r\n\t\t\t\t INSERT IGNORE INTO `stat_caches` (`cache_id`, `found`, `notfound`, `note`, `will_attend`) VALUES (nCacheId, IF(nFound>0, nFound, 0), IF(nNotFound>0, nNotFound, 0), IF(nNote>0, nNote, 0), IF(nWillAttend>0, nWillAttend, 0));\r\n\t END IF;\r\n\r\n\t IF nFound!=0 THEN\r\n SELECT `date` INTO nDate FROM `cache_logs` WHERE `cache_id`=nCacheId AND `type` IN (1, 7) ORDER BY `date` DESC LIMIT 1;\r\n UPDATE `stat_caches` SET `last_found`=nDate WHERE `cache_id`=nCacheId;\r\n\t END IF;\r\n\r\n\t UPDATE `stat_user` SET `found`=IF(`found`+nFound>0, `found`+nFound, 0), `notfound`=IF(`notfound`+nNotFound>0, `notfound`+nNotFound, 0), `note`=IF(`note`+nNote>0, `note`+nNote, 0), `will_attend`=IF(`will_attend`+nWillAttend>0, `will_attend`+nWillAttend, 0) WHERE `user_id`=nUserId;\r\n\t IF ROW_COUNT() = 0 THEN\r\n\t\t\t\t INSERT IGNORE INTO `stat_user` (`user_id`, `found`, `notfound`, `note`, `will_attend`) VALUES (nUserId, IF(nFound>0, nFound, 0), IF(nNotFound>0, nNotFound, 0), IF(nNote>0, nNote, 0), IF(nWillAttend>0, nWillAttend, 0));\r\n\t END IF;\r\n\r\n\t CALL sp_refresh_statpic(nUserId);\r\n\t END;");
sql_dropProcedure('sp_updateall_logstat');
sql("CREATE PROCEDURE sp_updateall_logstat (OUT nModified INT)\r\n\t -- COMMENT 'recalc found, last_found, notfound and note of stat_cache_logs, stat_caches and stat_user for all entries'\r\n\t BEGIN\r\n\t SET nModified=0;\r\n\r\n\t INSERT IGNORE INTO `stat_user` (`user_id`) SELECT `user_id` FROM `cache_logs` GROUP BY `user_id`;\r\n\t INSERT IGNORE INTO `stat_caches` (`cache_id`) SELECT `cache_id` FROM `cache_logs` GROUP BY `cache_id`;\r\n\t INSERT IGNORE INTO `stat_cache_logs` (`cache_id`, `user_id`) SELECT `cache_id`, `user_id` FROM `cache_logs`;\r\n\r\n\t /* stat_user.found */\r\n\t UPDATE `stat_user`, (SELECT `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (1, 7) GROUP BY `user_id`) AS `tblFound` SET `stat_user`.`found`=`tblFound`.`count` WHERE `stat_user`.`user_id`=`tblFound`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.notfound */\r\n\t UPDATE `stat_user`, (SELECT `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (2) GROUP BY `user_id`) AS `tblNotFound` SET `stat_user`.`notfound`=`tblNotFound`.`count` WHERE `stat_user`.`user_id`=`tblNotFound`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.note */\r\n\t UPDATE `stat_user`, (SELECT `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (3) GROUP BY `user_id`) AS `tblNote` SET `stat_user`.`note`=`tblNote`.`count` WHERE `stat_user`.`user_id`=`tblNote`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.will_attend */\r\n\t UPDATE `stat_user`, (SELECT `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (8) GROUP BY `user_id`) AS `tblWillAttend` SET `stat_user`.`will_attend`=`tblWillAttend`.`count` WHERE `stat_user`.`user_id`=`tblWillAttend`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_caches.found and stat_caches.last_found */\r\n\t UPDATE `stat_caches`, (SELECT `cache_id`, COUNT(*) AS `count`, MAX(`date`) AS `last_found` FROM `cache_logs` WHERE `type` IN (1, 7) GROUP BY `cache_id`) AS `tblFound` SET `stat_caches`.`found`=`tblFound`.`count`, `stat_caches`.`last_found`=`tblFound`.`last_found` WHERE `stat_caches`.`cache_id`=`tblFound`.`cache_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_caches.notfound */\r\n\t UPDATE `stat_caches`, (SELECT `cache_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (2) GROUP BY `cache_id`) AS `tblNotFound` SET `stat_caches`.`notfound`=`tblNotFound`.`count` WHERE `stat_caches`.`cache_id`=`tblNotFound`.`cache_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_caches.note */\r\n\t UPDATE `stat_caches`, (SELECT `cache_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (3) GROUP BY `cache_id`) AS `tblNote` SET `stat_caches`.`note`=`tblNote`.`count` WHERE `stat_caches`.`cache_id`=`tblNote`.`cache_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_caches.will_attend */\r\n\t UPDATE `stat_caches`, (SELECT `cache_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (8) GROUP BY `cache_id`) AS `tblWillAttend` SET `stat_caches`.`will_attend`=`tblWillAttend`.`count` WHERE `stat_caches`.`cache_id`=`tblWillAttend`.`cache_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.found */\r\n\t UPDATE `stat_cache_logs`, (SELECT `cache_id`, `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (1, 7) GROUP BY `user_id`, `cache_id`) AS `tblFound` SET `stat_cache_logs`.`found`=`tblFound`.`count` WHERE `stat_cache_logs`.`cache_id`=`tblFound`.`cache_id` AND `stat_cache_logs`.`user_id`=`tblFound`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.notfound */\r\n\t UPDATE `stat_cache_logs`, (SELECT `cache_id`, `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (2) GROUP BY `user_id`, `cache_id`) AS `tblNotFound` SET `stat_cache_logs`.`notfound`=`tblNotFound`.`count` WHERE `stat_cache_logs`.`cache_id`=`tblNotFound`.`cache_id` AND `stat_cache_logs`.`user_id`=`tblNotFound`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.note */\r\n\t UPDATE `stat_cache_logs`, (SELECT `cache_id`, `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (3) GROUP BY `user_id`, `cache_id`) AS `tblNote` SET `stat_cache_logs`.`note`=`tblNote`.`count` WHERE `stat_cache_logs`.`cache_id`=`tblNote`.`cache_id` AND `stat_cache_logs`.`user_id`=`tblNote`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t /* stat_cache_logs.will_attend */\r\n\t UPDATE `stat_cache_logs`, (SELECT `cache_id`, `user_id`, COUNT(*) AS `count` FROM `cache_logs` WHERE `type` IN (8) GROUP BY `user_id`, `cache_id`) AS `tblWillAttend` SET `stat_cache_logs`.`will_attend`=`tblWillAttend`.`count` WHERE `stat_cache_logs`.`cache_id`=`tblWillAttend`.`cache_id` AND `stat_cache_logs`.`user_id`=`tblWillAttend`.`user_id`;\r\n\t SET nModified=nModified+ROW_COUNT();\r\n\r\n\t CALL sp_refreshall_statpic();\r\n\t END;");
sql_dropProcedure('sp_update_hiddenstat');
sql("CREATE PROCEDURE sp_update_hiddenstat (IN nUserId INT, IN bRemoved BOOLEAN)\r\n\t COMMENT 'increment/decrement stat_user.hidden'\r\n\t BEGIN\r\n\t\t\t DECLARE nHidden INT DEFAULT 1;\r\n\t\t\t IF bRemoved = TRUE THEN SET nHidden = -1; END IF;\r\n\t\t\t UPDATE `stat_user` SET `stat_user`.`hidden`=IF(`stat_user`.`hidden`+nHidden>0, `stat_user`.`hidden`+nHidden, 0) WHERE `stat_user`.`user_id`=nUserId;\r\n\t\t\t IF ROW_COUNT() = 0 THEN\r\n\t\t\t INSERT IGNORE INTO `stat_user` (`user_id`, `hidden`) VALUES (nUserId, IF(nHidden>0, nHidden, 0));\r\n\t\t\t END IF;\r\n\r\n\t CALL sp_refresh_statpic(nUserId);\r\n\t END;");
