2 --- This file does away with the bits of db_stats_agg.php that have to
3 --- do with file download stats.
7 --- No longer needed tables
9 DROP TABLE frs_dlstats_file_agg;
10 DROP TABLE frs_dlstats_grouptotal_agg;
11 DROP TABLE frs_dlstats_group_agg;
14 --- Get up-to-date info
16 DELETE FROM frs_dlstats_filetotal_agg;
17 INSERT INTO frs_dlstats_filetotal_agg
18 SELECT file_id, 0 as downloads
20 UPDATE frs_dlstats_filetotal_agg
21 SET downloads = (SELECT count(*)
22 FROM frs_dlstats_file WHERE frs_dlstats_file.file_id = frs_dlstats_filetotal_agg.file_id);
25 --- Whenever a new file is inserted into the frs_file table, add an entry
26 --- to the _agg tables too.
29 CREATE FUNCTION "frs_dlstats_filetotal_insert_agg" () RETURNS OPAQUE AS '
31 INSERT INTO frs_dlstats_filetotal_agg (file_id, downloads) VALUES (NEW.file_id, 0);
36 CREATE TRIGGER "frs_file_insert_trig" AFTER INSERT ON "frs_file" FOR EACH ROW EXECUTE PROCEDURE frs_dlstats_filetotal_insert_agg();
39 --- Whenever a file gets downloaded, increment stats
41 CREATE RULE frs_dlstats_file_rule AS ON INSERT TO frs_dlstats_file DO
42 UPDATE frs_dlstats_filetotal_agg
43 SET downloads = (frs_dlstats_filetotal_agg.downloads + 1)
44 WHERE (frs_dlstats_filetotal_agg.file_id = new.file_id);
47 --- Create a view to get file downloads by month
49 CREATE VIEW frs_dlstats_file_agg_vw AS
50 SELECT month, day, file_id, count(*) AS downloads
52 GROUP BY month, day, file_id;
55 --- Create a view to get group total downloads
57 CREATE VIEW frs_dlstats_grouptotal_vw AS
58 SELECT frs_package.group_id, sum(frs_dlstats_filetotal_agg.downloads) AS downloads
59 FROM frs_package,frs_release,frs_file,frs_dlstats_filetotal_agg
60 WHERE frs_package.package_id=frs_release.package_id
61 AND frs_release.release_id=frs_file.release_id
62 AND frs_file.file_id=frs_dlstats_filetotal_agg.file_id
63 GROUP BY frs_package.group_id;
66 --- Create a view to get group aggregate stats by month
68 CREATE VIEW frs_dlstats_group_vw AS
69 SELECT frs_package.group_id, fdfa.month, fdfa.day, sum(fdfa.downloads) AS downloads
70 FROM frs_package, frs_release, frs_file, frs_dlstats_file_agg_vw fdfa
71 WHERE frs_package.package_id=frs_release.package_id
72 AND frs_release.release_id=frs_file.release_id
73 AND frs_file.file_id=fdfa.file_id
74 GROUP BY frs_package.group_id, fdfa.month, fdfa.day;
77 --- Add Latin as a supported language
79 INSERT INTO supported_languages (name, filename, classname, language_code) values ('Latin', 'Latin.class', 'Latin', 'la');