In my last entry I wrote a script to handle saving a schema (to a python pickle file), restoring the schema, and doing backup and restore. After I wrote the script, I compiled it using the py2exe utility so that I could send the script with my Delphi app.

In my Delphi app, I created a sub-folder called "pyscript" and stored the compiled files in that folder. The following is the code that I have for a form that interfaces with my MySQL schema script.

Note the conflicting names. My script has schema in the name, but my Delphi code has backup in the name. This is because when I wrote the python script, my primary objective was to create a lazy approach to schema updates, but I also included data backup and restore functions (since it made sense to me at the time). The primary function in the Delphi app is backup.

The Delphi Form:


object fmMySQLBackup: TfmMySQLBackup
  Left = 444
  Top = 164
  Width = 436
  Height = 332
  Caption = 'MySQL Backup'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  OnShow = FormShow
  PixelsPerInch = 96
  TextHeight = 13
  object Panel1: TPanel
    Left = 0
    Top = 0
    Width = 428
    Height = 137
    Align = alTop
    BevelOuter = bvNone
    TabOrder = 0
    object lblLocation: TLabel
      Left = 104
      Top = 8
      Width = 52
      Height = 13
      Caption = 'Backup to:'
    end
    object lblFile: TLabel
      Left = 104
      Top = 40
      Width = 56
      Height = 13
      Caption = 'Restore file:'
    end
    object lblSchemaSave: TLabel
      Left = 104
      Top = 72
      Width = 40
      Height = 13
      Caption = 'Save to:'
    end
    object lblSchemaUpdt: TLabel
      Left = 104
      Top = 104
      Width = 57
      Height = 13
      Caption = 'Update File:'
    end
    object btnRestore: TButton
      Left = 8
      Top = 40
      Width = 89
      Height = 25
      Caption = 'Restore'
      TabOrder = 0
      OnClick = btnRestoreClick
    end
    object btnBackup: TButton
      Left = 8
      Top = 8
      Width = 89
      Height = 25
      Caption = 'Backup data'
      TabOrder = 1
      OnClick = btnBackupClick
    end
    object btnSchemaSave: TButton
      Left = 8
      Top = 72
      Width = 89
      Height = 25
      Caption = 'Schema Save'
      TabOrder = 2
      OnClick = btnSchemaSaveClick
    end
    object btnSchemaUpdt: TButton
      Left = 8
      Top = 104
      Width = 89
      Height = 25
      Caption = 'Schema Update'
      TabOrder = 3
      OnClick = btnSchemaUpdtClick
    end
  end
  object groupbox: TGroupBox
    Left = 0
    Top = 137
    Width = 428
    Height = 161
    Align = alClient
    Caption = 'Backup files'
    TabOrder = 1
    object lbFiles: TListBox
      Left = 2
      Top = 15
      Width = 424
      Height = 144
      Hint = 'Right click here to see more options!'
      Align = alClient
      ItemHeight = 13
      ParentShowHint = False
      PopupMenu = menu
      ShowHint = True
      TabOrder = 0
      OnClick = lbFilesClick
    end
  end
  object Zlib: TIdCompressorZLibEx
    Left = 376
    Top = 8
  end
  object menu: TPopupMenu
    Left = 80
    Top = 185
    object Viewbackupfiles1: TMenuItem
      Caption = 'View backup files'
      OnClick = Viewbackupfiles1Click
    end
    object Viewcurrentlog1: TMenuItem
      Caption = 'View current log'
      OnClick = Viewcurrentlog1Click
    end
    object Viewoldlog1: TMenuItem
      Caption = 'View old log'
      OnClick = Viewoldlog1Click
    end
  end
end


The Delphi Code:


{ FormMySQLBackup.pas

- Uses mysql_schema.exe program
  mysql_schema.exe is really just a compiled python script which uses DBAPI to
  do it's work.

}
unit FormMySQLBackup;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ExtCtrls, StdCtrls, DateUtils, JCLShell, StrUtils, FileCtrl,
  IdBaseComponent, IdZLibCompressorBase, IdCompressorZLibEx, Menus;

