The Firebird 5.0 Language Reference | CREATE DATABASE

Who Can Create a Database

The CREATE DATABASE statement can be executed by:

Administrators
Users with the CREATE DATABASE privilege

Examples Using `CREATE DATABASE`

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 1, and will use WIN1251 as its default character set.
```
SET SQL DIALECT 1;
CREATE DATABASE 'D:\test.fdb'
USER 'wizard' PASSWORD 'player'
PAGE_SIZE = 4096 DEFAULT CHARACTER SET WIN1251;
```
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.
```
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 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.
```
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`

Alters the file organisation of a database, toggles its “copy-safe” state, manages encryption, and other database-wide configuration

Available in

DSQL, ESQL — limited feature set

Syntax

ALTER {DATABASE | SCHEMA} <alter_db_option> [<alter_db_option> ...]

<alter_db_option> :==
    <add_sec_clause>
  | {ADD DIFFERENCE FILE 'diff_file' | DROP DIFFERENCE FILE}
  | {BEGIN | END} BACKUP
  | SET DEFAULT CHARACTER SET charset
  | {ENCRYPT WITH plugin_name [KEY key_name] | DECRYPT}
  | SET LINGER TO linger_duration
  | DROP LINGER
  | SET DEFAULT SQL SECURITY {INVOKER | DEFINER}
  | {ENABLE | DISABLE} PUBLICATION
  | INCLUDE <pub_table_filter> TO PUBLICATION
  | EXCLUDE <pub_table_filter> FROM PUBLICATION

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

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

<pub_table_filter> ::=
    ALL
  | TABLE table_name [, table_name ...]

Note

Multiple files can be added in one ADD clause:

ALTER DATABASE
  ADD FILE x LENGTH 8000
    FILE y LENGTH 8000
    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.

Table 1. `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 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`

Who Can Alter the Database

The ALTER DATABASE statement can be executed by:

Administrators
Users with the ALTER DATABASE privilege

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 difference file (or, delta file) that stores any mutations to the database whenever it is switched to the “copy-safe” mode.This clause does not add a file, but it configures name and path of the delta file when the database is in “copy-safe” mode.To change the existing setting, 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 configured, the file will have the same path and name as the database, but with the .delta file extension.

Caution

If only a filename 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 difference file specified previously in the ADD DIFFERENCE FILE clause.This does not delete a file, but DROP DIFFERENCE FILE clears (resets) 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 name, the ALTER DATABASE BEGIN BACKUP statement does not start a backup process, but only freezes the database, to create 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 difference 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.To modify the default collation, use ALTER CHARACTER SET on the default character set of the database.

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 connections to the database usually “live” 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 once-only operations 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):

fbsvcmgr host:service_mgr user sysdba password xxx
       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 the INCLUDE ALL TO PUBLICATION clause is used, all tables created afterward will also be replicated, unless overridden explicitly in the CREATE TABLE statement.

INCLUDE … TO PUBLICATION

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

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

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;
```
Changing the default character set for a database to WIN1251
```
ALTER DATABASE
  SET DEFAULT CHARACTER SET WIN1252;
```
Setting a linger-delay of 30 seconds
```
ALTER DATABASE
  SET LINGER TO 30;
```
Encrypting the database with a plugin called DbCrypt
```
ALTER DATABASE
  ENCRYPT WITH DbCrypt;
```
Decrypting the database
```
ALTER DATABASE
  DECRYPT;
```

`DROP DATABASE`

Drops (deletes) the database of the current connection

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.

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

Who Can Drop a Database

The DROP DATABASE statement can be executed by:

Administrators
Users with the DROP DATABASE privilege

Example of `DROP DATABASE`

Deleting the current database

DROP DATABASE;

Who Can Create A Database

Who Can Create a Database

Examples Using CREATE DATABASE

ALTER DATABASE

Who Can Alter the Database

Parameters for ALTER DATABASE

Examples of ALTER DATABASE Usage

DROP DATABASE

Who Can Drop a Database

Example of DROP DATABASE

Examples Using `CREATE DATABASE`

`ALTER DATABASE`

Parameters for `ALTER DATABASE`

Examples of `ALTER DATABASE` Usage

`DROP DATABASE`

Example of `DROP DATABASE`