00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { PostgreSQL Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { and Sergey Merkuriev }
00008 { }
00009 {*********************************************************}
00010
00011 {@********************************************************}
00012 { Copyright (c) 1999-2006 Zeos Development Group }
00013 { }
00014 { License Agreement: }
00015 { }
00016 { This library is distributed in the hope that it will be }
00017 { useful, but WITHOUT ANY WARRANTY; without even the }
00018 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00019 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00020 { Public License for more details. }
00021 { }
00022 { The source code of the ZEOS Libraries and packages are }
00023 { distributed under the Library GNU General Public }
00024 { License (see the file COPYING / COPYING.ZEOS) }
00025 { with the following modification: }
00026 { As a special exception, the copyright holders of this }
00027 { library give you permission to link this library with }
00028 { independent modules to produce an executable, }
00029 { regardless of the license terms of these independent }
00030 { modules, and to copy and distribute the resulting }
00031 { executable under terms of your choice, provided that }
00032 { you also meet, for each linked independent module, }
00033 { the terms and conditions of the license of that module. }
00034 { An independent module is a module which is not derived }
00035 { from or based on this library. If you modify this }
00036 { library, you may extend this exception to your version }
00037 { of the library, but you are not obligated to do so. }
00038 { If you do not wish to do so, delete this exception }
00039 { statement from your version. }
00040 { }
00041 { }
00042 { The project web site is located on: }
00043 { http:
00044 { http:
00045 { svn:
00046 { }
00047 { http:
00048 { http:
00049 { }
00050 { }
00051 { }
00052 { Zeos Development Group. }
00053 {********************************************************@}
00054
00055 unit ZDbcPostgreSqlMetadata;
00056
00057 interface
00058
00059 {$I ZDbc.inc}
00060
00061 uses
00062 {$IFNDEF VER130BELOW}
00063 Types,
00064 {$ENDIF}
00065 Classes, SysUtils, ZSysUtils, ZDbcIntfs, ZDbcMetadata,
00066 ZCompatibility, ZDbcPostgreSqlUtils, ZDbcConnection;
00067
00068 type
00069
00070 {** Implements PostgreSQL Database Metadata. }
00071 TZPostgreSQLDatabaseMetadata = class(TZAbstractDatabaseMetadata)
00072 private
00073 FDatabase: string;
00074 function EscapeString(const S: string): string;
00075 protected
00076 function HaveMinimumServerVersion(MajorVersion: Integer;
00077 MinorVersion: Integer): Boolean;
00078 function GetMaxIndexKeys: Integer;
00079 function GetMaxNameLength: Integer;
00080 function GetPostgreSQLType(Oid: Integer): string;
00081 function GetSQLTypeByOid(Oid: Integer): TZSQLType;
00082 function GetSQLTypeByName(TypeName: string): TZSQLType;
00083 function TableTypeSQLExpression(TableType: string; UseSchemas: Boolean):
00084 string;
00085 procedure ParseACLArray(List: TStrings; AclString: string);
00086 function GetPrivilegeName(Permission: char): string;
00087
00088 function UncachedGetTables(const Catalog: string; const SchemaPattern: string;
00089 const TableNamePattern: string; const Types: TStringDynArray): IZResultSet; override;
00090 function UncachedGetSchemas: IZResultSet; override;
00091 function UncachedGetCatalogs: IZResultSet; override;
00092 function UncachedGetTableTypes: IZResultSet; override;
00093 function UncachedGetColumns(const Catalog: string; const SchemaPattern: string;
00094 const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet; override;
00095 function UncachedGetTablePrivileges(const Catalog: string; const SchemaPattern: string;
00096 const TableNamePattern: string): IZResultSet; override;
00097 function UncachedGetColumnPrivileges(const Catalog: string; const Schema: string;
00098 const Table: string; const ColumnNamePattern: string): IZResultSet; override;
00099 function UncachedGetPrimaryKeys(const Catalog: string; const Schema: string;
00100 const Table: string): IZResultSet; override;
00101 function UncachedGetImportedKeys(const Catalog: string; const Schema: string;
00102 const Table: string): IZResultSet; override;
00103 function UncachedGetExportedKeys(const Catalog: string; const Schema: string;
00104 const Table: string): IZResultSet; override;
00105 function UncachedGetCrossReference(const PrimaryCatalog: string; const PrimarySchema: string;
00106 const PrimaryTable: string; const ForeignCatalog: string; const ForeignSchema: string;
00107 const ForeignTable: string): IZResultSet; override;
00108 function UncachedGetIndexInfo(const Catalog: string; const Schema: string; const Table: string;
00109 Unique: Boolean; Approximate: Boolean): IZResultSet; override;
00110 function UncachedGetSequences(const Catalog: string; const SchemaPattern: string;
00111 const SequenceNamePattern: string): IZResultSet; override;
00112 function UncachedGetProcedures(const Catalog: string; const SchemaPattern: string;
00113 const ProcedureNamePattern: string): IZResultSet; override;
00114 function UncachedGetProcedureColumns(const Catalog: string; const SchemaPattern: string;
00115 const ProcedureNamePattern: string; const ColumnNamePattern: string):
00116 IZResultSet; override;
00117 function UncachedGetVersionColumns(const Catalog: string; const Schema: string;
00118 const Table: string): IZResultSet; override;
00119 function UncachedGetTypeInfo: IZResultSet; override;
00120
00121
00122 public
00123 constructor Create(Connection: TZAbstractConnection; Url: string;
00124 Info: TStrings);
00125 destructor Destroy; override;
00126
00127 function GetDatabaseProductName: string; override;
00128 function GetDatabaseProductVersion: string; override;
00129 function GetDriverName: string; override;
00130 function GetDriverMajorVersion: Integer; override;
00131 function GetDriverMinorVersion: Integer; override;
00132 function UsesLocalFilePerTable: Boolean; override;
00133 function SupportsMixedCaseIdentifiers: Boolean; override;
00134 function StoresUpperCaseIdentifiers: Boolean; override;
00135 function StoresLowerCaseIdentifiers: Boolean; override;
00136 function StoresMixedCaseIdentifiers: Boolean; override;
00137 function SupportsMixedCaseQuotedIdentifiers: Boolean; override;
00138 function StoresUpperCaseQuotedIdentifiers: Boolean; override;
00139 function StoresLowerCaseQuotedIdentifiers: Boolean; override;
00140 function StoresMixedCaseQuotedIdentifiers: Boolean; override;
00141 function GetSQLKeywords: string; override;
00142 function GetNumericFunctions: string; override;
00143 function GetStringFunctions: string; override;
00144 function GetSystemFunctions: string; override;
00145 function GetTimeDateFunctions: string; override;
00146 function GetSearchStringEscape: string; override;
00147 function GetExtraNameCharacters: string; override;
00148
00149 function SupportsExpressionsInOrderBy: Boolean; override;
00150 function SupportsOrderByUnrelated: Boolean; override;
00151 function SupportsGroupBy: Boolean; override;
00152 function SupportsGroupByUnrelated: Boolean; override;
00153 function SupportsGroupByBeyondSelect: Boolean; override;
00154 function SupportsIntegrityEnhancementFacility: Boolean; override;
00155 function GetSchemaTerm: string; override;
00156 function GetProcedureTerm: string; override;
00157 function GetCatalogTerm: string; override;
00158 function GetCatalogSeparator: string; override;
00159 function SupportsSchemasInDataManipulation: Boolean; override;
00160 function SupportsSchemasInProcedureCalls: Boolean; override;
00161 function SupportsSchemasInTableDefinitions: Boolean; override;
00162 function SupportsSchemasInIndexDefinitions: Boolean; override;
00163 function SupportsSchemasInPrivilegeDefinitions: Boolean; override;
00164 function SupportsCatalogsInDataManipulation: Boolean; override;
00165 function SupportsCatalogsInProcedureCalls: Boolean; override;
00166 function SupportsCatalogsInTableDefinitions: Boolean; override;
00167 function SupportsCatalogsInIndexDefinitions: Boolean; override;
00168 function SupportsCatalogsInPrivilegeDefinitions: Boolean; override;
00169 function SupportsPositionedDelete: Boolean; override;
00170 function SupportsPositionedUpdate: Boolean; override;
00171 function SupportsSelectForUpdate: Boolean; override;
00172 function SupportsStoredProcedures: Boolean; override;
00173 function SupportsSubqueriesInComparisons: Boolean; override;
00174 function SupportsSubqueriesInExists: Boolean; override;
00175 function SupportsSubqueriesInIns: Boolean; override;
00176 function SupportsSubqueriesInQuantifieds: Boolean; override;
00177 function SupportsCorrelatedSubqueries: Boolean; override;
00178 function SupportsUnion: Boolean; override;
00179 function SupportsUnionAll: Boolean; override;
00180 function SupportsOpenCursorsAcrossCommit: Boolean; override;
00181 function SupportsOpenCursorsAcrossRollback: Boolean; override;
00182 function SupportsOpenStatementsAcrossCommit: Boolean; override;
00183 function SupportsOpenStatementsAcrossRollback: Boolean; override;
00184
00185 function GetMaxBinaryLiteralLength: Integer; override;
00186 function GetMaxCharLiteralLength: Integer; override;
00187 function GetMaxColumnNameLength: Integer; override;
00188 function GetMaxColumnsInGroupBy: Integer; override;
00189 function GetMaxColumnsInIndex: Integer; override;
00190 function GetMaxColumnsInOrderBy: Integer; override;
00191 function GetMaxColumnsInSelect: Integer; override;
00192 function GetMaxColumnsInTable: Integer; override;
00193 function GetMaxConnections: Integer; override;
00194 function GetMaxCursorNameLength: Integer; override;
00195 function GetMaxIndexLength: Integer; override;
00196 function GetMaxSchemaNameLength: Integer; override;
00197 function GetMaxProcedureNameLength: Integer; override;
00198 function GetMaxCatalogNameLength: Integer; override;
00199 function GetMaxRowSize: Integer; override;
00200 function DoesMaxRowSizeIncludeBlobs: Boolean; override;
00201 function GetMaxStatementLength: Integer; override;
00202 function GetMaxStatements: Integer; override;
00203 function GetMaxTableNameLength: Integer; override;
00204 function GetMaxTablesInSelect: Integer; override;
00205 function GetMaxUserNameLength: Integer; override;
00206
00207 function GetDefaultTransactionIsolation: TZTransactIsolationLevel; override;
00208 function SupportsTransactions: Boolean; override;
00209 function SupportsTransactionIsolationLevel(Level: TZTransactIsolationLevel):
00210 Boolean; override;
00211 function SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
00212 override;
00213 function SupportsDataManipulationTransactionsOnly: Boolean; override;
00214 function DataDefinitionCausesTransactionCommit: Boolean; override;
00215 function DataDefinitionIgnoredInTransactions: Boolean; override;
00216
00217 function SupportsResultSetType(_Type: TZResultSetType): Boolean; override;
00218 function SupportsResultSetConcurrency(_Type: TZResultSetType;
00219 Concurrency: TZResultSetConcurrency): Boolean; override;
00220 end;
00221
00222 implementation
00223
00224 uses
00225 ZMessages, ZDbcUtils, ZDbcPostgreSql;
00226
00227 { TZMySQLDatabaseMetadata }
00228
00229 {**
00230 @param S a string.
00231 @return escaped string
00232 }
00233 function TZPostgreSQLDatabaseMetadata.EscapeString(const S: string): string;
00234 var
00235 I: Integer;
00236 begin
00237 Result := S;
00238 for I := Length(Result) downto 1 do
00239 if (Result[I] = '''') or (Result[I] = '\') then
00240 Insert('\', Result, I);
00241 Result := '''' + Result + '''';
00242 if HaveMinimumServerVersion(8, 1) then
00243 Result := 'E' + Result;
00244 end;
00245
00246 {**
00247 Constructs this object and assignes the main properties.
00248 @param Connection a database connection object.
00249 @param Url a database connection url string.
00250 @param Info an extra connection properties.
00251 }
00252 constructor TZPostgreSQLDatabaseMetadata.Create(
00253 Connection: TZAbstractConnection; Url: string; Info: TStrings);
00254 var
00255 TempInfo: TStrings;
00256 Hostname, UserName, Password: string;
00257 Port: Integer;
00258 begin
00259 inherited Create(Connection, Url, Info);
00260
00261 TempInfo := TStringList.Create;
00262 try
00263 ResolveDatabaseUrl(Url, Info, HostName, Port, FDatabase,
00264 UserName, Password, TempInfo);
00265 finally
00266 TempInfo.Free;
00267 end;
00268 end;
00269
00270 {**
00271 Destroys this object and cleanups the memory.
00272 }
00273 destructor TZPostgreSQLDatabaseMetadata.Destroy;
00274 begin
00275 inherited Destroy;
00276 end;
00277
00278
00279
00280
00281 {**
00282 What's the name of this database product?
00283 @return database product name
00284 }
00285 function TZPostgreSQLDatabaseMetadata.GetDatabaseProductName: string;
00286 begin
00287 Result := 'PostgreSQL';
00288 end;
00289
00290 {**
00291 What's the version of this database product?
00292 @return database version
00293 }
00294 function TZPostgreSQLDatabaseMetadata.GetDatabaseProductVersion: string;
00295 begin
00296 Result := '';
00297 end;
00298
00299 {**
00300 What's the name of this JDBC driver?
00301 @return JDBC driver name
00302 }
00303 function TZPostgreSQLDatabaseMetadata.GetDriverName: string;
00304 begin
00305 Result := 'Zeos Database Connectivity Driver for PostgreSQL';
00306 end;
00307
00308 {**
00309 What's this JDBC driver's major version number?
00310 @return JDBC driver major version
00311 }
00312 function TZPostgreSQLDatabaseMetadata.GetDriverMajorVersion: Integer;
00313 begin
00314 Result := 1;
00315 end;
00316
00317 {**
00318 What's this JDBC driver's minor version number?
00319 @return JDBC driver minor version number
00320 }
00321 function TZPostgreSQLDatabaseMetadata.GetDriverMinorVersion: Integer;
00322 begin
00323 Result := 1;
00324 end;
00325
00326 {**
00327 Does the database use a file for each table?
00328 @return true if the database uses a local file for each table
00329 }
00330 function TZPostgreSQLDatabaseMetadata.UsesLocalFilePerTable: Boolean;
00331 begin
00332 Result := False;
00333 end;
00334
00335 {**
00336 Does the database treat mixed case unquoted SQL identifiers as
00337 case sensitive and as a result store them in mixed case?
00338 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return false.
00339 @return <code>true</code> if so; <code>false</code> otherwise
00340 }
00341 function TZPostgreSQLDatabaseMetadata.SupportsMixedCaseIdentifiers: Boolean;
00342 begin
00343 Result := False;
00344 end;
00345
00346 {**
00347 Does the database treat mixed case unquoted SQL identifiers as
00348 case insensitive and store them in upper case?
00349 @return <code>true</code> if so; <code>false</code> otherwise
00350 }
00351 function TZPostgreSQLDatabaseMetadata.StoresUpperCaseIdentifiers: Boolean;
00352 begin
00353 Result := False;
00354 end;
00355
00356 {**
00357 Does the database treat mixed case unquoted SQL identifiers as
00358 case insensitive and store them in lower case?
00359 @return <code>true</code> if so; <code>false</code> otherwise
00360 }
00361 function TZPostgreSQLDatabaseMetadata.StoresLowerCaseIdentifiers: Boolean;
00362 begin
00363 Result := True;
00364 end;
00365
00366 {**
00367 Does the database treat mixed case unquoted SQL identifiers as
00368 case insensitive and store them in mixed case?
00369 @return <code>true</code> if so; <code>false</code> otherwise
00370 }
00371 function TZPostgreSQLDatabaseMetadata.StoresMixedCaseIdentifiers: Boolean;
00372 begin
00373 Result := False;
00374 end;
00375
00376 {**
00377 Does the database treat mixed case quoted SQL identifiers as
00378 case sensitive and as a result store them in mixed case?
00379 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver will always return true.
00380 @return <code>true</code> if so; <code>false</code> otherwise
00381 }
00382 function TZPostgreSQLDatabaseMetadata.SupportsMixedCaseQuotedIdentifiers: Boolean;
00383 begin
00384 Result := True;
00385 end;
00386
00387 {**
00388 Does the database treat mixed case quoted SQL identifiers as
00389 case insensitive and store them in upper case?
00390 @return <code>true</code> if so; <code>false</code> otherwise
00391 }
00392 function TZPostgreSQLDatabaseMetadata.StoresUpperCaseQuotedIdentifiers: Boolean;
00393 begin
00394 Result := False;
00395 end;
00396
00397 {**
00398 Does the database treat mixed case quoted SQL identifiers as
00399 case insensitive and store them in lower case?
00400 @return <code>true</code> if so; <code>false</code> otherwise
00401 }
00402 function TZPostgreSQLDatabaseMetadata.StoresLowerCaseQuotedIdentifiers: Boolean;
00403 begin
00404 Result := False;
00405 end;
00406
00407 {**
00408 Does the database treat mixed case quoted SQL identifiers as
00409 case insensitive and store them in mixed case?
00410 @return <code>true</code> if so; <code>false</code> otherwise
00411 }
00412 function TZPostgreSQLDatabaseMetadata.StoresMixedCaseQuotedIdentifiers: Boolean;
00413 begin
00414 Result := True;
00415 end;
00416
00417 {**
00418 Gets a comma-separated list of all a database's SQL keywords
00419 that are NOT also SQL92 keywords.
00420 @return the list
00421 }
00422 function TZPostgreSQLDatabaseMetadata.GetSQLKeywords: string;
00423 begin
00424 Result := 'abort,absolute,access,action,add,admin,after,aggregate,all,also,'+
00425 'alter,always,analyse,analyze,and,any,array,asc,assertion,assignment,'+
00426 'asymmetric,at,authorization,'+
00427 'backward,before,begin,between,bigint,binary,bit,boolean,both,'+
00428 'cache,called,cascade,cascaded,case,cast,catalog,chain,character,'+
00429 'characteristics,check,checkpoint,class,close,cluster,coalesce,'+
00430 'collate,column,comment,commit,committed,concurrently,configuration,'+
00431 'connect,connection,constraint,constraints,content,continue,'+
00432 'conversion,convert,copy,cost,createdb,createrole,createuser,cross,'+
00433 'csv,current,current_catalog,current_date,current_role,current_schema,'+
00434 'current_time,current_timestamp,current_user,cursor,cycle,'+
00435 'data,database,day,deallocate,dec,decimal,declare,default,defaults,'+
00436 'deferrable,deferred,definer,delimiter,delimiters,desc,dictionary,'+
00437 'disable,discard,distinct,do,document,domain,double,'+
00438 'each,else,enable,encoding,encrypted,end,end-exec,enum,escape,except,'+
00439 'excluding,exclusive,exec,execute,exists,explain,external,extract,'+
00440 'false,family,fetch,first,float,following,for,force,foreign,forward,'+
00441 'freeze,full,function,'+
00442 'global,grant,granted,greatest,'+
00443 'handler,header,hold,hour,'+
00444 'identity,if,ilike,immediate,immutable,implicit,in,including,'+
00445 'increment,indexes,inherit,inherits,initially,inner,inout,input,'+
00446 'insensitive,instead,int,intersect,interval,invoker,isnull,isolation,'+
00447 'join,'+
00448 'lancompiler,language,large,last,lc_collate,lc_ctype,leading,least,'+
00449 'left,level,like,limit,listen,load,local,localtime,localtimestamp,'+
00450 'location,lock,login,'+
00451 'mapping,match,maxvalue,minute,minvalue,mode,month,move,'+
00452 'name,names,national,natural,nchar,new,next,no,nocreatedb,nocreaterole,'+
00453 'nocreateuser,noinherit,nologin,none,nosuperuser,not,nothing,notify,'+
00454 'notnull,nowait,nullif,nulls,numeric,'+
00455 'object,of,off,offset,oids,old,only,operator,option,options,or,out,'+
00456 'outer,over,overlaps,overlay,owned,owner,'+
00457 'parser,partial,partition,password,placing,plans,position,preceding,'+
00458 'precision,prepare,prepared,preserve,prior,privileges,procedural,'+
00459 'procedure,'+
00460 'quote,'+
00461 'range,read,real,reassign,recheck,recursive,references,reindex,'+
00462 'relative,release,rename,repeatable,replace,replica,reset,restart,'+
00463 'restrict,return,returning,returns,revoke,right,role,rollback,row,'+
00464 'rows,rule,'+
00465 'savepoint,schema,scroll,search,second,security,sequence,serializable,'+
00466 'server,session,session_user,setof,share,show,similar,simple,smallint,'+
00467 'some,stable,standalone,start,statement,statistics,stdin,stdout,'+
00468 'storage,strict,strip,substring,superuser,symmetric,sysid,system,'+
00469 'tablespace,temp,template,temporary,text,then,time,timestamp,to,'+
00470 'trailing,transaction,treat,trigger,trim,true,truncate,trusted,type,'+
00471 'unbounded,uncommitted,unencrypted,union,unique,unknown,unlisten,'+
00472 'until,user,using,'+
00473 'vacuum,valid,validator,value,variadic,varying,verbose,version,view,'+
00474 'volatile,'+
00475 'when,whitespace,window,with,without,work,wrapper,write,'+
00476 'xml,xmlattributes,xmlconcat,xmlelement,xmlforest,xmlparse,xmlpi,'+
00477 'xmlroot,xmlserialize,'+
00478 'year,yes,'+
00479 'zone';
00480 end;
00481
00482 {**
00483 Gets a comma-separated list of math functions. These are the
00484 X/Open CLI math function names used in the JDBC function escape
00485 clause.
00486 @return the list
00487 }
00488 function TZPostgreSQLDatabaseMetadata.GetNumericFunctions: string;
00489 begin
00490 Result := '';
00491 end;
00492
00493 {**
00494 Gets a comma-separated list of string functions. These are the
00495 X/Open CLI string function names used in the JDBC function escape
00496 clause.
00497 @return the list
00498 }
00499 function TZPostgreSQLDatabaseMetadata.GetStringFunctions: string;
00500 begin
00501 Result := '';
00502 end;
00503
00504 {**
00505 Gets a comma-separated list of system functions. These are the
00506 X/Open CLI system function names used in the JDBC function escape
00507 clause.
00508 @return the list
00509 }
00510 function TZPostgreSQLDatabaseMetadata.GetSystemFunctions: string;
00511 begin
00512 Result := '';
00513 end;
00514
00515 {**
00516 Gets a comma-separated list of time and date functions.
00517 @return the list
00518 }
00519 function TZPostgreSQLDatabaseMetadata.GetTimeDateFunctions: string;
00520 begin
00521 Result := '';
00522 end;
00523
00524 {**
00525 Gets the string that can be used to escape wildcard characters.
00526 This is the string that can be used to escape '_' or '%' in
00527 the string pattern style catalog search parameters.
00528
00529 <P>The '_' character represents any single character.
00530 <P>The '%' character represents any sequence of zero or
00531 more characters.
00532
00533 @return the string used to escape wildcard characters
00534 }
00535 function TZPostgreSQLDatabaseMetadata.GetSearchStringEscape: string;
00536 begin
00537 Result := '\';
00538 end;
00539
00540 {**
00541 Gets all the "extra" characters that can be used in unquoted
00542 identifier names (those beyond a-z, A-Z, 0-9 and _).
00543 @return the string containing the extra characters
00544 }
00545 function TZPostgreSQLDatabaseMetadata.GetExtraNameCharacters: string;
00546 begin
00547 Result := '';
00548 end;
00549
00550
00551
00552
00553 {**
00554 Are expressions in "ORDER BY" lists supported?
00555 @return <code>true</code> if so; <code>false</code> otherwise
00556 }
00557 function TZPostgreSQLDatabaseMetadata.SupportsExpressionsInOrderBy: Boolean;
00558 begin
00559 Result := True;
00560 end;
00561
00562 {**
00563 Can an "ORDER BY" clause use columns not in the SELECT statement?
00564 @return <code>true</code> if so; <code>false</code> otherwise
00565 }
00566 function TZPostgreSQLDatabaseMetadata.SupportsOrderByUnrelated: Boolean;
00567 begin
00568 Result := HaveMinimumServerVersion(6, 4);
00569 end;
00570
00571 {**
00572 Is some form of "GROUP BY" clause supported?
00573 @return <code>true</code> if so; <code>false</code> otherwise
00574 }
00575 function TZPostgreSQLDatabaseMetadata.SupportsGroupBy: Boolean;
00576 begin
00577 Result := True;
00578 end;
00579
00580 {**
00581 Can a "GROUP BY" clause use columns not in the SELECT?
00582 @return <code>true</code> if so; <code>false</code> otherwise
00583 }
00584 function TZPostgreSQLDatabaseMetadata.SupportsGroupByUnrelated: Boolean;
00585 begin
00586 Result := HaveMinimumServerVersion(6, 4);
00587 end;
00588
00589 {**
00590 Can a "GROUP BY" clause add columns not in the SELECT
00591 provided it specifies all the columns in the SELECT?
00592 @return <code>true</code> if so; <code>false</code> otherwise
00593 }
00594 function TZPostgreSQLDatabaseMetadata.SupportsGroupByBeyondSelect: Boolean;
00595 begin
00596 Result := HaveMinimumServerVersion(6, 4);
00597 end;
00598
00599 {**
00600 Is the SQL Integrity Enhancement Facility supported?
00601 @return <code>true</code> if so; <code>false</code> otherwise
00602 }
00603 function TZPostgreSQLDatabaseMetadata.SupportsIntegrityEnhancementFacility: Boolean;
00604 begin
00605 Result := False;
00606 end;
00607
00608 {**
00609 What's the database vendor's preferred term for "schema"?
00610 @return the vendor term
00611 }
00612 function TZPostgreSQLDatabaseMetadata.GetSchemaTerm: string;
00613 begin
00614 Result := 'schema';
00615 end;
00616
00617 {**
00618 What's the database vendor's preferred term for "procedure"?
00619 @return the vendor term
00620 }
00621 function TZPostgreSQLDatabaseMetadata.GetProcedureTerm: string;
00622 begin
00623 Result := 'function';
00624 end;
00625
00626 {**
00627 What's the database vendor's preferred term for "catalog"?
00628 @return the vendor term
00629 }
00630 function TZPostgreSQLDatabaseMetadata.GetCatalogTerm: string;
00631 begin
00632 Result := 'database';
00633 end;
00634
00635 {**
00636 What's the separator between catalog and table name?
00637 @return the separator string
00638 }
00639 function TZPostgreSQLDatabaseMetadata.GetCatalogSeparator: string;
00640 begin
00641 Result := '.';
00642 end;
00643
00644 {**
00645 Can a schema name be used in a data manipulation statement?
00646 @return <code>true</code> if so; <code>false</code> otherwise
00647 }
00648 function TZPostgreSQLDatabaseMetadata.SupportsSchemasInDataManipulation: Boolean;
00649 begin
00650 Result := HaveMinimumServerVersion(7, 3);
00651 end;
00652
00653 {**
00654 Can a schema name be used in a procedure call statement?
00655 @return <code>true</code> if so; <code>false</code> otherwise
00656 }
00657 function TZPostgreSQLDatabaseMetadata.SupportsSchemasInProcedureCalls: Boolean;
00658 begin
00659 Result := HaveMinimumServerVersion(7, 3);
00660 end;
00661
00662 {**
00663 Can a schema name be used in a table definition statement?
00664 @return <code>true</code> if so; <code>false</code> otherwise
00665 }
00666 function TZPostgreSQLDatabaseMetadata.SupportsSchemasInTableDefinitions: Boolean;
00667 begin
00668 Result := HaveMinimumServerVersion(7, 3);
00669 end;
00670
00671 {**
00672 Can a schema name be used in an index definition statement?
00673 @return <code>true</code> if so; <code>false</code> otherwise
00674 }
00675 function TZPostgreSQLDatabaseMetadata.SupportsSchemasInIndexDefinitions: Boolean;
00676 begin
00677 Result := HaveMinimumServerVersion(7, 3);
00678 end;
00679
00680 {**
00681 Can a schema name be used in a privilege definition statement?
00682 @return <code>true</code> if so; <code>false</code> otherwise
00683 }
00684 function TZPostgreSQLDatabaseMetadata.SupportsSchemasInPrivilegeDefinitions: Boolean;
00685 begin
00686 Result := HaveMinimumServerVersion(7, 3);
00687 end;
00688
00689 {**
00690 Can a catalog name be used in a data manipulation statement?
00691 @return <code>true</code> if so; <code>false</code> otherwise
00692 }
00693 function TZPostgreSQLDatabaseMetadata.SupportsCatalogsInDataManipulation: Boolean;
00694 begin
00695 Result := False;
00696 end;
00697
00698 {**
00699 Can a catalog name be used in a procedure call statement?
00700 @return <code>true</code> if so; <code>false</code> otherwise
00701 }
00702 function TZPostgreSQLDatabaseMetadata.SupportsCatalogsInProcedureCalls: Boolean;
00703 begin
00704 Result := False;
00705 end;
00706
00707 {**
00708 Can a catalog name be used in a table definition statement?
00709 @return <code>true</code> if so; <code>false</code> otherwise
00710 }
00711 function TZPostgreSQLDatabaseMetadata.SupportsCatalogsInTableDefinitions: Boolean;
00712 begin
00713 Result := False;
00714 end;
00715
00716 {**
00717 Can a catalog name be used in an index definition statement?
00718 @return <code>true</code> if so; <code>false</code> otherwise
00719 }
00720 function TZPostgreSQLDatabaseMetadata.SupportsCatalogsInIndexDefinitions: Boolean;
00721 begin
00722 Result := False;
00723 end;
00724
00725 {**
00726 Can a catalog name be used in a privilege definition statement?
00727 @return <code>true</code> if so; <code>false</code> otherwise
00728 }
00729 function TZPostgreSQLDatabaseMetadata.SupportsCatalogsInPrivilegeDefinitions: Boolean;
00730 begin
00731 Result := False;
00732 end;
00733
00734 {**
00735 Is positioned DELETE supported?
00736 @return <code>true</code> if so; <code>false</code> otherwise
00737 }
00738 function TZPostgreSQLDatabaseMetadata.SupportsPositionedDelete: Boolean;
00739 begin
00740 Result := False;
00741 end;
00742
00743 {**
00744 Is positioned UPDATE supported?
00745 @return <code>true</code> if so; <code>false</code> otherwise
00746 }
00747 function TZPostgreSQLDatabaseMetadata.SupportsPositionedUpdate: Boolean;
00748 begin
00749 Result := False;
00750 end;
00751
00752 {**
00753 Is SELECT for UPDATE supported?
00754 @return <code>true</code> if so; <code>false</code> otherwise
00755 }
00756 function TZPostgreSQLDatabaseMetadata.SupportsSelectForUpdate: Boolean;
00757 begin
00758 Result := HaveMinimumServerVersion(6, 5);
00759 end;
00760
00761 {**
00762 Are stored procedure calls using the stored procedure escape
00763 syntax supported?
00764 @return <code>true</code> if so; <code>false</code> otherwise
00765 }
00766 function TZPostgreSQLDatabaseMetadata.SupportsStoredProcedures: Boolean;
00767 begin
00768 Result := False;
00769 end;
00770
00771 {**
00772 Are subqueries in comparison expressions supported?
00773 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00774 @return <code>true</code> if so; <code>false</code> otherwise
00775 }
00776 function TZPostgreSQLDatabaseMetadata.SupportsSubqueriesInComparisons: Boolean;
00777 begin
00778 Result := True;
00779 end;
00780
00781 {**
00782 Are subqueries in 'exists' expressions supported?
00783 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00784 @return <code>true</code> if so; <code>false</code> otherwise
00785 }
00786 function TZPostgreSQLDatabaseMetadata.SupportsSubqueriesInExists: Boolean;
00787 begin
00788 Result := True;
00789 end;
00790
00791 {**
00792 Are subqueries in 'in' statements supported?
00793 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00794 @return <code>true</code> if so; <code>false</code> otherwise
00795 }
00796 function TZPostgreSQLDatabaseMetadata.SupportsSubqueriesInIns: Boolean;
00797 begin
00798 Result := True;
00799 end;
00800
00801 {**
00802 Are subqueries in quantified expressions supported?
00803 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00804 @return <code>true</code> if so; <code>false</code> otherwise
00805 }
00806 function TZPostgreSQLDatabaseMetadata.SupportsSubqueriesInQuantifieds: Boolean;
00807 begin
00808 Result := True;
00809 end;
00810
00811 {**
00812 Are correlated subqueries supported?
00813 A JDBC Compliant<sup><font size=-2>TM</font></sup> driver always returns true.
00814 @return <code>true</code> if so; <code>false</code> otherwise
00815 }
00816 function TZPostgreSQLDatabaseMetadata.SupportsCorrelatedSubqueries: Boolean;
00817 begin
00818 Result := HaveMinimumServerVersion(7, 1);
00819 end;
00820
00821 {**
00822 Is SQL UNION supported?
00823 @return <code>true</code> if so; <code>false</code> otherwise
00824 }
00825 function TZPostgreSQLDatabaseMetadata.SupportsUnion: Boolean;
00826 begin
00827 Result := True;
00828 end;
00829
00830 {**
00831 Is SQL UNION ALL supported?
00832 @return <code>true</code> if so; <code>false</code> otherwise
00833 }
00834 function TZPostgreSQLDatabaseMetadata.SupportsUnionAll: Boolean;
00835 begin
00836 Result := HaveMinimumServerVersion(7, 1);
00837 end;
00838
00839 {**
00840 Can cursors remain open across commits?
00841 @return <code>true</code> if cursors always remain open;
00842 <code>false</code> if they might not remain open
00843 }
00844 function TZPostgreSQLDatabaseMetadata.SupportsOpenCursorsAcrossCommit: Boolean;
00845 begin
00846 Result := False;
00847 end;
00848
00849 {**
00850 Can cursors remain open across rollbacks?
00851 @return <code>true</code> if cursors always remain open;
00852 <code>false</code> if they might not remain open
00853 }
00854 function TZPostgreSQLDatabaseMetadata.SupportsOpenCursorsAcrossRollback: Boolean;
00855 begin
00856 Result := False;
00857 end;
00858
00859 {**
00860 Can statements remain open across commits?
00861 @return <code>true</code> if statements always remain open;
00862 <code>false</code> if they might not remain open
00863 }
00864 function TZPostgreSQLDatabaseMetadata.SupportsOpenStatementsAcrossCommit: Boolean;
00865 begin
00866 Result := True;
00867 end;
00868
00869 {**
00870 Can statements remain open across rollbacks?
00871 @return <code>true</code> if statements always remain open;
00872 <code>false</code> if they might not remain open
00873 }
00874 function TZPostgreSQLDatabaseMetadata.SupportsOpenStatementsAcrossRollback: Boolean;
00875 begin
00876 Result := True;
00877 end;
00878
00879 //----------------------------------------------------------------------
00880 // The following group of methods exposes various limitations
00881 // based on the target database with the current driver.
00882 // Unless otherwise specified, a result of zero means there is no
00883 // limit, or the limit is not known.
00884
00885 {**
00886 How many hex characters can you have in an inline binary literal?
00887 @return max binary literal length in hex characters;
00888 a result of zero means that there is no limit or the limit is not known
00889 }
00890 function TZPostgreSQLDatabaseMetadata.GetMaxBinaryLiteralLength: Integer;
00891 begin
00892 Result := 0;
00893 end;
00894
00895 {**
00896 What's the max length for a character literal?
00897 @return max literal length;
00898 a result of zero means that there is no limit or the limit is not known
00899 }
00900 function TZPostgreSQLDatabaseMetadata.GetMaxCharLiteralLength: Integer;
00901 begin
00902 Result := 0;
00903 end;
00904
00905 {**
00906 What's the limit on column name length?
00907 @return max column name length;
00908 a result of zero means that there is no limit or the limit is not known
00909 }
00910 function TZPostgreSQLDatabaseMetadata.GetMaxColumnNameLength: Integer;
00911 begin
00912 Result := getMaxNameLength;
00913 end;
00914
00915 {**
00916 What's the maximum number of columns in a "GROUP BY" clause?
00917 @return max number of columns;
00918 a result of zero means that there is no limit or the limit is not known
00919 }
00920 function TZPostgreSQLDatabaseMetadata.GetMaxColumnsInGroupBy: Integer;
00921 begin
00922 Result := 0;
00923 end;
00924
00925 {**
00926 What's the maximum number of columns allowed in an index?
00927 @return max number of columns;
00928 a result of zero means that there is no limit or the limit is not known
00929 }
00930 function TZPostgreSQLDatabaseMetadata.GetMaxColumnsInIndex: Integer;
00931 begin
00932 Result := GetMaxIndexKeys;
00933 end;
00934
00935 {**
00936 What's the maximum number of columns in an "ORDER BY" clause?
00937 @return max number of columns;
00938 a result of zero means that there is no limit or the limit is not known
00939 }
00940 function TZPostgreSQLDatabaseMetadata.GetMaxColumnsInOrderBy: Integer;
00941 begin
00942 Result := 0;
00943 end;
00944
00945 {**
00946 What's the maximum number of columns in a "SELECT" list?
00947 @return max number of columns;
00948 a result of zero means that there is no limit or the limit is not known
00949 }
00950 function TZPostgreSQLDatabaseMetadata.GetMaxColumnsInSelect: Integer;
00951 begin
00952 Result := 0;
00953 end;
00954
00955 {**
00956 What's the maximum number of columns in a table?
00957 @return max number of columns;
00958 a result of zero means that there is no limit or the limit is not known
00959 }
00960 function TZPostgreSQLDatabaseMetadata.GetMaxColumnsInTable: Integer;
00961 begin
00962 Result := 1600;
00963 end;
00964
00965 {**
00966 How many active connections can we have at a time to this database?
00967 @return max number of active connections;
00968 a result of zero means that there is no limit or the limit is not known
00969 }
00970 function TZPostgreSQLDatabaseMetadata.GetMaxConnections: Integer;
00971 begin
00972 Result := 8192;
00973 end;
00974
00975 {**
00976 What's the maximum cursor name length?
00977 @return max cursor name length in bytes;
00978 a result of zero means that there is no limit or the limit is not known
00979 }
00980 function TZPostgreSQLDatabaseMetadata.GetMaxCursorNameLength: Integer;
00981 begin
00982 Result := GetMaxNameLength;
00983 end;
00984
00985 {**
00986 Retrieves the maximum number of bytes for an index, including all
00987 of the parts of the index.
00988 @return max index length in bytes, which includes the composite of all
00989 the constituent parts of the index;
00990 a result of zero means that there is no limit or the limit is not known
00991 }
00992 function TZPostgreSQLDatabaseMetadata.GetMaxIndexLength: Integer;
00993 begin
00994 Result := 0;
00995 end;
00996
00997 {**
00998 What's the maximum length allowed for a schema name?
00999 @return max name length in bytes;
01000 a result of zero means that there is no limit or the limit is not known
01001 }
01002 function TZPostgreSQLDatabaseMetadata.GetMaxSchemaNameLength: Integer;
01003 begin
01004 Result := GetMaxNameLength;
01005 end;
01006
01007 {**
01008 What's the maximum length of a procedure name?
01009 @return max name length in bytes;
01010 a result of zero means that there is no limit or the limit is not known
01011 }
01012 function TZPostgreSQLDatabaseMetadata.GetMaxProcedureNameLength: Integer;
01013 begin
01014 Result := GetMaxNameLength;
01015 end;
01016
01017 {**
01018 What's the maximum length of a catalog name?
01019 @return max name length in bytes;
01020 a result of zero means that there is no limit or the limit is not known
01021 }
01022 function TZPostgreSQLDatabaseMetadata.GetMaxCatalogNameLength: Integer;
01023 begin
01024 Result := GetMaxNameLength;
01025 end;
01026
01027 {**
01028 What's the maximum length of a single row?
01029 @return max row size in bytes;
01030 a result of zero means that there is no limit or the limit is not known
01031 }
01032 function TZPostgreSQLDatabaseMetadata.GetMaxRowSize: Integer;
01033 begin
01034 if HaveMinimumServerVersion(7, 1) then
01035 Result := 1073741824
01036 else Result := 8192;
01037 end;
01038
01039 {**
01040 Did getMaxRowSize() include LONGVARCHAR and LONGVARBINARY
01041 blobs?
01042 @return <code>true</code> if so; <code>false</code> otherwise
01043 }
01044 function TZPostgreSQLDatabaseMetadata.DoesMaxRowSizeIncludeBlobs: Boolean;
01045 begin
01046 Result := True;
01047 end;
01048
01049 {**
01050 What's the maximum length of an SQL statement?
01051 @return max length in bytes;
01052 a result of zero means that there is no limit or the limit is not known
01053 }
01054 function TZPostgreSQLDatabaseMetadata.GetMaxStatementLength: Integer;
01055 begin
01056 if HaveMinimumServerVersion(7, 0) then
01057 Result := 0
01058 else Result := 16348
01059 end;
01060
01061 {**
01062 How many active statements can we have open at one time to this
01063 database?
01064 @return the maximum number of statements that can be open at one time;
01065 a result of zero means that there is no limit or the limit is not known
01066 }
01067 function TZPostgreSQLDatabaseMetadata.GetMaxStatements: Integer;
01068 begin
01069 Result := 1;
01070 end;
01071
01072 {**
01073 What's the maximum length of a table name?
01074 @return max name length in bytes;
01075 a result of zero means that there is no limit or the limit is not known
01076 }
01077 function TZPostgreSQLDatabaseMetadata.GetMaxTableNameLength: Integer;
01078 begin
01079 Result := GetMaxNameLength;
01080 end;
01081
01082 {**
01083 What's the maximum number of tables in a SELECT statement?
01084 @return the maximum number of tables allowed in a SELECT statement;
01085 a result of zero means that there is no limit or the limit is not known
01086 }
01087 function TZPostgreSQLDatabaseMetadata.GetMaxTablesInSelect: Integer;
01088 begin
01089 Result := 0;
01090 end;
01091
01092 {**
01093 What's the maximum length of a user name?
01094 @return max user name length in bytes;
01095 a result of zero means that there is no limit or the limit is not known
01096 }
01097 function TZPostgreSQLDatabaseMetadata.GetMaxUserNameLength: Integer;
01098 begin
01099 Result := GetMaxNameLength;
01100 end;
01101
01102 //----------------------------------------------------------------------
01103
01104 {**
01105 What's the database's default transaction isolation level? The
01106 values are defined in <code>java.sql.Connection</code>.
01107 @return the default isolation level
01108 @see Connection
01109 }
01110 function TZPostgreSQLDatabaseMetadata.GetDefaultTransactionIsolation:
01111 TZTransactIsolationLevel;
01112 begin
01113 Result := tiReadCommitted;
01114 end;
01115
01116 {**
01117 Are transactions supported? If not, invoking the method
01118 <code>commit</code> is a noop and the isolation level is TRANSACTION_NONE.
01119 @return <code>true</code> if transactions are supported; <code>false</code> otherwise
01120 }
01121 function TZPostgreSQLDatabaseMetadata.SupportsTransactions: Boolean;
01122 begin
01123 Result := True;
01124 end;
01125
01126 {**
01127 Does this database support the given transaction isolation level?
01128 @param level the values are defined in <code>java.sql.Connection</code>
01129 @return <code>true</code> if so; <code>false</code> otherwise
01130 @see Connection
01131 }
01132 function TZPostgreSQLDatabaseMetadata.SupportsTransactionIsolationLevel(
01133 Level: TZTransactIsolationLevel): Boolean;
01134 begin
01135 Result := (Level = tiSerializable) or (Level = tiReadCommitted);
01136 end;
01137
01138 {**
01139 Are both data definition and data manipulation statements
01140 within a transaction supported?
01141 @return <code>true</code> if so; <code>false</code> otherwise
01142 }
01143 function TZPostgreSQLDatabaseMetadata.
01144 SupportsDataDefinitionAndDataManipulationTransactions: Boolean;
01145 begin
01146 Result := True;
01147 end;
01148
01149 {**
01150 Are only data manipulation statements within a transaction
01151 supported?
01152 @return <code>true</code> if so; <code>false</code> otherwise
01153 }
01154 function TZPostgreSQLDatabaseMetadata.
01155 SupportsDataManipulationTransactionsOnly: Boolean;
01156 begin
01157 Result := False;
01158 end;
01159
01160 {**
01161 Does a data definition statement within a transaction force the
01162 transaction to commit?
01163 @return <code>true</code> if so; <code>false</code> otherwise
01164 }
01165 function TZPostgreSQLDatabaseMetadata.DataDefinitionCausesTransactionCommit: Boolean;
01166 begin
01167 Result := False;
01168 end;
01169
01170 {**
01171 Is a data definition statement within a transaction ignored?
01172 @return <code>true</code> if so; <code>false</code> otherwise
01173 }
01174 function TZPostgreSQLDatabaseMetadata.DataDefinitionIgnoredInTransactions: Boolean;
01175 begin
01176 Result := False;
01177 end;
01178
01179 {**
01180 Gets a description of the stored procedures available in a
01181 catalog.
01182
01183 <P>Only procedure descriptions matching the schema and
01184 procedure name criteria are returned. They are ordered by
01185 PROCEDURE_SCHEM, and PROCEDURE_NAME.
01186
01187 <P>Each procedure description has the the following columns:
01188 <OL>
01189 <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
01190 <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
01191 <LI><B>PROCEDURE_NAME</B> String => procedure name
01192 <LI> reserved for future use
01193 <LI> reserved for future use
01194 <LI> reserved for future use
01195 <LI><B>REMARKS</B> String => explanatory comment on the procedure
01196 <LI><B>PROCEDURE_TYPE</B> short => kind of procedure:
01197 <UL>
01198 <LI> procedureResultUnknown - May return a result
01199 <LI> procedureNoResult - Does not return a result
01200 <LI> procedureReturnsResult - Returns a result
01201 </UL>
01202 </OL>
01203
01204 @param catalog a catalog name; "" retrieves those without a
01205 catalog; null means drop catalog name from the selection criteria
01206 @param schemaPattern a schema name pattern; "" retrieves those
01207 without a schema
01208 @param procedureNamePattern a procedure name pattern
01209 @return <code>ResultSet</code> - each row is a procedure description
01210 @see #getSearchStringEscape
01211 }
01212 function TZPostgreSQLDatabaseMetadata.UncachedGetProcedures(const Catalog: string;
01213 const SchemaPattern: string; const ProcedureNamePattern: string): IZResultSet;
01214 var
01215 SQL, LProcedureNamePattern: string;
01216 begin
01217 if ProcedureNamePattern = '' then
01218 LProcedureNamePattern := '%'
01219 else
01220 LProcedureNamePattern := ProcedureNamePattern;
01221
01222 if HaveMinimumServerVersion(7, 3) then
01223 begin
01224 SQL := 'SELECT NULL AS PROCEDURE_CAT, n.nspname AS PROCEDURE_SCHEM,'
01225 + ' p.proname AS PROCEDURE_NAME, NULL AS RESERVED1, NULL AS RESERVED2,'
01226 + ' NULL AS RESERVED3, d.description AS REMARKS, '
01227 + IntToStr(ProcedureReturnsResult) + ' AS PROCEDURE_TYPE '
01228 + ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p '
01229 + ' LEFT JOIN pg_catalog.pg_description d ON (p.oid=d.objoid) '
01230 + ' LEFT JOIN pg_catalog.pg_class c ON (d.classoid=c.oid AND'
01231 + ' c.relname=''pg_proc'') LEFT JOIN pg_catalog.pg_namespace pn ON'
01232 + ' (c.relnamespace=pn.oid AND pn.nspname=''pg_catalog'') '
01233 + ' WHERE p.pronamespace=n.oid';
01234 if SchemaPattern <> '' then
01235 SQL := SQL + ' AND n.nspname LIKE ' + EscapeString(SchemaPattern);
01236 SQL := SQL + ' AND p.proname LIKE ' + EscapeString(LProcedureNamePattern)
01237 + ' ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME';
01238 end
01239 else
01240 begin
01241 SQL := 'SELECT NULL AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM,'
01242 + ' p.proname AS PROCEDURE_NAME, NULL AS RESERVED1, NULL AS RESERVED2,'
01243 + ' NULL AS RESERVED3, NULL AS REMARKS, '
01244 + IntToStr(ProcedureReturnsResult) + ' AS PROCEDURE_TYPE'
01245 + ' FROM pg_proc p WHERE p.proname LIKE '
01246 + EscapeString(LProcedureNamePattern)
01247 + ' ORDER BY PROCEDURE_NAME';
01248 end;
01249
01250 Result := CopyToVirtualResultSet(
01251 GetConnection.CreateStatement.ExecuteQuery(SQL),
01252 ConstructVirtualResultSet(ProceduresColumnsDynArray));
01253 end;
01254
01255 {**
01256 Gets a description of a catalog's stored procedure parameters
01257 and result columns.
01258
01259 <P>Only descriptions matching the schema, procedure and
01260 parameter name criteria are returned. They are ordered by
01261 PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value,
01262 if any, is first. Next are the parameter descriptions in call
01263 order. The column descriptions follow in column number order.
01264
01265 <P>Each row in the <code>ResultSet</code> is a parameter description or
01266 column description with the following fields:
01267 <OL>
01268 <LI><B>PROCEDURE_CAT</B> String => procedure catalog (may be null)
01269 <LI><B>PROCEDURE_SCHEM</B> String => procedure schema (may be null)
01270 <LI><B>PROCEDURE_NAME</B> String => procedure name
01271 <LI><B>COLUMN_NAME</B> String => column/parameter name
01272 <LI><B>COLUMN_TYPE</B> Short => kind of column/parameter:
01273 <UL>
01274 <LI> procedureColumnUnknown - nobody knows
01275 <LI> procedureColumnIn - IN parameter
01276 <LI> procedureColumnInOut - INOUT parameter
01277 <LI> procedureColumnOut - OUT parameter
01278 <LI> procedureColumnReturn - procedure return value
01279 <LI> procedureColumnResult - result column in <code>ResultSet</code>
01280 </UL>
01281 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01282 <LI><B>TYPE_NAME</B> String => SQL type name, for a UDT type the
01283 type name is fully qualified
01284 <LI><B>PRECISION</B> int => precision
01285 <LI><B>LENGTH</B> int => length in bytes of data
01286 <LI><B>SCALE</B> short => scale
01287 <LI><B>RADIX</B> short => radix
01288 <LI><B>NULLABLE</B> short => can it contain NULL?
01289 <UL>
01290 <LI> procedureNoNulls - does not allow NULL values
01291 <LI> procedureNullable - allows NULL values
01292 <LI> procedureNullableUnknown - nullability unknown
01293 </UL>
01294 <LI><B>REMARKS</B> String => comment describing parameter/column
01295 </OL>
01296
01297 <P><B>Note:</B> Some databases may not return the column
01298 descriptions for a procedure. Additional columns beyond
01299 REMARKS can be defined by the database.
01300
01301 @param catalog a catalog name; "" retrieves those without a
01302 catalog; null means drop catalog name from the selection criteria
01303 @param schemaPattern a schema name pattern; "" retrieves those
01304 without a schema
01305 @param procedureNamePattern a procedure name pattern
01306 @param columnNamePattern a column name pattern
01307 @return <code>ResultSet</code> - each row describes a stored procedure parameter or
01308 column
01309 @see #getSearchStringEscape
01310 }
01311 function TZPostgreSQLDatabaseMetadata.UncachedGetProcedureColumns(const Catalog: string;
01312 const SchemaPattern: string; const ProcedureNamePattern: string;
01313 const ColumnNamePattern: string): IZResultSet;
01314 var
01315 I, ReturnType, ColumnTypeOid, ArgOid: Integer;
01316 SQL, ReturnTypeType: string;
01317 ArgTypes: TStrings;
01318 ResultSet,
01319 ColumnsRS: IZResultSet;
01320 begin
01321 Result := ConstructVirtualResultSet(ProceduresColColumnsDynArray);
01322
01323 if HaveMinimumServerVersion(7, 3) then
01324 begin
01325 SQL := 'SELECT n.nspname,p.proname,p.prorettype,p.proargtypes,t.typtype,'
01326 + 't.typrelid FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n,'
01327 + ' pg_catalog.pg_type t WHERE p.pronamespace=n.oid AND p.prorettype=t.oid';
01328 if SchemaPattern <> '' then
01329 SQL := SQL + ' AND n.nspname LIKE ' + EscapeString(SchemaPattern);
01330 SQL := SQL + ' AND p.proname LIKE ' + EscapeString(ToLikeString(ProcedureNamePattern))
01331 + ' ORDER BY n.nspname, p.proname';
01332 end
01333 else
01334 SQL := 'SELECT NULL AS nspname,p.proname,p.prorettype,p.proargtypes,'
01335 + 't.typtype,t.typrelid FROM pg_proc p, pg_type t'
01336 + ' WHERE p.prorettype=t.oid'
01337 + ' AND p.proname LIKE '
01338 + EscapeString(ToLikeString(ProcedureNamePattern))
01339 + ' ORDER BY p.proname';
01340
01341 ArgTypes := TStringList.Create;
01342 try
01343 ResultSet:=GetConnection.CreateStatement.ExecuteQuery(SQL); //FirmOS Patch
01344 with ResultSet do
01345 begin
01346 while Next do
01347 begin
01348 ReturnType := StrToInt(GetStringByName('prorettype'));
01349 ReturnTypeType := GetStringByName('typtype');
01350 PutSplitString(ArgTypes, GetStringByName('proargtypes'), #10#13#9' ');
01351
01352 if ReturnTypeType <> 'c' then
01353 begin
01354 Result.MoveToInsertRow;
01355 Result.UpdateNull(1);
01356 Result.UpdateString(2, GetStringByName('nspname'));
01357 Result.UpdateString(3, GetStringByName('proname'));
01358 Result.UpdateString(4, 'returnValue');
01359 Result.UpdateInt(5, Ord(pctReturn));
01360 Result.UpdateInt(6, Ord(GetSQLTypeByOid(ReturnType)));
01361 Result.UpdateString(7, GetPostgreSQLType(ReturnType));
01362 Result.UpdateNull(8);
01363 Result.UpdateNull(9);
01364 Result.UpdateNull(10);
01365 Result.UpdateNull(11);
01366 Result.UpdateInt(12, Ord(ntNullableUnknown));
01367 Result.UpdateNull(13);
01368 Result.InsertRow;
01369 end;
01370
01371 for I := 0 to ArgTypes.Count-1 do
01372 begin
01373 ArgOid := StrToInt(ArgTypes.Strings[i]);
01374 Result.MoveToInsertRow;
01375 Result.UpdateNull(1);
01376 Result.UpdateString(2, GetStringByName('nspname'));
01377 Result.UpdateString(3, GetStringByName('proname'));
01378 Result.UpdateString(4, '$' + IntToStr(I));
01379 Result.UpdateInt(5, Ord(pctIn));
01380 Result.UpdateInt(6, Ord(GetSQLTypeByOid(ArgOid)));
01381 Result.UpdateString(7, GetPostgreSQLType(ArgOid));
01382 Result.UpdateNull(8);
01383 Result.UpdateNull(9);
01384 Result.UpdateNull(10);
01385 Result.UpdateNull(11);
01386 Result.UpdateInt(12, Ord(ntNullableUnknown));
01387 Result.UpdateNull(13);
01388 Result.InsertRow;
01389 end;
01390
01391 if ReturnTypeType = 'c' then
01392 begin
01393 ColumnsRS := GetConnection.CreateStatement.ExecuteQuery(
01394 Format('SELECT a.attname,a.atttypid'
01395 + ' FROM pg_catalog.pg_attribute a WHERE a.attrelid=%s'
01396 + ' ORDER BY a.attnum',
01397 [ResultSet.GetStringByName('typrelid')]));
01398 while ColumnsRS.Next do
01399 begin
01400 ColumnTypeOid := ColumnsRS.GetIntByName('atttypid');
01401 Result.MoveToInsertRow;
01402 Result.UpdateNull(1);
01403 Result.UpdateString(2, GetStringByName('nspname'));
01404 Result.UpdateString(3, GetStringByName('proname'));
01405 Result.UpdateString(4, ColumnsRS.GetStringByName('attname'));
01406 Result.UpdateInt(5, Ord(pctResultSet));
01407 Result.UpdateInt(6, Ord(GetSQLTypeByOid(ColumnTypeOid)));
01408 Result.UpdateString(7, GetPostgreSQLType(ColumnTypeOid));
01409 Result.UpdateNull(8);
01410 Result.UpdateNull(9);
01411 Result.UpdateNull(10);
01412 Result.UpdateNull(11);
01413 Result.UpdateInt(12, Ord(ntNullableUnknown));
01414 Result.UpdateNull(13);
01415 Result.InsertRow;
01416 end;
01417 ColumnsRS.Close;
01418 end;
01419 end;
01420 Close;
01421 end;
01422 finally
01423 ArgTypes.Free;
01424 end;
01425 end;
01426
01427 {**
01428 Gets a description of tables available in a catalog.
01429
01430 <P>Only table descriptions matching the catalog, schema, table
01431 name and type criteria are returned. They are ordered by
01432 TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
01433
01434 <P>Each table description has the following columns:
01435 <OL>
01436 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01437 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01438 <LI><B>TABLE_NAME</B> String => table name
01439 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01440 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01441 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01442 <LI><B>REMARKS</B> String => explanatory comment on the table
01443 </OL>
01444
01445 <P><B>Note:</B> Some databases may not return information for
01446 all tables.
01447
01448 @param catalog a catalog name; "" retrieves those without a
01449 catalog; null means drop catalog name from the selection criteria
01450 @param schemaPattern a schema name pattern; "" retrieves those
01451 without a schema
01452 @param tableNamePattern a table name pattern
01453 @param types a list of table types to include; null returns all types
01454 @return <code>ResultSet</code> - each row is a table description
01455 @see #getSearchStringEscape
01456 }
01457 function TZPostgreSQLDatabaseMetadata.UncachedGetTables(const Catalog: string;
01458 const SchemaPattern: string; const TableNamePattern: string;
01459 const Types: TStringDynArray): IZResultSet;
01460 var
01461 I: Integer;
01462 TableType, OrderBy, SQL: string;
01463 UseSchemas: Boolean;
01464 LTypes: TStringDynArray;
01465 begin
01466 UseSchemas := True;
01467
01468 if HaveMinimumServerVersion(7, 3) then
01469 begin
01470 SQL := ' SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,'
01471 + ' c.relname AS TABLE_NAME, '
01472 + ' CASE (n.nspname LIKE ''pg\\_%'')'
01473 + ' OR (n.nspname=''information_schema'')'
01474 + ' WHEN true THEN CASE n.nspname '
01475 + ' WHEN ''pg_catalog'' THEN CASE c.relkind '
01476 + ' WHEN ''r'' THEN ''SYSTEM TABLE'''
01477 + ' WHEN ''v'' THEN ''SYSTEM VIEW'' '
01478 + ' WHEN ''i'' THEN ''SYSTEM INDEX'' '
01479 + ' ELSE NULL '
01480 + ' END '
01481 + ' WHEN ''information_schema'' THEN CASE c.relkind '
01482 + ' WHEN ''r'' THEN ''SYSTEM TABLE'''
01483 + ' WHEN ''v'' THEN ''SYSTEM VIEW'' '
01484 + ' WHEN ''i'' THEN ''SYSTEM INDEX'' '
01485 + ' ELSE NULL '
01486 + ' END '
01487 + ' WHEN ''pg_toast'' THEN CASE c.relkind '
01488 + ' WHEN ''r'' THEN ''SYSTEM TOAST TABLE'' '
01489 + ' WHEN ''i'' THEN ''SYSTEM TOAST INDEX'' '
01490 + ' ELSE NULL '
01491 + ' END '
01492 + ' ELSE CASE c.relkind '
01493 + ' WHEN ''r'' THEN ''TEMPORARY TABLE'' '
01494 + ' WHEN ''i'' THEN ''TEMPORARY INDEX'' '
01495 + ' ELSE NULL '
01496 + ' END '
01497 + ' END '
01498 + ' WHEN false THEN CASE c.relkind '
01499 + ' WHEN ''r'' THEN ''TABLE'' '
01500 + ' WHEN ''i'' THEN ''INDEX'' '
01501 + ' WHEN ''S'' THEN ''SEQUENCE'' '
01502 + ' WHEN ''v'' THEN ''VIEW'' '
01503 + ' ELSE NULL '
01504 + ' END '
01505 + ' ELSE NULL '
01506 + ' END '
01507 + ' AS TABLE_TYPE, d.description AS REMARKS '
01508 + ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c '
01509 + ' LEFT JOIN pg_catalog.pg_description d'
01510 + ' ON (c.oid = d.objoid AND d.objsubid = 0) '
01511 + ' LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid'
01512 + ' AND dc.relname=''pg_class'') LEFT JOIN pg_catalog.pg_namespace dn'
01513 + ' ON (dn.oid=dc.relnamespace AND dn.nspname=''pg_catalog'') '
01514 + ' WHERE c.relnamespace = n.oid ';
01515
01516 begin
01517 SQL := SQL + ' AND n.nspname LIKE '
01518 + EscapeString(ToLikeString(SchemaPattern));
01519 end;
01520 OrderBy := ' ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME';
01521 end
01522 else
01523 begin
01524 UseSchemas := False;
01525 TableType := ' CASE c.relname LIKE ''pg\\_%'' '
01526 + 'WHEN true THEN CASE c.relname LIKE ''pg\\_toast\\_%'' '
01527 + 'WHEN true THEN CASE c.relkind '
01528 + ' WHEN ''r'' THEN ''SYSTEM TOAST TABLE'' '
01529 + ' WHEN ''i'' THEN ''SYSTEM TOAST INDEX'' '
01530 + ' ELSE NULL '
01531 + 'END '
01532 + 'WHEN false THEN CASE c.relname LIKE ''pg\\_temp\\_%'' '
01533 + ' WHEN true THEN CASE c.relkind '
01534 + ' WHEN ''r'' THEN ''TEMPORARY TABLE'' '
01535 + ' WHEN ''i'' THEN ''TEMPORARY INDEX'' '
01536 + ' ELSE NULL '
01537 + ' END '
01538 + ' WHEN false THEN CASE c.relkind '
01539 + ' WHEN ''r'' THEN ''SYSTEM TABLE'' '
01540 + ' WHEN ''v'' THEN ''SYSTEM VIEW'' '
01541 + ' WHEN ''i'' THEN ''SYSTEM INDEX'' '
01542 + ' ELSE NULL '
01543 + ' END '
01544 + ' ELSE NULL '
01545 + 'END '
01546 + 'ELSE NULL '
01547 + 'END '
01548 + 'WHEN false THEN CASE c.relkind '
01549 + ' WHEN ''r'' THEN ''TABLE'' '
01550 + ' WHEN ''i'' THEN ''INDEX'' '
01551 + ' WHEN ''S'' THEN ''SEQUENCE'' '
01552 + ' WHEN ''v'' THEN ''VIEW'' '
01553 + ' ELSE NULL '
01554 + 'END '
01555 + 'ELSE NULL '
01556 + ' END ';
01557 OrderBy := ' ORDER BY TABLE_TYPE,TABLE_NAME ';
01558 SQL := 'SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM,'
01559 + ' c.relname AS TABLE_NAME, ' + TableType + ' AS TABLE_TYPE,'
01560 + ' NULL AS REMARKS FROM pg_class c WHERE true ';
01561 end;
01562
01563 if (Types = nil) or (Length(Types) = 0) then
01564 begin
01565 SetLength(LTypes, 5);
01566 LTypes[0] := 'TABLE';
01567 LTypes[1] := 'VIEW';
01568 LTypes[2] := 'INDEX';
01569 LTypes[3] := 'SEQUENCE';
01570 LTypes[4] := 'TEMPORARY TABLE';
01571 end
01572 else
01573 LTypes := Types;
01574
01575 SQL := SQL + ' AND c.relname LIKE ' + EscapeString(ToLikeString(TableNamePattern))
01576 + ' AND (false';
01577 for I := 0 to High(LTypes) do
01578 SQL := SQL + ' OR (' + TableTypeSQLExpression(LTypes[i], UseSchemas) + ')';
01579 SQL := SQL + ')' + OrderBy;
01580
01581 Result := CopyToVirtualResultSet(
01582 GetConnection.CreateStatement.ExecuteQuery(SQL),
01583 ConstructVirtualResultSet(TableColumnsDynArray));
01584 end;
01585
01586 {**
01587 Gets the schema names available in this database. The results
01588 are ordered by schema name.
01589
01590 <P>The schema column is:
01591 <OL>
01592 <LI><B>TABLE_SCHEM</B> String => schema name
01593 </OL>
01594
01595 @return <code>ResultSet</code> - each row has a single String column that is a
01596 schema name
01597 }
01598 function TZPostgreSQLDatabaseMetadata.UncachedGetSchemas: IZResultSet;
01599 var
01600 SQL: string;
01601 begin
01602 if HaveMinimumServerVersion(7, 3) then
01603 begin
01604 SQL := 'SELECT nspname AS TABLE_SCHEM FROM pg_catalog.pg_namespace'
01605 + ' WHERE nspname <> ''pg_toast'' AND nspname NOT'
01606 + ' LIKE ''pg\\_temp\\_%'' ORDER BY TABLE_SCHEM';
01607 end else
01608 SQL := 'SELECT ''''::text AS TABLE_SCHEM ORDER BY TABLE_SCHEM';
01609
01610 Result := CopyToVirtualResultSet(
01611 GetConnection.CreateStatement.ExecuteQuery(SQL),
01612 ConstructVirtualResultSet(SchemaColumnsDynArray));
01613 end;
01614
01615 {**
01616 Gets the catalog names available in this database. The results
01617 are ordered by catalog name.
01618
01619 <P>The catalog column is:
01620 <OL>
01621 <LI><B>TABLE_CAT</B> String => catalog name
01622 </OL>
01623
01624 @return <code>ResultSet</code> - each row has a single String column that is a
01625 catalog name
01626 }
01627 function TZPostgreSQLDatabaseMetadata.UncachedGetCatalogs: IZResultSet;
01628 var
01629 SQL: string;
01630 begin
01631 if HaveMinimumServerVersion(7, 3) then
01632 begin
01633 SQL := 'SELECT datname AS TABLE_CAT FROM pg_catalog.pg_database'
01634 + ' ORDER BY TABLE_CAT';
01635 end else
01636 SQL := 'SELECT datname AS TABLE_CAT FROM pg_database ORDER BY TABLE_CAT';
01637
01638 Result := CopyToVirtualResultSet(
01639 GetConnection.CreateStatement.ExecuteQuery(SQL),
01640 ConstructVirtualResultSet(CatalogColumnsDynArray));
01641 end;
01642
01643 {**
01644 Gets the table types available in this database. The results
01645 are ordered by table type.
01646
01647 <P>The table type is:
01648 <OL>
01649 <LI><B>TABLE_TYPE</B> String => table type. Typical types are "TABLE",
01650 "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY",
01651 "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
01652 </OL>
01653
01654 @return <code>ResultSet</code> - each row has a single String column that is a
01655 table type
01656 }
01657 function TZPostgreSQLDatabaseMetadata.UncachedGetTableTypes: IZResultSet;
01658 const
01659 Types: array [0..10] of string = ('TABLE', 'VIEW', 'INDEX',
01660 'SEQUENCE', 'SYSTEM TABLE', 'SYSTEM TOAST TABLE',
01661 'SYSTEM TOAST INDEX', 'SYSTEM VIEW', 'SYSTEM INDEX',
01662 'TEMPORARY TABLE', 'TEMPORARY INDEX');
01663 var
01664 I: Integer;
01665 begin
01666 Result := ConstructVirtualResultSet(TableTypeColumnsDynArray);
01667 for I := 0 to 10 do
01668 begin
01669 Result.MoveToInsertRow;
01670 Result.UpdateString(1, Types[I]);
01671 Result.InsertRow;
01672 end;
01673 end;
01674
01675 {**
01676 Gets a description of table columns available in
01677 the specified catalog.
01678
01679 <P>Only column descriptions matching the catalog, schema, table
01680 and column name criteria are returned. They are ordered by
01681 TABLE_SCHEM, TABLE_NAME and ORDINAL_POSITION.
01682
01683 <P>Each column description has the following columns:
01684 <OL>
01685 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01686 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01687 <LI><B>TABLE_NAME</B> String => table name
01688 <LI><B>COLUMN_NAME</B> String => column name
01689 <LI><B>DATA_TYPE</B> short => SQL type from java.sql.Types
01690 <LI><B>TYPE_NAME</B> String => Data source dependent type name,
01691 for a UDT the type name is fully qualified
01692 <LI><B>COLUMN_SIZE</B> int => column size. For char or date
01693 types this is the maximum number of characters, for numeric or
01694 decimal types this is precision.
01695 <LI><B>BUFFER_LENGTH</B> is not used.
01696 <LI><B>DECIMAL_DIGITS</B> int => the number of fractional digits
01697 <LI><B>NUM_PREC_RADIX</B> int => Radix (typically either 10 or 2)
01698 <LI><B>NULLABLE</B> int => is NULL allowed?
01699 <UL>
01700 <LI> columnNoNulls - might not allow NULL values
01701 <LI> columnNullable - definitely allows NULL values
01702 <LI> columnNullableUnknown - nullability unknown
01703 </UL>
01704 <LI><B>REMARKS</B> String => comment describing column (may be null)
01705 <LI><B>COLUMN_DEF</B> String => default value (may be null)
01706 <LI><B>SQL_DATA_TYPE</B> int => unused
01707 <LI><B>SQL_DATETIME_SUB</B> int => unused
01708 <LI><B>CHAR_OCTET_LENGTH</B> int => for char types the
01709 maximum number of bytes in the column
01710 <LI><B>ORDINAL_POSITION</B> int => index of column in table
01711 (starting at 1)
01712 <LI><B>IS_NULLABLE</B> String => "NO" means column definitely
01713 does not allow NULL values; "YES" means the column might
01714 allow NULL values. An empty string means nobody knows.
01715 </OL>
01716
01717 @param catalog a catalog name; "" retrieves those without a
01718 catalog; null means drop catalog name from the selection criteria
01719 @param schemaPattern a schema name pattern; "" retrieves those
01720 without a schema
01721 @param tableNamePattern a table name pattern
01722 @param columnNamePattern a column name pattern
01723 @return <code>ResultSet</code> - each row is a column description
01724 @see #getSearchStringEscape
01725 }
01726 function TZPostgreSQLDatabaseMetadata.UncachedGetColumns(const Catalog: string;
01727 const SchemaPattern: string; const TableNamePattern: string;
01728 const ColumnNamePattern: string): IZResultSet;
01729 {const
01730 VARHDRSZ = 4;
01731 }var
01732 TypeOid, AttTypMod: Integer;
01733 SQL, PgType: string;
01734 begin
01735 Result := ConstructVirtualResultSet(TableColColumnsDynArray);
01736
01737 if HaveMinimumServerVersion(7, 3) then
01738 begin
01739 SQL := 'SELECT n.nspname,' {1}
01740 + 'c.relname,' {2}
01741 + 'a.attname,' {3}
01742 + 'a.atttypid,' {4}
01743 + 'a.attnotnull,' {5}
01744 + 'a.atttypmod,' {6}
01745 + 'a.attlen,' {7}
01746 + 'a.attnum,' {8}
01747 + 'pg_get_expr(def.adbin, def.adrelid) as adsrc,' {9}
01748 + 'dsc.description ' {10}
01749 + ' FROM pg_catalog.pg_namespace n '
01750 + ' JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid) '
01751 + ' JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid) '
01752 + ' LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid'
01753 + ' AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc'
01754 + ' ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) '
01755 + ' LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid'
01756 + ' AND dc.relname=''pg_class'') LEFT JOIN pg_catalog.pg_namespace dn'
01757 + ' ON (dc.relnamespace=dn.oid AND dn.nspname=''pg_catalog'') '
01758 + ' WHERE a.attnum > 0 AND NOT a.attisdropped';
01759 if SchemaPattern <> '' then
01760 begin
01761 SQL := SQL + ' AND n.nspname LIKE '
01762 + EscapeString(SchemaPattern);
01763 end;
01764 end
01765 else
01766 begin
01767 SQL := 'SELECT NULL::text AS nspname,' {1}
01768 + 'c.relname,' {2}
01769 + 'a.attname,' {3}
01770 + 'a.atttypid,' {4}
01771 + 'a.attnotnull,' {5}
01772 + 'a.atttypmod,' {6}
01773 + 'a.attlen,' {7}
01774 + 'a.attnum,' {8}
01775 + 'NULL AS adsrc,' {9}
01776 + 'NULL AS description' {10}
01777 + 'FROM pg_class c, pg_attribute a '
01778 + ' WHERE a.attrelid=c.oid AND a.attnum > 0 ';
01779 end;
01780
01781 SQL := SQL + ' AND c.relname LIKE ' + EscapeString(ToLikeString(TableNamePattern))
01782 + ' AND a.attname LIKE ' + EscapeString(ToLikeString(ColumnNamePattern))
01783 + ' ORDER BY nspname,relname,attnum';
01784
01785 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01786 begin
01787 while Next do
01788 begin
01789 AttTypMod := GetInt(6 {atttypmod});
01790
01791 TypeOid := GetInt(4 {atttypid});
01792 PgType := GetPostgreSQLType(TypeOid);
01793
01794 Result.MoveToInsertRow;
01795 Result.UpdateNull(1);
01796 Result.UpdateString(2, GetString(1 {nspname}));
01797 Result.UpdateString(3, GetString(2 {relname}));
01798 Result.UpdateString(4, GetString(3 {attname}));
01799 Result.UpdateInt(5, Ord(GetSQLTypeByOid(TypeOid)));
01800 Result.UpdateString(6, PgType);
01801 Result.UpdateInt(8, 0);
01802
01803 if (PgType = 'bpchar') or (PgType = 'varchar') then
01804 begin
01805 if AttTypMod <> -1 then
01806 Result.UpdateInt(7, AttTypMod - 4)
01807 else Result.UpdateInt(7, 0);
01808 end
01809 else if (PgType = 'numeric') or (PgType = 'decimal') then
01810 begin
01811 Result.UpdateInt(7, ((AttTypMod - 4) div 65536));
01812 Result.UpdateInt(9, ((AttTypMod -4) mod 65536));
01813 Result.UpdateInt(10, 10);
01814 end
01815 else if (PgType = 'bit') or (PgType = 'varbit') then
01816 begin
01817 Result.UpdateInt(7, AttTypMod);
01818 Result.UpdateInt(10, 2);
01819 end
01820 else
01821 begin
01822 Result.UpdateInt(7, GetInt(7 {attlen}));
01823 Result.UpdateInt(10, 2);
01824 end;
01825
01826 Result.UpdateNull(8);
01827 if GetBoolean(5 {attnotnull}) then
01828 begin
01829 Result.UpdateString(18, 'NO');
01830 Result.UpdateInt(11, Ord(ntNoNulls));
01831 end
01832 else
01833 begin
01834 Result.UpdateString(18, 'YES');
01835 Result.UpdateInt(11, Ord(ntNullable));
01836 end;
01837
01838 Result.UpdateString(12, GetString(10 {description}));
01839 Result.UpdateString(13, GetString(9 {adsrc}));
01840 Result.UpdateNull(14);
01841 Result.UpdateNull(15);
01842 Result.UpdateInt(16, Result.GetInt(7));
01843 Result.UpdateInt(17, GetInt(8 {attnum}));
01844
01845 Result.UpdateNullByName('AUTO_INCREMENT');
01846 Result.UpdateBooleanByName('CASE_SENSITIVE',
01847 GetIdentifierConvertor.IsCaseSensitive(GetString(3 {attname})));
01848 Result.UpdateBooleanByName('SEARCHABLE', True);
01849 Result.UpdateBooleanByName('WRITABLE', True);
01850 Result.UpdateBooleanByName('DEFINITELYWRITABLE', True);
01851 Result.UpdateBooleanByName('READONLY', False);
01852
01853 Result.InsertRow;
01854 end;
01855 Close;
01856 end;
01857 end;
01858
01859 {**
01860 Gets a description of the access rights for a table's columns.
01861
01862 <P>Only privileges matching the column name criteria are
01863 returned. They are ordered by COLUMN_NAME and PRIVILEGE.
01864
01865 <P>Each privilige description has the following columns:
01866 <OL>
01867 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01868 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01869 <LI><B>TABLE_NAME</B> String => table name
01870 <LI><B>COLUMN_NAME</B> String => column name
01871 <LI><B>GRANTOR</B> => grantor of access (may be null)
01872 <LI><B>GRANTEE</B> String => grantee of access
01873 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01874 INSERT, UPDATE, REFRENCES, ...)
01875 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01876 to grant to others; "NO" if not; null if unknown
01877 </OL>
01878
01879 @param catalog a catalog name; "" retrieves those without a
01880 catalog; null means drop catalog name from the selection criteria
01881 @param schema a schema name; "" retrieves those without a schema
01882 @param table a table name
01883 @param columnNamePattern a column name pattern
01884 @return <code>ResultSet</code> - each row is a column privilege description
01885 @see #getSearchStringEscape
01886 }
01887 function TZPostgreSQLDatabaseMetadata.UncachedGetColumnPrivileges(const Catalog: string;
01888 const Schema: string; const Table: string; const ColumnNamePattern: string): IZResultSet;
01889 var
01890 I, J: Integer;
01891 SQL, Column, Owner: string;
01892 Privileges, Grantable, Grantee: string;
01893 Permissions, PermissionsExp: TStrings;
01894 begin
01895 Result := ConstructVirtualResultSet(TableColPrivColumnsDynArray);
01896
01897 if HaveMinimumServerVersion(7, 3) then
01898 begin
01899 SQL := 'SELECT n.nspname,c.relname,u.usename,c.relacl,a.attname '
01900 + ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c,'
01901 + ' pg_catalog.pg_user u, pg_catalog.pg_attribute a '
01902 + ' WHERE c.relnamespace = n.oid AND u.usesysid = c.relowner '
01903 + ' AND c.oid = a.attrelid AND c.relkind = ''r'''
01904 + ' AND a.attnum > 0 AND NOT a.attisdropped';
01905 if Schema <> '' then
01906 SQL := SQL + ' AND n.nspname = ' + EscapeString(Schema);
01907 end
01908 else
01909 begin
01910 SQL := 'SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl,'
01911 + 'a.attname FROM pg_class c, pg_user u,pg_attribute a '
01912 + ' WHERE u.usesysid = c.relowner AND c.oid = a.attrelid '
01913 + ' AND a.attnum > 0 AND c.relkind = ''r''';
01914 end;
01915
01916 SQL := SQL + ' AND c.relname = ' + EscapeString(Table)
01917 + ' AND a.attname LIKE ' + EscapeString(ToLikeString(ToLikeString(ColumnNamePattern)))
01918 + ' ORDER BY attname';
01919
01920 Permissions := TStringList.Create;
01921 PermissionsExp := TStringList.Create;
01922 try
01923 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
01924 begin
01925 while Next do
01926 begin
01927
01928
01929 Column := GetStringByName('attname');
01930 Owner := GetStringByName('usename');
01931 Permissions.Clear;
01932 ParseACLArray(Permissions, GetStringByName('relacl'));
01933 for I := 0 to Permissions.Count-1 do
01934 begin
01935 PutSplitString(PermissionsExp, Permissions.Strings[I], '=');
01936 if PermissionsExp.Count < 2 then
01937 Continue;
01938 Grantee := PermissionsExp.Strings[0];
01939 if Grantee = '' then
01940 Grantee := 'PUBLIC';
01941 Privileges := PermissionsExp.Strings[1];
01942 for J := 1 to Length(Privileges) do
01943 begin
01944 if Owner = Grantee then
01945 Grantable := 'YES'
01946 else Grantable := 'NO';
01947 Result.MoveToInsertRow;
01948 Result.UpdateNull(1);
01949 Result.UpdateString(2, Schema);
01950 Result.UpdateString(3, Table);
01951 Result.UpdateString(4, Column);
01952 Result.UpdateString(5, Owner);
01953 Result.UpdateString(6, Grantee);
01954 Result.UpdateString(7, GetPrivilegeName(Privileges[J]));
01955 Result.UpdateString(8, grantable);
01956 Result.InsertRow;
01957 end;
01958 end;
01959 end;
01960 Close;
01961 end;
01962 finally
01963 Permissions.Free;
01964 PermissionsExp.Free;
01965 end;
01966 end;
01967
01968 {**
01969 Gets a description of the access rights for each table available
01970 in a catalog. Note that a table privilege applies to one or
01971 more columns in the table. It would be wrong to assume that
01972 this priviledge applies to all columns (this may be true for
01973 some systems but is not true for all.)
01974
01975 <P>Only privileges matching the schema and table name
01976 criteria are returned. They are ordered by TABLE_SCHEM,
01977 TABLE_NAME, and PRIVILEGE.
01978
01979 <P>Each privilige description has the following columns:
01980 <OL>
01981 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
01982 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
01983 <LI><B>TABLE_NAME</B> String => table name
01984 <LI><B>GRANTOR</B> => grantor of access (may be null)
01985 <LI><B>GRANTEE</B> String => grantee of access
01986 <LI><B>PRIVILEGE</B> String => name of access (SELECT,
01987 INSERT, UPDATE, REFRENCES, ...)
01988 <LI><B>IS_GRANTABLE</B> String => "YES" if grantee is permitted
01989 to grant to others; "NO" if not; null if unknown
01990 </OL>
01991
01992 @param catalog a catalog name; "" retrieves those without a
01993 catalog; null means drop catalog name from the selection criteria
01994 @param schemaPattern a schema name pattern; "" retrieves those
01995 without a schema
01996 @param tableNamePattern a table name pattern
01997 @return <code>ResultSet</code> - each row is a table privilege description
01998 @see #getSearchStringEscape
01999 }
02000 function TZPostgreSQLDatabaseMetadata.UncachedGetTablePrivileges(const Catalog: string;
02001 const SchemaPattern: string; const TableNamePattern: string): IZResultSet;
02002 var
02003 I, J: Integer;
02004 SQL, SchemaName, TableName, Owner: string;
02005 Privileges, Grantable, Grantee: string;
02006 Permissions, PermissionsExp: TStringList;
02007 begin
02008 Result := ConstructVirtualResultSet(TablePrivColumnsDynArray);
02009
02010 if HaveMinimumServerVersion(7, 3) then
02011 begin
02012 SQL := 'SELECT n.nspname,c.relname,u.usename,c.relacl '
02013 + ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c,'
02014 + ' pg_catalog.pg_user u WHERE c.relnamespace = n.oid '
02015 + ' AND u.usesysid = c.relowner AND c.relkind = ''r'' ';
02016 if SchemaPattern <> '' then
02017 SQL := SQL + ' AND n.nspname LIKE ' + EscapeString(SchemaPattern);
02018 end
02019 else
02020 begin
02021 SQL := 'SELECT NULL::text AS nspname,c.relname,u.usename,c.relacl '
02022 + ' FROM pg_class c, pg_user u WHERE u.usesysid = c.relowner '
02023 + ' AND c.relkind = ''r'' ';
02024 end;
02025
02026 SQL := SQL + ' AND c.relname LIKE ' + EscapeString(ToLikeString(TableNamePattern))
02027 + ' ORDER BY nspname, relname';
02028
02029 Permissions := TStringList.Create;
02030 PermissionsExp := TStringList.Create;
02031 try
02032 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02033 begin
02034 while Next do
02035 begin
02036 SchemaName := GetStringByName('nspname');
02037 TableName := GetStringByName('relname');
02038 Owner := GetStringByName('usename');
02039 SchemaName := GetStringByName('nspname');
02040 Permissions.Clear;
02041 ParseACLArray(Permissions, GetStringByName('relacl'));
02042 Permissions.Sort;
02043 for I := 0 to Permissions.Count-1 do
02044 begin
02045 PutSplitString(PermissionsExp, Permissions.Strings[I], '=');
02046 if PermissionsExp.Count < 2 then
02047 Continue;
02048 Grantee := PermissionsExp.Strings[0];
02049 if Grantee = '' then
02050 Grantee := 'PUBLIC';
02051 Privileges := PermissionsExp.Strings[1];
02052 for J := 1 to Length(Privileges) do
02053 begin
02054 if Owner = Grantee then
02055 Grantable := 'YES'
02056 else Grantable := 'NO';
02057 Result.MoveToInsertRow;
02058 Result.UpdateNull(1);
02059 Result.UpdateString(2, SchemaName);
02060 Result.UpdateString(3, TableName);
02061 Result.UpdateString(4, Owner);
02062 Result.UpdateString(5, Grantee);
02063 Result.UpdateString(6, GetPrivilegeName(Privileges[J]));
02064 Result.UpdateString(7, grantable);
02065 Result.InsertRow;
02066 end;
02067 end;
02068 end;
02069 Close;
02070 end;
02071 finally
02072 Permissions.Free;
02073 PermissionsExp.Free;
02074 end;
02075 end;
02076
02077 {**
02078 Gets a description of a table's columns that are automatically
02079 updated when any value in a row is updated. They are
02080 unordered.
02081
02082 <P>Each column description has the following columns:
02083 <OL>
02084 <LI><B>SCOPE</B> short => is not used
02085 <LI><B>COLUMN_NAME</B> String => column name
02086 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
02087 <LI><B>TYPE_NAME</B> String => Data source dependent type name
02088 <LI><B>COLUMN_SIZE</B> int => precision
02089 <LI><B>BUFFER_LENGTH</B> int => length of column value in bytes
02090 <LI><B>DECIMAL_DIGITS</B> short => scale
02091 <LI><B>PSEUDO_COLUMN</B> short => is this a pseudo column
02092 like an Oracle ROWID
02093 <UL>
02094 <LI> versionColumnUnknown - may or may not be pseudo column
02095 <LI> versionColumnNotPseudo - is NOT a pseudo column
02096 <LI> versionColumnPseudo - is a pseudo column
02097 </UL>
02098 </OL>
02099
02100 @param catalog a catalog name; "" retrieves those without a
02101 catalog; null means drop catalog name from the selection criteria
02102 @param schema a schema name; "" retrieves those without a schema
02103 @param table a table name
02104 @return <code>ResultSet</code> - each row is a column description
02105 @exception SQLException if a database access error occurs
02106 }
02107 function TZPostgreSQLDatabaseMetadata.UncachedGetVersionColumns(const Catalog: string;
02108 const Schema: string; const Table: string): IZResultSet;
02109 begin
02110 Result := ConstructVirtualResultSet(TableColVerColumnsDynArray);
02111
02112 Result.MoveToInsertRow;
02113 Result.UpdateNull(1);
02114 Result.UpdateString(2, 'ctid');
02115 Result.UpdateInt(3, Ord(GetSQLTypeByName('tid')));
02116 Result.UpdateString(4, 'tid');
02117 Result.UpdateNull(5);
02118 Result.UpdateNull(6);
02119 Result.UpdateNull(7);
02120 Result.UpdateInt(4, Ord(vcPseudo));
02121 Result.InsertRow;
02122 end;
02123
02124 {**
02125 Gets a description of a table's primary key columns. They
02126 are ordered by COLUMN_NAME.
02127
02128 <P>Each primary key column description has the following columns:
02129 <OL>
02130 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
02131 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
02132 <LI><B>TABLE_NAME</B> String => table name
02133 <LI><B>COLUMN_NAME</B> String => column name
02134 <LI><B>KEY_SEQ</B> short => sequence number within primary key
02135 <LI><B>PK_NAME</B> String => primary key name (may be null)
02136 </OL>
02137
02138 @param catalog a catalog name; "" retrieves those without a
02139 catalog; null means drop catalog name from the selection criteria
02140 @param schema a schema name; "" retrieves those
02141 without a schema
02142 @param table a table name
02143 @return <code>ResultSet</code> - each row is a primary key column description
02144 @exception SQLException if a database access error occurs
02145 }
02146 function TZPostgreSQLDatabaseMetadata.UncachedGetPrimaryKeys(const Catalog: string;
02147 const Schema: string; const Table: string): IZResultSet;
02148 var
02149 SQL, Select, From, Where: string;
02150 begin
02151 if HaveMinimumServerVersion(7, 3) then
02152 begin
02153 Select := 'SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,';
02154 From := ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct,'
02155 + ' pg_catalog.pg_class ci, pg_catalog.pg_attribute a,'
02156 + ' pg_catalog.pg_index i';
02157 Where := ' AND ct.relnamespace = n.oid';
02158 if Schema <> '' then
02159 Where := Where + ' AND n.nspname = ' + EscapeString(Schema);
02160 end
02161 else
02162 begin
02163 Select := 'SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM,';
02164 From := ' FROM pg_class ct, pg_class ci, pg_attribute a, pg_index i';
02165 end;
02166 SQL := Select + ' ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME,'
02167 + ' a.attnum AS KEY_SEQ, ci.relname AS PK_NAME'
02168 + From
02169 + ' WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid'
02170 + ' AND a.attrelid=ci.oid AND i.indisprimary';
02171 if Table <> '' then
02172 SQL := SQL + ' AND ct.relname = ' + EscapeString(Table);
02173 SQL := SQL + Where + ' ORDER BY table_name, pk_name, key_seq';
02174
02175 Result := CopyToVirtualResultSet(
02176 GetConnection.CreateStatement.ExecuteQuery(SQL),
02177 ConstructVirtualResultSet(PrimaryKeyColumnsDynArray));
02178 end;
02179
02180 {**
02181 Gets a description of the primary key columns that are
02182 referenced by a table's foreign key columns (the primary keys
02183 imported by a table). They are ordered by PKTABLE_CAT,
02184 PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.
02185
02186 <P>Each primary key column description has the following columns:
02187 <OL>
02188 <LI><B>PKTABLE_CAT</B> String => primary key table catalog
02189 being imported (may be null)
02190 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema
02191 being imported (may be null)
02192 <LI><B>PKTABLE_NAME</B> String => primary key table name
02193 being imported
02194 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
02195 being imported
02196 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
02197 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
02198 <LI><B>FKTABLE_NAME</B> String => foreign key table name
02199 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
02200 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
02201 <LI><B>UPDATE_RULE</B> short => What happens to
02202 foreign key when primary is updated:
02203 <UL>
02204 <LI> importedNoAction - do not allow update of primary
02205 key if it has been imported
02206 <LI> importedKeyCascade - change imported key to agree
02207 with primary key update
02208 <LI> importedKeySetNull - change imported key to NULL if
02209 its primary key has been updated
02210 <LI> importedKeySetDefault - change imported key to default values
02211 if its primary key has been updated
02212 <LI> importedKeyRestrict - same as importedKeyNoAction
02213 (for ODBC 2.x compatibility)
02214 </UL>
02215 <LI><B>DELETE_RULE</B> short => What happens to
02216 the foreign key when primary is deleted.
02217 <UL>
02218 <LI> importedKeyNoAction - do not allow delete of primary
02219 key if it has been imported
02220 <LI> importedKeyCascade - delete rows that import a deleted key
02221 <LI> importedKeySetNull - change imported key to NULL if
02222 its primary key has been deleted
02223 <LI> importedKeyRestrict - same as importedKeyNoAction
02224 (for ODBC 2.x compatibility)
02225 <LI> importedKeySetDefault - change imported key to default if
02226 its primary key has been deleted
02227 </UL>
02228 <LI><B>FK_NAME</B> String => foreign key name (may be null)
02229 <LI><B>PK_NAME</B> String => primary key name (may be null)
02230 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
02231 constraints be deferred until commit
02232 <UL>
02233 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
02234 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
02235 <LI> importedKeyNotDeferrable - see SQL92 for definition
02236 </UL>
02237 </OL>
02238
02239 @param catalog a catalog name; "" retrieves those without a
02240 catalog; null means drop catalog name from the selection criteria
02241 @param schema a schema name; "" retrieves those
02242 without a schema
02243 @param table a table name
02244 @return <code>ResultSet</code> - each row is a primary key column description
02245 @see #getExportedKeys
02246 }
02247 function TZPostgreSQLDatabaseMetadata.UncachedGetImportedKeys(const Catalog: string;
02248 const Schema: string; const Table: string): IZResultSet;
02249 begin
02250 Result := UncachedGetCrossReference('', '', '', Catalog, Schema, Table);
02251 end;
02252
02253 {**
02254 Gets a description of the foreign key columns that reference a
02255 table's primary key columns (the foreign keys exported by a
02256 table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM,
02257 FKTABLE_NAME, and KEY_SEQ.
02258
02259 <P>Each foreign key column description has the following columns:
02260 <OL>
02261 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
02262 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
02263 <LI><B>PKTABLE_NAME</B> String => primary key table name
02264 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
02265 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
02266 being exported (may be null)
02267 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
02268 being exported (may be null)
02269 <LI><B>FKTABLE_NAME</B> String => foreign key table name
02270 being exported
02271 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
02272 being exported
02273 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
02274 <LI><B>UPDATE_RULE</B> short => What happens to
02275 foreign key when primary is updated:
02276 <UL>
02277 <LI> importedNoAction - do not allow update of primary
02278 key if it has been imported
02279 <LI> importedKeyCascade - change imported key to agree
02280 with primary key update
02281 <LI> importedKeySetNull - change imported key to NULL if
02282 its primary key has been updated
02283 <LI> importedKeySetDefault - change imported key to default values
02284 if its primary key has been updated
02285 <LI> importedKeyRestrict - same as importedKeyNoAction
02286 (for ODBC 2.x compatibility)
02287 </UL>
02288 <LI><B>DELETE_RULE</B> short => What happens to
02289 the foreign key when primary is deleted.
02290 <UL>
02291 <LI> importedKeyNoAction - do not allow delete of primary
02292 key if it has been imported
02293 <LI> importedKeyCascade - delete rows that import a deleted key
02294 <LI> importedKeySetNull - change imported key to NULL if
02295 its primary key has been deleted
02296 <LI> importedKeyRestrict - same as importedKeyNoAction
02297 (for ODBC 2.x compatibility)
02298 <LI> importedKeySetDefault - change imported key to default if
02299 its primary key has been deleted
02300 </UL>
02301 <LI><B>FK_NAME</B> String => foreign key name (may be null)
02302 <LI><B>PK_NAME</B> String => primary key name (may be null)
02303 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
02304 constraints be deferred until commit
02305 <UL>
02306 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
02307 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
02308 <LI> importedKeyNotDeferrable - see SQL92 for definition
02309 </UL>
02310 </OL>
02311
02312 @param catalog a catalog name; "" retrieves those without a
02313 catalog; null means drop catalog name from the selection criteria
02314 @param schema a schema name; "" retrieves those
02315 without a schema
02316 @param table a table name
02317 @return <code>ResultSet</code> - each row is a foreign key column description
02318 @see #getImportedKeys
02319 }
02320 function TZPostgreSQLDatabaseMetadata.UncachedGetExportedKeys(const Catalog: string;
02321 const Schema: string; const Table: string): IZResultSet;
02322 begin
02323 Result := UncachedGetCrossReference(Catalog, Schema, Table, '', '', '');
02324 end;
02325
02326 {**
02327 Gets a description of the foreign key columns in the foreign key
02328 table that reference the primary key columns of the primary key
02329 table (describe how one table imports another's key.) This
02330 should normally return a single foreign key/primary key pair
02331 (most tables only import a foreign key from a table once.) They
02332 are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and
02333 KEY_SEQ.
02334
02335 <P>Each foreign key column description has the following columns:
02336 <OL>
02337 <LI><B>PKTABLE_CAT</B> String => primary key table catalog (may be null)
02338 <LI><B>PKTABLE_SCHEM</B> String => primary key table schema (may be null)
02339 <LI><B>PKTABLE_NAME</B> String => primary key table name
02340 <LI><B>PKCOLUMN_NAME</B> String => primary key column name
02341 <LI><B>FKTABLE_CAT</B> String => foreign key table catalog (may be null)
02342 being exported (may be null)
02343 <LI><B>FKTABLE_SCHEM</B> String => foreign key table schema (may be null)
02344 being exported (may be null)
02345 <LI><B>FKTABLE_NAME</B> String => foreign key table name
02346 being exported
02347 <LI><B>FKCOLUMN_NAME</B> String => foreign key column name
02348 being exported
02349 <LI><B>KEY_SEQ</B> short => sequence number within foreign key
02350 <LI><B>UPDATE_RULE</B> short => What happens to
02351 foreign key when primary is updated:
02352 <UL>
02353 <LI> importedNoAction - do not allow update of primary
02354 key if it has been imported
02355 <LI> importedKeyCascade - change imported key to agree
02356 with primary key update
02357 <LI> importedKeySetNull - change imported key to NULL if
02358 its primary key has been updated
02359 <LI> importedKeySetDefault - change imported key to default values
02360 if its primary key has been updated
02361 <LI> importedKeyRestrict - same as importedKeyNoAction
02362 (for ODBC 2.x compatibility)
02363 </UL>
02364 <LI><B>DELETE_RULE</B> short => What happens to
02365 the foreign key when primary is deleted.
02366 <UL>
02367 <LI> importedKeyNoAction - do not allow delete of primary
02368 key if it has been imported
02369 <LI> importedKeyCascade - delete rows that import a deleted key
02370 <LI> importedKeySetNull - change imported key to NULL if
02371 its primary key has been deleted
02372 <LI> importedKeyRestrict - same as importedKeyNoAction
02373 (for ODBC 2.x compatibility)
02374 <LI> importedKeySetDefault - change imported key to default if
02375 its primary key has been deleted
02376 </UL>
02377 <LI><B>FK_NAME</B> String => foreign key name (may be null)
02378 <LI><B>PK_NAME</B> String => primary key name (may be null)
02379 <LI><B>DEFERRABILITY</B> short => can the evaluation of foreign key
02380 constraints be deferred until commit
02381 <UL>
02382 <LI> importedKeyInitiallyDeferred - see SQL92 for definition
02383 <LI> importedKeyInitiallyImmediate - see SQL92 for definition
02384 <LI> importedKeyNotDeferrable - see SQL92 for definition
02385 </UL>
02386 </OL>
02387
02388 @param primaryCatalog a catalog name; "" retrieves those without a
02389 catalog; null means drop catalog name from the selection criteria
02390 @param primarySchema a schema name; "" retrieves those
02391 without a schema
02392 @param primaryTable the table name that exports the key
02393 @param foreignCatalog a catalog name; "" retrieves those without a
02394 catalog; null means drop catalog name from the selection criteria
02395 @param foreignSchema a schema name; "" retrieves those
02396 without a schema
02397 @param foreignTable the table name that imports the key
02398 @return <code>ResultSet</code> - each row is a foreign key column description
02399 @see #getImportedKeys
02400 }
02401 function TZPostgreSQLDatabaseMetadata.UncachedGetCrossReference(const PrimaryCatalog: string;
02402 const PrimarySchema: string; const PrimaryTable: string; const ForeignCatalog: string;
02403 const ForeignSchema: string; const ForeignTable: string): IZResultSet;
02404 var
02405 SQL, Select, From, Where: string;
02406 DeleteRule, UpdateRule, Rule: string;
02407 {FKeyName, }FKeyColumn, PKeyColumn, Targs: string;
02408 Action, KeySequence, Advance: Integer;
02409 List: TStrings;
02410 Deferrability: Integer;
02411 Deferrable, InitiallyDeferred: Boolean;
02412 begin
02413 Result := ConstructVirtualResultSet(CrossRefColumnsDynArray);
02414
02415 if HaveMinimumServerVersion(7, 3) then
02416 begin
02417 Select := 'SELECT DISTINCT n1.nspname as pnspname,n2.nspname as fnspname,';
02418 From := ' FROM pg_catalog.pg_namespace n1 JOIN pg_catalog.pg_class c1'
02419 + ' ON (c1.relnamespace = n1.oid) JOIN pg_catalog.pg_index i'
02420 + ' ON (c1.oid=i.indrelid) JOIN pg_catalog.pg_class ic'
02421 + ' ON (i.indexrelid=ic.oid) JOIN pg_catalog.pg_attribute a'
02422 + ' ON (ic.oid=a.attrelid), pg_catalog.pg_namespace n2'
02423 + ' JOIN pg_catalog.pg_class c2 ON (c2.relnamespace=n2.oid),'
02424 + ' pg_catalog.pg_trigger t1 JOIN pg_catalog.pg_proc p1'
02425 + ' ON (t1.tgfoid=p1.oid), pg_catalog.pg_trigger t2'
02426 + ' JOIN pg_catalog.pg_proc p2 ON (t2.tgfoid=p2.oid)';
02427 Where := '';
02428 if PrimarySchema <> ''then
02429 begin
02430 Where := Where + ' AND n1.nspname = '
02431 + EscapeString(PrimarySchema);
02432 end;
02433 if ForeignSchema <> '' then
02434 begin
02435 Where := Where + ' AND n2.nspname = '
02436 + EscapeString(ForeignSchema);
02437 end;
02438 end
02439 else
02440 begin
02441 Select := 'SELECT DISTINCT NULL::text as pnspname, NULL::text as fnspname,';
02442 From := ' FROM pg_class c1 JOIN pg_index i ON (c1.oid=i.indrelid)'
02443 + ' JOIN pg_class ic ON (i.indexrelid=ic.oid) JOIN pg_attribute a'
02444 + ' ON (ic.oid=a.attrelid), pg_class c2, pg_trigger t1'
02445 + ' JOIN pg_proc p1 ON (t1.tgfoid=p1.oid), pg_trigger t2'
02446 + ' JOIN pg_proc p2 ON (t2.tgfoid=p2.oid)';
02447 end;
02448
02449 SQL := Select + ' c1.relname as prelname, c2.relname as frelname,'
02450 + ' t1.tgconstrname, a.attnum as keyseq, ic.relname as fkeyname,'
02451 + ' t1.tgdeferrable, t1.tginitdeferred, t1.tgnargs,t1.tgargs,'
02452 + ' p1.proname as updaterule, p2.proname as deleterule'
02453 + From
02454 + ' WHERE (t1.tgrelid=c1.oid AND t1.tgisconstraint'
02455 + ' AND t1.tgconstrrelid=c2.oid AND p1.proname'
02456 + ' LIKE ' + EscapeString('RI\_FKey\_%\_upd')
02457 + ') AND (t2.tgrelid=c1.oid'
02458 + ' AND t2.tgisconstraint AND t2.tgconstrrelid=c2.oid '
02459 + ' AND p2.proname LIKE ' + EscapeString('RI\_FKey\_%\_del')
02460 + ') AND i.indisprimary'
02461 + Where;
02462 if PrimaryTable <> '' then
02463 SQL := SQL + ' AND c1.relname=' + EscapeString(PrimaryTable);
02464 if ForeignTable <> '' then
02465 SQL := SQL + ' AND c2.relname=' + EscapeString(ForeignTable);
02466 SQL := SQL + ' ORDER BY ';
02467
02468 if PrimaryTable <> '' then
02469 begin
02470 if HaveMinimumServerVersion(7, 3) then
02471 SQL := SQL + 'fnspname, ';
02472 SQL := SQL + 'frelname';
02473 end
02474 else
02475 begin
02476 if HaveMinimumServerVersion(7, 3) then
02477 SQL := SQL + 'pnspname, ';
02478 SQL := SQL + 'prelname';
02479 end;
02480
02481 SQL := SQL + ', keyseq';
02482
02483 List := TStringList.Create;
02484 try
02485 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02486 begin
02487 while Next do
02488 begin
02489 Result.MoveToInsertRow;
02490 Result.UpdateString(2, GetString(1));
02491 Result.UpdateString(6, GetString(2));
02492 Result.UpdateString(3, GetString(3));
02493 Result.UpdateString(7, GetString(4));
02494
02495 //FKeyName := GetString(5);
02496 UpdateRule := GetString(12);
02497 if UpdateRule <> '' then
02498 begin
02499 Rule := Copy(UpdateRule, 9, Length(UpdateRule) - 12);
02500 Action := Ord(ikNoAction);
02501 if (Rule = '') or (Rule = 'noaction') then
02502 Action := Ord(ikNoAction);
02503 if Rule = 'cascade' then
02504 Action := Ord(ikCascade);
02505 if Rule = 'setnull' then
02506 Action := Ord(ikSetNull);
02507 if Rule = 'setdefault' then
02508 Action := Ord(ikSetDefault);
02509 if Rule = 'restrict' then
02510 Action := Ord(ikRestrict);
02511 Result.UpdateInt(10, Action);
02512 end;
02513
02514 DeleteRule := GetString(13);
02515 if DeleteRule <> '' then
02516 begin
02517 Rule := Copy(DeleteRule, 9, Length(DeleteRule) - 12);
02518 Action := Ord(ikNoAction);
02519 if Rule = 'cascade' then
02520 Action := Ord(ikCascade);
02521 if Rule = 'setnull' then
02522 Action := Ord(ikSetNull);
02523 if Rule = 'setdefault' then
02524 Action := Ord(ikSetDefault);
02525 if Rule = 'restrict' then
02526 Action := Ord(ikRestrict);
02527 Result.UpdateInt(11, Action);
02528 end;
02529
02530 KeySequence := GetInt(6);
02531 Targs := GetString(11);
02532
02533 //<unnamed>\000ww\000vv\000UNSPECIFIED\000m\000a\000n\000b\000
02534 //for Postgresql 7.3
02535 //$1\000ww\000vv\000UNSPECIFIED\000m\000a\000n\000b\000
02536 //$2\000ww\000vv\000UNSPECIFIED\000m\000a\000n\000b\000
02537
02538 Advance := 4 + (KeySequence - 1) * 2;
02539 PutSplitStringEx(List, Targs, '\000');
02540
02541 if Advance <= List.Count-1 then
02542 FKeyColumn := List.Strings[Advance];
02543 if Advance + 1 <= List.Count-1 then
02544 PKeyColumn := List.Strings[Advance+1];
02545 Result.UpdateString(4, PKeyColumn);
02546 Result.UpdateString(8, FKeyColumn);
02547 Result.UpdateString(9, GetString(6)); //KEY_SEQ
02548
02549 if List.Strings[0] = '<unnamed>' then
02550 Result.UpdateString(12, Targs) //FK_NAME
02551 else Result.UpdateString(12, List.Strings[0]); //FK_NAME
02552
02553 Result.UpdateString(13, GetString(6)); //PK_ NAME
02554
02555 Deferrability := Ord(ikNotDeferrable);
02556 Deferrable := GetBoolean(8);
02557 InitiallyDeferred := GetBoolean(9);
02558 if Deferrable then
02559 begin
02560 if InitiallyDeferred then
02561 Deferrability := Ord(ikInitiallyDeferred)
02562 else Deferrability := Ord(ikInitiallyImmediate);
02563 end;
02564 Result.UpdateInt(14, Deferrability);
02565 Result.InsertRow;
02566 end;
02567 Close;
02568 end;
02569 finally
02570 List.Free;
02571 end;
02572 end;
02573
02574 {**
02575 Gets a description of all the standard SQL types supported by
02576 this database. They are ordered by DATA_TYPE and then by how
02577 closely the data type maps to the corresponding JDBC SQL type.
02578
02579 <P>Each type description has the following columns:
02580 <OL>
02581 <LI><B>TYPE_NAME</B> String => Type name
02582 <LI><B>DATA_TYPE</B> short => SQL data type from java.sql.Types
02583 <LI><B>PRECISION</B> int => maximum precision
02584 <LI><B>LITERAL_PREFIX</B> String => prefix used to quote a literal
02585 (may be null)
02586 <LI><B>LITERAL_SUFFIX</B> String => suffix used to quote a literal
02587 (may be null)
02588 <LI><B>CREATE_PARAMS</B> String => parameters used in creating
02589 the type (may be null)
02590 <LI><B>NULLABLE</B> short => can you use NULL for this type?
02591 <UL>
02592 <LI> typeNoNulls - does not allow NULL values
02593 <LI> typeNullable - allows NULL values
02594 <LI> typeNullableUnknown - nullability unknown
02595 </UL>
02596 <LI><B>CASE_SENSITIVE</B> Boolean=> is it case sensitive?
02597 <LI><B>SEARCHABLE</B> short => can you use "WHERE" based on this type:
02598 <UL>
02599 <LI> typePredNone - No support
02600 <LI> typePredChar - Only supported with WHERE .. LIKE
02601 <LI> typePredBasic - Supported except for WHERE .. LIKE
02602 <LI> typeSearchable - Supported for all WHERE ..
02603 </UL>
02604 <LI><B>UNSIGNED_ATTRIBUTE</B> Boolean => is it unsigned?
02605 <LI><B>FIXED_PREC_SCALE</B> Boolean => can it be a money value?
02606 <LI><B>AUTO_INCREMENT</B> Boolean => can it be used for an
02607 auto-increment value?
02608 <LI><B>LOCAL_TYPE_NAME</B> String => localized version of type name
02609 (may be null)
02610 <LI><B>MINIMUM_SCALE</B> short => minimum scale supported
02611 <LI><B>MAXIMUM_SCALE</B> short => maximum scale supported
02612 <LI><B>SQL_DATA_TYPE</B> int => unused
02613 <LI><B>SQL_DATETIME_SUB</B> int => unused
02614 <LI><B>NUM_PREC_RADIX</B> int => usually 2 or 10
02615 </OL>
02616
02617 @return <code>ResultSet</code> - each row is an SQL type description
02618 }
02619 function TZPostgreSQLDatabaseMetadata.UncachedGetTypeInfo: IZResultSet;
02620 var
02621 SQL: string;
02622 begin
02623 Result := ConstructVirtualResultSet(TypeInfoColumnsDynArray);
02624
02625 if HaveMinimumServerVersion(7, 3) then
02626 SQL := ' SELECT typname FROM pg_catalog.pg_type '
02627 else SQL := ' SELECT typname FROM pg_type ';
02628
02629 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02630 begin
02631 while Next do
02632 begin
02633 Result.MoveToInsertRow;
02634 Result.UpdateString(1, GetString(1));
02635 Result.UpdateInt(2, Ord(GetSQLTypeByName(GetString(1))));
02636 Result.UpdateInt(3, 9);
02637 Result.UpdateInt(7, Ord(ntNoNulls));
02638 Result.UpdateBoolean(8, False);
02639 Result.UpdateBoolean(9, False);
02640 Result.UpdateBoolean(11, False);
02641 Result.UpdateBoolean(12, False);
02642 Result.UpdateInt(18, 10);
02643 Result.InsertRow;
02644 end;
02645 Close;
02646 end;
02647 end;
02648
02649 {**
02650 Gets a description of a table's indices and statistics. They are
02651 ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.
02652
02653 <P>Each index column description has the following columns:
02654 <OL>
02655 <LI><B>TABLE_CAT</B> String => table catalog (may be null)
02656 <LI><B>TABLE_SCHEM</B> String => table schema (may be null)
02657 <LI><B>TABLE_NAME</B> String => table name
02658 <LI><B>NON_UNIQUE</B> Boolean => Can index values be non-unique?
02659 false when TYPE is tableIndexStatistic
02660 <LI><B>INDEX_QUALIFIER</B> String => index catalog (may be null);
02661 null when TYPE is tableIndexStatistic
02662 <LI><B>INDEX_NAME</B> String => index name; null when TYPE is
02663 tableIndexStatistic
02664 <LI><B>TYPE</B> short => index type:
02665 <UL>
02666 <LI> tableIndexStatistic - this identifies table statistics that are
02667 returned in conjuction with a table's index descriptions
02668 <LI> tableIndexClustered - this is a clustered index
02669 <LI> tableIndexHashed - this is a hashed index
02670 <LI> tableIndexOther - this is some other style of index
02671 </UL>
02672 <LI><B>ORDINAL_POSITION</B> short => column sequence number
02673 within index; zero when TYPE is tableIndexStatistic
02674 <LI><B>COLUMN_NAME</B> String => column name; null when TYPE is
02675 tableIndexStatistic
02676 <LI><B>ASC_OR_DESC</B> String => column sort sequence, "A" => ascending,
02677 "D" => descending, may be null if sort sequence is not supported;
02678 null when TYPE is tableIndexStatistic
02679 <LI><B>CARDINALITY</B> int => When TYPE is tableIndexStatistic, then
02680 this is the number of rows in the table; otherwise, it is the
02681 number of unique values in the index.
02682 <LI><B>PAGES</B> int => When TYPE is tableIndexStatisic then
02683 this is the number of pages used for the table, otherwise it
02684 is the number of pages used for the current index.
02685 <LI><B>FILTER_CONDITION</B> String => Filter condition, if any.
02686 (may be null)
02687 </OL>
02688
02689 @param catalog a catalog name; "" retrieves those without a
02690 catalog; null means drop catalog name from the selection criteria
02691 @param schema a schema name; "" retrieves those without a schema
02692 @param table a table name
02693 @param unique when true, return only indices for unique values;
02694 when false, return indices regardless of whether unique or not
02695 @param approximate when true, result is allowed to reflect approximate
02696 or out of data values; when false, results are requested to be
02697 accurate
02698 @return <code>ResultSet</code> - each row is an index column description
02699 }
02700 function TZPostgreSQLDatabaseMetadata.UncachedGetIndexInfo(const Catalog: string;
02701 const Schema: string; const Table: string; Unique: Boolean;
02702 Approximate: Boolean): IZResultSet;
02703 var
02704 SQL, Select, From, Where: string;
02705 begin
02706 if HaveMinimumServerVersion(7, 3) then
02707 begin
02708 Select := 'SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM,';
02709 From := ' FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct,'
02710 + ' pg_catalog.pg_class ci, pg_catalog.pg_index i,'
02711 + ' pg_catalog.pg_attribute a, pg_catalog.pg_am am';
02712 Where := ' AND n.oid = ct.relnamespace';
02713 if Schema <> '' then
02714 Where := Where + ' AND n.nspname = ' + EscapeString(Schema);
02715 end
02716 else
02717 begin
02718 Select := 'SELECT NULL AS TABLE_CAT, NULL AS TABLE_SCHEM,';
02719 From := ' FROM pg_class ct, pg_class ci, pg_index i, pg_attribute a,'
02720 + ' pg_am am';
02721 end;
02722
02723 SQL := Select + ' ct.relname AS TABLE_NAME, NOT i.indisunique'
02724 + ' AS NON_UNIQUE, NULL AS INDEX_QUALIFIER, ci.relname AS INDEX_NAME,'
02725 + ' CASE i.indisclustered WHEN true THEN ' + IntToStr(Ord(tiClustered))
02726 + ' ELSE CASE am.amname WHEN ''hash'' THEN ' + IntToStr(Ord(tiHashed))
02727 + ' ELSE ' + IntToStr(Ord(tiOther)) + ' END END AS TYPE,'
02728 + ' a.attnum AS ORDINAL_POSITION, a.attname AS COLUMN_NAME,'
02729 + ' NULL AS ASC_OR_DESC, ci.reltuples AS CARDINALITY,'
02730 + ' ci.relpages AS PAGES, NULL AS FILTER_CONDITION'
02731 + From
02732 + ' WHERE ct.oid=i.indrelid AND ci.oid=i.indexrelid'
02733 + ' AND a.attrelid=ci.oid AND ci.relam=am.oid' + Where
02734 + ' AND ct.relname = ' + EscapeString(Table);
02735
02736 if Unique then
02737 SQL := SQL + ' AND i.indisunique';
02738 SQL := SQL + ' ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION';
02739
02740 Result := CopyToVirtualResultSet(
02741 GetConnection.CreateStatement.ExecuteQuery(SQL),
02742 ConstructVirtualResultSet(IndexInfoColumnsDynArray));
02743 end;
02744
02745 function TZPostgreSQLDatabaseMetadata.UncachedGetSequences(const Catalog, SchemaPattern,
02746 SequenceNamePattern: string): IZResultSet;
02747 var
02748 SQL: string;
02749 begin
02750 Result := ConstructVirtualResultSet(SequenceColumnsDynArray);
02751
02752 SQL := ' SELECT nspname, relname ' +
02753 'FROM pg_catalog.pg_namespace n, pg_catalog.pg_class ct ' +
02754 'WHERE relkind = ''S'' ' +
02755 'AND n.oid = ct.relnamespace';
02756
02757 if SequenceNamePattern <> '' then
02758 SQL := SQL + ' AND ' + Format('relname = ''%s''', [SequenceNamePattern]);
02759 if SchemaPattern <> '' then
02760 SQL := SQL + ' AND ' + Format('nspname = ''%s''', [SchemaPattern]);
02761
02762 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02763 begin
02764 while Next do
02765 begin
02766 Result.MoveToInsertRow;
02767 Result.UpdateNull(1);
02768 Result.UpdateString(2, GetStringByName('nspname'));
02769 Result.UpdateString(3, GetStringByName('relname'));
02770 Result.InsertRow;
02771 end;
02772 Close;
02773 end;
02774 end;
02775
02776 {**
02777 Does the database support the given result set type?
02778 @param type defined in <code>java.sql.ResultSet</code>
02779 @return <code>true</code> if so; <code>false</code> otherwise
02780 }
02781 function TZPostgreSQLDatabaseMetadata.SupportsResultSetType(
02782 _Type: TZResultSetType): Boolean;
02783 begin
02784 Result := _Type = rtScrollInsensitive;
02785 end;
02786
02787 {**
02788 Does the database support the concurrency type in combination
02789 with the given result set type?
02790
02791 @param type defined in <code>java.sql.ResultSet</code>
02792 @param concurrency type defined in <code>java.sql.ResultSet</code>
02793 @return <code>true</code> if so; <code>false</code> otherwise
02794 }
02795 function TZPostgreSQLDatabaseMetadata.SupportsResultSetConcurrency(
02796 _Type: TZResultSetType; Concurrency: TZResultSetConcurrency): Boolean;
02797 begin
02798 Result := (_Type = rtScrollInsensitive) and (Concurrency = rcReadOnly);
02799 end;
02800
02801 function TZPostgreSQLDatabaseMetadata.HaveMinimumServerVersion(
02802 MajorVersion: Integer; MinorVersion: Integer): Boolean;
02803 var
02804 PostgreSQLConnection: IZPostgreSQLConnection;
02805 begin
02806 PostgreSQLConnection := GetConnection as IZPostgreSQLConnection;
02807 Result := (MajorVersion < PostgreSQLConnection.GetServerMajorVersion)
02808 or ((MajorVersion = PostgreSQLConnection.GetServerMajorVersion)
02809 and (MinorVersion <= PostgreSQLConnection.GetServerMinorVersion));
02810 end;
02811
02812 function TZPostgreSQLDatabaseMetadata.GetMaxIndexKeys: Integer;
02813 var
02814 SQL, From: string;
02815 begin
02816 if HaveMinimumServerVersion(7, 3) then
02817 begin
02818 From := ' pg_catalog.pg_namespace n, pg_catalog.pg_type t1,'
02819 + ' pg_catalog.pg_type t2 WHERE t1.typnamespace=n.oid'
02820 + ' AND n.nspname=''pg_catalog'' AND ';
02821 end else
02822 From := ' pg_type t1, pg_type t2 WHERE ';
02823 SQL := ' SELECT t1.typlen/t2.typlen FROM ' + From
02824 + ' t1.typelem=t2.oid AND t1.typname=''oidvector'' ';
02825
02826 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02827 begin
02828 if not Next then
02829 raise Exception.Create(SUnknownError);
02830 Result := GetInt(1);
02831 Close;
02832 end;
02833 end;
02834
02835 function TZPostgreSQLDatabaseMetadata.GetMaxNameLength: Integer;
02836 var
02837 SQL: string;
02838 begin
02839 if HaveMinimumServerVersion(7, 3) then
02840 begin
02841 SQL := ' SELECT t.typlen FROM pg_catalog.pg_type t,'
02842 + ' pg_catalog.pg_namespace n WHERE t.typnamespace=n.oid'
02843 + ' AND t.typname=''name'' AND n.nspname=''pg_catalog'' ';
02844 end else
02845 SQL := ' SELECT typlen FROM pg_type WHERE typname=''name'' ';
02846
02847 with GetConnection.CreateStatement.ExecuteQuery(SQL) do
02848 begin
02849 if not Next then
02850 raise Exception.Create(SUnknownError);
02851 Result := GetIntByName('typlen');
02852 Close;
02853 end;
02854 end;
02855
02856 function TZPostgreSQLDatabaseMetadata.GetPostgreSQLType(Oid: Integer): string;
02857 begin
02858 Result := (GetConnection as IZPostgreSQLConnection).GetTypeNameByOid(Oid);
02859 end;
02860
02861 function TZPostgreSQLDatabaseMetadata.GetSQLTypeByOid(Oid: Integer): TZSQLType;
02862 var
02863 PostgreSQLConnection: IZPostgreSQLConnection;
02864 begin
02865 PostgreSQLConnection := GetConnection as IZPostgreSQLConnection;
02866 Result := PostgreSQLToSQLType(PostgreSQLConnection,
02867 PostgreSQLConnection.GetTypeNameByOid(Oid));
02868 end;
02869
02870 function TZPostgreSQLDatabaseMetadata.GetSQLTypeByName(
02871 TypeName: string): TZSQLType;
02872 begin
02873 Result := PostgreSQLToSQLType(
02874 GetConnection as IZPostgreSQLConnection, TypeName);
02875 end;
02876
02877 function TZPostgreSQLDatabaseMetadata.TableTypeSQLExpression(
02878 TableType: string; UseSchemas: Boolean): string;
02879 begin
02880 if UseSchemas then
02881 begin
02882 if TableType = 'TABLE' then
02883 Result := ' c.relkind = ''r'' AND n.nspname NOT LIKE ''pg\\_%'' '
02884 else if TableType = 'VIEW' then
02885 Result := ' c.relkind = ''v'' AND n.nspname <> ''pg_catalog'' '
02886 else if TableType = 'INDEX' then
02887 Result := ' c.relkind = ''i'' AND n.nspname NOT LIKE ''pg\\_%'' '
02888 else if TableType = 'SEQUENCE' then
02889 Result := ' c.relkind = ''S'' '
02890 else if TableType = 'SYSTEM TABLE' then
02891 Result := ' c.relkind = ''r'' AND n.nspname = ''pg_catalog'' '
02892 else if TableType = 'SYSTEM TOAST TABLE' then
02893 Result := ' c.relkind = ''r'' AND n.nspname = ''pg_toast'' '
02894 else if TableType = 'SYSTEM TOAST INDEX' then
02895 Result := ' c.relkind = ''i'' AND n.nspname = ''pg_toast'' '
02896 else if TableType = 'SYSTEM VIEW' then
02897 Result := ' c.relkind = ''v'' AND n.nspname = ''pg_catalog'' '
02898 else if TableType = 'SYSTEM INDEX' then
02899 Result := ' c.relkind = ''i'' AND n.nspname = ''pg_catalog'' '
02900 else if TableType = 'TEMPORARY TABLE' then
02901 Result := ' c.relkind = ''r'' AND n.nspname LIKE ''pg\\_temp\\_%'' '
02902 else if TableType = 'TEMPORARY INDEX' then
02903 Result := 'c.relkind = ''i'' AND n.nspname LIKE ''pg\\_temp\\_%'' ';
02904 end
02905 else
02906 begin
02907 if TableType = 'TABLE' then
02908 Result := ' c.relkind = ''r'' AND c.relname NOT LIKE ''pg\\_%'' '
02909 else if TableType = 'VIEW' then
02910 Result := ' c.relkind = ''v'' AND c.relname NOT LIKE ''pg\\_%'' '
02911 else if TableType = 'INDEX' then
02912 Result := ' c.relkind = ''i'' AND c.relname NOT LIKE ''pg\\_%'' '
02913 else if TableType = 'SEQUENCE' then
02914 Result := ' c.relkind = ''S'' '
02915 else if TableType = 'SYSTEM TABLE' then
02916 Result := ' c.relkind = ''r'' AND c.relname LIKE ''pg\\_%'' AND c.relname '+
02917 'NOT LIKE ''pg\\_toast\\_%'' AND c.relname NOT LIKE ''pg\\_temp\\_%'' '
02918 else if TableType = 'SYSTEM TOAST TABLE' then
02919 Result := ' c.relkind = ''r'' AND c.relname LIKE ''pg\\_toast\\_%'' '
02920 else if TableType = 'SYSTEM TOAST INDEX' then
02921 Result := ' c.relkind = ''i'' AND c.relname LIKE ''pg\\_toast\\_%'' '
02922 else if TableType = 'SYSTEM VIEW' then
02923 Result := 'c.relkind = ''v'' AND c.relname LIKE ''pg\\_%'''
02924 else if TableType = 'SYSTEM INDEX' then
02925 begin
02926 Result := ' c.relkind = ''v'' AND c.relname LIKE ''pg\\_%'' AND '+
02927 'c.relname NOT LIKE ''pg\\_toast\\_%'' AND c.relname '+
02928 'NOT LIKE ''pg\\_temp\\_%'' '
02929 end
02930 else if TableType = 'TEMPORARY TABLE' then
02931 Result := ' c.relkind = ''r'' AND c.relname LIKE ''pg\\_temp\\_%'' '
02932 else if TableType = 'TEMPORARY INDEX' then
02933 Result := ' c.relkind = ''i'' AND c.relname LIKE ''pg\\_temp\\_%'' '
02934 end;
02935 end;
02936
02937 procedure TZPostgreSQLDatabaseMetadata.ParseACLArray(
02938 List: TStrings; AclString: string);
02939 var
02940 PrevChar: Char;
02941 InQuotes: Boolean;
02942 I, BeginIndex: Integer;
02943 begin
02944 if AclString = '' then Exit;
02945 InQuotes := False;
02946 PrevChar := ' ';
02947 BeginIndex := 2;
02948 for I := BeginIndex to Length(AclString) do
02949 begin
02950 if (AclString[I] = '"') and (PrevChar <> '\' ) then
02951 InQuotes := not InQuotes
02952 else if (AclString[I] = ',') and not InQuotes then
02953 begin
02954 List.Add(Copy(AclString, BeginIndex, I - BeginIndex));
02955 BeginIndex := I+1;
02956 end;
02957 PrevChar := AclString[I];
02958 end;
02959
02960
02961 List.Add(Copy(AclString, BeginIndex, Length(AclString) - BeginIndex));
02962
02963
02964 for I := 0 to List.Count-1 do
02965 begin
02966 if (List.Strings[i][1] = '"')
02967 and (List.Strings[i][Length(List.Strings[i])] = '"') then
02968 List.Strings[i] := Copy(List.Strings[i], 2, Length(List.Strings[i])-2);
02969 end;
02970 end;
02971
02972 function TZPostgreSQLDatabaseMetadata.GetPrivilegeName(Permission: Char): string;
02973 begin
02974 case Permission of
02975 'a': Result := 'INSERT';
02976 'r': Result := 'SELECT';
02977 'w': Result := 'UPDATE';
02978 'd': Result := 'DELETE';
02979 'R': Result := 'RULE';
02980 'x': Result := 'REFERENCES';
02981 't': Result := 'TRIGGER';
02982 'X': Result := 'EXECUTE';
02983 'U': Result := 'USAGE';
02984 'C': Result := 'CREATE';
02985 'T': Result := 'CREATE TEMP';
02986 else Result := 'UNKNOWN';
02987 end;
02988 end;
02989
02990 end.