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:
- 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.
- HashMap: Only the capacity (cache size) is used to create it.
cache-size
: Main container which stores all the cache size configurations. Cache size are read by iterating over all the containers insidecache-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 is10000
;com.goldencode.p2j.persist.DynamicTablesHelper
:cache
default size is8192
;com.goldencode.p2j.persist.FQLHelperCache
:cache
default size is8192
;com.goldencode.p2j.persist.FQLPreprocessor
:cache
default size is2048
(without a discriminator) andastCache
default size is8192
(the discriminator is"ast"
);com.goldencode.p2j.persist.Persistence
:staticQueryCache
default size is1024
;com.goldencode.p2j.persist.orm.Persister
:updateCache
default size is4096
;com.goldencode.p2j.persist.orm.Session
:cache
default size is1024
;com.goldencode.p2j.persist.FastFindCache
:l2Cache
default size is10
(the discriminator is"L2"
) andl3Cache
default size is100
(the discriminator is"L3"
);com.goldencode.p2j.persist.SortCriterion
:cache
default size is65536
;com.goldencode.p2j.persist.DynamicQueryHelper
:lvl1Cache
default size is65536
(the discriminator is"lvl1"
) andlvl2Cache
default size is16384
(the discriminator is"lvl2"
);com.goldencode.p2j.persist.DynamicValidationHelper
:cache
default size is65536
;com.goldencode.p2j.persist.orm.TempTableDataSourceProvider
:psCache
default size is8192
;com.goldencode.p2j.util.SourceNameMapper
:sourceCache
default size is8
(the discriminator is"source"
),searchPathCache
default size is64
(the discriminator is"search"
),SourceNameMapperCache.convertedNames
default size is16384
(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 throughCacheManager
after #8281 is finished and not be available anymore:
com.goldencode.p2j.persist.DynamicQueryHelper
:lvl1Cache
default size is65536
andlvl2Cache
default size is16384
;lvl1Cache
andlvl2Cache
can be configured in the/server/default/standard/runtime/default/
container.lvl1Cache
needs to be configured using thedynamic-query-cache-lvl1-size
node andlvl2Cache
with thedynamic-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 is65536
;cache
can be configured in the/server/default/standard/runtime/default/
container.cache
needs to be configured using thedynamic-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.
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.
- 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 theSessionFactory
instances will still be registered and deregistered. In this way, when the context is closed, theSessionFactory
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.