SGBD Informix

CREATE TABLE TBTRACKSEARCH

(

 SEARCHID SERIAL8(1) NOT NULL,

 IDUSER INTEGER,

 INSERTDATE DATETIME YEAR TO SECOND NOT NULL,

 FINISHDATE DATETIME YEAR TO SECOND,

 DOCUMENTKEY1 INT8 NOT NULL,

 DOCUMENTKEY2 INT8 NOT NULL,

 DOCUMENTKEY3 INTEGER NOT NULL,

 XMLREQUESTREPLY LVARCHAR(MAX),

 SEARCHSTATUS SMALLINT NOT NULL,

 OPENSTATUS SMALLINT NOT NULL,

 COLDIDSEARCHED INT8,

 DONTSHOW SMALLINT,

 OBSERVATION LVARCHAR(MAX)

);

CREATE TABLE TBTRACKINSTANCE

(

 INSTANCEID SERIAL(1) NOT NULL,

 SERVERID SMALLINT,

 LASTUPDATE DATETIME YEAR TO SECOND

);

CREATE TABLE TBTRACKSEARCHWORK

(

 IDSEARCHWORK SERIAL8(1) NOT NULL,

 SEARCHID INT8 NOT NULL,

 SERVERID SMALLINT,

 INSTANCEID INTEGER

);

CREATE TABLE TBTRACKEVENTS

(

 EVENTID SERIAL8(1) NOT NULL,

 SEARCHID INT8 NOT NULL,

 EVENTTYPE SMALLINT NOT NULL,

 EVENTDATE DATETIME YEAR TO SECOND NOT NULL,

 EVENTMESSAGE LVARCHAR(300),

 ISSUERIDENTITYNUMBER LVARCHAR(20),

 ISSUERNAME LVARCHAR(150),

 EMISSIONDATE DATETIME YEAR TO SECOND,

 TOTALVALUE MONEY(15),

 RECEIVERIDENTITYNUMBER LVARCHAR(20),

 SECTOR SMALLINT,

 RECEIVERNAME LVARCHAR(150),

 USERIDENTIFICATION LVARCHAR(100)

);

CREATE TABLE TBTRACKLIST

(

 ID SERIAL8(1) NOT NULL,

 DOCUMENTNUMBER INTEGER NOT NULL,

 EMISSIONDATE DATETIME YEAR TO SECOND NOT NULL,

 DOCUMENTKEY1 BIGINT NOT NULL,

 DOCUMENTKEY2 BIGINT NOT NULL,

 DOCUMENTKEY3 INT NOT NULL,

 COMPANYEMITTER LVARCHAR(200) NOT NULL,

 TOTALVALUE MONEY(15),

 STATUS SMALLINT,

 USERID INTEGER

);

CREATE TABLE TBTRACKRECAP

(

 RECAPID SERIAL8(1) NOT NULL,

 DOCUMENTKEY1 INT8 NOT NULL,

 DOCUMENTKEY2 INT8 NOT NULL,

 DOCUMENTKEY3 INTEGER NOT NULL,

 JOBKEY LVARCHAR(60) NOT NULL,

 STATUS SMALLINT,

 KIND SMALLINT,

 INSERTEDDATE DATETIME YEAR TO SECOND NOT NULL,

 SEARCHID INT8 NOT NULL,

 SERVERID SMALLINT

);

CREATE TABLE TBTRACKSECTOR

(        ID SERIAL NOT NULL,

 SECTOR SMALLINT,

 SECTORNAME VARCHAR(150) NOT NULL,

 CNPJ INT8 NOT NULL,

 OWNERID VARCHAR(36) NOT NULL,

 PRIMARY KEY (ID)

);

CREATE TABLE TBTRACKENTERPRISE

(

   ID SERIAL NOT NULL,

   IDENTITYNUMBER INT8 NOT NULL,

   OWNERID VARCHAR(36) NOT NULL,

   PRIMARY KEY (ID)

);

CREATE TABLE TBTRACKCOLDCONTROL

(

   ID SERIAL8 (1) NOT NULL,

   COLDID INT8,

   LAST_ID INT8

);

CREATE TABLE TBTRACKCOLDCONTROL

(

 ID SERIAL8(1) NOT NULL,

 COLDID INT8,

 LAST_ID INT8

);

/**PRIMARY KEY CONSTRAINTS**/

ALTER TABLE TBTRACKSEARCH ADD CONSTRAINT PRIMARY KEY (SEARCHID);

ALTER TABLE TBTRACKINSTANCE ADD CONSTRAINT PRIMARY KEY (INSTANCEID);

ALTER TABLE TBTRACKSEARCHWORK ADD CONSTRAINT PRIMARY KEY (IDSearchWork);

ALTER TABLE TBTRACKEVENTS ADD CONSTRAINT PRIMARY KEY (EventID);

ALTER TABLE TBTRACKLIST ADD CONSTRAINT PRIMARY KEY (ID);

ALTER TABLE TBTRACKRECAP ADD CONSTRAINT PRIMARY KEY (RECAPID);

ALTER TABLE TBTRACKSECTOR ADD CONSTRAINT PRIMARY KEY (ID);

/**FOREIGN KEY CONSTRAINTS**/

ALTER TABLE TBTRACKSEARCHWORK ADD CONSTRAINT FOREIGN KEY(INSTANCEID) REFERENCES TBTRACKINSTANCE (INSTANCEID) CONSTRAINT FK_TBTRACKSEARCHWORK_TBTRACKINSTANCE;

ALTER TABLE TBTRACKSEARCHWORK ADD CONSTRAINT FOREIGN KEY(SEARCHID) REFERENCES TBTRACKSEARCH (SEARCHID) CONSTRAINT FK_TBTRACKSEARCHWORK_TBTRACKSEARCH;

ALTER TABLE TBTRACKEVENTS ADD CONSTRAINT FOREIGN KEY(SearchID) REFERENCES TBTRACKSEARCH (SEARCHID) CONSTRAINT FK_TBTRACKEVENTS_TBTRACKSEARCH;

/**TABELA OPCIONAL - VERIFICAR EXISTENCIA**/

CREATE TABLE TBDOCSEARCHPROCESS(

 PROCESSID BIGINT NOT NULL,

 DOCKEY1 BIGINT NOT NULL,

 DOCKEY2 BIGINT NOT NULL,

 DOCKEY3 BIGINT NOT NULL,

 PROTOCOL LVARCHAR(MAX) NOT NULL

);

ALTER TABLE TBDOCSEARCHPROCESS ADD CONSTRAINT PRIMARY KEY (PROCESSID);

Atualização

CREATE TABLE TBTRACKCUSTOMFIELDS

(

 CUSTOMID SERIAL8(1) NOT NULL,

 EVENTID INT8 NOT NULL,

 TYPE SMALLINT NOT NULL,

 VALUE LVARCHAR(MAX)

);

ALTER TABLE TBTRACKCUSTOMFIELDS ADD CONSTRAINT PRIMARY KEY (CUSTOMID);

ALTER TABLE TBTRACKCUSTOMFIELDS ADD CONSTRAINT FOREIGN KEY(EVENTID) REFERENCES TBTRACKEVENTS (EVENTID) CONSTRAINT FK_TBTRACKCUSTOMFIELDS_TBTRACKEVENTS;

ALTER TABLE TBTRACKSEARCH

ADD OBSERVATION LVARCHAR(MAX);