Aktualisieren der DB beim Kunden

[Hobbycoder]

Danke für die zahlreichen Inspirationen/Informationen.

Zitat von Frickler:

Jede Datenbank hat virtuelle Tabellen mit Metadaten, aus denen man entnehmen kann, wie die Datenbank aufgebaut ist, wie die Tabellen heißen, welche Felder sie haben, welche Typen die haben usw. Die musst Du einfach abfragen, so bekommst Du die benötigten Infos.
Siehe https://mariadb.com/kb/en/informatio...-tables-table/

Das ist (zumindest für mich) eine richtig gute Idee, weil mir das das Auswerten und verlgeichen der DB supereinfach macht. Mir geht's ja nur darum
- Neue Tabellen zur erzeugen
- Weggefallene Tabellen zu löschen
- Neue Columns hinzufügen
- Weggefallene Columns zu entfernen
- Datetype und Länge der Columns überprüfen und anpassen
- Defaultwert der Columns überprüfen und anpassen

Das konnte ich jetzt mit eine kleine Klasse erledigen, die ich hier mal reinsetze falls das mal irgendwer gebrauchen kann.

Delphi-Quellcode:

unit uTestTables2;

interface

uses System.Classes, System.SysUtils, ZConnection, ZDataset, Data.DB, System.Generics.Collections,
  uRWStream;

type
  TModifyFlag=(mfNoChanges, mfAdd, mfModify, mfDelete);
  TColumnModifyFlag=(cmfNone, cmfDatatype, cmfDefaultValue);

  TDBColumn=class
  private
    FName: string;
    FColumnDefault: string;
    FColumnType: string;
    FModifyFlag: TModifyFlag;
    FColumnModifyFlag: TColumnModifyFlag;
    procedure SetColumnDefault(const Value: string);
    procedure SetColumnType(const Value: string);
    procedure SetName(const Value: string);
    procedure SetModifyFlag(const Value: TModifyFlag);
    procedure SetColumnModifyFlag(const Value: TColumnModifyFlag);
  public
    constructor Create;
    procedure WriteToStream(st: TStream);
    procedure ReadFromStream(st: TStream; Version: Integer);
    procedure Compare(Source: TObject);
    procedure Assign(Source: TObject);
    function GetDefault: string;
  published
    property Name: string read FName write SetName;
    property ColumnType: string read FColumnType write SetColumnType;
    property ColumnDefault: string read FColumnDefault write SetColumnDefault;
    property ModifyFlag: TModifyFlag read FModifyFlag write SetModifyFlag;
    property ColumnModifyFlag: TColumnModifyFlag read FColumnModifyFlag write SetColumnModifyFlag;
  end;

  TDBTable=class(TObjectList<TDBColumn>)
  private
    FName: string;
    FCollation: string;
    FModifyFlag: TModifyFlag;
    procedure SetCollation(const Value: string);
    procedure SetName(const Value: string);
    procedure SetModifyFlag(const Value: TModifyFlag);
  public
    procedure WriteToStream(st: TStream);
    procedure ReadFromStream(st: TStream; Version: Integer);
    procedure Compare(Source: TObject);
    function GetColumnByName(value: string): TDBColumn;
    procedure Assign(Source: TObject);
  published
    property Name: string read FName write SetName;
    property Collation: string read FCollation write SetCollation;
    property ModifyFlag: TModifyFlag read FModifyFlag write SetModifyFlag;
  end;

  TDBDatabase=class(TObjectList<TDBTable>)
  private
    FFilename: string;
    FSchemaName: string;
    FCollation: string;
    procedure SetCollation(const Value: string);
    procedure SetSchemaName(const Value: string);
    function GetSchema(Con: TZConnection; Schema: string): Boolean;
    procedure GetTables(Con: TZConnection; Schema: string);
    procedure GetColumns(Con: TZConnection; Schema: string; TT: TDBTable);
  public
    procedure Compare(Source: TObject);
    procedure BuildFromDB(Con: TZConnection; RootPass: string; Schema: string);
    procedure LoadFromFile(Filename: string = '');
    procedure SaveToFile(Filename: string = '');
    function GetTableByName(value: string): TDBTable;
    function IsModified: Boolean;
    function CreateTableScript(Script: TStrings): Boolean;
    function ExecSQLLine(Con: TZConnection; Line: string): Boolean;
  published
    property SchemaName: string read FSchemaName write SetSchemaName;
    property Collation: string read FCollation write SetCollation;
  end;

