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

Для добавления новых коллекторов данных необходимо и достаточно создать БД определенного формата и заполнять в ней таблицу трафика (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