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

[Reinier Olislagers]

On 19-4-2012 11:14, LacaK wrote:
> Reinier Olislagers  wrote / napísal(a):
>> On 18-4-2012 8:27, LacaK wrote:
>>> 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 can fight^H^H^H talk about that ;) The reason why I chose these is
that they match the %object%_%property% naming convention in the other
queries...

Also a problem is that you can have both a unique constraint and a
unique index... Of course, implementing the constraint would probably be
done by the index... but an index without a constraint could also be
possible.
Is this function meant to show all indexes or only indexes meant for
constraints? The presence of IsAscending would suggest it is meant for
all indexes as there is no such thing as an ascending constraint...

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

>> 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.
It will certainly be doable. I know enough about Sybase, MS SQL Server,
Firebird that I think it will quite likely work.
PostgreSQL, Oracle probably also. Mysql: could well be.

Also, I propose to return NULL in case a column value is not supported
by the database driver... this will allow column order to remain and
future improvements to be made

BTW, IMO, complicated queries does not matter that much as long as it is
annotated why the query was chosen (i.e. link to db reference, including
version number).

> 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
Yep.
Firebird, Sybase and Oracle have system catalogs/system views/system
tables that provide similar info (as the ones you mentioned probably
also have - don't know about MySQL).

Handy comparison between proprietary tables and information_schema approach:
Firebird:
http://www.alberton.info/firebird_sql_meta_info.html
MS SQL:
http://www.alberton.info/sql_server_meta_info.html
Oracle:
http://www.alberton.info/oracle_meta_info.html
PostgreSQL:
http://www.alberton.info/postgresql_meta_info.html

One thing I noticed in the information_schema approach: you won't get
info about objects you don't have access to.
So you won't get procedures you can't execute etc.

Don't know how that works with the current implementation (i.e.
Delphi+DbExpress)? FPC Firebird connector will happily return ALL
objects, regardless of permissions, if I understand the query correctly.

I don't think this is a problem though as having info on something you
have no permissions to doesn't seem like a useful concept.
If agreed, sometime in future I'll probably rewrite the MS SQL queries
to use INFORMATION_SCHEMA... but will have to use existing approach
(with the sysobjects table) for Sybase, as it doesn't support
information_schema...

In fact, currently GetSchemaInfoSQL returns an SMetadataUnavailable
error inTSQLConnection; I might be able to write up the
information_schema approach which would be directly usable by compatible
databases... (Excepting the package stuff... I'll just return the error
there).

Regards,
Reinier