что такое каскадное удаление
Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Часть 11.6: Каскадное удаления данных в базах данных SQLite
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Прошлая публикация была про каскадную модификацию, в этой мы с вами поговорим про каскадное удаление данных из базы данных SQLite3. Каскадное удаление данных это способ удалить данные из связанных таблиц и при этом не нарушить ограничение внешнего ключа, кроме того, каскадное удаление данных упрощает нам работу при удалении строк в связанных таблицах.
Каскадное удаления данных в базах данных SQLite
Из этой записи вы узнаете для чего нужно каскадное удаление данных в реляционных базах данных и, как каскадное удаление облегчает жизнь разработчику. И увидите пример работы каскадного удаления данных из базы данных под управлением SQLite в таблицах, связанных связью многие ко многим, в общем, увидите в действие конструкцию ON DELETE CASCADE.
Каскадное удаление данных из базы данных SQLite
Мы уже рассмотрели всевозможные способы обеспечения целостности данных в базах данных под управлением SQLite. Успели разобраться с ограничениями уровня столбца и ограничениями уровня таблицы и чуть ранее рассмотрели каскадное обновление данных. Теперь перейдем к каскадному удалению данных в базах данных SQLite3.
При реализации связей между таблицами при помощи внешнего ключа или FOREIGN KEY мы сталкиваемся с такой проблемой: для удаления данных из таблиц базы данных нам необходимо выполнить две команды DELETE: сперва нужно удалить данные из таблицы справочника, а затем удалить строку из таблицы, которая ссылается на справочник. В противном случае SQLite скажет, что было нарушено ограничение внешнего ключа и не даст удалить данные.
Чтобы избежать такой проблемы и не писать лишние SQL запросы в SQLite было реализовано каскадное удаление данных из таблиц, при каскадном удаление данных мы не пишем лишний SQL запрос DELETE, а также гарантируем себе то, что целостность данных в базах данных не будет нарушена.
Операция каскадного удаления данных реализуется при помощи ключевой фразы ON DELETE CASCADE. Фраза ON DELETE CASCADE говорит SQLite о том, что сперва нужно удалить данные из справочника или домена данных, а затем данные нужно удалять из таблицы, которая ссылается на этот справочник.
Чтобы реализовать каскадное удаление данных из таблицы базы данных, нужно записать правило ON DELETE сразу после FOREIGN KEY, тогда SQLite начнет «понимать», что при удалении данных из справочника, ей нужно удалять и данные из связанных таблиц.
Давайте реализуем пример связи многие ко многим и посмотрим, как происходит каскадное удаление данных из базы данных SQLite3 и поймем, как работает конструкция ON DELETE CASCADE.
Примеры каскадного удаления данных из базе данных SQLite. ON DELETE в SQLite3
Чтобы посмотреть, как удалить данные каскадом, давайте создадим три таблицы в базе данных, которые будут связаны связью многие ко многим, воспользуемся командой CREATE:
Сказ о том, как каскадное удаление в Realm долгий запуск победило
Все пользователи считают быстрый запуск и отзывчивый UI в мобильных приложениях само собой разумеющимся. Если приложение запускается долго, пользователь начинает грустить и злиться. Запросто можно подпортить клиентский опыт или вовсе потерять пользователя ещё до того, как он начал пользоваться приложением.
Однажды мы обнаружили, что приложение Додо Пицца запускается в среднем 3 секунды, а у некоторых «счастливчиков» 15-20 секунд.
Под катом история с хеппи эндом: про рост базы данных Realm, утечку памяти, то, как мы копили вложенные объекты, а после взяли себя в руки и всё починили.
Автор статьи: Максим Качинкин — Android-разработчик в Додо Пицце.
Три секунды от клика на иконку приложения до onResume() первого активити — бесконечность. А у некоторых пользователей время запуска доходило до 15-20 секунд. Как такое вообще возможно?
Поиск и анализ проблемы
Сегодня любое мобильное приложение должно запускаться быстро и быть отзывчивым. Но дело не только в мобильном приложении. Пользовательский опыт взаимодействия с сервисом и компанией — это комплексная вещь. Например, в нашем случае скорость доставки — один из ключевых показателей для сервиса пиццы. Если доставка быстрая, то пицца будет горячей, и клиент, который хочет есть сейчас, не будет долго ждать. Для приложения, в свою очередь, важно создавать ощущение быстрого сервиса, ведь если приложение только 20 секунд запускается, то сколько придётся ждать пиццу?
Сначала мы сами сталкивались с тем, что иногда приложение запускается пару-тройку секунд, а потом до нас стали долетать жалобы других коллег о том, что «долго». Но стабильно повторить эту ситуацию нам не удавалось.
Долго — это сколько? Согласно Google-документации, если холодный старт приложения занимает менее 5 секунд, то это считается «как бы нормально». Android-приложение Додо Пиццы запускалось (согласно Firebase метрике _app_start) при холодном старте в среднем за 3 секунды — «Not great, not terrible», как говорится.
Но потом стали появляться жалобы, что приложение запускается очень-очень-очень долго! Для начала мы решили измерить, что же такое «очень-очень-очень долго». И воспользовались для этого Firebase trace App start trace.
Этот стандартный трейс измеряет время между моментом, когда пользователь открывает приложение, и моментом, когда выполнится onResume() первого активити. В Firebase Console эта метрика называется _app_start. Выяснилось что:
На ум пришло две мысли:
Что не так с базой данных Realm
Мы стали проверять, как меняется содержимое базы со временем жизни приложения, от первой установки и далее в процессе активного использования. Посмотреть содержимое базы данных Realm можно через Stetho или более подробно и наглядно, открыв файл через Realm Studio. Чтобы посмотреть содержимое базы через ADB, копируем файл базы Realm:
Посмотрев на содержимое базы в разное время, мы выяснили, что количество объектов определённого типа постоянно увеличивается.
На картинке показан фрагмент Realm Studio для двух файлов: слева — база приложения спустя некоторое время после установки, справа — после активного использования. Видно, что количество объектов ImageEntity и MoneyType сильно выросло (на скриншоте показано количество объектов каждого типа).
Связь роста базы данных с временем запуска
Неконтролируемый рост базы данных — это очень плохо. Но как это влияет на время запуска приложения? Померить это достаточно просто через ActivityManager. Начиная с Android 4.4, logcat отображает лог со строкой Displayed и временем. Это время равно промежутку с момента запуска приложения до конца отрисовки активити. За это время происходят события:
При этом был такой же характер зависимости размера и роста базы, которая выросла с 4 МБ до 15 МБ. Итого получается, что со временем (с ростом холодных запусков) росло и время запуска приложения и размер базы. У нас на руках появилась гипотеза. Теперь оставалось подтвердить зависимость. Поэтому мы решили убрать «утечки» и проверить, ускорит ли это запуск.
Причины бесконечного роста базы данных
Прежде чем убирать «утечки», стоит разобраться, почему они вообще появились. Для этого вспомним, что такое Realm.
Realm — это нереляционная база данных. Она позволяет описывать связи между объектами похожим способом, которым описывают многие ORM реляционные базы данных на Android. При этом Realm сохраняет напрямую объекты в памяти с наименьшим количеством преобразований и маппингов. Это позволяет читать данные с диска очень быстро, что является сильной стороной Realm, за которую его любят.
(В рамках данной статьи этого описания нам будет достаточно. Более подробно о Realm можно прочитать в крутой документации или в их академии).
Многие разработчики привыкли работать в большей степени с реляционными базами данных (например, ORM-базами c SQL под капотом). И такие вещи как каскадное удаление данных часто кажутся само собой разумеющимся делом. Но не в Realm.
Кстати говоря, фичу каскадного удаления просят сделать уже давно. Эту доработку и другую, связанную с ней, активно обсуждали. Было ощущение, что она скоро будет сделана. Но потом всё перевелось в введение сильных и слабых ссылок, которые тоже автоматом решили бы эту проблему. По этой задаче был довольно живой и активный пул-реквест, который пока из-за внутренних сложностей поставили на паузу.
Утечка данных без каскадного удаления
Как именно утекают данные, если надеяться на несуществующее каскадное удаление? Если у вас есть вложенные Realm-объекты, то их нужно обязательно удалять.
Рассмотрим (почти) реальный пример. У нас есть объект CartItemEntity :
Так как связь с ними потеряна, мы их больше не читаем и не удаляем (только если не обращаться к ним явно или не чистить всю «таблицу»). Мы это назвали «утечками памяти».
Когда мы работаем с Realm, то должны явно проходить по всем элементам и явно все удалять перед такими операциями. Это можно сделать, например, вот так:
Если сделать так, то всё будет работать как надо. В данном примере мы предполагаем, что внутри image, customizationEntity и cartComboProducts нет других вложенных Realm-объектов, поэтому нет других вложенных циклов и удалений.
Решение «по-быстрому»
Первым делом мы решили подчистить самые быстрорастущие объекты и проверить результаты — решит ли это нашу изначальную проблему. Сначала было сделано наиболее простое и интуитивно-понятное решение, а именно: каждый объект должен быть ответственным за удаление за собой своих детей. Для этого ввели такой интерфейс, который возвращал список своих вложенных Realm-объектов:
И реализовали его в наших Realm-объектах:
В getNestedEntities мы возвращаем всех детей плоским списком. А каждый дочерний объект также может реализовывать интерфейс NestedEntityAware, сообщая что у него есть внутренние Realm-объекты на удаление, например ScheduleEntity :
И так далее вложенность объектов может повторяться.
Затем пишем метод, который рекурсивно удаляет все вложенные объекты. Метод (сделанный в виде экстеншена) deleteAllNestedEntities получает все верхнеуровневые объекты и методом deleteNestedRecursively рекурсивно удаляет всё вложенное, используя интерфейс NestedEntityAware:
Мы проделали это с самыми быстрорастущими объектами и проверили, что получилось.
В результате те объекты, которые мы покрыли этим решением, перестали расти. А общий рост базы замедлился, но не остановился.
Решение «по-нормальному»
База хоть и стала расти медленнее, но все равно росла. Поэтому мы стали искать дальше. В нашем проекте очень активно используется кеширование данных в Realm. Поэтому писать для каждого объекта все вложенные объекты трудозатратно, плюс повышается риск ошибки, ведь можно забыть указать объекты при изменении кода.
Хотелось сделать так, чтобы не использовать интерфейсы, а чтобы всё работало само.
Когда мы хотим, чтобы что-то работало само, приходится использовать рефлексию. Для этого мы можем пройтись по каждому полю класса и проверить, является ли он Realm-объектом или списком объектов:
Если поле является RealmModel или RealmList, то сложим объект этого поля в список вложенных объектов. Всё точно так же, как мы делали выше, только тут оно будет делаться само. Сам метод каскадного удаления получается очень простым и выглядит так:
В итоге в нашем клиентском коде мы используем «каскадное удаление» при каждой операции изменения данных. Например, для операции вставки это выглядит вот так:
Сначала метод getManagedEntities получает все добавляемые объекты, а потом метод cascadeDelete рекурсивно удаляет все собранные объекты перед записью новых. В итоге мы используем этот подход по всему приложению. Утечки памяти в Realm полностью исчезли. Проведя тот же замер зависимости времени запуска от количества холодных запусков приложения, мы видим результат.
Зелёная линия показывает зависимость времени запуска приложения от количества холодных стартов при автоматическом каскадном удалении вложенных объектов.
Результаты и выводы
Постоянно растущая база данных Realm сильно замедляла запуск приложения. Мы выпустили обновление с собственным «каскадным удалением» вложенных объектов. И теперь отслеживаем и оцениваем, как наше решение повлияло на время запуска приложения через метрику _app_start.
Для анализа берём промежуток времени 90 дней и видим: время запуска приложения, как медианное, так и то, что приходится на 95 процентиль пользователей, начало уменьшаться и больше не поднимается.
Если посмотреть на семидневный график, то метрика _app_start полностью выглядит адекватной и составляет меньше 1 секунды.
Отдельно стоит добавить, что по умолчанию Firebase шлёт уведомления, если медианное значение _app_start превышает 5 секунд. Однако, как мы видим, на это не стоит полагаться, а лучше зайти и проверить его явно.
Особенность базы данных Realm заключается в том, что это нереляционная база данных. Несмотря на простое использование, схожесть работы с ORM-решениями и связывание объектов, у неё нет каскадного удаления.
Если это не учитывать, то вложенные объекты будут накапливаться, «утекать». База данных будет расти постоянно, что в свою очередь скажется на замедлении работы или запуске приложения.
Я поделился нашим опытом, как быстро сделать каскадное удаление объектов в Realm, которого пока нет из коробки, но о котором давно говорят и говорят. В нашем случае это сильно ускорило время запуска приложения.
Несмотря на обсуждение скорого появления этой фичи, отсутствие каскадного удаления в Realm сделано by design. Если вы проектируете новое приложение, то учитывайте это. А если уже используете Realm — проверьте, нет ли у вас таких проблем.
Каскадное удаление объектов
Задача каскадного удаления
Пусть дана следующая диаграмма:
Варианты решения проблемы
Вариантов может быть очень много, в данной статье будут приведено только несколько. Технология предоставляет механизмы для решения проблемы (в основном они опираются на использование бизнес-серверов), варианты ограничиваются лишь фантазией разработчика.
Специальные интерфейсы
Для реализации каскадного удаления можно воспользоваться специально разработанными интерфейсами IReferencesCascadeDelete и IReferencesNullDelete.
Рекурсивное удаление
Это самый простой вариант, но и самый недружелюбный к пользователю: удаление 1 объекта может привести к удалению важной информации информации, связанной с данным объектом.
Фиктивный объект
Такой вариант позволяет сохранить все данные, кроме того объекта, который необходимо удалить. Однако в базе останется множество объектов, ссылающихся на несуществующий.
Стоит также отметить, что данный способ требует дополнительной обработки данных при выводе пользователю. Объекты, ссылающиеся на фиктивные, необходимо фильтровать или обрабатывать особым образом.
Вариантов решения проблемы несколько:
Алгоритм для второго варианта:
Фиктивное удаление
После этого объект уходит на обновление в базу и остается в ней, но считается удаленным. Разумеется, необходимо реализовывать логику, которая будет “считать” такие объекты удаленными: при выводе информации пользователю необходимо накладывать ограничения на выводимые данные.
Пример
Добавить логику в бизнес-сервера объектов (на примере Адреса ):
Далее, чтобы пользователю не выводились “удаленные” данные при просмотре списка объектов, требуется на соответствующий контрол наложить ограничение вида:
MySQL – каскадное удаление строк из таблиц
В данном посте я хочу проверить механизм каскадного удаления связанных таблиц. Создам новую базу данных, 2 связанных таблицы – отделы (departments) и сотрудники (employee). Сделаю стартовое заполнение. И попробую удалить один из отделов… Все работы буду делать из консоли MySQL.exe для тренировки…
Создание БД из консоли…
Итак, БД DB_Company успешно создана на сервере mysql;
Создание таблиц в БД из консоли…
Также создаём таблицу сотрудников…
Как видно из рисунка – таблицы успешно созданы…
Связывание таблиц
Теперь свяжем таблицы через создание ограничения и внешнего ключа для таблицы employee… Но, для начала, нам нужно создать колонку для внешнего ключа…
Мы создали просто внешний ключ, а нам нужно создать внешний ключ с параметром каскадного удаления, поэтому удалим внешний ключ и создадим его снова с нужными параметрами…
Создаём внешний ключ с условием каскадного удаления…
Итак, проверим всё ли правильно, вызвав show create table…
Итак, ON DELETE CASCADE, установлен!
Заполнение таблиц
Теперь заполним таблицы некоторыми значениями. Пусть у нас будет 3 отдела – логистика, продажи, разработка…
И 6 сотрудников, по 2 на каждый отдел…
Теперь, если мы удалим, например 3 отдел Development из таблицы Department, то должны удалиться сотрудники Semen и Mihail…
Итак, механизм каскадного удаления в MySQL – прекрасно работает!
Оператор ON DELETE CASCADE в MySQL
Главное меню » Базы данных » MySQL » Оператор ON DELETE CASCADE в MySQL
Предполагая, что вы создали две таблицы с внешним ключом FOREIGN KEY внутри отношения внешнего ключа, отображая одну родительскую и дочернюю таблицы. После этого, предназначенный для одного FOREIGN KEY, должен быть исправлен, чтобы другой был успешным во время каскадных действий, затем мы указываем оператор ON DELETE CASCADE. Возможно, если один оператор FOREIGN KEY определяет ON DELETE CASCADE, каскадные функции вызовут исключение.
Давайте поймем, как во всей таблице MySQL мы могли бы использовать оператор ON DELETE CASCADE.
Вы должны открыть недавно установленную клиентскую оболочку командной строки в MySQL, чтобы продолжить работу. При открытии вам будет предложено ввести пароль, чтобы продолжить использование клиентской оболочки командной строки MySQL.
Далее мы создадим две таблицы с названиями «order» и «customer». Обе взаимные таблицы связаны с каскадной функцией удаления с использованием внешнего ключа. На этом этапе «order» является родительской таблицей, а дочерняя таблица – «customer». С помощью прилагаемых скриптов вместе с соответствующими записями вы должны построить обе таблицы. Используйте приведенную ниже команду «use», чтобы выбрать базу данных, в которой вы хотите работать, или создать в ней таблицы. Здесь «data» – это база данных, которую мы используем.
Создать родительскую таблицу:
Прежде всего, вы должны создать таблицу «order» вместе с ее полями с помощью команды CREATE TABLE, как показано в запросе ниже. Столбец «ID» будет использован в следующей таблице «customer» в качестве внешнего ключа.
Добавим данные в эту таблицу. Вы должны выполнить показанные ниже запросы в оболочке командной строки в MySQL и запустить каждую команду отдельно в командной строке или просто добавить все команды в командную строку за один шаг.
Теперь проверим таблицу «order» после внесения в нее значений. Для этой цели вы можете использовать команду SELECT следующим образом:
Вы можете видеть, что данные были успешно сохранены в таблице «order», как и ожидалось.
Создайте дочернюю таблицу с помощью DELETE Cascade:
Теперь настала очередь создать еще одну таблицу под названием «customer».
Во-первых, вы должны ввести ключевое слово «CREATE» вместе с именем таблицы. Затем вам нужно добавить имена полей или столбцов вместе с их типами данных. Вы должны назвать последний столбец, который будет использоваться в качестве внешнего ключа в этой таблице, так же, как вы назвали его в предыдущей таблице. Как вы знаете, столбец «ID» из таблицы «order» использовался в качестве внешнего ключа в таблице «customer» как «OrderID». После этого вы должны добавить ключевое слово «CONSTRAINT», которое используется для инициализации FOREIGN Key, вместе со ссылкой на предыдущую таблицу. Теперь вам нужно использовать оператор «DELETE CASCADE» вместе с ключевым словом «ON».
После того, как таблица была создана и DELETE CASCADE был успешно применен к этой таблице, пора вставить некоторые значения в эту таблицу. Для этого попробуйте выполнить следующие инструкции одну за другой в клиентской оболочке командной строки в MySQL.
Впоследствии сделайте вставку запросов. Это момент, чтобы проверить таблицу, были ли данные добавлены успешно или нет. Поэтому попробуйте эту команду ниже, чтобы сделать это:
Здесь вы можете увидеть вывод таблицы, в которой данные эффективно назначены ей и без каких-либо ошибок или сбоев.
Удалить записи:
Теперь, когда вы удаляете какие-либо данные или строку из родительской таблицы, она также удаляет данные или строку из дочерней таблицы из-за включенного «DELETE CASCADE» на внешнем ключе, упомянутом в дочерней таблице. Давайте сначала попробуем запрос DELETE, а затем проверим результаты. Мы будем удалять данные из таблицы «order», где «ID» равен «11». Если такой же «ID» будет найден в таблице «customer» в столбце внешнего ключа «OrderID», тогда соответствующая строка или данные в таблице «customer» также будут удалены. Для этого попробуйте следующую команду в командной строке:
Сначала проверим родительскую таблицу. Затем введите оператор SELECT, указанную ниже, чтобы получить оставшиеся записи таблицы «order» после удаления некоторых записей. Вы увидите, что запись таблицы, где «ID» был «11», была успешно удалена из этой таблицы. Это означает, что относительные записи с одинаковым значением идентификатора «11» также будут удалены из дочерней таблицы.
Получить записи дочерней таблицы с помощью команды SELECT так же просто, как и раньше. Просто попробуйте команду ниже, и вы получите результат.
Получив результаты, вы можете увидеть, что запись «CustID», имеющая значение «1», была полностью удалена. Это связано с тем, что столбец «OrderID» имеет значение «11» в своей первой строке, что приводит к удалению этой строки.
Когда вы пытаетесь удалить родительскую таблицу с помощью команды DROP, MySQL не позволит вам это сделать. Это связано с тем, что в родительской таблице включен DELETE CASCADE. Итак, чтобы удалить таблицу, вы должны сначала удалить из нее DELETE CASCADE.
Вывод:
Мы закончили с объяснением DELETE CASCADE в MySQL. Чтобы было понятнее, попробуйте больше примеров в конце.
Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.