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

[LacaK]

Reinier Olislagers  wrote / napísal(a):
> 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...
>
>   
ok, my approach is when not implemented then be compatible with Delphi, 
but also your proposal is acceptable for me ;-)

> 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?
As far as I can test in Delphi also indexes are returned (then column 
ConstraintName is empty)

>  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
>
>   
ok

> 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 do not think, that we must care about permissions

> 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...
>   
Exactly. Due to compatibilty with Sybase we must use sys* tables
(drawback is that, you need CatalogName and SchemaName, which are not in 
sysobjects, so you must use DB_NAME() and join to sysusers)

> 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).
>   
Yes good point.

-Laco.

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