[fpc-pascal] Re: SQLDB GetSchemaInfoSQL for indexes etc?
lacak at zoznam.sk
Thu Apr 19 13:04:07 CEST 2012
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
> ;) 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
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
> Is this function meant to show all indexes or only indexes meant for
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 ;)
>>> 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
>> 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
> 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:
> MS SQL:
> 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
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
Yes good point.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the fpc-pascal