Combining multiple databases on disk?

General
1

A different question. To recap:

I’m trying to use multi-processing where each helper app is creating an in memory database (two tables, where each has the other as foreign key)

At the end I need to combine all these databases into one.

I could save them to disk and use a shell in the main app to combine them with

sqlite3 database1 .dump | sqlite database3
sqlite3 database2 .dump | sqlite database3

and then load the resulting database3

Now with multiple helper apps (and therefore multiple databases) how would I make sure they are not interfering with each other? So when I run a shell command and then run the next one … how does that work if the previous action hasn’t finished?

2

I would not count on the OS to help manage the databases for you.

Perhaps, your helper apps could somehow signal the main app that they are done, and what is their respective database file is. There would be code in the main app to read the database files. The code would include logic to manage conflicts.

3

Sorry, I had changed the question while you answered, so your answer seems a bit off.

But yes, I had concluded too that the main app should do the combining (so I just need to pass the filename back which is easy) but the problem still persists - see above.

4

Could you create a main db in your app, helpers create on disk dbs - queue the folderitems in your app and use maindb.AttachDatabase -> process -> maindb.DetachDatabase on the queued dbs to pull them together ?

5

leave helpers db in memory and attach them to the main db on disk…

6

How?

7

http://documentation.xojo.com/index.php/SQLiteDatabase.AttachDatabase

8

Thanks, but Attach is not what I’m looking for. It all has to go into one database for storage and later (sometimes much later) retrieval.

9

you attach one db to the other
copy records using sql
detach

10

Have a look at this blog by Geoff.

11

@Wayne Golding : In this case you have to do the reverse:
attach to the memory db the general db (with file)
copy the record with a single sql command (or more for more tables)
detach the db

@Markus Winter : I’ve done in this way in many apps, since I have to elaborate a lot of data. A memory db is really, really fast and save to the db is faster then file to file copy.