Skip to content

Commit 31ba8bf

Browse files
ansgarbeckeransgarbecker
committed
refactor: migration from Array[TSQLSpecifityId] to TSqlProvider
Refs #667, #1880
1 parent 678c7b8 commit 31ba8bf

12 files changed

+771
-594
lines changed

‎source/apphelpers.pas‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1850,9 +1850,9 @@ function TSortItems.ComposeOrderClause(Connection: TDBConnection): String;
18501850
if Result <> '' then
18511851
Result := Result + ', ';
18521852
if SortItem.Order = sioAscending then
1853-
SortOrder := Connection.GetSQLSpecifity(spOrderAsc)
1853+
SortOrder := Connection.SqlProvider.GetSql(qOrderAsc)
18541854
else
1855-
SortOrder := Connection.GetSQLSpecifity(spOrderDesc);
1855+
SortOrder := Connection.SqlProvider.GetSql(qOrderDesc);
18561856
Result := Result + Connection.QuoteIdent(SortItem.Column) + ' ' + SortOrder;
18571857
end;
18581858
end;

‎source/connections.pas‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,7 +14,7 @@ interface
1414
laz.VirtualTrees, Menus, Graphics, extra_controls, lazaruscompat,
1515
{$IFDEF Windows} ActiveX {$ELSE} laz.FakeActiveX {$ENDIF},
1616
dbconnection, RegExpr, Types, FileUtil,
17-
Math, ActnList, ComboEx, EditBtn, Buttons, ColorBox, extfiledialog;
17+
Math, ActnList, ComboEx, EditBtn, Buttons, ColorBox, extfiledialog, dbstructures;
1818

1919
type
2020

‎source/dbconnection.pas‎

Lines changed: 172 additions & 542 deletions
Large diffs are not rendered by default.

‎source/dbstructures.interbase.pas‎

Lines changed: 68 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,13 @@
66
interface
77

88
uses
9-
dbstructures;
9+
dbstructures, StrUtils;
10+
11+
type
12+
TInterbaseProvider = class(TSqlProvider)
13+
public
14+
function GetSql(AId: TQueryId): string; override;
15+
end;
1016

1117
var
1218

@@ -173,4 +179,65 @@ interface
173179

174180
implementation
175181

