wxString dlgSchedule::GetInsertSql()
{
wxString sql;
if (!schedule)
{
wxString name = GetName();
wxString jscjobid, list = wxT("NULL");
if (jobId)
jscjobid = NumToStr(jobId);
else
jscjobid = wxT("<JobId>");
// Build the various arrays of values
sql = wxT("INSERT INTO pgagent.pga_schedule (jscid, jscjobid, jscname, jscdesc, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths, jscenabled, jscstart, jscend)\n")
wxT("VALUES(<SchId>, ") + jscjobid + wxT(", ") + qtDbString(name) + wxT(", ") + qtDbString(txtComment->GetValue()) + wxT(", ")
+ wxT("'") + ChkListBox2PgArray(chkMinutes) + wxT("', ")
+ wxT("'") + ChkListBox2PgArray(chkHours) + wxT("', ")
+ wxT("'") + ChkListBox2PgArray(chkWeekdays) + wxT("', ")
+ wxT("'") + ChkListBox2PgArray(chkMonthdays) + wxT("', ")
+ wxT("'") + ChkListBox2PgArray(chkMonths) + wxT("', ")
+ BoolToStr(chkEnabled->GetValue()) + wxT(", ")
+ wxT("'") + DateToAnsiStr(calStart->GetValue() + timStart->GetValue()) + wxT("'");
if (calEnd->GetValue().IsValid())
sql += wxT(", '") + DateToAnsiStr(calEnd->GetValue() + timEnd->GetValue()) + wxT("'");
else
sql += wxT(", NULL");
sql += wxT(");\n");
}
return sql;
}
void pgUser::ShowTreeDetail(ctlTree *browser, frmMain *form, ctlListView *properties, ctlSQLBox *sqlPane)
{
if (!expandedKids)
{
expandedKids = true;
pgSet *set = GetServer()->ExecuteSet(wxT("SELECT groname, grolist FROM pg_group ORDER BY groname"));
if (set)
{
while (!set->Eof())
{
wxString groupName = set->GetVal(wxT("groname"));
wxString str = set->GetVal(wxT("grolist"));
if (!str.IsNull())
{
wxStringTokenizer ids(str.Mid(1, str.Length() - 2), wxT(","));
while (ids.HasMoreTokens())
{
if (StrToLong(ids.GetNextToken()) == GetUserId())
{
groupsIn.Add(groupName);
break;
}
}
}
set->MoveNext();
}
delete set;
}
}
if (properties)
{
CreateListColumns(properties);
properties->AppendItem(_("Name"), GetName());
properties->AppendItem(_("User ID"), GetUserId());
properties->AppendItem(_("Account expires"), DateToAnsiStr(GetAccountExpires()));
properties->AppendItem(_("Superuser?"), BoolToYesNo(GetSuperuser()));
properties->AppendItem(_("Create databases?"), BoolToYesNo(GetCreateDatabase()));
properties->AppendItem(_("Update catalogs?"), BoolToYesNo(GetUpdateCatalog()));
wxString groupList;
size_t index;
for (index = 0 ; index < groupsIn.GetCount() ; index++)
{
if (!groupList.IsEmpty())
groupList += wxT(", ");
groupList += groupsIn.Item(index);
}
properties->AppendItem(_("Member of"), groupList);
for (index = 0; index < configList.GetCount() ; index++)
{
wxString item = configList.Item(index);
properties->AppendItem(item.BeforeFirst('='), item.AfterFirst('='));
}
}
}
wxString pgUser::GetSql(ctlTree *browser)
{
if (sql.IsNull())
{
sql = wxT("-- User: "******"\n\n")
+ wxT("-- DROP USER ") + GetQuotedFullIdentifier() + wxT(";")
+ wxT("\n\nCREATE USER ") + GetQuotedIdentifier()
+ wxT("\n WITH SYSID ") + NumToStr(userId);
if (GetPassword() != wxT("********"))
AppendIfFilled(sql, wxT("\n ENCRYPTED PASSWORD "), qtDbString(GetPassword()));
sql += wxT("\n ");
if (GetCreateDatabase()) sql += wxT(" CREATEDB");
else sql += wxT(" NOCREATEDB");
if (GetUpdateCatalog()) sql += wxT(" CREATEUSER");
else sql += wxT(" NOCREATEUSER");
if (GetAccountExpires().IsValid())
AppendIfFilled(sql, wxT(" VALID UNTIL "), qtDbString(DateToAnsiStr(GetAccountExpires())));
sql += 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 USER ") + GetQuotedIdentifier()
+ wxT(" SET ") + configList.Item(index).BeforeFirst('=') + wxT("='") + configList.Item(index).AfterFirst('=') + wxT("';\n");
else
sql += wxT("ALTER USER ") + GetQuotedIdentifier()
+ wxT(" SET ") + configList.Item(index).BeforeFirst('=') + wxT("=") + configList.Item(index).AfterFirst('=') + wxT(";\n");
}
for (index = 0 ; index < groupsIn.GetCount() ; index++)
sql += wxT("ALTER GROUP ") + qtIdent(groupsIn.Item(index))
+ wxT(" ADD USER ") + GetQuotedIdentifier() + wxT(";\n");
}
return sql;
}
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;
}
wxString dlgRole::GetSql()
{
int pos;
wxString sql;
wxString name = GetName();
wxString passwd = txtPasswd->GetValue();
bool createDB = chkCreateDB->GetValue(),
createRole = chkCreateRole->GetValue(),
superuser = chkSuperuser->GetValue(),
inherits = chkInherits->GetValue(),
canLogin = chkCanLogin->GetValue(),
replication = chkReplication->GetValue();
if (role)
{
// Edit Mode
AppendNameChange(sql, wxT("ROLE ") + role->GetQuotedFullIdentifier());
wxString options;
if (canLogin != role->GetCanLogin())
{
if (canLogin)
options = wxT(" LOGIN");
else
options = wxT(" NOLOGIN");
}
if (canLogin && !passwd.IsEmpty())
options += wxT(" ENCRYPTED PASSWORD ") + qtDbString(connection->EncryptPassword(name, passwd));
if (createDB != role->GetCreateDatabase() || createRole != role->GetCreateRole()
|| superuser != role->GetSuperuser() || inherits != role->GetInherits()
|| replication != role->GetReplication())
{
options += wxT("\n ");
if (superuser != role->GetSuperuser())
{
if (superuser)
options += wxT(" SUPERUSER");
else
options += wxT(" NOSUPERUSER");
}
if (inherits != role->GetInherits())
{
if (inherits)
options += wxT(" INHERIT");
else
options += wxT(" NOINHERIT");
}
if (createDB != role->GetCreateDatabase())
{
if (createDB)
options += wxT(" CREATEDB");
else
options += wxT(" NOCREATEDB");
}
if (createRole != role->GetCreateRole())
{
if (createRole)
options += wxT(" CREATEROLE");
else
options += wxT(" NOCREATEROLE");
}
if (connection->BackendMinimumVersion(9, 1))
{
if (replication != role->GetReplication())
{
if (replication)
options += wxT(" REPLICATION");
else
options += wxT(" NOREPLICATION");
}
}
}
if (!datValidUntil->GetValue().IsValid() || DateToStr(datValidUntil->GetValue() + timValidUntil->GetValue()) != DateToStr(role->GetAccountExpires()))
{
if (datValidUntil->GetValue().IsValid())
options += wxT("\n VALID UNTIL ") + qtDbString(DateToAnsiStr(datValidUntil->GetValue() + timValidUntil->GetValue()));
else if (!role->GetIsValidInfinity() && role->GetAccountExpires().GetValue() != -1)
options += wxT("\n VALID UNTIL 'infinity'");
}
if (txtConnectionLimit->GetValue().Length() == 0)
{
if (role->GetConnectionLimit() != -1)
{
options += wxT(" CONNECTION LIMIT -1");
}
}
else
{
if (txtConnectionLimit->GetValue() != NumToStr(role->GetConnectionLimit()))
{
options += wxT(" CONNECTION LIMIT ") + txtConnectionLimit->GetValue();
}
}
if (!options.IsNull())
sql += wxT("ALTER ROLE ") + qtIdent(name) + options + wxT(";\n");
if (!connection->BackendMinimumVersion(9, 5) &&
chkUpdateCat->GetValue() != role->GetUpdateCatalog())
{
if (!connection->HasPrivilege(wxT("Table"), wxT("pg_authid"), wxT("update")))
sql += wxT(" -- Can't update 'UpdateCatalog privilege: can't write to pg_authid.\n")
wxT("-- ");
sql += wxT("UPDATE pg_authid SET rolcatupdate=") + BoolToStr(chkUpdateCat->GetValue())
+ wxT(" WHERE OID=") + role->GetOidStr() + wxT(";\n");
}
int cnt = lbRolesIn->GetCount();
wxArrayString tmpRoles = role->GetRolesIn();
// check for added roles
for (pos = 0 ; pos < cnt ; pos++)
{
wxString roleName = lbRolesIn->GetString(pos);
int index = tmpRoles.Index(roleName);
if (index >= 0)
{
// role membership unchanged
tmpRoles.RemoveAt(index);
}
else
{
bool admin = false;
if (roleName.Right(PGROLE_ADMINOPTION_LEN) == PGROLE_ADMINOPTION)
{
admin = true;
roleName = roleName.Left(roleName.Length() - PGROLE_ADMINOPTION_LEN);
}
else
{
// new role membership without admin option
index = tmpRoles.Index(roleName + PGROLE_ADMINOPTION);
if (index >= 0)
{
// old membership with admin option
tmpRoles.RemoveAt(index);
sql += wxT("REVOKE ADMIN OPTION FOR ") + qtIdent(roleName)
+ wxT(" FROM ") + qtIdent(name) + wxT(";\n");
continue;
}
}
index = tmpRoles.Index(roleName);
if (index >= 0)
{
// admin option added to existing membership
tmpRoles.RemoveAt(index);
}
sql += wxT("GRANT ") + qtIdent(roleName)
+ wxT(" TO ") + qtIdent(name);
if (admin)
sql += wxT(" WITH ADMIN OPTION");
sql += wxT(";\n");
}
}
// check for removed roles
for (pos = 0 ; pos < (int)tmpRoles.GetCount() ; pos++)
{
sql += wxT("REVOKE ") + qtIdent(tmpRoles.Item(pos))
+ wxT(" FROM ") + qtIdent(name) + wxT(";\n");
}
}
else
{
// Create Mode
sql = wxT(
"CREATE ROLE ") + qtIdent(name);
if (canLogin)
{
sql += wxT(" LOGIN");
if (!passwd.IsEmpty())
sql += wxT(" ENCRYPTED PASSWORD ") + qtDbString(connection->EncryptPassword(name, passwd));
}
if (createDB || createRole || !inherits || superuser)
sql += wxT("\n ");
if (superuser)
sql += wxT(" SUPERUSER");
if (!inherits)
sql += wxT(" NOINHERIT");
if (createDB)
sql += wxT(" CREATEDB");
if (createRole)
sql += wxT(" CREATEROLE");
if (connection->BackendMinimumVersion(9, 1))
{
if (replication)
sql += wxT(" REPLICATION");
}
if (datValidUntil->GetValue().IsValid())
sql += wxT("\n VALID UNTIL ") + qtDbString(DateToAnsiStr(datValidUntil->GetValue() + timValidUntil->GetValue()));
else
sql += wxT("\n VALID UNTIL 'infinity'");
if (txtConnectionLimit->GetValue().Length() > 0)
{
sql += wxT(" CONNECTION LIMIT ") + txtConnectionLimit->GetValue();
}
int cnt = lbRolesIn->GetCount();
wxString grants;
if (cnt)
{
wxString roleName;
for (pos = 0 ; pos < cnt ; pos++)
{
bool admin = false;
roleName = lbRolesIn->GetString(pos);
if (roleName.Right(PGROLE_ADMINOPTION_LEN) == PGROLE_ADMINOPTION)
{
roleName = roleName.Left(roleName.Length() - PGROLE_ADMINOPTION_LEN);
admin = true;
}
grants += wxT("GRANT ") + qtIdent(roleName)
+ wxT(" TO ") + qtIdent(name);
if (admin)
grants += wxT(" WITH ADMIN OPTION;\n");
else
grants += wxT(";\n");
}
}
sql += wxT(";\n") + grants;
if (superuser && !chkUpdateCat->GetValue() &&
!connection->BackendMinimumVersion(9, 5))
sql += wxT("UPDATE pg_authid SET rolcatupdate=false WHERE rolname=") + qtDbString(name) + wxT(";\n");
}
wxArrayString vars;
wxString dbname;
wxString parameter;
wxString value;
size_t index;
if (role)
{
for (index = 0 ; index < role->GetVariables().GetCount() ; index++)
vars.Add(role->GetVariables().Item(index));
}
int cnt = lstVariables->GetItemCount();
// check for changed or added vars
for (pos = 0 ; pos < cnt ; pos++)
{
wxString newDb = 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)
{
dbname = vars.Item(index);
parameter = vars.Item(index + 1);
value = vars.Item(index + 2);
if (newDb == dbname && newVar == parameter)
{
oldVal = value;
vars.RemoveAt(index);
vars.RemoveAt(index);
vars.RemoveAt(index);
break;
}
}
if (oldVal != newVal)
{
if (newDb.Length() == 0)
sql += wxT("ALTER ROLE ") + qtIdent(name);
else
sql += wxT("ALTER ROLE ") + qtIdent(name) + wxT(" IN DATABASE ") + newDb;
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)
{
dbname = vars.Item(pos);
parameter = vars.Item(pos + 1);
value = vars.Item(pos + 2);
if (dbname.Length() == 0)
{
sql += wxT("ALTER ROLE ") + qtIdent(name)
+ wxT(" RESET ") + parameter
+ wxT(";\n");
}
else
{
sql += wxT("ALTER ROLE ") + qtIdent(name) + wxT(" IN DATABASE ") + dbname
+ wxT(" RESET ") + parameter + wxT(";\n");
}
}
AppendComment(sql, wxT("ROLE"), 0, role);
if (seclabelPage && connection->BackendMinimumVersion(9, 2))
sql += seclabelPage->GetSqlForSecLabels(wxT("ROLE"), qtIdent(name));
return sql;
}
wxString dlgSchedule::GetUpdateSql()
{
wxString sql, name;
name = GetName();
if (schedule)
{
// edit mode
wxString vars;
if (name != schedule->GetName())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscname = ") + qtDbString(name));
}
if (txtComment->GetValue() != schedule->GetComment())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscdesc = ") + qtDbString(txtComment->GetValue()));
}
if ((!chkEnabled->IsChecked() && schedule->GetEnabled()) || (chkEnabled->IsChecked() && !schedule->GetEnabled()))
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscenabled = ") + BoolToStr(chkEnabled->IsChecked()));
}
if (calStart->GetValue() + timStart->GetValue() != schedule->GetStart())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscstart = '") + DateToAnsiStr(calStart->GetValue() + timStart->GetValue()) + wxT("'"));
}
if (calEnd->GetValue().IsValid())
{
if (schedule->GetEnd().IsValid())
{
if (calEnd->GetValue() + timEnd->GetValue() != schedule->GetEnd())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscend = '") + DateToAnsiStr(calEnd->GetValue() + timEnd->GetValue()) + wxT("'"));
}
}
else
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscend = '") + DateToAnsiStr(calEnd->GetValue() + wxTimeSpan()) + wxT("'"));
}
}
else
{
if (schedule->GetEnd().IsValid())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscend = NULL"));
}
}
if (ChkListBox2StrArray(chkMinutes) != schedule->GetMinutes())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscminutes = '") + ChkListBox2PgArray(chkMinutes) + wxT("'"));
}
if (ChkListBox2StrArray(chkHours) != schedule->GetHours())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jschours = '") + ChkListBox2PgArray(chkHours) + wxT("'"));
}
if (ChkListBox2StrArray(chkWeekdays) != schedule->GetWeekdays())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscweekdays = '") + ChkListBox2PgArray(chkWeekdays) + wxT("'"));
}
if (ChkListBox2StrArray(chkMonthdays) != schedule->GetMonthdays())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscmonthdays = '") + ChkListBox2PgArray(chkMonthdays) + wxT("'"));
}
if (ChkListBox2StrArray(chkMonths) != schedule->GetMonths())
{
if (!vars.IsEmpty())
vars.Append(wxT(", "));
vars.Append(wxT("jscmonths = '") + ChkListBox2PgArray(chkMonths) + wxT("'"));
}
if (!vars.IsEmpty())
sql = wxT("UPDATE pgagent.pga_schedule SET ") + vars + wxT("\n")
wxT(" WHERE jscid=") + NumToStr(recId) + wxT(";\n");
}
else
{
// create mode
// Handled by GetInsertSQL
}
unsigned int x = 0;
int y = 0;
wxDateTime tmpDateTime;
wxString newDate, newTime;
// Remove old exceptions
for (x = 0; x < deleteExceptions.Count(); x++)
{
sql += wxT("DELETE FROM pgagent.pga_exception\n WHERE jexid = ") + deleteExceptions[x] + wxT(";\n");
}
// Update dirty exceptions
for (y = 0; y < lstExceptions->GetItemCount(); y++)
{
if (lstExceptions->GetText(y, 2) == BoolToStr(true) &&
lstExceptions->GetText(y, 3) != wxEmptyString)
{
if (lstExceptions->GetText(y, 0) == _("<any>"))
newDate = wxT("null");
else
{
tmpDateTime.ParseFormat(lstExceptions->GetText(y, 0), wxT("%x"));
newDate = wxT("'") + tmpDateTime.FormatISODate() + wxT("'");
}
if (lstExceptions->GetText(y, 1) == _("<any>"))
newTime = wxT("null");
else
{
tmpDateTime.ParseTime(lstExceptions->GetText(y, 1));
newTime = wxT("'") + tmpDateTime.FormatISOTime() + wxT("'");
}
sql += wxT("UPDATE pgagent.pga_exception SET jexdate = ") + newDate +
wxT(", jextime = ") + newTime + wxT("\n WHERE jexid = ") +
lstExceptions->GetText(y, 3) + wxT(";\n");
}
}
// Insert new exceptions
for (y = 0; y < lstExceptions->GetItemCount(); y++)
{
if (lstExceptions->GetText(y, 2) == wxEmptyString &&
lstExceptions->GetText(y, 3) == wxEmptyString)
{
if (lstExceptions->GetText(y, 0) == _("<any>"))
newDate = wxT("null");
else
{
tmpDateTime.ParseFormat(lstExceptions->GetText(y, 0), wxT("%x"));
newDate = wxT("'") + tmpDateTime.FormatISODate() + wxT("'");
}
if (lstExceptions->GetText(y, 1) == _("<any>"))
newTime = wxT("null");
else
{
tmpDateTime.ParseTime(lstExceptions->GetText(y, 1));
newTime = wxT("'") + tmpDateTime.FormatISOTime() + wxT("'");
}
sql += wxT("INSERT INTO pgagent.pga_exception (jexscid, jexdate, jextime)\n VALUES (")
+ NumToStr(recId) + wxT(", ") + newDate + wxT(", ") + newTime + wxT(");\n");
}
}
return sql;
}
wxString dlgUser::GetSql()
{
int pos;
wxString sql;
wxString name=GetName();
wxString passwd=txtPasswd->GetValue();
bool createDB=chkCreateDB->GetValue(),
createUser=chkCreateUser->GetValue();
if (user)
{
// Edit Mode
AppendNameChange(sql);
wxString options;
if (!passwd.IsEmpty())
options += wxT(" ENCRYPTED PASSWORD ") + qtDbString(connection->EncryptPassword(name, passwd));
if (createDB != user->GetCreateDatabase() || createUser != user->GetSuperuser())
options += wxT("\n ");
if (createDB != user->GetCreateDatabase())
{
if (createDB)
options += wxT(" CREATEDB");
else
options += wxT(" NOCREATEDB");
}
if (createUser != user->GetSuperuser())
{
if (createUser)
options += wxT(" CREATEUSER");
else
options += wxT(" NOCREATEUSER");
}
if (DateToStr(datValidUntil->GetValue()) != DateToStr(user->GetAccountExpires()))
{
if (datValidUntil->GetValue().IsValid())
options += wxT("\n VALID UNTIL ") + qtDbString(DateToAnsiStr(datValidUntil->GetValue() + timValidUntil->GetValue()));
else
options += wxT("\n VALID UNTIL 'infinity'");
}
if (!options.IsNull())
sql += wxT("ALTER USER ") + qtIdent(name) + options + wxT(";\n");
int cnt=lbGroupsIn->GetCount();
wxArrayString tmpGroups=user->GetGroupsIn();
// check for added groups
for (pos=0 ; pos < cnt ; pos++)
{
wxString groupName=lbGroupsIn->GetString(pos);
int index=tmpGroups.Index(groupName);
if (index >= 0)
tmpGroups.RemoveAt(index);
else
sql += wxT("ALTER GROUP ") + qtIdent(groupName)
+ wxT(" ADD USER ") + qtIdent(name) + wxT(";\n");
}
// check for removed groups
for (pos=0 ; pos < (int)tmpGroups.GetCount() ; pos++)
{
sql += wxT("ALTER GROUP ") + qtIdent(tmpGroups.Item(pos))
+ wxT(" DROP USER ") + qtIdent(name) + wxT(";\n");
}
}
else
{
// Create Mode
long id=StrToLong(txtID->GetValue());
sql = wxT(
"CREATE USER ") + qtIdent(name);
if (id)
sql += wxT("\n WITH SYSID ") + NumToStr(id);
if (!passwd.IsEmpty())
sql += wxT(" ENCRYPTED PASSWORD ") + qtDbString(connection->EncryptPassword(name, passwd));
if (createDB || createUser)
sql += wxT("\n ");
if (createDB)
sql += wxT(" CREATEDB");
if (createUser)
sql += wxT(" CREATEUSER");
if (datValidUntil->GetValue().IsValid())
sql += wxT("\n VALID UNTIL ") + qtDbString(DateToAnsiStr(datValidUntil->GetValue() + timValidUntil->GetValue()));
else
sql += wxT("\n VALID UNTIL 'infinity'");
sql += wxT(";\n");
int cnt = lbGroupsIn->GetCount();
for (pos=0 ; pos < cnt ; pos++)
sql += wxT("ALTER GROUP ") + qtIdent(lbGroupsIn->GetString(pos))
+ wxT(" ADD USER ") + qtIdent(name) + wxT(";\n");
}
wxArrayString vars;
size_t index;
if (user)
{
for (index = 0 ; index < user->GetConfigList().GetCount() ; index++)
vars.Add(user->GetConfigList().Item(index));
}
int cnt=lstVariables->GetItemCount();
// 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)
{
if (newVar != wxT("search_path") && newVar != wxT("temp_tablespaces"))
sql += wxT("ALTER USER ") + qtIdent(name)
+ wxT(" SET ") + newVar
+ wxT("='") + newVal
+ wxT("';\n");
else
sql += wxT("ALTER USER ") + qtIdent(name)
+ wxT(" SET ") + newVar
+ wxT("=") + newVal
+ wxT(";\n");
}
}
// check for removed vars
for (pos=0 ; pos < (int)vars.GetCount() ; pos++)
{
sql += wxT("ALTER USER ") + qtIdent(name)
+ wxT(" RESET ") + vars.Item(pos).BeforeFirst('=')
+ wxT(";\n");
}
return sql;
}
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));
}
}
}
}
}