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 // pgIndex.cpp - Index class
9 //
10 //////////////////////////////////////////////////////////////////////////
11
12 // wxWindows headers
13 #include <wx/wx.h>
14
15 // App headers
16 #include "pgAdmin3.h"
17 #include "frm/frmMain.h"
18 #include "utils/misc.h"
19 #include "utils/pgfeatures.h"
20 #include "schema/pgIndex.h"
21 #include "schema/pgConstraints.h"
22 #include "schema/pgIndexConstraint.h"
23
24
pgIndexBase(pgSchema * newSchema,pgaFactory & factory,const wxString & newName)25 pgIndexBase::pgIndexBase(pgSchema *newSchema, pgaFactory &factory, const wxString &newName)
26 : pgSchemaObject(newSchema, factory, newName)
27 {
28 showExtendedStatistics = false;
29 }
30
GetTranslatedMessage(int kindOfMessage) const31 wxString pgIndexBase::GetTranslatedMessage(int kindOfMessage) const
32 {
33 wxString message = wxEmptyString;
34
35 switch (kindOfMessage)
36 {
37 case RETRIEVINGDETAILS:
38 message = _("Retrieving details on index");
39 message += wxT(" ") + GetName();
40 break;
41 case REFRESHINGDETAILS:
42 message = _("Refreshing index");
43 message += wxT(" ") + GetName();
44 break;
45 case GRANTWIZARDTITLE:
46 message = _("Privileges for index");
47 message += wxT(" ") + GetName();
48 break;
49 case DROPINCLUDINGDEPS:
50 message = wxString::Format(_("Are you sure you wish to drop index \"%s\" including all objects that depend on it?"),
51 GetFullIdentifier().c_str());
52 break;
53 case DROPEXCLUDINGDEPS:
54 message = wxString::Format(_("Are you sure you wish to drop index \"%s\"?"),
55 GetFullIdentifier().c_str());
56 break;
57 case DROPCASCADETITLE:
58 message = _("Drop index cascaded?");
59 break;
60 case DROPTITLE:
61 message = _("Drop index?");
62 break;
63 case PROPERTIESREPORT:
64 message = _("Index properties report");
65 message += wxT(" - ") + GetName();
66 break;
67 case PROPERTIES:
68 message = _("Index properties");
69 break;
70 case DDLREPORT:
71 message = _("Index DDL report");
72 message += wxT(" - ") + GetName();
73 break;
74 case DDL:
75 message = _("Index DDL");
76 break;
77 case STATISTICSREPORT:
78 message = _("Index statistics report");
79 message += wxT(" - ") + GetName();
80 break;
81 case OBJSTATISTICS:
82 message = _("Index statistics");
83 break;
84 case DEPENDENCIESREPORT:
85 message = _("Index dependencies report");
86 message += wxT(" - ") + GetName();
87 break;
88 case DEPENDENCIES:
89 message = _("Index dependencies");
90 break;
91 case DEPENDENTSREPORT:
92 message = _("Index dependents report");
93 message += wxT(" - ") + GetName();
94 break;
95 case DEPENDENTS:
96 message = _("Index dependents");
97 break;
98 }
99
100 return message;
101 }
102
DropObject(wxFrame * frame,ctlTree * browser,bool cascaded)103 bool pgIndexBase::DropObject(wxFrame *frame, ctlTree *browser, bool cascaded)
104 {
105 wxString sql = wxT("DROP INDEX ") + this->GetSchema()->GetQuotedIdentifier() + wxT(".") + this->GetQuotedIdentifier();
106 if (cascaded)
107 sql += wxT(" CASCADE");
108 return GetDatabase()->ExecuteVoid(sql);
109 }
110
GetCreate()111 wxString pgIndexBase::GetCreate()
112 {
113 wxString str;
114 // no functional indexes so far
115
116 str = wxT("CREATE ");
117 if (GetIsUnique())
118 str += wxT("UNIQUE ");
119 str += wxT("INDEX ");
120 str += qtIdent(GetName())
121 + wxT("\n ON ") + GetQuotedSchemaPrefix(GetIdxSchema()) + qtIdent(GetIdxTable())
122 + wxT("\n USING ") + GetIndexType()
123 + wxT("\n (");
124 if (GetProcName().IsNull())
125 str += GetQuotedColumns();
126 else
127 {
128 str += GetQuotedSchemaPrefix(GetProcNamespace()) + qtIdent(GetProcName()) + wxT("(") + GetQuotedColumns() + wxT(")");
129 if (!this->GetOperatorClasses().IsNull())
130 str += wxT(" ") + GetOperatorClasses();
131 }
132
133 str += wxT(")");
134
135 if (GetConnection()->BackendMinimumVersion(8, 2) && GetFillFactor().Length() > 0)
136 str += wxT("\n WITH (FILLFACTOR=") + GetFillFactor() + wxT(")");
137
138 if (GetConnection()->BackendMinimumVersion(8, 0) && tablespace != GetDatabase()->GetDefaultTablespace())
139 str += wxT("\nTABLESPACE ") + qtIdent(tablespace);
140
141 AppendIfFilled(str, wxT("\n WHERE "), GetConstraint());
142
143 str += wxT(";\n");
144
145 if (GetConnection()->BackendMinimumVersion(7, 5))
146 if (GetIsClustered())
147 str += wxT("ALTER TABLE ") + GetQuotedSchemaPrefix(GetIdxSchema()) + qtIdent(GetIdxTable())
148 + wxT(" CLUSTER ON ") + qtIdent(GetName())
149 + wxT(";\n");
150
151 return str;
152 }
153
154
GetSql(ctlTree * browser)155 wxString pgIndexBase::GetSql(ctlTree *browser)
156 {
157 if (sql.IsNull())
158 {
159 sql = wxT("-- Index: ") + GetQuotedFullIdentifier() + wxT("\n\n")
160 + wxT("-- DROP INDEX ") + GetQuotedFullIdentifier() + wxT(";\n\n")
161 + GetCreate()
162 + GetCommentSql();
163 }
164 return sql;
165 }
166
167
168
ReadColumnDetails()169 void pgIndexBase::ReadColumnDetails()
170 {
171 if (!expandedKids)
172 {
173 expandedKids = true;
174 bool indexconstraint = GetMetaType() == PGM_PRIMARYKEY || GetMetaType() == PGM_UNIQUE || GetMetaType() == PGM_EXCLUDE;
175
176 // Allocate memory to store column def
177 if (columnCount > 0) columnList.Alloc(columnCount);
178
179 if (GetConnection()->BackendMinimumVersion(7, 4))
180 {
181 long i;
182
183 for (i = 1 ; i <= columnCount ; i++)
184 {
185 if (i > 1)
186 {
187 columns += wxT(", ");
188 quotedColumns += wxT(", ");
189 }
190
191 wxString options, coldef, opcname;
192 if (GetConnection()->BackendMinimumVersion(8, 3))
193 options = wxT(" i.indoption[") + NumToStr((long)(i - 1)) + wxT("] AS options,\n");
194
195 pgSet *res;
196 wxString query;
197
198 if (GetConnection()->BackendMinimumVersion(9, 0))
199 {
200 query = wxT("SELECT\n") + options +
201 wxT(" pg_get_indexdef(i.indexrelid, ") + NumToStr(i) + GetDatabase()->GetPrettyOption() + wxT(") AS coldef,\n") +
202 wxT(" op.oprname,\n") +
203 wxT(" CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname\n");
204 if (GetConnection()->BackendMinimumVersion(9, 1))
205 query += wxT(",\n coll.collname, nspc.nspname as collnspname\n");
206 query += wxT("FROM pg_index i\n")
207 wxT("JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = ") + NumToStr(i) + wxT(")\n") +
208 wxT("LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass[") + NumToStr((long)(i - 1)) + wxT("])\n") +
209 wxT("LEFT OUTER JOIN pg_constraint c ON (c.conindid = i.indexrelid) ")
210 wxT("LEFT OUTER JOIN pg_operator op ON (op.oid = c.conexclop[") + NumToStr(i) + wxT("])\n");
211 if (GetConnection()->BackendMinimumVersion(9, 1))
212 query += wxT("LEFT OUTER JOIN pg_collation coll ON a.attcollation=coll.oid\n")
213 wxT("LEFT OUTER JOIN pg_namespace nspc ON coll.collnamespace=nspc.oid\n");
214 query += wxT("WHERE i.indexrelid = ") + GetOidStr();
215 }
216 else
217 {
218 query = wxT("SELECT\n") + options +
219 wxT(" pg_get_indexdef(i.indexrelid, ") + NumToStr(i) + GetDatabase()->GetPrettyOption() + wxT(") AS coldef,\n") +
220 wxT(" CASE WHEN (o.opcdefault = FALSE) THEN o.opcname ELSE null END AS opcname\n") +
221 wxT("FROM pg_index i\n") +
222 wxT("JOIN pg_attribute a ON (a.attrelid = i.indexrelid AND attnum = ") + NumToStr(i) + wxT(")\n") +
223 wxT("LEFT OUTER JOIN pg_opclass o ON (o.oid = i.indclass[") + NumToStr((long)(i - 1)) + wxT("])\n") +
224 wxT("WHERE i.indexrelid = ") + GetOidStr();
225 }
226
227 res = ExecuteSet(query);
228
229 if (res->NumRows() > 0)
230 {
231 coldef = res->GetVal(wxT("coldef"));
232
233 if (GetConnection()->BackendMinimumVersion(9, 1) && !indexconstraint)
234 {
235 wxString collation = wxEmptyString;
236 if (!res->GetVal(wxT("collname")).IsEmpty())
237 {
238 collation = qtIdent(res->GetVal(wxT("collnspname"))) + wxT(".") + qtIdent(res->GetVal(wxT("collname")));
239 coldef += wxT(" COLLATE ") + collation;
240 }
241 collationsArray.Add(collation);
242 }
243 else
244 {
245 collationsArray.Add(wxEmptyString);
246 }
247
248 opcname = res->GetVal(wxT("opcname"));
249 opclassesArray.Add(opcname);
250 if (!opcname.IsEmpty())
251 coldef += wxT(" ") + opcname;
252
253 // Get the column options
254 if (GetConnection()->BackendMinimumVersion(8, 3))
255 {
256 long opt = res->GetLong(wxT("options"));
257
258 if (opt && (opt & 0x0001)) // Descending...
259 {
260 ordersArray.Add(wxT("DESC"));
261 coldef += wxT(" DESC");
262 // NULLS FIRST is the default for descending
263 if (!(opt && (opt & 0x0002)))
264 {
265 nullsArray.Add(wxT("NULLS LAST"));
266 coldef += wxT(" NULLS LAST");
267 }
268 else
269 {
270 nullsArray.Add(wxEmptyString);
271 }
272 }
273 else // Ascending...
274 {
275 ordersArray.Add(wxT("ASC"));
276 if ((opt && (opt & 0x0002)))
277 {
278 nullsArray.Add(wxT("NULLS FIRST"));
279 coldef += wxT(" NULLS FIRST");
280 }
281 else
282 {
283 nullsArray.Add(wxEmptyString);
284 }
285 }
286 }
287 else
288 {
289 ordersArray.Add(wxEmptyString);
290 nullsArray.Add(wxEmptyString);
291 }
292 }
293
294 if (isExclude)
295 {
296 coldef += wxT(" WITH ") + res->GetVal(wxT("oprname"));
297 }
298 columns += coldef;
299 quotedColumns += coldef;
300 columnList.Add(coldef);
301
302 //resolve memory leak occurred while expanding the index node in object browser
303 delete res;
304 res = NULL;
305 }
306 }
307 else
308 {
309 // its a 7.3 db
310
311 // We cannot use SELECT IN (colNumbers) here because we couldn't be sure
312 // about the read order
313 wxStringTokenizer collist(GetColumnNumbers());
314 wxStringTokenizer args(procArgTypeList);
315 wxString cn, ct;
316 columnCount = 0;
317
318 while (collist.HasMoreTokens())
319 {
320 cn = collist.GetNextToken();
321 ct = args.GetNextToken();
322
323 pgSet *colSet = ExecuteSet(
324 wxT("SELECT attname as conattname\n")
325 wxT(" FROM pg_attribute\n")
326 wxT(" WHERE attrelid=") + GetOidStr() + wxT(" AND attnum=") + cn);
327 if (colSet)
328 {
329 if (columnCount)
330 {
331 columns += wxT(", ");
332 quotedColumns += wxT(", ");
333 }
334 wxString colName = colSet->GetVal(0);
335 columns += colName;
336 columnList.Add(colName);
337 ordersArray.Add(wxEmptyString);
338 nullsArray.Add(wxEmptyString);
339 opclassesArray.Add(wxEmptyString);
340 collationsArray.Add(wxEmptyString);
341 quotedColumns += qtIdent(colName);
342
343 if (!ct.IsNull())
344 {
345 pgSet *typeSet = ExecuteSet(wxT(
346 "SELECT typname FROM pg_type where oid=") + ct);
347 if (typeSet)
348 {
349 if (columnCount)
350 {
351 procArgs += wxT(", ");
352 typedColumns += wxT(", ");
353 quotedTypedColumns += wxT(", ");
354 }
355 wxString colType = typeSet->GetVal(0);
356 procArgs += colType;
357 typedColumns += colName + wxT("::") + colType;
358 quotedTypedColumns += qtIdent(colName) + wxT("::") + colType;
359 delete typeSet;
360 }
361 }
362 delete colSet;
363 }
364 columnCount++;
365 }
366 }
367 wxStringTokenizer ops(operatorClassList);
368 wxString op;
369 while (ops.HasMoreTokens())
370 {
371 op = ops.GetNextToken();
372 pgSet *set = ExecuteSet(wxT(
373 "SELECT opcname FROM pg_opclass WHERE oid=") + op);
374 if (set)
375 {
376 if (!operatorClasses.IsNull())
377 operatorClasses += wxT(", ");
378 operatorClasses += set->GetVal(0);
379 delete set;
380 }
381 }
382 }
383 }
384
385
ShowTreeDetail(ctlTree * browser,frmMain * form,ctlListView * properties,ctlSQLBox * sqlPane)386 void pgIndexBase::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
387 {
388 ReadColumnDetails();
389 if (properties)
390 {
391 CreateListColumns(properties);
392
393 properties->AppendItem(_("Name"), GetName());
394 properties->AppendItem(_("OID"), GetOid());
395 if (GetConnection()->BackendMinimumVersion(8, 0))
396 properties->AppendItem(_("Tablespace"), tablespace);
397 if (!GetProcName().IsNull())
398 properties->AppendItem(_("Procedure "), GetSchemaPrefix(GetProcNamespace()) + GetProcName() + wxT("(") + GetTypedColumns() + wxT(")"));
399 else
400 properties->AppendItem(_("Columns"), GetColumns());
401
402 properties->AppendItem(_("Operator classes"), GetOperatorClasses());
403 properties->AppendYesNoItem(_("Unique?"), GetIsUnique());
404 properties->AppendYesNoItem(_("Primary?"), GetIsPrimary());
405 properties->AppendYesNoItem(_("Clustered?"), GetIsClustered());
406 properties->AppendYesNoItem(_("Valid?"), GetIsValid());
407 properties->AppendItem(_("Access method"), GetIndexType());
408 properties->AppendItem(_("Constraint"), GetConstraint());
409 properties->AppendYesNoItem(_("System index?"), GetSystemObject());
410 if (GetConnection()->BackendMinimumVersion(8, 2))
411 properties->AppendItem(_("Fill factor"), GetFillFactor());
412 properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
413 }
414 }
415
416
ShowStatistics(frmMain * form,ctlListView * statistics)417 void pgIndexBase::ShowStatistics(frmMain *form, ctlListView *statistics)
418 {
419 wxString sql =
420 wxT("SELECT idx_scan AS ") + qtIdent(_("Index Scans")) +
421 wxT(", idx_tup_read AS ") + qtIdent(_("Index Tuples Read")) +
422 wxT(", idx_tup_fetch AS ") + qtIdent(_("Index Tuples Fetched")) +
423 wxT(", idx_blks_read AS ") + qtIdent(_("Index Blocks Read")) +
424 wxT(", idx_blks_hit AS ") + qtIdent(_("Index Blocks Hit"));
425
426 if (GetConnection()->HasFeature(FEATURE_SIZE))
427 sql += wxT(", pg_size_pretty(pg_relation_size(") + GetOidStr() + wxT(")) AS ") + qtIdent(_("Index Size"));
428
429 if (showExtendedStatistics)
430 {
431 sql += wxT(", version AS ") + qtIdent(_("Version")) + wxT(",\n")
432 wxT(" tree_level AS ") + qtIdent(_("Tree Level")) + wxT(",\n")
433 wxT(" pg_size_pretty(index_size) AS ") + qtIdent(_("Index Size")) + wxT(",\n")
434 wxT(" root_block_no AS ") + qtIdent(_("Root Block No")) + wxT(",\n")
435 wxT(" internal_pages AS ") + qtIdent(_("Internal Pages")) + wxT(",\n")
436 wxT(" leaf_pages AS ") + qtIdent(_("Leaf Pages")) + wxT(",\n")
437 wxT(" empty_pages AS ") + qtIdent(_("Empty Pages")) + wxT(",\n")
438 wxT(" deleted_pages AS ") + qtIdent(_("Deleted Pages")) + wxT(",\n")
439 wxT(" avg_leaf_density AS ") + qtIdent(_("Average Leaf Density")) + wxT(",\n")
440 wxT(" leaf_fragmentation AS ") + qtIdent(_("Leaf Fragmentation")) + wxT("\n")
441 wxT(" FROM pgstatindex('") + GetQuotedFullIdentifier() + wxT("'), pg_stat_all_indexes stat");
442 }
443 else
444 {
445 sql += wxT("\n")
446 wxT(" FROM pg_stat_all_indexes stat");
447 }
448 sql += wxT("\n")
449 wxT(" JOIN pg_statio_all_indexes statio ON stat.indexrelid = statio.indexrelid\n")
450 wxT(" JOIN pg_class cl ON cl.oid=stat.indexrelid\n")
451 wxT(" WHERE stat.indexrelid = ") + GetOidStr();
452
453 DisplayStatistics(statistics, sql);
454 }
455
456
Refresh(ctlTree * browser,const wxTreeItemId item)457 pgObject *pgIndexBase::Refresh(ctlTree *browser, const wxTreeItemId item)
458 {
459 pgObject *index = 0;
460 pgCollection *coll = browser->GetParentCollection(item);
461 if (coll)
462 index = indexFactory.CreateObjects(coll, 0, wxT("\n AND cls.oid=") + GetOidStr());
463
464 return index;
465 }
466
467
HasPgstatindex()468 bool pgIndexBase::HasPgstatindex()
469 {
470 return GetConnection()->HasFeature(FEATURE_PGSTATINDEX);
471 }
472
473
executePgstatindexFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)474 executePgstatindexFactory::executePgstatindexFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
475 {
476 mnu->Append(id, _("&Extended index statistics"), _("Get extended statistics via pgstatindex for the selected object."), wxITEM_CHECK);
477 }
478
479
StartDialog(frmMain * form,pgObject * obj)480 wxWindow *executePgstatindexFactory::StartDialog(frmMain *form, pgObject *obj)
481 {
482 if (!((pgIndexBase *)obj)->GetShowExtendedStatistics())
483 {
484 ((pgIndexBase *)obj)->iSetShowExtendedStatistics(true);
485 wxTreeItemId item = form->GetBrowser()->GetSelection();
486 if (obj == form->GetBrowser()->GetObject(item))
487 form->SelectStatisticsTab();
488 }
489 else
490 ((pgIndexBase *)obj)->iSetShowExtendedStatistics(false);
491
492 form->GetMenuFactories()->CheckMenu(obj, form->GetMenuBar(), (ctlMenuToolbar *)form->GetToolBar());
493
494 return 0;
495 }
496
497
CheckEnable(pgObject * obj)498 bool executePgstatindexFactory::CheckEnable(pgObject *obj)
499 {
500 return obj &&
501 (obj->IsCreatedBy(indexFactory) || obj->IsCreatedBy(primaryKeyFactory)
502 || obj->IsCreatedBy(uniqueFactory) || obj->IsCreatedBy(excludeFactory)) &&
503 ((pgIndexBase *)obj)->HasPgstatindex();
504 }
505
CheckChecked(pgObject * obj)506 bool executePgstatindexFactory::CheckChecked(pgObject *obj)
507 {
508 if (!obj)
509 return false;
510
511 if (obj->GetMetaType() == PGM_INDEX || obj->GetMetaType() == PGM_PRIMARYKEY
512 || obj->GetMetaType() == PGM_UNIQUE || obj->GetMetaType() == PGM_EXCLUDE)
513 return ((pgIndexBase *)obj)->GetShowExtendedStatistics();
514
515 return false;
516 }
517
518
pgIndex(pgSchema * newSchema,const wxString & newName)519 pgIndex::pgIndex(pgSchema *newSchema, const wxString &newName)
520 : pgIndexBase(newSchema, indexFactory, newName)
521 {
522 }
523
524
CreateObjects(pgCollection * coll,ctlTree * browser,const wxString & restriction)525 pgObject *pgIndexBaseFactory::CreateObjects(pgCollection *coll, ctlTree *browser, const wxString &restriction)
526 {
527 pgSchemaObjCollection *collection = (pgSchemaObjCollection *)coll;
528 pgIndexBase *index = 0;
529 wxString query;
530
531 wxString proname, projoin;
532 if (collection->GetConnection()->BackendMinimumVersion(7, 4))
533 {
534 proname = wxT("indnatts, ");
535 if (collection->GetConnection()->BackendMinimumVersion(7, 5))
536 {
537 proname += wxT("cls.reltablespace AS spcoid, spcname, ");
538 projoin = wxT(" LEFT OUTER JOIN pg_tablespace ta on ta.oid=cls.reltablespace\n");
539 }
540 }
541 else
542 {
543 proname = wxT("proname, pn.nspname as pronspname, proargtypes, ");
544 projoin = wxT(" LEFT OUTER JOIN pg_proc pr ON pr.oid=indproc\n")
545 wxT(" LEFT OUTER JOIN pg_namespace pn ON pn.oid=pr.pronamespace\n");
546 }
547 query = wxT("SELECT DISTINCT ON(cls.relname) cls.oid, cls.relname as idxname, indrelid, indkey, indisclustered, indisvalid, indisunique, indisprimary, n.nspname,\n")
548 wxT(" ") + proname + wxT("tab.relname as tabname, indclass, con.oid AS conoid, CASE contype WHEN 'p' THEN desp.description WHEN 'u' THEN desp.description WHEN 'x' THEN desp.description ELSE des.description END AS description,\n")
549 wxT(" pg_get_expr(indpred, indrelid") + collection->GetDatabase()->GetPrettyOption() + wxT(") as indconstraint, contype, condeferrable, condeferred, amname\n");
550 if (collection->GetConnection()->BackendMinimumVersion(8, 2))
551 query += wxT(", substring(array_to_string(cls.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor \n");
552 query += wxT(" FROM pg_index idx\n")
553 wxT(" JOIN pg_class cls ON cls.oid=indexrelid\n")
554 wxT(" JOIN pg_class tab ON tab.oid=indrelid\n")
555 + projoin +
556 wxT(" JOIN pg_namespace n ON n.oid=tab.relnamespace\n")
557 wxT(" JOIN pg_am am ON am.oid=cls.relam\n")
558 wxT(" LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint') AND dep.deptype='i')\n")
559 wxT(" LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)\n")
560 wxT(" LEFT OUTER JOIN pg_description des ON (des.objoid=cls.oid AND des.classoid='pg_class'::regclass)\n")
561 wxT(" LEFT OUTER JOIN pg_description desp ON (desp.objoid=con.oid AND desp.objsubid = 0 AND desp.classoid='pg_constraint'::regclass)\n")
562 wxT(" WHERE indrelid = ") + collection->GetOidStr()
563 + restriction + wxT("\n")
564 wxT(" ORDER BY cls.relname");
565 pgSet *indexes = collection->GetDatabase()->ExecuteSet(query);
566
567 if (indexes)
568 {
569 while (!indexes->Eof())
570 {
571 switch (*(indexes->GetCharPtr(wxT("contype"))))
572 {
573 case 0:
574 index = new pgIndex(collection->GetSchema()->GetSchema(), indexes->GetVal(wxT("idxname")));
575 break;
576 case 'p':
577 index = new pgPrimaryKey(collection->GetSchema()->GetSchema(), indexes->GetVal(wxT("idxname")));
578 ((pgPrimaryKey *)index)->iSetConstraintOid(indexes->GetOid(wxT("conoid")));
579 break;
580 case 'u':
581 index = new pgUnique(collection->GetSchema()->GetSchema(), indexes->GetVal(wxT("idxname")));
582 ((pgUnique *)index)->iSetConstraintOid(indexes->GetOid(wxT("conoid")));
583 break;
584 case 'x':
585 index = new pgExclude(collection->GetSchema()->GetSchema(), indexes->GetVal(wxT("idxname")));
586 ((pgExclude *)index)->iSetConstraintOid(indexes->GetOid(wxT("conoid")));
587 break;
588 default:
589 index = 0;
590 break;
591 }
592
593 index->iSetOid(indexes->GetOid(wxT("oid")));
594 index->iSetIsClustered(indexes->GetBool(wxT("indisclustered")));
595 index->iSetIsValid(indexes->GetBool(wxT("indisvalid")));
596 index->iSetIsUnique(indexes->GetBool(wxT("indisunique")));
597 index->iSetIsPrimary(indexes->GetBool(wxT("indisprimary")));
598 index->iSetIsExclude(*(indexes->GetCharPtr(wxT("contype"))) == 'x');
599 index->iSetColumnNumbers(indexes->GetVal(wxT("indkey")));
600 index->iSetIdxSchema(indexes->GetVal(wxT("nspname")));
601 index->iSetComment(indexes->GetVal(wxT("description")));
602 index->iSetIdxTable(indexes->GetVal(wxT("tabname")));
603 index->iSetRelTableOid(indexes->GetOid(wxT("indrelid")));
604 if (collection->GetConnection()->BackendMinimumVersion(7, 4))
605 {
606 index->iSetColumnCount(indexes->GetLong(wxT("indnatts")));
607 if (collection->GetConnection()->BackendMinimumVersion(8, 0))
608 {
609 if (indexes->GetOid(wxT("spcoid")) == 0)
610 index->iSetTablespaceOid(collection->GetDatabase()->GetTablespaceOid());
611 else
612 index->iSetTablespaceOid(indexes->GetOid(wxT("spcoid")));
613
614 if (indexes->GetVal(wxT("spcname")) == wxEmptyString)
615 index->iSetTablespace(collection->GetDatabase()->GetTablespace());
616 else
617 index->iSetTablespace(indexes->GetVal(wxT("spcname")));
618 }
619
620 }
621 else
622 {
623 index->iSetColumnCount(0L);
624 index->iSetProcNamespace(indexes->GetVal(wxT("pronspname")));
625 index->iSetProcName(indexes->GetVal(wxT("proname")));
626 index->iSetProcArgTypeList(indexes->GetVal(wxT("proargtypes")));
627 }
628 index->iSetOperatorClassList(indexes->GetVal(wxT("indclass")));
629 index->iSetDeferrable(indexes->GetBool(wxT("condeferrable")));
630 index->iSetDeferred(indexes->GetBool(wxT("condeferred")));
631 index->iSetConstraint(indexes->GetVal(wxT("indconstraint")));
632 index->iSetIndexType(indexes->GetVal(wxT("amname")));
633 if (collection->GetConnection()->BackendMinimumVersion(8, 2))
634 index->iSetFillFactor(indexes->GetVal(wxT("fillfactor")));
635
636 if (browser)
637 {
638 browser->AppendObject(collection, index);
639 indexes->MoveNext();
640 }
641 else
642 break;
643 }
644
645 delete indexes;
646 }
647 return index;
648 }
649
650
CreateCollection(pgObject * obj)651 pgCollection *pgIndexBaseFactory::CreateCollection(pgObject *obj)
652 {
653 return new pgIndexBaseCollection(GetCollectionFactory(), (pgSchema *)obj);
654 }
655
656
657
CreateObjects(pgCollection * collection,ctlTree * browser,const wxString & restriction)658 pgObject *pgIndexFactory::CreateObjects(pgCollection *collection, ctlTree *browser, const wxString &restriction)
659 {
660 return pgIndexBaseFactory::CreateObjects(collection, browser, restriction + wxT("\n AND conname IS NULL"));
661 }
662
663
GetTranslatedMessage(int kindOfMessage) const664 wxString pgIndexBaseCollection::GetTranslatedMessage(int kindOfMessage) const
665 {
666 wxString message = wxEmptyString;
667
668 switch (kindOfMessage)
669 {
670 case RETRIEVINGDETAILS:
671 message = _("Retrieving details on indexes");
672 break;
673 case REFRESHINGDETAILS:
674 message = _("Refreshing indexes");
675 break;
676 case OBJECTSLISTREPORT:
677 message = _("Indexes list report");
678 break;
679 }
680
681 return message;
682 }
683
684 /////////////////////////////
685
686 #include "images/index.pngc"
687 #include "images/indexes.pngc"
688
pgIndexFactory()689 pgIndexFactory::pgIndexFactory()
690 : pgIndexBaseFactory(__("Index"), __("New Index..."), __("Create a new Index."), index_png_img)
691 {
692 metaType = PGM_INDEX;
693 }
694
695
696 pgIndexFactory indexFactory;
697 static pgaCollectionFactory cf(&indexFactory, __("Indexes"), indexes_png_img);
698
699
pgIndexBaseCollection(pgaFactory * factory,pgSchema * sch)700 pgIndexBaseCollection::pgIndexBaseCollection(pgaFactory *factory, pgSchema *sch)
701 : pgSchemaObjCollection(factory, sch)
702 {
703 }
704
705
ShowStatistics(frmMain * form,ctlListView * statistics)706 void pgIndexBaseCollection::ShowStatistics(frmMain *form, ctlListView *statistics)
707 {
708 wxLogInfo(wxT("Displaying statistics for indexes on ") + GetSchema()->GetName());
709
710 bool hasSize = GetConnection()->HasFeature(FEATURE_SIZE);
711
712 // Add the statistics view columns
713 statistics->ClearAll();
714 statistics->AddColumn(_("Index Name"));
715 statistics->AddColumn(_("Index Scans"));
716 statistics->AddColumn(_("Index Tuples Read"));
717 statistics->AddColumn(_("Index Tuples Fetched"));
718 if (hasSize)
719 statistics->AddColumn(_("Size"));
720
721 wxString sql = wxT("SELECT indexrelname, ")
722 wxT("idx_scan, idx_tup_read, idx_tup_fetch");
723
724 if (hasSize)
725 sql += wxT(", pg_size_pretty(pg_relation_size(indexrelid)) AS ") + qtIdent(wxT("size"));
726
727 sql += wxT("\n")
728 wxT(" FROM pg_stat_all_indexes stat\n")
729 wxT(" JOIN pg_class cls ON cls.oid=indexrelid\n")
730 wxT(" LEFT JOIN pg_depend dep ON (dep.classid = cls.tableoid AND dep.objid = cls.oid AND dep.refobjsubid = '0' AND dep.refclassid=(SELECT oid FROM pg_class WHERE relname='pg_constraint'))\n")
731 wxT(" LEFT OUTER JOIN pg_constraint con ON (con.tableoid = dep.refclassid AND con.oid = dep.refobjid)\n")
732 wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetSchema()->GetName())
733 + wxT(" AND stat.relname = ") + qtDbString(GetSchema()->GetName())
734 + wxT(" AND con.contype IS NULL")
735 + wxT("\n ORDER BY indexrelname");
736
737 pgSet *stats = GetDatabase()->ExecuteSet(sql);
738
739 if (stats)
740 {
741 long pos = 0;
742 while (!stats->Eof())
743 {
744 statistics->InsertItem(pos, stats->GetVal(wxT("indexrelname")), PGICON_STATISTICS);
745 statistics->SetItem(pos, 1, stats->GetVal(wxT("idx_scan")));
746 statistics->SetItem(pos, 2, stats->GetVal(wxT("idx_tup_read")));
747 statistics->SetItem(pos, 3, stats->GetVal(wxT("idx_tup_fetch")));
748 if (hasSize)
749 statistics->SetItem(pos, 4, stats->GetVal(wxT("size")));
750 stats->MoveNext();
751 pos++;
752 }
753
754 delete stats;
755 }
756 }
757