X-Git-Url: https://git.stg.codes/stg.git/blobdiff_plain/4271ab433cd55bbd2612292bcf39e4dc3d7274f1..0907aa4037b12b6b88ee24495d4577a064d4f8db:/projects/stargazer/inst/var/00-base-00.postgresql.sql

diff --git a/projects/stargazer/inst/var/00-base-00.postgresql.sql b/projects/stargazer/inst/var/00-base-00.postgresql.sql
new file mode 100644
index 00000000..6b48d75c
--- /dev/null
+++ b/projects/stargazer/inst/var/00-base-00.postgresql.sql
@@ -0,0 +1,644 @@
+/*
+ *    This program is free software; you can redistribute it and/or modify
+ *    it under the terms of the GNU General Public License as published by
+ *    the Free Software Foundation; either version 2 of the License, or
+ *    (at your option) any later version.
+ *
+ *    This program is distributed in the hope that it will be useful,
+ *    but WITHOUT ANY WARRANTY; without even the implied warranty of
+ *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ *    GNU General Public License for more details.
+ *
+ *    You should have received a copy of the GNU General Public License
+ *    along with this program; if not, write to the Free Software
+ *    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
+ */
+
+/*
+ *****************************************************************************
+ *
+ * Скрипт генерации структуры базы для хранения данных Stargazer-a
+ *
+ * Примечание.
+ *      * dm_permission_flag. Представляет собой битовую маску - rw.
+ *          r - чтение, w - изменение параметра.
+ *          0 - дествие запрещено, 1 - действие разрешено
+ *
+ *      * dm_traff_type. Число определяющее тип подсчета трафика:
+ *          0 - up - считается по upload
+ *          1 - down - считается по download
+ *          2 - max - считается по максимальному среди upload/download
+ *          3 - up+down - считается по сумме upload и download
+ *
+ *      * dm_session_event_type. Указывает тип записи в логе о сессии.
+ *        'c' - connect, 'd' - disconnect.
+ *
+ *****************************************************************************
+ */
+
+/*
+ *  $Revision: 1.12 $
+ *  $Date: 2009/08/20 14:58:43 $
+ */
+
+
+/*
+ *****************************************************************************
+ * -= Создание типов и доменов =-
+ *****************************************************************************
+ */
+
+CREATE DOMAIN dm_name AS VARCHAR(32) NOT NULL;
+CREATE DOMAIN dm_password AS VARCHAR(64) NOT NULL;
+CREATE DOMAIN dm_permission_flag AS SMALLINT NOT NULL
+    CHECK ( value BETWEEN 0 AND 3 );
+CREATE DOMAIN dm_money AS NUMERIC(12, 4) NOT NULL DEFAULT 0;
+CREATE DOMAIN dm_traff_type AS SMALLINT NOT NULL
+    CHECK ( value BETWEEN 0 AND 3 );
+CREATE DOMAIN dm_day AS SMALLINT NOT NULL
+    CHECK ( value BETWEEN 0 AND 31 )
+    DEFAULT 0;
+CREATE DOMAIN dm_session_event_type AS CHAR(1) NOT NULL
+    CHECK ( value = 'c' OR value = 'd' );
+
+/*
+ *****************************************************************************
+ * -= Создание таблиц =-
+ *****************************************************************************
+ */
+
+CREATE TABLE tb_info
+(
+    version INTEGER NOT NULL
+);
+
+CREATE TABLE tb_admins
+(
+    pk_admin SERIAL PRIMARY KEY,
+    login dm_name UNIQUE,
+    passwd dm_password NOT NULL,
+    chg_conf dm_permission_flag,
+    chg_password dm_permission_flag,
+    chg_stat dm_permission_flag,
+    chg_cash dm_permission_flag,
+    usr_add_del dm_permission_flag,
+    chg_tariff dm_permission_flag,
+    chg_admin dm_permission_flag,
+    chg_service dm_permission_flag,
+    chg_corporation dm_permission_flag
+);
+
+CREATE TABLE tb_tariffs
+(
+    pk_tariff SERIAL PRIMARY KEY,
+    name dm_name UNIQUE,
+    fee dm_money,
+    free dm_money,
+    passive_cost dm_money,
+    traff_type dm_traff_type
+);
+
+CREATE TABLE tb_tariffs_params
+(
+    pk_tariff_param SERIAL PRIMARY KEY,
+    fk_tariff INTEGER NOT NULL,
+    dir_num SMALLINT NOT NULL,
+    price_day_a dm_money,
+    price_day_b dm_money,
+    price_night_a dm_money,
+    price_night_b dm_money,
+    threshold INTEGER NOT NULL,
+    time_day_begins TIME NOT NULL,
+    time_day_ends TIME NOT NULL,
+
+    FOREIGN KEY (fk_tariff)
+        REFERENCES tb_tariffs (pk_tariff)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_corporations
+(
+    pk_corporation SERIAL PRIMARY KEY,
+    name dm_name UNIQUE,
+    cash dm_money
+);
+
+CREATE TABLE tb_users
+(
+    pk_user SERIAL PRIMARY KEY,
+    fk_tariff INTEGER,
+    fk_tariff_change INTEGER,
+    fk_corporation INTEGER,
+    address VARCHAR(256) NOT NULL,
+    always_online BOOLEAN NOT NULL,
+    credit dm_money,
+    credit_expire TIMESTAMP NOT NULL,
+    disabled BOOLEAN NOT NULL,
+    disabled_detail_stat BOOLEAN NOT NULL,
+    email VARCHAR(256) NOT NULL,
+    grp dm_name,
+    note TEXT NOT NULL,
+    passive BOOLEAN NOT NULL,
+    passwd dm_password,
+    phone VARCHAR(256) NOT NULL,
+    name dm_name UNIQUE,
+    real_name VARCHAR(256) NOT NULL,
+    cash dm_money,
+    free_mb dm_money,
+    last_activity_time TIMESTAMP NOT NULL,
+    last_cash_add dm_money,
+    last_cash_add_time TIMESTAMP NOT NULL,
+    passive_time INTEGER NOT NULL,
+
+    FOREIGN KEY (fk_tariff)
+        REFERENCES tb_tariffs (pk_tariff)
+        ON DELETE CASCADE,
+    FOREIGN KEY (fk_tariff_change)
+        REFERENCES tb_tariffs (pk_tariff)
+        ON DELETE CASCADE,
+    FOREIGN KEY (fk_corporation)
+        REFERENCES tb_corporations (pk_corporation)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_detail_stats
+(
+    pk_detail_stat BIGSERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    dir_num SMALLINT NOT NULL,
+    ip INET NOT NULL,
+    download BIGINT NOT NULL,
+    upload BIGINT NOT NULL,
+    cost dm_money,
+    from_time TIMESTAMP NOT NULL,
+    till_time TIMESTAMP NOT NULL,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_services
+(
+    pk_service SERIAL PRIMARY KEY,
+    name dm_name UNIQUE,
+    comment TEXT NOT NULL,
+    cost dm_money,
+    pay_day dm_day
+);
+
+CREATE TABLE tb_users_services
+(
+    pk_user_service SERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    fk_service INTEGER NOT NULL,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE,
+    FOREIGN KEY (fk_service)
+        REFERENCES tb_services (pk_service)
+);
+
+CREATE TABLE tb_messages
+(
+    pk_message SERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    ver SMALLINT NOT NULL,
+    msg_type SMALLINT NOT NULL,
+    last_send_time TIMESTAMP NOT NULL,
+    creation_time TIMESTAMP NOT NULL,
+    show_time INTEGER NOT NULL,
+    repeat SMALLINT NOT NULL,
+    repeat_period INTEGER NOT NULL,
+    msg_text TEXT NOT NULL,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_stats_traffic
+(
+    pk_stat_traffic BIGSERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    stats_date DATE NOT NULL,
+    dir_num SMALLINT NOT NULL,
+    download BIGINT NOT NULL,
+    upload BIGINT NOT NULL,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE,
+    UNIQUE (fk_user, stats_date, dir_num)
+);
+
+CREATE TABLE tb_users_data
+(
+    pk_user_data SERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    num SMALLINT NOT NULL,
+    data VARCHAR(256) NOT NULL,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_allowed_ip
+(
+    pk_allowed_ip SERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    ip INET NOT NULL,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_sessions_log
+(
+    pk_session_log SERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    event_time TIMESTAMP NOT NULL,
+    event_type dm_session_event_type,
+    ip INET NOT NULL,
+    cash dm_money,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_sessions_data
+(
+    pk_session_data SERIAL PRIMARY KEY,
+    fk_session_log INTEGER NOT NULL,
+    dir_num SMALLINT NOT NULL,
+    session_upload BIGINT NOT NULL,
+    session_download BIGINT NOT NULL,
+    month_upload BIGINT NOT NULL,
+    month_download BIGINT NOT NULL,
+
+    FOREIGN KEY (fk_session_log)
+        REFERENCES tb_sessions_log (pk_session_log)
+        ON DELETE CASCADE
+);
+
+CREATE TABLE tb_parameters
+(
+    pk_parameter SERIAL PRIMARY KEY,
+    name dm_name UNIQUE
+);
+
+CREATE TABLE tb_params_log
+(
+    pk_param_log SERIAL PRIMARY KEY,
+    fk_user INTEGER NOT NULL,
+    fk_parameter INTEGER NOT NULL,
+    fk_admin INTEGER NOT NULL,
+    ip INET NOT NULL,
+    event_time TIMESTAMP NOT NULL,
+    from_val VARCHAR(256),
+    to_val VARCHAR(256),
+    comment TEXT,
+
+    FOREIGN KEY (fk_user)
+        REFERENCES tb_users (pk_user)
+        ON DELETE CASCADE,
+    FOREIGN KEY (fk_parameter)
+        REFERENCES tb_parameters (pk_parameter),
+    FOREIGN KEY (fk_admin)
+        REFERENCES tb_admins (pk_admin)
+        ON DELETE CASCADE
+);
+
+/*
+ *****************************************************************************
+ * -= Создание хранимых процедур =-
+ *****************************************************************************
+ */
+
+CREATE FUNCTION sp_add_message(_login dm_name,
+                               _ver SMALLINT,
+                               _msg_type SMALLINT,
+                               _last_send_time TIMESTAMP,
+                               _creation_time TIMESTAMP,
+                               _show_time INTEGER,
+                               _repeat SMALLINT,
+                               _repeat_period INTEGER,
+                               _msg_text TEXT)
+RETURNS INTEGER
+AS $$
+DECLARE
+    _pk_user INTEGER;
+BEGIN
+    SELECT pk_user INTO _pk_user
+        FROM tb_users
+        WHERE name = _login;
+    IF _pk_user IS NULL THEN
+        RAISE EXCEPTION 'User % not found', _login;
+        RETURN -1;
+    END IF;
+    INSERT INTO tb_messages
+        (fk_user,
+         ver,
+         msg_type,
+         last_send_time,
+         creation_time,
+         show_time,
+         repeat,
+         repeat_period,
+         msg_text)
+    VALUES
+        (_pk_user,
+         _ver,
+         _msg_type,
+         _last_send_time,
+         _creation_time,
+         _show_time,
+         _repeat,
+         _repeat_period,
+         _msg_text);
+    RETURN CURRVAL('tb_messages_pk_message_seq');
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION sp_add_tariff(_name dm_name, _dirs INTEGER)
+RETURNS INTEGER
+AS $$
+DECLARE
+    pk_tariff INTEGER;
+BEGIN
+    INSERT INTO tb_tariffs
+        (name,
+         fee,
+         free,
+         passive_cost,
+         traff_type)
+    VALUES
+        (_name,
+         0, 0, 0, 0);
+    SELECT CURRVAL('tb_tariffs_pk_tariff_seq') INTO pk_tariff;
+    FOR i IN 1.._dirs LOOP
+        INSERT INTO tb_tariffs_params
+            (fk_tariff,
+             dir_num,
+             price_day_a,
+             price_day_b,
+             price_night_a,
+             price_night_b,
+             threshold,
+             time_day_begins,
+             time_day_ends)
+        VALUES
+            (pk_tariff,
+             i - 1,
+             0, 0, 0, 0, 0,
+             CAST('1970-01-01 00:00:00+00' AS TIMESTAMP),
+             CAST('1970-01-01 00:00:00+00' AS TIMESTAMP));
+    END LOOP;
+    RETURN pk_tariff;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION sp_add_user(_name dm_name)
+RETURNS INTEGER
+AS $$
+DECLARE
+    pk_user INTEGER;
+BEGIN
+    INSERT INTO tb_users
+        (fk_tariff,
+         fk_tariff_change,
+         fk_corporation,
+         address,
+         always_online,
+         credit,
+         credit_expire,
+         disabled,
+         disabled_detail_stat,
+         email,
+         grp,
+         note,
+         passive,
+         passwd,
+         phone,
+         name,
+         real_name,
+         cash,
+         free_mb,
+         last_activity_time,
+         last_cash_add,
+         last_cash_add_time,
+         passive_time)
+    VALUES
+        (NULL, NULL, NULL, '', FALSE, 0, CAST('now' AS TIMESTAMP),
+         FALSE, FALSE, '', '', '', FALSE, '', '', _name, '', 0, 0,
+         CAST('now' AS TIMESTAMP), 0, CAST('now' AS TIMESTAMP), 0);
+    SELECT CURRVAL('tb_users_pk_user_seq') INTO pk_user;
+    RETURN pk_user;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION sp_add_stats_traffic (_login dm_name,
+                                      _stats_date DATE,
+                                      _dir_num SMALLINT,
+                                      _upload BIGINT,
+                                      _download BIGINT)
+RETURNS INTEGER
+AS $$
+DECLARE
+    _pk_user INTEGER;
+BEGIN
+    SELECT pk_user INTO _pk_user
+        FROM tb_users
+        WHERE name = _login;
+
+    IF _pk_user IS NULL THEN
+        RAISE EXCEPTION 'User % not found', _login;
+        RETURN -1;
+    END IF;
+
+    UPDATE tb_stats_traffic SET
+        upload = _upload,
+        download = _download
+    WHERE fk_user = _pk_user AND
+          dir_num = _dir_num AND
+          stats_date = _stats_date;
+
+    IF NOT FOUND THEN
+        INSERT INTO tb_stats_traffic
+            (fk_user,
+             dir_num,
+             stats_date,
+             upload,
+             download)
+        VALUES
+            (_pk_user,
+             _dir_num,
+             _stats_date,
+             _upload,
+             _download);
+    END IF;
+
+    RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION sp_set_user_data (_pk_user INTEGER,
+                                  _num SMALLINT,
+                                  _data VARCHAR(256))
+RETURNS INTEGER
+AS $$
+BEGIN
+    UPDATE tb_users_data SET
+        data = _data
+    WHERE fk_user = _pk_user AND num = _num;
+
+    IF NOT FOUND THEN
+        INSERT INTO tb_users_data
+            (fk_user,
+             num,
+             data)
+        VALUES
+            (_pk_user,
+             _num,
+             _data);
+    END IF;
+
+    RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION sp_add_param_log_entry(_login dm_name,
+                                       _admin_login dm_name,
+                                       _ip INET,
+                                       _param_name dm_name,
+                                       _event_time TIMESTAMP,
+                                       _from VARCHAR(256),
+                                       _to VARCHAR(256),
+                                       _comment TEXT)
+RETURNS INTEGER
+AS $$
+DECLARE
+    _pk_user INTEGER;
+    _pk_admin INTEGER;
+    _pk_param INTEGER;
+BEGIN
+    SELECT pk_user INTO _pk_user
+        FROM tb_users
+        WHERE name = _login;
+    IF _pk_user IS NULL THEN
+        RAISE EXCEPTION 'User % not found', _login;
+        RETURN -1;
+    END IF;
+
+    SELECT pk_admin INTO _pk_admin
+        FROM tb_admins
+        WHERE login = _admin_login;
+    IF _pk_admin IS NULL THEN
+        RAISE EXCEPTION 'Admin % not found', _admin_login;
+        RETURN -1;
+    END IF;
+
+    SELECT pk_parameter INTO _pk_param
+        FROM tb_parameters
+        WHERE name = _param_name;
+
+    IF NOT FOUND THEN
+        INSERT INTO tb_parameters (name) VALUES (_param_name);
+        SELECT CURRVAL('tb_parameters_pk_parameter_seq') INTO _pk_param;
+    END IF;
+
+    INSERT INTO tb_params_log
+        (fk_user,
+         fk_parameter,
+         fk_admin,
+         ip,
+         event_time,
+         from_val,
+         to_val,
+         comment)
+    VALUES
+        (_pk_user,
+         _pk_param,
+         _pk_admin,
+         _ip,
+         _event_time,
+         _from,
+         _to,
+         _comment);
+
+    RETURN 1;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION sp_add_session_log_entry(_login dm_name,
+                                         _event_time TIMESTAMP,
+                                         _event_type dm_session_event_type,
+                                         _ip INET,
+                                         _cash dm_money)
+RETURNS INTEGER
+AS $$
+DECLARE
+    _pk_user INTEGER;
+    _pk_session_log INTEGER;
+BEGIN
+    SELECT pk_user INTO _pk_user
+        FROM tb_users
+        WHERE name = _login;
+    IF _pk_user IS NULL THEN
+        RAISE EXCEPTION 'User % not found', _login;
+        RETURN -1;
+    END IF;
+    
+    INSERT INTO tb_sessions_log
+        (fk_user,
+         event_time,
+         event_type,
+         ip,
+         cash)
+    VALUES
+        (_pk_user,
+         _event_time,
+         _event_type,
+         _ip,
+         _cash);
+
+    SELECT CURRVAL('tb_sessions_log_pk_session_log_seq') INTO _pk_session_log;
+
+    RETURN _pk_session_log;
+END;
+$$ LANGUAGE plpgsql;
+
+/*
+ *****************************************************************************
+ * -= Создание администратора =-
+ *
+ * Двоичные права доступа пока не поддерживаются, по этому используются флаги
+ *****************************************************************************
+ */
+INSERT INTO tb_admins
+    (login, passwd,
+     chg_conf, chg_password, chg_stat,
+     chg_cash, usr_add_del, chg_tariff,
+     chg_admin, chg_service, chg_corporation)
+VALUES
+    ('admin',
+     'geahonjehjfofnhammefahbbbfbmpkmkmmefahbbbfbmpkmkmmefahbbbfbmpkmk',
+     1, 1, 1, 1, 1, 1, 1, 1, 1);
+INSERT INTO tb_admins
+    (login, passwd,
+     chg_conf, chg_password, chg_stat,
+     chg_cash, usr_add_del, chg_tariff,
+     chg_admin, chg_service, chg_corporation)
+VALUES
+    ('@stargazer',
+     '',
+     0, 0, 0, 0, 0, 0, 0, 0, 0);
+
+INSERT INTO tb_info
+    (version)
+VALUES
+    (5);