Re: Support of INHERIT in existing tables (8.2+ releases) - Mailing list pgadmin-hackers

Guillaume Lelarge a écrit :
> Dave Page a écrit :
>> On Fri, Jul 11, 2008 at 11:30 PM, Guillaume Lelarge
>> <guillaume@lelarge.info> wrote:
>>
>>> So, here is a patch to add support on adding inherited tables on already
>>> existing tables. The feature is available since PostgreSQL 8.2 release.
>>>
>>> Tested on Linux. It works fine there. I didn't check Win32 platform
>>> because
>>> I didn't change any xrc files :) Didn't check on Mac OS X either cause I
>>> don't have one (btw, I'm following ebay's Mac Mini list to get one
>>> soon).
>>
>> Hmm, testing on 8.3.3 on Windows, I created parent and child table,
>> then tried to add parent to child's inherit list separately and got:
>>
>> ERROR:  syntax error at or near "from"
>> LINE 1: ALTER TABLE child ADD COLUMN id Inherited from table ...
>>
>> The actual SQL generated is:
>>
>> ALTER TABLE child ADD COLUMN id Inherited from table parent;
>> ALTER TABLE child INHERIT parent;
>>
>> Shouldn't it just be doing:
>>
>> ALTER TABLE child INHERIT parent;
>>
>
> Yeah, you're right. I had this issue during my tests and I'm sure I
> fixed it. Don't know why it isn't in the patch I sent. Sorry about this.
> I can't check right now but I will take care of this issue this afternoon.
>

This patch fixes the issue you found.

I have one question about inherited columns display in the table
properties dialog. There's a "Inherited from" column on the Columns tab.
It shows the word "inherited" when the column is inherited. But I
initialy thought it would show the table name of the inherited column.
Is it a bug? (btw, I checked in 1.8.4, it works the same (or it bugs the
same :) ))


--
Guillaume.
  http://www.postgresqlfr.org
  http://dalibo.com
Index: pgadmin/include/schema/pgTable.h
===================================================================
--- pgadmin/include/schema/pgTable.h    (revision 7393)
+++ pgadmin/include/schema/pgTable.h    (working copy)
@@ -72,6 +72,7 @@
     long GetInheritedTableCount() { if (inheritedTableCount < 0) UpdateInheritance(); return inheritedTableCount; }
     wxString GetInheritedTables() { GetInheritedTableCount(); return inheritedTables; }
     wxString GetQuotedInheritedTables() { GetInheritedTableCount(); return quotedInheritedTables; }
+    wxArrayString GetInheritedTablesOidList() { GetInheritedTableCount(); return inheritedTablesOidList; }
     wxArrayString GetQuotedInheritedTablesList() { GetInheritedTableCount(); return quotedInheritedTablesList; }
     wxString GetCoveringIndex(ctlTree *browser, const wxString &collist);
     pgCollection *GetColumnCollection(ctlTree *browser);
@@ -124,7 +125,7 @@
     long inheritedTableCount;
     wxString quotedInheritedTables, inheritedTables, primaryKey, quotedPrimaryKey,
         primaryKeyName, primaryKeyColNumbers, tablespace;
-    wxArrayString quotedInheritedTablesList;
+    wxArrayString quotedInheritedTablesList, inheritedTablesOidList;
     slSet *replicationSet;
     OID tablespaceOid;
 };
Index: pgadmin/include/dlg/dlgTable.h
===================================================================
--- pgadmin/include/dlg/dlgTable.h    (revision 7393)
+++ pgadmin/include/dlg/dlgTable.h    (working copy)
@@ -56,7 +56,7 @@
     wxString GetItemConstraintType(ctlListView *list, long pos);
     bool hasPK;

-    wxArrayString previousColumns, previousConstraints;
+    wxArrayString previousColumns, previousConstraints, previousTables;
     wxArrayString tableOids, inheritedTableOids;
     wxTreeItemId columnsItem, constraintsItem;

