SQLLite Datensatz hinzufügen, wenn nicht vorhanden

[Peter666]

Hi,

ich wollte gerne mit so wenig wie möglichem Aufwand in folgende Datenbank:

Code:

CREATE TABLE [Cities] (
[ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Province] INTEGER  NOT NULL,
[Name] VARCHAR(256)  NOT NULL,
[Latitude] FLOAT  NULL,
[Longitude] FLOAT  NULL
);

CREATE TABLE [Countries] (
[ID] INTEGER  NOT NULL PRIMARY KEY,
[Region] INTEGER  NOT NULL,
[Name] VARCHAR(256)  NOT NULL
);

CREATE TABLE [Location] (
[ID] INTEGER  PRIMARY KEY NOT NULL,
[City] INTEGER  NOT NULL,
[Radio] INTEGER  NOT NULL,
[Frequency] FLOAT  NULL,
[Band] INTEGER  NULL
);

CREATE TABLE [Province] (
[ID] INTEGER  NOT NULL PRIMARY KEY,
[Country] INTEGER  NOT NULL,
[Name] VARCHAR(256)  NOT NULL
);

CREATE TABLE [Stations] (
[ID] INTEGER  PRIMARY KEY NOT NULL,
[Genre] INTEGER  NULL,
[Name] VARCHAR(256)  NOT NULL,
[URL] VARCHAR(256)  NOT NULL,
[Logo] VARCHAR(256)  NULL
);

meine Datensätze einfügen und wenn möglich mittels einer einzigen bzw. so wenig wie möglichen SQL Anweisung(en). Dabei sollen keine Dupletten auftreten.

Delphi-Quellcode:

procedure AddToDatabase(const CountryRegion: Integer;
      const CountryName: String; const ProvinceName: String;
      const CityName: String; const CityLatitude,CityLongitude: Single;
      const RadioName, RadioUrl, RadioLogoUrl: String;
      const RadioGenre: Integer; const RadioBand: Integer;
      const RadioFrequency: Single);

Bis jetzt habe ich immer erst eine SQL Abfrage gemacht ob der Name des Landes in dem Datensatz ist, wenn nicht dann geschrieben. Die ID habe ich genommen und bin in die Cities Tabelle gegangen, hab das selbe mit dem Stadteintrag gemacht, dann in der Stations hinzugefügt und zuletzt den Eintrag in Location. Das sind mir aber ein bisschen zu viele Einträge, deswegen würde ich das mit so wenig wie möglich Abfragen realisieren. Hat jemand eine Idee, wie die Abfrage dafür aussehen kann? Das letzte mal das ich mit SQL gearbeitet habe ist etwas her und damals hab ich die Queries einfach in Office erstellt und in Delphi eingefügt.

[AlexII]

Ist aber sehr umständlich... UNIQUE übernimmt dir die Arbeit. Damit wird der Wert nur ein Mal gespeichert!

Delphi-Quellcode:

CREATE TABLE [tbland] (
  [id_land] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, 
  [land] TEXT NOT NULL UNIQUE)

[Klaus01]

Hallo,

vielleicht hilft Dir upsert oder insert or replace weiter.

Grüße
Klaus

[Sir Rufo]

Ein INSERT OR REPLACE ist immer mit Vorsicht zu genießen, denn bei einem REPLACE wird die alte Zeile gelöscht und mit den übergebenen Daten wieder eingefügt.

Da könnte es besser sein ein ON CONFLICT IGNORE zu verwenden. Allerdings verbergen sich auch hier Fallstricke. Bei MySQL (ON DUPLICATE KEY UPDATE) wird z.B. trotz Update der AutoInc-Wert erhöht.

[Peter666]

Danke, ich hab die Einträge wie folgt geändert:

Code:

