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