swdev:howto:create_database_using_firebird [Audina Documentation]

This is an old revision of the document!

Create Database Using Firebird

Patient table

Supports the following features:

Generator with Triggers.
Primary Key.
Indices.

/******************************************************************************/
/*                 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                                 */
/******************************************************************************/

Product table

Supports the following features:

Primary Key.
Foreign Key.
Field Check Constraint.

/******************************************************************************/
/*                 Generated by IBExpert 10/19/2010 16:45:22                  */
/******************************************************************************/
SET NAMES NONE;
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE TABLE ethos (
    patientno        INTEGER NOT NULL,
    fittingdate      DATE NOT NULL,
    ear              SMALLINT NOT NULL,
    instrumentid     VARCHAR(5) NOT NULL,
    activemem        SMALLINT NOT NULL,
    input_mux        SMALLINT,
    preamp_gain0     SMALLINT,
    preamp_gain1     SMALLINT,
    c1_ratio         SMALLINT,
    c2_ratio         SMALLINT,
    c3_ratio         SMALLINT,
    c4_ratio         SMALLINT,
    c5_ratio         SMALLINT,
    c6_ratio         SMALLINT,
    c7_ratio         SMALLINT,
    c8_ratio         SMALLINT,
    c1_tk            SMALLINT,
    c2_tk            SMALLINT,
    c3_tk            SMALLINT,
    c4_tk            SMALLINT,
    c5_tk            SMALLINT,
    c6_tk            SMALLINT,
    c7_tk            SMALLINT,
    c8_tk            SMALLINT,
    c1_mpo           SMALLINT,
    c2_mpo           SMALLINT,
    c3_mpo           SMALLINT,
    c4_mpo           SMALLINT,
    c5_mpo           SMALLINT,
    c6_mpo           SMALLINT,
    c7_mpo           SMALLINT,
    c8_mpo           SMALLINT,
    beq1_gain        SMALLINT,
    beq2_gain        SMALLINT,
    beq3_gain        SMALLINT,
    beq4_gain        SMALLINT,
    beq5_gain        SMALLINT,
    beq6_gain        SMALLINT,
    beq7_gain        SMALLINT,
    beq8_gain        SMALLINT,
    beq9_gain        SMALLINT,
    beq10_gain       SMALLINT,
    beq11_gain       SMALLINT,
    beq12_gain       SMALLINT,
    expansion_ratio  SMALLINT,
    expansion_tk     SMALLINT,
    matrix_gain      SMALLINT,
    noise_reduction  SMALLINT,
    fbc_enable       SMALLINT,
    time_const       SMALLINT,
    numprog          SMALLINT,
    progswitchmode   SMALLINT,
    vc_map           SMALLINT,
    vc_range         SMALLINT,
    vc_pos           SMALLINT,
    dir_spacing      SMALLINT,
    ad_sens          SMALLINT,
    switch_tone      SMALLINT,
    lowbatwarning    SMALLINT,
    tone_frequency   SMALLINT,
    tone_level       SMALLINT,
    tone_reference   SMALLINT,
    noise_level      SMALLINT,
    autotelecoil     SMALLINT,
    poweronlevel     SMALLINT,
    powerondelay     SMALLINT,
    cal_input        SMALLINT,
    mic_cal          SMALLINT,
    enablehpmode     SMALLINT,
    autofit          SMALLINT
);
 
/* Check constraints definition */
ALTER TABLE ethos ADD CONSTRAINT chk_activemem_ethos CHECK (activemem BETWEEN 0 AND 3);
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
ALTER TABLE ethos ADD CONSTRAINT pk_ethos 
  PRIMARY KEY (patientno, fittingdate, ear, instrumentid, activemem);
 
/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/
ALTER TABLE ethos ADD CONSTRAINT fk_ethos 
  FOREIGN KEY (patientno, fittingdate, ear, instrumentid) 
  REFERENCES fitting (patientno, fittingdate, ear, instrumentid) 
  ON DELETE CASCADE ON UPDATE CASCADE;
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

Datalog table

Supports the following features:

Binary data BLOB.
Cascading on Delete and Update.

/******************************************************************************/
/*                 Generated by IBExpert 10/19/2010 16:57:22                  */
/******************************************************************************/
SET NAMES NONE;
 
/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/
CREATE TABLE datalog (
    patientno     INTEGER NOT NULL,
    fittingdate   DATE NOT NULL,
    ear           SMALLINT NOT NULL,
    instrumentid  VARCHAR(5) NOT NULL,
    logversion    SMALLINT NOT NULL,
    logdata       BLOB SUB_TYPE 2 SEGMENT SIZE 2048
);
 
