SF.net SVN: postfixadmin:[730] trunk
Brought to you by:
christian_boltz,
gingerdog
Menu
▾
▴
SF.net SVN: postfixadmin:[730] trunk
|
From: <chr...@us...> - 2009-10-18 22:43:22
|
Revision: 730
http://postfixadmin.svn.sourceforge.net/postfixadmin/?rev=730&view=rev
Author: christian_boltz
Date: 2009-10-18 22:43:13 +0000 (Sun, 18 Oct 2009)
Log Message:
-----------
support for displaying quota (dovecot 1.1 and 1.2 format)
upgrade.php
- create quota and quota2 table (upgrade_729)
- create the triggers required by dovecot (upgrade_730_pgsql)
list-virtual.php
- updated to work with both quota tables
Patch by Varren Volz, https://sourceforge.net/tracker/?func=detail&aid=2867629&group_id=191583&atid=937966
- changed query for 1.1 quota table to
WHERE [...] AND ( $table_quota.path='quota/storage' OR $table_quota.path IS NULL )
This fixes https://sourceforge.net/tracker/?func=detail&aid=2794247&group_id=191583&atid=937964
(users not shown when initial email is not sent)
config.inc.php, functions.php
- new config option $CONF['new_quota_table'] (YES means dovecot 1.2 format)
- set variables for new quota2 table
DOCUMENTS/DOVECOT.txt
- added note that quota table is automatically created
- added note about different quota tables for dovecot 1.0/1.1 and >= 1.2
Modified Paths:
--------------
trunk/DOCUMENTS/DOVECOT.txt
trunk/config.inc.php
trunk/functions.inc.php
trunk/list-virtual.php
trunk/upgrade.php
Modified: trunk/DOCUMENTS/DOVECOT.txt
===================================================================
--- trunk/DOCUMENTS/DOVECOT.txt 2009-10-18 13:29:35 UTC (rev 729)
+++ trunk/DOCUMENTS/DOVECOT.txt 2009-10-18 22:43:13 UTC (rev 730)
@@ -117,16 +117,15 @@
Create database in Mysql:
+(This is automatically done by postfixadmin's setup.php)
-create table quota (
- username varchar(255) /*!40100 CHARACTER SET latin1 */ not null,
- path varchar(100) /*!40100 CHARACTER SET latin1 */ not null,
- current integer,
- primary key (username, path)
-) TYPE=MyISAM ;
-
-
Enable quota support in Postfixadmin config.inc.php:
$CONF['used_quotas'] = 'YES';
$CONF['quota'] = 'YES';
+
+Note: The above text describes the configuration for dovecot 1.0 & 1.1 quota table format.
+
+If you use dovecot 1.2 or newer,
+- use the 'quota2' table (also created by setup.php)
+- set $CONF['new_quota_table'] = 'YES'
Modified: trunk/config.inc.php
===================================================================
--- trunk/config.inc.php 2009-10-18 13:29:35 UTC (rev 729)
+++ trunk/config.inc.php 2009-10-18 22:43:13 UTC (rev 730)
@@ -74,6 +74,7 @@
'vacation' => 'vacation',
'vacation_notification' => 'vacation_notification',
'quota' => 'quota',
+ 'quota2' => 'quota2',
);
// Site Admin
@@ -362,8 +363,12 @@
// See: DOCUMENTATION/DOVECOT.txt
// http://wiki.dovecot.org/Quota/Dict
//
-// $CONF['used_quotas'] = 'YES';
+$CONF['used_quotas'] = 'NO';
+// if you use dovecot >= 1.2, set this to yes.
+// Note about dovecot config: table "quota" is for 1.0 & 1.1, table "quota2" is for dovecot 1.2 and newer
+$CONF['new_quota_table'] = 'NO';
+
//
// Normally, the TCP port number does not have to be specified.
// $CONF['create_mailbox_subdirs_hostport']=143;
Modified: trunk/functions.inc.php
===================================================================
--- trunk/functions.inc.php 2009-10-18 13:29:35 UTC (rev 729)
+++ trunk/functions.inc.php 2009-10-18 22:43:13 UTC (rev 730)
@@ -2352,4 +2352,5 @@
$table_vacation = table_by_key ('vacation');
$table_vacation_notification = table_by_key('vacation_notification');
$table_quota = table_by_key ('quota');
+$table_quota2 = table_by_key ('quota2');
/* vim: set expandtab softtabstop=4 tabstop=4 shiftwidth=4: */
Modified: trunk/list-virtual.php
===================================================================
--- trunk/list-virtual.php 2009-10-18 13:29:35 UTC (rev 729)
+++ trunk/list-virtual.php 2009-10-18 22:43:13 UTC (rev 730)
@@ -158,29 +158,60 @@
}
}
+# TODO: reduce number of different queries by not depending on too much config options
+# (it probably won't hurt to include a field in the resultset that is not displayed later)
if ($CONF['vacation_control_admin'] == 'YES')
{
if (boolconf('used_quotas'))
- $query = "SELECT $table_mailbox.*, $table_vacation.active AS v_active, $table_quota.current FROM $table_mailbox
- LEFT JOIN $table_vacation ON $table_mailbox.username=$table_vacation.email
- LEFT JOIN $table_quota ON $table_mailbox.username=$table_quota.username
- WHERE $table_mailbox.domain='$fDomain'
- ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";
-// AND $table_quota.path='quota/storage'
- else
+ {
+ if (boolconf('new_quota_table'))
+ {
+ $query = "SELECT $table_mailbox.*, $table_vacation.active AS v_active, $table_quota2.bytes FROM $table_mailbox
+ LEFT JOIN $table_vacation ON $table_mailbox.username=$table_vacation.email
+ LEFT JOIN $table_quota2 ON $table_mailbox.username=$table_quota2.username
+ WHERE $table_mailbox.domain='$fDomain'
+ ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";
+ }
+ else
+ {
+ $query = "SELECT $table_mailbox.*, $table_vacation.active AS v_active, $table_quota.current FROM $table_mailbox
+ LEFT JOIN $table_vacation ON $table_mailbox.username=$table_vacation.email
+ LEFT JOIN $table_quota ON $table_mailbox.username=$table_quota.username
+ WHERE $table_mailbox.domain='$fDomain' AND
+ ( $table_quota.path='quota/storage' OR $table_quota.path IS NULL )
+ ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";
+ }
+ }
+ else # $CONF[used_quotas] = NO
+ {
$query = "SELECT $table_mailbox.*, $table_vacation.active AS v_active FROM $table_mailbox
LEFT JOIN $table_vacation ON $table_mailbox.username=$table_vacation.email
WHERE $table_mailbox.domain='$fDomain' ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";
+ }
}
-else
+else # $CONF['vacation_control_admin'] == 'NO'
{
-
- $query = "SELECT * FROM $table_mailbox WHERE domain='$fDomain' ORDER BY username LIMIT $fDisplay, $page_size";
- if ('pgsql'==$CONF['database_type'])
+ if (boolconf('used_quotas'))
{
- $query = "SELECT *,extract(epoch from created) as uts_created,extract(epoch from modified) as uts_modified FROM $table_mailbox WHERE domain='$fDomain' ORDER BY username LIMIT $page_size OFFSET $fDisplay";
+ if (boolconf('new_quota_table'))
+ {
+ $query = "SELECT $table_mailbox.*, $table_quota2.bytes as current FROM $table_mailbox
+ LEFT JOIN $table_quota2 ON $table_mailbox.username=$table_quota2.username
+ WHERE $table_mailbox.domain='$fDomain' ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";
+ }
+ else
+ {
+ $query = "SELECT $table_mailbox.*, $table_quota.current FROM $table_mailbox
+ LEFT JOIN $table_quota ON $table_mailbox.username=$table_quota.username
+ WHERE $table_mailbox.domain='$fDomain' AND
+ ( $table_quota.path='quota/storage' OR $table_quota.path IS NULL )
+ ORDER BY $table_mailbox.username LIMIT $page_size OFFSET $fDisplay";
+ }
+ }
+ else # $CONF[used_quotas] = NO
+ {
+ $query = "SELECT * FROM $table_mailbox WHERE domain='$fDomain' ORDER BY username LIMIT $page_size OFFSET $fDisplay";
}
-
}
$result = db_query ($query);
if ($result['rows'] > 0)
Modified: trunk/upgrade.php
===================================================================
--- trunk/upgrade.php 2009-10-18 13:29:35 UTC (rev 729)
+++ trunk/upgrade.php 2009-10-18 22:43:13 UTC (rev 730)
@@ -171,6 +171,7 @@
'{RENAME_COLUMN}' => 'CHANGE COLUMN',
'{MYISAM}' => 'ENGINE=MyISAM',
'{INNODB}' => 'ENGINE=InnoDB',
+ '{BIGINT}' => 'bigint',
);
$sql = "$sql $attach_mysql";
@@ -187,6 +188,7 @@
'{RENAME_COLUMN}' => 'ALTER COLUMN', # PgSQL : ALTER TABLE x RENAME x TO y
'{MYISAM}' => '',
'{INNODB}' => '',
+ '{BIGINT}' => 'bigint',
'int(1)' => 'int',
'int(10)' => 'int',
'int(11)' => 'int',
@@ -1086,3 +1088,94 @@
db_query_parsed(_add_index('mailbox', 'domain', 'domain'));
db_query_parsed(_add_index('alias', 'domain', 'domain'));
}
+
+function upgrade_729() {
+ $table_quota = table_by_key('quota');
+ $table_quota2 = table_by_key('quota2');
+
+ # table for dovecot v1.0 & 1.1
+ db_query_parsed("
+ CREATE TABLE {IF_NOT_EXISTS} $table_quota (
+ username VARCHAR(255) {LATIN1} NOT NULL,
+ path VARCHAR(100) {LATIN1} NOT NULL,
+ current {BIGINT},
+ PRIMARY KEY (username, path)
+ ) {MYISAM} ;
+ ");
+
+ # table for dovecot >= 1.2
+ db_query_parsed("
+ CREATE TABLE {IF_NOT_EXISTS} $table_quota2 (
+ username VARCHAR(100) {LATIN1} NOT NULL,
+ bytes {BIGINT} NOT NULL DEFAULT 0,
+ messages integer NOT NULL DEFAULT 0,
+ PRIMARY KEY (username)
+ ) {MYISAM} ;
+ ");
+}
+
+function upgrade_730_pgsql() {
+ $table_quota = table_by_key('quota');
+ $table_quota2 = table_by_key('quota2');
+
+ # trigger for dovecot v1.0 & 1.1 quota table
+ # taken from http://wiki.dovecot.org/Quota/Dict
+ db_query_parsed("
+ CREATE OR REPLACE FUNCTION merge_quota() RETURNS TRIGGER AS \$merge_quota\$
+ BEGIN
+ UPDATE $table_quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path;
+ IF found THEN
+ RETURN NULL;
+ ELSE
+ RETURN NEW;
+ END IF;
+ END;
+ \$merge_quota\$ LANGUAGE plpgsql;
+ ");
+ db_query_parsed("
+ CREATE TRIGGER mergequota BEFORE INSERT ON $table_quota FOR EACH ROW EXECUTE PROCEDURE merge_quota();
+ ");
+
+ # trigger for dovecot >= 1.2 quota table
+ # taken from http://wiki.dovecot.org/Quota/Dict, table/trigger name changed to quota2 naming
+ db_query_parsed("
+ CREATE OR REPLACE FUNCTION merge_quota2() RETURNS TRIGGER AS \$\$
+ BEGIN
+ IF NEW.messages < 0 OR NEW.messages IS NULL THEN
+ -- ugly kludge: we came here from this function, really do try to insert
+ IF NEW.messages IS NULL THEN
+ NEW.messages = 0;
+ ELSE
+ NEW.messages = -NEW.messages;
+ END IF;
+ return NEW;
+ END IF;
+
+ LOOP
+ UPDATE $table_quota2 SET bytes = bytes + NEW.bytes,
+ messages = messages + NEW.messages
+ WHERE username = NEW.username;
+ IF found THEN
+ RETURN NULL;
+ END IF;
+
+ BEGIN
+ IF NEW.messages = 0 THEN
+ INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username);
+ ELSE
+ INSERT INTO $table_quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username);
+ END IF;
+ return NULL;
+ EXCEPTION WHEN unique_violation THEN
+ -- someone just inserted the record, update it
+ END;
+ END LOOP;
+ END;
+ \$\$ LANGUAGE plpgsql;
+");
+
+ db_query_parsed("
+ CREATE TRIGGER mergequota2 BEFORE INSERT ON $table_quota2
+ FOR EACH ROW EXECUTE PROCEDURE merge_quota2();
+ ");
+}
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
