CREATE DATABASE

The CREATE DATABASE statement creates a new database. Any user logged in to a database can issue the statement. (The user must also have permissions at the operating system to create files in the specified location.)

Syntax

CREATE DATABASE [ IF NOT EXISTS ] database-name DICTIONARY_PATH 'dict-path-name' [ DATA_PATH 'data-path-name' ] [ ; 'data-path-name' ]... ] [ NO_REFERENTIAL_INTEGRITY ] [ BOUND ] [ REUSE_DDF ] [ DBSEC_AUTHENTICATION ] [ DBSEC_AUTHORIZATION ] [ V1_METADATA | V2_METADATA ] [ ENCODING < 'codepage-name' | 'CPcodepage-number' | DEFAULT > ]

database-name ::= a user-defined name for the database

dict-path-name ::= a user-defined name for the location of the data dictionary files (DDFs)

data-path-name ::= a user-defined name for the location of the data files

codepage-name ::= the name of a valid code page

CPcodepage-number ::= a number of a valid code page preceded by “CP”

Remarks

CREATE DATABASE creates only a database, not an associated data source name (DSN). You need to create a DSN separately if you want one. See Setting Up ODBC Database Access in Pervasive PSQL User's Guide.

The CREATE DATABASE statement cannot be used to create the first database on a server. The reason is that a user must log on to a database before issuing the CREATE DATABASE statement. Therefore, at least one database must already exist.

The CREATE DATABASE statement cannot be used in a stored procedure or in a user-defined function.

Database Name and IF NOT EXISTS Clause

Database-name specifies a name for the new database. The database names must be unique within a server and conform to the rules for identifiers. See Identifier Restrictions by Identifier Type in Advanced Operations Guide.

An error occurs if the database exists and you omit the IF NOT EXISTS clause (status code 2303). No error returns if you include the IF NOT EXISTS clause.

Dictionary Path

Dict-path-name specifies where the dictionary files (DDFs) reside on physical storage. The data files are also placed in this same location when you use the CREATE TABLE statement or create tables using Pervasive PSQL Control Center (PCC). See Dictionary Location in Pervasive PSQL User's Guide.

Data Path

Data-path-name specifies a possible location of the data files for the database (see Note below). You can specify multiple path names by delimiting them with a semicolon.

Data-path-name can be any path that is valid from the database engine's point of view, not from the calling application's perspective. The location specified must already exist. The CREATE DATABASE statement does not create directories.

Omit data-path-name if you want to use the same location for the data files as for the dictionary files. You may also specify the same location by passing an empty string for data-path-name. For example, specifying DATA_PATH '' indicates an empty string for the data path.

Note: If you create tables using the CREATE TABLE statement or with PCC, the data files are placed in the first dict-path-name specified. If no dict-path-names are specified, data files are created in the dict-path-name location.

Data-path-name is useful if you are creating tables through the Distributed Tuning Interface (DTI). The DTI function PvAddTable allows you to specify where you want the data files located. See PvAddTable() in the Developer Reference book Distributed Tuning Interface Guide.

Referential Integrity

By default, the database engine enforces referential integrity. If you specify the NO_REFERENTIAL_INTEGRITY clause, then any triggers and referential integrity defined in the database are not enforced.

See Setting Up Referential Integrity and Interactions Between Btrieve and Relational Constraints.

BOUND

If BOUND is specified, the DDFs are bound to the database. A bound database associates a database name with a single set of DDFs, which refer to only one set of data files. The DDFs are bound whether they already existed or are created through the execution of the CREATE DATABASE statement.

If DDFs are bound, you cannot use those DDFs for more than one database, nor can you refer to the data files by more than one set of DDFs.

If BOUND is not specified then the DDFs are not bound to a database.

See Bound Database versus Integrity Enforced in Advanced Operations Guide.

Dictionary Files

The REUSE_DDF keyword associates any existing DDFs with the database. The existing DDFs must in the dict-path-name location.

If REUSE_DDF is omitted, new DDFs are created unless DDFs already exists in the dict-path-name location. If DDFs exists in the dict-path-name location, they are associated with the database instead of new DDFs being created.

Security

The database engine supports three security models for the transactional interface:

•

Classic. A user who successfully logs into the computer has access to the database contents at whatever level of file system rights the user has been assigned to the data file. File system rights are assigned through the operating system.

•

Database. Database user accounts are unrelated to operating system user accounts. A user’s access rights to the data are governed by user permissions set up in the database.

•