/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/
ALTER TABLE datalog ADD CONSTRAINT pk_datalog 
  PRIMARY KEY (patientno, fittingdate, ear, instrumentid);
 
/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/
ALTER TABLE datalog ADD CONSTRAINT fk_datalog 
  FOREIGN KEY (patientno, fittingdate, ear, instrumentid) 
  REFERENCES fitting (patientno, fittingdate, ear, instrumentid) 
  ON DELETE CASCADE ON UPDATE CASCADE;
 
/******************************************************************************/
/*                                 Privileges                                 */
/******************************************************************************/

StylePhotos table

Supports the following features:

Image data BLOB.

/******************************************************************************/
/*                 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                                 */
/******************************************************************************/

Creating Database Programmatically (C#)

Source: Run an embedded SQL Script agains Firebird

Run an embedded SQL Script against Firebird

I had an idea that it would be great if my application could automatically create any database that it required.

I have in the past attempted to hardcode database construction routines but dislike this approach as it makes the code messy and difficult to maintain. Instead, I favoured the idea of executing a script containing my SQL statements which could be used to construct the database. The application would just simply execute the script, an approach I’ve used in the past with SQL Sever.

Whilst investigating further, I found these useful links:

However, distributing a separate script file would allow a user to change or view the contents of the file.

Having experimented with embedded resource files, I thought a similar approach would protect the script from abuse by the average user and only venerable to experienced programmers.

I tested various routines and arrived at the following working example:

void ButtonRunScriptClick(object sender, System.EventArgs e)
{
  //specify the database name
  string dbName = "test.fdb";
  //instantiate a new connection stringbuilder
  FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
  csb.Database = dbName;
  csb.UserID = "SYSDBA";
  csb.Password = "masterkey";
  csb.ServerType = 1;         //embedded server
  //instantiate a connection object
  FbConnection con = new FbConnection(csb.ToString());
  //check if the database exists
  if (System.IO.File.Exists(dbName) == false)
  //create the database as it didn’t exist
  FbConnection.CreateDatabase(csb.ToString());
  //run the script against the current connection
  RunScript("Test.Sql", con);
}
 
void RunScript(string ScriptName, FbConnection connection)
{
  //get a reference to the executing assembly
  System.Reflection.Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly();
  //instantiate a textReader object initialised using a stream to the embedded resource
  System.IO.TextReader textReader = new System.IO.StreamReader(assembly.GetManifestResourceStream(ScriptName));
  FbScript script = new FbScript(textReader);
  //parse the script
  script.Parse();
  //open the connection
  connection.Open();
  FbBatchExecution fbe = new FbBatchExecution(connection);
  foreach (string cmd in script.Results)
  {
    //add each sql statement to the batch
    fbe.SqlStatements.Add(cmd);
  }
  //execute the batch
  fbe.Execute();
  //close the connection
  connection.Close();
}

To test the above code, you will need to do the following:

Download and install the 'Firebird ADO.Net Provider' from DotNetFirebird
Once installed, create a new Windows Application and add a reference to your project for the FirebirdSql.Data.Firebird assebly loaded in the GAC.
Copy & Paste the sample code into your project.

Add the following Using Directives:

using FirebirdSql.Data.Firebird;
using FirebirdSql.Data.Firebird.Isql;

Download 'Embedded Firebird for Windows' from DotNetFirebird and extract ‘fbembed.dll’ and ‘firebird.msg’ from the zip archive. These files should be copied to your applications directory.
Create a text file named 'Test.Sql' to contain your Firebird SQL script and save it in your project directory. I would suggest including a CREATE TABLE statement such as:
```
CREATE TABLE CLIENTS (
client_id INTEGER NOT NULL,
firstname CHAR(20),
lastname CHAR(20),
PRIMARY KEY (client_id)); 
```
Note: The filename is case sensitive, if you choose a different filename you will need to amend the sample code to reflect this change.
Add the file you created in Step 4 to your project and set the build action to Embedded Resource.
Add a button to your form named buttonRunScript and check that its Click Event is set to the ButtonRunScriptClick Event Handler.

Hopefully, when you build the project and click the Run Script button, a file will be created in the applications directory named TEST.FDB.

Providing there were no errors during the applications execution the database should have been constructed according to the statements in the SQL script.

There are several Database Admin Tools you can use to inspect/update and alter the database, one of which is FlameRobin which can also be downloaded for free.