Кот в мешке: мастерство обработки ошибок внешних ключей SQLite12.03.2024 12:45

Привет, меня зовут Тимур, я Android-разработчик в KTS.

Сталкивались с загадочными ошибками Foreign Key в SQLite для Android? Сегодня мы раскроем завесу тайны и расскажем как извлекать подробную информацию об ошибках этого типа.

Содержание

Что такое «Ограничение внешнего ключа»

Если вы работали с SQLite, то скорее всего сталкивались с ограничениями внешнего ключа. 

Если нет, представьте, что у вас есть две таблицы в базе данных:

В таблице «студенты» у каждого студента есть уникальный идентификатор, например номер студенческого билета. Теперь вы хотите связать таблицу «студенты» и «курсы» — чтобы знать, какие студенты на какие курсы записаны. Для этого создается таблица «записи на курсы».

Для связи мы используем Foreign Key в таблице «записи на курсы», которые указывают на идентификаторы из таблиц «студенты» и «курсы» . 

Так между таблицами появляется связь, благодаря которой невозможно добавить запись в таблицу «запись на курсы» с идентификатором студента или курса, которого нет в соответствующей таблице. Это обеспечивает целостность данных и предотвращает появление «бесхозных» записей.

Почему появляется исключение Foreign Key и как выглядит ее стандартное описание

Иногда данные в приложение приходят с сервера. Они могут быть не консистентными, например, в данных есть запись на курс со студентом, которого отчислили (удалили из таблицы), но запись на курс с таким студентом еще осталась.

При сохранении записи на курс со ссылкой на студента, которого больше нет в БД, мы получим ошибку ограничения внешнего ключа. По хорошему на сервере нужно было заранее об этом подумать и сделать каскадное удаление данных, чтобы вообще не допускать этой проблемы. Но все мы допускаем ошибки, поэтому если к нам попали ошибочные данные, то по дефолту мы получаем ошибку такого вида:

Все, что мы можем понять из этого сообщения, это то, что где-то в таблице "transactions" произошла ошибка. Чтобы понять, какая запись вызвала проблему, необходимо привлекать бэкендеров или мобильных разработчиков.

В статье я покажу способы, которые позволят превратить непонятную ошибку в понятную:

Благодаря такому описанию менеджеры с доступом в админку смогут поправить ошибку самостоятельно.

Все описанные в статье методы мы использовали в таких проектах, как ПИК и Ascott Group. 

В какие стороны безрезультатно копали

Подмена / генерация байт кода

Про патчинг байт кода можно почитать в статье. 

Если вкратце, необходимо создать плагин компилятора, в котором можно добавлять необходимые трансформации для вашего кода. Для решения нашей проблемы в методе visitMethod класса трансформации нужно трансформировать методы, у которых есть одна из аннотаций Room (insert, update и delete), и добавить в вызов этих методов обработку Foreign Key исключения для логирования ошибки. 

Мы также использовали этот метод для другой задачи — логирование вызовов и результатов методов для отладки.

По итогам работы с этим подходом мы поняли, что генерация может легко сломать ваш код и эту поломку будет сложно отладить, поэтому мы стали искать другие решения.

Первое решение: обёртка для SQLite-запроса с обработкой и поиском причины проблемы

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

Решение заключается в обёртке в виде функции расширения, которая перехватывает SQLiteConstraintException. На основе данных, использованных в запросе с ошибкой, выполняются дополнительные SQL-запросы, которые определяют, для какой записи в таблице отсутствуют необходимые для связи данные.

Есть обёртки для операций INSERT, UPDATE и DELETE. Алгоритм для поиска ошибки при INSERT и UPDATE одинаковый, а вот для DELETE немного отличается. 

Начнём с операций INSERT и UPDATE. Обёртка для них выглядит так:

suspend inline fun > FinanceDB.insertOrUpdateWithFKCheck(
   itemsToInsert: List,
   insertCallback: (items: List) -> Unit
) {
   if (itemsToInsert.isEmpty()) return


   checkFkException(
       tryBlock = {
           insertCallback(itemsToInsert)
       },
       catchFkExceptionBlock = {
           getFkExceptionFormattingOnSave(
               itemsToSave = itemsToInsert,
               action = TableAction.INSERT,
           )
       }
   )
}

