mirror of
https://github.com/cgrates/cgrates.git
synced 2026-02-11 18:16:24 +05:00
83 lines
2.4 KiB
SQL
83 lines
2.4 KiB
SQL
--
|
|
-- Table structure for table `cdrs`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS cdrs;
|
|
CREATE TABLE cdrs (
|
|
id SERIAL PRIMARY KEY,
|
|
cgrid CHAR(40) NOT NULL,
|
|
run_id VARCHAR(64) NOT NULL,
|
|
origin_host VARCHAR(64) NOT NULL,
|
|
source VARCHAR(64) NOT NULL,
|
|
origin_id VARCHAR(64) NOT NULL,
|
|
tor VARCHAR(16) NOT NULL,
|
|
request_type VARCHAR(24) NOT NULL,
|
|
tenant VARCHAR(64) NOT NULL,
|
|
category VARCHAR(64) NOT NULL,
|
|
account VARCHAR(128) NOT NULL,
|
|
subject VARCHAR(128) NOT NULL,
|
|
destination VARCHAR(128) NOT NULL,
|
|
setup_time TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
pdd NUMERIC(12,9) NOT NULL,
|
|
answer_time TIMESTAMP WITH TIME ZONE NULL,
|
|
usage NUMERIC(30,9) NOT NULL,
|
|
supplier VARCHAR(128) NOT NULL,
|
|
disconnect_cause VARCHAR(64) NOT NULL,
|
|
extra_fields jsonb NOT NULL,
|
|
cost_source VARCHAR(64) NOT NULL,
|
|
cost NUMERIC(20,4) DEFAULT NULL,
|
|
cost_details jsonb,
|
|
account_summary jsonb,
|
|
extra_info text,
|
|
created_at TIMESTAMP WITH TIME ZONE,
|
|
updated_at TIMESTAMP WITH TIME ZONE NULL,
|
|
deleted_at TIMESTAMP WITH TIME ZONE NULL,
|
|
UNIQUE (cgrid, run_id, origin_id)
|
|
);
|
|
;
|
|
DROP INDEX IF EXISTS deleted_at_cp_idx;
|
|
CREATE INDEX deleted_at_cp_idx ON cdrs (deleted_at);
|
|
|
|
|
|
DROP TABLE IF EXISTS sessions_costs;
|
|
CREATE TABLE sessions_costs (
|
|
id SERIAL PRIMARY KEY,
|
|
cgrid CHAR(40) NOT NULL,
|
|
run_id VARCHAR(64) NOT NULL,
|
|
origin_host VARCHAR(64) NOT NULL,
|
|
origin_id VARCHAR(64) NOT NULL,
|
|
cost_source VARCHAR(64) NOT NULL,
|
|
usage NUMERIC(30,9) NOT NULL,
|
|
cost_details jsonb,
|
|
created_at TIMESTAMP WITH TIME ZONE,
|
|
deleted_at TIMESTAMP WITH TIME ZONE NULL,
|
|
UNIQUE (cgrid, run_id)
|
|
);
|
|
DROP INDEX IF EXISTS cgrid_sessionscost_idx;
|
|
CREATE INDEX cgrid_sessionscost_idx ON sessions_costs (cgrid, run_id);
|
|
DROP INDEX IF EXISTS origin_sessionscost_idx;
|
|
CREATE INDEX origin_sessionscost_idx ON sessions_costs (origin_host, origin_id);
|
|
DROP INDEX IF EXISTS run_origin_sessionscost_idx;
|
|
CREATE INDEX run_origin_sessionscost_idx ON sessions_costs (run_id, origin_id);
|
|
DROP INDEX IF EXISTS deleted_at_sessionscost_idx;
|
|
CREATE INDEX deleted_at_sessionscost_idx ON sessions_costs (deleted_at);
|
|
|
|
--
|
|
-- Table structure for table `versions`
|
|
--
|
|
|
|
DROP TABLE IF EXISTS versions;
|
|
CREATE TABLE versions (
|
|
"id" SERIAL PRIMARY KEY,
|
|
"item" varchar(64) NOT NULL,
|
|
"version" INTEGER NOT NULL,
|
|
UNIQUE (item)
|
|
);
|
|
|
|
|
|
|
|
ALTER TABLE cdrs RENAME COLUMN usage to usage_old;
|
|
ALTER TABLE cdrs ADD usage NUMERIC(30);
|
|
UPDATE cdrs SET usage = usage_old * 1000000000 WHERE usage_old IS NOT NULL;
|
|
ALTER TABLE cdrs DROP COLUMN usage_old;
|