wxString dlgColumn::GetDefinition()
{
wxString sql;
sql = GetQuotedTypename(cbDatatype->GetGuessedSelection());
if (!cbCollation->GetValue().IsEmpty() && cbCollation->GetValue() != wxT("pg_catalog.\"default\""))
sql += wxT(" COLLATE ") + cbCollation->GetValue();
if (chkNotNull->GetValue())
sql += wxT(" NOT NULL");
AppendIfFilled(sql, wxT(" DEFAULT "), txtDefault->GetValue());
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 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 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 dlgColumn::GetSql()
{
wxString sql;
wxString name = GetName();
bool isSerial = (cbDatatype->GetValue() == wxT("serial") || cbDatatype->GetValue() == wxT("bigserial") || cbDatatype->GetValue() == wxT("smallserial"));
if (table)
{
if (column)
{
if (name != column->GetName())
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT(" RENAME ") + qtIdent(column->GetName())
+ wxT(" TO ") + qtIdent(name)
+ wxT(";\n");
wxString len;
if (txtLength->IsEnabled())
len = txtLength->GetValue();
wxString prec;
if (txtPrecision->IsEnabled())
prec = txtPrecision->GetValue();
if (connection->BackendMinimumVersion(7, 5))
{
if ((cbDatatype->GetValue() != column->GetRawTypename() && !column->GetIsArray()) ||
(cbDatatype->GetValue() != column->GetRawTypename() + wxT("[]") && column->GetIsArray()) ||
(!cbCollation->GetValue().IsEmpty() && cbCollation->GetValue() != column->GetCollation()) ||
(isVarLen && txtLength->IsEnabled() && StrToLong(len) != column->GetLength()) ||
(isVarPrec && txtPrecision->IsEnabled() && StrToLong(prec) != column->GetPrecision()))
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name) + wxT(" TYPE ")
+ GetQuotedTypename(cbDatatype->GetGuessedSelection());
if (!cbCollation->GetValue().IsEmpty() && cbCollation->GetValue() != column->GetCollation())
sql += wxT(" COLLATE ") + cbCollation->GetValue();
sql += wxT(";\n");
}
}
else
{
wxString sqlPart;
if (cbDatatype->GetCount() > 1 && cbDatatype->GetValue() != column->GetRawTypename())
sqlPart = wxT("atttypid=") + dlgTypeProperty::GetTypeOid(cbDatatype->GetGuessedSelection());
if (!sqlPart.IsEmpty() ||
(isVarLen && txtLength->IsEnabled() && StrToLong(prec) != column->GetLength()) ||
(isVarPrec && txtPrecision->IsEnabled() && StrToLong(prec) != column->GetPrecision()))
{
long typmod = pgDatatype::GetTypmod(column->GetRawTypename(), len, prec);
if (!sqlPart.IsEmpty())
sqlPart += wxT(", ");
sqlPart += wxT("atttypmod=") + NumToStr(typmod);
}
if (!sqlPart.IsEmpty())
{
sql += wxT("UPDATE pg_attribute\n")
wxT(" SET ") + sqlPart + wxT("\n")
wxT(" WHERE attrelid=") + table->GetOidStr() +
wxT(" AND attnum=") + NumToStr(column->GetColNumber()) + wxT(";\n");
}
}
if (txtDefault->GetValue() != column->GetDefault())
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name);
if (txtDefault->GetValue().IsEmpty())
sql += wxT(" DROP DEFAULT");
else
sql += wxT(" SET DEFAULT ") + txtDefault->GetValue();
sql += wxT(";\n");
}
if (chkNotNull->GetValue() != column->GetNotNull())
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name);
if (chkNotNull->GetValue())
sql += wxT(" SET");
else
sql += wxT(" DROP");
sql += wxT(" NOT NULL;\n");
}
if (txtAttstattarget->GetValue() != NumToStr(column->GetAttstattarget()))
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name);
if (txtAttstattarget->GetValue().IsEmpty())
sql += wxT(" SET STATISTICS -1");
else
sql += wxT(" SET STATISTICS ") + txtAttstattarget->GetValue();
sql += wxT(";\n");
}
wxArrayString vars;
size_t index;
for (index = 0 ; index < column->GetVariables().GetCount() ; index++)
vars.Add(column->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 TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name)
+ wxT("\n SET (") + newVar + wxT("=") + newVal + wxT(");\n");
}
}
// check for removed vars
for (pos = 0 ; pos < (int)vars.GetCount() ; pos++)
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name)
+ wxT("\n RESET (") + vars.Item(pos).BeforeFirst('=') + wxT(");\n");
}
if (cbStorage->GetValue() != column->GetStorage())
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name)
+ wxT(" SET STORAGE ") + cbStorage->GetValue()
+ wxT(";\n");
}
}
else
{
sql = wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ADD COLUMN ") + qtIdent(name)
+ wxT(" ") + GetQuotedTypename(cbDatatype->GetGuessedSelection());
if (!cbCollation->GetValue().IsEmpty() && cbCollation->GetValue() != wxT("pg_catalog.\"default\""))
sql += wxT(" COLLATE ") + cbCollation->GetValue();
if (chkNotNull->GetValue())
sql += wxT(" NOT NULL");
if (!isSerial && !txtDefault->GetValue().IsEmpty())
sql += wxT(" DEFAULT ") + txtDefault->GetValue();
sql += wxT(";\n");
if (!txtAttstattarget->GetValue().IsEmpty())
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name)
+ wxT(" SET STATISTICS ") + txtAttstattarget->GetValue()
+ wxT(";\n");
// check for added vars
for (int pos = 0 ; pos < lstVariables->GetItemCount() ; pos++)
{
sql += wxT("ALTER TABLE ") + table->GetQuotedFullIdentifier()
+ wxT("\n ALTER COLUMN ") + qtIdent(name)
+ wxT("\n SET (") + lstVariables->GetText(pos) + wxT("=")
+ lstVariables->GetText(pos, 1) + wxT(");\n");
}
}
AppendComment(sql, wxT("COLUMN ") + table->GetQuotedFullIdentifier()
+ wxT(".") + qtIdent(name), column);
if (seclabelPage && connection->BackendMinimumVersion(9, 1))
sql += seclabelPage->GetSqlForSecLabels(wxT("COLUMN"), table->GetQuotedFullIdentifier()
+ wxT(".") + qtIdent(name));
// securityPage will exists only for PG 8.4 and later
if (connection->BackendMinimumVersion(8, 4))
sql += securityPage->GetGrant(wxT("arwx"), table->GetQuotedFullIdentifier(), ¤tAcl, qtIdent(name));
}
return sql;
}
