Project

General

Profile

Wiki » Documentation » Installation Configuration and Administration Guide » Directory Configuration Reference »

Database Configuration

Older Versions

This section describes the runtime database configuration for FWD v4 and later, which changed substantially from older versions of FWD. For information on configuring the database for older versions, please refer to Database Configuration v3.

Search Algorithm

The database configuration can be located in different locations to differentiate the option as global for all servers or specific to a server. The search algorithm used for these options is the Server Search Algorithm.

The database nodes must be located in the global server location /server/default/database/<database_node>/ or in the per-server location:

/server/<server_id>/database/<database_node>/

where <server_id> is the name of the server and <database_node> is the JDBC database name.

Port Services Mappings

The port-services section maps a list of names to their TCP port numbers.

<node class="container" name="port_services">
  <node class="integer" name="my_first_service">
     <node-attribute name="value" value="3333"/>
  </node>
  <node class="integer" name="my_second_service">
     <node-attribute name="value" value="3343"/>
  </node>
</node>

This is used only by database connection setup.

Where the 4GL uses the services file to process services names to port mappings, FWD uses this data to create a logical connection to a database using the specified parameters. The 4GL statement which defines this is the CONNECT statement, and it will always result in a call to the ConnectionManager.connect(...)

The CONNECT statement uses a series of parameters among which is :

-S { port-number | service-name }

This specifies the port number or service name used to connect to a FWD server running on the specified host. If the host is specified, then it is required to specify either the port or service name. In the case the argument is not a number the directory will be searched to find the port_services node that has the specified name.

Persistence

The persistence node of the directory controls whether persistence (i.e., database) support is active, and whether foreign keys are in use. By default, persistence support is active and foreign key support is disabled. Since it is unusual to change either of these defaults, this node is not present in the directory at all by default.

The active parameter controls whether runtime persistence support is turned on. It would be an uncommon ABL application which does not use a database at all, but persistence support can be disabled. This may be useful, for instance, when running simple test cases which do not use a database. The following setting under the standard/runtime/ directory path disables it:

<node class="container" name="persistence">
  <node class="boolean" name="active">
     <node-attribute name="value" value="FALSE"/>
  </node>
</node>

Note that when persistence is disabled, the FWD server will log a warning at startup.

The foreign-keys parameter controls whether the conversion will calculate and enforce referential integrity using “natural” joins. A natural join is one where two tables are linked by an exactly matching index specification which is defined as unique in at least one of the tables. The default value of foreign-keys parameter is FALSE.

This parameter is deprecated and should not be used. Traditionally, Progress code has not had any concept of referential integrity. This means that normal relational database management system (RDBMS) rules may be and probably are broken in most applications. As one example, in Progress the order of deletes between two tables is not important (it can be in any order), because Progress traditionally has not known about nor enforced any relationship between tables. When adding referential integrity where there was none in the past, such problems can cause a serious deadlocking bug. There are known issues with the current implementation of foreign keys such that many converted Progress applications would not work properly if this is enabled. Some of these problems can likely be resolved in the FWD runtime, but they do still exist at the time of this writing.

Example:

<node class="container" name="persistence">
   <node class="boolean" name="foreign-keys">
      <node-attribute name="value" value="FALSE"/>
   </node>
</node>

Cache Sizes

Cache sizes can be defined in directory.xml and will be configured only once at the server bootstrap through initialization methods. Currently most of the caches that have their size configured belong to the com.goldencode.p2j.persist package and are defined under the persistence/cache-size container:

<node class="container" name="persistence">
    <node class="container" name="cache-size">

    </node>
</node>

There are two types of caches that can be configured:
  1. LRUCache: The name of the cache will be the class name used when configuring the cache size (if a discriminator is provided, it will also be included in the name). Alongside the name, the cache can be provided with a function which confirms whether an expiration candidate element may actually be expired and a policy to follow when capacity cannot be ensured, due to the function vetoing expiration candidates.
  2. HashMap: Only the capacity (cache size) is used to create it.
Configurations containers and attributes:
  • cache-size: Main container which stores all the cache size configurations. Cache size are read by iterating over all the containers inside cache-size.
  • size container: Container that stores a single cache-size configuration. It does not have a specific name set.
  • class-name: The name of the class used to identify which cache the size belongs to.
  • size: The size that will be used by the cache.
  • discriminator: An identifier used to get the correct cache size when multiple cache sizes are defined for the same class. A discriminator can be used even if there is a single cache that needs to be configured in a class, but it has to be used when initializing to create the cache with the configured value.

To configure a single cache size for a class, use the following structure in the persistence/cache-size container:

