ALTER TABELA DODAJ KOLUMNĘ, JEŚLI NIE ISTNIEJE w SQLite


89

Niedawno mieliśmy potrzebę dodania kolumn do kilku naszych istniejących tabel bazy danych SQLite. Można to zrobić za pomocą ALTER TABLE ADD COLUMN. Oczywiście, jeśli tabela została już zmieniona, chcemy ją zostawić w spokoju. Niestety, SQLite nie obsługuje IF NOT EXISTSklauzuli on ALTER TABLE.

Nasze obecne obejście polega na wykonaniu instrukcji ALTER TABLE i zignorowaniu wszelkich błędów „zduplikowanej nazwy kolumny”, tak jak w tym przykładzie w Pythonie (ale w C ++).

Jednak naszym zwykłym podejściem do konfigurowania schematów bazy danych jest posiadanie skryptu .sql zawierającego instrukcje CREATE TABLE IF NOT EXISTSi CREATE INDEX IF NOT EXISTS, które można wykonać za pomocą narzędzia sqlite3_execlub sqlite3narzędzia wiersza poleceń. Nie możemy wstawić ALTER TABLEtych plików skryptów, ponieważ jeśli ta instrukcja zawiedzie, nic po niej nie zostanie wykonane.

Chcę, aby definicje tabel były w jednym miejscu i nie były dzielone między pliki .sql i .cpp. Czy istnieje sposób na napisanie obejścia ALTER TABLE ADD COLUMN IF NOT EXISTSw czystym SQLite SQL?

Odpowiedzi:


64

Mam w 99% czystą metodę SQL. Chodzi o to, aby wersjonować swój schemat. Możesz to zrobić na dwa sposoby:

  • Polecenie pragma „user_version” ( PRAGMA user_version) służy do przechowywania przyrostowego numeru wersji schematu bazy danych.

  • Przechowuj numer wersji we własnej zdefiniowanej tabeli.

W ten sposób po uruchomieniu oprogramowanie może sprawdzić schemat bazy danych i, jeśli to konieczne, uruchomić ALTER TABLEzapytanie, a następnie zwiększyć przechowywaną wersję. Jest to o wiele lepsze niż próbowanie różnych aktualizacji „na ślepo”, zwłaszcza jeśli baza danych rośnie i zmienia się kilka razy na przestrzeni lat.


7
Jaka jest wartość początkowa user_version? Zakładam zero, ale byłoby miło zobaczyć to udokumentowane.
Craig McQueen

Nawet z tym, czy można to zrobić w czystym SQL, skoro sqlite nie obsługuje IFi ALTER TABLEnie ma warunku? Co masz na myśli mówiąc „99% czystego SQL”?
Craig McQueen

1
@CraigMcQueen Jeśli chodzi o początkową wartość user_version, wydaje się, że wynosi 0, ale tak naprawdę jest to wartość zdefiniowana przez użytkownika, więc możesz utworzyć własną wartość początkową.
MPelletier

7
Pytanie o user_versionwartość początkową jest istotne, gdy masz istniejącą bazę danych i nigdy wcześniej jej nie user_versionużywałeś, ale chcesz zacząć jej używać, więc musisz założyć, że sqlite ustawił ją na określoną wartość początkową.
Craig McQueen

1
@CraigMcQueen Zgadzam się, ale wygląda na to, że nie jest to udokumentowane.
MPelletier

30

Jednym obejściem jest utworzenie kolumn i wychwycenie wyjątku / błędu, który pojawia się, jeśli kolumna już istnieje. Dodając wiele kolumn, należy je dodawać w oddzielnych instrukcjach ALTER TABLE, aby jeden duplikat nie przeszkadzał w utworzeniu pozostałych.

Z sqlite-net zrobiliśmy coś takiego. Nie jest doskonały, ponieważ nie możemy odróżnić powielonych błędów sqlite od innych błędów 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));
    }
}

28

SQLite obsługuje również instrukcję pragma o nazwie „table_info”, która zwraca jeden wiersz na kolumnę w tabeli wraz z nazwą kolumny (i innymi informacjami o kolumnie). Możesz użyć tego w zapytaniu, aby sprawdzić brakującą kolumnę, a jeśli nie, zmienić tabelę.

PRAGMA table_info(foo_table_name)

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


30
Twoja odpowiedź byłaby znacznie doskonalsza, gdybyś zamiast zwykłego linku podał kod, za pomocą którego można zakończyć wyszukiwanie.
Michael Alan Huff,

