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;
}
