Schema Scripts - Firebird

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:

  • Self-documentation. A script is a plain text file, easily handled in any development system, both for updating and reference. Scripts can —and should —include detailed comment text. Alterations to metadata can be signed and dated manually.
  • Control of database development. Scripting all database definitions allows schema creation to be integrated closely with design tasks and code review cycles.
  • Repeatable and traceable creation of metadata. A completely reconstructable schema is a requirement in the quality assurance and disaster recovery systems of many organizations.
  • Orderly construction and reconstruction of database metadata. Experienced Firebird programmers often create a set of DDL scripts, designed to run and commit in a specific order, to make debugging easy and ensure that objects will exist when later, dependent objects refer to them.

What Is in a DDL Script?

SQL Statements

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”).

Comments

A script can also contain comments, in two varieties.

Block Comments

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

Inline Comments

The /* .... */ comment style can also be embedded inside a statement as an inline comment:

One-Line Comments

In Firebird scripts you can use an alternative convention for commenting a single line: the double hyphen.

-- comment

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:

isql Statements

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.

Terminator Symbols

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:

Basic Steps

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.

CAUTION

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:

  • In an interactive isql session using the OUTPUT command to pass a series of DDL statements to a file.
  • In a plain ASCII text editor that formats line breaks according to the rules of the operating system shell in which the DDL script will be executed.
  • Using one of the many specialized script editor tools that are available in third- party admin tools for Firebird.
  • Using a CASE tool that can output DDL scripts according to the Firebird (InterBase) conventions.

    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:

  • On Windows the line terminator is a carriage return plus a line feed (ASCII 13 followed by ASCII 10).
  • On Linux/UNIX the line terminator is a line feed or a “newline” (ASCII 10).
  • On Mac OS X the line terminator is a newline (ASCII 10) and on native Macintosh it is a carriage return (ASCII 13).

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.

For example:

or

Committing Statements in Scripts

DDL Statements

Statements in DDL scripts can be committed in one or more of the following ways:

  • By including COMMIT statements at appropriate points in the script to ensure that new database objects are available to all subsequent statements that depend on them
  • By including this statement at the beginning of the script:
SET AUTODDL ON ;

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.

DML Statements

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.

Executing Scripts

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.

Disaster Recovery

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.

Development Control

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.

Metadata Extraction

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:

  • Firebird does not preserve comments when it stores metadata definitions. Many of the system tables do have a BLOB column, usually named RDB$DESCRIPTION, in which a single, contiguous piece of user-provided description can be stored. The metadata extract tool in isql does not output it, although some third-party tools do provide support for maintaining it.
  • All metadata extraction tools generate only the current metadata. There is no history of changes—dates, reasons, or authors.
  • Some tools, including isql, are known to generate metadata in the wrong sequence for dependency, making the scripts useless for regenerating the database without editing. Such a task is between tedious and impossible, depending on how well the repairer knows the metadata.
  • Even moderately sized databases may have an enormous number of objects, especially where the system design makes intensive use of embedded code modules. Very large scripts are prone to failure due to various execution or resource limits. Large, poorly organized scripts are also confusing and annoying to work with as documentation.

Manual Scripting

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

Sample Suite of Schema Scripts

FILE CONTENTS

Chaining 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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics