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:	380
Committed:	Mon Jan 10 10:13:17 2022 UTC (2 years, 10 months ago) by tony
Content type:	text/x-pascal
File size:	10734 byte(s)
Log Message:	propset for eol-style
#	Content
1	(*
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	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