<node class="container" name="01"> <!-- container name is unrelated and it doesn't affect the configuration -->
    <node class="string" name="class-name">
      <node-attribute name="value" value="com.goldencode.p2j.persist.BufferManager"/>
    </node>
    <node class="integer" name="size">
      <node-attribute name="value" value="10000"/>
    </node>
</node>

To configure multiple cache sizes for a class, the same structure as above is used, but an additional discriminator node is added.

<node class="container" name="08">
    <node class="string" name="class-name">
        <node-attribute name="value" value="com.goldencode.p2j.persist.FastFindCache"/>
    </node>
    <node class="integer" name="size">
        <node-attribute name="value" value="16"/>
    </node>
    <node class="string" name="discriminator">
        <node-attribute name="value" value="L2"/>
    </node>
</node>
Note that a container needs to be defined for each cache size and the discriminator will be used to make the difference between multiple cache sizes defined for the same class.

The list of caches that can have their size configured:
  • com.goldencode.p2j.persist.BufferManager: convertedNames default size is 10000;
  • com.goldencode.p2j.persist.DynamicTablesHelper: cache default size is 8192;
  • com.goldencode.p2j.persist.FQLHelperCache: cache default size is 8192;
  • com.goldencode.p2j.persist.FQLPreprocessor: cache default size is 2048 (without a discriminator) and astCache default size is 8192 (the discriminator is "ast");
  • com.goldencode.p2j.persist.Persistence: staticQueryCache default size is 1024;
  • com.goldencode.p2j.persist.orm.Persister: updateCache default size is 4096;
  • com.goldencode.p2j.persist.orm.Session: cache default size is 1024;
  • com.goldencode.p2j.persist.FastFindCache: l2Cache default size is 10 (the discriminator is "L2") and l3Cache default size is 100 (the discriminator is "L3");
  • com.goldencode.p2j.persist.SortCriterion: cache default size is 65536;
  • com.goldencode.p2j.persist.DynamicQueryHelper: lvl1Cache default size is 65536 (the discriminator is "lvl1") and lvl2Cache default size is 16384 (the discriminator is "lvl2");
  • com.goldencode.p2j.persist.DynamicValidationHelper: cache default size is 65536;
  • com.goldencode.p2j.persist.orm.TempTableDataSourceProvider: psCache default size is 8192;
  • com.goldencode.p2j.util.SourceNameMapper: sourceCache default size is 8 (the discriminator is "source"), searchPathCache default size is 64 (the discriminator is "search"), SourceNameMapperCache.convertedNames default size is 16384 (the discriminator is "names").

Note that even if the cache belongs to an inner class, the configuration will always use the main class when configuring the cache size.

The following caches will be configured through CacheManager after #8281 is finished and not be available anymore:
  • com.goldencode.p2j.persist.DynamicQueryHelper: lvl1Cache default size is 65536 and lvl2Cache default size is 16384;
    lvl1Cache and lvl2Cache can be configured in the /server/default/standard/runtime/default/ container. lvl1Cache needs to be configured using the dynamic-query-cache-lvl1-size node and lvl2Cache with the dynamic-query-cache-lvl2-size node:
      <node class="container" name="server">
    <node class="container" name="default">
      <node class="container" name="standard">
        <node class="container" name="runtime">
          <node class="container" name="default">
            ...
            <node class="integer" name="dynamic-query-cache-lvl1-size">
              <node-attribute name="value" value="65536"/>
            </node>
            <node class="integer" name="dynamic-query-cache-lvl2-size">
              <node-attribute name="value" value="16384"/>
            </node>
          </node>
        </node>
        ...
      </node>
    </node>
  </node>
  • com.goldencode.p2j.persist.DynamicValidationHelper: cache default size is 65536;
    cache can be configured in the /server/default/standard/runtime/default/ container. cache needs to be configured using the dynamic-valexp-cache-size node:
      <node class="container" name="server">
    <node class="container" name="default">
      <node class="container" name="standard">
        <node class="container" name="runtime">
          <node class="container" name="default">
            ...
            <node class="integer" name="dynamic-valexp-cache-size">
              <node-attribute name="value" value="65536"/>
            </node>
          </node>
        </node>
        ...
      </node>
    </node>
  </node>

All DynamicQueryHelper and DynamicValidationHelper caches can be configured in the /server/default/runtime/default/ container which is the default when no configuration is found in the /server/default/standard/runtime/default/ container.

Session Lifespan

This is an option that can be configured in directory.xml, it allows for any session to be reclaimed in a configurable time period after not being used.
It can be defined in the persistence container in the following way:

