1 //////////////////////////////////////////////////////////////////////////
2 //
3 // pgAdmin III - PostgreSQL Tools
4 //
5 // Copyright (C) 2002 - 2016, The pgAdmin Development Team
6 // This software is released under the PostgreSQL Licence
7 //
8 // dlgForeignKey.cpp - PostgreSQL Foreign Key Property
9 //
10 //////////////////////////////////////////////////////////////////////////
11
12 // wxWindows headers
13 #include <wx/wx.h>
14
15 // App headers
16 #include "pgAdmin3.h"
17 #include "utils/misc.h"
18 #include "frm/frmMain.h"
19 #include "frm/frmHint.h"
20 #include "schema/pgTable.h"
21 #include "schema/pgForeignKey.h"
22 #include "dlg/dlgForeignKey.h"
23 #include "schema/pgColumn.h"
24
25
26 #define chkDeferrable CTRL_CHECKBOX("chkDeferrable")
27 #define chkDeferred CTRL_CHECKBOX("chkDeferred")
28 #define cbReferences CTRL_COMBOBOX("cbReferences")
29 #define chkMatchFull CTRL_CHECKBOX("chkMatchFull")
30 #define chkDontValidate CTRL_CHECKBOX("chkDontValidate")
31
32 #define chkAutoIndex CTRL_CHECKBOX("chkAutoIndex")
33 #define txtIndexName CTRL_TEXT("txtIndexName")
34
35 #define cbRefColumns CTRL_COMBOBOX("cbRefColumns")
36 #define btnAddRef CTRL_BUTTON("btnAddRef")
37 #define btnRemoveRef CTRL_BUTTON("btnRemoveRef")
38
39 #define rbOnUpdate CTRL_RADIOBOX("rbOnUpdate")
40 #define rbOnDelete CTRL_RADIOBOX("rbOnDelete")
41
42
43 BEGIN_EVENT_TABLE(dlgForeignKey, dlgProperty)
44 EVT_CHECKBOX(XRCID("chkDeferrable"), dlgProperty::OnChange)
45 EVT_CHECKBOX(XRCID("chkAutoIndex") , dlgProperty::OnChange)
46 EVT_TEXT(XRCID("txtIndexName"), dlgProperty::OnChange)
47
48 EVT_CHECKBOX(XRCID("chkDontValidate"), dlgForeignKey::OnChangeValidate)
49 EVT_LIST_ITEM_SELECTED(XRCID("lstColumns"), dlgForeignKey::OnSelChangeCol)
50 EVT_TEXT(XRCID("cbReferences"), dlgForeignKey::OnSelChangeRef)
51 EVT_COMBOBOX(XRCID("cbReferences"), dlgForeignKey::OnSelChangeRef)
52 EVT_TEXT(XRCID("cbColumns"), dlgForeignKey::OnSelChangeRefCol)
53 EVT_COMBOBOX(XRCID("cbColumns"), dlgForeignKey::OnSelChangeRefCol)
54 EVT_TEXT(XRCID("cbRefColumns"), dlgForeignKey::OnSelChangeRefCol)
55 EVT_COMBOBOX(XRCID("cbRefColumns"), dlgForeignKey::OnSelChangeRefCol)
56 EVT_BUTTON(XRCID("btnAddRef"), dlgForeignKey::OnAddRef)
57 EVT_BUTTON(XRCID("btnRemoveRef"), dlgForeignKey::OnRemoveRef)
58 EVT_BUTTON(wxID_OK, dlgForeignKey::OnOK)
59 #ifdef __WXMAC__
60 EVT_SIZE( dlgForeignKey::OnChangeSize)
61 #endif
62 END_EVENT_TABLE();
63
64
CreateDialog(frmMain * frame,pgObject * node,pgObject * parent)65 dlgProperty *pgForeignKeyFactory::CreateDialog(frmMain *frame, pgObject *node, pgObject *parent)
66 {
67 return new dlgForeignKey(this, frame, (pgForeignKey *)node, (pgTable *)parent);
68 }
69
70
dlgForeignKey(pgaFactory * f,frmMain * frame,pgForeignKey * node,pgTable * parentNode)71 dlgForeignKey::dlgForeignKey(pgaFactory *f, frmMain *frame, pgForeignKey *node, pgTable *parentNode)
72 : dlgCollistProperty(f, frame, wxT("dlgForeignKey"), parentNode)
73 {
74 foreignKey = node;
75 }
76
77
dlgForeignKey(pgaFactory * f,frmMain * frame,ctlListView * colList)78 dlgForeignKey::dlgForeignKey(pgaFactory *f, frmMain *frame, ctlListView *colList)
79 : dlgCollistProperty(f, frame, wxT("dlgForeignKey"), colList)
80 {
81 foreignKey = 0;
82 }
83
84
DefaultIndexName(const wxString & name)85 wxString dlgForeignKey::DefaultIndexName(const wxString &name)
86 {
87 if (name.IsEmpty())
88 return wxEmptyString;
89
90 if (name.Left(3) == wxT("fk_"))
91 return wxT("fki_") + name.Mid(3);
92 else if (name.Left(3) == wxT("FK_"))
93 return wxT("FKI_") + name.Mid(3);
94 else
95 return wxT("fki_") + name;
96 }
97
98
OnOK(wxCommandEvent & ev)99 void dlgForeignKey::OnOK(wxCommandEvent &ev)
100 {
101 if (chkAutoIndex->IsEnabled() && !chkAutoIndex->GetValue()
102 && frmHint::ShowHint(this, HINT_FKINDEX) == wxID_CANCEL)
103 return;
104
105 dlgProperty::OnOK(ev);
106 }
107
108
109 #ifdef __WXMAC__
OnChangeSize(wxSizeEvent & ev)110 void dlgForeignKey::OnChangeSize(wxSizeEvent &ev)
111 {
112 lstColumns->SetSize(wxDefaultCoord, wxDefaultCoord,
113 ev.GetSize().GetWidth(), ev.GetSize().GetHeight() - 450);
114 if (GetAutoLayout())
115 {
116 Layout();
117 }
118 }
119 #endif
120
121
CheckChange()122 void dlgForeignKey::CheckChange()
123 {
124 if (processing)
125 return;
126
127 processing = true;
128
129 wxString name = GetName();
130
131 wxString cols;
132 int pos;
133 for (pos = 0 ; pos < lstColumns->GetItemCount() ; pos++)
134 {
135 if (pos)
136 cols += wxT(", ");
137 cols += qtIdent(lstColumns->GetText(pos));
138 }
139
140 bool canDef = chkDeferrable->GetValue();
141 if (!canDef)
142 chkDeferred->SetValue(false);
143 chkDeferred->Enable(canDef);
144
145 txtIndexName->Enable(table && chkAutoIndex->GetValue());
146
147 wxString coveringIndex;
148 if (table)
149 {
150 coveringIndex = table->GetCoveringIndex(mainForm->GetBrowser(), cols);
151
152 if (coveringIndex.IsEmpty())
153 {
154 if (!chkAutoIndex->IsEnabled())
155 {
156 chkAutoIndex->Enable();
157 chkAutoIndex->SetValue(true);
158 txtIndexName->Enable();
159 txtIndexName->SetValue(savedIndexName);
160 }
161
162 wxString idxName = txtIndexName->GetValue().Strip(wxString::both);
163
164 if (name != savedFKName || idxName == savedIndexName)
165 {
166 if (idxName.IsEmpty() || idxName == DefaultIndexName(savedFKName))
167 {
168 idxName = DefaultIndexName(name);
169 txtIndexName->SetValue(idxName);
170 }
171 }
172 savedIndexName = idxName;
173 }
174 else
175 {
176 if (chkAutoIndex->IsEnabled())
177 savedIndexName = txtIndexName->GetValue();
178
179 txtIndexName->SetValue(coveringIndex);
180 chkAutoIndex->SetValue(false);
181
182 txtIndexName->Disable();
183 chkAutoIndex->Disable();
184 }
185 }
186
187 savedFKName = name;
188 processing = false;
189
190 if (foreignKey)
191 {
192 bool enable = true;
193 if (chkAutoIndex->GetValue())
194 {
195 CheckValid(enable, !txtIndexName->GetValue().IsEmpty(),
196 _("Please specify covering index name."));
197 }
198 else
199 enable = txtName->GetValue() != foreignKey->GetName() || txtComment->GetValue() != foreignKey->GetComment();
200
201 if (connection->BackendMinimumVersion(9, 1) && !foreignKey->GetValid() && !chkDontValidate->GetValue())
202 enable = true;
203
204 EnableOK(enable);
205 }
206 else
207 {
208 bool enable = true;
209 txtComment->Enable(!name.IsEmpty());
210 CheckValid(enable, lstColumns->GetItemCount() > 0, _("Please specify columns."));
211 CheckValid(enable, !chkAutoIndex->GetValue() || !txtIndexName->GetValue().IsEmpty(),
212 _("Please specify covering index name."));
213 EnableOK(enable);
214 }
215
216 }
217
218
OnChangeValidate(wxCommandEvent & ev)219 void dlgForeignKey::OnChangeValidate(wxCommandEvent &ev)
220 {
221 CheckChange();
222 }
223
224
OnSelChangeCol(wxListEvent & ev)225 void dlgForeignKey::OnSelChangeCol(wxListEvent &ev)
226 {
227 btnRemoveRef->Enable();
228 }
229
230
OnSelChangeRefCol(wxCommandEvent & ev)231 void dlgForeignKey::OnSelChangeRefCol(wxCommandEvent &ev)
232 {
233 btnAddRef->Enable(cbColumns->GetCurrentSelection() >= 0 && cbRefColumns->GetCurrentSelection() >= 0);
234 }
235
236
OnSelChangeRef(wxCommandEvent & ev)237 void dlgForeignKey::OnSelChangeRef(wxCommandEvent &ev)
238 {
239 cbRefColumns->Clear();
240
241 wxString tab = cbReferences->GetValue();
242 wxString nsp;
243 if (tab.Find('.') >= 0)
244 {
245 nsp = tab.BeforeFirst('.');
246 tab = tab.AfterFirst('.');
247 }
248 else
249 nsp = database->GetDefaultSchema();
250
251 pgSet *set = connection->ExecuteSet(
252 wxT("SELECT attname\n")
253 wxT(" FROM pg_attribute att, pg_class cl, pg_namespace nsp\n")
254 wxT(" WHERE attrelid=cl.oid AND relnamespace=nsp.oid\n")
255 wxT(" AND nspname=") + qtDbString(nsp) +
256 wxT("\n AND relname=") + qtDbString(tab) +
257 wxT("\n AND attnum > 0\n")
258 wxT("\n AND NOT attisdropped\n")
259 wxT("\n ORDER BY attnum"));
260 if (set)
261 {
262 while (!set->Eof())
263 {
264 cbRefColumns->Append(set->GetVal(0));
265 set->MoveNext();
266 }
267 delete set;
268
269 if (cbRefColumns->GetCount())
270 cbRefColumns->SetSelection(0);
271 }
272
273 OnSelChangeRefCol(ev);
274 }
275
276
OnAddRef(wxCommandEvent & ev)277 void dlgForeignKey::OnAddRef(wxCommandEvent &ev)
278 {
279 wxString col = cbColumns->GetValue();
280 wxString ref = cbRefColumns->GetValue();
281 if (!col.IsEmpty() && !ref.IsEmpty())
282 {
283 lstColumns->AppendItem(columnFactory.GetIconId(), col, ref);
284 cbColumns->Delete(cbColumns->GetCurrentSelection());
285 cbRefColumns->Delete(cbRefColumns->GetCurrentSelection());
286 cbReferences->Disable();
287
288 if (cbColumns->GetCount())
289 cbColumns->SetSelection(0);
290
291 if (cbRefColumns->GetCount())
292 cbRefColumns->SetSelection(0);
293
294 OnSelChangeRefCol(ev);
295 CheckChange();
296 }
297 }
298
299
OnRemoveRef(wxCommandEvent & ev)300 void dlgForeignKey::OnRemoveRef(wxCommandEvent &ev)
301 {
302 long pos = lstColumns->GetSelection();
303
304 if (pos >= 0)
305 {
306 wxString col = lstColumns->GetText(pos);
307 wxString ref = lstColumns->GetText(pos, 1);
308 cbColumns->Append(col);
309 cbRefColumns->Append(ref);
310
311 lstColumns->DeleteItem(pos);
312 cbReferences->Enable(lstColumns->GetItemCount() == 0);
313 btnRemoveRef->Disable();
314 }
315 }
316
317
GetObject()318 pgObject *dlgForeignKey::GetObject()
319 {
320 return foreignKey;
321 }
322
323
CreateObject(pgCollection * collection)324 pgObject *dlgForeignKey::CreateObject(pgCollection *collection)
325 {
326 wxString name = GetName();
327 if (name.IsEmpty())
328 return 0;
329
330 pgObject *obj = foreignKeyFactory.CreateObjects(collection, 0, wxT(
331 "\n AND conname=") + qtDbString(name) + wxT(
332 "\n AND cl.relnamespace=") + table->GetSchema()->GetOidStr());
333 return obj;
334 }
335
336
Go(bool modal)337 int dlgForeignKey::Go(bool modal)
338 {
339 lstColumns->CreateColumns(0, _("Local"), _("Referenced"), -1);
340
341 processing = true; // protect from OnChange execution
342
343 btnAddRef->Disable();
344 btnRemoveRef->Disable();
345
346 if (readOnly)
347 {
348 chkAutoIndex->Disable();
349 txtIndexName->Disable();
350 }
351
352
353 if (foreignKey)
354 {
355 // edit mode
356 txtName->Enable(connection->BackendMinimumVersion(9, 2));
357 cbReferences->Append(foreignKey->GetReferences());
358 cbReferences->SetValue(foreignKey->GetReferences());
359 cbReferences->Disable();
360
361 chkDeferrable->SetValue(foreignKey->GetDeferrable());
362 chkDeferred->SetValue(foreignKey->GetDeferred());
363 chkMatchFull->SetValue(foreignKey->GetMatch() == wxT("FULL"));
364 if (connection->BackendMinimumVersion(9, 1))
365 chkDontValidate->SetValue(!foreignKey->GetValid());
366 chkDeferrable->Disable();
367 chkDeferred->Disable();
368 chkMatchFull->Disable();
369 chkDontValidate->Enable(connection->BackendMinimumVersion(9, 1));
370 if(!connection->BackendMinimumVersion(9, 1))
371 {
372 chkDontValidate->SetValue(true);
373 }
374 rbOnUpdate->SetStringSelection(foreignKey->GetOnUpdate());
375 rbOnDelete->SetStringSelection(foreignKey->GetOnDelete());
376 rbOnUpdate->Disable();
377 rbOnDelete->Disable();
378
379 chkAutoIndex->SetValue(false);
380 txtIndexName->SetValue(foreignKey->GetCoveringIndex());
381 if (!txtIndexName->GetValue().IsEmpty())
382 {
383 chkAutoIndex->Disable();
384 txtIndexName->Disable();
385 }
386
387 btnAddRef->Disable();
388 btnRemoveRef->Disable();
389 cbColumns->Disable();
390 cbRefColumns->Disable();
391
392 int pos = 0;
393 wxStringTokenizer cols(foreignKey->GetFkColumns(), wxT(","));
394 wxStringTokenizer refs(foreignKey->GetRefColumns(), wxT(","));
395 while (cols.HasMoreTokens())
396 {
397 wxString col = cols.GetNextToken().Trim(false).Trim(true);
398 wxString ref = refs.GetNextToken().Trim(false).Trim(true);
399 if (pos++)
400 {
401 if (col.Last() == ',')
402 col.RemoveLast();
403 if (ref.Last() == ',')
404 ref.RemoveLast();
405 }
406 lstColumns->AppendItem(columnFactory.GetIconId(), col, ref);
407 }
408 }
409 else
410 {
411 // create mode
412 txtComment->Disable();
413
414 chkDontValidate->Enable(connection->BackendMinimumVersion(9, 1));
415
416 wxString systemRestriction;
417 if (!settings->GetShowSystemObjects())
418 systemRestriction = wxT(" AND ") + connection->SystemNamespaceRestriction(wxT("nsp.nspname"));
419
420 wxString sql = wxT("SELECT nspname, relname FROM pg_namespace nsp, pg_class cl\n")
421 wxT(" WHERE relnamespace=nsp.oid AND relkind='r'\n");
422
423 if (connection->BackendMinimumVersion(8, 1))
424 sql += wxT(" AND nsp.nspname NOT LIKE E'pg\\_temp\\_%'\n");
425 else
426 sql += wxT(" AND nsp.nspname NOT LIKE 'pg\\_temp\\_%'\n");
427
428 sql += systemRestriction +
429 wxT(" ORDER BY nspname, relname");
430
431 pgSet *set = connection->ExecuteSet(sql);
432
433 if (set)
434 {
435 while (!set->Eof())
436 {
437 cbReferences->Append(database->GetSchemaPrefix(set->GetVal(0)) + set->GetVal(1));
438 set->MoveNext();
439 }
440 delete set;
441 cbReferences->SetSelection(0);
442 }
443 if (!table)
444 {
445 chkAutoIndex->Disable();
446 chkAutoIndex->SetValue(false);
447 txtIndexName->Disable();
448 cbClusterSet->Disable();
449 cbClusterSet = 0;
450 }
451 }
452
453 processing = false;
454
455 // Reset the labels as the XRC defined values will have been localised :-(
456
457 rbOnUpdate->SetString(0, wxT("NO ACTION"));
458 rbOnUpdate->SetString(1, wxT("RESTRICT"));
459 rbOnUpdate->SetString(2, wxT("CASCADE"));
460 rbOnUpdate->SetString(3, wxT("SET NULL"));
461 rbOnUpdate->SetString(4, wxT("SET DEFAULT"));
462
463 rbOnDelete->SetString(0, wxT("NO ACTION"));
464 rbOnDelete->SetString(1, wxT("RESTRICT"));
465 rbOnDelete->SetString(2, wxT("CASCADE"));
466 rbOnDelete->SetString(3, wxT("SET NULL"));
467 rbOnDelete->SetString(4, wxT("SET DEFAULT"));
468
469 wxCommandEvent nullEvent;
470 OnSelChangeRef(nullEvent);
471
472 return dlgCollistProperty::Go(modal);
473 }
474
475
GetSql()476 wxString dlgForeignKey::GetSql()
477 {
478 wxString sql;
479 wxString name = GetName();
480
481 if (!foreignKey)
482 {
483 sql = wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
484 + wxT("\n ADD");
485 AppendIfFilled(sql, wxT(" CONSTRAINT "), qtIdent(name));
486 sql += wxT(" FOREIGN KEY ") + GetDefinition()
487 + wxT(";\n");
488 }
489 else
490 {
491 if (foreignKey->GetName() != name)
492 {
493 sql = wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
494 + wxT("\n RENAME CONSTRAINT ") + qtIdent(foreignKey->GetName())
495 + wxT(" TO ") + qtIdent(name) + wxT(";\n");
496 }
497 if (connection->BackendMinimumVersion(9, 1) && !foreignKey->GetValid() && !chkDontValidate->GetValue())
498 {
499 sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
500 + wxT("\n VALIDATE CONSTRAINT ") + qtIdent(name) + wxT(";\n");
501 }
502 }
503
504 if (!name.IsEmpty())
505 AppendComment(sql, wxT("CONSTRAINT ") + qtIdent(name)
506 + wxT(" ON ") + table->GetQuotedFullIdentifier(), foreignKey);
507
508 if (chkAutoIndex->GetValue())
509 {
510 sql += wxT("CREATE INDEX ") + qtIdent(txtIndexName->GetValue())
511 + wxT("\n ON ") + table->GetQuotedFullIdentifier()
512 + wxT("(");
513
514 int pos;
515 for (pos = 0 ; pos < lstColumns->GetItemCount() ; pos++)
516 {
517 if (pos)
518 sql += wxT(", ");
519
520 sql += qtIdent(lstColumns->GetText(pos));
521 }
522
523 sql += wxT(");\n");
524 }
525 return sql;
526 }
527
528
GetDefinition()529 wxString dlgForeignKey::GetDefinition()
530 {
531 wxString sql;
532 wxString cols, refs;
533
534 int pos;
535
536 for (pos = 0 ; pos < lstColumns->GetItemCount() ; pos++)
537 {
538 if (pos)
539 {
540 cols += wxT(", ");
541 refs += wxT(", ");
542 }
543 cols += qtIdent(lstColumns->GetText(pos));
544 refs += qtIdent(lstColumns->GetText(pos, 1));
545 }
546
547 sql = wxT("(") + cols
548 + wxT(") REFERENCES ");
549 AppendQuoted(sql, cbReferences->GetValue());
550 sql += wxT(" (") + refs
551 + wxT(")");
552
553 if (chkMatchFull->GetValue())
554 sql += wxT(" MATCH FULL");
555
556 sql += wxT("\n ")
557 wxT(" ON UPDATE ") + rbOnUpdate->GetStringSelection() +
558 wxT(" ON DELETE ") + rbOnDelete->GetStringSelection();
559
560 if (chkDeferrable->GetValue())
561 sql += wxT("\n DEFERRABLE");
562 if (chkDeferred->GetValue())
563 sql += wxT(" INITIALLY DEFERRED");
564
565 if (chkDontValidate->GetValue())
566 sql += wxT("\n NOT VALID");
567
568 return sql;
569 }
570