Creating a Firebird Database
Following our plan, we created the database and Programmer table with this code typed at the SQL> and CON> prompts within isql. The subset of SQL used to define the structure of a database is known as a Data Definition Language (DDL). Keywords include CREATE, DROP, DATABASE, TABLE, GRANT, KEY, PRIMARY, FOREIGN, REFERENCES, NULL, CHAR, VARCHAR, INT, DATE and CURRENCY.
CREATE DATABASE 'F:\Firebird2\Contributions.fdb' page_size 8192 user 'student' password 'pp4s'; CREATE TABLE Programmer (ID INT NOT NULL PRIMARY KEY, Forename VARCHAR(15) NOT NULL, Surname VARCHAR(15), YearGroup VARCHAR(3), Age INT NOT NULL CHECK (AGE > 10 AND Age < 20)); GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON Programmer TO PUBLIC WITH GRANT OPTION;
We confirmed that the Programmer Table had been created as intended:SHOW TABLE Programmer;
ID INTEGER Not Null FORENAME VARCHAR(15) Not Null SURNAME VARCHAR(15) Nullable YEARGROUP VARCHAR(3) Nullable AGE INTEGER Not Null CONSTRAINT INTEG_2: Primary key (ID) CONSTRAINT INTEG_5: CHECK (AGE > 10 AND Age < 20)
NB Typing EXIT; at the SQL> prompt of the isql tool causes changes to the database to be saved but the command QUIT; rolls back any changes you have made in the current session.
The following code creates the Programs table and grants rights to it.
CREATE TABLE Programs (Name VARCHAR(20) NOT NULL PRIMARY KEY, Description VARCHAR(50), Category VARCHAR(20), ProgrammerID INT NOT NULL, FOREIGN KEY (ProgrammerID) REFERENCES Programmer(ID)); GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON Programs TO PUBLIC WITH GRANT OPTION;
As before, we confirmed the result of our code.SHOW TABLE Programs;
NAME VARCHAR(20) Not Null DESCRIPTION VARCHAR(50) Nullable CATEGORY VARCHAR(20) Nullable PROGRAMMERID INTEGER Nullable CONSTRAINT INTEG_30: Foreign key (PROGRAMMERID) References PROGRAMMER (ID) CONSTRAINT INTEG_26: Primary key (NAME)
The subset of SQL used to modify the database date is known as a Data Manipulation Language (DML). This DML has amongst its keywords SELECT, FROM, WHERE, ORDER, GROUP, BY, LIKE, ASC, DESC, INSERT, INTO, DELETE, VALUES, COUNT, AVG, MAX and MIN. We were able to create records using isql e.g.
INSERT INTO Programmer VALUES (1,'Matt','Tucker','L6',16);
provided that we did not violate any constraints:
INSERT INTO Programs VALUES ('Test','Test FK','Test',100);
Statement failed, SQLSTATE = 23000 violation of FOREIGN KEY constraint "INTEG_30" on table "PROGRAMS" -Foreign key reference target does not exist
Finally, we deleted this database and tested to make sure that we could create and populate the database from a script (in the file Contributions.sql). The script also adds stored procedures (see later section) to the database.INPUT 'F:\Firebird\Contributions.sql';
We supply the file together with the source for all database demonstration programs in db_contributions.zip so that you can try it. Put the unzipped Contributions.sql in a convenient folder and change the INPUT statement accordingly.We have not added a more recent contribution to the database. To do this, you can type the following at the SQL> and CON> prompts within isql;
INSERT INTO Programs (Name, Description, Category, ProgrammerID) Values
('Invader', 'Survival of a bug against white blood cells','Game',50);
Indexes speed up queries, and are very worthwhile for large numbers of records. They are prepared automatically for key fields and you can create others for single or multiple fields to match your queries. You can inactivate an index while adding many records then activate it afterwards, which will cause it to be rebuilt. These three commands show you how to create, deactivate and activate an index.
CREATE INDEX FullnameX ON Programmer (Surname, Forename);
ALTER INDEX FullnameX INACTIVE;
ALTER INDEX FullnameX ACTIVE;
Since Firebird is derived from Interbase the original manuals in this zip file are very helpful.