Problem with GetTableNames and Postgre

[pawelp]

Hi,

I have a small Problem with the GetTableNames method of TZConnection.
The result List contains besides the table names also indices and primary keys for all the tables.

I call:

var
tables : TStringList

MyZConnection.GetTableNames('','public', tables);

The Postgre Version I'm using is 8.3

[mdaems]

Hi,

I limited the default output of TZPostgresSQLDatabaseMetadata.GetTables to 'TABLE', 'VIEW' and 'TEMPORARY TABLE' and added a new GetTableNames overloaded procedure allowing to specify the table types yourself.
The patch:

Code: Select all

Index: src/component/ZConnection.pas
===================================================================
--- src/component/ZConnection.pas	(revision 423)
+++ src/component/ZConnection.pas	(working copy)
@@ -211,6 +211,7 @@
     procedure GetSchemaNames(List: TStrings);
     procedure GetTableNames(const Pattern: string; List: TStrings);overload;
     procedure GetTableNames(const tablePattern,schemaPattern: string; List: TStrings);overload;
+    procedure GetTableNames(const tablePattern,schemaPattern: string; Types: TStringDynArray; List: TStrings);overload;
     procedure GetColumnNames(const TablePattern, ColumnPattern: string; List: TStrings);
 
     procedure GetStoredProcNames(const Pattern: string; List: TStrings);
@@ -1086,26 +1087,36 @@
   @param List a string list to fill out.
 }
 procedure TZConnection.GetTableNames(const Pattern: string; List: TStrings);
-var
-  Metadata: IZDatabaseMetadata;
-  ResultSet: IZResultSet;
 begin
-  CheckConnected;
+  GetTableNames('',Pattern,nil,List);
+end;
 
-  List.Clear;
-  Metadata := DbcConnection.GetMetadata;
-  ResultSet := Metadata.GetTables('', '', Pattern, nil);
-  while ResultSet.Next do
-    List.Add(ResultSet.GetStringByName('TABLE_NAME'));
+{**
+  Fills string list with table names.
+  @param tablePattern a pattern for table names.
+  @param schemaPattern a pattern for schema names.
+  @param List a string list to fill out.
+}
+procedure TZConnection.GetTableNames(const tablePattern,schemaPattern: string; List: TStrings);
+begin
+  GetTableNames(tablePattern,schemaPattern,nil,List);
 end;
 
 {**
   Fills string list with table names.
   @param tablePattern a pattern for table names.
   @param schemaPattern a pattern for schema names.
+  @param types a TStringDynArray specifying the table types to look for.
+    possible values can be found by reading
+     TZConnection.DbcConnection.GetMetadata.GetTableTypes
+     eg. for PostGreSQL this includes :'TABLE', 'VIEW', 'INDEX', 'SEQUENCE',
+                                       'SYSTEM TABLE', 'SYSTEM TOAST TABLE',
+                                       'SYSTEM TOAST INDEX', 'SYSTEM VIEW',
+                                       'SYSTEM INDEX', 'TEMPORARY TABLE',
+                                       'TEMPORARY INDEX'
   @param List a string list to fill out.
 }
-procedure TZConnection.GetTableNames(const tablePattern,schemaPattern: string; List: TStrings);
+procedure TZConnection.GetTableNames(const tablePattern,schemaPattern: string; Types: TStringDynArray; List: TStrings);
 var
   Metadata: IZDatabaseMetadata;
   ResultSet: IZResultSet;
@@ -1114,7 +1125,7 @@
 
   List.Clear;
   Metadata := DbcConnection.GetMetadata;
-  ResultSet := Metadata.GetTables('', schemaPattern, tablePattern, nil);
+  ResultSet := Metadata.GetTables('', schemaPattern, tablePattern, types);
   while ResultSet.Next do
     List.Add(ResultSet.GetStringByName('TABLE_NAME'));
 end;
Index: src/dbc/ZDbcPostgreSqlMetadata.pas
===================================================================
--- src/dbc/ZDbcPostgreSqlMetadata.pas	(revision 423)
+++ src/dbc/ZDbcPostgreSqlMetadata.pas	(working copy)
@@ -1740,12 +1740,14 @@
 
     if (Types = nil) or (Length(Types) = 0) then
     begin
-      SetLength(LTypes, 5);
+      SetLength(LTypes, 3);
+      // SetLength(LTypes, 6);
       LTypes[0] := 'TABLE';
       LTypes[1] := 'VIEW';
-      LTypes[2] := 'INDEX';
-      LTypes[3] := 'SEQUENCE';
-      LTypes[4] := 'TEMPORARY TABLE';
+      LTypes[2] := 'TEMPORARY TABLE';
+      // LTypes[3] := 'SYSTEM TABLE';
+      // LTypes[4] := 'SYSTEM TOAST TABLE';
+      // LTypes[5] := 'SYSTEM VIEW';
     end
     else
       LTypes := Types;

SVN Rev. 426

[pawelsel]

I think there is no need to remove sequences from list. Only the indexes makes troubles.
In PostgreSQL You can do something like this:

SELECT * FROM some_sequence;

And it returns data from that sequence.

[pawelp]

Gum'

at first, many thanks for that quick help, great work.

I think its better if the GetTableNames methode returns just table names, per default. If you need sequences, you can use the new overloaded version to specify them.
But IMHO most users will use the GetTableNames to build some kind of admin tools, either for backuping data from those tables, or to change data in there, or to make associations of some kind.
I'm using it for malking database backups and to make an O/R Map for those tables.
In those cases sequences are of no good.

I'm not 100% sure, but are sequences (in postgres) not named using the table name and the sufix "_pkey"? for example for a table named "mytabke" the sequence would be named "mytable_pkey".

[mdaems]

Pawel seems to be a typical name for your region

My conclusion looks like this:
- indexes are no valid table types at all as they are not 'select'-able.
- when doing metadata calls to resolve columns of a query (= zeoslib internal workings) all valid table types should be queried --> some work to do...
- for 'standard' work, using the TZConnections.GetTableNames function should return only real tables and views.

Is this realistic and correct? Sorry for being a PG dummy...

Mark

[pawelsel]

Mark,
I think that this is the best solution.

Pawelp ,
With suffix _pkey PostgreSQL create by default constraints for primary keys. This is not the sequence. Sequences names could be any name you wish and the sequences are usually used with DEFAULT on primary numeric column of table. From version 8.2 of PostgreSQL the sequences must be created before the creation of table using it. Typical declaration:

CREATE SEQUENCE some_schema.sq_some_sequence;
CREATE TABLE some_schema.SOME_TABLE
(
ID numeric(10,0) DEFAULT nextval('some_schema.sq_some_sequence') NOT NULL,
"NAME" varchar(20) NOT NULL,
DESCRIPTION varchar(200) NOT NULL,
PRIMARY KEY (ID)
);

After this PostgreSQL create implicitly constraint "some_table_pkey".

Pawel

[pawelp]

Yes. Pawel is a quite common name here in Poland

Mark, I think this is a good solution.
And about the "PG dummy", this isn't something you should be sorry about. Anyone is an expert in some fields and a dummy in other fields And we all (or at least the most of us) always do learn something new and try to improve ourselves.
I'm not a Pg expert, either.

Pawel, I mistaken sequences with primary keys. You are of course right.
But as I never used sequences (never had to, SERIALS were just good enough ) I did not seen them on the resulting table names list, and did not thought of them.

---
Pawel 'Stealth' Piotrowski