1 DROP SCHEMA reporter CASCADE;
5 CREATE SCHEMA reporter;
7 CREATE TABLE reporter.template_folder (
9 parent INT REFERENCES reporter.template_folder (id) DEFERRABLE INITIALLY DEFERRED,
10 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
11 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
13 shared BOOL NOT NULL DEFAULT FALSE,
14 share_with INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
16 CREATE INDEX rpt_tmpl_fldr_owner_idx ON reporter.template_folder (owner);
18 CREATE TABLE reporter.report_folder (
19 id SERIAL PRIMARY KEY,
20 parent INT REFERENCES reporter.report_folder (id) DEFERRABLE INITIALLY DEFERRED,
21 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
22 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
24 shared BOOL NOT NULL DEFAULT FALSE,
25 share_with INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
27 CREATE INDEX rpt_rpt_fldr_owner_idx ON reporter.report_folder (owner);
29 CREATE TABLE reporter.output_folder (
30 id SERIAL PRIMARY KEY,
31 parent INT REFERENCES reporter.output_folder (id) DEFERRABLE INITIALLY DEFERRED,
32 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
33 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
35 shared BOOL NOT NULL DEFAULT FALSE,
36 share_with INT REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
38 CREATE INDEX rpt_output_fldr_owner_idx ON reporter.output_folder (owner);
41 CREATE TABLE reporter.template (
42 id SERIAL PRIMARY KEY,
43 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
44 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
46 description TEXT NOT NULL,
48 folder INT NOT NULL REFERENCES reporter.template_folder (id)
50 CREATE INDEX rpt_tmpl_owner_idx ON reporter.template (owner);
51 CREATE INDEX rpt_tmpl_fldr_idx ON reporter.template (folder);
53 CREATE TABLE reporter.report (
54 id SERIAL PRIMARY KEY,
55 owner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
56 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
57 template INT NOT NULL REFERENCES reporter.template (id) DEFERRABLE INITIALLY DEFERRED,
59 folder INT NOT NULL REFERENCES reporter.report_folder (id),
60 recur BOOL NOT NULL DEFAULT FALSE,
63 CREATE INDEX rpt_rpt_owner_idx ON reporter.report (owner);
64 CREATE INDEX rpt_rpt_fldr_idx ON reporter.report (folder);
66 CREATE TABLE reporter.schedule (
67 id SERIAL PRIMARY KEY,
68 report INT NOT NULL REFERENCES reporter.report (id) DEFERRABLE INITIALLY DEFERRED,
69 folder INT NOT NULL REFERENCES reporter.output_folder (id),
70 runner INT NOT NULL REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
71 run_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
72 start_time TIMESTAMP WITH TIME ZONE,
73 complete_time TIMESTAMP WITH TIME ZONE,
75 excel_format BOOL NOT NULL DEFAULT TRUE,
76 html_format BOOL NOT NULL DEFAULT TRUE,
77 csv_format BOOL NOT NULL DEFAULT TRUE,
81 CREATE INDEX rpt_sched_runner_idx ON reporter.schedule (runner);
82 CREATE INDEX rpt_sched_folder_idx ON reporter.schedule (folder);