Функция insertOrUpdateWithFKCheck является обёрткой для операции вставки. Основная задача функции — перехватить SQLiteConstraintException и сгенерировать более информативное сообщение об ошибке. 

Вот как выглядит функция checkFkException, которая проверяет нарушение ограничения внешних ключей:

inline fun > checkFkException(  
    tryBlock: () -> Unit,  
    catchFkExceptionBlock: () -> FkExceptionFormattingInfo  
) {  
    try {  
        tryBlock()  
    } catch (sqliteException: SQLiteConstraintException) {  
        sqliteException.message  
            ?.lowercase(Locale.getDefault())  
            ?.takeIf { errorMessage -> FOREIGN_KEY_EXCEPTION_TEXT in errorMessage }  
            ?: throw sqliteException  
  
        val errorMessage = createFkExceptionErrorMessage(
             messageInfo = catchFkExceptionBlock()
        )  
        throw SQLiteConstraintException(errorMessage)  
    }  
}

Если функция ловит SQLiteConstraintException, нужно узнать, что вызвало ошибку. Срабатывает лямбда catchFkExceptionBlock, которая возвращает отформатированную информацию о причине ошибки и кладёт ее в SQLiteConstraintException чтобы пробросить исключение дальше. 

В лямбде catchFkExceptionBlock вызывается функция getFkExceptionFormattingOnSave, она предназначена для поиска данных, которые вызывают ошибки нарушения целостности данных для операций INSERT и UPDATE.

getFkExceptionFormattingOnSave

suspend inline fun >
Db.getFkExceptionFormattingOnSave(  
    itemsToSave: List,  
    action: TableAction,  
    apiQueryName: String? = null  
): FkExceptionFormattingInfo {  
    val entityInfo = itemsToSave.first()  
    val entityInfoToFkIds = mutableMapOf>()  
    val foreignKeyRelations = getForeignKeyRelationFor(entityInfo)  
  
    val errorItems = itemsToSave.filter { itemToInsert ->  
        foreignKeyRelations.all { fkRelation ->  
            val foreignKeyValue = fkRelation.childFkIdExtractor(itemToInsert) ?: return@all true  
            fkRelation.parentFkIds.contains(foreignKeyValue).not()  
                .also { constraintFails ->  
                    if (constraintFails) {  
                        val constraintFailsList = entityInfoToFkIds[fkRelation.entityInfo]  
                            ?.toMutableList()  
                            ?.apply {  
                                add(foreignKeyValue)  
                            } ?: listOf(foreignKeyValue)  
                        entityInfoToFkIds[fkRelation.entityInfo] = constraintFailsList  
                    }  
                }  
        }  
    }  
  
    return FkExceptionFormattingInfo(  
        tableName = entityInfo.tableName,  
        action = action,  
        errorItems = errorItems,  
        entityInfoToFkIds = entityInfoToFkIds,  
        apiQueryName = apiQueryName  
    )  
}

Функция принимает список элементов itemsToSave, action (INSERT, UPDATE) и анализирует внешние ключи, связанные с типом дженерика BaseEntity.

BaseEntity — интерфейс, от которого наследуются все сущности, и он нужен для вывода в ошибку такой информации, как имя таблицы, имя внешнего ключа и его значение.

После пробегаемся по данным, которые должны были сохраниться и отношениям, чтобы вычислить элементы которые приводят к ошибкам. Для того чтобы определить, какой внешний ключ приводит к ошибке, мы должны проверить: есть ли запись со значением внешнего ключа в таблице, на которую он ссылается. Если значения нет, то именно этот внешний ключ приводит к ошибке.

После того как мы нашли причину, кладём данные в FkExceptionFormattingInfo для последующего форматирования сообщения. 

Вот так выглядит сообщение при попытке сохранить и обновить запись с несуществующими ссылками:

