ACBDServFacil.sql

CREATE GENERATOR GEN_CLIENTE_FORNECEDOR;

CREATE TABLE CLIENTE_FORNECEDOR (
    ID              INTEGER NOT NULL,
    NOME            VARCHAR(100) NOT NULL,
    CAD_NACIONAL    CHAR(1) default 'F',
    CPF             VARCHAR(20),
    CNPJ            VARCHAR(20),
    IE              VARCHAR(20),
    CLI_FORN        CHAR(1) default 'C',
    PAIS            VARCHAR(100),
    UF              CHAR(2),
    CIDADE          VARCHAR(100),
    BAIRRO          VARCHAR(100),
    RUA             VARCHAR(100),
    CEP             VARCHAR(9),
    CELULAR         VARCHAR(15),
    DT_NASCIMENTO   DATE,
    DT_HR_CADASTRO  TIMESTAMP NOT NULL,
    SEXO            CHAR(1)
);

ALTER TABLE CLIENTE_FORNECEDOR ADD PRIMARY KEY (ID);

SET TERM ^ ;

/* Trigger: CLIENTE_FORNECEDOR_BI0 */
CREATE OR ALTER TRIGGER CLIENTE_FORNECEDOR_BI0 FOR CLIENTE_FORNECEDOR
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if(new.id is null) then
  new.id = gen_id(gen_cliente_fornecedor, 1);
end
^


SET TERM ; ^

SET SQL DIALECT 3;

CREATE TABLE ESPECIE (
    ID          INTEGER NOT NULL,
    ESPECIE     VARCHAR(50),
    TIPO        VARCHAR(30),
    ATALHO      CHAR(3),
    TX_ADM      DECIMAL(6,4),
    ASSOCIACAO  VARCHAR(30)
);

ALTER TABLE ESPECIE ADD PRIMARY KEY (ID);

SET SQL DIALECT 3;

CREATE GENERATOR GEN_ESTOQUE;

CREATE TABLE ESTOQUE (
    ID             INTEGER NOT NULL,
    PRODUTO        VARCHAR(150),
    QTDE           DECIMAL(15,4),
    PREC_CUSTO     DECIMAL(15,4),
    PREC_VENDA     DECIMAL(15,4),
    MARCA          VARCHAR(50),
    FORNECEDOR     VARCHAR(100),
    NCM_NBS        VARCHAR(10),
    ORIGEM         CHAR(2),
    CSOSN_CST      VARCHAR(6),
    CFOP           VARCHAR(6),
    ID_FORNECEDOR  INTEGER
);

ALTER TABLE ESTOQUE ADD PRIMARY KEY (ID);

ALTER TABLE ESTOQUE ADD CONSTRAINT FK_FORNECEDOR FOREIGN KEY (ID_FORNECEDOR) REFERENCES CLIENTE_FORNECEDOR (ID);

SET TERM ^ ;

/* Trigger: ESTOQUE_BI0 */
CREATE OR ALTER TRIGGER ESTOQUE_BI0 FOR ESTOQUE
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if(new.id is null) then
  new.id = gen_id(gen_estoque, 1);
end
^
SET TERM ; ^

SET SQL DIALECT 3;

CREATE TABLE FINANCEIRO (
    ID               INTEGER NOT NULL,
    DT_PREVISAO_PAG  DATE,
    DT_PAGAMENTO     DATE,
    DT_VENCIMENTO    DATE,
    VAL_JUROS        DECIMAL(15,4),
    VAL_ACRESCIMO    DECIMAL(15,4),
    VAL_ORIGINAL     DECIMAL(15,4),
    VAL_PAGAR        DECIMAL(15,4),
    VAL_PAGO         DECIMAL(15,4),
    TP_ESPECIE       VARCHAR(50),
    TIPO             VARCHAR(25),
    PERC_JUROS       DECIMAL(6,4),
    PERC_ACRESCIMO   DECIMAL(6,4),
    ORIGEM           VARCHAR(15),
    DOCUMENTO        VARCHAR(10),
    ID_ESPECIE       INTEGER NOT NULL
);

