sdupdsed.pas

SQLDirect Component Library is a light-weight Borland Database Engine replacement for Borland Delphi

{*******************************************************}
{							}
{       Delphi SQLDirect Component Library		}
{       SQLUpdate Editor Dialog	   			}
{                                                       }
{       Copyright (c) 1997,2005 by Yuri Sheino		}
{                                                       }
{*******************************************************}

unit SDUpdSEd;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, ComCtrls, Menus, Db,
  SDConsts, SDCommon, SDEngine;

type
  TSDUpdateSQLEditForm = class(TForm)
    pcUpdateSQL: TPageControl;
    tsOptions: TTabSheet;
    tsSQL: TTabSheet;
    pnlButton: TPanel;
    btOk: TButton;
    btCancel: TButton;
    lblSQLText: TLabel;
    meSQLText: TMemo;
    gbxGenSQL: TGroupBox;
    lblTableName: TLabel;
    cbTableName: TComboBox;
    lblKeyFields: TLabel;
    lbKeyFields: TListBox;
    lblUpdateFields: TLabel;
    lbUpdateFields: TListBox;
    btDataSetDefaults: TButton;
    btGenerateSQL: TButton;
    pmFields: TPopupMenu;
    miSelectAll: TMenuItem;
    miClearAll: TMenuItem;
    rgrStatementType: TRadioGroup;
    btGetTableFields: TButton;
    cbQuotedFields: TCheckBox;
    btSelectPrimKeys: TButton;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormCloseQuery(Sender: TObject; var CanClose: Boolean);
    procedure cbTableNameChange(Sender: TObject);
    procedure TableNameChanged(Sender: TObject);
    procedure GetTableFieldsClick(Sender: TObject);
    procedure DataSetDefaultsClick(Sender: TObject);
    procedure GenerateSQLClick(Sender: TObject);
    procedure SelectAllClick(Sender: TObject);
    procedure ClearAllClick(Sender: TObject);
    procedure StatementTypeClick(Sender: TObject);
    procedure SQLTextExit(Sender: TObject);
    procedure btSelectPrimKeysClick(Sender: TObject);
  private
    FRefreshSQL:TStrings;
    FModifySQL: TStrings;
    FInsertSQL: TStrings;
    FDeleteSQL: TStrings;
    FUpdateSQL: TSDUpdateSQL;
    FFieldInfo,
    FTableInfo: TStrings;
    function FindFieldAlias(const TableName, FieldName: string): string;
    procedure GenerateModifySQL;
    procedure GenerateInsertSQL;
    procedure GenerateDeleteSQL;
    procedure GenerateRefreshSQL;
    procedure LoadDefaultOptions;
    procedure LoadStatements;
    procedure SelectAllFields;
    procedure SetSelectedAllItems(lb: TListBox; State: Boolean);
  public
    procedure InitProperties;
    procedure SaveStatements;
    function QuoteName(const AIdentName: string): string;
    property UpdateSQL: TSDUpdateSQL read FUpdateSQL write FUpdateSQL;
  end;

var
  SDUpdateSQLEditForm: TSDUpdateSQLEditForm;

function EditUpdateSQL(AUpdateSQL: TSDUpdateSQL): Boolean;

implementation

{$R *.DFM}

function EditUpdateSQL(AUpdateSQL: TSDUpdateSQL): Boolean;
var
  Dlg: TSDUpdateSQLEditForm;
  sCaption: string;
begin
  Result := False;
  sCaption := '';
  Dlg := TSDUpdateSQLEditForm.Create(Application);
  try
    if (AUpdateSQL.Owner is TForm) then
      sCaption := (AUpdateSQL.Owner as TForm).Name + '.';

    sCaption := sCaption + AUpdateSQL.Name;
    if Assigned( AUpdateSQL.DataSet ) then
      sCaption := Format('%s (%s)', [sCaption, AUpdateSQL.DataSet.Name])
    else
      sCaption := Format('%s (%s)', [sCaption, SNoDataSet]);

    Dlg.Caption := sCaption;
    Dlg.UpdateSQL := AUpdateSQL;
    Dlg.InitProperties;
    if Dlg.ShowModal = mrOk then begin
      Dlg.SaveStatements;

      Result := True;
    end;
  finally
    Dlg.Free;
  end;
