SET SQL DIALECT 3;

SET NAMES NONE;

SET CLIENTLIB 'C:\Program Files\Firebird\Firebird_1_5\bin\fbclient.dll';

CREATE DATABASE '127.0.0.1:C:\TEMP\Test.fdb'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 4096
DEFAULT CHARACTER SET NONE;



/******************************************************************************/
/****                              Generateurs                             ****/
/******************************************************************************/

CREATE GENERATOR GEN_CLIENT_ID;
SET GENERATOR GEN_CLIENT_ID TO 0;



SET TERM ^ ; 



/******************************************************************************/
/****                          Procedures Stockées                         ****/
/******************************************************************************/

CREATE PROCEDURE PS_GEN_CLIENT_ID
RETURNS (
    ID INTEGER)
AS
BEGIN
  EXIT;
END^



SET TERM ; ^


/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE CIVILITE (
    IDCIVILITE  SMALLINT NOT NULL,
    LABEL       VARCHAR(10) NOT NULL
);

CREATE TABLE CLIENT (
    IDCLIENT   BIGINT NOT NULL,
    CIVILID    SMALLINT,
    NOM        VARCHAR(40) NOT NULL,
    PRENOM     VARCHAR(30),
    DESCRIPT   VARCHAR(60)
);



/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE CIVILITE ADD CONSTRAINT PK_CIVILITE PRIMARY KEY (IDCIVILITE);
ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (IDCLIENT);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE CLIENT ADD CONSTRAINT FK_CLIENT_CIV FOREIGN KEY (CIVILID) REFERENCES CIVILITE (IDCIVILITE);


/******************************************************************************/
/****                               Index                                  ****/
/******************************************************************************/

CREATE DESCENDING INDEX IDX_IDCLT_DESC ON CLIENT (IDCLIENT);
CREATE INDEX IDX_NOM_ASC ON CLIENT (NOM);
CREATE DESCENDING INDEX IDX_NOM_DESC ON CLIENT (NOM);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;

/* Trigger: CLIENT_BI */
CREATE TRIGGER CLIENT_BI FOR CLIENT
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDCLIENT IS NULL) THEN
    NEW.IDCLIENT = GEN_ID(GEN_CLIENT_ID,1);
END
^

SET TERM ; ^



/******************************************************************************/
/****                          Procedures Stockées                         ****/
/******************************************************************************/


SET TERM ^ ;

ALTER PROCEDURE PS_GEN_CLIENT_ID
RETURNS (
    ID INTEGER)
AS
BEGIN
  ID = GEN_ID(GEN_CLIENT_ID, 1);
  SUSPEND;
END
^


SET TERM ; ^

INSERT INTO CIVILITE (IDCIVILITE,LABEL) VALUES (0,'Monsieur');
INSERT INTO CIVILITE (IDCIVILITE,LABEL) VALUES (1,'Madame');
INSERT INTO CIVILITE (IDCIVILITE,LABEL) VALUES (2,'Mlle');

COMMIT;