ALTER TABLE ADD COLUMN, ЕСЛИ НЕ СУЩЕСТВУЕТ В SQLite

Недавно нам потребовалось добавить столбцы в некоторые из существующих таблиц базы данных SQLite. Это можно сделать с помощью ALTER TABLE ADD COLUMN. Конечно, если таблица уже была изменена, мы хотим оставить ее в покое. К сожалению, SQLite не поддерживает предложение IF NOT EXISTS на ALTER TABLE.

Нашим текущим решением является выполнение инструкции ALTER TABLE и игнорирование любых ошибок "дублированного столбца", как этот пример Python (но на С++).

Однако наш обычный подход к настройке схем баз данных состоит в том, чтобы иметь .sql script, содержащий операторы CREATE TABLE IF NOT EXISTS и CREATE INDEX IF NOT EXISTS, которые могут быть выполнены с помощью sqlite3_exec или инструмента командной строки sqlite3. Мы не можем помещать ALTER TABLE в эти файлы script, потому что, если этот оператор терпит неудачу, ничего после его выполнения не будет.

Я хочу иметь определения таблиц в одном месте и не разбивать файлы .sql и .cpp. Есть ли способ написать обходной путь для ALTER TABLE ADD COLUMN IF NOT EXISTS в чистом SQLite SQL?

Ответы

Ответ 1

У меня есть метод 99% чистого SQL. Идея состоит в том, чтобы создать версию вашей схемы. Вы можете сделать это двумя способами:

  • Используйте команду прагмы 'user_version' ( PRAGMA user_version), чтобы сохранить добавочный номер для вашей версии схемы базы данных.

  • Сохраните ваш номер версии в вашей собственной определенной таблице.

Таким образом, когда программное обеспечение запущено, оно может проверить схему базы данных и, если необходимо, выполнить запрос ALTER TABLE, а затем увеличить сохраненную версию. Это гораздо лучше, чем пытаться делать всевозможные "слепые" обновления, особенно если ваша база данных растет и изменяется несколько раз за последние годы.

Ответ 2

Один способ - просто создать столбцы и уловить исключение/ошибку, возникающие, если столбец уже существует. При добавлении нескольких столбцов добавьте их в отдельные инструкции ALTER TABLE, чтобы один дубликат не мешал другим создавать.

С sqlite-net мы сделали что-то вроде этого. Это не идеально, поскольку мы не можем отличить повторяющиеся ошибки sqlite от других ошибок sqlite.

Dictionary<string, string> columnNameToAddColumnSql = new Dictionary<string, string>
{
    {
        "Column1",
        "ALTER TABLE MyTable ADD COLUMN Column1 INTEGER"
    },
    {
        "Column2",
        "ALTER TABLE MyTable ADD COLUMN Column2 TEXT"
    }
};

foreach (var pair in columnNameToAddColumnSql)
{
    string columnName = pair.Key;
    string sql = pair.Value;

    try
    {
        this.DB.ExecuteNonQuery(sql);
    }
    catch (System.Data.SQLite.SQLiteException e)
    {
        _log.Warn(e, string.Format("Failed to create column [{0}]. Most likely it already exists, which is fine.", columnName));
    }
}

Ответ 3

SQLite также поддерживает прагматический оператор "table_info", который возвращает по одной строке на столбец в таблице с именем столбца (и другой информацией о столбце). Вы можете использовать это в запросе для проверки отсутствующего столбца, а если нет, изменить таблицу.

PRAGMA table_info(foo_table_name)

http://www.sqlite.org/pragma.html#pragma_table_info

Ответ 4

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

try {
   db.execSQL("ALTER TABLE " + TABLE_NAME + " ADD COLUMN foo TEXT default null");
} catch (SQLiteException ex) {
   Log.w(TAG, "Altering " + TABLE_NAME + ": " + ex.getMessage());
}

Ответ 5

есть метод PRAGMA, это table_info (table_name), он возвращает всю информацию таблицы.

Вот реализация, как использовать его для проверки столбца существует или нет,

    public boolean isColumnExists (String table, String column) {
         boolean isExists = false
         Cursor cursor;
         try {           
            cursor = db.rawQuery("PRAGMA table_info("+ table +")", null);
            if (cursor != null) {
                while (cursor.moveToNext()) {
                    String name = cursor.getString(cursor.getColumnIndex("name"));
                    if (column.equalsIgnoreCase(name)) {
                        isExists = true;
                        break;
                    }
                }
            }

         } finally {
            if (cursor != null && !cursor.isClose()) 
               cursor.close();
         }
         return isExists;
    }

Вы также можете использовать этот запрос без использования цикла,

cursor = db.rawQuery("PRAGMA table_info("+ table +") where name = " + column, null);

Ответ 7

Я взял ответ выше в С#/.NET и переписал его для Qt/C++, не сильно изменив его, но я хотел оставить его здесь для любого, кто в будущем ищет ответ C++ 'ish'.

    bool MainWindow::isColumnExisting(QString &table, QString &columnName){

    QSqlQuery q;

    try {
        if(q.exec("PRAGMA table_info("+ table +")"))
            while (q.next()) {
                QString name = q.value("name").toString();     
                if (columnName.toLower() == name.toLower())
                    return true;
            }

    } catch(exception){
        return false;
    }
    return false;
}

Ответ 8

В качестве альтернативы вы можете использовать оператор CASE-WHEN TSQL в сочетании с pragma_table_info, чтобы узнать, существует ли столбец:

select case(CNT) 
    WHEN 0 then printf('not found')
    WHEN 1 then printf('found')
    END
FROM (SELECT COUNT(*) AS CNT FROM pragma_table_info('myTableName') WHERE name='columnToCheck')