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

[Reinier Olislagers]

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

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,

regards,
Reinier