Для операций удаления мы получаем сущности элементов по id, которые хотели удалить, и модели с отношениями. Получив всё необходимое, пробегаемся по моделям с отношениями и берём те, которые не пустые: в них ещё есть ссылка на значение, которое мы пытались удалить, из-за этого и возникает ошибка. Дальше формируем сообщение об ошибке, это делается так же, как для INSERT и UPDATE.

Формируем сообщение об ошибке

suspend inline fun > Db.deleteWithFKCheck(
    itemIdsToDelete: List,
    deleteCallback: (items: List) -> Unit
) {
    if (itemIdsToDelete.isEmpty()) return
    checkFkException(
        tryBlock = {
            batchedQueryInTransaction(
                list = itemIdsToDelete,
                query = deleteCallback
            )
        },
        catchFkExceptionBlock = {
            val items = getItemsByIds(itemIdsToDelete)
            val entityInfo = items.first()
            val entityInfoToFkIds = mutableMapOf, List>()
            val entityRelations: List> = itemIdsToDelete.queryWithParameterCountCheck { ids ->
                getEntityRelationByIds(ids) as List>
            }

            val errorItems = entityRelations.filter { entityRelation ->
                entityRelation.embeddedConstraintsEmpty().not()
                    .also { constraintFails ->
                        if (constraintFails) {
                            entityInfoToFkIds.putAll(
                                entityRelation.getEmbeddedConstraints()
                                    .map { relation ->
                                        relation to relation.remotePrimaryItem
                                    }
                                    .filter { it.second != null }
                                    .groupBy { it.first }
                                    .mapValues { (_, infoItems) ->
                                        infoItems.mapNotNull { infoItem -> infoItem.second }
                                    }
                            )
                        }
                    }
            }.map { it.embeddedValue }

            FkExceptionFormattingInfo(
                tableName = entityInfo.tableName,
                action = TableAction.DELETE,
                errorItems = errorItems,
                entityInfoToFkIds = entityInfoToFkIds
            )
        }
    )
}

Вот так выглядит сообщение при попытке удалить запись, на которую ещё кто-то ссылается:

Плюсы:

Минусы:

• Большое количество кода. Для достижения такой высокой структурированности и информативности требуется написать много дополнительного кода, что усложняет поддержку в целом. Для каждой сущности нужно выполнить следующие действия:

  • Наследоваться от BaseEntity и переопределить значения

  • Прописать в функции getForeignKeyRelationFor какие есть внешние ссылки для каждой из локальной сущности.

  • Написать запрос для получения всех id, на которые есть ссылки

  • Написать запрос на получения сущностей по id 

  • Написать запрос для получения данных по id, которые возвращают relations модель, а значит, для таких запросов ещё дополнительно нужно создать relations модели

  • И каждый запрос обернуть правильным экстеншеном

• Для поиска проблемной сущности выполняются дополнительные запросы в базу, это занимает определённое время

• Сложность понимания. Новым разработчикам легко ошибиться в работе с этим подходом, поэтому для изучения потребуется время

• Определённо не подходит для приложений с большим количеством сущностей и связей, потому что с каждым разом придётся писать всё больше и больше кода по настройке

Улучшения:

Минусы этого подхода заставили нас искать другие решения. В итоге мы остановились на варианте, о котором поговорим далее.

Финальное решение: Собственный SupportSQLiteOpenHelper

Давайте начнем с основ. SupportSQLiteOpenHelper — это интерфейс для отображения поведения вспомогательного класса SQLiteOpenHelper для управления созданием базы данных и управлением версиями. SupportSQLiteOpenHelper используется под капотом в библиотеке Room для работы с базой данных. Подобный механизм также есть и в KMP-библиотеке SQLDelight (только для Android в фабрике AndroidSqliteDriver).

Внутри SupportSQLiteOpenHelper используется SupportSQLiteDatabase, который имеет методы для выполнения SQL-команд и других распространенных задач управления базой данных. SupportSQLiteDatabase, в свою очередь, компилирует инструкцию SQL в виде SupportSQLiteStatement, которую можно выполнить для базы данных. 

