swdev:howto:create_database_using_firebird [Audina Documentation]

This is an old revision of the document!

Create Database Using Firebird

Patient table

/******************************************************************************/
/*                 Generated by IBExpert 10/19/2010 16:29:54                  */
/******************************************************************************/
 
SET NAMES NONE;
 
 
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
 
 
CREATE GENERATOR patient_no_gen;
 
CREATE TABLE patient (
    patientno    INTEGER NOT NULL,
    patientid    VARCHAR(13),
    firstname    VARCHAR(25),
    lastname     VARCHAR(40) NOT NULL,
    STYLE        SMALLINT,
    speechtest   VARCHAR(1),
    leftserial   VARCHAR(12),
    rightserial  VARCHAR(12),
    leftsrt      VARCHAR(12),
    rightsrt     VARCHAR(12),
    bisrt        VARCHAR(12),
    leftmcl      VARCHAR(12),
    rightmcl     VARCHAR(12),
    bimcl        VARCHAR(12),
    leftucl      VARCHAR(12),
    rightucl     VARCHAR(12),
    biucl        VARCHAR(12),
    birthdate    DATE,
    gender       CHAR(1),
    address1     VARCHAR(40),
    address2     VARCHAR(40),
    city         VARCHAR(40),
    state        VARCHAR(35),
    country      VARCHAR(40),
    zip          VARCHAR(10),
    email        VARCHAR(50),
    homephone    VARCHAR(20),
    workphone    VARCHAR(20),
    mobilephone  VARCHAR(20),
    rag250       SMALLINT DEFAULT -1 NOT NULL,
    rag500       SMALLINT DEFAULT -1 NOT NULL,
    rag750       SMALLINT DEFAULT -1 NOT NULL,
    rag1000      SMALLINT DEFAULT -1 NOT NULL,
    rag1500      SMALLINT DEFAULT -1 NOT NULL,
    rag2000      SMALLINT DEFAULT -1 NOT NULL,
    rag3000      SMALLINT DEFAULT -1 NOT NULL,
    rag4000      SMALLINT DEFAULT -1 NOT NULL,
    rag6000      SMALLINT DEFAULT -1 NOT NULL,
    rag8000      SMALLINT DEFAULT -1 NOT NULL,
    lag250       SMALLINT DEFAULT -1 NOT NULL,
    lag500       SMALLINT DEFAULT -1 NOT NULL,
    lag750       SMALLINT DEFAULT -1 NOT NULL,
    lag1000      SMALLINT DEFAULT -1 NOT NULL,
    lag1500      SMALLINT DEFAULT -1 NOT NULL,
    lag2000      SMALLINT DEFAULT -1 NOT NULL,
    lag3000      SMALLINT DEFAULT -1 NOT NULL,
    lag4000      SMALLINT DEFAULT -1 NOT NULL,
    lag6000      SMALLINT DEFAULT -1 NOT NULL,
    lag8000      SMALLINT DEFAULT -1 NOT NULL
);
 
 
 
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
 
ALTER TABLE patient ADD CONSTRAINT pk_patient PRIMARY KEY (patientno);
 
 
/******************************************************************************/
/*                                  Indices                                   */
/******************************************************************************/
 
CREATE INDEX firstnamex ON patient (firstname);
CREATE INDEX lastfirstnmx ON patient (lastname, firstname);
CREATE INDEX lastnamex ON patient (lastname);
CREATE INDEX leftserialx ON patient (leftserial);
CREATE INDEX patientidx ON patient (patientid);
CREATE INDEX rightserialx ON patient (rightserial);
 
 
/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/
 
 
SET TERM ^ ;
 
 
/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/
 
 
 
/* Trigger: SET_PATIENT_NO */
CREATE OR ALTER TRIGGER set_patient_no FOR patient
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.patientno IS NULL) THEN
  NEW.patientno = gen_id(patient_no_gen,1);
END
^
 
 
SET TERM ; ^
 
 
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

Fitting table

/******************************************************************************/
/*                 Generated by IBExpert 10/19/2010 16:38:37                  */
/******************************************************************************/
 
SET NAMES NONE;
 
 
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
 
 
 
CREATE TABLE fitting (
    patientno           INTEGER NOT NULL,
    fittingdate         DATE NOT NULL,
    ear                 SMALLINT NOT NULL,
    instrumentid        VARCHAR(5) NOT NULL,
    maxmemory           SMALLINT,
    toggle              SMALLINT,
    uservc              SMALLINT,
    fittingmode         SMALLINT,
    productno           SMALLINT,
    productname         VARCHAR(25),
    notes               VARCHAR(300),
    serialno            VARCHAR(12),
    memchangetone       SMALLINT,
    memchangemultibeep  SMALLINT,
    prescriptiontype    SMALLINT DEFAULT 0
);
 
 
 
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
 
ALTER TABLE fitting ADD CONSTRAINT pk_fitting 
  PRIMARY KEY (patientno, fittingdate, ear, instrumentid);
 
 
/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/
 
ALTER TABLE fitting ADD CONSTRAINT fk_fitting_patient FOREIGN KEY (patientno) 
  REFERENCES patient (patientno) ON DELETE CASCADE ON UPDATE CASCADE;
 
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

Style table

/******************************************************************************/
/*                 Generated by IBExpert 10/19/2010 16:43:46                  */
/******************************************************************************/
 
SET NAMES NONE;
 
 
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
 
 
 
CREATE TABLE stylephotos (
    STYLE       INTEGER,
    stylephoto  BLOB SUB_TYPE 0 SEGMENT SIZE 80
);
 
 
 
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/