168 lines
6.6 KiB
SQL
168 lines
6.6 KiB
SQL
-- V2 phase-2 gateway matching and landing rewrite expansion.
|
|
-- This migration is additive and keeps legacy customer_gateway_policies and
|
|
-- vendor_gateway_prefix_rules in place for staged API/OpenSIPS rollout.
|
|
|
|
CREATE TABLE `business_prefixes` (
|
|
`id` VARCHAR(32) NOT NULL,
|
|
`prefix` VARCHAR(32) NOT NULL,
|
|
`name` VARCHAR(120) NOT NULL,
|
|
`description` VARCHAR(500) NULL,
|
|
`priority` INTEGER NOT NULL DEFAULT 100,
|
|
`status` ENUM('ENABLED', 'DISABLED') NOT NULL DEFAULT 'ENABLED',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
`created_by` VARCHAR(32) NULL,
|
|
`updated_by` VARCHAR(32) NULL,
|
|
`version` INTEGER NOT NULL DEFAULT 1,
|
|
`deleted_at` DATETIME(3) NULL,
|
|
|
|
UNIQUE INDEX `business_prefixes_prefix_key`(`prefix`),
|
|
INDEX `business_prefixes_status_priority_idx`(`status`, `priority`),
|
|
INDEX `business_prefixes_deleted_at_idx`(`deleted_at`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE `customer_gateways`
|
|
ADD COLUMN `line_group_id` VARCHAR(32) NULL,
|
|
ADD COLUMN `billing_cycle_sec` INTEGER NOT NULL DEFAULT 60,
|
|
ADD COLUMN `cycle_rate` DECIMAL(20, 6) NOT NULL DEFAULT 0,
|
|
ADD COLUMN `caller_match_mode` ENUM('ANY', 'PREFIXES') NOT NULL DEFAULT 'ANY',
|
|
ADD COLUMN `callee_match_mode` ENUM('ANY', 'BUSINESS_PREFIXES') NOT NULL DEFAULT 'ANY';
|
|
|
|
CREATE INDEX `customer_gateways_line_group_id_idx` ON `customer_gateways`(`line_group_id`);
|
|
|
|
ALTER TABLE `customer_gateways`
|
|
ADD CONSTRAINT `customer_gateways_line_group_id_fkey`
|
|
FOREIGN KEY (`line_group_id`) REFERENCES `landing_line_groups`(`id`)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
CREATE TABLE `customer_gateway_ips` (
|
|
`id` VARCHAR(32) NOT NULL,
|
|
`gateway_id` VARCHAR(32) NOT NULL,
|
|
`source_ip` VARCHAR(45) NOT NULL,
|
|
`status` ENUM('ENABLED', 'DISABLED') NOT NULL DEFAULT 'ENABLED',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
`created_by` VARCHAR(32) NULL,
|
|
`updated_by` VARCHAR(32) NULL,
|
|
`version` INTEGER NOT NULL DEFAULT 1,
|
|
`deleted_at` DATETIME(3) NULL,
|
|
|
|
UNIQUE INDEX `customer_gateway_ips_gateway_id_source_ip_key`(`gateway_id`, `source_ip`),
|
|
INDEX `customer_gateway_ips_source_ip_status_idx`(`source_ip`, `status`),
|
|
INDEX `customer_gateway_ips_deleted_at_idx`(`deleted_at`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE `customer_gateway_ips`
|
|
ADD CONSTRAINT `customer_gateway_ips_gateway_id_fkey`
|
|
FOREIGN KEY (`gateway_id`) REFERENCES `customer_gateways`(`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
INSERT INTO `customer_gateway_ips` (
|
|
`id`,
|
|
`gateway_id`,
|
|
`source_ip`,
|
|
`status`,
|
|
`created_at`,
|
|
`updated_at`,
|
|
`created_by`,
|
|
`updated_by`,
|
|
`version`,
|
|
`deleted_at`
|
|
)
|
|
SELECT
|
|
CONCAT('cgip_', LEFT(REPLACE(UUID(), '-', ''), 27)),
|
|
`id`,
|
|
`source_ip`,
|
|
`status`,
|
|
`created_at`,
|
|
`updated_at`,
|
|
`created_by`,
|
|
`updated_by`,
|
|
`version`,
|
|
`deleted_at`
|
|
FROM `customer_gateways`
|
|
WHERE `source_ip` IS NOT NULL
|
|
AND `source_ip` <> '';
|
|
|
|
CREATE TABLE `customer_gateway_business_prefixes` (
|
|
`id` VARCHAR(32) NOT NULL,
|
|
`gateway_id` VARCHAR(32) NOT NULL,
|
|
`business_prefix_id` VARCHAR(32) NOT NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`created_by` VARCHAR(32) NULL,
|
|
|
|
UNIQUE INDEX `cgbp_gateway_business_prefix_key`(`gateway_id`, `business_prefix_id`),
|
|
INDEX `customer_gateway_business_prefixes_business_prefix_id_idx`(`business_prefix_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE `customer_gateway_business_prefixes`
|
|
ADD CONSTRAINT `customer_gateway_business_prefixes_gateway_id_fkey`
|
|
FOREIGN KEY (`gateway_id`) REFERENCES `customer_gateways`(`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `customer_gateway_business_prefixes`
|
|
ADD CONSTRAINT `customer_gateway_business_prefixes_business_prefix_id_fkey`
|
|
FOREIGN KEY (`business_prefix_id`) REFERENCES `business_prefixes`(`id`)
|
|
ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
CREATE TABLE `customer_gateway_caller_prefixes` (
|
|
`id` VARCHAR(32) NOT NULL,
|
|
`gateway_id` VARCHAR(32) NOT NULL,
|
|
`prefix` VARCHAR(64) NOT NULL,
|
|
`priority` INTEGER NOT NULL DEFAULT 100,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`created_by` VARCHAR(32) NULL,
|
|
|
|
UNIQUE INDEX `customer_gateway_caller_prefixes_gateway_id_prefix_key`(`gateway_id`, `prefix`),
|
|
INDEX `customer_gateway_caller_prefixes_gateway_id_priority_idx`(`gateway_id`, `priority`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE `customer_gateway_caller_prefixes`
|
|
ADD CONSTRAINT `customer_gateway_caller_prefixes_gateway_id_fkey`
|
|
FOREIGN KEY (`gateway_id`) REFERENCES `customer_gateways`(`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `vendor_gateways`
|
|
ADD COLUMN `landing_callee_prefix` VARCHAR(64) NULL;
|
|
|
|
CREATE TABLE `vendor_gateway_caller_rewrite_pool` (
|
|
`id` VARCHAR(32) NOT NULL,
|
|
`vendor_gateway_id` VARCHAR(32) NOT NULL,
|
|
`caller` VARCHAR(64) NOT NULL,
|
|
`weight` INTEGER NOT NULL DEFAULT 1,
|
|
`status` ENUM('ENABLED', 'DISABLED') NOT NULL DEFAULT 'ENABLED',
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
`created_by` VARCHAR(32) NULL,
|
|
`updated_by` VARCHAR(32) NULL,
|
|
`version` INTEGER NOT NULL DEFAULT 1,
|
|
`deleted_at` DATETIME(3) NULL,
|
|
|
|
INDEX `vendor_gateway_caller_rewrite_pool_vendor_gateway_id_status_idx`(`vendor_gateway_id`, `status`),
|
|
INDEX `vendor_gateway_caller_rewrite_pool_deleted_at_idx`(`deleted_at`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
ALTER TABLE `vendor_gateway_caller_rewrite_pool`
|
|
ADD CONSTRAINT `vendor_gateway_caller_rewrite_pool_vendor_gateway_id_fkey`
|
|
FOREIGN KEY (`vendor_gateway_id`) REFERENCES `vendor_gateways`(`id`)
|
|
ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE `raw_cdrs`
|
|
ADD COLUMN `raw_callee` VARCHAR(64) NULL,
|
|
ADD COLUMN `business_prefix_id` VARCHAR(32) NULL,
|
|
ADD COLUMN `business_prefix` VARCHAR(32) NULL,
|
|
ADD COLUMN `landing_caller` VARCHAR(64) NULL,
|
|
ADD COLUMN `landing_callee` VARCHAR(64) NULL;
|
|
|
|
CREATE INDEX `raw_cdrs_business_prefix_id_started_at_idx` ON `raw_cdrs`(`business_prefix_id`, `started_at`);
|
|
|
|
ALTER TABLE `raw_cdrs`
|
|
ADD CONSTRAINT `raw_cdrs_business_prefix_id_fkey`
|
|
FOREIGN KEY (`business_prefix_id`) REFERENCES `business_prefixes`(`id`)
|
|
ON DELETE SET NULL ON UPDATE CASCADE;
|