This section is divided into the following topics under the heading PSQL Database Concepts:

The following topics cover PSQL database concepts:

A named database (also called a DBname) is a database with a logical name that allows users to identify it without knowing its location. PSQL requires that all databases be named. When you name a database, you associate that name with a particular dictionary directory path and one or more data file paths.

A named database is connected to through various access methods. For ODBC access, for example, you must set up a data source name (DSN) to refer to the named database. Multiple DSNs may point to the same named database. See ODBC Database Access in ODBC Guide. For other access methods, application developers can connect to a named database using the API for that access method. Refer to the developer reference guides in the PSQL documentation.

The easiest way to create a named database is by using PSQL Control Center. See To create a new database in PSQL User's Guide. Application developers can also create a named database through different access methods APIs. For example, see CREATE DATABASE for SQL, PvCreateDatabase() for DTI, and Data Access Application Blocks for ADO.NET.

The Relational Engine supports two versions of metadata, referred to as version 1 (V1) and version 2 (V2). V2 metadata allows for identifier names up to 128 bytes long for many identifiers, permissions on views and stored procedures, and data dictionary files (DDFs) specific for V2 metadata.

See SQL Grammar Support in ODBC Guide.

An identifier is the name of a database or of a column, table, procedure, or other named object within the database. Identifiers are designated as either regular or delimited.

A regular identifier is an identifier that is not surrounded by double quotes. Regular identifier must begin with a letter, either upper or lower case. The remainder of the identifier can consist of any combination of upper or lower case letters, digits, and valid characters.

You cannot use a reserved word as a regular identifier.

Regular identifiers are case-insensitive.

A delimited identifier is an identifier surrounded by double quotes. Delimited identifier can consist of any string of valid characters enclosed in double quotes.

While it is not recommended, reserved words can be used as delimited identifiers. For example, INSERT is not permitted as a regular identifier, but "INSERT" is permitted as a delimited identifier. If an identifier is also a keyword, it must be delimited by double quotation marks. For example, SELECT "password" FROM my_pword_tbl. Password is a keyword in the SET PASSWORD statement, so it must be delimited.

In addition to the general restrictions listed above, the following table lists restrictions specific to each type of identifier.

Identifiers generally must be unique within a certain scope. That is, instances of the same type of object using the same name cannot be used within the same arena. Table 2 shows the arena, or the scope, within which a given object name must be unique.

To support existing applications that do not specify a database name when creating or opening Btrieve files, PSQL maintains the concept of a default database for each transactional database engine. The default database is a predefined database named DefaultDB. To make use of the new security models without having to modify your application code, you can associate your Btrieve data directories with the default database, then set up users and privileges in the default database to control access to the data files in those directories.

The database engine also understands the concept of the current database for each client connection. If no database name is specified in a Btrieve Login (78), Create (14), or Open (0) operation, the transactional engine assumes the operation is associated with the current database. For each client, the current database is the database to which the most recent Login (78) operation occurred (explicit login). If the client computer has requested no explicit login operations, the current database is the database to which the most recent Create (14) or Open (0) operation occurred (implicit login). If no explicit or implicit logins have occurred, then the current database is the default database, described in the preceding paragraph. Note that the current database may change at any time when the given client performs an implicit or explicit login, or closes the last file handle, making DefaultDB the current database. The current database for each client is independent of other clients’ activities.

The simplest way to configure the new security model for existing applications is to associate all Btrieve data directories with the default database, and set up rights for the group PUBLIC within this database. The group PUBLIC is automatically created along with the Master user when you enable security for a database. See MicroKernel Engine Security Quick Start.

All PSQL databases use a common data format. This commonality allows different access methods, such as transactional and relational, to access the same data. The system through which all access methods operate is called the MicroKernel Engine.

Each PSQL database table is a separate file with a default file extension of .mkd. Developers, however, can specify any file name extension desired. A MicroKernel file may contain both data and indexes, and is organized into various types of pages. A MicroKernel file contains data in the common data format.

Each PSQL database also contains a set of data dictionary files, with a file extension of .ddf. The DDF files contain the schema of the database. The DDFs for V1 metadata and V2 metadata use different file names. See System Tables in SQL Engine Reference.

(The MicroKernel Engine is unconcerned with the schema of the data apart from the key fields. However, the provision for referential integrity or access via SQL requires knowledge of the schema.)

The names and locations of PSQL databases are contained in a binary file named dbnames.cfg. For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started with PSQL.

All of the files associated with a PSQL database can be viewed from the operating system. Table 3 summarizes the associated files.

The size limit depends on the file version, page size, and number of records per page, as the following tables summarize.

The maximum size of a data file is 64 TB. You must use a file format of 13.0 or newer to have a single file size larger than 256 GB.

Note that the following table assumes no record compression on the file. If you use record compression, take into account that additional records are stored per page. See Choosing a Page Size and Estimating File Size, both in PSQL Programmer's Guide.

The maximum size of a data file is 256 GB. You must use a file format of 9.5 or newer to have a single file size larger than 128 GB.

Note that the following table assumes no record compression on the file. If you use record compression, take into account that additional records are stored per page. See Choosing a Page Size and Estimating File Size, both in PSQL Programmer's Guide.

The maximum size of a data file is 128 GB. You must use a file format of 9.0 or newer to have a single file size larger than 64 GB.

Note that the following table assumes no record compression on the file. If you use record compression, take into account that additional records are stored per page. See Choosing a Page Size and Estimating File Size, both in PSQL Programmer's Guide.

By default, a data file is automatically broken into 2 GB operating system file segments as its size passes that boundary. The configuration property Limit Segment Size to 2 GB allows you to specify whether you want files divided into 2 GB segments or unified in a single, nonsegmented file. The advantage of using a larger nonsegmented file is more efficient disk I/O. Therefore, you can expect increased performance. Note that 13.0 format files do not support segmentation.

