518 lines
22 KiB
SQL
518 lines
22 KiB
SQL
/* AREA DE DESARROLLO */
|
|
CREATE SCHEMA desarrollo1;
|
|
SET SEARCH_PATH TO desarrollo1;
|
|
|
|
/* REQUERIDO PARA TIPO GEOMETRY */
|
|
CREATE EXTENSION IF NOT EXISTS postgis;
|
|
|
|
/*==============================================================*/
|
|
/* Table: APLICACION */
|
|
/*==============================================================*/
|
|
create table if not exists APLICACION (
|
|
ID_APLICACION INT4 not null,
|
|
NOMBRE_APP varchar(100) null,
|
|
VIGENTE BOOL null,
|
|
constraint PK_APLICACION primary key (ID_APLICACION)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: COMUNA */
|
|
/*==============================================================*/
|
|
create table if not exists COMUNA (
|
|
ID_COMUNA INT4 not null,
|
|
ID_REGION INT4 null,
|
|
NOMBRE_COMUNA varchar(100) null,
|
|
constraint PK_COMUNA primary key (ID_COMUNA)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: CONDUCTOR */
|
|
/*==============================================================*/
|
|
create table if not exists CONDUCTOR (
|
|
PATENTE VARCHAR(10) null,
|
|
RUT NUMERIC(12) null,
|
|
VIGENTE BOOL null
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: DISPOSITIVO */
|
|
/*==============================================================*/
|
|
create table if not exists DISPOSITIVO (
|
|
ID_DISPOSITIVO INT4 not null,
|
|
ID_PARADERO INT4 null,
|
|
VIGENTE BOOL null,
|
|
ULTIMA_CONEXION DATE null,
|
|
constraint PK_DISPOSITIVO primary key (ID_DISPOSITIVO)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: FUNCIONARIO */
|
|
/*==============================================================*/
|
|
create table if not exists FUNCIONARIO (
|
|
RUT NUMERIC(12) null,
|
|
ID_OPERADOR INT4 null,
|
|
DESDE DATE null,
|
|
HASTA DATE null
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_CALENDAR */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_CALENDAR (
|
|
ID_LINEA INT4 not null,
|
|
MONDAY BOOL null,
|
|
TUESDAY BOOL null,
|
|
WEDNESDAY BOOL null,
|
|
THURSDAY BOOL null,
|
|
FRIDAY BOOL null,
|
|
SALURDAY BOOL null,
|
|
SUNDAY BOOL null,
|
|
constraint PK_GTFS_CALENDAR primary key (ID_LINEA)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_FREQUENCIE */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_FREQUENCIE (
|
|
ID_TRIPS INT4 null,
|
|
START_TIME TIME null,
|
|
END_TIME TIME null,
|
|
HEADWAY_SECS int null,
|
|
EXACT_TIME int null
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Index: INDEX_FREQUENCIE */
|
|
/*==============================================================*/
|
|
create index INDEX_FREQUENCIE on GTFS_FREQUENCIE (
|
|
ID_TRIPS
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_ROUTES */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_ROUTES (
|
|
ID_ROUTES INT4 not null,
|
|
ID_OPERADOR INT4 null,
|
|
ID_ROUTE_TYPE int null,
|
|
SHORT_NAME varchar(100) null,
|
|
LONG_NAME varchar(300) null,
|
|
DESCRIPCION varchar(500) null,
|
|
ROUTE_COLOR VARCHAR(6) null,
|
|
ROUTE_TEXT_COLOR VARCHAR(6) null,
|
|
ROUTE_SORT_ORDER int null,
|
|
constraint PK_GTFS_ROUTES primary key (ID_ROUTES)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_ROUTE_TYPE */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_ROUTE_TYPE (
|
|
ID_ROUTE_TYPE int not null,
|
|
DESCRIPCION varchar(100) null,
|
|
constraint PK_GTFS_ROUTE_TYPE primary key (ID_ROUTE_TYPE)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_SHAPE */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_SHAPE (
|
|
ID_SHAPES INT4 not null,
|
|
SHAPE_PT_LAT geometry null,
|
|
SHAPE_PT_LON geometry null,
|
|
SHAPE_PT_SEQUENCE int null,
|
|
SHAOE_DIST_TRAVELED float null,
|
|
constraint PK_GTFS_SHAPE primary key (ID_SHAPES)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_STOP_TIMES */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_STOP_TIMES (
|
|
ID_PARADERO INT4 not null,
|
|
ID_TRIPS INT4 not null,
|
|
ARRIVAL_TIME TIME null,
|
|
STOP_SEQUENCE int null,
|
|
STOP_HEADSIGN varchar(100) null,
|
|
constraint PK_GTFS_STOP_TIMES primary key (ID_PARADERO, ID_TRIPS)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: GTFS_TRIPS */
|
|
/*==============================================================*/
|
|
create table if not exists GTFS_TRIPS (
|
|
ID_TRIPS INT4 not null,
|
|
ID_ROUTES INT4 null,
|
|
ID_LINEA INT4 null,
|
|
ID_SHAPES INT4 null,
|
|
ID_TRIPS_REGRESO INT4 null,
|
|
TRIP_HEADSIGN varchar(100) null,
|
|
SHORT_NAME varchar(100) null,
|
|
DIRECCION_ID int null,
|
|
constraint PK_GTFS_TRIPS primary key (ID_TRIPS)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: LINEA */
|
|
/*==============================================================*/
|
|
create table if not exists LINEA (
|
|
ID_LINEA INT4 not null,
|
|
ID_OPERADOR INT4 null,
|
|
IID_TIPO_TRANSPORTE INT4 null,
|
|
ID_REGION INT4 null,
|
|
VIGENTE BOOL null,
|
|
NOMBRE VARCHAR(100) null,
|
|
URL varchar(300) null,
|
|
constraint PK_LINEA primary key (ID_LINEA)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: OPERADOR */
|
|
/*==============================================================*/
|
|
create table if not exists OPERADOR (
|
|
ID_OPERADOR INT4 not null,
|
|
ID_REGION INT4 null,
|
|
VIGENTE BOOL null,
|
|
constraint PK_OPERADOR primary key (ID_OPERADOR)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: PARADERO */
|
|
/*==============================================================*/
|
|
create table if not exists PARADERO (
|
|
ID_PARADERO INT4 not null,
|
|
ID_COMUNA INT4 null,
|
|
ID_TIPO_PARADERO INT4 null,
|
|
VIGENTE BOOL null,
|
|
STOP_CODE varchar(100) null,
|
|
STOP_NAME varchar(100) null,
|
|
STOP_DESC varchar(300) null,
|
|
STOP_LAT geometry null,
|
|
STOP_LON geometry null,
|
|
constraint PK_PARADERO primary key (ID_PARADERO)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: PARADERO_IMAGEN */
|
|
/*==============================================================*/
|
|
create table if not exists PARADERO_IMAGEN (
|
|
ID_PARADERO INT4 not null,
|
|
IMAGEN BYTEA null,
|
|
constraint PK_PARADERO_IMAGEN primary key (ID_PARADERO)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: PERSONA */
|
|
/*==============================================================*/
|
|
create table if not exists PERSONA (
|
|
RUT NUMERIC(12) not null,
|
|
ID_TIPO_TRATAMIENTO INT4 null,
|
|
ID_COMUNA INT4 null,
|
|
DV CHAR(1) null,
|
|
NOMBRES VARCHAR(100) null,
|
|
APELLIDO_A VARCHAR(100) null,
|
|
APELLIDO_B VARCHAR(100) null,
|
|
FONO VARCHAR(100) null,
|
|
EMAIL VARCHAR(100) null,
|
|
FECHA_NACIMIENTO DATE null,
|
|
DIRECCION VARCHAR(100) null,
|
|
constraint PK_PERSONA primary key (RUT)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: REGION */
|
|
/*==============================================================*/
|
|
create table if not exists REGION (
|
|
ID_REGION INT4 not null,
|
|
NOMBRE_REGION VARCHAR(100) not null,
|
|
constraint PK_REGION primary key (ID_REGION)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: ROL */
|
|
/*==============================================================*/
|
|
create table if not exists ROL (
|
|
ID_ROL INT4 not null,
|
|
NOMBRE_ROL varchar(100) not null,
|
|
constraint PK_ROL primary key (ID_ROL)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: ROL_APLICACION */
|
|
/*==============================================================*/
|
|
create table if not exists ROL_APLICACION (
|
|
ID_APLICACION INT4 not null,
|
|
ID_ROL INT4 not null,
|
|
SOLO_VISUALIZAR BOOL null,
|
|
ID_ROL_APP SERIAL4 not null,
|
|
CONSTRAINT rol_aplicacion_pk PRIMARY KEY (ID_ROL_APP)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX rol_aplicacion_id_aplicacion_idx ON ROL_APLICACION USING btree (ID_APLICACION, ID_ROL);
|
|
|
|
/*==============================================================*/
|
|
/* Table: TIPO_DISPOSITIVO */
|
|
/*==============================================================*/
|
|
create table if not exists TIPO_DISPOSITIVO (
|
|
ID_DISPOSITIVO INT4 null,
|
|
ID_TIPO_DISPOSITIVO INT4 null
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: TIPO_PARADERO */
|
|
/*==============================================================*/
|
|
create table if not exists TIPO_PARADERO (
|
|
ID_TIPO_PARADERO INT4 not null,
|
|
DESCRIPCION VARCHAR(100) null,
|
|
constraint PK_TIPO_PARADERO primary key (ID_TIPO_PARADERO)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: TIPO_TRANSPORTE */
|
|
/*==============================================================*/
|
|
create table if not exists TIPO_TRANSPORTE (
|
|
IID_TIPO_TRANSPORTE INT4 not null,
|
|
DESCRIPCION VARCHAR(50) null,
|
|
constraint PK_TIPO_TRANSPORTE primary key (IID_TIPO_TRANSPORTE)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: TIPO_TRATAMIENTO_PERSONA */
|
|
/*==============================================================*/
|
|
create table if not exists TIPO_TRATAMIENTO_PERSONA (
|
|
ID_TIPO_TRATAMIENTO INT4 not null,
|
|
TRATAMIENTO varchar(50) null,
|
|
constraint PK_TIPO_TRATAMIENTO_PERSONA primary key (ID_TIPO_TRATAMIENTO)
|
|
);
|
|
|
|
comment on table TIPO_TRATAMIENTO_PERSONA is
|
|
'Establece el tratamiento de como dirigirse hacia una persona:
|
|
Ejemplo
|
|
Señor
|
|
Señora
|
|
Srta';
|
|
|
|
/*==============================================================*/
|
|
/* Table: TIPO_VEHICULO */
|
|
/*==============================================================*/
|
|
create table if not exists TIPO_VEHICULO (
|
|
ID_TIPO_VEHICULO int not null,
|
|
DESCRIPCION varchar(100) null,
|
|
constraint PK_TIPO_VEHICULO primary key (ID_TIPO_VEHICULO)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: USUARIO */
|
|
/*==============================================================*/
|
|
create table if not exists USUARIO (
|
|
LOGIN VARCHAR(20) not null,
|
|
RUT NUMERIC(12) null,
|
|
CLAVE VARCHAR(20) null,
|
|
VIGENTE bool null,
|
|
constraint PK_USUARIO primary key (LOGIN)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: USUARIO_ROL */
|
|
/*==============================================================*/
|
|
create table if not exists USUARIO_ROL (
|
|
LOGIN VARCHAR(20) null,
|
|
ID_ROL INT4 null,
|
|
VIGENTE BOOL null
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: VEHICULO */
|
|
/*==============================================================*/
|
|
create table if not exists VEHICULO (
|
|
PPU VARCHAR(10) not null,
|
|
ID_TIPO_VEHICULO int null,
|
|
VIGENTE BOOL null,
|
|
constraint PK_VEHICULO primary key (PPU)
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* Table: VEHICULO_LINEA */
|
|
/*==============================================================*/
|
|
create table if not exists VEHICULO_LINEA (
|
|
PATENTE VARCHAR(10) null,
|
|
ID_LINEA INT4 null,
|
|
VIGENTE BOOL null
|
|
);
|
|
|
|
/*==============================================================*/
|
|
/* View: VW_PARADERO_LINEA */
|
|
/*==============================================================*/
|
|
create or replace view VW_PARADERO_LINEA as
|
|
select;
|
|
|
|
alter table COMUNA
|
|
add constraint FK_COMUNA_REFERENCE_REGION foreign key (ID_REGION)
|
|
references REGION (ID_REGION)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table CONDUCTOR
|
|
add constraint FK_CONDUCTO_REFERENCE_VEHICULO foreign key (PATENTE)
|
|
references VEHICULO (PPU)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table CONDUCTOR
|
|
add constraint FK_CONDUCTO_REFERENCE_PERSONA foreign key (RUT)
|
|
references PERSONA (RUT)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table DISPOSITIVO
|
|
add constraint FK_DISPOSIT_REFERENCE_PARADERO foreign key (ID_PARADERO)
|
|
references PARADERO (ID_PARADERO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table FUNCIONARIO
|
|
add constraint FK_FUNCIONA_REFERENCE_PERSONA foreign key (RUT)
|
|
references PERSONA (RUT)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table FUNCIONARIO
|
|
add constraint FK_FUNCIONA_REFERENCE_OPERADOR foreign key (ID_OPERADOR)
|
|
references OPERADOR (ID_OPERADOR)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_CALENDAR
|
|
add constraint FK_GTFS_CAL_REFERENCE_LINEA foreign key (ID_LINEA)
|
|
references LINEA (ID_LINEA)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_FREQUENCIE
|
|
add constraint FK_GTFS_FRE_REFERENCE_GTFS_TRI foreign key (ID_TRIPS)
|
|
references GTFS_TRIPS (ID_TRIPS)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_ROUTES
|
|
add constraint FK_GTFS_ROU_REFERENCE_OPERADOR foreign key (ID_OPERADOR)
|
|
references OPERADOR (ID_OPERADOR)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_ROUTES
|
|
add constraint FK_GTFS_ROU_REFERENCE_GTFS_ROU foreign key (ID_ROUTE_TYPE)
|
|
references GTFS_ROUTE_TYPE (ID_ROUTE_TYPE)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_STOP_TIMES
|
|
add constraint FK_GTFS_STO_REFERENCE_PARADERO foreign key (ID_PARADERO)
|
|
references PARADERO (ID_PARADERO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_STOP_TIMES
|
|
add constraint FK_GTFS_STO_REFERENCE_GTFS_TRI foreign key (ID_TRIPS)
|
|
references GTFS_TRIPS (ID_TRIPS)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_TRIPS
|
|
add constraint FK_GTFS_TRI_REFERENCE_GTFS_ROU foreign key (ID_ROUTES)
|
|
references GTFS_ROUTES (ID_ROUTES)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_TRIPS
|
|
add constraint FK_GTFS_TRI_REFERENCE_LINEA foreign key (ID_LINEA)
|
|
references LINEA (ID_LINEA)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_TRIPS
|
|
add constraint FK_GTFS_TRI_REFERENCE_GTFS_SHA foreign key (ID_SHAPES)
|
|
references GTFS_SHAPE (ID_SHAPES)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table GTFS_TRIPS
|
|
add constraint FK_GTFS_TRI_REFERENCE_GTFS_TRI foreign key (ID_TRIPS_REGRESO)
|
|
references GTFS_TRIPS (ID_TRIPS)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table LINEA
|
|
add constraint FK_LINEA_REFERENCE_OPERADOR foreign key (ID_OPERADOR)
|
|
references OPERADOR (ID_OPERADOR)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table LINEA
|
|
add constraint FK_LINEA_REFERENCE_TIPO_TRA foreign key (IID_TIPO_TRANSPORTE)
|
|
references TIPO_TRANSPORTE (IID_TIPO_TRANSPORTE)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table LINEA
|
|
add constraint FK_LINEA_REFERENCE_REGION foreign key (ID_REGION)
|
|
references REGION (ID_REGION)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table OPERADOR
|
|
add constraint FK_OPERADOR_REFERENCE_REGION foreign key (ID_REGION)
|
|
references REGION (ID_REGION)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table PARADERO
|
|
add constraint FK_PARADERO_REFERENCE_COMUNA foreign key (ID_COMUNA)
|
|
references COMUNA (ID_COMUNA)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table PARADERO
|
|
add constraint FK_PARADERO_REFERENCE_TIPO_PAR foreign key (ID_TIPO_PARADERO)
|
|
references TIPO_PARADERO (ID_TIPO_PARADERO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table PARADERO_IMAGEN
|
|
add constraint FK_PARADERO_REFERENCE_PARADERO foreign key (ID_PARADERO)
|
|
references PARADERO (ID_PARADERO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table PERSONA
|
|
add constraint FK_PERSONA_REFERENCE_COMUNA foreign key (ID_COMUNA)
|
|
references COMUNA (ID_COMUNA)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table PERSONA
|
|
add constraint FK_PERSONA_REFERENCE_TIPO_TRA foreign key (ID_TIPO_TRATAMIENTO)
|
|
references TIPO_TRATAMIENTO_PERSONA (ID_TIPO_TRATAMIENTO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table ROL_APLICACION
|
|
add constraint FK_ROL_APLI_REFERENCE_APLICACI foreign key (ID_APLICACION)
|
|
references APLICACION (ID_APLICACION)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table ROL_APLICACION
|
|
add constraint FK_ROL_APLI_REFERENCE_ROL foreign key (ID_ROL)
|
|
references ROL (ID_ROL)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table TIPO_DISPOSITIVO
|
|
add constraint FK_TIPO_DIS_REFERENCE_DISPOSIT foreign key (ID_DISPOSITIVO)
|
|
references DISPOSITIVO (ID_DISPOSITIVO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table USUARIO
|
|
add constraint FK_USUARIO_REFERENCE_PERSONA foreign key (RUT)
|
|
references PERSONA (RUT)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table USUARIO_ROL
|
|
add constraint FK_USUARIO__REFERENCE_USUARIO foreign key (LOGIN)
|
|
references USUARIO (LOGIN)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table USUARIO_ROL
|
|
add constraint FK_USUARIO__REFERENCE_ROL foreign key (ID_ROL)
|
|
references ROL (ID_ROL)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table VEHICULO
|
|
add constraint FK_VEHICULO_REFERENCE_TIPO_VEH foreign key (ID_TIPO_VEHICULO)
|
|
references TIPO_VEHICULO (ID_TIPO_VEHICULO)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table VEHICULO_LINEA
|
|
add constraint FK_VEHICULO_REFERENCE_VEHICULO foreign key (PATENTE)
|
|
references VEHICULO (PPU)
|
|
on delete restrict on update restrict;
|
|
|
|
alter table VEHICULO_LINEA
|
|
add constraint FK_VEHICULO_REFERENCE_LINEA foreign key (ID_LINEA)
|
|
references LINEA (ID_LINEA)
|
|
on delete restrict on update restrict;
|