Chapter 5. Data Definition (DDL) Statements

DDL is the data definition language subset of Firebird’s SQL language. DDL statements are used to create, modify and delete database objects that have been created by users. When a DDL statement is committed, the metadata for the object are created, changed or deleted.

5.1 DATABASE

This section describes how to create a database, connect to an existing database, alter the file structure of a database and how to delete one. It also explains how to back up a database in two quite different ways and how to switch the database to the copy-safe mode for performing an external backup safely.

5.1.1 CREATE DATABASE

Used forCreating a new database

Available inDSQL, ESQL

Syntax

  1. CREATE {DATABASE | SCHEMA} <filespec>
  2. [<db_initial_option> [<db_initial_option> ...]]
  3. [<db_config_option> [<db_config_option> ...]]
  4. <db_initial_option> ::=
  5. USER username
  6. | PASSWORD 'password'
  7. | ROLE rolename
  8. | PAGE_SIZE [=] size
  9. | LENGTH [=] num [PAGE[S]]
  10. | SET NAMES 'charset'
  11. <db_config_option> ::=
  12. DEFAULT CHARACTER SET default_charset
  13. [COLLATION collation] -- not supported in ESQL
  14. | <sec_file>
  15. | DIFFERENCE FILE 'diff_file' -- not supported in ESQL
  16. <filespec> ::= "'" [server_spec]{filepath | db_alias} "'"
  17. <server_spec> ::=
  18. host[/{port | service}]:
  19. | \\host\
  20. | <protocol>://[host[:{port | service}]/]
  21. <protocol> ::= inet | inet4 | inet6 | wnet | xnet
  22. <sec_file> ::=
  23. FILE 'filepath'
  24. [LENGTH [=] num [PAGE[S]]
  25. [STARTING [AT [PAGE]] pagenum]

Note

Each db_initial_option and db_config_option can occur at most once, except sec_file, which can occur zero or more times.

Table 5.1.1.1 CREATE DATABASE Statement Parameters

ParameterDescription

filespec

File specification for primary database file

server_spec

Remote server specification. Some protocols require specifying a hostname. Optionally includes a port number or service name. Required if the database is created on a remote server.

filepath

Full path and file name including its extension. The file name must be specified according to the rules of the platform file system being used.

db_alias

Database alias previously created in the databases.conf file

host

Host name or IP address of the server where the database is to be created

port

The port number where the remote server is listening (parameter RemoteServicePort in firebird.conf file)

service

Service name. Must match the parameter value of RemoteServiceName in firebird.conf file)

username

Username of the owner of the new database. The maximum length is 63 characters. The username can optionally be enclosed in single or double quotes. When a username is enclosed in double quotes, it is case-sensitive following the rules for quoted identifiers. When enclosed in single quotes, it behaves as if the value was specified without quotes. The user must be an administrator or have the CREATE DATABASE privilege.

password

Password of the user as the database owner. When using the Legacy_Auth authentication plugin, only the first 8 characters are used. Case-sensitive

rolename

The name of the role whose rights should be taken into account when creating a database. The role name can be enclosed in single or double quotes. When the role name is enclosed in double quotes, it is case-sensitive following the rules for quoted identifiers. When enclosed in single quotes, it behaves as if the value was specified without quotes.

size

Page size for the database, in bytes. Possible values are 4096, 8192, 16384 and 32768. The default page size is 8192.

num

Maximum size of the primary database file, or a secondary file, in pages

charset

Specifies the character set of the connection available to a client connecting after the database is successfully created. Single quotes are required.

default_charset

Specifies the default character set for string data types

collation

Default collation for the default character set

sec_file

File specification for a secondary file

pagenum

Starting page number for a secondary database file

diff_file

File path and name for DIFFERENCE files (.delta files) for backup mode

The CREATE DATABASE statement creates a new database. You can use CREATE DATABASE or CREATE SCHEMA. They are synonymous, but we recommend to always use CREATE DATABASE as this may change in a future version of Firebird.

A database may consist of one or several files. The first (main) file is called the primary file, subsequent files are called secondary file(s).

Multi-file Databases

Nowadays, multi-file databases are considered an anachronism. It made sense to use multi-file databases on old file systems where the size of any file is limited. For instance, you could not create a file larger than 4 GB on FAT32.

The primary file specification is the name of the database file and its extension with the full path to it according to the rules of the OS platform file system being used. The database file must not exist at the moment the database is being created. If it does exist, you will get an error message, and the database will not be created.

