[fpc-devel] Questions regarding PostgreSQL support in SqlDB

[Joost van der Sluis]

Op zaterdag 08-08-2009 om 19:03 uur [tijdzone +0200], schreef Graeme
Geldenhuys:

> 1..)
> In the function TPQConnection.GetSchemaInfoSQL(..) there is the following SQL statements.
> * First off, this statement doesn't return a single row when I run it directly in psql or pgAdmin III.

> * Why are so many fields got the value 0. For example the system table "pg_attribute" contains most of the information for the fields that return 0. Fields like type, datatype, scale, length, etc...

The output of the query has to the same as for the other databases. So
that's why several 'empty/0' fields are there. And for type/datatype the
value in pg_attribute could have to be translated to the right value.

But it's this way because nobody looked at it before, and all those
things like column_datatype are effectively used by no-body, afaik. But
patches are welcome. (The only reason this function is implemented at
all is that it is used by Connection.getTableNames and .GetFieldNames.)

> ============================================
>     stColumns    : s := 'select '+
>                           'a.attnum                 as recno, '+
>                           '''''                     as catalog_name, '+
>                           '''''                     as schema_name, '+
>                           'c.relname                as table_name, '+
>                           'a.attname                as column_name, '+
>                           '0                        as column_position, '+
>                           '0                        as column_type, '+
>                           '0                        as column_datatype, '+
>                           '''''                     as column_typename, '+
>                           '0                        as column_subtype, '+
>                           '0                        as column_precision, '+
>                           '0                        as column_scale, '+
>                           'a.atttypmod              as column_length, '+
>                           'not a.attnotnull         as column_nullable '+
>                         'from '+
>                           ' pg_class c, pg_attribute a '+
>                         'WHERE '+
>                         // This can lead to problems when case-sensitive tablenames are used.
>                           '(c.oid=a.attrelid) and (a.attnum>0) and (not a.attisdropped) and (upper(c.relname)=''' + Uppercase(SchemaObjectName) + ''') ' +
>                         'order by a.attname';
> 
> ============================================
> 
> Instead of the above query, why not use the Information Schema views to pull that information out in a much more friendly manner. Here is a quick example...
> 
> ============================================
> SELECT ordinal_position,            
>        column_name,                 
>        data_type,                   
>        column_default,              
>        is_nullable,                 
>        character_maximum_length,    
>        numeric_precision            
>     FROM information_schema.columns 
>    WHERE table_name = 'test_table'
> ORDER BY ordinal_position
> ============================================

This probably didn't exist when this code was written (postgres 5) and
the information_schema is probably just a view which references to
pg_attribute...

> 2..)
> Then in function TPQConnection.TranslateFldType(..) we have the following lines...
> 
>     Oid_text               : Result := ftBlob;
>     Oid_Bytea              : Result := ftBlob;
> 
> Shouldn't Oid_text return ftMemo instead of ftBlob?

Could be. In fact, Bytea is not a blob field. So to support blob-fields,
Text was abused. Which is also wrong. But Text isn't memo either.
Actually it's a varchar....

Base problem is that Postgres has a different idea of field-types then
the databases you are used to use. (For blob-fields Postgresql ask you
to use a plain number-field in which you store the blob-id, and then use
seperate functions to retrieve the blob-data. Problem is that sqldb can
never map correctly to those blob-fields, because you can never know if
a number-field actually contains a reference to a blob-field)

But before my vacation i wrote something about making this mapping
adjustable. That's still one my investigation list.

> 3..)
> In procedure TPQConnection.PrepareStatement(..) there is a const TypeStrings being setup. Many of those entries show "unknown" when in fact they could probably have PostgreSQL types associated instead. The 16th and 18th item (counting starts at 1) could most probably be "bytea" instead of "unknown". There are a few others as well.

Probably they are not used at all, so why bother. ;)

But please create bug-reports, preferrably with actual user-case
problems, not only based on the code, but also show the results. That
way we can find some way to be as compatible possible with other
databases like Firebird.

Joost.