Using SQLite in Android development. Tips and tricks

Hi, Habr!
I've been working on Android for a while and today I would like to tell you about the experience gained in the process of solving one task.

For me, this project was the first where it was necessary to use SQLite closely (before it was needed no more than for select <something> <from somewhere> ).

The task is as follows: scan the barcodes of products, recognize them, compare with reference books and display the result to the user.

During the decision made some interesting conclusions for themselves.

')
1) The primary key of the tables does not have to be called " _id ".

This is only necessary if you want to display the table using the standard mechanism.
ListView - CursorAdapter - LoaderManager - ContentProvider (see note here )
In principle, the trivial statement described in the documentation, however somehow (personally, in any case, I got the impression that the primary key field in the tables must be called _id) . I have always done this before, without going into details, to avoid it.

Another key name may be necessary if you need to import a previously developed table structure into SQLite.
In my case, the reference tables already have their own fields [Something_ID] , which are used to join these tables. And it is logical to make these fields primary keys, since they will be automatically indexed.

2) Solving the problem of automatically creating a database structure and filling it with initial data.

At first, when I first started the application, I thought of simply receiving data from a remote server and doing insert reference tables. This is a bad option, since there is a lot of data (a little more than 2Mb).

A little better is to do bulkInsert , i.e. insert data within a single transaction. It works faster, but not fundamentally different from the original version. On Habré on this topic there is already a good article .

The implementation variant bulkInsert in the provider:

@Override public int bulkInsert(Uri uri, ContentValues[] values) { int numInserted = 0; final String table = selectTable(uri); database = databaseHandler.getWritableDatabase(); database.beginTransaction(); try { for (ContentValues cv : values) { if (database.insert(table, null, cv) <= 0) { throw new SQLException("Failed to insert row into " + uri); } } database.setTransactionSuccessful(); numInserted = values.length; } finally { database.endTransaction(); getContext().getContentResolver().notifyChange(uri, null); } return numInserted; } 

And I would like the pre-filled data tables to be ready for the user to start working with the application. And there was an alternative option - the library android-SQLite-asset-helper

The essence is this: the database is not created on the device at the time of work, but in the process of developing an application, it is compressed, ziped and put into assets . Further in the project, the database helper is inherited not from the standard SQLiteOpenHelper , but from SQLiteAssetHelper . And everything, when the user first accesses the database is copied into the application, the implementation details are encapsulated in the helper (and I was even too lazy to go into them).

I really liked the approach:

• Speed. It takes me less than a second to copy 2MB of the filled database with a dozen tables, which happens exactly 1 time in the entire lifetime of the application.
  There will also be no additional data conversions from one format to another (I would begin to solve a similar problem before, putting assets, say, a JSON file, and reading it into the database when it is first launched).
• Simplify the development of the database structure. There is no tedious need to write table creation scripts in the OnCreate helper +; you can use additional applications for managing SQLite. For ubunt I liked SQLitestudio , simple and clear. (Although not a buggy, to be honest - in the current version 2.1.4 it cannot create a trigger for the presentation, but where it failed to do it, it completed the standard console sqlite3 ).

3) Features of the interaction of views (view) SQLite android LoaderManager.
I will not dwell on the issues of what LoaderManager is and how to use it, I personally was helped by a wonderful series of articles . I can only say that I wanted to use exactly LoaderManager to assign him the task of automatically updating the changed data in the list.

However, it is necessary to insert data into the table, and to display it from the associated view, where instead of id-fields the values ​​are substituted:

  CREATE TABLE [table_scan] ( [_id] INTEGER PRIMARY KEY AUTOINCREMENT, [NR_ID] INTEGER NOT NULL, [T_ID] INTEGER NOT NULL, [Color_ID] INTEGER NOT NULL, [R_ID] INTEGER NOT NULL, [Barcode] TEXT NOT NULL, [NumberSeat] INTEGER, [Date] DATETIME NOT NULL DEFAULT(DATETIME('now', 'localtime')), [Deleted] INTEGER NOT NULL DEFAULT '0', [Status] INTEGER NOT NULL DEFAULT '0', [Export] INTEGER NOT NULL DEFAULT '0'); CREATE VIEW [view_scan] AS SELECT _id, Barcode, Status, Deleted, NumberSeat, goods_catalog.T_Articul, colors_catalog.Color_Name, sizes_catalog.R_Name FROM table_scan INNER JOIN goods_catalog ON goods_catalog.T_ID = table_scan.T_ID INNER JOIN colors_catalog ON colors_catalog.Color_ID = table_scan.Color_ID INNER JOIN sizes_catalog ON sizes_catalog.R_ID = table_scan.R_ID WHERE Deleted = 0; 

In the forehead, this option, as it turned out, does not work. For the uri loader on the table and the uri on the view - two different uri :)
Those. if you initialize view_scan in it, instead of table_scan , then there will not be a list when inserting it into the update table.
With the table, everything is perfectly updated, but at the output, instead of beautiful values, their ID keys are incomprehensible to people.

The first option found in the SQLite documentation was a suitable solution. You cannot insert data directly into a view (which is expected), but you can create a trigger that automatically inserts it into the desired table.

Ok, I complement the view with missing id fields

 CREATE VIEW [view_scan] AS SELECT table_scan._id, table_scan.NR_ID, table_scan.T_ID,table_scan.Color_ID, table_scan.R_ID, table_scan.Barcode, table_scan.NumberSeat, table_scan.Deleted, table_scan.Status, goods_catalog.T_Articul, colors_catalog.Color_Name, sizes_catalog.R_Name FROM table_scan INNER JOIN goods_catalog ON goods_catalog.T_ID = table_scan.T_ID INNER JOIN colors_catalog ON colors_catalog.Color_ID = table_scan.Color_ID INNER JOIN sizes_catalog ON sizes_catalog.R_ID = table_scan.R_ID WHERE Deleted = 0; 

and write the insert trigger:

 CREATE TRIGGER insert_view_scan instead of insert on view_scan begin insert into table_scan(NR_ID,T_ID,Color_ID,R_ID,Barcode,NumberSeat,Status) values(new.NR_ID, new.T_ID, new.Color_ID, new.R_ID, new.Barcode, new.NumberSeat, new.Status); end; 

Now everything works. In LoaderManager, during initialization, the uri views are given, the insert request also goes to the view, and the rest of the work is done by SQLite. The loader does what it should, i.e. monitor the cursor and automatically transfer the changed data to the list adapter.

That's all. It will be interesting to read something else about advanced techniques of working with SQLite on Android.
Well, objective criticism is also interesting :)