182+
183+
{ TInterbaseProvider }
184+
185+
function TInterbaseProvider.GetSql(AId: TQueryId): string;
186+
begin
187+
case AId of
188+
qDatabaseDrop: Result := 'DROP DATABASE %s';
189+
qEmptyTable: Result := 'TRUNCATE ';
190+
qRenameTable: Result := 'RENAME TABLE %s TO %s';
191+
qRenameView: Result := 'RENAME TABLE %s TO %s';
192+
qCurrentUserHost: Result := IfThen(
193+
FNetType in [ntInterbase_TCPIP, ntInterbase_Local],
194+
'select user from rdb$database',
195+
'select current_user || ''@'' || mon$attachments.mon$remote_host from mon$attachments where mon$attachments.mon$attachment_id = current_connection'
196+
);
197+
qLikeCompare: Result := '%s LIKE %s';
198+
qAddColumn: Result := 'ADD COLUMN %s';
199+
qChangeColumn: Result := 'CHANGE COLUMN %s %s';
200+
qRenameColumn: Result := '';
201+
qSessionVariables: Result := 'SHOW VARIABLES';
202+
qGlobalVariables: Result := 'SHOW GLOBAL VARIABLES';
203+
qISSchemaCol: Result := '%s_SCHEMA';
204+
qUSEQuery: Result := '';
205+
qKillQuery: Result := 'KILL %d';
206+
qKillProcess: Result := 'KILL %d';
207+
qFuncLength: Result := 'LENGTH';
208+
qFuncCeil: Result := 'CEIL';
209+
qFuncLeft: Result := 'SUBSTR(%s, 1, %d)';
210+
qFuncNow: Result := ' cast(''now'' as timestamp) from rdb$database';
211+
qFuncLastAutoIncNumber: Result := 'LAST_INSERT_ID()';
212+
qLockedTables: Result := '';
213+
qDisableForeignKeyChecks: Result := '';
214+
qEnableForeignKeyChecks: Result := '';
215+
qForeignKeyDrop: Result := 'DROP FOREIGN KEY %s';
216+
qGetTableColumns: Result := 'SELECT r.RDB$FIELD_NAME AS field_name,'+
217+
' r.RDB$DESCRIPTION AS field_description,'+
218+
' r.RDB$DEFAULT_VALUE AS field_default_value,'+
219+
' r.RDB$NULL_FLAG AS null_flag,'+
220+
' f.RDB$FIELD_LENGTH AS field_length,'+
221+
' f.RDB$FIELD_PRECISION AS field_precision,'+
222+
' f.RDB$FIELD_SCALE AS field_scale,'+
223+
' f.RDB$FIELD_TYPE AS field_type,'+
224+
' f.RDB$FIELD_SUB_TYPE AS field_subtype,'+
225+
' coll.RDB$COLLATION_NAME AS field_collation,'+
226+
' cset.RDB$CHARACTER_SET_NAME AS field_charset'+
227+
' FROM RDB$RELATION_FIELDS r'+
228+
' LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME'+
229+
' LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID'+
230+
' LEFT JOIN RDB$COLLATIONS coll ON f.RDB$COLLATION_ID = coll.RDB$COLLATION_ID'+
231+
' AND F.RDB$CHARACTER_SET_ID = COLL.RDB$CHARACTER_SET_ID'+
232+
' WHERE r.RDB$RELATION_NAME=%s'+
233+
' ORDER BY r.RDB$FIELD_POSITION';
234+
qGetCollations: Result := 'SELECT RDB$COLLATION_NAME AS "Collation",'+
235+
' RDB$COLLATION_ID AS "Id",'+
236+
' RDB$CHARACTER_SET_ID'+
237+
' FROM RDB$COLLATIONS';
238+
qGetCharsets: Result := 'SELECT RDB$CHARACTER_SET_NAME AS "Charset", RDB$CHARACTER_SET_NAME AS "Description" FROM RDB$CHARACTER_SETS';
239+
end;
240+
end;
241+
242+
176243
end.

‎source/dbstructures.mssql.pas‎

Lines changed: 80 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,13 @@
55
interface
66

77
uses
8-
dbstructures;
8+
dbstructures, StrUtils;
9+
10+
type
11+
TMsSqlProvider = class(TSqlProvider)
12+
public
13+
function GetSql(AId: TQueryId): string; override;
14+
end;
915

1016
var
1117

@@ -409,4 +415,77 @@ interface
409415

410416
implementation
411417

