In sqldb, this:

type TSchemaType = (stNoSchema, stTables, stSysTables, stProcedures, stColumns, stProcedureParams, stIndexes, stPackages); is used in the GetSchemaInfoSQL function:

I noticed stIndexes, stProcedureParams and stPackages do not seem to be used in the current sqldb connectors in packages\fcl-db\src\sqldb\ and below.

The others are used in GetSchemaInfoSQL to get an SQL command that returns the names of the objects (tables etc): e.g. tables: Firebird:
s := 'select '+
'rdb$relation_id as recno, '+
'''' + DatabaseName + ''' as catalog_name, '+
''''' as schema_name, '+
'rdb$relation_name as table_name, '+
'0 as table_type '+
'from '+
'rdb$relations '+
'where '+
'(rdb$system_flag = 0 or rdb$system_flag is
null) ' + // and rdb$view_blr is null

                        'order by rdb$relation_name';

MS SQL Server:
Result := format(SCHEMA_QUERY, ['table_name','U']);

Does anybody know what stIndexes, stProcedureParams and stPackages should return and when they would be used?

What happens e.g. with indexes - that IIRC can have unique names per database (Firebird,...) or per table (MS SQL Server,...)?

I am trying to see if having a list of indexes in the database connectors would help with the data dictionary
(packages\fcl-db\src\datadict)...

Thanks,
Reinier

stIndexes: get a list of indexes from a table.
stPRocedureParams: get the parameters of a stored procedure stPackages: list packages (Oracle and Firebird)

It would help, yes.

Michael.

Thanks Michael.
Function signature is
GetSchemaInfoSQL(SchemaType : TSchemaType; SchemaObjectName, SchemaPattern : string) : string;
... assuming SchemaObjectName would be used similar to the existing code and would specify table name (stIndexes)/stored proc name (stProcedureParams)?

I believe package names are unique within dbs in Oracle (don't know Firebird, IIRC, that's planned for the upcoming 3.0, right?), so no parameters required for stPackages?

SchemaPattern doesn't seem to be used; perhaps meant as some kind of filter to limit the results?

I'll implement at least stIndexes for MS SQL Server, perhaps ProcedureParams as well..
Later on the same for Sybase, perhaps Firebird.

Thanks,
Reinier

const SCHEMA_QUERY='select name as %s from sysobjects where type=''%s''
order by 1';

Result := format(SCHEMA_QUERY, ['table_name','U']);

PostgreSQL:

    stTables     : s := 'select '+
                          'relfilenode              as recno, '+
                          '''' + DatabaseName + ''' as catalog_name, '+
                          '''''                     as schema_name, '+
                          'relname                  as table_name, '+
                          '0                        as table_type '+
                        'from '+
                          'pg_class '+
                        'where '+
                          '(relowner > 1) and relkind=''r''' +
                        'order by relname';

Ooops, comparing these, it seems there is some kind of standard result set that the MSSQL Connector does not follow as it returns only a single name column.
I'll update the mssqlconn connector when I get to it.

Is there any further documentation on the required output, e.g. the table_type? recno presumably is unique id, catalog_name, schema_name are presumably ISO catalog/schema, table_name is the name of the table.

Does it perhaps follow ODBC conventions or something?

Thanks,
Reinier

Presumably, yes.

Joost should be able to answer these questions in more detail.

Michael.

See also http://docwiki.embarcadero.com/VCL/en/SqlExpr.TSchemaType Where also stUserNames is added (may be, that this would be good add also in FPC)

And Delphi supports also (beside GetTableNames, GetProcedureNames, GetFieldNames)
GetIndexNames
http://docwiki.embarcadero.com/VCL/XE2/en/SqlExpr.TSQLConnection.GetIndexNames and others like GetPackageNames, GetProcedureParams (where implementation across SQL-connectors may be problematic)

L.

See
http://docwiki.embarcadero.com/VCL/XE2/en/SqlExpr.TCustomSQLDataSet.SetSchemaInfo

SchemaPattern doesn't seem to be used; perhaps meant as some kind of filter to limit the results?

Yes

I'll implement at least stIndexes for MS SQL Server, perhaps

It will be good do some comparasion tests with DbExpress (how names results columns , which columns are returned etc.) http://docwiki.embarcadero.com/RADStudio/XE2/en/Fetching_Metadata_into_a_dbExpress_Dataset

L.

Does it perhaps follow ODBC conventions or something?

or SQL-Standard INFORMATION_SCHEMA views ;-)
L.

