00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { SQL Select Objects and Assembler classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZSelectSchema;
00055
00056 interface
00057
00058 {$I ZParseSql.inc}
00059
00060 uses ZClasses, Contnrs, ZCompatibility;
00061
00062 type
00063
00064 {** Case Sensitive/Unsensitive identificator processor. }
00065 IZIdentifierConvertor = interface (IZInterface)
00066 ['{2EB07B9B-1E96-4A42-8084-6F98D9140B27}']
00067
00068 function IsCaseSensitive(const Value: string): Boolean;
00069 function IsQuoted(const Value: string): Boolean;
00070 function Quote(const Value: string): string;
00071 function ExtractQuote(const Value: string): string;
00072 end;
00073
00074 {** Implements a table reference assembly. }
00075 TZTableRef = class (TObject)
00076 private
00077 FCatalog: string;
00078 FSchema: string;
00079 FTable: string;
00080 FAlias: string;
00081 public
00082 constructor Create(const Catalog, Schema, Table, Alias: string);
00083 function FullName: string;
00084
00085 property Catalog: string read FCatalog write FCatalog;
00086 property Schema: string read FSchema write FSchema;
00087 property Table: string read FTable write FTable;
00088 property Alias: string read FAlias write FAlias;
00089 end;
00090
00091 {** Implements a field reference assembly. }
00092 TZFieldRef = class (TObject)
00093 private
00094 FIsField: Boolean;
00095 FCatalog: string;
00096 FSchema: string;
00097 FTable: string;
00098 FField: string;
00099 FAlias: string;
00100 FTableRef: TZTableRef;
00101 FLinked: Boolean;
00102 public
00103 constructor Create(IsField: Boolean; const Catalog, Schema, Table,
00104 Field, Alias: string; TableRef: TZTableRef);
00105
00106 property IsField: Boolean read FIsField write FIsField;
00107 property Catalog: string read FCatalog write FCatalog;
00108 property Schema: string read FSchema write FSchema;
00109 property Table: string read FTable write FTable;
00110 property Field: string read FField write FField;
00111 property Alias: string read FAlias write FAlias;
00112 property TableRef: TZTableRef read FTableRef write FTableRef;
00113 property Linked: Boolean read FLinked write FLinked;
00114 end;
00115
00116 {** Defines an interface to select assembly. }
00117 IZSelectSchema = interface (IZInterface)
00118 ['{3B892975-57E9-4EB7-8DB1-BDDED91E7FBC}']
00119
00120 procedure AddField(FieldRef: TZFieldRef);
00121 procedure InsertField(Index: Integer; FieldRef: TZFieldRef);
00122 procedure DeleteField(FieldRef: TZFieldRef);
00123
00124 procedure AddTable(TableRef: TZTableRef);
00125
00126 procedure LinkReferences(Convertor: IZIdentifierConvertor);
00127
00128 function FindTableByFullName(const Catalog, Schema, Table: string): TZTableRef;
00129 function FindTableByShortName(const Table: string): TZTableRef;
00130 function FindFieldByShortName(const Field: string): TZFieldRef;
00131
00132 function LinkFieldByIndexAndShortName(
00133 ColumnIndex: Integer; const Field: string): TZFieldRef;
00134
00135 function GetFieldCount: Integer;
00136 function GetTableCount: Integer;
00137 function GetField(Index: Integer): TZFieldRef;
00138 function GetTable(Index: Integer): TZTableRef;
00139
00140 property FieldCount: Integer read GetFieldCount;
00141 property Fields[Index: Integer]: TZFieldRef read GetField;
00142 property TableCount: Integer read GetTableCount;
00143 property Tables[Index: Integer]: TZTableRef read GetTable;
00144 end;
00145
00146 {** Implements a select assembly. }
00147 TZSelectSchema = class (TZAbstractObject, IZSelectSchema)
00148 private
00149 FFields: TObjectList;
00150 FTables: TObjectList;
00151
00152 procedure ConvertIdentifiers(Convertor: IZIdentifierConvertor);
00153 public
00154 constructor Create;
00155 destructor Destroy; override;
00156
00157 procedure AddField(FieldRef: TZFieldRef);
00158 procedure InsertField(Index: Integer; FieldRef: TZFieldRef);
00159 procedure DeleteField(FieldRef: TZFieldRef);
00160
00161 procedure AddTable(TableRef: TZTableRef);
00162
00163 procedure LinkReferences(Convertor: IZIdentifierConvertor);
00164
00165 function FindTableByFullName(const Catalog, Schema, Table: string): TZTableRef;
00166 function FindTableByShortName(const Table: string): TZTableRef;
00167 function FindFieldByShortName(const Field: string): TZFieldRef;
00168
00169 function LinkFieldByIndexAndShortName(
00170 ColumnIndex: Integer; const Field: string): TZFieldRef;
00171
00172 function GetFieldCount: Integer;
00173 function GetTableCount: Integer;
00174 function GetField(Index: Integer): TZFieldRef;
00175 function GetTable(Index: Integer): TZTableRef;
00176
00177 property FieldCount: Integer read GetFieldCount;
00178 property Fields[Index: Integer]: TZFieldRef read GetField;
00179 property TableCount: Integer read GetTableCount;
00180 property Tables[Index: Integer]: TZTableRef read GetTable;
00181 end;
00182
00183 implementation
00184
00185 { TZTableRef }
00186
00187 {**
00188 Creates a table reference object.
00189 @param Catalog a catalog name.
00190 @param Schema a schema name.
00191 @param Table a table name.
00192 @param Alias a table alias.
00193 }
00194 constructor TZTableRef.Create(const Catalog, Schema, Table, Alias: string);
00195 begin
00196 FCatalog := Catalog;
00197 FSchema := Schema;
00198 FTable := Table;
00199 FAlias := Alias;
00200 end;
00201
00202 {**
00203 Gets a full database table name.
00204 @return a full database table name.
00205 }
00206 function TZTableRef.FullName: string;
00207 begin
00208 Result := FCatalog + '.' + FSchema + '.' + FTable;
00209
00210 while (Result <> '') and (Result[1] = '.') do
00211 Delete(Result, 1, 1);
00212 end;
00213
00214 { TZFieldRef }
00215
00216 {**
00217 Creates a field reference object.
00218 @param IsField flag which separates table columns from expressions.
00219 @param Catalog a catalog name.
00220 @param Schema a schema name.
00221 @param Table a table name.
00222 @param Field a field name.
00223 @param Alias a field alias.
00224 }
00225 constructor TZFieldRef.Create(IsField: Boolean; const Catalog, Schema, Table,
00226 Field, Alias: string; TableRef: TZTableRef);
00227 begin
00228 FIsField := IsField;
00229 FCatalog := Catalog;
00230 FSchema := Schema;
00231 FTable := Table;
00232 FField := Field;
00233 FAlias := Alias;
00234 FTableRef := TableRef;
00235 FLinked := False;
00236 end;
00237
00238 { TZSelectSchema }
00239
00240 {**
00241 Constructs this assembly object and assignes the main properties.
00242 }
00243 constructor TZSelectSchema.Create;
00244 begin
00245 FFields := TObjectList.Create;
00246 FTables := TObjectList.Create;
00247 end;
00248
00249 {**
00250 Destroys this object and cleanups the memory.
00251 }
00252 destructor TZSelectSchema.Destroy;
00253 begin
00254 FFields.Free;
00255 FTables.Free;
00256 end;
00257
00258 {**
00259 Finds a table reference by catalog and table name.
00260 @param Catalog a database catalog name.
00261 @param Schema a database schema name.
00262 @param Table a database table name.
00263 @return a found table reference object or <code>null</code> otherwise.
00264 }
00265 function TZSelectSchema.FindTableByFullName(
00266 const Catalog, Schema, Table: string): TZTableRef;
00267 var
00268 I: Integer;
00269 Current: TZTableRef;
00270 begin
00271 Result := nil;
00272
00273 { Looks a table by it's full name. }
00274 for I := 0 to FTables.Count - 1 do
00275 begin
00276 Current := TZTableRef(FTables[I]);
00277 if (Current.Schema = Schema) and (Current.Table = Table) then
00278 begin
00279 Result := Current;
00280 Exit;
00281 end;
00282 end;
00283
00284 { Looks a table by it's short name. }
00285 for I := 0 to FTables.Count - 1 do
00286 begin
00287 Current := TZTableRef(FTables[I]);
00288 if (Current.Schema = '') and (Current.Table = Table) then
00289 begin
00290 Result := Current;
00291 Exit;
00292 end;
00293 end;
00294 end;
00295
00296 {**
00297 Finds a table reference by table name or table alias.
00298 @param Table a database table name or alias.
00299 @return a found table reference object or <code>null</code> otherwise.
00300 }
00301 function TZSelectSchema.FindTableByShortName(const Table: string): TZTableRef;
00302 var
00303 I: Integer;
00304 Current: TZTableRef;
00305 begin
00306 Result := nil;
00307
00308 { Looks a table by it's alias. }
00309 for I := 0 to FTables.Count - 1 do
00310 begin
00311 Current := TZTableRef(FTables[I]);
00312 if Current.Alias = Table then
00313 begin
00314 Result := Current;
00315 Exit;
00316 end;
00317 end;
00318
00319 { Looks a table by it's name. }
00320 for I := 0 to FTables.Count - 1 do
00321 begin
00322 Current := TZTableRef(FTables[I]);
00323 if Current.Table = Table then
00324 begin
00325 Result := Current;
00326 Exit;
00327 end;
00328 end;
00329 end;
00330
00331 {**
00332 Finds a field reference by field name or field alias.
00333 @param Field a table field name or alias.
00334 @return a found field reference object or <code>null</code> otherwise.
00335 }
00336 function TZSelectSchema.FindFieldByShortName(const Field: string): TZFieldRef;
00337 var
00338 I: Integer;
00339 Current: TZFieldRef;
00340 begin
00341 Result := nil;
00342 if Field = '' then
00343 Exit;
00344
00345 { Looks a field by it's alias. }
00346 for I := 0 to FFields.Count - 1 do
00347 begin
00348 Current := TZFieldRef(FFields[I]);
00349 if Current.Alias = Field then
00350 begin
00351 Result := Current;
00352 Exit;
00353 end;
00354 end;
00355
00356 { Looks a field by it's name. }
00357 for I := 0 to FFields.Count - 1 do
00358 begin
00359 Current := TZFieldRef(FFields[I]);
00360 if Current.Field = Field then
00361 begin
00362 Result := Current;
00363 Exit;
00364 end;
00365 end;
00366 end;
00367
00368 {**
00369 Links a field reference by index and/or field name or field alias.
00370 @param ColumnIndex an index of the column.
00371 @param Field a table field name or alias.
00372 @return a found field reference object or <code>null</code> otherwise.
00373 }
00374 function TZSelectSchema.LinkFieldByIndexAndShortName(
00375 ColumnIndex: Integer; const Field: string): TZFieldRef;
00376 var
00377 I: Integer;
00378 Current: TZFieldRef;
00379 begin
00380 Result := nil;
00381 if Field = '' then
00382 Exit;
00383
00384 { Looks by field index. }
00385 if (ColumnIndex > 0) and (ColumnIndex <= FFields.Count) then
00386 begin
00387 Current := TZFieldRef(FFields[ColumnIndex - 1]);
00388 if not Current.Linked
00389 and ((Current.Alias = Field) or (Current.Field = Field)) then
00390 begin
00391 Result := Current;
00392 Result.Linked := True;
00393 Exit;
00394 end;
00395 end;
00396
00397 { Looks a field by it's alias. }
00398 for I := 0 to FFields.Count - 1 do
00399 begin
00400 Current := TZFieldRef(FFields[I]);
00401 if not Current.Linked and (Current.Alias = Field) then
00402 begin
00403 Result := Current;
00404 Result.Linked := True;
00405 Exit;
00406 end;
00407 end;
00408
00409 { Looks a field by field and table aliases. }
00410 for I := 0 to FFields.Count - 1 do
00411 begin
00412 Current := TZFieldRef(FFields[I]);
00413 if not Current.Linked and Assigned(Current.TableRef)
00414 and (((Current.TableRef.Alias + '.' + Current.Field) = Field)
00415 or (((Current.TableRef.Table + '.' + Current.Field) = Field))) then
00416 begin
00417 Result := Current;
00418 Result.Linked := True;
00419 Exit;
00420 end;
00421 end;
00422
00423 { Looks a field by it's name. }
00424 for I := 0 to FFields.Count - 1 do
00425 begin
00426 Current := TZFieldRef(FFields[I]);
00427 if not Current.Linked and (Current.Field = Field) then
00428 begin
00429 Result := Current;
00430 Result.Linked := True;
00431 Exit;
00432 end;
00433 end;
00434 end;
00435
00436 {**
00437 Convert all table and field identifiers..
00438 @param Convertor an identifier convertor.
00439 }
00440 procedure TZSelectSchema.ConvertIdentifiers(Convertor: IZIdentifierConvertor);
00441 var
00442 I: Integer;
00443 begin
00444 if Convertor = nil then Exit;
00445
00446 for I := 0 to FFields.Count - 1 do
00447 begin
00448 with TZFieldRef(FFields[I]) do
00449 begin
00450 Catalog := Convertor.ExtractQuote(Catalog);
00451 Schema := Convertor.ExtractQuote(Schema);
00452 Table := Convertor.ExtractQuote(Table);
00453 Field := Convertor.ExtractQuote(Field);
00454 Alias := Convertor.ExtractQuote(Alias);
00455 end;
00456 end;
00457
00458 for I := 0 to FTables.Count - 1 do
00459 begin
00460 with TZTableRef(FTables[I]) do
00461 begin
00462 Catalog := Convertor.ExtractQuote(Catalog);
00463 Schema := Convertor.ExtractQuote(Schema);
00464 Table := Convertor.ExtractQuote(Table);
00465 Alias := Convertor.ExtractQuote(Alias);
00466 end;
00467 end;
00468 end;
00469
00470 {**
00471 Links references between fields and tables.
00472 @param Convertor an identifier convertor.
00473 }
00474 procedure TZSelectSchema.LinkReferences(Convertor: IZIdentifierConvertor);
00475 var
00476 I, J: Integer;
00477 FieldRef: TZFieldRef;
00478 TableRef: TZTableRef;
00479 TempFields: TObjectList;
00480 begin
00481 ConvertIdentifiers(Convertor);
00482 TempFields := FFields;
00483 FFields := TObjectList.Create;
00484
00485 try
00486 for I := 0 to TempFields.Count - 1 do
00487 begin
00488 FieldRef := TZFieldRef(TempFields[I]);
00489 TableRef := nil;
00490
00491 if not FieldRef.IsField then
00492 begin
00493 FFields.Add(TZFieldRef.Create(FieldRef.IsField, FieldRef.Catalog,
00494 FieldRef.Schema, FieldRef.Table, FieldRef.Field, FieldRef.Alias,
00495 FieldRef.TableRef));
00496 Continue;
00497 end
00498 else if (FieldRef.Schema <> '') and (FieldRef.Table <> '') then
00499 begin
00500 TableRef := FindTableByFullName(FieldRef.Catalog, FieldRef.Schema,
00501 FieldRef.Table);
00502 end
00503 else if FieldRef.Table <> '' then
00504 TableRef := FindTableByShortName(FieldRef.Table)
00505 else if FieldRef.Field = '*' then
00506 begin
00507 { Add all fields from all tables. }
00508 for J := 0 to FTables.Count - 1 do
00509 begin
00510 with TZTableRef(FTables[J]) do
00511 begin
00512 FFields.Add(TZFieldRef.Create(True, Catalog, Schema,
00513 Table, '*', '', TZTableRef(FTables[J])));
00514 end;
00515 end;
00516 Continue;
00517 end;
00518
00519 if TableRef <> nil then
00520 begin
00521 FFields.Add(TZFieldRef.Create(True, TableRef.Catalog, TableRef.Schema,
00522 TableRef.Table, FieldRef.Field, FieldRef.Alias, TableRef));
00523 end
00524 else
00525 begin
00526 FFields.Add(TZFieldRef.Create(True, FieldRef.Catalog, FieldRef.Schema,
00527 FieldRef.Table, FieldRef.Field, FieldRef.Alias, TableRef));
00528 end;
00529 end;
00530 finally
00531 TempFields.Free;
00532 end;
00533 end;
00534
00535 {**
00536 Adds a new field to this select schema.
00537 @param FieldRef a field reference object.
00538 }
00539 procedure TZSelectSchema.AddField(FieldRef: TZFieldRef);
00540 begin
00541 FFields.Add(FieldRef);
00542 end;
00543
00544 {**
00545 Inserts a new field to this select schema.
00546 @param Index an index where to insert a new field reference.
00547 @param FieldRef a field reference object.
00548 }
00549 procedure TZSelectSchema.InsertField(Index: Integer; FieldRef: TZFieldRef);
00550 begin
00551 FFields.Insert(Index, FieldRef);
00552 end;
00553
00554 {**
00555 Deletes a field from this select schema.
00556 @param FieldRef a field reference object.
00557 }
00558 procedure TZSelectSchema.DeleteField(FieldRef: TZFieldRef);
00559 begin
00560 FFields.Remove(FieldRef);
00561 end;
00562
00563 {**
00564 Adds a new table to this select schema.
00565 @param TableRef a table reference object.
00566 }
00567 procedure TZSelectSchema.AddTable(TableRef: TZTableRef);
00568 begin
00569 FTables.Add(TableRef);
00570 end;
00571
00572 {**
00573 Gets a field reference by index.
00574 @param Index an index of the reference.
00575 @returns a pointer to the field reference.
00576 }
00577 function TZSelectSchema.GetField(Index: Integer): TZFieldRef;
00578 begin
00579 Result := TZFieldRef(FFields[Index]);
00580 end;
00581
00582 {**
00583 Gets a count of field references.
00584 @returns a count of field references.
00585 }
00586 function TZSelectSchema.GetFieldCount: Integer;
00587 begin
00588 Result := FFields.Count;
00589 end;
00590
00591 {**
00592 Gets a table reference by index.
00593 @param Index an index of the reference.
00594 @returns a pointer to the table reference.
00595 }
00596 function TZSelectSchema.GetTable(Index: Integer): TZTableRef;
00597 begin
00598 Result := TZTableRef(FTables[Index]);
00599 end;
00600
00601 {**
00602 Gets a count of table references.
00603 @returns a count of table references.
00604 }
00605 function TZSelectSchema.GetTableCount: Integer;
00606 begin
00607 Result := FTables.Count;
00608 end;
00609
00610 end.
00611