<node class="container" name="persistence">
    <node class="integer" name="session-lifespan">
        <node-attribute name="value" value="1000"/>
    </node>
</node>

The attribute value is measured in milliseconds. In the example above, a session can be reclaimed only if it didn't exceed 1 second of inactivity.

Each persistence context will have a SessionFactory instance associated which will manage the create/mark/reclaim/expire process of a session:
  • When a session is created, it will check for any session that can be reclaimed. It will create one if none can be reclaimed or return the existent reclaimable session.
  • When a session is not used anymore and needs to be closed, it is marked as reclaimable instead.
  • A session expires when the time period in which it can be claimed is exceeded. In this case, the session is closed.

All SessionFactory instances are registered to SessionFactory.SessionCloseThread which manages the closing of the sessions that exceed the time period in which they can be reclaimed.

Lifespan options:
  • If no option is set, the lifespan will be set to a default value (1 second).
  • If 0 is used as an option, the session will go through the reclaim process indefinitely. The managing thread will not start, but the SessionFactory instances will still be registered and deregistered. In this way, when the context is closed, the SessionFactory will be deregistered and any reclaimable session will be closed.
  • If a negative value is provided, session reclaiming will be disabled.
  • If a positive value is provided, it will represent the maximum time in which the session can be reclaimed.

Database Instances

Each database instance which the application can use must be configured via a database container, which at a high level looks like this:

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="p2j">
      <!-- general settings -->
      ...
    </node>
    <node class="container" name="orm">
      <!-- ORM (Object-to-Relational-Mapping) settings -->
      ...
    </node>
  </node>

The name attribute in the second level container above with the value my_database corresponds with the physical database name in the legacy environment. This can be (but is not necessarily) different from the schema name and database instance name described in the sections below.

Each of the XML comments above are placeholders for nested containers with specific settings, possibly in more deeply nested containers. These settings are described in the various sections below.

P2J Section

The p2j container holds general purpose FWD database runtime settings:

  • schema (String) the name of the database's schema; typically, this is the name of the schema that was exported from the 4GL Data Dictionary. This can be, but it does not need to be, the same name as the name configured in the database container.
  • load_at_startup (boolean) determines whether the database is auto-connected (in the 4GL sense, not the JDBC sense) at server startup. If not auto-connected, it is assumed the original 4GL code used a CONNECT statement when this database needed to be connected. [TODO: this setting is obsolete; document new database-connections configuration]
  • embedded-collation (String) this is used to set collation rules. The value should be set to the string value en_US_P2J, which currently is the only custom collation supported by FWD. [TODO: document newer collator settings]

Example:

<node class="container" name="database">
  <node class="container" name="my_database">
    ...
    <node class="container" name="p2j">
      <node class="string" name="schema">
        <node-attribute name="value" value="my_schema"/>
      </node>
      <node class="boolean" name="load_at_startup">
        <node-attribute name="value" value="TRUE"/>
      </node>
      <node class="string" name="embedded-collation">
        <node-attribute name="value" value="en_US_FWD"/>
      </node>
   </node>
   ...

ORM Section

The ORM section contains settings which allow FWD to map its persistence framework Java objects, which represent relational database constructs (e.g., tables, records, etc.), to the corresponding constructs in the backing database.

Dialect

The dialect container is used to specify the database dialect in use.

The levels of support for SQL standards among different database vendors vary, and each vendor may define extensions and features not specified by standards. To account for these differences in syntax, FWD implements an abstraction layer which requires the type of database dialect in use to be specified.

As of FWD version 4, the following dialects are provided:

Database Dialect Name Notes
PostgreSQL com.goldencode.p2j.persist.dialect.P2JPostgreSQLDialect Recommended for production use
SQL Server com.goldencode.p2j.persist.dialect.SQLServerDialect Developed several years ago, support may be incomplete; has not been tested recently
H2 com.goldencode.p2j.persist.dialect.H2Dialect Used heavily for testing and for FWD's temporary table support; not recommended for external database production use 
<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="string" name="dialect">
        <node-attribute name="value" value="com.goldencode.p2j.persist.dialect.P2JPostgreSQLDialect"/>
      </node>
      ...
Connection

The connection container is used to set up a JDBC connection to the permanent database as configured in the specified directory. The common path the connection directory nodes have in common is:

databaseDirNodePath + my_permanent_db + "/orm/connection/"

where databaseDirNodePath is the full database container path from the directory file and the my_database variable represents the name of the database.

