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