1 |
(*
|
2 |
* IBX For Lazarus (Firebird Express)
|
3 |
*
|
4 |
* The contents of this file are subject to the Initial Developer's
|
5 |
* Public License Version 1.0 (the "License"); you may not use this
|
6 |
* file except in compliance with the License. You may obtain a copy
|
7 |
* of the License here:
|
8 |
*
|
9 |
* http://www.firebirdsql.org/index.php?op=doc&id=idpl
|
10 |
*
|
11 |
* Software distributed under the License is distributed on an "AS
|
12 |
* IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
|
13 |
* implied. See the License for the specific language governing rights
|
14 |
* and limitations under the License.
|
15 |
*
|
16 |
* The Initial Developer of the Original Code is Tony Whyman.
|
17 |
*
|
18 |
* The Original Code is (C) 2014 Tony Whyman, MWA Software
|
19 |
* (http://www.mwasoftware.co.uk).
|
20 |
*
|
21 |
* All Rights Reserved.
|
22 |
*
|
23 |
* Contributor(s): ______________________________________.
|
24 |
*
|
25 |
*)
|
26 |
program fbsql;
|
27 |
|
28 |
{$mode objfpc}{$H+}
|
29 |
|
30 |
uses
|
31 |
{$IFDEF UNIX}{$IFDEF UseCThreads}
|
32 |
cthreads,
|
33 |
{$ENDIF}{$ENDIF}
|
34 |
{$IFDEF WINDOWS} Windows, {$ENDIF}
|
35 |
Classes, SysUtils, CustApp
|
36 |
{ you can add units after this }
|
37 |
,IBDatabase, ibxscript, IBExtract, DB, IBVersion,
|
38 |
IBDataOutput, RegExpr
|
39 |
{$IFDEF UNIX} ,TermIO, IOStream {$ENDIF}
|
40 |
|
41 |
;
|
42 |
|
43 |
const
|
44 |
FExceptionTrapped: boolean = false;
|
45 |
|
46 |
type
|
47 |
TInteractiveSQLProcessor = class;
|
48 |
|
49 |
{ TFBSQL }
|
50 |
|
51 |
TFBSQL = class(TCustomApplication)
|
52 |
private
|
53 |
FIBDatabase: TIBDatabase;
|
54 |
FIBTransaction: TIBTransaction;
|
55 |
FIBXScript: TIBXScript;
|
56 |
FISQLProcessor: TInteractiveSQLProcessor;
|
57 |
FExtract: TIBExtract;
|
58 |
FOutputFile: TStream;
|
59 |
FDataOutputFormatter: TDataOutputFormatter;
|
60 |
FPromptedForPassword: string;
|
61 |
procedure LogHandler(Sender: TObject; Msg: string);
|
62 |
procedure ErrorLogHandler(Sender: TObject; Msg: string);
|
63 |
procedure loginPrompt(Database: TIBDatabase; LoginParams: TStrings);
|
64 |
protected
|
65 |
procedure DoRun; override;
|
66 |
procedure ShowException(E: Exception); override;
|
67 |
public
|
68 |
constructor Create(TheOwner: TComponent); override;
|
69 |
procedure WriteHelp; virtual;
|
70 |
end;
|
71 |
|
72 |
{ TInteractiveSQLProcessor }
|
73 |
|
74 |
{This is a TCustomIBXScript descendent that uses the console for input/output.
|
75 |
It additionally supports QUIT/EXIT Commands. The log file can either be redirected
|
76 |
to the console or sent to a separate file.}
|
77 |
|
78 |
TInteractiveSQLProcessor = class(TCustomIBXScript)
|
79 |
private
|
80 |
FUseLogFile: boolean;
|
81 |
protected
|
82 |
procedure Add2Log(const Msg: string; IsError: boolean=true); override;
|
83 |
function ProcessStatement(stmt: string): boolean; override;
|
84 |
public
|
85 |
constructor Create(aOwner: TComponent); override;
|
86 |
procedure Run;
|
87 |
property UseLogFile: boolean read FUseLogFile write FUseLogFile;
|
88 |
end;
|
89 |
|
90 |
{$IFDEF UNIX}
|
91 |
function getpassword: string;
|
92 |
var oldattr, newattr: termios;
|
93 |
stdinStream: TIOStream;
|
94 |
c: char;
|
95 |
begin
|
96 |
Result := '';
|
97 |
stdinStream := TIOStream.Create(iosInput);
|
98 |
try
|
99 |
TCGetAttr(stdinStream.Handle, oldattr);
|
100 |
newattr := oldattr;
|
101 |
newattr.c_lflag := newattr.c_lflag and not (ICANON or ECHO);
|
102 |
TCSetAttr( stdinStream.Handle, TCSANOW, newattr );
|
103 |
try
|
104 |
repeat
|
105 |
read(c);
|
106 |
if c = #10 then break;
|
107 |
write('*');
|
108 |
Result += c;
|
109 |
until false;
|
110 |
writeln;
|
111 |
finally
|
112 |
TCSetAttr( stdinStream.Handle, TCSANOW, oldattr );
|
113 |
end;
|
114 |
finally
|
115 |
stdinStream.Free;
|
116 |
end;
|
117 |
end;
|
118 |
{$ENDIF}
|
119 |
{$IFDEF WINDOWS}
|
120 |
function getpassword: string;
|
121 |
var oldmode, newmode: DWORD;
|
122 |
c: char;
|
123 |
begin
|
124 |
Result := '';
|
125 |
GetConsoleMode(GetStdHandle(STD_INPUT_HANDLE), oldmode);
|
126 |
newmode := oldmode - ENABLE_ECHO_INPUT - ENABLE_LINE_INPUT;
|
127 |
SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),newmode);
|
128 |
try
|
129 |
repeat
|
130 |
read(c);
|
131 |
if c = #13 then break;
|
132 |
write('*');
|
133 |
Result += c;
|
134 |
until false;
|
135 |
writeln;
|
136 |
finally
|
137 |
SetConsoleMode(GetStdHandle(STD_INPUT_HANDLE),oldmode);
|
138 |
end
|
139 |
end;
|
140 |
{$ENDIF}
|
141 |
|
142 |
{ TInteractiveSQLProcessor }
|
143 |
|
144 |
procedure TInteractiveSQLProcessor.Add2Log(const Msg: string; IsError: boolean);
|
145 |
begin
|
146 |
if UseLogFile then
|
147 |
inherited Add2Log(Msg,IsError)
|
148 |
else
|
149 |
if IsError then
|
150 |
writeln(stderr,msg)
|
151 |
else
|
152 |
writeln(msg);
|
153 |
end;
|
154 |
|
155 |
function TInteractiveSQLProcessor.ProcessStatement(stmt: string): boolean;
|
156 |
var RegexObj: TRegExpr;
|
157 |
Terminator: char;
|
158 |
ucStmt: string;
|
159 |
begin
|
160 |
Result := inherited ProcessStatement(stmt);
|
161 |
if not Result then
|
162 |
begin
|
163 |
Terminator := SQLStatementReader.Terminator;
|
164 |
ucStmt := AnsiUpperCase(stmt);
|
165 |
RegexObj := TRegExpr.Create;
|
166 |
try
|
167 |
RegexObj.Expression := '^ *(QUIT|EXIT) *(\' + Terminator + '|)';
|
168 |
if RegexObj.Exec(ucStmt) then
|
169 |
begin
|
170 |
TInteractiveSQLStatementReader(SQLStatementReader).Terminated := true;
|
171 |
Result := true;
|
172 |
end;
|
173 |
finally
|
174 |
RegexObj.Free;
|
175 |
end;
|
176 |
end;
|
177 |
end;
|
178 |
|
179 |
constructor TInteractiveSQLProcessor.Create(aOwner: TComponent);
|
180 |
begin
|
181 |
inherited Create(aOwner);
|
182 |
SetSQLStatementReader(TInteractiveSQLStatementReader.Create);
|
183 |
Echo := true;
|
184 |
end;
|
185 |
|
186 |
procedure TInteractiveSQLProcessor.Run;
|
187 |
begin
|
188 |
ProcessStream;
|
189 |
end;
|
190 |
|
191 |
{ TFBSQL }
|
192 |
|
193 |
procedure TFBSQL.LogHandler(Sender: TObject; Msg: string);
|
194 |
begin
|
195 |
if FOutputFile <> nil then
|
196 |
FOutputFile.WriteAnsiString(Msg + LineEnding)
|
197 |
else
|
198 |
writeln( Msg);
|
199 |
end;
|
200 |
|
201 |
procedure TFBSQL.ErrorLogHandler(Sender: TObject; Msg: string);
|
202 |
begin
|
203 |
writeln(stderr, Msg);
|
204 |
end;
|
205 |
|
206 |
procedure TFBSQL.loginPrompt(Database: TIBDatabase; LoginParams: TStrings);
|
207 |
var password: string;
|
208 |
begin
|
209 |
if LoginParams.Values['password'] <> '' then Exit;
|
210 |
write(LoginParams.Values['user_name'] + '''s Password:');
|
211 |
password := getpassword;
|
212 |
if password <> '' then
|
213 |
LoginParams.Values['password'] := password;
|
214 |
end;
|
215 |
|
216 |
procedure TFBSQL.DoRun;
|
217 |
var
|
218 |
ErrorMsg: String;
|
219 |
SQLFileName: string;
|
220 |
DoExtract: boolean;
|
221 |
OutputFileName: string;
|
222 |
i: integer;
|
223 |
ExtractTypes: TExtractTypes;
|
224 |
Opts,NonOpts: TStrings;
|
225 |
OutputFormat: string;
|
226 |
SQLStatement: string;
|
227 |
begin
|
228 |
writeln(stderr,'fbsql: an SQL interpreter for Firebird');
|
229 |
writeln(stderr,'Built using IBX ' + IBX_VERSION);
|
230 |
writeln(stderr,'Copyright (c) MWA Software ' + system.copy({$I %DATE%},1,4));
|
231 |
|
232 |
// quick check parameters
|
233 |
Opts := TStringList.Create;
|
234 |
NonOpts := TStringList.Create;
|
235 |
try
|
236 |
ErrorMsg := CheckOptions('aAhbegu:i:o:p:r:s:t:c:',['help','user','pass','role'],Opts,NonOpts);
|
237 |
{Database name is last parameter if given and not an option}
|
238 |
if (NonOpts.Count > 0) and ((Opts.Count = 0) or
|
239 |
((Opts.ValueFromIndex[Opts.Count-1] <> NonOpts[NonOpts.Count-1])) or
|
240 |
(ParamCount = 1) or (ParamStr(ParamCount-1)[2] in ['!','A','h','b','e','g']))then
|
241 |
FIBDatabase.DatabaseName := ParamStr(ParamCount);
|
242 |
finally
|
243 |
Opts.Free;
|
244 |
NonOpts.Free;
|
245 |
end;
|
246 |
if ErrorMsg<>'' then begin
|
247 |
ShowException(Exception.Create(ErrorMsg));
|
248 |
Terminate;
|
249 |
Exit;
|
250 |
end;
|
251 |
|
252 |
// parse parameters
|
253 |
if HasOption('h','help') then
|
254 |
begin
|
255 |
WriteHelp;
|
256 |
Terminate;
|
257 |
Exit;
|
258 |
end;
|
259 |
|
260 |
SQLFileName := '';
|
261 |
OutputFileName := '';
|
262 |
DoExtract := false;
|
263 |
ExtractTypes := [];
|
264 |
FDataOutputFormatter := TIBBlockFormatOut;
|
265 |
SQLStatement := '';
|
266 |
|
267 |
{Initialise user_name and password from environment if available}
|
268 |
|
269 |
if GetEnvironmentVariable('ISC_USER') <> '' then
|
270 |
FIBDatabase.Params.Add('user_name=' + GetEnvironmentVariable('ISC_USER'));
|
271 |
|
272 |
if GetEnvironmentVariable('ISC_PASSWORD') <> '' then
|
273 |
FIBDatabase.Params.Add('password=' + GetEnvironmentVariable('ISC_PASSWORD'));
|
274 |
|
275 |
{Process Command line options}
|
276 |
|
277 |
if HasOption('a') then
|
278 |
begin
|
279 |
DoExtract := true;
|
280 |
end;
|
281 |
|
282 |
if HasOption('A') then
|
283 |
begin
|
284 |
DoExtract := true;
|
285 |
ExtractTypes := [etData];
|
286 |
end;
|
287 |
|
288 |
if not HasOption('b') then
|
289 |
begin
|
290 |
FIBXScript.StopOnFirstError := false;
|
291 |
FISQLProcessor.StopOnFirstError := false;
|
292 |
end;
|
293 |
|
294 |
if not HasOption('e') then
|
295 |
FIBXScript.Echo := false;
|
296 |
|
297 |
if HasOption('i') then
|
298 |
SQLFileName := GetOptionValue('i');
|
299 |
|
300 |
if HasOption('g')then
|
301 |
ExtractTypes += [etGrantsToUser];
|
302 |
|
303 |
if HasOption('o') then
|
304 |
begin
|
305 |
OutputFileName := GetOptionValue('o');
|
306 |
FISQLProcessor.UseLogFile := true;
|
307 |
end;
|
308 |
|
309 |
if HasOption('p','pass') then
|
310 |
FIBDatabase.Params.Values['password'] := GetOptionValue('p','pass');
|
311 |
|
312 |
if HasOption('r','role') then
|
313 |
FIBDatabase.Params.Values['sql_role_name'] := GetOptionValue('r','role');
|
314 |
|
315 |
if HasOption('s') then
|
316 |
SQLStatement := GetOptionValue('s');
|
317 |
|
318 |
if HasOption('t') then
|
319 |
begin
|
320 |
OutputFormat := GetOptionValue('t');
|
321 |
if OutputFormat = 'CSV' then
|
322 |
FDataOutputFormatter := TIBCSVDataOut
|
323 |
else
|
324 |
if OutputFormat = 'INS' then
|
325 |
FDataOutputFormatter := TIBInsertStmtsOut
|
326 |
else
|
327 |
if OutputFormat = 'BLK' then
|
328 |
FDataOutputFormatter := TIBBlockFormatOut
|
329 |
else
|
330 |
raise Exception.CreateFmt('Unrecognised data output format "%s"',[OutputFormat]);
|
331 |
end;
|
332 |
|
333 |
if HasOption('u','user') then
|
334 |
FIBDatabase.Params.Values['user_name'] := GetOptionValue('u','user');
|
335 |
|
336 |
if HasOption('c','charset') then
|
337 |
FIBDatabase.Params.Values['lc_ctype'] := GetOptionValue('c','charset');
|
338 |
|
339 |
{Validation}
|
340 |
|
341 |
FIBDatabase.LoginPrompt := (FIBDatabase.Params.IndexOfName('user_name') <> -1) and
|
342 |
(FIBDatabase.Params.Values['password'] = '');
|
343 |
|
344 |
if not DoExtract then
|
345 |
begin
|
346 |
if (SQLStatement <> '') and (SQLFileName <> '') then
|
347 |
raise Exception.Create('An SQL Script File and text cannot be simultaneously requested');
|
348 |
|
349 |
if (SQLStatement = '') and (SQLFileName <> '') and not FileExists(SQLFileName) then
|
350 |
raise Exception.CreateFmt('SQL File "%s" not found!',[SQLFileName]);
|
351 |
|
352 |
end;
|
353 |
|
354 |
if DoExtract and ((SQLFileName <> '') or (SQLStatement <> '')) then
|
355 |
raise Exception.Create('Extract and script execution cannot be simulateously requested');
|
356 |
|
357 |
{This is where it all happens}
|
358 |
|
359 |
FIBXScript.DataOutputFormatter := FDataOutputFormatter.Create(self);
|
360 |
FISQLProcessor.DataOutputFormatter := FDataOutputFormatter.Create(self);
|
361 |
|
362 |
if OutputFileName <> '' then
|
363 |
FOutputFile := TFileStream.Create(OutputFileName,fmCreate);
|
364 |
|
365 |
FIBDatabase.Connected := FIBDatabase.DatabaseName <> '';
|
366 |
try
|
367 |
if DoExtract then
|
368 |
begin
|
369 |
FExtract.ExtractObject(eoDatabase,'',ExtractTypes);
|
370 |
if FOutputFile <> nil then
|
371 |
FExtract.Items.SaveToStream(FOutputFile)
|
372 |
else
|
373 |
for i := 0 to FExtract.Items.Count - 1 do
|
374 |
writeln(FExtract.Items[i]);
|
375 |
end
|
376 |
else
|
377 |
if SQLFileName <> '' then
|
378 |
FIBXScript.RunScript(SQLFileName)
|
379 |
else
|
380 |
if SQLStatement <> '' then
|
381 |
FIBXScript.ExecSQLScript(SQLStatement)
|
382 |
else
|
383 |
FISQLProcessor.Run;
|
384 |
finally
|
385 |
FIBDatabase.Connected := false;
|
386 |
if FOutputFile <> nil then
|
387 |
FOutputFile.Free;
|
388 |
end;
|
389 |
|
390 |
|
391 |
// stop program loop
|
392 |
Terminate;
|
393 |
end;
|
394 |
|
395 |
procedure TFBSQL.ShowException(E: Exception);
|
396 |
begin
|
397 |
FExceptionTrapped := true;
|
398 |
writeln(stderr,'Error: ' + E.Message);
|
399 |
end;
|
400 |
|
401 |
constructor TFBSQL.Create(TheOwner: TComponent);
|
402 |
begin
|
403 |
inherited Create(TheOwner);
|
404 |
StopOnException:=True;
|
405 |
|
406 |
{ Create Components }
|
407 |
FIBDatabase := TIBDatabase.Create(self);
|
408 |
FIBDatabase.OnLogin := @loginPrompt;
|
409 |
FIBDatabase.Params.Clear;
|
410 |
FIBDatabase.Params.Values['lc_ctype'] := 'UTF8';
|
411 |
FIBTransaction := TIBTransaction.Create(self);
|
412 |
FIBTransaction.DefaultDatabase := FIBDatabase;
|
413 |
FIBXScript := TIBXScript.Create(self);
|
414 |
FIBXScript.Database := FIBDatabase;
|
415 |
FIBXScript.Transaction := FIBTransaction;
|
416 |
FIBXScript.OnOutputLog := @LogHandler;
|
417 |
FIBXScript.OnErrorLog := @ErrorLogHandler;
|
418 |
FISQLProcessor := TInteractiveSQLProcessor.Create(self);
|
419 |
FISQLProcessor.Database := FIBDatabase;
|
420 |
FISQLProcessor.Transaction := FIBTransaction;
|
421 |
FISQLProcessor.OnOutputLog := @LogHandler;
|
422 |
FISQLProcessor.OnErrorLog := @ErrorLogHandler;
|
423 |
FExtract := TIBExtract.Create(self);
|
424 |
FExtract.Database := FIBDatabase;
|
425 |
FExtract.Transaction := FIBTransaction;
|
426 |
|
427 |
FIBTransaction.Params.Add('concurrency');
|
428 |
FIBTransaction.Params.Add('wait');
|
429 |
|
430 |
end;
|
431 |
|
432 |
procedure TFBSQL.WriteHelp;
|
433 |
begin
|
434 |
writeln(stderr,'Usage: ',ExtractFileName(ExeName),' <options> <database name>');
|
435 |
writeln(stderr,'Options:');
|
436 |
writeln(stderr,'-a write database metadata to stdout');
|
437 |
writeln(stderr,'-A write database metadata and table data to stdout');
|
438 |
writeln(stderr,'-b stop on first error');
|
439 |
writeln(stderr,'-e echo sql statements to stdout');
|
440 |
writeln(stderr,'-g include grants to normal users in database metadata');
|
441 |
writeln(stderr,'-i <filename> execute SQL script from file');
|
442 |
writeln(stderr,'-h show this information');
|
443 |
writeln(stderr,'-o <filename> output to this file instead of stdout');
|
444 |
writeln(stderr,'-p <password> provide password on command line (insecure)');
|
445 |
writeln(stderr,'-r <rolename> open database with this rolename');
|
446 |
writeln(stderr,'-s <sql> Execute SQL text');
|
447 |
writeln(stderr,'-t specify output format for SQL Statements');
|
448 |
writeln(stderr,' BLK (default) for block format');
|
449 |
writeln(stderr,' CSV for CSV format');
|
450 |
writeln(stderr,' INS for Insert Statement format');
|
451 |
writeln(stderr,'-u <username> open database with this username (defaults to SYSDBA)');
|
452 |
writeln(stderr,'-c <character set name> connection charset');
|
453 |
writeln;
|
454 |
writeln(stderr,'Environment Variables:');
|
455 |
writeln(stderr,'ISC_USER Login user Name');
|
456 |
writeln(stderr,'ISC_PASSWORD Login password');
|
457 |
end;
|
458 |
|
459 |
var
|
460 |
Application: TFBSQL;
|
461 |
begin
|
462 |
Application:=TFBSQL.Create(nil);
|
463 |
Application.Run;
|
464 |
Application.Free;
|
465 |
if FExceptionTrapped then
|
466 |
Halt(1);
|
467 |
end.
|
468 |
|