SQLDB GetSchemaInfoSQL for indexes etc?

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

SQLDB GetSchemaInfoSQL for indexes etc?

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

michael.vancanneyt


On Tue, 17 Apr 2012, Reinier Olislagers wrote:

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

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

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

It would help, yes.

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

Reinier Olislagers
On 17-4-2012 10:41, [hidden email] wrote:

> On Tue, 17 Apr 2012, Reinier Olislagers wrote:
>> In sqldb, this:
>> type TSchemaType = (stNoSchema, stTables, stSysTables, stProcedures,
>> stColumns, stProcedureParams, stIndexes, stPackages);
>> is used in the GetSchemaInfoSQL function:
>> Does anybody know what stIndexes, stProcedureParams and stPackages
>> should return and when they would be used?
>
> stIndexes: get a list of indexes from a table.
> stPRocedureParams: get the parameters of a stored procedure
> stPackages: list packages (Oracle and Firebird)

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

Reinier Olislagers
In reply to this post by Reinier Olislagers
On 17-4-2012 10:36, Reinier Olislagers wrote:

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

michael.vancanneyt


On Tue, 17 Apr 2012, Reinier Olislagers wrote:

> On 17-4-2012 10:36, Reinier Olislagers wrote:
>> 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:
> 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.

Presumably, yes.

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

Michael.
_______________________________________________
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 michael.vancanneyt

>>
>> type TSchemaType = (stNoSchema, stTables, stSysTables, stProcedures,
>> stColumns, stProcedureParams, stIndexes, stPackages);
>> is used in the GetSchemaInfoSQL function:
>>
>> Does anybody know what stIndexes, stProcedureParams and stPackages
>> should return and when they would be used?
>
> stIndexes: get a list of indexes from a table.
> stPRocedureParams: get the parameters of a stored procedure
> stPackages: list packages (Oracle and Firebird)
>
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.

_______________________________________________
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


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

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

>
> Does it perhaps follow ODBC conventions or something?
>  
or SQL-Standard INFORMATION_SCHEMA views ;-)
L.

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

Reinier Olislagers
On 17-4-2012 13:33, LacaK wrote:
>
>>
>> Does it perhaps follow ODBC conventions or something?
>>  
> or SQL-Standard INFORMATION_SCHEMA views ;-)
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 ;)

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

Reinier Olislagers
In reply to this post by LacaK
On 17-4-2012 12:58, LacaK wrote:
>> ... assuming SchemaObjectName would be used similar to the existing code
>> and would specify table name (stIndexes)/stored proc name
>> (stProcedureParams)?
> See
> http://docwiki.embarcadero.com/VCL/XE2/en/SqlExpr.TCustomSQLDataSet.SetSchemaInfo
Thanks Laco, very helpful.

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

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

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

> 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
> _______________________________________________
> fpc-pascal maillist  -  [hidden email]
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>
>  

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

Reinier Olislagers
Very helpful, thanks Laco.

Regards,
ReinierOn 18-04-12 08:27 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.

> 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
> _______________________________________________
> fpc-pascal maillist  -  [hidden email]
> http://lists.freepascal.org/mailman/listinfo/fpc-pascal
>
>

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

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

Reinier Olislagers
In reply to this post by LacaK
On 18-4-2012 8:27, 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')
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
_______________________________________________
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?

michael.vancanneyt


On Thu, 19 Apr 2012, Reinier Olislagers wrote:

> On 18-4-2012 8:27, 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')
> 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).

Getting a list of tables is already part of TSQLConnection ?

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

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

Reinier Olislagers
On 19-4-2012 9:41, [hidden email] wrote:
> On Thu, 19 Apr 2012, Reinier Olislagers wrote:
>> 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).
>
> Getting a list of tables is already part of TSQLConnection ?
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

>> 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).
>
> 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)
... 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
_______________________________________________
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):
On 18-4-2012 8:27, 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')
    
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.

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


  
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

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

  
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.
  
yes
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)
  
may be, but I think, that IsPrimary, IsUnigue, IsAscending would better names

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

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

 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,

  
-Laco.


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

Reinier Olislagers
On 19-4-2012 11:14, LacaK wrote:

> Reinier Olislagers  wrote / napísal(a):
>> On 18-4-2012 8:27, LacaK wrote:
>>> 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)
>>  
> may be, but I think, that IsPrimary, IsUnigue, IsAscending would better
> names
;) 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...

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

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

> 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
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
_______________________________________________
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
Reinier Olislagers  wrote / napísal(a):
On 19-4-2012 11:14, LacaK wrote:
  
Reinier Olislagers  wrote / napísal(a):
    
On 18-4-2012 8:27, LacaK wrote:
      
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)
  
      
may be, but I think, that IsPrimary, IsUnigue, IsAscending would better
names
    
;) 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...

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

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?
As far as I can test in Delphi also indexes are returned (then column ConstraintName is empty)

 The presence of IsAscending would suggest it is meant for
all indexes as there is no such thing as an ascending constraint...

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

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

  
ok

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

  
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
    
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 do not think, that we must care about permissions

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

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).
  
Yes good point.

-Laco.


_______________________________________________
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):
On 18-4-2012 8:27, 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')
    
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.

  
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.


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

Reinier Olislagers
On 19-4-2012 13:28, LacaK wrote:

> Reinier Olislagers  wrote / napísal(a):
>> On 18-4-2012 8:27, 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')
>>>    
>> 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.
>>
>>  
> 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
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
123