ContentProvider Sqlite and many users. Should I create individual db`s for users or how to control that

Question

As the title say. I have search for a solution but I thing i'm on the wrong path with this. I feels like the easiest way would be to setup a new db when the app recognizes user change. Max 2-3 users. The flow of the ContentProvider will be the same for all users. Well yes I could tag all insert's with some user-id but that sounds to easy :) I can drop the ContentProvider if`necessary to make this work. Any idea about a good approach to this?

After some research I see that ContentProvider is loaded before the Application so it's difficult to change the db name. Also SQLiteOpenHelper onUpgrade() need to upgrade many database files in this case. Is the Manifest registered ContentProvider db name written in stone?

Here's part of the Table layout, I have more, like 10 tables

public final class TableUser {

    public TableUser() {
    }

    public static final class User implements BaseColumns {
        private User() {
        }
        public static final Uri CONTENT_URI = Uri.parse("content://" + UserContentProvider.AUTHORITY + "/user");

        public static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.doktor.user";

        public static final String USER_ID = "_id";

        public static final String UUID = "uuid";

        public static final String USERNAME = "username";

        public static final String USERPASSWORD = "userpassword";

        public static final String REGISTRATIONID = "registrationid";

        public static final String LAST_LOGGED_IN = "lastloggedin";     

    }

    public static final class FriendRequests implements BaseColumns {

        private FriendRequests() {
        }
        public static final Uri CONTENT_URI = Uri.parse("content://" + UserContentProvider.AUTHORITY + "/friendRequests");

        public static final String CONTENT_TYPE = "vnd.android.cursor.dir/vnd.doktor.friendRequests";

        public static final String FRIENDREQUESTS_ID = "_id";

        public static final String NAME = "name";

        public static final String UUID = "uuid";

        public static final String MESSAGE = "message"; 

        public static final String NOTIFICATION_ID = "notification_id";     

        public static final String DEVICE_TYPE = "device_type";

        public static final String ADDEDDATE = "added_date";
    }
}

Here is the table creation

db.execSQL("CREATE TABLE " +  USER_TABLE_NAME + " (" +
       User.USER_ID         + " INTEGER PRIMARY KEY AUTOINCREMENT," + 
       User.UUID            + " VARCHAR(255)," + 
       User.USERNAME        + " VARCHAR(255)," + 
       User.USERPASSWORD    + " VARCHAR(255)," + 
       User.REGISTRATIONID  + " LONGTEXT," + 
       User.LAST_LOGGED_IN  + " VARCHAR(255)" + ");");

db.execSQL("CREATE TABLE " +  FRIEND_REQUEST_TABLE_NAME + " (" + 
    FriendRequests.FRIENDREQUESTS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 
    FriendRequests.NAME              + " VARCHAR(255)," +
    FriendRequests.UUID              + " VARCHAR(255)," +
    FriendRequests.MESSAGE           + " VARCHAR(255)," +
    FriendRequests.NOTIFICATION_ID   + " VARCHAR(255)," +   
    FriendRequests.DEVICE_TYPE       + " VARCHAR(255)," +                          
    FriendRequests.ADDEDDATE         + " VARCHAR(255)" + ");");

If the database structure is exactly the same for any user(with only the actual data being different) I would use a single database with different ids passed as user identifiers. I would use the `ContentProvider`and insert the user identifier in the `Uri(s)`. This way you will have the same code for database actions and you'll only need to extract the identifier from the `Uri`(to append to the actual sqlite query). I'm also assuming the provider will be private to your app(and not exported for use by other apps?). — user, Oct 24 '13 at 08:41
Provider is private yes, if I extract the identifier and append to the query, It sound like I must create a new column in every table representing the user? I updated my Question with Tables layout — Erik, Oct 24 '13 at 13:04
Yes, so you can link that entry to the target use. From the looks of your `User` table it seems that the `USER_ID` column is the one to use across other tables to link to the user identity. — user, Oct 24 '13 at 13:47
Added table creation to my question. For some peculiar reason I have `PRIMARY KEY AUTOINCREMENT` set on that `User.USER_ID`hmm. Remember now I created the `_id` in eash tables for cases like this and forgot it — Erik, Oct 24 '13 at 14:08
That doesn't matter, the id needs to be unique. In the other columns you'll probably need to add another column(like `user_identif` for example) where you'll only insert values present in `User.USER_ID`(so the `FriendRequests.FRIENDREQUESTS_ID` will be the id of the entry in that table disregarding user id). When you'll make the queries you'll need to add an extra selection to the query to filter the `user_identif` column for the proper values : `AND user_identif=user_id_extracted_from_uri`. — user, Oct 24 '13 at 14:52
Grate, post this as an answer thanks. Users can login to the app using any of there gmail accounts. I think the `user_identif` should be there email address. so I have to create a `user_identif` in the `User` as well for this to work. Might as well throw in some `Foreign key constraints with on delete cascade` to learn even more fun stuff.. — Erik, Oct 24 '13 at 15:15

score 1 · Accepted Answer · answered Oct 25 '13 at 04:47

As I said in my comments, I would use a single database with all the data(from all the possible users) in each tables you have, with another extra column representing the actual user identifier(present in whatever table you need user identification). You'll then use this column to filter the normal database queries(insert, query etc) to get/modify only the data for that particular user. That identifier would have to be passed through the Uri if you use a ContentProvider, and then appended to the normal selection clause with AND user_identif=user_id_extracted_from_uri.

And you could use the email address as the identifier, which would be more reliable across devices instead of some generated user ids.

ContentProvider Sqlite and many users. Should I create individual db`s for users or how to control that

1 Answers1