wxString pgLanguage::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Language: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP LANGUAGE ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE ");
if (GetTrusted())
sql += wxT("TRUSTED ");
sql += wxT("PROCEDURAL LANGUAGE '") + GetName()
+ wxT("'\n HANDLER ") + qtIdent(GetHandlerProc());
if (!GetInlineProc().IsEmpty())
sql += wxT("\n INLINE ") + qtIdent(GetInlineProc());
if (!GetValidatorProc().IsEmpty())
sql += wxT("\n VALIDATOR ") + qtIdent(GetValidatorProc());
sql += wxT(";\n")
+ GetOwnerSql(8, 3, wxT("LANGUAGE ") + GetName())
+ GetGrant(wxT("U"), wxT("LANGUAGE ") + GetQuotedFullIdentifier());
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString pgSequence::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
UpdateValues();
sql = wxT("-- Sequence: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP SEQUENCE ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE SEQUENCE ") + GetQuotedFullIdentifier()
+ wxT("\n INCREMENT ") + GetIncrement().ToString()
+ wxT("\n MINVALUE ") + GetMinValue().ToString()
+ wxT("\n MAXVALUE ") + GetMaxValue().ToString()
+ wxT("\n START ") + GetLastValue().ToString()
+ wxT("\n CACHE ") + GetCacheValue().ToString();
if (GetCycled())
sql += wxT("\n CYCLE");
sql += wxT(";\n")
+ GetOwnerSql(7, 3, wxT("TABLE ") + GetQuotedFullIdentifier());
if (!GetConnection()->BackendMinimumVersion(8, 2))
sql += GetGrant(wxT("arwdRxt"), wxT("TABLE ") + GetQuotedFullIdentifier());
else
sql += GetGrant(wxT("rwU"), wxT("TABLE ") + GetQuotedFullIdentifier());
sql += GetCommentSql();
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
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;
}
wxString pgType::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Type: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP TYPE ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE TYPE ") + GetQuotedFullIdentifier();
if (GetTypeClass() == TYPE_COMPOSITE)
{
sql += wxT(" AS\n (");
sql += GetQuotedTypesList();
}
else if (GetTypeClass() == TYPE_ENUM)
{
sql += wxT(" AS ENUM\n (");
sql += GetQuotedLabelList();
}
else
{
sql += wxT("\n (INPUT=") + qtIdent(GetInputFunction())
+ wxT(", OUTPUT=") + qtIdent(GetOutputFunction());
AppendIfFilled(sql, wxT(", DEFAULT="), qtDbString(GetDefault()));
if (!GetElement().IsNull())
{
sql += wxT(",\n ELEMENT=") + GetElement()
+ wxT(", DELIMITER='") + GetDelimiter() + wxT("'");
}
sql += wxT(",\n INTERNALLENGTH=") + NumToStr(GetInternalLength())
+ wxT(", ALIGNMENT=" + GetAlignment()
+ wxT(", STORAGE=") + GetStorage());
if (GetConnection()->BackendMinimumVersion(8, 3))
{
if (GetTypmodinFunction() != wxEmptyString && GetTypmodoutFunction() != wxEmptyString)
{
sql += wxT(",\n TYPMOD_IN=") + GetTypmodinFunction()
+ wxT(", TYPMOD_OUT=") + GetTypmodoutFunction();
}
else if (GetTypmodinFunction() != wxEmptyString)
sql += wxT(",\n TYPMOD_IN=") + GetTypmodinFunction();
else if (GetTypmodoutFunction() != wxEmptyString)
sql += wxT(",\n TYPMOD_OUT=") + GetTypmodoutFunction();
}
if (GetConnection()->BackendMinimumVersion(9, 1) && GetCollatable())
{
sql += wxT(",\n COLLATABLE=true");
}
}
sql += wxT(");\n")
+ GetOwnerSql(8, 0)
+ GetCommentSql();
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString pgDomain::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Domain: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP DOMAIN ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE DOMAIN ") + GetQuotedFullIdentifier()
+ wxT("\n AS ") + GetQuotedBasetype();
if (GetCollationOid() > 0)
sql += wxT("\n COLLATE ") + GetQuotedCollation();
AppendIfFilled(sql, wxT("\n DEFAULT "), GetDefault());
// CONSTRAINT Name Dont know where it's stored, may be omitted anyway
if (notNull)
sql += wxT("\n NOT NULL");
// Get a count of the constraints.
int consCount = 0;
pgCollection *constraints = browser->FindCollection(checkFactory, GetId());
if (constraints)
{
constraints->ShowTreeDetail(browser);
treeObjectIterator consIt(browser, constraints);
pgObject *data;
while ((data = consIt.GetNextObject()) != 0)
{
data->ShowTreeDetail(browser);
sql += wxT("\n CONSTRAINT ") + data->GetQuotedIdentifier()
+ wxT(" ") + data->GetTypeName().Upper()
+ wxT(" ") ;
switch (data->GetMetaType())
{
case PGM_CHECK:
sql += wxT("(") + ((pgCheck *)data)->GetDefinition() + wxT(")");
if (GetDatabase()->BackendMinimumVersion(9, 2) && !((pgCheck *)data)->GetValid())
sql += wxT(" NOT VALID");
break;
}
}
}
sql += wxT(";\n")
+ GetOwnerSql(7, 4)
+ GetCommentSql();
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString pgConversion::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Conversion: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP CONVERSION ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE ");
if (GetDefaultConversion())
sql += wxT("DEFAULT ");
sql += wxT("CONVERSION ") + qtIdent(GetName())
+ wxT("\n FOR '") + GetForEncoding() + wxT("'")
+ wxT("\n TO '") + GetToEncoding() + wxT("'")
+ wxT("\n FROM ") + GetDatabase()->GetQuotedSchemaPrefix(GetProcNamespace())
+ qtIdent(GetProc()) + wxT(";\n")
+ GetOwnerSql(8, 0);
}
return sql;
}
wxString pgForeignTable::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Foreign Table: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP FOREIGN TABLE ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE FOREIGN TABLE ") + GetQuotedFullIdentifier()
+ wxT("\n (") + GetQuotedTypesList()
+ wxT(")\n SERVER ") + GetForeignServer();
if (!GetOptionsList().IsEmpty())
sql += wxT("\n OPTIONS (") + GetOptionsList() + wxT(")");
sql += wxT(";\n")
+ GetOwnerSql(9, 1)
+ GetCommentSql();
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString pgForeignServer::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Server: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP SERVER ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE SERVER ") + GetQuotedFullIdentifier()
+ wxT("\n FOREIGN DATA WRAPPER ") + qtIdent(GetForeignDataWrapper()->GetName());
if (!GetType().IsEmpty())
sql += wxT("\n TYPE ") + qtDbString(GetType());
if (!GetVersion().IsEmpty())
sql += wxT("\n VERSION ") + qtDbString(GetVersion());
if (!GetOptions().IsEmpty())
sql += wxT("\n OPTIONS (") + GetCreateOptions() + wxT(")");
sql += wxT(";\n")
+ GetOwnerSql(8, 4, wxT("SERVER ") + GetQuotedFullIdentifier())
+ GetGrant(wxT("U"), wxT("FOREIGN SERVER ") + GetQuotedFullIdentifier());
}
return sql;
}
wxString pgForeignDataWrapper::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Foreign Data Wrapper: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP FOREIGN DATA WRAPPER ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE ");
sql += wxT("FOREIGN DATA WRAPPER ") + GetName();
if (!GetHandlerProc().IsEmpty())
sql += wxT("\n HANDLER ") + GetHandlerProc();
if (!GetValidatorProc().IsEmpty())
sql += wxT("\n VALIDATOR ") + GetValidatorProc();
if (!GetOptions().IsEmpty())
sql += wxT("\n OPTIONS (") + GetCreateOptions() + wxT(")");
sql += wxT(";\n")
+ GetOwnerSql(8, 4, wxT("FOREIGN DATA WRAPPER ") + GetName())
+ GetGrant(wxT("U"), wxT("FOREIGN DATA WRAPPER ") + GetQuotedFullIdentifier());
}
return sql;
}
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;
}
wxString pgFunction::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
wxString qtName = GetQuotedFullIdentifier() + wxT("(") + GetArgListWithNames() + wxT(")");
wxString qtSig = GetQuotedFullIdentifier() + wxT("(") + GetArgSigList() + wxT(")");
sql = wxT("-- Function: ") + qtSig + wxT("\n\n")
+ wxT("-- DROP FUNCTION ") + qtSig + wxT(";")
+ wxT("\n\nCREATE OR REPLACE FUNCTION ") + qtName;
// Use Oracle style syntax for edb-spl functions
if (GetLanguage() == wxT("edbspl") && GetProcType() == 2)
{
sql += wxT("\nRETURN ");
sql += GetReturnType();
sql += wxT(" AS");
if (GetSource().StartsWith(wxT("\n")))
sql += GetSource();
else
sql += wxT("\n") + GetSource();
}
else
{
sql += wxT("\n RETURNS ");
if (GetReturnAsSet() && !GetReturnType().StartsWith(wxT("TABLE")))
sql += wxT("SETOF ");
sql += GetReturnType();
sql += wxT(" AS\n");
if (GetLanguage().IsSameAs(wxT("C"), false))
{
sql += qtDbString(GetBin()) + wxT(", ") + qtDbString(GetSource());
}
else
{
if (GetConnection()->BackendMinimumVersion(7, 5))
sql += qtDbStringDollar(GetSource());
else
sql += qtDbString(GetSource());
}
sql += wxT("\n LANGUAGE ") + GetLanguage() + wxT(" ");
if (GetConnection()->BackendMinimumVersion(8, 4) && GetIsWindow())
sql += wxT("WINDOW ");
sql += GetVolatility();
if (GetConnection()->BackendMinimumVersion(9, 2) && GetIsLeakProof())
sql += wxT(" LEAKPROOF");
if (GetIsStrict())
sql += wxT(" STRICT");
if (GetSecureDefiner())
sql += wxT(" SECURITY DEFINER");
// PostgreSQL 8.3+ cost/row estimations
if (GetConnection()->BackendMinimumVersion(8, 3))
{
sql += wxT("\n COST ") + NumToStr(GetCost());
if (GetReturnAsSet())
sql += wxT("\n ROWS ") + NumToStr(GetRows());
}
}
if (!sql.Strip(wxString::both).EndsWith(wxT(";")))
sql += wxT(";");
size_t i;
for (i = 0 ; i < configList.GetCount() ; i++)
{
if (configList.Item(i).BeforeFirst('=') != wxT("search_path") &&
configList.Item(i).BeforeFirst('=') != wxT("temp_tablespaces"))
sql += wxT("\nALTER FUNCTION ") + qtSig
+ wxT(" SET ") + configList.Item(i).BeforeFirst('=') + wxT("='") + configList.Item(i).AfterFirst('=') + wxT("';\n");
else
sql += wxT("\nALTER FUNCTION ") + qtSig
+ wxT(" SET ") + configList.Item(i).BeforeFirst('=') + wxT("=") + configList.Item(i).AfterFirst('=') + wxT(";\n");
}
sql += wxT("\n")
+ GetOwnerSql(8, 0, wxT("FUNCTION ") + qtSig)
+ GetGrant(wxT("X"), wxT("FUNCTION ") + qtSig);
if (!GetComment().IsNull())
{
sql += wxT("COMMENT ON FUNCTION ") + qtSig
+ wxT(" IS ") + qtDbString(GetComment()) + wxT(";\n");
}
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString pgType::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Type: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP TYPE ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE TYPE ") + GetQuotedFullIdentifier();
if (GetTypeClass() == TYPE_COMPOSITE)
{
sql += wxT(" AS\n (");
sql += GetQuotedTypesList();
}
else if (GetTypeClass() == TYPE_ENUM)
{
sql += wxT(" AS ENUM\n (");
sql += GetQuotedLabelList();
}
else if (GetTypeClass() == TYPE_RANGE)
{
sql += wxT(" AS RANGE\n (")
wxT("SUBTYPE=") + rngsubtypestr;
if (!rngsubopcstr.IsEmpty())
sql += wxT(",\n SUBTYPE_OPCLASS=") + rngsubopcstr;
if (!rngcollationstr.IsEmpty())
sql += wxT(",\n COLLATION=") + rngcollationstr;
if (!rngcanonical.IsEmpty())
sql += wxT(",\n CANONICAL=") + rngcanonical;
if (!rngsubdiff.IsEmpty())
sql += wxT(",\n SUBTYPE_DIFF=") + rngsubdiff;
}
else
{
sql += wxT("\n (INPUT=") + qtIdent(GetInputFunction())
+ wxT(",\n OUTPUT=") + qtIdent(GetOutputFunction());
if (GetConnection()->BackendMinimumVersion(7, 4))
{
if (!GetReceiveFunction().IsEmpty())
{
sql += wxT(",\n RECEIVE=") + GetReceiveFunction();
}
if (!GetSendFunction().IsEmpty())
{
sql += wxT(",\n SEND=") + GetSendFunction();
}
}
if (GetConnection()->BackendMinimumVersion(8, 3))
{
if (!GetTypmodinFunction().IsEmpty())
sql += wxT(",\n TYPMOD_IN=") + GetTypmodinFunction();
if (!GetTypmodoutFunction().IsEmpty())
sql += wxT(",\n TYPMOD_OUT=") + GetTypmodoutFunction();
if (GetAnalyzeFunction() != wxEmptyString)
sql += wxT(",\n ANALYZE=") + GetAnalyzeFunction();
}
if (GetConnection()->BackendMinimumVersion(8, 4))
{
sql += wxT(",\n CATEGORY=") + qtDbString(GetCategory());
if (GetPrefered())
sql += wxT(",\n PREFERRED=true");
}
if (GetPassedByValue())
sql += wxT(",\n PASSEDBYVALUE");
AppendIfFilled(sql, wxT(", DEFAULT="), qtDbString(GetDefault()));
if (!GetElement().IsNull())
{
sql += wxT(",\n ELEMENT=") + GetElement()
+ wxT(", DELIMITER='") + GetDelimiter() + wxT("'");
}
sql += wxT(",\n INTERNALLENGTH=") + NumToStr(GetInternalLength())
+ wxT(", ALIGNMENT=" + GetAlignment()
+ wxT(", STORAGE=") + GetStorage());
if (GetConnection()->BackendMinimumVersion(9, 1) && GetCollatable())
{
sql += wxT(",\n COLLATABLE=true");
}
if (GetConnection()->BackendMinimumVersion(9, 1) && GetCollatable())
{
sql += wxT(",\n COLLATABLE=true");
}
}
sql += wxT(");\n")
+ GetOwnerSql(8, 0)
+ GetCommentSql();
if (GetConnection()->BackendMinimumVersion(9, 1))
sql += GetSeqLabelsSql();
}
return sql;
}
wxString gpExtTable::GetSql(ctlTree *browser)
{
wxString colDetails;
wxString prevComment;
wxString q;
if (sql.IsNull())
{
sql = wxT("-- External Table: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP EXTERNAL TABLE ") + GetQuotedFullIdentifier() + wxT(";\n\n");
/* Now get required information from pg_exttable */
if (GetDatabase()->BackendMinimumVersion(8, 2, 5))
{
q += wxT(
"SELECT x.location, x.fmttype, x.fmtopts, x.command, ")
wxT("x.rejectlimit, x.rejectlimittype,")
wxT("(SELECT relname ")
wxT("FROM pg_class ")
wxT("WHERE Oid=x.fmterrtbl) AS errtblname, ")
wxT("pg_catalog.pg_encoding_to_char(x.encoding) ")
wxT("FROM pg_catalog.pg_exttable x, pg_catalog.pg_class c ")
wxT("WHERE x.reloid = c.oid AND c.oid = ") + GetOidStr();
}
else
{
/* not SREH and encoding colums yet */
q += wxT(
"SELECT x.location, x.fmttype, x.fmtopts, x.command, ")
wxT("-1 as rejectlimit, null as rejectlimittype,")
wxT("null as errtblname, ")
wxT("null as encoding ")
wxT("FROM pg_catalog.pg_exttable x, pg_catalog.pg_class c ")
wxT("WHERE x.reloid = c.oid AND c.oid = ") + GetOidStr();
}
pgSet *extTable = GetDatabase()->ExecuteSet(q);
wxString locations = extTable->GetVal(0);
wxString fmttype = extTable->GetVal(1);
wxString fmtopts = extTable->GetVal(2);
wxString command = extTable->GetVal(3);
wxString rejlim = extTable->GetVal(4);
wxString rejlimtype = extTable->GetVal(5);
wxString errtblname = extTable->GetVal(6);
wxString extencoding = extTable->GetVal(7);
if ((command.Length() > 0) ||
(locations.Mid(1, 4) == wxT("http")))
{
sql += wxT("CREATE EXTERNAL WEB TABLE ") +
GetQuotedFullIdentifier() + wxT("\n(\n");
}
else
{
sql += wxT("CREATE EXTERNAL TABLE ") +
GetQuotedFullIdentifier() + wxT("\n(\n");
}
// Get the columns
pgCollection *columns = browser->FindCollection(columnFactory, GetId());
if (columns)
{
columns->ShowTreeDetail(browser);
treeObjectIterator colIt1(browser, columns);
treeObjectIterator colIt2(browser, columns);
int lastRealCol = 0;
int currentCol = 0;
pgColumn *column;
// Iterate the columns to find the last 'real' one
while ((column = (pgColumn *)colIt1.GetNextObject()) != 0)
{
currentCol++;
if (column->GetInheritedCount() == 0)
lastRealCol = currentCol;
}
// Now build the actual column list
int colCount = 0;
while ((column = (pgColumn *)colIt2.GetNextObject()) != 0)
{
column->ShowTreeDetail(browser);
if (column->GetColNumber() > 0)
{
if (colCount)
{
// Only add a comma if this isn't the last 'real' column
if (colCount != lastRealCol)
sql += wxT(",");
if (!prevComment.IsEmpty())
sql += wxT(" -- ") + firstLineOnly(prevComment);
sql += wxT("\n");
}
/* if (column->GetInheritedCount() > 0)
{
if (!column->GetIsLocal())
sql += wxString::Format(wxT("-- %s "), _("Inherited"))
+ wxT("from table ") + column->GetInheritedTableName() + wxT(":");
}*/
sql += wxT(" ") + column->GetQuotedIdentifier() + wxT(" ")
+ column->GetDefinition();
prevComment = column->GetComment();
// Whilst we are looping round the columns, grab their comments as well.
// Perhaps we should also get storage types here?
colDetails += column->GetCommentSql();
if (colDetails.Length() > 0)
if (colDetails.Last() != '\n') colDetails += wxT("\n");
colCount++;
}
}
}
if (!prevComment.IsEmpty())
sql += wxT(" -- ") + firstLineOnly(prevComment);
sql += wxT("\n)\n");
if(command.Length() > 0)
{
wxString on_clause = locations;
/* remove curly braces */
on_clause = locations.Mid(1, locations.Length() - 2);
/* add EXECUTE clause */
sql += wxT(" EXECUTE E'");
for (size_t i = 0; i < command.Length(); i++)
{
if (command[i] == wxT('\\')) sql += wxT('\\');
if (command[i] == wxT('\'')) sql += wxT('\'');
sql += command[i];
}
sql += wxT("' ");
/* add ON clause */
wxString temp;
if(on_clause.StartsWith(wxT("HOST:"), &temp))
{
sql += wxT("ON HOST '") + temp + wxT("'");
}
else if(on_clause.StartsWith(wxT("PER_HOST"), &temp))
sql += wxT("ON HOST ");
else if(on_clause.StartsWith(wxT("MASTER_ONLY"), &temp))
sql += wxT("ON MASTER ");
else if(on_clause.StartsWith(wxT("SEGMENT_ID:"), &temp))
sql += wxT("ON SEGMENT ") + temp + wxT(" ");
else if(on_clause.StartsWith(wxT("TOTAL_SEGS:"), &temp))
sql += wxT("ON ") + temp + wxT(" ");
else if(on_clause.StartsWith(wxT("ALL_SEGMENTS"), &temp))
sql += wxT("ON ALL ");
else
sql += on_clause;
sql += wxT("\n ");
}
else
{
/* add LOCATION clause */
locations = locations.Mid(1, locations.Length() - 2);
wxStringTokenizer locs(locations, wxT(","));
wxString token;
token = locs.GetNextToken();
sql += wxT(" LOCATION (\n '");
sql += token;
sql += wxT("'");
while (locs.HasMoreTokens())
{
sql += wxT(",\n '");
sql += locs.GetNextToken();
sql += wxT("'");
}
sql += wxT("\n)\n ");
}
/* add FORMAT clause */
sql += wxT("FORMAT '");
sql += fmttype[0] == 't' ? wxT("text") : wxT("csv");
sql += wxT("'");
sql += wxT(" (");
for (size_t i = 0; i < fmtopts.Length(); i++)
{
if (fmtopts[i] == wxT('\\')) sql += wxT('\\');
sql += fmtopts[i];
}
sql += wxT(")\n");
if (GetDatabase()->BackendMinimumVersion(8, 2))
{
/* add ENCODING clause */
sql += wxT("ENCODING '");
sql += extencoding ;
sql += wxT("'");
/* add Single Row Error Handling clause (if any) */
if(rejlim.Length() > 0)
{
sql += wxT("\n");
/*
* NOTE: error tables get automatically generated if don't exist.
* therefore we must be sure that this statment will be dumped after
* the error relation CREATE is dumped, so that we won't try to
* create it twice. For now we rely on the fact that we pick dumpable
* objects sorted by OID, and error table oid *should* always be less
* than its external table oid (could that not be true sometimes?)
*/
if(errtblname.Length() > 0)
{
sql += wxT("LOG ERRORS INTO ");
sql += errtblname;
sql += wxT(" ");
}
/* reject limit */
sql += wxT("SEGMENT REJECT LIMIT ");
sql += rejlim;
/* reject limit type */
if(rejlimtype[0] == 'r')
sql += wxT(" ROWS");
else
sql += wxT(" PERCENT");
}
}
sql += wxT(";\n")
+ GetOwnerSql(7, 3, wxEmptyString, wxT("TABLE"));
sql += GetGrant(wxT("r"), wxT("TABLE ") + GetQuotedFullIdentifier());
sql += GetCommentSql()
+ wxT("\n");
// Column/constraint comments
if (!colDetails.IsEmpty())
sql += colDetails + wxT("\n");
}
return sql;
}