Const
  FileVersion = 1;
  StringSeparator = '`';

implementation

{ TDBColumn }

procedure TDBColumn.Assign(Source: TObject);
var
  TC: TDBColumn;
begin
  if Source is TDBColumn then
  begin
    TC:=TDBColumn(Source);
    self.FName:=TC.Name;
    self.FColumnType:=TC.ColumnType;
    self.FColumnDefault:=TC.ColumnDefault;
  end;
end;

procedure TDBColumn.Compare(Source: TObject);
var
  TC: TDBColumn;
begin
  Self.FModifyFlag:=mfNoChanges;
  if Source is TDBColumn then
  begin
    TC:=TDBColumn(Source);
    if (self.FColumnDefault<>TC.ColumnDefault) then
    begin
      Self.FModifyFlag:=mfModify;
      self.FColumnModifyFlag:=cmfDefaultValue;
      self.FColumnDefault:=TC.ColumnDefault;
    end;
    if (self.FColumnType<>TC.ColumnType) then
    begin
      Self.FModifyFlag:=mfModify;
      self.FColumnModifyFlag:=cmfDatatype;
      self.FColumnType:=TC.ColumnType;
    end;
  end;
end;

constructor TDBColumn.Create;
begin
  inherited;
  Self.FColumnModifyFlag:=cmfNone;
end;

function TDBColumn.GetDefault: string;
begin
  if self.FColumnDefault<>'NULL' then
    Result:=''''+self.FColumnDefault+'''' else
    Result:=Self.FColumnDefault;
end;

procedure TDBColumn.ReadFromStream(st: TStream; Version: Integer);
begin
  if st<>nil then
  begin
    self.FName:=ReadStrFromStream(st);
    self.FColumnType:=ReadStrFromStream(st);
    self.FColumnDefault:=ReadStrFromStream(st);
  end;
end;

procedure TDBColumn.SetColumnDefault(const Value: string);
begin
  FColumnDefault := Value;
end;

procedure TDBColumn.SetColumnModifyFlag(const Value: TColumnModifyFlag);
begin
  FColumnModifyFlag := Value;
end;

procedure TDBColumn.SetColumnType(const Value: string);
begin
  FColumnType := Value;
end;

procedure TDBColumn.SetModifyFlag(const Value: TModifyFlag);
begin
  FModifyFlag := Value;
end;

procedure TDBColumn.SetName(const Value: string);
begin
  FName := Value;
end;

procedure TDBColumn.WriteToStream(st: TStream);
begin
  if st<>nil then
  begin
    WriteStrToStream(st, self.FName);
    WriteStrToStream(st, self.FColumnType);
    WriteStrToStream(st, self.FColumnDefault);
  end;
end;

{ TDBTable }

procedure TDBTable.Assign(Source: TObject);
var
  TT: TDBTable;
  TC: TDBColumn;
  I: Integer;
begin
  if Source is TDBTable then
  begin
    TT:=TDBTable(Source);
    self.FName:=TT.Name;
    self.FCollation:=TT.Collation;
    self.Clear;
    for I := 0 to TT.Count-1 do
    begin
      TC:=TDBColumn.Create;
      TC.Assign(TT[i]);
      TC.ModifyFlag:=mfAdd;
      self.Add(TC);
    end;
  end;
end;

procedure TDBTable.Compare(Source: TObject);
var
  TT: TDBTable;
  TC, TC2: TDBColumn;
  I: Integer;
begin
  if Source is TDBTable then
  begin
    TT:=TDBTable(Source);
    for I := 0 to Self.Count-1 do
    begin
      TC:=TT.GetColumnByName(self[i].Name);
      if TC<>nil then
        self[i].Compare(TC) else
        self[i].ModifyFlag:=mfDelete;
      if self[i].ModifyFlag<>mfNoChanges then self.FModifyFlag:=mfModify;
    end;
    for I := 0 to TT.Count-1 do
    begin
      TC:=self.GetColumnByName(TT[i].Name);
      if TC=nil then
      begin
        TC2:=TDBColumn.Create;
        TC2.Assign(TC);
        TC2.ModifyFlag:=mfAdd;
        self.Add(TC2);
        self.ModifyFlag:=mfModify;
      end;
    end;
  end;