type
  TfmMySQLBackup = class(TForm)
    btnBackup: TButton;
    lblLocation: TLabel;
    btnRestore: TButton;
    lbFiles: TListBox;
    Panel1: TPanel;
    lblFile: TLabel;
    btnSchemaSave: TButton;
    btnSchemaUpdt: TButton;
    groupbox: TGroupBox;
    lblSchemaSave: TLabel;
    lblSchemaUpdt: TLabel;
    Zlib: TIdCompressorZLibEx;
    menu: TPopupMenu;
    Viewbackupfiles1: TMenuItem;
    Viewcurrentlog1: TMenuItem;
    Viewoldlog1: TMenuItem;
    procedure lbFilesClick(Sender: TObject);
    procedure btnBackupClick(Sender: TObject);
    procedure btnRestoreClick(Sender: TObject);
    procedure btnSchemaSaveClick(Sender: TObject);
    procedure btnSchemaUpdtClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Viewbackupfiles1Click(Sender: TObject);
    procedure Viewcurrentlog1Click(Sender: TObject);
    procedure Viewoldlog1Click(Sender: TObject);
  private
    { Private declarations }
    folder: String;
    dbname: String;
    username: String;
    password: String;
    host: String;
    port: String;
    log_file: String;
    oldlog_file: String;
    backup_file: String;
    restore_file: String;
    schemasave_file: String;
    schemaupdt_file: String;
    curr_date: String;
    procedure Init;
    procedure RunCommand(command, filename: String);
    procedure ManageLogFile;
    procedure ViewOldLogFile;
    procedure ViewCurrentLogFile;
    procedure ViewBackupFiles;
  public
    { Public declarations }
    procedure Open(dbname, username, password, host, port, logfile: String);
    procedure Restore(filename, dbname, username, password, host, port, logfile: String);
    procedure Backup(filename, dbname, username, password, host, port, logfile: String);
    procedure SchemaSave(filename, dbname, username, password, host, port, logfile: String);
    procedure SchemaUpdate(filename, dbname, username, password, host, port, logfile: String);
    function GetBackupFileName: String;
    function GetSchemaFileName: String;
  end;

var
  fmMySQLBackup: TfmMySQLBackup;

implementation

{$R *.dfm}

{ TfmMySQLBackup }

procedure TfmMySQLBackup.Backup(filename, dbname, username, password, host,
  port, logfile: String);
begin
  if (logfile = '') and (log_file = '') then
    begin
      log_file := folder+'logfile.txt';
    end
  else if logfile <> '' then
    self.log_file := logfile;
  self.dbname := dbname;
  self.username := username;
  self.password := password;
  self.host := host;
  self.port := port;
  self.Init;
  if filename = '' then
    filename := backup_file
  else
    backup_file := filename;
  self.RunCommand('backup',filename);
end;

{
  Initialize file names
}
procedure TfmMySQLBackup.Init;
begin
  folder := ExtractFilePath(Application.ExeName);
  curr_date := FormatDateTime('YYYYmmdd',Now);
  //Get the list of previous backups
  lbFiles.Clear;
  //Get backup file listing
  ViewBackupFiles;
  //Make our file names
  backup_file := folder+'backup_'+dbname+'_'+curr_date+'.data';
  if log_file = '' then
    log_file := folder+'logfile.txt';
  oldlog_file := folder+LeftStr(ExtractFileName(log_file),length(ExtractFileName(log_file))-length(ExtractFileExt(log_file))) + '.zip';
  if lbFiles.Count > 0 then
    restore_file := folder+lbFiles.Items[lbFiles.ItemIndex]
  else
    restore_file := 'ERROR: No file selected';
  schemasave_file := folder+'schema_'+dbname+'_new.data';
  schemaupdt_file := folder+'schema_'+dbname+'.data';
  //Update captions
  lblFile.Caption := 'Restore File: ' + restore_file;
  lblLocation.Caption := 'Backup to: ' + backup_file;
  lblSchemaSave.Caption := 'Save to: ' + schemasave_file;
  lblSchemaUpdt.Caption := 'Update file: ' + schemaupdt_file;
  self.ManageLogFile;
