如何在添加列之前检查列是否存在 [英] How do I check if a column exist before adding it

问题描述

我有一个数据库，如果它不存在，我想向其中添加一列. 如何使用sqlite.swift API做到这一点?

I have a db, and I want to add a column to it if it doesn't exist. How do I do that with sqlite.swift API?

推荐答案

通常，如果要向现有表中添加新列，则需要一个迁移路径.您可以使用userVersion属性来管理数据库架构的版本:

Generally you'll want to have a migration path if you're adding new columns to existing tables. You can use the userVersion attribute to manage versions of your database schema:

if db.userVersion < 1 {

    db.create(table: users) { t in
        t.column(id, primaryKey: true)
        t.column(email, unique: true)
    }

    db.userVersion = 1
}

if db.userVersion < 2 {

    db.alter(table: users, add: name)
    db.alter(table: users, add: age)

    db.userVersion = 2
}

您也可以按照Max的建议在create(table:…)级别上使用ifNotExists::

You can also, as Max suggested, use ifNotExists: at the create(table:…) level:

 db.create(table: users, ifNotExists: true) { t in
    t.column(id, primaryKey: true)
    t.column(email, unique: true)
 }

但是要添加新列，您必须解析笨拙的PRAGMA语句:

But for adding new columns, you have to parse an unwieldy PRAGMA statement:

 let tableInfo = Array(db.prepare("PRAGMA table_info(users)"))
 if tableInfo.filter { col in col[1] == "name" } == nil {
    db.alter(table: users, add: name)
 }
 if tableInfo.filter { col in col[1] == "age" } == nil {
    db.alter(table: users, add: age)
 }

可读性不高(或不建议阅读)，但是，如果您要处理的是旧版数据库，则可能是必要的.

Not nearly as readable (or recommended), but if you're dealing with a legacy database, maybe necessary.

对于更复杂的更改，请务必阅读 ALTER TABLE文档.

Be sure to read the ALTER TABLE documentation for more complicated alterations.

这篇关于如何在添加列之前检查列是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！