· 8 years ago · Mar 13, 2018, 09:14 AM
1CREATE TABLE IF NOT EXISTS `volcano_shared`.`Staffs` (
2 `mobile` INT NOT NULL,
3 `name` VARCHAR(45) NOT NULL,
4 `location` VARCHAR(45) NULL,
5 `role` VARCHAR(45) NOT NULL,
6 `created_at` INT NULL,
7 `created_by` INT NULL,
8 `updated_at` INT NULL,
9 `updated_by` INT NULL,
10 `auth_key` VARCHAR(32) NULL,
11 `password_hash` VARCHAR(100) NULL,
12 `is_active` INT NOT NULL DEFAULT 1,
13 `tenant_db` VARCHAR(45) NOT NULL,
14 `is_third_party` INT NOT NULL DEFAULT 1,
15 `company` INT NULL DEFAULT 0 COMMENT 'Volcano Staffs should have 0',
16 `timezone` VARCHAR(45) NOT NULL,
17 PRIMARY KEY (`mobile`))
18ENGINE = InnoDB
19COMMENT = 'Routine Groups';
20
21CREATE TABLE IF NOT EXISTS `matunda_db`.`Customers` (
22 `mobile` VARCHAR(255) NOT NULL,
23 `name` VARCHAR(100) NOT NULL,
24 `id` VARCHAR(45) NULL,
25 `nationality` VARCHAR(45) NULL,
26 `passport` VARCHAR(100) NULL,
27 `gender` INT NULL COMMENT '1 - Male \n2 - Female',
28 `age` INT NULL,
29 `from_nation` VARCHAR(45) NULL,
30 `to_nation` VARCHAR(45) NULL,
31 `created_at` INT NOT NULL,
32 `created_by` INT NOT NULL,
33 `updated_at` INT NOT NULL,
34 `updated_by` INT NOT NULL,
35 PRIMARY KEY (`mobile`),
36 INDEX `CUSTOMERS_AUTHOR_idx` (`created_by` ASC),
37 INDEX `CUSTOMERS_EDITOR_idx` (`updated_by` ASC),
38 CONSTRAINT `CUSTOMERS_AUTHOR`
39 FOREIGN KEY (`created_by`)
40 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
41 ON DELETE NO ACTION
42 ON UPDATE NO ACTION,
43 CONSTRAINT `CUSTOMERS_EDITOR`
44 FOREIGN KEY (`updated_by`)
45 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
46 ON DELETE NO ACTION
47 ON UPDATE NO ACTION)
48ENGINE = InnoDB;
49
50CREATE TABLE IF NOT EXISTS `matunda_db`.`setting` (
51 `id` INT(11) NOT NULL AUTO_INCREMENT,
52 `type` VARCHAR(10) CHARACTER SET 'utf8' NOT NULL,
53 `section` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
54 `key` VARCHAR(255) CHARACTER SET 'utf8' NOT NULL,
55 `value` TEXT CHARACTER SET 'utf8' NOT NULL,
56 `status` SMALLINT(6) NOT NULL DEFAULT '1',
57 `createdAt` INT(11) NOT NULL,
58 `updatedAt` INT(11) NOT NULL,
59 `description` VARCHAR(255) CHARACTER SET 'utf8' NULL DEFAULT NULL,
60 PRIMARY KEY (`id`))
61ENGINE = InnoDB
62
63CREATE TABLE IF NOT EXISTS `matunda_db`.`POSes` (
64 `serial` VARCHAR(100) NOT NULL,
65 `mobile` INT NOT NULL,
66 `simcard` VARCHAR(45) NULL,
67 `owner` VARCHAR(45) NOT NULL,
68 `created_at` INT NOT NULL,
69 `created_by` INT NOT NULL,
70 `updated_at` INT NOT NULL,
71 `updated_by` INT NOT NULL,
72 `is_active` INT NOT NULL DEFAULT 0,
73 PRIMARY KEY (`serial`),
74 INDEX `POS_AUTHOR_idx` (`created_by` ASC),
75 INDEX `POS_EDITOR_idx` (`updated_by` ASC),
76 CONSTRAINT `POS_AUTHOR`
77 FOREIGN KEY (`created_by`)
78 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
79 ON DELETE NO ACTION
80 ON UPDATE NO ACTION,
81 CONSTRAINT `POS_EDITOR`
82 FOREIGN KEY (`updated_by`)
83 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
84 ON DELETE NO ACTION
85 ON UPDATE NO ACTION)
86ENGINE = InnoDB;
87
88CREATE TABLE IF NOT EXISTS `matunda_db`.`Buses` (
89 `regno` VARCHAR(20) NOT NULL,
90 `leftseats` INT NOT NULL,
91 `rightseats` INT NOT NULL,
92 `backseats` VARCHAR(45) NOT NULL,
93 `doorside` VARCHAR(1) NOT NULL DEFAULT 'r',
94 `driver` INT NOT NULL,
95 `total_seats` INT NOT NULL,
96 `created_at` INT NOT NULL,
97 `created_by` INT NOT NULL,
98 `updated_at` INT NOT NULL,
99 `updated_by` INT NOT NULL,
100 PRIMARY KEY (`regno`),
101 INDEX `BUS_AUTHOR_idx` (`created_by` ASC),
102 INDEX `BUS_EDITOR_idx` (`updated_by` ASC),
103 CONSTRAINT `BUS_AUTHOR`
104 FOREIGN KEY (`created_by`)
105 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
106 ON DELETE NO ACTION
107 ON UPDATE NO ACTION,
108 CONSTRAINT `BUS_EDITOR`
109 FOREIGN KEY (`updated_by`)
110 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
111 ON DELETE NO ACTION
112 ON UPDATE NO ACTION)
113ENGINE = InnoDB;
114
115CREATE TABLE IF NOT EXISTS `matunda_db`.`Buses` (
116 `regno` VARCHAR(20) NOT NULL,
117 `leftseats` INT NOT NULL,
118 `rightseats` INT NOT NULL,
119 `backseats` VARCHAR(45) NOT NULL,
120 `doorside` VARCHAR(1) NOT NULL DEFAULT 'r',
121 `driver` INT NOT NULL,
122 `total_seats` INT NOT NULL,
123 `created_at` INT NOT NULL,
124 `created_by` INT NOT NULL,
125 `updated_at` INT NOT NULL,
126 `updated_by` INT NOT NULL,
127 PRIMARY KEY (`regno`),
128 INDEX `BUS_AUTHOR_idx` (`created_by` ASC),
129 INDEX `BUS_EDITOR_idx` (`updated_by` ASC),
130 CONSTRAINT `BUS_AUTHOR`
131 FOREIGN KEY (`created_by`)
132 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION,
135 CONSTRAINT `BUS_EDITOR`
136 FOREIGN KEY (`updated_by`)
137 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION)
140ENGINE = InnoDB;
141
142CREATE TABLE IF NOT EXISTS `matunda_db`.`Buses` (
143 `regno` VARCHAR(20) NOT NULL,
144 `leftseats` INT NOT NULL,
145 `rightseats` INT NOT NULL,
146 `backseats` VARCHAR(45) NOT NULL,
147 `doorside` VARCHAR(1) NOT NULL DEFAULT 'r',
148 `driver` INT NOT NULL,
149 `total_seats` INT NOT NULL,
150 `created_at` INT NOT NULL,
151 `created_by` INT NOT NULL,
152 `updated_at` INT NOT NULL,
153 `updated_by` INT NOT NULL,
154 PRIMARY KEY (`regno`),
155 INDEX `BUS_AUTHOR_idx` (`created_by` ASC),
156 INDEX `BUS_EDITOR_idx` (`updated_by` ASC),
157 CONSTRAINT `BUS_AUTHOR`
158 FOREIGN KEY (`created_by`)
159 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
160 ON DELETE NO ACTION
161 ON UPDATE NO ACTION,
162 CONSTRAINT `BUS_EDITOR`
163 FOREIGN KEY (`updated_by`)
164 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION)
167ENGINE = InnoDB;