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 // dlgIndex.cpp - PostgreSQL Index 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 "dlg/dlgIndex.h"
20 #include "schema/pgIndex.h"
21 #include "schema/pgColumn.h"
22 #include "schema/pgTable.h"
23
24
25 #define cbTablespace CTRL_COMBOBOX("cbTablespace")
26 #define cbType CTRL_COMBOBOX("cbType")
27 #define chkUnique CTRL_CHECKBOX("chkUnique")
28 #define chkClustered CTRL_CHECKBOX("chkClustered")
29 #define chkConcurrent CTRL_CHECKBOX("chkConcurrent")
30 #define txtWhere CTRL_TEXT("txtWhere")
31 #define txtFillFactor CTRL_TEXT("txtFillFactor")
32 #define cbOpClass CTRL_COMBOBOX("cbOpClass")
33 #define chkDesc CTRL_CHECKBOX("chkDesc")
34 #define rdbNullsFirst CTRL_RADIOBUTTON("rdbNullsFirst")
35 #define rdbNullsLast CTRL_RADIOBUTTON("rdbNullsLast")
36 #define cbCollation CTRL_COMBOBOX("cbCollation")
37
38
39 BEGIN_EVENT_TABLE(dlgIndexBase, dlgCollistProperty)
40 EVT_TEXT(XRCID("cbTablespace"), dlgProperty::OnChange)
41 EVT_COMBOBOX(XRCID("cbTablespace"), dlgProperty::OnChange)
42 EVT_TEXT(XRCID("txtFillFactor"), dlgProperty::OnChange)
43 EVT_LIST_ITEM_SELECTED(XRCID("lstColumns"), dlgIndexBase::OnSelectListCol)
44 EVT_COMBOBOX(XRCID("cbColumns"), dlgIndexBase::OnSelectComboCol)
45 END_EVENT_TABLE();
46
47
CreateDialog(frmMain * frame,pgObject * node,pgObject * parent)48 dlgProperty *pgIndexFactory::CreateDialog(frmMain *frame, pgObject *node, pgObject *parent)
49 {
50 return new dlgIndex(this, frame, (pgIndex *)node, (pgTable *)parent);
51 }
52
53
dlgIndexBase(pgaFactory * f,frmMain * frame,const wxString & resName,pgIndexBase * node,pgTable * parentNode)54 dlgIndexBase::dlgIndexBase(pgaFactory *f, frmMain *frame, const wxString &resName, pgIndexBase *node, pgTable *parentNode)
55 : dlgCollistProperty(f, frame, resName, parentNode)
56 {
57 index = node;
58 wxASSERT(!table || table->GetMetaType() == PGM_TABLE || table->GetMetaType() == GP_PARTITION);
59 }
60
61
dlgIndexBase(pgaFactory * f,frmMain * frame,const wxString & resName,ctlListView * colList)62 dlgIndexBase::dlgIndexBase(pgaFactory *f, frmMain *frame, const wxString &resName, ctlListView *colList)
63 : dlgCollistProperty(f, frame, resName, colList)
64 {
65 index = 0;
66 }
67
68
GetObject()69 pgObject *dlgIndexBase::GetObject()
70 {
71 return index;
72 }
73
74
Go(bool modal)75 int dlgIndexBase::Go(bool modal)
76 {
77
78 if (index)
79 {
80 // edit mode
81 txtName->Enable(connection->BackendMinimumVersion(9, 2));
82 cbColumns->Disable();
83
84 if (txtFillFactor)
85 {
86 txtFillFactor->SetValue(index->GetFillFactor());
87 }
88 }
89 else
90 {
91 // create mode
92 }
93
94 if (txtFillFactor)
95 {
96 txtFillFactor->SetValidator(numericValidator);
97 if (connection->BackendMinimumVersion(8, 2))
98 txtFillFactor->Enable();
99 else
100 txtFillFactor->Disable();
101 }
102
103 btnAddCol->Disable();
104 btnRemoveCol->Disable();
105
106 return dlgCollistProperty::Go(modal);
107 }
108
OnSelectListCol(wxListEvent & ev)109 void dlgIndexBase::OnSelectListCol(wxListEvent &ev)
110 {
111 OnSelectCol();
112 }
113
OnSelectComboCol(wxCommandEvent & ev)114 void dlgIndexBase::OnSelectComboCol(wxCommandEvent &ev)
115 {
116 if (cbType)
117 {
118 wxString method = wxEmptyString;
119
120 if (cbType->GetValue().Length() == 0)
121 {
122 method = cbType->GetStringKey(1);
123 }
124 else
125 {
126 method = cbType->GetStringKey(cbType->GetCurrentSelection());
127 }
128
129 cbOpClass->Clear();
130
131 wxString sql = wxT("SELECT opcname FROM pg_opclass ")
132 wxT("WHERE opcmethod=") + method +
133 wxT(" AND NOT opcdefault")
134 wxT(" ORDER BY 1");
135 pgSet *set = connection->ExecuteSet(sql);
136 if (set)
137 {
138 while (!set->Eof())
139 {
140 cbOpClass->Append(set->GetVal(0));
141 set->MoveNext();
142 }
143 delete set;
144 }
145 }
146
147 OnSelectCol();
148 }
149
OnSelectCol()150 void dlgIndexBase::OnSelectCol()
151 {
152 // Can't change the columns on an existing index.
153 if (index)
154 return;
155
156 if (lstColumns->GetSelection() != wxNOT_FOUND)
157 btnRemoveCol->Enable(true);
158 else
159 btnRemoveCol->Enable(false);
160
161 if (cbColumns->GetSelection() != wxNOT_FOUND && !cbColumns->GetValue().IsEmpty())
162 btnAddCol->Enable(true);
163 else
164 btnAddCol->Enable(false);
165 }
166
167
CheckChange()168 void dlgIndexBase::CheckChange()
169 {
170 if (index)
171 {
172 EnableOK(txtName->GetValue() != index->GetName() ||
173 txtComment->GetValue() != index->GetComment() ||
174 cbTablespace->GetOIDKey() != index->GetTablespaceOid() ||
175 txtFillFactor->GetValue() != index->GetFillFactor());
176 }
177 else
178 {
179 bool enable = true;
180 txtComment->Enable(!GetName().IsEmpty());
181 CheckValid(enable, lstColumns->GetItemCount() > 0, _("Please specify columns."));
182 EnableOK(enable);
183 }
184 }
185
186
187 BEGIN_EVENT_TABLE(dlgIndex, dlgIndexBase)
188 EVT_BUTTON(XRCID("btnAddCol"), dlgIndex::OnAddCol)
189 EVT_BUTTON(XRCID("btnRemoveCol"), dlgIndex::OnRemoveCol)
190 EVT_CHECKBOX(XRCID("chkClustered"), dlgProperty::OnChange)
191 EVT_CHECKBOX(XRCID("chkDesc"), dlgIndex::OnDescChange)
192 #ifdef __WXMAC__
193 EVT_SIZE( dlgIndex::OnChangeSize)
194 #endif
195 EVT_COMBOBOX(XRCID("cbType"), dlgIndex::OnSelectType)
196 END_EVENT_TABLE();
197
198
dlgIndex(pgaFactory * f,frmMain * frame,pgIndex * index,pgTable * parentNode)199 dlgIndex::dlgIndex(pgaFactory *f, frmMain *frame, pgIndex *index, pgTable *parentNode)
200 : dlgIndexBase(f, frame, wxT("dlgIndex"), index, parentNode)
201 {
202 lstColumns->AddColumn(_("Column name"), 90);
203 lstColumns->AddColumn(_("Order"), 40);
204 lstColumns->AddColumn(_("NULLs order"), 50);
205 lstColumns->AddColumn(_("Op. class"), 40);
206 lstColumns->AddColumn(_("Collation"), 40);
207 }
208
209
CheckChange()210 void dlgIndex::CheckChange()
211 {
212 bool fill = false;
213
214 txtComment->Enable(!txtName->GetValue().IsEmpty());
215 chkClustered->Enable(!txtName->GetValue().IsEmpty());
216
217 if (index)
218 {
219 if (txtFillFactor)
220 {
221 fill = txtFillFactor->GetValue() != index->GetFillFactor() && !txtFillFactor->GetValue().IsEmpty();
222 }
223
224 EnableOK(fill ||
225 txtComment->GetValue() != index->GetComment() ||
226 chkClustered->GetValue() != index->GetIsClustered() ||
227 cbTablespace->GetOIDKey() != index->GetTablespaceOid() ||
228 (txtName->GetValue() != index->GetName() &&
229 txtName->GetValue().Length() != 0));
230 }
231 else
232 {
233 wxString name = GetName();
234
235 bool enable = true;
236 CheckValid(enable, !name.IsEmpty() || (name.IsEmpty() && this->database->BackendMinimumVersion(9, 0)), _("Please specify name."));
237 CheckValid(enable, lstColumns->GetItemCount() > 0, _("Please specify columns."));
238 EnableOK(enable);
239 }
240 }
241
OnSelectType(wxCommandEvent & ev)242 void dlgIndex::OnSelectType(wxCommandEvent &ev)
243 {
244 // The column options available change depending on the
245 // index type. We need to clear the column list, and
246 // setup some of the other controls accordingly.
247
248 wxString newType = cbType->GetValue();
249 bool changingDefault = false;
250
251 // Detect if we're changing between default and btree (which are the same) to
252 // avoid annoying the user needlessly.
253 if ((m_previousType == wxEmptyString && cbType->GetValue() == wxT("btree")) ||
254 (m_previousType == wxT("btree") && cbType->GetValue() == wxEmptyString))
255 changingDefault = true;
256
257 if (lstColumns->GetItemCount() > 0 && !changingDefault)
258 {
259 if (wxMessageBox(_("Changing the index type will cause the column list to be cleared. Do you wish to continue?"), _("Change index type?"), wxYES_NO) != wxYES)
260 {
261 cbType->SetValue(m_previousType);
262 return;
263 }
264
265 // Move all the columns back to the combo
266 for (int pos = lstColumns->GetItemCount(); pos > 0; pos--)
267 {
268 wxString colName = lstColumns->GetItemText(pos - 1);
269
270 lstColumns->DeleteItem(pos - 1);
271 cbColumns->Append(colName);
272 }
273 }
274
275 if (newType == wxT("btree") || newType == wxEmptyString)
276 {
277 cbOpClass->Enable(true);
278 chkDesc->Enable(true);
279 rdbNullsFirst->Enable(true);
280 rdbNullsLast->Enable(true);
281 }
282 else
283 {
284 cbOpClass->Enable(false);
285 chkDesc->Enable(false);
286 rdbNullsFirst->Enable(false);
287 rdbNullsLast->Enable(false);
288 }
289
290 // Make a note of the type so we can compare if it changes again.
291 m_previousType = cbType->GetValue();
292 }
293
294
GetColumns()295 wxString dlgIndex::GetColumns()
296 {
297 wxString sql;
298
299 int pos;
300 // iterate cols
301 for (pos = 0 ; pos < lstColumns->GetItemCount() ; pos++)
302 {
303 if (pos)
304 sql += wxT(", ");
305
306 sql += qtIdent(lstColumns->GetItemText(pos));
307
308 if (this->database->BackendMinimumVersion(9, 1))
309 {
310 wxString collation = lstColumns->GetText(pos, 4);
311 if (!collation.IsEmpty() && collation != wxT("pg_catalog.\"default\""))
312 sql += wxT(" COLLATE ") + collation;
313 }
314
315 wxString opclass = lstColumns->GetText(pos, 3);
316 if (!opclass.IsEmpty())
317 sql += wxT(" ") + opclass;
318
319 if (this->database->BackendMinimumVersion(8, 3))
320 {
321 wxString order = lstColumns->GetText(pos, 1);
322 if (!order.IsEmpty())
323 sql += wxT(" ") + order;
324
325 wxString nullsOrder = lstColumns->GetText(pos, 2);
326 if (!nullsOrder.IsEmpty())
327 sql += wxT(" NULLS ") + nullsOrder;
328 }
329 }
330 return sql;
331 }
332
333
Go(bool modal)334 int dlgIndex::Go(bool modal)
335 {
336 if (!connection->BackendMinimumVersion(7, 4))
337 chkClustered->Disable();
338
339 if (index)
340 {
341 // edit mode: view only
342
343 // We only display the column options (ASC/DESC, NULLS FIRST/LAST)
344 // on PostgreSQL 8.3+, for btree indexes.
345 wxArrayString colsArr = index->GetColumnList();
346 wxArrayString collationsArray = index->GetCollationsArray();
347 wxString colDef, descDef, nullsDef, opclassDef;
348 if (this->database->BackendMinimumVersion(8, 3) && index->GetIndexType() == wxT("btree"))
349 {
350 for (unsigned int colIdx = 0, colsCount = colsArr.Count(); colIdx < colsCount; colIdx++)
351 {
352 colDef = colsArr.Item(colIdx);
353 descDef = index->GetOrdersArray().Item(colIdx);
354 nullsDef = index->GetNullsArray().Item(colIdx);
355 opclassDef = index->GetOpClassesArray().Item(colIdx);
356
357 lstColumns->InsertItem(colIdx, colDef, columnFactory.GetIconId());
358 lstColumns->SetItem(colIdx, 1, descDef);
359 lstColumns->SetItem(colIdx, 2, nullsDef);
360 lstColumns->SetItem(colIdx, 3, opclassDef);
361 if (colIdx < collationsArray.Count())
362 lstColumns->SetItem(colIdx, 4, collationsArray.Item(colIdx));
363 }
364 }
365 else
366 {
367 for (unsigned int colIdx = 0, colsCount = colsArr.Count(); colIdx < colsCount; colIdx++)
368 {
369 int pos = colDef.First(wxT(" "));
370 if (pos > 0)
371 {
372 opclassDef = colDef.Mid(pos + 1);
373 colDef = colDef.Mid(0, pos);
374 }
375 else
376 opclassDef = wxEmptyString;
377
378 lstColumns->InsertItem(colIdx, colsArr.Item(colIdx), columnFactory.GetIconId());
379 lstColumns->SetItem(colIdx, 3, cbOpClass->GetValue());
380 if (colIdx < collationsArray.Count())
381 lstColumns->SetItem(colIdx, 4, collationsArray.Item(colIdx));
382 }
383 }
384
385 cbType->Append(index->GetIndexType());
386 chkUnique->SetValue(index->GetIsUnique());
387 chkClustered->SetValue(index->GetIsClustered());
388 txtWhere->SetValue(index->GetConstraint());
389 cbType->SetSelection(0);
390 cbType->Disable();
391 txtWhere->Disable();
392 chkUnique->Disable();
393 chkConcurrent->Disable();
394 PrepareTablespace(cbTablespace, index->GetTablespaceOid());
395 cbOpClass->Disable();
396 chkDesc->Disable();
397 rdbNullsFirst->Disable();
398 rdbNullsLast->Disable();
399 cbCollation->Disable();
400 lstColumns->Disable();
401 }
402 else
403 {
404 // create mode
405 PrepareTablespace(cbTablespace);
406 cbType->Append(wxT(""));
407 pgSet *set = connection->ExecuteSet(wxT(
408 "SELECT oid, amname FROM pg_am"));
409 if (set)
410 {
411 while (!set->Eof())
412 {
413 cbType->Append(set->GetVal(1), set->GetVal(0));
414 set->MoveNext();
415 }
416 delete set;
417 }
418
419 if (connection->BackendMinimumVersion(9, 1))
420 {
421 // fill collation combobox
422 cbCollation->Append(wxEmptyString);
423 set = connection->ExecuteSet(
424 wxT("SELECT nspname, collname\n")
425 wxT(" FROM pg_collation c, pg_namespace n\n")
426 wxT(" WHERE c.collnamespace=n.oid\n")
427 wxT(" ORDER BY nspname, collname"));
428 if (set)
429 {
430 while (!set->Eof())
431 {
432 wxString name = qtIdent(set->GetVal(wxT("nspname"))) + wxT(".") + qtIdent(set->GetVal(wxT("collname")));
433 cbCollation->Append(name);
434 set->MoveNext();
435 }
436 delete set;
437 }
438 cbCollation->SetSelection(0);
439 }
440 else
441 cbCollation->Disable();
442
443 if (!this->database->BackendMinimumVersion(8, 2))
444 chkConcurrent->Disable();
445
446 if (!this->database->BackendMinimumVersion(8, 3))
447 {
448 chkDesc->Disable();
449 rdbNullsFirst->Disable();
450 rdbNullsLast->Disable();
451 }
452
453 // Add the default tablespace
454 cbTablespace->Insert(_("<default tablespace>"), 0, (void *)0);
455 cbTablespace->SetSelection(0);
456 }
457
458 // Reset the labels as the XRC defined values will have been localised :-(
459 rdbNullsFirst->SetLabel(wxT("FIRST"));
460 rdbNullsLast->SetLabel(wxT("LAST"));
461
462 int returnCode = dlgIndexBase::Go(modal);
463
464 if (index && connection->BackendMinimumVersion(8, 0))
465 txtName->Enable(true);
466
467 // This fixes a UI glitch on MacOS X
468 // Because of the new layout code, the Columns pane doesn't size itself properly
469 SetSize(GetSize().GetWidth() + 1, GetSize().GetHeight());
470 SetSize(GetSize().GetWidth() - 1, GetSize().GetHeight());
471
472 return returnCode;
473 }
474
475
OnAddCol(wxCommandEvent & ev)476 void dlgIndex::OnAddCol(wxCommandEvent &ev)
477 {
478 wxString colName = cbColumns->GetValue();
479
480 if (!colName.IsEmpty())
481 {
482 long colIndex = lstColumns->InsertItem(lstColumns->GetItemCount(), colName, columnFactory.GetIconId());
483
484
485 if (this->database->BackendMinimumVersion(8, 3))
486 {
487 if (chkDesc->GetValue())
488 {
489 if (chkDesc->IsEnabled())
490 lstColumns->SetItem(colIndex, 1, wxT("DESC"));
491
492
493 if (rdbNullsLast->GetValue())
494 {
495 if (rdbNullsLast->IsEnabled())
496 lstColumns->SetItem(colIndex, 2, wxT("LAST"));
497 }
498 else
499 {
500 if (rdbNullsLast->IsEnabled())
501 lstColumns->SetItem(colIndex, 2, wxT("FIRST"));
502 }
503 }
504 else
505 {
506 if (chkDesc->IsEnabled())
507 lstColumns->SetItem(colIndex, 1, wxT("ASC"));
508
509 if (rdbNullsFirst->GetValue())
510 {
511 if (rdbNullsFirst->IsEnabled())
512 lstColumns->SetItem(colIndex, 2, wxT("FIRST"));
513 }
514 else
515 {
516 if (rdbNullsLast->IsEnabled())
517 lstColumns->SetItem(colIndex, 2, wxT("LAST"));
518 }
519 }
520
521 lstColumns->SetItem(colIndex, 3, cbOpClass->GetValue());
522 lstColumns->SetItem(colIndex, 4, cbCollation->GetValue());
523 }
524
525 cbColumns->Delete(cbColumns->GetCurrentSelection());
526 if (cbColumns->GetCount())
527 cbColumns->SetSelection(0);
528
529 CheckChange();
530 if (!cbColumns->GetCount())
531 btnAddCol->Disable();
532 }
533 }
534
535
OnRemoveCol(wxCommandEvent & ev)536 void dlgIndex::OnRemoveCol(wxCommandEvent &ev)
537 {
538 long pos = lstColumns->GetSelection();
539 if (pos >= 0)
540 {
541 wxString colName = lstColumns->GetItemText(pos);
542
543 lstColumns->DeleteItem(pos);
544 cbColumns->Append(colName);
545
546 CheckChange();
547 btnRemoveCol->Disable();
548 }
549 }
550
551 #ifdef __WXMAC__
OnChangeSize(wxSizeEvent & ev)552 void dlgIndex::OnChangeSize(wxSizeEvent &ev)
553 {
554 lstColumns->SetSize(wxDefaultCoord, wxDefaultCoord,
555 ev.GetSize().GetWidth(), ev.GetSize().GetHeight() - 700);
556 if (GetAutoLayout())
557 {
558 Layout();
559 }
560 }
561 #endif
562
GetSql()563 wxString dlgIndex::GetSql()
564 {
565 wxString sql;
566
567 if (table)
568 {
569 wxString name = GetName();
570 if (!index)
571 {
572 sql = wxT("CREATE ");
573 if (chkUnique->GetValue())
574 sql += wxT("UNIQUE ");
575
576 sql += wxT("INDEX ");
577
578 if (chkConcurrent->GetValue())
579 sql += wxT("CONCURRENTLY ");
580
581 sql += qtIdent(name);
582
583 sql += wxT("\n ON ") + table->GetQuotedFullIdentifier();
584
585 if (cbType->GetCurrentSelection() > 0)
586 AppendIfFilled(sql, wxT(" USING "), cbType->GetValue());
587
588 sql += wxT(" (") + GetColumns()
589 + wxT(")");
590
591 if (txtFillFactor)
592 {
593 if (connection->BackendMinimumVersion(8, 2) && txtFillFactor->GetValue().Length() > 0)
594 sql += wxT("\n WITH (FILLFACTOR=") + txtFillFactor->GetValue() + wxT(")");
595 }
596
597 if (cbTablespace->GetOIDKey() > 0)
598 AppendIfFilled(sql, wxT("\n TABLESPACE "), qtIdent(cbTablespace->GetValue()));
599
600 AppendIfFilled(sql, wxT(" WHERE "), txtWhere->GetValue());
601 sql += wxT(";\n");
602 }
603 else
604 {
605 if (connection->BackendMinimumVersion(8, 2) && txtFillFactor->GetValue().Length() > 0)
606 sql += wxT("ALTER INDEX ") + qtIdent(index->GetSchema()->GetName()) + wxT(".")
607 + qtIdent(index->GetName()) + wxT("\n SET (FILLFACTOR=")
608 + txtFillFactor->GetValue() + wxT(");\n");
609
610 if(connection->BackendMinimumVersion(8, 0))
611 {
612 if (index->GetName() != txtName->GetValue() &&
613 !txtName->GetValue().IsEmpty())
614 sql += wxT("ALTER INDEX ") + qtIdent(index->GetSchema()->GetName()) + wxT(".")
615 + qtIdent(index->GetName()) + wxT("\n RENAME TO ")
616 + qtIdent(txtName->GetValue()) + wxT(";\n");
617
618 if (cbTablespace->GetOIDKey() != index->GetTablespaceOid())
619 sql += wxT("ALTER INDEX ") + qtIdent(index->GetSchema()->GetName()) + wxT(".") + qtIdent(name)
620 + wxT("\n SET TABLESPACE ") + qtIdent(cbTablespace->GetValue())
621 + wxT(";\n");
622 }
623 }
624 if (connection->BackendMinimumVersion(7, 4) && chkClustered->IsEnabled())
625 {
626 if (index && index->GetIsClustered() && !chkClustered->GetValue())
627 sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
628 + wxT("\n SET WITHOUT CLUSTER;\n");
629 else if (chkClustered->GetValue() && (!index || !index->GetIsClustered()))
630 sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
631 + wxT("\n CLUSTER ON ") + qtIdent(name) + wxT(";\n");
632 }
633
634 if (txtComment->IsEnabled())
635 {
636 AppendComment(sql, wxT("INDEX"), table->GetSchema(), index);
637 }
638 }
639 return sql;
640 }
641
642
CreateObject(pgCollection * collection)643 pgObject *dlgIndex::CreateObject(pgCollection *collection)
644 {
645 wxString name = GetName();
646
647 pgObject *obj = indexFactory.CreateObjects(collection, 0, wxT(
648 "\n AND cls.relname=") + qtDbString(name) + wxT(
649 "\n AND cls.relnamespace=") + table->GetSchema()->GetOidStr());
650 return obj;
651 }
652
OnDescChange(wxCommandEvent & ev)653 void dlgIndex::OnDescChange(wxCommandEvent &ev)
654 {
655 if (chkDesc->GetValue())
656 {
657 rdbNullsFirst->SetValue(true);
658 }
659 else
660 {
661 rdbNullsLast->SetValue(true);
662 }
663 }
664