Идея в том, что мы реализуем собственный SupportSQLiteOpenHelper, чтобы в методах SupportSQLiteStatement обработать исключение SQLiteConstraintException и на основе данных, которые есть в инструкции, получить запрос, на котором произошла ошибка.  

Краткий план:

• Сначала рассмотрим, как выглядит работа с SupportSQLite*-интерфейсами в коде, который сгенерировал Room, чтобы понять, как это решение будет работать.

• Реализация собственного SupportSQLiteOpenHelper и обработка ошибки Foreign Key

Как Room работает с SupportSQLite* интерфейсами в сгенерированном коде

Когда Room генерирует код для запросов, он работает именно с реализациями этих интерфейсов, а именно с классом FrameworkSQLiteOpenHelperFactory. В Room мы описываем DAO-интерфейс, а Room генерирует всю имплементацию самостоятельно. 

Ниже можно увидеть пример с описанием интерфейса TransactionDao с операцией вставки:  

@Dao
interface TransactionDao {

   @Insert(onConflict = OnConflictStrategy.REPLACE)
   suspend fun insert(transactions: List)
}

А так выглядит сгенерированный Room-ом код для операции вставки, которую мы описали в TransactionDao ранее:

@Override
public Object insert(final List transactions,
   final Continuation continuation) {
 return CoroutinesRoom.execute(__db, true, new Callable() {
   @Override
   public Unit call() throws Exception {
     __db.beginTransaction();
     try {
       __insertionAdapterOfLocalTransaction.insert(transactions);
       __db.setTransactionSuccessful();
       return Unit.INSTANCE;
     } finally {
       __db.endTransaction();
     }
   }
 }, continuation);
}

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

Также в сгенерированном коде мы можем увидеть вызов метода insert, который использует SupportSQLiteStatement для биндинга данных и для операции вставки:

fun insert(entity: T) {
   val stmt: SupportSQLiteStatement = acquire()
   try {
       bind(stmt, entity)
       stmt.executeInsert()
   } finally {
       release(stmt)
   }
}

Далее мы переопределим executeInsert и другие методы, чтобы перехватывать в них ошибку. Выглядит перехват ошибки так (реализацию функции withForeignKeyCheck рассмотрим позже):

override fun executeInsert(): Long {
   return database.withForeignKeyCheck(
       sqlQuery = sql, 
       args = bindArgsCache.toTypedArray(),
       executeQuery = {  
           supportSQLiteStatement.executeInsert()
       }
   ) 
}

Реализация собственного SupportSQLiteOpenHelper:

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

Для начала нужно понять, как сделать так, чтобы Room начал использовать наши SupportSQLiteOpenHelper, SupportSQLiteDatabase и SupportSQLiteStatement.

У Room.databaseBuilder есть метод, в который мы можем положить нашу реализацию SupportSQLiteOpenHelper. Тогда Room будет работать с ней, а не с FrameworkSQLiteOpenHelperFactory. Выглядит это так:

Room.databaseBuilder(  
    context,  
    FinanceDB::class.java,  
    FinanceDB.DATABASE_NAME,  
)  
    .openHelperFactory(  
        factory = FinanceQLiteOpenHelperFactory(  
            delegate = FrameworkSQLiteOpenHelperFactory()  
        )  
    )  
    .build()

Как я говорил ранее, то же самое можно сделать и в SqlDelight:

AndroidSqliteDriver(
   openHelper = FinanceQLiteOpenHelperFactory(
       delegate = FrameworkSQLiteOpenHelperFactory()
   ) ,
   schema = Database.Schema,
   context = context,
   name = DATABASE_NAME,
   callback = object : AndroidSqliteDriver.Callback(Database.Schema) {
       override fun onOpen(db: SupportSQLiteDatabase) {
           db.setForeignKeyConstraintsEnabled(true)
       }
   }
)

Для достижения нашей цели нет необходимости реализовывать все методы с нуля. Для сокращения объема кода мы будем использовать Kotlin Delegation. Это фича позволяет переопределить только те методы, которые требуется изменить, избавляя от необходимости реализации всех подлежащих переопределению методов. 

