· 7 years ago · Jan 02, 2019, 11:12 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4CREATE SCHEMA IF NOT EXISTS `tracking_trucks` DEFAULT CHARACTER SET utf8 COLLATE utf8_estonian_ci ;
5USE `tracking_trucks` ;
6CREATE TABLE IF NOT EXISTS `tracking_trucks`.`producto` (
7 `idproducto` INT(100) AUTO_INCREMENT NOT NULL,
8 `nombre_producto` VARCHAR(45) NULL,
9 `categoria` VARCHAR(45) NULL,
10 PRIMARY KEY (`idproducto`))
11ENGINE = InnoDB;
12CREATE TABLE IF NOT EXISTS `tracking_trucks`.`chofer` (
13 `rut` VARCHAR(50) NOT NULL,
14 `nombre_chofer` VARCHAR(45) NULL,
15 `comentarios` VARCHAR(45) NULL,
16 PRIMARY KEY (`rut`))
17ENGINE = InnoDB;
18CREATE TABLE IF NOT EXISTS `tracking_trucks`.`truck` (
19 `patente` VARCHAR(60) NOT NULL,
20 `chofer_rut` VARCHAR(50) NULL,
21 `marca` VARCHAR(45) NULL,
22 `modelo` VARCHAR(45) NULL,
23 `ano` VARCHAR(45) NULL,
24 `color` VARCHAR(45) NULL,
25 `estado` VARCHAR(45) NULL,
26 `fecha_neumaticos` DATE NULL,
27 `dynafleet` VARCHAR(10) CHARACTER SET 'big5' NULL,
28 `nota` VARCHAR(1000) NULL,
29 `imagen` LONGBLOB NULL,
30 PRIMARY KEY (`patente`),
31 INDEX `fk_truck_chofer1_idx` (`chofer_rut` ASC),
32 CONSTRAINT `fk_truck_chofer1`
33 FOREIGN KEY (`chofer_rut`)
34 REFERENCES `tracking_trucks`.`chofer` (`rut`)
35 ON DELETE SET NULL
36 ON UPDATE CASCADE)
37ENGINE = InnoDB;
38CREATE TABLE IF NOT EXISTS `tracking_trucks`.`flete` (
39 `id_flete` VARCHAR(100) NOT NULL,
40 `truck_patente` VARCHAR(60) NULL,
41 `fecha_salida` DATETIME NULL,
42 `fecha_llegada` DATETIME NULL,
43 `fecha_retorno` DATETIME NULL,
44 `destino` VARCHAR(45) NULL,
45 `viaticos` VARCHAR(45) NULL,
46 `total_gastos` VARCHAR(45) NULL,
47 `lugar_carga` VARCHAR(45) NULL,
48 `lugar_descarga` VARCHAR(45) NULL,
49 `km_salida` VARCHAR(45) NULL,
50 `km_llegada` VARCHAR(45) NULL,
51 `litros_gastados` VARCHAR(45) NULL,
52 `km_recorridos` VARCHAR(45) NULL,
53 `cliente_flete` VARCHAR(45) NULL,
54 `valor_flete` VARCHAR(45) NULL,
55 `abono_flete` VARCHAR(45) NULL,
56 `por_cobrar_flete` VARCHAR(45) NULL,
57 INDEX `fk_viaje_truck1_idx` (`truck_patente` ASC),
58 PRIMARY KEY (`id_flete`),
59 CONSTRAINT `fk_viaje_truck1`
60 FOREIGN KEY (`truck_patente`)
61 REFERENCES `tracking_trucks`.`truck` (`patente`)
62 ON DELETE SET NULL
63 ON UPDATE CASCADE)
64ENGINE = InnoDB;
65CREATE TABLE IF NOT EXISTS `tracking_trucks`.`compra` (
66 `idcompra` VARCHAR(50) NOT NULL,
67 `fecha_compra` DATE NULL,
68 `proveedor` VARCHAR(45) NULL,
69 `flete_id_flete` VARCHAR(100) NULL,
70 PRIMARY KEY (`idcompra`),
71 INDEX `fk_compra_flete1_idx` (`flete_id_flete` ASC),
72 CONSTRAINT `fk_compra_flete1`
73 FOREIGN KEY (`flete_id_flete`)
74 REFERENCES `tracking_trucks`.`flete` (`id_flete`)
75 ON DELETE SET NULL
76 ON UPDATE CASCADE)
77ENGINE = InnoDB;
78CREATE TABLE IF NOT EXISTS `tracking_trucks`.`venta` (
79 `idventa` VARCHAR(50) NOT NULL,
80 `fecha_venta` DATE NULL,
81 `cliente` VARCHAR(45) NULL,
82 PRIMARY KEY (`idventa`))
83ENGINE = InnoDB;
84CREATE TABLE IF NOT EXISTS `tracking_trucks`.`detalle_compra` (
85 `producto_idproducto` INT NOT NULL,
86 `compra_idcompra` VARCHAR(50) NOT NULL,
87 `cantidad_compra` VARCHAR(100) NULL,
88 `preciocompra_unidad` VARCHAR(100) NULL,
89 `valorcompra_total` VARCHAR(100) NULL,
90 `abono_compra` VARCHAR(100) NULL,
91 `por_pagar` VARCHAR(100) NULL,
92 INDEX `fk_detalle_compra_Producto_idx` (`producto_idproducto` ASC),
93 INDEX `fk_detalle_compra_Compra1_idx` (`compra_idcompra` ASC),
94 CONSTRAINT `fk_detalle_compra_Producto`
95 FOREIGN KEY (`producto_idproducto`)
96 REFERENCES `tracking_trucks`.`producto` (`idproducto`)
97 ON DELETE CASCADE
98 ON UPDATE CASCADE,
99 CONSTRAINT `fk_detalle_compra_Compra1`
100 FOREIGN KEY (`compra_idcompra`)
101 REFERENCES `tracking_trucks`.`compra` (`idcompra`)
102 ON DELETE CASCADE
103 ON UPDATE CASCADE)
104ENGINE = InnoDB;
105CREATE TABLE IF NOT EXISTS `tracking_trucks`.`detalle_venta` (
106 `venta_idventa` VARCHAR(50) NOT NULL,
107 `producto_idproducto` INT NOT NULL,
108 `cantidad_venta` VARCHAR(100) NULL,
109 `precioventa_unidad` VARCHAR(100) NULL,
110 `valorventa_total` VARCHAR(100) NULL,
111 INDEX `fk_detalle_venta_Venta1_idx` (`venta_idventa` ASC),
112 INDEX `fk_detalle_venta_Producto1_idx` (`producto_idproducto` ASC),
113 CONSTRAINT `fk_detalle_venta_Venta1`
114 FOREIGN KEY (`venta_idventa`)
115 REFERENCES `tracking_trucks`.`venta` (`idventa`)
116 ON DELETE CASCADE
117 ON UPDATE CASCADE,
118 CONSTRAINT `fk_detalle_venta_Producto1`
119 FOREIGN KEY (`producto_idproducto`)
120 REFERENCES `tracking_trucks`.`producto` (`idproducto`)
121 ON DELETE CASCADE
122 ON UPDATE CASCADE)
123ENGINE = InnoDB;
124CREATE TABLE IF NOT EXISTS `tracking_trucks`.`login` (
125 `id_login` INT NOT NULL,
126 `usuario` VARCHAR(45) NULL,
127 `password` VARCHAR(45) NULL,
128 PRIMARY KEY (`id_login`))
129ENGINE = InnoDB;
130CREATE TABLE IF NOT EXISTS `tracking_trucks`.`infracciones` (
131 `id_infraccion` VARCHAR(60) NOT NULL,
132 `chofer_rut` VARCHAR(50) NOT NULL,
133 `imagen_infraccion` LONGBLOB NULL,
134 `fecha_infraccion` DATE NULL,
135 PRIMARY KEY (`id_infraccion`),
136 INDEX `fk_infracciones_chofer1_idx` (`chofer_rut` ASC),
137 CONSTRAINT `fk_infracciones_chofer1`
138 FOREIGN KEY (`chofer_rut`)
139 REFERENCES `tracking_trucks`.`chofer` (`rut`)
140 ON DELETE CASCADE
141 ON UPDATE CASCADE)
142ENGINE = InnoDB;
143CREATE TABLE IF NOT EXISTS `tracking_trucks`.`escaneos` (
144 `id_escaneo` INT NOT NULL AUTO_INCREMENT,
145 `fecha_escaneo` DATE NULL,
146 `archivo_sdp3` LONGBLOB NULL,
147 `truck_patente` VARCHAR(60) NOT NULL,
148 PRIMARY KEY (`id_escaneo`),
149 INDEX `fk_escaneos_truck1_idx` (`truck_patente` ASC),
150 CONSTRAINT `fk_escaneos_truck1`
151 FOREIGN KEY (`truck_patente`)
152 REFERENCES `tracking_trucks`.`truck` (`patente`)
153 ON DELETE CASCADE
154 ON UPDATE CASCADE)
155ENGINE = InnoDB;
156
157
158SET SQL_MODE=@OLD_SQL_MODE;
159SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
160SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;