CREATE TABLE [Cities] (
[ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[Province] INTEGER  NOT NULL,
[Name] VARCHAR(256)  UNIQUE NOT NULL,
[Latitude] FLOAT  NULL,
[Longitude] FLOAT  NULL
);

CREATE TABLE [Countries] (
[ID] INTEGER  PRIMARY KEY NOT NULL,
[Region] INTEGER  NOT NULL,
[Name] VARCHAR(256)  UNIQUE NOT NULL
);

CREATE TABLE [Location] (
[ID] INTEGER  PRIMARY KEY NOT NULL,
[City] INTEGER  NOT NULL,
[Radio] INTEGER UNIQUE NOT NULL,
[Frequency] FLOAT  NULL,
[Band] INTEGER  NULL
);

CREATE TABLE [Province] (
[ID] INTEGER  PRIMARY KEY NOT NULL,
[Country] INTEGER  NOT NULL,
[Name] VARCHAR(256)  UNIQUE NOT NULL
);

CREATE TABLE [Stations] (
[ID] INTEGER  PRIMARY KEY NOT NULL,
[Genre] INTEGER  NULL,
[Name] VARCHAR(256)  NOT NULL,
[URL] VARCHAR(256)  UNIQUE NOT NULL,
[Logo] VARCHAR(256)  NULL
);

Bei Stations habe ich nur die URL als Unique eingetragen, da in diversen Regionen die Sendernamen mitunter gleich sind.

[AlexII]

Dann würde ich noch folgendes machen, das ganze in die try except setzen:

Delphi-Quellcode:

  try
    // hier die ganzen SQL Abfragen 
  except
    on E: Exception do MessageDlg('Fehler! Die Abfrage konnte nicht durchgeführt werden!'
    + sLineBreak + sLineBreak + E.ClassName + sLineBreak + E.Message, mtError, [mbOk], 0);
  end;

In der "E.Message" wird angezeigt was schief gelaufen ist. Falls ein vorhandener Eintrag hinzugefügt wird, wird in der "E.Message" die Meldung stehen, dass der Wert bereits vorhanden ist. :thumb:

[Sir Rufo]

@AlexII

Erklär doch bitte mal was passiert, wenn du das

Delphi-Quellcode:

try except

nicht verwendest und eine Exception auftritt.

[AlexII]

Zitat:

Zitat von Sir Rufo (Beitrag 1285889)

@AlexII

Erklär doch bitte mal was passiert, wenn du das

Delphi-Quellcode:

try except

nicht verwendest und eine Exception auftritt.

Es wird ein Fehler angezeigt, das auf jeden Fall denke ich, aber ich weiß nicht was das für eine Fehlermeldung sein wird. Irgendwas mit "DB error" oder so, womit man nichts anfangen kann. Oder irre ich mich?

Werde ich aber testen! :thumb:

[Sir Rufo]

Zitat:

Zitat von AlexII (Beitrag 1285892)

Zitat:

Zitat von Sir Rufo (Beitrag 1285889)

@AlexII

Erklär doch bitte mal was passiert, wenn du das

Delphi-Quellcode:

try except

nicht verwendest und eine Exception auftritt.

Es wird ein Fehler angezeigt, das auf jeden Fall denke ich, aber ich weiß nicht was das für eine Fehlermeldung sein wird. Irgendwas mit "DB error" oder so, womit man nichts anfangen kann. Oder irre ich mich?

Da kommt also ein Dialog mit einer Fehlermeldung und die Verarbeitung wird abgebrochen.
Mit deiner Variante kommt ein Dialog mit einer angepassten Fehlermeldung und die Verarbeitung wird hinter dem

Delphi-Quellcode:

try except

Block fortgesetzt!

Um Fehlermeldungen umzubenennen, bzw. benutzerfreundlich zu formulieren nimmt man

Delphi-Quellcode:

try

except
  on E: Exception do
    raise Exception.Create( 'Hier ist was falsch' );
end;

Wobei es sinnvoll ist nur auf spezielle Exceptions so zu reagieren und andere einfach durchlaufen zu lassen.

Das mal durchlesen: http://docwiki.embarcadero.com/RADSt.../de/Exceptions

[Peter666]

Mit der Unique Eigenschaft vereinfacht sich das ganze.

So kann ich z.B. folgendes machen:

Delphi-Quellcode:

with Query.SQL do
  begin
   Add('INSERT OR REPLACE INTO Countries (Region, Name)');
   Add(format('VALUES (%d,"%s");',[Integer(CountryRegion),CountryName]));
  end;

Wie kann ich mir die aktualisierte Zeile anzeigen lassen?

Peter

[mkinzler]

Besser Parameter verwenden

Query.SQL.Text := 'INSERT OR REPLACE INTO Countries (Region, Name) VALUES ( :region, :name);';

...

Delphi-Quellcode:

Query.SQL.ParamByName('region').Value/asInteger := CountryRegion;

oder

Delphi-Quellcode:

Query.SQL.Params[0].Value/asInteger := CountryRegion;

Zitat:

Wie kann ich mir die aktualisierte Zeile anzeigen lassen?

Wie meinst Du das? Den wert nach dem Update?

[Peter666]

Danke, genau ich wollte den Geschriebenen bzw. Aktualisierten Wert in der Ausgabe haben. Sonst müsste ich ja ein

Code:

SELECT last_insert_rowid();

als separates Query vornehmen.
Am liebsten wäre mir, wenn ich das INSERT OR REPLACE durch ein INSERT ersetzen könnte. In dem Fall bekomme ich aber ja nicht den last_insert_rowid() geliefert, sofern der Eintrag schon vorhanden ist.

Peter

[mkinzler]

Da SQLite kein RETURNING kennt ( wie z.B. FireBird), wirst Du den Datensatz neu Laden müsssen.

[Sir Rufo]

Ich würde mir das Leben nicht so schwer machen. Das Beispiel ist zwar für FireDAC, das Konzept sollte sich aber problemlos auf alle anderen DB-Frameworks umsetzen lassen.

Delphi-Quellcode:

unit Unit1;

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Stan.Param,
  FireDAC.DatS, FireDAC.DApt.Intf, FireDAC.DApt, Data.DB, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client;

const
  SQL_QUERY_MAX = 1;
  SQL_QUERY: array [0 .. SQL_QUERY_MAX] of string = (
    {0} 'SELECT id FROM foo WHERE val=:val',
    {1} 'INSERT INTO foo (val) VALUES(:val)' );

type
  TDataModule1 = class( TDataModule )
    FDConnection1: TFDConnection;
    FDQuery1: TFDQuery;
  private
    FQueries: array [0 .. SQL_QUERY_MAX] of TFDQuery;
    function GetQuery( const Index: Integer ): TFDQuery;
  protected
    property FooGetQuery: TFDQuery index 0 read GetQuery;
    property FooAddQuery: TFDQuery index 1 read GetQuery;
  public
    function GetFooId( const Val: string ): Integer;
    function AddFoo( const Val: string ): Integer;
    function GetFooIdOrAdd( const Val: string ): Integer;
  end;

var
  DataModule1: TDataModule1;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}
{ TDataModule1 }

