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

[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