If the full path to the database is not specified, the database will be created in one of the system directories. The particular directory depends on the operating system. For this reason, unless you have a strong reason to prefer that situation, always specify either the absolute path or an alias, when creating a database.

5.1.1.1 Using a Database Alias

You can use aliases instead of the full path to the primary database file. Aliases are defined in the databases.conf file in the following format:

  1. alias = filepath

Note

Executing a CREATE DATABASE statement requires special consideration in the client application or database driver. As a result, it is not always possible to execute a CREATE DATABASE statement. Some drivers provide other ways to create databases. For example, Jaybird provides the class org.firebirdsql.management.FBManager to programmatically create a database.

If necessary, you can always fallback to isql to create a database.

5.1.1.2 Creating a Database on a Remote Server

If you create a database on a remote server, you need to specify the remote server specification. The remote server specification depends on the protocol being used. If you use the TCP/IP protocol to create a database, the primary file specification should look like this:

  1. host[/{port|service}]:{filepath | db_alias}

If you use the Named Pipes protocol to create a database on a Windows server, the primary file specification should look like this:

  1. \\host\{filepath | db_alias}

Firebird also has a unified URL-like syntax for the remote server specification. In this syntax, the first part specifies the name of the protocol, then a host name or IP address, port number, and path of the primary database file, or an alias.

The following values can be specified as the protocol:

INET

TCP/IP (first tries to connect using the IPv6 protocol, if it fails, then IPv4)

INET4

TCP/IP v4

INET6

TCP/IP v6

WNET

NetBEUI or Named Pipes Protocol

XNET

local protocol (does not include a host, port and service name)

  1. <protocol>://[host[:{port | service}]/]{filepath | db_alias}

5.1.1.3 Optional Parameters for CREATE DATABASE

USER and PASSWORD

Clauses for specifying the username and the password, respectively, of an existing user in the security database (security4.fdb or whatever is configured in the SecurityDatabase configuration). You do not have to specify the username and password if the ISC_USER and ISC_PASSWORD environment variables are set. The user specified in the process of creating the database will be its owner. This will be important when considering database and object privileges.

ROLE

The ROLE clause specifies the name of the role (usually RDB$ADMIN), which will be taken into account when creating the database. The role must be assigned to the user in the applicable security database.

PAGE_SIZE

Clause for specifying the database page size. This size will be set for the primary file and all secondary files of the database. If you specify the database page size less than 4,096, it will be automatically rounded up to 4,096. Other values not equal to either 4,096, 8,192, 16,384 or 32,768 will be changed to the closest smaller supported value. If the database page size is not specified, it is set to the default value of 8,192.

Bigger Isn’t Always Better.

Larger page sizes can fit more records on a single page, have wider indexes, and more indexes, but they will also waste more space for blobs (compare the wasted space of a 3KB blob on page size 4096 with one on 32768: +/- 1KB vs +/- 29KB), and increase memory consumption of the page cache.

LENGTH

Clause specifying the maximum size of the primary or secondary database file, in pages. When a database is created, its primary and secondary files will occupy the minimum number of pages necessary to store the system data, regardless of the value specified in the LENGTH clause. The LENGTH value does not affect the size of the only (or last, in a multi-file database) file. The file will keep increasing its size automatically when necessary.

SET NAMES

Clause specifying the character set of the connection available after the database is successfully created. The character set NONE is used by default. Notice that the character set should be enclosed in a pair of apostrophes (single quotes).

DEFAULT CHARACTER SET

Clause specifying the default character set for creating data structures of string data types. Character sets are used for CHAR, VARCHAR and BLOB SUB_TYPE TEXT data types. The character set NONE is used by default. It is also possible to specify the default COLLATION for the default character set, making that collation sequence the default for the default character set. The default will be used for the entire database except where an alternative character set, with or without a specified collation, is used explicitly for a field, domain, variable, cast expression, etc.

STARTING AT

Clause that specifies the database page number at which the next secondary database file should start. When the previous file is completely filled with data according to the specified page number, the system will start adding new data to the next database file.

DIFFERENCE FILE

Clause specifying the path and name for the file delta that stores any mutations to the database file after it has been switched to the copy-safe mode by the ALTER DATABASE BEGIN BACKUP statement. For the detailed description of this clause, see ALTER DATABASE.

5.1.1.4 Specifying the Database Dialect

Databases are created in Dialect 3 by default. For the database to be created in SQL dialect 1, you will need to execute the statement SET SQL DIALECT 1 from script or the client application, e.g. in isql, before the CREATE DATABASE statement.