Другими словами, методы, которые мы не переопределим, будут использовать реализацию из FrameworkSQLiteOpenHelperFactory. Этот шаг не обязателен, поэтому если вам нужно сделать что-то более кастомное, то можно обойтись и без делегирования. 

Мы используем FrameworkSQLiteOpenHelperFactory чтобы создать SupportSQLiteOpenHelper:

internal class FinanceQLiteOpenHelperFactory(  
    private val delegate: SupportSQLiteOpenHelper.Factory  
) : SupportSQLiteOpenHelper.Factory {  
  
    override fun create(  
        configuration: SupportSQLiteOpenHelper.Configuration  
    ): SupportSQLiteOpenHelper {  
        return InterceptorSQLiteOpenHelper(
	        openHelper = delegate.create(configuration)
        )  
    }  
}

Созданный SupportSQLiteOpenHelper передаётся в InterceptorSQLiteOpenHelper, в котором с помощью делегирования отдаём некоторую работу дефолтной реализации из FrameworkSQLiteOpenHelperFactory, а сами переопределяем только необходимое:

internal class InterceptorSQLiteOpenHelper(  
    private val openHelper: SupportSQLiteOpenHelper  
) : SupportSQLiteOpenHelper by openHelper {  
  
    override val readableDatabase: SupportSQLiteDatabase  
        get() = InterceptorSQLiteDatabase(openHelper.readableDatabase)  
  
    override val writableDatabase: SupportSQLiteDatabase  
        get() = InterceptorSQLiteDatabase(openHelper.writableDatabase)  
}

То же самое мы делаем с SupportSQLiteDatabase, тут переопределяем только метод компиляции инструкции SupportSQLiteStatement.

internal class InterceptorSQLiteDatabase(  
    private val database: SupportSQLiteDatabase  
) : SupportSQLiteDatabase by database {

	override fun compileStatement(
		sql: String
	): SupportSQLiteStatement {  
		return InterceptorSQLiteStatement(  
			supportSQLiteStatement = database.compileStatement(sql),  
			database = database,  
			sql = sql,  
		)  
	}
}

С SupportSQLiteStatement делаем так же, но тут уже будет выполняться перехват ошибок. Для этого нужно переопределить методы execute, executeUpdateDelete и executeInsert, в которых будет перехват ошибок, и методы bind*, в которых мы будем получать аргументы и сохранять в список.

Выглядит это вот так:

internal class InterceptorSQLiteStatement(
    private val supportSQLiteStatement: SupportSQLiteStatement,
    private val database: SupportSQLiteDatabase,
    private val sql: String
) : SupportSQLiteStatement by supportSQLiteStatement {

    private val bindArgsCache: MutableList = mutableListOf()

    override fun bindNull(index: Int) {
        saveArgsToCache(index, null)
        supportSQLiteStatement.bindNull(index)
    }

    override fun bindLong(index: Int, value: Long) {
        saveArgsToCache(index, value)
        supportSQLiteStatement.bindLong(index, value)
    }

    override fun bindDouble(index: Int, value: Double) {
        saveArgsToCache(index, value)
        supportSQLiteStatement.bindDouble(index, value)
    }

    override fun bindString(index: Int, value: String) {
        saveArgsToCache(index, value)
        supportSQLiteStatement.bindString(index, value)
    }

    override fun bindBlob(index: Int, value: ByteArray) {
        saveArgsToCache(index, value)
        supportSQLiteStatement.bindBlob(index, value)
    }

    override fun clearBindings() {
        bindArgsCache.clear()
        supportSQLiteStatement.clearBindings()
    }

    override fun execute() {
        database.withForeignKeyCheck(sql, bindArgsCache.toTypedArray()) { _, _ ->
            supportSQLiteStatement.execute()
        }
    }

    override fun executeUpdateDelete(): Int {
        return database.withForeignKeyCheck(sql, bindArgsCache.toTypedArray()) { _, _ ->
            supportSQLiteStatement.executeUpdateDelete()
        }
    }

    override fun executeInsert(): Long {
        return database.withForeignKeyCheck(sql, bindArgsCache.toTypedArray()) { _, _ ->
            supportSQLiteStatement.executeInsert()
        }
    }

    private fun saveArgsToCache(bindIndex: Int, value: Any?) {
        val index = bindIndex - 1
        if (index >= bindArgsCache.size) {
            // Add null entries to the list until we have the desired # of indices
            for (i in bindArgsCache.size..index) {
                bindArgsCache.add(null)
            }
        }
        bindArgsCache[index] = value
    }
}

