void pgRole::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
{
if (!expandedKids)
{
expandedKids=true;
wxString rolesquery;
if (GetConnection()->BackendMinimumVersion(8, 2))
rolesquery = wxT("SELECT rolname, admin_option,\n")
wxT(" pg_catalog.shobj_description(r.oid, 'pg_authid') AS description\n");
else
rolesquery = wxT("SELECT rolname, admin_option\n");
rolesquery += wxT(" FROM pg_roles r\n")
wxT(" JOIN pg_auth_members ON r.oid=roleid\n")
wxT(" WHERE member=") + GetOidStr() + wxT("\n")
wxT(" ORDER BY rolname");
pgSetIterator roles(GetConnection(), rolesquery);
while (roles.RowsLeft())
{
wxString role=roles.GetVal(wxT("rolname"));
if (roles.GetBool(wxT("admin_option")))
role += PGROLE_ADMINOPTION;
rolesIn.Add(role);
}
}
if (properties)
{
CreateListColumns(properties);
properties->AppendItem(_("Name"), GetName());
properties->AppendItem(_("OID"), GetOid());
properties->AppendItem(_("Account expires"), DateToAnsiStr(GetAccountExpires()));
properties->AppendItem(_("Can login?"), BoolToYesNo(GetCanLogin()));
properties->AppendItem(_("Superuser?"), BoolToYesNo(GetSuperuser()));
properties->AppendItem(_("Create databases?"), BoolToYesNo(GetCreateDatabase()));
properties->AppendItem(_("Create roles?"), BoolToYesNo(GetCreateRole()));
properties->AppendItem(_("Update catalogs?"), BoolToYesNo(GetUpdateCatalog()));
properties->AppendItem(_("Inherits?"), BoolToYesNo(GetInherits()));
if (server->GetConnection()->BackendMinimumVersion(9, 1))
{
properties->AppendItem(_("Replication?"), BoolToYesNo(GetReplication()));
}
wxString strConnLimit;
strConnLimit.Printf(wxT("%ld"), GetConnectionLimit());
properties->AppendItem(_("Connection Limit"), strConnLimit);
properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
wxString roleList;
size_t index;
for (index=0 ; index < rolesIn.GetCount() ; index++)
{
if (!roleList.IsEmpty())
roleList += wxT(", ");
roleList += rolesIn.Item(index);
}
properties->AppendItem(_("Member of"), roleList);
for (index=0; index < configList.GetCount() ; index++)
{
wxString item=configList.Item(index);
properties->AppendItem(item.BeforeFirst('='), item.AfterFirst('='));
}
}
}
wxString pgRole::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- Role: ") + GetName() + wxT("\n\n")
+ wxT("-- DROP ROLE ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE ROLE ") + GetQuotedIdentifier();
if (GetCanLogin())
{
sql += wxT(" LOGIN");
if (GetPassword() != wxT("********") && !GetPassword().IsEmpty())
AppendIfFilled(sql, wxT("\n ENCRYPTED PASSWORD "), qtDbString(GetPassword()));
}
sql += wxT("\n ");
if (this->GetSuperuser()) sql += wxT(" SUPERUSER");
else sql += wxT(" NOSUPERUSER");
if (GetInherits()) sql += wxT(" INHERIT");
else sql += wxT(" NOINHERIT");
if (GetCreateDatabase()) sql += wxT(" CREATEDB");
else sql += wxT(" NOCREATEDB");
if (GetCreateRole()) sql += wxT(" CREATEROLE");
else sql += wxT(" NOCREATEROLE");
if (server->GetConnection()->BackendMinimumVersion(9, 1))
{
if (GetReplication()) sql += wxT(" REPLICATION");
else sql += wxT(" NOREPLICATION");
}
if (GetConnectionLimit() > 0)
sql += wxT(" CONNECTION LIMIT ") + NumToStr(GetConnectionLimit());
if (GetAccountExpires().IsValid())
AppendIfFilled(sql, wxT(" VALID UNTIL "), qtDbString(DateToAnsiStr(GetAccountExpires())));
if (GetRolQueueName().Length() > 0)
AppendIfFilled(sql, wxT(" RESOURCE QUEUE "), GetRolQueueName());
sql +=wxT(";\n");
if (this->GetSuperuser() && !GetUpdateCatalog())
sql += wxT("UPDATE pg_authid SET rolcatupdate=false WHERE rolname=") + qtDbString(GetIdentifier()) + wxT(";\n");
size_t index;
for (index=0 ; index < configList.GetCount() ; index++)
{
if (configList.Item(index).BeforeFirst('=') != wxT("search_path") &&
configList.Item(index).BeforeFirst('=') != wxT("temp_tablespaces"))
sql += wxT("ALTER ROLE ") + GetQuotedIdentifier()
+ wxT(" SET ") + configList.Item(index).BeforeFirst('=') + wxT("='") + configList.Item(index).AfterFirst('=') + wxT("';\n");
else
sql += wxT("ALTER ROLE ") + GetQuotedIdentifier()
+ wxT(" SET ") + configList.Item(index).BeforeFirst('=') + wxT("=") + configList.Item(index).AfterFirst('=') + wxT(";\n");
}
for (index=0 ; index < rolesIn.GetCount() ; index++)
{
wxString role=rolesIn.Item(index);
bool admin=false;
if (role.Right(PGROLE_ADMINOPTION_LEN) == PGROLE_ADMINOPTION)
{
admin=true;
role=role.Left(role.Length()-PGROLE_ADMINOPTION_LEN);
}
sql += wxT("GRANT ") + qtIdent(role)
+ wxT(" TO ") + GetQuotedIdentifier();
if (admin)
sql += wxT(" WITH ADMIN OPTION");
sql += wxT(";\n");
}
if (!GetComment().IsNull())
{
sql += wxT("COMMENT ON ROLE ") + GetQuotedFullIdentifier() + wxT(" IS ")
+ qtDbString(GetComment()) + wxT(";\n");
}
}
return sql;
}
void pgDatabase::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
{
if (Connect() == PGCONN_OK)
{
// Set the icon if required
UpdateIcon(browser);
// Add child nodes if necessary
if (browser->GetChildrenCount(GetId(), false) == 0)
{
wxLogInfo(wxT("Adding child object to database %s"), GetIdentifier().c_str());
if (settings->GetDisplayOption(_("Catalogs")))
browser->AppendCollection(this, catalogFactory);
if (settings->GetDisplayOption(_("Casts")))
browser->AppendCollection(this, castFactory);
if (settings->GetDisplayOption(_("Extensions")) && GetConnection()->BackendMinimumVersion(9, 1))
browser->AppendCollection(this, extensionFactory);
if (settings->GetDisplayOption(_("Foreign Data Wrappers")) && GetConnection()->BackendMinimumVersion(8, 4))
browser->AppendCollection(this, foreignDataWrapperFactory);
if (settings->GetDisplayOption(_("Languages")))
browser->AppendCollection(this, languageFactory);
if (settings->GetDisplayOption(_("Synonyms")) && connection()->EdbMinimumVersion(8, 0))
if (!GetConnection()->BackendMinimumVersion(8, 4))
browser->AppendCollection(this, synonymFactory);
if (settings->GetDisplayOption(_("Schemas")))
browser->AppendCollection(this, schemaFactory);
if (settings->GetDisplayOption(_("Slony-I Clusters")))
browser->AppendCollection(this, slClusterFactory);
wxString missingFKsql = wxT("SELECT COUNT(*) FROM\n")
wxT(" (SELECT tgargs from pg_trigger tr\n")
wxT(" LEFT JOIN pg_depend dep ON dep.objid=tr.oid AND deptype = 'i'\n")
wxT(" LEFT JOIN pg_constraint co ON refobjid = co.oid AND contype = 'f'\n")
wxT(" WHERE \n");
if (connection()->BackendMinimumVersion(9, 0))
missingFKsql += wxT("tgisinternal\n");
else
missingFKsql += wxT("tgisconstraint\n");
missingFKsql += wxT(" AND co.oid IS NULL\n")
wxT(" GROUP BY tgargs\n")
wxT(" HAVING count(1) = 3) AS foo");
missingFKs = StrToLong(connection()->ExecuteScalar(missingFKsql));
}
}
GetServer()->iSetLastDatabase(GetName());
if (properties)
{
// Setup listview
CreateListColumns(properties);
properties->AppendItem(_("Name"), GetName());
properties->AppendItem(_("OID"), NumToStr(GetOid()));
properties->AppendItem(_("Owner"), GetOwner());
properties->AppendItem(_("ACL"), GetAcl());
if (!GetPath().IsEmpty())
properties->AppendItem(_("Path"), GetPath());
// We may not actually be connected...
if (GetConnection() && GetConnection()->BackendMinimumVersion(8, 0))
{
properties->AppendItem(_("Tablespace"), GetTablespace());
properties->AppendItem(_("Default tablespace"), GetDefaultTablespace());
}
properties->AppendItem(_("Encoding"), GetEncoding());
if (GetConnection() && GetConnection()->BackendMinimumVersion(8, 4))
{
properties->AppendItem(_("Collation"), GetCollate());
properties->AppendItem(_("Character type"), GetCType());
}
properties->AppendItem(_("Default schema"), defaultSchema);
properties->AppendItem(_("Default table ACL"), m_defPrivsOnTables);
properties->AppendItem(_("Default sequence ACL"), m_defPrivsOnSeqs);
properties->AppendItem(_("Default function ACL"), m_defPrivsOnFuncs);
size_t i;
wxString username;
wxString varname;
wxString varvalue;
for (i = 0 ; i < variables.GetCount() ; i++)
{
wxStringTokenizer tkz(variables.Item(i), wxT("="));
while (tkz.HasMoreTokens())
{
username = tkz.GetNextToken();
varname = tkz.GetNextToken();
varvalue = tkz.GetNextToken();
}
if (username.Length() == 0)
{
properties->AppendItem(varname, varvalue);
}
else
{
// should we add the parameters for the username?
// I don't think so
// but if we want this, how will we display that?
}
}
properties->AppendYesNoItem(_("Allow connections?"), GetAllowConnections());
properties->AppendYesNoItem(_("Connected?"), GetConnected());
if (GetConnection() && GetConnection()->BackendMinimumVersion(8, 1))
{
wxString strConnLimit;
strConnLimit.Printf(wxT("%ld"), GetConnectionLimit());
properties->AppendItem(_("Connection limit"), strConnLimit);
}
properties->AppendYesNoItem(_("System database?"), GetSystemObject());
if (GetMissingFKs())
properties->AppendItem(_("Old style FKs"), GetMissingFKs());
if (!GetSchemaRestriction().IsEmpty())
properties->AppendItem(_("Schema restriction"), GetSchemaRestriction());
properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
if (!GetLabels().IsEmpty())
{
wxArrayString seclabels = GetProviderLabelArray();
if (seclabels.GetCount() > 0)
{
for (unsigned int index = 0 ; index < seclabels.GetCount() - 1 ; index += 2)
{
properties->AppendItem(seclabels.Item(index), seclabels.Item(index + 1));
}
}
}
}
if (form && GetCanHint() && !hintShown)
{
ShowHint(form, false);
}
}
void pgRole::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
{
if (!expandedKids)
{
expandedKids = true;
wxString rolesquery;
if (GetConnection()->BackendMinimumVersion(8, 2))
rolesquery = wxT("SELECT rolname, admin_option,\n")
wxT(" pg_catalog.shobj_description(r.oid, 'pg_authid') AS description\n");
else
rolesquery = wxT("SELECT rolname, admin_option\n");
rolesquery += wxT(" FROM pg_roles r\n")
wxT(" JOIN pg_auth_members ON r.oid=roleid\n")
wxT(" WHERE member=") + GetOidStr() + wxT("\n")
wxT(" ORDER BY rolname");
pgSetIterator roles(GetConnection(), rolesquery);
while (roles.RowsLeft())
{
wxString role = roles.GetVal(wxT("rolname"));
if (roles.GetBool(wxT("admin_option")))
role += PGROLE_ADMINOPTION;
rolesIn.Add(role);
}
// Get configuration
wxString query;
if (GetConnection()->BackendMinimumVersion(9, 0))
{
query = wxT("WITH configs AS ")
wxT("(SELECT datname, unnest(setconfig) AS config")
wxT(" FROM pg_db_role_setting s")
wxT(" LEFT JOIN pg_database d ON d.oid=s.setdatabase")
wxT(" WHERE s.setrole=") + NumToStr(GetOid()) + wxT(")\n")
wxT("SELECT datname, split_part(config, '=', 1) AS variable, replace(config, split_part(config, '=', 1) || '=', '') AS value\n")
wxT("FROM configs");
}
else
{
wxString query_myrole = wxT("SELECT rolconfig FROM pg_roles WHERE oid=") + NumToStr(GetOid());
query = wxT("SELECT '' AS datname, split_part(config, '=', 1) AS variable,\n")
wxT(" replace(config,split_part(config, '=', 1) || '=', '') AS value\n")
wxT("FROM (\n")
wxT(" SELECT\n")
wxT(" (\n")
wxT(" SELECT rolconfig[i]\n")
wxT(" FROM pg_roles\n")
wxT(" WHERE oid=") + NumToStr(GetOid()) + wxT("\n")
wxT(" ) AS config\n")
wxT(" FROM generate_series(array_lower((") + query_myrole + wxT("),1), array_upper((") + query_myrole + wxT("),1)) AS i\n")
wxT(" ) configs");
}
pgSet *configs = GetConnection()->ExecuteSet(query);
if (configs)
{
while (!configs->Eof())
{
variables.Add(configs->GetVal(wxT("datname")));
variables.Add(configs->GetVal(wxT("variable")));
variables.Add(configs->GetVal(wxT("value")));
configs->MoveNext();
}
delete configs;
}
}
if (properties)
{
CreateListColumns(properties);
properties->AppendItem(_("Name"), GetName());
properties->AppendItem(_("OID"), GetOid());
properties->AppendItem(_("Account expires"), DateToAnsiStr(GetAccountExpires()));
properties->AppendItem(_("Can login?"), BoolToYesNo(GetCanLogin()));
properties->AppendItem(_("Superuser?"), BoolToYesNo(GetSuperuser()));
properties->AppendItem(_("Create databases?"), BoolToYesNo(GetCreateDatabase()));
properties->AppendItem(_("Create roles?"), BoolToYesNo(GetCreateRole()));
if (!server->GetConnection()->BackendMinimumVersion(9, 5))
properties->AppendItem(_("Update catalogs?"), BoolToYesNo(GetUpdateCatalog()));
properties->AppendItem(_("Inherits?"), BoolToYesNo(GetInherits()));
if (server->GetConnection()->BackendMinimumVersion(9, 1))
{
properties->AppendItem(_("Replication?"), BoolToYesNo(GetReplication()));
}
wxString strConnLimit;
strConnLimit.Printf(wxT("%ld"), GetConnectionLimit());
properties->AppendItem(_("Connection Limit"), strConnLimit);
properties->AppendItem(_("Comment"), firstLineOnly(GetComment()));
wxString roleList;
size_t index;
for (index = 0 ; index < rolesIn.GetCount() ; index++)
{
if (!roleList.IsEmpty())
roleList += wxT(", ");
roleList += rolesIn.Item(index);
}
properties->AppendItem(_("Member of"), roleList);
wxString dbname;
wxString parameter;
wxString value;
for (index = 0; index < variables.GetCount() ; index += 3)
{
dbname = variables.Item(index);
parameter = variables.Item(index + 1);
value = variables.Item(index + 2);
if (dbname.Length() == 0)
{
properties->AppendItem(parameter, value);
}
else
{
properties->AppendItem(parameter + wxT(" (database ") + dbname + wxT(")"), value);
}
}
if (!GetLabels().IsEmpty())
{
wxArrayString seclabels = GetProviderLabelArray();
if (seclabels.GetCount() > 0)
{
for (unsigned int index = 0 ; index < seclabels.GetCount() - 1 ; index += 2)
{
properties->AppendItem(seclabels.Item(index), seclabels.Item(index + 1));
}
}
}
}
}
wxString pgDatabase::GetSql(ctlTree *browser)
{
if (sql.IsEmpty())
{
// If we can't connect to this database, use the maintenance DB
pgConn *myConn = GetConnection();
if (!myConn)
myConn = GetServer()->GetConnection();
sql = wxT("-- Database: ") + GetQuotedFullIdentifier() + wxT("\n\n")
+ wxT("-- DROP DATABASE ") + GetQuotedIdentifier() + wxT(";")
+ wxT("\n\nCREATE DATABASE ") + GetQuotedIdentifier()
+ wxT("\n WITH OWNER = ") + qtIdent(GetOwner())
+ wxT("\n ENCODING = ") + qtDbString(GetEncoding());
if (!GetTablespace().IsEmpty())
sql += wxT("\n TABLESPACE = ") + qtIdent(GetTablespace());
if (myConn && myConn->BackendMinimumVersion(8, 4))
{
sql += wxT("\n LC_COLLATE = ") + qtDbString(GetCollate());
sql += wxT("\n LC_CTYPE = ") + qtDbString(GetCType());
}
if (myConn && myConn->BackendMinimumVersion(8, 1))
{
sql += wxT("\n CONNECTION LIMIT = ");
sql << GetConnectionLimit();
}
sql += wxT(";\n");
size_t i;
wxString username;
wxString varname;
wxString varvalue;
for (i = 0 ; i < variables.GetCount() ; i++)
{
wxStringTokenizer tkz(variables.Item(i), wxT("="));
while (tkz.HasMoreTokens())
{
username = tkz.GetNextToken();
varname = tkz.GetNextToken();
varvalue = tkz.GetNextToken();
}
if (username.Length() == 0)
{
sql += wxT("ALTER DATABASE ") + GetQuotedFullIdentifier();
}
else
{
sql += wxT("ALTER ROLE ") + username + wxT(" IN DATABASE ") + GetQuotedFullIdentifier();
}
if (varname != wxT("search_path") && varname != wxT("temp_tablespaces"))
sql += wxT(" SET ") + varname + wxT("='") + varvalue + wxT("';\n");
else
sql += wxT(" SET ") + varname + wxT("=") + varvalue + wxT(";\n");
}
if (myConn)
{
if (!myConn->BackendMinimumVersion(8, 2))
sql += GetGrant(wxT("CT"));
else
sql += GetGrant(wxT("CTc"));
}
sql += wxT("\n") + pgDatabase::GetDefaultPrivileges('r', m_defPrivsOnTables, wxT(""));
sql += pgDatabase::GetDefaultPrivileges('S', m_defPrivsOnSeqs, wxT(""));
sql += pgDatabase::GetDefaultPrivileges('f', m_defPrivsOnFuncs, wxT(""));
sql += GetCommentSql();
if (myConn->BackendMinimumVersion(9, 2))
sql += GetSeqLabelsSql();
}
return sql;
}