· 8 years ago · Dec 19, 2017, 04:18 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Dec 19 11:16:06 2017
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema raw_rate_card_db
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema raw_rate_card_db
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `raw_rate_card_db` DEFAULT CHARACTER SET utf8 ;
18USE `raw_rate_card_db` ;
19
20-- -----------------------------------------------------
21-- Table `raw_rate_card_db`.`carrier`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`carrier` (
24 `carrier_id` INT NOT NULL AUTO_INCREMENT,
25 `carrier_name` VARCHAR(128) NOT NULL,
26 `email` VARCHAR(64) NOT NULL,
27 `address` VARCHAR(64) NOT NULL,
28 `phone_number` VARCHAR(64) NOT NULL,
29 `taxable` TINYINT NOT NULL,
30 `tier_number` VARCHAR(45) NOT NULL,
31 `two_digit_unique_code` VARCHAR(45) NOT NULL,
32 `description` MEDIUMTEXT NULL,
33 `date_added` TIMESTAMP NOT NULL,
34 `add_ts` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
35 PRIMARY KEY (`carrier_id`),
36 UNIQUE INDEX `provider_name_UNIQUE` (`carrier_name` ASC))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `raw_rate_card_db`.`trunks`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`trunks` (
44 `trunk_id` INT NOT NULL AUTO_INCREMENT,
45 `cx_trunk_id` INT NULL,
46 `carrier_id` INT NOT NULL,
47 `trunk_ip` VARCHAR(32) NOT NULL,
48 `trunk_port` INT NOT NULL,
49 `active` TINYINT NOT NULL DEFAULT 1,
50 `metadata` VARCHAR(128) NULL,
51 PRIMARY KEY (`trunk_id`),
52 INDEX `fk_trunks_providers1_idx` (`carrier_id` ASC),
53 CONSTRAINT `fk_trunks_providers1`
54 FOREIGN KEY (`carrier_id`)
55 REFERENCES `raw_rate_card_db`.`carrier` (`carrier_id`)
56 ON DELETE NO ACTION
57 ON UPDATE NO ACTION)
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `raw_rate_card_db`.`rate_card_profiles`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`rate_card_profiles` (
65 `profile_id` INT NOT NULL AUTO_INCREMENT,
66 `name` VARCHAR(128) NOT NULL,
67 `description` MEDIUMTEXT NULL,
68 `column1` VARCHAR(45) NULL,
69 `column2` VARCHAR(45) NULL,
70 `column3` VARCHAR(45) NULL,
71 `column4` VARCHAR(45) NULL,
72 `cloumn5` VARCHAR(45) NULL,
73 `column6` VARCHAR(45) NULL,
74 `column7` VARCHAR(45) NULL,
75 `column8` VARCHAR(45) NULL,
76 `column9` VARCHAR(45) NULL,
77 `column10` VARCHAR(45) NULL,
78 PRIMARY KEY (`profile_id`))
79ENGINE = InnoDB;
80
81
82-- -----------------------------------------------------
83-- Table `raw_rate_card_db`.`destinations`
84-- -----------------------------------------------------
85CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`destinations` (
86 `id` INT NOT NULL,
87 `prefix` VARCHAR(32) NOT NULL,
88 `name` VARCHAR(64) NULL,
89 PRIMARY KEY (`id`),
90 UNIQUE INDEX `id_UNIQUE` (`id` ASC))
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `raw_rate_card_db`.`teleu_rates`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`teleu_rates` (
98 `id` INT NOT NULL AUTO_INCREMENT,
99 `destinations_id` INT NOT NULL,
100 `buy_rate` FLOAT NOT NULL,
101 `sell_rate` FLOAT NOT NULL,
102 `description` VARCHAR(128) NULL,
103 `is_fixed` TINYINT NOT NULL DEFAULT 0,
104 `update_ts` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
105 PRIMARY KEY (`id`),
106 INDEX `fk_teleu-_rates_destinations1_idx` (`destinations_id` ASC),
107 CONSTRAINT `fk_teleu-_rates_destinations1`
108 FOREIGN KEY (`destinations_id`)
109 REFERENCES `raw_rate_card_db`.`destinations` (`id`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION)
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `raw_rate_card_db`.`rate_cards`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`rate_cards` (
119 `rate_card_id` INT NOT NULL,
120 `rate_card_name` VARCHAR(45) NULL,
121 `carrier_id` INT NOT NULL,
122 `tech_prefix` VARCHAR(45) NULL,
123 `start_ts` DATETIME NULL,
124 `end_ts` DATETIME NULL,
125 `add_ts` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
126 `description` MEDIUMTEXT NULL,
127 PRIMARY KEY (`rate_card_id`),
128 INDEX `fk_rate_cards_carrier1_idx` (`carrier_id` ASC),
129 CONSTRAINT `fk_rate_cards_carrier1`
130 FOREIGN KEY (`carrier_id`)
131 REFERENCES `raw_rate_card_db`.`carrier` (`carrier_id`)
132 ON DELETE NO ACTION
133 ON UPDATE NO ACTION)
134ENGINE = InnoDB;
135
136
137-- -----------------------------------------------------
138-- Table `raw_rate_card_db`.`ratecards_trunks`
139-- -----------------------------------------------------
140CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`ratecards_trunks` (
141 `id` INT NOT NULL AUTO_INCREMENT,
142 `trunk_id` INT NOT NULL,
143 `rate_card_id` INT NOT NULL,
144 PRIMARY KEY (`id`),
145 INDEX `fk_ratecards_trunks_trunks1_idx` (`trunk_id` ASC),
146 INDEX `fk_ratecards_trunks_final_rate_cards1_idx` (`rate_card_id` ASC),
147 CONSTRAINT `fk_ratecards_trunks_trunks1`
148 FOREIGN KEY (`trunk_id`)
149 REFERENCES `raw_rate_card_db`.`trunks` (`trunk_id`)
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION,
152 CONSTRAINT `fk_ratecards_trunks_final_rate_cards1`
153 FOREIGN KEY (`rate_card_id`)
154 REFERENCES `raw_rate_card_db`.`rate_cards` (`rate_card_id`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION)
157ENGINE = InnoDB;
158
159
160-- -----------------------------------------------------
161-- Table `raw_rate_card_db`.`rates`
162-- -----------------------------------------------------
163CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`rates` (
164 `rate_id` INT NOT NULL AUTO_INCREMENT,
165 `rate_card_id` INT NOT NULL,
166 `destinations_id` INT NOT NULL,
167 `buy_rate` FLOAT NULL,
168 `buy_rate_minimum` FLOAT NOT NULL DEFAULT 0,
169 `buy_rate_increment` INT NOT NULL DEFAULT 1,
170 `sell_rate` FLOAT NULL,
171 `sell_rate_minimum` FLOAT NOT NULL DEFAULT 0,
172 `sell_rate_increment` INT NOT NULL DEFAULT 1,
173 `start_ts` DATETIME NULL,
174 `end_ts` DATETIME NULL,
175 PRIMARY KEY (`rate_id`),
176 INDEX `fk_final_rates_final_rate_cards1_idx` (`rate_card_id` ASC),
177 INDEX `fk_rates_destinations1_idx` (`destinations_id` ASC),
178 CONSTRAINT `fk_final_rates_final_rate_cards1`
179 FOREIGN KEY (`rate_card_id`)
180 REFERENCES `raw_rate_card_db`.`rate_cards` (`rate_card_id`)
181 ON DELETE NO ACTION
182 ON UPDATE NO ACTION,
183 CONSTRAINT `fk_rates_destinations1`
184 FOREIGN KEY (`destinations_id`)
185 REFERENCES `raw_rate_card_db`.`destinations` (`id`)
186 ON DELETE NO ACTION
187 ON UPDATE NO ACTION)
188ENGINE = InnoDB;
189
190
191-- -----------------------------------------------------
192-- Table `raw_rate_card_db`.`callplans`
193-- -----------------------------------------------------
194CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`callplans` (
195 `id` INT NOT NULL AUTO_INCREMENT,
196 `title` VARCHAR(64) NOT NULL,
197 `subtitle` VARCHAR(128) NULL,
198 `available` ENUM('available', 'unavailable', 'deleted', 'staged', 'pending') NOT NULL DEFAULT 'available',
199 `valid_through` DATETIME NULL,
200 `add_ts` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
201 `callplanscol` VARCHAR(45) NULL,
202 PRIMARY KEY (`id`))
203ENGINE = InnoDB;
204
205
206-- -----------------------------------------------------
207-- Table `raw_rate_card_db`.`callplan_codes`
208-- -----------------------------------------------------
209CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`callplan_codes` (
210 `id` INT NOT NULL AUTO_INCREMENT,
211 `callplans_id` INT NOT NULL,
212 `code` VARCHAR(16) NOT NULL,
213 PRIMARY KEY (`id`),
214 INDEX `fk_callplan_codes_callplans1_idx` (`callplans_id` ASC),
215 CONSTRAINT `fk_callplan_codes_callplans1`
216 FOREIGN KEY (`callplans_id`)
217 REFERENCES `raw_rate_card_db`.`callplans` (`id`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION)
220ENGINE = InnoDB;
221
222
223-- -----------------------------------------------------
224-- Table `raw_rate_card_db`.`callplan_ratecards`
225-- -----------------------------------------------------
226CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`callplan_ratecards` (
227 `id` INT NOT NULL AUTO_INCREMENT,
228 `rate_card_id` INT NOT NULL,
229 `callplan_id` INT NOT NULL,
230 `priority` INT NOT NULL DEFAULT 0,
231 PRIMARY KEY (`id`),
232 INDEX `fk_callplan_ratecards_rate_cards1_idx` (`rate_card_id` ASC),
233 INDEX `fk_callplan_ratecards_callplans1_idx` (`callplan_id` ASC),
234 CONSTRAINT `fk_callplan_ratecards_rate_cards1`
235 FOREIGN KEY (`rate_card_id`)
236 REFERENCES `raw_rate_card_db`.`rate_cards` (`rate_card_id`)
237 ON DELETE NO ACTION
238 ON UPDATE NO ACTION,
239 CONSTRAINT `fk_callplan_ratecards_callplans1`
240 FOREIGN KEY (`callplan_id`)
241 REFERENCES `raw_rate_card_db`.`callplans` (`id`)
242 ON DELETE NO ACTION
243 ON UPDATE NO ACTION)
244ENGINE = InnoDB;
245
246
247SET SQL_MODE=@OLD_SQL_MODE;
248SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
249SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;