Hello,
I would like to know, if is Xojo good for multiuser database application - inhouse CRM. How is solved data synchronization between different clients? Can I have auto-commit on fields?
Thanks
PostgreSQL has a Listen and Notification system that can be used for this. There is an example in Database/PostgreSQL/ListenAndNotify.
We use the built in SQLite database in a multi-user environment with 3-4 users and a database with about 10K records in it. We haven’t had any issues but you need to make sure you do the error checking for the potential for multiple users writing to the database at the same time.
This post might be helpful to you: Here ya go…
My CRM uses mySQL for the central database and SQLite for the local copy per user when speed it needed. I create the SQLite db, copy out the mySQL data to it, index it, use it, sync the data back, then toast the local SQLite file(s). Syncing will always be a pain unless you lock records then it is even more pain!
Hi david I care what you done, where I can see an example, I want to do the same with mysql synchronize local data base with sqlite.
Syncing between local and remote databases is really hard, if you want to have multiple local clients and multiple tables, with relationships between them. Have a look at the post Joseph linked to for a good start. Given the amount of time and effort it takes to develop syncing services I doubt you’ll get people sharing code for free…
The Listen example seems to be missing the Port for the connection and fails with "Couldn’t connect to server: invalid port number: “0"”
I’ve found the big problem with synching is conflict resolution which is completely different in almost all cases. Business rules vary widely and make an “out of the box” implementation tough.
Seconded, thirded, and generally pointed at with bells on. Syncing is hard.
And usually involves “business rules” which are hard to generalize to each application
If the rule is just “newest wins” that not so bad - compare timestamps & push from client to server or server to client.
But its rarely that simple
[quote]Hi david I care what you done, where I can see an example, I want to do the same with mysql synchronize local data base with sqlite.
[/quote]
I have a CRM app called CRMHaven that stores all its data in the cloud (MySQL). When I perform a de-duplication operation, I download every customer’s name and address details to an SQLite database. Xojo can now search for duplicates very fast, showing you the results in a Preview. If you choose to accept its results, it then writes the few updates to the MySQL server quickly then deletes the local SQLite database.
I originally was doing this without the SQLite database on 100K clients and it was taking hours. With the local database it changed to seconds.
In this case, remember to take note of timezones. A time in the future is not necessarily newer, just to complicate matters!
Never store ANYTHING in local time zones
Use UTC and convert when displaying
Been there done that have the battle scars to show for it