SQLLite3 query over multiple databases...

Krivvenz · February 6, 2019, 9:30am

Hi Guys,

Does anyone know if it is possible to run a query using 2 local open sqllite3 databases?

I’ve not been able to get it working :(.

I want to copy data from rows in a table from database1 and insert / update them into a table in database2.

Something like the below for example:

UPDATE database1

SET database1.CoinValue = database2.CoinValue

FROM dbo.Coins AS database2

INNER JOIN ExistingDB.dbo.Coins AS database1 ON database2.CoinPK = database1.CoinPK

WHERE database1.CoinValue <> database2.CoinValue

From what I can see though I’m not sure this is possible?

Kind Regards,

Mike.

anaqim · February 6, 2019, 9:59am

I know this is not what you asked but i have never seen a reason to run 2 databases on a client, but if you have to I guess you have to.

It should not be a problem to open two databases but unless you provide us with sample lua code, your question is very generic.

I assume you studied this page https://docs.coronalabs.com/api/library/sqlite3/index.html

Krivvenz · February 6, 2019, 10:26am

Hi Anaqim,

It’s all to do with that problem I posted last week about data loss… with copying a master database from the resources directory to the documents directory for updating purposes, reading the existing database in the documents directory and storing it in memory, then writing it to the newly copied master database.

After the responses on the post I wanted to try and do the following:

Database structure example:

table_coins

id
date
title

table_coins will contain pre-populated data of all the coins.

1, 1999, Rare 1p

2, 2000 Rare 2p

table_userData

coin_id_fk
quantity

table_userData will contain user specific data that is manipulated through the app. E,g when a user types in that they have an amount of coins, this will insert a row into table_userData storing the information.

1, 3

2, 5

When the app is first opened, the master database is copied from resources to the documents directory and renamed to userData.

During an update:

userData.db will be in the documents directory.

Copy the master database from the resources directory to the documents directory (leaving name as master.db).

Open both databases.

Replace all of userData table_coins with master table_coins

In order to do that last step I wanted to do it via sql statements to avoid having to read each row from master, saving the results in memory then writing it to userData as that is basically how I do it at the moment with data loss occurring.

After reading everyones advice I was hoping to actually create a temp_table in userData, copy master.table_coins to userData.temp_table, check if it was successful, if it was then delete userData.table_coins and rename temp to table_coins.

I’ve been all over that page and the Internet for that matter lol… these 2 resources are very good but information seems sooo limited for what I am trying to do.

http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki

https://mroth.net/lua-sqlite3/documentation

Kind Regards,

Mike.

anaqim · February 6, 2019, 10:31am

you are talking about moving a very small amount of data.

why not keep your solution but add a trick to void potential data loss?

read user data into a table like you do

then save it to disk as a json file

clear the table

reload it from the disk (which also verifies that it was written correctly)

insert into new db

OPTIONAL delete the json from disk

Krivvenz · February 6, 2019, 10:48am

Hi Anaqim,
That was just an example, the real data set has about 20 column for table_coins with 600 rows and the userData has about 5 columns with up to 600 rows… still pretty tiny I know :).

So your idea during an update would be:

Save userData.table_userData as a Json file.

Delete userData database from documents directory.

Copy master database from resources directory to documents directory and re-name to userData.

Read stored Json file into memory.

Loop through Json performing inserts to userData.table_userData.

Some sort of verification / count the json records v the amount inserted.

Delete Json file.

Thanks for your replies by the way :).

anaqim · February 6, 2019, 10:56am

Hi Mike,

Yeah thats about it though it is probably good practice to wait with deleting anything until the very end, after the conversion has taken place and has been verified. You could also save those arrays on disk with version stamps and never delete any of them, for complete rollback possibilities, should the worst ever happen. Disk space cost is close to nil anyway.

In my current project I insert thousands of rows on local device in a split second with little to no indication that something is going on on the device (depending on device age and performance). You should be more than safe

agramonte · February 6, 2019, 2:20pm

why not create a new table instead of a new database?

Krivvenz · February 6, 2019, 2:34pm

Hi Agramonte,

How would that work? I would still need a master database to contain all the coin information and bundle it with the app and the user would still need a database in the documents directory so the app can manipulate user specific data for each coin.

agramonte · February 7, 2019, 3:25am

I would have to dig up the code. But at one point before I started using Gamesparks I had something similar. The update table was just a script. It would run and create the table. Then I can update the values in the other table after making a backup and then I would delete the update table.

carloscosta · February 8, 2019, 4:53pm

you have 2 solutions.

put your scores in a remote database. send and get data from that database should be very easy if you choose cloud storage.
use only 1 local database. You copy your master database from your resource to the documents folder so you can manipulate.

After that you only use the copied database, nothing more. check at start if the document database already exist. if not, copy a new one, if it exists, use it as your master. 99% of my apps use this method, till now, 0 issues.

