-
-
Notifications
You must be signed in to change notification settings - Fork 444
/
dbconnection.pas
7967 lines (7148 loc) · 267 KB
/
dbconnection.pas
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
unit dbconnection;
interface
uses
Classes, SysUtils, windows, dbstructures, SynRegExpr, Generics.Collections, Generics.Defaults,
DateUtils, Types, Math, Dialogs, ADODB, DB, DBCommon, ComObj, Graphics, ExtCtrls, StrUtils,
gnugettext, AnsiStrings, Controls, Forms, System.IOUtils;
type
{$M+} // Needed to add published properties
{ TDBObjectList and friends }
TListNodeType = (lntNone, lntDb, lntGroup, lntTable, lntView, lntFunction, lntProcedure, lntTrigger, lntEvent, lntColumn);
TListNodeTypes = Set of TListNodeType;
TDBConnection = class;
TConnectionParameters = class;
TDBQuery = class;
TDBQueryList = TObjectList<TDBQuery>;
TDBObject = class(TPersistent)
private
FCreateCode: String;
FCreateCodeFetched: Boolean;
FWasSelected: Boolean;
FConnection: TDBConnection;
function GetObjType: String;
function GetImageIndex: Integer;
function GetOverlayImageIndex: Integer;
function GetPath: String;
procedure SetCreateCode(Value: String);
public
// Table options:
Name, Schema, Database, Column, Engine, Comment, RowFormat, CreateOptions, Collation: String;
Created, Updated, LastChecked: TDateTime;
Rows, Size, Version, AvgRowLen, MaxDataLen, IndexLen, DataLen, DataFree, AutoInc, CheckSum: Int64;
// Routine options:
Body, Definer, Returns, DataAccess, Security, ArgTypes: String;
Deterministic: Boolean;
NodeType, GroupType: TListNodeType;
constructor Create(OwnerConnection: TDBConnection);
procedure Assign(Source: TPersistent); override;
procedure Drop;
function IsSameAs(CompareTo: TDBObject): Boolean;
function QuotedDatabase(AlwaysQuote: Boolean=True): String;
function QuotedName(AlwaysQuote: Boolean=True; SeparateSegments: Boolean=True): String;
function QuotedDbAndTableName(AlwaysQuote: Boolean=True): String;
function QuotedColumn(AlwaysQuote: Boolean=True): String;
function RowCount: Int64;
function GetCreateCode: String; overload;
function GetCreateCode(RemoveAutoInc, RemoveDefiner: Boolean): String; overload;
property ObjType: String read GetObjType;
property ImageIndex: Integer read GetImageIndex;
property OverlayImageIndex: Integer read GetOverlayImageIndex;
property Path: String read GetPath;
property CreateCode: String read GetCreateCode write SetCreateCode;
property WasSelected: Boolean read FWasSelected write FWasSelected;
property Connection: TDBConnection read FConnection;
end;
PDBObject = ^TDBObject;
TDBObjectList = class(TObjectList<TDBObject>)
private
FDatabase: String;
FDataSize: Int64;
FLargestObjectSize: Int64;
FLastUpdate: TDateTime;
FCollation: String;
FOnlyNodeType: TListNodeType;
public
property Database: String read FDatabase;
property DataSize: Int64 read FDataSize;
property LargestObjectSize: Int64 read FLargestObjectSize;
property LastUpdate: TDateTime read FLastUpdate;
property Collation: String read FCollation;
property OnlyNodeType: TListNodeType read FOnlyNodeType;
end;
TDatabaseCache = class(TObjectList<TDBObjectList>); // A list of db object lists, used for caching
TDBObjectComparer = class(TComparer<TDBObject>)
function Compare(const Left, Right: TDBObject): Integer; override;
end;
TDBObjectDropComparer = class(TComparer<TDBObject>)
function Compare(const Left, Right: TDBObject): Integer; override;
end;
// General purpose editing status flag
TEditingStatus = (esUntouched, esModified, esDeleted, esAddedUntouched, esAddedModified, esAddedDeleted);
TOidStringPairs = TDictionary<POid, String>;
TColumnPart = (cpAll, cpName, cpType, cpAllowNull, cpDefault, cpVirtuality, cpComment, cpCollation);
TColumnParts = Set of TColumnPart;
TColumnDefaultType = (cdtNothing, cdtText, cdtNull, cdtAutoInc, cdtExpression);
// Column object, many of them in a TObjectList
TTableColumn = class(TObject)
private
FConnection: TDBConnection;
procedure SetStatus(Value: TEditingStatus);
public
Name, OldName: String;
DataType, OldDataType: TDBDatatype;
LengthSet: String;
Unsigned, AllowNull, ZeroFill, LengthCustomized: Boolean;
DefaultType: TColumnDefaultType;
DefaultText: String;
OnUpdateType: TColumnDefaultType;
OnUpdateText: String;
Comment, Charset, Collation, Expression, Virtuality: String;
FStatus: TEditingStatus;
constructor Create(AOwner: TDBConnection);
destructor Destroy; override;
function SQLCode(OverrideCollation: String=''; Parts: TColumnParts=[cpAll]): String;
function ValueList: TStringList;
function CastAsText: String;
property Status: TEditingStatus read FStatus write SetStatus;
property Connection: TDBConnection read FConnection;
end;
PTableColumn = ^TTableColumn;
TTableColumnList = TObjectList<TTableColumn>;
TTableKey = class(TObject)
private
FConnection: TDBConnection;
function GetImageIndex: Integer;
public
Name, OldName: String;
IndexType, OldIndexType, Algorithm, Comment: String;
Columns, SubParts: TStringList;
Modified, Added: Boolean;
constructor Create(AOwner: TDBConnection);
destructor Destroy; override;
procedure Modification(Sender: TObject);
function SQLCode: String;
property ImageIndex: Integer read GetImageIndex;
end;
TTableKeyList = TObjectList<TTableKey>;
// Helper object to manage foreign keys in a TObjectList
TForeignKey = class(TObject)
private
FConnection: TDBConnection;
public
KeyName, OldKeyName, ReferenceTable, OnUpdate, OnDelete: String;
Columns, ForeignColumns: TStringList;
Modified, Added, KeyNameWasCustomized: Boolean;
constructor Create(AOwner: TDBConnection);
destructor Destroy; override;
function SQLCode(IncludeSymbolName: Boolean): String;
end;
TForeignKeyList = TObjectList<TForeignKey>;
TRoutineParam = class(TObject)
public
Name, Context, Datatype: String;
end;
TRoutineParamList = TObjectList<TRoutineParam>;
// Structures for in-memory changes of a TMySQLQuery
TCellData = class(TObject)
public
NewText, OldText: String;
NewIsNull, OldIsNull: Boolean;
NewIsFunction, OldIsFunction: Boolean;
Modified: Boolean;
destructor Destroy; override;
end;
TRowData = class(TObjectList<TCellData>)
public
RecNo: Int64;
Inserted: Boolean;
end;
TUpdateData = TObjectList<TRowData>;
// PLink.exe related
TProcessPipe = class(TObject)
public
ReadHandle: THandle;
WriteHandle: THandle;
constructor Create;
destructor Destroy; override;
end;
TPlink = class(TObject)
private
FProcessInfo: TProcessInformation;
FInPipe: TProcessPipe;
FOutPipe: TProcessPipe;
FErrorPipe: TProcessPipe;
FConnection: TDBConnection;
function ReadPipe(const Pipe: TProcessPipe): String;
function AsciiToAnsi(Text: AnsiString): AnsiString;
function CleanEscSeq(const Buffer: String): String;
procedure SendText(Text: String);
public
procedure Connect;
constructor Create(Connection: TDBConnection);
destructor Destroy; override;
end;
{ TConnectionParameters and friends }
TNetType = (ntMySQL_TCPIP, ntMySQL_NamedPipe, ntMySQL_SSHtunnel,
ntMSSQL_NamedPipe, ntMSSQL_TCPIP, ntMSSQL_SPX, ntMSSQL_VINES, ntMSSQL_RPC,
ntPgSQL_TCPIP, ntPgSQL_SSHtunnel);
TNetTypeGroup = (ngMySQL, ngMSSQL, ngPgSQL);
TNetGroupLibs = TDictionary<TNetTypeGroup, TStringList>;
TConnectionParameters = class(TObject)
strict private
FNetType: TNetType;
FHostname, FUsername, FPassword, FAllDatabases, FLibraryOrProvider, FComment, FStartupScriptFilename,
FSessionPath, FSSLPrivateKey, FSSLCertificate, FSSLCACertificate, FSSLCipher, FServerVersion,
FSSHHost, FSSHUser, FSSHPassword, FSSHPlinkExe, FSSHPrivateKey: String;
FPort, FSSHPort, FSSHLocalPort, FSSHTimeout, FCounter, FQueryTimeout, FKeepAlive: Integer;
FLoginPrompt, FCompressed, FLocalTimeZone, FFullTableStatus,
FWindowsAuth, FWantSSL, FIsFolder, FCleartextPluginEnabled: Boolean;
FSessionColor: TColor;
FLastConnect: TDateTime;
class var FLibraries: TNetGroupLibs;
function GetImageIndex: Integer;
function GetSessionName: String;
public
constructor Create; overload;
constructor Create(SessionRegPath: String); overload;
procedure SaveToRegistry;
function CreateConnection(AOwner: TComponent): TDBConnection;
function CreateQuery(Connection: TDbConnection): TDBQuery;
function NetTypeName(LongFormat: Boolean): String;
function IsCompatibleToWin10S: Boolean;
function GetNetTypeGroup: TNetTypeGroup;
function IsMySQL: Boolean;
function IsMSSQL: Boolean;
function IsPostgreSQL: Boolean;
function IsMariaDB: Boolean;
function IsPercona: Boolean;
function IsTokudb: Boolean;
function IsInfiniDB: Boolean;
function IsInfobright: Boolean;
function IsAzure: Boolean;
function IsMemSQL: Boolean;
function IsRedshift: Boolean;
property ImageIndex: Integer read GetImageIndex;
function GetLibraries: TStringList;
function DefaultLibrary: String;
function DefaultPort: Integer;
function DefaultUsername: String;
published
property IsFolder: Boolean read FIsFolder write FIsFolder;
property NetType: TNetType read FNetType write FNetType;
property NetTypeGroup: TNetTypeGroup read GetNetTypeGroup;
property ServerVersion: String read FServerVersion write FServerVersion;
property Counter: Integer read FCounter;
property LastConnect: TDateTime read FLastConnect;
property SessionPath: String read FSessionPath write FSessionPath;
property SessionName: String read GetSessionName;
property SessionColor: TColor read FSessionColor write FSessionColor;
property Hostname: String read FHostname write FHostname;
property Port: Integer read FPort write FPort;
property Username: String read FUsername write FUsername;
property Password: String read FPassword write FPassword;
property LoginPrompt: Boolean read FLoginPrompt write FLoginPrompt;
property WindowsAuth: Boolean read FWindowsAuth write FWindowsAuth;
property CleartextPluginEnabled: Boolean read FCleartextPluginEnabled write FCleartextPluginEnabled;
property AllDatabasesStr: String read FAllDatabases write FAllDatabases;
property LibraryOrProvider: String read FLibraryOrProvider write FLibraryOrProvider;
property Comment: String read FComment write FComment;
property StartupScriptFilename: String read FStartupScriptFilename write FStartupScriptFilename;
property QueryTimeout: Integer read FQueryTimeout write FQueryTimeout;
property KeepAlive: Integer read FKeepAlive write FKeepAlive;
property Compressed: Boolean read FCompressed write FCompressed;
property LocalTimeZone: Boolean read FLocalTimeZone write FLocalTimeZone;
property FullTableStatus: Boolean read FFullTableStatus write FFullTableStatus;
property SSHHost: String read FSSHHost write FSSHHost;
property SSHPort: Integer read FSSHPort write FSSHPort;
property SSHUser: String read FSSHUser write FSSHUser;
property SSHPassword: String read FSSHPassword write FSSHPassword;
property SSHTimeout: Integer read FSSHTimeout write FSSHTimeout;
property SSHPrivateKey: String read FSSHPrivateKey write FSSHPrivateKey;
property SSHLocalPort: Integer read FSSHLocalPort write FSSHLocalPort;
property SSHPlinkExe: String read FSSHPlinkExe write FSSHPlinkExe;
property WantSSL: Boolean read FWantSSL write FWantSSL;
property SSLPrivateKey: String read FSSLPrivateKey write FSSLPrivateKey;
property SSLCertificate: String read FSSLCertificate write FSSLCertificate;
property SSLCACertificate: String read FSSLCACertificate write FSSLCACertificate;
property SSLCipher: String read FSSLCipher write FSSLCipher;
end;
PConnectionParameters = ^TConnectionParameters;
{ TDBConnection }
TDBLogCategory = (lcInfo, lcSQL, lcUserFiredSQL, lcError, lcDebug, lcScript);
TDBLogEvent = procedure(Msg: String; Category: TDBLogCategory=lcInfo; Connection: TDBConnection=nil) of object;
TDBEvent = procedure(Connection: TDBConnection; Database: String) of object;
TDBDataTypeArray = Array of TDBDataType;
TSQLSpecifityId = (spDatabaseTable, spDatabaseTableId,
spDbObjectsTable, spDbObjectsCreateCol, spDbObjectsUpdateCol, spDbObjectsTypeCol,
spEmptyTable, spRenameTable, spRenameView, spCurrentUserHost,
spAddColumn, spChangeColumn,
spSessionVariables, spGlobalVariables,
spISTableSchemaCol,
spUSEQuery, spKillQuery, spKillProcess,
spFuncLength, spFuncCeil,
spLockedTables);
TDBConnection = class(TComponent)
private
FActive: Boolean;
FConnectionStarted: Cardinal;
FServerUptime: Integer;
FServerDateTimeOnStartup: String;
FParameters: TConnectionParameters;
FPlink: TPlink;
FLoginPromptDone: Boolean;
FDatabase: String;
FAllDatabases: TStringList;
FLogPrefix: String;
FOnLog: TDBLogEvent;
FOnConnected: TDBEvent;
FOnDatabaseChanged: TDBEvent;
FOnObjectnamesChanged: TDBEvent;
FRowsFound: Int64;
FRowsAffected: Int64;
FWarningCount: Cardinal;
FServerOS: String;
FServerVersionUntouched: String;
FRealHostname: String;
FLastQueryDuration, FLastQueryNetworkDuration: Cardinal;
FLastQuerySQL: String;
FIsUnicode: Boolean;
FIsSSL: Boolean;
FTableEngines: TStringList;
FTableEngineDefault: String;
FCollationTable: TDBQuery;
FCharsetTable: TDBQuery;
FSessionVariables: TDBQuery;
FInformationSchemaObjects: TStringList;
FDatabaseCache: TDatabaseCache;
FResultCount: Integer;
FStatementNum: Cardinal;
FCurrentUserHostCombination: String;
FAllUserHostCombinations: TStringList;
FLockedByThread: TThread;
FQuoteChar: Char;
FQuoteChars: String;
FDatatypes: TDBDataTypeArray;
FThreadID: Int64;
FSQLSpecifities: Array[TSQLSpecifityId] of String;
FKeepAliveTimer: TTimer;
FFavorites: TStringList;
FPrefetchResults: TDBQueryList;
FRegClasses: TOidStringPairs;
procedure SetActive(Value: Boolean); virtual; abstract;
procedure DoBeforeConnect; virtual;
procedure DoAfterConnect; virtual;
procedure DetectUSEQuery(SQL: String); virtual;
procedure SetDatabase(Value: String);
function GetThreadId: Int64; virtual; abstract;
function GetCharacterSet: String; virtual;
procedure SetCharacterSet(CharsetName: String); virtual; abstract;
function GetLastErrorCode: Cardinal; virtual; abstract;
function GetLastErrorMsg: String; virtual; abstract;
function GetAllDatabases: TStringList; virtual;
function GetTableEngines: TStringList; virtual;
function GetCollationTable: TDBQuery; virtual;
function GetCollationList: TStringList;
function GetCharsetTable: TDBQuery; virtual;
function GetCharsetList: TStringList;
function GetInformationSchemaObjects: TStringList; virtual;
function GetConnectionUptime: Integer;
function GetServerUptime: Integer;
function GetServerNow: TDateTime;
function GetCurrentUserHostCombination: String;
function GetAllUserHostCombinations: TStringList;
function DecodeAPIString(a: AnsiString): String;
function ExtractIdentifier(var SQL: String): String;
function GetRowCount(Obj: TDBObject): Int64; virtual; abstract;
procedure ClearCache(IncludeDBObjects: Boolean);
procedure FetchDbObjects(db: String; var Cache: TDBObjectList); virtual; abstract;
procedure SetLockedByThread(Value: TThread); virtual;
procedure KeepAliveTimerEvent(Sender: TObject);
procedure Drop(Obj: TDBObject); virtual;
procedure PrefetchResults(SQL: String);
procedure FreeResults(Results: TDBQuery);
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure Query(SQL: String; DoStoreResult: Boolean=False; LogCategory: TDBLogCategory=lcSQL); virtual; abstract;
procedure Log(Category: TDBLogCategory; Msg: String);
function EscapeString(Text: String; ProcessJokerChars: Boolean=False; DoQuote: Boolean=True): String;
function QuoteIdent(Identifier: String; AlwaysQuote: Boolean=True; Glue: Char=#0): String;
function DeQuoteIdent(Identifier: String; Glue: Char=#0): String;
function QuotedDbAndTableName(DB, Obj: String): String;
function FindObject(DB, Obj: String): TDBObject;
function escChars(const Text: String; EscChar, Char1, Char2, Char3, Char4: Char): String;
function UnescapeString(Text: String): String;
function ExtractLiteral(var SQL: String; Prefix: String): String;
function GetResults(SQL: String): TDBQuery;
function GetCol(SQL: String; Column: Integer=0): TStringList;
function GetVar(SQL: String; Column: Integer=0): String; overload;
function GetVar(SQL: String; Column: String): String; overload;
function Ping(Reconnect: Boolean): Boolean; virtual; abstract;
function RefreshAllDatabases: TStringList;
function GetDBObjects(db: String; Refresh: Boolean=False; OnlyNodeType: TListNodeType=lntNone): TDBObjectList;
function DbObjectsCached(db: String): Boolean;
function ParseDateTime(Str: String): TDateTime;
function GetKeyColumns(Columns: TTableColumnList; Keys: TTableKeyList): TStringList;
function ConnectionInfo: TStringList; virtual;
function GetLastResults: TDBQueryList; virtual; abstract;
function GetCreateCode(Obj: TDBObject): String; virtual;
procedure PrefetchCreateCode(Objects: TDBObjectList);
function GetSessionVariables(Refresh: Boolean): TDBQuery;
function GetSessionVariable(VarName: String; DefaultValue: String=''; Refresh: Boolean=False): String;
function MaxAllowedPacket: Int64; virtual; abstract;
function GetSQLSpecifity(Specifity: TSQLSpecifityId): String;
function ExplainAnalyzer(SQL, DatabaseName: String): Boolean; virtual;
function GetDateTimeValue(Input: String; Datatype: TDBDatatypeIndex): String;
procedure ClearDbObjects(db: String);
procedure ClearAllDbObjects;
procedure ParseTableStructure(CreateTable: String; Columns: TTableColumnList; Keys: TTableKeyList; ForeignKeys: TForeignKeyList);
procedure ParseViewStructure(CreateCode: String; DBObj: TDBObject; Columns: TTableColumnList;
var Algorithm, Definer, SQLSecurity, CheckOption, SelectCode: String);
procedure ParseRoutineStructure(Obj: TDBObject; Parameters: TRoutineParamList);
procedure PurgePrefetchResults;
function GetDatatypeByName(var DataType: String; DeleteFromSource: Boolean; Identifier: String=''): TDBDatatype;
function GetDatatypeByNativeType(NativeType: Integer; Identifier: String=''): TDBDatatype;
function ApplyLimitClause(QueryType, QueryBody: String; Limit, Offset: Int64): String;
function LikeClauseTail: String;
property Parameters: TConnectionParameters read FParameters write FParameters;
property ThreadId: Int64 read GetThreadId;
property ConnectionUptime: Integer read GetConnectionUptime;
property ServerUptime: Integer read GetServerUptime;
property ServerNow: TDateTime read GetServerNow;
property CharacterSet: String read GetCharacterSet write SetCharacterSet;
property LastErrorCode: Cardinal read GetLastErrorCode;
property LastErrorMsg: String read GetLastErrorMsg;
property ServerOS: String read FServerOS;
property ServerVersionUntouched: String read FServerVersionUntouched;
property QuoteChars: String read FQuoteChars;
function ServerVersionStr: String;
function ServerVersionInt: Integer;
function NdbClusterVersionInt: Integer;
property RowsFound: Int64 read FRowsFound;
property RowsAffected: Int64 read FRowsAffected;
property WarningCount: Cardinal read FWarningCount;
property LastQueryDuration: Cardinal read FLastQueryDuration;
property LastQueryNetworkDuration: Cardinal read FLastQueryNetworkDuration;
property IsUnicode: Boolean read FIsUnicode;
property IsSSL: Boolean read FIsSSL;
property AllDatabases: TStringList read GetAllDatabases;
property TableEngines: TStringList read GetTableEngines;
property TableEngineDefault: String read FTableEngineDefault;
property CollationTable: TDBQuery read GetCollationTable;
property CollationList: TStringList read GetCollationList;
property CharsetTable: TDBQuery read GetCharsetTable;
property CharsetList: TStringList read GetCharsetList;
property InformationSchemaObjects: TStringList read GetInformationSchemaObjects;
property ResultCount: Integer read FResultCount;
property CurrentUserHostCombination: String read GetCurrentUserHostCombination;
property AllUserHostCombinations: TStringList read GetAllUserHostCombinations;
property LockedByThread: TThread read FLockedByThread write SetLockedByThread;
property Datatypes: TDBDataTypeArray read FDatatypes;
property Favorites: TStringList read FFavorites;
property RegClasses: TOidStringPairs read FRegClasses;
function GetLockedTableCount(db: String): Integer;
function IdentifierEquals(Ident1, Ident2: String): Boolean;
published
property Active: Boolean read FActive write SetActive default False;
property Database: String read FDatabase write SetDatabase;
property LogPrefix: String read FLogPrefix write FLogPrefix;
property OnLog: TDBLogEvent read FOnLog write FOnLog;
property OnConnected: TDBEvent read FOnConnected write FOnConnected;
property OnDatabaseChanged: TDBEvent read FOnDatabaseChanged write FOnDatabaseChanged;
property OnObjectnamesChanged: TDBEvent read FOnObjectnamesChanged write FOnObjectnamesChanged;
end;
TDBConnectionList = TObjectList<TDBConnection>;
{ TMySQLConnection }
TMySQLRawResults = Array of PMYSQL_RES;
TMySQLConnection = class(TDBConnection)
private
FHandle: PMYSQL;
FLib: TMySQLLib;
FLastRawResults: TMySQLRawResults;
procedure SetActive(Value: Boolean); override;
procedure DoBeforeConnect; override;
procedure DoAfterConnect; override;
function GetThreadId: Int64; override;
function GetCharacterSet: String; override;
procedure SetCharacterSet(CharsetName: String); override;
function GetLastErrorCode: Cardinal; override;
function GetLastErrorMsg: String; override;
function GetAllDatabases: TStringList; override;
function GetTableEngines: TStringList; override;
function GetCollationTable: TDBQuery; override;
function GetCharsetTable: TDBQuery; override;
function GetCreateViewCode(Database, Name: String): String;
function GetRowCount(Obj: TDBObject): Int64; override;
procedure FetchDbObjects(db: String; var Cache: TDBObjectList); override;
procedure SetLockedByThread(Value: TThread); override;
public
constructor Create(AOwner: TComponent); override;
property Lib: TMySQLLib read FLib;
procedure Query(SQL: String; DoStoreResult: Boolean=False; LogCategory: TDBLogCategory=lcSQL); override;
function Ping(Reconnect: Boolean): Boolean; override;
function ConnectionInfo: TStringList; override;
function GetLastResults: TDBQueryList; override;
function GetCreateCode(Obj: TDBObject): String; override;
property LastRawResults: TMySQLRawResults read FLastRawResults;
function MaxAllowedPacket: Int64; override;
function ExplainAnalyzer(SQL, DatabaseName: String): Boolean; override;
end;
TAdoRawResults = Array of _RecordSet;
TAdoDBConnection = class(TDBConnection)
private
FAdoHandle: TAdoConnection;
FLastRawResults: TAdoRawResults;
FLastError: String;
procedure SetActive(Value: Boolean); override;
procedure DoAfterConnect; override;
function GetThreadId: Int64; override;
procedure SetCharacterSet(CharsetName: String); override;
function GetLastErrorCode: Cardinal; override;
function GetLastErrorMsg: String; override;
function GetAllDatabases: TStringList; override;
function GetCollationTable: TDBQuery; override;
function GetCharsetTable: TDBQuery; override;
function GetInformationSchemaObjects: TStringList; override;
function GetRowCount(Obj: TDBObject): Int64; override;
procedure FetchDbObjects(db: String; var Cache: TDBObjectList); override;
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure Query(SQL: String; DoStoreResult: Boolean=False; LogCategory: TDBLogCategory=lcSQL); override;
function Ping(Reconnect: Boolean): Boolean; override;
function ConnectionInfo: TStringList; override;
function GetLastResults: TDBQueryList; override;
function MaxAllowedPacket: Int64; override;
property LastRawResults: TAdoRawResults read FLastRawResults;
end;
TPGRawResults = Array of PPGresult;
TPQerrorfields = (PG_DIAG_SEVERITY, PG_DIAG_SQLSTATE, PG_DIAG_MESSAGE_PRIMARY, PG_DIAG_MESSAGE_DETAIL, PG_DIAG_MESSAGE_HINT, PG_DIAG_STATEMENT_POSITION, PG_DIAG_INTERNAL_POSITION, PG_DIAG_INTERNAL_QUERY, PG_DIAG_CONTEXT, PG_DIAG_SOURCE_FILE, PG_DIAG_SOURCE_LINE, PG_DIAG_SOURCE_FUNCTION);
TPgConnection = class(TDBConnection)
private
FHandle: PPGconn;
FLib: TPostgreSQLLib;
FLastRawResults: TPGRawResults;
procedure SetActive(Value: Boolean); override;
procedure DoBeforeConnect; override;
function GetThreadId: Int64; override;
procedure SetCharacterSet(CharsetName: String); override;
function GetLastErrorCode: Cardinal; override;
function GetLastErrorMsg: String; override;
function GetAllDatabases: TStringList; override;
function GetCharsetTable: TDBQuery; override;
procedure FetchDbObjects(db: String; var Cache: TDBObjectList); override;
procedure Drop(Obj: TDBObject); override;
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
property Lib: TPostgreSQLLib read FLib;
procedure Query(SQL: String; DoStoreResult: Boolean=False; LogCategory: TDBLogCategory=lcSQL); override;
function Ping(Reconnect: Boolean): Boolean; override;
function ConnectionInfo: TStringList; override;
function GetLastResults: TDBQueryList; override;
function MaxAllowedPacket: Int64; override;
function GetRowCount(Obj: TDBObject): Int64; override;
property LastRawResults: TPGRawResults read FLastRawResults;
end;
{ TDBQuery }
TDBQuery = class(TComponent)
private
FSQL: String;
FConnection: TDBConnection;
FRecNo,
FRecordCount: Int64;
FColumnNames: TStringList;
FColumnOrgNames: TStringList;
FAutoIncrementColumn: Integer;
FColumnTypes: Array of TDBDatatype;
FColumnLengths: TIntegerDynArray;
FColumnFlags: TCardinalDynArray;
FCurrentUpdateRow: TRowData;
FEof: Boolean;
FStoreResult: Boolean;
FColumns: TTableColumnList;
FKeys: TTableKeyList;
FForeignKeys: TForeignKeyList;
FEditingPrepared: Boolean;
FUpdateData: TUpdateData;
FDBObject: TDBObject;
FFormatSettings: TFormatSettings;
procedure SetRecNo(Value: Int64); virtual; abstract;
function ColumnExists(Column: Integer): Boolean;
procedure SetColumnOrgNames(Value: TStringList);
procedure SetDBObject(Value: TDBObject);
procedure CreateUpdateRow;
function GetKeyColumns: TStringList;
function GridQuery(QueryType, QueryBody: String): String;
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure Execute(AddResult: Boolean=False; UseRawResult: Integer=-1); virtual; abstract;
procedure First;
procedure Next;
function ColumnCount: Integer;
function GetColBinData(Column: Integer; var baData: TBytes): Boolean; virtual; abstract;
function Col(Column: Integer; IgnoreErrors: Boolean=False): String; overload; virtual; abstract;
function Col(ColumnName: String; IgnoreErrors: Boolean=False): String; overload;
function ColumnLengths(Column: Integer): Int64; virtual;
function HexValue(Column: Integer; IgnoreErrors: Boolean=False): String; overload;
function HexValue(BinValue: String): String; overload;
function HexValue(var ByteData: TBytes): String; overload;
function DataType(Column: Integer): TDBDataType;
function MaxLength(Column: Integer): Int64;
function ValueList(Column: Integer): TStringList;
function ColExists(Column: String): Boolean;
function ColIsPrimaryKeyPart(Column: Integer): Boolean; virtual; abstract;
function ColIsUniqueKeyPart(Column: Integer): Boolean; virtual; abstract;
function ColIsKeyPart(Column: Integer): Boolean; virtual; abstract;
function ColIsVirtual(Column: Integer): Boolean;
function ColAttributes(Column: Integer): TTableColumn;
function IsNull(Column: Integer): Boolean; overload; virtual; abstract;
function IsNull(Column: String): Boolean; overload;
function IsFunction(Column: Integer): Boolean;
function HasResult: Boolean; virtual; abstract;
function GetWhereClause: String;
procedure CheckEditable;
procedure DeleteRow;
function InsertRow: Int64;
procedure SetCol(Column: Integer; NewText: String; Null: Boolean; IsFunction: Boolean);
function EnsureFullRow(Refresh: Boolean): Boolean;
function HasFullData: Boolean;
function Modified(Column: Integer): Boolean; overload;
function Modified: Boolean; overload;
function Inserted: Boolean;
function SaveModifications: Boolean;
function DatabaseName: String; virtual; abstract;
function TableName: String; virtual; abstract;
function QuotedDbAndTableName: String;
procedure DiscardModifications;
procedure PrepareColumnAttributes;
procedure PrepareEditing;
property RecNo: Int64 read FRecNo write SetRecNo;
property Eof: Boolean read FEof;
property RecordCount: Int64 read FRecordCount;
property ColumnNames: TStringList read FColumnNames;
property StoreResult: Boolean read FStoreResult write FStoreResult;
property ColumnOrgNames: TStringList read FColumnOrgNames write SetColumnOrgNames;
property AutoIncrementColumn: Integer read FAutoIncrementColumn;
property DBObject: TDBObject read FDBObject write SetDBObject;
property SQL: String read FSQL write FSQL;
property Connection: TDBConnection read FConnection;
end;
PDBQuery = ^TDBQuery;
{ TMySQLQuery }
TMySQLQuery = class(TDBQuery)
private
FConnection: TMySQLConnection;
FResultList: TMySQLRawResults;
FCurrentResults: PMYSQL_RES;
FCurrentRow: PMYSQL_ROW;
procedure SetRecNo(Value: Int64); override;
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure Execute(AddResult: Boolean=False; UseRawResult: Integer=-1); override;
function GetColBinData(Column: Integer; var baData: TBytes): Boolean; override;
function Col(Column: Integer; IgnoreErrors: Boolean=False): String; overload; override;
function ColIsPrimaryKeyPart(Column: Integer): Boolean; override;
function ColIsUniqueKeyPart(Column: Integer): Boolean; override;
function ColIsKeyPart(Column: Integer): Boolean; override;
function IsNull(Column: Integer): Boolean; overload; override;
function HasResult: Boolean; override;
function DatabaseName: String; override;
function TableName: String; override;
end;
TAdoDBQuery = class(TDBQuery)
private
FCurrentResults: TAdoQuery;
FResultList: Array of TAdoQuery;
procedure SetRecNo(Value: Int64); override;
public
destructor Destroy; override;
procedure Execute(AddResult: Boolean=False; UseRawResult: Integer=-1); override;
function GetColBinData(Column: Integer; var baData: TBytes): Boolean; override;
function Col(Column: Integer; IgnoreErrors: Boolean=False): String; overload; override;
function ColIsPrimaryKeyPart(Column: Integer): Boolean; override;
function ColIsUniqueKeyPart(Column: Integer): Boolean; override;
function ColIsKeyPart(Column: Integer): Boolean; override;
function IsNull(Column: Integer): Boolean; overload; override;
function HasResult: Boolean; override;
function DatabaseName: String; override;
function TableName: String; override;
end;
TPGQuery = class(TDBQuery)
private
FConnection: TPgConnection;
FCurrentResults: PPGresult;
FRecNoLocal: Integer;
FResultList: TPGRawResults;
procedure SetRecNo(Value: Int64); override;
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure Execute(AddResult: Boolean=False; UseRawResult: Integer=-1); override;
function GetColBinData(Column: Integer; var baData: TBytes): Boolean; override;
function Col(Column: Integer; IgnoreErrors: Boolean=False): String; overload; override;
function ColIsPrimaryKeyPart(Column: Integer): Boolean; override;
function ColIsUniqueKeyPart(Column: Integer): Boolean; override;
function ColIsKeyPart(Column: Integer): Boolean; override;
function IsNull(Column: Integer): Boolean; overload; override;
function HasResult: Boolean; override;
function DatabaseName: String; override;
function TableName: String; override;
end;
function mysql_authentication_dialog_ask(
Handle: PMYSQL;
_type: Integer;
prompt: PAnsiChar;
buf: PAnsiChar;
buf_len: Integer
): PAnsiChar; cdecl;
exports
mysql_authentication_dialog_ask;
{$I const.inc}
implementation
uses apphelpers, loginform, change_password;
{ TProcessPipe }
constructor TProcessPipe.Create;
var
Success: Boolean;
begin
inherited;
Success := CreatePipe(ReadHandle, WriteHandle, nil, 8192);
if Success then
Success := DuplicateHandle(
GetCurrentProcess, ReadHandle,
GetCurrentProcess, @ReadHandle, 0, True,
DUPLICATE_CLOSE_SOURCE OR DUPLICATE_SAME_ACCESS
);
if Success then
Success := DuplicateHandle(
GetCurrentProcess, WriteHandle,
GetCurrentProcess, @WriteHandle, 0, True,
DUPLICATE_CLOSE_SOURCE OR DUPLICATE_SAME_ACCESS
);
if not Success then
raise EDbError.Create(_('Error creating I/O pipes'));
end;
destructor TProcessPipe.Destroy;
begin
CloseHandle(ReadHandle);
CloseHandle(WriteHandle);
inherited;
end;
{ TPlink }
constructor TPlink.Create(Connection: TDBConnection);
begin
inherited Create;
FConnection := Connection;
FInPipe := TProcessPipe.Create;
FOutPipe := TProcessPipe.Create;
FErrorPipe := TProcessPipe.Create;
end;
destructor TPlink.Destroy;
begin
FConnection.Log(lcInfo, f_('Closing plink.exe process #%d ...', [FProcessInfo.dwProcessId]));
TerminateProcess(FProcessInfo.hProcess, 0);
CloseHandle(FProcessInfo.hProcess);
CloseHandle(FProcessInfo.hThread);
FInPipe.Free;
FOutPipe.Free;
FErrorPipe.Free;
inherited;
end;
procedure TPlink.Connect;
var
PlinkCmd, PlinkCmdDisplay: String;
OutText, ErrorText, UserInput: String;
rx: TRegExpr;
StartupInfo: TStartupInfo;
ExitCode: LongWord;
Waited, ReturnedSomethingAt, PortChecks: Integer;
begin
// Check if local port is open
PortChecks := 0;
while not PortOpen(FConnection.Parameters.SSHLocalPort) do begin
Inc(PortChecks);
if PortChecks >= 20 then
raise EDbError.CreateFmt(_('Could not execute PLink: Port %d already in use.'), [FConnection.Parameters.SSHLocalPort]);
FConnection.Log(lcInfo, f_('Port #%d in use. Checking if #%d is available...', [FConnection.Parameters.SSHLocalPort, FConnection.Parameters.SSHLocalPort+1]));
FConnection.Parameters.SSHLocalPort := FConnection.Parameters.SSHLocalPort + 1;
end;
// Build plink.exe command line
// plink bob@domain.com -pw myPassw0rd1 -P 22 -i "keyfile.pem" -L 55555:localhost:3306
PlinkCmd := FConnection.Parameters.SSHPlinkExe + ' -ssh ';
if FConnection.Parameters.SSHUser.Trim <> '' then
PlinkCmd := PlinkCmd + FConnection.Parameters.SSHUser.Trim + '@';
if FConnection.Parameters.SSHHost.Trim <> '' then
PlinkCmd := PlinkCmd + FConnection.Parameters.SSHHost.Trim
else
PlinkCmd := PlinkCmd + FConnection.Parameters.Hostname;
if FConnection.Parameters.SSHPassword <> '' then begin
// Escape double quote with backslash, see issue #261
PlinkCmd := PlinkCmd + ' -pw "' + StringReplace(FConnection.Parameters.SSHPassword, '"', '\"', [rfReplaceAll]) + '"';
end;
if FConnection.Parameters.SSHPort > 0 then
PlinkCmd := PlinkCmd + ' -P ' + IntToStr(FConnection.Parameters.SSHPort);
if FConnection.Parameters.SSHPrivateKey <> '' then
PlinkCmd := PlinkCmd + ' -i "' + FConnection.Parameters.SSHPrivateKey + '"';
PlinkCmd := PlinkCmd + ' -N -L ' + IntToStr(FConnection.Parameters.SSHLocalPort) + ':' + FConnection.Parameters.Hostname + ':' + IntToStr(FConnection.Parameters.Port);
rx := TRegExpr.Create;
rx.Expression := '(-pw\s+")[^"]*(")';
PlinkCmdDisplay := rx.Replace(PlinkCmd, '${1}******${2}', True);
FConnection.Log(lcInfo, f_('Attempt to create plink.exe process, waiting %ds for response ...', [FConnection.Parameters.SSHTimeout]));
FConnection.Log(lcInfo, PlinkCmdDisplay);
// Prepare process
FillChar(StartupInfo, SizeOf(StartupInfo), 0);
StartupInfo.cb := SizeOf(StartupInfo);
StartupInfo.dwFlags := STARTF_USESHOWWINDOW or STARTF_USESTDHANDLES;
StartupInfo.wShowWindow := SW_HIDE;
StartupInfo.hStdInput:= FInPipe.ReadHandle;
StartupInfo.hStdError:= FErrorPipe.WriteHandle;
StartupInfo.hStdOutput:= FOutPipe.WriteHandle;
// Create plink.exe process
FillChar(FProcessInfo, SizeOf(FProcessInfo), 0);
if not CreateProcess(
nil,
PChar(PlinkCmd),
nil,
nil,
true,
CREATE_DEFAULT_ERROR_MODE or CREATE_NEW_CONSOLE or NORMAL_PRIORITY_CLASS,
nil,
PChar(GetCurrentDir),
StartupInfo,
FProcessInfo) then begin
ErrorText := CRLF + CRLF + PlinkCmdDisplay + CRLF + CRLF + 'System message: ' + SysErrorMessage(GetLastError);
ErrorText := f_('Could not execute PLink: %s', [ErrorText]);
raise EDbError.Create(ErrorText);
end;
// Wait until timeout has finished, or some text returned.
// Parse pipe output and probably show some message in a dialog.
Waited := 0;
ReturnedSomethingAt := -1;
while Waited < FConnection.Parameters.SSHTimeout*1000 do begin
Inc(Waited, 200);
WaitForSingleObject(FProcessInfo.hProcess, 200);
GetExitCodeProcess(FProcessInfo.hProcess, ExitCode);
if ExitCode <> STILL_ACTIVE then
raise EDbError.CreateFmt(_('PLink exited unexpected. Command line was: %s'), [CRLF+PlinkCmdDisplay]);
OutText := Trim(ReadPipe(FOutPipe));
ErrorText := ReadPipe(FErrorPipe);
if (OutText <> '') or (ErrorText <> '') then begin
ReturnedSomethingAt := Waited;
FConnection.Log(lcDebug, Format('PLink output after %d ms. OutPipe: "%s" ErrorPipe: "%s"', [Waited, OutText, ErrorText]));
end;
if OutText <> '' then begin
if ExecRegExpr('(login as|Passphrase for key "[^"]+")\s*\:', OutText) then begin
// Prompt for username
UserInput := InputBox('PLink:', OutText, '');
SendText(UserInput + CRLF);
end else if ExecRegExpr('password\s*\:', OutText) then begin
// Prompt for password. Send * as first char of prompt param so InputBox hides input characters
UserInput := InputBox('PLink:', #31+OutText, '');
SendText(UserInput + CRLF);
end else begin
// Informational message box
rx.Expression := '^[^\.]+\.';
if rx.Exec(OutText) then begin // First words end with a dot - use it as caption
MessageDialog('PLink: '+rx.Match[0], OutText, mtInformation, [mbOK])
end else begin
MessageDialog('PLink:', OutText, mtInformation, [mbOK]);
end;
end;
end;
if ErrorText <> '' then begin
rx.Expression := '([^\.]+\?)(\s*\(y\/n\s*(,[^\)]+)?\)\s*)$';
if rx.Exec(ErrorText) then begin
// Prompt user with question
case MessageDialog(Trim(rx.Match[1]), Copy(ErrorText, 1, Length(ErrorText)-rx.MatchLen[2]), mtConfirmation, [mbYes, mbNo, mbCancel]) of
mrYes:
SendText('y');
mrNo:
SendText('n');
mrCancel: begin
Destroy;
raise EDbError.Create(_('PLink cancelled'));
end;
end;
end else if ErrorText.StartsWith('Using username ', True)
or ErrorText.StartsWith('Pre-authentication banner ', True)
then begin
// See #577 - new plink version sends this informational text to error pipe
FConnection.Log(lcError, 'PLink: '+ErrorText);
SendText(CRLF);
end else begin
// Any other error message goes here.
if ErrorText.Contains('Access denied') then begin
// This is a final connection error - end loop in this case
Destroy;
raise EDbError.Create(ErrorText);
end else begin
// Just show error text and proceed looping
MessageDialog('PLink:', ErrorText, mtError, [mbOK]);
end;
end;
end;
// Exit loop after 2s idletime when there was output earlier
if (ReturnedSomethingAt > 0) and (Waited >= ReturnedSomethingAt+2000) then
Break;
Application.ProcessMessages;
end;
rx.Free;
end;
function TPlink.ReadPipe(const Pipe: TProcessPipe): String;
var
BufferReadCount, OutLen: Cardinal;
BytesRemaining: Cardinal;
Buffer: array [0..1023] of AnsiChar;
R: AnsiString;
begin
Result := '';
if Pipe.ReadHandle = INVALID_HANDLE_VALUE then
raise EDbError.Create(_('Error reading I/O pipes'));
// Check if there is data to read from stdout
PeekNamedPipe(Pipe.ReadHandle, nil, 0, nil, @BufferReadCount, nil);
if BufferReadCount <> 0 then begin
FillChar(Buffer, sizeof(Buffer), 'z');
// Read by 1024 bytes chunks
BytesRemaining := BufferReadCount;
OutLen := 0;
while BytesRemaining >= 1024 do begin
// Read stdout pipe
ReadFile(Pipe.ReadHandle, Buffer, 1024, BufferReadCount, nil);
Dec(BytesRemaining, BufferReadCount);
SetLength(R, OutLen + BufferReadCount);
Move(Buffer, R[OutLen + 1], BufferReadCount);
Inc(OutLen, BufferReadCount);
end;
if BytesRemaining > 0 then begin
ReadFile(Pipe.ReadHandle, Buffer, BytesRemaining, BufferReadCount, nil);
SetLength(R, OutLen + BufferReadCount);
Move(Buffer, R[OutLen + 1], BufferReadCount);
end;
R := AsciiToAnsi(R);
{$WARNINGS OFF}
Result := AnsiToUtf8(R);
{$WARNINGS ON}