integration_possibilities
no way to compare when less than two revisions
Differences
This shows you the differences between two versions of the page.
Previous revision | |||
— | integration_possibilities [2014/12/30 15:01] (current) – external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Integration Possibilities====== | ||
+ | |||
+ | 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. | ||
+ | |||
+ | ===== Reporting 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. | ||
+ | |||
+ | ==== Examples ==== | ||
+ | |||
+ | |||
+ | 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, | ||
+ | |||
+ | ==== Known Issues ==== | ||
+ | |||
+ | |||
+ | SQL-Ledger uses a single ' | ||
+ | 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. | ||
+ | |||
+ | ==== Strategies ==== | ||
+ | |||
+ | 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. | ||
+ | |||
+ | ==== Examples ==== | ||
+ | |||
+ | 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' | ||
+ | |||
+ | 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. | ||
+ | |||
+ | ==== Strategies==== | ||
+ | |||
+ | |||
+ | 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, | ||
+ | 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, | ||
+ | DBI-Link can simplify the replication process by reducing the operation to a set of SQL queries. | ||
+ | |||
+ | **Next:** [[: | ||
+ | |||
+ | //(First version from: An Introduction to SQL-Ledger by Chris Travers, 2006)// | ||
integration_possibilities.txt · Last modified: 2014/12/30 15:01 by 127.0.0.1