While I know that standard exists - I can tell without looking it up that the integer table_type column we're using is a vendor specific extra - that's how good my crystal ball is.
Of course, sometimes the ball is wrong and I get the blame ;)

Thanks Laco, very helpful.

Browsing through those docs, they don't seem to tell you WHAT columns exactly they return so perhaps that's left up to the implementer. Or am I once again looking in the wrong place?
(I know ADO.Net has a similar schema functionality that provides a bare minimum - even less than that - and each driver builder can add their own extensions - great fun).

I don't have Delphi with dbExpress here, so can't test.

Anyway, I think I have some draft SQL standard in PDF lying around somewhere - I'll try and dig it up and see what INFORMATION_SCHEMA views should throw up.

Thanks,

regards,
Reinier

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')
(in FPC (for IBConnection) we have catalog_name, schema_name, table_name, table_type)

for stProcedures : CatalogName, SchemaName, ProcedureName, ProcedureType ('PROCEDURE')
(in FPC (for IBConnection) we have catalog_name, schema_name, proc_name, proc_type and others)

for stColumns : CatalogName, SchemaName, TableName, ColumnName, TypeName ('char', 'integer' etc.), Precision, Scale, Ordinal, DefaultValue, IsNullable, IsAutoincrement, and others.

for stIndexes : CatalogName, SchemaName, TableName, IndexName, ConstraintName, IsPrimary, IsUnique, IsAscending
(in list are included also PRIMARY KEYs and UNIQUE constraints)

for stUserNames : CatalogName, SchemaName
(used by GetSchemaNames)

HTH
L.

Very helpful, thanks Laco.

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')
(in FPC (for IBConnection) we have catalog_name, schema_name, table_name, table_type)

for stProcedures : CatalogName, SchemaName, ProcedureName, ProcedureType ('PROCEDURE')
(in FPC (for IBConnection) we have catalog_name, schema_name, proc_name, proc_type and others)

for stColumns : CatalogName, SchemaName, TableName, ColumnName, TypeName ('char', 'integer' etc.), Precision, Scale, Ordinal, DefaultValue, IsNullable, IsAutoincrement, and others.

for stIndexes : CatalogName, SchemaName, TableName, IndexName, ConstraintName, IsPrimary, IsUnique, IsAscending
(in list are included also PRIMARY KEYs and UNIQUE constraints)

for stUserNames : CatalogName, SchemaName
(used by GetSchemaNames)

HTH
L.

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

Getting a list of tables is already part of TSQLConnection ?

It might be better to put this in a README file in the sqldb directory.

Cluttering the source with documentation is not done. (well, not in FPC)

Michael.

Mmm yes... you're right: GetTableNames, GetProcedureNames, GetFieldNames are already there.
As Lacak mentioned though:
GetIndexNames, GetPackageNames, GetProcedureParams http://docwiki.embarcadero.com/VCL/XE2/en/SqlExpr.TSQLConnection_Functions

... thanks for the gentle reminder there ARE other ways out there ;) I hear & obey ;)

Not that it won't be slightly ridiculous to refer to ibconnection.pp in the readme but well... better some documentation than none... Actually, if I can get a sensible patch into the fpdocs XML file, I'll do that... at least there will be some use for normal end users so they know more or less what the function is actually supposed to be used for....

Thanks,
Reinier

Reinier Olislagers wrote / napísal(a):

ok also MS SQL Server: http://msdn.microsoft.com/en-us/library/ms186224.aspx

also MS SQL http://msdn.microsoft.com/en-us/library/ms188757.aspx

yes

may be, but I think, that IsPrimary, IsUnigue, IsAscending would better names

ok

ok

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

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.

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

-Laco.

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

Agreed ;)

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

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

Reinier Olislagers wrote / napísal(a):

ok, my approach is when not implemented then be compatible with Delphi, but also your proposal is acceptable for me ;-)

As far as I can test in Delphi also indexes are returned (then column ConstraintName is empty)

ok

I do not think, that we must care about permissions

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)

Yes good point.

-Laco.

Reinier Olislagers wrote / napísal(a):

Thinking about column names I would suggest, change it according to SQL-Standard (information_schema views). To be fully compatible. (because ATM we are not compatible with SQL-Standard NOR Delphi) Advantage will be, that we will be able do for example 'select * from INFORMATION_SCHEMA.TABLES'
so if some DB provides also other columns (additional information on tables, procedures, indexes, columns etc.) we will get it. It will of course lead to breaking backward compatibility, but I think, that there is very, very (if any) small amount of users, which use setschemainfo+open to get metadata information.

