db.migration.sqlserver.NOTIFIER_V1.0.0__Init_notifier.sql Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of awe-notifier-spring-boot-starter Show documentation
AWE Spring Boot Starter Notifier
There is a newer version: 4.10.3
Show newest version
--------------------------------------------------------
--  NOTIFIER DDL
--------------------------------------------------------

--------------------------------------------------------
--  DDL for Table AweSub
--  Subscriptions
--------------------------------------------------------
create TABLE AweSub
(
    Ide INTEGER           not NULL,
    Acr VARCHAR(10)       not NULL,
    Nom VARCHAR(30)       not NULL,
    Des VARCHAR(250),
    Act INTEGER DEFAULT 1 not NULL
);

--------------------------------------------------------
--  DDL for Table AweSubUsr
--  User subscriptions
--------------------------------------------------------
create TABLE AweSubUsr
(
    Ide    INTEGER   not NULL,
    IdeOpe INTEGER   not NULL,
    IdeSub INTEGER   not NULL,
    SubNot INTEGER DEFAULT 1 not NULL,
    SubEma INTEGER DEFAULT 0 not NULL
);

--------------------------------------------------------
--  DDL for Table AweNot
--  Notifications
--------------------------------------------------------
create TABLE AweNot
(
    Ide    INTEGER     not NULL,
    IdeSub INTEGER     not NULL,
    Typ    VARCHAR(10) not NULL,
    Ico    VARCHAR(30) not NULL,
    Nom    VARCHAR(30) not NULL,
    Des    VARCHAR(250),
    Scr    VARCHAR(100),
    Cod    VARCHAR(100),
    Dat    DATETIME    not NULL
);

--------------------------------------------------------
--  DDL for Table AweNotUsr
--  User notifications
--------------------------------------------------------
create TABLE AweNotUsr
(
    Ide    INTEGER           not NULL,
    IdeOpe INTEGER           not NULL,
    IdeNot INTEGER           not NULL,
    Unr    INTEGER DEFAULT 1 not NULL
);

--------------------------------------------------------
--  DDL for HISTORIC TABLES
--------------------------------------------------------

create TABLE HISAweSub
(
    HISope VARCHAR(20),
    HISdat DATE,
    HISact VARCHAR(1),
    Ide    INTEGER,
    Acr    VARCHAR(10),
    Nom    VARCHAR(30),
    Des    VARCHAR(250),
    Act    INTEGER
);

create TABLE HISAweSubUsr
(
    HISope VARCHAR(20),
    HISdat DATE,
    HISact VARCHAR(1),
    Ide    INTEGER,
    IdeOpe INTEGER,
    IdeSub INTEGER,
    SubNot INTEGER,
    SubEma INTEGER
);

create TABLE HISAweNot
(
    HISope VARCHAR(20) not NULL,
    HISdat DATE        not NULL,
    HISact VARCHAR(1)  not NULL,
    Ide    INTEGER,
    IdeSub INTEGER,
    Typ    VARCHAR(10),
    Ico    VARCHAR(30),
    Nom    VARCHAR(30),
    Des    VARCHAR(250),
    Scr    VARCHAR(100),
    Cod    VARCHAR(100),
    Dat    DATETIME
);

create TABLE HISAweNotUsr
(
    HISope    VARCHAR(20) not NULL,
    HISdat    DATE        not NULL,
    HISact    VARCHAR(1)  not NULL,
    Ide       INTEGER,
    IdeOpe    INTEGER,
    IdeNot    INTEGER,
    Unr       INTEGER
);

--------------------------------------------------------
--  DDL for CONSTRAINTS
--------------------------------------------------------

create UNIQUE INDEX PK_AWESUB ON AweSub (Ide);
create UNIQUE INDEX PK_AWESUBUSR ON AweSubUsr (Ide);
create UNIQUE INDEX PK_AWENOT ON AweNot (Ide);
create UNIQUE INDEX PK_AWENOTUSR ON AweNotUsr (Ide);
create INDEX HISAweSubI1 ON HISAweSub (HISope, HISdat, HISact);
create INDEX HISAweSubUsrI1 ON HISAweSubUsr (HISope, HISdat, HISact);
create INDEX HISAweNotI1 ON HISAweNot (HISope, HISdat, HISact);
create INDEX HISAweNotUsrI1 ON HISAweNotUsr (HISope, HISdat, HISact);

-- Notifier sequences
insert into AweKey (KeyNam, KeyVal) values ('Sub', (select coalesce(max(Ide),0) + 1 from AweSub));
insert into AweKey (KeyNam, KeyVal) values ('SubUsr', (select coalesce(max(Ide),0) + 1 from AweSubUsr));
insert into AweKey (KeyNam, KeyVal) values ('Not', (select coalesce(max(Ide),0) + 1 from AweNot));
insert into AweKey (KeyNam, KeyVal) values ('NotUsr', (select coalesce(max(Ide),0) + 1 from AweNotUsr));