]> git.stg.codes - stg.git/blob - stargazer/inst/var/00-base-00.postgresql.sql
6b48d75cef819d27ba1a55a5d7ac3934ba54c1ac
[stg.git] / stargazer / inst / var / 00-base-00.postgresql.sql
1 /*
2  *    This program is free software; you can redistribute it and/or modify
3  *    it under the terms of the GNU General Public License as published by
4  *    the Free Software Foundation; either version 2 of the License, or
5  *    (at your option) any later version.
6  *
7  *    This program is distributed in the hope that it will be useful,
8  *    but WITHOUT ANY WARRANTY; without even the implied warranty of
9  *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
10  *    GNU General Public License for more details.
11  *
12  *    You should have received a copy of the GNU General Public License
13  *    along with this program; if not, write to the Free Software
14  *    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
15  */
16
17 /*
18  *****************************************************************************
19  *
20  * Скрипт генерации структуры базы для хранения данных Stargazer-a
21  *
22  * Примечание.
23  *      * dm_permission_flag. Представляет собой битовую маску - rw.
24  *          r - чтение, w - изменение параметра.
25  *          0 - дествие запрещено, 1 - действие разрешено
26  *
27  *      * dm_traff_type. Число определяющее тип подсчета трафика:
28  *          0 - up - считается по upload
29  *          1 - down - считается по download
30  *          2 - max - считается по максимальному среди upload/download
31  *          3 - up+down - считается по сумме upload и download
32  *
33  *      * dm_session_event_type. Указывает тип записи в логе о сессии.
34  *        'c' - connect, 'd' - disconnect.
35  *
36  *****************************************************************************
37  */
38
39 /*
40  *  $Revision: 1.12 $
41  *  $Date: 2009/08/20 14:58:43 $
42  */
43
44
45 /*
46  *****************************************************************************
47  * -= Создание типов и доменов =-
48  *****************************************************************************
49  */
50
51 CREATE DOMAIN dm_name AS VARCHAR(32) NOT NULL;
52 CREATE DOMAIN dm_password AS VARCHAR(64) NOT NULL;
53 CREATE DOMAIN dm_permission_flag AS SMALLINT NOT NULL
54     CHECK ( value BETWEEN 0 AND 3 );
55 CREATE DOMAIN dm_money AS NUMERIC(12, 4) NOT NULL DEFAULT 0;
56 CREATE DOMAIN dm_traff_type AS SMALLINT NOT NULL
57     CHECK ( value BETWEEN 0 AND 3 );
58 CREATE DOMAIN dm_day AS SMALLINT NOT NULL
59     CHECK ( value BETWEEN 0 AND 31 )
60     DEFAULT 0;
61 CREATE DOMAIN dm_session_event_type AS CHAR(1) NOT NULL
62     CHECK ( value = 'c' OR value = 'd' );
63
64 /*
65  *****************************************************************************
66  * -= Создание таблиц =-
67  *****************************************************************************
68  */
69
70 CREATE TABLE tb_info
71 (
72     version INTEGER NOT NULL
73 );
74
75 CREATE TABLE tb_admins
76 (
77     pk_admin SERIAL PRIMARY KEY,
78     login dm_name UNIQUE,
79     passwd dm_password NOT NULL,
80     chg_conf dm_permission_flag,
81     chg_password dm_permission_flag,
82     chg_stat dm_permission_flag,
83     chg_cash dm_permission_flag,
84     usr_add_del dm_permission_flag,
85     chg_tariff dm_permission_flag,
86     chg_admin dm_permission_flag,
87     chg_service dm_permission_flag,
88     chg_corporation dm_permission_flag
89 );
90
91 CREATE TABLE tb_tariffs
92 (
93     pk_tariff SERIAL PRIMARY KEY,
94     name dm_name UNIQUE,
95     fee dm_money,
96     free dm_money,
97     passive_cost dm_money,
98     traff_type dm_traff_type
99 );
100
101 CREATE TABLE tb_tariffs_params
102 (
103     pk_tariff_param SERIAL PRIMARY KEY,
104     fk_tariff INTEGER NOT NULL,
105     dir_num SMALLINT NOT NULL,
106     price_day_a dm_money,
107     price_day_b dm_money,
108     price_night_a dm_money,
109     price_night_b dm_money,
110     threshold INTEGER NOT NULL,
111     time_day_begins TIME NOT NULL,
112     time_day_ends TIME NOT NULL,
113
114     FOREIGN KEY (fk_tariff)
115         REFERENCES tb_tariffs (pk_tariff)
116         ON DELETE CASCADE
117 );
118
119 CREATE TABLE tb_corporations
120 (
121     pk_corporation SERIAL PRIMARY KEY,
122     name dm_name UNIQUE,
123     cash dm_money
124 );
125
126 CREATE TABLE tb_users
127 (
128     pk_user SERIAL PRIMARY KEY,
129     fk_tariff INTEGER,
130     fk_tariff_change INTEGER,
131     fk_corporation INTEGER,
132     address VARCHAR(256) NOT NULL,
133     always_online BOOLEAN NOT NULL,
134     credit dm_money,
135     credit_expire TIMESTAMP NOT NULL,
136     disabled BOOLEAN NOT NULL,
137     disabled_detail_stat BOOLEAN NOT NULL,
138     email VARCHAR(256) NOT NULL,
139     grp dm_name,
140     note TEXT NOT NULL,
141     passive BOOLEAN NOT NULL,
142     passwd dm_password,
143     phone VARCHAR(256) NOT NULL,
144     name dm_name UNIQUE,
145     real_name VARCHAR(256) NOT NULL,
146     cash dm_money,
147     free_mb dm_money,
148     last_activity_time TIMESTAMP NOT NULL,
149     last_cash_add dm_money,
150     last_cash_add_time TIMESTAMP NOT NULL,
151     passive_time INTEGER NOT NULL,
152
153     FOREIGN KEY (fk_tariff)
154         REFERENCES tb_tariffs (pk_tariff)
155         ON DELETE CASCADE,
156     FOREIGN KEY (fk_tariff_change)
157         REFERENCES tb_tariffs (pk_tariff)
158         ON DELETE CASCADE,
159     FOREIGN KEY (fk_corporation)
160         REFERENCES tb_corporations (pk_corporation)
161         ON DELETE CASCADE
162 );
163
164 CREATE TABLE tb_detail_stats
165 (
166     pk_detail_stat BIGSERIAL PRIMARY KEY,
167     fk_user INTEGER NOT NULL,
168     dir_num SMALLINT NOT NULL,
169     ip INET NOT NULL,
170     download BIGINT NOT NULL,
171     upload BIGINT NOT NULL,
172     cost dm_money,
173     from_time TIMESTAMP NOT NULL,
174     till_time TIMESTAMP NOT NULL,
175
176     FOREIGN KEY (fk_user)
177         REFERENCES tb_users (pk_user)
178         ON DELETE CASCADE
179 );
180
181 CREATE TABLE tb_services
182 (
183     pk_service SERIAL PRIMARY KEY,
184     name dm_name UNIQUE,
185     comment TEXT NOT NULL,
186     cost dm_money,
187     pay_day dm_day
188 );
189
190 CREATE TABLE tb_users_services
191 (
192     pk_user_service SERIAL PRIMARY KEY,
193     fk_user INTEGER NOT NULL,
194     fk_service INTEGER NOT NULL,
195
196     FOREIGN KEY (fk_user)
197         REFERENCES tb_users (pk_user)
198         ON DELETE CASCADE,
199     FOREIGN KEY (fk_service)
200         REFERENCES tb_services (pk_service)
201 );
202
203 CREATE TABLE tb_messages
204 (
205     pk_message SERIAL PRIMARY KEY,
206     fk_user INTEGER NOT NULL,
207     ver SMALLINT NOT NULL,
208     msg_type SMALLINT NOT NULL,
209     last_send_time TIMESTAMP NOT NULL,
210     creation_time TIMESTAMP NOT NULL,
211     show_time INTEGER NOT NULL,
212     repeat SMALLINT NOT NULL,
213     repeat_period INTEGER NOT NULL,
214     msg_text TEXT NOT NULL,
215
216     FOREIGN KEY (fk_user)
217         REFERENCES tb_users (pk_user)
218         ON DELETE CASCADE
219 );
220
221 CREATE TABLE tb_stats_traffic
222 (
223     pk_stat_traffic BIGSERIAL PRIMARY KEY,
224     fk_user INTEGER NOT NULL,
225     stats_date DATE NOT NULL,
226     dir_num SMALLINT NOT NULL,
227     download BIGINT NOT NULL,
228     upload BIGINT NOT NULL,
229
230     FOREIGN KEY (fk_user)
231         REFERENCES tb_users (pk_user)
232         ON DELETE CASCADE,
233     UNIQUE (fk_user, stats_date, dir_num)
234 );
235
236 CREATE TABLE tb_users_data
237 (
238     pk_user_data SERIAL PRIMARY KEY,
239     fk_user INTEGER NOT NULL,
240     num SMALLINT NOT NULL,
241     data VARCHAR(256) NOT NULL,
242
243     FOREIGN KEY (fk_user)
244         REFERENCES tb_users (pk_user)
245         ON DELETE CASCADE
246 );
247
248 CREATE TABLE tb_allowed_ip
249 (
250     pk_allowed_ip SERIAL PRIMARY KEY,
251     fk_user INTEGER NOT NULL,
252     ip INET NOT NULL,
253
254     FOREIGN KEY (fk_user)
255         REFERENCES tb_users (pk_user)
256         ON DELETE CASCADE
257 );
258
259 CREATE TABLE tb_sessions_log
260 (
261     pk_session_log SERIAL PRIMARY KEY,
262     fk_user INTEGER NOT NULL,
263     event_time TIMESTAMP NOT NULL,
264     event_type dm_session_event_type,
265     ip INET NOT NULL,
266     cash dm_money,
267
268     FOREIGN KEY (fk_user)
269         REFERENCES tb_users (pk_user)
270         ON DELETE CASCADE
271 );
272
273 CREATE TABLE tb_sessions_data
274 (
275     pk_session_data SERIAL PRIMARY KEY,
276     fk_session_log INTEGER NOT NULL,
277     dir_num SMALLINT NOT NULL,
278     session_upload BIGINT NOT NULL,
279     session_download BIGINT NOT NULL,
280     month_upload BIGINT NOT NULL,
281     month_download BIGINT NOT NULL,
282
283     FOREIGN KEY (fk_session_log)
284         REFERENCES tb_sessions_log (pk_session_log)
285         ON DELETE CASCADE
286 );
287
288 CREATE TABLE tb_parameters
289 (
290     pk_parameter SERIAL PRIMARY KEY,
291     name dm_name UNIQUE
292 );
293
294 CREATE TABLE tb_params_log
295 (
296     pk_param_log SERIAL PRIMARY KEY,
297     fk_user INTEGER NOT NULL,
298     fk_parameter INTEGER NOT NULL,
299     fk_admin INTEGER NOT NULL,
300     ip INET NOT NULL,
301     event_time TIMESTAMP NOT NULL,
302     from_val VARCHAR(256),
303     to_val VARCHAR(256),
304     comment TEXT,
305
306     FOREIGN KEY (fk_user)
307         REFERENCES tb_users (pk_user)
308         ON DELETE CASCADE,
309     FOREIGN KEY (fk_parameter)
310         REFERENCES tb_parameters (pk_parameter),
311     FOREIGN KEY (fk_admin)
312         REFERENCES tb_admins (pk_admin)
313         ON DELETE CASCADE
314 );
315
316 /*
317  *****************************************************************************
318  * -= Создание хранимых процедур =-
319  *****************************************************************************
320  */
321
322 CREATE FUNCTION sp_add_message(_login dm_name,
323                                _ver SMALLINT,
324                                _msg_type SMALLINT,
325                                _last_send_time TIMESTAMP,
326                                _creation_time TIMESTAMP,
327                                _show_time INTEGER,
328                                _repeat SMALLINT,
329                                _repeat_period INTEGER,
330                                _msg_text TEXT)
331 RETURNS INTEGER
332 AS $$
333 DECLARE
334     _pk_user INTEGER;
335 BEGIN
336     SELECT pk_user INTO _pk_user
337         FROM tb_users
338         WHERE name = _login;
339     IF _pk_user IS NULL THEN
340         RAISE EXCEPTION 'User % not found', _login;
341         RETURN -1;
342     END IF;
343     INSERT INTO tb_messages
344         (fk_user,
345          ver,
346          msg_type,
347          last_send_time,
348          creation_time,
349          show_time,
350          repeat,
351          repeat_period,
352          msg_text)
353     VALUES
354         (_pk_user,
355          _ver,
356          _msg_type,
357          _last_send_time,
358          _creation_time,
359          _show_time,
360          _repeat,
361          _repeat_period,
362          _msg_text);
363     RETURN CURRVAL('tb_messages_pk_message_seq');
364 END;
365 $$ LANGUAGE plpgsql;
366
367 CREATE FUNCTION sp_add_tariff(_name dm_name, _dirs INTEGER)
368 RETURNS INTEGER
369 AS $$
370 DECLARE
371     pk_tariff INTEGER;
372 BEGIN
373     INSERT INTO tb_tariffs
374         (name,
375          fee,
376          free,
377          passive_cost,
378          traff_type)
379     VALUES
380         (_name,
381          0, 0, 0, 0);
382     SELECT CURRVAL('tb_tariffs_pk_tariff_seq') INTO pk_tariff;
383     FOR i IN 1.._dirs LOOP
384         INSERT INTO tb_tariffs_params
385             (fk_tariff,
386              dir_num,
387              price_day_a,
388              price_day_b,
389              price_night_a,
390              price_night_b,
391              threshold,
392              time_day_begins,
393              time_day_ends)
394         VALUES
395             (pk_tariff,
396              i - 1,
397              0, 0, 0, 0, 0,
398              CAST('1970-01-01 00:00:00+00' AS TIMESTAMP),
399              CAST('1970-01-01 00:00:00+00' AS TIMESTAMP));
400     END LOOP;
401     RETURN pk_tariff;
402 END;
403 $$ LANGUAGE plpgsql;
404
405 CREATE FUNCTION sp_add_user(_name dm_name)
406 RETURNS INTEGER
407 AS $$
408 DECLARE
409     pk_user INTEGER;
410 BEGIN
411     INSERT INTO tb_users
412         (fk_tariff,
413          fk_tariff_change,
414          fk_corporation,
415          address,
416          always_online,
417          credit,
418          credit_expire,
419          disabled,
420          disabled_detail_stat,
421          email,
422          grp,
423          note,
424          passive,
425          passwd,
426          phone,
427          name,
428          real_name,
429          cash,
430          free_mb,
431          last_activity_time,
432          last_cash_add,
433          last_cash_add_time,
434          passive_time)
435     VALUES
436         (NULL, NULL, NULL, '', FALSE, 0, CAST('now' AS TIMESTAMP),
437          FALSE, FALSE, '', '', '', FALSE, '', '', _name, '', 0, 0,
438          CAST('now' AS TIMESTAMP), 0, CAST('now' AS TIMESTAMP), 0);
439     SELECT CURRVAL('tb_users_pk_user_seq') INTO pk_user;
440     RETURN pk_user;
441 END;
442 $$ LANGUAGE plpgsql;
443
444 CREATE FUNCTION sp_add_stats_traffic (_login dm_name,
445                                       _stats_date DATE,
446                                       _dir_num SMALLINT,
447                                       _upload BIGINT,
448                                       _download BIGINT)
449 RETURNS INTEGER
450 AS $$
451 DECLARE
452     _pk_user INTEGER;
453 BEGIN
454     SELECT pk_user INTO _pk_user
455         FROM tb_users
456         WHERE name = _login;
457
458     IF _pk_user IS NULL THEN
459         RAISE EXCEPTION 'User % not found', _login;
460         RETURN -1;
461     END IF;
462
463     UPDATE tb_stats_traffic SET
464         upload = _upload,
465         download = _download
466     WHERE fk_user = _pk_user AND
467           dir_num = _dir_num AND
468           stats_date = _stats_date;
469
470     IF NOT FOUND THEN
471         INSERT INTO tb_stats_traffic
472             (fk_user,
473              dir_num,
474              stats_date,
475              upload,
476              download)
477         VALUES
478             (_pk_user,
479              _dir_num,
480              _stats_date,
481              _upload,
482              _download);
483     END IF;
484
485     RETURN 1;
486 END;
487 $$ LANGUAGE plpgsql;
488
489 CREATE FUNCTION sp_set_user_data (_pk_user INTEGER,
490                                   _num SMALLINT,
491                                   _data VARCHAR(256))
492 RETURNS INTEGER
493 AS $$
494 BEGIN
495     UPDATE tb_users_data SET
496         data = _data
497     WHERE fk_user = _pk_user AND num = _num;
498
499     IF NOT FOUND THEN
500         INSERT INTO tb_users_data
501             (fk_user,
502              num,
503              data)
504         VALUES
505             (_pk_user,
506              _num,
507              _data);
508     END IF;
509
510     RETURN 1;
511 END;
512 $$ LANGUAGE plpgsql;
513
514 CREATE FUNCTION sp_add_param_log_entry(_login dm_name,
515                                        _admin_login dm_name,
516                                        _ip INET,
517                                        _param_name dm_name,
518                                        _event_time TIMESTAMP,
519                                        _from VARCHAR(256),
520                                        _to VARCHAR(256),
521                                        _comment TEXT)
522 RETURNS INTEGER
523 AS $$
524 DECLARE
525     _pk_user INTEGER;
526     _pk_admin INTEGER;
527     _pk_param INTEGER;
528 BEGIN
529     SELECT pk_user INTO _pk_user
530         FROM tb_users
531         WHERE name = _login;
532     IF _pk_user IS NULL THEN
533         RAISE EXCEPTION 'User % not found', _login;
534         RETURN -1;
535     END IF;
536
537     SELECT pk_admin INTO _pk_admin
538         FROM tb_admins
539         WHERE login = _admin_login;
540     IF _pk_admin IS NULL THEN
541         RAISE EXCEPTION 'Admin % not found', _admin_login;
542         RETURN -1;
543     END IF;
544
545     SELECT pk_parameter INTO _pk_param
546         FROM tb_parameters
547         WHERE name = _param_name;
548
549     IF NOT FOUND THEN
550         INSERT INTO tb_parameters (name) VALUES (_param_name);
551         SELECT CURRVAL('tb_parameters_pk_parameter_seq') INTO _pk_param;
552     END IF;
553
554     INSERT INTO tb_params_log
555         (fk_user,
556          fk_parameter,
557          fk_admin,
558          ip,
559          event_time,
560          from_val,
561          to_val,
562          comment)
563     VALUES
564         (_pk_user,
565          _pk_param,
566          _pk_admin,
567          _ip,
568          _event_time,
569          _from,
570          _to,
571          _comment);
572
573     RETURN 1;
574 END;
575 $$ LANGUAGE plpgsql;
576
577 CREATE FUNCTION sp_add_session_log_entry(_login dm_name,
578                                          _event_time TIMESTAMP,
579                                          _event_type dm_session_event_type,
580                                          _ip INET,
581                                          _cash dm_money)
582 RETURNS INTEGER
583 AS $$
584 DECLARE
585     _pk_user INTEGER;
586     _pk_session_log INTEGER;
587 BEGIN
588     SELECT pk_user INTO _pk_user
589         FROM tb_users
590         WHERE name = _login;
591     IF _pk_user IS NULL THEN
592         RAISE EXCEPTION 'User % not found', _login;
593         RETURN -1;
594     END IF;
595     
596     INSERT INTO tb_sessions_log
597         (fk_user,
598          event_time,
599          event_type,
600          ip,
601          cash)
602     VALUES
603         (_pk_user,
604          _event_time,
605          _event_type,
606          _ip,
607          _cash);
608
609     SELECT CURRVAL('tb_sessions_log_pk_session_log_seq') INTO _pk_session_log;
610
611     RETURN _pk_session_log;
612 END;
613 $$ LANGUAGE plpgsql;
614
615 /*
616  *****************************************************************************
617  * -= Создание администратора =-
618  *
619  * Двоичные права доступа пока не поддерживаются, по этому используются флаги
620  *****************************************************************************
621  */
622 INSERT INTO tb_admins
623     (login, passwd,
624      chg_conf, chg_password, chg_stat,
625      chg_cash, usr_add_del, chg_tariff,
626      chg_admin, chg_service, chg_corporation)
627 VALUES
628     ('admin',
629      'geahonjehjfofnhammefahbbbfbmpkmkmmefahbbbfbmpkmkmmefahbbbfbmpkmk',
630      1, 1, 1, 1, 1, 1, 1, 1, 1);
631 INSERT INTO tb_admins
632     (login, passwd,
633      chg_conf, chg_password, chg_stat,
634      chg_cash, usr_add_del, chg_tariff,
635      chg_admin, chg_service, chg_corporation)
636 VALUES
637     ('@stargazer',
638      '',
639      0, 0, 0, 0, 0, 0, 0, 0, 0);
640
641 INSERT INTO tb_info
642     (version)
643 VALUES
644     (5);