· 6 years ago · Jun 09, 2019, 01:04 PM
1██████╗ ███████╗███╗ ███╗ ██████╗ ███╗ ██╗███████╗ █████╗ ██████╗
2██╔══██╗██╔════╝████╗ ████║██╔═══██╗████╗ ██║██╔════╝██╔══██╗██╔══██╗
3██║ ██║█████╗ ██╔████╔██║██║ ██║██╔██╗ ██║███████╗███████║██║ ██║
4██║ ██║██╔══╝ ██║╚██╔╝██║██║ ██║██║╚██╗██║╚════██║██╔══██║██║ ██║
5██████╔╝███████╗██║ ╚═╝ ██║╚██████╔╝██║ ╚████║███████║██║ ██║██████╔╝
6╚═════╝ ╚══════╝╚═╝ ╚═╝ ╚═════╝ ╚═╝ ╚═══╝╚══════╝╚═╝ ╚═╝╚═════╝
7
8I am DemonSad <3
9
10Target : http://www.siepe.educacao.pe.gov.br/ ( Sistema De Informações Da Educação De Pernambuco )
11
12Greetings: TioSan, Angel, Cloudmore, NedLock
13
14-- ------------------------------------------------------------------------------------------------
15"Good Vibes!"
16---------------------------------------------------------------------------------------------------
17---------------------------------------------------------------------------------
18CREATE TABLE IF NOT EXISTS `agentes` (
19 `agentid` int(11) NOT NULL default '0',
20 `name` varchar(100) NOT NULL default '',
21 `agentpassword` varchar(50) NOT NULL default '',
22 `horario` int(11) NOT NULL default '0',
23 PRIMARY KEY (`agentid`)
24) ENGINE=InnoDB DEFAULT CHARSET=utf8;
25
26CREATE TABLE IF NOT EXISTS expr_alias (
27 `aliasid` INTEGER PRIMARY KEY AUTO_INCREMENT,
28 `name` VARCHAR(60) NOT NULL
29) ENGINE=InnoDB DEFAULT CHARSET=utf8;
30
31CREATE TABLE IF NOT EXISTS expr_alias_expression (
32 `aliasid` INTEGER NOT NULL,
33 `expression` VARCHAR(200) NOT NULL,
34 FOREIGN KEY (`aliasid`) REFERENCES expr_alias(`aliasid`) ON DELETE CASCADE
35) ENGINE=InnoDB DEFAULT CHARSET=utf8;
36
37CREATE TABLE IF NOT EXISTS regras_negocio (
38 id integer PRIMARY KEY auto_increment,
39 prio integer NOT NULL default 0,
40 `desc` varchar(255) default NULL,
41 origem text NOT NULL,
42 destino text NOT NULL,
43 validade text NOT NULL,
44 diasDaSemana varchar(30) NOT NULL DEFAULT "sun,mon,tue,wed,thu,fri,sat",
45 record boolean NOT NULL default false,
46 ativa boolean NOT NULL default true
47) ENGINE=InnoDB DEFAULT CHARSET=utf8;
48
49CREATE TABLE IF NOT EXISTS regras_negocio_actions (
50 regra_id integer NOT NULL,
51 prio integer NOT NULL,
52 `action` varchar(250) NOT NULL,
53 PRIMARY KEY(regra_id, prio),
54 FOREIGN KEY (regra_id) REFERENCES regras_negocio(id) ON UPDATE CASCADE ON DELETE CASCADE
55) ENGINE=InnoDB DEFAULT CHARSET=utf8;
56
57CREATE TABLE IF NOT EXISTS regras_negocio_actions_config (
58 regra_id integer NOT NULL,
59 prio integer NOT NULL,
60 `key` varchar(255) NOT NULL,
61 `value` text NOT NULL,
62 PRIMARY KEY(regra_id,prio,`key`),
63 FOREIGN KEY (regra_id, prio) REFERENCES regras_negocio_actions (regra_id, prio) ON UPDATE CASCADE ON DELETE CASCADE
64) ENGINE=InnoDB DEFAULT CHARSET=utf8;
65
66CREATE TABLE IF NOT EXISTS `registry` (
67 `context` VARCHAR(50),
68 `key` VARCHAR(30),
69 `value` VARCHAR(250),
70 PRIMARY KEY (`context`,`key`)
71) ENGINE=InnoDB DEFAULT CHARSET=utf8;
72
73CREATE TABLE IF NOT EXISTS `ccustos` (
74 `codigo` char(7) NOT NULL,
75 `tipo` char(1) NOT NULL,
76 `nome` varchar(40) NOT NULL,
77 `descricao` varchar(250) default NULL,
78 PRIMARY KEY (`codigo`)
79) ENGINE=InnoDB DEFAULT CHARSET=utf8;
80
81CREATE TABLE IF NOT EXISTS `cdr` (
82 `calldate` datetime NOT NULL default '0000-00-00 00:00:00',
83 `clid` varchar(80) NOT NULL default '',
84 `src` varchar(80) NOT NULL default '',
85 `dst` varchar(80) NOT NULL default '',
86 `dcontext` varchar(80) NOT NULL default '',
87 `channel` varchar(80) NOT NULL default '',
88 `dstchannel` varchar(80) NOT NULL default '',
89 `lastapp` varchar(80) NOT NULL default '',
90 `lastdata` varchar(80) NOT NULL default '',
91 `duration` int(11) NOT NULL default '0',
92 `billsec` int(11) NOT NULL default '0',
93 `disposition` varchar(45) NOT NULL default '',
94 `amaflags` int(20) NOT NULL default '0',
95 `accountcode` varchar(20) NOT NULL default '',
96 `uniqueid` varchar(32) NOT NULL default '',
97 `userfield` varchar(255) NOT NULL default '',
98 KEY `calldate` (`calldate`),
99 KEY `dst` (`dst`),
100 KEY `accountcode` (`accountcode`)
101) ENGINE=InnoDB DEFAULT CHARSET=utf8;
102
103CREATE TABLE IF NOT EXISTS `cdr_compactado` (
104 `userfield` varchar(255) default NULL,
105 `arquivo` varchar(255) default NULL,
106 `data` date default NULL
107) ENGINE=InnoDB DEFAULT CHARSET=utf8;
108
109CREATE TABLE IF NOT EXISTS `events` (
110 `id` int(10) unsigned NOT NULL auto_increment,
111 `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
112 `event` longtext,
113 PRIMARY KEY (`id`)
114) ENGINE=InnoDB DEFAULT CHARSET=utf8;
115
116CREATE TABLE IF NOT EXISTS `grupos` (
117 `cod_grupo` integer NOT NULL auto_increment,
118 `nome` varchar(30) NOT NULL,
119 UNIQUE KEY `cod_grupo` (`cod_grupo`)
120) ENGINE=InnoDB DEFAULT CHARSET=utf8;
121
122CREATE TABLE IF NOT EXISTS `oper_ccustos` (
123 `operadora` int(11) NOT NULL,
124 `ccustos` char(7) NOT NULL,
125 PRIMARY KEY (`operadora`,`ccustos`)
126) ENGINE=InnoDB DEFAULT CHARSET=utf8;
127
128CREATE TABLE IF NOT EXISTS `oper_contas` (
129 `operadora` int(11) NOT NULL,
130 `conta` int(11) NOT NULL,
131 PRIMARY KEY (`operadora`,`conta`)
132) ENGINE=InnoDB DEFAULT CHARSET=utf8;
133
134CREATE TABLE IF NOT EXISTS `operadoras` (
135 `codigo` bigint(20) unsigned NOT NULL auto_increment,
136 `nome` varchar(50) NOT NULL,
137 `tpm` int(11) default '0',
138 `tdm` int(11) default '0',
139 `tbf` float default '0',
140 `tbc` float default '0',
141 `vpf` float NOT NULL default '0',
142 `vpc` float NOT NULL default '0',
143 PRIMARY KEY (`codigo`)
144) ENGINE=InnoDB DEFAULT CHARSET=utf8;
145
146
147CREATE TABLE IF NOT EXISTS groups (
148 name varchar(50) PRIMARY KEY,
149 inherit varchar(50),
150 FOREIGN KEY (inherit) REFERENCES groups(name) ON UPDATE CASCADE
151)ENGINE=InnoDB DEFAULT CHARSET=utf8;
152
153CREATE TABLE IF NOT EXISTS `peers` (
154 `id` int(11) NOT NULL auto_increment,
155 `name` varchar(80) NOT NULL default '',
156 `password` VARCHAR(12) NOT NULL,
157 `accountcode` varchar(20) default NULL,
158 `amaflags` varchar(13) default NULL,
159 `callgroup` varchar(10) default NULL,
160 `callerid` varchar(80) default NULL,
161 `canreinvite` char(3) default 'no',
162 `context` varchar(80) default NULL,
163 `defaultip` varchar(15) default NULL,
164 `dtmfmode` varchar(7) default NULL,
165 `fromuser` varchar(80) default NULL,
166 `fromdomain` varchar(80) default NULL,
167 `fullcontact` varchar(80) default NULL,
168 `host` varchar(31) NOT NULL default '',
169 `insecure` varchar(4) default NULL,
170 `language` char(2) default 'br',
171 `mailbox` varchar(50) default NULL,
172 `md5secret` varchar(80) default '',
173 `nat` varchar(5) NOT NULL default 'no',
174 `deny` varchar(95) default NULL,
175 `permit` varchar(95) default NULL,
176 `mask` varchar(95) default NULL,
177 `pickupgroup` integer default NULL,
178 `port` varchar(5) NOT NULL default '',
179 `qualify` char(5) default NULL,
180 `restrictcid` char(1) default NULL,
181 `rtptimeout` char(3) default NULL,
182 `rtpholdtimeout` char(3) default NULL,
183 `secret` varchar(80) default NULL,
184 `type` varchar(6) NOT NULL default 'friend',
185 `username` varchar(80) NOT NULL default '',
186 `disallow` varchar(100) default 'all',
187 `allow` varchar(100) default 'ulaw;alaw;gsm',
188 `musiconhold` varchar(100) default NULL,
189 `regseconds` int(11) NOT NULL default '0',
190 `ipaddr` varchar(15) NOT NULL default '',
191 `regexten` varchar(80) NOT NULL default '',
192 `cancallforward` char(3) default 'yes',
193 `setvar` varchar(100) NOT NULL default '',
194 `vinculo` varchar(255) NOT NULL default '',
195 `email` varchar(255) default NULL,
196 `canal` varchar(255) default NULL,
197 `call-limit` varchar(4) default NULL,
198 `incominglimit` varchar(4) default NULL,
199 `outgoinglimit` varchar(4) default NULL,
200 `usa_vc` varchar(4) NOT NULL default 'no',
201 `peer_type` char(1) NOT NULL default 'R',
202 `credits` int(11) default NULL,
203 `authenticate` boolean not null default false,
204 `subscribecontext` varchar(40) default NULL,
205 `trunk` varchar(3) NOT NULL,
206 `group` varchar(50) NOT NULL DEFAULT 'users',
207 `time_total` int(11) default NULL,
208 `time_chargeby` char(1) default NULL,
209 `regserver` int(250) default NULL,
210 `dnd` BOOL NOT NULL DEFAULT '0',
211 `sigame` VARCHAR( 20 ) NULL ,
212 PRIMARY KEY (`id`),
213 UNIQUE KEY `name` (`name`),
214 KEY `name_2` (`name`),
215 FOREIGN KEY (`group`) REFERENCES groups(`name`) ON UPDATE CASCADE ON DELETE RESTRICT,
216 FOREIGN KEY (`pickupgroup`) REFERENCES grupos(`cod_grupo`) ON UPDATE CASCADE ON DELETE SET NULL
217) ENGINE=InnoDB DEFAULT CHARSET=utf8;
218
219CREATE TABLE IF NOT EXISTS `services_log` (
220 `date` datetime NOT NULL,
221 `peer` varchar(80) NOT NULL,
222 `service` varchar(50) NOT NULL,
223 `state` tinyint(1) NOT NULL,
224 `status` varchar(255) NOT NULL
225) ENGINE=InnoDB DEFAULT CHARSET=utf8;
226
227-- CREATE TABLE IF NOT EXISTS `permissoes` (
228-- `cod_rotina` int(11) NOT NULL default '0',
229-- `cod_usuario` int(11) NOT NULL default '0',
230-- `permissao` char(1) NOT NULL default 'S',
231-- PRIMARY KEY (`cod_rotina`,`cod_usuario`)
232-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
233
234CREATE TABLE IF NOT EXISTS `queue_log` (
235 `time` datetime NOT NULL default '0000-00-00 00:00:00',
236 `callid` varchar(20) NOT NULL default '',
237 `queuename` varchar(20) NOT NULL default '',
238 `agent` varchar(20) NOT NULL default '',
239 `event` varchar(20) NOT NULL default '',
240 `arg1` varchar(100) NOT NULL default '',
241 `arg2` varchar(100) NOT NULL default '',
242 `arg3` varchar(100) NOT NULL default ''
243) ENGINE=InnoDB DEFAULT CHARSET=utf8;
244
245CREATE TABLE IF NOT EXISTS `queue_members` (
246 `uniqueid` int(10) unsigned NOT NULL auto_increment,
247 `membername` varchar(40) default NULL,
248 `queue_name` varchar(128) default NULL,
249 `interface` varchar(128) default NULL,
250 `penalty` int(11) default NULL,
251 `paused` tinyint(1) default NULL,
252 PRIMARY KEY (`uniqueid`),
253 UNIQUE KEY `queue_interface` (`queue_name`,`interface`)
254) ENGINE=InnoDB DEFAULT CHARSET=utf8;
255
256CREATE TABLE `queue_peers` (
257 `fila` varchar(80) NOT NULL default '',
258 `ramal` int(11) NOT NULL,
259 PRIMARY KEY (`ramal`,`fila`)
260) ENGINE=InnoDB DEFAULT CHARSET=utf8;
261
262CREATE TABLE IF NOT EXISTS `queues` (
263 `name` varchar(128) NOT NULL,
264 `musiconhold` varchar(128) default NULL,
265 `announce` varchar(128) default NULL,
266 `context` varchar(128) default NULL,
267 `timeout` int(11) default NULL,
268 `monitor_type` tinyint(1) default NULL,
269 `monitor_format` varchar(128) default NULL,
270 `queue_youarenext` varchar(128) default NULL,
271 `queue_thereare` varchar(128) default NULL,
272 `queue_callswaiting` varchar(128) default NULL,
273 `queue_holdtime` varchar(128) default NULL,
274 `queue_minutes` varchar(128) default NULL,
275 `queue_seconds` varchar(128) default NULL,
276 `queue_lessthan` varchar(128) default NULL,
277 `queue_thankyou` varchar(128) default NULL,
278 `queue_reporthold` varchar(128) default NULL,
279 `announce_frequency` int(11) default NULL,
280 `announce_round_seconds` int(11) default NULL,
281 `announce_holdtime` varchar(128) default NULL,
282 `retry` int(11) default NULL,
283 `wrapuptime` int(11) default NULL,
284 `maxlen` int(11) default NULL,
285 `servicelevel` int(11) default NULL,
286 `strategy` varchar(128) default NULL,
287 `joinempty` varchar(128) default NULL,
288 `leavewhenempty` varchar(128) default NULL,
289 `eventmemberstatus` tinyint(1) default NULL,
290 `eventwhencalled` tinyint(1) default NULL,
291 `reportholdtime` tinyint(1) default NULL,
292 `memberdelay` int(11) default NULL,
293 `weight` int(11) default NULL,
294 `timeoutrestart` tinyint(1) default NULL,
295 `periodic_announce` varchar(50) default NULL,
296 `periodic_announce_frequency` int(11) default NULL,
297 `max_call_queue` int(11) default '0',
298 `max_time_call` int(11) default '0',
299 `alert_mail` varchar(80) default NULL,
300 PRIMARY KEY (`name`)
301) ENGINE=InnoDB DEFAULT CHARSET=utf8;
302
303CREATE TABLE IF NOT EXISTS `queues_agent` (
304 `agent_id` int(11) NOT NULL,
305 `queue` varchar(80) NOT NULL
306) ENGINE=InnoDB DEFAULT CHARSET=utf8;
307
308-- CREATE TABLE IF NOT EXISTS `rotinas` (
309-- `cod_rotina` int(11) NOT NULL default '0',
310-- `desc_rotina` varchar(50) NOT NULL default '',
311-- PRIMARY KEY (`cod_rotina`)
312-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
313
314CREATE TABLE IF NOT EXISTS `sounds` (
315 `arquivo` varchar(50) NOT NULL,
316 `descricao` varchar(80) NOT NULL,
317 `data` datetime default NULL,
318 `tipo` char(3) NOT NULL default 'AST',
319 `secao` varchar(30) NOT NULL,
320 PRIMARY KEY (`arquivo`,`tipo`,`secao`)
321) ENGINE=InnoDB DEFAULT CHARSET=utf8;
322
323CREATE TABLE IF NOT EXISTS `tarifas` (
324 `operadora` int(11) NOT NULL default '0',
325 `ddi` smallint(6) NOT NULL default '0',
326 `pais` varchar(30) NOT NULL default '',
327 `ddd` smallint(6) NOT NULL default '0',
328 `cidade` varchar(30) NOT NULL default '',
329 `estado` char(2) NOT NULL default '',
330 `prefixo` varchar(6) NOT NULL default '',
331 `codigo` int(11) NOT NULL auto_increment,
332 PRIMARY KEY (`codigo`),
333 UNIQUE KEY `operadora` (`operadora`,`ddi`,`ddd`,`prefixo`,`cidade`)
334) ENGINE=InnoDB DEFAULT CHARSET=utf8;
335
336CREATE TABLE IF NOT EXISTS `tarifas_valores` (
337 `codigo` int(11) NOT NULL,
338 `data` datetime NOT NULL,
339 `vcel` float NOT NULL default '0',
340 `vfix` float NOT NULL default '0',
341 `vpf` float NOT NULL default '0',
342 `vpc` float NOT NULL default '0',
343 PRIMARY KEY (`codigo`,`data`)
344) ENGINE=InnoDB DEFAULT CHARSET=utf8;
345
346CREATE TABLE IF NOT EXISTS `trunks` (
347 `id` int(11) NOT NULL auto_increment,
348 `name` varchar(80) NOT NULL default '',
349 `accountcode` varchar(20) default NULL,
350 `callerid` varchar(80) default NULL,
351 `context` varchar(80) default NULL,
352 `dtmfmode` varchar(7) default NULL,
353 `insecure` varchar(20) default NULL,
354 `secret` varchar(80) default NULL,
355 `username` varchar(80) default NULL,
356 `allow` varchar(100) default 'g729;ilbc;gsm;ulaw;alaw',
357 `channel` varchar(255) default NULL,
358 `type` varchar(200) default NULL,
359 `trunktype` char(1) NOT NULL,
360 `host` varchar(31) default NULL,
361 `trunk_redund` int(11) default NULL,
362 `time_total` int(11) default NULL,
363 `time_chargeby` char(1) default NULL,
364 `dialmethod` VARCHAR(6) NOT NULL DEFAULT 'NORMAL',
365 `id_regex` VARCHAR(255) NULL,
366 `map_extensions` BOOLEAN DEFAULT FALSE,
367 `reverse_auth` BOOLEAN DEFAULT TRUE,
368 `dtmf_dial` BOOLEAN NOT NULL DEFAULT FALSE,
369 `dtmf_dial_number` VARCHAR(50) DEFAULT NULL,
370 `domain` VARCHAR( 250 ) NOT NULL,
371 PRIMARY KEY (`id`),
372 UNIQUE KEY `name` (`name`)
373) ENGINE=InnoDB DEFAULT CHARSET=utf8;
374
375CREATE TABLE IF NOT EXISTS `time_history` (
376 `id` integer NOT NULL auto_increment,
377 `owner` integer NOT NULL,
378 `year` integer NOT NULL,
379 `month` integer,
380 `day` integer,
381 `used` integer NOT NULL default '0',
382 `changed` timestamp NOT NULL default CURRENT_TIMESTAMP,
383 `owner_type` char(1) NOT NULL default 'T',
384 PRIMARY KEY (`id`)
385) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
386
387-- CREATE TABLE IF NOT EXISTS `vinculos` (
388-- `ramal` varchar(80) default NULL,
389-- `cod_usuario` varchar(80) default NULL
390-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
391
392
393CREATE TABLE IF NOT EXISTS `voicemail_messages` (
394 `id` int(11) NOT NULL auto_increment,
395 `msgnum` int(11) NOT NULL default '0',
396 `dir` varchar(80) default '',
397 `context` varchar(80) default '',
398 `macrocontext` varchar(80) default '',
399 `callerid` varchar(40) default '',
400 `origtime` varchar(40) default '',
401 `duration` varchar(20) default '',
402 `mailboxuser` varchar(80) default '',
403 `mailboxcontext` varchar(80) default '',
404 `recording` longblob,
405 PRIMARY KEY (`id`),
406 KEY `dir` (`dir`)
407) ENGINE=InnoDB DEFAULT CHARSET=utf8;
408
409
410CREATE TABLE IF NOT EXISTS `voicemail_users` (
411 `uniqueid` int(11) NOT NULL auto_increment,
412 `customer_id` varchar(11) NOT NULL default '0',
413 `context` varchar(50) default '',
414 `mailbox` varchar(11) NOT NULL default '0',
415 `password` varchar(5) NOT NULL default '0',
416 `fullname` varchar(150) NOT NULL default '',
417 `email` varchar(50) NOT NULL default '',
418 `pager` varchar(50) default '',
419 `tz` varchar(10) NOT NULL default 'central24',
420 `attach` varchar(4) NOT NULL default 'yes',
421 `saycid` varchar(4) NOT NULL default 'yes',
422 `dialout` varchar(10) default '',
423 `callback` varchar(10) default '',
424 `review` varchar(4) NOT NULL default 'no',
425 `operator` varchar(4) NOT NULL default 'no',
426 `envelope` varchar(4) NOT NULL default 'no',
427 `sayduration` varchar(4) NOT NULL default 'no',
428 `saydurationm` tinyint(4) NOT NULL default '1',
429 `sendvoicemail` varchar(4) NOT NULL default 'no',
430 `delete` varchar(4) NOT NULL default 'no',
431 `nextaftercmd` varchar(4) NOT NULL default 'yes',
432 `forcename` varchar(4) NOT NULL default 'no',
433 `forcegreetings` varchar(4) NOT NULL default 'no',
434 `hidefromdir` varchar(4) NOT NULL default 'yes',
435 `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
436 PRIMARY KEY (`uniqueid`),
437 KEY `mailbox_context` (`mailbox`,`context`)
438) ENGINE=InnoDB DEFAULT CHARSET=utf8;
439
440CREATE TABLE IF NOT EXISTS `lista_abandono` (
441 `time` int(20) NOT NULL,
442 `data` varchar(150) NOT NULL,
443 `fila` varchar(150) NOT NULL,
444 `canal` varchar(150) NOT NULL,
445 `evento` varchar(150) NOT NULL,
446 `par1` varchar(30) NOT NULL,
447 `par2` varchar(30) NOT NULL,
448 `par3` varchar(30) NOT NULL,
449 `date` datetime NOT NULL,
450 `id` int(11) NOT NULL AUTO_INCREMENT ,
451 PRIMARY KEY (`id`)
452) ENGINE=InnoDB DEFAULT CHARSET=utf8;
453
454-- CREATE TABLE IF NOT EXISTS `permissoes_vinculos` (
455-- `id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
456-- `id_peer` VARCHAR( 100 ) NOT NULL ,
457-- `tipo` CHAR( 1 ) NOT NULL,
458-- `id_vinculado` VARCHAR( 100 ) NOT NULL
459-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
460
461-- CREATE TABLE IF NOT EXISTS `alertas` (
462-- `recurso` VARCHAR( 20 ) NOT NULL ,
463-- `tipo` VARCHAR( 10 ) NOT NULL ,
464-- `tme` INT( 10 ) NOT NULL ,
465-- `sla` INT( 10 ) NOT NULL ,
466-- `item` VARCHAR( 20 ) NOT NULL ,
467-- `alerta` VARCHAR( 255 ) NOT NULL ,
468-- `destino` VARCHAR( 255 ) NOT NULL ,
469-- `ativo` TINYINT( 1 ) NOT NULL
470-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
471
472CREATE TABLE IF NOT EXISTS ars_operadora (
473 `id` integer primary key auto_increment,
474 `name` varchar(30) not null
475) ENGINE=InnoDB DEFAULT CHARSET=utf8;
476
477CREATE TABLE IF NOT EXISTS ars_estado (
478 `cod` char(2) primary key,
479 `name` varchar(30) not null
480) ENGINE=InnoDB DEFAULT CHARSET=utf8;
481
482CREATE TABLE IF NOT EXISTS ars_cidade (
483 `id` integer primary key auto_increment,
484 `name` varchar(30) not null
485) ENGINE=InnoDB DEFAULT CHARSET=utf8;
486
487CREATE TABLE IF NOT EXISTS ars_ddd (
488 `cod` char(2),
489 `estado` char(2),
490 `cidade` integer,
491 primary key (`cod`,`estado`,`cidade`),
492 foreign key (`estado`) references ars_estado(`cod`) on update cascade on delete restrict,
493 foreign key (`cidade`) references ars_cidade(`id`) on update cascade on delete restrict
494) ENGINE=InnoDB DEFAULT CHARSET=utf8;
495
496CREATE TABLE IF NOT EXISTS ars_prefixo (
497 `prefixo` integer,
498 `cidade` integer,
499 `operadora` integer,
500 primary key (`prefixo`,`cidade`,`operadora`),
501 foreign key (`operadora`) references ars_operadora(`id`) on update cascade on delete restrict,
502 foreign key (`cidade`) references ars_cidade(`id`) on update cascade on delete restrict
503) ENGINE=InnoDB DEFAULT CHARSET=utf8;
504
505--
506-- Estrutura da tabela `contacts_group`
507--
508CREATE TABLE IF NOT EXISTS `contacts_group` (
509 `id` integer NOT NULL AUTO_INCREMENT,
510 `name` varchar(20) NOT NULL,
511 PRIMARY KEY (`id`)
512) ENGINE=InnoDB DEFAULT CHARSET=utf8;
513
514--
515-- Estrutura da tabela `contacts_names`
516--
517CREATE TABLE IF NOT EXISTS `contacts_names` (
518 `id` char(11) NOT NULL,
519 `name` varchar(80) NOT NULL,
520 `address` varchar(100) NOT NULL,
521 `city` varchar(50) NOT NULL,
522 `state` varchar(2) NOT NULL,
523 `cep` varchar(8) NOT NULL,
524 `phone_1` varchar(15) NOT NULL,
525 `cell_1` varchar(15) NOT NULL,
526 `group` integer NOT NULL,
527 CONSTRAINT contacts_group_fk FOREIGN KEY (`group`) REFERENCES contacts_group(`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
528 PRIMARY KEY (`id`)
529) ENGINE=InnoDB DEFAULT CHARSET=utf8;
530
531
532-- NOVA ESTRUTURA ADICIONADA / TIAGO
533
534--
535-- Estrutura da tabela `profiles`
536--
537CREATE TABLE IF NOT EXISTS `profiles` (
538 `id` int(11) NOT NULL AUTO_INCREMENT,
539 `name` varchar(255) NOT NULL,
540 `created` datetime NOT NULL,
541 `updated` datetime NOT NULL,
542 PRIMARY KEY (`id`)
543) ENGINE=InnoDB DEFAULT CHARSET=utf8;
544
545
546--
547-- Estrutura da tabela `permissions`
548--
549CREATE TABLE IF NOT EXISTS `permissions` (
550 `id` int(11) NOT NULL,
551 `name` VARCHAR(255) NOT NULL,
552 `created` datetime NOT NULL,
553 `updated` datetime NOT NULL,
554 PRIMARY KEY (`id`)
555) ENGINE=InnoDB DEFAULT CHARSET=utf8;
556
557
558--
559-- Estrutura da tabela `users`
560--
561CREATE TABLE IF NOT EXISTS `users` (
562 `id` int(11) NOT NULL AUTO_INCREMENT,
563 `name` VARCHAR(45) NOT NULL,
564 `password` VARCHAR(45) NOT NULL,
565 `email` VARCHAR(255) NOT NULL,
566 `profile_id` INT NOT NULL,
567 `created` datetime NOT NULL,
568 `updated` datetime NOT NULL,
569 PRIMARY KEY (`id`),
570 KEY `profile_id` (`profile_id`),
571 CONSTRAINT `fk_user_profile` FOREIGN KEY (`profile_id` ) REFERENCES `profiles` (`id`)
572) ENGINE=InnoDB DEFAULT CHARSET=utf8;
573
574--
575-- Estrutura da tabela `profiles_permissions`
576--
577CREATE TABLE IF NOT EXISTS `profiles_permissions` (
578 `id` int(11) NOT NULL AUTO_INCREMENT,
579 `permission_id` varchar(256) NOT NULL,
580 `profile_id` INT NOT NULL,
581 `allow` tinyint(1) NOT NULL default '0',
582 `created` datetime NOT NULL,
583 `updated` datetime NOT NULL,
584 PRIMARY KEY (`id`),
585 KEY `profile_id` (`profile_id`),
586 CONSTRAINT `fk_user_profiles` FOREIGN KEY (`profile_id` ) REFERENCES `profiles` (`id`)
587) ENGINE=InnoDB DEFAULT CHARSET=utf8;
588
589--
590-- Estrutura da tabela `users_permissions`
591--
592CREATE TABLE IF NOT EXISTS `users_permissions` (
593 `id` int(11) NOT NULL AUTO_INCREMENT,
594 `user_id` int(11) NOT NULL,
595 `permission_id` varchar(256) NOT NULL,
596 `allow` tinyint(1) NOT NULL default '0',
597 `created` datetime NOT NULL,
598 `updated` datetime NOT NULL,
599 PRIMARY KEY (`id`),
600 KEY `user_id` (`user_id`),
601 CONSTRAINT `fk_user_users` FOREIGN KEY (`user_id` ) REFERENCES `users` (`id`)
602) ENGINE=InnoDB DEFAULT CHARSET=utf8;
603
604--
605-- Estrutura da tabela `binds`
606--
607CREATE TABLE IF NOT EXISTS `binds` (
608 `id` int(11) NOT NULL AUTO_INCREMENT,
609 `peer_id` int(11) NOT NULL,
610 `user_id` int(11) NOT NULL,
611 `created` datetime NOT NULL,
612 `updated` datetime NOT NULL,
613 PRIMARY KEY (`id`),
614 KEY `peer_id` (`peer_id`),
615 CONSTRAINT `binds_refs_peer_id` FOREIGN KEY (`peer_id`) REFERENCES `peers` (`id`),
616 KEY `user_id` (`user_id`),
617 CONSTRAINT `binds_peer_refs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
618) ENGINE=InnoDB DEFAULT CHARSET=utf8;
619
620--
621-- Estrutura da tabela `password_recovery`
622--
623CREATE TABLE IF NOT EXISTS `password_recovery` (
624 `id` int(11) NOT NULL AUTO_INCREMENT,
625 `user_id` int(11) NOT NULL,
626 `code` varchar(10) NOT NULL,
627 `created` datetime NOT NULL,
628 `expiration` datetime NOT NULL,
629 PRIMARY KEY (`id`),
630 KEY `user_id` (`user_id`),
631 CONSTRAINT `password_recovery_refs_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
632) ENGINE=InnoDB DEFAULT CHARSET=utf8;