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

[michael.vancanneyt at wisa.be]

On Thu, 19 Apr 2012, Reinier Olislagers wrote:

> 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.
>
>> 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
>
>> 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.
> 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)
> ... 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.
>
>> 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?)
> .... 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...
>
>> 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 and functionality (e.g. just getting a list of tables instead
> of always having to run queries might be nice functionality).

Getting a list of tables is already part of TSQLConnection ?

>
> 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).

It might be better to put this in a README file in the sqldb directory.

Cluttering the source with documentation is not done. (well, not in FPC)

Michael.