dbimportdata.pas

DBDesigner 4 is a database design system that integrates database design, modelling, creation and ma

unit DBImportData;

interface

uses
  SysUtils, Types, Classes, Variants, QTypes, QGraphics, QControls, QForms,
  QDialogs, QStdCtrls, QCheckLst, QButtons, QGrids, QComCtrls, QExtCtrls,
  Qt, IniFiles, SqlExpr, DBXpress, FMTBcd, DB, MainDM, DBDM;

type
  PTableImportOptions = ^TTableImportOptions;
  TTableImportOptions = record
    Tablename: string;
    TableDirectory: string;
    Tableindex: integer;
    TextImportWithSep: Boolean;
    TextImportFirstRowHoldsColumnNames: Boolean;
    TextImportTextDelim,
    TextImportTextSep: string;
    TextColumns: TStringList;
    TextTestLines: TStringList;
    ColumnNames: TStringList;
    DestTableName: string;
    DestColumns,
    DestColumnsValues: TStringList;
    DelDestTableBeforInserts,
    TrimColumns: Boolean;
    ReplaceChars: TStringList;
    CheckReturnsInTxt: Boolean;
    PresetName: string;
  end;

  TDBImportDataForm = class(TForm)
    DirDlg: TSaveDialog;
    Label4: TLabel;
    NewPresetBtn: TSpeedButton;
    Bevel2: TBevel;
    PresetLU: TComboBox;
    PageControl: TPageControl;
    TextOptionsSheet: TTabSheet;
    DataLbl: TLabel;
    FixedLengthHintLbl: TLabel;
    Bevel3: TBevel;
    Label12: TLabel;
    FixLengthOptionsGBox: TGroupBox;
    ColumnsLbl: TLabel;
    SetColsSBtn: TSpeedButton;
    IgnoreFirstLineCBox: TCheckBox;
    ColumnsEd: TEdit;
    SepOptionsGBox: TGroupBox;
    Label5: TLabel;
    Label6: TLabel;
    SepTabRBtn: TRadioButton;
    SepSemicolonRBtn: TRadioButton;
    SepCommaRBtn: TRadioButton;
    SepSpaceRBtn: TRadioButton;
    SepUserDefRBtn: TRadioButton;
    SepUserDefEd: TEdit;
    TextDelimLU: TComboBox;
    FirstRowHoldsColNameCBox: TCheckBox;
    TextWithSepRBtn: TRadioButton;
    TextGrid: TStringGrid;
    TextWithFixedLengthRBtn: TRadioButton;
    ColNameEd: TEdit;
    MappingSheet: TTabSheet;
    Label7: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    Label11: TLabel;
    DestTblLU: TComboBox;
    SourceDG: TDrawGrid;
    DestDG: TDrawGrid;
    SpecialFieldsLBox: TListBox;
    AutoMappingBtn: TBitBtn;
    TabSheet1: TTabSheet;
    GroupBox1: TGroupBox;
    DelDestTblCBox: TCheckBox;
    TrimColumnsCBox: TCheckBox;
    CheckReturnsInTxtCBox: TCheckBox;
    GroupBox2: TGroupBox;
    ReplaceChrGrid: TStringGrid;
    StatusPnl: TPanel;
    ConnectionSBtn: TSpeedButton;
    StatusLbl: TLabel;
    ModePageControl: TPageControl;
    TextImportSheet: TTabSheet;
    DBImportSheet: TTabSheet;
    DirEd: TEdit;
    Label2: TLabel;
    BrowseDirOrDBConnBtn: TSpeedButton;
    SourceLBox: TCheckListBox;
    Label3: TLabel;
    SubmitBtn: TSpeedButton;
    CancelBtn: TSpeedButton;
    Label1: TLabel;
    SourceDBConnEd: TEdit;
    GetSourceDBConnSBtn: TSpeedButton;
    SourceTblsLBox: TCheckListBox;
    Label8: TLabel;
    Label13: TLabel;
    DBConnEd: TEdit;
    GetDBConnSBtn: TSpeedButton;
    SourceSQLConn: TSQLConnection;
    SourceSQLDataSet: TSQLDataSet;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);

    procedure SetData(FileMode: Boolean; DirOrDBConn: string;
      DestTable: string = ''; DefaultMappingData: string = '');
    procedure RefreshFileList;
    procedure BrowseDirOrDBConnBtnClick(Sender: TObject);
    procedure DirEdKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure SourceLBoxClick(Sender: TObject);

    procedure ShowTableOptions(TblIndex: integer);
    procedure SetTableOptions(Sender: TObject);

    function GetTblOpt(nr: integer): PTableImportOptions;
    procedure TextGridDrawCell(Sender: TObject; ACol, ARow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure TextGridMouseUp(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure TextGridMouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure SetColsSBtnClick(Sender: TObject);
    procedure TextGridMouseMove(Sender: TObject; Shift: TShiftState; X,
      Y: Integer);
    procedure TextGridKeyUp(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure NewPresetBtnClick(Sender: TObject);

    procedure GetPresetList;
    procedure SavePresetToIniFile(name: string);
    procedure GetPresetsFromIniFile(name: string);

    procedure PresetLUCloseUp(Sender: TObject);
    procedure SourceDGDrawCell(Sender: TObject; ACol, ARow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure DestTblLUCloseUp(Sender: TObject);
    procedure DestDGDrawCell(Sender: TObject; ACol, ARow: Integer;
      Rect: TRect; State: TGridDrawState);
    procedure SourceDGMouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure DestDGDragOver(Sender, Source: TObject; X, Y: Integer;
      State: TDragState; var Accept: Boolean);
    procedure DestDGDragDrop(Sender, Source: TObject; X, Y: Integer);
    procedure DestDGKeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure SpecialFieldsLBoxMouseDown(Sender: TObject;
      Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
    procedure ImportBtnClick(Sender: TObject);
    procedure ColNameEdChange(Sender: TObject);

    procedure DisplayColName(Row: integer);
    procedure AutoMappingBtnClick(Sender: TObject);
    procedure ReplaceChrGridSetEditText(Sender: TObject; ACol,
      ARow: Integer; const Value: WideString);

    procedure GetSourceDBConnSBtnClick(Sender: TObject);
    procedure ModePageControlChange(Sender: TObject);
    procedure GetDBConnSBtnClick(Sender: TObject);
    procedure FirstRowHoldsColNameCBoxClick(Sender: TObject);
    procedure CancelBtnClick(Sender: TObject);

    procedure SetDestDBConn(theDBConn: Pointer);

    procedure SetDirectoryOrFilename(dirOrFileName: string);
  private
    { Private declarations }
    SourceDir: string;
    OptionsForTable: integer;
    ActiveTableOptions: PTableImportOptions;
    TblOptList: TList;

    MouseInDown: Boolean;
    IsSettingOptions: Boolean;

    SourceColumn: integer;

    DefaultMapping: TStringList;

    SourceDBConn, DestDBConn: TDBConn;
  public
    { Public declarations }
    Version: string;
  end;

var
  DBImportDataForm: TDBImportDataForm;

implementation

uses EditorString, Progress;

{$R *.xfm}

function SortStringListWithInts(List: TStringList; Index1, Index2: Integer): Integer;
begin
  SortStringListWithInts:=0;
  try
    if(StrToInt(List[Index1])<StrToInt(List[Index2]))then
      SortStringListWithInts:=-1
    else if(StrToInt(List[Index1])>StrToInt(List[Index2]))then
      SortStringListWithInts:=1;
  except
  end;
end;

procedure TDBImportDataForm.FormCreate(Sender: TObject);
begin
  Version:='1.0.0.24';

  SourceDir:='';
  OptionsForTable:=-1;
  ActiveTableOptions:=nil;
  TblOptList:=TList.Create;
  MouseInDown:=False;
  IsSettingOptions:=False;

  DefaultMapping:=TStringList.Create;

  DestDG.ColCount:=2;
  DestDG.ColWidths[0]:=80;
  DestDG.ColWidths[1]:=250;

  GetPresetList;

  PageControl.ActivePageIndex:=0;

  ReplaceChrGrid.Cells[0, 0]:='Char';
  ReplaceChrGrid.Cells[1, 0]:='Replace with';

  SourceDir:=DMMain.LoadValueFromSettingsIniFile('RecentDirectories', 'RecentImportDataFileDir', '');

  //Center Window but beware of two screens
  Top:=(Screen.Height-Height) div 2;
  if(Screen.Width=(Screen.Height/0.75)*2)or
    (Screen.Width=(Screen.Height*1.25)*2)then
    Left:=((Screen.Width div 2)-Width) div 2
  else
    Left:=(Screen.Width-Width) div 2;

  StatusLbl.Caption:='Not connected to a Database. Version '+Version;

  RefreshFileList;
end;

procedure TDBImportDataForm.FormDestroy(Sender: TObject);
var i: integer;
begin
  for i:=0 to TblOptList.Count-1 do
  begin
    PTableImportOptions(TblOptList[i]).TextColumns.Free;
    PTableImportOptions(TblOptList[i]).TextTestLines.Free;
    PTableImportOptions(TblOptList[i]).ColumnNames.Free;
    PTableImportOptions(TblOptList[i]).DestColumns.Free;
    PTableImportOptions(TblOptList[i]).DestColumnsValues.Free;
    PTableImportOptions(TblOptList[i]).ReplaceChars.Free;

    dispose(TblOptList[i]);
  end;

  TblOptList.Free;

  DefaultMapping.Free;
end;

procedure TDBImportDataForm.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  if(ModalResult<>mrOK)then
    ModalResult:=mrAbort;
end;

procedure TDBImportDataForm.SetData(FileMode: Boolean;
  DirOrDBConn: string; DestTable: string = ''; DefaultMappingData: string = '');
var theTables: TStringList;
begin
  if(FileMode)then
  begin
    ModePageControl.ActivePage:=TextImportSheet;

    //Wenn keine Filename sondern Dir 黚ergeben wurde...
    if(ExtractFileName(DirOrDBConn)='')then
    begin
      SourceDir:=DirOrDBConn;
      RefreshFileList;
    end
    else
    begin
      SourceLBox.Clear;
      SourceDir:=ExtractFilePath(DirOrDBConn);
      DirEd.Text:=SourceDir;
      SourceLBox.Items.Add(ExtractFileName(DirOrDBConn));

      SourceLBox.ItemIndex:=0;
      SourceLBox.Checked[0]:=True;
      SourceLBoxClick(self);
    end;

  end
  else
  begin
    ModePageControl.ActivePage:=DBImportSheet;
  end;

  theTables:=TStringList.Create;
  try
    DMDB.GetDBTables(theTables);
    DestTblLU.Items.Assign(theTables);

    DestTblLU.ItemIndex:=DestTblLU.Items.IndexOf(DestTable);

    if(DestTblLU.ItemIndex=-1)then