PRAGMA table_info (nazwa_tabeli). To polecenie wyświetli każdą kolumnę nazwa_tabeli jako wiersz wyniku. Na podstawie tego wyniku możesz określić, czy kolumna istniała, czy nie.
Hao Nguyen

2
Czy istnieje sposób, aby to zrobić, łącząc pragmę w części większej instrukcji SQL, tak aby kolumna była dodawana, jeśli nie istnieje, ale w przeciwnym razie nie istnieje, tylko w jednym zapytaniu?
Michael

1
@Michał. O ile wiem, nie możesz. Problem z komendą PRAGMA polega na tym, że nie możesz o nią zapytać. komenda nie przedstawia danych do silnika SQL, zwraca wyniki bezpośrednio
Kowlown

1
Czy to nie stwarza warunków wyścigu? Powiedzmy, że sprawdzam nazwy kolumn, widzę, że brakuje mojej kolumny, ale w międzyczasie inny proces dodaje kolumnę. Następnie spróbuję dodać kolumnę, ale otrzymam błąd, ponieważ już istnieje. Myślę, że mam najpierw zablokować bazę danych, czy coś? Jestem noobem do sqlite, boję się :).
Ben Farmer,

25

Jeśli robisz to w instrukcji aktualizacji bazy danych, być może najprostszym sposobem jest po prostu przechwycenie zgłoszonego wyjątku, jeśli próbujesz dodać pole, które może już istnieć.

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

2
Nie lubię programowania w stylu wyjątków, ale jest on niesamowicie czysty. Może trochę mnie kołysałeś.
Stephen J

Też mi się to nie podoba, ale C ++ to najbardziej wyjątkowy język programowania w historii. Więc myślę, że nadal można to postrzegać jako „ważne”.
potężny

Mój przypadek użycia dla SQLite = Nie chcę robić mnóstwa dodatkowego kodowania czegoś głupiego, prostego / jeden liner w innych językach (MSSQL). Dobra odpowiedź ... chociaż jest to „programowanie w stylu wyjątków”, jest w funkcji aktualizacji / izolowane, więc przypuszczam, że jest to dopuszczalne.
maplemale

Podczas gdy innym się to nie podoba, myślę, że to najlepsze rozwiązanie lol
Adam Varhegyi

13

threre to metoda PRAGMA to table_info (nazwa_tabeli), zwraca wszystkie informacje z tabeli.

Oto implementacja, jak go używać, aby sprawdzić, czy kolumna istnieje, czy nie,

    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;
    }

Możesz również użyć tego zapytania bez używania pętli,

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

Kursor kursora = db.rawQuery ("wybierz * z tableName", null); kolumny = kursor.getColumnNames ();
Vahe Gharibyan

1
Chyba zapomniałeś zamknąć kursora :-)
Pecana

@VaheGharibyan, więc po prostu wybierzesz wszystko w swojej bazie danych, aby uzyskać nazwy kolumn ?! Po prostu mówisz we give no shit about performance:)).
Farid

Uwaga, ostatnie zapytanie jest nieprawidłowe. Właściwe zapytanie to: SELECT * FROM pragma_table_info(...)(zwróć uwagę na SELECT i podkreślenie między pragmą a informacjami o tabeli). Nie jestem pewien, w której wersji faktycznie go dodali, nie działało na 3.16.0, ale działa na 3.22.0.
PressingOnAlways

3

Dla tych, którzy chcą użyć pragma table_info()wyniku jako części większego SQL.

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

Kluczową częścią jest użycie pragma_table_info('<table_name>')zamiast pragma table_info('<table_name>').


Ta odpowiedź jest inspirowana odpowiedzią @Robert Hawkey. Powodem, dla którego publikuję to jako nową odpowiedź, jest to, że nie mam wystarczającej reputacji, aby opublikować ją jako komentarz.


1

Wymyślam to zapytanie

SELECT CASE (SELECT count(*) FROM pragma_table_info(''product'') c WHERE c.name = ''purchaseCopy'') WHEN 0 THEN ALTER TABLE product ADD purchaseCopy BLOB END
  • Zapytanie wewnętrzne zwróci 0 lub 1, jeśli kolumna istnieje.
  • Na podstawie wyniku zmień kolumnę

code = Error (1), message = System.Data.SQLite.SQLiteException (0x800007BF): błąd logiki SQL w pobliżu „ALTER”: błąd składni w System.Data.SQLite.SQLite3.Prepare
イ ン コ グ ニ ト ア レ ク セ イ


