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);