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

[michael.vancanneyt at wisa.be]

On Thu, 19 Apr 2012, Reinier Olislagers wrote:

> On 19-4-2012 15:37, michael.vancanneyt at wisa.be 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.