Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
swdev:howto:create_database_using_firebird [2010/10/19 16:46] smayr |
swdev:howto:create_database_using_firebird [2011/02/25 16:17] (current) smayr [Run an embedded SQL Script against Firebird] |
||
---|---|---|---|
Line 1: | Line 1: | ||
== Create Database Using Firebird == | == Create Database Using Firebird == | ||
+ | === Patient table === | ||
+ | |||
+ | Supports the following features: | ||
+ | * Generator with Triggers. | ||
+ | * Primary Key. | ||
+ | * Indices. | ||
- | === Patient table === | ||
<code sql> | <code sql> | ||
/ | / | ||
/* | /* | ||
/ | / | ||
- | |||
SET NAMES none; | SET NAMES none; | ||
- | |||
- | |||
/ | / | ||
/* | /* | ||
/ | / | ||
- | |||
- | |||
CREATE GENERATOR patient_no_gen; | CREATE GENERATOR patient_no_gen; | ||
Line 69: | Line 69: | ||
lag8000 | lag8000 | ||
); | ); | ||
- | |||
- | |||
- | |||
/ | / | ||
/* Primary Keys */ | /* Primary Keys */ | ||
/ | / | ||
- | |||
ALTER TABLE patient ADD CONSTRAINT pk_patient PRIMARY KEY (patientno); | ALTER TABLE patient ADD CONSTRAINT pk_patient PRIMARY KEY (patientno); | ||
- | |||
/ | / | ||
/* Indices | /* Indices | ||
/ | / | ||
- | + | CREATE INDEX firstnamex | |
- | CREATE INDEX firstnamex ON patient (firstname); | + | |
CREATE INDEX lastfirstnmx ON patient (lastname, firstname); | CREATE INDEX lastfirstnmx ON patient (lastname, firstname); | ||
- | CREATE INDEX lastnamex ON patient (lastname); | + | CREATE INDEX lastnamex |
- | CREATE INDEX leftserialx ON patient (leftserial); | + | CREATE INDEX leftserialx |
- | CREATE INDEX patientidx ON patient (patientid); | + | CREATE INDEX patientidx |
CREATE INDEX rightserialx ON patient (rightserial); | CREATE INDEX rightserialx ON patient (rightserial); | ||
- | |||
/ | / | ||
/* Triggers | /* Triggers | ||
/ | / | ||
- | |||
- | |||
SET TERM ^ ; | SET TERM ^ ; | ||
- | |||
/ | / | ||
/* Triggers for tables | /* Triggers for tables | ||
/ | / | ||
- | |||
- | |||
- | |||
/* Trigger: SET_PATIENT_NO */ | /* Trigger: SET_PATIENT_NO */ | ||
CREATE OR ALTER TRIGGER set_patient_no FOR patient | CREATE OR ALTER TRIGGER set_patient_no FOR patient | ||
Line 115: | Line 102: | ||
end | end | ||
^ | ^ | ||
- | |||
SET TERM ; ^ | SET TERM ; ^ | ||
- | |||
- | |||
/ | / | ||
Line 125: | Line 109: | ||
/ | / | ||
</ | </ | ||
- | |||
=== Fitting table === | === Fitting table === | ||
<code sql> | <code sql> | ||
- | |||
/ | / | ||
/* | /* | ||
/ | / | ||
- | |||
SET NAMES none; | SET NAMES none; | ||
- | |||
- | |||
/ | / | ||
/* | /* | ||
/ | / | ||
- | |||
- | |||
- | |||
CREATE TABLE fitting ( | CREATE TABLE fitting ( | ||
patientno | patientno | ||
Line 160: | Line 136: | ||
prescriptiontype | prescriptiontype | ||
); | ); | ||
- | |||
- | |||
- | |||
/ | / | ||
/* Primary Keys */ | /* Primary Keys */ | ||
/ | / | ||
- | |||
ALTER TABLE fitting ADD CONSTRAINT pk_fitting | ALTER TABLE fitting ADD CONSTRAINT pk_fitting | ||
PRIMARY KEY (patientno, fittingdate, | PRIMARY KEY (patientno, fittingdate, | ||
- | |||
/ | / | ||
/* Foreign Keys */ | /* Foreign Keys */ | ||
/ | / | ||
+ | ALTER TABLE fitting ADD CONSTRAINT fk_fitting_patient | ||
+ | FOREIGN KEY (patientno) | ||
+ | REFERENCES patient (patientno) | ||
+ | ON DELETE CASCADE ON UPDATE CASCADE; | ||
- | ALTER TABLE fitting ADD CONSTRAINT fk_fitting_patient FOREIGN KEY (patientno) | + | / |
- | | + | /* |
+ | / | ||
+ | </ | ||
+ | === Product table === | ||
+ | |||
+ | Supports the following features: | ||
+ | * Primary Key. | ||
+ | * Foreign Key. | ||
+ | * Field Check Constraint. | ||
+ | |||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES none; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE TABLE ethos ( | ||
+ | patientno | ||
+ | fittingdate | ||
+ | ear SMALLINT NOT NULL, | ||
+ | instrumentid | ||
+ | activemem | ||
+ | input_mux | ||
+ | preamp_gain0 | ||
+ | preamp_gain1 | ||
+ | c1_ratio | ||
+ | c2_ratio | ||
+ | c3_ratio | ||
+ | c4_ratio | ||
+ | c5_ratio | ||
+ | c6_ratio | ||
+ | c7_ratio | ||
+ | c8_ratio | ||
+ | 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 | ||
+ | c2_mpo | ||
+ | c3_mpo | ||
+ | c4_mpo | ||
+ | c5_mpo | ||
+ | c6_mpo | ||
+ | c7_mpo | ||
+ | c8_mpo | ||
+ | beq1_gain | ||
+ | beq2_gain | ||
+ | beq3_gain | ||
+ | beq4_gain | ||
+ | beq5_gain | ||
+ | beq6_gain | ||
+ | beq7_gain | ||
+ | beq8_gain | ||
+ | beq9_gain | ||
+ | beq10_gain | ||
+ | beq11_gain | ||
+ | beq12_gain | ||
+ | expansion_ratio | ||
+ | expansion_tk | ||
+ | matrix_gain | ||
+ | noise_reduction | ||
+ | fbc_enable | ||
+ | time_const | ||
+ | numprog | ||
+ | progswitchmode | ||
+ | vc_map | ||
+ | vc_range | ||
+ | vc_pos | ||
+ | dir_spacing | ||
+ | ad_sens | ||
+ | switch_tone | ||
+ | lowbatwarning | ||
+ | tone_frequency | ||
+ | tone_level | ||
+ | tone_reference | ||
+ | noise_level | ||
+ | autotelecoil | ||
+ | poweronlevel | ||
+ | powerondelay | ||
+ | cal_input | ||
+ | mic_cal | ||
+ | enablehpmode | ||
+ | autofit | ||
+ | ); | ||
+ | |||
+ | /* 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, | ||
+ | |||
+ | / | ||
+ | /* Foreign Keys */ | ||
+ | / | ||
+ | ALTER TABLE ethos ADD CONSTRAINT fk_ethos | ||
+ | FOREIGN KEY (patientno, fittingdate, | ||
+ | REFERENCES fitting (patientno, fittingdate, | ||
+ | ON DELETE CASCADE ON UPDATE CASCADE; | ||
/ | / | ||
Line 184: | Line 266: | ||
/ | / | ||
</ | </ | ||
+ | === Datalog table === | ||
+ | |||
+ | Supports the following features: | ||
+ | * Binary data BLOB. | ||
+ | * Cascading on Delete and Update. | ||
- | === Style table === | ||
<code sql> | <code sql> | ||
/ | / | ||
- | /* | + | /* |
/ | / | ||
- | |||
SET NAMES none; | SET NAMES none; | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | CREATE TABLE datalog ( | ||
+ | patientno | ||
+ | fittingdate | ||
+ | ear | ||
+ | instrumentid | ||
+ | logversion | ||
+ | logdata | ||
+ | ); | ||
+ | / | ||
+ | /* Primary Keys */ | ||
+ | / | ||
+ | ALTER TABLE datalog ADD CONSTRAINT pk_datalog | ||
+ | PRIMARY KEY (patientno, fittingdate, | ||
/ | / | ||
- | /* Tables | + | /* |
/ | / | ||
+ | ALTER TABLE datalog ADD CONSTRAINT fk_datalog | ||
+ | FOREIGN KEY (patientno, fittingdate, | ||
+ | REFERENCES fitting (patientno, fittingdate, | ||
+ | ON DELETE CASCADE ON UPDATE CASCADE; | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | </ | ||
+ | === StylePhotos table === | ||
+ | |||
+ | Supports the following features: | ||
+ | * Image data BLOB. | ||
+ | |||
+ | <code sql> | ||
+ | / | ||
+ | /* | ||
+ | / | ||
+ | SET NAMES none; | ||
+ | |||
+ | / | ||
+ | /* | ||
+ | / | ||
CREATE TABLE stylephotos ( | CREATE TABLE stylephotos ( | ||
style | style | ||
stylephoto | stylephoto | ||
); | ); | ||
- | |||
- | |||
- | |||
/ | / | ||
Line 213: | Line 333: | ||
/ | / | ||
</ | </ | ||
+ | |||
+ | == Creating Database Programmatically (C#) == | ||
+ | Source: [[http:// | ||
+ | |||
+ | === 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:// | ||
+ | * [[http:// | ||
+ | |||
+ | 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: | ||
+ | <code csharp> | ||
+ | void ButtonRunScriptClick(object sender, System.EventArgs e) | ||
+ | { | ||
+ | //specify the database name | ||
+ | string dbName = " | ||
+ | // | ||
+ | FbConnectionStringBuilder csb = new FbConnectionStringBuilder(); | ||
+ | csb.Database = dbName; | ||
+ | csb.UserID = " | ||
+ | csb.Password = " | ||
+ | csb.ServerType = 1; // | ||
+ | // | ||
+ | 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(" | ||
+ | } | ||
+ | |||
+ | void RunScript(string ScriptName, FbConnection connection) | ||
+ | { | ||
+ | //get a reference to the executing assembly | ||
+ | System.Reflection.Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly(); | ||
+ | // | ||
+ | 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 ' | ||
+ | # 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: <code csharp> | ||
+ | using FirebirdSql.Data.Firebird; | ||
+ | using FirebirdSql.Data.Firebird.Isql;</ | ||
+ | # Download ' | ||
+ | # Create a text file named ' | ||
+ | CREATE TABLE CLIENTS ( | ||
+ | client_id integer not null, | ||
+ | firstname char(20), | ||
+ | lastname char(20), | ||
+ | PRIMARY KEY (client_id)); | ||
+ | # 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 '' | ||
+ | |||
+ | 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/ |