end;

procedure TfmMySQLBackup.Open(dbname, username, password, host, port,
  logfile: String);
begin
  if (logfile = '') and (log_file = '') then
    begin
      log_file := folder+'logfile.txt';
    end
  else if logfile <> '' then
    self.log_file := logfile;
  self.dbname := dbname;
  self.username := username;
  self.password := password;
  self.host := host;
  self.port := port;
  self.Init;
  self.Show;
end;

procedure TfmMySQLBackup.Restore(filename, dbname, username, password,
  host, port, logfile: String);
begin
  if filename = '' then
    Exit;
  if (logfile = '') and (log_file = '') then
    begin
      log_file := folder+'logfile.txt';
    end
  else if logfile <> '' then
    self.log_file := logfile;
  self.dbname := dbname;
  self.username := username;
  self.password := password;
  self.host := host;
  self.port := port;
  if filename = '' then
    filename := restore_file;
  self.RunCommand('restore',filename);
end;

procedure TfmMySQLBackup.RunCommand(command, filename: String);
var
  cmd_line, options: String;
begin
  //Configure our command
  cmd_line := '"'+folder+'pyscript\mysql_schema.exe"';
  options := '-d ' + dbname +
    ' -u ' + username +
    ' -p ' + password +
    ' -o ' + command +
    ' -f "' + filename + '"' +
    ' -H ' + host +
    ' -P ' + port +
    ' -l "' + log_file + '"';
  //Run our command
  if not ShellExecAndWait(cmd_line,options) then
    begin
      MessageDlg('Error running operation.  Try again: ' + SysErrorMessage(GetLastError()), mtError, [mbOK], 0);
      Exit;
    end;
  //Display the results of our command
  ViewCurrentLogFile;
end;

procedure TfmMySQLBackup.SchemaSave(filename, dbname, username, password,
  host, port, logfile: String);
begin
  if filename = '' then
    Exit;
  if (logfile = '') and (log_file = '') then
    begin
      log_file := folder+'logfile.txt';
    end
  else if logfile <> '' then
    self.log_file := logfile;
  self.dbname := dbname;
  self.username := username;
  self.password := password;
  self.host := host;
  self.port := port;
  if filename = '' then
    filename := schemasave_file;
  self.RunCommand('schema_save',filename);
end;

procedure TfmMySQLBackup.SchemaUpdate(filename, dbname, username, password,
  host, port, logfile: String);
begin
  if filename = '' then
    Exit;
  if (logfile = '') and (log_file = '') then
    begin
      log_file := folder+'logfile.txt';
    end
  else if logfile <> '' then
    self.log_file := logfile;
  self.dbname := dbname;
  self.username := username;
  self.password := password;
  self.host := host;
  self.port := port;
  if filename = '' then
    filename := schemaupdt_file;
  self.RunCommand('schema_updt',filename);
end;

procedure TfmMySQLBackup.lbFilesClick(Sender: TObject);
begin
  if groupbox.Caption <> 'Backup files' then
    Exit;
  if lbFiles.Count > 0 then
    restore_file := folder+lbFiles.Items[lbFiles.ItemIndex];
  lblFile.Caption := 'Restore File: ' + restore_file;
end;

procedure TfmMySQLBackup.btnBackupClick(Sender: TObject);
begin
  self.RunCommand('backup',backup_file);
end;

procedure TfmMySQLBackup.btnRestoreClick(Sender: TObject);
begin
  self.RunCommand('restore',restore_file);
end;

procedure TfmMySQLBackup.btnSchemaSaveClick(Sender: TObject);
begin
  self.RunCommand('schema_save',schemasave_file);
end;

procedure TfmMySQLBackup.btnSchemaUpdtClick(Sender: TObject);
begin
  self.RunCommand('schema_updt',schemaupdt_file);