5.1.1.5 Who Can Create a Database

The CREATE DATABASE statement can be executed by:

5.1.1.6 Examples Using CREATE DATABASE

  1. Creating a database in Windows, located on disk D with a page size of 4,096. The owner of the database will be the user wizard. The database will be in Dialect , and will use WIN1251 as its default character set.

    1. SET SQL DIALECT 1;
    2. CREATE DATABASE 'D:\test.fdb'
    3. USER 'wizard' PASSWORD 'player'
    4. PAGE_SIZE = 4096 DEFAULT CHARACTER SET WIN1251;
  2. Creating a database in the Linux operating system with a page size of 8,192 (default). The owner of the database will be the user wizard. The database will be in Dialect 3 and will use UTF8 as its default character set, with UNICODE_CI_AI as the default collation.

    1. CREATE DATABASE '/home/firebird/test.fdb'
    2. USER 'wizard' PASSWORD 'player'
    3. DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
  3. Creating a database on the remote server baseserver with the path specified in the alias test that has been defined previously in the file databases.conf. The TCP/IP protocol is used. The owner of the database will be the user wizard. The database will be in Dialect 3 and will use UTF8 as its default character set.

    1. CREATE DATABASE 'baseserver:test'
    2. USER 'wizard' PASSWORD 'player'
    3. DEFAULT CHARACTER SET UTF8;
  4. Creating a database in Dialect 3 with UTF8 as its default character set. The primary file will contain up to 10,000 pages with a page size of 8,192. As soon as the primary file has reached the maximum number of pages, Firebird will start allocating pages to the secondary file test.fdb2. If that file is filled up to its maximum as well, test.fdb3 becomes the recipient of all new page allocations. As the last file, it has no page limit imposed on it by Firebird. New allocations will continue for as long as the file system allows it or until the storage device runs out of free space. If a LENGTH parameter were supplied for this last file, it would be ignored.

    1. SET SQL DIALECT 3;
    2. CREATE DATABASE 'baseserver:D:\test.fdb'
    3. USER 'wizard' PASSWORD 'player'
    4. PAGE_SIZE = 8192
    5. DEFAULT CHARACTER SET UTF8
    6. FILE 'D:\test.fdb2'
    7. STARTING AT PAGE 10001
    8. FILE 'D:\test.fdb3'
    9. STARTING AT PAGE 20001;
  5. Creating a database in Dialect 3 with UTF8 as its default character set. The primary file will contain up to 10,000 pages with a page size of 8,192. As far as file size and the use of secondary files are concerned, this database will behave exactly like the one in the previous example.

    1. SET SQL DIALECT 3;
    2. CREATE DATABASE 'baseserver:D:\test.fdb'
    3. USER 'wizard' PASSWORD 'player'
    4. PAGE_SIZE = 8192
    5. LENGTH 10000 PAGES
    6. DEFAULT CHARACTER SET UTF8
    7. FILE 'D:\test.fdb2'
    8. FILE 'D:\test.fdb3'
    9. STARTING AT PAGE 20001;

See alsoSection 5.1.2, ALTER DATABASE, Section 5.1.3, DROP DATABASE

5.1.2 ALTER DATABASE

Used forAltering the file organisation of a database, toggling its copy-safe state, managing encryption, and other database-wide configuration

Available inDSQL, ESQL — limited feature set

