File indexing completed on 2024-04-21 06:00:37
0001 DROP TABLE IF EXISTS ppload.stat_ppload_files_downloaded_unique; 0002 CREATE TABLE ppload.stat_ppload_files_downloaded_unique like ppload.stat_ppload_files_downloaded; 0003 0004 DELIMITER $$ 0005 drop PROCEDURE generate_stat_files_downloaded; 0006 CREATE PROCEDURE `generate_stat_files_downloaded`() 0007 BEGIN 0008 0009 DROP TABLE IF EXISTS ppload.tmp_stat_ppload_files_downloaded; 0010 0011 CREATE TABLE ppload.tmp_stat_ppload_files_downloaded 0012 (INDEX `idx_coll` (`collection_id`),INDEX `idx_file` (`file_id`)) 0013 ENGINE MyISAM 0014 AS 0015 SELECT f.owner_id, f.collection_id, f.file_id, COUNT(1) AS count_dl FROM ppload.ppload_files_downloaded f 0016 WHERE f.downloaded_timestamp < DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') 0017 GROUP BY f.collection_id, f.file_id 0018 ; 0019 RENAME TABLE ppload.stat_ppload_files_downloaded TO ppload.old_stat_ppload_files_downloaded, ppload.tmp_stat_ppload_files_downloaded TO ppload.stat_ppload_files_downloaded; 0020 DROP TABLE IF EXISTS ppload.old_stat_ppload_files_downloaded; 0021 0022 DROP TABLE IF EXISTS ppload.tmp_stat_ppload_files_downloaded_unique; 0023 0024 CREATE TABLE ppload.tmp_stat_ppload_files_downloaded_unique 0025 (INDEX `idx_coll` (`collection_id`),INDEX `idx_file` (`file_id`)) 0026 ENGINE MyISAM 0027 AS 0028 SELECT f.owner_id, f.collection_id, f.file_id, COUNT(1) AS count_dl FROM ppload.ppload_files_downloaded_unique f 0029 WHERE f.downloaded_timestamp > '2019-06-01 00:00:00' AND f.downloaded_timestamp < DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') 0030 GROUP BY f.collection_id, f.file_id 0031 ; 0032 RENAME TABLE ppload.stat_ppload_files_downloaded_unique TO ppload.old_stat_ppload_files_downloaded_unique, ppload.tmp_stat_ppload_files_downloaded_unique TO ppload.stat_ppload_files_downloaded_unique; 0033 DROP TABLE IF EXISTS ppload.old_stat_ppload_files_downloaded_unique; 0034 0035 END$$ 0036 DELIMITER ;