ALTER TABLE FINANCEIRO ADD PRIMARY KEY (ID);
ALTER TABLE FINANCEIRO ADD CONSTRAINT FK_ESPECIE FOREIGN KEY (ID_ESPECIE) REFERENCES ESPECIE (ID);

SET SQL DIALECT 3;

CREATE GENERATOR GEN_FUNCIONARIO;

CREATE TABLE FUNCIONARIO (
    ID              INTEGER NOT NULL,
    NOME            VARCHAR(100) NOT NULL,
    SEXO            CHAR(1),
    DT_NASCIMENTO   DATE,
    CONVENIO        CHAR(1) default 'N',
    CPF             VARCHAR(20),
    CELULAR         VARCHAR(15) NOT NULL,
    SALARIO         DECIMAL(15,4),
    PERC_COMISSAO   DECIMAL(15,2),
    CARGO           VARCHAR(25),
    USUARIO         VARCHAR(20) NOT NULL,
    SENHA           VARCHAR(255) NOT NULL,
    OBS             BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    DT_HR_CADASTRO  TIMESTAMP
);

ALTER TABLE FUNCIONARIO ADD PRIMARY KEY (ID);

SET TERM ^ ;

/* Trigger: FUNCIONARIO_BI0 */
CREATE OR ALTER TRIGGER FUNCIONARIO_BI0 FOR FUNCIONARIO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if(new.id is null) then
  new.id = gen_id(gen_funcionario, 1);
end
^
SET TERM ; ^

SET SQL DIALECT 3;

CREATE GENERATOR GEN_SERVICO;

CREATE TABLE SERVICO (
    ID            INTEGER NOT NULL,
    DT_HR_INICIO  TIMESTAMP,
    DT_HR_FINAL   TIMESTAMP,
    TOT_SERVICO   DECIMAL(15,2),
    STATUS        VARCHAR(20) NOT NULL,
    DT_PREVISAO   DATE,
    ID_VEICULO    INTEGER NOT NULL,
    ID_CLIENTE    INTEGER NOT NULL
);

ALTER TABLE SERVICO ADD PRIMARY KEY (ID);
ALTER TABLE SERVICO ADD CONSTRAINT FK_CLIENTE FOREIGN KEY (ID_CLIENTE) REFERENCES CLIENTE_FORNECEDOR (ID);
ALTER TABLE SERVICO ADD CONSTRAINT FK_VEICULO FOREIGN KEY (ID_VEICULO) REFERENCES VEICULO (ID);

SET TERM ^ ;

/* Trigger: SERVICO_BI0 */
CREATE OR ALTER TRIGGER SERVICO_BI0 FOR SERVICO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if(new.id is null) then
  new.id = gen_id(gen_servico, 1);
end
^

SET TERM ; ^

SET SQL DIALECT 3;

CREATE GENERATOR GEN_VEICULO;

CREATE TABLE VEICULO (
    ID              INTEGER NOT NULL,
    MODELO          VARCHAR(100) NOT NULL,
    POTENCIA_CV     INTEGER,
    ANO_FABRICAO    SMALLINT,
    ANO_MODELO      SMALLINT,
    CHASSI          VARCHAR(50),
    COR             VARCHAR(50),
    TRACAO          VARCHAR(15),
    RENAVAM         VARCHAR(15),
    DT_HR_CADASTRO  TIMESTAMP
);

ALTER TABLE VEICULO ADD PRIMARY KEY (ID);

SET TERM ^ ;

/* Trigger: VEICULO_BI0 */
CREATE OR ALTER TRIGGER VEICULO_BI0 FOR VEICULO
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  if(new.id is null) then
  new.id = gen_id(gen_veiculo, 1);
end
^

SET TERM ; ^