== 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: [[http://codefrenzy.blogspot.com/2005/11/run-embedded-sql-script-against.html|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:
* [[http://www.dotnetfirebird.org/blog/2005/03/using-fbconnectionstringbuilder.html}Using FbConnectionStringBuilder]]
* [[http://www.dotnetfirebird.org/blog/2005/03/batch-sqlddl-execution.html|Batch SQL/DDL Execution]]
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 [[http://www.flamerobin.org|FlameRobin]] which can also be downloaded for free.