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.
*yes
*
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.
*in principle I am not against it. Let's use DB specific views if they
are there, but use COLUMN NAMING compatible with sql standard
(like Reinier mentioned here:
http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization
)
*
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!
*Yes it will be more complicated.
I did short comparasion in Delphi and in case of BDE table names are
returned as "schema.table" in case of ADO,DBX only "table" without
schema is returned. So again inconsistency between various DB client
technologies.
If others agree, IMO we can do it like this:
1. use PG specific system view query, where we add SCHEMA_TABLE_NAME
column (other columns like SCHEMA_NAME, TABLE_NAME, TABLE_TYPE etc
remains) which will be as you suggested: schemaname||'.'||relname
2. override for TPQConnection.GetTableNames:
GetDBInfo(stTables,'','SCHEMA_TABLE_NAME',List)
-Laco.
*
(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
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
http://lists.freepascal.org/mailman/listinfo/fpc-pascal
