Sorry I have taken a while to respond. Apparently my home server
(postgres, mail relay, dns, dhcp, http proxy etc) decided that my first
post to this list for probably a couple of years was a sufficiently
auspicious occasion to warrant a major dummy-spit, and I have only just
coaxed it back to life!
On 12/14/2012 09:53 PM, LacaK wrote:
Hi John,
Thus schema name is not selected for postresql, nor is it returned
for any connection. as only the field specified by AReturnField, in
this case table_name, is added to the output list. Note that
pg_class does not have an easy access to the schema/owner by name, it
would require a join on an oid.
Suggestions:
1) The pg database also has a view, pg_stat_user_tables, that is
simple and has the schema as well as the table name. There may be
some good reason for not using it that I am not aware of.
I think, that if this view(s) is(are) presented in all versions of
PostgreSQL which fcl-db is going to support, that it is no problem use
them
(I must note, that my preffered way is follow sql standard
INFORMATION_SCHEMA views at least in column naming)
The equivalent INFORMATION_SCHEMA view is "tables", but it does not
provide any easy way of distinguishing user tables from system tables.
It appears to me that it would be necessary to link back to "schemata"
and only select those schemas that were/were not owned by postgres. (Of
course, this would be OK if we were only selecting for a particular
schema.) If we were putting the code into TSQLConnection, it would be
worth the pain to use the standard INFORMATION_SCHEMA views, I guess,
but if it is done separately for each db connection, it seems it is just
as easy - and probably more reliable - to use the postgres view where
all is done for us.
2) The simple way to get the schemas would be to simply 'select ...
schemaname||'.'||relname as table_name ... from pg_stat_user_tables.
This would be a change ONLY to the sql in pqconnection. (If worst
comes to worst, I dare say could create my own pqconnection
derivative with this change.)
Hm, if you want get also schema_name then you should use this approach:
sqlquery1.SchemaType:=stTables;
sqlquery1.Open;
and in loop fill TStrings using
sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name')
At present, the schema isn't read into the query (so we have to change
the db connection), and the above code would have to be put into
TSQLConnecion, where (1) it would only apply to stTables, and (2) would
not work if any database species did not return the schema. Probably to
do this it would be necessary to be able to select multiple fields in
the AReturnField of GetDBInfo, all of which would be far more
complicated than what I suggested as a "simple" solution!
(This is before we start on the argument about whether it is better to
return multiple fields and concatenate them in the client, or select the
concatenated fields on the server in the first place, which I think is
quite debatable.)
3) A better solution, in my opinion, would be to add some extra
TSchemaTypes, with matching TSQLConnection calls, for GetSchemas,
GetTablesInSchema, and possibly the "TableBySchema" option as in
(2). It seems the "infrastructure" already exists to do all of these
things.
Personally I am not fan of this approach (mainly, because of keeping
Delphi compatibility).
So how does Delphi do it ? I can't imagine it returns all the table
names with no schema specified (as a filter) or prepended. It is a long
time since I used Delphi against a real database (Delphi 4 C/S against
Oracle) but I can't remember any of these problems. I have no argument
with matching Delphi behaviour if it is feasible, but by the same token
I don't see adding behaviour is a problem as long as existing behaviour
is not broken.
-Laco.
@Reinier:
Analysis:
As far as I can work out, a call to GetTableNames calls GetDBInfo, with
parameters
ASchemaType : TSchemaType - This specifies what info we want - user
tables, sys tables, procedures, columns etc
ASchemaObjectName - Doesn't seem to be used, it is specified as ""
No, it's not used for GetTableNames; it is used e.g. to specify the
table name if you want to know info about columns.
Yes, I saw that, but there seems no reason why it could not be used as a
schema selection.
I also had a flip through the previous discussion, but it was indeed a
long and tortuous thread to follow!
So the question comes back to "how should the table names be returned
?" Indeed, is there a specification for what should be returned for all
of these metadata queries ?
cheers,
John Sunderland
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