Syntax

  1. ALTER {DATABASE | SCHEMA} <alter_db_option> [<alter_db_option> ...]
  2. <alter_db_option> :==
  3. <add_sec_clause>
  4. | {ADD DIFFERENCE FILE 'diff_file' | DROP DIFFERENCE FILE}
  5. | {BEGIN | END} BACKUP
  6. | SET DEFAULT CHARACTER SET charset
  7. | {ENCRYPT WITH plugin_name [KEY key_name] | DECRYPT}
  8. | SET LINGER TO linger_duration
  9. | DROP LINGER
  10. | SET DEFAULT SQL SECURITY {INVOKER | DEFINER}
  11. | {ENABLE | DISABLE} PUBLICATION
  12. | INCLUDE <pub_table_filter> TO PUBLICATION
  13. | EXCLUDE <pub_table_filter> FROM PUBLICATION
  14. <add_sec_clause> ::= ADD <sec_file> [<sec_file> ...]
  15. <sec_file> ::=
  16. FILE 'filepath'
  17. [STARTING [AT [PAGE]] pagenum]
  18. [LENGTH [=] num [PAGE[S]]
  19. <pub_table_filter> ::=
  20. ALL
  21. | TABLE table_name [, table_name ...]

Note

Multiple files can be added in one ADD clause:

  1. ALTER DATABASE
  2. ADD FILE x LENGTH 8000
  3. FILE y LENGTH 8000
  4. FILE z

Multiple occurrences of add_sec_clause (ADD FILE clauses) are allowed; an ADD FILE clause that adds multiple files (as in the example above) can be mixed with others that add only one file. The statement was documented incorrectly in the old InterBase 6 Language Reference.

Table 5.1.2.1 ALTER DATABASE Statement Parameters

ParameterDescription

add_sec_clause

Adding a secondary database file

sec_file

File specification for secondary file

filepath

Full path and file name of the delta file or secondary database file

pagenum

Page number from which the secondary database file is to start

num

Maximum size of the secondary file in pages

diff_file

File path and name of the .delta file (difference file)

charset

New default character set of the database

linger_duration

Duration of linger delay in seconds; must be greater than or equal to 0 (zero)

plugin_name

The name of the encryption plugin

key_name

The name of the encryption key

pub_table_filter

Filter of tables to include to or exclude from publication

table_name

Name (identifier) of a table

The ALTER DATABASE statement can:

  • add secondary files to a database

  • switch a single-file database into and out of the copy-safe mode (DSQL only)

  • set or unset the path and name of the delta file for physical backups (DSQL only)

Note

SCHEMA is currently a synonym for DATABASE; this may change in a future version, so we recommend to always use DATABASE

5.1.2.1 Who Can Alter the Database

The ALTER DATABASE statement can be executed by:

5.1.2.2 Parameters for ALTER DATABASE

ADD (FILE)

Adds secondary files to the database. It is necessary to specify the full path to the file and the name of the secondary file. The description for the secondary file is similar to the one given for the CREATE DATABASE statement.

ADD DIFFERENCE FILE

Specifies the path and name of the delta file that stores any mutations to the database whenever it is switched to the copy-safe mode. This clause does not actually add any file. It just overrides the default name and path of the .delta file. To change the existing settings, you should delete the previously specified description of the .delta file using the DROP DIFFERENCE FILE clause before specifying the new description of the delta file. If the path and name of the .delta file are not overridden, the file will have the same path and name as the database, but with the .delta file extension.

Caution

If only a file name is specified, the .delta file will be created in the current directory of the server. On Windows, this will be the system directory — a very unwise location to store volatile user files and contrary to Windows file system rules.

DROP DIFFERENCE FILE

Deletes the description (path and name) of the .delta file specified previously in the ADD DIFFERENCE FILE clause. The file is not actually deleted. DROP DIFFERENCE FILE deletes the path and name of the .delta file from the database header. Next time the database is switched to the copy-safe mode, the default values will be used (i.e. the same path and name as those of the database, but with the .delta extension).

BEGIN BACKUP

Switches the database to the copy-safe mode. ALTER DATABASE with this clause freezes the main database file, making it possible to back it up safely using file system tools, even if users are connected and performing operations with data. Until the backup state of the database is reverted to NORMAL, all changes made to the database will be written to the .delta (difference) file.

Important

Despite its syntax, a statement with the BEGIN BACKUP clause does not start a backup process but just creates the conditions for doing a task that requires the database file to be read-only temporarily.

END BACKUP

Switches the database from the copy-safe mode to the normal mode. A statement with this clause merges the .delta file with the main database file and restores the normal operation of the database. Once the END BACKUP process starts, the conditions no longer exist for creating safe backups by means of file system tools.

Warning

Use of BEGIN BACKUP and END BACKUP and copying the database files with filesystem tools, is not safe with multi-file databases! Use this method only on single-file databases.

Making a safe backup with the gbak utility remains possible at all times, although it is not recommended running gbak while the database is in LOCKED or MERGE state.

SET DEFAULT CHARACTER SET

Changes the default character set of the database. This change does not affect existing data or columns. The new default character set will only be used in subsequent DDL commands.

ENCRYPT WITH

See Encrypting a Database in the Security chapter.

DECRYPT

See Decrypting a Database in the Security chapter.

SET LINGER TO

Sets the linger-delay. The linger-delay applies only to Firebird SuperServer, and is the number of seconds the server keeps a database file (and its caches) open after the last connection to that database was closed. This can help to improve performance at low cost, when the database is opened and closed frequently, by keeping resources warm for the next connection.

Note

This mode can be useful for web applications - without a connection pool - where the connection to the database usually lives for a very short time.

Warning

The SET LINGER TO and DROP LINGER clauses can be combined in a single statement, but the last clause wins. For example, ALTER DATABASE SET LINGER TO 5 DROP LINGER will set the linger-delay to 0 (no linger), while ALTER DATABASE DROP LINGER SET LINGER to 5 will set the linger-delay to 5 seconds.

DROP LINGER

Drops the linger-delay (sets it to zero). Using DROP LINGER is equivalent to using SET LINGER TO 0.

Note

Dropping LINGER is not an ideal solution for the occasional need to turn it off for some once-only condition where the server needs a forced shutdown. The gfix utility now has the -NoLinger switch, which will close the specified database immediately after the last attachment is gone, regardless of the LINGER setting in the database. The LINGER setting is retained and works normally the next time.

The same one-off override is also available through the Services API, using the tag isc_spb_prp_nolinger, e.g. (in one line):

  1. fbsvcmgr host:service_mgr user sysdba password xxx
  2. action_properties dbname employee prp_nolinger

Warning

The DROP LINGER and SET LINGER TO clauses can be combined in a single statement, but the last clause wins.

SET DEFAULT SQL SECURITY

Specifies the default SQL SECURITY option to apply at runtime for objects without the SQL Security property set. See also SQL Security in chapter Security.

ENABLE PUBLICATION

Enables publication of this database for replication. Replication begins (or continues) with the next transaction started after this transaction commits.

DISABLE PUBLICATION

Enables publication of this database for replication. Replication is disabled immediately after commit.

EXCLUDE …​ FROM PUBLICATION

Excludes tables from publication. If INCLUDE ALL TO PUBLICATION clause is used, then all tables created afterwards will also be replicated, unless overridden explicitly in the CREATE TABLE statement.

INCLUDE …​ TO PUBLICATION

Includes tables to publication. If INCLUDE ALL TO PUBLICATION clause is used, then all tables created afterwards will also be replicated, unless overridden explicitly in the CREATE TABLE statement.

Replication

  • Other than the syntax, configuring Firebird for replication is not covered in this language reference.

  • All replication management commands are DDL statements and thus effectively executed at the transaction commit time.

5.1.2.3 Examples of ALTER DATABASE Usage

  1. Adding a secondary file to the database. As soon as 30000 pages are filled in the previous primary or secondary file, the Firebird engine will start adding data to the secondary file test4.fdb.

    1. ALTER DATABASE
    2. ADD FILE 'D:\test4.fdb'
    3. STARTING AT PAGE 30001;
  2. Specifying the path and name of the delta file:

    1. ALTER DATABASE
    2. ADD DIFFERENCE FILE 'D:\test.diff';
  3. Deleting the description of the delta file:

    1. ALTER DATABASE
    2. DROP DIFFERENCE FILE;
  4. Switching the database to the copy-safe mode:

    1. ALTER DATABASE
    2. BEGIN BACKUP;
  5. Switching the database back from the copy-safe mode to the normal operation mode:

    1. ALTER DATABASE
    2. END BACKUP;
  6. Changing the default character set for a database to WIN1251

    1. ALTER DATABASE
    2. SET DEFAULT CHARACTER SET WIN1252;
  7. Setting a linger-delay of 30 seconds

    1. ALTER DATABASE
    2. SET LINGER TO 30;
  8. Encrypting the database with a plugin called DbCrypt

    1. ALTER DATABASE
    2. ENCRYPT WITH DbCrypt;
  9. Decrypting the database

    1. ALTER DATABASE
    2. DECRYPT;

See alsoSection 5.1.1, CREATE DATABASE, Section 5.1.3, DROP DATABASE

5.1.3 DROP DATABASE

Used forDeleting the database to which you are currently connected

Available inDSQL, ESQL

Syntax

  1. DROP DATABASE

The DROP DATABASE statement deletes the current database. Before deleting a database, you have to connect to it. The statement deletes the primary file, all secondary files and all shadow files.

Note

Contrary to CREATE DATABASE and ALTER DATABASE, DROP SCHEMA is not a valid alias for DROP DATABASE. This is intentional.

5.1.3.1 Who Can Drop a Database

The DROP DATABASE statement can be executed by:

5.1.3.2 Example of DROP DATABASE

Deleting the current database

  1. DROP DATABASE;

See alsoSection 5.1.1, CREATE DATABASE, Section 5.1.2, ALTER DATABASE