Best strategy to get data from different SQLite Db's in different computers...

General
1

I have made an app that gathers and processes some data locally using an SQLite DB. This was installed in different computers, in different locations… Now I want to develop a new app, kind of ‘parent’, to be able to centralize, compute and display the results of every instance of the original installed programs…

What do you guys suggest is the best approach to connect the apps and send/pull the data to the main app ?

thanks
R.-

2

PostgreSQL for one, MySQL for another, a custom socket for a third.

3

Hey David… Could you please elaborate a bit ?
The apps rely on SQLite so I don’t know how should I implement another type of DB.

Can you give me an example of a custom socket ?

Thanks

4

Basically, what you have is a bunch of instances of your app on various computers, each managing its own data. You want a central place to capture the data. That will likely be a database server, implemented on a machine that can be reached from each of the users computers. As @David Cox indicated, Postgre is an excellent cross platform choice. I prefer to stay away from mySQL however, because of its very tricky licence.

If you have not done so yet, you will have to build into your app some code to send the local data to the server, presumably through a middleware app that will manage updating and conflicts as the case may be. Sockets is one way that the app could communicate with the middleware. Of course, if you never built code to synchronize your local data with a server, you will have to distribute the updated app to every user.

5

Hey Louis thanks for the reply…

You got the situation right, but I don’t want to capture data in a new database. I just want to compute new totals and display them. Not sure I need another DB.

My main difficulty relies on that I don’t know how to establish a link between the instances and the new app that has to display the data of the local db’s

6

Create an app with a ServerSocket and have each client use a TCPSocket to communicate with it. Another approach would be use UDP.

7

SQLite is NOT an SQL server, it cannot handle multiple connections to itself with any level of certainty. At best you will lock the database and stop all the other ‘clients’ accessing it (including the local user), at worst you will corrupt it. SQLite locks the entire database when updating/inserting.

See https://www.sqlite.org/faq.html#q5

If you are only going to read then you have some modicum of hope of success. If you are going to write then you will have to write a server that handle the SQL transactions, locking, queries, etc. This server would have to be on all the remote machines. In other words, you would have to write what MariaDB/MySQL/Postgres et al do.

The other approach is to export the data to some format, transfer it to your central application and load it into SQLIte. You could do this with a batch/cronjob to read selected files from sqlite and export as SQL. Then compile all these together and import them.
Of course, you could write your own software to achieve the same result: export the SQL from remote machines, import it into the local one. How you transfer it depends on the remote machines, ie, behind a firewall or NAT device.