0

Wziąłem powyższą odpowiedź w C # / .Net i przepisałem ją na Qt / C ++, nie zmieniając się zbytnio, ale chciałem zostawić ją tutaj dla każdego, kto w przyszłości szuka odpowiedzi „ish” w C ++.

    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;
}

0

Alternatywnie możesz użyć instrukcji CASE-WHEN TSQL w połączeniu z pragma_table_info, aby dowiedzieć się, czy kolumna istnieje:

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') 

tutaj jak zmienić tabelę? kiedy jest zgodna nazwa kolumny?
user2700767

0

Oto moje rozwiązanie, ale w pythonie (próbowałem i nie udało mi się znaleźć żadnego posta na temat związany z pythonem):

# modify table for legacy version which did not have leave type and leave time columns of rings3 table.
sql = 'PRAGMA table_info(rings3)' # get table info. returns an array of columns.
result = inquire (sql) # call homemade function to execute the inquiry
if len(result)<= 6: # if there are not enough columns add the leave type and leave time columns
    sql = 'ALTER table rings3 ADD COLUMN leave_type varchar'
    commit(sql) # call homemade function to execute sql
    sql = 'ALTER table rings3 ADD COLUMN leave_time varchar'
    commit(sql)

Użyłem PRAGMA, aby uzyskać informacje o tabeli. Zwraca wielowymiarową tablicę pełną informacji o kolumnach - jedną tablicę na kolumnę. Liczę liczbę tablic, aby uzyskać liczbę kolumn. Jeśli nie ma wystarczającej liczby kolumn, dodaję kolumny za pomocą polecenia ALTER TABLE.


0

Wszystkie te odpowiedzi są w porządku, jeśli wykonujesz jedną linię na raz. Jednak pierwotnym pytaniem było wprowadzenie skryptu sql, który byłby wykonywany przez pojedyncze wykonanie bazy danych, a wszystkie rozwiązania (takie jak sprawdzenie, czy kolumna jest tam z wyprzedzeniem) wymagałyby, aby program wykonawczy miał wiedzę o tym, jakie tabele i kolumny są zmieniane / dodawane lub wstępnie przetwarzają i analizują skrypt wejściowy w celu określenia tych informacji. Zazwyczaj nie będziesz uruchamiać tego w czasie rzeczywistym lub często. Tak więc pomysł wyłapania wyjątku jest akceptowalny, a następnie przejść dalej. Na tym polega problem ... jak iść dalej. Na szczęście komunikat o błędzie zawiera wszystkie informacje potrzebne do tego. Chodzi o to, aby wykonać sql, jeśli wyjątek od wywołania alter table, możemy znaleźć wiersz tablicy alter w sql, a następnie zwrócić pozostałe wiersze i wykonać, aż się powiedzie lub nie będzie można znaleźć więcej pasujących wierszy tabeli alter. Oto przykładowy kod, w którym mamy skrypty sql w tablicy. Iterujemy tablicę wykonującą każdy skrypt. Wzywamy to dwukrotnie, aby polecenie alter table zakończyło się niepowodzeniem, ale program się powiódł, ponieważ usuwamy polecenie alter table z sql i ponownie wykonujemy zaktualizowany kod.

#!/bin/sh
# the next line restarts using wish \

exec /opt/usr8.6.3/bin/tclsh8.6  "$0" ${1+"$@"}
foreach pkg {sqlite3 } {
    if { [ catch {package require {*}$pkg } err ] != 0 } {
    puts stderr "Unable to find package $pkg\n$err\n ... adjust your auto_path!";
    }
}
array set sqlArray {
    1 {
    CREATE TABLE IF NOT EXISTS Notes (
                      id INTEGER PRIMARY KEY AUTOINCREMENT,
                      name text,
                      note text,
                      createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
                      updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) 
                      );
    CREATE TABLE IF NOT EXISTS Version (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        version text,
                        createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
                        updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) )
                        );
    INSERT INTO Version(version) values('1.0');
    }
    2 {
    CREATE TABLE IF NOT EXISTS Tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name text,
        tag text,
        createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
        updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) 
        );
    ALTER TABLE Notes ADD COLUMN dump text;
    INSERT INTO Version(version) values('2.0');
    }
    3 {
    ALTER TABLE Version ADD COLUMN sql text;
    INSERT INTO Version(version) values('3.0');
    }
}

