+++ /dev/null
-/*
- * 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);
git.stg.codes / stg.git / blobdiff