418+
419+
function TMsSqlProvider.GetSql(AId: TQueryId): string;
420+
begin
421+
case AId of
422+
qDatabaseTable: Result := IfThen(
423+
ServerVersion<=899,
424+
'master..sysdatabases',
425+
'sys.databases'
426+
);
427+
qDatabaseTableId: Result := IfThen(
428+
ServerVersion<=899,
429+
'dbid',
430+
'database_id'
431+
);
432+
qDatabaseDrop: Result := 'DROP DATABASE %s';
433+
qDbObjectsTable: Result := IfThen(
434+
ServerVersion<=899,
435+
'..sysobjects',
436+
'.sys.objects'
437+
);
438+
qDbObjectsCreateCol: Result := IfThen(
439+
ServerVersion<=899,
440+
'crdate',
441+
'create_date'
442+
);
443+
qDbObjectsUpdateCol: Result := IfThen(
444+
ServerVersion<=899,
445+
'',
446+
'modify_date'
447+
);
448+
qDbObjectsTypeCol: Result := IfThen(
449+
ServerVersion<=899,
450+
'xtype',
451+
'type'
452+
);
453+
qEmptyTable: Result := 'DELETE FROM ';
454+
qRenameTable: Result := 'EXEC sp_rename %s, %s';
455+
qRenameView: Result := 'EXEC sp_rename %s, %s';
456+
qCurrentUserHost: Result := 'SELECT SYSTEM_USER';
457+
qLikeCompare: Result := '%s LIKE %s';
458+
qAddColumn: Result := 'ADD %s';
459+
qChangeColumn: Result := 'ALTER COLUMN %s %s';
460+
qSessionVariables: Result := 'SELECT comment, value FROM master.dbo.syscurconfigs ORDER BY comment';
461+
qGlobalVariables: Result := 'SELECT comment, value FROM master.dbo.syscurconfigs ORDER BY comment';
462+
qISSchemaCol: Result := '%s_CATALOG';
463+
qUSEQuery: Result := 'USE %s';
464+
qKillQuery: Result := 'KILL %d';
465+
qKillProcess: Result := 'KILL %d';
466+
qFuncLength: Result := 'LEN';
467+
qFuncCeil: Result := 'CEILING';
468+
qFuncLeft: Result := 'LEFT(%s, %d)';
469+
qFuncNow: Result := 'GETDATE()';
470+
qFuncLastAutoIncNumber: Result := 'LAST_INSERT_ID()';
471+
qLockedTables: Result := '';
472+
qDisableForeignKeyChecks: Result := '';
473+
qEnableForeignKeyChecks: Result := '';
474+
qForeignKeyDrop: Result := 'DROP FOREIGN KEY %s';
475+
qGetTableColumns: Result := '';
476+
qGetCollations: Result := 'SELECT '''' AS "Collation", '+
477+
''''' AS "Charset", 0 AS "Id", '+
478+
''''' AS "Default", '''' AS "Compiled", '+
479+
'1 AS "Sortlen"';
480+
qGetCharsets: Result := 'SELECT name AS Charset, description AS Description FROM master.sys.syscharsets';
481+
qGetRowCountApprox: Result := IfThen(
482+
FServerVersion >= 900,
483+
'SELECT SUM("rows") FROM "sys"."partitions" WHERE "index_id" IN (0, 1) AND "object_id" = object_id(:EscapedDbSchemaName)',
484+
''
485+
);
486+
else Result := inherited;
487+
end;
488+
end;
489+
490+
412491
end.

‎source/dbstructures.mysql.pas‎

Lines changed: 112 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
interface
66

77
uses
8-
Classes, SysUtils, Types, dbstructures;
8+
Classes, SysUtils, Types, dbstructures, StrUtils;
99

1010

1111
const
@@ -319,6 +319,13 @@ TMySQLLib = class(TDbLib)
319319
constructor Create(UsedDllFile, HintDefaultDll: String); override;
320320
function IsLibMariadb: Boolean;
321321
end;
322+
323+
TMySqlProvider = class(TSqlProvider)
324+
public
325+
function GetSql(AId: TQueryId): string; override;
326+
end;
327+
328+
322329
var
323330
MySQLKeywords: TStringList;
324331
MySQLErrorCodes: TStringList;
@@ -3221,6 +3228,110 @@ procedure TMySQLLib.AssignProcedures;
32213228
end;
32223229

32233230

