sqlite表示用变量插入_SQLite：当插入表示删除时

sqlite表示用变量插入

Modernizing some of SoundCloud’s Android app storage layers, I’ve been especially invested in databases, and have been migrating a lot of the core entities of the app between our in-house ORM and industry-known alternatives, as Room and SQLDelight.

我对SoundCloud的某些Android应用存储层进行了现代化改造，特别是在数据库方面进行了投资，并且已在内部ORM和行业知名的替代方案(如Room和SQLDelight)之间迁移了该应用的许多核心实体。

There are many parts to that work, ranging from understanding the current standings of the schemas to coming up with improvements, risk mitigation or reducing overall tech-debt. It has been a great opportunity to learn about the internals of SQLite, the RDBMS that powers most mobile apps, widely popular in Android but that also backs iOS’ CoreData framework.

这项工作涉及很多部分，从了解架构的当前状况到提出改进，减轻风险或减少总体技术债务。 这是一个很好的机会，可以了解SQLite的内部结构，它是为大多数移动应用程序提供支持的RDBMS，在Android中广为流行，但也支持iOS的CoreData框架。

Today, I want to describe an interesting scenario we’ve hit while using a few more advanced features of SQLite: Foreign Key trigger actions, but more deeply, how their usage in combination to insertion statements can raise unexpected effects.

今天，我想描述一个有趣的场景，它在使用SQLite的一些更高级功能时遇到了： 外键触发动作 ，但更深入地讲，如何将其与插入语句结合使用会产生意想不到的效果。

外键动作 (Foreign Key Actions)

Let’s imagine a schema with two tables, User and Track and a junction table TrackCreator whose schema is as follows:

假设有一个包含两个表的模式， User和Track以及一个联结表 TrackCreator其模式如下：

FloorPlan 😉 FloorPlan generated生成的图

Having a junction table is great because it allows us to write queries that JOIN the columns of either of the referenced tables, for example, if we need to get the creator name for a given Track.

拥有联结表非常有用，因为它使我们能够编写查询，以将JOIN所引用的表中的任何一个列作为查询，例如，如果需要获取给定Track的创建者name 。

Now, if either the Track or the User that created it gets deleted or have its id altered, SQLite can automatically update our TrackCreator table if we set a Foreign Key action for these references.

现在，如果删除了Track或创建它的User或更改了其id ，如果我们为这些引用设置了Foreign Key操作 ，则SQLite可以自动更新TrackCreator表。

CREATE TABLE IF NOT EXISTS TrackCreator (
  `track_id` TEXT NOT NULL,
  `user_id` TEXT NOT NULL,
  
  PRIMARY KEY(`track_id`, `user_id`),
  
  FOREIGN KEY(`track_id`) REFERENCES `Track`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE ,
  FOREIGN KEY(`user_id`) REFERENCES `User`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE 
)

SQLite’s documentation does a great job explaining what foreign keys are, and more interestingly the possible actions one can register as triggers to an update or deletion in the parent table.

SQLite的文档很好地解释了什么是外键，更有趣的是可能采取的措施 可以在父表中注册为更新或删除的触发器。

解决冲突 (Conflict resolution)

SQLite also accounts for possible conflict resolutions when constraints are violated: on the example above, stating anINSERT for an already existing Track or User would violate the PRIMARY KEY uniqueness constraint on either table.

当违反约束时，SQLite还考虑了可能的冲突解决方案 ：在上面的示例中，为已存在的Track或User声明INSERT会违反任何一个表上的PRIMARY KEY唯一性约束。

Most ORMs make it really easy to define a conflict resolution, altering the INSERT statement to include the ON CONFLICT clause:

大多数ORM确实很容易定义冲突解决方案，将INSERT语句更改为包含ON CONFLICT子句：

When working with Room, that can be done on the Dao by specifying it in an annotation:

使用Room时，可以在Dao通过在批注中指定它来完成：

@Dao
interface TrackDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun insert(tracks: List<TrackEntity>)
}

抓住 (The catch)

Now, on the setup above, a surprising effect would happen when inserting an already existing Track or User. As SQLite’s documentation states:

现在，在上面的设置中，当插入一个已经存在的Track或User时，将会发生令人惊讶的效果。 正如SQLite的文档所述：

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes (…) prior to inserting or updating the current row.

当发生UNIQUE或PRIMARY KEY约束冲突时，REPLACE算法将在插入或更新当前行之前删除(…)。

So here we are:

所以我们在这里：

And at that point, our ON DELETE action will trigger, effectively removing the entry from the junction table as well. That means queries that rely on the JOIN statement will yield no results.

到那时，我们的ON DELETE动作将触发，并有效地从联结表中删除该条目 。 这意味着依赖于JOIN语句的查询将不会产生任何结果。

Android特性 (Android specificities)

