ALTER TABLE ADD COLUMN IF NOT EXISTS dans SQLite

Question

Nous avons récemment eu besoin d'ajouter des colonnes à quelques-unes de nos tables de base de données SQLite existantes. Cela peut être fait avec ALTER TABLE ADD COLUMN . Bien entendu, si la table a déjà été modifiée, nous voulons la laisser telle quelle. Malheureusement, SQLite ne prend pas en charge la fonction IF NOT EXISTS clause sur ALTER TABLE .

Notre solution actuelle consiste à exécuter l'instruction ALTER TABLE et à ignorer les erreurs de type "nom de colonne en double", de la manière suivante cet exemple Python (mais en C++).

Cependant, notre approche habituelle de la mise en place de schémas de base de données consiste à disposer d'un script .sql contenant les éléments suivants CREATE TABLE IF NOT EXISTS y CREATE INDEX IF NOT EXISTS qui peuvent être exécutées à l'aide de sqlite3_exec ou le sqlite3 outil de ligne de commande. Nous ne pouvons pas mettre ALTER TABLE dans ces fichiers script car si cette instruction échoue, tout ce qui suit ne sera pas exécuté.

Je souhaite que les définitions des tables soient regroupées en un seul endroit et ne soient pas réparties entre les fichiers .sql et .cpp. Existe-t-il un moyen d'écrire une solution de contournement pour ALTER TABLE ADD COLUMN IF NOT EXISTS en SQLite pur ?

Answer 1

J'ai une méthode SQL pure à 99%. L'idée est de versionner votre schéma. Vous pouvez le faire de deux manières :

• Utilisez la commande pragma 'user_version' ( PRAGMA user_version ) pour stocker un numéro incrémentiel pour la version de votre schéma de base de données.

• Enregistrez votre numéro de version dans votre propre tableau.

De cette manière, lorsque le logiciel est lancé, il peut vérifier le schéma de la base de données et, si nécessaire, exécuter votre ALTER TABLE puis incrémente la version stockée. Cette méthode est de loin préférable à la tentative de mise à jour "à l'aveugle", surtout si votre base de données s'enrichit et change plusieurs fois au fil des ans.

Answer 2

Si vous faites cela dans une déclaration de mise à jour de la base de données, le plus simple est peut-être d'attraper l'exception levée si vous essayez d'ajouter un champ qui peut déjà exister.

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

Answer 3

SQLite supporte également une instruction pragma appelée "table_info" qui renvoie une ligne par colonne dans une table avec le nom de la colonne (et d'autres informations sur la colonne). Vous pouvez l'utiliser dans une requête pour vérifier la colonne manquante et, si elle n'est pas présente, modifier la table.

PRAGMA table_info(foo_table_name)

Exemple de sortie :

cid

nom

type

non nul

dflt_value

pk

0

id

entier

0

nul

1

1

type

texte

0

nul

0

2

données

json

0

nul

0

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

Answer 4

Une solution de contournement consiste à créer les colonnes et à détecter l'exception/l'erreur qui survient si la colonne existe déjà. Lorsque vous ajoutez plusieurs colonnes, ajoutez-les dans des instructions ALTER TABLE distinctes afin qu'un duplicata n'empêche pas la création des autres.

Con sqlite-net Nous avons procédé de la manière suivante. Ce n'est pas parfait, car nous ne pouvons pas distinguer les erreurs sqlite dupliquées des autres erreurs 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));
    }
}

Answer 5

Pour ceux qui veulent utiliser pragma table_info() Le résultat de l'enquête de la Commission européenne s'inscrit dans le cadre d'une enquête SQL plus large.

select count(*) from
pragma_table_info('<table_name>')
where name='<column_name>';

L'essentiel est d'utiliser pragma_table_info('<table_name>') au lieu de pragma table_info('<table_name>') .

---

Cette réponse est inspirée de la réponse de @Robert Hawkey. La raison pour laquelle je la poste en tant que nouvelle réponse est que je n'ai pas assez de réputation pour la poster en tant que commentaire.