[fpc-pascal] Re: SQLDB GetSchemaInfoSQL for indexes etc?

[LacaK]

Reinier Olislagers  wrote / napísal(a):
> On 18-4-2012 8:27, LacaK wrote:
>   
>>> I don't have Delphi with dbExpress here, so can't test.
>>>   
>>>       
>> I did some tests in Delphi XE with DBExpress and MySQL:
>>
>> for stTables column names are: CatalogName, SchemaName, TableName,
>> TableType ('TABLE')
>>     
> So difference with FPC: the names (catalog_name, schema_name,
> table_name, table_type). The FPC names seem to match the ISO SQL*) names
> more (ISO has them capitalized, no problem if no quotes are used).
> In contrast to dbExpress (and probably the standard), FPC also has recno
> (internal database object ID? Could be very handy.)
> Other difference: TableType ('TABLE'): I think Delphi+dbExpress better
> match ISO SQL*) here (though 'BASE TABLE', 'VIEW') instead of the 0 that
> FPC returns - at least in the Firebird, PostgreSQL connectors that I
> checked.
>
> *) at least an SQL2008 draft version I found some time ago...
> The PostgreSQL documentation is also very nice:
> http://www.postgresql.org/docs/current/static/information-schema.html
> As is the Mimer (link to old documentation; newer is inside a frame):
> http://developer.mimer.com/documentation/html_91/Mimer_SQL_Engine_DocSet/Data_dic_views2.html
>   

> I'd prefer changing TABLE_TYPE to the ISO way of doing things, but of
> course it could break existing applications if any of them use this
> functionality... Still, FPC always returns 0 so I suppose it would have
> been useless anyway, so no harm in changing it.
>
>   
ok also MS SQL Server: http://msdn.microsoft.com/en-us/library/ms186224.aspx

>> for stProcedures : CatalogName, SchemaName, ProcedureName, ProcedureType
>> ('PROCEDURE')
>> (in FPC (for IBConnection) we have catalog_name, schema_name, proc_name,
>> proc_type and others)
>>     
> Yep, in_params and out_params: number of in and out parameters apparently...
> Once again, proc_type returns always 0 (Firebird - or is not implemented
> - PostgreSQL); changing it to varchar returning 'FUNCTION' or
> 'PROCEDURE' would make sense IMO.
> For using FUNCTION or PROCEDURE: see e.g. the IBM DB2 for iSeries/AS/400
> documentation via
> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogans.htm#catroutines
>
>   
also MS SQL http://msdn.microsoft.com/en-us/library/ms188757.aspx
>> for stColumns : CatalogName, SchemaName, TableName, ColumnName, TypeName
>> ('char', 'integer' etc.), Precision, Scale, Ordinal, DefaultValue,
>> IsNullable, IsAutoincrement, and others.
>>     
> FPC Firebird does not have Ordinal but column_position - probably the
> same meaning.
>   
yes
> Apart from naming issues: FPC does not have DefaultValue, or
> IsAutoIncrement.
> Furthermore, apart from column_name, column_position and the table info,
> none of the columns (e.g. column_type) return any useful data, always 0
> or an empty string.
>
>
>   
>> for stIndexes : CatalogName, SchemaName, TableName, IndexName,
>> ConstraintName, IsPrimary, IsUnique, IsAscending
>> (in list are included also PRIMARY KEYs and UNIQUE constraints)
>>     
> Got it, so both constraints and indexes... which of course overlap to a
> large extent.
> Suggest recno (object identifier: integer), catalog_name, schema_name,
> table_name, index_name, constraint_name, constraint_primary (boolean),
> constraint_unique (boolean), index_ascending (boolean)
>   
may be, but I think, that IsPrimary, IsUnigue, IsAscending would better 
names

> ... we could add index_unique and constraint_check later/when needed
>
>   
>> for stUserNames : CatalogName, SchemaName
>> (used by GetSchemaNames)
>>     
> Ok, could be added; suggest recno (object identifier: integer),
> catalog_name and schema_name as column names for consistency with the
> existing code.
>   
ok
> >From Michael's post:
>   
>> stPRocedureParams: get the parameters of a stored procedure
>>     
> Suggestion: hijack/adapt PostgreSQL's method - probably quite close to ISO:
> http://www.postgresql.org/docs/current/static/infoschema-parameters.html
> (cross checked with IBM iSeries/AS400 DB2 at
> http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstcatalogans.htm#catparameters)
>
> recno (object identifier: integer), catalog_name, schema_name,
> ordinal_position (integer), parameter_mode ('IN'/'OUT'/'INOUT'),
> parameter_name, data_type (varchar; probably db dependent for blobs etc?)
>   
ok
> .... we might add CHARACTER_MAXIMUM_LENGTH and/or CHARACTER_OCTET_LENGTH
> and/or NUMERIC_PRECISION, and character set details, but I think that
> might be going too far...
>   
we do not must add things, what nobody need ;-)

>   
>> stPackages: list packages (Oracle and Firebird) 
>>     
> Oracle info:
> adapted from:
> http://www.oracleappsqueries.com/list-all-invalid-packages/
> select  object_id
>         ,object_name
> from    all_objects
> where   object_type='PACKAGE' -- original also had PACKAGE BODY, which
> will get us duplicates?
> ... also available is owner etc.
> Perhaps something like
> recno,catalog_name,schema_name,package_name
> ?
> I'll leave that to the Oracle experts..
> IIRC, Firebird packages are planned for Firebird 3.0; haven't seen any
> documentation on it yet.
>
> Plans
> =====
> I'll focus on getting lazdatadesktop/datadict support for MSSQL/Sybase
> running first; afterwards we can look at the things we can add for other
> databases
Yes IMO there is worth add only such things, which are usable/doable at 
least in 2-3 sql connectors
So please check if your changes can be done also in any other 2 
connectors and if it will not lead to very complicated queries against 
system catalogs.

Note INFORMATION_SCHEMA are supported by:
MSSQL: http://msdn.microsoft.com/en-us/library/ms186778.aspx
MySQL: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
PostgreSQL: 
http://www.postgresql.org/docs/9.0/interactive/information-schema.html

>  and functionality (e.g. just getting a list of tables instead
> of always having to run queries might be nice functionality).
>
> Because documentation is lacking, I propose annotating sqldb.pp to the
> effect that the Interbase/Firebird implementation is the reference
> implementation.
> Then in the GetSchemaInfoSQL function in  ibconnection.pp, indicate what
> the queries do, and what they return (refer to column names being
> similar or the same as information_schema in SQL ISO standard, but has
> deviations).
> Also indicate rec_no refers to a unique database-specific identifier -
> if available - that can be used in further querying the metadata (e.g.
> object_id in MS SQL server, or the id columns in Firebird rdb$....
> system tables).
>
> Of course, further comments/suggestions/flames welcome ;)
>
> Thanks a lot Laco & Michael,
>
>   
-Laco.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20120419/4059acce/attachment.html>