В методах execute* можно увидеть экстеншен withForeignKeyCheck, в который мы передаём сам SQL-запрос и его аргументы. Этот экстеншен перехватывает исключение SQLiteConstraintException, а потом на основе запроса и его аргументов пытается улучшить сообщение.

Выглядит этот экстеншен так:

internal inline fun  SupportSQLiteDatabase.withForeignKeyCheck(
   sql: String,
   args: Array,
   query: (String, Array) -> R
): R {
   try {
       return query(sql, args)
   } catch (e: SQLiteConstraintException) {


       val prepareSql = prepareSql(sql)
       val argsList = args.map { it.toString() }


       val formattedSqlQuery = prepareSql.replace("?", "%S").format(*args)


       val foreignKeyMessage = try {
           getForeignKeyMessage(sql = prepareSql.lowercase(), args = argsList)
       } catch (t: Throwable) {
           Timber.e("Fet foreign key message for sql=$formattedSqlQuery")
           ""
       }


       throw SQLiteConstraintException("$formattedSqlQuery\n$foreignKeyMessage")
   }
}

Тут мы на основе SQL-запроса и аргументов можем вывести полноценный SQL-запрос.

Вот так выглядит пример SQL-запроса, который попадает в withForeignKeyCheck:

INSERT OR REPLACE INTO `transactions` 
(`id`,`bill_id`,`category_id`,`amount`,`date`,`comment`) VALUES (?,?,?,?,?,?)

А вот так выглядит список аргументов:

[1, 2, 3, 10, 0, null]

Имея всю эту информацию, мы можем получить запрос в таком виде:

INSERT OR REPLACE INTO `transactions` 
(`id`,`bill_id`,`category_id`,`amount`,`date`,`comment`) VALUES (1,2,3,10,0,NULL)

Это уже круто. Ведь стандартная ошибка не скажет, при добавлении каких значений она появилась.

Хотелось бы еще получить информацию о том, что именно привело к ошибке. Для этого распарсим запрос, вытащим отношения и с помощью дополнительных запросов выясним, какая запись была утеряна. 

Следует сразу обратить внимание на то, что код из примера может не работать на определенных запросах, так как парсинг для SQL был написан вручную, и я мог что-то не учесть.

Давайте подробнее разберём, как вытащить нужную информацию. Так выглядит функция getForeignKeyMessage:

getForeignKeyMessage

private fun SupportSQLiteDatabase.getForeignKeyMessage(
   sql: String,
   args: List,
): String {
   return when {
       sql.isInsert || sql.isUpdate -> {
           val tableName = getTableNameFromSqlQuery(sql) ?: return ""


           getForeignKeyValueForInsertOrUpdate(
               sql = sql,
               args = args,
               tableName = tableName,
           )?.joinToString(",\n") { (foreignKey, value) ->
               "FK Error ($tableName.${foreignKey.localColumn} -> " +
                   "${foreignKey.foreignTable}.${foreignKey.foreignColumn})\n" +
                   "There is no field with ${foreignKey.foreignColumn}=${value} " +
                   "in the ${foreignKey.foreignTable} table"
           }.orEmpty()
       }


       sql.isDelete -> {
           val tableName = getTableNameFromSqlQuery(sql) ?: return ""


           getForeignKeyValuesForDelete(
               sql = sql,
               args = args,
               tableName = tableName,
           )?.joinToString(",\n") { (foreignKey, primaryKeyName, primaryKeyValue, value) ->
               "FK Error ($value.${foreignKey?.localColumn} -> " +
                   "${foreignKey?.foreignTable}.${foreignKey?.foreignColumn})\n" +
                   "For $tableName.$primaryKeyName=$primaryKeyValue: it is not " +
                   "possible to delete the field, because the " +
                   "${foreignKey?.foreignTable}.${foreignKey?.foreignColumn} " +
                   "is used in the table \"$value\""
           }.orEmpty()
       }


       else -> ""
   }
}

