Query * Mailbox::create( Transaction * t, User * owner ) { Query * q; if ( deleted() ) { q = new Query( "update mailboxes " "set deleted='f',owner=$2,first_recent=uidnext,flag=$3 " "where id=$1", 0 ); q->bind( 1, id() ); } else if ( id() == 0 ) { q = new Query( "insert into mailboxes " "(name,owner,uidnext,uidvalidity,deleted,flag) " "values ($1,$2,1,1,'f',$3)", 0 ); q->bind( 1, name() ); } else { return 0; } if ( owner ) q->bind( 2, owner->id() ); else q->bindNull( 2 ); if ( !d->flag.isEmpty() ) q->bind( 3, d->flag ); else q->bindNull( 3 ); t->enqueue( q ); // We assume that the caller has checked permissions. Mailbox * m = parent(); while ( m ) { Query * q = 0; if ( m->deleted() ) { // Should we leave it be or undelete it? } else if ( m->id() == 0 ) { q = new Query( "insert into mailboxes " "(name,owner,uidnext,uidvalidity,deleted) " "values ($1,null,1,1,'f')", 0 ); q->bind( 1, m->name() ); } // We rely on the trigger to set the ownership of the // intermediate mailboxes being created. if ( q ) t->enqueue( q ); m = m->parent(); } t->enqueue( new Query( "notify mailboxes_updated", 0 ) ); return q; }
Deliverator( Injectee * message, const UString & mailbox, const EString & user ) : q( 0 ), i( 0 ), m( message ), mbn( mailbox ), un( user ), p( 0 ), mb( 0 ) { Allocator::addEternal( this, "deliver object" ); q = new Query( "select al.mailbox, n.name as namespace, u.login " "from aliases al " "join addresses a on (al.address=a.id) " "left join users u on (al.id=u.alias) " "left join namespaces n on (u.parentspace=n.id) " "where (lower(a.localpart)=$1 and lower(a.domain)=$2) " "or (lower(u.login)=$3)", this ); if ( user.contains( '@' ) ) { int at = user.find( '@' ); q->bind( 1, user.mid( 0, at ).lower() ); q->bind( 2, user.mid( at + 1 ).lower() ); } else { q->bindNull( 1 ); q->bindNull( 2 ); } q->bind( 3, user.lower() ); q->execute(); }
void SessionInitialiser::findRecent() { if ( !d->recent ) return; Row * r = d->recent->nextRow(); if ( !r ) return; uint recent = r->getInt( "first_recent" ); List<Session>::Iterator i( d->sessions ); while ( i && i->readOnly() ) ++i; Session * s = i; if ( !s ) s = d->sessions.firstElement(); // happens if all sessions are RO if ( !s ) return; // could happen if a session violently dies if ( recent >= d->newUidnext ) return; // just to avoid the unnecessary update below while ( recent < d->newUidnext ) s->addRecent( recent++ ); if ( !d->changeRecent ) return; Query * q = new Query( "update mailboxes set first_recent=$2 " "where id=$1 and first_recent<$2", 0 ); q->bind( 1, d->mailbox->id() ); q->bind( 2, recent ); submit( q ); }
void SaslConnection::close() { Endpoint client = peer(); Connection::close(); if ( !u || logged || !client.valid() || client.protocol() == Endpoint::Unix ) return; logged = true; Query * q = new Query( "insert into connections " "(username,address,port,mechanism,authfailures," "syntaxerrors,started_at,ended_at,userid) " "values ($1,$2,$3,$4,$5,$6," "$7::interval + 'epoch'::timestamptz," "$8::interval + 'epoch'::timestamptz,$9)", 0 ); q->bind( 1, u->login() ); q->bind( 2, client.address() ); q->bind( 3, client.port() ); q->bind( 4, m ); q->bind( 5, af ); q->bind( 6, sf ); q->bind( 7, s ); q->bind( 8, (uint)time( 0 ) ); q->bind( 9, u->id() ); q->execute(); }
Query * User::remove( Transaction * t ) { Query * q = new Query( "delete from users where login=$1", 0 ); q->bind( 1, d->login ); t->enqueue( q ); return q; }
Query * Mailbox::remove( Transaction * t ) { if ( deleted() ) return 0; Query * q = new Query( "update mailboxes set deleted='t',owner=null " "where id=$1", 0 ); q->bind( 1, id() ); t->enqueue( q ); q = new Query( "delete from permissions where mailbox=$1", 0 ); q->bind( 1, id() ); t->enqueue( q ); t->enqueue( new Query( "notify mailboxes_updated", 0 ) ); return q; }
bool ManageSieveCommand::deleteScript() { if ( !d->t ) { d->name = string(); end(); d->t = new Transaction( this ); // select first, so the no() calls below work d->query = new Query( "select active from scripts " "where owner=$1 and name=$2", this ); d->query->bind( 1, d->sieve->user()->id() ); d->query->bind( 2, d->name ); d->t->enqueue( d->query ); // then delete Query * q = new Query( "delete from scripts where owner=$1 and " "name=$2 and active='f'", this ); q->bind( 1, d->sieve->user()->id() ); q->bind( 2, d->name ); d->t->enqueue( q ); if ( d->no.isEmpty() ) d->t->commit(); } if ( !d->t->done() ) return false; if ( d->t->failed() ) { no( "Couldn't delete script: " + d->t->error() ); } else { Row * r = d->query->nextRow(); if ( !r ) no( "No such script" ); else if ( r->getBoolean( "active" ) ) no( "Can't delete active script" ); else log( "Deleted script " + d->name ); } return true; }
Query Query::valueInListCondition (const QString &column, const QList<QVariant> &values) { Query query (notr ("%1 IN (%2)")); query.arg (column); query.arg (repeatString (notr ("?"), values.size (), notr (","))); foreach (const QVariant &value, values) query.bind (value); return query; }
SpoolManager::SpoolManager() : d( new SpoolManagerData ) { setLog( new Log ); Query * q = new Query( "update deliveries " "set expires_at=current_timestamp+interval '900 s' " "where expires_at<current_timestamp+interval '900 s' " "and id in " "(select delivery from delivery_recipients" " where action=$1 or action=$2)", 0 ); q->bind( 1, Recipient::Unknown ); q->bind( 2, Recipient::Delayed ); q->execute(); }
void Migration_20100216135637_add_launch_methods::up () { createTable ("launch_methods"); // Creates the id column addColumn ("launch_methods", "name" , dataTypeString ()); addColumn ("launch_methods", "short_name" , dataTypeString ()); addColumn ("launch_methods", "log_string" , dataTypeString ()); addColumn ("launch_methods", "keyboard_shortcut" , dataTypeCharacter ()); // Use a string type because SQLite does not support enums addColumn ("launch_methods", "type" , dataTypeString ()); addColumn ("launch_methods", "towplane_registration", dataTypeString ()); addColumn ("launch_methods", "person_required" , dataTypeBoolean ()); addColumn ("launch_methods", "comments" , dataTypeString ()); try { transaction (); foreach (LaunchMethod launchMethod, readConfiguredLaunchMethods ()) { // Don't use the methods of Database or LaunchMethod - they use the // current schema, we need to use the schema after this migration. std::cout << "Importing launch method: " << launchMethod.toString () << std::endl; Query query ( "INSERT INTO launch_methods" "(id,name,short_name,log_string,keyboard_shortcut,type,towplane_registration,person_required,comments)" "values (?,?,?,?,?,?,?,?,?)" ); query.bind (launchMethod.getId ()); query.bind (launchMethod.name); query.bind (launchMethod.shortName); query.bind (launchMethod.logString); query.bind (launchMethod.keyboardShortcut); query.bind (LaunchMethod::typeToDb (launchMethod.type)); query.bind (launchMethod.towplaneRegistration); query.bind (launchMethod.personRequired); query.bind (launchMethod.comments); executeQuery (query); } commit (); }
void execute() { if ( !q ) { q = new Query( "select not exists (select * from " "information_schema.table_privileges where " "privilege_type='DELETE' and table_name=" "'messages' and grantee=$1) and not exists " "(select u.usename from pg_catalog.pg_class c " "left join pg_catalog.pg_user u on " "(u.usesysid=c.relowner) where c.relname=" "'messages' and u.usename=$1) as allowed", this ); q->bind( 1, Configuration::text( Configuration::DbUser ) ); q->execute(); } if ( !q->done() ) return; Row * r = q->nextRow(); if ( q->failed() || !r || r->getBoolean( "allowed" ) == false ) { EString s( "Refusing to start because we have too many " "privileges on the messages table in secure " "mode." ); result->setError( s ); l->log( s, Log::Disaster ); if ( q->failed() ) { l->log( "Query: " + q->description(), Log::Disaster ); l->log( "Error: " + q->error(), Log::Disaster ); } } else { result->setState( Query::Completed ); } result->notify(); }
void Delete::execute() { if ( state() != Executing || !ok() ) return; if ( d->first ) { d->first = false; // We should really require DeleteMessages and Expunge only if // we know the mailbox isn't empty; but we'll know that inside // the transaction, and permitted() won't let us clean that up // if we don't have permission. So it'll have to wait until we // query permissions ourselves. requireRight( d->m, Permissions::DeleteMailbox ); requireRight( d->m, Permissions::DeleteMessages ); requireRight( d->m, Permissions::Expunge ); } if ( !permitted() ) return; if ( !transaction() ) { setTransaction( new Transaction( this ) ); Query * lock = new Query( "select * from mailboxes " "where id=$1 for update", 0 ); lock->bind( 1, d->m->id() ); transaction()->enqueue( lock ); d->messages = new Query( "select count(mm.uid)::bigint as messages " "from mailbox_messages mm " "join messages m on (mm.message=m.id) " "join mailboxes mb on (mm.mailbox=mb.id) " "where mm.mailbox=$1 and not mm.seen and " "(mm.uid>=mb.first_recent or m.idate>$2)", this ); d->messages->bind( 1, d->m->id() ); Date now; now.setCurrentTime(); d->messages->bind( 2, now.unixTime() - 20 ); transaction()->enqueue( d->messages ); transaction()->execute(); } if ( d->messages ) { if ( !d->messages->done() ) return; int64 messages = 0; Row * r = d->messages->nextRow(); if ( d->messages->failed() || !r ) error( No, "Could not determine if any messages exist" ); else messages = r->getBigint( "messages" ); if ( messages ) error( No, "Cannot delete mailbox: " + fn( messages ) + " messages exist" ); d->messages = 0; if ( ok() && d->m->remove( transaction() ) == 0 ) error( No, "Cannot delete mailbox " + d->m->name().ascii() ); Mailbox::refreshMailboxes( transaction() ); transaction()->commit(); } if ( !transaction()->done() ) return; if ( transaction()->failed() ) { error( No, "Database error: " + transaction()->error() ); return; } finish(); }
void CreateView::execute() { if ( d->name.isEmpty() ) { parseOptions(); Utf8Codec c; d->name = c.toUnicode( next() ); d->source = c.toUnicode( next() ); UString owner( c.toUnicode( next() ) ); d->selector = parseSelector( args() ); if ( !c.valid() ) error( "Argument encoding: " + c.error() ); if ( d->name.isEmpty() ) error( "No name supplied for the view." ); if ( d->source.isEmpty() ) error( "No source mailbox name supplied." ); if ( !d->selector ) error( "Invalid search expression supplied." ); database( true ); Mailbox::setup( this ); if ( !owner.isEmpty() ) { d->user = new User; d->user->setLogin( owner ); d->user->refresh( this ); } } if ( !choresDone() ) return; if ( !d->t ) { if ( d->user ) { if ( d->user->state() == User::Unverified ) return; if ( d->user->state() == User::Nonexistent ) error( "No user named " + d->user->login().utf8() ); if ( !d->name.startsWith( "/" ) ) d->name = d->user->home()->name() + "/" + d->name; if ( !d->source.startsWith( "/" ) ) d->source = d->user->home()->name() + "/" + d->source; } d->ms = Mailbox::obtain( d->source ); if ( !d->ms || d->ms->deleted() ) error( "Can't create view on " + d->source.utf8() ); d->t = new Transaction( this ); d->mv = Mailbox::obtain( d->name ); Query * q = d->mv->create( d->t, d->user ); if ( !q ) error( "Couldn't create view named " + d->name.utf8() ); q = new Query( "insert into views " "(view, selector, source, nextmodseq) values " "((select id from mailboxes where name=$1)," "$2, " "((select id from mailboxes where name=$3), " "1::bigint)", this ); q->bind( 1, d->name ); q->bind( 2, d->selector->string() ); q->bind( 3, d->ms->name() ); d->t->enqueue( q ); d->t->commit(); } if ( !d->t->done() ) return; if ( d->t->failed() ) error( "Couldn't create view" ); finish(); }
void execute() { if ( !r ) { r = new RetentionSelector( mailbox, this ); r->execute(); } if ( !t ) { t = new ::Transaction( this ); nms = new Query( "select nextmodseq from mailboxes " "where id=$1 for update", this ); nms->bind( 1, mailbox->id() ); t->enqueue( nms ); t->execute(); } if ( nms ) { if ( !r->done() ) return; if ( !nms->done() ) return; ms = nms->nextRow()->getBigint( "nextmodseq" ); nms = 0; Selector * s = new Selector; if ( r->retains() ) { Selector * n = new Selector( Selector::Not ); s->add( n ); n->add( r->retains() ); } s->add( new Selector( this->s ) ); s->simplify(); EStringList wanted; wanted.append( "mailbox" ); wanted.append( "uid" ); wanted.append( "message" ); iq = s->query( 0, mailbox, 0, this, false, &wanted, false ); int i = iq->string().find( " from " ); uint msb = s->placeHolder(); uint ub = s->placeHolder(); uint rb = s->placeHolder(); iq->setString( "insert into deleted_messages " "(mailbox,uid,message,modseq,deleted_by,reason) " + iq->string().mid( 0, i ) + ", $" + fn( msb ) +", $" + fn( ub ) + ", $" + fn( rb ) + iq->string().mid( i ) ); iq->bind( msb, ms ); iq->bind( ub, user->id() ); iq->bind( rb, "POP delete " + Scope::current()->log()->id() ); t->enqueue( iq ); t->execute(); } if ( iq ) { if ( !iq->done() ) return; if ( iq->rows() ) { // at least one message was deleted Query * q = new Query( "update mailboxes set " "nextmodseq=$1 where id=$2", this ); q->bind( 1, ms+1 ); q->bind( 2, mailbox->id() ); t->enqueue( q ); Mailbox::refreshMailboxes( t ); } iq = 0; t->commit(); } if ( !t->done() ) return; if ( t->failed() ) log( "Error deleting messages: " + t->error() ); }
bool ManageSieveCommand::setActive() { if ( !d->t ) { EString name = string(); end(); if ( !d->no.isEmpty() ) return true; d->t = new Transaction( this ); if ( name.isEmpty() ) { Query * q = new Query( "update scripts set active='f' " "where owner=$1 and active='t'", 0 ); q->bind( 1, d->sieve->user()->id() ); d->t->enqueue( q ); d->query = new Query( "select ''::text as name", this ); log( "Deactivating all scripts" ); } else { d->query = new Query( "select name from scripts " "where owner=$1 and name=$2 " "for update", this ); d->query->bind( 1, d->sieve->user()->id() ); d->query->bind( 2, name ); } d->t->enqueue( d->query ); d->t->execute(); } if ( d->query && !d->query->done() ) return false; if ( d->query ) { Row * r = d->query->nextRow(); if ( !r ) { d->t->rollback(); no( "No such script" ); return true; } d->query = 0; if ( !r->getEString( "name" ).isEmpty() ) { Query * q = new Query( "update scripts set active=(name=$2) " "where owner=$1 and " "(name=$2 or active='t')", this ); q->bind( 1, d->sieve->user()->id() ); q->bind( 2, r->getEString( "name" ) ); d->t->enqueue( q ); log( "Activating script " + r->getEString( "name" ) ); } d->t->commit(); } if ( !d->t->done() ) return false; if ( d->t->failed() ) no( "Couldn't activate script: " + d->t->error() ); return true; }