end;

procedure TfmMySQLBackup.FormShow(Sender: TObject);
begin
  if folder = '' then
    MessageDlg('Programming Warning: The module was not initialized properly '+#13+#10+'before.', mtWarning, [mbOK], 0);
  groupbox.Caption := 'Backup files';
end;

procedure TfmMySQLBackup.ManageLogFile;
Var
  oldlogfile,oldlog_text: TFileStream;
  log_buffer, oldlog_buffer: TStringList;
begin
  if not FileExists(log_file) then
    Exit;
  //load the current log file
  log_buffer := TStringList.Create;
  log_buffer.LoadFromFile(log_file);
  //load the zlib compressed old log file
  if FileExists(oldlog_file) then
    oldlogfile := TFileStream.Create(oldlog_file,fmOpenRead)
  else
    oldlogfile := TFileStream.Create(oldlog_file,fmCreate);
  oldlogfile.Seek(0,soFromBeginning);
  oldlog_text := TFileStream.Create(folder+'temp.txt',fmCreate);
  //decompress the old log
  if oldlogfile.Size > 0 then
    Zlib.InflateStream(oldlogfile,oldlog_text);
  oldlog_text.Seek(0, soFromBeginning);
  //append the current log to the old log
  oldlog_buffer := TStringList.Create;
  oldlog_buffer.LoadFromStream(oldlog_text);
  oldlog_text.Free;
  oldlogfile.Free;
  oldlog_buffer.AddStrings(log_buffer);
  //Save our new appended text to a single stream
  oldlog_text := TFileStream.Create(folder+'temp.txt',fmCreate);
  oldlog_buffer.SaveToStream(oldlog_text);
  oldlog_text.Seek(0,soFromBeginning);
  oldlogfile := TFileStream.Create(oldlog_file, fmCreate);
  //Compress our new old log into the file
  Zlib.DeflateStream(oldlog_text,oldlogfile,1);
  //close our streams
  log_buffer.Free;
  oldlog_buffer.Free;
  oldlog_text.Free;
  oldlogfile.Free;
  if not Deletefile(folder+'temp.txt') then
    begin
      oldlog_text := TFileStream.Create(folder+'temp.txt',fmCreate);
      oldlog_text.Free;
    end;
  //Delete the log file
  if not DeleteFile(log_file) then
    MessageDlg('Error removing log file for backup utility.', mtWarning, [mbOK], 0);
end;

procedure TfmMySQLBackup.ViewOldLogFile;
Var
  oldlogfile, oldlog_text: TFileStream;
begin
  if not FileExists(oldlog_file) then
    begin
      MessageDlg('Old log file doesn''t exist.', mtWarning, [mbOK], 0);
      Exit;
    end;
  groupbox.Caption := 'Log file results';
  //load the zlib compressed old log file
  oldlogfile := TFileStream.Create(oldlog_file,fmOpenRead);
  if oldlogfile.Size = 0 then
    begin
      oldlogfile.Free;
      Exit;
    end;
  oldlogfile.Seek(0,soFromBeginning);
  oldlog_text := TFileStream.Create(folder+'temp.txt',fmCreate);
  //decompress the old log
  Zlib.InflateStream(oldlogfile,oldlog_text);
  //append the current log to the old log
  lbFiles.Clear;
  lbFiles.Sorted := False;
  oldlog_text.Seek(0, soFromBeginning);
  lbFiles.Items.LoadFromStream(oldlog_text);
  oldlog_text.Free;
  oldlogfile.Free;
  if not Deletefile(folder+'temp.txt') then
    begin
      oldlog_text := TFileStream.Create(folder+'temp.txt',fmCreate);
      oldlog_text.Free;
    end;
end;

function TfmMySQLBackup.GetBackupFileName: String;
begin
  GetBackupFileName := self.backup_file;
end;

function TfmMySQLBackup.GetSchemaFileName: String;
begin
  GetSchemaFileName := self.schemasave_file;
end;

procedure TfmMySQLBackup.FormCreate(Sender: TObject);
begin
  self.Init;
end;

procedure TfmMySQLBackup.Viewbackupfiles1Click(Sender: TObject);
begin
  ViewBackupFiles;
end;

procedure TfmMySQLBackup.ViewBackupFiles;
Var
  sr: TSearchRec;
begin
  groupbox.Caption := 'Backup files';
  lbFiles.Clear;
  //Use FindFirst, FindNext and FindClose to do a directory listing
  if FindFirst(folder+'backup_'+dbname+'_*.data',faAnyFile,sr) = 0 then
    begin
      repeat
        lbFiles.Items.Append(sr.Name);
      until FindNext(sr) <> 0;
      FindClose(sr);
    end;
  lbFiles.Sorted := True;
  if lbFiles.Count > 0 then
    lbFiles.ItemIndex := 0;
end;

procedure TfmMySQLBackup.ViewCurrentLogFile;
begin
  if not FileExists(log_file) then
    begin
      MessageDlg('Log file doesn''t exist.', mtWarning, [mbOK], 0);
      Exit;
    end;
  groupbox.Caption := 'Log file results';
  lbFiles.Clear;
  lbFiles.Sorted := False;
  lbFiles.Items.LoadFromFile(log_file);
  if lbFiles.Count > 0 then
    lbFiles.ItemIndex := lbFiles.Count - 1;
end;

procedure TfmMySQLBackup.Viewcurrentlog1Click(Sender: TObject);
begin
  ViewCurrentLogFile;
end;

procedure TfmMySQLBackup.Viewoldlog1Click(Sender: TObject);
begin
  ViewOldLogFile;
end;

end.

Popular posts from this blog

Another Delphi tool that I've been working with has been the Indy components. I've been using them for doing e-mail directly in my apps. I currently use the latest snapshot of the components (the web-site has instructions how to install the components) and make sure you use the OpenSSL libs (older versions need a special set of OpenSSL libs, but the newer version uses the regular libs). The problem I'm trying to solve with this is that there are offices in various states which need to send partial employee information to the organization headquarters on a monthly basis. In turn, on occasion, the headquarters might send an update to one or more of the offices. Integrating e-mail has the advantage of reducing the number of steps required to complete the task. Of course, doing this increases the complexity of the application and this increases the risk that the program will produce errors (and discourage end users). The features that this code has are as follows: 1. Conf...
Read more
OpenOffice.org Macro Over the next couple of posts, I'll be posting the portions of a script I used to Generate a listing of personnel from a database table in OpenOffice.org Writer using the Basic language interface. What tasks are performed: 1. Styles are made and modified 2. Data is read and inserted into pre-formatted tables 3. The tables are modified (cells are merged and split) 4. New pages are entered 5. Names are Indexed and Headings added to the table of contents 6. Table of contents and an index are generated 7. The Table of Contents is modified to include hyperlinks around the name of the table of contents entry In my work on this script, I got a lot of help from various public support sites reading previous posts and finding the answers I needed. As well, once I figured out how to use the documentation, I used that a lot to help figure out the parts that I couldn't find sample code for. Part 1: Changing styles. I made a procedure to change all the styles that I wa...
Read more
Here's the problem that I was facing: I'm lazy. Solution: Create a script that will work in a number of situations (potentially bad in some situations). I've written a couple of programs in Delphi which use MySQL as the back end database. One of the programs is deployed in a couple of places already and in one case, a far off location. I was having a challenge with database schema updates: maintaining a list of changes, keeping the installation process seamless, and having a manageable update process. I decided to take a break from Delphi coding and made a script in Python. I then used the script to exe utility and made my script into an executable which I now distribute with my small Delphi app. I'll warn you now, I'm a messy coder and I've just started to learn Python, but you might get some good ideas from the script below for your own coding. There are 4 functions: 1. Save the schema to a pickle file 2. Restore/update a schema from a pickle file 3. Backup...
Read more