В этой функции мы хотим определить, какой запрос выполнялся — INSERT, UPDATE или DELETE. Это нужно, потому что алгоритм поиска проблемных мест отличается для разных запросов. 

Сначала посмотрим, как искать информацию для INSERT и UPDATE, а потом для DELETE.

Провалимся в функцию getForeignKeyValueForInsertOrUpdate. Выглядит она так:

getForeignKeyValueForInsertOrUpdate

private fun SupportSQLiteDatabase.getForeignKeyValueForInsertOrUpdate(
   sql: String,
   args: List,
   tableName: String,
): List? {
   val foreignKeyList = queryForeignKeyList(tableName)


   return foreignKeyList.mapNotNull { foreignKey ->
       val value = getValuesFromUpdateAndInsertSqlQueryByColumnName(sql, args, foreignKey)


       value?.let {
           val cursor = query(
               "SELECT * FROM ${foreignKey.foreignTable} WHERE ${foreignKey.foreignColumn} = ?",
               arrayOf(value)
           )


           val isForeignKey = cursor.use { it.moveToFirst() }.not()


           if (isForeignKey) {
               ForeignKeyInsertUpdate(
                   foreignKey = foreignKey,
                   value = value,
               )
           } else {
               null
           }
       }
   }.ifEmpty { null }
}

В ней выполняем запрос и PRAGMA foreign_key_list(table_name), который получает список ссылок на внешние ключи. Потом мы просто кладём всю эту информацию в список. На основе этих данных мы будем делать дополнительные запросы в таблицы, на которые ссылаются внешние ключи, чтобы выяснить, каких данных не хватает для успешного завершения запроса.

После того как мы получили все внешние ссылки, начинаем проверять, какая из них вызывает ошибку. 

С помощью функции getValuesFromUpdateOrInsertSqlQueryByColumnName.

Мы получаем значения для INSERT или UPDATE, которые могли вызвать ошибку. Например, при запросе…

INSERT OR REPLACE INTO `transactions` 
(`id`,`bill_id`,`category_id`,`amount`,`date`,`comment`) 
VALUES (1,2,3,10,0,NULL)

…мы получим значения для bill_id и category_id, потому что они являются полями с внешними ссылками.

Дальше мы хотим выполнить запрос в таблицу, на которую ссылается значение. Если запись по такому значению не найдена, то ошибка есть. Когда мы нашли все значения, вызывающие ошибку, формируем сообщение.

Вот так выглядят пример сообщения об ошибке при добавлении и обновлении:

Теперь разберём, как искать ошибки для запроса на удаление. За это отвечает функция getForeignKeyValuesForDelete.

getForeignKeyValuesForDelete

private fun SupportSQLiteDatabase.getForeignKeyValuesForDelete(
   sql: String,
   tableName: String,
   args: List,
): List? {


   val foreignKeyList = findForeignKeysInAllTable(tableName)


   if (sql.isDelete.not() || foreignKeyList.isEmpty()) return null


   val primaryKeyName = queryPrimaryKeyName(tableName) ?: return null


   val sqlWithArgs = formatSqlQuery(sql, args.toTypedArray())
   val conditions = getConditionsFromSqlDeleteQuery(sql = sqlWithArgs)


   val cursorWithDeleteConditions = query(
       "SELECT * FROM $tableName WHERE $conditions"
   )


   val primaryKeyValues = mutableListOf()


   cursorWithDeleteConditions.use {
       while (it.moveToNext()) {
           val primaryKeyIndex = it.getColumnIndex(primaryKeyName)
           if (primaryKeyIndex != -1) {
               primaryKeyValues += it.getString(primaryKeyIndex)
           }
       }
   }


   return primaryKeyValues.mapNotNull { primaryKeyValue ->
       findForeignKeyCreateErrorsForDelete(
           primaryKeyValue = primaryKeyValue,
           foreignKeyList = foreignKeyList,
           primaryKeyName = primaryKeyName,
       )
   }.flatten()
}

