79 lines
2.6 KiB
SQL
79 lines
2.6 KiB
SQL
create table if not exists da_groups (
|
|
da_name varchar(20) not null,
|
|
da_comment varchar(100) default null,
|
|
constraint da_groups_pk primary key (da_name)
|
|
);
|
|
|
|
insert into da_groups (da_name, da_comment)
|
|
values
|
|
('general', 'Общие контакты'),
|
|
('work', 'Рабочие контакты'),
|
|
('personal', 'Личные контакты');
|
|
|
|
create table if not exists da_lists (
|
|
da_name varchar(20) not null,
|
|
da_comment varchar(100) default null,
|
|
constraint da_lists_pk primary key (da_name)
|
|
);
|
|
|
|
insert into da_lists (da_name, da_comment)
|
|
values
|
|
('general', 'Общий'),
|
|
('whitelist', 'Белый'),
|
|
('blacklist', 'Черный');
|
|
|
|
create table if not exists da_numbers (
|
|
da_number varchar(12) not null,
|
|
da_group varchar(20) not null default 'general',
|
|
da_list varchar(20) not null default 'general',
|
|
da_all_cc int not null default 0,
|
|
da_white_cc int not null default 0,
|
|
da_black_cc int not null default 0,
|
|
da_comment varchar(100) default null,
|
|
constraint da_numbers_pk primary key (da_number),
|
|
foreign key (da_group) references da_groups (da_name) on delete set null on update cascade,
|
|
foreign key (da_list) references da_lists (da_name) on delete set null on update cascade
|
|
);
|
|
|
|
create table if not exists da_sms (
|
|
da_id bigserial not null,
|
|
da_date timestamp not null default NOW(),
|
|
da_to varchar(12) not null,
|
|
da_from varchar(12) not null,
|
|
da_text text not null,
|
|
constraint da_sms_pk primary key (da_id)
|
|
);
|
|
|
|
create table if not exists da_ussd_type (
|
|
da_id bigserial not null,
|
|
da_comment varchar(100) not null,
|
|
constraint da_ussd_type_pk primary key (da_id)
|
|
);
|
|
|
|
insert into da_ussd_type (da_id, da_comment)
|
|
values
|
|
('0', 'Уведомление'),
|
|
('1', 'Запрос'),
|
|
('2', 'Прервано сетью'),
|
|
('3', 'Ответ другого локального клиента'),
|
|
('4', 'Операция не поддерживается'),
|
|
('5', 'Тайм-аут сети');
|
|
|
|
create table if not exists da_ussd (
|
|
da_id bigserial not null,
|
|
da_date timestamp not null default NOW(),
|
|
da_to varchar(12) not null,
|
|
da_type smallint not null,
|
|
da_text text not null,
|
|
constraint da_ussd_pk primary key (da_id)
|
|
);
|
|
|
|
create table if not exists da_server (
|
|
da_id int not null default 1,
|
|
da_transparent_mode bool not null default false,
|
|
da_internal_number varchar(12) not null,
|
|
da_external_number varchar(12) not null,
|
|
da_external_number_on bool not null default true,
|
|
constraint da_server_pk primary key (da_id)
|
|
);
|