Android Question SQL Question - connect local db to main db

[trepdas]

Hello there good people.



I am working on my final studies project and I was wondering if any of you can throw me some leads to help me learn while I make the progress.

here are my needs :

every app creates a unique id of itself when installing.
(already done).

each app has it's own local db (very simple table, 50 items, two rows, two strings)
(already done)

all apps can connect to the main web hosted sql server (linux)
(almost done)

This main database will contain all app's local databases in one table.(app id , string1, string2)

app users can read the complete main database but able to modify or delete only the data that matches in their own local db.

how this is done?

(I assume that app id will be the key)

in other words,
every time a user is modifying his own listing (in the local db) , the listing in the main db has to be synced.

your help, thoughts, ideas, code samples will be very and extremely appreciated.

 

[drgottjr]

in theory, relatively simple:

user updates local database
user updates main database with a constraint, eg, WHERE ... AND app_id = 'this_unique_app_id'. (assuming you can guarantee a unique app_id).

in practice, you have no guarantee user will be online when local db is updated, so you have to be prepared to copy local updates to a local file (as sql statements). the next time user is online, she updates the main db from the local file BEFORE any new updates. (or keeps adding to the local file until she gets online.)

and, for the conspiracy-minded, you have no guarantee that some ill-tempered state actor doesn't send an app_id belonging to someone else and wipes out
that user's db or worse. having multiple copies of data is always risky. if user gets a new device or resets device or reinstalls your app, how does she recover her data?

 

[aeric]

I think you need extra security. An App Id is not sufficient.
I recommend to use user id and password pair (and salt) in main database. Optionally use a token pair with your App id at the client app.

 

[OliverA]

I think you need extra security. An App Id is not sufficient.
I recommend to use user id and password pair (and salt) in main database. Optionally use a token pair with your App id at the client app.

and, for the conspiracy-minded, you have no guarantee that some ill-tempered state actor doesn't send an app_id belonging to someone else and wipes out
that user's db or worse. having multiple copies of data is always risky. if user gets a new device or resets device or reinstalls your app, how does she recover her data?

I think these are valid points for production projects, but I think the author here is just looking for a "simple" solution to the given problem. It's great to point out though that such simple solution would be really bad out in the wild...

 

[trepdas]

I think these are valid points for production projects, but I think the author here is just looking for a "simple" solution to the given problem. It's great to point out though that such simple solution would be really bad out in the wild...

Yes, this is exactly it.

Thank you so far all of you good people for helping examine and refreshing my mind

 

[trepdas]

I think you need extra security. An App Id is not sufficient.
I recommend to use user id and password pair (and salt) in main database. Optionally use a token pair with your App id at the client app.

Yes, the App id is completely unique per each app.
but ,You're right, I can't relay on a single app id only.
I will adopt the user id as well...
Thank you

 

[trepdas]

in theory, relatively simple:

user updates local database
user updates main database with a constraint, eg, WHERE ... AND app_id = 'this_unique_app_id'. (assuming you can guarantee a unique app_id).

in practice, you have no guarantee user will be online when local db is updated, so you have to be prepared to copy local updates to a local file (as sql statements). the next time user is online, she updates the main db from the local file BEFORE any new updates. (or keeps adding to the local file until she gets online.)

and, for the conspiracy-minded, you have no guarantee that some ill-tempered state actor doesn't send an app_id belonging to someone else and wipes out
that user's db or worse. having multiple copies of data is always risky. if user gets a new device or resets device or reinstalls your app, how does she recover her data?

Thank you so much for this valuable info.
I will work on backing up and restoring later on. I am still building the main shield...