[fpc-devel] Questions regarding PostgreSQL support in SqlDB

[Graeme Geldenhuys]

Hi,

I have recently been playing around with PostgreSQL and SqlDB. I seem to have quite a few problems. tiOPF's test suite normally gives database components a good workout and currently SqlDB + PostgreSQL has about 40 tests failing out of 160 tests. SqlDB + Firebird does much better, but still not a 100% success rate. I hope to resolve all of these.

Here are a few questions regarding the PostgreSQL support in SqlDB:

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


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



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?


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.


As I work through the rest of the code, I'll make notes and report more findings here.


 
Regards,
 - Graeme -

________________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/