never had a problem with any of this solutions.

I didn’t understood your logic about saving your scores, but should be pretty easy.

check local document database if exists
if not copy master to it.
if yes just open it to use it. (you never use your master database you only use your copied, and you only copy once the database, when first time the app is running)
update database as you wish.
closing app, close database.

next time you run your app its the same thing.

anaqim · February 6, 2019, 9:59am

I know this is not what you asked but i have never seen a reason to run 2 databases on a client, but if you have to I guess you have to.

It should not be a problem to open two databases but unless you provide us with sample lua code, your question is very generic.

I assume you studied this page https://docs.coronalabs.com/api/library/sqlite3/index.html

Krivvenz · February 6, 2019, 10:26am

Hi Anaqim,

It’s all to do with that problem I posted last week about data loss… with copying a master database from the resources directory to the documents directory for updating purposes, reading the existing database in the documents directory and storing it in memory, then writing it to the newly copied master database.

After the responses on the post I wanted to try and do the following:

Database structure example:

table_coins

id
date
title

table_coins will contain pre-populated data of all the coins.

1, 1999, Rare 1p

2, 2000 Rare 2p

table_userData

coin_id_fk
quantity

table_userData will contain user specific data that is manipulated through the app. E,g when a user types in that they have an amount of coins, this will insert a row into table_userData storing the information.

1, 3

2, 5

When the app is first opened, the master database is copied from resources to the documents directory and renamed to userData.

During an update:

userData.db will be in the documents directory.

Copy the master database from the resources directory to the documents directory (leaving name as master.db).

Open both databases.

Replace all of userData table_coins with master table_coins

In order to do that last step I wanted to do it via sql statements to avoid having to read each row from master, saving the results in memory then writing it to userData as that is basically how I do it at the moment with data loss occurring.

After reading everyones advice I was hoping to actually create a temp_table in userData, copy master.table_coins to userData.temp_table, check if it was successful, if it was then delete userData.table_coins and rename temp to table_coins.

I’ve been all over that page and the Internet for that matter lol… these 2 resources are very good but information seems sooo limited for what I am trying to do.

http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki

https://mroth.net/lua-sqlite3/documentation

Kind Regards,

Mike.

anaqim · February 6, 2019, 10:31am

you are talking about moving a very small amount of data.

why not keep your solution but add a trick to void potential data loss?

read user data into a table like you do

then save it to disk as a json file

clear the table

reload it from the disk (which also verifies that it was written correctly)

insert into new db

OPTIONAL delete the json from disk

Krivvenz · February 6, 2019, 10:48am

Hi Anaqim,
That was just an example, the real data set has about 20 column for table_coins with 600 rows and the userData has about 5 columns with up to 600 rows… still pretty tiny I know :).

So your idea during an update would be:

Save userData.table_userData as a Json file.

Delete userData database from documents directory.

Copy master database from resources directory to documents directory and re-name to userData.

Read stored Json file into memory.

Loop through Json performing inserts to userData.table_userData.

Some sort of verification / count the json records v the amount inserted.

Delete Json file.

Thanks for your replies by the way :).

anaqim · February 6, 2019, 10:56am

Hi Mike,

Yeah thats about it though it is probably good practice to wait with deleting anything until the very end, after the conversion has taken place and has been verified. You could also save those arrays on disk with version stamps and never delete any of them, for complete rollback possibilities, should the worst ever happen. Disk space cost is close to nil anyway.

In my current project I insert thousands of rows on local device in a split second with little to no indication that something is going on on the device (depending on device age and performance). You should be more than safe

agramonte · February 6, 2019, 2:20pm

why not create a new table instead of a new database?

Krivvenz · February 6, 2019, 2:34pm

Hi Agramonte,

How would that work? I would still need a master database to contain all the coin information and bundle it with the app and the user would still need a database in the documents directory so the app can manipulate user specific data for each coin.

agramonte · February 7, 2019, 3:25am

I would have to dig up the code. But at one point before I started using Gamesparks I had something similar. The update table was just a script. It would run and create the table. Then I can update the values in the other table after making a backup and then I would delete the update table.

carloscosta · February 8, 2019, 4:53pm

you have 2 solutions.

put your scores in a remote database. send and get data from that database should be very easy if you choose cloud storage.
use only 1 local database. You copy your master database from your resource to the documents folder so you can manipulate.

After that you only use the copied database, nothing more. check at start if the document database already exist. if not, copy a new one, if it exists, use it as your master. 99% of my apps use this method, till now, 0 issues.

never had a problem with any of this solutions.

I didn’t understood your logic about saving your scores, but should be pretty easy.

check local document database if exists
if not copy master to it.
if yes just open it to use it. (you never use your master database you only use your copied, and you only copy once the database, when first time the app is running)
update database as you wish.
closing app, close database.

next time you run your app its the same thing.