· 8 years ago · Jul 24, 2018, 04:12 PM
1
2-- drop sequences
3--
4drop sequence if exists users_seq;
5drop sequence if exists todos_seq;
6
7--
8-- create sequences
9--
10create sequence users_seq minvalue 0 start with 0 increment by 1 no cycle;
11create sequence todos_seq minvalue 0 start with 0 increment by 1 no cycle;
12
13--
14-- drop tables
15--
16drop table if exists users cascade;
17drop table if exists todos cascade;
18drop table if exists customer_contacts cascade;
19drop table if exists customers cascade;
20drop table if exists license_astaro cascade;
21drop table if exists license_trendmicro cascade;
22drop table if exists licenses cascade;
23
24--
25-- drop functions
26--
27drop function if exists hash_password();
28drop function if exists set_user_id();
29drop function if exists set_todo_id();
30drop function if exists is_valid_date(char);
31drop function if exists todo_results(target_user integer);
32drop function if exists reminder_count(integer);
33
34--
35-- drop types
36--
37drop type if exists todo_type;
38
39--
40-- drop language
41--
42drop language if exists plpgsql;
43
44--
45-- create language
46--
47create language plpgsql;
48
49--
50-- create tables
51--
52
53create table users (
54 id integer primary key,
55 username text unique not null,
56 password text not null,
57 status text not null check (status in('active','inactive')),
58 rank text not null check (rank in('admin','user'))
59);
60
61create table todos (
62 id integer primary key,
63 user_id integer references users(id),
64 priority smallint not null default 5,
65 category text,
66 title text not null,
67 body text not null,
68 created_on timestamp not null default CURRENT_TIMESTAMP,
69 created_by integer references users(id),
70 finish_on timestamp not null default CURRENT_TIMESTAMP,
71 repeat text not null default 'none' check (repeat in ('none','yearly','monthly','weekly','daily')),
72 -- means: remind me one month before finish_on, then one week (10080 minutes before finish_on), then 6 days before finish_on (8640 minutes) etc...
73 reminder interval[] default '{ 44640 minutes, 10080 minutes, 8640 minutes, 7200 minutes, 5760 minutes, 4320 minutes, 2880 minutes, 1400 minutes }',
74 done boolean not null default false
75);
76
77--
78-- functions
79--
80
81-- function:hash password
82create function hash_password() returns trigger as $hash_password$
83begin
84 NEW.password = md5(NEW.password);
85 return NEW;
86end;
87$hash_password$ language 'plpgsql';
88
89-- function:set user id
90create function set_user_id() returns trigger as $set_user_id$
91begin
92 NEW.id = nextval('users_seq');
93 return NEW;
94end;
95$set_user_id$ language 'plpgsql';
96
97-- function:set todo id
98create function set_todo_id() returns trigger as $set_todo_id$
99begin
100 NEW.id = nextval('todos_seq');
101 return NEW;
102end;
103$set_todo_id$ language 'plpgsql';
104
105-- function:is_valid_date
106create function is_valid_date(CHAR) returns bool as $is_valid_date$
107declare
108 result bool;
109 valid_format text := 'DD-MM-YYYY';
110begin
111 select to_char(to_date($1,valid_format),valid_format) = $1
112 into result;
113 return result;
114end;
115$is_valid_date$ language 'plpgsql';
116
117-- function:reminder_count - counts the reminder times of a given todo (via id)
118create function reminder_count(integer) returns bigint as $reminder_count$
119declare
120 reminder_count integer := 0;
121begin
122 select count(*) from (select regexp_split_to_table(array_to_string(reminder, '-'), '-') from todos where id=$1) as foo
123 into reminder_count;
124 return reminder_count;
125end;
126$reminder_count$ language 'plpgsql';
127
128--
129-- triggers
130--
131create trigger hash_password before insert or update on users
132 for each row execute procedure hash_password();
133
134create trigger set_user_id before insert on users
135 for each row execute procedure set_user_id();
136
137create trigger set_todo_id before insert on todos
138 for each row execute procedure set_todo_id();
139
140--
141-- DATA
142--
143insert into users (id, username, password, status, rank)
144 values (nextval('users_seq'), 'admin', 'password', 'active', 'admin');
145
146
147--
148-- license tables
149--
150create table customer_contacts (
151 id integer primary key,
152 first_name text not null,
153 last_name text not null,
154 phone text not null,
155 mobile text not null,
156 email text not null
157);
158
159create table customers (
160 id integer primary key,
161 company text not null,
162 customer_contacts_id integer references customer_contacts(id)
163);
164
165create table license_astaro (
166 id integer primary key,
167 "version" text not null default 'V7',
168 ip inet not null default '127.0.0.1',
169 hostname text,
170 websecurity text array[2] check ( (websecurity[1] in('yes','no')) and (is_valid_date(websecurity[2])) ),
171 mailsecurity text array[2] check ( (mailsecurity[1] in ('yes', 'no')) and (is_valid_date(mailsecurity[2])) ),
172 gold text array[2] check ( (gold[1] in ('yes', 'no')) and (is_valid_date(gold[2])) ),
173 platinum text array[2] check ( (platinum[1] in ('yes', 'no')) and (is_valid_date(platinum[2])) )
174);
175
176create table license_trendmicro (
177 id integer primary key,
178 product text not null,
179 "version" text not null
180);
181
182create table licenses (
183 id integer primary key,
184 "type" text not null check ("type" in ('astaro','trendmicro')),
185 license_id integer not null,
186 customer_id integer references customers(id),
187 title text not null,
188 expires_on timestamp not null,
189 foreign key (license_id) references license_astaro(id),
190 foreign key (license_id) references license_trendmicro(id)
191);
192
193create type todo_type as (
194 id integer,
195 user_id integer,
196 title text,
197 body text,
198 created_by integer,
199 created_on timestamp,
200 finish_on timestamp,
201 reminder boolean,
202 reminder_used interval,
203 done boolean
204);
205
206create function todo_results(target_user integer)returns setof todo_type as $$
207declare
208 old_r record;
209 new_r todo_type;
210 reminder_r record;
211 reminder_length integer := 0;
212 run integer := 0;
213begin
214 -- first get all undone todos for "today"
215 for old_r in
216 select id, user_id, title, body, created_by, created_on, finish_on, done, reminder from todos
217 where extract(year from finish_on)=extract(year from CURRENT_TIMESTAMP)
218 and extract(month from finish_on)=extract(month from CURRENT_TIMESTAMP)
219 and extract(day from finish_on)=extract(day from CURRENT_TIMESTAMP)
220 and user_id = target_user
221 and done = false
222 loop
223 new_r.id := old_r.id;
224 new_r.user_id := old_r.user_id;
225 new_r.title := old_r.title;
226 new_r.body := old_r.body;
227 new_r.created_by := old_r.created_by;
228 new_r.created_on := old_r.created_on;
229 new_r.finish_on := old_r.finish_on;
230 new_r.done := old_r.done;
231 new_r.reminder := false;
232 new_r.reminder_used := '1 day';
233 return next new_r;
234 end loop;
235
236 -- second get all todos with "reminders" and return them if one reminder applies
237 for old_r in
238 select id, user_id, title, body, created_by, created_on, finish_on, done, reminder from todos
239 where user_id = target_user
240 and reminder_count(id) > 0
241 loop
242 while run <= reminder_count(old_r.id) loop
243 run := run + 1;
244 for reminder_r in
245 select id, user_id, title, body, created_by, created_on, finish_on, done from todos
246 where id = old_r.id and done = false
247 and (age(old_r.finish_on, current_timestamp) < old_r.reminder[run])
248 and ((age(old_r.finish_on, current_timestamp) + interval '1 day') > old_r.reminder[run])
249 or (age(old_r.finish_on, current_timestamp) = old_r.reminder[run])
250 loop
251 new_r.id := old_r.id;
252 new_r.user_id := old_r.user_id;
253 new_r.title := old_r.title;
254 new_r.body := old_r.body;
255 new_r.created_by := old_r.created_by;
256 new_r.created_on := old_r.created_on;
257 new_r.finish_on := old_r.finish_on;
258 new_r.done := old_r.done;
259 new_r.reminder := true;
260 new_r.reminder_used := old_r.reminder[run];
261 return next new_r;
262 end loop;
263 end loop;
264 run := 0;
265 end loop;
266
267 return;
268end;
269$$ language 'plpgsql';