wxString dlgSchema::GetSql()
{
wxString sql, name;
name = qtIdent(GetName());
if (schema)
{
// edit mode
AppendNameChange(sql);
AppendOwnerChange(sql, wxT("SCHEMA ") + name);
}
else
{
// create mode
sql = wxT("CREATE SCHEMA ") + name;
AppendIfFilled(sql, wxT("\n AUTHORIZATION "), qtIdent(cbOwner->GetValue()));
sql += wxT(";\n");
}
AppendComment(sql, wxT("SCHEMA"), 0, schema);
sql += GetGrant(wxT("UC"), wxT("SCHEMA ") + name);
if (connection->BackendMinimumVersion(9, 0) && defaultSecurityChanged)
sql += GetDefaultPrivileges(name);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("SCHEMA"), name);
return sql;
}
wxString dlgExtTable::GetSql()
{
wxString sql, name=GetName();
if (extTable)
{
// edit mode
if (name != extTable->GetName())
{
sql += wxT("ALTER TABLE ") + extTable->GetQuotedFullIdentifier()
+ wxT(" RENAME TO ") + qtIdent(name) + wxT(";\n");
}
}
if (!extTable || txtSqlBox->GetText() != oldDefinition)
{
sql += wxT("CREATE EXTERNAL TABLE ") + schema->GetQuotedPrefix() + qtIdent(name) + wxT("\n")
+ txtSqlBox->GetText()
+ wxT(";\n");
}
if (extTable)
AppendOwnerChange(sql, wxT("TABLE ") + schema->GetQuotedPrefix() + qtIdent(name));
else
AppendOwnerNew(sql, wxT("TABLE ") + schema->GetQuotedPrefix() + qtIdent(name));
sql += GetGrant(wxT("r"), wxT("TABLE ") + schema->GetQuotedPrefix() + qtIdent(name));
AppendComment(sql, wxT("TABLE"), schema, extTable);
return sql;
}
wxString dlgForeignServer::GetSql()
{
wxString sql, name;
name = txtName->GetValue();
if (foreignserver)
{
// edit mode
AppendNameChange(sql, wxT("SERVER ") + qtIdent(foreignserver->GetName()));
if (txtVersion->GetValue() != foreignserver->GetVersion())
{
sql = wxT("ALTER SERVER ") + qtIdent(name)
+ wxT("\n VERSION ") + qtDbString(txtVersion->GetValue()) + wxT(";\n");
}
wxString sqloptions = GetOptionsSql();
if (sqloptions.Length() > 0)
{
sql += wxT("ALTER SERVER ") + name
+ wxT("\n OPTIONS (") + sqloptions + wxT(");\n");
}
AppendOwnerChange(sql, wxT("SERVER ") + qtIdent(name));
}
else
{
// create mode
sql = wxT("CREATE SERVER ") + qtIdent(name);
if (!(txtType->GetValue()).IsEmpty())
sql += wxT("\n TYPE ") + qtDbString(txtType->GetValue());
if (!(txtVersion->GetValue()).IsEmpty())
sql += wxT("\n VERSION ") + qtDbString(txtVersion->GetValue());
sql += wxT("\n FOREIGN DATA WRAPPER ") + qtIdent(foreigndatawrapper->GetName());
// check for options
if (lstOptions->GetItemCount() > 0)
{
wxString options = wxEmptyString;
for (int pos = 0 ; pos < lstOptions->GetItemCount() ; pos++)
{
if (options.Length() > 0)
options += wxT(", ");
options += lstOptions->GetText(pos, 0)
+ wxT(" '") + lstOptions->GetText(pos, 1) + wxT("' ");
}
sql += wxT("\n OPTIONS (") + options + wxT(")");
}
sql += wxT(";\n");
AppendOwnerNew(sql, wxT("SERVER ") + qtIdent(name));
}
sql += GetGrant(wxT("U"), wxT("SERVER ") + qtIdent(name));
AppendComment(sql, wxT("SERVER"), 0, foreignserver);
return sql;
}
wxString dlgCollation::GetSql()
{
wxString sql;
wxString name;
if (collation)
{
// edit mode
name = schema->GetQuotedPrefix() + qtIdent(GetName());;
AppendNameChange(sql, wxT("COLLATION ") + collation->GetQuotedFullIdentifier());
AppendOwnerChange(sql, wxT("COLLATION ") + name);
AppendSchemaChange(sql, wxT("COLLATION ") + name);
}
else
{
// create mode
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
sql = wxT("CREATE COLLATION ") + name;
if (cbCollation->GetValue().IsEmpty())
{
if (txtLocale->GetValue().IsEmpty())
{
sql += wxT("(LC_COLLATE=") + qtDbString(txtLcCollate->GetValue())
+ wxT(", LC_CTYPE=") + qtDbString(txtLcCtype->GetValue())
+ wxT(")");
}
else
{
sql += wxT("(LOCALE=") + qtDbString(txtLocale->GetValue()) + wxT(")");
}
}
else
{
sql += wxT(" FROM ") + cbCollation->GetValue();
}
sql += wxT(";\n");
AppendOwnerNew(sql, wxT("COLLATION ") + schema->GetQuotedPrefix() + qtIdent(name));
}
AppendComment(sql, wxT("COLLATION ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), collation);
return sql;
}
wxString dlgLanguage::GetSql()
{
wxString sql, name;
name = cbName->GetValue();
if (language)
{
// edit mode
if (name != language->GetName())
sql += wxT("ALTER LANGUAGE ") + qtIdent(language->GetName())
+ wxT("\n RENAME TO ") + qtIdent(name) + wxT(";\n");
if (connection->BackendMinimumVersion(8, 3))
AppendOwnerChange(sql, wxT("LANGUAGE ") + qtIdent(name));
}
else
{
// create mode
if (connection->BackendMinimumVersion(8, 1) && cbName->FindString(name) >= 0)
{
sql = wxT("CREATE LANGUAGE ") + qtIdent(name) + wxT(";\n");
}
else
{
sql = wxT("CREATE ");
if (chkTrusted->GetValue())
sql += wxT("TRUSTED ");
sql += wxT("LANGUAGE ") + qtIdent(name) + wxT("\n HANDLER ") + qtIdent(cbHandler->GetValue());
if (connection->BackendMinimumVersion(9, 0))
AppendIfFilled(sql, wxT("\n INLINE "), qtIdent(cbInline->GetValue()));
AppendIfFilled(sql, wxT("\n VALIDATOR "), qtIdent(cbValidator->GetValue()));
sql += wxT(";\n");
}
if (connection->BackendMinimumVersion(8, 3))
AppendOwnerNew(sql, wxT("LANGUAGE ") + qtIdent(name));
}
sql += GetGrant(wxT("U"), wxT("LANGUAGE ") + qtIdent(name));
AppendComment(sql, wxT("LANGUAGE ") + qtIdent(name), 0, language);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("LANGUAGE"), qtIdent(name));
return sql;
}
wxString dlgTextSearchDictionary::GetSql()
{
wxString sql;
wxString objname=schema->GetQuotedPrefix() + qtIdent(GetName());
if (dict)
{
// edit mode
AppendNameChange(sql);
AppendOwnerChange(sql, wxT("TEXT SEARCH DICTIONARY ") + objname);
wxString sqloptions = GetOptionsSql();
if (sqloptions.Length() > 0)
{
sql += wxT("ALTER TEXT SEARCH DICTIONARY ") + objname
+ wxT(" (") + sqloptions + wxT(")");
}
}
else
{
// create mode
sql = wxT("CREATE TEXT SEARCH DICTIONARY ")
+ schema->GetQuotedPrefix() + GetName()
+ wxT(" (")
+ wxT("\n TEMPLATE = ") + cbTemplate->GetValue();
// check for options
for (int pos=0 ; pos < lstOptions->GetItemCount() ; pos++)
{
sql += wxT(", ") + lstOptions->GetText(pos, 0)
+ wxT("=") + lstOptions->GetText(pos, 1);
}
sql += wxT("\n);\n");
}
AppendComment(sql, wxT("TEXT SEARCH DICTIONARY ") + objname, dict);
return sql;
}
// Note: CREATE DATABASE cannot be part of a multi-statement query as of
// PG83, and never actually would have been transaction-safe prior
// to then. Therefore, when creating a new database, only the CREATE
// statement comes from GetSql(), subsequent ALTERs come from GetSql2()
wxString dlgDatabase::GetSql()
{
wxString sql, name;
name = GetName();
if (database)
{
// edit mode
AppendNameChange(sql);
AppendOwnerChange(sql, wxT("DATABASE ") + qtIdent(name));
AppendComment(sql, wxT("DATABASE"), 0, database);
if (seclabelPage && connection->BackendMinimumVersion(9, 2))
sql += seclabelPage->GetSqlForSecLabels(wxT("DATABASE"), qtIdent(name));
if (connection->BackendMinimumVersion(8, 4))
{
if (cbTablespace->GetCurrentSelection() > 0 && cbTablespace->GetOIDKey() > 0
&& cbTablespace->GetOIDKey() != database->GetTablespaceOid())
sql += wxT("ALTER DATABASE ") + qtIdent(name)
+ wxT("\n SET TABLESPACE ") + qtIdent(cbTablespace->GetValue())
+ wxT(";\n");
}
if (connection->BackendMinimumVersion(8, 1))
{
long connLimit;
if (txtConnLimit->GetValue().IsEmpty())
connLimit = -1;
else if (!txtConnLimit->GetValue().ToLong(&connLimit))
connLimit = database->GetConnectionLimit();
if (connLimit != database->GetConnectionLimit())
{
wxString strConnLimit;
strConnLimit << connLimit;
sql += wxT("ALTER DATABASE ") + qtIdent(name)
+ wxT("\n WITH CONNECTION LIMIT = ")
+ strConnLimit
+ wxT(";\n");
}
}
if (!connection->BackendMinimumVersion(8, 2))
sql += GetGrant(wxT("CT"), wxT("DATABASE ") + qtIdent(name));
else
sql += GetGrant(wxT("CTc"), wxT("DATABASE ") + qtIdent(name));
wxArrayString vars;
wxString username;
wxString varname;
wxString varvalue;
size_t index;
int pos;
// copy database->GetVariables() into vars
for (index = 0 ; index < database->GetVariables().GetCount() ; index++)
vars.Add(database->GetVariables().Item(index));
// check for changed or added vars
for (pos = 0 ; pos < lstVariables->GetItemCount() ; pos++)
{
wxString newUsr = lstVariables->GetText(pos);
wxString newVar = lstVariables->GetText(pos, 1);
wxString newVal = lstVariables->GetText(pos, 2);
wxString oldVal;
for (index = 0 ; index < vars.GetCount() ; index += 3)
{
username = vars.Item(index);
varname = vars.Item(index + 1);
varvalue = vars.Item(index + 2);
if (newUsr == username && newVar == varname)
{
oldVal = varvalue;
vars.RemoveAt(index);
vars.RemoveAt(index);
vars.RemoveAt(index);
break;
}
}
if (oldVal != newVal)
{
if (newUsr.Length() == 0)
sql += wxT("ALTER DATABASE ") + qtIdent(name);
else
sql += wxT("ALTER ROLE ") + newUsr + wxT(" IN DATABASE ") + qtIdent(name);
if (newVar != wxT("search_path") && newVar != wxT("temp_tablespaces"))
{
sql += wxT("\n SET ") + newVar + wxT(" = '") + newVal + wxT("';\n");
}
else
{
sql += wxT("\n SET ") + newVar + wxT(" = ") + newVal + wxT(";\n");
}
}
}
// check for removed vars
for (pos = 0 ; pos < (int)vars.GetCount() ; pos += 3)
{
username = vars.Item(index);
varname = vars.Item(index + 1);
varvalue = vars.Item(index + 2);
if (username.Length() == 0)
{
sql += wxT("ALTER DATABASE ") + qtIdent(name)
+ wxT("\n RESET ") + varname
+ wxT(";\n");
}
else
{
sql += wxT("ALTER ROLE ") + username + wxT(" IN DATABASE ") + qtIdent(name)
+ wxT("\n RESET ") + varname + wxT(";\n");
}
}
if (defaultSecurityChanged)
sql += wxT("\n") + GetDefaultPrivileges();
}
else
{
// create mode
sql = wxT("CREATE DATABASE ") + qtIdent(name)
+ wxT("\n WITH ENCODING=") + qtDbString(cbEncoding->GetValue());
AppendIfFilled(sql, wxT("\n OWNER="), qtIdent(cbOwner->GetValue()));
AppendIfFilled(sql, wxT("\n TEMPLATE="), qtIdent(cbTemplate->GetValue()));
AppendIfFilled(sql, wxT("\n LOCATION="), txtPath->GetValue());
if (connection->BackendMinimumVersion(8, 4))
{
wxString strCollate = cbCollate->GetValue();
if (!strCollate.IsEmpty())
AppendIfFilled(sql, wxT("\n LC_COLLATE="), qtDbString(strCollate));
wxString strCType = cbCType->GetValue();
if (!strCType.IsEmpty())
AppendIfFilled(sql, wxT("\n LC_CTYPE="), qtDbString(strCType));
}
if (connection->BackendMinimumVersion(8, 1))
{
AppendIfFilled(sql, wxT("\n CONNECTION LIMIT="), (txtConnLimit->GetValue() == wxT("-") ? wxT("-1") : txtConnLimit->GetValue()));
}
if (cbTablespace->GetCurrentSelection() > 0 && cbTablespace->GetOIDKey() > 0)
sql += wxT("\n TABLESPACE=") + qtIdent(cbTablespace->GetValue());
sql += wxT(";\n");
}
return sql.Trim(false);
}
wxString dlgTablespace::GetSql()
{
wxString sql;
wxString name=GetName();
if (tablespace)
{
// Edit Mode
AppendNameChange(sql);
AppendOwnerChange(sql, wxT("TABLESPACE ") + qtIdent(name));
sql += GetGrant(wxT("C"), wxT("TABLESPACE ") + qtIdent(name));
AppendComment(sql, wxT("TABLESPACE"), 0, tablespace);
wxArrayString vars;
size_t index;
for (index = 0 ; index < tablespace->GetVariables().GetCount() ; index++)
vars.Add(tablespace->GetVariables().Item(index));
int cnt=lstVariables->GetItemCount();
int pos;
// check for changed or added vars
for (pos=0 ; pos < cnt ; pos++)
{
wxString newVar=lstVariables->GetText(pos);
wxString newVal=lstVariables->GetText(pos, 1);
wxString oldVal;
for (index=0 ; index < vars.GetCount() ; index++)
{
wxString var=vars.Item(index);
if (var.BeforeFirst('=').IsSameAs(newVar, false))
{
oldVal = var.Mid(newVar.Length()+1);
vars.RemoveAt(index);
break;
}
}
if (oldVal != newVal)
{
sql += wxT("ALTER TABLESPACE ") + qtIdent(name)
+ wxT(" SET (") + newVar
+ wxT("=") + newVal
+ wxT(");\n");
}
}
// check for removed vars
for (pos=0 ; pos < (int)vars.GetCount() ; pos++)
{
sql += wxT("ALTER TABLESPACE ") + qtIdent(name)
+ wxT(" RESET (") + vars.Item(pos).BeforeFirst('=')
+ wxT(");\n");
}
}
else
{
// Create Mode
sql = wxT("CREATE TABLESPACE ") + qtIdent(name);
AppendIfFilled(sql, wxT(" OWNER "), qtIdent(cbOwner->GetValue()));
sql += wxT(" LOCATION ") + qtDbString(txtLocation->GetValue())
+ wxT(";\n");
}
return sql;
}
wxString dlgView::GetSql()
{
wxString sql;
wxString name;
wxString withoptions = wxEmptyString;
bool editQuery = false;
if (view)
{
// edit mode
name = GetName();
if (name != view->GetName())
{
if (connection->BackendMinimumVersion(8, 3))
{
if (connection->BackendMinimumVersion(9, 3))
{
if (view->GetMaterializedView())
{
AppendNameChange(sql, wxT("MATERIALIZED VIEW ") + view->GetQuotedFullIdentifier());
editQuery = true;
}
else
AppendNameChange(sql, wxT("VIEW ") + view->GetQuotedFullIdentifier());
}
else
AppendNameChange(sql, wxT("VIEW ") + view->GetQuotedFullIdentifier());
}
else
AppendNameChange(sql, wxT("TABLE ") + view->GetQuotedFullIdentifier());
}
if (connection->BackendMinimumVersion(8, 4) && cbSchema->GetName() != view->GetSchema()->GetName())
{
if (connection->BackendMinimumVersion(9, 3))
{
if (view->GetMaterializedView())
{
AppendSchemaChange(sql, wxT("MATERIALIZED VIEW " + qtIdent(view->GetSchema()->GetName()) + wxT(".") + qtIdent(name)));
editQuery = true;
}
else
AppendSchemaChange(sql, wxT("VIEW " + qtIdent(view->GetSchema()->GetName()) + wxT(".") + qtIdent(name)));
}
else
AppendSchemaChange(sql, wxT("VIEW " + qtIdent(view->GetSchema()->GetName()) + wxT(".") + qtIdent(name)));
}
else
AppendSchemaChange(sql, wxT("TABLE " + qtIdent(view->GetSchema()->GetName()) + wxT(".") + qtIdent(name)));
}
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
if (!view || txtSqlBox->GetText().Trim(true).Trim(false) != oldDefinition.Trim(true).Trim(false))
{
if (editQuery)
{
// Delete the materialized view query
sql += wxT("DROP MATERIALIZED VIEW ") + name + wxT(";");
}
// Check if user creates the materialized view
if (!chkMaterializedView->GetValue())
{
sql += wxT("CREATE OR REPLACE VIEW ") + name;
if (connection->BackendMinimumVersion(9, 2) && chkSecurityBarrier->GetValue())
withoptions += wxT("security_barrier=true");
if (connection->BackendMinimumVersion(9, 4) && cbCheckOption->GetSelection() > 0)
{
if (withoptions.Length() > 0)
withoptions += wxT(", ");
withoptions += wxT("check_option=") + cbCheckOption->GetValue().Lower();
}
if (withoptions.Length() > 0)
sql += wxT(" WITH (") + withoptions + wxT(")");
sql += wxT(" AS\n")
+ txtSqlBox->GetText().Trim(true).Trim(false)
+ wxT(";\n");
}
else if (connection->BackendMinimumVersion(9, 3) && chkMaterializedView->GetValue())
{
sql += wxT("CREATE MATERIALIZED VIEW ") + name;
// Add the parameter of tablespace and storage parameter to create the materilized view
if (txtFillFactor->GetValue().Trim().Length() > 0 || chkVacEnabled->GetValue() == true || chkToastVacEnabled->GetValue() == true)
{
bool fillFactorFlag, toastTableFlag;
fillFactorFlag = false;
toastTableFlag = false;
sql += wxT("\nWITH (");
if (txtFillFactor->GetValue().Trim().Length() > 0)
{
sql += wxT("\n FILLFACTOR = ") + txtFillFactor->GetValue();
fillFactorFlag = true;
}
bool valChanged = false;
wxString newVal;
wxString resetStr;
if (connection->BackendMinimumVersion(9, 3) && chkCustomVac->GetValue())
{
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_enabled"), BoolToStr(chkVacEnabled->GetValue()));
if (!fillFactorFlag)
{
int position = sql.Find(',', true);
if (position != wxNOT_FOUND)
sql.Remove(position, 1);
toastTableFlag = true;
}
newVal = AppendNum(valChanged, txtBaseVac, tableVacBaseThr);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_vacuum_threshold"), newVal);
}
newVal = AppendNum(valChanged, txtBaseAn, tableAnlBaseThr);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_analyze_threshold"), newVal);
}
newVal = AppendNum(valChanged, txtFactorVac, tableVacFactor);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_vacuum_scale_factor"), newVal);
}
newVal = AppendNum(valChanged, txtFactorAn, tableAnlFactor);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_analyze_scale_factor"), newVal);
}
newVal = AppendNum(valChanged, txtVacDelay, tableCostDelay);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_vacuum_cost_delay"), newVal);
}
newVal = AppendNum(valChanged, txtVacLimit, tableCostLimit);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_vacuum_cost_limit"), newVal);
}
newVal = AppendNum(valChanged, txtFreezeMinAge, tableFreezeMinAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_freeze_min_age"), newVal);
}
newVal = AppendNum(valChanged, txtFreezeMaxAge, tableFreezeMaxAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_freeze_max_age"), newVal);
}
newVal = AppendNum(valChanged, txtFreezeTableAge, tableFreezeTableAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("autovacuum_freeze_table_age"), newVal);
}
}
if (connection->BackendMinimumVersion(9, 3) && chkCustomToastVac->GetValue())
{
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_enabled"), BoolToStr(chkToastVacEnabled->GetValue()));
if (!fillFactorFlag && !toastTableFlag)
{
int position = sql.Find(',', true);
if (position != wxNOT_FOUND)
sql.Remove(position, 1);
}
newVal = AppendNum(valChanged, txtBaseToastVac, toastTableVacBaseThr);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_vacuum_threshold"), newVal);
}
newVal = AppendNum(valChanged, txtFactorToastVac, toastTableVacFactor);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_vacuum_scale_factor"), newVal);
}
newVal = AppendNum(valChanged, txtToastVacDelay, toastTableCostDelay);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_vacuum_cost_delay"), newVal);
}
newVal = AppendNum(valChanged, txtToastVacLimit, toastTableCostLimit);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_vacuum_cost_limit"), newVal);
}
newVal = AppendNum(valChanged, txtToastFreezeMinAge, toastTableFreezeMinAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_freeze_min_age"), newVal);
}
newVal = AppendNum(valChanged, txtToastFreezeMaxAge, toastTableFreezeMaxAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_freeze_max_age"), newVal);
}
newVal = AppendNum(valChanged, txtToastFreezeTableAge, toastTableFreezeTableAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(sql, resetStr, wxT("toast.autovacuum_freeze_table_age"), newVal);
}
}
sql += wxT("\n)\n");
}
if (cboTablespace->GetCurrentSelection() > 0 && cboTablespace->GetOIDKey() > 0)
sql += wxT("\nTABLESPACE ") + qtIdent(cboTablespace->GetValue());
wxString sqlDefinition;
bool tmpLoopFlag = true;
sqlDefinition = txtSqlBox->GetText().Trim(true).Trim(false);
// 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;
if (chkMatViewWithData->GetValue())
sql += wxT("\n WITH DATA;\n");
else
sql += wxT("\n WITH NO DATA;\n");
}
}
else if (view)
{
if (!chkMaterializedView->GetValue())
{
if (connection->BackendMinimumVersion(9, 2))
{
if (chkSecurityBarrier->GetValue() && view->GetSecurityBarrier() != wxT("true"))
sql += wxT("ALTER VIEW ") + name + wxT("\n SET (security_barrier=true);\n");
else if (!chkSecurityBarrier->GetValue() && view->GetSecurityBarrier() == wxT("true"))
sql += wxT("ALTER VIEW ") + name + wxT("\n SET (security_barrier=false);\n");
}
if (connection->BackendMinimumVersion(9, 4)
&& cbCheckOption->GetValue().Lower().Cmp(view->GetCheckOption()) != 0)
{
if (cbCheckOption->GetValue().Cmp(wxT("No")) == 0)
sql += wxT("ALTER VIEW ") + name + wxT(" RESET (check_option);\n");
else
sql += wxT("ALTER VIEW ") + name + wxT("\n SET (check_option=") + cbCheckOption->GetValue().Lower() + wxT(");\n");
}
if (withoptions.Length() > 0)
sql += wxT(" WITH (") + withoptions + wxT(")");
}
else if (connection->BackendMinimumVersion(9, 3) && chkMaterializedView->GetValue())
{
if (txtFillFactor->GetValue() != view->GetFillFactor())
{
// If fill factor value get changed then set the new value
if (txtFillFactor->GetValue().Trim().Length() > 0)
{
sql += wxT("ALTER MATERIALIZED VIEW ") + name
+ wxT("\n SET (FILLFACTOR=")
+ txtFillFactor->GetValue() + wxT(");\n");
}
else
{
// If fill factor value get changed and value is not blank then do the reset
sql += wxT("ALTER MATERIALIZED VIEW ") + name
+ wxT(" RESET(\n")
wxT(" FILLFACTOR\n")
wxT(");\n");
}
}
bool isPopulatedFlag = false;
if (view->GetIsPopulated().Cmp(wxT("t")) == 0)
isPopulatedFlag = true;
if (chkMatViewWithData->GetValue() != isPopulatedFlag)
{
// If checkbox is checked then set WITH NO DATA
if (isPopulatedFlag)
{
sql += wxT("REFRESH MATERIALIZED VIEW ") + name
+ wxT(" WITH NO DATA;\n");
}
else
{
sql += wxT("REFRESH MATERIALIZED VIEW ") + name
+ wxT(" WITH DATA;\n");
}
}
// Altered the storage parameters for the materialized view?
if (!chkCustomVac->GetValue())
{
if (hasVacuum)
{
sql += wxT("ALTER MATERIALIZED VIEW ") + name
+ wxT(" RESET(\n")
wxT(" autovacuum_enabled,\n")
wxT(" autovacuum_vacuum_threshold,\n")
wxT(" autovacuum_analyze_threshold,\n")
wxT(" autovacuum_vacuum_scale_factor,\n")
wxT(" autovacuum_analyze_scale_factor,\n")
wxT(" autovacuum_vacuum_cost_delay,\n")
wxT(" autovacuum_vacuum_cost_limit,\n")
wxT(" autovacuum_freeze_min_age,\n")
wxT(" autovacuum_freeze_max_age,\n")
wxT(" autovacuum_freeze_table_age\n")
wxT(");\n");
}
}
else
{
wxString vacStr;
bool changed = (chkVacEnabled->GetValue() != tableVacEnabled);
bool valChanged = false;
wxString newVal;
wxString setStr;
wxString resetStr;
if (changed)
{
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_enabled"), BoolToStr(chkVacEnabled->GetValue()));
}
newVal = AppendNum(valChanged, txtBaseVac, tableVacBaseThr);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_vacuum_threshold"), newVal);
}
newVal = AppendNum(valChanged, txtBaseAn, tableAnlBaseThr);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_analyze_threshold"), newVal);
}
newVal = AppendNum(valChanged, txtFactorVac, tableVacFactor);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_vacuum_scale_factor"), newVal);
}
newVal = AppendNum(valChanged, txtFactorAn, tableAnlFactor);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_analyze_scale_factor"), newVal);
}
newVal = AppendNum(valChanged, txtVacDelay, tableCostDelay);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_vacuum_cost_delay"), newVal);
}
newVal = AppendNum(valChanged, txtVacLimit, tableCostLimit);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_vacuum_cost_limit"), newVal);
}
newVal = AppendNum(valChanged, txtFreezeMinAge, tableFreezeMinAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_freeze_min_age"), newVal);
}
newVal = AppendNum(valChanged, txtFreezeMaxAge, tableFreezeMaxAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_freeze_max_age"), newVal);
}
newVal = AppendNum(valChanged, txtFreezeTableAge, tableFreezeTableAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("autovacuum_freeze_table_age"), newVal);
}
if (!setStr.IsEmpty())
{
vacStr = wxT("ALTER MATERIALIZED VIEW ") + name + setStr + wxT("\n);\n");;
changed = true;
}
if (!resetStr.IsEmpty())
{
vacStr += wxT("ALTER MATERIALIZED VIEW ") + name + resetStr + wxT("\n);\n");;
changed = true;
}
if (changed)
sql += vacStr;
}
if (!chkCustomToastVac->GetValue())
{
if (toastTableHasVacuum)
{
sql += wxT("ALTER MATERIALIZED VIEW ") + name
+ wxT(" RESET(\n")
wxT(" toast.autovacuum_enabled,\n")
wxT(" toast.autovacuum_vacuum_threshold,\n")
wxT(" toast.autovacuum_analyze_threshold,\n")
wxT(" toast.autovacuum_vacuum_scale_factor,\n")
wxT(" toast.autovacuum_analyze_scale_factor,\n")
wxT(" toast.autovacuum_vacuum_cost_delay,\n")
wxT(" toast.autovacuum_vacuum_cost_limit,\n")
wxT(" toast.autovacuum_freeze_min_age,\n")
wxT(" toast.autovacuum_freeze_max_age,\n")
wxT(" toast.autovacuum_freeze_table_age\n")
wxT(");\n");
}
}
else
{
wxString vacStr;
bool changed = (chkToastVacEnabled->GetValue() != toastTableVacEnabled);
bool valChanged = false;
wxString newVal;
wxString setStr;
wxString resetStr;
if (changed)
{
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_enabled"), BoolToStr(chkToastVacEnabled->GetValue()));
}
newVal = AppendNum(valChanged, txtBaseToastVac, toastTableVacBaseThr);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_vacuum_threshold"), newVal);
}
newVal = AppendNum(valChanged, txtFactorToastVac, toastTableVacFactor);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_vacuum_scale_factor"), newVal);
}
newVal = AppendNum(valChanged, txtToastVacDelay, toastTableCostDelay);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_vacuum_cost_delay"), newVal);
}
newVal = AppendNum(valChanged, txtToastVacLimit, toastTableCostLimit);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_vacuum_cost_limit"), newVal);
}
newVal = AppendNum(valChanged, txtToastFreezeMinAge, toastTableFreezeMinAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_freeze_min_age"), newVal);
}
newVal = AppendNum(valChanged, txtToastFreezeMaxAge, toastTableFreezeMaxAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_freeze_max_age"), newVal);
}
newVal = AppendNum(valChanged, txtToastFreezeTableAge, toastTableFreezeTableAge);
if (valChanged)
{
valChanged = false;
FillAutoVacuumParameters(setStr, resetStr, wxT("toast.autovacuum_freeze_table_age"), newVal);
}
if (!setStr.IsEmpty())
{
vacStr = wxT("ALTER MATERIALIZED VIEW ") + name + setStr + wxT("\n);\n");
changed = true;
}
if (!resetStr.IsEmpty())
{
vacStr += wxT("ALTER MATERIALIZED VIEW ") + name + resetStr + wxT("\n);\n");
changed = true;
}
if (changed)
sql += vacStr;
}
if (cboTablespace->GetOIDKey() != view->GetTablespaceOid())
{
sql += wxT("ALTER MATERIALIZED VIEW ") + name
+ wxT("\n SET TABLESPACE ") + qtIdent(cboTablespace->GetValue())
+ wxT(";\n");
}
}
}
if (view)
AppendOwnerChange(sql, wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
else
AppendOwnerNew(sql, wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
sql += GetGrant(wxT("arwdRxt"), wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
if (connection->BackendMinimumVersion(9, 3) && chkMaterializedView->GetValue())
AppendComment(sql, wxT("MATERIALIZED VIEW ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), view);
else
AppendComment(sql, wxT("VIEW ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), view);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("VIEW"), qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
return sql;
}
wxString dlgSequence::GetSql()
{
wxString sql, name;
if (sequence)
{
// edit mode
name = GetName();
if (connection->BackendMinimumVersion(8, 3))
AppendNameChange(sql, wxT("SEQUENCE ") + sequence->GetQuotedFullIdentifier());
else
AppendNameChange(sql, wxT("TABLE ") + sequence->GetQuotedFullIdentifier());
if (connection->BackendMinimumVersion(8, 4))
AppendOwnerChange(sql, wxT("SEQUENCE ") + schema->GetQuotedPrefix() + qtIdent(name));
else
AppendOwnerChange(sql, wxT("TABLE ") + schema->GetQuotedPrefix() + qtIdent(name));
// This is where things get hairy. Per some thought by Horvath Gabor,
// we need to adjust the min/max sequence values, and the the current
// value per the rules:
//
// 1 Any ALTER SEQUENCE MIN/MAXVALUE statements that widen the range
// 2 SETVAL
// 3 Any ALTER SEQUENCE MIN/MAXVALUE statements that narrow the range.
//
// We'll change any other options at the end.
wxString tmp;
// MIN/MAX changes that widen the range.
if (connection->BackendMinimumVersion(7, 4))
{
tmp = wxEmptyString;
if (txtMin->GetValue().IsEmpty())
tmp += wxT("\n NO MINVALUE");
else if (StrToLongLong(txtMin->GetValue()) < sequence->GetMinValue())
tmp += wxT("\n MINVALUE ") + txtMin->GetValue();
if (txtMax->GetValue().IsEmpty())
tmp += wxT("\n NO MAXVALUE");
else if (StrToLongLong(txtMax->GetValue()) > sequence->GetMaxValue())
tmp += wxT("\n MAXVALUE ") + txtMax->GetValue();
if (!tmp.IsEmpty())
{
sql += wxT("ALTER SEQUENCE ") + schema->GetQuotedPrefix() + qtIdent(name)
+ tmp + wxT(";\n");
}
}
// The new sequence value
if (txtStart->GetValue() != sequence->GetLastValue().ToString())
sql += wxT("SELECT setval('") + qtIdent(schema->GetName()) + wxT(".") + qtIdent(name)
+ wxT("', ") + txtStart->GetValue()
+ wxT(", true);\n");
// Min/Max changes that narrow the ranges, as well as other changes.
if (connection->BackendMinimumVersion(7, 4))
{
tmp = wxEmptyString;
if (txtIncrement->GetValue() != sequence->GetIncrement().ToString())
tmp += wxT("\n INCREMENT ") + txtIncrement->GetValue();
if ((!txtMin->GetValue().IsEmpty()) && StrToLongLong(txtMin->GetValue()) > sequence->GetMinValue())
tmp += wxT("\n MINVALUE ") + txtMin->GetValue();
if ((!txtMax->GetValue().IsEmpty()) && StrToLongLong(txtMax->GetValue()) < sequence->GetMaxValue())
tmp += wxT("\n MAXVALUE ") + txtMax->GetValue();
if (txtCache->GetValue() != sequence->GetCacheValue().ToString())
tmp += wxT("\n CACHE ") + txtCache->GetValue();
if (chkCycled->GetValue() != sequence->GetCycled())
{
if (chkCycled->GetValue())
tmp += wxT("\n CYCLE");
else
tmp += wxT("\n NO CYCLE");
}
if (!tmp.IsEmpty())
{
sql += wxT("ALTER SEQUENCE ") + schema->GetQuotedPrefix() + qtIdent(name)
+ tmp + wxT(";\n");
}
if (connection->BackendMinimumVersion(8, 1))
AppendSchemaChange(sql, wxT("SEQUENCE ") + schema->GetQuotedPrefix() + qtIdent(name));
}
}
else
{
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
// create mode
sql = wxT("CREATE SEQUENCE ") + name;
if (chkCycled->GetValue())
sql += wxT(" CYCLE");
AppendIfFilled(sql, wxT("\n INCREMENT "), txtIncrement->GetValue());
AppendIfFilled(sql, wxT("\n START "), txtStart->GetValue());
AppendIfFilled(sql, wxT("\n MINVALUE "), txtMin->GetValue());
AppendIfFilled(sql, wxT("\n MAXVALUE "), txtMax->GetValue());
AppendIfFilled(sql, wxT("\n CACHE "), txtCache->GetValue());
sql += wxT(";\n");
if (cbOwner->GetGuessedSelection() > 0)
{
if (connection->BackendMinimumVersion(8, 4))
{
AppendOwnerChange(sql, wxT("SEQUENCE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
}
else
{
AppendOwnerChange(sql, wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
}
}
}
if (!connection->BackendMinimumVersion(8, 2))
sql += GetGrant(wxT("arwdRxt"), wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
else
sql += GetGrant(wxT("rwU"), wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
AppendComment(sql, wxT("SEQUENCE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), sequence);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("SEQUENCE"), qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
return sql;
}
wxString dlgView::GetSql()
{
wxString sql;
wxString name;
if (view)
{
// edit mode
name = GetName();
if (name != view->GetName())
{
if (connection->BackendMinimumVersion(8, 3))
AppendNameChange(sql, wxT("VIEW ") + view->GetQuotedFullIdentifier());
else
AppendNameChange(sql, wxT("TABLE ") + view->GetQuotedFullIdentifier());
}
if (connection->BackendMinimumVersion(8, 4) && cbSchema->GetName() != view->GetSchema()->GetName())
AppendSchemaChange(sql, wxT("VIEW " + qtIdent(view->GetSchema()->GetName()) + wxT(".") + qtIdent(name)));
else
AppendSchemaChange(sql, wxT("TABLE " + qtIdent(view->GetSchema()->GetName()) + wxT(".") + qtIdent(name)));
}
if (!view || txtSqlBox->GetText().Trim(true).Trim(false) != oldDefinition.Trim(true).Trim(false))
{
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
sql += wxT("CREATE OR REPLACE VIEW ") + name;
if (connection->BackendMinimumVersion(9, 2) && chkSecurityBarrier->GetValue())
sql += wxT(" WITH (security_barrier=true)");
sql += wxT(" AS\n")
+ txtSqlBox->GetText().Trim(true).Trim(false)
+ wxT(";\n");
}
else if (view)
{
if (connection->BackendMinimumVersion(9, 2))
{
if (chkSecurityBarrier->GetValue() && view->GetSecurityBarrier() != wxT("true"))
sql += wxT("ALTER VIEW ") + name + wxT("\n SET (security_barrier=true);\n");
else if (!chkSecurityBarrier->GetValue() && view->GetSecurityBarrier() == wxT("true"))
sql += wxT("ALTER VIEW ") + name + wxT("\n SET (security_barrier=false);\n");
}
}
if (view)
AppendOwnerChange(sql, wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
else
AppendOwnerNew(sql, wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
sql += GetGrant(wxT("arwdRxt"), wxT("TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
AppendComment(sql, wxT("VIEW ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), view);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("VIEW"), qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
return sql;
}
wxString dlgOperator::GetSql()
{
wxString sql, name;
if (oper)
{
// edit mode
name = oper->GetQuotedFullIdentifier()
+ wxT("(") + oper->GetOperands() + wxT(")");
AppendOwnerChange(sql, wxT("OPERATOR ") + name);
AppendSchemaChange(sql, wxT("OPERATOR ") + name);
name = qtIdent(cbSchema->GetValue()) + wxT(".") + GetName()
+ wxT("(") + oper->GetOperands() + wxT(")");
}
else
{
// create mode
name = qtIdent(cbSchema->GetValue()) + wxT(".") + GetName() + wxT("(");
if (cbLeftType->GetGuessedSelection() > 0)
name += GetQuotedTypename(cbLeftType->GetGuessedSelection());
else
name += wxT("NONE");
name += wxT(", ");
if (cbRightType->GetGuessedSelection() > 0)
name += GetQuotedTypename(cbRightType->GetGuessedSelection());
else
name += wxT("NONE");
name += wxT(")");
sql = wxT("CREATE OPERATOR ") + qtIdent(cbSchema->GetValue()) + wxT(".") + GetName()
+ wxT("(\n PROCEDURE=") + procedures.Item(cbProcedure->GetGuessedSelection());
AppendIfFilled(sql, wxT(",\n LEFTARG="), GetQuotedTypename(cbLeftType->GetGuessedSelection()));
AppendIfFilled(sql, wxT(",\n RIGHTARG="), GetQuotedTypename(cbRightType->GetGuessedSelection()));
AppendIfFilled(sql, wxT(",\n COMMUTATOR="), cbCommutator->GetValue().Trim());
AppendIfFilled(sql, wxT(",\n NEGATOR="), cbNegator->GetValue().Trim());
if (cbLeftType->GetGuessedSelection() > 0 && cbRightType->GetGuessedSelection() > 0)
{
if (cbRestrict->GetCurrentSelection() > 0)
sql += wxT(",\n RESTRICT=") + procedures.Item(cbRestrict->GetCurrentSelection() - 1);
if (cbJoin->GetCurrentSelection() > 0)
sql += wxT(",\n JOIN=") + procedures.Item(cbJoin->GetCurrentSelection() - 1);
if (!connection->BackendMinimumVersion(8, 3))
{
AppendFilledOperator(sql, wxT(",\n SORT1="), cbLeftSort);
AppendFilledOperator(sql, wxT(",\n SORT2="), cbRightSort);
AppendFilledOperator(sql, wxT(",\n LTCMP="), cbLess);
AppendFilledOperator(sql, wxT(",\n GTCMP="), cbGreater);
}
if (chkCanMerge->GetValue() || chkCanHash->GetValue())
{
sql += wxT(",\n ");
if (chkCanHash->GetValue())
{
if (chkCanMerge->GetValue())
sql += wxT("HASHES, MERGES");
else
sql += wxT("HASHES");
}
else if (chkCanMerge->GetValue())
sql += wxT("MERGES");
}
}
sql += wxT(");\n");
AppendOwnerChange(sql, wxT("OPERATOR ") + name);
}
AppendComment(sql, wxT("OPERATOR ") + name, oper);
return sql;
}
wxString dlgFunction::GetSql()
{
wxString sql;
wxString name;
wxString objType;
if (isProcedure)
objType = wxT("PROCEDURE ");
else
objType = wxT("FUNCTION ");
bool isC = cbLanguage->GetValue().IsSameAs(wxT("C"), false);
bool didChange = !function
|| cbLanguage->GetValue() != function->GetLanguage()
|| cbVolatility->GetValue() != function->GetVolatility()
|| chkSecureDefiner->GetValue() != function->GetSecureDefiner()
|| chkStrict->GetValue() != function->GetIsStrict()
|| GetArgs() != function->GetArgListWithNames()
|| chkLeakProof->GetValue() != function->GetIsLeakProof()
|| (isC && (txtObjectFile->GetValue() != function->GetBin() || txtLinkSymbol->GetValue() != function->GetSource()))
|| (!isC && txtSqlBox->GetText() != function->GetSource());
if (connection->BackendMinimumVersion(8, 3))
{
didChange = (didChange ||
txtCost->GetValue() != NumToStr(function->GetCost()) ||
(chkSetof->GetValue() && txtRows->GetValue() != NumToStr(function->GetRows())));
}
if (function)
{
name = GetName();
// edit mode
if (name != function->GetName())
{
if (!isProcedure)
AppendNameChange(sql, wxT("FUNCTION ") + function->GetQuotedFullIdentifier()
+ wxT("(") + function->GetArgSigList() + wxT(")"));
else
AppendNameChange(sql, wxT("FUNCTION ") + function->GetQuotedFullIdentifier());
}
if (didChange)
sql += wxT("CREATE OR REPLACE ") + objType;
}
else
{
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
// create mode
sql = wxT("CREATE " ) + objType;
}
if (didChange)
{
if (isProcedure && GetArgs().IsEmpty())
{
sql += schema->GetQuotedPrefix() + qtIdent(GetName());
}
else
{
sql += schema->GetQuotedPrefix() + qtIdent(GetName())
+ wxT("(") + GetArgs() + wxT(")");
}
if (!isProcedure)
{
sql += wxT(" RETURNS ");
if (chkSetof->GetValue() && !cbReturntype->GetValue().StartsWith(wxT("TABLE")))
sql += wxT("SETOF ");
sql += cbReturntype->GetValue();
}
sql += wxT(" AS\n");
if (isProcedure)
{
sql += txtSqlBox->GetText();
sql = sql.Trim(true);
if (!sql.EndsWith(wxT(";")))
sql += wxT(";\n");
else
sql += wxT("\n");
}
else
{
if (cbLanguage->GetValue().IsSameAs(wxT("C"), false))
{
sql += qtDbString(txtObjectFile->GetValue());
if (!txtLinkSymbol->GetValue().IsEmpty())
sql += wxT(", ") + qtDbString(txtLinkSymbol->GetValue());
}
else
{
if (connection->BackendMinimumVersion(7, 5))
sql += qtDbStringDollar(txtSqlBox->GetText());
else
sql += qtDbString(txtSqlBox->GetText());
}
sql += wxT("\nLANGUAGE ") + cbLanguage->GetValue();
if (chkWindow->GetValue())
sql += wxT(" WINDOW ");
else
sql += wxT(" ");
sql += cbVolatility->GetValue();
if (connection->BackendMinimumVersion(9, 2))
{
if (!chkLeakProof->GetValue())
sql += wxT(" NOT");
sql += wxT(" LEAKPROOF");
}
if (chkStrict->GetValue())
sql += wxT(" STRICT");
if (chkSecureDefiner->GetValue())
sql += wxT(" SECURITY DEFINER");
// PostgreSQL 8.3+ cost/row estimations
if (connection->BackendMinimumVersion(8, 3))
{
if (txtCost->GetValue().Length() > 0)
sql += wxT("\nCOST ") + txtCost->GetValue();
if (chkSetof->GetValue() && txtRows->GetValue().Length() > 0)
sql += wxT("\nROWS ") + txtRows->GetValue();
}
sql += wxT(";\n");
}
}
name = schema->GetQuotedPrefix() + qtIdent(name)
+ wxT("(") + GetArgs(false, true) + wxT(")");
if (function)
{
AppendOwnerChange(sql, wxT("FUNCTION ") + name);
AppendSchemaChange(sql, wxT("FUNCTION ") + name);
}
else
{
if (cbOwner->GetCurrentSelection() > 0)
AppendOwnerNew(sql, wxT("FUNCTION ") + name);
}
if (isProcedure)
sql += GetGrant(wxT("X"), wxT("PROCEDURE ") + name);
else
{
wxArrayString vars;
size_t index;
if (function)
{
for (index = 0 ; index < function->GetConfigList().GetCount() ; index++)
vars.Add(function->GetConfigList().Item(index));
}
int cnt = lstVariables->GetItemCount();
int pos;
// check for changed or added vars
for (pos = 0 ; pos < cnt ; pos++)
{
wxString newVar = lstVariables->GetText(pos);
wxString newVal = lstVariables->GetText(pos, 1);
wxString oldVal;
for (index = 0 ; index < vars.GetCount() ; index++)
{
wxString var = vars.Item(index);
if (var.BeforeFirst('=').IsSameAs(newVar, false))
{
oldVal = var.Mid(newVar.Length() + 1);
vars.RemoveAt(index);
break;
}
}
// Reset the vars if they've changed, or the function definition has
// changed, which will remove them all :-(
if ((oldVal != newVal) || didChange)
{
if (newVar != wxT("search_path") && newVar != wxT("temp_tablespaces"))
sql += wxT("ALTER FUNCTION ") + name
+ wxT("\n SET ") + newVar
+ wxT("='") + newVal
+ wxT("';\n");
else
sql += wxT("ALTER FUNCTION ") + name
+ wxT("\n SET ") + newVar
+ wxT("=") + newVal
+ wxT(";\n");
}
}
// check for removed vars
for (pos = 0 ; pos < (int)vars.GetCount() ; pos++)
{
sql += wxT("ALTER FUNCTION ") + name
+ wxT("\n RESET ") + vars.Item(pos).BeforeFirst('=')
+ wxT(";\n");
}
sql += GetGrant(wxT("X"), wxT("FUNCTION ") + name);
}
if (isProcedure)
AppendComment(sql, wxT("PROCEDURE ") + name, function);
else
{
AppendComment(sql, wxT("FUNCTION ") + name, function);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("FUNCTION"), name);
}
return sql;
}
wxString dlgDomain::GetSql()
{
wxString sql, name;
int pos;
wxString definition;
int index = -1;
wxArrayString tmpDef = previousConstraints;
wxString tmpsql = wxEmptyString;
if (domain)
{
// edit mode
name = GetName();
if (txtName->GetValue() != domain->GetName())
{
if (connection->BackendMinimumVersion(9, 2))
AppendNameChange(sql, wxT("DOMAIN ") + domain->GetQuotedFullIdentifier());
else
AppendNameChange(sql, wxT("TYPE ") + domain->GetQuotedFullIdentifier());
}
if (chkNotNull->GetValue() != domain->GetNotNull())
{
sql += wxT("ALTER DOMAIN ") + domain->GetQuotedFullIdentifier();
if (chkNotNull->GetValue())
sql += wxT("\n SET NOT NULL;\n");
else
sql += wxT("\n DROP NOT NULL;\n");
}
if (txtDefault->GetValue() != domain->GetDefault())
{
sql += wxT("ALTER DOMAIN ") + domain->GetQuotedFullIdentifier();
if (txtDefault->GetValue().IsEmpty())
sql += wxT("\n DROP DEFAULT;\n");
else
sql += wxT("\n SET DEFAULT ") + txtDefault->GetValue() + wxT(";\n");
}
// Build a temporary list of ADD CONSTRAINTs, and fixup the list to remove
for (pos = 0; pos < lstConstraints->GetItemCount() ; pos++)
{
wxString conname = qtIdent(lstConstraints->GetItemText(pos));
definition = conname;
definition += wxT(" CHECK ") + constraintsDefinition.Item(pos);
index = tmpDef.Index(definition);
if (index >= 0)
tmpDef.RemoveAt(index);
else
{
tmpsql += wxT("ALTER DOMAIN ") + domain->GetQuotedFullIdentifier()
+ wxT("\n ADD");
if (!conname.IsEmpty())
tmpsql += wxT(" CONSTRAINT ");
tmpsql += definition + wxT(";\n");
}
}
// Add the DROP CONSTRAINTs...
for (index = 0 ; index < (int)tmpDef.GetCount() ; index++)
{
definition = tmpDef.Item(index);
if (definition[0U] == '"')
definition = definition.Mid(1).BeforeFirst('"');
else
definition = definition.BeforeFirst(' ');
sql += wxT("ALTER DOMAIN ") + domain->GetQuotedFullIdentifier()
+ wxT("\n DROP CONSTRAINT ") + qtIdent(definition) + wxT(";\n");
}
// Add the ADD CONSTRAINTs...
sql += tmpsql;
AppendOwnerChange(sql, wxT("DOMAIN ") + domain->GetQuotedFullIdentifier());
AppendSchemaChange(sql, wxT("DOMAIN ") + domain->GetQuotedFullIdentifier());
}
else
{
// create mode
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
sql = wxT("CREATE DOMAIN ") + name
+ wxT("\n AS ") + GetQuotedTypename(cbDatatype->GetGuessedSelection());
if (!cbCollation->GetValue().IsEmpty() && cbCollation->GetValue() != wxT("pg_catalog.\"default\""))
sql += wxT("\n COLLATE ") + cbCollation->GetValue();
AppendIfFilled(sql, wxT("\n DEFAULT "), txtDefault->GetValue());
if (chkNotNull->GetValue())
sql += wxT("\n NOT NULL");
for (pos = 0 ; pos < lstConstraints->GetItemCount() ; pos++)
{
wxString name = lstConstraints->GetItemText(pos);
wxString definition = constraintsDefinition.Item(pos);
if (!name.IsEmpty())
sql += wxT("\n CONSTRAINT ") + qtIdent(name) + wxT(" CHECK ") + definition;
else
sql += wxT("\n CHECK ") + definition;
}
sql += wxT(";\n");
AppendOwnerNew(sql, wxT("DOMAIN ") + name);
}
AppendComment(sql, wxT("DOMAIN ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), domain);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("DOMAIN"), name);
return sql;
}
wxString dlgForeignDataWrapper::GetSql()
{
wxString sql, name;
name = txtName->GetValue();
if (fdw)
{
// edit mode
sql = wxEmptyString;
AppendNameChange(sql);
if (cbHandler->GetValue() != fdw->GetHandlerProc())
{
if (cbHandler->GetValue().IsEmpty())
sql += wxT("ALTER FOREIGN DATA WRAPPER ") + qtIdent(name)
+ wxT("\n NO HANDLER;\n");
else
sql += wxT("ALTER FOREIGN DATA WRAPPER ") + qtIdent(name)
+ wxT("\n HANDLER ") + qtIdent(cbHandler->GetValue())
+ wxT(";\n");
}
if (cbValidator->GetValue() != fdw->GetValidatorProc())
{
if (cbValidator->GetValue().IsEmpty())
sql += wxT("ALTER FOREIGN DATA WRAPPER ") + qtIdent(name)
+ wxT("\n NO VALIDATOR;\n");
else
sql += wxT("ALTER FOREIGN DATA WRAPPER ") + qtIdent(name)
+ wxT("\n VALIDATOR ") + qtIdent(cbValidator->GetValue())
+ wxT(";\n");
}
wxString sqloptions = GetOptionsSql();
if (sqloptions.Length() > 0)
{
sql += wxT("ALTER FOREIGN DATA WRAPPER ") + name
+ wxT(" OPTIONS (") + sqloptions + wxT(");");
}
AppendOwnerChange(sql, wxT("FOREIGN DATA WRAPPER ") + qtIdent(name));
}
else
{
// create mode
sql = wxT("CREATE FOREIGN DATA WRAPPER ") + qtIdent(name);
AppendIfFilled(sql, wxT("\n HANDLER "), qtIdent(cbHandler->GetValue()));
AppendIfFilled(sql, wxT("\n VALIDATOR "), qtIdent(cbValidator->GetValue()));
// check for options
if (lstOptions->GetItemCount() > 0)
{
wxString options = wxEmptyString;
for (int pos = 0 ; pos < lstOptions->GetItemCount() ; pos++)
{
if (options.Length() > 0)
options += wxT(", ");
options += lstOptions->GetText(pos, 0)
+ wxT(" '") + lstOptions->GetText(pos, 1) + wxT("' ");
}
sql += wxT("\n OPTIONS (") + options + wxT(")");
}
sql += wxT(";\n");
AppendOwnerNew(sql, wxT("FOREIGN DATA WRAPPER ") + qtIdent(name));
}
sql += GetGrant(wxT("U"), wxT("FOREIGN DATA WRAPPER ") + qtIdent(name));
AppendComment(sql, wxT("FOREIGN DATA WRAPPER"), 0, fdw);
return sql;
}
wxString dlgDomain::GetSql()
{
wxString sql, name;
if (domain)
{
// edit mode
name = GetName();
if (txtName->GetValue() != domain->GetName())
{
if (connection->BackendMinimumVersion(9, 2))
AppendNameChange(sql, wxT("DOMAIN ") + domain->GetQuotedFullIdentifier());
else
AppendNameChange(sql, wxT("TYPE ") + domain->GetQuotedFullIdentifier());
}
if (chkNotNull->GetValue() != domain->GetNotNull())
{
sql += wxT("ALTER DOMAIN ") + domain->GetQuotedFullIdentifier();
if (chkNotNull->GetValue())
sql += wxT("\n SET NOT NULL;\n");
else
sql += wxT("\n DROP NOT NULL;\n");
}
if (txtDefault->GetValue() != domain->GetDefault())
{
sql += wxT("ALTER DOMAIN ") + domain->GetQuotedFullIdentifier();
if (txtDefault->GetValue().IsEmpty())
sql += wxT("\n DROP DEFAULT;\n");
else
sql += wxT("\n SET DEFAULT ") + txtDefault->GetValue() + wxT(";\n");
}
if (txtCheck->GetValue() != domain->GetCheck())
{
if (!domain->GetCheck().IsEmpty())
sql += wxT("ALTER DOMAIN ") + schema->GetQuotedPrefix() + qtIdent(name)
+ wxT(" DROP CONSTRAINT ") + qtIdent(domain->GetCheckConstraintName());
if (!txtCheck->GetValue().IsEmpty())
{
sql += wxT("ALTER DOMAIN ") + schema->GetQuotedPrefix() + qtIdent(name)
+ wxT(" ADD ");
if (!domain->GetCheck().IsEmpty())
sql += wxT("CONSTRAINT ") + qtIdent(domain->GetCheckConstraintName());
sql += wxT("\n CHECK (") + txtCheck->GetValue() + wxT(")");
if (chkDontValidate->GetValue())
sql += wxT(" NOT VALID");
}
}
if (chkDontValidate->IsEnabled() && !domain->GetValid() && !chkDontValidate->GetValue())
{
sql += wxT("ALTER DOMAIN ") + schema->GetQuotedPrefix() + qtIdent(name)
+ wxT(" VALIDATE CONSTRAINT ") + qtIdent(domain->GetCheckConstraintName()) + wxT(";\n");
}
AppendOwnerChange(sql, wxT("DOMAIN ") + domain->GetQuotedFullIdentifier());
AppendSchemaChange(sql, wxT("DOMAIN ") + domain->GetQuotedFullIdentifier());
}
else
{
// create mode
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
sql = wxT("CREATE DOMAIN ") + name
+ wxT("\n AS ") + GetQuotedTypename(cbDatatype->GetGuessedSelection());
if (!cbCollation->GetValue().IsEmpty() && cbCollation->GetValue() != wxT("pg_catalog.\"default\""))
sql += wxT("\n COLLATE ") + cbCollation->GetValue();
if (chkDontValidate->GetValue())
sql += wxT(";\nALTER DOMAIN ") + name + wxT(" ADD ");
AppendIfFilled(sql, wxT("\n DEFAULT "), txtDefault->GetValue());
if (chkNotNull->GetValue())
sql += wxT("\n NOT NULL");
if (!txtCheck->GetValue().IsEmpty())
sql += wxT("\n CHECK (") + txtCheck->GetValue() + wxT(")");
if (chkDontValidate->GetValue())
sql += wxT(" NOT VALID");
sql += wxT(";\n");
AppendOwnerNew(sql, wxT("DOMAIN ") + name);
}
AppendComment(sql, wxT("DOMAIN ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), domain);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("DOMAIN"), name);
return sql;
}
wxString dlgForeignTable::GetSql()
{
wxString sql;
wxString name;
if (foreigntable)
{
// Edit Mode
name = qtIdent(foreigntable->GetSchema()->GetName()) + wxT(".") + qtIdent(GetName());
AppendNameChange(sql, wxT("FOREIGN TABLE ") + foreigntable->GetQuotedFullIdentifier());
AppendOwnerChange(sql, wxT("FOREIGN TABLE ") + name);
sql += GetSqlForTypes();
wxString sqloptions = GetOptionsSql();
if (sqloptions.Length() > 0)
{
sql += wxT("ALTER FOREIGN TABLE ") + name
+ wxT("\n OPTIONS (") + sqloptions + wxT(");\n");
}
AppendSchemaChange(sql, wxT("FOREIGN TABLE ") + name);
}
else
{
name = qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName());
// Create Mode
sql = wxT("CREATE FOREIGN TABLE " + name);
sql += wxT(" (");
int i;
for (i = 0 ; i < lstMembers->GetItemCount() ; i++)
{
if (i)
sql += wxT(",\n ");
sql += qtIdent(lstMembers->GetItemText(i)) + wxT(" ")
+ GetFullTypeName(i);
}
sql += wxT(") SERVER ") + cbForeignServer->GetValue();
// check for options
if (lstOptions->GetItemCount() > 0)
{
wxString options = wxEmptyString;
for (int pos = 0 ; pos < lstOptions->GetItemCount() ; pos++)
{
if (options.Length() > 0)
options += wxT(", ");
options += lstOptions->GetText(pos, 0)
+ wxT(" '") + lstOptions->GetText(pos, 1) + wxT("' ");
}
sql += wxT("\n OPTIONS (") + options + wxT(")");
}
sql += wxT(";\n");
}
AppendComment(sql, wxT("FOREIGN TABLE ") + qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()), foreigntable);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("FOREIGN TABLE "), qtIdent(cbSchema->GetValue()) + wxT(".") + qtIdent(GetName()));
return sql;
}
