263 lines
8.3 KiB
Plaintext
263 lines
8.3 KiB
Plaintext
#+ Database creation script for SQLite
|
|
#+
|
|
#+ Note: This script is a helper script to create an empty database schema
|
|
#+ Adapt it to fit your needs
|
|
|
|
|
|
#+ Create all tables in database.
|
|
FUNCTION db_create_tables()
|
|
EXECUTE IMMEDIATE "CREATE TABLE existencia (
|
|
tipo_inventario VARCHAR(100),
|
|
num_doc INT,
|
|
fecha DATE,
|
|
codid INT,
|
|
cod_mov SMALLINT,
|
|
cod_seccion INT,cod_tramo INT,cod_fila INT,cod_pos INT,
|
|
cantidad DEC(12,5),
|
|
bodega smallint,
|
|
status_t CHAR(1)) "
|
|
EXECUTE IMMEDIATE "CREATE TABLE seqreg (
|
|
sr_name VARCHAR(30) NOT NULL,
|
|
sr_last INTEGER NOT NULL,
|
|
CONSTRAINT pk_seqreg PRIMARY KEY(sr_name))"
|
|
|
|
{ EXECUTE IMMEDIATE "CREATE TABLE productos (
|
|
tipo_inventario VARCHAR(100),
|
|
cod_n SMALLINT NOT NULL,
|
|
cod_grupo SMALLINT NOT NULL,
|
|
cod_tipo SMALLINT NOT NULL,
|
|
cod_sec SMALLINT NOT NULL,
|
|
descrip_esp CHAR(100) NOT NULL,
|
|
unidad_med CHAR(4) NOT NULL,
|
|
codid INTEGER NOT NULL,
|
|
factor_conv DECIMAL(12,5),
|
|
imagen VARCHAR(80),
|
|
CONSTRAINT idx_productos_1 PRIMARY KEY(tipo_inventario,cod_grupo, cod_n, cod_sec, cod_tipo))"}
|
|
EXECUTE IMMEDIATE "CREATE TABLE productos (
|
|
tipo_inventario VARCHAR(100),
|
|
cod_n INT NOT NULL,
|
|
cod_grupo INT NOT NULL,
|
|
cod_tipo INT NOT NULL,
|
|
cod_sec INT NOT NULL,
|
|
descrip_esp CHAR(100) NOT NULL,
|
|
unidad_med CHAR(4) NOT NULL,
|
|
codid INTEGER NOT NULL,
|
|
factor_conv DECIMAL(12,5),
|
|
imagen VARCHAR(80),
|
|
desglose VARCHAR(1))"
|
|
EXECUTE IMMEDIATE "CREATE TABLE desglose (
|
|
tipo_inventario VARCHAR(100) NOT NULL,
|
|
fecha DATE,
|
|
cod_n INT NOT NULL,
|
|
cod_grupo INT NOT NULL,
|
|
cod_tipo INT NOT NULL,
|
|
cod_sec INT NOT NULL,
|
|
descrip_esp CHAR(100) NOT NULL,
|
|
unidad_med CHAR(4) NOT NULL,
|
|
codid INTEGER NOT NULL,
|
|
factor_conv DECIMAL(12,5),
|
|
lado_1 decimal(12,5) NOT NULL,
|
|
lado_2 decimal(12,5) NOT NULL,
|
|
lado_3 decimal(12,5) NOT NULL,
|
|
cantidad decimal(12,5) NOT NULL,
|
|
cantidad2 decimal(12,5) NOT NULL,
|
|
cod_bodega int NOT NULL,
|
|
cod_seccion int NOT NULL,
|
|
cod_tramo int NOT NULL,
|
|
cod_fila int NOT NULL,
|
|
cod_pos int NOT NULL,
|
|
observacion VARCHAR(200),
|
|
idproducto VARCHAR(25),
|
|
idrow serial,
|
|
idtrx INT,
|
|
tono VARCHAR(100),
|
|
calidad VARCHAR(100)
|
|
)"
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE clase_productos (
|
|
cod_n CHAR(1) NOT NULL,
|
|
descripcion CHAR(50) NOT NULL,
|
|
CONSTRAINT idx_clase_productos_1 PRIMARY KEY(cod_n))"
|
|
EXECUTE IMMEDIATE "CREATE TABLE grupo_producto (
|
|
cod_grupo INT NOT NULL,
|
|
descripcion CHAR(50) NOT NULL,
|
|
CONSTRAINT idx_grupo_producto_2 PRIMARY KEY(cod_grupo))"
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE seg000 (
|
|
usuario CHAR(50) NOT NULL,
|
|
nombre CHAR(80),
|
|
sec_vend SMALLINT,
|
|
password CHAR(8),
|
|
ocupacion CHAR(60),
|
|
bodega smallint,
|
|
CONSTRAINT idx_seg000_1 PRIMARY KEY(usuario))"
|
|
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE companias (
|
|
cod_cia SMALLINT NOT NULL,
|
|
nombre CHAR(100),
|
|
direccion CHAR(100),
|
|
telefono CHAR(20),
|
|
representante CHAR(50),
|
|
servidor_sync CHAR(20),
|
|
pais CHAR(100),
|
|
ciudad CHAR(100),
|
|
correo_electronico CHAR(80),
|
|
ultimo_usuario CHAR(50),
|
|
ultima_fecha_login CHAR(1),
|
|
prima_us DECIMAL(12,2),
|
|
itbis DECIMAL(12,2),
|
|
rnc CHAR(11),
|
|
porc_desc DECIMAL(12,2),
|
|
bodega smallint,
|
|
CONSTRAINT idx_companias_1 PRIMARY KEY(cod_cia))"
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE irtb00012(
|
|
idtrx int NULL,
|
|
fecha date NULL,
|
|
bodega smallint NULL,
|
|
cod_seccion int NULL,
|
|
cod_tramo int NULL,
|
|
cod_n int NULL,
|
|
cod_grupo int NULL,
|
|
cod_tipo int NULL,
|
|
cod_sec int NULL,
|
|
cantidad decimal(10, 2) NULL,
|
|
balance decimal(12, 2) NULL,
|
|
us_crea nvarchar(50) NULL,
|
|
fech_crea datetime year to minute NULL,
|
|
us_mod nvarchar(50) NULL,
|
|
fech_mod date NULL,
|
|
codid INT
|
|
) "
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE intb00012(
|
|
idtrx int NULL,
|
|
fecha date NULL,
|
|
bodega smallint NULL,
|
|
cod_seccion int NULL,
|
|
cod_tramo int NULL,
|
|
cod_n smallint NULL,
|
|
cod_grupo int NULL,
|
|
cod_tipo int NULL,
|
|
cod_sec int NULL,
|
|
cantidad decimal(10, 2) NULL,
|
|
balance decimal(12, 2) NULL,
|
|
us_crea nvarchar(50) NULL,
|
|
fech_crea datetime year to minute NULL,
|
|
us_mod nvarchar(50) NULL,
|
|
fech_mod date NULL,
|
|
codid INT
|
|
) "
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE secciones(
|
|
cod_seccion int NOT NULL,
|
|
cod_bodega SMALLINT NOT NULL,
|
|
descripcion VARCHAR(30) NOT NULL) "
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE tramos (
|
|
cod_bodega smallint,
|
|
cod_seccion int,
|
|
cod_tramo int,
|
|
descripcion VARCHAR(60))"
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE filas (
|
|
cod_bodega smallint,
|
|
cod_seccion int,
|
|
cod_tramo int,
|
|
cod_fila int,
|
|
descripcion VARCHAR(60))"
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE posiciones (
|
|
cod_bodega smallint,
|
|
cod_seccion int,
|
|
cod_tramo int,
|
|
cod_fila int,
|
|
cod_pos int,
|
|
descripcion VARCHAR(60))"
|
|
|
|
EXECUTE IMMEDIATE "CREATE TABLE iptb00012(
|
|
idtrx int NULL,
|
|
fecha date NULL,
|
|
bodega smallint NULL,
|
|
cod_seccion int NULL,
|
|
cod_tramo int NULL,
|
|
cod_fila int NULL,
|
|
cod_pos int NULL,
|
|
cod_n int NULL,
|
|
cod_grupo int NULL,
|
|
cod_tipo int NULL,
|
|
cod_sec int NULL,
|
|
cantidad decimal(10, 2) NULL,
|
|
balance decimal(12, 2) NULL,
|
|
us_crea nvarchar(50) NULL,
|
|
fech_crea datetime year to minute NULL,
|
|
us_mod nvarchar(50) NULL,
|
|
fech_mod date NULL,
|
|
lado_1 decimal(12,4),
|
|
lado_2 decimal(12,4),
|
|
lado_3 decimal(12,4),
|
|
codid INT
|
|
) "
|
|
EXECUTE IMMEDIATE "CREATE TABLE bodegas (
|
|
cod_bodega SMALLINT NOT NULL,
|
|
descripcion CHAR(80),
|
|
usuario VARCHAR(50))"
|
|
EXECUTE IMMEDIATE "CREATE TABLE intb00044 (
|
|
tipo_inventario varchar(50) ,
|
|
fecha_conteo date,
|
|
disponible nchar(10),
|
|
fecha_mod datetime,
|
|
us_mod varchar(50)) "
|
|
|
|
# EXECUTE IMMEDIATE "CREATE INDEX pk_iptb00012_1 ON iptb00012(codid,fecha)"
|
|
END FUNCTION
|
|
|
|
#+ Drop all tables from database.
|
|
FUNCTION db_drop_tables()
|
|
WHENEVER ERROR CONTINUE
|
|
|
|
EXECUTE IMMEDIATE "DROP TABLE seqreg"
|
|
EXECUTE IMMEDIATE "DROP TABLE productos"
|
|
EXECUTE IMMEDIATE "DROP TABLE clase_productos"
|
|
EXECUTE IMMEDIATE "DROP TABLE grupo_producto"
|
|
EXECUTE IMMEDIATE "DROP TABLE seg000"
|
|
EXECUTE IMMEDIATE "DROP TABLE companias"
|
|
EXECUTE IMMEDIATE "DROP TABLE irtb00011"
|
|
EXECUTE IMMEDIATE "DROP TABLE irtb00012"
|
|
EXECUTE IMMEDIATE "DROP TABLE intb00011"
|
|
EXECUTE IMMEDIATE "DROP TABLE intb00012"
|
|
EXECUTE IMMEDIATE "DROP TABLE intb00044"
|
|
|
|
EXECUTE IMMEDIATE "DROP TABLE iptb00011"
|
|
EXECUTE IMMEDIATE "DROP TABLE iptb00012"
|
|
EXECUTE IMMEDIATE "DROP TABLE bodegas"
|
|
EXECUTE IMMEDIATE "DROP TABLE tramos"
|
|
EXECUTE IMMEDIATE "DROP TABLE filas"
|
|
EXECUTE IMMEDIATE "DROP TABLE posiciones"
|
|
EXECUTE IMMEDIATE "DROP TABLE secciones"
|
|
EXECUTE IMMEDIATE "DROP TABLE existencia"
|
|
EXECUTE IMMEDIATE "DROP TABLE desglose"
|
|
END FUNCTION
|
|
|
|
FUNCTION db_add_indexes()
|
|
DEFINE msg STRING
|
|
# EXECUTE IMMEDIATE "DROP INDEX pk_iptb00012_1 "
|
|
# EXECUTE IMMEDIATE "DROP INDEX pk_intb00012_1 "
|
|
# EXECUTE IMMEDIATE "DROP INDEX pk_irtb00012_1 "
|
|
|
|
# EXECUTE IMMEDIATE "DROP INDEX pk_productos_2 "
|
|
# EXECUTE IMMEDIATE "DROP INDEX pk_productos_1 "
|
|
# EXECUTE IMMEDIATE "DROP INDEX idx_desglose"
|
|
|
|
EXECUTE IMMEDIATE "CREATE INDEX pk_iptb00012_1 ON iptb00012(codid,fecha)"
|
|
EXECUTE IMMEDIATE "CREATE INDEX pk_intb00012_1 ON intb00012(codid,fecha)"
|
|
EXECUTE IMMEDIATE "CREATE INDEX pk_irtb00012_1 ON irtb00012(codid,fecha)"
|
|
EXECUTE IMMEDIATE "CREATE UNIQUE INDEX idx_desglose ON desglose(idproducto)"
|
|
|
|
EXECUTE IMMEDIATE "CREATE INDEX pk_productos_2 ON productos(tipo_inventario)"
|
|
EXECUTE IMMEDIATE "CREATE INDEX pk_productos_1 ON productos(codid)"
|
|
CALL fgl_winmessage("INFO","INDICES CREADOS","INFO")
|
|
END FUNCTION
|
|
|