/usr/share/gforge/db/20030112.sql is in gforge-db-postgresql 5.3.2+20141104-3+deb8u3.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | ---
--- This file does away with the bits of db_stats_agg.php that have to
--- do with file download stats.
---
---
--- No longer needed tables
---
DROP TABLE frs_dlstats_file_agg;
DROP TABLE frs_dlstats_grouptotal_agg;
DROP TABLE frs_dlstats_group_agg;
---
--- Get up-to-date info
---
DELETE FROM frs_dlstats_filetotal_agg;
INSERT INTO frs_dlstats_filetotal_agg
SELECT file_id, 0 as downloads
FROM frs_file;
UPDATE frs_dlstats_filetotal_agg
SET downloads = (SELECT count(*)
FROM frs_dlstats_file WHERE frs_dlstats_file.file_id = frs_dlstats_filetotal_agg.file_id);
---
--- Whenever a new file is inserted into the frs_file table, add an entry
--- to the _agg tables too.
---
CREATE FUNCTION "frs_dlstats_filetotal_insert_agg" () RETURNS OPAQUE AS '
BEGIN
INSERT INTO frs_dlstats_filetotal_agg (file_id, downloads) VALUES (NEW.file_id, 0);
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER "frs_file_insert_trig" AFTER INSERT ON "frs_file" FOR EACH ROW EXECUTE PROCEDURE frs_dlstats_filetotal_insert_agg();
---
--- Whenever a file gets downloaded, increment stats
---
CREATE RULE frs_dlstats_file_rule AS ON INSERT TO frs_dlstats_file DO
UPDATE frs_dlstats_filetotal_agg
SET downloads = (frs_dlstats_filetotal_agg.downloads + 1)
WHERE (frs_dlstats_filetotal_agg.file_id = new.file_id);
---
--- Create a view to get file downloads by month
---
CREATE VIEW frs_dlstats_file_agg_vw AS
SELECT month, day, file_id, count(*) AS downloads
FROM frs_dlstats_file
GROUP BY month, day, file_id;
---
--- Create a view to get group total downloads
---
CREATE VIEW frs_dlstats_grouptotal_vw AS
SELECT frs_package.group_id, sum(frs_dlstats_filetotal_agg.downloads) AS downloads
FROM frs_package,frs_release,frs_file,frs_dlstats_filetotal_agg
WHERE frs_package.package_id=frs_release.package_id
AND frs_release.release_id=frs_file.release_id
AND frs_file.file_id=frs_dlstats_filetotal_agg.file_id
GROUP BY frs_package.group_id;
---
--- Create a view to get group aggregate stats by month
---
CREATE VIEW frs_dlstats_group_vw AS
SELECT frs_package.group_id, fdfa.month, fdfa.day, sum(fdfa.downloads) AS downloads
FROM frs_package, frs_release, frs_file, frs_dlstats_file_agg_vw fdfa
WHERE frs_package.package_id=frs_release.package_id
AND frs_release.release_id=frs_file.release_id
AND frs_file.file_id=fdfa.file_id
GROUP BY frs_package.group_id, fdfa.month, fdfa.day;
---
--- Add Latin as a supported language
---
INSERT INTO supported_languages (name, filename, classname, language_code) values ('Latin', 'Latin.class', 'Latin', 'la');
|