Re: RE : RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDB GetSchemaInfoSQLforindexesetc?
On Thu, 19 Apr 2012, Ludo Brands wrote:
>>> 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.
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.
RE : RE : RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDBGetSchemaInfoSQLforindexesetc?
> > 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.
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.
Re: RE : RE : RE : RE : [fpc-pascal] Re: RE : RE : Re: SQLDBGetSchemaInfoSQLforindexesetc?
On Thu, 19 Apr 2012, Ludo Brands wrote:
>>> 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.
> Exactly because indexes and foreign keys have also an owner schema
> (CONSTRAINT_SCHEMA), I overlooked datadictionary.
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 ?
RE : RE : RE : RE : RE : [fpc-pascal] Re: RE : RE : Re:SQLDBGetSchemaInfoSQLforindexesetc?
> > Exactly because indexes and foreign keys have also an owner schema
> > (CONSTRAINT_SCHEMA), I overlooked datadictionary.
> 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 ?
Sorry. I have been working on ODBC drivers for a long time and the
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'.
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,
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 ;-)
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.
> 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.
ok so at this phase no changes in sqldb TSQLConnection and descendants
> 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.
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,
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)