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. _______________________________________________ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel