Table of Contents
An open database system and programming API allows for many types of integration. There are some challenges, but in the end, one can integrate a large number of tools.
Any reporting tool which can access the PostgreSQL database can be used with SQL-Ledger for custom reporting. These can include programs like Microsoft Access and Excel (using the ODBC drivers), PgAccess (A PostgreSQL front-end written in TCL/Tk with a similar feel to Access), Rekall, Crystal Reports, OpenOffice and more.
We have created spreadsheets of the summaries of activity by day and used the ODBC driver to import these into Excel. Excel can also read HTML tables, so one can use PostgreSQL to create an HTML table of the result and save it with a .xls extension so that Windows opens it with Excel. These could then be served via the same web server that serves SQL-Ledger.
Line of Business Tools on PostgreSQL
Various line of business tools have been written using PostgreSQL in large part due to the fact that it is far more mature than MySQL in areas relating to data integrity enforcement, transactional processing, and the like. These tools can be integrated with SQL-Ledger in various ways. One could integrate this program with the HERMES CRM framework, for example.
SQL-Ledger uses a single 'id' sequence across many tables. At the same time it is expected that these tables do not have identical id values in their records as they are used as a sort of pseudo-foreign key by the acc.trans table which stores the financial transaction information. If the integration solution does not keep this in mind, it is possible to create a situation where the account transactions are ambiguously associated with a number of different types of financial transactions. This would lead to a large number of problems.
In general, it is advisable to run all such programs that benefit from integration in the same database but under different schemas. This allows PostgreSQL to become the main method of synchronizing the data in real time. However, sometimes this can require dumping the database recreating the tables etc. in a different schema and importing the data back into SQL-Ledger. One possibility for this sort of integration is to use database triggers to replicate the data between the applications in real-time. This can avoid the main issue of duplicate id's. One issue that can occur however relates to updates. If one updates a customer record in HERMES, for example, how do we know which record to update in SQL-Ledger? There are solutions to this problem but they do require some forethought. A second possibility is to use views to allow one application to present the data from the other as its own. This can be cleaner regarding update issues, but it can also pose issues regarding duplicate id fields.
Others have integrated L'ane POS and SQL-Ledger in order to make it work better with touch screen devices. Still others have successfully integrated SQL-Ledger and Interchange. In both cases, I believe that triggers were used to perform the actual integration.
Line of Business Tools on other RDBMS's
Often there are requests to integrate SQL-Ledger with applications like SugarCRM, OSCommerce, and other applications running on MySQL or other database managers. This is a far more complex field and it requires a great deal more effort than integrating applications within the same database.
Ordinarily real-time integration is not always possible. MySQL does not support the SQL extension SQL/MED (Management of External Data) so it is not possible to replicate the data in real-time. Therefore one generally resorts to integrating the system using time-based updates. Replication may be somewhat error-prone unless the database manager supports triggers (first added to MySQL in 5.0) or other mechanisms to ensure that all changed records can be detected and replicated. In general, it is usually advisable to add two fields to the record- one that shows the insert time and one that shows the last update. Additionally, I would suggest adding additional information to the SQL-Ledger tables so that you can track the source record from the other application in the case of an update. In general, one must write replication scripts that dump the information from one and add it to the other. This must go both ways.
Integration Products and Open Source Projects
While many people write Perl scripts to accomplish the replication, an open source project exists called DBI-Link. This package requires PL/Perl to be installed in PostgreSQL, and it allows Post-greSQL to present any data accessible via Perl's DBI framework as PostgreSQL tables. DBI-Link can be used to allow PostgreSQL to pull the data from MySQL or other database managers. DBI-Link can simplify the replication process by reducing the operation to a set of SQL queries.
Next: Customization Guide
(First version from: An Introduction to SQL-Ledger by Chris Travers, 2006)