Index: pgadmin/schema/pgTable.cpp
===================================================================
--- pgadmin/schema/pgTable.cpp    (revision 7393)
+++ pgadmin/schema/pgTable.cpp    (working copy)
@@ -511,7 +511,7 @@
 {
     // not checked so far
     pgSet *props=ExecuteSet(
-        wxT("SELECT c.relname , nspname\n")
+        wxT("SELECT c.oid, c.relname , nspname\n")
         wxT("  FROM pg_inherits i\n")
         wxT("  JOIN pg_class c ON c.oid = i.inhparent\n")
         wxT("  JOIN pg_namespace n ON n.oid=c.relnamespace\n")
@@ -533,6 +533,7 @@
                     + qtIdent(props->GetVal(wxT("relname")));
             quotedInheritedTablesList.Add(GetQuotedSchemaPrefix(props->GetVal(wxT("nspname")))
                     + qtIdent(props->GetVal(wxT("relname"))));
+            inheritedTablesOidList.Add(props->GetVal(wxT("oid")));
             props->MoveNext();
             inheritedTableCount++;
         }
Index: pgadmin/dlg/dlgTable.cpp
===================================================================
--- pgadmin/dlg/dlgTable.cpp    (revision 7393)
+++ pgadmin/dlg/dlgTable.cpp    (working copy)
@@ -157,14 +157,19 @@
         if (table->GetTablespaceOid() != 0)
             cbTablespace->SetKey(table->GetTablespaceOid());

+        inheritedTableOids=table->GetInheritedTablesOidList();
+
         wxArrayString qitl=table->GetQuotedInheritedTablesList();
         size_t i;
         for (i=0 ; i < qitl.GetCount() ; i++)
+        {
+            previousTables.Add(qitl.Item(i));
             lbTables->Append(qitl.Item(i));
+        }

-        btnAddTable->Disable();
-        lbTables->Disable();
-        cbTables->Disable();
+        btnAddTable->Enable(connection->BackendMinimumVersion(8, 2));
+        lbTables->Enable(connection->BackendMinimumVersion(8, 2));
+        cbTables->Enable(connection->BackendMinimumVersion(8, 2));
         chkHasOids->Enable(table->GetHasOids() && connection->BackendMinimumVersion(8, 0));
         cbTablespace->Enable(connection->BackendMinimumVersion(7, 5));

@@ -278,11 +283,30 @@
         // create mode
         btnChangeCol->Hide();

+        // Add the default tablespace
+        cbTablespace->Insert(_("<default tablespace>"), 0, (void *)0);
+        cbTablespace->SetSelection(0);
+    }
+
+    if (connection->BackendMinimumVersion(8,2) || !table)
+    {
         wxString systemRestriction;
         if (!settings->GetShowSystemObjects())
         systemRestriction =
             wxT("   AND ") + connection->SystemNamespaceRestriction(wxT("n.nspname"));
-
+
+        if (table)
+        {
+            wxString oids = table->GetOidStr();
+            int i;
+            for (i=0 ; i < (int)inheritedTableOids.GetCount() ; i++)
+            {
+                oids += wxT(", ") + inheritedTableOids.Item(i);
+            }
+            if (oids.Length() > 0)
+                systemRestriction += wxT(" AND c.oid NOT IN (") + oids + wxT(")");
+        }
+
         pgSet *set=connection->ExecuteSet(
             wxT("SELECT c.oid, c.relname , nspname\n")
             wxT("  FROM pg_class c\n")
@@ -302,10 +326,6 @@
             }
             delete set;
         }
-
-        // Add the default tablespace
-        cbTablespace->Insert(_("<default tablespace>"), 0, (void *)0);
-        cbTablespace->SetSelection(0);
     }

     FillConstraint();
@@ -511,28 +531,31 @@
         wxArrayString tmpDef=previousColumns;
         wxString tmpsql;

-        // Build a tmeporary list of ADD COLUMNs, and fixup the list to remove
+        // Build a temporary list of ADD COLUMNs, and fixup the list to remove
         for (pos=0; pos < lstColumns->GetItemCount() ; pos++)
         {
-            definition = lstColumns->GetText(pos, 3);
-            if (definition.IsEmpty())
+            if (lstColumns->GetText(pos, 2).IsEmpty())
             {
-                definition=qtIdent(lstColumns->GetText(pos)) + wxT(" ") + lstColumns->GetText(pos, 1);
-                index=tmpDef.Index(definition);
-                if (index < 0)
-                    tmpsql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
-                        +  wxT(" ADD COLUMN ") + definition + wxT(";\n");
-            }
-            else
-            {
-                tmpsql += definition;
-
-                pgColumn *column=(pgColumn*) StrToLong(lstColumns->GetText(pos, 6));
-                if (column)
+                definition = lstColumns->GetText(pos, 3);
+                if (definition.IsEmpty())
                 {
-                    index=tmpDef.Index(column->GetQuotedIdentifier()
-                                + wxT(" ") + column->GetDefinition());
+                    definition=qtIdent(lstColumns->GetText(pos)) + wxT(" ") + lstColumns->GetText(pos, 1);
+                    index=tmpDef.Index(definition);
+                    if (index < 0)
+                        tmpsql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+                            +  wxT(" ADD COLUMN ") + definition + wxT(";\n");
                 }
+                else
+                {
+                    tmpsql += definition;
+
+                    pgColumn *column=(pgColumn*) StrToLong(lstColumns->GetText(pos, 6));
+                    if (column)
+                    {
+                        index=tmpDef.Index(column->GetQuotedIdentifier()
+                                    + wxT(" ") + column->GetDefinition());
+                    }
+                }
             }
             if (index >= 0)
                 tmpDef.RemoveAt(index);
@@ -555,10 +578,34 @@
         AppendNameChange(sql);
         AppendOwnerChange(sql, wxT("TABLE ") + tabname);

+        tmpDef=previousTables;
+        tmpsql.Empty();
+
+        // Build a temporary list of INHERIT tables, and fixup the list to remove
+        for (pos = 0 ; pos < (int)lbTables->GetCount() ; pos++)
+        {
+            definition = lbTables->GetString(pos);
+            index = tmpDef.Index(definition);
+            if (index < 0)
+                tmpsql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+                    +  wxT(" INHERIT ") + qtIdent(definition) + wxT(";\n");
+            else
+                tmpDef.RemoveAt(index);
+        }
+
+        for (index = 0 ; index < (int)tmpDef.GetCount() ; index++)
+        {
+            definition = tmpDef.Item(index);
+            sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+                +  wxT(" NO INHERIT ") + qtIdent(definition) + wxT(";\n");
+        }
+        // Add the ADD COLUMNs...
+        sql += tmpsql;
+
         tmpDef=previousConstraints;
         tmpsql.Empty();

-        // Build a tmeporary list of ADD CONSTRAINTs, and fixup the list to remove
+        // Build a temporary list of ADD CONSTRAINTs, and fixup the list to remove
         for (pos=0; pos < lstConstraints->GetItemCount() ; pos++)
         {
             wxString conname= qtIdent(lstConstraints->GetItemText(pos));