With Firebird, as with all true SQL database management systems, you build your database and its objects, the metadata or schema of a database, using statements from a specialized subset of SQL statements known as Data Definition Language, or DDL. A batch of DDL statements in a text file is known as a script. A script, or a set of scripts, can be processed by isql directly at the command line or through a tool that provides a user riendly interface for isql’s script-processing capability.
About Firebird Scripts
A script for creating and altering database objects is sometimes referred to as a data definition file or, more commonly, a DDL script. A DDL script can contain certain isql statements, specifically some of the SET <parameter> commands. COMMIT is also a valid statement in a script.
Other scripts can be written for inserting basic or “control” data into tables, updating columns, performing data conversions, and doing other maintenance tasks involving data manipulation. These are known as DML scripts (for Data Manipulation Language).
DDL and DML commands can be mixed in a script. However, to avoid data integrity problems, it is strongly recommended that DDL and DML should be split into separate scripts. Script processing allows “chaining” of scripts, linking one script file to another by means of the isql INPUT <filespec > statement.
Script statements are executed in strict order. Use of the SET AUTODDL command enables you to control where statements or blocks of statements will be committed. It is also an option to defer committing the contents of a script until the entire script has been processed.
Why Use Scripts?
It is very good practice to use DDL scripts to create your database and its objects. Some of the reasons include the following:
What Is in a DDL Script?
A DDL script consists of one or more SQL statements to CREATE, ALTER, or DROP a database or any other object. It can include DML statements, although it is recommended to keep DDL and DML statements in separate scripts.
Procedure language (PSQL) statements defining stored procedures and triggers can also be included. PSQL blocks get special treatment in scripts with regard to statement terminator symbols (see the upcoming section “Terminator Symbols”).
A script can also contain comments, in two varieties.
Block comments in DDL scripts use the C convention:
/* This comment can span multiple lines in a script */
A block comment can occur on the same line as a SQL statement or isql command and can be of any length, as long as it is preceded by /* and followed by */.
The /* .... */ comment style can also be embedded inside a statement as an inline comment:
In Firebird scripts you can use an alternative convention for commenting a single line: the double hyphen.
In release 1.0.x, this double-hyphen style of comment cannot be used for inline comments or for “commenting out” part of a line.
From release 1.5 forward, the -- commenting convention can be used anywhere on a line to “comment out” everything from the marker to the end of the current line, for example:
The isql commands SET AUTODDL, SET SQL DIALECT, SET TERM, and INPUT are valid statements in a Firebird script—see Chapter Interactive SQL Utility (isql) for details of these commands.
All statements that are to be executed in the script must end with a terminator symbol. The default symbol is the semicolon (;).
The default terminator can be overridden for all statements except procedure language statements (PSQL) by issuing a SET TERM command in the script.
Terminators and Procedure Language (PSQL)
PSQL does not permit any terminator other than the default semicolon (;). This restriction is necessary because CREATE PROCEDURE, RECREATE PROCEDURE, ALTER PROCEDURE, CREATE TRIGGER, and ALTER TRIGGER, together with their subsequent PSQL statements, are complex statements in their own right—statements within a statement. The compiler needs to see semicolons in order to recognize each distinct PSQL statement.
Thus, in scripts, it is necessary to override the terminator being used for script commands before commencing to issue the PSQL statements for a stored procedure or a trigger. After the last END statement of the procedure source has been terminated, the terminator should be reset to the default using another SET TERM statement. For example:
Any string may be used as an alternative terminator, for example:
The SQL statement silently fails if significant text follows the terminator character on the same line. White space and comments can safely follow the terminator, but other statements cannot.
For example, in the following sequence, the COMMIT statement will not be executed:ALTER TABLE ATABLE ADD F2 INTEGER; COMMIT;
whereas this one is fine:
The basic steps for using script files are outlined in the following sections.
Step 1: Create the Script File
Use any suitable text editor. At the learning stage, you might wish to follow each DDL statement with a COMMIT statement, to ensure that an object will be visible to subsequent statements. As you become more practiced, you will learn to commit statements in blocks, employing SET AUTODDL ON and SET AUTODDL OFF as a means of controlling interdependencies and testing/debugging scripts.
Step 2: Execute the Script
Use the INPUT command in an isql session or the Execute button (or equivalent) in your database management tool.
Isql on POSIX:
SQL> INPUT /data/scripts/myscript.sql;
Isql on Win32:
SQL> INPUT d:datascriptsmyscript.sql;
Step 3: View Output and Confirm Database Changes
Tools and Firebird isql versions vary in the information they return when a script trips up on a bad command. A feature added after Firebird 1.0 provides better script error reporting than previous versions.
How to Create Scripts
You can create DDL scripts in several ways, including the following:
You can use any text editor to create a SQL script file, as long as the final file format is plain text (ASCII) and has lines terminated according to the rules of your operating system shell:
Some editing tools provide the capability to save in different text formats. It may prove useful to be able to save Linux-compatible scripts on a Windows machine, for example. However, take care that you use an editor that saves only plain ASCII text.
A complete schema script file must begin with either a CREATE DATABASE statement or, if the database already exists, a CONNECT statement (including the user name and password in single quotes) that specifies the database on which the script file is to operate. The CONNECT or CREATE keyword must be followed by a complete, absolute database file name and directory path in single quotes.
Committing Statements in Scripts
Statements in DDL scripts can be committed in one or more of the following ways:
To turn off automatic commit of DDL in an isql script, use this:SET AUTODDL OFF ;
The ON and OFF keywords are optional. The abbreviation SET AUTO can be used as a two-way switch. For clarity of self-documentation, it is recommended that you use SET AUTODDL with the explicit ON and OFF keywords.
Autocommit in isql
If you are running your script in isql, changes to the database from data definition (DDL) statements—for example, CREATE and ALTER statements—are automatically committed by default. This means that other users of the database see changes as soon as each DDL statement is executed.
Some scripting tools deliberately turn off this autocommitting behavior when running scripts, since it can make debugging difficult. Make sure you understand the behavior of any third-party tool you use for scripts.
Changes made to the database by data manipulation (DML) statements—INSERT, UPDATE, and DELETE—are not permanent until they are committed. Explicitly include COMMIT statements in your script to commit DML changes.
To undo all database changes since the last COMMIT, use ROLLBACK. Committed changes cannot be rolled back.
DDL scripts can be executed in an interactive isql session using the INPUT command, as described in the previous summary. Many of the third-party tools have the ability to execute and even to intelligently debug scripts in a GUI environment.
Managing Your Schema Scripts
Keeping a well-organized suite of scripts that precisely reflects the up-to-date state of your metadata is a valuable practice that admirably satisfies the most rigorous quality assurance system. The use of ample commentary within scripts is highly recommended, as is archiving all script versions in a version control system.
The most obvious purpose of such a practice is to provide a “fallback of last resort” for disaster recovery. If worse comes to worst —a database is ruined and backups are lost—metadata can be reconstructed from scripts. Surviving data from an otherwise unrecoverable database can be reconstituted by experts and pumped back.
It is usually likely that more than one developer will work on the development of a database during its life cycle. Developers notoriously abhor writing system documentation! Keeping an annotated script record of every database change—including those applied interactively using isql or a third-party tool—is a painless and secure solution that works for everybody.
Several admin tools for Firebird, including isql, are capable of extracting metadata from a database for saving as a script file. While metadata extraction is a handy adjunct to your scripting, there are good reasons to treat these tools as “helpers” and make a point of maintaining your main schema scripts manually:
The author strongly advocates maintaining fully annotated schema scripts manually and splitting the mass into separate files. The sample suite of scripts in Table records and regenerates a database named leisurestore.fdb.
Sample Suite of Schema Scripts
A stable suite of scripts can be “chained” together using the isql INPUT statement as the last statement in the preceding script. For example, to chain leisurestore_02.sql to the end of leisurestore_01.sql, end the script this way:
The Master Script Approach
The INPUT statement is not restricted to being the last in a script. Hence, another useful approach to maintaining script suites is to have one “master” script that inputs each of the subsidiary scripts in order. It has the benefit of making it easy to maintain large suites, and you can include comments to indicate to others the contents of each input script.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.