All of the application data used by BaanERP is stored in database tables in the underlying RDBMS. To keep the majority of the BaanERP processing independent of the RDBMS, BaanERP uses its own data dictionary. The data dictionary includes domain, schema, and referential integrity information that is stored in a database independent manner.
The BaanERP system provides a RDBMS interface, called 'database driver', to the major RDBMSs (Oracle, Informix, Sybase, DB2, and Microsoft SQL Server). The BaanERP database driver has a built-in mechanism for preserving referential integrity; it does not depend on the underlying RDBMS for maintaining referential integrity.
BaanERP Database Concepts
A relational database presents information to the user in the form of tables. In a table, data is organized in columns and rows. Each column (also referred to as a field) represents a category of data. Each row (also referred to as a record) represents a unique instance of data for the categories defined by the columns. A field always refers to a domain, which defines a set of values from which one or more fields can draw their actual values. For example, the "tcweek" domain is the set of all integers greater than zero and less than or equal to 53.
Every database table has a field, or a combination of fields, which uniquely identify each record in the table. This unique identifier is referred to as the primary key. Primary keys are fundamental to database operations, as they provide the only record-level addressing mechanism in the relational model. Primary keys act as references to the records in a table.
With a relational database, you can store data across multiple tables and you can define relationships between the tables. This means that individual tables can be kept small and data redundancy can be minimized. A relationship exists between two tables when they have one or more fields in common. So, for example, a Customer Detail table can be linked to an Order table by including a Customer ID field in both tables. In the Customer Detail table, the Customer ID field is the primary key. In the Order table, it is referred to as a foreign key. By linking the two tables in this way, there is no need for the Order table to include customer details such as name and address. Note that references from one table to another must always use the primary key.
Indexes facilitate speedy searching and sorting of database tables. An index is a special kind of file (or part of a file) in which each entry consists of two values, a data value and a pointer. The data value is a value for some field in the indexed table. The pointer identifies the record that contains this value in the particular field. This is analogous to a conventional book index, where the index consists of entries with pointers (the page numbers) that facilitate the retrieval of information from the body of the book. Note that it is also possible to construct an index based on the values of a combination of two or more fields. Every table must have at least one index, which is an index on the primary key field(s). This is referred to as the primary index. An index on any other field(s) is referred to as a secondary index.
With respect to database actions, a transaction is a sequence of related actions that are treated as a unit. The actions that make up a transaction are processed in their entirety, or not at all.
A transaction ends with the function commit.transaction() (all changes made during the transaction are stored in the database) or with the function abort.transaction() (no changes are stored in the database). A transaction starts either at the beginning of a process, with the function set.transaction.readonly(), with the function db.lock.table(), or after the preceding transaction has ended. A transaction is automatically rolled back (that is, it is undone) when a process is canceled and if a program ends without a commit.transaction() or abort.transaction() after the last database call. Undoing a transaction is only possible if the underlying database system supports this.
Certain database actions cannot be placed within a transaction, because they cannot be rolled back. These actions are: db.create.table(), db.drop.table(), and set.transaction.readonly()]. These functions can be called only at the start of a program or after the end of the preceding transaction.
You can set a retry point immediately before a transaction. In case of an error, the system returns to this point and re-executes the transaction from there.
A read-only transaction is a transaction in which you are permitted only to read records (without lock) from the database. You retain read consistency during the entire transaction. This means that during the transaction your view of the database does not change, even if other users update the records. A read-only transaction starts with the function set.transaction.readonly() (this must be called after ending the preceding transaction or at the beginning of the program) and ends with a commit.transaction() or abort.transaction(). A consistent view consumes a large amount of memory, so a read-only transaction must be as short as possible; user interaction during the transaction is not recommended.
Database inconsistencies can arise when two or more processes attempt to update or delete the same record or table. Read inconsistencies can arise when changes made during a transaction are visible to other processes before the transaction has been completed for example, the transaction might subsequently be abandoned.
To avoid such inconsistencies, BaanERP supports the following locking mechanisms: record/page locking, table locking, and application locking.
To ensure that only one process at a time can modify a record, the database driver locks the record when the first process attempts to modify it. Other processes cannot then update or delete the record until the lock has been released. However, they can still read the record. While one process is updating a table, it is important that other processes retain read consistency on the table. Read consistency means that a process does not see uncommitted changes. Updates become visible to other processes only when the transaction has been successfully committed. Some database systems do not support read consistency, and so a dirty read is possible. A dirty read occurs when one process updates a record and another process views the record before the modifications have been committed. If the modifications are rolled back, the information read by the second process becomes invalid. Some databases, such as SYBASE and Microsoft SQL Server 6.5, use page locking instead of record locking. That is, they lock an entire page in a table instead of an individual record. A page is a predefined block size (that is, number of bytes). The number of records locked partly depends on the record size.
Locking a record for longer than required can result in unnecessarily long waiting times. The use of delayed locks solves this problem to a great extent. A delayed lock is applied to a record immediately before changes are committed to the database and not earlier. When the record is initially read, it is temporarily stored. Immediately before updating the database, the system reads the value of the record again, this time placing a lock on it. If the record is already locked, the system goes back to the retry point and retries the transaction. If the record is not locked, the system compares the content of the record from the first read with the content from the second read. If changes have been made to the record by another process since the first read, the error ROWCHANGED is returned and the transaction is undone. If no changes have occurred, the update is committed to the database.
You place a delayed lock by adding the keyword FOR UPDATE to the SELECT statement. For example:
SELECT pctst999.* FROM pctst999 FOR UPDATE
pctst999.dsca = "...."
A retry point is a position in a program script to which the program returns if an error occurs within a transaction. The transaction is then retried. There are a number of situations where retry points are useful:
- During the time that a delayed lock is applied to a record/page, an error can occur that causes the system to execute an abort.transaction(). In such cases, all that BaanERP can do is inform the program that the transaction has been aborted. However, if retry points are used, the system can automatically retry the transaction without the user being aware of this.
- Some database systems generate an abort.transaction()] when a dirty record is read (that is, a record that has been changed but not yet committed). An abort.transaction() may also be generated when two or more processes simultaneously attempt to change, delete, or add the same record. In all these situations, BaanERP Tools can conceal the problem from the user by using retry points. It simply retries the transaction. If there is no retry point, the transaction is aborted and the session is terminated.
- In BaanERP, updates are buffered, so the success or failure of an update is not known until commit.transaction() is called. If an update fails, the commit of the transaction also fails, and the entire transaction must be repeated. If retry points are used, the system automatically retries the transaction.
- Retry points can also resolve potential deadlock problems. If, for example, the system is unable to lock a record, it rolls the transaction back and tries again.
It is vital that retry points are included in all update programs. The retry point for a transaction must be placed at the start of a transaction. The following example illustrates how you program retry points:
db.retry.point() | set retry point
if db.retry.hit() then
...... | code to execute when the system
| goes back to retry point
...... | initialization of retry point
The function db.retry.hit() returns 0 when the retry point is generated that is, the first time the code is executed. It returns a value unequal to 0 when the system returns to the retry point through the database layer.
When the system goes back to a retry point, it clears the internal stack of functions, local variables, and so on that were called during the transaction. The program continues from where the retry point was generated. The value of global variables is NOT reset.
When a commit fails, the database automatically returns to its state at the start of the transaction; the program is set back to the last retry point. It is vital, therefore, that the retry point is situated at the start of the transaction. The db.retry.hit() call must follow the db.retry.point() call. Do not place it in the SQL loop itself as this makes the code very nontransparent. When a retry point is placed within a transaction, the system produces a message and terminates the session.
BaanERP provides a table locking mechanism, which enables you to lock all the records in a specified table. A table lock prevents other processes from modifying or locking records in the table but not from reading them. This is useful when a particular transaction would otherwise require a large number of record locks. You use the db.lock.table() function to apply a table lock.
An application lock prevents other applications and users from reading and/or modifying an applications data during critical operations. It is not part of a transaction and so is not automatically removed when a transaction is committed. Instead, an application lock is removed when the application ends or when appl.delete() is called.
Microsoft SQL Server Database Driver
This section describes the RDBMS interface issues with respect to Microsoft SQL Server.
Because so many tables are needed, a convention is used for naming tables, columns within tables, and indexes to data within the tables. This chapter describes the data dictionary and the naming conventions used by the BaanERP database drivers to access data stored in the RDBMS. It also discusses how BaanERP data types are mapped to SQL Server data types.
The BaanERP data dictionary maps BaanERP data types, domains, schemas, and referential integrity information to the appropriate information in the RDBMS. When storing or retrieving data in the RDBMS, the database driver maps data dictionary information to database table definitions. BaanERP data dictionary information can be kept in shared memory where it will be available to all running BaanERP application servers. The data dictionary information is shared among all the sessions open within a single database driver.
The BaanERP data types cannot be used directly by the database driver to create SQL Server tables. This is because not all BaanERP data types exactly match SQL Server data types. To create valid SQL Server tables, the driver must perform some mapping or translation. When mapping the BaanERP data dictionary to tables in SQL Server, conventions are used for the table names, column names, and index names.
Table naming convention
The external name of a BaanERP table stored in SQL Server has the following format:
The components of the external table name are:
- A two-letter code referring to the BaanERP package the table belongs to. For example, a table defined by the Tools package has the package code tt.
- The data dictionary table name consists of a three-letter module identifier followed by a three-digit number. The module identifier refers to the module the table belongs to; the number is just a sequence number.
- Within BaanERP, three-digit numbers are used to identify different instances of the BaanERP application database, called 'companies'. Company number 000 denotes the meta-database containing various system data common to all companies (including currencies and languages used). In addition to company 000, there may be several other companies in a BaanERP system, each with its own set of tables for application data.
Column naming convention
Each column in the BaanERP data dictionary corresponds to one or more columns in a SQL Server table. The rules for column names are as follows:
- When a BaanERP column name is created in SQL Server, it is preceded by the string t_. For example, the BaanERP column with the name cpac is created in SQL Server with the name t_cpac. If a BaanERP column name contains a period, it is replaced by the underscore character.
- Long string columns
- BaanERP columns of type string can exceed the maximum length of character columns in SQL Server. The SQL Server data type CHAR has a limit of 254 characters. When a BaanERP string column exceeds this limit, the column is split into segments with up to 254 characters each. The first 254 characters are mapped to a column where the name of the column is extended with
_1. The next 254 characters are mapped to a column with a name extended by 2, and so on, until all the characters of the string are mapped to a column. For example, if a BaanERP string column called desc contains 300 characters, the following two SQL Server columns are created: t_desc_1 with size 254, and t_desc_2 with size 46.
Table 1: Mapping between BaanERP and MSQL data types.
|BaanERP data types
||MSQL data types
- Array columns
- In the BaanERP data dictionary, array columns can be defined. An array column is a column with multiple elements. The number of elements is called the depth. For example, a column containing a date can be defined as an array of three elements: a day, a month, and a year. In SQL Server, the three elements of the array column are placed in separate columns. The names of these columns include a suffix with the element number. For example, an array column called date will be transformed to: t_date_1 for element 1, t_date_2 for element 2, and t_date_3 for element 3.
Data type mapping
Table 1 shows the mapping between BaanERP data types and their SQL Server counterparts.
Note that the MSQL driver uses the SQL Server CHAR data type since ANSI-compliant behavior is expected for character data, such as with the BaanERP string type. Since BaanERP SQL expects ANSI-compliant string comparison semantics, the SQL Server CHAR data type is used instead of VARCHAR. This SQL Server data type is used because a BaanERP string data type has characteristics that conform to the ANSI specification for character data. When the CHAR data type is used, operations such as comparison and concatenation can be done in a predefined manner with predictable results.
- In addition to the above naming conventions and data types, the following rules apply when mapping BaanERP data to SQL Server data:
- Since the binary sort order is selected during the installation, SQL Server treats object names with case sensitivity.
- All columns created by the BaanERP database driver have the NOT NULL constraint. BaanERP does not support the NULL value concept of SQL.
- The date range supported by the BaanERP application server is not the same as the range for SQL Server (SQL Server is more restrictive), so some BaanERP dates are not valid when stored with the MSQL driver. The BaanERP date number 0 is mapped to the earliest possible date in SQL Server (01-Jan-1753). The earliest possible BaanERP date is then 02-Jan-1753 and the latest is 31-Dec-9999.
The ODBC interface
ODBC is an application programming interface (API) used to communicate with the database server. It is made up of a function library that can be called from an application program to execute SQL statements and communicate with the data source. The ODBC functions called by the MSQL database driver perform the following actions:
- Connect to Microsoft SQL Server (open session)
- Allocate a statement handle
- Parse a SQL statement
- Bind input variables
- Define result variables
- Execute a SQL statement
- Fetch the resulting rows
- Commit or abort a transaction
- Close, unbind and drop a cursor
- Disconnect from MSQL (close session)