-- MariaDB dump 10.19 Distrib 10.11.7-MariaDB, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: ocpldb -- ------------------------------------------------------ -- Server version 10.11.7-MariaDB-1:10.11.7+maria~ubu2204 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `CACHE_ACCESS_LOGS` -- DROP TABLE IF EXISTS `CACHE_ACCESS_LOGS`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `CACHE_ACCESS_LOGS` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `event_date` datetime NOT NULL, `cache_id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `source` varchar(2) NOT NULL COMMENT 'B - browser - main opencaching site, M - mobile, O - okapi', `event` varchar(32) NOT NULL COMMENT 'viewcache, viewlogs, ... ', `ip_addr` varchar(32) NOT NULL COMMENT 'request IP', `user_agent` text DEFAULT NULL COMMENT 'User-Agent HTTP header', `forwarded_for` varchar(128) DEFAULT NULL COMMENT 'X-Forwarded-For HTTP header', `okapi_consumer_key` varchar(20) DEFAULT NULL COMMENT 'OKAPI consumer key', `info_text` varchar(2048) DEFAULT NULL COMMENT 'Free info text', PRIMARY KEY (`id`), KEY `access_logs_cache_id` (`cache_id`), KEY `access_logs_user_id` (`user_id`), KEY `event_date` (`event_date`), FULLTEXT KEY `ip_addr` (`ip_addr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `GeoKretyAPI` -- DROP TABLE IF EXISTS `GeoKretyAPI`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `GeoKretyAPI` ( `userID` int(11) NOT NULL COMMENT 'opencaching user id', `secid` varchar(130) NOT NULL COMMENT 'user GeoKrety secid ', UNIQUE KEY `userID` (`userID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Table is used to integration OC account with GeoKrety'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `GeoKretyAPIerrors` -- DROP TABLE IF EXISTS `GeoKretyAPIerrors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `GeoKretyAPIerrors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dateTime` datetime NOT NULL, `operationType` int(11) NOT NULL, `dataSent` text NOT NULL, `response` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `PowerTrail` -- DROP TABLE IF EXISTS `PowerTrail`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `PowerTrail` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text NOT NULL, `centerLatitude` float NOT NULL DEFAULT 0, `centerLongitude` float NOT NULL DEFAULT 0, `type` int(11) NOT NULL, `status` int(11) NOT NULL, `dateCreated` datetime NOT NULL, `cacheCount` int(11) NOT NULL, `description` text NOT NULL, `image` text NOT NULL, `perccentRequired` int(11) NOT NULL, `conquestedCount` int(11) NOT NULL DEFAULT 0, `points` float NOT NULL DEFAULT 0, `uuid` varchar(36) DEFAULT NULL, PRIMARY KEY (`id`), KEY `uuid` (`uuid`), KEY `status` (`status`), KEY `type` (`type`), KEY `dateCreated` (`dateCreated`), KEY `cacheCount` (`cacheCount`), KEY `conquestedCount` (`conquestedCount`), KEY `points` (`points`), FULLTEXT KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `PowerTrail_actionsLog` -- DROP TABLE IF EXISTS `PowerTrail_actionsLog`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `PowerTrail_actionsLog` ( `id` int(11) NOT NULL AUTO_INCREMENT, `PowerTrailId` int(11) NOT NULL, `userId` int(11) NOT NULL, `actionDateTime` datetime NOT NULL, `actionType` int(11) NOT NULL, `description` text NOT NULL, `cacheId` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `PowerTrailId` (`PowerTrailId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `PowerTrail_cacheCandidate` -- DROP TABLE IF EXISTS `PowerTrail_cacheCandidate`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `PowerTrail_cacheCandidate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `PowerTrailId` int(11) NOT NULL, `cacheId` int(11) NOT NULL, `link` text NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`), KEY `cacheId` (`cacheId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `PowerTrail_comments` -- DROP TABLE IF EXISTS `PowerTrail_comments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `PowerTrail_comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userId` int(11) NOT NULL, `PowerTrailId` int(11) NOT NULL, `commentType` int(11) NOT NULL COMMENT '1 - comment; 2 - conquested;', `commentText` text NOT NULL, `logDateTime` datetime NOT NULL, `dbInsertDateTime` datetime NOT NULL, `deleted` tinyint(1) NOT NULL, `uuid` varchar(36) DEFAULT NULL, PRIMARY KEY (`id`), KEY `PowerTrailId` (`PowerTrailId`), KEY `uuid` (`uuid`), KEY `commentType` (`commentType`), KEY `deleted` (`deleted`), KEY `userId` (`userId`), KEY `logDateTime` (`logDateTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `PowerTrail_owners` -- DROP TABLE IF EXISTS `PowerTrail_owners`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `PowerTrail_owners` ( `PowerTrailId` int(11) NOT NULL, `userId` int(11) NOT NULL, `privileages` int(11) NOT NULL, UNIQUE KEY `PowerTrailId` (`PowerTrailId`,`userId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `admin_user_notes` -- DROP TABLE IF EXISTS `admin_user_notes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `admin_user_notes` ( `note_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `admin_id` int(11) DEFAULT NULL, `cache_id` int(11) DEFAULT NULL, `automatic` tinyint(1) DEFAULT NULL COMMENT 'bool note type', `datetime` timestamp NOT NULL DEFAULT current_timestamp(), `content` varchar(4096) DEFAULT NULL, PRIMARY KEY (`note_id`), KEY `user_id` (`user_id`), KEY `admin_id` (`admin_id`), CONSTRAINT `admin_user_notes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`), CONSTRAINT `admin_user_notes_ibfk_2` FOREIGN KEY (`admin_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `approval_status` -- DROP TABLE IF EXISTS `approval_status`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `approval_status` ( `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `status` int(11) NOT NULL, `date_approval` datetime DEFAULT NULL, PRIMARY KEY (`cache_id`), KEY `date_approval` (`date_approval`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_polish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `badge_area` -- DROP TABLE IF EXISTS `badge_area`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `badge_area` ( `badge_id` int(11) NOT NULL, `shape` geometry NOT NULL, PRIMARY KEY (`badge_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `badge_cache` -- DROP TABLE IF EXISTS `badge_cache`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `badge_cache` ( `badge_id` int(11) NOT NULL, `cache_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `badge_categories` -- DROP TABLE IF EXISTS `badge_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `badge_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sequence` int(11) NOT NULL, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `badge_levels` -- DROP TABLE IF EXISTS `badge_levels`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `badge_levels` ( `badge_id` int(11) NOT NULL, `level` int(11) NOT NULL, `name` varchar(30) DEFAULT NULL, `threshold` int(11) NOT NULL, `picture` varchar(255) DEFAULT NULL COMMENT 'path to the picuture of level', PRIMARY KEY (`badge_id`,`level`) USING BTREE, KEY `level` (`level`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `badge_user` -- DROP TABLE IF EXISTS `badge_user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `badge_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `badge_id` int(11) NOT NULL, `level_id` int(11) NOT NULL, `level_date` datetime NOT NULL, `prev_val` int(11) NOT NULL DEFAULT 0, `curr_val` int(11) NOT NULL, `next_val` int(11) NOT NULL, `description` text NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_id` (`user_id`,`badge_id`), KEY `badge_id` (`badge_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `badges` -- DROP TABLE IF EXISTS `badges`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `badges` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, `category_id` int(11) NOT NULL, `sequence` int(11) NOT NULL, `picture` varchar(255) NOT NULL, `trigger_type` int(11) NOT NULL COMMENT ' NONE = 0; CRON = 1; LOG_CACHE = 2; LOG_CACHE_AUTHOR = 3; TITLED_CACHE = 4; TITLED_CACHE_AUTHOR = 5; LOG_GEOPATH = 6; LOG_GEOPATH_AUTHOR = 7; RECOMMENDATION = 8;', `belonging_query` text NOT NULL, `gained_query` text NOT NULL, `short_description` char(100) NOT NULL, `description` text NOT NULL, `cfg_period_threshold` char(1) NOT NULL COMMENT 'Table in badge.php: Level of the badge. Column: (P)eriod or (T)hreshold', `cfg_show_positions` char(2) NOT NULL COMMENT ' - none, L - list, M - map', `graphic_author` text NOT NULL, `description_author` text NOT NULL, `attendant` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_arch` -- DROP TABLE IF EXISTS `cache_arch`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_arch` ( `cache_id` int(11) NOT NULL, `step` int(11) NOT NULL, PRIMARY KEY (`cache_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_attrib` -- DROP TABLE IF EXISTS `cache_attrib`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_attrib` ( `id` int(11) NOT NULL DEFAULT 0, `language` char(2) NOT NULL, `text_short` varchar(20) NOT NULL, `text_long` varchar(60) NOT NULL, `icon_large` varchar(60) NOT NULL, `icon_no` varchar(60) NOT NULL, `icon_undef` varchar(60) NOT NULL, `category` tinyint(2) NOT NULL DEFAULT 0, `default` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`language`,`id`), KEY `category` (`category`,`id`), KEY `default` (`default`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_coordinates` -- DROP TABLE IF EXISTS `cache_coordinates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_coordinates` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL, `date_modified` datetime NOT NULL, `longitude` double NOT NULL, `latitude` double NOT NULL, PRIMARY KEY (`id`), KEY `cache_id` (`cache_id`,`date_modified`), KEY `longitude` (`longitude`), KEY `latitude` (`latitude`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_countries` -- DROP TABLE IF EXISTS `cache_countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_countries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL, `date_modified` datetime NOT NULL, `country` char(2) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `cache_id` (`cache_id`,`date_modified`), KEY `country` (`country`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_desc` -- DROP TABLE IF EXISTS `cache_desc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_desc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) DEFAULT NULL, `language` char(2) DEFAULT NULL, `desc` mediumtext DEFAULT NULL COMMENT 'HTML formatted geocache description', `desc_html` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Format in which `desc` column is encoded: 0 - DO NOT USE (unknown format based on HTML); 1 - unsafe HTML (needs to be purified before it is included on a HTML page); 2 - safe HTML (may be included "as is" on HTML pages, without any further processing).', `hint` mediumtext DEFAULT NULL COMMENT 'HTML-escaped hint, will contain
s', `short_desc` mediumtext DEFAULT NULL COMMENT 'Plain text short description', `date_created` timestamp NOT NULL DEFAULT current_timestamp(), `last_modified` datetime DEFAULT NULL, `uuid` varchar(36) DEFAULT NULL, `node` tinyint(4) NOT NULL DEFAULT 0, `rr_comment` text DEFAULT NULL COMMENT 'OcTeam notes displayed in geocache description', `reactivation_rule` text DEFAULT NULL COMMENT 'Geocache reactivation rules defined by geocache user', PRIMARY KEY (`id`), UNIQUE KEY `cache_id` (`cache_id`,`language`), KEY `last_modified` (`last_modified`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cacheDescAfterInsert AFTER INSERT ON cache_desc FOR EACH ROW BEGIN UPDATE caches SET caches.desc_languages = ( SELECT GROUP_CONCAT(language) FROM cache_desc AS cd WHERE cd.cache_id = NEW.cache_id ) WHERE caches.cache_id = NEW.cache_id; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cacheDescAfterUpdate AFTER UPDATE ON cache_desc FOR EACH ROW BEGIN IF OLD.cache_id != NEW.cache_id OR OLD.language != NEW.language THEN UPDATE caches SET caches.desc_languages = ( SELECT GROUP_CONCAT(language) FROM cache_desc AS cd WHERE cd.cache_id = NEW.cache_id ) WHERE caches.cache_id = NEW.cache_id; IF OLD.cache_id != NEW.cache_id THEN UPDATE caches SET caches.desc_languages = ( SELECT GROUP_CONCAT(language) FROM cache_desc AS cd WHERE cd.cache_id = OLD.cache_id ) WHERE caches.cache_id = OLD.cache_id; END IF; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cacheDescAfterDelete AFTER DELETE ON cache_desc FOR EACH ROW BEGIN IF IFNULL(@deleting_cache, 0) = 0 THEN UPDATE caches SET caches.desc_languages = ( SELECT GROUP_CONCAT(language) FROM cache_desc AS cd WHERE cd.cache_id = OLD.cache_id ) WHERE caches.cache_id = OLD.cache_id; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `cache_ignore` -- DROP TABLE IF EXISTS `cache_ignore`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_ignore` ( `cache_id` int(10) NOT NULL, `user_id` int(10) NOT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `cache_id` (`cache_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_location` -- DROP TABLE IF EXISTS `cache_location`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_location` ( `cache_id` int(10) unsigned NOT NULL, `last_modified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `adm1` varchar(120) DEFAULT NULL, `adm2` varchar(120) DEFAULT NULL, `adm3` varchar(120) DEFAULT NULL, `adm4` varchar(120) DEFAULT NULL, `code1` varchar(2) DEFAULT NULL, `code2` varchar(3) DEFAULT NULL, `code3` varchar(4) DEFAULT NULL, `code4` varchar(5) DEFAULT NULL, PRIMARY KEY (`cache_id`), KEY `code1` (`code1`,`code2`,`code3`,`code4`), KEY `adm1` (`adm1`,`adm2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='via cronjob'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_logs` -- DROP TABLE IF EXISTS `cache_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_logs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) DEFAULT NULL, `user_id` int(11) DEFAULT NULL, `type` int(11) DEFAULT NULL, `date` datetime DEFAULT NULL, `text` mediumtext DEFAULT NULL, `text_html` tinyint(1) NOT NULL DEFAULT 0, `text_htmledit` tinyint(1) NOT NULL DEFAULT 0, `last_modified` datetime NOT NULL, `okapi_syncbase` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `uuid` varchar(36) DEFAULT NULL, `picturescount` int(11) NOT NULL DEFAULT 0, `mp3count` int(11) NOT NULL DEFAULT 0, `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `owner_notified` int(1) NOT NULL DEFAULT 0, `node` tinyint(4) NOT NULL DEFAULT 0, `deleted` tinyint(1) NOT NULL DEFAULT 0, `del_by_user_id` int(11) DEFAULT NULL, `last_deleted` datetime DEFAULT NULL, `edit_by_user_id` int(11) DEFAULT NULL, `edit_count` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `date` (`date`), KEY `owner_notified` (`owner_notified`), KEY `last_modified` (`last_modified`), KEY `date_created` (`date_created`), KEY `uuid` (`uuid`), KEY `okapi_syncbase` (`okapi_syncbase`), KEY `by_user_and_cache` (`user_id`,`cache_id`), KEY `by_user_founds` (`user_id`,`type`,`deleted`), KEY `by_cache` (`cache_id`,`deleted`,`date`,`date_created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cache_logs_insert AFTER INSERT ON `cache_logs` FOR EACH ROW BEGIN IF NEW.`deleted` = 0 THEN -- new, not-deleted log is now added CALL inc_logs_stats(NEW.`type`, NEW.`user_id`, NEW.`cache_id`); IF (NEW.type = 1) THEN INSERT INTO user_finds (date, user_id, number) VALUES (NEW.date, NEW.user_id, 1) ON DUPLICATE KEY UPDATE number = number + 1; END IF; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cache_logs_update AFTER UPDATE ON `cache_logs` FOR EACH ROW BEGIN IF OLD.`deleted` = 0 AND NEW.`deleted` = 0 THEN -- update of active log IF OLD.`type` <> NEW.`type` THEN CALL dec_logs_stats(OLD.`type`, OLD.`user_id`, OLD.`cache_id`); CALL inc_logs_stats(NEW.`type`, NEW.`user_id`, NEW.`cache_id`); END IF; ELSEIF OLD.`deleted` = 1 AND NEW.`deleted` = 0 THEN -- log UNDELETE CALL inc_logs_stats(NEW.`type`, NEW.`user_id`, NEW.`cache_id`); ELSEIF OLD.`deleted` = 0 AND NEW.`deleted` = 1 THEN -- log DELETE CALL dec_logs_stats(OLD.`type`, OLD.`user_id`, OLD.`cache_id`); ELSE -- do NOTHING - update of removed log without status change CALL nop(); END IF; IF OLD.deleted = 0 AND OLD.type = 1 THEN IF NEW.deleted = 1 OR NEW.type <> 1 OR DATE(NEW.date) <> DATE(OLD.date) THEN UPDATE user_finds SET number = number - 1 WHERE date = DATE(OLD.date) AND user_id = OLD.user_id AND number > 0; END IF; END IF; IF NEW.deleted = 0 AND NEW.type = 1 THEN IF OLD.deleted = 1 OR OLD.type <> 1 OR DATE(NEW.date) <> DATE(OLD.date) THEN INSERT INTO user_finds (date, user_id, number) VALUES (NEW.date, NEW.user_id, 1) ON DUPLICATE KEY UPDATE number = number + 1; END IF; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cache_logs_delete AFTER DELETE ON `cache_logs` FOR EACH ROW begin IF OLD.`deleted` = 0 THEN -- not-deleted log is now removed CALL dec_logs_stats(OLD.`type`, OLD.`user_id`, OLD.`cache_id`); UPDATE user_finds SET number = number - 1 WHERE date = DATE(OLD.date) AND user_id = OLD.user_id AND number > 0; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `cache_mod_cords` -- DROP TABLE IF EXISTS `cache_mod_cords`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_mod_cords` ( `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `date` timestamp NOT NULL DEFAULT current_timestamp(), `longitude` double NOT NULL, `latitude` double NOT NULL, PRIMARY KEY (`cache_id`,`user_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Custom coordinates of geocache set by user'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_moved` -- DROP TABLE IF EXISTS `cache_moved`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_moved` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `log_id` int(11) DEFAULT NULL, `date` datetime NOT NULL, `longitude` double NOT NULL, `latitude` double NOT NULL, `km` float NOT NULL, PRIMARY KEY (`id`), KEY `cache_id` (`cache_id`,`date`), KEY `longitude` (`longitude`), KEY `latitude` (`latitude`), KEY `log_id` (`log_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_notes` -- DROP TABLE IF EXISTS `cache_notes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_notes` ( `note_id` int(11) NOT NULL DEFAULT 0, `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `date` timestamp NOT NULL DEFAULT current_timestamp(), `desc_html` tinyint(1) NOT NULL DEFAULT 1, `desc` text NOT NULL COMMENT 'User note for geocache', PRIMARY KEY (`cache_id`,`user_id`), KEY `note_id` (`note_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='User notes for geocaches'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_npa_areas` -- DROP TABLE IF EXISTS `cache_npa_areas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_npa_areas` ( `cache_id` int(10) unsigned NOT NULL, `npa_id` int(10) unsigned NOT NULL, `parki_id` int(10) unsigned NOT NULL, `calculated` tinyint(1) NOT NULL, PRIMARY KEY (`cache_id`,`npa_id`), KEY `parki_id` (`parki_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_rating` -- DROP TABLE IF EXISTS `cache_rating`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_rating` ( `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`cache_id`,`user_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cacheRatingAfterInsert` AFTER INSERT ON `cache_rating` FOR EACH ROW BEGIN UPDATE `caches` SET `topratings` = ( SELECT COUNT(*) FROM `cache_rating` WHERE `cache_rating`.`cache_id` = NEW.`cache_id` ) WHERE `cache_id` = NEW.`cache_id`; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cacheRatingAfterUpdate` AFTER UPDATE ON `cache_rating` FOR EACH ROW BEGIN IF OLD.`cache_id` != NEW.`cache_id` THEN UPDATE `caches` SET `topratings` = ( SELECT COUNT(*) FROM `cache_rating` WHERE `cache_rating`.`cache_id` = OLD.`cache_id` ) WHERE `cache_id` = OLD.`cache_id`; UPDATE `caches` SET `topratings` = ( SELECT COUNT(*) FROM `cache_rating` WHERE `cache_rating`.`cache_id` = NEW.`cache_id` ) WHERE `cache_id` = NEW.`cache_id`; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cacheRatingAfterDelete` AFTER DELETE ON `cache_rating` FOR EACH ROW BEGIN IF IFNULL(@deleting_cache, 0) = 0 THEN UPDATE `caches` SET `topratings` = ( SELECT COUNT(*) FROM `cache_rating` WHERE `cache_rating`.`cache_id` = OLD.`cache_id` ) WHERE `cache_id` = OLD.`cache_id`; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `cache_size` -- DROP TABLE IF EXISTS `cache_size`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_size` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pl` varchar(60) NOT NULL, `en` varchar(60) NOT NULL, `nl` varchar(60) NOT NULL, `ro` varchar(60) NOT NULL, `de` varchar(60) NOT NULL, `fr` varchar(60) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_status` -- DROP TABLE IF EXISTS `cache_status`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_status` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pl` varchar(60) NOT NULL, `en` varchar(60) NOT NULL, `nl` varchar(60) NOT NULL, `de` varchar(60) NOT NULL, `fr` varchar(60) NOT NULL, `ro` varchar(60) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_titled` -- DROP TABLE IF EXISTS `cache_titled`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_titled` ( `cache_id` int(11) NOT NULL, `rate` float NOT NULL, `ratio` float NOT NULL, `rating` int(11) NOT NULL, `found` int(11) NOT NULL, `days` int(11) NOT NULL, `date_alg` date NOT NULL, `log_id` int(11) NOT NULL, PRIMARY KEY (`cache_id`), KEY `date_alg` (`date_alg`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_type` -- DROP TABLE IF EXISTS `cache_type`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sort` int(11) NOT NULL DEFAULT 100 COMMENT 'This also is the translation ID number; see I18n::getIdColumnName()', `short` varchar(10) NOT NULL, `pl` varchar(60) NOT NULL, `en` varchar(60) NOT NULL, `nl` varchar(60) NOT NULL, `de` varchar(60) NOT NULL, `fr` varchar(60) NOT NULL, `ro` varchar(60) NOT NULL, `icon_large` varchar(60) NOT NULL, `icon_small` varchar(60) NOT NULL, `color` varchar(7) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_visits2` -- DROP TABLE IF EXISTS `cache_visits2`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_visits2` ( `cache_id` int(11) NOT NULL, `user_id_ip` varchar(15) NOT NULL DEFAULT '' COMMENT 'user_id or used IP address', `type` varchar(1) NOT NULL COMMENT 'C=cache_visits; U=last_user_unique_visit; P=prepublication_user_visit', `count` int(11) NOT NULL DEFAULT 0, `visit_date` datetime NOT NULL, PRIMARY KEY (`cache_id`,`user_id_ip`,`type`), KEY `type` (`type`,`visit_date`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='User visits at geocache webpage counter'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `cache_watches` -- DROP TABLE IF EXISTS `cache_watches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cache_watches` ( `cache_id` int(11) NOT NULL DEFAULT 0, `user_id` int(11) NOT NULL DEFAULT 0, `last_executed` datetime DEFAULT NULL, PRIMARY KEY (`cache_id`,`user_id`), KEY `cache_id` (`cache_id`), KEY `user_id` (`user_id`), KEY `cache_id_user_id` (`cache_id`,`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cache_watches_insert AFTER INSERT ON `cache_watches` FOR EACH ROW begin UPDATE caches SET watcher = watcher + 1 WHERE `cache_id` = NEW.cache_id; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER cache_watches_delete AFTER DELETE ON `cache_watches` FOR EACH ROW begin IF IFNULL(@deleting_cache, 0) = 0 THEN UPDATE caches SET watcher = watcher - 1 WHERE `cache_id` = OLD.cache_id AND watcher > 0; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `caches` -- DROP TABLE IF EXISTS `caches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caches` ( `cache_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, `longitude` double DEFAULT NULL, `latitude` double DEFAULT NULL, `last_modified` datetime DEFAULT NULL, `okapi_syncbase` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `date_created` datetime DEFAULT NULL, `type` int(11) DEFAULT NULL, `status` int(11) DEFAULT NULL, `country` char(2) DEFAULT NULL, `date_hidden` datetime DEFAULT NULL, `founds` int(11) DEFAULT NULL, `notfounds` int(11) DEFAULT NULL, `notes` int(11) DEFAULT NULL, `images` int(11) DEFAULT NULL, `last_found` datetime DEFAULT NULL, `desc_languages` varchar(60) DEFAULT NULL, `size` int(11) DEFAULT NULL, `difficulty` int(11) DEFAULT NULL, `terrain` int(11) DEFAULT NULL, `uuid` varchar(36) DEFAULT NULL, `watcher` int(11) DEFAULT 0, `logpw` varchar(20) DEFAULT NULL, `picturescount` int(11) NOT NULL DEFAULT 0, `mp3count` int(11) NOT NULL DEFAULT 0, `search_time` double DEFAULT NULL, `way_length` double DEFAULT NULL, `wp_gc` varchar(7) NOT NULL, `wp_nc` varchar(6) NOT NULL, `wp_ge` varchar(7) NOT NULL COMMENT 'GPSGames', `wp_tc` varchar(7) NOT NULL COMMENT 'TerraCaching', `wp_qc` varchar(10) DEFAULT NULL COMMENT 'QualityCaching', `wp_oc` varchar(6) DEFAULT NULL, `default_desclang` char(2) NOT NULL DEFAULT '', `date_activate` datetime DEFAULT NULL, `topratings` int(11) NOT NULL DEFAULT 0, `ignorer_count` int(11) DEFAULT NULL, `node` tinyint(4) NOT NULL DEFAULT 0, `votes` int(11) NOT NULL DEFAULT 0, `score` float(2,1) NOT NULL DEFAULT 0.0, `need_npa_recalc` tinyint(1) NOT NULL DEFAULT 1, `org_user_id` int(11) DEFAULT NULL COMMENT 'Origianl user_id, who created the geocache', `date_published` datetime DEFAULT NULL, PRIMARY KEY (`cache_id`), UNIQUE KEY `wp_oc` (`wp_oc`), KEY `date_created` (`date_created`), KEY `latitude` (`latitude`), KEY `country` (`country`), KEY `status` (`status`,`date_activate`), KEY `last_modified` (`last_modified`), KEY `score` (`score`), KEY `type` (`type`), KEY `size` (`size`), KEY `difficulty` (`difficulty`), KEY `terrain` (`terrain`), KEY `name` (`name`), KEY `votes` (`votes`), KEY `picturescount` (`picturescount`), KEY `user_id` (`user_id`), KEY `need_npa_recalc` (`need_npa_recalc`), KEY `okapi_syncbase` (`okapi_syncbase`), KEY `longitude` (`longitude`), KEY `caches_org_user_id` (`org_user_id`), KEY `date_hidden` (`date_hidden`), KEY `founds` (`founds`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cachesBeforeInsert` BEFORE INSERT ON `caches` FOR EACH ROW BEGIN SET NEW.`need_npa_recalc` = 1; SET NEW.`date_published` = ( CASE WHEN NEW.`status` IN (1, 2) THEN NOW() ELSE NULL END ); END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cachesAfterInsert` AFTER INSERT ON `caches` FOR EACH ROW BEGIN INSERT IGNORE INTO `cache_coordinates` (`cache_id`, `date_modified`, `longitude`, `latitude`) VALUES (NEW.`cache_id`, NOW(), NEW.`longitude`, NEW.`latitude`); INSERT IGNORE INTO `cache_countries` (`cache_id`, `date_modified`, `country`) VALUES (NEW.`cache_id`, NOW(), NEW.`country`); UPDATE `user`, ( SELECT COUNT(*) AS `hidden_count` FROM `caches` WHERE `user_id` = NEW.`user_id` AND `status` IN (1, 2, 3) ) AS `c` SET `user`.`hidden_count`=`c`.`hidden_count` WHERE `user`.`user_id` = NEW.`user_id`; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cachesBeforeUpdate` BEFORE UPDATE ON `caches` FOR EACH ROW BEGIN IF OLD.`longitude` != NEW.`longitude` OR OLD.`latitude` != NEW.`latitude` THEN SET NEW.`need_npa_recalc` = 1; END IF; IF OLD.`date_published` IS NULL AND NEW.`status` IN (1, 2) THEN SET NEW.`date_published` = NOW(); END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cachesAfterUpdate` AFTER UPDATE ON `caches` FOR EACH ROW BEGIN IF NEW.`longitude` != OLD.`longitude` OR NEW.`latitude` != OLD.`latitude` THEN INSERT IGNORE INTO `cache_coordinates` (`cache_id`, `date_modified`, `longitude`, `latitude`) VALUES (NEW.`cache_id`, NOW(), NEW.`longitude`, NEW.`latitude`); END IF; IF NEW.`country` != OLD.`country` THEN INSERT IGNORE INTO `cache_countries` (`cache_id`, `date_modified`, `country`) VALUES (NEW.`cache_id`, NOW(), NEW.`country`); END IF; IF NEW.`status` != OLD.`status` OR NEW.`user_id` != OLD.`user_id` THEN UPDATE `user`, ( SELECT COUNT(*) AS `hidden_count` FROM `caches` WHERE `user_id` = NEW.`user_id` AND `status` IN (1, 2, 3) ) AS `c` SET `user`.`hidden_count` = `c`.`hidden_count` WHERE `user`.`user_id` = NEW.`user_id`; IF NEW.`user_id` != OLD.`user_id` THEN UPDATE `user`, ( SELECT COUNT(*) AS `hidden_count` FROM `caches` WHERE `user_id` = OLD.`user_id` AND `status` IN (1, 2, 3) ) AS `c` SET `user`.`hidden_count` = `c`.`hidden_count` WHERE `user`.`user_id` = OLD.`user_id`; END IF; END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cachesBeforeDelete` BEFORE DELETE ON `caches` FOR EACH ROW BEGIN IF IFNULL(@allowdelete, 0) = 0 THEN -- protection against accidential cache deletion; -- call to nonexistent proc throws error CALL must_not_delete_caches(); ELSE -- This is used e.g. for preparing developer VMs -- prevent recursive write access to caches table SET @deleting_cache = 1; -- owner's cache content & derived data DELETE FROM `caches_additions` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_coordinates` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_countries` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_desc` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_location` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_npa_areas` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `caches_attributes` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `chowner` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `opensprawdzacz` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `PowerTrail_actionsLog` WHERE `cacheId` = OLD.`cache_id`; DELETE FROM `PowerTrail_cacheCandidate` WHERE `cacheId` = OLD.`cache_id`; DELETE FROM `powerTrail_caches` WHERE `cacheId` = OLD.`cache_id`; DELETE FROM `waypoints` WHERE `cache_id` = OLD.`cache_id`; -- log entries and other cache-related data by users DELETE FROM `badge_cache` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_ignore` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_logs` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_mod_cords` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_moved` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_notes` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_rating` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_titled` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_watches` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `geokret_log` WHERE `geocache_id` = OLD.`cache_id`; DELETE FROM `recommendation_plan` WHERE `cacheId` = OLD.`cache_id`; DELETE FROM `scores` WHERE `cache_id` = OLD.`cache_id`; -- admin data DELETE FROM `admin_user_notes` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `approval_status` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `reports` WHERE `cache_id` = OLD.`cache_id`; -- other data DELETE FROM `CACHE_ACCESS_LOGS` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_arch` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `cache_visits2` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `notify_waiting` WHERE `cache_id` = OLD.`cache_id`; DELETE FROM `search_index` WHERE `cache_id` = OLD.`cache_id`; SET @deleting_cache = 0; -- There is also some OKAPI data, but it's temporary and will -- be cleaned up by cronjob. END IF; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`ocpl`@`localhost`*/ /*!50003 TRIGGER `cachesAfterDelete` AFTER DELETE ON `caches` FOR EACH ROW BEGIN UPDATE `user`, ( SELECT COUNT(*) AS `hidden_count` FROM `caches` WHERE `user_id` = OLD.`user_id` AND `status` IN (1, 2, 3) ) AS `c` SET `user`.`hidden_count` = `c`.`hidden_count` WHERE `user`.`user_id` = OLD.`user_id`; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Table structure for table `caches_additions` -- DROP TABLE IF EXISTS `caches_additions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caches_additions` ( `cache_id` int(11) NOT NULL COMMENT 'geocache identifier (table caches)', `altitude` int(11) DEFAULT NULL COMMENT 'geocache altitude', PRIMARY KEY (`cache_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='contain useful but not very important geocache information '; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `caches_attributes` -- DROP TABLE IF EXISTS `caches_attributes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `caches_attributes` ( `cache_id` int(11) NOT NULL, `attrib_id` int(11) NOT NULL, PRIMARY KEY (`cache_id`,`attrib_id`), KEY `attrib_id` (`attrib_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `chowner` -- DROP TABLE IF EXISTS `chowner`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `chowner` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `chowner_user_id` (`user_id`), KEY `chowner_cache_id` (`cache_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `countries` -- DROP TABLE IF EXISTS `countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `countries` ( `country_id` int(11) NOT NULL AUTO_INCREMENT, `pl` varchar(128) NOT NULL, `en` varchar(128) NOT NULL, `nl` varchar(128) NOT NULL, `de` varchar(128) NOT NULL, `fr` varchar(128) NOT NULL, `ro` varchar(128) NOT NULL, `short` char(2) NOT NULL, `list_default_pl` int(1) NOT NULL DEFAULT 0, `sort_pl` varchar(128) NOT NULL, `list_default_en` int(1) NOT NULL DEFAULT 0, `sort_en` varchar(128) NOT NULL, `list_default_nl` int(1) NOT NULL DEFAULT 0, `sort_nl` varchar(128) NOT NULL, `list_default_de` int(1) NOT NULL DEFAULT 0, `sort_de` varchar(128) NOT NULL, `list_default_fr` int(1) NOT NULL DEFAULT 0, `sort_fr` varchar(128) NOT NULL, `list_default_ro` int(1) NOT NULL DEFAULT 0, `sort_ro` varchar(128) NOT NULL, PRIMARY KEY (`country_id`), UNIQUE KEY `short` (`short`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `db_update_history` -- DROP TABLE IF EXISTS `db_update_history`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `db_update_history` ( `uuid` varchar(36) NOT NULL, `name` varchar(60) NOT NULL COMMENT 'redundant information / cache', `wasRunAt` datetime NOT NULL, PRIMARY KEY (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `db_update_tests` -- DROP TABLE IF EXISTS `db_update_tests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `db_update_tests` ( `uuid` varchar(36) NOT NULL, `id` int(11) DEFAULT NULL COMMENT 'new comment', `created` datetime NOT NULL, `fnumber` float(5,1) unsigned DEFAULT 99.0 COMMENT 'some float number', `modifytest` text NOT NULL COMMENT 'test column', PRIMARY KEY (`uuid`), KEY `uc` (`uuid`,`created`), KEY `id` (`id`), FULLTEXT KEY `uft` (`uuid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `email_schemas` -- DROP TABLE IF EXISTS `email_schemas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `email_schemas` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `version` int(11) NOT NULL DEFAULT 1, `object_type` int(11) DEFAULT 1 COMMENT '1 - cache, 2 - PowerTrail', `shortdesc` varchar(100) NOT NULL, `text` varchar(10000) NOT NULL, `receiver` int(11) NOT NULL, `author_id` int(11) DEFAULT NULL, `date_created` timestamp NULL DEFAULT current_timestamp(), `deleted` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `version` (`version`), KEY `object_type` (`object_type`), KEY `receiver` (`receiver`), KEY `name` (`name`), KEY `deleted` (`deleted`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `email_user` -- DROP TABLE IF EXISTS `email_user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `email_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ipaddress` varchar(20) NOT NULL, `date_generated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `from_user_id` int(11) NOT NULL DEFAULT 0, `from_email` varchar(60) NOT NULL, `to_user_id` int(11) NOT NULL DEFAULT 0, `to_email` varchar(60) NOT NULL, `mail_subject` varchar(255) NOT NULL, `send_emailaddress` int(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `from_user_id` (`from_user_id`), KEY `date_generated` (`date_generated`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_areas` -- DROP TABLE IF EXISTS `geodb_areas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_areas` ( `loc_id` int(11) NOT NULL DEFAULT 0, `area_id` int(11) NOT NULL DEFAULT 0, `polygon_id` int(11) DEFAULT NULL, `pol_seq_no` int(11) NOT NULL DEFAULT 0, `exclude_area` smallint(1) NOT NULL DEFAULT 0, `area_type` int(11) NOT NULL DEFAULT 0, `area_subtype` int(11) DEFAULT NULL, `coord_type` int(11) NOT NULL DEFAULT 0, `coord_subtype` int(11) DEFAULT NULL, `resolution` int(11) NOT NULL DEFAULT 0, `valid_since` date DEFAULT NULL, `date_type_since` int(11) DEFAULT NULL, `valid_until` date NOT NULL DEFAULT '0000-00-00', `date_type_until` int(11) NOT NULL DEFAULT 0, UNIQUE KEY `loc_id` (`loc_id`,`area_id`), KEY `areas_loc_id_idx` (`loc_id`), KEY `areas_area_id_idx` (`area_id`), KEY `areas_pol_id_idx` (`polygon_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_changelog` -- DROP TABLE IF EXISTS `geodb_changelog`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_changelog` ( `id` int(11) NOT NULL DEFAULT 0, `datum` date NOT NULL DEFAULT '0000-00-00', `beschreibung` text NOT NULL, `autor` varchar(50) NOT NULL DEFAULT '', `version` varchar(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_coordinates` -- DROP TABLE IF EXISTS `geodb_coordinates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_coordinates` ( `loc_id` int(11) NOT NULL DEFAULT 0, `lon` double DEFAULT NULL, `lat` double DEFAULT NULL, `coord_type` int(11) NOT NULL DEFAULT 0, `coord_subtype` int(11) DEFAULT NULL, `valid_since` date DEFAULT NULL, `date_type_since` int(11) DEFAULT NULL, `valid_until` date NOT NULL DEFAULT '0000-00-00', `date_type_until` int(11) NOT NULL DEFAULT 0, KEY `coord_loc_id_idx` (`loc_id`), KEY `coord_lon_idx` (`lon`), KEY `coord_lat_idx` (`lat`), KEY `coord_type_idx` (`coord_type`), KEY `coord_stype_idx` (`coord_subtype`), KEY `coord_since_idx` (`valid_since`), KEY `coord_until_idx` (`valid_until`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_floatdata` -- DROP TABLE IF EXISTS `geodb_floatdata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_floatdata` ( `loc_id` int(11) NOT NULL DEFAULT 0, `float_val` double NOT NULL DEFAULT 0, `float_type` int(11) NOT NULL DEFAULT 0, `float_subtype` int(11) DEFAULT NULL, `valid_since` date DEFAULT NULL, `date_type_since` int(11) DEFAULT NULL, `valid_until` date NOT NULL DEFAULT '0000-00-00', `date_type_until` int(11) NOT NULL DEFAULT 0, KEY `float_lid_idx` (`loc_id`), KEY `float_val_idx` (`float_val`), KEY `float_type_idx` (`float_type`), KEY `float_stype_idx` (`float_subtype`), KEY `float_since_idx` (`valid_since`), KEY `float_until_idx` (`valid_until`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_hierarchies` -- DROP TABLE IF EXISTS `geodb_hierarchies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_hierarchies` ( `loc_id` int(11) NOT NULL DEFAULT 0, `level` int(11) NOT NULL DEFAULT 0, `id_lvl1` int(11) NOT NULL DEFAULT 0, `id_lvl2` int(11) DEFAULT NULL, `id_lvl3` int(11) DEFAULT NULL, `id_lvl4` int(11) DEFAULT NULL, `id_lvl5` int(11) DEFAULT NULL, `id_lvl6` int(11) DEFAULT NULL, `id_lvl7` int(11) DEFAULT NULL, `id_lvl8` int(11) DEFAULT NULL, `id_lvl9` int(11) DEFAULT NULL, `valid_since` date DEFAULT NULL, `date_type_since` int(11) DEFAULT NULL, `valid_until` date NOT NULL DEFAULT '0000-00-00', `date_type_until` int(11) NOT NULL DEFAULT 0, KEY `hierarchy_loc_id_idx` (`loc_id`), KEY `hierarchy_level_idx` (`level`), KEY `hierarchy_lvl1_idx` (`id_lvl1`), KEY `hierarchy_lvl2_idx` (`id_lvl2`), KEY `hierarchy_lvl3_idx` (`id_lvl3`), KEY `hierarchy_lvl4_idx` (`id_lvl4`), KEY `hierarchy_lvl5_idx` (`id_lvl5`), KEY `hierarchy_lvl6_idx` (`id_lvl6`), KEY `hierarchy_lvl7_idx` (`id_lvl7`), KEY `hierarchy_lvl8_idx` (`id_lvl8`), KEY `hierarchy_lvl9_idx` (`id_lvl9`), KEY `hierarchy_since_idx` (`valid_since`), KEY `hierarchy_until_idx` (`valid_until`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_intdata` -- DROP TABLE IF EXISTS `geodb_intdata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_intdata` ( `loc_id` int(11) NOT NULL DEFAULT 0, `int_val` bigint(20) NOT NULL DEFAULT 0, `int_type` int(11) NOT NULL DEFAULT 0, `int_subtype` int(11) DEFAULT NULL, `valid_since` date DEFAULT NULL, `date_type_since` int(11) DEFAULT NULL, `valid_until` date NOT NULL DEFAULT '0000-00-00', `date_type_until` int(11) NOT NULL DEFAULT 0, KEY `int_lid_idx` (`loc_id`), KEY `int_val_idx` (`int_val`), KEY `int_type_idx` (`int_type`), KEY `int_stype_idx` (`int_subtype`), KEY `int_since_idx` (`valid_since`), KEY `int_until_idx` (`valid_until`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_locations` -- DROP TABLE IF EXISTS `geodb_locations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_locations` ( `loc_id` int(11) NOT NULL DEFAULT 0, `loc_type` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`loc_id`), KEY `loc_type_idx` (`loc_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_polygons` -- DROP TABLE IF EXISTS `geodb_polygons`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_polygons` ( `polygon_id` int(11) NOT NULL DEFAULT 0, `seq_no` int(11) NOT NULL DEFAULT 0, `lon` double NOT NULL DEFAULT 0, `lat` double NOT NULL DEFAULT 0, UNIQUE KEY `polygon_id` (`polygon_id`,`seq_no`), KEY `polygons_pid_idx` (`polygon_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_search` -- DROP TABLE IF EXISTS `geodb_search`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_search` ( `id` int(11) NOT NULL AUTO_INCREMENT, `loc_id` int(11) NOT NULL DEFAULT 0, `sort` varchar(255) NOT NULL, `simple` varchar(255) NOT NULL, `simplehash` int(11) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `sort` (`sort`), KEY `simple` (`simple`), KEY `simplehash` (`simplehash`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_textdata` -- DROP TABLE IF EXISTS `geodb_textdata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_textdata` ( `loc_id` int(11) NOT NULL DEFAULT 0, `text_val` varchar(255) NOT NULL DEFAULT '', `text_type` int(11) NOT NULL DEFAULT 0, `text_locale` varchar(5) DEFAULT NULL, `is_native_lang` smallint(1) DEFAULT NULL, `is_default_name` smallint(1) DEFAULT NULL, `valid_since` date DEFAULT NULL, `date_type_since` int(11) DEFAULT NULL, `valid_until` date NOT NULL DEFAULT '0000-00-00', `date_type_until` int(11) NOT NULL DEFAULT 0, KEY `text_lid_idx` (`loc_id`), KEY `text_val_idx` (`text_val`), KEY `text_type_idx` (`text_type`), KEY `text_locale_idx` (`text_locale`), KEY `text_native_idx` (`is_native_lang`), KEY `text_default_idx` (`is_default_name`), KEY `text_since_idx` (`valid_since`), KEY `text_until_idx` (`valid_until`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geodb_type_names` -- DROP TABLE IF EXISTS `geodb_type_names`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geodb_type_names` ( `type_id` int(11) NOT NULL DEFAULT 0, `type_locale` varchar(5) NOT NULL DEFAULT '', `name` varchar(255) NOT NULL DEFAULT '', UNIQUE KEY `type_id` (`type_id`,`type_locale`), KEY `tid_tnames_idx` (`type_id`), KEY `locale_tnames_idx` (`type_locale`), KEY `name_tnames_idx` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `geokret_log` -- DROP TABLE IF EXISTS `geokret_log`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `geokret_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `log_date_time` datetime NOT NULL, `enqueue_date_time` datetime NOT NULL, `user_id` int(11) NOT NULL, `geocache_id` int(11) NOT NULL, `log_type` int(11) NOT NULL, `comment` varchar(160) NOT NULL, `tracking_code` varchar(10) NOT NULL, `geokret_id` int(11) NOT NULL, `geokret_name` varchar(160) NOT NULL, `last_try` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `enqueue_date_time` (`enqueue_date_time`), KEY `last_try_index` (`last_try`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_item` -- DROP TABLE IF EXISTS `gk_item`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_item` ( `id` int(11) NOT NULL, `name` varchar(60) NOT NULL, `description` longtext NOT NULL, `userid` int(11) NOT NULL, `datecreated` datetime NOT NULL, `datemodified` datetime NOT NULL, `distancetravelled` float NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, `typeid` int(11) NOT NULL, `stateid` tinyint(4) NOT NULL, `missing` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `stateid` (`stateid`), KEY `typeid` (`typeid`), KEY `id_stateid_typeid` (`id`,`stateid`,`typeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_item_type` -- DROP TABLE IF EXISTS `gk_item_type`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_item_type` ( `id` int(11) NOT NULL, `name` varchar(60) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_item_waypoint` -- DROP TABLE IF EXISTS `gk_item_waypoint`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_item_waypoint` ( `id` int(11) NOT NULL, `wp` varchar(10) NOT NULL, PRIMARY KEY (`id`,`wp`), KEY `wp` (`wp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_move` -- DROP TABLE IF EXISTS `gk_move`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_move` ( `id` int(11) NOT NULL, `itemid` int(11) NOT NULL, `latitude` double NOT NULL, `longitude` double NOT NULL, `datemoved` datetime NOT NULL, `datelogged` datetime NOT NULL, `userid` int(11) NOT NULL, `comment` longtext NOT NULL, `logtypeid` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `itemid` (`itemid`), KEY `userid` (`userid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_move_type` -- DROP TABLE IF EXISTS `gk_move_type`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_move_type` ( `id` int(11) NOT NULL, `name` varchar(60) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_move_waypoint` -- DROP TABLE IF EXISTS `gk_move_waypoint`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_move_waypoint` ( `id` int(11) NOT NULL, `wp` varchar(10) NOT NULL, PRIMARY KEY (`id`,`wp`), KEY `wp` (`wp`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gk_user` -- DROP TABLE IF EXISTS `gk_user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gk_user` ( `id` int(11) NOT NULL, `name` varchar(60) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gns_locations` -- DROP TABLE IF EXISTS `gns_locations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gns_locations` ( `RC` tinyint(4) NOT NULL DEFAULT 0, `UFI` int(11) NOT NULL DEFAULT 0, `UNI` int(11) NOT NULL DEFAULT 0, `LAT` double NOT NULL DEFAULT 0, `LON` double NOT NULL DEFAULT 0, `DMS_LAT` int(11) DEFAULT NULL, `DMS_LONG` int(11) DEFAULT NULL, `MGRS` varchar(4) DEFAULT NULL, `JOG` varchar(7) DEFAULT NULL, `FC` char(1) DEFAULT NULL, `DSG` varchar(5) DEFAULT NULL, `PC` tinyint(4) DEFAULT NULL, `CC1` char(2) DEFAULT NULL, `ADM1` char(2) DEFAULT NULL, `POP` varchar(200) DEFAULT NULL, `ELEV` int(11) DEFAULT NULL, `CC2` char(2) DEFAULT NULL, `NT` char(1) DEFAULT NULL, `LC` char(2) DEFAULT NULL, `SHORT_FORM` varchar(128) DEFAULT NULL, `GENERIC` varchar(128) DEFAULT NULL, `SORT_NAME` varchar(200) DEFAULT NULL, `FULL_NAME` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, `FULL_NAME_ND` varchar(200) DEFAULT NULL, `SORT_NAME_RG` varchar(200) DEFAULT NULL, `FULL_NAME_RG` varchar(200) DEFAULT NULL, `FULL_NAME_ND_RG` varchar(200) DEFAULT NULL, `NOTE` varchar(200) DEFAULT NULL, `MODIFY_DATE` date DEFAULT NULL, `DISPLAY` varchar(200) DEFAULT NULL, `NAME_RANK` int(11) DEFAULT NULL, `NAME_LINK` int(11) DEFAULT NULL, `TRANSL_CD` varchar(200) DEFAULT NULL, `NM_MODIFY_DATE` date DEFAULT NULL, `ADMTXT1` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, `ADMTXT3` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, `ADMTXT4` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, `ADMTXT2` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, PRIMARY KEY (`UNI`), KEY `ufi` (`UFI`), KEY `key1` (`DSG`,`CC1`,`ADM1`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `gns_search` -- DROP TABLE IF EXISTS `gns_search`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `gns_search` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uni_id` int(11) NOT NULL DEFAULT 0, `sort` varchar(255) NOT NULL, `simple` varchar(255) NOT NULL, `simplehash` int(11) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `simplehash` (`simplehash`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `import_caches_date` -- DROP TABLE IF EXISTS `import_caches_date`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `import_caches_date` ( `node_id` int(11) NOT NULL, `updated` int(11) NOT NULL, PRIMARY KEY (`node_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_polish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `languages` -- DROP TABLE IF EXISTS `languages`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `languages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `short` char(2) NOT NULL, `pl` varchar(60) NOT NULL, `en` varchar(60) NOT NULL, `nl` varchar(60) NOT NULL, `de` varchar(60) NOT NULL, `fr` varchar(60) NOT NULL, `ro` varchar(60) NOT NULL, `list_default_pl` int(1) NOT NULL DEFAULT 0, `list_default_en` int(1) NOT NULL DEFAULT 0, `list_default_nl` int(1) NOT NULL DEFAULT 0, `list_default_de` int(1) NOT NULL DEFAULT 0, `list_default_fr` int(1) NOT NULL DEFAULT 0, `list_default_ro` int(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `short` (`short`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `log_types` -- DROP TABLE IF EXISTS `log_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `log_types` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_status` int(1) NOT NULL DEFAULT 0, `permission` char(1) NOT NULL, `pl` varchar(60) NOT NULL, `en` varchar(60) NOT NULL, `nl` varchar(60) NOT NULL, `de` varchar(60) NOT NULL, `fr` varchar(60) NOT NULL, `ro` varchar(60) NOT NULL, `icon_small` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `log_types_text` -- DROP TABLE IF EXISTS `log_types_text`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `log_types_text` ( `id` int(11) NOT NULL AUTO_INCREMENT, `log_types_id` int(11) NOT NULL DEFAULT 0, `lang` char(2) NOT NULL, `text_combo` varchar(255) NOT NULL, `text_listing` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `lang` (`lang`,`log_types_id`), KEY `log_types_id` (`log_types_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `logentries` -- DROP TABLE IF EXISTS `logentries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `logentries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `module` varchar(30) DEFAULT NULL, `eventid` int(11) NOT NULL DEFAULT 0, `userid` int(11) NOT NULL DEFAULT 0, `objectid1` int(11) NOT NULL DEFAULT 0, `objectid2` int(11) NOT NULL DEFAULT 0, `logtext` mediumtext NOT NULL, `details` blob NOT NULL, `logtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), KEY `logtime` (`logtime`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `mp3` -- DROP TABLE IF EXISTS `mp3`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `mp3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(36) NOT NULL, `url` varchar(255) NOT NULL, `last_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `title` varchar(250) DEFAULT NULL, `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `last_url_check` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `object_id` int(11) NOT NULL DEFAULT 0, `object_type` int(11) NOT NULL DEFAULT 0, `user_id` int(11) NOT NULL DEFAULT 0, `local` int(1) NOT NULL DEFAULT 1, `unknown_format` int(1) NOT NULL DEFAULT 0, `display` int(1) NOT NULL DEFAULT 1, `node` tinyint(4) NOT NULL DEFAULT 0, `seq` smallint(5) unsigned NOT NULL DEFAULT 1, PRIMARY KEY (`id`), KEY `last_modified` (`last_modified`), KEY `url` (`url`), KEY `title` (`title`), KEY `object_id` (`object_id`), KEY `uuid` (`uuid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `news` -- DROP TABLE IF EXISTS `news`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `news` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category` varchar(10) DEFAULT NULL COMMENT 'category of news allow to select only set of news', `title` tinytext DEFAULT NULL COMMENT 'Title of the news', `content` text DEFAULT NULL, `user_id` int(11) NOT NULL DEFAULT 0 COMMENT 'Author userID', `edited_by` int(11) NOT NULL DEFAULT 0 COMMENT 'UserID who last edited this news', `hide_author` int(1) NOT NULL DEFAULT 1 COMMENT 'Dislpay OC Team instead of author', `show_onmainpage` int(1) NOT NULL DEFAULT 1 COMMENT 'Show news on mainpage', `show_notlogged` int(1) NOT NULL DEFAULT 0 COMMENT 'Show news to not logged users', `date_publication` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Date from which to publish the news', `date_expiration` datetime DEFAULT NULL, `date_mainpageexp` datetime DEFAULT NULL COMMENT 'End of publication on the main page', `date_lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT 'Last modification date', PRIMARY KEY (`id`), KEY `show_onmainpage` (`show_onmainpage`), KEY `show_notlogged` (`show_notlogged`), KEY `date_publication` (`date_publication`), KEY `date_expiration` (`date_expiration`), KEY `date_mainpageexp` (`date_mainpageexp`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `notify_waiting` -- DROP TABLE IF EXISTS `notify_waiting`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `notify_waiting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL DEFAULT 0, `user_id` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `cache_user` (`cache_id`,`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `npa_areas` -- DROP TABLE IF EXISTS `npa_areas`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `npa_areas` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `linkid` int(4) DEFAULT NULL, `sitename` varchar(255) NOT NULL, `sitecode` varchar(255) NOT NULL, `sitetype` char(1) NOT NULL, `shape` geometry NOT NULL, PRIMARY KEY (`id`), KEY `shape` (`sitecode`(32)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `nuts_codes` -- DROP TABLE IF EXISTS `nuts_codes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `nuts_codes` ( `code` varchar(10) NOT NULL, `name` varchar(120) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, PRIMARY KEY (`code`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `nuts_layer` -- DROP TABLE IF EXISTS `nuts_layer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `nuts_layer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level` tinyint(1) NOT NULL, `code` varchar(5) NOT NULL, `shape` geometry NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`), KEY `level` (`level`), SPATIAL KEY `shape` (`shape`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_authorizations` -- DROP TABLE IF EXISTS `okapi_authorizations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_authorizations` ( `consumer_key` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `user_id` int(11) NOT NULL, `last_access_token` datetime DEFAULT NULL, PRIMARY KEY (`consumer_key`,`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_cache` -- DROP TABLE IF EXISTS `okapi_cache`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_cache` ( `key` varchar(64) NOT NULL, `score` float DEFAULT NULL, `expires` datetime DEFAULT NULL, `value` mediumblob DEFAULT NULL, PRIMARY KEY (`key`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_cache_reads` -- DROP TABLE IF EXISTS `okapi_cache_reads`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_cache_reads` ( `cache_key` varchar(64) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_clog` -- DROP TABLE IF EXISTS `okapi_clog`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_clog` ( `id` int(10) NOT NULL AUTO_INCREMENT, `data` mediumblob DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_consumers` -- DROP TABLE IF EXISTS `okapi_consumers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_consumers` ( `key` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `name` varchar(100) NOT NULL, `secret` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `url` varchar(250) DEFAULT NULL, `email` varchar(70) DEFAULT NULL, `date_created` datetime NOT NULL, `bflags` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_diagnostics` -- DROP TABLE IF EXISTS `okapi_diagnostics`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_diagnostics` ( `recorded_at` timestamp NOT NULL DEFAULT current_timestamp(), `expires` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `action` char(1) NOT NULL, `comment` varchar(255) NOT NULL, `consumer_key` varchar(20) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL, KEY `by_action` (`action`,`recorded_at`), KEY `by_expires` (`expires`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_nonces` -- DROP TABLE IF EXISTS `okapi_nonces`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_nonces` ( `consumer_key` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `nonce_hash` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `timestamp` int(10) NOT NULL, PRIMARY KEY (`consumer_key`,`nonce_hash`) ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_search_results` -- DROP TABLE IF EXISTS `okapi_search_results`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_search_results` ( `set_id` mediumint(6) unsigned NOT NULL, `cache_id` mediumint(6) unsigned NOT NULL, PRIMARY KEY (`set_id`,`cache_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=COMPACT; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_search_sets` -- DROP TABLE IF EXISTS `okapi_search_sets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_search_sets` ( `id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT, `params_hash` varchar(64) NOT NULL, `date_created` datetime NOT NULL, `expires` datetime NOT NULL, PRIMARY KEY (`id`), KEY `by_hash` (`params_hash`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_signals` -- DROP TABLE IF EXISTS `okapi_signals`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_signals` ( `id` int(10) NOT NULL AUTO_INCREMENT, `type` tinyint(2) NOT NULL, `payload` blob NOT NULL, `created_at` datetime NOT NULL, `fetched_at` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_stats_hourly` -- DROP TABLE IF EXISTS `okapi_stats_hourly`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_stats_hourly` ( `consumer_key` varchar(32) NOT NULL, `user_id` int(10) NOT NULL, `period_start` datetime NOT NULL, `service_name` varchar(80) NOT NULL, `total_calls` int(10) NOT NULL, `http_calls` int(10) NOT NULL, `total_runtime` float NOT NULL DEFAULT 0, `http_runtime` float NOT NULL DEFAULT 0, PRIMARY KEY (`consumer_key`,`user_id`,`period_start`,`service_name`), KEY `by_service` (`service_name`,`period_start`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_stats_monthly` -- DROP TABLE IF EXISTS `okapi_stats_monthly`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_stats_monthly` ( `consumer_key` varchar(32) NOT NULL, `user_id` int(10) NOT NULL, `period_start` datetime NOT NULL, `service_name` varchar(80) NOT NULL, `total_calls` int(10) NOT NULL, `http_calls` int(10) NOT NULL, `total_runtime` float NOT NULL DEFAULT 0, `http_runtime` float NOT NULL DEFAULT 0, PRIMARY KEY (`consumer_key`,`user_id`,`period_start`,`service_name`), KEY `by_service` (`service_name`,`period_start`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_stats_temp` -- DROP TABLE IF EXISTS `okapi_stats_temp`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_stats_temp` ( `datetime` datetime NOT NULL, `consumer_key` varchar(32) NOT NULL DEFAULT 'internal', `user_id` int(10) NOT NULL DEFAULT -1, `service_name` varchar(80) NOT NULL, `calltype` enum('internal','http') NOT NULL, `runtime` float NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_submitted_objects` -- DROP TABLE IF EXISTS `okapi_submitted_objects`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_submitted_objects` ( `object_type` tinyint(2) NOT NULL, `object_id` int(11) NOT NULL, `consumer_key` varchar(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, PRIMARY KEY (`object_type`,`object_id`), KEY `by_consumer` (`consumer_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_tile_caches` -- DROP TABLE IF EXISTS `okapi_tile_caches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_tile_caches` ( `z` tinyint(2) NOT NULL, `x` mediumint(6) unsigned NOT NULL, `y` mediumint(6) unsigned NOT NULL, `cache_id` mediumint(6) unsigned NOT NULL, `z21x` int(10) unsigned NOT NULL, `z21y` int(10) unsigned NOT NULL, `status` tinyint(1) unsigned NOT NULL, `type` tinyint(1) unsigned NOT NULL, `rating` tinyint(1) unsigned DEFAULT NULL, `flags` tinyint(1) unsigned NOT NULL, `name_crc` int(10) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`z`,`x`,`y`,`cache_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_tile_status` -- DROP TABLE IF EXISTS `okapi_tile_status`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_tile_status` ( `z` tinyint(2) NOT NULL, `x` mediumint(6) unsigned NOT NULL, `y` mediumint(6) unsigned NOT NULL, `status` tinyint(1) unsigned NOT NULL, PRIMARY KEY (`z`,`x`,`y`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_tokens` -- DROP TABLE IF EXISTS `okapi_tokens`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_tokens` ( `key` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `secret` varchar(40) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `token_type` enum('request','access') NOT NULL, `timestamp` int(10) NOT NULL, `user_id` int(10) DEFAULT NULL, `consumer_key` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `verifier` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL, `callback` varchar(2083) DEFAULT NULL, PRIMARY KEY (`key`), KEY `by_consumer` (`consumer_key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `okapi_vars` -- DROP TABLE IF EXISTS `okapi_vars`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `okapi_vars` ( `var` varchar(32) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL, `value` text DEFAULT NULL, PRIMARY KEY (`var`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `opensprawdzacz` -- DROP TABLE IF EXISTS `opensprawdzacz`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `opensprawdzacz` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL, `proby` int(11) NOT NULL, `sukcesy` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `cache_id` (`cache_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `parkipl` -- DROP TABLE IF EXISTS `parkipl`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `parkipl` ( `OGR_FID` int(11) NOT NULL AUTO_INCREMENT, `SHAPE` geometry NOT NULL, `id` double DEFAULT NULL, `name` varchar(80) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, `type` varchar(30) DEFAULT NULL, `dispclass` double DEFAULT NULL, `xcoords` varchar(11) DEFAULT NULL, `ycoords` varchar(11) DEFAULT NULL, `link` varchar(240) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci NOT NULL DEFAULT 'www.parkinarodowe.edu.pl/pn/ ', `logo` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci NOT NULL DEFAULT 'npa.png', UNIQUE KEY `OGR_FID` (`OGR_FID`), SPATIAL KEY `SHAPE` (`SHAPE`), KEY `id` (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pds_send` -- DROP TABLE IF EXISTS `pds_send`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pds_send` ( `user_id` int(11) NOT NULL, `email` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pds_was_sent` -- DROP TABLE IF EXISTS `pds_was_sent`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pds_was_sent` ( `user_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `pictures` -- DROP TABLE IF EXISTS `pictures`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `pictures` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(36) NOT NULL, `url` varchar(255) NOT NULL, `last_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `title` varchar(250) DEFAULT NULL, `description` text DEFAULT NULL, `desc_html` int(11) NOT NULL DEFAULT 0, `date_created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `last_url_check` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `object_id` int(11) NOT NULL DEFAULT 0, `object_type` int(11) NOT NULL DEFAULT 0, `user_id` int(11) NOT NULL DEFAULT 0, `thumb_url` varchar(255) NOT NULL DEFAULT '', `thumb_last_generated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `spoiler` int(1) NOT NULL DEFAULT 0, `local` int(1) NOT NULL DEFAULT 1, `unknown_format` int(1) NOT NULL DEFAULT 0, `display` int(1) NOT NULL DEFAULT 1, `node` tinyint(4) NOT NULL DEFAULT 0, `seq` smallint(5) unsigned NOT NULL DEFAULT 1, PRIMARY KEY (`id`), KEY `last_modified` (`last_modified`), KEY `url` (`url`), KEY `title` (`title`), KEY `object_id` (`object_id`), KEY `uuid` (`uuid`), KEY `object_type` (`object_type`), KEY `display` (`display`), KEY `unknown_format` (`unknown_format`), KEY `seq` (`seq`), KEY `date_created` (`date_created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `powerTrail_caches` -- DROP TABLE IF EXISTS `powerTrail_caches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `powerTrail_caches` ( `cacheId` int(11) NOT NULL, `PowerTrailId` int(11) NOT NULL, `points` float NOT NULL, `isFinal` smallint(6) NOT NULL DEFAULT 0 COMMENT 'if cache is final cache = 1, not final cache = 0', UNIQUE KEY `cacheId` (`cacheId`,`PowerTrailId`), KEY `PowerTrailId` (`PowerTrailId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='combain caches witch PowerTrails'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `powertrail_progress` -- DROP TABLE IF EXISTS `powertrail_progress`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `powertrail_progress` ( `user_id` int(11) NOT NULL, `pt_id` int(11) NOT NULL, `founds` int(11) NOT NULL, PRIMARY KEY (`user_id`,`pt_id`), KEY `pt_id` (`pt_id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `queries` -- DROP TABLE IF EXISTS `queries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `queries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL DEFAULT 0, `name` varchar(60) NOT NULL, `options` blob NOT NULL, `uuid` varchar(36) NOT NULL, `filters_count` int(11) NOT NULL DEFAULT 0, `last_queried` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `UUID` (`uuid`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `recommendation_plan` -- DROP TABLE IF EXISTS `recommendation_plan`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `recommendation_plan` ( `cacheId` int(11) NOT NULL, `userId` int(11) NOT NULL, `logId` int(11) NOT NULL, PRIMARY KEY (`cacheId`,`userId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `removed_objects` -- DROP TABLE IF EXISTS `removed_objects`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `removed_objects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `localID` int(11) NOT NULL DEFAULT 0, `uuid` varchar(36) NOT NULL, `type` int(1) NOT NULL DEFAULT 0, `removed_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `node` tinyint(4) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `UUID` (`uuid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `reports` -- DROP TABLE IF EXISTS `reports`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `reports` ( `id` int(11) NOT NULL AUTO_INCREMENT, `object_type` int(11) NOT NULL DEFAULT 1 COMMENT '1 - cache, 2 - PowerTrail', `uuid` varchar(36) DEFAULT NULL, `user_id` int(11) NOT NULL, `cache_id` int(11) DEFAULT NULL, `PowerTrail_id` int(11) DEFAULT NULL, `type` tinyint(10) NOT NULL DEFAULT 4, `text` varchar(4096) NOT NULL, `note` text NOT NULL, `submit_date` timestamp NOT NULL DEFAULT current_timestamp(), `status` tinyint(3) NOT NULL DEFAULT 0, `secret` tinytext DEFAULT NULL, `changed_by` int(11) NOT NULL DEFAULT 0, `changed_date` timestamp NULL DEFAULT NULL, `responsible_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `type` (`type`), KEY `status` (`status`), KEY `responsible_id` (`responsible_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `reports_log` -- DROP TABLE IF EXISTS `reports_log`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `reports_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `report_id` int(11) NOT NULL, `type` int(11) NOT NULL, `content` text DEFAULT NULL, `user_id` int(11) NOT NULL, `poll_id` int(11) DEFAULT NULL, `date_created` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `report_id` (`report_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `reports_poll` -- DROP TABLE IF EXISTS `reports_poll`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `reports_poll` ( `id` int(11) NOT NULL AUTO_INCREMENT, `report_id` int(11) NOT NULL, `date_start` datetime NOT NULL, `date_end` datetime NOT NULL, `question` tinytext NOT NULL, `ans1` tinytext NOT NULL, `ans2` tinytext NOT NULL, `ans3` tinytext DEFAULT NULL, PRIMARY KEY (`id`), KEY `report_id` (`report_id`), KEY `date_end` (`date_end`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `reports_poll_votes` -- DROP TABLE IF EXISTS `reports_poll_votes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `reports_poll_votes` ( `poll_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `vote` int(11) NOT NULL, `date_created` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`poll_id`,`user_id`), KEY `vote` (`vote`), KEY `poll_id` (`poll_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `reports_watches` -- DROP TABLE IF EXISTS `reports_watches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `reports_watches` ( `report_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `date_created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (`report_id`,`user_id`), KEY `report_id` (`report_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin COMMENT='Stores info about OC Team users who watches reports'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `route_points` -- DROP TABLE IF EXISTS `route_points`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `route_points` ( `route_id` int(11) DEFAULT NULL, `point_nr` int(10) DEFAULT NULL, `lon` double DEFAULT NULL, `lat` double DEFAULT NULL, KEY `route_id` (`route_id`), KEY `lon` (`lon`,`lat`), KEY `lat` (`lat`), KEY `point_nr` (`point_nr`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='route points'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `routes` -- DROP TABLE IF EXISTS `routes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `routes` ( `route_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `name` varchar(60) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `radius` float DEFAULT NULL, `length` float DEFAULT NULL, `options` blob DEFAULT NULL, PRIMARY KEY (`route_id`), KEY `name` (`name`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='routes name'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `scores` -- DROP TABLE IF EXISTS `scores`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `scores` ( `id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `score` float NOT NULL, PRIMARY KEY (`id`), KEY `cache_id` (`cache_id`), KEY `user_id` (`user_id`), KEY `score` (`score`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `search_ignore` -- DROP TABLE IF EXISTS `search_ignore`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `search_ignore` ( `word` varchar(30) NOT NULL, PRIMARY KEY (`word`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `search_index` -- DROP TABLE IF EXISTS `search_index`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `search_index` ( `object_type` tinyint(4) NOT NULL, `cache_id` int(11) NOT NULL, `hash` int(10) unsigned NOT NULL, `count` tinyint(4) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`object_type`,`cache_id`,`hash`), KEY `object_type` (`object_type`,`hash`,`cache_id`,`count`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `search_index_times` -- DROP TABLE IF EXISTS `search_index_times`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `search_index_times` ( `object_type` tinyint(4) NOT NULL, `object_id` int(11) NOT NULL, `last_refresh` datetime NOT NULL, PRIMARY KEY (`object_type`,`object_id`), KEY `last_refresh` (`last_refresh`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `search_words` -- DROP TABLE IF EXISTS `search_words`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `search_words` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `word` varchar(255) NOT NULL, `simple` varchar(30) NOT NULL, `hash` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `hash` (`hash`,`word`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `statpics` -- DROP TABLE IF EXISTS `statpics`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `statpics` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tplpath` varchar(200) NOT NULL, `previewpath` varchar(200) NOT NULL, `description` varchar(80) NOT NULL, `maxtextwidth` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `sys_logins` -- DROP TABLE IF EXISTS `sys_logins`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sys_logins` ( `id` int(11) NOT NULL AUTO_INCREMENT, `remote_addr` varchar(15) NOT NULL, `timestamp` datetime NOT NULL, PRIMARY KEY (`id`), KEY `timestamp` (`timestamp`), KEY `remote_addr` (`remote_addr`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `sys_sessions` -- DROP TABLE IF EXISTS `sys_sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sys_sessions` ( `uuid` varchar(36) NOT NULL, `user_id` int(11) NOT NULL, `permanent` tinyint(1) NOT NULL, `last_login` datetime NOT NULL, PRIMARY KEY (`uuid`), KEY `last_login` (`last_login`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `sysconfig` -- DROP TABLE IF EXISTS `sysconfig`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sysconfig` ( `name` varchar(60) NOT NULL, `value` text NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `transaction_test` -- DROP TABLE IF EXISTS `transaction_test`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `transaction_test` ( `id` int(10) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `user` -- DROP TABLE IF EXISTS `user`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(60) CHARACTER SET utf8mb3 COLLATE utf8mb3_polish_ci DEFAULT NULL, `password` varchar(512) DEFAULT NULL, `password_salt` varchar(10) NOT NULL DEFAULT '', `password_hashing_rounds` int(10) NOT NULL DEFAULT 1, `email` varchar(60) DEFAULT NULL, `role` set('ocTeamMember','advUser','newsPublisher','sysAdmin') NOT NULL COMMENT 'role of the user: ocTeamMember|advUser(confidential user-beta content)|newsPublisher|sysAdmins(tech. admin)', `latitude` double DEFAULT NULL, `longitude` double DEFAULT NULL, `last_modified` datetime DEFAULT NULL, `last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `last_login_mobile` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `is_active_flag` int(11) DEFAULT NULL, `new_pw_code` tinytext DEFAULT NULL COMMENT 'Code to change password', `new_pw_exp` datetime DEFAULT NULL COMMENT 'new_pw_code expiration date', `date_created` datetime DEFAULT NULL, `new_email_code` tinytext DEFAULT NULL COMMENT 'Code to change email', `new_email_exp` datetime DEFAULT NULL COMMENT 'new_email_code expiration date', `new_email_date` int(11) DEFAULT NULL, `new_email` varchar(60) DEFAULT NULL, `hidden_count` int(11) DEFAULT 0, `log_notes_count` int(11) DEFAULT 0, `founds_count` int(11) DEFAULT 0, `notfounds_count` int(11) DEFAULT 0, `uuid` varchar(36) DEFAULT NULL, `uuid_mobile` varchar(36) DEFAULT NULL, `permanent_login_flag` int(11) DEFAULT NULL, `watchmail_mode` int(11) NOT NULL DEFAULT 1 COMMENT '0=daily; 1=hourly; 2=weekly', `watchmail_hour` int(11) NOT NULL DEFAULT 0, `watchmail_nextmail` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `watchmail_day` int(11) NOT NULL DEFAULT 7 COMMENT '1=mon; 7=sun', `activation_code` varchar(13) NOT NULL, `statpic_logo` int(11) NOT NULL DEFAULT 0, `statpic_text` varchar(30) NOT NULL, `power_trail_email` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'sending notification emails from geoPaths module if value = 1', `notify_radius` int(11) NOT NULL DEFAULT 0, `notify_caches` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Notify user about new caches', `notify_logs` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Notify user about new logs', `guru` tinyint(1) NOT NULL DEFAULT 0, `node` tinyint(4) NOT NULL DEFAULT 0, `stat_ban` tinyint(1) NOT NULL DEFAULT 0, `description` varchar(4096) DEFAULT NULL, `rules_confirmed` int(1) NOT NULL DEFAULT 0, `ozi_filips` varchar(255) DEFAULT NULL COMMENT 'path to the op dir at mobile device', `verify_all` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`user_id`), UNIQUE KEY `email` (`email`), KEY `notify_radius` (`notify_radius`), KEY `username` (`username`), KEY `hidden_count` (`hidden_count`), KEY `founds_count` (`founds_count`), KEY `notfounds_count` (`notfounds_count`), KEY `uuid` (`uuid`), KEY `notify_caches` (`notify_caches`), KEY `notify_logs` (`notify_logs`), KEY `last_login` (`last_login`), KEY `is_active_flag` (`is_active_flag`), KEY `date_created` (`date_created`), KEY `role_index` (`role`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `user_finds` -- DROP TABLE IF EXISTS `user_finds`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user_finds` ( `date` date NOT NULL DEFAULT '0000-00-00', `user_id` int(11) NOT NULL DEFAULT 0, `number` int(11) DEFAULT NULL, PRIMARY KEY (`date`,`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `user_neighbourhoods` -- DROP TABLE IF EXISTS `user_neighbourhoods`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user_neighbourhoods` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `seq` int(11) NOT NULL COMMENT 'Number in sequence', `name` tinytext NOT NULL, `longitude` double NOT NULL, `latitude` double NOT NULL, `radius` int(11) NOT NULL, `notify` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user_id_2` (`user_id`,`seq`), KEY `seq` (`seq`), KEY `notify` (`notify`), CONSTRAINT `user_neighbourhoods_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Stores additional user MyNeighborhood areas'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `user_nick_history` -- DROP TABLE IF EXISTS `user_nick_history`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user_nick_history` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'PK', `user_id` int(11) NOT NULL COMMENT 'FK to user.user_id', `date_from` datetime NOT NULL COMMENT 'Start date of the nick value', `date_to` datetime DEFAULT NULL COMMENT 'End date of the nick value, NULL if current', `username` varchar(60) NOT NULL COMMENT 'The actual nick in a given period of time', `change_comment` text DEFAULT NULL COMMENT 'Change comment', `change_by_user_id` int(11) DEFAULT NULL COMMENT 'User who changed the nick, FT to user.user_id', PRIMARY KEY (`id`), KEY `user_nick_hist_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `user_preferences` -- DROP TABLE IF EXISTS `user_preferences`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user_preferences` ( `user_id` int(11) NOT NULL, `key` varchar(25) NOT NULL COMMENT 'key identifies set of user preferences ', `value` text DEFAULT NULL, PRIMARY KEY (`user_id`,`key`), KEY `user_id` (`user_id`), CONSTRAINT `user_preferences_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='minor user setting in context of UI'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `user_settings` -- DROP TABLE IF EXISTS `user_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `user_settings` ( `user_id` int(11) NOT NULL, `newcaches_no_limit` tinyint(4) NOT NULL COMMENT 'ignore finds limit for creating new cache. (User always may create new cache)', UNIQUE KEY `user_id` (`user_id`), CONSTRAINT `user_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `vote_choiceOptions` -- DROP TABLE IF EXISTS `vote_choiceOptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vote_choiceOptions` ( `optionId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique option id', `electionId` int(11) NOT NULL COMMENT 'id of election', `name` text NOT NULL COMMENT 'name of the option', `description` text DEFAULT NULL COMMENT 'description of the option', `link` text DEFAULT NULL COMMENT 'link added to option', `orderIdx` int(11) NOT NULL COMMENT 'order of the option on the list', PRIMARY KEY (`optionId`), KEY `FK_vote_choiceOptions_vote_elections` (`electionId`), CONSTRAINT `FK_vote_choiceOptions_vote_elections` FOREIGN KEY (`electionId`) REFERENCES `vote_elections` (`electionId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Options available in the voting'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `vote_elections` -- DROP TABLE IF EXISTS `vote_elections`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vote_elections` ( `electionId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id of the record', `name` text NOT NULL COMMENT 'election name', `startDate` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'beginning of election ', `endDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'end of election ', `voterCriteria` text NOT NULL COMMENT 'JSON with criteria for voters', `electionRules` text NOT NULL COMMENT 'JSON with election data', `description` text NOT NULL COMMENT 'description of election to display', PRIMARY KEY (`electionId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Voting data'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `vote_voters` -- DROP TABLE IF EXISTS `vote_voters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vote_voters` ( `userId` int(11) NOT NULL COMMENT 'id of the user (from user table)', `electionId` int(11) NOT NULL COMMENT 'id of election (from vote_elections)', `ip` varchar(15) NOT NULL COMMENT 'IP address of the user', `additionalData` text NOT NULL COMMENT 'Additional data for user verification', PRIMARY KEY (`userId`,`electionId`), KEY `FK_vote_voters_vote_elections` (`electionId`), CONSTRAINT `FK_vote_voters_user` FOREIGN KEY (`userId`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT `FK_vote_voters_vote_elections` FOREIGN KEY (`electionId`) REFERENCES `vote_elections` (`electionId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='User participation in votings'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `vote_votes` -- DROP TABLE IF EXISTS `vote_votes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `vote_votes` ( `voteId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique id of the vote', `electionId` int(11) NOT NULL COMMENT 'id of the election', `optionId` int(11) NOT NULL COMMENT 'option selected in voting ', `date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date of the vote', `hash` text DEFAULT NULL COMMENT 'optional hash to store additional data', PRIMARY KEY (`voteId`), KEY `FK_vote_votes_vote_elections` (`electionId`), KEY `FK_vote_votes_vote_choiceOptions` (`optionId`), CONSTRAINT `FK_vote_votes_vote_choiceOptions` FOREIGN KEY (`optionId`) REFERENCES `vote_choiceOptions` (`optionId`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_vote_votes_vote_elections` FOREIGN KEY (`electionId`) REFERENCES `vote_elections` (`electionId`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='Votes from elections'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `watches_waiting` -- DROP TABLE IF EXISTS `watches_waiting`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `watches_waiting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL DEFAULT 0, `object_id` int(11) NOT NULL DEFAULT 0, `object_type` int(11) NOT NULL DEFAULT 0, `date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `watchtext` mediumtext NOT NULL, `watchtype` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `object_id` (`object_id`), KEY `date_added` (`date_added`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `waypoint_type` -- DROP TABLE IF EXISTS `waypoint_type`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `waypoint_type` ( `id` int(11) NOT NULL AUTO_INCREMENT, `pl` varchar(60) NOT NULL, `en` varchar(60) NOT NULL, `nl` varchar(60) NOT NULL, `de` varchar(60) NOT NULL, `ro` varchar(60) NOT NULL, `fr` varchar(60) NOT NULL, `icon` varchar(60) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `waypoints` -- DROP TABLE IF EXISTS `waypoints`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `waypoints` ( `wp_id` int(11) NOT NULL AUTO_INCREMENT, `cache_id` int(11) NOT NULL DEFAULT 0, `longitude` double DEFAULT NULL, `latitude` double DEFAULT NULL, `type` tinyint(1) DEFAULT NULL, `status` tinyint(1) NOT NULL DEFAULT 1, `stage` tinyint(1) NOT NULL DEFAULT 0, `desc` varchar(600) DEFAULT NULL, `opensprawdzacz` binary(1) NOT NULL, PRIMARY KEY (`wp_id`,`cache_id`), KEY `cache_id` (`cache_id`), KEY `longitude` (`longitude`,`latitude`), KEY `latitude` (`latitude`), KEY `stage` (`stage`), KEY `status` (`status`), KEY `type` (`type`), KEY `opensprawdzacz` (`opensprawdzacz`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci COMMENT='cache waypoints'; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping routines for database 'ocpldb' -- /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; /*!50003 DROP PROCEDURE IF EXISTS `dec_logs_stats` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE DEFINER=`ocpl`@`localhost` PROCEDURE `dec_logs_stats`( IN `type` int(11), IN `user_id` int(11), IN `cache_id` int(11) ) BEGIN CASE `type` WHEN 1 THEN -- FOUND CALL dec_powertrail_progress(user_id, cache_id); ELSE CALL nop(); END CASE; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; /*!50003 DROP PROCEDURE IF EXISTS `dec_powertrail_progress` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE DEFINER=`ocpl`@`localhost` PROCEDURE `dec_powertrail_progress`( IN `p_user_id` int(11), IN `p_cache_id` int(11) ) BEGIN DECLARE p_pt_id int(11) DEFAULT NULL; -- find powertrail of this cache SELECT PowerTrailId INTO p_pt_id FROM powerTrail_caches WHERE cacheId = p_cache_id LIMIT 1; IF p_pt_id IS NOT NULL THEN UPDATE powertrail_progress SET founds = founds - 1 WHERE user_id = p_user_id AND pt_id = p_pt_id AND founds > 0; END IF; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; /*!50003 DROP PROCEDURE IF EXISTS `inc_logs_stats` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE DEFINER=`ocpl`@`localhost` PROCEDURE `inc_logs_stats`( IN `type` int(11), IN `user_id` int(11), IN `cache_id` int(11) ) BEGIN CASE `type` WHEN 1 THEN -- FOUND CALL inc_powertrail_progress(user_id, cache_id); ELSE CALL nop(); END CASE; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; /*!50003 DROP PROCEDURE IF EXISTS `inc_powertrail_progress` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE DEFINER=`ocpl`@`localhost` PROCEDURE `inc_powertrail_progress`( IN `user_id` int(11), IN `cache_id` int(11) ) BEGIN DECLARE pt_id int(11) DEFAULT NULL; -- find powertrail of this cache SELECT PowerTrailId INTO pt_id FROM powerTrail_caches WHERE cacheId = cache_id LIMIT 1; IF pt_id IS NOT NULL THEN INSERT INTO powertrail_progress (user_id, pt_id, founds) VALUES (user_id, pt_id, 1) ON DUPLICATE KEY UPDATE founds = founds + 1; END IF; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; /*!50003 DROP PROCEDURE IF EXISTS `nop` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb3 */ ; /*!50003 SET character_set_results = utf8mb3 */ ; /*!50003 SET collation_connection = utf8mb3_general_ci */ ; DELIMITER ;; CREATE DEFINER=`ocpl`@`localhost` PROCEDURE `nop`() BEGIN END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2024-04-25 4:01:21