Использование SQLite в Android-разработке. Tips and tricks

Привет, Хабр!
Некоторое время занимаюсь разработкой для Android и сегодня хотел бы рассказать об опыте, полученном в процессе решения одной задачи.

Для меня этот проект стал первым, где надо было вплотную использовать SQLite (раньше он был нужен не более, чем для select <что-нибудь> <откуда-то>).

Задача такова: сканировать штрих-коды товаров, распознавать их, сверять со справочниками и выводить результат пользователю.

В ходе решения сделал несколько интересных для себя выводов.

1) Первичный ключ таблиц не обязательно должен называться "_id".

Это нужно, только если вы хотите отображать таблицу, используя стандартный механизм
ListView — CursorAdapter — LoaderManager — ContentProvider (см. примечание здесь)
В принципе, тривиальное утверждение, описанное в документации, однако как-то (лично у меня, во всяком случае) сложилось представление, что поле первичного ключа в таблицах обязательно должно называться _id. Раньше всегда так делал, не вдаваясь в подробности, во избежание.

Другое название ключа может быть необходимым, если надо импортировать в SQLite уже разработанную ранее структуру таблиц.
В моём случае — у таблиц справочников есть уже свои поля [Something_ID], по которым выполняется присоединение этих таблиц. И логично эти поля сделать первичными ключами, поскольку они будут автоматически проиндексированы.

2) Решение задачи автоматического создания структуры БД и заполнения её начальными данными.

Сначала, при первом запуске приложения думал просто получать данные от удалённого сервера и делать insert таблиц справочников. Это плохой вариант, поскольку данных много (чуть больше 2Mb).

Чуть лучше — делать bulkInsert, т.е. вставлять данные в рамках одной транзакции. Работает быстрей, но принципиально не отличается от первоначального варианта. На хабре на эту тему уже есть хорошая статья.

Вариант реализации bulkInsert в провайдере:

    @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;
    }

А хотелось бы, чтобы предзаполненные таблицы с данными уже были готовы к началу работы пользователя с приложением. И нашёлся альтернативный вариант — библиотека android-SQLite-asset-helper

Суть такова: БД создаётся не на устройстве в момент работы, а в процессе разработки приложения, сжимается, зипуется и кладётся в assets. Далее в проекте хелпер работы с БД наследуется не от стандартного SQLiteOpenHelper, а от SQLiteAssetHelper. И всё, при первом обращении пользователя база копируется в приложение, подробности реализации инкапсулированы в хелпере (и мне даже лень было в них вдаваться).

Подход очень понравился своими преимуществами:

• Скорость. У меня на копирование 2Мб заполненной базы с десятком таблиц уходит меньше секунды, что происходит ровно 1 раз за всё время жизни приложения.
  Также отсутсуют дополнительные конвертации данных из одного формата в другой (раньше подобную задачу я стал бы решать, положив в assets, скажем, JSON-файл, и начитывая его в базу при первом запуске).
• Упрощение разработки структуры БД. Отпадает утомительная необходимость писать скрипты создания таблиц в OnCreate хелпера + можно использовать дополнительные приложения для управления SQLite. Для убунту мне понравилась SQLitestudio, простая и понятная. (Хотя и небезглючная, честно сказать — в текущей версии 2.1.4 не может создать триггер для представления, но где не справилась она, доделал через стандартный консольный sqlite3).

3) Особенности взаимодействия представлений (view) SQLite с андроидным LoaderManager.
На вопросах что такое LoaderManager и как им пользоваться, подробно останавливаться не стану, лично мне помог прекрасный цикл статей. Скажу только, что хотел использовать именно LoaderManager, чтобы возложить на него задачу автоматического обновления изменившихся данных в списке.

Однако, вставлять данные надо в таблицу, а отображать — из связанной с ней вьюхи, где вместо id-полей подставлены значения:

 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;

В лоб такой вариант, как оказалось, не работает. Для лоадера uri на таблицу и uri на вьюху — два разных uri :)
Т.е. если проинициализировать в нём view_scan, вместо table_scan, то при вставке в таблицу обновления списка не будет.
С таблицей же всё отлично обновляется, но на выходе вместо красивых значений — их ID-ключи, непонятные людям.

Подходящим решением оказался первый же вариант, найденный в документации SQLite. Во вьюху нельзя вставлять данные напрямую (что ожидаемо), но можно создать триггер, который автоматически вставляет их в нужную таблицу.

Ок, дополняю вьюху недостающими id-полями

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;

и пишу триггер вставки:

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;

Теперь всё работает. В LoaderManager при инициализации отдаётся uri вьюхи, запрос на вставку тоже идёт к вьюхе, а всю остальную работу делает SQLite. Лоадер при этом делает то, что должен, т.е. мониторит курсор и автоматически передаёт адаптеру списка изменившиеся данные.

На этом всё. Будет интересно почитать ещё что-нибудь про продвинутые техники работы со SQLite на Android.
Ну и объективная критика тоже интересна :)