Пользователь
0,0
рейтинг
11 декабря 2013 в 11:01

Разработка → Использование 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.
Ну и объективная критика тоже интересна :)
Александр @Sash0k_k
карма
5,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (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
    … т.е. мониторит курсор и автоматически передаёт адаптеру списка изменившиеся данные.


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

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.