File indexing completed on 2024-04-14 05:53:07
0001 USE `pling`; 0002 0003 CREATE TABLE IF NOT EXISTS `stat_page_impression` 0004 ( 0005 `object_id` int(11) NOT NULL, 0006 `object_type` int(11) NOT NULL, 0007 `seen_at` int(11) NOT NULL, 0008 `ip_inet` varbinary(16) NOT NULL, 0009 `member_id_viewer` int(11) DEFAULT NULL, 0010 `ipv6` varchar(50) DEFAULT NULL, 0011 `ipv4` varchar(50) DEFAULT NULL, 0012 `fingerprint` varchar(50) DEFAULT NULL, 0013 `user_agent` varchar(255) DEFAULT NULL, 0014 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 0015 PRIMARY KEY (`object_id`, `object_type`, `seen_at`, `ip_inet`) 0016 ) ENGINE = InnoDB 0017 DEFAULT CHARSET = `latin1`; 0018 0019 0020 0021 # #insert/update page views into table stat_page_views_mv 0022 # USE `pling`; 0023 # 0024 # #Update mechanism 0025 # START TRANSACTION; 0026 # 0027 # DROP TABLE IF EXISTS `stat_page_views_mv_new`; 0028 # CREATE TABLE `stat_page_views_mv_new` LIKE `stat_page_views_mv`; 0029 # 0030 # INSERT INTO `stat_page_views_mv_new` 0031 # SELECT `stat_page_views`.`project_id` AS `project_id`, 0032 # COUNT(1) AS `count_views`, 0033 # COUNT(DISTINCT `stat_page_views`.`ip`) AS `count_visitor`, 0034 # MAX(`stat_page_views`.`created_at`) AS `last_view` 0035 # FROM `stat_page_views` 0036 # WHERE `stat_page_views`.`created_at` >= CONCAT(CURDATE(), ' 00:00:00') 0037 # GROUP BY `stat_page_views`.`project_id`; 0038 # 0039 # RENAME TABLE `stat_page_views_mv` TO `stat_page_views_mv_old`; 0040 # RENAME TABLE `stat_page_views_mv_new` TO `stat_page_views_mv`; 0041 # DROP TABLE IF EXISTS `stat_page_views_mv_old`; 0042 # 0043 # COMMIT; 0044 #