function TDataModule1.AddFoo( const Val: string ): Integer;
var
  LQry: TFDQuery;
begin
  LQry := FooAddQuery;
  LQry.ParamByName( 'val' ).Value := Val;
  LQry.ExecSQL;
  Result := LQry.Connection.GetLastAutoGenValue( 'id' );
end;

function TDataModule1.GetFooId( const Val: string ): Integer;
var
  LQry: TFDQuery;
begin
  LQry := FooGetQuery;
  LQry.ParamByName( 'val' ).Value := Val;
  LQry.Open;
  try
    if LQry.Eof
    then
      Result := 0
    else
      Result := LQry.Fields[0].Value;
  finally
    LQry.Close;
  end;
end;

function TDataModule1.GetFooIdOrAdd( const Val: string ): Integer;
begin
  Result := GetFooId( Val );
  if Result = 0
  then
    Result := AddFoo( Val );
end;

function TDataModule1.GetQuery( const Index: Integer ): TFDQuery;
begin
  if FQueries[index] = nil
  then
    begin
      Result := TFDQuery.Create( Self );
      Result.Connection := FDConnection1;
      Result.SQL.Text := SQL_QUERY[index];
      FQueries[Index] := Result;
    end
  else
    Result := FQueries[Index];
end;

end.

[Peter666]

Danke, ja in der Form hab ich das schon von Anfang an gemacht:

Quasi ein:

Code:

for i:=0 to 1 do
begin
 if i=1 then
  InsertIntoDB;
 if FindInDB then exit;
end;

Das Problem ist halt das das quälend langsam ist. Nun ist es eigentlich egal und ich hab mich schon gefragt, ob man nicht das ganze mit weniger Abfragen realisieren kann. Trotzdem vielen Dank für die schnelle Hilfe :)

Peter

[Sir Rufo]

Dann probier meine Variante einmal aus und du wirst eine Verbesserung der Performance feststellen.