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

[michael.vancanneyt at wisa.be]

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.