Использование 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.
Ну и объективная критика тоже интересна :)
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 21

    0
    Можете дать ссылку на приложение? Хотя бы для теста? Возникают ещё вопросы: 2Мб базы — это все данные в приложении или только необходимая часть? Приложение клиент-серверное или без сервера?
      0
      приложение пишется для внутреннего использования на предприятии, поэтому, к сожалению, не могу.

      2 метра — это данные справочников, которые инициализируются в локальную БД при первом запуске.
      Есть ещё удалённый сервер MS SQL, с которым работаю через jtds, но это уже не относится непосредственно к теме статьи.
      0
      А хотелось бы, чтобы предзаполненные таблицы с данными уже были готовы к началу работы пользователя с приложением. И нашёлся альтернативный вариант — библиотека android-SQLite-asset-helper

      А вы пробовали потом заменить эту базу новой при использовании этой библиотеки?
      Т.е. выпускаете новую версию, кладете новую базу в apk, у пользователя, у которого уже было ваше приложение, новая база с новой версии перезапишет старую, уже скопированную базу?
        0
        Не пробовал. Вообще в описании библиотеки фича заявлена, скорей всего — столкнусь с этим в дальнейшем :)
          0
          Попробуйте. По-моему, issue на эту тему уже полгода висит.
        0
        Иногда так напрягает возиться со схемами в мелких приложениях… а не практиковали использование какого-нибудь простенького schemaless хранилища?
          0
          В самых простых случаях — да, не гнушаюсь хранить в Prefence набор каких-нибудь JSONObject.toString() и парсить их оттуда по необходимости.

          Конкретно тут — у таблиц есть структура, которая сохранена для совместимости с удалённым sql-сервером. В частности, справочники ведь необходимо будет обновлять.
          0
          Использовал ormlite в своем приложение на android, очень удобная ORM библиотека для SQLite.
            0
            Ваша база данных больше одного мегабайта. Вы копируете её из assets при первом запуске, если Вы планируете запускать Ваше приложение на андроид меньше или равно 2.2, то в assets нужно разбить файл на части, не превышающие 1 мегабайт.
              +1
              Это касается только «несжатых» типов. Дописываете до имени базы расширение .jpeg и все нормально копируется.
                0
                Спасибо, теперь я тоже это знаю!
                  0
                  использовал .mp3 в своё время )
                  0
                  Спасибо за дополнение.
                  Об этой проблеме у старых версий aapt мне известно, и разрабам android-sqlite-asset-helper — тоже. Поэтому, кстати, у них предполагается, что база должна быть упакована в zip.
                  А у меня проект API 14+
                  0
                  Хотел бы задать вопрос комментирующим:
                  А часто ли вы используете ContentProvider для работы с данными, при условии что эти самые данные не нужно перекидывать между приложениями?
                  Мне показался вариант использовать ContentProvider для целей хранения внутренних данных очень громоздким.
                    0
                    имхо, если данные необходимо отображать в списке, то ContentProvider без вариантов (чтобы пользоваться лоадером). А если база нужна как промежуточное/внутренне хранилище, можно работать с ней напрямую.
                    Например, приходилось писать велосипед кэшер картинок с небольшими постобработками, который сохранял некоторые данные в базе. В подобных случаях провайдер — лишнее звено.
                      0
                      А что если между этими вариантами? Она нужна как и внутреннее хранилище так и для отображения данных в списке.

                      Думаю стоит пояснить что я имею ввиду под громоздкостью:
                      мало того, что есть аспект общей громоздкости (нужно написать много boilerplate'а), который, в принципе, можно обойти, так есть еще аспект очень неудобной работы со всякими хитрыми JOIN'ами.
                        0
                        много boilerplate'а
                        Много, но ведь и пишется 1 раз, а потом таскается из проекта в проект. По сути оно зло конечно, остаётся только смириться и третий раз апеллировать к лоадерам.

                        аспект очень неудобной работы со всякими хитрыми JOIN'ами
                        вот ими пусть sqlite и занимается. Думаю логично, если интерфейс к базе максимально прост, а все сложные запросы написаны вьюхами.

                        Поначалу не очень понимал, зачем ContentProvider-ы нужны, и старался их не использовать. Привык...)

                        И кстати, было бы интересно посмотреть исследования, сколько оверхеда даёт провайдер по сравнению с прямыми запросами к базе (полгода назад не смог найти такой информации)
                      0
                      Насколько я понимаю, если есть необходимость использовать курсор в адаптере, например для ListView то без ContentProvider теперь (то есть начиная с API15) не обойтись. Курсор теперь назначается адаптеру списка через реализацию интерфейса лоадера. Старый способ теперь depricated. В свою очередь лоадеру нужен URI, что обязывает создавать ContentProvider.
                        +1
                        Необязательно. Есть SQLiteCursorLoader сделанный многоуважаемым господином CommonsGuy который работает напрямую с SQLite курсорами.
                          0
                          Благодарю, не знал.
                      0
                      … т.е. мониторит курсор и автоматически передаёт адаптеру списка изменившиеся данные.


                      Вы уверены что передает только изменившиеся? Было бы интересно взглянуть на код вашего лоадера

                      Only users with full accounts can post comments. Log in, please.