trunk/testsuite/Test24.pas

(*
 *  IBX Test suite. This program is used to test the IBX non-visual
 *  components and provides a semi-automated pass/fail check for each test.
 *
 *  The contents of this file are subject to the Initial Developer's
 *  Public License Version 1.0 (the "License"); you may not use this
 *  file except in compliance with the License. You may obtain a copy
 *  of the License here:
 *
 *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
 *
 *  Software distributed under the License is distributed on an "AS
 *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 *  implied. See the License for the specific language governing rights
 *  and limitations under the License.
 *
 *  The Initial Developer of the Original Code is Tony Whyman.
 *
 *  The Original Code is (C) 2021 Tony Whyman, MWA Software
 *  (http://www.mwasoftware.co.uk).
 *
 *  All Rights Reserved.
 *
 *  Contributor(s): ______________________________________.
 *
*)      
unit Test24;

{$mode objfpc}{$H+}

{Test 24: IB Parser Tests}

{Iterates through a set of test strings, parsing each one and displaying the
 results including any parameters found. Note last string is the empty string}


interface

uses
  Classes, SysUtils,   TestApplication, IBXTestBase, IB, IBSQLParser, IBUtils;

const
  aTestID    = '24';
  aTestTitle = 'IB Parser Tests';

const
  TestStrings: array [0..13] of string = (
  'Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, 2.2, 2..30 '  +
       'A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH' + LF +
       'From EMPLOYEE A' +  LF +
       'JOIN Depts D On D.DEPT_NO = A.DEPT_NO;',

  'with recursive Depts As (' +
        'Select DEPT_NO, DEPARTMENT, "HEAD_DEPT", cast(DEPARTMENT  as VarChar(256)) as DEPT_PATH /* test */,' +
        'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH ' + LF +
        'From DEPARTMENT Where HEAD_DEPT is NULL ' + LF +
        'UNION ALL' + CRLF +
        'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH ||  '' / '' || D.DEPARTMENT as DEPT_PATH,' +
        'Depts.DEPT_KEY_PATH || '';'' || D.DEPT_NO as DEPT_KEY_PATH ' +
        'From DEPARTMENT  D'+ CR +
        'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO' + LF +
        ')' +
        '--ignore' + LF +
        'Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, 2.2, 2..30 '  +
        'A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH' + LF +
        'From EMPLOYEE A' +  LF +
        'JOIN Depts D On D.DEPT_NO = A.DEPT_NO',

        'Update EMPLOYEE A Set '#13#10'  A.DEPT_NO = :DEPT_NO,'#13#10 +
        '  A.FIRST_NAME = ''Mr/Ms '' || :FIRST_NAME,'#13#10+
        '  A.HIRE_DATE = :HIRE_DATE,'#13#10+
        '  A.JOB_CODE = :JOB_CODE,'#13#10'  A.JOB_COUNTRY = :JOB_COUNTRY,'#13#10+
        '  A.JOB_GRADE = :JOB_GRADE,'#13#10'  A.LAST_NAME = :LAST_NAME,'#13#10+
        '  A.PHONE_EXT = :PHONE_EXT,'#13#10'  A.SALARY = :SALARY ' +
        'Where A.EMP_NO = :OLD_EMP_NO;',

        'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
        'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY /* what''s this */) '+
        'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE, //end comment' + CRLF +
        ':DEPT_NO, :"JOB""CODE", ''Tester''''s way'''''''''', :JOB_COUNTRY, :"$SALARY")',

        'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',

        'Select DEPT_NO, DEPARTMENT, "HEAD_DEPT", cast(DEPARTMENT  as VarChar(256)) as DEPT_PATH /* test */,' +
        'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH ' + LF +
        'From DEPARTMENT Where HEAD_DEPT is NULL ' + LF +
        'UNION ALL' + CRLF +
        'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH ||  '' / '' || D.DEPARTMENT as DEPT_PATH,' +
        'Depts.DEPT_KEY_PATH || '';'' || D.DEPT_NO as DEPT_KEY_PATH ' +
        'From DEPARTMENT  D'+ CR +
        'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO',

        'Select DEPT_NO, DEPARTMENT, "HEAD_DEPT", cast(DEPARTMENT  as VarChar(256)) as DEPT_PATH /* test */,' +
        'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH ' + LF +
        'From DEPARTMENT Where HEAD_DEPT is NULL ' + LF +
        'UNION ALL' + CRLF +
        'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH ||  '' / '' || D.DEPARTMENT as DEPT_PATH,' +
        'Depts.DEPT_KEY_PATH || '';'' || D.DEPT_NO as DEPT_KEY_PATH ' +
        'From DEPARTMENT  D'+ CR +
        'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO '+
        'Order by 1,2',

        'with test1 as (Select * from Employee), test2 as (Select Emp_no, ''A test'' from Employee) '+
        'Select * from test1',

      'with recursive Account_Types_List As('#10+
      'Select ACCOUNTTYPE_ID, PARENT, ACCOUNTTYPENAME,'#10+
      '  ACCOUNTTYPENAME as SortName, '''' as INDENT'#10+
      'From MYLB_ACCOUNTTYPES'#10+
      'Where PARENT is Null'#10+
      'UNION ALL'#10+
      'Select A.ACCOUNTTYPE_ID, A.PARENT, A.ACCOUNTTYPENAME,'#10+
      'L.SortName || A.ACCOUNTTYPENAME as SortName, L.INDENT || ''    '' as INDENT'#10+
      'From MYLB_ACCOUNTTYPES A'#10+
      'Join Account_Types_List L On A.PARENT = L.ACCOUNTTYPE_ID)'#10+
      #10+
      'Select ACCOUNTTYPE_ID, PARENT, INDENT || ACCOUNTTYPENAME as Name'#10+
      'From Account_Types_List'#10+
      #10+
      'Order by SortName',

      'SELECT r.RDB$DB_KEY, cast(''Local'' as VarChar(6)) as MAPTYPE, r.RDB$MAP_NAME,'#10+
      '    Case'#10+
      '      When r.RDB$MAP_USING = ''P'' and r.RDB$MAP_PLUGIN is not null then cast (''Plugin '' || r.RDB$MAP_PLUGIN as VarChar(20))'#10+
      '      When r.RDB$MAP_USING = ''P'' and r.RDB$MAP_PLUGIN is null then ''Any Plugin'''#10+
      '      When r.RDB$MAP_USING = ''S'' then ''Any Plugin Serverwide'''#10+
      '      When r.RDB$MAP_USING = ''M'' then ''Mapping'''#10+
      '      When r.RDB$MAP_USING = ''*'' then ''*'''#10+
      '      else'#10+
      '        ''Using '' || r.RDB$MAP_USING || '','' || coalesce(r.RDB$MAP_PLUGIN,'''') End as MAP_USING,'#10+
      '    r.RDB$MAP_USING, r.RDB$MAP_PLUGIN,'#10+
      '    r.RDB$MAP_DB, r.RDB$MAP_FROM_TYPE, r.RDB$MAP_FROM,'#10+
      '    Trim(r.RDB$MAP_FROM_TYPE) || '': '' || Trim(r.RDB$MAP_FROM) as MAP_FROM,'#10+
      '    r.RDB$MAP_TO_TYPE,'#10+
      '    T.RDB$TYPE_NAME as MAP_TO_TYPE, r.RDB$MAP_TO,'#10+
      '    Trim(T.RDB$TYPE_NAME) || '': '' || Trim(r.RDB$MAP_TO) as MAP_TO,'#10+
      '    r.RDB$SYSTEM_FLAG, r.RDB$DESCRIPTION'#10+
      'FROM RDB$AUTH_MAPPING r'#10+
      'JOIN RDB$TYPES T On T.RDB$TYPE = r.RDB$MAP_TO_TYPE and T.RDB$FIELD_NAME = ''RDB$MAP_TO_TYPE'''#10+
      'UNION'#10+
      'SELECT r.RDB$DB_KEY, ''Global'', r.SEC$MAP_NAME,'#10+
      '    Case'#10+
      '      When r.SEC$MAP_USING = ''P'' and r.SEC$MAP_PLUGIN is not null then cast (''Plugin '' || r.SEC$MAP_PLUGIN as VarChar(20))'#10+
      '      When r.SEC$MAP_USING = ''P'' and r.SEC$MAP_PLUGIN is null then ''Any Plugin'''#10+
      '      When r.SEC$MAP_USING = ''S'' then ''Any Plugin Serverwide'''#10+
      '      When r.SEC$MAP_USING = ''M'' then ''Mapping'''#10+
      '      When r.SEC$MAP_USING = ''*'' then ''*'''#10+
      '      else'#10+
      '        ''Using '' || r.SEC$MAP_USING || '','' || coalesce(r.SEC$MAP_PLUGIN,'''') End as MAP_USING,'#10+
      '    r.SEC$MAP_USING, r.SEC$MAP_PLUGIN,'#10+
      '    r.SEC$MAP_DB, r.SEC$MAP_FROM_TYPE,  r.SEC$MAP_FROM,'#10+
      '    Trim(r.SEC$MAP_FROM_TYPE) || '': '' || Trim(r.SEC$MAP_FROM) as MAP_FROM,'#10+
      '    r.SEC$MAP_TO_TYPE,'#10+
      '    T.RDB$TYPE_NAME as MAP_TO_TYPE,r.SEC$MAP_TO,'#10+
      '    Trim(T.RDB$TYPE_NAME) || '': '' || Trim(r.SEC$MAP_TO) as MAP_TO,'#10+
      '    null,null'#10+
      'FROM SEC$GLOBAL_AUTH_MAPPING r'#10+
      'JOIN RDB$TYPES T On T.RDB$TYPE = r.SEC$MAP_TO_TYPE and T.RDB$FIELD_NAME = ''RDB$MAP_TO_TYPE''',

      'Select * From A '+
      'Group By a1,b1 '+
      'Union '+
      'Select * From B Rows 3 to 4',

      'Select * From A '+
      'Union '+
      'Select * From B Order by 1 Rows 3 to 4',

      'Select * From A '+
      'Union '+
      'Select * From B '+
      'Union '+
      'Select * FROM C Order by 1',

       '')  ;
type

{ TTest24 }

  TTest24 = class(TIBXTestBase)
  private
    procedure WriteSelect(parser: TSelectSQLParser);
  protected
    function GetTestID: AnsiString; override;
    function GetTestTitle: AnsiString; override;
  public
    procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
  end;


implementation

{ TTest24 }

procedure TTest24.WriteSelect(parser: TSelectSQLParser);
var j: integer;
begin
  with parser do
  begin
    for j := 0 to CTECount -1 do
    begin
      write(OutFile,'CTE',j);
      if CTES[j]^.Recursive then write(OutFile,' recursive');
      writeln(OutFile,': ',CTES[j]^.Name,' as ',CTEs[j]^.text);
    end;
    writeln(OutFile,'Select: ' ,SelectClause);
    writeln(OutFile,'From: ',FromClause);
    writeln(OutFile,'Where: ',WhereClause);
    writeln(OutFile,'Group By: ',GroupClause);
    writeln(OutFile,'Having: ',HavingClause);
    if Union <> nil then
    begin
      write(OutFile,'Union');
      if Union.UnionAll then write(OutFile,' All');
      writeln(OutFile);
      WriteSelect(Union);
    end;
    writeln(OutFile,'Order by: ',OrderByClause);
    writeln(OutFile,'Plan: ',PlanClause);
    writeln(OutFile,'Rows: ',RowsClause);
    writeln(OutFile,'SQL: ',SQLText);
    writeln(OutFile,'Params: ');
    for j := 0 to ParamList.Count - 1 do
      write(OutFile,ParamList[j],',');
    writeln(OutFile);
    writeln(OutFile);
  end;
end;

function TTest24.GetTestID: AnsiString;
begin
  Result := aTestID;
end;

function TTest24.GetTestTitle: AnsiString;
begin
  Result := aTestTitle;
end;

procedure TTest24.RunTest(CharSet: AnsiString; SQLDialect: integer);
var i: integer;
    parser: TSelectSQLParser;
begin
  for i := 0 to Length(TestStrings) -1 do
  begin
    parser :=  TSelectSQLParser.Create(nil,TestStrings[i]);
    try
    with parser do
    begin
      writeln(OutFile,'Test String ',i);
      writeln(OutFile,'---------------------------------------------------');
      writeln(OutFile,TestStrings[i]);
      writeln(OutFile,'---------------------------------------------------');
      writeln(OutFile);
      if NotaSelectStmt then
      begin
        writeln(OutFile,'Not a Select Statement');
        continue;
      end;
      WriteSelect(parser);
      if i = 4 then
        Add2WhereClause('JOB_CODE = 2');
      if i = 5 then
         OrderByClause := '1';
      writeln(Outfile,'Updated SQL');
      writeln(OutFile,SQLText);
    end;
    finally
      parser.Free
    end;
  end;
end;

initialization
  RegisterTest(TTest24);

end.

Revision:	323
Committed:	Thu Feb 25 12:14:35 2021 UTC (3 years, 8 months ago) by tony
Content type:	text/x-pascal
File size:	10734 byte(s)
Log Message:	Fixed Merged
#	User	Rev	Content
1	tony	323	(*
2			* IBX Test suite. This program is used to test the IBX non-visual
3			* components and provides a semi-automated pass/fail check for each test.
4			*
5			* The contents of this file are subject to the Initial Developer's
6			* Public License Version 1.0 (the "License"); you may not use this
7			* file except in compliance with the License. You may obtain a copy
8			* of the License here:
9			*
10			* http://www.firebirdsql.org/index.php?op=doc&id=idpl
11			*
12			* Software distributed under the License is distributed on an "AS
13			* IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
14			* implied. See the License for the specific language governing rights
15			* and limitations under the License.
16			*
17			* The Initial Developer of the Original Code is Tony Whyman.
18			*
19			* The Original Code is (C) 2021 Tony Whyman, MWA Software
20			* (http://www.mwasoftware.co.uk).
21			*
22			* All Rights Reserved.
23			*
24			* Contributor(s): ______________________________________.
25			*
26			*)
27	tony	315	unit Test24;
28
29			{$mode objfpc}{$H+}
30
31			{Test 24: IB Parser Tests}
32
33			{Iterates through a set of test strings, parsing each one and displaying the
34			results including any parameters found. Note last string is the empty string}
35
36
37			interface
38
39			uses
40			Classes, SysUtils, TestApplication, IBXTestBase, IB, IBSQLParser, IBUtils;
41
42			const
43			aTestID = '24';
44			aTestTitle = 'IB Parser Tests';
45
46			const
47			TestStrings: array [0..13] of string = (
48			'Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, 2.2, 2..30 ' +
49			'A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH' + LF +
50			'From EMPLOYEE A' + LF +
51			'JOIN Depts D On D.DEPT_NO = A.DEPT_NO;',
52
53			'with recursive Depts As (' +
54			'Select DEPT_NO, DEPARTMENT, "HEAD_DEPT", cast(DEPARTMENT as VarChar(256)) as DEPT_PATH /* test */,' +
55			'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH ' + LF +
56			'From DEPARTMENT Where HEAD_DEPT is NULL ' + LF +
57			'UNION ALL' + CRLF +
58			'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH \|\| '' / '' \|\| D.DEPARTMENT as DEPT_PATH,' +
59			'Depts.DEPT_KEY_PATH \|\| '';'' \|\| D.DEPT_NO as DEPT_KEY_PATH ' +
60			'From DEPARTMENT D'+ CR +
61			'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO' + LF +
62			')' +
63			'--ignore' + LF +
64			'Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE, 2.2, 2..30 ' +
65			'A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH' + LF +
66			'From EMPLOYEE A' + LF +
67			'JOIN Depts D On D.DEPT_NO = A.DEPT_NO',
68
69			'Update EMPLOYEE A Set '#13#10' A.DEPT_NO = :DEPT_NO,'#13#10 +
70			' A.FIRST_NAME = ''Mr/Ms '' \|\| :FIRST_NAME,'#13#10+
71			' A.HIRE_DATE = :HIRE_DATE,'#13#10+
72			' A.JOB_CODE = :JOB_CODE,'#13#10' A.JOB_COUNTRY = :JOB_COUNTRY,'#13#10+
73			' A.JOB_GRADE = :JOB_GRADE,'#13#10' A.LAST_NAME = :LAST_NAME,'#13#10+
74			' A.PHONE_EXT = :PHONE_EXT,'#13#10' A.SALARY = :SALARY ' +
75			'Where A.EMP_NO = :OLD_EMP_NO;',
76
77			'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
78			'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY /* what''s this */) '+
79			'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE, //end comment' + CRLF +
80			':DEPT_NO, :"JOB""CODE", ''Tester''''s way'''''''''', :JOB_COUNTRY, :"$SALARY")',
81
82			'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',
83
84			'Select DEPT_NO, DEPARTMENT, "HEAD_DEPT", cast(DEPARTMENT as VarChar(256)) as DEPT_PATH /* test */,' +
85			'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH ' + LF +
86			'From DEPARTMENT Where HEAD_DEPT is NULL ' + LF +
87			'UNION ALL' + CRLF +
88			'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH \|\| '' / '' \|\| D.DEPARTMENT as DEPT_PATH,' +
89			'Depts.DEPT_KEY_PATH \|\| '';'' \|\| D.DEPT_NO as DEPT_KEY_PATH ' +
90			'From DEPARTMENT D'+ CR +
91			'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO',
92
93			'Select DEPT_NO, DEPARTMENT, "HEAD_DEPT", cast(DEPARTMENT as VarChar(256)) as DEPT_PATH /* test */,' +
94			'cast(DEPT_NO as VarChar(64)) as DEPT_KEY_PATH ' + LF +
95			'From DEPARTMENT Where HEAD_DEPT is NULL ' + LF +
96			'UNION ALL' + CRLF +
97			'Select D.DEPT_NO, D.DEPARTMENT, D.HEAD_DEPT, Depts.DEPT_PATH \|\| '' / '' \|\| D.DEPARTMENT as DEPT_PATH,' +
98			'Depts.DEPT_KEY_PATH \|\| '';'' \|\| D.DEPT_NO as DEPT_KEY_PATH ' +
99			'From DEPARTMENT D'+ CR +
100			'JOIN Depts On D.HEAD_DEPT = Depts.DEPT_NO '+
101			'Order by 1,2',
102
103			'with test1 as (Select * from Employee), test2 as (Select Emp_no, ''A test'' from Employee) '+
104			'Select * from test1',
105
106			'with recursive Account_Types_List As('#10+
107			'Select ACCOUNTTYPE_ID, PARENT, ACCOUNTTYPENAME,'#10+
108			' ACCOUNTTYPENAME as SortName, '''' as INDENT'#10+
109			'From MYLB_ACCOUNTTYPES'#10+
110			'Where PARENT is Null'#10+
111			'UNION ALL'#10+
112			'Select A.ACCOUNTTYPE_ID, A.PARENT, A.ACCOUNTTYPENAME,'#10+
113			'L.SortName \|\| A.ACCOUNTTYPENAME as SortName, L.INDENT \|\| '' '' as INDENT'#10+
114			'From MYLB_ACCOUNTTYPES A'#10+
115			'Join Account_Types_List L On A.PARENT = L.ACCOUNTTYPE_ID)'#10+
116			#10+
117			'Select ACCOUNTTYPE_ID, PARENT, INDENT \|\| ACCOUNTTYPENAME as Name'#10+
118			'From Account_Types_List'#10+
119			#10+
120			'Order by SortName',
121
122			'SELECT r.RDB$DB_KEY, cast(''Local'' as VarChar(6)) as MAPTYPE, r.RDB$MAP_NAME,'#10+
123			' Case'#10+
124			' When r.RDB$MAP_USING = ''P'' and r.RDB$MAP_PLUGIN is not null then cast (''Plugin '' \|\| r.RDB$MAP_PLUGIN as VarChar(20))'#10+
125			' When r.RDB$MAP_USING = ''P'' and r.RDB$MAP_PLUGIN is null then ''Any Plugin'''#10+
126			' When r.RDB$MAP_USING = ''S'' then ''Any Plugin Serverwide'''#10+
127			' When r.RDB$MAP_USING = ''M'' then ''Mapping'''#10+
128			' When r.RDB$MAP_USING = '''' then '''''#10+
129			' else'#10+
130			' ''Using '' \|\| r.RDB$MAP_USING \|\| '','' \|\| coalesce(r.RDB$MAP_PLUGIN,'''') End as MAP_USING,'#10+
131			' r.RDB$MAP_USING, r.RDB$MAP_PLUGIN,'#10+
132			' r.RDB$MAP_DB, r.RDB$MAP_FROM_TYPE, r.RDB$MAP_FROM,'#10+
133			' Trim(r.RDB$MAP_FROM_TYPE) \|\| '': '' \|\| Trim(r.RDB$MAP_FROM) as MAP_FROM,'#10+
134			' r.RDB$MAP_TO_TYPE,'#10+
135			' T.RDB$TYPE_NAME as MAP_TO_TYPE, r.RDB$MAP_TO,'#10+
136			' Trim(T.RDB$TYPE_NAME) \|\| '': '' \|\| Trim(r.RDB$MAP_TO) as MAP_TO,'#10+
137			' r.RDB$SYSTEM_FLAG, r.RDB$DESCRIPTION'#10+
138			'FROM RDB$AUTH_MAPPING r'#10+
139			'JOIN RDB$TYPES T On T.RDB$TYPE = r.RDB$MAP_TO_TYPE and T.RDB$FIELD_NAME = ''RDB$MAP_TO_TYPE'''#10+
140			'UNION'#10+
141			'SELECT r.RDB$DB_KEY, ''Global'', r.SEC$MAP_NAME,'#10+
142			' Case'#10+
143			' When r.SEC$MAP_USING = ''P'' and r.SEC$MAP_PLUGIN is not null then cast (''Plugin '' \|\| r.SEC$MAP_PLUGIN as VarChar(20))'#10+
144			' When r.SEC$MAP_USING = ''P'' and r.SEC$MAP_PLUGIN is null then ''Any Plugin'''#10+
145			' When r.SEC$MAP_USING = ''S'' then ''Any Plugin Serverwide'''#10+
146			' When r.SEC$MAP_USING = ''M'' then ''Mapping'''#10+
147			' When r.SEC$MAP_USING = '''' then '''''#10+
148			' else'#10+
149			' ''Using '' \|\| r.SEC$MAP_USING \|\| '','' \|\| coalesce(r.SEC$MAP_PLUGIN,'''') End as MAP_USING,'#10+
150			' r.SEC$MAP_USING, r.SEC$MAP_PLUGIN,'#10+
151			' r.SEC$MAP_DB, r.SEC$MAP_FROM_TYPE, r.SEC$MAP_FROM,'#10+
152			' Trim(r.SEC$MAP_FROM_TYPE) \|\| '': '' \|\| Trim(r.SEC$MAP_FROM) as MAP_FROM,'#10+
153			' r.SEC$MAP_TO_TYPE,'#10+
154			' T.RDB$TYPE_NAME as MAP_TO_TYPE,r.SEC$MAP_TO,'#10+
155			' Trim(T.RDB$TYPE_NAME) \|\| '': '' \|\| Trim(r.SEC$MAP_TO) as MAP_TO,'#10+
156			' null,null'#10+
157			'FROM SEC$GLOBAL_AUTH_MAPPING r'#10+
158			'JOIN RDB$TYPES T On T.RDB$TYPE = r.SEC$MAP_TO_TYPE and T.RDB$FIELD_NAME = ''RDB$MAP_TO_TYPE''',
159
160			'Select * From A '+
161			'Group By a1,b1 '+
162			'Union '+
163			'Select * From B Rows 3 to 4',
164
165			'Select * From A '+
166			'Union '+
167			'Select * From B Order by 1 Rows 3 to 4',
168
169			'Select * From A '+
170			'Union '+
171			'Select * From B '+
172			'Union '+
173			'Select * FROM C Order by 1',
174
175			'') ;
176			type
177
178			{ TTest24 }
179
180			TTest24 = class(TIBXTestBase)
181			private
182			procedure WriteSelect(parser: TSelectSQLParser);
183			protected
184			function GetTestID: AnsiString; override;
185			function GetTestTitle: AnsiString; override;
186			public
187			procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
188			end;
189
190
191			implementation
192
193			{ TTest24 }
194
195			procedure TTest24.WriteSelect(parser: TSelectSQLParser);
196			var j: integer;
197			begin
198			with parser do
199			begin
200			for j := 0 to CTECount -1 do
201			begin
202			write(OutFile,'CTE',j);
203			if CTES[j]^.Recursive then write(OutFile,' recursive');
204			writeln(OutFile,': ',CTES[j]^.Name,' as ',CTEs[j]^.text);
205			end;
206			writeln(OutFile,'Select: ' ,SelectClause);
207			writeln(OutFile,'From: ',FromClause);
208			writeln(OutFile,'Where: ',WhereClause);
209			writeln(OutFile,'Group By: ',GroupClause);
210			writeln(OutFile,'Having: ',HavingClause);
211			if Union <> nil then
212			begin
213			write(OutFile,'Union');
214			if Union.UnionAll then write(OutFile,' All');
215			writeln(OutFile);
216			WriteSelect(Union);
217			end;
218			writeln(OutFile,'Order by: ',OrderByClause);
219			writeln(OutFile,'Plan: ',PlanClause);
220			writeln(OutFile,'Rows: ',RowsClause);
221			writeln(OutFile,'SQL: ',SQLText);
222			writeln(OutFile,'Params: ');
223			for j := 0 to ParamList.Count - 1 do
224			write(OutFile,ParamList[j],',');
225			writeln(OutFile);
226			writeln(OutFile);
227			end;
228			end;
229
230			function TTest24.GetTestID: AnsiString;
231			begin
232			Result := aTestID;
233			end;
234
235			function TTest24.GetTestTitle: AnsiString;
236			begin
237			Result := aTestTitle;
238			end;
239
240			procedure TTest24.RunTest(CharSet: AnsiString; SQLDialect: integer);
241			var i: integer;
242			parser: TSelectSQLParser;
243			begin
244			for i := 0 to Length(TestStrings) -1 do
245			begin
246			parser := TSelectSQLParser.Create(nil,TestStrings[i]);
247			try
248			with parser do
249			begin
250			writeln(OutFile,'Test String ',i);
251			writeln(OutFile,'---------------------------------------------------');
252			writeln(OutFile,TestStrings[i]);
253			writeln(OutFile,'---------------------------------------------------');
254			writeln(OutFile);
255			if NotaSelectStmt then
256			begin
257			writeln(OutFile,'Not a Select Statement');
258			continue;
259			end;
260			WriteSelect(parser);
261			if i = 4 then
262			Add2WhereClause('JOB_CODE = 2');
263			if i = 5 then
264			OrderByClause := '1';
265			writeln(Outfile,'Updated SQL');
266			writeln(OutFile,SQLText);
267			end;
268			finally
269			parser.Free
270			end;
271			end;
272			end;
273
274			initialization
275			RegisterTest(TTest24);
276
277			end.
278