L.

Agreed that renaming the stuff to ISO makes sense.... will however be a bit more problematic with indexes, after my brief glance at the specs.

grep -i --recursive SchemaInfo * | grep -v .svn | more In FPC:
=> no code other than the db connectors
In Lazarus:

3rd party code: might be some.

Will shelve this for the rewrite after the lazdatadesktop work for MS SQL Server...

Regards,
Reinier

I completely agree. Delphi isn't that consistent with itself neither. Just take a look at the schema info returned by dbado and you'll get fe. for siTables the columns TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME and TABLE_TYPE.

Going one step further I would not bother about stIndexes in favor of table_constraints, referential_constraints,key_column_usage, constraint_column_usage and constraint_table_usage which is also iso. The info contained in these views can't be combined in one stIndexes result set. Advantage is also that adhering to these standard views, as noted earlier, can be just a 'select * from INFORMATION_SCHEMA.xxx' for those db's that support INFORMATION_SCHEMA. For those that don't, there are enough queries around that map to INFORMATION_SCHEMA or to the very similar ODBC equivalents.

Ludo

Reinier Olislagers wrote / napísal(a):

Yes, I would suggest for stIndexes:

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, index_name, CONSTRAINT_NAME,
+ optionaly: constraint_primary (boolean), constraint_unique (boolean), index_ascending (boolean), ...

L.

Sounds good.

Ludo Brands wrote / napísal(a):

ok good, it seems, that there are at least 3 votes for changing it to sql compliant

Ludo here I do not understand what do you want to say. may be, that my english is not so good ;-)
Can you explain please what is your proposal regarding to stIndexes ?

L.

Ludo here I do not understand what do you want to say. may be, that my

english is not so good ;-)

Can you explain please what is your proposal regarding to stIndexes ?

stIndexes is currently not implemented: keep it that way (or drop it) but add and implement stTableConstraints, stReferentialConstraints, stKeyColumnUsage,
stConstraintColumnUsage and stConstraintTableUsage. And why not some other missing information_schema views like 'views' or 'schemata'.

Delphi compatibility? Delphi adodb defines and implements the following: type TSchemaInfo = (siAsserts, siCatalogs, siCharacterSets, siCollations, siColumns, siCheckConstraints, siConstraintColumnUsage, siConstraintTableUsage, siKeyColumnUsage, siReferentialConstraints, siTableConstraints, siColumnsDomainUsage, siIndexes, siColumnPrivileges, siTablePrivileges, siUsagePrivileges, siProcedures, siSchemata, siSQLLanguages, siStatistics, siTables, siTranslations, siProviderTypes, siViews, siViewColumnUsage, siViewTableUsage, siProcedureParameters, siForeignKeys, siPrimaryKeys, siProcedureColumns);

Ludo

Fine with Ludo's proposal; dropping stIndexes... and adding new ISO compliant stuff if needed.
Delphi has at least adodb and dbexpress with various implementations.... so not much of a standard.

Keeping to the information_schema standard seems like a good idea - especially because it will make it easier to easily get useful info from an ISO SQL 92+ compliant database..

Anybody against this? Michael? Joost?
Has anybody used this functionality in sqldb at all?

Regards,
Reinier

Not against.

Has anybody used this functionality in sqldb at all?

No. For a simple reason:

I implemented all this information in fpdatadict;
I think it belongs more there, and definitely not in the basic data API.

Michael.

I know you put stuff there... ATM there is some overlap between the two. I don't mind just leaving sqldb alone and just working with fpdatadict & the fpdd* database specific code... but it's a good idea if we agree where/if we need to split things.

This:

... does confuse me a bit though.

Could you tell me your thoughts on the way you see the split (if any)? Thanks,
Reinier

It's a historic issue:

I think fpdatadict was historically implemented first. It was implemented separately because I do not believe this belongs in sqldb. For instance, the data dictionary also supports DBF files.

Obviously Joost thought otherwise, and started a parallel implementation in sqldb. I cannot undo that (unless Joost agrees, of course).

Now, supposing it must remain in sqldb:

I do not know if the calls for schema information will provide all info that datadict needs. If they do not, then I must re-implement them in datadict. If they do, then the default fpdatadict information retrieval routines can use the new schema calls.

Unfortunately, it would take some time to investigate how much it overlaps.

The bottom line is that I still think that this kind of info belongs in fpdatadict, which is broader in scope than sqldb (you could use it in zeos, for instance). But if it is available from sqldb, then fpdatadict can of course reuse that.