end;

{ TSDUpdateSQLEditForm }
procedure TSDUpdateSQLEditForm.InitProperties;
begin
  LoadDefaultOptions;
  SelectAllFields;

  LoadStatements;
  DataSetDefaultsClick( nil );  // at first load dataset fields only
  StatementTypeClick( nil );
end;

{ At first show dataset fields and disable "DataSet Defaults" button }
procedure TSDUpdateSQLEditForm.LoadDefaultOptions;
var
  SaveCursor: TCursor;
  i: Integer;
  q: TSDQuery;
begin
  if FUpdateSQL.DataSet = nil then
    Exit;
  if not(FUpdateSQL.DataSet is TSDQuery) then
    Exit;

  q := TSDQuery(FUpdateSQL.DataSet);
  SaveCursor := Screen.Cursor;
  try
    Screen.Cursor := crHourGlass;

    FFieldInfo.Clear;
    FTableInfo.Clear;

    q.GetFieldInfoFromSQL( q.Text, FFieldInfo, FTableInfo );

    cbTableName.Clear;
    lbKeyFields.Clear;
    lbUpdateFields.Clear;

    for i:=0 to FTableInfo.Count-1 do
      if Length( FTableInfo.Names[i] ) > 0 then
        cbTableName.Items.Add( FTableInfo.Values[FTableInfo.Names[i]] );

    if cbTableName.Items.Count > 0 then
      cbTableName.ItemIndex := 0;
  finally
    Screen.Cursor := SaveCursor;
  end;
end;

procedure TSDUpdateSQLEditForm.LoadStatements;
begin
  FRefreshSQL.Assign( FUpdateSQL.RefreshSQL );
  FModifySQL.Assign( FUpdateSQL.ModifySQL );
  FInsertSQL.Assign( FUpdateSQL.InsertSQL );
  FDeleteSQL.Assign( FUpdateSQL.DeleteSQL );
end;

procedure TSDUpdateSQLEditForm.SaveStatements;
begin
  FUpdateSQL.RefreshSQL.Assign( FRefreshSQL );
  FUpdateSQL.ModifySQL.Assign( FModifySQL );
  FUpdateSQL.InsertSQL.Assign( FInsertSQL );
  FUpdateSQL.DeleteSQL.Assign( FDeleteSQL );
end;

procedure TSDUpdateSQLEditForm.SelectAllFields;
begin
  pmFields.PopupComponent := lbKeyFields;
  SelectAllClick( nil );
  pmFields.PopupComponent := lbUpdateFields;
  SelectAllClick( nil );
end;

procedure TSDUpdateSQLEditForm.SetSelectedAllItems(lb: TListBox; State: Boolean);
var
  i: Integer;
begin
  for i:=0 to lb.Items.Count-1 do
    lb.Selected[i] := State;
end;

{
FFieldInfo.Strings[i] = "FieldAlias=[Owner.]Table.FieldName"
FTableInfo.Strings[i] = "Alias=TableName"

!!! same FieldName could be located in different tables with different aliases !!!

}
function TSDUpdateSQLEditForm.FindFieldAlias(const TableName, FieldName: string): string;
var
  i, j: Integer;
  s, sField, sTable: string;
