DATABASE

Table of Contents

CREATE DATABASE
ALTER DATABASE
DROP 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.

CREATE DATABASE

Table of Contents

Using a Database Alias
Creating a Database Remotely
Optional Parameters for CREATE DATABASE
Examples Using CREATE DATABASE

Used for: Creating a new database

Available in: DSQL, ESQL

Syntax:

       CREATE {DATABASE | SCHEMA} '<filespec>'
       [USER 'username' [PASSWORD 'password']]
       [PAGE_SIZE [=] size]
       [LENGTH [=] num [PAGE[S]]
       [SET NAMES 'charset'] 
       [DEFAULT CHARACTER SET default_charset
         [COLLATION collation]] -- not supported in ESQL
       [<sec_file> [<sec_file> ...]]
       [DIFFERENCE FILE 'diff_file']; -- not supported in ESQL

       <filespec> ::= [<server_spec>]{filepath | db_alias}

       <server_spec> ::= servername [/{port|service}]: | \\servername\

       <sec_file> ::= FILE 'filepath'
       [LENGTH [=] num [PAGE[S]] [STARTING [AT [PAGE]] pagenum]

Table 5.1. CREATE DATABASE Statement Parameters

Parameter	Description
filespec	File specification for primary database file
server_spec	Remote server specification in TCP/IP or Windows Networking style. Optionally includes a port number or service name
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 `aliases.conf` file
servername	Host name or IP address of the server where the database is to be created
username	User name of the owner of the new database. It may consist of up to 31 characters. Case-insensitive
password	Password of the user name as the database owner. The maximum length is 31 characters; however only the first 8 characters are considered. Case-sensitive
size	Page size for the database, in bytes. Possible values are 4096 (the default), 8192 and 16384
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 specificaton for a secondary file
pagenum	Starting page number for a secondary database file
diff_file	File path and name for DIFFERENCE files (.delta files)

The CREATE DATABASE statement creates a new database. You can use CREATE DATABASE or CREATE SCHEMA. They are synonymous.

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 a throwback. 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 when 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 the absolute path, when creating either the database or an alias for it.

Using a Database Alias

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

       alias = filepath

Creating a Database Remotely

If you create a database on a remote server, you should 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:

servername[/{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:

\\servername\{filepath | db_alias}

Optional Parameters for CREATE DATABASE

Optional USER and PASSWORD: Clauses for specifying the user name and the password, respectively, of an existing user in the security database security2.fdb. 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.

Optional 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 changed automatically to the default page size, 4,096. Other values not equal to either 4,096, 8,192 or 16,384 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 4,096.

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

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

Optional DEFAULT CHARACTER SET: Clause specifying the default character set for creating data structures of string data types. Character sets are applied to CHAR, VARCHAR and BLOB 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.

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

SET SQL 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. isql, before the CREATE DATABASE statement.

Examples Using CREATE DATABASE

Creating a database in Windows, located on disk D with a page size of 8,192. The owner of the database will be the user wizard. The database will be in Dialect 1 and it will use WIN1251 as its default character set.
```
SET SQL DIALECT 1;
CREATE DATABASE 'D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192 DEFAULT CHARACTER SET WIN1251;
            
```
Creating a database in the Linux operating system with a page size of 4,096. The owner of the database will be the user wizard. The database will be in Dialect 3 and it will use UTF8 as its default character set, with UNICODE_CI_AI as the default collation.
```
CREATE DATABASE '/home/firebird/test.fdb'
USER 'wizard' PASSWORD 'player'
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;
            
```
Creating a database on the remote server “baseserver” with the path specified in the alias “test” that has been defined previously in the file aliases.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.
```
CREATE DATABASE 'baseserver:test'
USER 'wizard' PASSWORD 'player'
DEFAULT CHARACTER SET UTF8;
            
```
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.
```
SET SQL DIALECT 3;
CREATE DATABASE 'baseserver:D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192
DEFAULT CHARACTER SET UTF8
FILE 'D:\test.fdb2'
STARTING AT PAGE 10001
FILE 'D:\test.fdb3'
STARTING AT PAGE 20001;
            
```
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.
```
SET SQL DIALECT 3;
CREATE DATABASE 'baseserver:D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 8192
LENGTH 10000 PAGES
DEFAULT CHARACTER SET UTF8
FILE 'D:\test.fdb2'
FILE 'D:\test.fdb3'
STARTING AT PAGE 20001;
            
```

ALTER DATABASE

Table of Contents

Parameters for ALTER DATABASE

Used for: Altering the file organisation of a database or toggling its “copy-safe” state

Available in: DSQL—both functions. ESQL—file reorganisation only

Syntax:

ALTER {DATABASE | SCHEMA}
[<add_sec_clause> [<add_sec_clause> ...]]
[ADD DIFFERENCE FILE 'diff_file' | DROP DIFFERENCE FILE]
[{BEGIN | END} BACKUP];

<add_sec_clause> ::= ADD <sec_file> [<sec_file> ...]

<sec_file> ::= FILE 'filepath'

ADD FILE <sec_file>

          [STARTING [AT [PAGE]] pagenum]
          [LENGTH [=] num [PAGE[S]]

	Note
Multiple files can be added in one ADD clause: ALTER DATABASE ADD FILE x LENGTH 8000 FILE y LENGTH 8000 FILE z Multiple ADD FILE clauses are allowed; and 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 incorrectly documented in the old InterBase 6 Language Reference.

Note

Multiple files can be added in one ADD clause:

  ALTER DATABASE
    ADD FILE x LENGTH 8000
        FILE y LENGTH 8000
        FILE z

Multiple ADD FILE clauses are allowed; and 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 incorrectly documented in the old InterBase 6 Language Reference.

Table 5.2. ALTER DATABASE Statement Parameters

Parameter	Description
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 the 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)

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)

Only administrators have the authority to use ALTER DATABASE.

Parameters for ALTER DATABASE

The ADD FILE clause: adds a secondary file 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.

The ADD DIFFERENCE FILE clause: 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: This is the clause that 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: This is the clause that 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: is the clause used to switch 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 to run gbak while the database is in LOCKED or MERGE state.

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 to run gbak while the database is in LOCKED or MERGE state.

Examples of ALTER DATABASE Usage

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.
```
ALTER DATABASE
ADD FILE 'D:\test4.fdb'
STARTING AT PAGE 30001;
          
```

Specifying the path and name of the delta file:

ALTER DATABASE
ADD DIFFERENCE FILE 'D:\test.diff';

Deleting the description of the delta file:

ALTER DATABASE
DROP DIFFERENCE FILE;

Switching the database to the “copy-safe” mode:
```
ALTER DATABASE
BEGIN BACKUP;
          
```
Switching the database back from the “copy-safe” mode to the normal operation mode:
```
ALTER DATABASE
END BACKUP;
          
```

DROP DATABASE

Used for: Deleting the database to which you are currently connected

Available in: DSQL, ESQL

Syntax:

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.

Only administrators have the authority to use DROP DATABASE.

Example: Deleting the database the client is connected to.

DROP DATABASE;