Michael.

Hi Admin, i hope its not forbidden here, otherwise please delete this mail.

Hi all,

we are a 9 person company looking for
a hardware-near software engineer or programmer
or a
software-near electronic designer.

for embedded systems and HTML based user interfaces.

We are developing and producing material testing systems.

Most of our firmware is PASCAL, thats the reason why we are searching here. But also Perl and a little bit C as well as Assembler. Maybe Erlang and Python as well as Forth in the future...

More infos about our company at:

http://www.schleibinger.com

We are located in a small village in the very green hart of Upper-Bavaria.

If you are interested please write an e-mail to me: greim(a)schleibinger.com

Kind Regards

Markus Greim

Schleibinger Geräte
Teubert u. Greim GmbH
Gewerbestrasse 4
84428 Buchbach
Germany

http://www.schleibinger.com

Court having jurisdiction:
Amtsgericht Traunstein HRB 9646
CEO:
Dipl.-Ing. (FH) Oliver Teubert
Dipl.-Ing. (Univ.) Markus Greim
VAT-ID: DE 174 175 046

Some of the metadata are necessary in the basic data API (tables, columns, indices) and are a partial overlap with the TFPDDEngine descendants. >From an abstraction perspective, I think it is better to have database specifics as much as possible in one location and use standards as much as possible when specifying metadata.

To be honest, I just discovered fpdatadict. Probably the missing implementation of a lot of the databases is one of the reasons why I haven't noticed it before.
First impression is that the fpdatadict implementation makes sense but unfortunately the properties used are perhaps close to one particular database but are insufficient or confusing for other databases (no reference to schema or catalog, NUMERIC_SCALE, length vs. octet_length, collation,...).

Ludo

The data dictionary is an extension of what existed in the BDE times in Borland. It also featured a data dictionary but for some reason, Borland abandonded it (never understood why, as it is a good idea).

This explains why it uses in large part that terminology. Other parts are based on firebird terminology for the simple reason that I use that database for all my projects. Even the database diff is in production use.

As for re-using existing terminology (schema data etc.), this is dangerous as it creates the expectation that the implementation conforms to a certain standard, which is what I want to avoid.

(I don't believe I've ever used the word schema in connection to a database. I think of an electrical wiring blueprint if I hear that word ;) )

But adding some aliases and add new features such as octet_length should not be a problem (NUMERIC_SCALE exists in precision).

Michael.

Yes; at first look, the datadict approach seems more object oriented and polished to me.

Or... part in datadict and part in sqldb. Either case is of course messy.

If they do, then the default fpdatadict information retrieval routines can use the new schema calls. Unfortunately, it would take some time to investigate how much it overlaps.

Understood.

The bottom line is that I still think that this kind of info belongs in fpdatadict, which is broader in scope than sqldb (you could use it in zeos, for instance). But if it is available from sqldb, then fpdatadict can of course reuse that.

Datadict seems a nice object oriented wrapper; having some lower level functions in sqldb that datadict calls might not be as bad as it sounds... of course, if you want to be able to plug in other database drivers then the picture changes.
I would agree with you though that the functionality would be more or less duplicated AFAICT.... without any obvious benefit.

The downside could be that people that are used to other drivers might expect the sqldb way of doing things. However, the apparent lack of users up to now (and lacklustre implementation in the drivers themselves) seems to suggest that would not be a big problem.

Proposal
========
1. As I'm interested in getting support for MS SQL Server and Sybase ASE into lazdatadesktop, I propose I'll go on with trying to make that work using the current sqldb structure. This will mean that a lot of code will go into new datadict fpddmssql.pp and fpddsybase.pp modules. I'll submit patches when done.
2. With that experience, I might have a better idea whether extending/changing sqldb with ISO information_schema could easily work for datadict.... however, I must say your argument re other db adapters does make a lot of sense.

If so, I'll convert lazdatadesktop and the mssqlconn sqldb connector, breaking compatibility.
Next, I'll convert Firebird sqldb to use the new approach. If those work and are acceptable, I can submit a patch for the other connectors (Oracle, PostgreSQL, mysql)... but will probably need some support for that.

If not, I can try and adapt fpdatadict.pp and their dependents to use information_schema calls in e.g. ImportIndexes in order to make a default implementation for ISO compatible RDBMS... which non-compatible sqldb/dbf/zeos/whatever dbs will override...
I will just ignore sqldb; perhaps provide a patch for mssqlconn to at least let it spit out similar info as Firebird.

