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 _______________________________________________ fpc-pascal maillist - fpc-pascal@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-pascal