During our investigation, we were made aware of this behaviour by reading SQLite log statements. On Android, the mechanism to enable logging isn’t straightforward, but exists.

在我们的调查过程中，我们通过读取SQLite日志语句了解了此行为。 在Android上，启用日志记录的机制并不简单，但存在 。

When inserting Tracks that violate the uniqueness constraint of the primary key, we confirm our hypothesis:

在插入违反主键唯一性约束的Tracks ，我们确认了以下假设：

V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "BEGIN EXCLUSIVE;"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "INSERT OR REPLACE INTO `Users` (...) VALUES (...)"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "INSERT OR REPLACE INTO `Users` (...) VALUES (...)"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- TRIGGER room_table_modification_trigger_trackuser_DELETE"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- UPDATE room_table_modification_log SET invalidated = 1 WHERE table_id = 7 AND invalidated = 0"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- TRIGGER room_table_modification_trigger_trackuser_DELETE"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- UPDATE room_table_modification_log SET invalidated = 1 WHERE table_id = 7 AND invalidated = 0"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- TRIGGER room_table_modification_trigger_users_DELETE"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- UPDATE room_table_modification_log SET invalidated = 1 WHERE table_id = 0 AND invalidated = 0"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- TRIGGER room_table_modification_trigger_users_INSERT"
V/SQLiteStatements: /data/com.soundcloud/databases/sc.db: "-- UPDATE room_table_modification_log SET invalidated = 1 WHERE table_id = 0 AND invalidated = 0"

Starting on version 3.24.0 (2018–06–04), SQLite does provide an UPSERT statement (following the syntax established by PostgreSQL) but the bundled version of SQLite in the Android framework is not quite up-to-date.

从版本3.24.0(2018-06-04)开始，SQLite确实提供了 UPSERT 语句 (遵循PostgreSQL建立的语法)，但是在Android框架中捆绑SQLite版本不是最新的 。

UPSERT is a special syntax addition to INSERT that causes INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint.

UPSERT是INSERT的特殊语法补充，如果INSERT违反唯一性约束，则它会使INSERT表现为UPDATE或no-op。

It is still possible to ‘manually implement’ UPSERT by mimicking SQLite’s syntax:

仍然可以通过模仿SQLite的语法来“手动实现” UPSERT ：

迁移时间 (Time for a migration)

In our case, we could relax our constraint definition by dropping the ON DELETE action trigger, instead of implementing the UPSERT. Here, the usage of SQLite brings a bit more work: the ALTER TABLE statements omit many of the advanced clauses other SQL engines provide, including CONSTRAINT manipulation related.

在我们的例子中，我们可以通过删除ON DELETE操作触发器来放松约束定义，而不是实现UPSERT 。 在这里，SQLite的使用带来了更多工作： ALTER TABLE 语句忽略了其他SQL引擎提供的许多高级子句，包括与CONSTRAINT操作有关的子句。

So, in order to change the constraint definition for the foreign key, we require a multi-step migration, that will copy the existing data into a temporary table and rename it afterwards:

因此，为了更改外键的约束定义，我们需要一个多步骤迁移，该迁移将把现有数据复制到一个临时表中，然后对其进行重命名：

database.execSQL("""
    CREATE TABLE IF NOT EXISTS TrackCreator_temp (
        `track_id` TEXT NOT NULL,
        `user_id` TEXT NOT NULL,
        PRIMARY KEY(`track_id`, `user_id`),
        FOREIGN KEY(`track_id`) REFERENCES `Track`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION ,
        FOREIGN KEY(`user_id`) REFERENCES `User`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION 
        )
    """.trimIndent())


// Move all data to temporary table
database.execSQL("INSERT INTO TrackCreator_temp SELECT * FROM TrackCreator")


// Drop mis-scheme table
database.execSQL("DROP TABLE TrackCreator")


// Rename temporary table to definitive name
database.execSQL("ALTER TABLE TrackCreator_temp RENAME TO TrackCreator")


// Recreate indexes if needed (omitted for brevity)

One must be attentive to the underlying mechanisms of third-party tooling. Dealing with persistence and databases, it pays off to get acquainted to some degree of detail, especially given the cost of a post-factum change.

必须注意第三方工具的基本机制。 处理持久性和数据库，在一定程度上了解细节是有回报的，尤其是考虑到事后变更的成本。

Being embedded, and in an almost feature-freeze on mobile OSs, SQLite’s lean API still provides ‘eureka moments’. There are always opportunities for learning.

SQLite的精益API被嵌入并且几乎在移动操作系统中处于冻结状态，仍然提供“尤里卡时刻”。 总会有学习的机会。

翻译自: https://proandroiddev.com/sqlite-when-insert-means-delete-1bb7ca85d785

sqlite表示用变量插入