end;

function TDBTable.GetColumnByName(value: string): TDBColumn;
var
  I: Integer;
begin
  Result:=nil;
  for I := 0 to Self.Count-1 do
    if self[i].Name.ToLower=value.ToLower then
    begin
      Result:=self[i];
      Break;
    end;
end;

procedure TDBTable.ReadFromStream(st: TStream; Version: Integer);
var
  TC: TDBColumn;
  I, C: Integer;
begin
  if st<>nil then
  begin
    Self.Clear;
    self.FName:=ReadStrFromStream(st);
    self.FCollation:=ReadStrFromStream(st);
    C:=ReadIntFromStream(st);
    for I := 0 to C-1 do
    begin
      TC:=TDBColumn.Create;
      TC.ReadFromStream(st, Version);
      self.Add(TC);
    end;
  end;
end;

procedure TDBTable.SetCollation(const Value: string);
begin
  FCollation := Value;
end;

procedure TDBTable.SetModifyFlag(const Value: TModifyFlag);
begin
  FModifyFlag := Value;
end;

procedure TDBTable.SetName(const Value: string);
begin
  FName := Value;
end;

procedure TDBTable.WriteToStream(st: TStream);
var
  I: Integer;
begin
  if st<>nil then
  begin
    WriteStrToStream(st, self.FName);
    WriteStrToStream(st, self.FCollation);
    WriteIntToStream(st, self.Count);
    for I := 0 to self.Count-1 do
      self[i].WriteToStream(st);
  end;
end;

{ TDBDatabase }

procedure TDBDatabase.BuildFromDB(Con: TZConnection; RootPass: string; Schema: string);
var
  FCon: TZConnection;
  FQ: TZQuery;
begin
  FCon:=TZConnection.Create(nil);
  try
    FCon.HostName:=Con.HostName;
    FCon.Port:=Con.Port;
    FCon.User:='root';
    Fcon.Password:=RootPass;
    Fcon.Database:='information_schema';
    Fcon.Protocol:=Con.Protocol;
    Fcon.LibraryLocation:=Con.LibraryLocation;
    Fcon.LibLocation:=Con.LibLocation;
    try
      FCon.Connect;
      if FCon.Connected then
      begin
        self.Clear;
        self.FSchemaName:='';
        self.FCollation:='';
        if self.GetSchema(FCon, Schema) then
        begin
          Self.GetTables(FCon, Schema);
        end;
      end;
    except

    end;
  finally
    FCon.Free;
  end;
end;

procedure TDBDatabase.Compare(Source: TObject);
var
  DB: TDBDatabase;
  TT, TT2: TDBTable;
  I: Integer;
begin
  if Source is TDBDatabase then
  begin
    DB:=TDBDatabase(Source);
    for I := 0 to self.Count-1 do
    begin
      TT:=DB.GetTableByName(self[i].Name);
      if TT<>nil then
        self[i].Compare(TT) else
        self[i].ModifyFlag:=mfDelete;
    end;
    for i:=0 to db.Count-1 do
    begin
      TT:=self.GetTableByName(DB[i].Name);
      if TT=nil then
      begin
        TT2:=TDBTable.Create(True);
        TT2.Assign(DB[i]);
        TT2.ModifyFlag:=mfAdd;
        self.Add(TT2);
      end;
    end;
  end;
end;

function TDBDatabase.CreateTableScript(Script: TStrings): Boolean;
var
  s: string;
  I: Integer;
  C: Integer;
