-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB_SCHEMA.sql
executable file
·111 lines (98 loc) · 3.56 KB
/
DB_SCHEMA.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
PRAGMA foreign_keys = ON;
-- -----------------------------------------------------
-- `CATEGORIA`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `CATEGORIA`;
CREATE TABLE IF NOT EXISTS `CATEGORIA` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT,
`NOMBRE` VARCHAR(45) NULL);
-- -----------------------------------------------------
-- `ESPECTACULO`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ESPECTACULO`;
CREATE TABLE IF NOT EXISTS `ESPECTACULO` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT,
`TITULO` VARCHAR(256) NULL,
`DESCRIPCION` TEXT NULL,
`CATEGORIA_ID` INT NOT NULL,
FOREIGN KEY (`CATEGORIA_ID`) REFERENCES `CATEGORIA` (`ID`));
-- -----------------------------------------------------
-- `ESPACIO`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ESPACIO`;
CREATE TABLE IF NOT EXISTS `ESPACIO` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT,
`NOMBRE` VARCHAR(128) NULL,
`AFORO` INT NULL,
`TELEFONO` VARCHAR(45) NULL,
`DIRECCION` VARCHAR(256) NULL,
`EMAIL` VARCHAR(256) NULL,
`LONGITUD` FLOAT NULL,
`LATITUD` FLOAT NULL);
-- -----------------------------------------------------
-- `SESION`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `SESION`;
CREATE TABLE IF NOT EXISTS `SESION` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT,
`FECHA_INICIO` TIMESTAMP NULL,
`FECHA_FINALIZACION` TIMESTAMP NULL,
`PRECIO` INT NULL,
`ENTRADAS_VENDIDAS` INT NULL,
`ESPECTACULO_ID` INT NOT NULL,
`ESPACIO_ID` INT NOT NULL,
CONSTRAINT `fk_SESION_ESPECTACULO1`
FOREIGN KEY (`ESPECTACULO_ID`)
REFERENCES `ESPECTACULO` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_SESION_ESPACIO1`
FOREIGN KEY (`ESPACIO_ID`)
REFERENCES `ESPACIO` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
-- -----------------------------------------------------
-- `USUARIO`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `USUARIO`;
CREATE TABLE IF NOT EXISTS `USUARIO` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT,
`LOGIN` VARCHAR(64) NULL,
`PASSWORD` TEXT NULL,
`NOMBRE` VARCHAR(128) NULL,
`FECHA_ALTA` TIMESTAMP NULL,
`FECHA_ULTIMA_COMPRA` TIMESTAMP NULL,
`TIPO_USUARIO` VARCHAR(64) NULL);
-- -----------------------------------------------------
-- `ENTRADA`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ENTRADA`;
CREATE TABLE IF NOT EXISTS `ENTRADA` (
`ID` INTEGER PRIMARY KEY AUTOINCREMENT,
`PRECIO` INT NULL,
`FILA` INT NULL,
`ASIENTO` INT NULL,
`SESION_ID` INT NOT NULL,
`USUARIO_ID` INT NOT NULL,
CONSTRAINT `fk_ENTRADA_SESION1`
FOREIGN KEY (`SESION_ID`)
REFERENCES `SESION` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_ENTRADA_USUARIO1`
FOREIGN KEY (`USUARIO_ID`)
REFERENCES `USUARIO` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE TRIGGER `ACTUALIZAR_ULTIMA_COMPRA` AFTER INSERT ON `ENTRADA`
BEGIN
UPDATE `USUARIO` SET `FECHA_ULTIMA_COMPRA` = ( strftime('%s', 'now') || '000' ) WHERE `ID` = NEW.`USUARIO_ID`;
END;
CREATE TRIGGER `INCREMENTAR_ENTRADAS_VENDIDAS` AFTER INSERT ON `ENTRADA`
BEGIN
UPDATE `SESION` SET `ENTRADAS_VENDIDAS` = `ENTRADAS_VENDIDAS` + 1 WHERE `ID` = NEW.`SESION_ID`;
END;
CREATE TRIGGER `DECREMENTAR_ENTRADAS_VENDIDAS` AFTER DELETE ON `ENTRADA`
BEGIN
UPDATE `SESION` SET `ENTRADAS_VENDIDAS` = `ENTRADAS_VENDIDAS` - 1 WHERE `ID` = OLD.`SESION_ID`;
END;