Support of INHERIT in existing tables (8.2+ releases)
От | Guillaume Lelarge |
---|---|
Тема | Support of INHERIT in existing tables (8.2+ releases) |
Дата | |
Msg-id | 4877DF18.5070308@lelarge.info обсуждение исходный текст |
Ответы |
Re: Support of INHERIT in existing tables (8.2+ releases)
("Dave Page" <dpage@pgadmin.org>)
Re: Support of INHERIT in existing tables (8.2+ releases) ("Dave Page" <dpage@pgadmin.org>) |
Список | pgadmin-hackers |
Hi, I don't know if you have read this page (http://www.postgresonline.com/journal/index.php?/archives/59-How-to-Inherit,-Unherit-and-Merge-Inherit.html) but it complains on the lack of inherit support for existing tables. 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). Regards. -- 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,7 +531,7 @@ 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); @@ -555,10 +575,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));
В списке pgadmin-hackers по дате отправления:
Следующее
От: "Dave Page"Дата:
Сообщение: Re: Support of INHERIT in existing tables (8.2+ releases)