begin
  Result := FieldName;

  for i:=0 to FFieldInfo.Count-1 do begin
    s := FFieldInfo.Values[FFieldInfo.Names[i]];
    j := Length(s);
    while j > 0 do begin
      if (s[j] = '.') and (j < Length(s)) then begin
      	// extract physical field and table name
        sField := Copy( s, j+1, Length(s)-j );
        sTable := Copy( s, 1, j-1 );

        if (sField = FieldName) and (sTable = TableName) then begin
		// return field alias (for parameter name)        
          Result := FFieldInfo.Names[i];
          Exit;
        end;
        Break;
      end;
      Dec( j );
    end;
  end;
end;

procedure TSDUpdateSQLEditForm.GenerateModifySQL;
const
  UpdateClause	= 'update ';
  SetClause	= 'set';
  WhereClause	= 'where';
  AndOp		= ' and';
  SetIndent	= '  ';
var
  sTableName, sStr, sField: string;
  i: Integer;
begin
  FModifySQL.Clear;

  sTableName := cbTableName.Text;
  FModifySQL.Add( UpdateClause + sTableName );
  FModifySQL.Add( SetClause );
	// add SET clause (update fields)
  with lbUpdateFields do
    for i:=0 to Items.Count-1 do
      if Selected[i] then begin
        sField := Items[i];
        sStr := Format( '%s%s = :%s',
        	[SetIndent, QuoteName(sField), QuoteName(FindFieldAlias(sTableName, sField))] );

        if FModifySQL.Strings[FModifySQL.Count-1] <> SetClause then
          FModifySQL.Strings[FModifySQL.Count-1] := FModifySQL.Strings[FModifySQL.Count-1] + ',';
        FModifySQL.Add( sStr );
      end;
	// add WHERE clause (key fields)
  FModifySQL.Add( WhereClause );
  with lbKeyFields do
    for i:=0 to Items.Count-1 do
      if Selected[i] then begin
        sField := lbKeyFields.Items[i];
        sStr := Format( '%s%s = :%s',
        	[SetIndent, QuoteName(sField), QuoteName('OLD_' + FindFieldAlias(sTableName, sField))] );

        if FModifySQL.Strings[FModifySQL.Count-1] <> WhereClause then
          FModifySQL.Strings[FModifySQL.Count-1] := FModifySQL.Strings[FModifySQL.Count-1] + AndOp;
        FModifySQL.Add( sStr );
      end;

end;

procedure TSDUpdateSQLEditForm.GenerateRefreshSQL;
const
  SelectClause	= 'select ';
  FromClause	= 'from ';
  WhereClause	= 'where';
  AndOp		= ' and';
  SetIndent	= '  ';
var
  sTableName, sStr, sField, sFields: string;
  i: Integer;
begin
  FRefreshSQL.Clear;
  sFields := '';

  sTableName := cbTableName.Text;
	// add fields in SELECT clause (refresh fields)
  with lbUpdateFields do
    for i:=0 to Items.Count-1 do
      if Selected[i] then begin
        sField := Items[i];
        if sFields <> '' then sFields := sFields + ', ';
        sFields := sFields + QuoteName(sField);
        sStr := FindFieldAlias(sTableName, sField);
        	// add a field alias, when it is necessary
        if sStr <> sField then
          sFields := sFields + ' as ' + QuoteName(sStr);
      end;
  FRefreshSQL.Add( SelectClause + sFields + #$0D#$0A + FromClause + sTableName );
	// add WHERE clause (key fields)
  FRefreshSQL.Add( WhereClause );
  with lbKeyFields do
    for i:=0 to Items.Count-1 do
      if Selected[i] then begin
        sField := lbKeyFields.Items[i];
        sStr := Format( '%s%s = :%s',
        	[SetIndent, QuoteName(sField), QuoteName('OLD_' + FindFieldAlias(sTableName, sField))] );

        if FRefreshSQL.Strings[FRefreshSQL.Count-1] <> WhereClause then
          FRefreshSQL.Strings[FRefreshSQL.Count-1] := FRefreshSQL.Strings[FRefreshSQL.Count-1] + AndOp;
        FRefreshSQL.Add( sStr );
      end;
end;