The configuration option is part of the Performance Tuning properties for a database engine. See To set the properties in PCC for an engine, and Limit Segment Size to 2 GB.

The property is set to on by default, causing files to segment at 2 GB boundaries as with previous releases. If you set the property to off, files can increase past the 2 GB boundary. See also Automatic Upgrade of File Version for additional information relating to the configuration property.

Any non-segmented files are subject to the limit on file size specified by your operating system. For example, creating a large file on a FAT32 file system with Limit Segment Size to 2 GB turned off creates multiple 4 GB file extensions. If a previously created file is already segmented, that segmentation remains on the file.

If the configuration property Create File Version is set to 9.0 or higher, version 8.x files are automatically converted to version 9.0 files when they reach the file limits for version 8.x, which is 64 GB. The following table summarizes this behavior.

For example, a version 8.x file that is 5 GB in size has already passed the 2 GB segmentation boundary. Because the file is already segmented, the segmentation remains on the file. Such a file would continue to segment and grow in size until it reaches 64 GB, at which size the automatic upgrade would occur. This is true whether the configuration property is set to yes or no because the file is already segmented. As the file grows beyond 64 GB, it will continue to segment until it reaches the maximum size allowed for a version 9.0 file, 128 GB.

A version 8.x file that is 1.5 GB in size would continue to grow until it reaches 2 GB in size. At that point, the automatic upgrade occurs if the configuration property is set to no. The file can continue to grow as a non-segmented file up to the size limit for version 9.0 files, 128 GB. If the configuration setting is set to yes, the 2 GB file would continue to segment and grow until it reaches 64 GB in size. At that size, the maximum for a version 8.x file, the automatic upgrade to version 9.0 occurs. As the file grows beyond 64 GB, it will continue to segment until it reaches the maximum size allowed for a version 9.0 file, 128 GB.

The Create File Version option is part of the Compatibility properties for a database engine. See To set the properties in PCC for an engine.

The two primary methods in which data is accessed from PSQL databases are transactional and relational.

With transactional, an application program navigates up and down along either physical or logical pathways through data records. Using a transactional API, an application program provides direct control and allows a developer to optimize data access based on knowledge of the underlying structure of the data. Btrieve is an example of a transactional database engine.

Relational is an access method in which data is represented as collections of tables, rows, and columns. The relational model insulates the developer from the underlying data structure and presents the data in a simple table format. ODBC is an example of a relational access method.

A single application program may include both types of access. For example, an application may use transactional access for adding and changing data, and relational access for querying the data and report writing.

You need to know the access method(s) used by the application programs that rely on your installation of PSQL. The access methods may have different configurations. You may need to customize the configuration to optimize a particular access method.

Also, troubleshooting is easier when you are aware of the access method(s) used by a given application program. For example, if an application program uses relational access through ODBC, you may need to troubleshoot a problem at the ODBC level rather than at the database management system.

See Configuration Reference for the tasks and references pertaining to customizing configurations.

The MicroKernel Engine supports two types of processing modes, local and client-server. An application accessing the database in local mode accesses a local copy of the engine. The local engine calls upon the operating system of the workstation which performs the I/O on a local or networked hard disk.

Client-server mode uses a server MicroKernel Engine executing on a shared file server. When an application program accesses the database engine in client-server mode, the requester connects to the remote engine. This requester passes transactional-level requests and data records between the application program and the server engine using the network protocol supported by the operating system. File I/O functions are completely handled by the server engine in client-server mode and the workstation has no operating system handles allocated to shared data files. Database manipulation is performed by the server-based engine on behalf of each workstation.

Note that the processing mode is determined by the configuration of the workstation and not the application program itself. This means that an application is capable of accessing both local and client-server database engines. The application program does not have to be recompiled to switch the application to client-server mode from local mode.

Both Workgroup and Server engine can operate in either mode. When an application on the same computer as the database engine accesses the engine, it is operating in local mode. When an application on a different machine access the engine, it is operating in client-server mode.

The client-server configurations may be customized for the Workgroup and Server versions of PSQL. Configuration settings exists in the PSQL Control Center (PCC) to facilitate the configuration of client-server configurations as well as stand-alone configurations.

See Configuration Reference for the tasks and references pertaining to configuring the client-server communications and database engine.

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 PSQL database engine (server) and a PSQL client application. A compatible encoding allows the server and client to interpret data correctly.

The encoding support is divided into database code page and client encoding. The two types of encoding are separate but interrelated. For ease of discussion, database code page and client encoding are discussed together. See Setting Up Network Communications for Clients in Getting Started with PSQL.

Refer to DSN Setup and Connection Strings in ODBC Guide. This topic also discusses ODBC connection strings.

Typically, an application provides its own file location information. As an alternative, you may provide file location mapping based on information in a text file, idshosts.

The idshosts file was one aspect of PSQL (IDS). IDS has been removed from the core product but the idshosts file is still configurable.

If your applications do not use the mapping feature through idshosts, set the configuration setting Use IDS to off. If your applications already use idshosts, or if you prefer to use this alternative method to map file locations, set Use IDS to on. See Use IDS.

Note that performance is slower when the idshosts file is used because of the time required to access the file and read its contents.

An idshosts file may be used only with a Windows, Linux, or macOS client requester. The client may communicate with a PSQL server on Windows, Linux, or macOS.

Refer to the comments in the idshosts file itself for how to format entries in the file. The comments also provide example mappings. By default, for Windows platforms, the idshosts file is installed to the \bin directory under the database client installation directory. For Linux, macOS, and Raspbian, idshosts is installed to the \etc directory under the database client installation directory (for example, /user/local/psql/etc).