begin
  Script.Clear;
  Result:=False;
  try
    Script.Add('USE '+StringSeparator+self.SchemaName+StringSeparator+';');
    for I := 0 to self.Count-1 do
    begin
      case self[i].ModifyFlag of
        mfAdd:
        begin
          s:='CREATE TABLE '+StringSeparator+self[i].Name+StringSeparator+' (';
          for C := 0 to self[i].Count-1 do
          begin
            s:=s+StringSeparator+self[i][C].Name+StringSeparator+' '+self[i][c].ColumnType+' DEFAULT '+self[i][c].ColumnDefault+' ,';
          end;
          s:=Copy(s, 1, Length(s)-2);
          s:=s+') COLLATE='''+self[i].Collation+''';';
          Script.Add(S);
        end;
        mfModify:
        begin
          for C := 0 to self[i].Count-1 do
            case self[i][c].ModifyFlag of
              mfModify:
              begin
                s:='ALTER TABLE '+StringSeparator+self[i].Name+StringSeparator+' CHANGE COLUMN '+StringSeparator+self[i][C].Name+StringSeparator+' '+StringSeparator+self[i][C].Name+StringSeparator+' '+self[i][c].ColumnType+' DEFAULT '+self[i][c].GetDefault+';';
                Script.Add(s);
              end;
              mfAdd:
              begin
                s:='ALTER TABLE '+StringSeparator+self[i].Name+StringSeparator+' ADD COLUMN '+StringSeparator+self[i][C].Name+StringSeparator+' '+self[i][c].ColumnType+' DEFAULT '+self[i][c].GetDefault+';';
                Script.Add(s);
              end;
              mfDelete:
              begin
                s:='ALTER TABLE '+StringSeparator+self[i].Name+StringSeparator+' DROP COLUMN '+StringSeparator+self[i][C].Name+StringSeparator+';';
                Script.Add(s);
              end;
            end;
        end;
        mfDelete:
        begin
          s:='DROP TABLE '+StringSeparator+self[i].Name+StringSeparator+';';
          Script.Add(s);
        end;
      end;
    end;
    Result:=True;
  finally

  end;
end;

function TDBDatabase.ExecSQLLine(Con: TZConnection; Line: string): Boolean;
var
  q: TZQuery;
begin
  Result:=False;
  q:=TZQuery.Create(nil);
  try
    q.Connection:=Con;
    q.SQL.Text:=Line;
    q.ExecSQL;
    Result:=True;
  finally
    q.Free;
  end;
end;

procedure TDBDatabase.GetColumns(Con: TZConnection; Schema: string; TT: TDBTable);
var
  q: TZQuery;
  TC: TDBColumn;
begin
  q:=TZQuery.Create(nil);
  try
    q.Connection:=Con;
    q.SQL.Text:='Select * from COLUMNS where TABLE_SCHEMA=:schema and TABLE_NAME=:tablename order by ORDINAL_POSITION';
    q.Params.ParamValues['schema']:=Schema;
    q.Params.ParamValues['tablename']:=TT.Name;
    q.Active:=True;
    while not q.Eof do
    begin
      TC:=TDBColumn.Create;
      TT.Add(TC);
      TC.Name:=q.FieldByName('COLUMN_NAME').AsString;
      TC.ColumnType:=q.FieldByName('COLUMN_TYPE').AsString;
      TC.FColumnDefault:=q.FieldByName('COLUMN_DEFAULT').AsString;
      q.Next;
    end;
    q.Active:=False;
  finally
    q.Free;
  end;
end;

function TDBDatabase.GetSchema(Con: TZConnection; Schema: string): Boolean;
var
  q: TZQuery;
begin
  Result:=False;
  q:=TZQuery.Create(nil);
  try
    q.Connection:=Con;
    q.SQL.Text:='Select * from SCHEMATA where SCHEMA_NAME=:schema';
    q.Params.ParamValues['schema']:=Schema;
    q.Active:=True;
    if q.RecordCount>0 then
    begin
      self.FSchemaName:=q.FieldByName('SCHEMA_NAME').AsString;
      self.FCollation:=q.FieldByName('DEFAULT_COLLATION_NAME').AsString;
      Result:=True;
    end;
    q.Active:=False;
  finally
    q.Free;
  end;
end;

function TDBDatabase.GetTableByName(value: string): TDBTable;
var
  I: Integer;
begin
  Result:=nil;
  for I := 0 to Self.Count-1 do
    if self[i].Name.ToLower=value.ToLower then
    begin
      Result:=self[i];
      Break;
    end;
end;

procedure TDBDatabase.GetTables(Con: TZConnection; Schema: string);
var
  q: TZQuery;
  TT: TDBTable;
  TC: TDBColumn;
begin
  q:=TZQuery.Create(nil);
  try
    q.Connection:=Con;
    q.SQL.Text:='Select * from TABLES where TABLE_SCHEMA=:schema order by TABLE_NAME';
    q.Params.ParamValues['schema']:=Schema;
    q.Active:=True;
    while not q.Eof do
    begin
      TT:=TDBTable.Create(True);
      self.Add(TT);
      TT.Name:=q.FieldByName('TABLE_NAME').AsString;
      TT.Collation:=q.FieldByName('TABLE_COLLATION').AsString;
      GetColumns(Con, Schema, TT);
      q.Next;
    end;
    q.Active:=False;
  finally
    q.Free;
  end;
end;

function TDBDatabase.IsModified: Boolean;
var
  I: Integer;
begin
  Result:=False;
  for I := 0 to self.Count-1 do
    if self[i].ModifyFlag<>mfNoChanges then
    begin
      Result:=True;
      Break;
    end;
end;

procedure TDBDatabase.LoadFromFile(Filename: string);
var
  st: TFileStream;
  Version: Integer;
  I, C: Integer;
  TT: TDBTable;
begin
  if Filename<>'' then FFilename:=Filename;
  if FileExists(FFilename) then
  begin
    Self.Clear;
    self.FSchemaName:='';
    self.FCollation:='';
    st:=TFileStream.Create(FFilename, fmOpenRead);
    if st<>nil then
    try
      Version:=ReadIntFromStream(st);
      self.FSchemaName:=ReadStrFromStream(st);
      self.FCollation:=ReadStrFromStream(st);
      C:=ReadIntFromStream(st);
      for I := 0 to C-1 do
      begin
        TT:=TDBTable.Create(True);
        TT.ReadFromStream(st, Version);
        self.Add(TT);
      end;
    finally
      st.Free;
    end;
  end;
end;

procedure TDBDatabase.SaveToFile(Filename: string);
var
  st: TFileStream;
  I: Integer;
begin
  if Filename<>'' then FFilename:=Filename;
  if DirectoryExists(ExtractFilePath(FFilename)) then
  begin
    st:=TFileStream.Create(FFilename, fmCreate);
    if st<>nil then
    try
      WriteIntToStream(st, FileVersion);
      WriteStrToStream(st, self.FSchemaName);
      WriteStrToStream(st, self.FCollation);
      WriteIntToStream(st, self.Count);
      for I := 0 to self.Count-1 do
        self[i].WriteToStream(st);
    finally
      st.Free;
    end;
  end;
end;

procedure TDBDatabase.SetCollation(const Value: string);
begin
  FCollation := Value;
end;

procedure TDBDatabase.SetSchemaName(const Value: string);
begin
  FSchemaName := Value;
end;

end.

Aufruf:

Delphi-Quellcode:

var
  DB, DB2: TDBDatabase;
  sl: TStringList;
  I: Integer;
  q: TZQuery;
begin
  DB:=TDBDatabase.Create(True);
  DB2:=TDBDatabase.Create(True);
  sl:=TStringList.Create;
  try
    if ExtactDBResource then
    begin
      DB.BuildFromDB(con1, '123456', 'testschema');  //Hier muss das Root-Passwort der DB verwendet werden.
      DB2.LoadFromFile(ExtractFilePath(Application.ExeName)+'testschema.shdat');
      DB.Compare(DB2);
      if DB.IsModified then
      begin
        if DB.CreateTableScript(sl) then
        begin
          if sl.Count>1 then
          begin
            for I := 1 to sl.Count-1 do
            begin
              DB.ExecSQLLine(con1, sl[i]);
            end;
          end;
        end;
      end;
    end;
  finally
    DB.Free;
    DB2.Free;
    sl.Free;
  end;

Die Proceduren LoadFromFile, SaveToFile, ReadFromStream und WriteToStream muss man zum Testen rausschmeißen.
Wer die dafür notwendige Unit haben möchte, kann mich ja anschreiben.
Und wer nicht ZEOS verwendet, muss das für seine vewendete DB-Komponenten anpassen.

Das ganze ist natürlich recht einfach gehalten, weil es mir ja nur um die o.g. Tabellen- und Feldeigenschaften geht. Wer mehr will, kann das ja entsprechend anpassen.