--- ibx/trunk/examples/fbsql/fbsql.lpr 2016/12/06 10:33:46 46 +++ ibx/trunk/examples/fbsql/fbsql.lpr 2017/01/09 15:31:51 47 @@ -31,20 +31,20 @@ uses {$IFDEF UNIX}{$IFDEF UseCThreads} cthreads, {$ENDIF}{$ENDIF} + {$IFDEF WINDOWS} Windows, {$ENDIF} Classes, SysUtils, CustApp { you can add units after this } - ,IBDatabase, ibxscript, IBExtract, IBQuery, DB; + ,IBDatabase, ibxscript, IBExtract, DB, IBVersion, + IBDataOutput, RegExpr + {$IFDEF UNIX} ,TermIO, IOStream {$ENDIF} -resourcestring + ; - sUnknownField = 'Unknown Field Type'; - sBadGraphic = 'Unable to generate CSV data for a Graphic Field'; - sBadParadox = 'Unable to generate CSV data for a Paradox OLE Field'; - sBadDBase = 'Unable to generate CSV data for a DBase OLE Field'; - sBadBinary = 'Unable to generate CSV data for a Binary Field'; - sBadCursor = 'Unable to generate CSV data for a Cursor Field'; +const + FExceptionTrapped: boolean = false; type + TInteractiveSQLProcessor = class; { TFBSQL } @@ -53,123 +53,161 @@ type FIBDatabase: TIBDatabase; FIBTransaction: TIBTransaction; FIBXScript: TIBXScript; + FISQLProcessor: TInteractiveSQLProcessor; FExtract: TIBExtract; - FQuery: TIBQuery; - FSQL: TStringStream; + FOutputFile: TStream; + FDataOutputFormatter: TDataOutputFormatter; procedure LogHandler(Sender: TObject; Msg: string); procedure ErrorLogHandler(Sender: TObject; Msg: string); - procedure HandleSelectSQL(Sender: TObject; SQLText: string); - procedure WriteCSV; + procedure loginPrompt(Database: TIBDatabase; LoginParams: TStrings); protected procedure DoRun; override; procedure ShowException(E: Exception); override; public constructor Create(TheOwner: TComponent); override; - destructor Destroy; override; procedure WriteHelp; virtual; end; -{ TFBSQL } + { TInteractiveSQLProcessor } -procedure TFBSQL.LogHandler(Sender: TObject; Msg: string); -begin - writeln( Msg); -end; + {This is a TCustomIBXScript descendent that uses the console for input/output. + It additionally suported QUIT/EXIT Commands. The log file can either be redirected + to the console or sent to a separate file.} -procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string); -begin - writeln(stderr, Msg); -end; + TInteractiveSQLProcessor = class(TCustomIBXScript) + private + FUseLogFile: boolean; + protected + procedure Add2Log(const Msg: string; IsError: boolean=true); override; + function ProcessStatement(stmt: string): boolean; override; + public + constructor Create(aOwner: TComponent); override; + procedure Run; + property UseLogFile: boolean read FUseLogFile write FUseLogFile; + end; -procedure TFBSQL.HandleSelectSQL(Sender: TObject; SQLText: string); +{$IFDEF UNIX} +function getpassword: string; +var oldattr, newattr: termios; + stdinStream: TIOStream; + c: char; begin - FQuery.SQL.Text := SQLText; - FQuery.Active := true; + Result := ''; + stdinStream := TIOStream.Create(iosInput); try - WriteCSV; + TCGetAttr(stdinStream.Handle, oldattr); + newattr := oldattr; + newattr.c_lflag := newattr.c_lflag and not (ICANON or ECHO); + TCSetAttr( stdinStream.Handle, TCSANOW, newattr ); + try + repeat + read(c); + if c = #10 then break; + write('*'); + Result += c; + until false; + writeln; + finally + TCSetAttr( stdinStream.Handle, TCSANOW, oldattr ); + end; finally - FQuery.Active := false; + stdinStream.Free; end; end; +{$ENDIF} +{$IFDEF WINDOWS} +function getpassword: string; +var oldmode, newmode: DWORD; + c: char; +begin + Result := ''; + GetConsoleMode(GetStdHandle(STD_INPUT_HANDLE), oldmode); + newmode := oldmode - ENABLE_ECHO_INPUT - ENABLE_LINE_INPUT; + SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),newmode); + try + repeat + read(c); + if c = #13 then break; + write('*'); + Result += c; + until false; + writeln; + finally + SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),oldmode); + end +end; +{$ENDIF} -procedure TFBSQL.WriteCSV; - - procedure WriteQuotedText(Text: string); - var Index: integer; - begin - Index := 1; - while Index <= Length(Text) do - if Text[Index] = '"' then - begin - Insert('"',Text,Index); - Inc(Index,2) - end - else - Inc(Index,1); - write('"' + Text + '"') - end; +{ TInteractiveSQLProcessor } - procedure WriteFieldList(Fields: TFields); - var I: integer; - begin - for I := 0 to Fields.Count - 1 do - begin - if I > 0 then write(','); - write(Fields[I].FieldName) - end; - writeln; - end; +procedure TInteractiveSQLProcessor.Add2Log(const Msg: string; IsError: boolean); +begin + if UseLogFile then + inherited Add2Log(Msg,IsError) + else + if IsError then + writeln(stderr,msg) + else + writeln(msg); +end; - procedure WriteRecord; - var I: integer; +function TInteractiveSQLProcessor.ProcessStatement(stmt: string): boolean; +var RegexObj: TRegExpr; + Terminator: char; + ucStmt: string; +begin + Result := inherited ProcessStatement(stmt); + if not Result then begin - with FQuery do - begin - for I := 0 to FieldCount - 1 do + Terminator := FSymbolStream.Terminator; + ucStmt := AnsiUpperCase(stmt); + RegexObj := TRegExpr.Create; + try + RegexObj.Expression := '^ *(QUIT|EXIT) *(\' + Terminator + '|)'; + if RegexObj.Exec(ucStmt) then begin - if I <> 0 then write(','); - case Fields[I].DataType of - ftUnknown: raise Exception.Create(sUnknownField); - ftString: WriteQuotedText(Fields[I].AsString); - ftSmallint, - ftInteger, - ftWord, - ftLargeInt, - ftBoolean: write(Fields[I].DisplayText); - ftFloat, - ftCurrency, - ftFmtBCD, - ftBCD: write(Fields[I].AsString); - ftDate, - ftTime: write(DateTimeToStr(Fields[I].AsDateTime)); - ftDateTime: WriteQuotedText(Fields[I].AsString); - ftBytes, - ftVarBytes, - ftBlob, - ftAutoInc: write(Fields[I].AsString); - ftMemo: WriteQuotedText(Fields[I].AsString); - ftGraphic: raise Exception.Create(sBadGraphic); - ftFmtMemo: WriteQuotedText(Fields[I].AsString); - ftParadoxOle: raise Exception.Create(sBadParadox); - ftDBaseOle: raise Exception.Create(sBadDBase); - ftTypedBinary:raise Exception.Create(sBadBinary); - ftCursor: raise Exception.Create(sBadCursor); - end + TInteractiveSymbolStream(FSymbolStream).Terminated := true; + Result := true; end; - writeln; + finally + RegexObj.Free; end; end; +end; + +constructor TInteractiveSQLProcessor.Create(aOwner: TComponent); begin - with FQuery do - begin - WriteFieldList(Fields); - First; - while not EOF do - begin - WriteRecord; - Next - end; - end + inherited Create(aOwner); + FSymbolStream := TInteractiveSymbolStream.Create; +end; + +procedure TInteractiveSQLProcessor.Run; +begin + ProcessStream; +end; + +{ TFBSQL } + +procedure TFBSQL.LogHandler(Sender: TObject; Msg: string); +begin + if FOutputFile <> nil then + FOutputFile.WriteAnsiString(Msg + LineEnding) + else + writeln( Msg); +end; + +procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string); +begin + writeln(stderr, Msg); +end; + +procedure TFBSQL.loginPrompt(Database: TIBDatabase; LoginParams: TStrings); +var password: string; +begin + write(LoginParams.Values['user_name'] + '''s Password:'); + password := getpassword; + if password <> '' then + LoginParams.Values['password'] := password; end; procedure TFBSQL.DoRun; @@ -177,12 +215,31 @@ var ErrorMsg: String; SQLFileName: string; DoExtract: boolean; + OutputFileName: string; i: integer; + ExtractTypes: TExtractTypes; + Opts,NonOpts: TStrings; + OutputFormat: string; + SQLStatement: string; begin - writeln(stderr,'fbsql: a non-interactive SQL interpreter for Firebird'); - writeln(stderr,'Copyright (c) MWA Software 2016'); + writeln(stderr,'fbsql: an SQL interpreter for Firebird'); + writeln(stderr,'Built using IBX ' + IBX_VERSION); + writeln(stderr,'Copyright (c) MWA Software 2017'); + // quick check parameters - ErrorMsg:=CheckOptions('ahbeufprs',['help','user','pass','role']); + Opts := TStringList.Create; + NonOpts := TStringList.Create; + try + ErrorMsg := CheckOptions('aAhbeu:i:o:p:r:s:t:',['help','user','pass','role'],Opts,NonOpts); + {Database name is last parameter if given and not an option} + if (NonOpts.Count > 0) and ((Opts.Count = 0) or + ((Opts.ValueFromIndex[Opts.Count-1] <> NonOpts[NonOpts.Count-1])) or + (ParamCount = 1) or (ParamStr(ParamCount-1)[2] in ['!','A','h','b','e']))then + FIBDatabase.DatabaseName := ParamStr(ParamCount); + finally + Opts.Free; + NonOpts.Free; + end; if ErrorMsg<>'' then begin ShowException(Exception.Create(ErrorMsg)); Terminate; @@ -190,7 +247,7 @@ begin end; // parse parameters - if HasOption('h','help') or (ParamCount = 0) then + if HasOption('h','help') then begin WriteHelp; Terminate; @@ -198,7 +255,11 @@ begin end; SQLFileName := ''; + OutputFileName := ''; DoExtract := false; + ExtractTypes := []; + FDataOutputFormatter := TIBBlockFormatOut; + SQLStatement := ''; {Initialise user_name and password from environment if available} @@ -210,73 +271,109 @@ begin {Process Command line options} - if HasOption('u','user') then - FIBDatabase.Params.Add('user_name=' + GetOptionValue('u','user')); - - if HasOption('p','pass') then - FIBDatabase.Params.Add('password=' + GetOptionValue('p','pass')); - - if HasOption('r','role') then - FIBDatabase.Params.Add('sql_role_name=' + GetOptionValue('r','role')); + if HasOption('a') then + DoExtract := true; - if (ParamCount >= 1) and (ParamStr(ParamCount)[1] <> '-') then - FIBDatabase.DatabaseName := ParamStr(ParamCount) - else - raise Exception.Create('Database Name Missing'); + if HasOption('A') then + begin + DoExtract := true; + ExtractTypes := [etData]; + end; if not HasOption('b') then + begin FIBXScript.StopOnFirstError := false; + FISQLProcessor.StopOnFirstError := false; + end; if not HasOption('e') then FIBXScript.Echo := false; - if HasOption('a') then - DoExtract := true; + if HasOption('i') then + SQLFileName := GetOptionValue('i'); - if HasOption('f') then - SQLFileName := GetOptionValue('f'); + if HasOption('o') then + begin + OutputFileName := GetOptionValue('o'); + FISQLProcessor.UseLogFile := true; + end; + + if HasOption('p','pass') then + FIBDatabase.Params.Values['password'] := GetOptionValue('p','pass'); + + if HasOption('r','role') then + FIBDatabase.Params.Values['sql_role_name'] := GetOptionValue('r','role'); if HasOption('s') then + SQLStatement := GetOptionValue('s'); + + if HasOption('t') then begin - FSQL.WriteString(GetOptionValue('s')); - FSQL.Position := 0; + OutputFormat := GetOptionValue('t'); + if OutputFormat = 'CSV' then + FDataOutputFormatter := TIBCSVDataOut + else + if OutputFormat = 'INS' then + FDataOutputFormatter := TIBInsertStmtsOut + else + if OutputFormat = 'BLK' then + FDataOutputFormatter := TIBBlockFormatOut + else + raise Exception.CreateFmt('Unrecognised data output format "%s"',[OutputFormat]); end; + if HasOption('u','user') then + FIBDatabase.Params.Values['user_name'] := GetOptionValue('u','user'); + {Validation} + FIBDatabase.LoginPrompt := (FIBDatabase.Params.IndexOfName('user_name') <> -1) and + (FIBDatabase.Params.Values['password'] = ''); + if not DoExtract then begin - if (SQLFileName = '') and (FSQL.DataString = '') then - raise Exception.Create('An SQL File must be provided'); - - if (FSQL.DataString <> '') and (SQLFileName <> '') then - raise Exception.Create('An SQL Script File and text cannot be simulateously requested'); + if (SQLStatement <> '') and (SQLFileName <> '') then + raise Exception.Create('An SQL Script File and text cannot be simultaneously requested'); - if (FSQL.DataString = '') and not FileExists(SQLFileName) then + if (SQLStatement = '') and (SQLFileName <> '') and not FileExists(SQLFileName) then raise Exception.CreateFmt('SQL File "%s" not found!',[SQLFileName]); end; - if DoExtract and ((SQLFileName <> '') or (FSQL.DataString <> '')) then + if DoExtract and ((SQLFileName <> '') or (SQLStatement <> '')) then raise Exception.Create('Extract and script execution cannot be simulateously requested'); {This is where it all happens} - FIBDatabase.Connected := true; + FIBXScript.DataOutputFormatter := FDataOutputFormatter.Create(self); + FISQLProcessor.DataOutputFormatter := FDataOutputFormatter.Create(self); + + if OutputFileName <> '' then + FOutputFile := TFileStream.Create(OutputFileName,fmCreate); + + FIBDatabase.Connected := FIBDatabase.DatabaseName <> ''; try if DoExtract then begin - FExtract.ExtractObject(eoDatabase); + FExtract.ExtractObject(eoDatabase,'',ExtractTypes); + if FOutputFile <> nil then + FExtract.Items.SaveToStream(FOutputFile) + else for i := 0 to FExtract.Items.Count - 1 do writeln(FExtract.Items[i]); end else - if FSQL.DataString = '' then - FIBXScript.PerformUpdate(SQLFileName,true) + if SQLFileName <> '' then + FIBXScript.RunScript(SQLFileName) + else + if SQLStatement <> '' then + FIBXScript.ExecSQLScript(SQLStatement) else - FIBXScript.PerformUpdate(FSQL,true); + FISQLProcessor.Run; finally FIBDatabase.Connected := false; + if FOutputFile <> nil then + FOutputFile.Free; end; @@ -286,6 +383,7 @@ end; procedure TFBSQL.ShowException(E: Exception); begin + FExceptionTrapped := true; writeln(stderr,'Error: ' + E.Message); end; @@ -293,10 +391,12 @@ constructor TFBSQL.Create(TheOwner: TCom begin inherited Create(TheOwner); StopOnException:=True; - FSQL := TStringStream.Create(''); { Create Components } FIBDatabase := TIBDatabase.Create(self); + FIBDatabase.OnLogin := @loginPrompt; + FIBDatabase.Params.Clear; + FIBDatabase.Params.Values['lc_ctype'] := 'UTF8'; FIBTransaction := TIBTransaction.Create(self); FIBTransaction.DefaultDatabase := FIBDatabase; FIBXScript := TIBXScript.Create(self); @@ -304,39 +404,38 @@ begin FIBXScript.Transaction := FIBTransaction; FIBXScript.OnOutputLog := @LogHandler; FIBXScript.OnErrorLog := @ErrorLogHandler; - FIBXScript.OnSelectSQL := @HandleSelectSQL; + FISQLProcessor := TInteractiveSQLProcessor.Create(self); + FISQLProcessor.Database := FIBDatabase; + FISQLProcessor.Transaction := FIBTransaction; + FISQLProcessor.OnOutputLog := @LogHandler; + FISQLProcessor.OnErrorLog := @ErrorLogHandler; FExtract := TIBExtract.Create(self); FExtract.Database := FIBDatabase; FExtract.Transaction := FIBTransaction; - FQuery := TIBQuery.Create(self); - FQuery.AllowAutoActivateTransaction := true; - FQuery.Database := FIBDatabase; - FQuery.Transaction := FIBTransaction; FIBTransaction.Params.Add('concurrency'); FIBTransaction.Params.Add('wait'); - FIBDatabase.Params.Add('lc_ctype=UTF8'); end; -destructor TFBSQL.Destroy; -begin - if assigned(FSQL) then FSQL.Free; - inherited Destroy; -end; - procedure TFBSQL.WriteHelp; begin writeln(stderr,'Usage: ',ExtractFileName(ExeName),' '); writeln(stderr,'Options:'); writeln(stderr,'-a write database metadata to stdout'); + writeln(stderr,'-A write database metadata and table data to stdout'); writeln(stderr,'-b stop on first error'); writeln(stderr,'-e echo sql statements to stdout'); - writeln(stderr,'-f execute SQL script from file'); + writeln(stderr,'-i execute SQL script from file'); writeln(stderr,'-h show this information'); + writeln(stderr,'-o output to this file instead of stdout'); writeln(stderr,'-p provide password on command line (insecure)'); writeln(stderr,'-r open database with this rolename'); writeln(stderr,'-s Execute SQL text'); + writeln(stderr,'-t specify output format for SQL Statements'); + writeln(stderr,' BLK (default) for block format'); + writeln(stderr,' CSV (default) for CSV format'); + writeln(stderr,' INS (default) for Insert Statement format'); writeln(stderr,'-u open database with this username (defaults to SYSDBA)'); writeln; writeln(stderr,'Environment Variables:'); @@ -348,8 +447,9 @@ var Application: TFBSQL; begin Application:=TFBSQL.Create(nil); - Application.Title:='fbsql'; Application.Run; Application.Free; + if FExceptionTrapped then + Halt(1); end.