The attributes of the connection container are:

  • driver_class : (String) the Java Driver class used to create the JDBC Connection
  • username : (String) the user name used to connect to the database.
  • password : (String) the password used to authenticate the user when connecting to the data base
  • url : (String) the jdbc url address of the database to which the connection is made. Note that the database name used in this URL does not need to match the physical database name configured in the database container; it is the name of the relational database instance to which JDBC connections will be made.
  • prepareThreshold (optional/Integer) : The threshold number of times a statement is called for which the query will be used as a named query, saving the execution plan at the database server (PostgreSQL only).

Example:

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="container" name="connection">
        <node class="string" name="driver_class">
          <node-attribute name="value" value="org.postgresql.Driver"/>
        </node>
        <node class="string" name="username">
          <node-attribute name="value" value="my_db_username"/>
        </node>
        <node class="string" name="password">
          <node-attribute name="value" value="my_db_users_password_in_clear_text"/>
        </node>
        <node class="string" name="url">
          <node-attribute name="value" value="jdbc:postgresql://localhost/my_database_instance"/>
        </node>
        <node class="integer" name="prepareThreshold">
          <node-attribute name="value" value="1"/>
        </node>
     </node>
     ...
C3P0

The c3p0 section can be used to provide initialization data for the c3p0 database connection pool.

c3p0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. In particular, c3p0 provides several useful services:

  • Classes which adapt traditional DriverManager-based JDBC drivers to the new javax.sql.DataSource scheme for acquiring database Connections.
  • Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.

The attributes of the c3p0 container are:

  • minPoolSize (Integer) the minimum number of connections a pool will maintain at any given time.
  • maxPoolSize (Integer) the maximum number of connections the pool will maintain at any given time. Be sure your database configuration allows at least this many connections (it is a good idea to reserve a few extra connections which the pool will not use, for database administration).
  • aquireIncrement (Integer) the number of connections at a time c3p0 will try to acquire when the pool is exhausted.
  • maxIdleTime (Integer) the number of seconds a pooled connection will be available before it is released. Set to 0 for connections that do not expire.
  • maxStatementsPerConnection (Integer) the maximum number PreparedStatements a DataSource will cache per JDBC connection. The pool will destroy the least-recently-used PreparedStatement when it hits this limit. Statement pooling is optional and may help or hurt performance, depending on the particular application. Remove this parameter or set it to 0 to disable statement pooling.

Please see also https://www.mchange.com/projects/c3p0/#configuration for more details on the above configuration options, which are passed through to c3p0.

Example (actual values should be determined by the needs of a particular application; those which affect performance should be determined through testing/profiling):

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
    ...
      <node class="container" name="c3p0">
        <node class="integer" name="minPoolSize">
          <node-attribute name="value" value="20"/>
        </node>
        <node class="integer" name="maxPoolSize">
          <node-attribute name="value" value="400"/>
        </node>
        <node class="integer" name="acquireIncrement">
          <node-attribute name="value" value="20"/>
        </node>
        <node class="integer" name="maxIdleTime">
          <node-attribute name="value" value="600"/>
        </node>
        <node class="integer" name="maxStatementsPerConnection">
          <node-attribute name="value" value="100"/>
        </node>
      </node>
      ...
JDBC

This section is used for JDBC additional settings:

  • fetch_size (Integer) represents the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query's scrollable result set. [TODO: this is a global parameter; move this documentation accordingly]

Example:

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="container" name="jdbc">
        <node class="integer" name="fetch_size">
          <node-attribute name="value" value="1024"/>
        </node>
      </node>
      ...
SQL Logging

It can be useful in limited situations to log all SQL statements which are executed by FWD for a particular database. The verbose nature of this logging can result in a very noise server log, and performance of an application may be adversely affected with SQL logging enabled. Therefore, this setting is only intended for diagnostic/debug use and it is recommended that it should be disabled for normal production use.

This setting is configured directly under the orm container.

  • show_sql ( boolean) used to set enable the logging of all the generated SQL statements. The default value is FALSE.

Example:

<node class="container" name="database">
  <node class="container" name="my_database">
    <node class="container" name="orm">
      ...
      <node class="boolean" name="show_sql">
        <node-attribute name="value" value="TRUE"/>
      </node>
      ...

Example Configuration

