Using SQLite in Android development. Tips and tricks

Hello, Habr!
I have been developing for Android for some time and today I would like to talk about the experience gained in solving one problem.

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

The task is as follows: scan barcodes of goods, recognize them, check against directories and display the result to the user.

In the course of the decision, he made several interesting conclusions.

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
ListView mechanism - CursorAdapter - LoaderManager - ContentProvider (see note here )
In principle, the trivial statement described in the documentation, but somehow (personally, I, anyway) happened the idea that the primary key field in the tables must be called _id . I always did this before, without going into details, in order to avoid.

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

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

At first, when starting the application for the first time, I thought of simply receiving data from a remote server and doing insert of the 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 in a single transaction. It works faster, but fundamentally does not differ from the original version. On a habr on this subject already there is a good article .

Variant of implementation of bulkInsert in 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 prefilled tables with data to be ready for the user to start working with the application. And there was an alternative option - the android-SQLite-asset-helper library.

The bottom line is this: the database is not created on the device at the time of work, but during application development, it is compressed, zipped and put into assets . Further in the project, the database helper is inherited not from the standard SQLiteOpenHelper , but from SQLiteAssetHelper . And that’s all, at the first user’s call, the database is copied to the application, implementation details are encapsulated in the helper (and I was too lazy to go into them).

I really liked the approach for its advantages:

• Speed. It takes me less than a second to copy a 2MB filled database with a dozen tables, which happens exactly 1 time for the entire lifetime of the application.
  There is also no additional conversion of data from one format to another (before, I would solve a similar problem by putting, say, a JSON file in assets, and reading it into the database the first time I start it).
• Simplification of the development of the database structure. There is no tedious need to write table creation scripts in the OnCreate helper + you can use additional SQLite management applications. For ubuntu, I liked SQLitestudio , simple and straightforward. (Although it’s not a mistake, to be honest - in the current version 2.1.4 it can’t create a trigger for presentation, but where it failed, I completed it using the standard console sqlite3 ).

3) Features of the interaction of SQLite views with the Android LoaderManager. I will not dwell
on questions about what LoaderManager is and how to use it; I personally have been helped by a wonderful series of articles . I can only say that I wanted to use the LoaderManager in order to entrust him with the task of automatically updating the changed data in the list.

However, you need to insert the data into the table, and display it from the view associated with it, where values ​​are substituted for id fields:

 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;

On the forehead, this option, as it turned out, does not work. For the uri loader on the table and uri on the view - two different uri :)
That is if you initialize view_scan in it , instead of table_scan , then when you insert into the table the list will not be updated.
Everything is perfectly updated with the table, but instead of beautiful values, the output is their ID keys, incomprehensible to people.

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

Ok, complement the view with the 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 the LoaderManager, initialization is given the view uri, the insertion request also goes to the view, and SQLite does the rest of the work. In this case, the loader does what it should, i.e. monitors the cursor and automatically transfers the changed data to the list adapter.

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