Использование 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.
Ну и объективная критика тоже интересна 🙂