SQLDB GetSchemaInfoSQL for indexes etc?

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

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

Michael Van Canneyt


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 ;)
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.
_______________________________________________
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 : [fpc-pascal] Re: RE : RE : Re: SQLDBGetSchemaInfoSQLforindexesetc?

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

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 : RE : [fpc-pascal] Re: RE : RE : Re: SQLDBGetSchemaInfoSQLforindexesetc?

Michael Van Canneyt


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 ?

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 : RE : [fpc-pascal] Re: RE : RE : Re:SQLDBGetSchemaInfoSQLforindexesetc?

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

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

LacaK
In reply to this post by Reinier Olislagers
Reinier Olislagers  wrote / napísal(a):
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 ;-)
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.


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

LacaK
In reply to this post by Reinier Olislagers
Reinier Olislagers  wrote / napĂ­sal(a):

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

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