Foreword Professional

With the PostgreSQL DatabaseEngine (PGDBE), for the first time it is possible to move existing flat-file table and index files based Xbase++ applications to a leading SQL Database Management System without any restrictions.

The PostgreSQL DatabaseEngine makes this possible with its hybrid characteristics. First, the DatabaseEngine behaves exactly as the DBFDBE, FOXDBE, NTXDBE and CDXDBE DatabaseEngines when opening or creating tables using standard xBase commands and functions such as DbUseArea(), DbCreate() or USE. In other words, the PostgreSQL DatabaseEngine supports navigational or Index-Sequential-Access to SQL Data. Second, by using SQL commands such as SELECT, INSERT, UPDATE or DELETE, the DatabaseEngine supports the transparent use of the PostgreSQL Database Management System. Therefore no restrictions apply for applications developed using SQL as the only data access and data manipulation method. Finally, the DatabaseEngine allows mixing of the two different access methods, thereby supporting the migration of existing navigational data access based applications to the PostgreSQL server and then, if wanted, adding SQL query capabilities in a step-by-step manner to increase performance.

In fact, developers who don't want to move from their navigational approach of thinking can continue to write new code based on their existing knowledge of navigational commands and functions of the Xbase++ language. On the other hand, developers who feel more comfortable using SQL can develop new applications using SQL data access methods only. Even the mixing of development teams with different levels of knowledge becomes possible, as the two data access methods supported by the DBE do not interfere with each other and can be mixed as required.

Based on the previously outlined feature set, the PostgreSQL DatabaseEngine should be considered the DBE of choice for all Xbase++ applications that need to share data between different workstations.

Benefits from using the PostgreSQL server with the PostgreSQL DatabaseEngine are:

a) Continued use of existing navigational business logic and data access code, thereby protecting existing investments, and the migration of existing DBF/NTX/CDX based applications to an SQL client/server approach without any major code changes.

b) Optional use of SQL for future application enhancements, or rewrite of existing navigational queries using SQL SELECT statements.

c) Increased performance in multi-user scenarios due to the client/server architecture. Only the data requested is transferred to the client. In the event of navigational data access, an intelligent and partitioned caching layer ensures optimum performance with thrashing the SQL server by repeated SELECT statements.

d) Isolation. Transactions are by default read-committed. This means queries or seek operations do not see record and index updates until they are committed. Multiple record changes of one or more tables can be grouped together into a single transaction. Making the data change only visible after the commit ensures that other users always have a consistent view of the data.

e) Reliability and recovery capabilities for free. Power outages on the client side no longer have any effect on data consistency. Index corruptions are no longer possible. Power outages on the server can be recovered.

f) Scalability in terms of users and data size. The amount of users supported by the PostgreSQL server is limited only by the underlying hardware.

g) Full text search capabilities for any character field (char, varchar, text). Additional features are result-ranking and result-highlighting. Individual stop-word dictionaries can be managed as well.

h) Support for geo-spatial data and queries. The PostgreSQL Server not only comes with data types to describe spatial data, such as point, line, rectangle, path, polygon and circle, it also has a rich set of geometric operations to perform scaling, do translations, perform intersections, and determine distances. Therefore it is very easy to write queries such as "show me all customers within 100 km around my current location".

i) Support for complex data types such as arrays, composite types, and XML data.

j) Finally, there are various extensions out there, ranging from load-balancing to database-replication, making it possible to use the PostgreSQL server in small, medium and even enterprise scenarios. This makes it the primary choice for all needs when it comes to multi-user data management.

About transactions

The PostgreSQL DatabaseServer implements concurrency control using an approach called Multiversion concurrency control. This means there are different versions of a single record/row stored in the database. Using this approach the SQL server can avoid unnecessary lock operations, which increases performance and still delivers consistent data to the client. In other words, MVCC provides each connection to the database with a snapshot of the database. Any changes made will not be seen by other users of the database until the transaction has been committed. This type of isolation level is known as read-commited isolation.

It should be noted that because of this feature, "dirty reads", as in the case of explicit locking of records with isam tables, are no longer possible. This does not lead to problems, it leads to more consistent data and may result in fewer errors within existing applications.