Database sync solutions for Delphi [closed]

Question

I am looking for some starting points integrating a Win32 Delphi application's data with a remote database for a web application.

Problem(s) this project intends to solve:

1) The desktop does not perform well over vpns. Users in remote office could use the web app instead.

2) Some companies prefer a web app to the desktop app

3) Mobile devices could hit the web app as a front end.

Issues I've identified:

Web application will run on a Unix based system, probably Linux while the desktop application uses NexusDB while the web application will likely be Postgres. Dissimilar platforms and databases.

Using Delphi it appears the Microsoft Sync Framework is not available for this project.

My first thought was to give the web app your standard REST API and have the desktop app hit the API as though it's a client every n-minutes from the local database server. Tons of issues I see with this already!

Answer 1

Richard, I have been down this path before and all I can say is DON'T DO IT! I use to work for a company that had a large Delphi Desktop Application (over 250 forms) running on DBISAM (very similar to what you have). Clients wanted a "Web" interface so people could remotely work and then have the web app and desktop app synch changes. Well, a few years later and the application was horrible - data issues and user workflow was terrible because managing the same data in two different places is a nightmare.

I would recommend moving your database to something like MySQL (Delphi and Web Client both hit) and use one database between the two interfaces. The reason the Delphi client is not working well over the VPN is because desktop databases like NexusDB and DBISAM copy way to much data over the pipe when it runs queries (pulls back all the data and then filters/orders, etc)- it not truly client / server like SQL Server or MySQL where all the heavy lifting is being done on the server and only the results come back. Of course, moving the Delphi app to DB like MySQL could eleviate speed issues all together - but you don't solve #2 and #3 with that.

Another option is to move the entire application to the web and only have 1 application to support. Of course, a good UI developer in a tool like Delphi can always make a superior user interface to a web app - especially in data-entry heavy applications - so that may not be an option for you.

I would be very weary of "synching data".

My 2 cents worth. Mike

Answer 2

If you use a RESTful based ORM, you could have both for instance AJAX and Client Delphi applications calling the same Delphi server, using JSON as transmission format, HTTP/1.1 as remote connection layer, Delphi and Javascript objects to access the data.

For instance, if you type http://localhost:8080/root/SampleRecord in your browser, you'll receive something like:

[{"ID":1},{"ID":2},{"ID":3},{"ID":4}]

And if you ask for http://localhost:8080/root/SampleRecord/1 you'll get:

{"ID":1,"Time":"2010-02-08T11:07:09","Name":"AB","Question":"To be or not to be"}

This can be consumed by any AJAX application, if you know a bit about JavaScript.

And the same HTTP/1.1 RESTful requests (GET/POST/PUT/DELETE/LOCK/UNLOCK...) are already available in any Client HTTP/1.1 application. The framework implements the server using the very fast kernel-mode http.sys (faster than any other HTTP server on Windows), and fast HTTP API for the client. You can even use HTTPS to handle a secure connection.

IMHO, using such an ORM is better than using only a database connection, because:

• It will follow more strictly the n-Tier principle: the business rules are written ONCE in the Delphi server, and you consume only services and RESTful operations with business objects;
• It will use HTTP/1.1 for connection which is faster, more standard across the Internet than any direct database connection, and can be strongly secured via HTTPS;
• JSON and RESTful over HTTP are de-facto standard for AJAX applications (even Microsoft uses it for WCF);
• The data will be transmitted using JSON, which is a very nice format for multiple front-end;
• The Stateless approach makes it very strong, even in unconnected mode;
• Using a local small replication of the database (we encourage SQLite for this) allow you to have client access in unconnected mode (for Delphi client, or for HTML 5 clients).