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