View Issue Details

IDProjectCategoryView StatusLast Update
0037144FPCDatabasepublic2020-05-26 10:50
ReporterJoeny Ang Assigned ToMichael Van Canneyt  
PrioritynormalSeverityminorReproducibilityalways
Status assignedResolutionopen 
Product Version3.3.1 
Summary0037144: [Patch] Improvements to TSQLConnection.GetStatementInfo()
DescriptionTSQLConnection.GetStatementInfo() returns information on the SQL statement passed to it. It is currently limited to basic SQL statements. This patch adds common DDL statements, but it is by no means complete. We may need to make it virtual so that SQLDB connections which inherits from TSQLConnection can add checks for statements that are unique to them.
TagsNo tags attached.
Fixed in Revision
FPCOldBugId
FPCTarget
Attached Files

Activities

Joeny Ang

2020-05-26 08:16

reporter  

fpc-sqldb-improve-TSQLConnection.GetStatementInfo.patch (11,740 bytes)   
--- packages/fcl-db/src/base/sqltypes.pp
+++ packages/fcl-db/src/base/sqltypes.pp
@@ -10,7 +10,17 @@
 
 type
   TStatementType = (stUnknown, stSelect, stInsert, stUpdate, stDelete,
-    stDDL, stGetSegment, stPutSegment, stExecProcedure,
+    stCreateTable, stAlterTable, stDropTable,
+    stCreateDomain, stAlterDomain, stDropDomain,
+    stCreateIndex, stAlterIndex, stDropIndex,
+    stCreateView, stAlterView, stDropView,
+    stCreateTrigger, stAlterTrigger, stDropTrigger,
+    stCreateException, stAlterException, stDropException,
+    stCreateSequence, stAlterSequence, stDropSequence,
+    stCreateProcedure, stAlterProcedure, stDropProcedure, stExecProcedure,
+    stCreateUser, stAlterUser, stDropUser,
+    stCreateRole, stDropRole, stCreateShadow, stDropShadow,
+    stDDL, stGetSegment, stPutSegment,
     stStartTrans, stCommit, stRollback, stSelectForUpd);
 
   TDBEventType = (detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack, detParamValue, detActualSQL);
--- packages/fcl-db/src/sqldb/sqldb.pp
+++ packages/fcl-db/src/sqldb/sqldb.pp
@@ -30,11 +30,20 @@
   TQuoteChars = sqltypes.TQuoteChars;
 
 const
-  StatementTokens : Array[TStatementType] of string = ('(unknown)', 'select',
-                  'insert', 'update', 'delete',
-                  'create', 'get', 'put', 'execute',
-                  'start','commit','rollback', '?'
-                 );
+  StatementTokens : Array[TStatementType] of string = (
+    '(unknown)', 'select', 'insert', 'update', 'delete',
+    'create table', 'alter table', 'drop table',
+    'create domain', 'alter domain', 'drop domain',
+    'create index', 'alter index', 'drop index',
+    'create view', 'alter view', 'drop view',
+    'create trigger', 'alter trigger', 'drop trigger',
+    'create exception', 'alter exception', 'drop exception',
+    'create sequence', 'alter sequence', 'drop sequence',
+    'create procedure', 'alter procedure', 'drop procedure', 'execute procedure',
+    'create user', 'alter user', 'drop user',
+    'create role', 'drop role', 'create shadow', 'drop shadow',
+    'ddl', 'get', 'put',
+    'start','commit','rollback', '?');
   TSchemaObjectNames: array[TSchemaType] of String = ('???', 'table_name',
       '???', 'procedure_name', 'column_name', 'param_name',
       'index_name', 'package_name', 'schema_name','sequence');
@@ -84,7 +93,7 @@
 
   TSQLStatementInfo = Record
     StatementType : TStatementType;
-    TableName : String;
+    ObjectName : String;
     Updateable : Boolean;
     WhereStartPos ,
     WhereStopPos : integer;
@@ -1702,13 +1711,29 @@
 function TSQLConnection.GetStatementInfo(const ASQL: string): TSQLStatementInfo;
 
 type
-  TParsePart = (ppStart,ppWith,ppSelect,ppTableName,ppFrom,ppWhere,ppGroup,ppOrder,ppBogus);
-  TPhraseSeparator = (sepNone, sepWhiteSpace, sepComma, sepComment, sepParentheses, sepDoubleQuote, sepEnd);
-  TKeyword = (kwWITH, kwSELECT, kwINSERT, kwUPDATE, kwDELETE, kwFROM, kwJOIN, kwWHERE, kwGROUP, kwORDER, kwUNION, kwROWS, kwLIMIT, kwUnknown);
+  TParsePart =
+    (ppStart, ppBogus, ppSelect, ppInsert, ppUpdate, ppDelete, ppTableName,
+     ppFrom, ppWhere, ppGroup, ppOrder, ppWith, ppCreate, ppAlter, ppDrop,
+     ppExecute);
+  TPhraseSeparator =
+    (sepNone, sepWhiteSpace, sepComma, sepComment, sepParentheses,
+     sepDoubleQuote, sepEnd);
+  TKeyword =
+    (kwSELECT, kwINSERT, kwUPDATE, kwDELETE, kwFROM, kwJOIN, kwWHERE, kwGROUP,
+     kwORDER, kwUNION, kwROWS, kwLIMIT, kwWITH, kwINTO,
+     kwCREATE, kwALTER, kwDROP, kwEXECUTE,
+     kwTABLE, kwDOMAIN, kwINDEX, kwVIEW, kwTRIGGER, kwEXCEPTION, kwSEQUENCE,
+     kwPROCEDURE, kwUSER, kwROLE, kwSHADOW,
+     kwUnknown);
 
 const
   KeywordNames: array[TKeyword] of string =
-    ('WITH', 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'FROM', 'JOIN', 'WHERE', 'GROUP', 'ORDER', 'UNION', 'ROWS', 'LIMIT', '');
+    ('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'FROM', 'JOIN', 'WHERE', 'GROUP',
+     'ORDER', 'UNION', 'ROWS', 'LIMIT', 'WITH', 'INTO',
+     'CREATE', 'ALTER', 'DROP', 'EXECUTE',
+     'TABLE', 'DOMAIN', 'INDEX', 'VIEW', 'TRIGGER', 'EXCEPTION', 'SEQUENCE',
+     'PROCEDURE', 'USER', 'ROLE', 'SHADOW',
+     '');
 
 var
   PSQL, CurrentP, SavedP,
@@ -1726,7 +1751,7 @@
   CurrentP := PSQL-1;
   PhraseP := PSQL;
 
-  Result.TableName := '';
+  Result.ObjectName := '';
   Result.Updateable := False;
   Result.WhereStartPos := 0;
   Result.WhereStopPos := 0;
@@ -1789,8 +1814,15 @@
           ppStart  : begin
                      Result.StatementType := StrToStatementType(s);
                      case Keyword of
-                       kwWITH  : ParsePart := ppWith;
-                       kwSELECT: ParsePart := ppSelect;
+                       kwWITH   : ParsePart := ppWith;
+                       kwSELECT : ParsePart := ppSelect;
+                       kwINSERT : ParsePart := ppInsert;
+                       kwUPDATE : ParsePart := ppUpdate;
+                       kwDELETE : ParsePart := ppDelete;
+                       kwCREATE : ParsePart := ppCreate;
+                       kwALTER  : ParsePart := ppAlter;
+                       kwDROP   : ParsePart := ppDrop;
+                       kwEXECUTE: ParsePart := ppExecute;
                        else      break;
                      end;
                      end;
@@ -1809,19 +1841,30 @@
                      if Keyword = kwFROM then
                        ParsePart := ppTableName;
                      end;
-          ppTableName:
+          ppInsert:  begin
+                       if Keyword = kwINTO then
+                         ParsePart := ppTableName;
+                     end;
+          ppDelete:  begin
+                       if KeyWord = kwFROM then
+                         ParsePart := ppTableName;
+                     end;
+          ppTableName, ppUpdate:
                      begin
                      // Meta-data requests are never updateable
                      //  and select statements from more than one table
                      //  and/or derived tables are also not updateable
-                     if Separator in [sepWhitespace, sepComment, sepDoubleQuote, sepEnd] then
+                     if Separator in [sepWhitespace, sepComment, sepDoubleQuote,
+                                      sepParentheses, sepEnd] then
                        begin
-                       Result.TableName := Result.TableName + s;
-                       Result.Updateable := True;
+                       Result.ObjectName := s;
+                       if Result.StatementType = stSelect then
+                         Result.Updateable := True;
                        end;
-                     // compound delimited classifier like: "schema name"."table name"
-                     if not (CurrentP^ in ['.','"']) then
-                       ParsePart := ppFrom;
+                     if Result.StatementType in [stSelect, stUpdate] then
+                       ParsePart := ppFrom
+                     else
+                       ParsePart := ppBogus;
                      end;
           ppFrom   : begin
                      if (Keyword in [kwWHERE, kwGROUP, kwORDER, kwLIMIT, kwROWS]) or
@@ -1833,15 +1876,15 @@
                          kwORDER: ParsePart := ppOrder;
                          else     ParsePart := ppBogus;
                        end;
-
-                       Result.WhereStartPos := PhraseP-PSQL+1;
+                       if ParsePart = ppWhere then
+                         Result.WhereStartPos := PhraseP-PSQL+1;
                        PStatementPart := CurrentP;
                        end
                      else
                      // joined table or user_defined_function (...)
                      if (Keyword = kwJOIN) or (Separator in [sepComma, sepParentheses]) then
                        begin
-                       Result.TableName := '';
+                       Result.ObjectName := '';
                        Result.Updateable := False;
                        end;
                      end;
