SynEdit - Get current SQL command block

[aehimself 396]

Hello,

 

I'm using TurboPack SynEdit latest version, fresh from GitHub. I have an editor where the user can enter multiple SQL commands, like:

 

SELECT * FROM MYTABLE

SELECT * FROM OTHERTABLE o

[...]

 

Is there a way SynEdt can return the full block, but the current command only? So, if the cursor is staying in line 1 just return "SELECT * FROM MYTABLE" but at line 2 it would "SELECT * FROM OTHERTABLE o"? Keep in mind that commands can be split across several lines, so simply returning the current line is not a good solution.

 

Summoning @pyscripter 🙂

[MarkShark 27]

Hey @aehimself

 

SynEdit does have the SQLHighlighter which can give you some context information about the SQL, but it doesn't really have a full SQL script processor.  Have you looked at the one in ZeosLib?  I don't use it for my stuff as I had written one for Oracle's scripting language way back and then adopted it for the other databases with my newer products.   Note that you will have to use some sort of statement terminator (which can be as simple as a semicolon or it can be super complex with Oracle's SQLPlus syntax or less complex with a standard "set terminator" type statement.)  My tools are Golden (for Oracle) and GoldSqall (for Oracle, PostgresSQL, Firebird, MySQL, etc) and they all have to do exactly what you're doing.

 

-Mark Ford

Benthic Software

 

[aehimself 396]

I was afraid of this so.

 

I do use ZeosLib. The main purpose I'm interested about the current command is to parse table aliases (which Zeos is kind enough to do so) but I need to be able to provide the current command only - otherwise it only considers the first one. Even though if I can split up to separate commands, I somehow need to confirm if that is the command under the cursor...

[ertank 27]

AFAIK, it is not common to have support for such parsing in text editor codes.

 

BTW, what you are trying to do is not a very simple task. You probably need to build it yourself even if you find a source to use as a base. It takes time and a lot of corner case fixes for your database of choice. You should consider having per database parsing like one for SQLite another for SQL Server, etc.

 

Also consider following examples. They are FirebirdSQL specific.

WITH CTE
AS (SELECT EXTRACT(WEEKDAY FROM ADATE) AS DAYOFWEEK, PAYMENTID, PAYMENTMETHODID, AVG(GENERALTOTALPRICE) GENERALTOTALPRICE
    FROM TRANSFERS
    WHERE STATIONID = 1 AND (PAYMENTMETHODID = 10 OR PAYMENTMETHODID = 20 OR PAYMENTMETHODID = 50 OR PAYMENTMETHODID = 60 OR PAYMENTMETHODID = 205) AND (TARIH >= '2022-01-01' AND TARIH <= '2022-01-31 23:59:59.999')
    GROUP BY 1, 2, 3)
SELECT DAYOFWEEK, SUM(GENERALTOTALPRICE)
FROM CTE
GROUP BY 1

UNION

SELECT 7, SUM(GENERALTOTALPRICE)
FROM CTE
GROUP BY 1

 

Another example;

set term ^ ;
execute block as
begin
  if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'POS' and rf.RDB$FIELD_NAME = 'AGROUP')) then
    execute statement 'alter table pos add agroup varchar(100);';
  if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'FUEL' and rf.RDB$FIELD_NAME = 'AGROUP')) then
    execute statement 'alter table fuel add agroup varchar(100);';
  if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'PAYMENT' and rf.RDB$FIELD_NAME = 'AGROUP')) then
    execute statement 'alter table payment add agroup varchar(100);';
end^
set term ; ^

 

I do not know your user base but all these are valid SQL statements for FirebirdSQL and can be placed one after another, might also include "COMMIT" statements between which is another thing to control in your code if you are to parse and execute each identified block as its own.

 

I did not check ZeosLib codebase. If they have any "parsing" and "identifying" SQL statement blocks. That would be a good starting point.

 

Alternative is to force your users to use simple statements with default terminator at the end. Such SQL statements are pretty easy to parse.

[aehimself 396]

For anyone interested I solved this without writing an actual interpreter, it parses all select aliases. The backdraw is that it offers aliases to use in statements where there is no such alias therefore executing the statement will throw an exception. This annoyance is perfectly acceptable by me, so probably I'll just leave it as it is.

 

It derives a new analyzer from Zeos's generic analyzer, and it looks like this:

Unit uAEGenericSqlAnalyser;

Interface

Uses ZGenericSqlAnalyser, ZTokenizer, ZSelectSchema;

Type
  TAEGenericSqlAnalyser = Class(TZGenericStatementAnalyser)
  public
    Function DefineAllSelectSchemaFromQuery(const Tokenizer: IZTokenizer; const SQL: string): TArray<IZSelectSchema>;
  End;

Implementation

Uses System.Contnrs, System.Classes;

Function TAEGenericSqlAnalyser.DefineAllSelectSchemaFromQuery(Const Tokenizer: IZTokenizer; Const SQL: string): TArray<IZSelectSchema>;
Var
  Tokens: TZTokenList;
  sections: TObjectList;
  ss: IZSelectSchema;
  FromTokens: TZTokenList;
  deleted: Boolean;
Begin
  SetLength(Result, 0);

  Tokens := TokenizeQuery(Tokenizer, SQL, True);
  Sections := SplitSections(Tokens);
  Try
    Repeat
      ss := DefineSelectSchemaFromSections(Sections);
      If Assigned(ss) Then
      Begin
        SetLength(Result, Length(Result) + 1);
        Result[High(Result)] := ss;

        FromTokens := FindSectionTokens(Sections, 'FROM');

        deleted := False;
        While (Sections.Count > 0) And Not deleted Do
        Begin
          deleted := TZStatementSection(sections[0]).Tokens = FromTokens;
          Sections.Delete(0);
        End;
      End;
    Until ss = nil;
  Finally
    Tokens.Free;
    Sections.Free;
  End;
End;

End.

 

To use it:

Procedure TForm1.GetTableName(Var inNameOrAlias: String);
Var
  sa: TAEGenericSqlAnalyser;
  sch: IZSelectSchema;
  a: Integer;
Begin
  sa := TAEGenericSqlAnalyser.Create;
  Try
    For sch In sa.DefineAllSelectSchemaFromQuery(SQLConnection.DbcConnection.GetTokenizer, SQLCommand) Do
      For a := 0 To sch.TableCount - 1 Do
        If sch.Tables[a].Table.ToLower = inNameOrAlias.ToLower Then
          Exit
        Else If sch.Tables[a].Alias.ToLower = inNameOrAlias.ToLower Then
        Begin
          inNameOrAlias := sch.Tables[a].Table;
          Exit;
        End;
  Finally
    FreeAndNil(sa);
  End;
End;

SQLConnection is a TZConnection object (can be replaced with TZTokenizer.Create if no connection is available), SQLCommand is the string containing all the statements. Simply call this method to "convert" an alias to a table name.

 

It can be optimized but since it's already pretty quick, I didn't really bother.

Yeah, extracting the current block would be more elegant. In 1 hours with minimal personal code, this is what I could achieve 🙂

Edited September 20, 2022 by aehimself