четверг, 26 ноября 2009 г.

Структура базы телефонного биллинга

Для добавления новых коллекторов данных необходимо и достаточно создать БД определенного формата и заполнять в ней таблицу трафика (telephony_log). Привожу код утилиты, инициирующей структуру требуемой БД (утилита вызывается из коллекторов при создании новой БД при ротации). Также рекомендуем просмотреть код поставляемых коллекторов (файлы telephony_agent_tacacs.tcl и telephony_agent_callbuilder.tcl, размером около 15 килобайт каждый).

Использование

./telephony_dataset_init.tcl
Утилита инициализации БД для хранения телефонной статистики.
Создает БД SQLite3 с указанным именем. Если не удалось создать БД, возвращает ошибку, иначе завершается без выдачи сообщений.
Примечание: используются только стандартные функции СУБД SQLite 3.5.9, модули расширения для заполнения базы не требуются.

Используйте программу следующим образом:
./telephony_dataset_init.tcl путь_БД


telephony_dataset_init.tcl

#!/usr/bin/tclsh8.5
# /opt/bin/telephony_dataset_init.tcl /srv/dataset/telephony/test.db
package require sqlite3

proc error {message} {
puts $message
exit
}

if {$argc!=1} {
error "Утилита инициализации БД для хранения телефонной статистики.
Создает БД SQLite3 с указанным именем. Если не удалось создать БД, возвращает ошибку, иначе завершается без выдачи сообщений.
Примечание: используются только стандартные функции СУБД SQLite 3.5.9, модули расширения для заполнения базы не требуются.

Используйте программу следующим образом:
\t$argv0 путь_БД
"
}

# операционная база, хранит последние записи
# старые записи могут сбрасываться в базы по месяцам или по годам в зависимости от скорости их заполнения
sqlite3 db [file join [lindex $argv 0]]
db timeout 2000
# структура базы фиксирована
# моментом потребления услуги считается время окончания разговора, хотя зависящая от времени стоимость считается по началу
db eval {
create table if not exists telephony_log (
nas_name text not null,
username text not null,
port blob not null,
date_start real not null,
duration integer not null,
origin text not null,
src text not null,
dst text not null,
code integer not null,
is_new integer not null default 1,
unique (nas_name,port,duration,origin,date_start) on conflict ignore
);
CREATE INDEX if not exists telephony_log_date_start_idx on telephony_log(date_start);
CREATE INDEX if not exists telephony_log_is_new_idx on telephony_log(is_new);
CREATE INDEX if not exists telephony_log_src_date_start_idx on telephony_log(src,date_start);
CREATE INDEX if not exists telephony_log_dst_date_start_idx on telephony_log(dst,date_start);

CREATE TABLE telephony_log_rating (
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
log_id integer not null, -- идентификатор биллингуемой записи
user_service_id integer not null, -- услуга пользователя, в которой указан тариф, оборудование, пользователь
src_user_id integer, -- идентификатор пользователя, которому принадлежит вызывающий номер
dst_user_id integer, -- идентификатор пользователя, которому принадлежит вызываемый номер
port_user_id integer, -- идентификатор пользователя, которому принадлежит порт
destname text collate NOCASE, -- название направления
destcode text, -- префикс E.164 номера телефона, текстовый для возможности хранения начинающихся с нуля и длинных префиксов
rcode text collate NOCASE, -- код региона
price real not null, -- стоимость минуты разговора
duration integer not null default 0, -- тарифицируемая продолжительность разговора, целых секунд
cost real not null, -- цена, единиц валюты currency, всегда без НДС, если cost>=0 то balance=active, иначе balance=passive
is_new integer not null default 1,
unique (log_id,user_service_id) on conflict replace -- услугу можно пробиллинговать только один раз
);
CREATE INDEX if not exists telephony_log_rating_user_service_id_idx on telephony_log_rating(user_service_id);
CREATE INDEX if not exists telephony_log_rating_is_new_idx on telephony_log_rating(is_new);
CREATE INDEX if not exists telephony_log_rating_log_id_idx on telephony_log_rating(log_id);

create table if not exists telephony_log_error (
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
log_id integer not null, -- идентификатор биллингуемой записи
user_service_id integer not null, -- услуга пользователя, в которой указан тариф, оборудование, пользователь
message text not null, -- сообщение об ошибке
unique (log_id,user_service_id) on conflict replace -- сохраняем только последнюю ошибку, т.к. при возникновении ошибки обработка записи прекращается
);
CREATE INDEX if not exists telephony_log_error_user_service_id_idx on telephony_log_error(user_service_id);
CREATE INDEX if not exists telephony_log_error_log_id_idx on telephony_log_error(log_id);

CREATE TABLE telephony_log_counter (
save_date REAL NOT NULL DEFAULT (julianday('now')),
delete_date REAL,
user_service_id integer not null, -- услуга пользователя, в которой указан тариф, оборудование, пользователь
value int not null, -- значение счетчика, представляет собой продолжительность разговоров в минутах
unique (user_service_id) on conflict replace -- при увеличении счетчика вставляется новая запись, а старая автоматически стирается
);
CREATE INDEX if not exists telephony_log_counter_user_service_id_idx on telephony_log_counter(user_service_id);

CREATE VIEW view_telephony_log_result as
select (select count(*) from telephony_log) as log,
(select count(*) from telephony_log_rating) as rating,
(select count(*) from telephony_log_error) as error;
}
db close

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru