SQLite: ALTER TABLE if not exists

[Kratons]

Schönen Tag liebe Community,

Ich habe wiedermals ein Problem bei dem ich selbst nicht weiterkomme...
Untzwa möchte ich gerne eine Tabelle in SQLite schritt für schritt überprüfen und gegbenenfalls anpassen,
dies soll so geschehen damit ich im nachhinein einfach ändreungen an der tabellenstruktur vornemen kann ohne die bereits vorhandenen daten zu verlieren.

Da SQLite allerdings die "if not exists" Clause bei Alter Table nicht unterstützt muss ich mich da jetzt irgentwie drumrum schreiben.
Ich bin schonmal dazu gekommen mit PRAGMA table_info die daten abfragen zu wollen, dies funktionirt jedoch nicht und mir wird immerzu nur ein:

Syntax error near "("

ausgegeben...
(Dem neben dem table_info!)

Code:

 dbC.Open; //Datenbank Erstellen falls nicht vorhanden.
  // erstelle Tabelle User falls nicht vorhanden
  dbQ.SQL.Text:='CREATE TABLE if not exists ''user'' (id varchar(36) primary key)';
  dbQ.ExecSQL; dbT.Commit;
  //erstelle spalte name falls nicht vorhanden
  dbQ.SQL.Text:='SELECT name FROM PRAGMA table_info (user)';
  dbQ.ExecSQL; dbT.Commit;
  if dbQ.EOF = True then
  begin
  dbQ.SQL.Text:='ALTER TABLE user ADD COLUMN name varchar(20) unique';
  dbQ.ExecSQL; dbT.Commit;
  end;

danach würde nnoch mehrere spalten kommen, diese sind dan ja aber einfach Copy/Paste....

Ich habs auchschon mit

Code:

  dbQ.SQL.Text:='SELECT name FROM PRAGMA table_info(user)';

und

Code:

  dbQ.SQL.Text:='SELECT name FROM PRAGMA table_info (''user'')';

Probiert leider ohne Ergebniss.

Schonmals vielen Dank für eure Antworten.

[Sir Rufo]

Das geht nur mit PRAGMA table_info( "user" ); . In einen SubSelect kannst du das nicht packen.

[Kratons]

ahh ok danke, das erklähr warum da nen Error kamm,
aber wenn ich das Pragma nur alleinstehend in ein SQL statement packen kann wie bekomme ich dan raus ob die von mir gesuchte spalte dabei ist?

[Sir Rufo]

Im Resultset nachschauen? Geht z.B. mit TDataSet.Locate.

Delphi-Quellcode:

dbQ.SQL.Text:='PRAGMA table_info ("user")';
dbQ.Open;
if not dbQ.Locate( 
  { KeyFields } 'name', 
  { KeyValues } 'foo', 
  { Options } [] ) 
then
  // Feld erzeugen

[Sir Rufo]

Generell würde ich da aber auch nicht zu viel Aufwand betreiben. In der Regel reicht es eigentlich aus entsprechende Upgrade-Skripte vorzuhalten, die man dann auf die Datenbank anwendet.

SQL-Code:

--
-- Upgrade from Version 0 (empty database)
--
PRAGMA foreign_keys = false;
PRAGMA ignore_check_constraints = false;

CREATE TABLE "user" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
);

PRAGMA ignore_check_constraints = true;
PRAGMA foreign_keys = true;

-- set new version
PRAGMA user_version = 1;

SQL-Code:

--
-- Upgrade from Version 1 (empty database)
--
PRAGMA foreign_keys = false;
PRAGMA ignore_check_constraints = false;

ALTER TABLE "user" ADD COLUMN "username" VARCHAR(30); 
ALTER TABLE "user" ADD COLUMN "password" VARCHAR(30); 

PRAGMA ignore_check_constraints = true;
PRAGMA foreign_keys = true;
-- set new version
PRAGMA user_version = 2;

Über das Statement PRAGMA user_version bekommt man den Wert (Integer) von der Datenbank und sich dann einfach das passende Upgrade-Skript heraus.

Dabei muss man dann aber nicht ständig alle Skripts irgendwie anpassen, sondern kann die Versionen auch einfach "durchlaufen", das spart jede Menge Arbeit und ist auch einfacher in der Handhabung.

Delphi-Quellcode:

procedure UpgradeDatabase( ToVersion : Integer; const ScriptDir : string );
var
  LLast,
  LCurrentVersion : Integer;
  LScript : string;
begin
  LLast := -1;
  repeat
    // "PRAGMA user_version" ausführen und den Wert lesen
    LCurrentVersion := GetDatabaseVersion;
    // Endlosschleife erkennen
    if LLast = LCurrent then
      raise Exception.CreateFmt( 'Upgrade-Script v%d -> v%d is missing!', [LCurrentVersion, ToVersion] );
    if LCurrentVersion <> ToVersion then
    begin
      // Upgrade-Skript laden
      LScript := LoadUpgradeScript( LCurrentVersion );
      // Upgrade-Skript ausführen (in einer Transaktion, soll Exception bei Skriptfehler werfen)
      DatabaseExecuteScript( LScript );
    end;
    LLast := LCurrentVersion;
  // solange bis die Ziel-Version erreicht wurde
  until LCurrentVersion = ToVersion;
end;

[Kratons]

Hi, danke nochmals sir Rufo,

hier meine Lösung: (hatte deinen letzten post noch nicht gelesen..)

Code:

  dbC.Open; //Datenbank Erstellen falls nicht vorhanden.
  // erstelle Tabelle **User** falls nicht vorhanden
  dbQ.SQL.Text:='CREATE TABLE if not exists ''user'' (id varchar(36) primary key, name varchar(20) unique, pw varchar(100), rights Integer)';
  dbQ.ExecSQL; dbT.Commit;
  dbQ.Close;
  //erstelle spalte name falls nicht vorhanden
  dbQ.SQL.Text:='PRAGMA table_info (''user'')';
  dbQ.Open;
  if not dbQ.Locate('name','name',[]) then
  begin
    dbQ.SQL.Text:='ALTER TABLE user ADD COLUMN name varchar(20)';
    dbQ.ExecSQL; dbT.Commit;
    dbQ.SQL .Text:='CREATE unique INDEX iusername ON user (name)';
    dbQ.ExecSQL; dbT.Commit;
  end;
  dbQ.Close;
  //erstelle spalte pw falls nicht vorhanden
  dbQ.SQL.Text:='PRAGMA table_info (''user'')';
  dbQ.Open;
  if not dbQ.Locate('name','pw',[]) then
  begin
    dbQ.SQL.Text:='ALTER TABLE user ADD COLUMN pw varchar(100)';
    dbQ.ExecSQL; dbT.Commit;
  end;
  dbQ.Close;
  //erstelle spalte rights falls nicht vorhanden
  dbQ.SQL.Text:='PRAGMA table_info (''user'')';
  dbQ.Open;
  if not dbQ.Locate('name','rights',[]) then
  begin
    dbQ.SQL.Text:='ALTER TABLE user ADD COLUMN rights Integer';
    dbQ.ExecSQL; dbT.Commit;
  end;
  dbQ.Close;

Hoffe das hilft jenem der das selbe Problem hat.
Dieser Code produziert exakt die Tabellen die ich brauch, bzw. sorgt dafür das nei ein der spalten fehlt.

Trotzdem vieleb dan kan sir Rufo für seine hilfe.

[Sherlock]

Was passiert denn schlimmes, wenn die Tabelle/Spalte bereits existiert?

Oracle gibt einen Fehler aus und macht weiter, ist das bei SQLite etwa anders?

Sherlock

[mikhal]

Schau dir mal in der Hilfe das Stichwort "Data.DB.TDataSet.GetFieldNames" an, vielleicht hilft dir das weiter.

Grüße
Mikhal

[Kratons]

Zitat von Sherlock:

Was passiert denn schlimmes, wenn die Tabelle/Spalte bereits existiert?

Oracle gibt einen Fehler aus und macht weiter, ist das bei SQLite etwa anders?

Sherlock

SQlite gibt ne Fehlermeldung aus und hat dann 2 knöpfe "Abort" und "continue"(at own risk of data coruption)

ich wollte halt verhindern das der Kunde hinterher bei jedem Programmstart erstmal 545618418768761264 mal continue drücken muss ...

Zitat von mikhal:

Schau dir mal in der Hilfe das Stichwort "Data.DB.TDataSet.GetFieldNames" an, vielleicht hilft dir das weiter.

Grüße
Mikhal

Vielen dank, für den vorschlag,
werde es mir anguckenwenn ich zeit hab...
(habe jetzt ja schon ne brauchbare lösung)