What do you guys think?

Reinier

Regards,
Reinier

See my previous message. From what I have seen so far, information_schema data provide more info than datadict represents.

Unfortunately, it would take some time to investigate how much it overlaps.

Yes

Zeos uses jdbc names for metadata which is very close, if not identical, to odbc names. It implements most if not all of the jdbc metadata calls. The TZSQLMetadata dataset descendant implements the following: mdProcedures, mdProcedureColumns, mdTables, mdSchemas,
mdCatalogs, mdTableTypes, mdColumns, mdColumnPrivileges, mdTablePrivileges,
mdBestRowIdentifier, mdVersionColumns, mdPrimaryKeys, mdImportedKeys, mdExportedKeys, mdCrossReference, mdTypeInfo, mdTriggers, mdIndexInfo, mdSequences,
mdUserDefinedTypes.

Ludo

I expect your patches on Monday :-)

Seriously: please go ahead. If you need help, just mail me.

I'll be glad to see fpdatadict support more engines; Maybe it will gain wider acceptance, and the same then for the lazarus data database desktop. I use it daily, but then, I use firebird...

Michael.

But adding some aliases and add new features such as octet_length should not be a problem (NUMERIC_SCALE exists in precision).

And where exist NUMERIC_PRECISION then? This is used in databases like Oracle that use numerics. A number(10,4) has precision 10 and scale 4, radix 10.

Ludo

fpdatadict uses the same name as the TField properties.

Size=4, Precision = 10.

Property Size : Integer Read FSize Write FSize Stored IsSizeStored;
Property Precision : Integer Read FPrecision Write FPrecision Stored IsPrecisionStored;

I agree that the names are confusing, but I blame Borland employees for that ;-)

Like I said: adding some aliases should take care of this.

Michael.

There are other databases (mssql, oracle) where it is difficult to not use schemas. So even when not using the word 'schema' the concept should be represented somewhere.

Ludo

Ehm, the datadictionary is the schema ?

Unless I misunderstand the meaning of schema :-)

Michael.

Aha. That's what you get when not adhering to standards ;)

Schemas "own" more objects than just Tables, Sequences and Domains. So I wouldn't have guessed that one.

Ludo

Touché :)

Schemas "own" more objects than just Tables, Sequences and Domains. So I wouldn't have guessed that one.

Correct. fpDatadict is a work in progress.

I use tables, sequences, domains, indexes and foreign keys.

I have not had the need for procedures, triggers and views yet.

Michael.

Exactly because indexes and foreign keys have also an owner schema (CONSTRAINT_SCHEMA), I overlooked datadictionary.

I have not had the need for procedures, triggers and views yet.

Ludo

I am not familiar with these terms, so I totally fail to understand the first part of your sentence...

Is there somewhere a reference about 'schema' data ?

Michael.

Sorry. I have been working on ODBC drivers for a long time and the terminology sticks.

Basically, every database object (with some exceptions) belongs to a schema. So TDDIndexDef and TDDForeignKeyDef should reference an owner. Since datadictionary isn't a property for these classes, it didn't jump out as an equivalent for 'schema'.

Some reference docs.
DB2 (db2 cli is the origin of odbc):
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fco m.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_schemaqualifiers.htm Mssql: http://msdn.microsoft.com/en-us/library/ms365789.aspx Oracle: http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm SQL92 standard: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt 4.11 SQL-schemas

Ludo

Reinier Olislagers wrote / napísal(a):

Not against ;-)
Small drawback will be, that for those DB that do not have INFORMATION_SCHEMA there will be need for adding many selects against system tables (or do not implement them at all ;-))

Has anybody used this functionality in sqldb at all?

No. And I do not expect that I will use it in future.

-Laco.

Reinier Olislagers wrote / napísal(a):

ok so at this phase no changes in sqldb TSQLConnection and descendants

1. As a minimum I would suggest change column names in existing queries to be compatible with SQL standards
Discussed earlier in this thread. This I would do immediately (independent of other changes).
2. Consider adding at least stSchemata (alias to stUserNames in Delphi)
3. for stTables, stColumns, stProcedures, stProcedureParams add in sqldb.pp default queries for INFORMATION_SCHEMA views TABLES, COLUMNS, ROUTINES, PARAMETERS
4. If somebody has interest add also other TSchemaTypes like Ludo wrote. (but for example for TODBCConnection we must use only existing API like SQLStatistics so we will not be able implement all)

L.