void
msg_status_cache::db_new_mail_notif()
{
DBG_PRINTF(5, "We have NEW MAIL!");
db_cnx db;
try {
sql_stream s("SELECT ms.mail_id,ms.status,sender,sender_fullname,subject,recipients FROM mail m JOIN mail_status ms USING(mail_id) WHERE ms.mail_id>:p1", db);
s << m_max_mail_id;
mail_id_t mail_id;
int status;
int count=0;
while (!s.eos()) {
QString sender,sender_fullname,subject,recipients;
s >> mail_id >> status>> sender >> sender_fullname >> subject >> recipients;
count++;
DBG_PRINTF(5, "Seen mail_id %d with status %d", mail_id, status);
if (!(status & mail_msg::statusRead)) {
gl_pApplication->desktop_notify(tr("New mail"), QString("From: %1\nSubject: %2").arg(sender_fullname.isEmpty()?sender:sender_fullname).arg(subject));
}
update(mail_id, status);
message_port::instance()->broadcast_new_mail(mail_id);
}
if (count>0 && get_config().get_bool("fetch/auto_incorporate_new_results", false)) {
message_port::instance()->broadcast_list_refresh_request();
}
}
catch(db_excpt& p) {
DBEXCPT(p);
}
}
bool
filter_log_list::fetch(mail_id_t mail_id)
{
db_cnx db;
m_mail_id=mail_id;
try {
/* If the filter has been deleted after the filter_log entry
creation, then coalesce(e.expr_id,0) will be 0.
Also if the filter has been modified since, hit_date-e.last_update will be <0
*/
sql_stream s("SELECT f.expr_id, e.name, e.expression, to_char(hit_date,'YYYYMMDDHHMI'),coalesce(e.expr_id,0), SIGN(EXTRACT(epoch FROM (hit_date-e.last_update))) FROM filter_log f LEFT JOIN filter_expr e ON e.expr_id=f.expr_id WHERE f.mail_id=:id ORDER BY hit_date", db);
s << mail_id;
while (!s.isEmpty()) {
filter_log_entry e;
QString hit_date;
int e_id, sign;
s >> e.m_expr_id >> e.m_filter_name >> e.m_filter_expression >> hit_date >> e_id >> sign;
e.m_deleted = (e_id==0);
e.m_modified = (sign<0);
e.m_hit_date = date(hit_date);
this->append(e);
}
}
catch (db_excpt p) {
DBEXCPT(p);
return false;
}
return true;
}
// insert or update the address
bool
mail_address::store()
{
db_cnx db;
try {
if (m_id) {
sql_stream s("UPDATE addresses SET email_addr=:p1,name=:p2,notes=:p3,nickname=:p4,invalid=:p5 WHERE addr_id=:id", db);
s << m_address << m_name << m_notes << m_nickname << m_invalid;
/*
if (!m_date_last_sent.isEmpty())
s << m_date_last_sent;
else
s << sql_null();
*/
s << m_id;
}
else {
if (!db.next_seq_val("seq_addr_id", &m_id))
return false;
sql_stream s("INSERT INTO addresses(addr_id,email_addr,name,notes,nickname,invalid) VALUES (:p1,:p2,:p3,:p4,:p5, :p6)", db);
s << m_id << m_address << m_name << m_notes << m_nickname << m_invalid;
/*
if (!m_date_last_sent.isEmpty())
s << m_date_last_sent;
else
s << sql_null();
*/
}
}
catch(db_excpt& p) {
DBEXCPT(p);
return false;
}
return true;
}
bool
message_tag::store()
{
bool result=true;
try {
db_cnx db;
if (m_tag_id<=0) {
db.next_seq_val("seq_tag_id", &m_tag_id);
sql_stream s("INSERT INTO tags(tag_id,name,parent_id) VALUES (:p1,:p2,:p3)", db);
s << m_tag_id << m_name;
if (m_parent_id)
s << m_parent_id;
else
s << sql_null();
}
else {
sql_stream s("UPDATE tags SET name=:p1, parent_id=:p3 where tag_id=:p2", db);
s << m_name;
if (m_parent_id)
s << m_parent_id;
else
s << sql_null();
s << m_tag_id;
}
}
catch(db_excpt& p) {
DBEXCPT(p);
result=false;
}
return result;
}
/* Fill in the cache */
void
tags_repository::fetch()
{
db_cnx db;
try {
/* The coalesce() converting parent_id from NULL to 0 is not technically
necessary, because the sqlstream>>(int&) operator does it anyway,
it's just for being explicit. */
sql_stream s("SELECT tag_id,name,coalesce(parent_id,0) FROM tags", db);
while (!s.eof()) {
int id, parent_id;
QString name;
s >> id >> name >> parent_id;
message_tag tag(id, name);
if (parent_id)
tag.set_parent_id(parent_id);
m_tags_map[id] = tag;
}
m_tags_map_fetched=true;
}
catch(db_excpt& p) {
m_tags_map.clear();
m_tags_map_fetched=false;
DBEXCPT(p);
}
}
bool
identities::fetch(bool force/*=false*/)
{
if (!force && m_fetched)
return true;
db_cnx db;
const QString default_email = get_config().get_string("default_identity");
try {
sql_stream s("SELECT identity_id, email_addr,username,xface,signature,root_tag,restricted FROM identities ORDER BY email_addr", db);
while (!s.eos()) {
mail_identity id;
s >> id.m_identity_id;
s >> id.m_email_addr >> id.m_name >> id.m_xface >> id.m_signature
>> id.m_root_tag_id >> id.m_is_restricted;
if (id.m_email_addr.isEmpty()) {
// Ignore identities with no email to be safe. This shouldn't happen.
continue;
}
id.m_fetched=true;
id.m_orig_email_addr = id.m_email_addr;
id.m_is_default = (id.m_email_addr == default_email);
(*this)[id.m_email_addr]=id;
}
}
catch(db_excpt& p) {
DBEXCPT(p);
clear();
return false;
}
return true;
}
void
db_cnx::handle_exception(db_excpt& e)
{
if (m_alerts_enabled) {
DBEXCPT(e);
}
}
bool
notepad::save()
{
if (!m_we->document()->isModified())
return true;
const QString& t=m_we->toPlainText();
db_cnx db;
try {
sql_stream s1("SELECT 1 FROM global_notepad", db);
if (s1.eof()) {
sql_stream s2("INSERT INTO global_notepad(contents,last_modified) VALUES(:p1, now())", db);
s2 << t;
}
else {
sql_stream s2("UPDATE global_notepad SET contents=:p1,last_modified=now()", db);
s2 << t;
}
}
catch (db_excpt& p) {
DBEXCPT(p);
return false;
}
disable_save();
m_we->document()->setModified(false);
return true;
}
bool
db_cnx::next_seq_val(const char* seqName, unsigned int* id)
{
try {
QString query = QString("SELECT nextval('%1')").arg(seqName);
sql_stream s(query, *this);
s >> *id;
}
catch(db_excpt& p) {
DBEXCPT(p);
return false;
}
return true;
}
void
role_perms_edit_dialog::set_checkboxes()
{
db_ctxt dbc(true);
try {
if (m_role_oid > 0) {
db_role role(m_role_oid);
QList<db_obj_privilege> privs_for_read = db_obj_privilege::ability_privileges("read", &dbc);
m_initial_privs[priv_read] = role.has_multiple_privileges(privs_for_read, &dbc);
QList<db_obj_privilege> privs_for_update = db_obj_privilege::ability_privileges("update", &dbc);
m_initial_privs[priv_update] = role.has_multiple_privileges(privs_for_update, &dbc);
QList<db_obj_privilege> privs_for_trash = db_obj_privilege::ability_privileges("trash", &dbc);
m_initial_privs[priv_trash] = role.has_multiple_privileges(privs_for_trash, &dbc);
QList<db_obj_privilege> privs_for_delete = db_obj_privilege::ability_privileges("delete", &dbc);
m_initial_privs[priv_delete] = role.has_multiple_privileges(privs_for_delete, &dbc);
QList<db_obj_privilege> privs_for_compose = db_obj_privilege::ability_privileges("compose", &dbc);
m_initial_privs[priv_compose] = role.has_multiple_privileges(privs_for_compose, &dbc);
QList<db_obj_privilege> privs_for_basic_management = db_obj_privilege::ability_privileges("admin-level1", &dbc);
m_initial_privs[priv_basic_management] = role.has_multiple_privileges(privs_for_basic_management, &dbc);
}
else {
memset((void*)m_initial_privs, 0, sizeof(m_initial_privs));
}
m_perm_read->setChecked(m_initial_privs[priv_read]);
m_perm_update->setChecked(m_initial_privs[priv_update]);
m_perm_trash->setChecked(m_initial_privs[priv_trash]);
m_perm_delete->setChecked(m_initial_privs[priv_delete]);
m_perm_compose->setChecked(m_initial_privs[priv_compose]);
m_perm_basic_management->setChecked(m_initial_privs[priv_basic_management]);
}
catch(db_excpt& p) {
DBEXCPT (p);
}
}
bool
notepad::load()
{
db_cnx db;
try {
sql_stream s ("SELECT contents FROM global_notepad", db);
if (!s.eof()) {
QString contents;
s >> contents;
set_contents(contents);
}
}
catch (db_excpt& p) {
DBEXCPT(p);
return false;
}
return true;
}
//static
QString
identities::email_from_id(int id)
{
db_cnx db;
try {
QString res;
sql_stream s("SELECT email_addr FROM identities WHERE identity_id=:p1", db);
s << id;
if (!s.eos()) {
s >> res;
return res;
}
}
catch(db_excpt& p) {
DBEXCPT(p);
}
return QString();
}
bool
db_cnx::next_seq_val(const char* seqName, int* id)
{
try {
begin_transaction();
sql_stream update(QString("UPDATE sequence set value=value+1 where name='%1'").arg(seqName),
*this);
sql_stream select(QString("SELECT value from sequence where name='%1'").arg(seqName),
*this);
commit_transaction();
select >> *id;
-- *id;
}
catch(db_excpt& p) {
DBEXCPT(p);
return false;
}
return true;
}
bool
message_tag::remove()
{
bool result=true;
db_cnx db;
try {
DBG_PRINTF(5,"message_tag::remove(%u)\n", getId());
db.begin_transaction();
sql_stream s1("DELETE FROM mail_tags WHERE tag=:p1", db);
s1 << getId();
sql_stream s2("DELETE FROM tags WHERE tag_id=:p1", db);
s2 << getId();
db.commit_transaction();
}
catch(db_excpt& p) {
db.rollback_transaction();
DBEXCPT(p);
result=false;
}
return result;
}
db_listener::db_listener(db_cnx& db, const QString notif_name)
{
m_socket_notifier = NULL;
m_db = db.datab();
try {
/*sql_stream s1("SELECT quote_ident(:p1)", db);
s1 << notif_name;
if (!s1.isEmpty()) {
s1 >> m_notif_name;
}
if (!m_notif_name.isEmpty()) {
m_db->add_listener(this);
setup_notification();
setup_db();
}*/
}
catch (db_excpt e)
{
DBEXCPT(e);
}
}
// role_oid => list of identities that this role can access
QMap<int,bool>
role_perms_edit_dialog::accessible_identities(Oid role_oid, db_ctxt* dbc)
{
db_cnx* db = dbc->m_db;
QMap<int,bool> list;
QString query = "SELECT identity_id FROM identities_permissions WHERE role_oid=:p1";
try {
sql_stream s(query, *db);
s << role_oid;
while (!s.eos()) {
int id;
s >> id;
list.insert(id,true);
}
}
catch(db_excpt& p) {
if (dbc && dbc->propagate_exceptions)
throw p;
else
DBEXCPT(p);
}
return list;
}
/* Fill in the cache */
void
tags_repository::fetch()
{
db_cnx db;
try {
sql_stream s("SELECT tag_id,name,coalesce(parent_id,0) FROM tags", db);
while (!s.eof()) {
int id, parent_id;
QString name;
s >> id >> name >> parent_id;
message_tag tag(id, name);
if (parent_id)
tag.set_parent_id(parent_id);
m_tags_map[id] = tag;
}
m_tags_map_fetched=true;
}
catch(db_excpt& p) {
m_tags_map.clear();
m_tags_map_fetched=false;
DBEXCPT(p);
}
}
/*
type="sent" or "recv"
*/
bool
mail_address::update_last_used(const QString type)
{
db_cnx db;
try {
if (!m_id) {
DBG_PRINTF(5,"warning: m_id=0\n");
return false;
}
if (type=="sent") {
sql_stream s("UPDATE addresses SET last_sent_to=:now:, nb_sent_to=1+coalesce(nb_sent_to,0) WHERE addr_id=:id", db);
s << m_id;
}
else if (type=="recv") {
sql_stream s("UPDATE addresses SET last_recv_from=:now: WHERE addr_id=:id", db);
s << m_id;
}
}
catch(db_excpt& p) {
DBEXCPT(p);
return false;
}
return true;
}
void
user_edit_dialog::done(int r)
{
bool success = false;
if(QDialog::Accepted==r) {
// ok was pressed
if (check_valid()) {
user u;
get_user_fields(u);
db_ctxt dbc;
dbc.propagate_exceptions = true;
try {
dbc.m_db->begin_transaction();
if (m_mode==new_user) {
success = create_new_user(u, &dbc);
}
else
success = update_user(u, &dbc);
if (success)
success = update_granted_roles(u, &dbc);
dbc.m_db->commit_transaction();
}
catch(db_excpt& p) {
DBG_PRINTF(3, "exception caugth");
dbc.m_db->rollback_transaction();
success = false;
DBEXCPT (p);
}
if (success)
QDialog::done(r);
}
}
else {
QDialog::done(r);
}
}
bool
mail_identity::update_db()
{
db_cnx db;
try {
sql_stream ss("SELECT 1 FROM identities WHERE email_addr=:p1", db);
ss << m_orig_email_addr;
if (!ss.eos()) {
sql_stream su("UPDATE identities SET email_addr=:p1, username=:p2, xface=:p3, signature=:p4, restricted=:r, root_tag=nullif(:t,0) WHERE email_addr=:p5", db);
su << m_email_addr << m_name << m_xface << m_signature << m_is_restricted << m_root_tag_id;
su << m_orig_email_addr;
}
else {
sql_stream si("INSERT INTO identities(email_addr,username,xface,signature,restricted,root_tag) VALUES (:p1,:p2,:p3,:p4,:p5,nullif(:p6,0))", db);
si << m_email_addr << m_name << m_xface << m_signature << m_is_restricted << m_root_tag_id;
}
m_orig_email_addr = m_email_addr;
}
catch(db_excpt& p) {
DBEXCPT(p);
return false;
}
return true;
}
void
role_perms_edit_dialog::set_grants()
{
db_ctxt dbc(true);
struct { QCheckBox* checkbox; const char* ability; }
mail_perms[] = {
{m_perm_read, "read"},
{m_perm_update, "update"},
{m_perm_trash, "trash"},
{m_perm_delete, "delete"},
{m_perm_compose, "compose"},
{m_perm_basic_management, "admin-level1"},
};
QString rolname = m_role_name->text().trimmed();
try {
dbc.m_db->begin_transaction();
if (!m_role_oid) {
// Create the new role
user::create_db_user(rolname, QString(), false, &dbc);
m_role_oid = user::oid_db_role(rolname, true);
if (!m_role_oid) {
QMessageBox::critical(this, tr("Error"), tr("The role could not be created."));
return;
}
}
db_role role(m_role_oid);
role.fetch_properties(&dbc);
if (rolname != role.name()) {
// Rename it in the db
role.rename(rolname, &dbc);
}
for (uint iperm=0; iperm<sizeof(mail_perms)/sizeof(mail_perms[0]); iperm++) {
// Set or unset permissions to exercise ability on messages
DBG_PRINTF(5, "processing permissions for ability: %s", mail_perms[iperm].ability);
QList<db_obj_privilege> privs = db_obj_privilege::ability_privileges(mail_perms[iperm].ability, &dbc);
for (int pi=0; pi < privs.size(); pi++) {
if (mail_perms[iperm].checkbox->isChecked())
role.grant(privs.at(pi), &dbc);
else
role.revoke(privs.at(pi), &dbc);
}
}
if (dbc.m_db->datab()->has_row_level_security()) {
QList<int> list_ids; // list of checked private identities
bool ids_changed = false; // did any checkbox switch state?
for (int row=0; row < m_list_idents->count(); row++) {
QListWidgetItem* item = m_list_idents->item(row);
int iid = item->data(Qt::UserRole).toInt(); // identity_id
if ((item->checkState() == Qt::Checked) != m_a_ids.contains(iid)) // compare 2 states
ids_changed = true;
if (item->checkState() == Qt::Checked)
list_ids.append(iid);
}
if (ids_changed) {
// call set_identity_permissions(in_oid, in_identities int[], in_perms char[] = ['A']);
sql_stream s("select set_identity_permissions(:o, :t, null)", *dbc.m_db);
s << m_role_oid << list_ids;
}
}
dbc.m_db->commit_transaction();
}
catch(db_excpt& p) {
dbc.m_db->rollback_transaction();
DBEXCPT (p);
}
}
