sqlite_comandos.sql

/*****SQLite comandos

/****Indices***
CREATE TABLE IF NOT EXISTS id_pe (
    pe       INTEGER PRIMARY KEY ASC ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL,
    pe_label STRING  NOT NULL DEFAULT pe
);

CREATE TABLE IF NOT EXISTS id_sp (
    sp INTEGER PRIMARY KEY ASC ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL,
 	sp_label STRING  NOT NULL DEFAULT sp
);

CREATE TABLE IF NOT EXISTS id_bl (
    bl INTEGER PRIMARY KEY ASC ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL,
    bl_label STRING  NOT NULL DEFAULT bl
);

//Tabla Grupos termicos e hidraulicos
CREATE TABLE IF NOT EXISTS id_gt (
    gt STRING NOT NULL DEFAULT gt PRIMARY KEY ASC
);

CREATE TABLE IF NOT EXISTS id_gh (
    gh STRING NOT NULL DEFAULT gh PRIMARY KEY ASC
);

CREATE TABLE IF NOT EXISTS id_em (
    em STRING NOT NULL DEFAULT em PRIMARY KEY ASC
);

CREATE TABLE IF NOT EXISTS id_amc (
    amc STRING NOT NULL DEFAULT amc PRIMARY KEY ASC
);

CREATE TABLE IF NOT EXISTS id_amv (
    amv STRING NOT NULL DEFAULT amv PRIMARY KEY ASC
);

/***Datos grupos termicos
CREATE TABLE IF NOT EXISTS gt_carac (
    gt      STRING NOT NULL DEFAULT gt REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE,
    gt_pmax REAL   DEFAULT (0) NOT NULL,
    gt_pmin REAL   DEFAULT (0) NOT NULL
);

CREATE TABLE IF NOT EXISTS gt_costos (
    gt        STRING NOT NULL DEFAULT gt REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE,
    gt_cvacio REAL 	 NOT NULL DEFAULT (0),
    gt_carr   REAL   NOT NULL DEFAULT (0),
    gt_cpar   REAL   NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gt_cvar_p (
    gt        STRING  REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gt,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gt_cvar_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gt_coefdisp_p (
    gt            STRING  REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gt,
    pe            INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gt_coefdisp_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gt_emax_p (
    gt        STRING  REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gt,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gt_emax_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gt_emin_p (
    gt        STRING  REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gt,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gt_emin_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gt_ener (
    gt      STRING NOT NULL DEFAULT gt REFERENCES id_gt (gt) ON DELETE CASCADE ON UPDATE CASCADE,
    gt_emin REAL   NOT NULL DEFAULT (0),
    gt_emax REAL   NOT NULL DEFAULT (0) 
);

/****Datos grupos hidraulicos
CREATE TABLE IF NOT EXISTS gh_carac (
    gh            STRING REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    gh_upmaxres   REAL   NOT NULL DEFAULT (0),
    gh_vpamxres   REAL   NOT NULL DEFAULT (0),
    gh_rendturbom REAL   NOT NULL DEFAULT (0),
    gh_rini       REAL   NOT NULL DEFAULT (0),
    gh_rfin       REAL   NOT NULL DEFAULT (0),
    gh_rendapor   REAL   NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gh_rmax_p (
    gh        STRING  REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gh_rmax_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gh_rmin_p (
    gh        STRING  REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gh_rmin_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gh_bmax_p (
    gh        STRING  REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gh_bmax_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gh_pflu_psb (
    gh          STRING  REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    pe          INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp          INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl          INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gh_pflu_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gh_apor_p (
    gh        STRING  REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    pe        INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gh_apor_p REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS gh_pobli_p (
    gh         STRING  REFERENCES id_gh (gh) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT gh,
    pe         INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    gh_pobli_p REAL    NOT NULL DEFAULT (0) 
);

/**** Datos demanda
CREATE TABLE IF NOT EXISTS dem_ord_psb (
    pe          INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp          INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl          INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    dem_ord_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS dem_pen_psb (
    pe          INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp          INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl          INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    dem_pen_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS dem_costoenenosum (
    dem_costoenenosum REAL NOT NULL DEFAULT (0) 
);

/****Datos de las empresas
CREATE TABLE IF NOT EXISTS em_theta_psb (
    em           STRING  REFERENCES id_em (em) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT em,
    pe           INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp           INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl           INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    em_theta_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS em_cbilpcio_psb (
    em              STRING  REFERENCES id_em (em) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT em,
    pe              INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp              INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl              INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    em_cbilpcio_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS em_cbilpot_psb (
    em             STRING  REFERENCES id_em (em) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT em,
    pe             INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp             INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl             INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    em_cbilpot_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS em_cdifpcio_psb (
    em              STRING  REFERENCES id_em (em) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT em,
    pe              INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp              INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl              INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    em_cdifpcio_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS em_cdifpot_psb (
    em             STRING  REFERENCES id_em (em) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT em,
    pe             INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp             INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl             INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    em_cdifpot_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS em_cuota (
    em          STRING REFERENCES id_em (em) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT em,
    em_cuotamin REAL   NOT NULL DEFAULT (0),
    em_pmin     REAL   NOT NULL DEFAULT (0) 
);

/*****Datos agentes marginalistas
CREATE TABLE IF NOT EXISTS amv_oferpot_psb (
    amv             STRING  REFERENCES id_amv (amv) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT amv,
    pe              INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp              INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl              INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    amv_oferpot_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS amv_oferpcio_psb (
    amv              STRING  REFERENCES id_amv (amv) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT amv,
    pe               INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp               INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl               INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    amv_oferpcio_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS amc_oferpot_psb (
    amc             STRING  REFERENCES id_amc (amc) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT amc,
    pe              INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp              INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl              INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    amc_oferpot_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS amc_oferpcio_psb (
    amc              STRING  REFERENCES id_amc (amc) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL DEFAULT amc,
    pe               INTEGER REFERENCES id_pe (pe) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    sp               INTEGER REFERENCES id_sp (sp) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    bl               INTEGER REFERENCES id_bl (bl) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
    amc_oferpcio_psb REAL    NOT NULL DEFAULT (0) 
);

CREATE TABLE IF NOT EXISTS lambda_tam (
    var   STRING  PRIMARY KEY ASC ON CONFLICT ROLLBACK NOT NULL,
    valor INTEGER DEFAULT (0)  NOT NULL);
    
CREATE TABLE IF NOT EXISTS lambda_tablas (
    Nombre STRING NOT NULL PRIMARY KEY);