Project

General

Profile

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>

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

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.

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.