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 // pgTable.cpp - Table class
9 //
10 //////////////////////////////////////////////////////////////////////////
11
12 #include "pgAdmin3.h"
13
14
15 #include "utils/misc.h"
16 #include "frm/frmHint.h"
17 #include "frm/frmMain.h"
18 #include "frm/frmMaintenance.h"
19 #include "schema/pgTable.h"
20 #include "schema/pgColumn.h"
21 #include "schema/pgIndexConstraint.h"
22 #include "schema/pgForeignKey.h"
23 #include "schema/pgCheck.h"
24 #include "utils/sysSettings.h"
25 #include "utils/pgfeatures.h"
26 #include "schema/pgRule.h"
27 #include "schema/pgTrigger.h"
28 #include "schema/pgConstraints.h"
29 #include "schema/gpPartition.h"
30
31
32 // App headers
33
pgTable(pgSchema * newSchema,const wxString & newName)34 pgTable::pgTable(pgSchema *newSchema, const wxString &newName)
35 : pgSchemaObject(newSchema, tableFactory, newName)
36 {
37 Init();
38 }
39
pgTable(pgSchema * newSchema,pgaFactory & newFactory,const wxString & newName)40 pgTable::pgTable(pgSchema *newSchema, pgaFactory &newFactory, const wxString &newName)
41 : pgSchemaObject(newSchema, newFactory, newName)
42 {
43 Init();
44 }
45
~pgTable()46 pgTable::~pgTable()
47 {
48 }
49
50
Init()51 void pgTable::Init()
52 {
53 rows = 0;
54 estimatedRows = 0.0;
55
56 hasToastTable = false;
57 autovacuum_enabled = 0;
58 toast_autovacuum_enabled = 0;
59
60 isPartitioned = false;
61 hasOids = false;
62 unlogged = false;
63 hasSubclass = false;
64 rowsCounted = false;
65 isReplicated = false;
66 showExtendedStatistics = false;
67 distributionIsRandom = false;
68
69 inheritedTableCount = 0;
70 triggerCount = 0;
71
72 tablespaceOid = 0;
73 ofTypeOid = 0;
74 }
75
76
GetTranslatedMessage(int kindOfMessage) const77 wxString pgTable::GetTranslatedMessage(int kindOfMessage) const
78 {
79 wxString message = wxEmptyString;
80
81 switch (kindOfMessage)
82 {
83 case RETRIEVINGDETAILS:
84 message = _("Retrieving details on table");
85 message += wxT(" ") + GetName();
86 break;
87 case REFRESHINGDETAILS:
88 message = _("Refreshing table");
89 message += wxT(" ") + GetName();
90 break;
91 case DROPINCLUDINGDEPS:
92 message = wxString::Format(_("Are you sure you wish to drop table \"%s\" including all objects that depend on it?"),
93 GetFullIdentifier().c_str());
94 break;
95 case DROPEXCLUDINGDEPS:
96 message = wxString::Format(_("Are you sure you wish to drop table \"%s\"?"),
97 GetFullIdentifier().c_str());
98 break;
99 case DROPCASCADETITLE:
100 message = _("Drop table cascaded?");
101 break;
102 case DROPTITLE:
103 message = _("Drop table?");
104 break;
105 case PROPERTIESREPORT:
106 message = _("Table properties report");
107 message += wxT(" - ") + GetName();
108 break;
109 case PROPERTIES:
110 message = _("Table properties");
111 break;
112 case DDLREPORT:
113 message = _("Table DDL report");
114 message += wxT(" - ") + GetName();
115 break;
116 case DDL:
117 message = _("Table DDL");
118 break;
119 case DATADICTIONNARYREPORT:
120 message = _("Table Data dictionary report");
121 message += wxT(" - ") + GetName();
122 break;
123 case STATISTICSREPORT:
124 message = _("Table statistics report");
125 message += wxT(" - ") + GetName();
126 break;
127 case OBJSTATISTICS:
128 message = _("Table statistics");
129 break;
130 case DEPENDENCIESREPORT:
131 message = _("Table dependencies report");
132 message += wxT(" - ") + GetName();
133 break;
134 case DEPENDENCIES:
135 message = _("Table dependencies");
136 break;
137 case DEPENDENTSREPORT:
138 message = _("Table dependents report");
139 message += wxT(" - ") + GetName();
140 break;
141 case DEPENDENTS:
142 message = _("Table dependents");
143 break;
144 case BACKUPTITLE:
145 message = wxString::Format(_("Backup table \"%s\""),
146 GetFullIdentifier().c_str());
147 break;
148 case RESTORETITLE:
149 message = wxString::Format(_("Restore table \"%s\""),
150 GetFullIdentifier().c_str());
151 break;
152 }
153
154 return message;
155 }
156
157
GetIconId()158 int pgTable::GetIconId()
159 {
160 if (isReplicated)
161 return tableFactory.GetReplicatedIconId();
162 else
163 return tableFactory.GetIconId();
164 }
165
166
GetNewMenu()167 wxMenu *pgTable::GetNewMenu()
168 {
169 wxMenu *menu = pgObject::GetNewMenu();
170 if (schema->GetCreatePrivilege())
171 {
172 columnFactory.AppendMenu(menu);
173 if (GetPrimaryKey().IsEmpty()) // Will not notice if pk has been added after last refresh
174 primaryKeyFactory.AppendMenu(menu);
175 foreignKeyFactory.AppendMenu(menu);
176 excludeFactory.AppendMenu(menu);
177 uniqueFactory.AppendMenu(menu);
178 checkFactory.AppendMenu(menu);
179 indexFactory.AppendMenu(menu);
180 ruleFactory.AppendMenu(menu);
181 triggerFactory.AppendMenu(menu);
182
183 /*
184 * TEMPORARY: Disable adding new partitions until that code is working right.
185 *
186 if (GetConnection() != 0 && GetConnection()->GetIsGreenplum() && GetIsPartitioned())
187 partitionFactory.AppendMenu(menu);
188 */
189 }
190 return menu;
191 }
192
193
GetReplicationStatus(ctlTree * browser,wxString * clusterName,long * setId)194 int pgTable::GetReplicationStatus(ctlTree *browser, wxString *clusterName, long *setId)
195 {
196 wxArrayString clusters = GetDatabase()->GetSlonyClusters(browser);
197
198 bool isSubscribed = false;
199
200 size_t i;
201 for (i = 0 ; i < clusters.GetCount() ; i++)
202 {
203 wxString nsp = qtIdent(wxT("_") + clusters.Item(i));
204
205 pgSetIterator sets(GetConnection(),
206 wxT("SELECT tab_set, sub_provider, ") + nsp + wxT(".getlocalnodeid(") + qtDbString(wxT("_") + clusters.Item(i)) + wxT(") AS localnode\n")
207 wxT(" FROM ") + nsp + wxT(".sl_table\n")
208 wxT(" LEFT JOIN ") + nsp + wxT(".sl_subscribe ON sub_set=tab_set\n")
209 wxT(" WHERE tab_reloid = ") + GetOidStr());
210
211 if (sets.RowsLeft())
212 {
213 if (clusterName)
214 *clusterName = clusters.Item(i);
215 if (setId)
216 *setId = sets.GetLong(wxT("tab_set"));
217 if (isSubscribed)
218 return REPLICATIONSTATUS_MULTIPLY_PUBLISHED;
219
220 long provider = sets.GetLong(wxT("sub_provider"));
221 if (provider)
222 {
223 if (provider != sets.GetLong(wxT("localnode")))
224 return REPLICATIONSTATUS_REPLICATED;
225
226 isSubscribed = true;
227
228 }
229 }
230 }
231 if (isSubscribed)
232 return REPLICATIONSTATUS_SUBSCRIBED;
233
234 return REPLICATIONSTATUS_NONE;
235 }
236
237
GetHelpPage(bool forCreate) const238 wxString pgTable::GetHelpPage(bool forCreate) const
239 {
240 if (forCreate)
241 return wxT("pg/sql-createtable");
242 else
243 return wxT("pg/sql-altertable");
244 }
245
246
DropObject(wxFrame * frame,ctlTree * browser,bool cascaded)247 bool pgTable::DropObject(wxFrame *frame, ctlTree *browser, bool cascaded)
248 {
249 wxString sql = wxT("DROP TABLE ") + this->GetSchema()->GetQuotedIdentifier() + wxT(".") + this->GetQuotedIdentifier();
250 if (cascaded)
251 sql += wxT(" CASCADE");
252 return GetDatabase()->ExecuteVoid(sql);
253 }
254
255
Truncate(bool cascaded)256 bool pgTable::Truncate(bool cascaded)
257 {
258 wxString sql = wxT("TRUNCATE TABLE ") + this->GetSchema()->GetQuotedIdentifier() + wxT(".") + this->GetQuotedIdentifier();
259 if (cascaded)
260 sql += wxT(" CASCADE");
261 return GetDatabase()->ExecuteVoid(sql);
262 }
263
264
ResetStats()265 bool pgTable::ResetStats()
266 {
267 wxString sql = wxT("SELECT pg_stat_reset_single_table_counters(")
268 + NumToStr(this->GetOid())
269 + wxT(")");
270 return GetDatabase()->ExecuteVoid(sql);
271 }
272
273
AppendStuff(wxString & sql,ctlTree * browser,pgaFactory & factory)274 void pgTable::AppendStuff(wxString &sql, ctlTree *browser, pgaFactory &factory)
275 {
276 wxString tmp;
277
278 pgCollection *collection = browser->FindCollection(factory, GetId());
279 if (collection)
280 {
281 tmp += wxT("\n");
282 collection->ShowTreeDetail(browser);
283
284 treeObjectIterator idxIt(browser, collection);
285 pgObject *obj;
286 while ((obj = idxIt.GetNextObject()) != 0)
287 {
288 obj->ShowTreeDetail(browser);
289
290 tmp += obj->GetSql(browser) + wxT("\n");
291 }
292 }
293
294 if (!tmp.IsEmpty() && tmp != wxT("\n"))
295 sql += tmp;
296 }
297
AppendStuffNoSql(wxString & sql,ctlTree * browser,pgaFactory & factory)298 void pgTable::AppendStuffNoSql(wxString &sql, ctlTree *browser, pgaFactory &factory)
299 {
300 pgCollection *collection = browser->FindCollection(factory, GetId());
301 if (collection)
302 {
303 collection->ShowTreeDetail(browser);
304
305 treeObjectIterator idxIt(browser, collection);
306 pgObject *obj;
307 while ((obj = idxIt.GetNextObject()) != 0)
308 {
309 obj->ShowTreeDetail(browser);
310 }
311 }
312 }
313
314
315
GetSql(ctlTree * browser)316 wxString pgTable::GetSql(ctlTree *browser)
317 {
318 wxString colDetails, conDetails;
319 wxString prevComment;
320 wxString cols_sql = wxEmptyString;
321
322 wxString columnPrivileges;
323
324 if (sql.IsNull())
325 {
326 // make sure all kids are appended
327 ShowTreeDetail(browser);
328 sql = wxT("-- Table: ") + GetQuotedFullIdentifier() + wxT("\n\n")
329 + wxT("-- DROP TABLE ") + GetQuotedFullIdentifier() + wxT(";")
330 + wxT("\n\nCREATE ");
331 if (GetUnlogged())
332 sql += wxT("UNLOGGED ");
333 sql += wxT("TABLE ") + GetQuotedFullIdentifier();
334
335 // of type (9.0 material)
336 if (ofTypeOid > 0)
337 sql += wxT("\nOF ") + qtIdent(ofType);
338
339 // Get a count of the constraints.
340 int consCount = 0;
341 pgCollection *constraints = browser->FindCollection(primaryKeyFactory, GetId());
342 if (constraints)
343 consCount = browser->GetChildrenCount(constraints->GetId());
344
345 // Get the columns
346 pgCollection *columns = browser->FindCollection(columnFactory, GetId());
347 if (columns)
348 {
349 columns->ShowTreeDetail(browser);
350 treeObjectIterator colIt1(browser, columns);
351 treeObjectIterator colIt2(browser, columns);
352
353
354 int lastRealCol = 0;
355 int currentCol = 0;
356 pgColumn *column;
357
358 // Iterate the columns to find the last 'real' one
359 while ((column = (pgColumn *)colIt1.GetNextObject()) != 0)
360 {
361 currentCol++;
362
363 if (column->GetInheritedCount() == 0)
364 lastRealCol = currentCol;
365 }
366
367 // Now build the actual column list
368 int colCount = 0;
369 while ((column = (pgColumn *)colIt2.GetNextObject()) != 0)
370 {
371 column->ShowTreeDetail(browser);
372 if (column->GetColNumber() > 0)
373 {
374 if (colCount)
375 {
376 // Only add a comma if this isn't the last 'real' column, or if there are constraints
377 if (colCount != lastRealCol || consCount)
378 cols_sql += wxT(",");
379 if (!prevComment.IsEmpty())
380 cols_sql += wxT(" -- ") + firstLineOnly(prevComment);
381
382 cols_sql += wxT("\n");
383 }
384
385 if (column->GetInheritedCount() > 0)
386 {
387 if (!column->GetIsLocal())
388 {
389 cols_sql += wxString::Format(wxT("-- %s "), _("Inherited"))
390 + wxT("from table ") + column->GetInheritedTableName() + wxT(":");
391 }
392 }
393
394 if (ofTypeOid > 0)
395 {
396 if (column->GetDefinition().Length() == 0)
397 {
398 cols_sql += wxString::Format(wxT("-- %s "), _("Inherited"))
399 + wxT("from type ") + ofType + wxT(": ")
400 + column->GetQuotedIdentifier();
401 }
402 else
403 {
404 cols_sql += wxT(" ") + column->GetQuotedIdentifier() + wxT(" WITH OPTIONS ")
405 + column->GetDefinition();
406 }
407 }
408 else
409 {
410 cols_sql += wxT(" ") + column->GetQuotedIdentifier() + wxT(" ")
411 + column->GetDefinition();
412 }
413
414 prevComment = column->GetComment();
415
416 // Whilst we are looping round the columns, grab their comments as well.
417 colDetails += column->GetCommentSql();
418 if (colDetails.Length() > 0)
419 if (colDetails.Last() != '\n')
420 colDetails += wxT("\n");
421 colDetails += column->GetStorageSql();
422 if (colDetails.Length() > 0)
423 if (colDetails.Last() != '\n')
424 colDetails += wxT("\n");
425 colDetails += column->GetAttstattargetSql();
426 if (colDetails.Length() > 0)
427 if (colDetails.Last() != '\n')
428 colDetails += wxT("\n");
429 colDetails += column->GetVariablesSql();
430 if (colDetails.Length() > 0)
431 if (colDetails.Last() != '\n')
432 colDetails += wxT("\n");
433
434 colCount++;
435 columnPrivileges += column->GetPrivileges();
436 }
437 }
438 }
439
440 // Now iterate the constraints
441 if (constraints)
442 {
443 constraints->ShowTreeDetail(browser);
444 treeObjectIterator consIt(browser, constraints);
445
446 pgObject *data;
447
448 while ((data = consIt.GetNextObject()) != 0)
449 {
450 data->ShowTreeDetail(browser);
451
452 cols_sql += wxT(",");
453
454 if (!prevComment.IsEmpty())
455 cols_sql += wxT(" -- ") + firstLineOnly(prevComment);
456
457 cols_sql += wxT("\n CONSTRAINT ") + data->GetQuotedIdentifier()
458 + wxT(" ") + data->GetTypeName().Upper()
459 + wxT(" ") ;
460
461 prevComment = data->GetComment();
462 if (!data->GetComment().IsEmpty())
463 conDetails += wxT("COMMENT ON CONSTRAINT ") + data->GetQuotedIdentifier() +
464 wxT(" ON ") + GetQuotedFullIdentifier() +
465 wxT(" IS ") + qtDbString(data->GetComment()) + wxT(";\n");
466
467 switch (data->GetMetaType())
468 {
469 case PGM_PRIMARYKEY:
470 case PGM_UNIQUE:
471 case PGM_EXCLUDE:
472 cols_sql += ((pgIndexConstraint *)data)->GetDefinition();
473 break;
474 case PGM_FOREIGNKEY:
475 cols_sql += ((pgForeignKey *)data)->GetDefinition();
476 break;
477 case PGM_CHECK:
478 cols_sql += wxT("(") + ((pgCheck *)data)->GetDefinition() + wxT(")");
479 if (GetDatabase()->BackendMinimumVersion(9, 2) && ((pgCheck *)data)->GetNoInherit())
480 cols_sql += wxT(" NO INHERIT");
481 if (GetDatabase()->BackendMinimumVersion(9, 2) && !((pgCheck *)data)->GetValid())
482 cols_sql += wxT(" NOT VALID");
483 break;
484 }
485 }
486 }
487 if (!prevComment.IsEmpty())
488 cols_sql += wxT(" -- ") + firstLineOnly(prevComment);
489
490 sql += wxT("\n(\n") + cols_sql + wxT("\n)");
491
492 if (GetInheritedTableCount())
493 {
494 sql += wxT("\nINHERITS (") + GetQuotedInheritedTables() + wxT(")");
495 }
496
497 if (GetConnection()->BackendMinimumVersion(8, 2))
498 {
499 sql += wxT("\nWITH (");
500 if (GetFillFactor().Length() > 0)
501 sql += wxT("\n FILLFACTOR=") + GetFillFactor() + wxT(", ");
502 if (GetAppendOnly().Length() > 0)
503 sql += wxT("APPENDONLY=") + GetAppendOnly() + wxT(", ");
504 if (GetCompressLevel().Length() > 0)
505 sql += wxT("COMPRESSLEVEL=") + GetCompressLevel() + wxT(", ");
506 if (GetOrientation().Length() > 0)
507 sql += wxT("ORIENTATION=") + GetOrientation() + wxT(", ");
508 if (GetCompressType().Length() > 0)
509 sql += wxT("COMPRESSTYPE=") + GetCompressType() + wxT(", ");
510 if (GetBlocksize().Length() > 0)
511 sql += wxT("BLOCKSIZE=") + GetBlocksize() + wxT(", ");
512 if (GetChecksum().Length() > 0)
513 sql += wxT("CHECKSUM=") + GetChecksum() + wxT(", ");
514 if (GetHasOids())
515 sql += wxT("\n OIDS=TRUE");
516 else
517 sql += wxT("\n OIDS=FALSE");
518 if(GetConnection()->BackendMinimumVersion(8, 4))
519 {
520 if (GetCustomAutoVacuumEnabled())
521 {
522 if (GetAutoVacuumEnabled() == 1)
523 sql += wxT(",\n autovacuum_enabled=true");
524 else if (GetCustomAutoVacuumEnabled() == 0)
525 sql += wxT(",\n autovacuum_enabled=false");
526 if (!GetAutoVacuumVacuumThreshold().IsEmpty())
527 {
528 sql += wxT(",\n autovacuum_vacuum_threshold=") + GetAutoVacuumVacuumThreshold();
529 }
530 if (!GetAutoVacuumVacuumScaleFactor().IsEmpty())
531 {
532 sql += wxT(",\n autovacuum_vacuum_scale_factor=") + GetAutoVacuumVacuumScaleFactor();
533 }
534 if (!GetAutoVacuumAnalyzeThreshold().IsEmpty())
535 {
536 sql += wxT(",\n autovacuum_analyze_threshold=") + GetAutoVacuumAnalyzeThreshold();
537 }
538 if (!GetAutoVacuumAnalyzeScaleFactor().IsEmpty())
539 {
540 sql += wxT(",\n autovacuum_analyze_scale_factor=") + GetAutoVacuumAnalyzeScaleFactor();
541 }
542 if (!GetAutoVacuumVacuumCostDelay().IsEmpty())
543 {
544 sql += wxT(",\n autovacuum_vacuum_cost_delay=") + GetAutoVacuumVacuumCostDelay();
545 }
546 if (!GetAutoVacuumVacuumCostLimit().IsEmpty())
547 {
548 sql += wxT(",\n autovacuum_vacuum_cost_limit=") + GetAutoVacuumVacuumCostLimit();
549 }
550 if (!GetAutoVacuumFreezeMinAge().IsEmpty())
551 {
552 sql += wxT(",\n autovacuum_freeze_min_age=") + GetAutoVacuumFreezeMinAge();
553 }
554 if (!GetAutoVacuumFreezeMaxAge().IsEmpty())
555 {
556 sql += wxT(",\n autovacuum_freeze_max_age=") + GetAutoVacuumFreezeMaxAge();
557 }
558 if (!GetAutoVacuumFreezeTableAge().IsEmpty())
559 {
560 sql += wxT(",\n autovacuum_freeze_table_age=") + GetAutoVacuumFreezeTableAge();
561 }
562 }
563 if (GetHasToastTable() && GetToastCustomAutoVacuumEnabled())
564 {
565 if (GetToastAutoVacuumEnabled() == 1)
566 sql += wxT(",\n toast.autovacuum_enabled=true");
567 else if (GetToastAutoVacuumEnabled() == 0)
568 sql += wxT(",\n toast.autovacuum_enabled=false");
569 if (!GetToastAutoVacuumVacuumThreshold().IsEmpty())
570 {
571 sql += wxT(",\n toast.autovacuum_vacuum_threshold=") + GetToastAutoVacuumVacuumThreshold();
572 }
573 if (!GetToastAutoVacuumVacuumScaleFactor().IsEmpty())
574 {
575 sql += wxT(",\n toast.autovacuum_vacuum_scale_factor=") + GetToastAutoVacuumVacuumScaleFactor();
576 }
577 if (!GetToastAutoVacuumVacuumCostDelay().IsEmpty())
578 {
579 sql += wxT(",\n toast.autovacuum_vacuum_cost_delay=") + GetToastAutoVacuumVacuumCostDelay();
580 }
581 if (!GetToastAutoVacuumVacuumCostLimit().IsEmpty())
582 {
583 sql += wxT(",\n toast.autovacuum_vacuum_cost_limit=") + GetToastAutoVacuumVacuumCostLimit();
584 }
585 if (!GetToastAutoVacuumFreezeMinAge().IsEmpty())
586 {
587 sql += wxT(",\n toast.autovacuum_freeze_min_age=") + GetToastAutoVacuumFreezeMinAge();
588 }
589 if (!GetToastAutoVacuumFreezeMaxAge().IsEmpty())
590 {
591 sql += wxT(",\n toast.autovacuum_freeze_max_age=") + GetToastAutoVacuumFreezeMaxAge();
592 }
593 if (!GetToastAutoVacuumFreezeTableAge().IsEmpty())
594 {
595 sql += wxT(",\n toast.autovacuum_freeze_table_age=") + GetToastAutoVacuumFreezeTableAge();
596 }
597 }
598 }
599 sql += wxT("\n)");
600 }
601 else
602 {
603 if (GetHasOids())
604 sql += wxT("\nWITH OIDS");
605 else
606 sql += wxT("\nWITHOUT OIDS");
607 }
608
609 if (GetConnection()->BackendMinimumVersion(8, 0) && tablespace != GetDatabase()->GetDefaultTablespace())
610 sql += wxT("\nTABLESPACE ") + qtIdent(tablespace);
611
612 if (GetConnection()->GetIsGreenplum())
613 {
614 // Add Greenplum DISTRIBUTED BY
615 if (distributionIsRandom)
616 {
617 sql += wxT("\nDISTRIBUTED RANDOMLY");
618 }
619 else if (GetDistributionColNumbers().Length() == 0)
620 {
621 // catalog table or other non-distributed table
622 }
623 else
624 {
625 // convert list of columns numbers to column names
626 wxStringTokenizer collist(GetDistributionColNumbers(), wxT(","));
627 wxString cn;
628 wxString distributionColumns;
629 while (collist.HasMoreTokens())
630 {
631 cn = collist.GetNextToken();
632 pgSet *set = ExecuteSet(
633 wxT("SELECT attname\n")
634 wxT(" FROM pg_attribute\n")
635 wxT(" WHERE attrelid=") + GetOidStr() + wxT(" AND attnum IN (") + cn + wxT(")"));
636 if (set)
637 {
638 if (!distributionColumns.IsNull())
639 {
640 distributionColumns += wxT(", ");
641 }
642 distributionColumns += qtIdent(set->GetVal(0));
643 delete set;
644 }
645 }
646
647 sql += wxT("\nDISTRIBUTED BY (");
648 sql += distributionColumns;
649
650 sql += wxT(")");
651 }
652
653 if (GetIsPartitioned())
654 if (GetConnection()->BackendMinimumVersion(8, 2, 9) && GetConnection()->GetIsGreenplum())
655 if (GetPartitionDef().Length() == 0)
656 {
657 wxString query = wxT("SELECT pg_get_partition_def(");
658 query += GetOidStr();
659 query += wxT(", true) ");
660 wxString partition_def = GetDatabase()->ExecuteScalar(query);
661 iSetPartitionDef(partition_def);
662 // pg_get_partition_def() doesn't work on partitions
663 if (GetPartitionDef().Length() == 0)
664 iSetPartitionDef(wxT("-- This partition has subpartitions"));
665 }
666 if (partitionDef.Length() > 0)
667 sql += wxT("\n") + partitionDef + wxT("\n");
668
669
670 }
671
672
673 sql += wxT(";\n")
674 + GetOwnerSql(7, 3);
675
676 if (GetConnection()->BackendMinimumVersion(8, 4))
677 sql += GetGrant(wxT("arwdDxt"));
678 else if (GetConnection()->BackendMinimumVersion(8, 2))
679 sql += GetGrant(wxT("arwdxt"));
680 else
681 sql += GetGrant(wxT("arwdRxt"));
682
683 sql += GetCommentSql();
684
685 if (GetConnection()->BackendMinimumVersion(9, 1))
686 sql += GetSeqLabelsSql();
687
688 // Column/constraint comments
689 if (!colDetails.IsEmpty())
690 sql += colDetails + wxT("\n");
691
692 if (!conDetails.IsEmpty())
693 sql += conDetails + wxT("\n");
694
695 if (!columnPrivileges.IsEmpty())
696 {
697 sql += columnPrivileges + wxT("\n");
698 }
699
700 AppendStuff(sql, browser, indexFactory);
701 AppendStuff(sql, browser, ruleFactory);
702 AppendStuff(sql, browser, triggerFactory);
703
704 /*
705 * Disable adding partitions until that code works.
706 *
707 if (partitionDef.Length() > 0)
708 {
709 AppendStuffNoSql(sql, browser, partitionFactory);
710 }
711 */
712 }
713 return sql;
714 }
715
GetCoveringIndex(ctlTree * browser,const wxString & collist)716 wxString pgTable::GetCoveringIndex(ctlTree *browser, const wxString &collist)
717 {
718 // delivers the name of the index which covers the named columns
719 wxCookieType cookie;
720
721 wxTreeItemId collItem = browser->GetFirstChild(GetId(), cookie);
722 while (collItem)
723 {
724 pgObject *data = browser->GetObject(collItem);
725 if (data && data->IsCollection() && (data->GetMetaType() == PGM_CONSTRAINT || data->GetMetaType() == PGM_INDEX))
726 {
727 wxCookieType cookie2;
728 wxTreeItemId item = browser->GetFirstChild(collItem, cookie2);
729 while (item)
730 {
731 pgIndex *index = (pgIndex *)browser->GetObject(item);
732 if (index && (index->GetMetaType() == PGM_INDEX || index->GetMetaType() == PGM_PRIMARYKEY
733 || index->GetMetaType() == PGM_UNIQUE || index->GetMetaType() == PGM_EXCLUDE))
734 {
735 index->ShowTreeDetail(browser);
736 if (collist == index->GetColumns() ||
737 collist + wxT(",") == index->GetColumns().Left(collist.Length() + 1))
738 return index->GetName();
739 }
740 item = browser->GetNextChild(collItem, cookie2);
741 }
742 }
743 collItem = browser->GetNextChild(GetId(), cookie);
744 }
745
746 return wxEmptyString;
747 }
748
749
GetCols(ctlTree * browser,size_t indent,wxString & QMs,bool withQM)750 wxString pgTable::GetCols(ctlTree *browser, size_t indent, wxString &QMs, bool withQM)
751 {
752 wxString sql;
753 wxString line;
754
755 int colcount = 0;
756 pgCollection *columns = browser->FindCollection(columnFactory, GetId());
757 if (columns)
758 {
759 columns->ShowTreeDetail(browser);
760 treeObjectIterator colIt(browser, columns);
761
762 pgColumn *column;
763 while ((column = (pgColumn *)colIt.GetNextObject()) != 0)
764 {
765 column->ShowTreeDetail(browser);
766 if (column->GetColNumber() > 0)
767 {
768 if (colcount++)
769 {
770 line += wxT(", ");
771 QMs += wxT(", ");
772 }
773 if (line.Length() > 60)
774 {
775 if (!sql.IsEmpty())
776 {
777 sql += wxT("\n") + wxString(' ', indent);
778 }
779 sql += line;
780 line = wxEmptyString;
781 QMs += wxT("\n") + wxString(' ', indent);
782 }
783
784 line += column->GetQuotedIdentifier();
785 if (withQM)
786 line += wxT("=?");
787 QMs += wxT("?");
788 }
789 }
790 }
791
792 if (!line.IsEmpty())
793 {
794 if (!sql.IsEmpty())
795 sql += wxT("\n") + wxString(' ', indent);
796 sql += line;
797 }
798 return sql;
799 }
800
GetColumnCollection(ctlTree * browser)801 pgCollection *pgTable::GetColumnCollection(ctlTree *browser)
802 {
803 pgCollection *columns = browser->FindCollection(columnFactory, GetId());
804 return columns;
805 }
806
GetConstraintCollection(ctlTree * browser)807 pgCollection *pgTable::GetConstraintCollection(ctlTree *browser)
808 {
809 pgCollection *constraints = browser->FindCollection(constraintFactory, GetId());
810 return constraints;
811 }
812
GetSelectSql(ctlTree * browser)813 wxString pgTable::GetSelectSql(ctlTree *browser)
814 {
815 wxString qms;
816 wxString sql =
817 wxT("SELECT ") + GetCols(browser, 7, qms, false) + wxT("\n")
818 wxT(" FROM ") + GetQuotedFullIdentifier() + wxT(";\n");
819 return sql;
820 }
821
822
GetInsertSql(ctlTree * browser)823 wxString pgTable::GetInsertSql(ctlTree *browser)
824 {
825 wxString qms;
826 wxString sql =
827 wxT("INSERT INTO ") + GetQuotedFullIdentifier() + wxT("(\n")
828 wxT(" ") + GetCols(browser, 12, qms, false) + wxT(")\n")
829 wxT(" VALUES (") + qms + wxT(");\n");
830 return sql;
831 }
832
833
GetUpdateSql(ctlTree * browser)834 wxString pgTable::GetUpdateSql(ctlTree *browser)
835 {
836 wxString qms;
837 wxString sql =
838 wxT("UPDATE ") + GetQuotedFullIdentifier() + wxT("\n")
839 wxT(" SET ") + GetCols(browser, 7, qms, true) + wxT("\n")
840 wxT(" WHERE <condition>;\n");
841 return sql;
842 }
843
GetDeleteSql(ctlTree * browser)844 wxString pgTable::GetDeleteSql(ctlTree *browser)
845 {
846 wxString sql =
847 wxT("DELETE FROM ") + GetQuotedFullIdentifier() + wxT("\n")
848 wxT(" WHERE <condition>;\n");
849 return sql;
850 }
851
EnableTriggers(const bool b)852 bool pgTable::EnableTriggers(const bool b)
853 {
854 wxString sql = wxT("ALTER TABLE ") + GetQuotedFullIdentifier() + wxT(" ");
855
856 if (!b)
857 sql += wxT("DISABLE");
858 else
859 sql += wxT("ENABLE");
860
861 sql += wxT(" TRIGGER ALL");
862
863 return GetDatabase()->ExecuteVoid(sql);
864 }
865
UpdateRows()866 void pgTable::UpdateRows()
867 {
868 pgSet *props = ExecuteSet(wxT("SELECT count(*) AS rows FROM ONLY ") + GetQuotedFullIdentifier());
869 if (props)
870 {
871 rows = props->GetLongLong(0);
872 delete props;
873 rowsCounted = true;
874 }
875 }
876
877
UpdateInheritance()878 void pgTable::UpdateInheritance()
879 {
880 // not checked so far
881 pgSet *props = ExecuteSet(
882 wxT("SELECT c.oid, c.relname , nspname\n")
883 wxT(" FROM pg_inherits i\n")
884 wxT(" JOIN pg_class c ON c.oid = i.inhparent\n")
885 wxT(" JOIN pg_namespace n ON n.oid=c.relnamespace\n")
886 wxT(" WHERE i.inhrelid = ") + GetOidStr() + wxT("\n")
887 wxT(" ORDER BY inhseqno"));
888 if (props)
889 {
890 inheritedTableCount = 0;
891 inheritedTables = wxT("");
892 while (!props->Eof())
893 {
894 if (inheritedTableCount)
895 {
896 inheritedTables += wxT(", ");
897 quotedInheritedTables += wxT(", ");
898 }
899 inheritedTables += props->GetVal(wxT("relname"));
900 quotedInheritedTables += GetQuotedSchemaPrefix(props->GetVal(wxT("nspname")))
901 + qtIdent(props->GetVal(wxT("relname")));
902 quotedInheritedTablesList.Add(GetQuotedSchemaPrefix(props->GetVal(wxT("nspname")))
903 + qtIdent(props->GetVal(wxT("relname"))));
904 inheritedTablesOidList.Add(props->GetVal(wxT("oid")));
905 props->MoveNext();
906 inheritedTableCount++;
907 }
908 delete props;
909 }
910 }
911
912
913
914
915
ShowTreeDetail(ctlTree * browser,frmMain * form,ctlListView * properties,ctlSQLBox * sqlPane)916 void pgTable::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
917 {
918 if (!expandedKids)
919 {
920 expandedKids = true;
921
922 browser->RemoveDummyChild(this);
923
924 // Log
925 wxLogInfo(wxT("Adding child object to table %s"), GetIdentifier().c_str());
926
927 browser->AppendCollection(this, columnFactory);
928 browser->AppendCollection(this, constraintFactory);
929 browser->AppendCollection(this, indexFactory);
930 browser->AppendCollection(this, ruleFactory);
931 browser->AppendCollection(this, triggerFactory);
932
933 if (GetConnection() != 0 && GetConnection()->GetIsGreenplum() && GetIsPartitioned())
934 browser->AppendCollection(this, partitionFactory);
935
936
937 // convert list of columns numbers to column names
938 wxStringTokenizer collist(GetPrimaryKeyColNumbers(), wxT(","));
939 wxString cn;
940
941 while (collist.HasMoreTokens())
942 {
943 cn = collist.GetNextToken();
944 pgSet *set = ExecuteSet(
945 wxT("SELECT attname\n")
946 wxT(" FROM pg_attribute\n")
947 wxT(" WHERE attrelid=") + GetOidStr() + wxT(" AND attnum IN (") + cn + wxT(")"));
948 if (set)
949 {
950 if (!primaryKey.IsNull())
951 {
952 quotedPrimaryKey += wxT(", ");
953 primaryKey += wxT(", ");
954 }
955 primaryKey += set->GetVal(0);
956 quotedPrimaryKey += qtIdent(set->GetVal(0));
957 delete set;
958 }
959 }
960
961 if (settings->GetAutoRowCountThreshold() >= GetEstimatedRows())
962 UpdateRows();
963
964 UpdateInheritance();
965 }
966
967 if (properties)
968 {
969 CreateListColumns(properties);
970
971 properties->AppendItem(_("Name"), GetName());
972 if (GetConnection() != 0 && GetConnection()->GetIsGreenplum())
973 {
974 gpPartition *p = dynamic_cast<gpPartition *>(this);
975 if (p != 0)
976 properties->AppendItem(_("Partition Name"), p->GetPartitionName());
977 }
978 properties->AppendItem(_("OID"), GetOid());
979 properties->AppendItem(_("Owner"), GetOwner());
980 if (GetConnection()->BackendMinimumVersion(8, 0))
981 properties->AppendItem(_("Tablespace"), tablespace);
982 properties->AppendItem(_("ACL"), GetAcl());
983 if (GetConnection()->BackendMinimumVersion(9, 0))
984 properties->AppendItem(_("Of type"), ofType);
985 if (GetPrimaryKey().IsNull())
986 properties->AppendItem(_("Primary key"), _("<no primary key>"));
987 else
988 properties->AppendItem(_("Primary key"), GetPrimaryKey());
989
990 properties->AppendItem(_("Rows (estimated)"), GetEstimatedRows());
991
992 if (GetConnection()->BackendMinimumVersion(8, 2))
993 properties->AppendItem(_("Fill factor"), GetFillFactor());
994
995 if (rowsCounted)
996 properties->AppendItem(_("Rows (counted)"), rows);
997 else
998 properties->AppendItem(_("Rows (counted)"), _("not counted"));
999
1000 long setId;
1001 wxString clusterName;
1002 long repStat = GetReplicationStatus(browser, &clusterName, &setId);
1003
1004 wxString clusterInfo;
1005 clusterInfo.Printf(_("Cluster \"%s\", set %ld"), clusterName.c_str(), setId);
1006
1007 wxString repString;
1008 switch (repStat)
1009 {
1010 case REPLICATIONSTATUS_SUBSCRIBED:
1011 repString = _("Published");
1012 break;
1013 case REPLICATIONSTATUS_REPLICATED:
1014 repString = _("Replicated");
1015 break;
1016 case REPLICATIONSTATUS_MULTIPLY_PUBLISHED:
1017 repString = _("Replicated");
1018 clusterName = _("Multiple clusters");
1019 break;
1020 default:
1021 break;
1022 }
1023 if (!repString.IsEmpty())
1024 properties->AppendItem(repString, clusterInfo);
1025
1026 properties->AppendYesNoItem(_("Inherits tables"), GetHasSubclass());
1027 properties->AppendItem(_("Inherited tables count"), GetInheritedTableCount());
1028 if (GetInheritedTableCount())
1029 properties->AppendItem(_("Inherited tables"), GetInheritedTables());
1030 if (GetConnection()->BackendMinimumVersion(9, 1))
1031 properties->AppendYesNoItem(_("Unlogged?"), GetUnlogged());
1032 properties->AppendYesNoItem(_("Has OIDs?"), GetHasOids());
1033 properties->AppendYesNoItem(_("System table?"), GetSystemObject());
1034
1035 /* Custom AutoVacuum Settings */
1036 if (GetConnection()->BackendMinimumVersion(8, 4) && GetCustomAutoVacuumEnabled())
1037 {
1038 if (GetAutoVacuumEnabled() != 2)
1039 {
1040 properties->AppendItem(_("Table auto-vacuum enabled?"), GetAutoVacuumEnabled() == 1 ? _("Yes") : _("No"));
1041 }
1042 if (!GetAutoVacuumVacuumThreshold().IsEmpty())
1043 properties->AppendItem(_("Table auto-vacuum VACUUM base threshold"), GetAutoVacuumVacuumThreshold());
1044 if (!GetAutoVacuumVacuumScaleFactor().IsEmpty())
1045 properties->AppendItem(_("Table auto-vacuum VACUUM scale factor"), GetAutoVacuumVacuumScaleFactor());
1046 if (!GetAutoVacuumAnalyzeThreshold().IsEmpty())
1047 properties->AppendItem(_("Table auto-vacuum ANALYZE base threshold"), GetAutoVacuumAnalyzeThreshold());
1048 if (!GetAutoVacuumAnalyzeScaleFactor().IsEmpty())
1049 properties->AppendItem(_("Table auto-vacuum ANALYZE scale factor"), GetAutoVacuumAnalyzeScaleFactor());
1050 if (!GetAutoVacuumVacuumCostDelay().IsEmpty())
1051 properties->AppendItem(_("Table auto-vacuum VACUUM cost delay"), GetAutoVacuumVacuumCostDelay());
1052 if (!GetAutoVacuumVacuumCostLimit().IsEmpty())
1053 properties->AppendItem(_("Table auto-vacuum VACUUM cost limit"), GetAutoVacuumVacuumCostLimit());
1054 if (!GetAutoVacuumFreezeMinAge().IsEmpty())
1055 properties->AppendItem(_("Table auto-vacuum FREEZE minimum age"), GetAutoVacuumFreezeMinAge());
1056 if (!GetAutoVacuumFreezeMaxAge().IsEmpty())
1057 properties->AppendItem(_("Table auto-vacuum FREEZE maximum age"), GetAutoVacuumFreezeMaxAge());
1058 if (!GetAutoVacuumFreezeTableAge().IsEmpty())
1059 properties->AppendItem(_("Table auto-vacuum FREEZE table age"), GetAutoVacuumFreezeTableAge());
1060 }
1061
1062 /* Custom TOAST-TABLE AutoVacuum Settings */
1063 if (GetConnection()->BackendMinimumVersion(8, 4) &&
1064 GetHasToastTable() &&
1065 GetToastCustomAutoVacuumEnabled())
1066 {
1067 if (GetToastAutoVacuumEnabled() != 2)
1068 {
1069 properties->AppendItem(_("Toast auto-vacuum enabled?"), GetToastAutoVacuumEnabled() == 1 ? _("Yes") : _("No"));
1070 }
1071 if (!GetToastAutoVacuumVacuumThreshold().IsEmpty())
1072 properties->AppendItem(_("Toast auto-vacuum VACUUM base threshold"), GetToastAutoVacuumVacuumThreshold());
1073 if (!GetToastAutoVacuumVacuumScaleFactor().IsEmpty())
1074 properties->AppendItem(_("Toast auto-vacuum VACUUM scale factor"), GetToastAutoVacuumVacuumScaleFactor());
1075 if (!GetToastAutoVacuumVacuumCostDelay().IsEmpty())
1076 properties->AppendItem(_("Toast auto-vacuum VACUUM cost delay"), GetToastAutoVacuumVacuumCostDelay());
1077 if (!GetToastAutoVacuumVacuumCostLimit().IsEmpty())
1078 properties->AppendItem(_("Toast auto-vacuum VACUUM cost limit"), GetToastAutoVacuumVacuumCostLimit());
1079 if (!GetToastAutoVacuumFreezeMinAge().IsEmpty())
1080 properties->AppendItem(_("Toast auto-vacuum FREEZE minimum age"), GetToastAutoVacuumFreezeMinAge());
1081 if (!GetToastAutoVacuumFreezeMaxAge().IsEmpty())
1082 properties->AppendItem(_("Toast auto-vacuum FREEZE maximum age"), GetToastAutoVacuumFreezeMaxAge());
1083 if (!GetToastAutoVacuumFreezeTableAge().IsEmpty())
1084 properties->AppendItem(_("Toast auto-vacuum FREEZE table age"), GetToastAutoVacuumFreezeTableAge());
1085 }
1086 properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
1087
1088 if (!GetLabels().IsEmpty())
1089 {
1090 wxArrayString seclabels = GetProviderLabelArray();
1091 if (seclabels.GetCount() > 0)
1092 {
1093 for (unsigned int index = 0 ; index < seclabels.GetCount() - 1 ; index += 2)
1094 {
1095 properties->AppendItem(seclabels.Item(index), seclabels.Item(index + 1));
1096 }
1097 }
1098 }
1099 }
1100 if (form && GetVacuumHint() && !hintShown)
1101 {
1102 ShowHint(form, false);
1103 }
1104 }
1105
1106
GetCanHint()1107 bool pgTable::GetCanHint()
1108 {
1109 return (GetVacuumHint() || primaryKey.IsEmpty());
1110 }
1111
1112
GetVacuumHint()1113 bool pgTable::GetVacuumHint()
1114 {
1115 bool canHint = false;
1116
1117 if (rowsCounted)
1118 {
1119 if (!estimatedRows || (estimatedRows == 1000 && rows.GetValue() != 1000))
1120 canHint = (rows >= 20);
1121 else
1122 {
1123 double rowsDbl = (wxLongLong_t)rows.GetValue();
1124 double quot = rowsDbl * 10. / estimatedRows;
1125 canHint = ((quot > 12 || quot < 8) && (rowsDbl < estimatedRows - 20. || rowsDbl > estimatedRows + 20.));
1126 }
1127 }
1128 else if (estimatedRows == 1000)
1129 {
1130 canHint = true;
1131 }
1132 return canHint;
1133 }
1134
1135
ShowHint(frmMain * form,bool force)1136 void pgTable::ShowHint(frmMain *form, bool force)
1137 {
1138 hintShown = true;
1139 int rc;
1140
1141 if (force)
1142 {
1143 wxArrayString hints;
1144 if (GetVacuumHint())
1145 hints.Add(HINT_VACUUM);
1146 if (primaryKey.IsEmpty())
1147 hints.Add(HINT_PRIMARYKEY);
1148
1149 rc = frmHint::ShowHint(form, hints, GetFullIdentifier(), force);
1150 }
1151 else
1152 rc = frmHint::ShowHint(form, HINT_VACUUM, GetFullIdentifier(), force);
1153
1154 if (rc == HINT_RC_FIX)
1155 {
1156 frmMaintenance *frm = new frmMaintenance(form, this);
1157 frm->Go();
1158 }
1159 }
1160
1161
1162
1163
Refresh(ctlTree * browser,const wxTreeItemId item)1164 pgObject *pgTable::Refresh(ctlTree *browser, const wxTreeItemId item)
1165 {
1166 pgTable *table = 0;
1167 pgCollection *coll = browser->GetParentCollection(item);
1168 if (coll)
1169 table = (pgTable *)tableFactory.CreateObjects(coll, 0, wxT("\n AND rel.oid=") + GetOidStr());
1170
1171 return table;
1172 }
1173
1174
HasPgstattuple()1175 bool pgTable::HasPgstattuple()
1176 {
1177 return GetConnection()->HasFeature(FEATURE_PGSTATTUPLE);
1178 }
1179
iSetTriggersEnabled(ctlTree * browser,bool enable)1180 void pgTable::iSetTriggersEnabled(ctlTree *browser, bool enable)
1181 {
1182 pgCollection *triggers = browser->FindCollection(triggerFactory, GetId());
1183 if (triggers)
1184 {
1185 triggers->ShowTreeDetail(browser);
1186 treeObjectIterator trgIt(browser, triggers);
1187
1188 pgTrigger *trigger;
1189 while ((trigger = (pgTrigger *)trgIt.GetNextObject()) != 0)
1190 {
1191 trigger->SetEnabled(browser, enable);
1192 }
1193 }
1194 }
1195
1196 ///////////////////////////////////////////////////////////
1197
1198
pgTableCollection(pgaFactory * factory,pgSchema * sch)1199 pgTableCollection::pgTableCollection(pgaFactory *factory, pgSchema *sch)
1200 : pgSchemaObjCollection(factory, sch)
1201 {
1202 }
1203
1204
GetTranslatedMessage(int kindOfMessage) const1205 wxString pgTableCollection::GetTranslatedMessage(int kindOfMessage) const
1206 {
1207 wxString message = wxEmptyString;
1208
1209 switch (kindOfMessage)
1210 {
1211 case RETRIEVINGDETAILS:
1212 message = _("Retrieving details on tables");
1213 break;
1214 case REFRESHINGDETAILS:
1215 message = _("Refreshing tables");
1216 break;
1217 case GRANTWIZARDTITLE:
1218 message = _("Privileges for tables");
1219 break;
1220 case STATISTICSREPORT:
1221 message = _("Tables statistics report");
1222 break;
1223 case OBJSTATISTICS:
1224 message = _("Tables statistics");
1225 break;
1226 case OBJECTSLISTREPORT:
1227 message = _("Tables list report");
1228 break;
1229 }
1230
1231 return message;
1232 }
1233
1234
ShowStatistics(frmMain * form,ctlListView * statistics)1235 void pgTableCollection::ShowStatistics(frmMain *form, ctlListView *statistics)
1236 {
1237 wxLogInfo(wxT("Displaying statistics for tables on %s"), GetSchema()->GetIdentifier().c_str());
1238
1239 bool hasSize = GetConnection()->HasFeature(FEATURE_SIZE);
1240
1241 // Add the statistics view columns
1242 statistics->ClearAll();
1243 statistics->AddColumn(_("Table Name"));
1244 statistics->AddColumn(_("Tuples inserted"));
1245 statistics->AddColumn(_("Tuples updated"));
1246 statistics->AddColumn(_("Tuples deleted"));
1247 if (GetConnection()->BackendMinimumVersion(8, 3))
1248 {
1249 statistics->AddColumn(_("Tuples HOT updated"));
1250 statistics->AddColumn(_("Live tuples"));
1251 statistics->AddColumn(_("Dead tuples"));
1252 }
1253 if (GetConnection()->BackendMinimumVersion(8, 2))
1254 {
1255 statistics->AddColumn(_("Last vacuum"));
1256 statistics->AddColumn(_("Last autovacuum"));
1257 statistics->AddColumn(_("Last analyze"));
1258 statistics->AddColumn(_("Last autoanalyze"));
1259 }
1260 if (GetConnection()->BackendMinimumVersion(9, 1))
1261 {
1262 statistics->AddColumn(_("Vacuum counter"));
1263 statistics->AddColumn(_("Autovacuum counter"));
1264 statistics->AddColumn(_("Analyze counter"));
1265 statistics->AddColumn(_("Autoanalyze counter"));
1266 }
1267 if (hasSize)
1268 statistics->AddColumn(_("Size"), 50);
1269
1270 wxString sql = wxT("SELECT st.relname, n_tup_ins, n_tup_upd, n_tup_del");
1271 if (GetConnection()->BackendMinimumVersion(8, 3))
1272 sql += wxT(", n_tup_hot_upd, n_live_tup, n_dead_tup");
1273 if (GetConnection()->BackendMinimumVersion(8, 2))
1274 sql += wxT(", last_vacuum, last_autovacuum, last_analyze, last_autoanalyze");
1275 if (GetConnection()->BackendMinimumVersion(9, 1))
1276 sql += wxT(", vacuum_count, autovacuum_count, analyze_count, autoanalyze_count");
1277 if (hasSize)
1278 sql += wxT(", pg_size_pretty(pg_relation_size(st.relid)")
1279 wxT(" + CASE WHEN cl.reltoastrelid = 0 THEN 0 ELSE pg_relation_size(cl.reltoastrelid) + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0) END")
1280 wxT(" + COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=st.relid)::int8, 0)) AS size");
1281
1282 sql += wxT("\n FROM pg_stat_all_tables st")
1283 wxT(" JOIN pg_class cl on cl.oid=st.relid\n")
1284 wxT(" WHERE schemaname = ") + qtDbString(GetSchema()->GetName())
1285 + wxT("\n ORDER BY relname");
1286
1287 pgSet *stats = GetDatabase()->ExecuteSet(sql);
1288
1289 if (stats)
1290 {
1291 long pos = 0;
1292 int i;
1293 while (!stats->Eof())
1294 {
1295 i = 4;
1296 statistics->InsertItem(pos, stats->GetVal(wxT("relname")), PGICON_STATISTICS);
1297 statistics->SetItem(pos, 1, stats->GetVal(wxT("n_tup_ins")));
1298 statistics->SetItem(pos, 2, stats->GetVal(wxT("n_tup_upd")));
1299 statistics->SetItem(pos, 3, stats->GetVal(wxT("n_tup_del")));
1300 if (GetConnection()->BackendMinimumVersion(8, 3))
1301 {
1302 statistics->SetItem(pos, i++, stats->GetVal(wxT("n_tup_hot_upd")));
1303 statistics->SetItem(pos, i++, stats->GetVal(wxT("n_live_tup")));
1304 statistics->SetItem(pos, i++, stats->GetVal(wxT("n_dead_tup")));
1305 }
1306 if (GetConnection()->BackendMinimumVersion(8, 2))
1307 {
1308 statistics->SetItem(pos, i++, stats->GetVal(wxT("last_vacuum")));
1309 statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autovacuum")));
1310 statistics->SetItem(pos, i++, stats->GetVal(wxT("last_analyze")));
1311 statistics->SetItem(pos, i++, stats->GetVal(wxT("last_autoanalyze")));
1312 }
1313 if (GetConnection()->BackendMinimumVersion(9, 1))
1314 {
1315 statistics->SetItem(pos, i++, stats->GetVal(wxT("vacuum_count")));
1316 statistics->SetItem(pos, i++, stats->GetVal(wxT("autovacuum_count")));
1317 statistics->SetItem(pos, i++, stats->GetVal(wxT("analyze_count")));
1318 statistics->SetItem(pos, i++, stats->GetVal(wxT("autoanalyze_count")));
1319 }
1320 if (hasSize)
1321 statistics->SetItem(pos, i, stats->GetVal(wxT("size")));
1322 stats->MoveNext();
1323 pos++;
1324 }
1325
1326 delete stats;
1327 }
1328
1329 }
1330
1331
1332 ///////////////////////////////////////////////////////////
1333
1334
ShowStatistics(frmMain * form,ctlListView * statistics)1335 void pgTable::ShowStatistics(frmMain *form, ctlListView *statistics)
1336 {
1337 wxString sql =
1338 wxT("SELECT seq_scan AS ") + qtIdent(_("Sequential Scans")) +
1339 wxT(", seq_tup_read AS ") + qtIdent(_("Sequential Tuples Read")) +
1340 wxT(", idx_scan AS ") + qtIdent(_("Index Scans")) +
1341 wxT(", idx_tup_fetch AS ") + qtIdent(_("Index Tuples Fetched")) +
1342 wxT(", n_tup_ins AS ") + qtIdent(_("Tuples Inserted")) +
1343 wxT(", n_tup_upd AS ") + qtIdent(_("Tuples Updated")) +
1344 wxT(", n_tup_del AS ") + qtIdent(_("Tuples Deleted"));
1345
1346 if (GetConnection()->BackendMinimumVersion(8, 3))
1347 {
1348 sql +=
1349 wxT(", n_tup_hot_upd AS ") + qtIdent(_("Tuples HOT Updated")) +
1350 wxT(", n_live_tup AS ") + qtIdent(_("Live Tuples")) +
1351 wxT(", n_dead_tup AS ") + qtIdent(_("Dead Tuples"));
1352 }
1353
1354 sql += wxT(", heap_blks_read AS ") + qtIdent(_("Heap Blocks Read")) +
1355 wxT(", heap_blks_hit AS ") + qtIdent(_("Heap Blocks Hit")) +
1356 wxT(", idx_blks_read AS ") + qtIdent(_("Index Blocks Read")) +
1357 wxT(", idx_blks_hit AS ") + qtIdent(_("Index Blocks Hit")) +
1358 wxT(", toast_blks_read AS ") + qtIdent(_("Toast Blocks Read")) +
1359 wxT(", toast_blks_hit AS ") + qtIdent(_("Toast Blocks Hit")) +
1360 wxT(", tidx_blks_read AS ") + qtIdent(_("Toast Index Blocks Read")) +
1361 wxT(", tidx_blks_hit AS ") + qtIdent(_("Toast Index Blocks Hit"));
1362
1363 if (GetConnection()->BackendMinimumVersion(8, 2))
1364 {
1365 sql +=
1366 wxT(", last_vacuum AS ") + qtIdent(_("Last Vacuum")) +
1367 wxT(", last_autovacuum AS ") + qtIdent(_("Last Autovacuum")) +
1368 wxT(", last_analyze AS ") + qtIdent(_("Last Analyze")) +
1369 wxT(", last_autoanalyze AS ") + qtIdent(_("Last Autoanalyze"));
1370 }
1371
1372 if (GetConnection()->BackendMinimumVersion(9, 1))
1373 {
1374 sql +=
1375 wxT(", vacuum_count AS ") + qtIdent(_("Vacuum counter")) +
1376 wxT(", autovacuum_count AS ") + qtIdent(_("Autovacuum counter")) +
1377 wxT(", analyze_count AS ") + qtIdent(_("Analyze counter")) +
1378 wxT(", autoanalyze_count AS ") + qtIdent(_("Autoanalyze counter"));
1379 }
1380
1381 if (GetConnection()->HasFeature(FEATURE_SIZE))
1382 {
1383 sql += wxT(", pg_size_pretty(pg_relation_size(stat.relid)) AS ") + qtIdent(_("Table Size"))
1384 + wxT(", CASE WHEN cl.reltoastrelid = 0 THEN ") + qtDbString(_("none")) + wxT(" ELSE pg_size_pretty(pg_relation_size(cl.reltoastrelid)+ COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=cl.reltoastrelid)::int8, 0)) END AS ") + qtIdent(_("Toast Table Size"))
1385 + wxT(", pg_size_pretty(COALESCE((SELECT SUM(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid=stat.relid)::int8, 0)) AS ") + qtIdent(_("Indexes Size"));
1386 }
1387
1388 if (showExtendedStatistics)
1389 {
1390 sql += wxT("\n")
1391 wxT(", tuple_count AS ") + qtIdent(_("Tuple Count")) + wxT(",\n")
1392 wxT(" pg_size_pretty(tuple_len) AS ") + qtIdent(_("Tuple Length")) + wxT(",\n")
1393 wxT(" tuple_percent AS ") + qtIdent(_("Tuple Percent")) + wxT(",\n")
1394 wxT(" dead_tuple_count AS ") + qtIdent(_("Dead Tuple Count")) + wxT(",\n")
1395 wxT(" pg_size_pretty(dead_tuple_len) AS ") + qtIdent(_("Dead Tuple Length")) + wxT(",\n")
1396 wxT(" dead_tuple_percent AS ") + qtIdent(_("Dead Tuple Percent")) + wxT(",\n")
1397 wxT(" pg_size_pretty(free_space) AS ") + qtIdent(_("Free Space")) + wxT(",\n")
1398 wxT(" free_percent AS ") + qtIdent(_("Free Percent")) + wxT("\n")
1399 wxT(" FROM pgstattuple('") + GetQuotedFullIdentifier() + wxT("'), pg_stat_all_tables stat");
1400 }
1401 else
1402 {
1403 sql += wxT("\n")
1404 wxT(" FROM pg_stat_all_tables stat");
1405 }
1406 sql += wxT("\n")
1407 wxT(" JOIN pg_statio_all_tables statio ON stat.relid = statio.relid\n")
1408 wxT(" JOIN pg_class cl ON cl.oid=stat.relid\n")
1409 wxT(" WHERE stat.relid = ") + GetOidStr();
1410
1411
1412 DisplayStatistics(statistics, sql);
1413 }
1414
1415
CreateObjects(pgCollection * collection,ctlTree * browser,const wxString & restriction)1416 pgObject *pgTableFactory::CreateObjects(pgCollection *collection, ctlTree *browser, const wxString &restriction)
1417 {
1418 wxString query;
1419 pgTable *table = 0;
1420
1421 // Greenplum returns reltuples and relpages as tuples per segmentDB and pages per segmentDB,
1422 // so we need to multiply them by the number of segmentDBs to get reasonable values.
1423 long gp_segments = 1;
1424 if (collection->GetConnection()->GetIsGreenplum())
1425 {
1426 query = wxT("SELECT count(*) AS gp_segments from pg_catalog.gp_configuration where definedprimary = 't' and content >= 0");
1427 gp_segments = StrToLong(collection->GetDatabase()->ExecuteScalar(query));
1428 if (gp_segments <= 1)
1429 gp_segments = 1;
1430 }
1431
1432 pgSet *tables;
1433 if (collection->GetConnection()->BackendMinimumVersion(8, 0))
1434 {
1435 query = wxT("SELECT rel.oid, rel.relname, rel.reltablespace AS spcoid, spc.spcname, pg_get_userbyid(rel.relowner) AS relowner, rel.relacl, rel.relhasoids, ")
1436 wxT("rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey,\n")
1437 wxT(" EXISTS(select 1 FROM pg_trigger\n")
1438 wxT(" JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'\n")
1439 wxT(" JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'\n")
1440 wxT(" WHERE tgrelid=rel.oid) AS isrepl,\n");
1441
1442 if (collection->GetConnection()->BackendMinimumVersion(9, 0))
1443 {
1444 query += wxT(" (select count(*) FROM pg_trigger\n")
1445 wxT(" WHERE tgrelid=rel.oid AND tgisinternal = FALSE) AS triggercount\n");
1446 }
1447 else
1448 {
1449 query += wxT(" (select count(*) FROM pg_trigger\n")
1450 wxT(" WHERE tgrelid=rel.oid AND tgisconstraint = FALSE) AS triggercount\n");
1451 }
1452
1453 if (collection->GetConnection()->BackendMinimumVersion(9, 1))
1454 query += wxT(", rel.relpersistence \n");
1455 if (collection->GetConnection()->BackendMinimumVersion(8, 2))
1456 query += wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'fillfactor=([0-9]*)') AS fillfactor \n");
1457 if (collection->GetConnection()->GetIsGreenplum())
1458 {
1459 query += wxT(", gpd.localoid, gpd.attrnums \n");
1460 query += wxT(", substring(array_to_string(rel.reloptions, ',') from 'appendonly=([a-z]*)') AS appendonly \n");
1461 query += wxT(", substring(array_to_string(rel.reloptions, ',') from 'compresslevel=([0-9]*)') AS compresslevel \n");
1462 query += wxT(", substring(array_to_string(rel.reloptions, ',') from 'orientation=([a-z]*)') AS orientation \n");
1463 query += wxT(", substring(array_to_string(rel.reloptions, ',') from 'compresstype=([a-z0-9]*)') AS compresstype \n");
1464 query += wxT(", substring(array_to_string(rel.reloptions, ',') from 'blocksize=([0-9]*)') AS blocksize \n");
1465 query += wxT(", substring(array_to_string(rel.reloptions, ',') from 'checksum=([a-z]*)') AS checksum \n");
1466 if (collection->GetConnection()->GetIsGreenplum() && collection->GetConnection()->BackendMinimumVersion(8, 2, 9))
1467 query += wxT(", rel.oid in (select parrelid from pg_partition) as ispartitioned\n");
1468 }
1469 else if (collection->GetConnection()->BackendMinimumVersion(8, 4))
1470 {
1471 query += wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled \n")
1472 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold \n")
1473 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_vacuum_scale_factor \n")
1474 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS autovacuum_analyze_threshold \n")
1475 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS autovacuum_analyze_scale_factor \n")
1476 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS autovacuum_vacuum_cost_delay \n")
1477 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS autovacuum_vacuum_cost_limit \n")
1478 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age \n")
1479 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age \n")
1480 wxT(", substring(array_to_string(rel.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age \n")
1481 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_enabled=([a-z|0-9]*)') AS toast_autovacuum_enabled \n")
1482 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_threshold=([0-9]*)') AS toast_autovacuum_vacuum_threshold \n")
1483 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_vacuum_scale_factor \n")
1484 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_threshold=([0-9]*)') AS toast_autovacuum_analyze_threshold \n")
1485 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS toast_autovacuum_analyze_scale_factor \n")
1486 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_delay=([0-9]*)') AS toast_autovacuum_vacuum_cost_delay \n")
1487 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_vacuum_cost_limit=([0-9]*)') AS toast_autovacuum_vacuum_cost_limit \n")
1488 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_min_age=([0-9]*)') AS toast_autovacuum_freeze_min_age \n")
1489 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_max_age=([0-9]*)') AS toast_autovacuum_freeze_max_age \n")
1490 wxT(", substring(array_to_string(tst.reloptions, ',') FROM 'autovacuum_freeze_table_age=([0-9]*)') AS toast_autovacuum_freeze_table_age \n")
1491 wxT(", rel.reloptions AS reloptions, tst.reloptions AS toast_reloptions \n")
1492 wxT(", (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable\n");
1493 }
1494 if (collection->GetConnection()->BackendMinimumVersion(9, 0))
1495 query += wxT(", rel.reloftype, typ.typname\n");
1496 if (collection->GetDatabase()->BackendMinimumVersion(9, 1))
1497 {
1498 query += wxT(",\n(SELECT array_agg(label) FROM pg_seclabels sl1 WHERE sl1.objoid=rel.oid AND sl1.objsubid=0) AS labels");
1499 query += wxT(",\n(SELECT array_agg(provider) FROM pg_seclabels sl2 WHERE sl2.objoid=rel.oid AND sl2.objsubid=0) AS providers");
1500 }
1501
1502 query += wxT(" FROM pg_class rel\n")
1503 wxT(" LEFT OUTER JOIN pg_tablespace spc on spc.oid=rel.reltablespace\n")
1504 wxT(" LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)\n")
1505 wxT(" LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'\n");
1506
1507 // Add the toast table for vacuum parameters.
1508 if (collection->GetConnection()->BackendMinimumVersion(8, 4))
1509 query += wxT(" LEFT OUTER JOIN pg_class tst ON tst.oid = rel.reltoastrelid\n");
1510
1511 if (collection->GetConnection()->GetIsGreenplum())
1512 query += wxT(" LEFT OUTER JOIN gp_distribution_policy gpd ON gpd.localoid=rel.oid\n");
1513
1514 if (collection->GetConnection()->BackendMinimumVersion(9, 0))
1515 query += wxT("LEFT JOIN pg_type typ ON rel.reloftype=typ.oid\n");
1516
1517 query += wxT(" WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = ") + collection->GetSchema()->GetOidStr() + wxT("\n");
1518
1519 // Greenplum: Eliminate (sub)partitions from the display, only show the parent partitioned table
1520 // and eliminate external tables
1521 if (collection->GetConnection()->GetIsGreenplum() && collection->GetConnection()->BackendMinimumVersion(8, 2, 9))
1522 query += wxT("AND rel.relstorage <> 'x' AND rel.oid NOT IN (SELECT parchildrelid from pg_partition_rule)");
1523
1524 query += restriction +
1525 wxT(" ORDER BY rel.relname");
1526 }
1527 else
1528 {
1529 query = wxT("SELECT rel.oid, rel.relname, pg_get_userbyid(rel.relowner) AS relowner, rel.relacl, rel.relhasoids, ")
1530 wxT("rel.relhassubclass, rel.reltuples, des.description, con.conname, con.conkey,\n")
1531 wxT(" (select count(*) FROM pg_trigger\n")
1532 wxT(" WHERE tgrelid=rel.oid AND tgisconstraint = FALSE) AS triggercount,\n")
1533 wxT(" EXISTS(select 1 FROM pg_trigger\n")
1534 wxT(" JOIN pg_proc pt ON pt.oid=tgfoid AND pt.proname='logtrigger'\n")
1535 wxT(" JOIN pg_proc pc ON pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'\n")
1536 wxT(" WHERE tgrelid=rel.oid) AS isrepl\n")
1537 wxT(" FROM pg_class rel\n")
1538 wxT(" LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0 AND des.classoid='pg_class'::regclass)\n")
1539 wxT(" LEFT OUTER JOIN pg_constraint con ON con.conrelid=rel.oid AND con.contype='p'\n")
1540 wxT(" WHERE rel.relkind IN ('r','s','t') AND rel.relnamespace = ") + collection->GetSchema()->GetOidStr() + wxT("\n")
1541 + restriction +
1542 wxT(" ORDER BY rel.relname");
1543 }
1544 tables = collection->GetDatabase()->ExecuteSet(query);
1545 if (tables)
1546 {
1547 while (!tables->Eof())
1548 {
1549 table = new pgTable(collection->GetSchema(), tables->GetVal(wxT("relname")));
1550
1551 table->iSetOid(tables->GetOid(wxT("oid")));
1552 table->iSetOwner(tables->GetVal(wxT("relowner")));
1553 table->iSetAcl(tables->GetVal(wxT("relacl")));
1554 if (collection->GetConnection()->BackendMinimumVersion(8, 0))
1555 {
1556 if (tables->GetOid(wxT("spcoid")) == 0)
1557 table->iSetTablespaceOid(collection->GetDatabase()->GetTablespaceOid());
1558 else
1559 table->iSetTablespaceOid(tables->GetOid(wxT("spcoid")));
1560
1561 if (tables->GetVal(wxT("spcname")) == wxEmptyString)
1562 table->iSetTablespace(collection->GetDatabase()->GetTablespace());
1563 else
1564 table->iSetTablespace(tables->GetVal(wxT("spcname")));
1565 }
1566 if (collection->GetConnection()->BackendMinimumVersion(9, 0))
1567 {
1568 table->iSetOfTypeOid(tables->GetOid(wxT("reloftype")));
1569 table->iSetOfType(tables->GetVal(wxT("typname")));
1570 }
1571 else
1572 {
1573 table->iSetOfTypeOid(0);
1574 table->iSetOfType(wxT(""));
1575 }
1576 table->iSetComment(tables->GetVal(wxT("description")));
1577 if (collection->GetConnection()->BackendMinimumVersion(9, 1))
1578 table->iSetUnlogged(tables->GetVal(wxT("relpersistence")) == wxT("u"));
1579 else
1580 table->iSetUnlogged(false);
1581 table->iSetHasOids(tables->GetBool(wxT("relhasoids")));
1582 table->iSetEstimatedRows(tables->GetDouble(wxT("reltuples")) * gp_segments);
1583 if (collection->GetConnection()->BackendMinimumVersion(8, 2))
1584 {
1585 table->iSetFillFactor(tables->GetVal(wxT("fillfactor")));
1586 }
1587 if (collection->GetConnection()->BackendMinimumVersion(8, 4))
1588 {
1589 table->iSetRelOptions(tables->GetVal(wxT("reloptions")));
1590 if (table->GetCustomAutoVacuumEnabled())
1591 {
1592 if (tables->GetVal(wxT("autovacuum_enabled")).IsEmpty())
1593 table->iSetAutoVacuumEnabled(2);
1594 else if (tables->GetBool(wxT("autovacuum_enabled")))
1595 table->iSetAutoVacuumEnabled(1);
1596 else
1597 table->iSetAutoVacuumEnabled(0);
1598 table->iSetAutoVacuumVacuumThreshold(tables->GetVal(wxT("autovacuum_vacuum_threshold")));
1599 table->iSetAutoVacuumVacuumScaleFactor(tables->GetVal(wxT("autovacuum_vacuum_scale_factor")));
1600 table->iSetAutoVacuumAnalyzeThreshold(tables->GetVal(wxT("autovacuum_analyze_threshold")));
1601 table->iSetAutoVacuumAnalyzeScaleFactor(tables->GetVal(wxT("autovacuum_analyze_scale_factor")));
1602 table->iSetAutoVacuumVacuumCostDelay(tables->GetVal(wxT("autovacuum_vacuum_cost_delay")));
1603 table->iSetAutoVacuumVacuumCostLimit(tables->GetVal(wxT("autovacuum_vacuum_cost_limit")));
1604 table->iSetAutoVacuumFreezeMinAge(tables->GetVal(wxT("autovacuum_freeze_min_age")));
1605 table->iSetAutoVacuumFreezeMaxAge(tables->GetVal(wxT("autovacuum_freeze_max_age")));
1606 table->iSetAutoVacuumFreezeTableAge(tables->GetVal(wxT("autovacuum_freeze_table_age")));
1607 }
1608 table->iSetHasToastTable(tables->GetBool(wxT("hastoasttable")));
1609 if (table->GetHasToastTable())
1610 {
1611 table->iSetToastRelOptions(tables->GetVal(wxT("toast_reloptions")));
1612
1613 if (table->GetToastCustomAutoVacuumEnabled())
1614 {
1615 if (tables->GetVal(wxT("toast_autovacuum_enabled")).IsEmpty())
1616 table->iSetToastAutoVacuumEnabled(2);
1617 else if (tables->GetBool(wxT("toast_autovacuum_enabled")))
1618 table->iSetToastAutoVacuumEnabled(1);
1619 else
1620 table->iSetToastAutoVacuumEnabled(0);
1621
1622 table->iSetToastAutoVacuumVacuumThreshold(tables->GetVal(wxT("toast_autovacuum_vacuum_threshold")));
1623 table->iSetToastAutoVacuumVacuumScaleFactor(tables->GetVal(wxT("toast_autovacuum_vacuum_scale_factor")));
1624 table->iSetToastAutoVacuumVacuumCostDelay(tables->GetVal(wxT("toast_autovacuum_vacuum_cost_delay")));
1625 table->iSetToastAutoVacuumVacuumCostLimit(tables->GetVal(wxT("toast_autovacuum_vacuum_cost_limit")));
1626 table->iSetToastAutoVacuumFreezeMinAge(tables->GetVal(wxT("toast_autovacuum_freeze_min_age")));
1627 table->iSetToastAutoVacuumFreezeMaxAge(tables->GetVal(wxT("toast_autovacuum_freeze_max_age")));
1628 table->iSetToastAutoVacuumFreezeTableAge(tables->GetVal(wxT("toast_autovacuum_freeze_table_age")));
1629 }
1630 }
1631 }
1632 table->iSetHasSubclass(tables->GetBool(wxT("relhassubclass")));
1633 table->iSetPrimaryKeyName(tables->GetVal(wxT("conname")));
1634 table->iSetIsReplicated(tables->GetBool(wxT("isrepl")));
1635 table->iSetTriggerCount(tables->GetLong(wxT("triggercount")));
1636 wxString cn = tables->GetVal(wxT("conkey"));
1637 cn = cn.Mid(1, cn.Length() - 2);
1638 table->iSetPrimaryKeyColNumbers(cn);
1639
1640 if (collection->GetConnection()->GetIsGreenplum())
1641 {
1642 Oid lo = tables->GetOid(wxT("localoid"));
1643 wxString db = tables->GetVal(wxT("attrnums"));
1644 db = db.Mid(1, db.Length() - 2);
1645 table->iSetDistributionColNumbers(db);
1646 if (lo > 0 && db.Length() == 0)
1647 table->iSetDistributionIsRandom();
1648 table->iSetAppendOnly(tables->GetVal(wxT("appendonly")));
1649 table->iSetCompressLevel(tables->GetVal(wxT("compresslevel")));
1650 table->iSetOrientation(tables->GetVal(wxT("orientation")));
1651 table->iSetCompressType(tables->GetVal(wxT("compresstype")));
1652 table->iSetBlocksize(tables->GetVal(wxT("blocksize")));
1653 table->iSetChecksum(tables->GetVal(wxT("checksum")));
1654
1655 table->iSetPartitionDef(wxT(""));
1656 table->iSetIsPartitioned(false);
1657
1658 if (collection->GetConnection()->BackendMinimumVersion(8, 2, 9))
1659 {
1660 table->iSetIsPartitioned(tables->GetBool(wxT("ispartitioned")));
1661 }
1662
1663 }
1664
1665 if (collection->GetConnection()->BackendMinimumVersion(9, 1))
1666 {
1667 table->iSetProviders(tables->GetVal(wxT("providers")));
1668 table->iSetLabels(tables->GetVal(wxT("labels")));
1669 }
1670
1671 if (browser)
1672 {
1673 browser->AppendObject(collection, table);
1674 tables->MoveNext();
1675 }
1676 else
1677 break;
1678 }
1679
1680 delete tables;
1681 }
1682 return table;
1683 }
1684
CanCreate()1685 bool pgTableObjCollection::CanCreate()
1686 {
1687 // We don't create sub-objects of Views or External tables
1688 if (GetTable()->GetMetaType() == PGM_VIEW || GetTable()->GetMetaType() == GP_EXTTABLE)
1689 return false;
1690
1691 return GetSchema()->GetCreatePrivilege();
1692 }
1693
1694
1695 #include "images/table.pngc"
1696 #include "images/table-repl.pngc"
1697 #include "images/table-repl-sm.pngc"
1698 #include "images/table-sm.pngc"
1699 #include "images/tables.pngc"
1700
pgTableFactory()1701 pgTableFactory::pgTableFactory()
1702 : pgSchemaObjFactory(__("Table"), __("New Table..."), __("Create a new Table."), table_png_img, table_sm_png_img)
1703 {
1704 metaType = PGM_TABLE;
1705 if (WantSmallIcon())
1706 replicatedIconId = addIcon(table_repl_sm_png_img);
1707 else
1708 replicatedIconId = addIcon(table_repl_png_img);
1709 }
1710
CreateCollection(pgObject * obj)1711 pgCollection *pgTableFactory::CreateCollection(pgObject *obj)
1712 {
1713 return new pgTableCollection(GetCollectionFactory(), (pgSchema *)obj);
1714 }
1715
1716 pgTableFactory tableFactory;
1717 static pgaCollectionFactory cf(&tableFactory, __("Tables"), tables_png_img);
1718
1719
CreateCollection(pgObject * obj)1720 pgCollection *pgTableObjFactory::CreateCollection(pgObject *obj)
1721 {
1722 return new pgTableObjCollection(GetCollectionFactory(), (pgTable *)obj);
1723 }
1724
1725
countRowsFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1726 countRowsFactory::countRowsFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1727 {
1728 mnu->Append(id, _("&Count"), _("Count rows in the selected object."));
1729 }
1730
1731
StartDialog(frmMain * form,pgObject * obj)1732 wxWindow *countRowsFactory::StartDialog(frmMain *form, pgObject *obj)
1733 {
1734 form->StartMsg(_("Counting rows"));
1735
1736 ((pgTable *)obj)->UpdateRows();
1737
1738 wxTreeItemId item = form->GetBrowser()->GetSelection();
1739 if (obj == form->GetBrowser()->GetObject(item))
1740 obj->ShowTreeDetail(form->GetBrowser(), 0, form->GetProperties());
1741
1742 form->EndMsg();
1743
1744 return 0;
1745 }
1746
1747
CheckEnable(pgObject * obj)1748 bool countRowsFactory::CheckEnable(pgObject *obj)
1749 {
1750 return obj && obj->IsCreatedBy(tableFactory);
1751 }
1752
1753
executePgstattupleFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1754 executePgstattupleFactory::executePgstattupleFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1755 {
1756 mnu->Append(id, _("&Extended table statistics"), _("Get extended statistics via pgstattuple for the selected object."), wxITEM_CHECK);
1757 }
1758
1759
StartDialog(frmMain * form,pgObject * obj)1760 wxWindow *executePgstattupleFactory::StartDialog(frmMain *form, pgObject *obj)
1761 {
1762 if (!((pgTable *)obj)->GetShowExtendedStatistics())
1763 {
1764 ((pgTable *)obj)->iSetShowExtendedStatistics(true);
1765 wxTreeItemId item = form->GetBrowser()->GetSelection();
1766 if (obj == form->GetBrowser()->GetObject(item))
1767 form->SelectStatisticsTab();
1768 }
1769 else
1770 ((pgTable *)obj)->iSetShowExtendedStatistics(false);
1771
1772 form->GetMenuFactories()->CheckMenu(obj, form->GetMenuBar(), (ctlMenuToolbar *)form->GetToolBar());
1773
1774 return 0;
1775 }
1776
1777
CheckEnable(pgObject * obj)1778 bool executePgstattupleFactory::CheckEnable(pgObject *obj)
1779 {
1780 return obj && obj->IsCreatedBy(tableFactory) && ((pgTable *)obj)->HasPgstattuple();
1781 }
1782
CheckChecked(pgObject * obj)1783 bool executePgstattupleFactory::CheckChecked(pgObject *obj)
1784 {
1785 return obj && obj->IsCreatedBy(tableFactory) && ((pgTable *)obj)->GetShowExtendedStatistics();
1786 }
1787
disableAllTriggersFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1788 disableAllTriggersFactory::disableAllTriggersFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1789 {
1790 mnu->Append(id, _("Disable triggers"), _("Disable all triggers on the selected table."));
1791 }
1792
1793
StartDialog(frmMain * form,pgObject * obj)1794 wxWindow *disableAllTriggersFactory::StartDialog(frmMain *form, pgObject *obj)
1795 {
1796 if (wxMessageBox(_("Are you sure you wish to disable all triggers on this table?"), _("Disable triggers"), wxYES_NO) != wxYES)
1797 return 0;
1798
1799 if (!((pgTable *)obj)->EnableTriggers(false))
1800 return 0;
1801
1802 ((pgTable *)obj)->iSetTriggersEnabled(form->GetBrowser(), false);
1803
1804 return 0;
1805 }
1806
1807
CheckEnable(pgObject * obj)1808 bool disableAllTriggersFactory::CheckEnable(pgObject *obj)
1809 {
1810 return obj && obj->IsCreatedBy(tableFactory) && obj->CanEdit()
1811 && (obj->GetOwner() == obj->GetConnection()->GetUser() || obj->GetServer()->GetSuperUser())
1812 && ((pgTable *)obj)->GetConnection()->BackendMinimumVersion(8, 1)
1813 && ((pgTable *)obj)->GetTriggerCount() > 0;
1814 }
1815
enableAllTriggersFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1816 enableAllTriggersFactory::enableAllTriggersFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1817 {
1818 mnu->Append(id, _("Enable triggers"), _("Enable all triggers on the selected table."));
1819 }
1820
1821
StartDialog(frmMain * form,pgObject * obj)1822 wxWindow *enableAllTriggersFactory::StartDialog(frmMain *form, pgObject *obj)
1823 {
1824 if (wxMessageBox(_("Are you sure you wish to enable all triggers on this table?"), _("Enable triggers"), wxYES_NO) != wxYES)
1825 return 0;
1826
1827 if (!((pgTable *)obj)->EnableTriggers(true))
1828 return 0;
1829
1830 ((pgTable *)obj)->iSetTriggersEnabled(form->GetBrowser(), true);
1831
1832 return 0;
1833 }
1834
1835
CheckEnable(pgObject * obj)1836 bool enableAllTriggersFactory::CheckEnable(pgObject *obj)
1837 {
1838 return obj && obj->IsCreatedBy(tableFactory) && obj->CanEdit()
1839 && (obj->GetOwner() == obj->GetConnection()->GetUser() || obj->GetServer()->GetSuperUser())
1840 && ((pgTable *)obj)->GetConnection()->BackendMinimumVersion(8, 1)
1841 && ((pgTable *)obj)->GetTriggerCount() > 0;
1842 }
1843
truncateFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1844 truncateFactory::truncateFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1845 {
1846 mnu->Append(id, _("&Truncate"), _("Truncate the selected table."));
1847 }
1848
1849
StartDialog(frmMain * form,pgObject * obj)1850 wxWindow *truncateFactory::StartDialog(frmMain *form, pgObject *obj)
1851 {
1852 if (wxMessageBox(_("Are you sure you wish to truncate this table?\n\nWARNING: This action will delete ALL data in the table!"), _("Truncate table"), wxYES_NO | wxICON_QUESTION | wxNO_DEFAULT) != wxYES)
1853 return 0;
1854
1855 ((pgTable *)obj)->Truncate(false);
1856 ((pgTable *)obj)->UpdateRows();
1857 wxTreeItemId item = form->GetBrowser()->GetSelection();
1858 if (obj == form->GetBrowser()->GetObject(item))
1859 obj->ShowTreeDetail(form->GetBrowser(), 0, form->GetProperties());
1860
1861 return 0;
1862 }
1863
1864
CheckEnable(pgObject * obj)1865 bool truncateFactory::CheckEnable(pgObject *obj)
1866 {
1867 return obj && obj->IsCreatedBy(tableFactory);
1868 }
1869
1870
truncateCascadedFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1871 truncateCascadedFactory::truncateCascadedFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1872 {
1873 mnu->Append(id, _("Truncate Cascaded"), _("Truncate the selected table and all referencing tables."));
1874 }
1875
1876
StartDialog(frmMain * form,pgObject * obj)1877 wxWindow *truncateCascadedFactory::StartDialog(frmMain *form, pgObject *obj)
1878 {
1879 if (wxMessageBox(_("Are you sure you wish to truncate this table and all tables that have foreign key references to this table?\n\nWARNING: This action will delete ALL data in the tables!"), _("Truncate table cascaded"), wxYES_NO | wxICON_QUESTION | wxNO_DEFAULT) != wxYES)
1880 return 0;
1881
1882 ((pgTable *)obj)->Truncate(true);
1883 ((pgTable *)obj)->UpdateRows();
1884 wxTreeItemId item = form->GetBrowser()->GetSelection();
1885 if (obj == form->GetBrowser()->GetObject(item))
1886 obj->ShowTreeDetail(form->GetBrowser(), 0, form->GetProperties());
1887
1888 return 0;
1889 }
1890
1891
CheckEnable(pgObject * obj)1892 bool truncateCascadedFactory::CheckEnable(pgObject *obj)
1893 {
1894 return obj && obj->IsCreatedBy(tableFactory) && ((pgTable *)obj)->GetConnection()->BackendMinimumVersion(8, 2);
1895 }
1896
1897
resetTableStatsFactory(menuFactoryList * list,wxMenu * mnu,ctlMenuToolbar * toolbar)1898 resetTableStatsFactory::resetTableStatsFactory(menuFactoryList *list, wxMenu *mnu, ctlMenuToolbar *toolbar) : contextActionFactory(list)
1899 {
1900 mnu->Append(id, _("&Reset table statistics"), _("Reset statistics of the selected table."));
1901 }
1902
1903
StartDialog(frmMain * form,pgObject * obj)1904 wxWindow *resetTableStatsFactory::StartDialog(frmMain *form, pgObject *obj)
1905 {
1906 if (wxMessageBox(_("Are you sure you wish to reset statistics of this table?"), _("Reset statistics"), wxYES_NO) != wxYES)
1907 return 0;
1908
1909 ((pgTable *)obj)->ResetStats();
1910 ((pgTable *)obj)->ShowStatistics(form, form->GetStatistics());
1911
1912 return 0;
1913 }
1914
1915
CheckEnable(pgObject * obj)1916 bool resetTableStatsFactory::CheckEnable(pgObject *obj)
1917 {
1918 return obj && obj->IsCreatedBy(tableFactory) && ((pgTable *)obj)->GetConnection()->BackendMinimumVersion(9, 0);
1919 }
1920