Сначала мы должны получить все внешние ссылки, которые ссылаются на таблицу, запись которой хотим удалить. Это необходимо, чтобы понять, где ещё используется удаляемое значение.  Делаем мы это с помощью функции findForeignKeysInAllTable:

internal fun SupportSQLiteDatabase.findForeignKeysInAllTable(
    foreignTable: String
): List {
   val tableNames = getAllTableNames()


   val foreignKeysList = mutableListOf()


   for (name in tableNames) {
       val foreignKeys = queryForeignKeyList(name)


       foreignKeysList.addAll(
           foreignKeys.filter { it.foreignTable == foreignTable }
       )
   }


   return foreignKeysList
}

Получаем имена всех таблиц, потом получаем список внешних ключей для каждой из них и кладём в результат только те, которые ссылаются на имя таблицы, которую мы передали в параметры функции. После мы делаем запрос, чтобы получить идентификаторы элементов, которые мы удаляем, подставив условие для удаления в SELECT.

После того как мы получили все первичные ключи, мы должны проверить для каждого из них используется ли удаляемое значение или нет. Делаем это в функции findForeignKeyCreateErrorsForDelete:

findForeignKeyCreateErrorsForDelete

private fun SupportSQLiteDatabase.findForeignKeyCreateErrorsForDelete(
   primaryKeyValue: String,
   foreignKeyList: List,
   primaryKeyName: String
): List? {
   val result = mutableListOf()
  
   result += foreignKeyList.mapNotNull { foreignKey ->
       val cursor = query(
           "SELECT * FROM ${foreignKey.localTable} WHERE ${foreignKey.localColumn} = ?",
           arrayOf(primaryKeyValue)
       )


       val isForeignKey = cursor.use { it.moveToFirst() }


       if (isForeignKey) {
           ForeignKeyDelete(
               fk = foreignKey,
               primaryKeyName = primaryKeyName,
               primaryKeyValue = primaryKeyValue,
               useTable = foreignKey.localTable,
           )
       } else {
           null
       }
   }


   return result.ifEmpty { null }
}

Делаем запрос в таблицу, где есть внешний ключ, ссылающийся на первичный ключ таблицы, из которой мы удаляли значение. Если оно существует, то из-за него и произошла ошибка. Далее из списка найденных элементов формируем понятное сообщение об ошибке.

Так выглядит пример сообщения об ошибке при удалении:

Плюсы:

• Маленькое количество кода — по сути, один раз написал обработку и забыл, все работает под капотом. То есть при добавлении новых сущностей не нужно писать дополнительного кода по сравнении с прошлым подходом.

• Простота — новым разработчикам не нужно будет разбираться в новом подходе по сравнению с прошлым, где нужно следить, чтобы запросы оборачивались в обертку.

• Может работать везде, где используется SQLiteOpenHelper (в Room и SqlDelight для Android-платформы).

Минусы:

• Приходится писать парсинг для SQLite, что не очень удобно. C другой стороны, это нужно сделать всего 1 раз. Вы можете начать с нашего примера

• При возникновении исключения всё ещё выполняются дополнительные запросы для получения более подробной информации об ошибке. Эти запросы могут занимать разное время в зависимости от объема данных в таблицах

Заключение

Весь код из статьи можно посмотреть в репозитории:
https://github.com/ktsstudio/android-fk-helper-sample. 

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

Использование такого подхода позволяет быстро искать и исправлять ошибки, связанные с исключениями Foreign Key.  Подробное описание ошибки позволяет нам полностью исключить привлечение разработчиков, потому что теперь ошибку может решить любой сотрудник с доступом в админку, раньше всегда приходилось привлекать разработчиков и тратить их время на решение проблемы, которое могло бы пойти на решение более важных задач.

© Habrahabr.ru