· 8 years ago · Dec 11, 2017, 11:30 PM
1-- MySQL Script generated by MySQL Workbench
2-- Mon Dec 11 18:28:39 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`.`rate_card_profiles`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`rate_card_profiles` (
24 `profile_id` INT NOT NULL AUTO_INCREMENT,
25 `description` MEDIUMTEXT NULL,
26 `column1` VARCHAR(45) NULL,
27 `column2` VARCHAR(45) NULL,
28 `column3` VARCHAR(45) NULL,
29 `column4` VARCHAR(45) NULL,
30 `cloumn5` VARCHAR(45) NULL,
31 `column6` VARCHAR(45) NULL,
32 `column7` VARCHAR(45) NULL,
33 `column8` VARCHAR(45) NULL,
34 `column9` VARCHAR(45) NULL,
35 `column10` VARCHAR(45) NULL,
36 PRIMARY KEY (`profile_id`))
37ENGINE = InnoDB;
38
39
40-- -----------------------------------------------------
41-- Table `raw_rate_card_db`.`providers`
42-- -----------------------------------------------------
43CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`providers` (
44 `provider_id` INT NOT NULL AUTO_INCREMENT,
45 `provider_name` VARCHAR(45) NOT NULL,
46 `provider_email` VARCHAR(45) NULL,
47 `provider_phone` VARCHAR(45) NULL,
48 `description` MEDIUMTEXT NULL,
49 `rate_card_profile_id` INT NOT NULL,
50 PRIMARY KEY (`provider_id`),
51 UNIQUE INDEX `provider_name_UNIQUE` (`provider_name` ASC),
52 INDEX `fk_providers_rate_card_profiles1_idx` (`rate_card_profile_id` ASC),
53 CONSTRAINT `fk_providers_rate_card_profiles1`
54 FOREIGN KEY (`rate_card_profile_id`)
55 REFERENCES `raw_rate_card_db`.`rate_card_profiles` (`profile_id`)
56 ON DELETE NO ACTION
57 ON UPDATE NO ACTION)
58ENGINE = InnoDB;
59
60
61-- -----------------------------------------------------
62-- Table `raw_rate_card_db`.`trunks`
63-- -----------------------------------------------------
64CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`trunks` (
65 `trunk_id` INT NOT NULL AUTO_INCREMENT,
66 `provider_id` INT NOT NULL,
67 `trunk_ip` VARCHAR(45) NOT NULL,
68 `trunk_port` INT NOT NULL,
69 PRIMARY KEY (`trunk_id`),
70 INDEX `fk_trunks_providers1_idx` (`provider_id` ASC),
71 CONSTRAINT `fk_trunks_providers1`
72 FOREIGN KEY (`provider_id`)
73 REFERENCES `raw_rate_card_db`.`providers` (`provider_id`)
74 ON DELETE NO ACTION
75 ON UPDATE NO ACTION)
76ENGINE = InnoDB;
77
78
79-- -----------------------------------------------------
80-- Table `raw_rate_card_db`.`raw_rate_cards`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`raw_rate_cards` (
83 `raw_rate_card_id` INT NOT NULL AUTO_INCREMENT,
84 `provider_id` INT NOT NULL,
85 `rate_card_name` VARCHAR(45) NULL,
86 `tech_prefix` VARCHAR(45) NULL,
87 `start_ts` DATETIME NULL,
88 `end_ts` DATETIME NULL,
89 `description` MEDIUMTEXT NULL,
90 `approved_ts` DATETIME NULL,
91 `approved_by` VARCHAR(45) NULL,
92 PRIMARY KEY (`raw_rate_card_id`),
93 INDEX `fk_rate_cards_providers1_idx` (`provider_id` ASC),
94 CONSTRAINT `fk_rate_cards_providers1`
95 FOREIGN KEY (`provider_id`)
96 REFERENCES `raw_rate_card_db`.`providers` (`provider_id`)
97 ON DELETE NO ACTION
98 ON UPDATE NO ACTION)
99ENGINE = InnoDB;
100
101
102-- -----------------------------------------------------
103-- Table `raw_rate_card_db`.`obie_prices`
104-- -----------------------------------------------------
105CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`obie_prices` (
106 `id` INT NOT NULL AUTO_INCREMENT,
107 `prefix` VARCHAR(45) NOT NULL,
108 `detination` VARCHAR(45) NULL,
109 `sell_rate` FLOAT NOT NULL,
110 `description` VARCHAR(128) NULL,
111 PRIMARY KEY (`id`))
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `raw_rate_card_db`.`raw_rates`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`raw_rates` (
119 `raw_rate_id` INT NOT NULL AUTO_INCREMENT,
120 `raw_rate_card_id` INT NOT NULL,
121 `prefix` VARCHAR(45) NULL,
122 `destination` VARCHAR(45) NULL,
123 `buy_rate` FLOAT NULL,
124 `buy_rate_minimum` FLOAT NOT NULL DEFAULT 0,
125 `buy_rate_increment` INT NOT NULL DEFAULT 1,
126 PRIMARY KEY (`raw_rate_id`),
127 INDEX `fk_rates_rate_cards1_idx` (`raw_rate_card_id` ASC),
128 CONSTRAINT `fk_rates_rate_cards1`
129 FOREIGN KEY (`raw_rate_card_id`)
130 REFERENCES `raw_rate_card_db`.`raw_rate_cards` (`raw_rate_card_id`)
131 ON DELETE NO ACTION
132 ON UPDATE NO ACTION)
133ENGINE = InnoDB;
134
135
136-- -----------------------------------------------------
137-- Table `raw_rate_card_db`.`final_rate_cards`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`final_rate_cards` (
140 `final_rate_card_id` INT NOT NULL,
141 `provider_id` INT NULL,
142 `rate_card_name` VARCHAR(45) NULL,
143 `tech_prefix` VARCHAR(45) NULL,
144 `start_ts` DATETIME NULL,
145 `end_ts` DATETIME NULL,
146 `description` MEDIUMTEXT NULL,
147 PRIMARY KEY (`final_rate_card_id`))
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `raw_rate_card_db`.`ratecards_trunks`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`ratecards_trunks` (
155 `id` INT NOT NULL AUTO_INCREMENT,
156 `trunk_id` INT NOT NULL,
157 `final_rate_card_id` INT NOT NULL,
158 PRIMARY KEY (`id`),
159 INDEX `fk_ratecards_trunks_trunks1_idx` (`trunk_id` ASC),
160 INDEX `fk_ratecards_trunks_final_rate_cards1_idx` (`final_rate_card_id` ASC),
161 CONSTRAINT `fk_ratecards_trunks_trunks1`
162 FOREIGN KEY (`trunk_id`)
163 REFERENCES `raw_rate_card_db`.`trunks` (`trunk_id`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION,
166 CONSTRAINT `fk_ratecards_trunks_final_rate_cards1`
167 FOREIGN KEY (`final_rate_card_id`)
168 REFERENCES `raw_rate_card_db`.`final_rate_cards` (`final_rate_card_id`)
169 ON DELETE NO ACTION
170 ON UPDATE NO ACTION)
171ENGINE = InnoDB;
172
173
174-- -----------------------------------------------------
175-- Table `raw_rate_card_db`.`final_rates`
176-- -----------------------------------------------------
177CREATE TABLE IF NOT EXISTS `raw_rate_card_db`.`final_rates` (
178 `final_rate_id` INT NOT NULL AUTO_INCREMENT,
179 `final_rate_card_id` INT NOT NULL,
180 `prefix` VARCHAR(45) NULL,
181 `destination` VARCHAR(45) NULL,
182 `buy_rate` FLOAT NULL,
183 `buy_rate_minimum` FLOAT NOT NULL DEFAULT 0,
184 `buy_rate_increment` INT NOT NULL DEFAULT 1,
185 `sell_rate` FLOAT NULL,
186 `sell_rate_minimum` FLOAT NOT NULL DEFAULT 0,
187 `sell_rate_increment` INT NOT NULL DEFAULT 1,
188 PRIMARY KEY (`final_rate_id`),
189 INDEX `fk_final_rates_final_rate_cards1_idx` (`final_rate_card_id` ASC),
190 CONSTRAINT `fk_final_rates_final_rate_cards1`
191 FOREIGN KEY (`final_rate_card_id`)
192 REFERENCES `raw_rate_card_db`.`final_rate_cards` (`final_rate_card_id`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196
197
198SET SQL_MODE=@OLD_SQL_MODE;
199SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
200SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;