3231+
{ TMySqlProvider }
3232+
3233+
function TMySqlProvider.GetSql(AId: TQueryId): string;
3234+
begin
3235+
case AId of
3236+
qDatabaseDrop: Result := 'DROP DATABASE %s';
3237+
qEmptyTable: Result := 'TRUNCATE ';
3238+
qRenameTable: Result := 'RENAME TABLE %s TO %s';
3239+
qRenameView: Result := 'RENAME TABLE %s TO %s';
3240+
qCurrentUserHost: Result := 'SELECT CURRENT_USER()';
3241+
qLikeCompare: Result := '%s LIKE %s';
3242+
qAddColumn: Result := 'ADD COLUMN %s';
3243+
qChangeColumn: Result := 'CHANGE COLUMN %s %s';
3244+
qGlobalStatus: Result := IfThen(
3245+
FNetType = ntMySQL_ProxySQLAdmin,
3246+
'SELECT * FROM stats_mysql_global',
3247+
'SHOW /*!50002 GLOBAL */ STATUS'
3248+
);
3249+
qCommandsCounters: Result := IfThen(
3250+
FNetType = ntMySQL_ProxySQLAdmin,
3251+
'SELECT * FROM stats_mysql_commands_counters',
3252+
'SHOW /*!50002 GLOBAL */ STATUS LIKE ''Com\_%'''
3253+
);
3254+
qSessionVariables: Result := 'SHOW VARIABLES';
3255+
qGlobalVariables: Result := 'SHOW GLOBAL VARIABLES';
3256+
qISSchemaCol: Result := '%s_SCHEMA';
3257+
qUSEQuery: Result := 'USE %s';
3258+
qKillQuery: Result := IfThen(
3259+
FNetType = ntMySQL_RDS,
3260+
'CALL mysql.rds_kill_query(%d)',
3261+
IfThen(
3262+
FServerVersion >= 50000,
3263+
'KILL QUERY %d',
3264+
'KILL %d'
3265+
)
3266+
);
3267+
qKillProcess: Result := IfThen(
3268+
FNetType = ntMySQL_RDS,
3269+
'CALL mysql.rds_kill(%d)',
3270+
'KILL %d'
3271+
);
3272+
qFuncLength: Result := 'LENGTH';
3273+
qFuncCeil: Result := 'CEIL';
3274+
qFuncLeft: Result := IfThen(
3275+
FNetType = ntMySQL_ProxySQLAdmin,
3276+
'SUBSTR(%s, 1, %d)',
3277+
'LEFT(%s, %d)'
3278+
);
3279+
qFuncNow: Result := IfThen(
3280+
FNetType = ntMySQL_ProxySQLAdmin,
3281+
'CURRENT_TIMESTAMP',
3282+
'NOW()'
3283+
);
3284+
qFuncLastAutoIncNumber: Result := 'LAST_INSERT_ID()';
3285+
qLockedTables: Result := IfThen(
3286+
(FNetType <> ntMySQL_ProxySQLAdmin) and (FServerVersion >= 50124),
3287+
'SHOW OPEN TABLES FROM %s WHERE in_use!=0',
3288+
''
3289+
);
3290+
qDisableForeignKeyChecks: Result := IfThen(
3291+
FServerVersion >= 40014,
3292+
'SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0',
3293+
''
3294+
);
3295+
qEnableForeignKeyChecks: Result := IfThen(
3296+
FServerVersion >= 40014,
3297+
'SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1)',
3298+
''
3299+
);
3300+
qForeignKeyDrop: Result := 'DROP FOREIGN KEY %s';
3301+
qGetTableColumns: Result := '';
3302+
qGetCollations: Result := IfThen(
3303+
FServerVersion >= 40100,
3304+
'SHOW COLLATION',
3305+
''
3306+
);
3307+
// Issue #1917: MariaDB 10.10.1+ versions have additional collations in IS.COLLATION_CHARACTER_SET_APPLICABILITY
3308+
qGetCollationsExtended: Result := IfThen(
3309+
FServerVersion >= 101001,
3310+
'SELECT'+
3311+
' FULL_COLLATION_NAME AS `Collation`'+
3312+
', CHARACTER_SET_NAME AS `Charset`'+
3313+
', ID AS `Id`'+
3314+
', IS_DEFAULT AS `Default`'+
3315+
', 0 AS `Sortlen`'+
3316+
' FROM information_schema.COLLATION_CHARACTER_SET_APPLICABILITY'+
3317+
' ORDER BY `Collation`',
3318+
''
3319+
);
3320+
qGetCharsets: Result := IfThen(
3321+
FServerVersion >= 40100,
3322+
'SHOW CHARSET',
3323+
''
3324+
);
3325+
qGetRowCountApprox: Result := IfThen(
3326+
FNetType <> ntMySQL_ProxySQLAdmin,
3327+
'SHOW TABLE STATUS LIKE :EscapedName',
3328+
''
3329+
);
3330+
else Result := inherited;
3331+
end;
3332+
end;
3333+
3334+
32243335
initialization
32253336

32263337
// Keywords copied from SynHighligherSQL

0 commit comments

Comments
 (0)