SQLDB GetSchemaInfoSQL for indexes etc?

classic Classic list List threaded Threaded
46 messages Options
123
Reply | Threaded
Open this post in threaded view
|

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

Ludo Brands
> 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.
>
 
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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: SQLDB GetSchemaInfoSQL for indexes etc?

LacaK
In reply to this post by Reinier Olislagers
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.

  
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.

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

Reinier Olislagers
In reply to this post by Ludo Brands
On 19-4-2012 14:13, Ludo Brands wrote:

>> 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.
>>
>  
> 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.
Sounds good.

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

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

LacaK
In reply to this post by Ludo Brands
Ludo Brands  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.

    
 
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.
ok good, it seems, that there are at least 3 votes for changing it to sql compliant

 

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


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

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

Ludo Brands
 
>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



 


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

Reinier Olislagers
On 19-4-2012 15:02, Ludo Brands wrote:

>  
>> 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);  
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
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

michael.vancanneyt


On Thu, 19 Apr 2012, Reinier Olislagers wrote:

> On 19-4-2012 15:02, Ludo Brands wrote:
>>
>>> 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);
> 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?

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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

Reinier Olislagers
On 19-4-2012 15:37, [hidden email] wrote:

>
>
> On Thu, 19 Apr 2012, Reinier Olislagers wrote:
>
>> On 19-4-2012 15:02, Ludo Brands wrote:
>>>
>>>> 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);
>> 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?
>
> 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.

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:
>> 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)...
>
> It would help, yes.
>
> Michael.
... does confuse me a bit though.

Could you tell me your thoughts on the way you see the split (if any)?
Thanks,
Reinier
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

michael.vancanneyt


On Thu, 19 Apr 2012, Reinier Olislagers wrote:

> On 19-4-2012 15:37, [hidden email] wrote:
>>
>>
>> On Thu, 19 Apr 2012, Reinier Olislagers wrote:
>>
>>> On 19-4-2012 15:02, Ludo Brands wrote:
>>>>
>>>>> 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);
>>> 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?
>>
>> 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.
>
> 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:
>>> 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)...
>>
>> It would help, yes.
>>
>> Michael.
> ... does confuse me a bit though.
>
> Could you tell me your thoughts on the way you see the split (if any)?

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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Job offer East of Munich / Stellenangebot Landkreis Mühldorf Oberbayern

greim
In reply to this post by Reinier Olislagers
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
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexesetc?

Ludo Brands
In reply to this post by michael.vancanneyt

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

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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexesetc?

michael.vancanneyt


On Thu, 19 Apr 2012, Ludo Brands wrote:

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

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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

Reinier Olislagers
In reply to this post by michael.vancanneyt
On 19-4-2012 16:07, [hidden email] wrote:
>
> 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).
Yes; at first look, the datadict approach seems more object oriented and
polished to me.

> 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.
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
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexesetc?

Ludo Brands
In reply to this post by michael.vancanneyt
> 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.
>

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

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

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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : Re: SQLDB GetSchemaInfoSQL for indexes etc?

michael.vancanneyt
In reply to this post by Reinier Olislagers


On Thu, 19 Apr 2012, Reinier Olislagers wrote:

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

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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL forindexesetc?

Ludo Brands
In reply to this post by michael.vancanneyt

> 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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL forindexesetc?

michael.vancanneyt


On Thu, 19 Apr 2012, Ludo Brands wrote:

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

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.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL forindexesetc?

Ludo Brands
In reply to this post by michael.vancanneyt

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

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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQL forindexesetc?

michael.vancanneyt


On Thu, 19 Apr 2012, Ludo Brands wrote:

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

Ehm, the datadictionary is the schema ?

Unless I misunderstand the meaning of schema :-)

Michael.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

RE : RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQLforindexesetc?

Ludo Brands
> > 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.
>
> Ehm, the datadictionary is the schema ?
>
> Unless I misunderstand the meaning of schema :-)
>

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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
123