|
Banco de arquivos - EdusoftBase |
Top Previous Next |
|
Esse banco é responsável por fazer o armazenamento de arquivos.
O nome do Banco a ser criado fica a critério do cliente, lembrando que deverá substituir o nome EDUSOFT_BASE pelo nome do Banco criado.
SQLServer:
CREATE DATABASE EDUSOFT_BASE
Oracle:
CREATE USER "EDUSOFT_BASE" PROFILE "DEFAULT" IDENTIFIED BY "EDUSOFT_BASE" DEFAULT TABLESPACE "TS_MENTORWEB" QUOTA UNLIMITED ON TS_MENTORWEB ACCOUNT UNLOCK;
GRANT "CONNECT" TO "EDUSOFT_BASE" WITH ADMIN OPTION;
GRANT "DBA" TO "EDUSOFT_BASE" WITH ADMIN OPTION;
SQLServer:
IF NOT EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = 'TB_ARQUIVO') CREATE TABLE TB_ARQUIVO ( ARQ_ID INTEGER IDENTITY(1,1) NOT NULL, ARQ_ARQUIVO IMAGE NOT NULL, ARQ_TIPO VARCHAR (50) NULL, ARQ_NOMARQ VARCHAR (200) NULL, ARQ_CODTEL VARCHAR (200) NULL
GO
IF NOT EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = 'TB_ARQUIVO_ENTIDADE') CREATE TABLE TB_ARQUIVO_ENTIDADE ( AEN_ID INTEGER IDENTITY(1,1) NOT NULL, AEN_ARQID INTEGER NOT NULL, AEN_ENTID INTEGER NULL, AEN_ENTCLA VARCHAR (255) NULL, AEN_DADOS_ENTIDADE VARCHAR (4000) NULL
GO
IF NOT EXISTS (SELECT 1 FROM SYS.KEY_CONSTRAINTS K INNER JOIN SYS.TABLES T ON T.OBJECT_ID = K.PARENT_OBJECT_ID WHERE T.NAME = 'TB_ARQUIVO' AND K.NAME = 'PK_ARQ') ALTER TABLE TB_ARQUIVO ADD CONSTRAINT PK_ARQ PRIMARY KEY (ARQ_ID) GO
IF NOT EXISTS (SELECT 1 FROM SYS.KEY_CONSTRAINTS K INNER JOIN SYS.TABLES T ON T.OBJECT_ID = K.PARENT_OBJECT_ID WHERE T.NAME = 'TB_ARQUIVO_ENTIDADE' AND K.NAME = 'PK_AEN') ALTER TABLE TB_ARQUIVO_ENTIDADE ADD CONSTRAINT PK_AEN PRIMARY KEY (AEN_ID) GO
IF NOT EXISTS (SELECT 1 FROM SYS.FOREIGN_KEYS F INNER JOIN SYS.TABLES T ON T.OBJECT_ID = F.PARENT_OBJECT_ID WHERE T.NAME = 'TB_ARQUIVO_ENTIDADE' AND F.NAME = 'FK_ARQ_AEN') ALTER TABLE TB_ARQUIVO_ENTIDADE ADD CONSTRAINT FK_ARQ_AEN FOREIGN KEY (AEN_ARQID) REFERENCES TB_ARQUIVO (ARQ_ID) GO
IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES I INNER JOIN SYS.TABLES T ON T.OBJECT_ID = I.OBJECT_ID WHERE T.NAME = 'TB_ARQUIVO_ENTIDADE' AND I.NAME = 'IDX_FK_ARQ_AEN') CREATE INDEX IDX_FK_ARQ_AEN ON TB_ARQUIVO_ENTIDADE (AEN_ARQID) GO
IF NOT EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = 'TB_DOCUMENTO_AUTENTICACAO') CREATE TABLE TB_DOCUMENTO_AUTENTICACAO ( DAU_ID INTEGER IDENTITY(1,1) NOT NULL, DAU_CODTEL VARCHAR (30) NOT NULL, DAU_VALIDADE INTEGER NULL, DAU_DESCRI VARCHAR (255) NOT NULL, DAU_TIPODOC VARCHAR (50) NOT NULL, ATIVO INTEGER NOT NULL, DATA_ULT_ALTERACAO DATETIME NOT NULL, USUARIO_ULT_ALTERACAO VARCHAR (255) NOT NULL
GO
IF NOT EXISTS (SELECT 1 FROM SYS.KEY_CONSTRAINTS K INNER JOIN SYS.TABLES T ON T.OBJECT_ID = K.PARENT_OBJECT_ID WHERE T.NAME = 'TB_DOCUMENTO_AUTENTICACAO' AND K.NAME = 'PK_DAU') ALTER TABLE TB_DOCUMENTO_AUTENTICACAO ADD CONSTRAINT PK_DAU PRIMARY KEY (DAU_ID) GO
Oracle:
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(OBJECT_ID) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'EDUSOFT_BASE' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TB_ARQUIVO'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE TABLE EDUSOFT_BASE.TB_ARQUIVO ( ARQ_ID NUMBER (10 , 0) NOT NULL, ARQ_ARQUIVO BLOB NOT NULL, ARQ_TIPO VARCHAR2 (50) NULL, ARQ_NOMARQ VARCHAR2 (200) NULL, ARQ_CODTEL VARCHAR2 (200) NULL )'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(OBJECT_ID) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'EDUSOFT_BASE' AND OBJECT_TYPE = 'SEQUENCE' AND OBJECT_NAME = 'ARQ_ID'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE EDUSOFT_BASE.ARQ_ID MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(OBJECT_ID) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'EDUSOFT_BASE' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TB_ARQUIVO_ENTIDADE'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE TABLE EDUSOFT_BASE.TB_ARQUIVO_ENTIDADE ( AEN_ID NUMBER (10 , 0) NOT NULL, AEN_ARQID NUMBER (10 , 0) NOT NULL, AEN_ENTID NUMBER (10 , 0) NULL, AEN_ENTCLA VARCHAR2 (255) NULL, AEN_DADOS_ENTIDADE VARCHAR2 (4000) NULL )'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(OBJECT_ID) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'EDUSOFT_BASE' AND OBJECT_TYPE = 'SEQUENCE' AND OBJECT_NAME = 'AEN_ID'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE EDUSOFT_BASE.AEN_ID MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(CONSTRAINT_NAME) INTO V_COUNT FROM DBA_CONSTRAINTS WHERE OWNER = 'EDUSOFT_BASE' AND TABLE_NAME = 'TB_ARQUIVO' AND CONSTRAINT_NAME = 'PK_ARQ'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE EDUSOFT_BASE.TB_ARQUIVO ADD CONSTRAINT PK_ARQ PRIMARY KEY (ARQ_ID)'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(CONSTRAINT_NAME) INTO V_COUNT FROM DBA_CONSTRAINTS WHERE OWNER = 'EDUSOFT_BASE' AND TABLE_NAME = 'TB_ARQUIVO_ENTIDADE' AND CONSTRAINT_NAME = 'PK_AEN'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE EDUSOFT_BASE.TB_ARQUIVO_ENTIDADE ADD CONSTRAINT PK_AEN PRIMARY KEY (AEN_ID)'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(CONSTRAINT_NAME) INTO V_COUNT FROM DBA_CONSTRAINTS WHERE OWNER = 'EDUSOFT_BASE' AND TABLE_NAME = 'TB_ARQUIVO_ENTIDADE' AND CONSTRAINT_NAME = 'FK_ARQ_AEN'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE EDUSOFT_BASE.TB_ARQUIVO_ENTIDADE ADD CONSTRAINT FK_ARQ_AEN FOREIGN KEY (AEN_ARQID) REFERENCES EDUSOFT_BASE.TB_ARQUIVO (ARQ_ID)'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(INDEX_NAME) INTO V_COUNT FROM DBA_INDEXES WHERE OWNER = 'EDUSOFT_BASE' AND TABLE_NAME = 'TB_ARQUIVO_ENTIDADE' AND INDEX_NAME = 'IDX_FK_ARQ_AEN'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE INDEX IDX_FK_ARQ_AEN ON EDUSOFT_BASE.TB_ARQUIVO_ENTIDADE (AEN_ARQID)'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(OBJECT_ID) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'EDUSOFT_BASE' AND OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TB_DOCUMENTO_AUTENTICACAO'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE TABLE EDUSOFT_BASE.TB_DOCUMENTO_AUTENTICACAO ( DAU_ID NUMBER (10 , 0) NOT NULL, DAU_CODTEL VARCHAR2 (30) NOT NULL, DAU_VALIDADE NUMBER (10 , 0) NULL, DAU_DESCRI VARCHAR2 (255) NOT NULL, DAU_TIPODOC VARCHAR2 (50) NOT NULL, ATIVO NUMBER (10 , 0) NOT NULL, DATA_ULT_ALTERACAO TIMESTAMP NOT NULL, USUARIO_ULT_ALTERACAO VARCHAR2 (255) NOT NULL )'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(OBJECT_ID) INTO V_COUNT FROM DBA_OBJECTS WHERE OWNER = 'EDUSOFT_BASE' AND OBJECT_TYPE = 'SEQUENCE' AND OBJECT_NAME = 'DAU_ID'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'CREATE SEQUENCE EDUSOFT_BASE.DAU_ID MINVALUE 1 MAXVALUE 999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
DECLARE V_COUNT NUMBER (10,0); BEGIN BEGIN SELECT COUNT(CONSTRAINT_NAME) INTO V_COUNT FROM DBA_CONSTRAINTS WHERE OWNER = 'EDUSOFT_BASE' AND TABLE_NAME = 'TB_DOCUMENTO_AUTENTICACAO' AND CONSTRAINT_NAME = 'PK_DAU'; EXCEPTION WHEN NO_DATA_FOUND THEN V_COUNT :=0; END; IF (V_COUNT = 0) THEN EXECUTE IMMEDIATE 'ALTER TABLE EDUSOFT_BASE.TB_DOCUMENTO_AUTENTICACAO ADD CONSTRAINT PK_DAU PRIMARY KEY (DAU_ID)'; END IF; END;
|