wxString pgView::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- View: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP VIEW ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE OR REPLACE VIEW ") + GetQuotedFullIdentifier();
if (GetConnection()->BackendMinimumVersion(9, 2) && GetSecurityBarrier().Length() > 0)
sql += wxT(" WITH (security_barrier=") + GetSecurityBarrier() + wxT(")");
sql += wxT(" AS \n")
+ GetFormattedDefinition()
+ wxT("\n\n")
+ GetOwnerSql(7, 3, wxT("TABLE ") + GetQuotedFullIdentifier());
if (GetConnection()->BackendMinimumVersion(8, 2))
sql += GetGrant(wxT("arwdxt"), wxT("TABLE ") + GetQuotedFullIdentifier());
else
sql += GetGrant(wxT("arwdRxt"), wxT("TABLE ") + GetQuotedFullIdentifier());
sql += GetCommentSql()
+ wxT("\n");
pgCollection *columns = browser->FindCollection(columnFactory, GetId());
if (columns)
{
wxString defaults, comments;
columns->ShowTreeDetail(browser);
treeObjectIterator colIt(browser, columns);
pgColumn *column;
while ((column = (pgColumn *)colIt.GetNextObject()) != 0)
{
column->ShowTreeDetail(browser);
if (column->GetColNumber() > 0)
{
if (!column->GetDefault().IsEmpty())
{
defaults += wxT("ALTER TABLE ") + GetQuotedFullIdentifier()
+ wxT(" ALTER COLUMN ") + column->GetQuotedIdentifier()
+ wxT(" SET DEFAULT ") + column->GetDefault()
+ wxT(";\n");
}
comments += column->GetCommentSql();
}
}
if (!defaults.IsEmpty())
sql += defaults + wxT("\n");
if (!comments.IsEmpty())
sql += comments + wxT("\n");
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
AppendStuff(sql, browser, ruleFactory);
AppendStuff(sql, browser, triggerFactory);
}
return sql;
}
void pgView::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
{
if (!expandedKids)
{
expandedKids = true;
browser->RemoveDummyChild(this);
browser->AppendCollection(this, columnFactory);
pgCollection *collection = browser->AppendCollection(this, ruleFactory);
collection->iSetOid(GetOid());
collection->ShowTreeDetail(browser);
treeObjectIterator colIt(browser, collection);
pgRule *rule;
while (!hasInsertRule && !hasUpdateRule && !hasDeleteRule && (rule = (pgRule *)colIt.GetNextObject()) != 0)
{
if (rule->GetEvent().Find(wxT("INSERT")) >= 0)
hasInsertRule = true;
if (rule->GetEvent().Find(wxT("UPDATE")) >= 0)
hasUpdateRule = true;
if (rule->GetEvent().Find(wxT("DELETE")) >= 0)
hasDeleteRule = true;
}
if (GetConnection()->BackendMinimumVersion(9, 1))
browser->AppendCollection(this, triggerFactory);
}
if (properties)
{
CreateListColumns(properties);
wxString def = GetDefinition().Left(250);
def.Replace(wxT("\n"), wxT(" "));
properties->AppendItem(_("Name"), GetName());
properties->AppendItem(_("OID"), GetOid());
properties->AppendItem(_("Owner"), GetOwner());
properties->AppendItem(_("ACL"), GetAcl());
properties->AppendItem(_("Definition"), def);
properties->AppendYesNoItem(_("System view?"), GetSystemObject());
properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
if (GetConnection()->BackendMinimumVersion(9, 2) && GetSecurityBarrier().Length() > 0)
properties->AppendItem(_("Security barrier?"), GetSecurityBarrier());
if (!GetLabels().IsEmpty())
{
wxArrayString seclabels = GetProviderLabelArray();
if (seclabels.GetCount() > 0)
{
for (unsigned int index = 0 ; index < seclabels.GetCount() - 1 ; index += 2)
{
properties->AppendItem(seclabels.Item(index), seclabels.Item(index + 1));
}
}
}
}
}
void pgView::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
{
if (!expandedKids)
{
expandedKids = true;
browser->RemoveDummyChild(this);
browser->AppendCollection(this, columnFactory);
pgCollection *collection = browser->AppendCollection(this, ruleFactory);
collection->iSetOid(GetOid());
collection->ShowTreeDetail(browser);
treeObjectIterator colIt(browser, collection);
pgRule *rule;
while (!hasInsertRule && !hasUpdateRule && !hasDeleteRule && (rule = (pgRule *)colIt.GetNextObject()) != 0)
{
if (rule->GetEvent().Find(wxT("INSERT")) >= 0)
hasInsertRule = true;
if (rule->GetEvent().Find(wxT("UPDATE")) >= 0)
hasUpdateRule = true;
if (rule->GetEvent().Find(wxT("DELETE")) >= 0)
hasDeleteRule = true;
}
if (GetConnection()->BackendMinimumVersion(9, 1))
browser->AppendCollection(this, triggerFactory);
}
if (properties)
{
CreateListColumns(properties);
wxString def = GetDefinition().Left(250);
def.Replace(wxT("\n"), wxT(" "));
properties->AppendItem(_("Name"), GetName());
properties->AppendItem(_("OID"), GetOid());
properties->AppendItem(_("Owner"), GetOwner());
properties->AppendItem(_("ACL"), GetAcl());
properties->AppendItem(_("Definition"), def);
properties->AppendYesNoItem(_("System view?"), GetSystemObject());
if (GetConnection()->BackendMinimumVersion(9, 2) && GetSecurityBarrier().Length() > 0)
properties->AppendItem(_("Security barrier?"), GetSecurityBarrier());
if (GetConnection()->BackendMinimumVersion(9, 3))
properties->AppendYesNoItem(_("Materialized view?"), GetMaterializedView());
/* Custom AutoVacuum Settings */
if (GetConnection()->BackendMinimumVersion(9, 3) && GetMaterializedView())
{
if (!GetFillFactor().IsEmpty())
properties->AppendItem(_("Fill factor"), GetFillFactor());
if (GetCustomAutoVacuumEnabled())
{
if (GetAutoVacuumEnabled() != 2)
{
properties->AppendItem(_("Table auto-vacuum enabled?"), GetAutoVacuumEnabled() == 1 ? _("Yes") : _("No"));
}
if (!GetAutoVacuumVacuumThreshold().IsEmpty())
properties->AppendItem(_("Table auto-vacuum VACUUM base threshold"), GetAutoVacuumVacuumThreshold());
if (!GetAutoVacuumVacuumScaleFactor().IsEmpty())
properties->AppendItem(_("Table auto-vacuum VACUUM scale factor"), GetAutoVacuumVacuumScaleFactor());
if (!GetAutoVacuumAnalyzeThreshold().IsEmpty())
properties->AppendItem(_("Table auto-vacuum ANALYZE base threshold"), GetAutoVacuumAnalyzeThreshold());
if (!GetAutoVacuumAnalyzeScaleFactor().IsEmpty())
properties->AppendItem(_("Table auto-vacuum ANALYZE scale factor"), GetAutoVacuumAnalyzeScaleFactor());
if (!GetAutoVacuumVacuumCostDelay().IsEmpty())
properties->AppendItem(_("Table auto-vacuum VACUUM cost delay"), GetAutoVacuumVacuumCostDelay());
if (!GetAutoVacuumVacuumCostLimit().IsEmpty())
properties->AppendItem(_("Table auto-vacuum VACUUM cost limit"), GetAutoVacuumVacuumCostLimit());
if (!GetAutoVacuumFreezeMinAge().IsEmpty())
properties->AppendItem(_("Table auto-vacuum FREEZE minimum age"), GetAutoVacuumFreezeMinAge());
if (!GetAutoVacuumFreezeMaxAge().IsEmpty())
properties->AppendItem(_("Table auto-vacuum FREEZE maximum age"), GetAutoVacuumFreezeMaxAge());
if (!GetAutoVacuumFreezeTableAge().IsEmpty())
properties->AppendItem(_("Table auto-vacuum FREEZE table age"), GetAutoVacuumFreezeTableAge());
}
if (GetHasToastTable() && GetToastCustomAutoVacuumEnabled())
{
if (GetToastAutoVacuumEnabled() != 2)
{
properties->AppendItem(_("Toast auto-vacuum enabled?"), GetToastAutoVacuumEnabled() == 1 ? _("Yes") : _("No"));
}
if (!GetToastAutoVacuumVacuumThreshold().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum VACUUM base threshold"), GetToastAutoVacuumVacuumThreshold());
if (!GetToastAutoVacuumVacuumScaleFactor().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum VACUUM scale factor"), GetToastAutoVacuumVacuumScaleFactor());
if (!GetToastAutoVacuumVacuumCostDelay().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum VACUUM cost delay"), GetToastAutoVacuumVacuumCostDelay());
if (!GetToastAutoVacuumVacuumCostLimit().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum VACUUM cost limit"), GetToastAutoVacuumVacuumCostLimit());
if (!GetToastAutoVacuumFreezeMinAge().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum FREEZE minimum age"), GetToastAutoVacuumFreezeMinAge());
if (!GetToastAutoVacuumFreezeMaxAge().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum FREEZE maximum age"), GetToastAutoVacuumFreezeMaxAge());
if (!GetToastAutoVacuumFreezeTableAge().IsEmpty())
properties->AppendItem(_("Toast auto-vacuum FREEZE table age"), GetToastAutoVacuumFreezeTableAge());
}
properties->AppendItem(_("Tablespace"), tablespace);
if (GetIsPopulated().Cmp(wxT("t")) == 0)
properties->AppendItem(_("With data?"), _("Yes"));
else
properties->AppendItem(_("With data?"), _("No"));
}
if (GetConnection()->BackendMinimumVersion(9, 4))
properties->AppendItem(_("Check Option"), GetCheckOption());
if (!GetLabels().IsEmpty())
{
wxArrayString seclabels = GetProviderLabelArray();
if (seclabels.GetCount() > 0)
{
for (unsigned int index = 0 ; index < seclabels.GetCount() - 1 ; index += 2)
{
properties->AppendItem(seclabels.Item(index), seclabels.Item(index + 1));
}
}
}
properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
}
}
wxString pgView::GetSql(ctlTree *browser)
{
wxString withoptions;
if (sql.IsNull())
{
bool IsMatViewFlag = false;
if (!GetMaterializedView())
{
sql = wxT("-- View: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP VIEW ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE OR REPLACE VIEW ") + GetQuotedFullIdentifier();
if (GetConnection()->BackendMinimumVersion(9, 2) && GetSecurityBarrier().Length() > 0)
withoptions = wxT("security_barrier=") + GetSecurityBarrier();
if (GetConnection()->BackendMinimumVersion(9, 4) && GetCheckOption().Length() > 0)
{
if (withoptions.Length() > 0)
withoptions += wxT(", ");
withoptions = wxT("check_option=") + GetCheckOption();
}
if (withoptions.Length() > 0)
sql += wxT(" WITH (") + withoptions + wxT(")");
}
else
{
sql = wxT("-- Materialized View: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP MATERIALIZED VIEW ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE MATERIALIZED VIEW ") + GetQuotedFullIdentifier();
IsMatViewFlag = true;
if (GetConnection()->BackendMinimumVersion(9, 3))
{
if (GetFillFactor().Length() > 0 || GetAutoVacuumEnabled() == 1 || GetToastAutoVacuumEnabled() == 1)
{
bool tmpFlagTable = false;
bool tmpFlagToastTable = false;
sql += wxT("\nWITH (");
if (GetFillFactor().Length() > 0)
sql += wxT("\n FILLFACTOR=") + GetFillFactor();
else
tmpFlagTable = true;
if (GetCustomAutoVacuumEnabled())
{
if (GetAutoVacuumEnabled() == 1)
{
if (tmpFlagTable)
sql += wxT("\n autovacuum_enabled=true");
else
sql += wxT(",\n autovacuum_enabled=true");
tmpFlagToastTable = true;
}
else if (GetCustomAutoVacuumEnabled() == 0)
{
sql += wxT(",\n autovacuum_enabled=false");
}
if (!GetAutoVacuumVacuumThreshold().IsEmpty())
{
sql += wxT(",\n autovacuum_vacuum_threshold=") + GetAutoVacuumVacuumThreshold();
}
if (!GetAutoVacuumVacuumScaleFactor().IsEmpty())
{
sql += wxT(",\n autovacuum_vacuum_scale_factor=") + GetAutoVacuumVacuumScaleFactor();
}
if (!GetAutoVacuumAnalyzeThreshold().IsEmpty())
{
sql += wxT(",\n autovacuum_analyze_threshold=") + GetAutoVacuumAnalyzeThreshold();
}
if (!GetAutoVacuumAnalyzeScaleFactor().IsEmpty())
{
sql += wxT(",\n autovacuum_analyze_scale_factor=") + GetAutoVacuumAnalyzeScaleFactor();
}
if (!GetAutoVacuumVacuumCostDelay().IsEmpty())
{
sql += wxT(",\n autovacuum_vacuum_cost_delay=") + GetAutoVacuumVacuumCostDelay();
}
if (!GetAutoVacuumVacuumCostLimit().IsEmpty())
{
sql += wxT(",\n autovacuum_vacuum_cost_limit=") + GetAutoVacuumVacuumCostLimit();
}
if (!GetAutoVacuumFreezeMinAge().IsEmpty())
{
sql += wxT(",\n autovacuum_freeze_min_age=") + GetAutoVacuumFreezeMinAge();
}
if (!GetAutoVacuumFreezeMaxAge().IsEmpty())
{
sql += wxT(",\n autovacuum_freeze_max_age=") + GetAutoVacuumFreezeMaxAge();
}
if (!GetAutoVacuumFreezeTableAge().IsEmpty())
{
sql += wxT(",\n autovacuum_freeze_table_age=") + GetAutoVacuumFreezeTableAge();
}
}
if (GetHasToastTable() && GetToastCustomAutoVacuumEnabled())
{
if (GetToastAutoVacuumEnabled() == 1)
{
if (tmpFlagTable && !tmpFlagToastTable)
sql += wxT("\n toast.autovacuum_enabled=true");
else
sql += wxT(",\n toast.autovacuum_enabled=true");
}
else if (GetToastAutoVacuumEnabled() == 0)
sql += wxT(",\n toast.autovacuum_enabled=false");
if (!GetToastAutoVacuumVacuumThreshold().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_vacuum_threshold=") + GetToastAutoVacuumVacuumThreshold();
}
if (!GetToastAutoVacuumVacuumScaleFactor().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_vacuum_scale_factor=") + GetToastAutoVacuumVacuumScaleFactor();
}
if (!GetToastAutoVacuumVacuumCostDelay().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_vacuum_cost_delay=") + GetToastAutoVacuumVacuumCostDelay();
}
if (!GetToastAutoVacuumVacuumCostLimit().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_vacuum_cost_limit=") + GetToastAutoVacuumVacuumCostLimit();
}
if (!GetToastAutoVacuumFreezeMinAge().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_freeze_min_age=") + GetToastAutoVacuumFreezeMinAge();
}
if (!GetToastAutoVacuumFreezeMaxAge().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_freeze_max_age=") + GetToastAutoVacuumFreezeMaxAge();
}
if (!GetToastAutoVacuumFreezeTableAge().IsEmpty())
{
sql += wxT(",\n toast.autovacuum_freeze_table_age=") + GetToastAutoVacuumFreezeTableAge();
}
}
sql += wxT("\n)");
}
if (tablespace != GetDatabase()->GetDefaultTablespace())
sql += wxT("\nTABLESPACE ") + qtIdent(tablespace);
wxString isPopulated;
if (GetIsPopulated().Cmp(wxT("t")) == 0)
isPopulated = wxT("WITH DATA;");
else
isPopulated = wxT("WITH NO DATA;");
wxString sqlDefinition;
bool tmpLoopFlag = true;
sqlDefinition = GetFormattedDefinition();
// Remove semicolon from the end of the string
while(tmpLoopFlag)
{
int length = sqlDefinition.Len();
int position = sqlDefinition.Find(';', true);
if ((position != wxNOT_FOUND) && (position = (length - 1)))
sqlDefinition.Remove(position, 1);
else
tmpLoopFlag = false;
}
sql += wxT(" AS \n")
+ sqlDefinition
+ wxT("\n")
+ isPopulated
+ wxT("\n\n")
+ GetOwnerSql(7, 3, wxT("TABLE ") + GetQuotedFullIdentifier());
}
}
if (!IsMatViewFlag)
{
sql += wxT(" AS \n")
+ GetFormattedDefinition()
+ wxT("\n\n")
+ GetOwnerSql(7, 3, wxT("TABLE ") + GetQuotedFullIdentifier());
}
if (GetConnection()->BackendMinimumVersion(8, 2))
sql += GetGrant(wxT("arwdxt"), wxT("TABLE ") + GetQuotedFullIdentifier());
else
sql += GetGrant(wxT("arwdRxt"), wxT("TABLE ") + GetQuotedFullIdentifier());
// "MATERIALIZED" isn't part of the object type name, it's a property, so
// we need to generate the comment SQL manually here, instead of using
// wxString pgObject::GetCommentSql()
if (!GetComment().IsNull())
{
if (IsMatViewFlag)
{
sql += wxT("COMMENT ON MATERIALIZED VIEW ") + GetQuotedFullIdentifier()
+ wxT("\n IS ") + qtDbString(GetComment()) + wxT(";\n");
}
else
{
sql += wxT("COMMENT ON VIEW ") + GetQuotedFullIdentifier()
+ wxT("\n IS ") + qtDbString(GetComment()) + wxT(";\n");
}
}
pgCollection *columns = browser->FindCollection(columnFactory, GetId());
if (columns)
{
wxString defaults, comments;
columns->ShowTreeDetail(browser);
treeObjectIterator colIt(browser, columns);
pgColumn *column;
while ((column = (pgColumn *)colIt.GetNextObject()) != 0)
{
column->ShowTreeDetail(browser);
if (column->GetColNumber() > 0)
{
if (!column->GetDefault().IsEmpty())
{
defaults += wxT("ALTER TABLE ") + GetQuotedFullIdentifier()
+ wxT(" ALTER COLUMN ") + column->GetQuotedIdentifier()
+ wxT(" SET DEFAULT ") + column->GetDefault()
+ wxT(";\n");
}
comments += column->GetCommentSql();
}
}
if (!defaults.IsEmpty())
sql += defaults + wxT("\n");
if (!comments.IsEmpty())
sql += comments + wxT("\n");
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
AppendStuff(sql, browser, ruleFactory);
AppendStuff(sql, browser, triggerFactory);
}
return sql;
}