Here is a full example of a permanent database configuration:

        <node class="container" name="database">
          <node class="container" name="my_database">
            <node class="container" name="p2j">
              <node class="string" name="schema">
                <node-attribute name="value" value="my_schema"/>
              </node>
              <node class="boolean" name="load_at_startup">
                <node-attribute name="value" value="TRUE"/>
              </node>
              <node class="string" name="embedded-collation">
                <node-attribute name="value" value="en_US_P2J"/>
              </node>
            </node>
            <node class="container" name="orm">
              <node class="string" name="dialect">
                <node-attribute name="value" value="com.goldencode.p2j.persist.dialect.P2JPostgreSQLDialect"/>
              </node>
              <node class="container" name="connection">
                <node class="string" name="driver_class">
                  <node-attribute name="value" value="org.postgresql.Driver"/>
                </node>
                <node class="string" name="url">
                  <node-attribute name="value" value="jdbc:postgresql://localhost:5433/my_database_instance"/>
                </node>
                <node class="string" name="username">
                  <node-attribute name="value" value="fwd_user"/>
                </node>
                <node class="string" name="password">
                  <node-attribute name="value" value="user"/>
                </node>
                <node class="integer" name="prepareThreshold">
                  <node-attribute name="value" value="1"/>
                </node>
              </node>
              <node class="boolean" name="show_sql">
                <node-attribute name="value" value="FALSE"/>
              </node>
              <node class="container" name="c3p0">
                <node class="integer" name="minPoolSize">
                  <node-attribute name="value" value="10"/>
                </node>
                <node class="integer" name="maxPoolSize">
                  <node-attribute name="value" value="50"/>
                </node>
                <node class="integer" name="acquireIncrement">
                  <node-attribute name="value" value="5"/>
                </node>
                <node class="integer" name="maxIdleTime">
                  <node-attribute name="value" value="900"/>
                </node>
                <node class="integer" name="maxStatementsPerConnection">
                  <node-attribute name="value" value="100"/>
                </node>
              </node>
              <node class="container" name="jdbc">
                <node class="integer" name="fetch_size">
                  <node-attribute name="value" value="1024"/>
                </node>
              </node>
            </node>
          </node>
        </node>

To reiterate, the legacy physical database name (in this case, my_database) does not need to match the legacy schema name (in this case my_schema), and neither needs to match the relational database instance name (in this case, my_database_instance).

The legacy physical database name and the legacy schema name are used by the FWD server to logically identify a relational database configuration, mapping it to legacy database references in an application. The relational database can have any arbitrary name; the JDBC URL specified in the directory's database configuration for a particular legacy database tells FWD how to connect to that resource.

Reference

With the exception of the port_services section, all of the configuration values below must be located in a <db_path>/database/<db_name>/ container. The port_services section must be placed directly under /server/default/ or in server/<server_id>/.

Here is the list of the database options and their path which can be configured within the directory:

Option ID Data Type Default Value Required Details
port_services/<service_name> Integer N/A No FWD uses this data to create a logical connection (in the 4GL sense) to a database using the specified parameters. It can also be specified as parameters to server startup if not given inside directory.
p2j/schema String N/A Yes The name of the schema associated with this database, as exported from the 4GL Data Dictionary.
p2j/load_at_startup boolean N/A Yes This is used to determine auto-connect status, in the 4GL connection sense (not the JDBC connection sense).
p2j/embeded_collation String N/A Yes This is used to set collation rules. The value should be set to the string value en_US_P2J which currently is the only custom collation supported by FWD.
orm/connection/driver_class String N/A Yes The Java Driver class used to create the JDBC connection.
orm/connection/username String N/A Yes The username used to connect to the database.
orm/connection/password String N/A Yes The password used to connect to the database.
orm/connection/url String N/A Yes The JDBC URL (formatted according to the database vendor's requirements) used by JDBC to connect to the database instance.
orm/connection/prepareThreshold Integer 3* No The threshold number of times a statement is called for which the query will be used as a named query saving the execution plan at the database. * Only used by PostreSQL (defaults to 3).
orm/c3p0/minSize Integer N/A Yes The minimum number of connections the pool will maintain at any given time.
orm/c3p0/maxSize Integer N/A Yes The maximum number of connections the pool will maintain at any given time.
orm/c3p0/maxIdleTime Integer 0 No The number of seconds a pooled connection will be available before it is released. Set to 0 or omit for connections that do not expire.
orm/c3p0/maxStatementsPerConnection Integer 0 No The total number PreparedStatements a DataSource will cache per pooled connection. The pool will destroy the least-recently-used PreparedStatement when it hits this limit. Set to 0 or omit to disable prepared statement caching.
orm/c3p0/acquireIncrement Integer N/A Yes This parameter is used to determine how many connections at a time c3p0 will try to acquire when the pool is exhausted.
orm/jdbc/fetch_size Integer N/A Yes Represents the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet with next().
orm/show_sql boolean false Yes Enable/disable the logging of all the generated SQL statements to the server log.
orm/dialect String N/A Yes The Java class that which defines database dialect-specific behavior and syntax.

© 2004-2021 Golden Code Development Corporation. ALL RIGHTS RESERVED.