@@ -1861,6 +1904,60 @@
                        ParsePart := ppBogus;
                        Result.Updateable := False;
                        end;
+                     end;
+          ppCreate : begin
+                     case Keyword of
+                     kwTABLE    : Result.StatementType := stCreateTable;
+                     kwDOMAIN   : Result.StatementType := stCreateDomain;
+                     kwINDEX    : Result.StatementType := stCreateIndex;
+                     kwVIEW     : Result.StatementType := stCreateView;
+                     kwTRIGGER  : Result.StatementType := stCreateTrigger;
+                     kwEXCEPTION: Result.StatementType := stCreateException;
+                     kwSEQUENCE : Result.StatementType := stCreateSequence;
+                     kwPROCEDURE: Result.StatementType := stCreateProcedure;
+                     kwUSER     : Result.StatementType := stCreateUser;
+                     kwROLE     : Result.StatementType := stCreateRole;
+                     kwSHADOW   : Result.StatementType := stCreateShadow;
+                     else         break;
+                     end;
+                     ParsePart := ppTableName;
+                     end;
+          ppAlter  : begin
+                     case Keyword of
+                     kwTABLE    : Result.StatementType := stAlterTable;
+                     kwDOMAIN   : Result.StatementType := stAlterDomain;
+                     kwINDEX    : Result.StatementType := stAlterIndex;
+                     kwVIEW     : Result.StatementType := stAlterView;
+                     kwTRIGGER  : Result.StatementType := stAlterTrigger;
+                     kwEXCEPTION: Result.StatementType := stAlterException;
+                     kwSEQUENCE : Result.StatementType := stAlterSequence;
+                     kwPROCEDURE: Result.StatementType := stAlterProcedure;
+                     kwUSER     : Result.StatementType := stAlterUser;
+                     else         break;
+                     end;
+                     ParsePart := ppTableName;
+                     end;
+          ppDrop   : begin
+                     case Keyword of
+                     kwTABLE    : Result.StatementType := stDropTable;
+                     kwDOMAIN   : Result.StatementType := stDropDomain;
+                     kwINDEX    : Result.StatementType := stDropIndex;
+                     kwVIEW     : Result.StatementType := stDropView;
+                     kwTRIGGER  : Result.StatementType := stDropTrigger;
+                     kwEXCEPTION: Result.StatementType := stDropException;
+                     kwSEQUENCE : Result.StatementType := stDropSequence;
+                     kwPROCEDURE: Result.StatementType := stDropProcedure;
+                     kwUSER     : Result.StatementType := stDropUser;
+                     kwROLE     : Result.StatementType := stDropRole;
+                     kwSHADOW   : Result.StatementType := stDropShadow;
+                     else         break;
+                     end;
+                     ParsePart := ppTableName;
+                     end;
+          ppExecute: begin
+                     if Keyword = kwPROCEDURE then
+                       Result.StatementType := stExecProcedure;
+                     ParsePart := ppTableName;
                      end;
         end; {case}
         end;
@@ -2650,7 +2747,7 @@
     if (FQuery.FSchemaType = stNoSchema) and FParseSQL then
       begin
       FQuery.FUpdateable:=Info.Updateable;
-      FQuery.FTableName:=Info.TableName;
+      FQuery.FTableName:=Info.ObjectName;
       FQuery.FWhereStartPos:=Info.WhereStartPos;
       FQuery.FWhereStopPos:=Info.WhereStopPos;
       if FQuery.ServerFiltered then

Michael Van Canneyt

2020-05-26 09:43

administrator   ~0123069

I need to look at this patch in more detail. It needs a lot of changes before it can be applied:

for starters, you may not change the order of the TStatementType: people can rely on the order:

if (St>=stDDL) and (st<=StExecSQL) then

This will mean something totally different after your patch is applied. So the new types must be added at the end.

Secondly, things like "domain" and "shadow" are very specific to firebird, they must be removed.

I agree with you that the method should be made virtual, so it can be overridden for connection-specific constructs.

But the enumeration of statement types should contain only common DDL constructions.
We can maybe add a stSpecific to indicate an engine-specific construct (this is different from stUnknown)

If you want to help, you can maybe take care of the above remarks and supply a new patch.

Joeny Ang

2020-05-26 10:50

reporter   ~0123072

Ok, I'll try to implement your ideas. Thanks :)

Issue History

Date Modified Username Field Change
2020-05-26 08:16 Joeny Ang New Issue
2020-05-26 08:16 Joeny Ang File Added: fpc-sqldb-improve-TSQLConnection.GetStatementInfo.patch
2020-05-26 09:08 Michael Van Canneyt Assigned To => Michael Van Canneyt
2020-05-26 09:08 Michael Van Canneyt Status new => assigned
2020-05-26 09:43 Michael Van Canneyt Note Added: 0123069
2020-05-26 10:50 Joeny Ang Note Added: 0123072