<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<br>
<blockquote cite="mid:50CD2392.2050703@netspace.net.au" type="cite">
<blockquote type="cite">
<blockquote type="cite"><br>
</blockquote>
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
<br>
(I must note, that my preffered way is follow sql standard
INFORMATION_SCHEMA views at least in column naming)
<br>
</blockquote>
The equivalent INFORMATION_SCHEMA view is "tables", but it does not
provide any easy way of distinguishing user tables from system tables.</blockquote>
<b>yes<br>
<br>
</b>
<blockquote cite="mid:50CD2392.2050703@netspace.net.au" type="cite">
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.</blockquote>
<b>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<br>
(like Reinier mentioned here:
<a class="moz-txt-link-freetext" href="http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization">http://wiki.lazarus.freepascal.org/Database_metadata#Proposal_for_extension.2Funiformization</a>
)<br>
<br>
</b>
<blockquote cite="mid:50CD2392.2050703@netspace.net.au" type="cite"><br>
<blockquote type="cite"><br>
<blockquote type="cite">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.)
<br>
</blockquote>
Hm, if you want get also schema_name then you should use this approach:
<br>
sqlquery1.SchemaType:=stTables;
<br>
sqlquery1.Open;
<br>
and in loop fill TStrings using
sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name')
<br>
</blockquote>
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!
<br>
</blockquote>
<b>Yes it will be more complicated.<br>
<br>
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.<br>
<br>
If others agree, IMO we can do it like this:<br>
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<br>
2. override for TPQConnection.GetTableNames:
GetDBInfo(stTables,'','SCHEMA_TABLE_NAME',List) <br>
<br>
-Laco.<br>
</b>
<blockquote cite="mid:50CD2392.2050703@netspace.net.au" type="cite"><br>
(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.)
<br>
<br>
<blockquote type="cite"><br>
<blockquote type="cite"><br>
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.
<br>
<br>
</blockquote>
Personally I am not fan of this approach (mainly, because of keeping
Delphi compatibility).
<br>
</blockquote>
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.
<br>
<blockquote type="cite"><br>
-Laco.
<br>
<br>
</blockquote>
@Reinier:
<br>
<blockquote type="cite">
<blockquote type="cite">Analysis:
<br>
As far as I can work out, a call to GetTableNames calls GetDBInfo, with
<br>
parameters
<br>
ASchemaType : TSchemaType - This specifies what info we want - user
<br>
tables, sys tables, procedures, columns etc
<br>
ASchemaObjectName - Doesn't seem to be used, it is specified as ""
<br>
</blockquote>
No, it's not used for GetTableNames; it is used e.g. to specify the
<br>
table name if you want to know info about columns.
<br>
</blockquote>
Yes, I saw that, but there seems no reason why it could not be used as
a schema selection.
<br>
<br>
I also had a flip through the previous discussion, but it was indeed a
long and tortuous thread to follow!
<br>
<br>
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 ?
<br>
<br>
cheers,
<br>
John Sunderland
<br>
_______________________________________________
<br>
fpc-pascal maillist - <a class="moz-txt-link-abbreviated" href="mailto:fpc-pascal@lists.freepascal.org">fpc-pascal@lists.freepascal.org</a>
<br>
<a class="moz-txt-link-freetext" href="http://lists.freepascal.org/mailman/listinfo/fpc-pascal">http://lists.freepascal.org/mailman/listinfo/fpc-pascal</a>
<br>
<br>
</blockquote>
<br>
</body>
</html>