Mixed. This policy has aspects of both of the other policies. Users log in using their operating system user names and passwords, but a user’s access rights to the data are governed by user permissions set up in the database.

See the chapter Pervasive PSQL Security in Advanced Operations Guide for a complete discussion of security.

The DBSEC_AUTHENTICATION and DBSEC_AUTHORIZATION keywords set the security policy for the database:


Keyword Included or Omitted in Statement		Security Model
DBSEC_AUTHENTICATION	DBSEC_AUTHORIZATION	Classic	Database	Mixed
omitted	omitted
included	included
omitted	included

Metadata Version

The relational interface supports two versions of metadata, referred to as version 1 (V1) and version 2 (V2). Metadata version applies to all data dictionary files (DDFs) within that database. V1 metadata is the default.

Among other features, V2 metadata allows for many identifier names to be up to 128 bytes long and for permissions on views and stored procedures. See Versions of Metadata for a complete discussion.

You may include or omit the V1_METADATA keyword to specify V1 metadata. You must include the V2_METADATA keyword to specify V2 metadata.

See also Versions of Metadata.

Encoding

An encoding is a standard for representing character sets. Character data must be put in a standard format, that is, encoded, so that a computer can process it digitally. An encoding must be established between the Pervasive PSQL database engine (server) and a Pervasive PSQL client application. A compatible encoding allows the server and client to interpret data correctly.

Encoding support is divided into database code page and client encoding. The two types of encoding are separate but interrelated (see Encoding Interaction in Getting Started With Pervasive PSQL).

Database code page and client encoding apply only to the relational interface. The transactional interface is not affected.

You specify a code page by using a name or by using “CP” followed by a code page number. Both must be quoted with single quotes. For example, a valid name is 'UTF-8' and a valid number is 'CP1251'.

Both the Windows and Linux operating systems have a default encoding referred to as the OS encoding. The default OS encoding differs between Windows and Linux. The keyword DEFAULT allows you to specify the OS encoding on the server.

If the ENCODING key word is omitted, the database defaults to the server OS encoding.

An invalid code page number or name returns the error "Invalid code page."

Note that, for SQL statement that involve the use of more than one database, you need to ensure that the database code page is the same for all of the databases. Otherwise, string data can be returned incorrectly.

Note: The database engine does not validate the encoding of the data and metadata that an application inserts into a database. The engine assumes that all data was entered using the encoding of the server or the client, as explained in Encoding Interaction in Getting Started With Pervasive PSQL.

For SQL statements that involve the use of more than one database (such as a multi-database join), ensure that the database code page is the same for all of the databases. Otherwise, string data can be returned incorrectly.

Valid Code Page Names and Numbers

You can view the list of supported code page names and numbers with PCC. Start PCC and access the New Database dialog (see To create a new database in Pervasive PSQL User's Guide). For the “Database Code Page” option, click Change code page. On the dialog that displays, click Database code page to see a list of available code pages.

On Linux, you can also use the dbmaint utility to display a list of supported code page names and numbers. See the “Examples” for dbmaint in Pervasive PSQL User's Guide.

Examples

The following example creates a database named “inventorydb” and specifies its location for DDFs on drive D: in the folder “mydbfiles\ddf_location.” New DDFs are created because none exist in “d:\mydbfiles\ddf_location.” The data files are placed in the same location as the DDFs. The database uses V1 metadata.

CREATE DATABASE inventorydb DICTIONARY_PATH 'D:\mydbfiles\ddf_location'

============

The following example creates a database named “HRUSBenefits” if it does not already exist, and specifies its location for DDFs on drive C: in the folder “HRDatabases\US.” Possible locations for the data files include the C: drive in a directory called “HRDatabases\US\DataFiles” and the E: drive in a directory called “Backups\HRUSData” (see Note under Data Path). Existing DDFs are used if they exist in the DICTIONARY_PATH. The database uses V1 metadata.

CREATE DATABASE IF NOT EXISTS HRUSBenefits DICTIONARY_PATH 'C:\HRDatabases\US' DATA_PATH 'C:\HRDatabases\US\DataFiles ; E:\Backups\HRUSData' REUSE_DDF

============

The following example creates a database named “EastEurope,” specifies its location for DDFs on drive C: in the folder “Europe\DbaseFiles,” creates new DDFs and binds them to the database, sets the security policy to mixed, and uses V2 metadata.

CREATE DATABASE EastEurope DICTIONARY_PATH 'C:\Europe\DbaseFiles' BOUND DBSEC_AUTHORIZATION V2_METADATA