# create db command , use in memory database for demonstration purposes
sqlite3 db :memory:

proc createSchema { sqlArray } {
    upvar $sqlArray sql
    # execute each sql script in order 
    foreach version [lsort -integer [array names sql ] ] {
    set cmd $sql($version)
    set ok 0
    while { !$ok && [string length $cmd ] } {  
        try {
        db eval $cmd
        set ok 1  ;   # it succeeded if we get here
        } on error { err backtrace } {
        if { [regexp {duplicate column name: ([a-zA-Z0-9])} [string trim $err ] match columnname ] } {
            puts "Error:  $err ... trying again" 
            set cmd [removeAlterTable $cmd $columnname ]
        } else {
            throw DBERROR "$err\n$backtrace"
        }
        }
    }
    }
}
# return sqltext with alter table command with column name removed
# if no matching alter table line found or result is no lines then
# returns ""
proc removeAlterTable { sqltext columnname } {
    set mode skip
    set result [list]
    foreach line [split $sqltext \n ] {
    if { [string first "alter table" [string tolower [string trim $line] ] ] >= 0 } {
        if { [string first $columnname $line ] } {
        set mode add
        continue;
        }
    }
    if { $mode eq "add" } {
        lappend result $line
    }
    }
    if { $mode eq "skip" } {
    puts stderr "Unable to find matching alter table line"
    return ""
    } elseif { [llength $result ] }  { 
    return [ join $result \n ]
    } else {
    return ""
    }
}
               
proc printSchema { } {
    db eval { select * from sqlite_master } x {
    puts "Table: $x(tbl_name)"
    puts "$x(sql)"
    puts "-------------"
    }
}
createSchema sqlArray
printSchema
# run again to see if we get alter table errors 
createSchema sqlArray
printSchema

oczekiwany wynik

Table: Notes
CREATE TABLE Notes (
                      id INTEGER PRIMARY KEY AUTOINCREMENT,
                      name text,
                      note text,
                      createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
                      updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) 
                      , dump text)
-------------
Table: sqlite_sequence
CREATE TABLE sqlite_sequence(name,seq)
-------------
Table: Version
CREATE TABLE Version (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        version text,
                        createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
                        updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) )
                        , sql text)
-------------
Table: Tags
CREATE TABLE Tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name text,
        tag text,
        createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
        updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) 
        )
-------------
Error:  duplicate column name: dump ... trying again
Error:  duplicate column name: sql ... trying again
Table: Notes
CREATE TABLE Notes (
                      id INTEGER PRIMARY KEY AUTOINCREMENT,
                      name text,
                      note text,
                      createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
                      updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) 
                      , dump text)
-------------
Table: sqlite_sequence
CREATE TABLE sqlite_sequence(name,seq)
-------------
Table: Version
CREATE TABLE Version (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        version text,
                        createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
                        updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) )
                        , sql text)
-------------
Table: Tags
CREATE TABLE Tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name text,
        tag text,
        createdDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) ,
        updatedDate integer(4) DEFAULT ( cast( strftime('%s', 'now') as int ) ) 
        )
-------------

0
select * from sqlite_master where type = 'table' and tbl_name = 'TableName' and sql like '%ColumnName%'

Logic: sql kolumna w sqlite_master zawiera definicję tabeli, więc z pewnością zawiera ciąg z nazwą kolumny.

Gdy szukasz podciągu, ma on swoje oczywiste ograniczenia. Dlatego sugerowałbym użycie jeszcze bardziej restrykcyjnego podłańcucha w ColumnName, na przykład coś takiego (podlega testom, ponieważ znak `` '' nie zawsze występuje):

select * from sqlite_master where type = 'table' and tbl_name = 'MyTable' and sql like '%`MyColumn` TEXT%'

0

Rozwiązuję to w 2 zapytaniach. To jest mój skrypt Unity3D korzystający z System.Data.SQLite.

IDbCommand command = dbConnection.CreateCommand();
            command.CommandText = @"SELECT count(*) FROM pragma_table_info('Candidat') c WHERE c.name = 'BirthPlace'";
            IDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    if (int.TryParse(reader[0].ToString(), out int result))
                    {
                        if (result == 0)
                        {
                            command = dbConnection.CreateCommand();
                            command.CommandText = @"ALTER TABLE Candidat ADD COLUMN BirthPlace VARCHAR";
                            command.ExecuteNonQuery();
                            command.Dispose();
                        }
                    }
                }
                catch { throw; }
            }
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.