Insert into multiple tables with ContentProvider

Question

I implement a PRIVATE ContentProvider that has several tables with relationships (from one to many, from many to many). In my current implementation, all tables are accessible by URIs. how can I simplify the interface, so the internal end-to-end tables will not need to access the URI?

for example, I have a POSTS table, each POST has many TAGS through the TAGGINGS table. I want to interact only with POSTS URIs and execute 'private' inside the ContentProvider .

for query it just return the cursor with the joined tables, but how to do it with insert ? bulkInsert , what should I look at?

Answer 1

This is a limitation of ContentProvider . If you do not expose your data to other applications, you can use your own adaptation of the database adapter using methods and queries that directly violate your requirements.

bulkInsert() will not help in this situation, since it inserts rows into only one table at a time. But look at the method ContentProvider.applyBatch () and ContentProviderOperation , ContentProviderOperation.Builder (you may need withValueBackReference() to insert one-to-many).

These links should help you understand how to use them:

http://www.grokkingandroid.com/better-performance-with-contentprovideroperation/ http://www.grokkingandroid.com/androids-contentprovideroperation-withbackreference-explained/ What is semantics withValueBackReference?

But note, using ContentProviderOperation much slower than bulkInsert() if you insert multiple rows at once, because each time the operation is executed, it parses Uri (string comparisons). Thus, you still have to set the Uri to be inserted into the child table.

If you decide to use applyBatch() , applyBatch() it in your provider so that it performs all operations in a single transaction, so you maintain data consistency and speed up database operations:

 @Override public ContentProviderResult[] applyBatch(ArrayList<ContentProviderOperation> operations) throws OperationApplicationException { final SQLiteDatabase db = mOpenHelper.getWritableDatabase(); db.beginTransaction(); try { ContentProviderResult[] results = super.applyBatch(operations); db.setTransactionSuccessful(); return results; } finally { db.endTransaction(); } } 

Answer 2

You can insert to multiply tables as long as required values ​​are required.

For instance:

 ContentValues v = new ContentValues(); v.put("title","post1"); v.put("tag","tag1"); getProvider().insert(POST_URI,v); 

In the implementation of insert you can check if fields ( tag ) exist in another table. If this is the case, it means that you have to do additional work - first insert the tag, if it does not exist, establish the correct connection between the tag and the message just inserted.

You can check the source code of android contacts for reference.

UPDATE:

To insert multiply tags, one hack-y way is to insert a comma-separated string. It is not elegant, but it works.

Answer 3

Just to get it right: do you want to have one URI and insert a message and all its tags with one call to the ContentProvider insert? Right?

The problem is that you need to have all the values ​​in the ContentValues ​​object. There is a reason for normalization in the database. However, this may be feasible. For tags, this should be easy. Just use one String for all tags. For example, "android, ios, bada, wp7" and parse this line in the insert method.

You can also use a naming convention plus an integer. And while there is tag1, tag2, ... tagX, you should read these values ​​from your ContentProvider insertion method.

Nothing is elegant but will work.

In this case, bulkInsert or applyBatch does not have a place in your code. They only come into the game if you want to use several calls for your ContentProvider at once and during one transaction.

But I think the best solution would be to actually use multiple operations, as described by biegleux.

Answer 4

Since you are going to insert into multiple tables, the usual SQLiteDatabase.insert helper functions SQLiteDatabase.insert not work. But it is quite feasible in execution and a pleasant way.

You need to look at this from the endpoint of the user who will embed the ContentProvider in you, even if it is only you. Therefore, first define names or keys for all of your fields. Since you will not use SQLiteDatabase.insert , you actually do not need to call them the same as the database fields. None of the names should be duplicated. If, for example, the fields in two different tables overlap, perhaps the tag in TableA , and in TableB you can define the name for this field as TableA.tag and TableB.tag . Or use semantic naming for more descriptive names that do not collide.

Then you need to create your insert queries using SQLiteStatement for this answer . Make sure that the names you use in createInsert are the same as the calling ContentProvider elements that are used as keys in ContentValues.