Hi, This is a new (as in 9.1) feature we have to support in pgAdmin.
>From the git log on https://github.com/gleu/pgadmin3/tree/ticket304: Add support for invalid foreign key constraints This patch adds a checkbox in the dlgForeignKey dialog, so that the user can create an "not validated" foreign key. Each foreign key of this kind is displayed with the usual icon with a red cross on it. A contextual menu allows the user to force the validation of the constraint. The user can also do this via the dlgForeignKey dialog. Patch attached. Comments welcomed :) -- Guillaume http://www.postgresql.fr http://dalibo.com
From 167deabd65be8da4c6d6459b5163430b3b47ed40 Mon Sep 17 00:00:00 2001 From: Guillaume Lelarge <guilla...@lelarge.info> Date: Sun, 6 Mar 2011 23:20:02 +0100 Subject: [PATCH] Add support for invalid foreign key constraints This patch adds a checkbox in the dlgForeignKey dialog, so that the user can create an "not validated" foreign key. Each foreign key of this kind is displayed with the usual icon with a red cross on it. A contextual menu allows the user to force the validation of the constraint. The user can also do this via the dlgForeignKey dialog. --- pgadmin/dlg/dlgForeignKey.cpp | 27 ++++++++ pgadmin/frm/frmMain.cpp | 2 + pgadmin/include/dlg/dlgForeignKey.h | 1 + pgadmin/include/images/foreignkeybad.xpm | 105 ++++++++++++++++++++++++++++++ pgadmin/include/schema/pgForeignKey.h | 35 +++++++++- pgadmin/pgAdmin3.vcproj | 4 + pgadmin/schema/pgForeignKey.cpp | 104 ++++++++++++++++++++++++------ pgadmin/ui/dlgForeignKey.xrc | 18 +++++- 8 files changed, 273 insertions(+), 23 deletions(-) create mode 100644 pgadmin/include/images/foreignkeybad.xpm diff --git a/pgadmin/dlg/dlgForeignKey.cpp b/pgadmin/dlg/dlgForeignKey.cpp index 1a0e952..76f8ac2 100644 --- a/pgadmin/dlg/dlgForeignKey.cpp +++ b/pgadmin/dlg/dlgForeignKey.cpp @@ -27,6 +27,7 @@ #define chkDeferred CTRL_CHECKBOX("chkDeferred") #define cbReferences CTRL_COMBOBOX("cbReferences") #define chkMatchFull CTRL_CHECKBOX("chkMatchFull") +#define chkDontValidate CTRL_CHECKBOX("chkDontValidate") #define chkAutoIndex CTRL_CHECKBOX("chkAutoIndex") #define txtIndexName CTRL_TEXT("txtIndexName") @@ -44,6 +45,7 @@ BEGIN_EVENT_TABLE(dlgForeignKey, dlgProperty) EVT_CHECKBOX(XRCID("chkAutoIndex") , dlgProperty::OnChange) EVT_TEXT(XRCID("txtIndexName"), dlgProperty::OnChange) + EVT_CHECKBOX(XRCID("chkDontValidate"), dlgForeignKey::OnChangeValidate) EVT_LIST_ITEM_SELECTED(XRCID("lstColumns"), dlgForeignKey::OnSelChangeCol) EVT_TEXT(XRCID("cbReferences"), dlgForeignKey::OnSelChangeRef) EVT_COMBOBOX(XRCID("cbReferences"), dlgForeignKey::OnSelChangeRef) @@ -195,6 +197,10 @@ void dlgForeignKey::CheckChange() } else enable = txtComment->GetValue() != foreignKey->GetComment(); + + if (connection->BackendMinimumVersion(9, 1) && !foreignKey->GetValid() && !chkDontValidate->GetValue()) + enable = true; + EnableOK(enable); } else @@ -210,6 +216,12 @@ void dlgForeignKey::CheckChange() } +void dlgForeignKey::OnChangeValidate(wxCommandEvent &ev) +{ + CheckChange(); +} + + void dlgForeignKey::OnSelChangeCol(wxListEvent &ev) { btnRemoveRef->Enable(); @@ -348,9 +360,12 @@ int dlgForeignKey::Go(bool modal) chkDeferrable->SetValue(foreignKey->GetDeferrable()); chkDeferred->SetValue(foreignKey->GetDeferred()); chkMatchFull->SetValue(foreignKey->GetMatch() == wxT("FULL")); + if (connection->BackendMinimumVersion(9, 1)) + chkDontValidate->SetValue(!foreignKey->GetValid()); chkDeferrable->Disable(); chkDeferred->Disable(); chkMatchFull->Disable(); + chkDontValidate->Enable(connection->BackendMinimumVersion(9, 1)); rbOnUpdate->SetStringSelection(foreignKey->GetOnUpdate()); rbOnDelete->SetStringSelection(foreignKey->GetOnDelete()); @@ -392,6 +407,8 @@ int dlgForeignKey::Go(bool modal) // create mode txtComment->Disable(); + chkDontValidate->Enable(connection->BackendMinimumVersion(9, 1)); + wxString systemRestriction; if (!settings->GetShowSystemObjects()) systemRestriction = wxT(" AND ") + connection->SystemNamespaceRestriction(wxT("nsp.nspname")); @@ -451,6 +468,12 @@ wxString dlgForeignKey::GetSql() sql += wxT(" FOREIGN KEY ") + GetDefinition() + wxT(";\n"); } + else if (!chkDontValidate->GetValue()) + { + sql = wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier() + + wxT(" VALIDATE CONSTRAINT ") + qtIdent(name) + wxT(";\n"); + } + if (!name.IsEmpty()) AppendComment(sql, wxT("CONSTRAINT ") + qtIdent(name) + wxT(" ON ") + table->GetQuotedFullIdentifier(), foreignKey); @@ -511,5 +534,9 @@ wxString dlgForeignKey::GetDefinition() sql += wxT("\n DEFERRABLE"); if (chkDeferred->GetValue()) sql += wxT(" INITIALLY DEFERRED"); + + if (chkDontValidate->GetValue()) + sql += wxT("\n NOT VALID"); + return sql; } diff --git a/pgadmin/frm/frmMain.cpp b/pgadmin/frm/frmMain.cpp index 309e8f8..3883043 100644 --- a/pgadmin/frm/frmMain.cpp +++ b/pgadmin/frm/frmMain.cpp @@ -76,6 +76,7 @@ #include "schema/pgServer.h" #include "slony/slCluster.h" #include "slony/slSet.h" +#include "schema/pgForeignKey.h" #if wxDIALOG_UNIT_COMPATIBILITY @@ -289,6 +290,7 @@ void frmMain::CreateMenus() new enabledisableTriggerFactory(menuFactories, toolsMenu, 0); new disableAllTriggersFactory(menuFactories, toolsMenu, 0); new enableAllTriggersFactory(menuFactories, toolsMenu, 0); + new validateForeignKeyFactory(menuFactories, toolsMenu, 0); toolsMenu->AppendSeparator(); //-------------------------- diff --git a/pgadmin/include/dlg/dlgForeignKey.h b/pgadmin/include/dlg/dlgForeignKey.h index f0bc24f..1ea73f7 100644 --- a/pgadmin/include/dlg/dlgForeignKey.h +++ b/pgadmin/include/dlg/dlgForeignKey.h @@ -46,6 +46,7 @@ private: #ifdef __WXMAC__ void OnChangeSize(wxSizeEvent &ev); #endif + void OnChangeValidate(wxCommandEvent &ev); void OnSelChangeCol(wxListEvent &ev); void OnSelChangeRef(wxCommandEvent &ev); void OnSelChangeRefCol(wxCommandEvent &ev); diff --git a/pgadmin/include/images/foreignkeybad.xpm b/pgadmin/include/images/foreignkeybad.xpm new file mode 100644 index 0000000..2ebbcce --- /dev/null +++ b/pgadmin/include/images/foreignkeybad.xpm @@ -0,0 +1,105 @@ +/* XPM */ +static const char * foreignkeybad_xpm[] = { +"16 16 86 1", +" c None", +". c #BCBCBC", +"+ c #8F8F8F", +"@ c #909090", +"# c #D1D0D1", +"$ c #F9F8F9", +"% c #F5F1F2", +"& c #858585", +"* c #B4B4B4", +"= c #D1CACE", +"- c #D76262", +"; c #C50000", +"> c #D05759", +", c #B495A1", +"' c #C5474B", +") c #DD6969", +"! c #E1E1E1", +"~ c #BAB1B7", +"{ c #CA5154", +"] c #F67777", +"^ c #C3474B", +"/ c #9B868F", +"( c #BD3D42", +"_ c #F15757", +": c #D5D5D5", +"< c #E0E0E0", +"[ c #7C747A", +"} c #F67676", +"| c #F67272", +"1 c #F56E6E", +"2 c #BC3B40", +"3 c #F15656", +"4 c #EF4F4F", +"5 c #EE4849", +"6 c #DFDFDF", +"7 c #7A7679", +"8 c #F56D6D", +"9 c #F46868", +"0 c #F36262", +"a c #F05555", +"b c #EF4E4F", +"c c #EE4848", +"d c #D3D2D3", +"e c #D0C8CC", +"f c #F36161", +"g c #F15B5B", +"h c #F05454", +"i c #EF4D4E", +"j c #EE4747", +"k c #B3B3B3", +"l c #D2D2D2", +"m c #D0CFCF", +"n c #C3C2C3", +"o c #DA6565", +"p c #F05354", +"q c #EF4D4D", +"r c #EE4646", +"s c #AA2C33", +"t c #8E8E8E", +"u c #B7B0B3", +"v c #D66262", +"w c #F05353", +"x c #EF4C4C", +"y c #EE4545", +"z c #EC3E3F", +"A c #EB3838", +"B c #BB4345", +"C c #F05252", +"D c #EF4B4B", +"E c #ED4445", +"F c #EB3737", +"G c #EA3131", +"H c #E92B2B", +"I c #F05151", +"J c #EE4A4A", +"K c #ED4344", +"L c #99434A", +"M c #E92A2B", +"N c #E82525", +"O c #E72020", +"P c #ED4243", +"Q c #C4464A", +"R c #678392", +"S c #99444B", +"T c #E71F20", +"U c #836C7C", +" ", +" ", +" ", +" ", +" .+@#$% ", +" &*#=-;> ,';) ", +".*!~{;];^/(;_;) ", +"+:<[;}|1;2;345; ", +"+:67{;890;abc;) ", +".*6de-;fghij;) ", +" &klmno;pqr;s ", +" .tuv;wxyzA;) ", +" B;CDE;FGH;) ", +" ;IJK;L;MNO; ", +" {;P;QRS;T;) ", +" Us;s Us;) "}; diff --git a/pgadmin/include/schema/pgForeignKey.h b/pgadmin/include/schema/pgForeignKey.h index a317518..2b0d478 100644 --- a/pgadmin/include/schema/pgForeignKey.h +++ b/pgadmin/include/schema/pgForeignKey.h @@ -20,6 +20,14 @@ public: pgForeignKeyFactory(); virtual dlgProperty *CreateDialog(frmMain *frame, pgObject *node, pgObject *parent); virtual pgObject *CreateObjects(pgCollection *obj, ctlTree *browser, const wxString &restr = wxEmptyString); + + int GetClosedIconId() + { + return closedId; + } + +protected: + int closedId; }; extern pgForeignKeyFactory foreignKeyFactory; @@ -29,6 +37,8 @@ public: pgForeignKey(pgTable *newTable, const wxString &newName = wxT("")); ~pgForeignKey(); + int GetIconId(); + wxString GetDefinition(); wxString GetFullName(); @@ -124,6 +134,14 @@ public: { match = s; } + bool GetValid() const + { + return valid; + } + void iSetValid(const bool b) + { + valid = b; + } wxString GetRelTableOidStr() const { return NumToStr(relTableOid) + wxT("::oid"); @@ -160,7 +178,11 @@ public: bool DropObject(wxFrame *frame, ctlTree *browser, bool cascaded); wxString GetConstraint(); - wxString GetSql(ctlTree *browser); + wxString GetSql(ctlTree *browser, bool forceRefresh = false); + wxString GetSql(ctlTree *browser) + { + return GetSql(browser, false); + } wxString GetHelpPage(bool forCreate) const { return wxT("pg/sql-altertable"); @@ -179,12 +201,13 @@ public: { return true; } + void Validate(frmMain *form); private: wxString onUpdate, onDelete, conkey, confkey, fkTable, fkSchema, references, refSchema; wxString fkColumns, refColumns, quotedFkColumns, quotedRefColumns, coveringIndex, match; - bool deferrable, deferred; + bool deferrable, deferred, valid; OID relTableOid; }; @@ -195,4 +218,12 @@ public: wxString GetTranslatedMessage(int kindOfMessage) const; }; +class validateForeignKeyFactory : public contextActionFactory +{ +public: + validateForeignKeyFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar); + wxWindow *StartDialog(frmMain *form, pgObject *obj); + bool CheckEnable(pgObject *obj); +}; + #endif diff --git a/pgadmin/pgAdmin3.vcproj b/pgadmin/pgAdmin3.vcproj index b91d673..068483d 100644 --- a/pgadmin/pgAdmin3.vcproj +++ b/pgadmin/pgAdmin3.vcproj @@ -1834,6 +1834,10 @@ > </File> <File + RelativePath=".\include\images\foreignbad.xpm" + > + </File> + <File RelativePath=".\include\images\forward.xpm" > </File> diff --git a/pgadmin/schema/pgForeignKey.cpp b/pgadmin/schema/pgForeignKey.cpp index e615e4d..9547440 100644 --- a/pgadmin/schema/pgForeignKey.cpp +++ b/pgadmin/schema/pgForeignKey.cpp @@ -15,6 +15,7 @@ // App headers #include "pgAdmin3.h" #include "utils/misc.h" +#include "frm/frmMain.h" #include "schema/pgForeignKey.h" #include "schema/pgConstraints.h" @@ -94,6 +95,15 @@ wxString pgForeignKey::GetTranslatedMessage(int kindOfMessage) const } +int pgForeignKey::GetIconId() +{ + if (!GetDatabase()->BackendMinimumVersion(9, 1) || GetValid()) + return foreignKeyFactory.GetIconId(); + else + return foreignKeyFactory.GetClosedIconId(); +} + + bool pgForeignKey::DropObject(wxFrame *frame, ctlTree *browser, bool cascaded) { wxString sql = wxT("ALTER TABLE ") + this->GetSchema()->GetQuotedIdentifier() + wxT(".") + qtIdent(fkTable) @@ -126,6 +136,10 @@ wxString pgForeignKey::GetDefinition() else sql += wxT("IMMEDIATE"); } + + if (GetDatabase()->BackendMinimumVersion(9, 1) && !GetValid()) + sql += wxT("\n NOT VALID"); + return sql; } @@ -140,10 +154,10 @@ wxString pgForeignKey::GetConstraint() } -wxString pgForeignKey::GetSql(ctlTree *browser) +wxString pgForeignKey::GetSql(ctlTree *browser, bool forceRefresh) { - if (sql.IsNull()) - { + //if (sql.IsNull() || forceRefresh) + //{ sql = wxT("-- Foreign Key: ") + GetQuotedFullIdentifier() + wxT("\n\n") + wxT("-- ALTER TABLE ") + GetQuotedSchemaPrefix(fkSchema) + qtIdent(fkTable) + wxT(" DROP CONSTRAINT ") + GetQuotedIdentifier() + wxT(";") @@ -153,7 +167,7 @@ wxString pgForeignKey::GetSql(ctlTree *browser) if (!GetComment().IsEmpty()) sql += wxT("COMMENT ON CONSTRAINT ") + GetQuotedIdentifier() + wxT(" ON ") + GetQuotedSchemaPrefix(fkSchema) + qtIdent(fkTable) + wxT(" IS ") + qtDbString(GetComment()) + wxT(";\n"); - } + //} return sql; } @@ -231,14 +245,14 @@ void pgForeignKey::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView * if (GetDeferrable()) properties->AppendItem(_("Initially?"), GetDeferred() ? wxT("DEFERRED") : wxT("IMMEDIATE")); + if (GetDatabase()->BackendMinimumVersion(9, 1)) + properties->AppendItem(_("Valid?"), BoolToYesNo(GetValid())); properties->AppendItem(_("System foreign key?"), BoolToYesNo(GetSystemObject())); properties->AppendItem(_("Comment"), firstLineOnly(GetComment())); } } - - pgObject *pgForeignKey::Refresh(ctlTree *browser, const wxTreeItemId item) { pgObject *foreignKey = 0; @@ -251,25 +265,39 @@ pgObject *pgForeignKey::Refresh(ctlTree *browser, const wxTreeItemId item) } +void pgForeignKey::Validate(frmMain *form) +{ + wxString sql = wxT("ALTER TABLE ") + GetQuotedSchemaPrefix(fkSchema) + qtIdent(fkTable) + + wxT("\n VALIDATE CONSTRAINT ") + GetQuotedIdentifier(); + GetDatabase()->ExecuteVoid(sql); + + iSetValid(true); + UpdateIcon(form->GetBrowser()); +} + pgObject *pgForeignKeyFactory::CreateObjects(pgCollection *coll, ctlTree *browser, const wxString &restriction) { + wxString sql; pgTableObjCollection *collection = (pgTableObjCollection *)coll; pgForeignKey *foreignKey = 0; - pgSet *foreignKeys = collection->GetDatabase()->ExecuteSet( - wxT("SELECT ct.oid, conname, condeferrable, condeferred, confupdtype, confdeltype, confmatchtype, ") - wxT("conkey, confkey, confrelid, nl.nspname as fknsp, cl.relname as fktab, ") - wxT("nr.nspname as refnsp, cr.relname as reftab, description\n") - wxT(" FROM pg_constraint ct\n") - wxT(" JOIN pg_class cl ON cl.oid=conrelid\n") - wxT(" JOIN pg_namespace nl ON nl.oid=cl.relnamespace\n") - wxT(" JOIN pg_class cr ON cr.oid=confrelid\n") - wxT(" JOIN pg_namespace nr ON nr.oid=cr.relnamespace\n") - wxT(" LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid\n") - wxT(" WHERE contype='f' AND conrelid = ") + collection->GetOidStr() - + restriction + wxT("\n") - wxT(" ORDER BY conname")); + sql = wxT("SELECT ct.oid, conname, condeferrable, condeferred, confupdtype, confdeltype, confmatchtype, ") + wxT("conkey, confkey, confrelid, nl.nspname as fknsp, cl.relname as fktab, ") + wxT("nr.nspname as refnsp, cr.relname as reftab, description"); + if (collection->GetDatabase()->BackendMinimumVersion(9, 1)) + sql += wxT(", convalidated"); + sql += wxT("\n FROM pg_constraint ct\n") + wxT(" JOIN pg_class cl ON cl.oid=conrelid\n") + wxT(" JOIN pg_namespace nl ON nl.oid=cl.relnamespace\n") + wxT(" JOIN pg_class cr ON cr.oid=confrelid\n") + wxT(" JOIN pg_namespace nr ON nr.oid=cr.relnamespace\n") + wxT(" LEFT OUTER JOIN pg_description des ON des.objoid=ct.oid\n") + wxT(" WHERE contype='f' AND conrelid = ") + collection->GetOidStr() + + restriction + wxT("\n") + wxT(" ORDER BY conname"); + + pgSet *foreignKeys = collection->GetDatabase()->ExecuteSet(sql); if (foreignKeys) { @@ -284,6 +312,8 @@ pgObject *pgForeignKeyFactory::CreateObjects(pgCollection *coll, ctlTree *browse foreignKey->iSetFkTable(foreignKeys->GetVal(wxT("fktab"))); foreignKey->iSetRefSchema(foreignKeys->GetVal(wxT("refnsp"))); foreignKey->iSetReferences(foreignKeys->GetVal(wxT("reftab"))); + if (collection->GetDatabase()->BackendMinimumVersion(9, 1)) + foreignKey->iSetValid(foreignKeys->GetBool(wxT("convalidated"))); wxString onUpd = foreignKeys->GetVal(wxT("confupdtype")); wxString onDel = foreignKeys->GetVal(wxT("confdeltype")); wxString match = foreignKeys->GetVal(wxT("confmatchtype")); @@ -352,6 +382,7 @@ wxString pgForeignKeyCollection::GetTranslatedMessage(int kindOfMessage) const ///////////////////////////// #include "images/foreignkey.xpm" +#include "images/foreignkeybad.xpm" pgForeignKeyFactory::pgForeignKeyFactory() @@ -359,7 +390,42 @@ pgForeignKeyFactory::pgForeignKeyFactory() { metaType = PGM_FOREIGNKEY; collectionFactory = &constraintCollectionFactory; + closedId = addIcon(foreignkeybad_xpm); } pgForeignKeyFactory foreignKeyFactory; + +validateForeignKeyFactory::validateForeignKeyFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list) +{ + mnu->Append(id, _("Validate foreign key"), _("Validate the selected foreign key.")); +} + + +wxWindow *validateForeignKeyFactory::StartDialog(frmMain *form, pgObject *obj) +{ + ((pgForeignKey *)obj)->Validate(form); + + wxTreeItemId item = form->GetBrowser()->GetSelection(); + if (obj == form->GetBrowser()->GetObject(item)) + { + obj->ShowTreeDetail(form->GetBrowser(), 0, form->GetProperties()); + } + form->GetMenuFactories()->CheckMenu(obj, form->GetMenuBar(), (ctlMenuToolbar *)form->GetToolBar()); + + /* + wxString sql = ((pgForeignKey *)obj)->GetSql(form->GetBrowser(), true); + wxLogError(sql); + form->GetSqlPane()->SetText(sql); + */ + + return 0; +} + + +bool validateForeignKeyFactory::CheckEnable(pgObject *obj) +{ + return obj && obj->IsCreatedBy(foreignKeyFactory) && obj->CanEdit() + && ((pgForeignKey *)obj)->GetConnection()->BackendMinimumVersion(9, 1) + && !((pgForeignKey *)obj)->GetValid(); +} diff --git a/pgadmin/ui/dlgForeignKey.xrc b/pgadmin/ui/dlgForeignKey.xrc index 5814f7a..ad54ce1 100644 --- a/pgadmin/ui/dlgForeignKey.xrc +++ b/pgadmin/ui/dlgForeignKey.xrc @@ -17,10 +17,10 @@ <object class="wxPanel" name="pnlProperties"> <object class="wxFlexGridSizer"> <cols>2</cols> - <rows>9</rows> + <rows>10</rows> <vgap>5</vgap> <hgap>5</hgap> - <growablerows>7</growablerows> + <growablerows>8</growablerows> <growablecols>1</growablecols> <object class="sizeritem"> <object class="wxStaticText" name="stName"> @@ -92,6 +92,20 @@ <border>4</border> </object> <object class="sizeritem"> + <object class="wxStaticText" name="stDontValidate"> + <label>Don't validate</label> + </object> + <flag>wxALIGN_CENTRE_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag> + <border>4</border> + </object> + <object class="sizeritem"> + <object class="wxCheckBox" name="chkDontValidate"> + <label></label> + </object> + <flag>wxEXPAND|wxALIGN_CENTER_VERTICAL|wxTOP|wxLEFT|wxRIGHT</flag> + <border>4</border> + </object> + <object class="sizeritem"> <object class="wxStaticText" name="stAutoIndex"> <label>Auto FK index</label> </object> -- 1.7.1
-- Sent via pgadmin-hackers mailing list (pgadmin-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-hackers