On 15/01/2024 01:10, m87562...@gmail.com wrote:
John Fawcett wrote:
On 13/01/2024 14:01, Matt wrote:
Hi,
I'm trying to setup quota with the following
layout
CREATE TABLE "domain" (
"id" INTEGER,
"name" TEXT,
"active" INTEGER DEFAULT 0,
PRIMARY KEY("id"),
UNIQUE("name")
)
CREATE TABLE "address" (
"id" INTEGER,
"localpart" TEXT NOT NULL,
"domain_id" INTEGER NOT NULL,
PRIMARY KEY("id"),
UNIQUE("localpart","domain_id")
)
CREATE TABLE "mailbox" (
"id" INTEGER,
"address_id" INTEGER,
"active" INTEGER DEFAULT 0,
"password" TEXT,
"quota_bytes" INTEGER DEFAULT 0,
PRIMARY KEY("id")
)
As I like to update the quota and I like to use a
dict/map I'm not sure if I
can update this following:
map {
pattern = priv/quota/storage
table = maibox
value_field = dummy
fields {
value_field = quota_bytes
id = mailbox_id
}
mailbox_id will be queried using password_query
using: "mailbox.id as
mailbox_id"
Using such map I hope to be able to use:
SELECT quota_bytes FROM mailbox WHERE id =
'$mailbox_id'
INSERT INTO mailbox (quota_bytes) VALUES
('$value') ON DUPLICATE KEY UPDATE
quota_bytes='$value'
Can someone help me with this ?
Hi Matt
Hi John, thank you very much for reaching out to me with your clear
explanation, I will respond below your quoted text.
as far as I know the quota plugin with the dict backend
requires you to
configure the name of your username field in the map. The
usual way to
configure it is to have a quota table like in the
documentation.
https://doc.dovecot.org/configuration_manual/quota/
quota_dict/
In your case where you don't have a username field in the
table that
stores the quotas, then depending on your database, you may
be able to
create a view that does include the username and configure
that in Dovecot.
Yes, the idea was a view from my side as well but as I try to keep
things as clean as possible I didn't mention it yet, also because -
you say later on in your reply - quota_dict is deprecated so I
already implemented "count" but was not sure about quota_clone as I
assumed I needed Redis because the manual said as "more complex
example" dict in the MySQL part so I was confused and thought to
implement Redis because of it; it seems I can put in almost any
backend that uses dict it seems.
https://doc.dovecot.org/configuration_manual/quota_clone_plugin
The quota plugin updates the values of two fields holding
the number of
messages and the number of bytes used. I noticed you don't
have the
messages field. I suggest to add it.
I know, I copied that part from the documentation when trying to find
a sane config; messages will be added but thank for you notice when
people read this thread later on. (I lots of them confused me all the
time as this was not a major one for me actually - point taken!)
https://doc.dovecot.org/configuration_manual/dict/#dict-sql
You shouldn't need to write any queries to update the
quota: Dovecot
will generate the queries.
True and that is what I like so the question remains what to do here,
a dict for Mysql with a view should do the trick I believe to
understand ?
On a final note, before you invest time in this, it may be
worthwhile
keeping an eye on the future plans
https://doc.dovecot.org/3.0/installation_guide/upgrading/
from-2.3-to-3.0/
Thanks for that! As I wasn't upgrading (yet) I could not find any
roadmap; this is what I needed!
You could use the count backend with quota plugin (which is
the
recommended backend for new installations) and then use the
quota_clone
plugin to keep your database in sync.
Indeed, but this will be a map in combination with a (SQL) dict and
the bespoken view in my DBMS ?
John
Matt
If you've already got quota working in count and you need the values available
in Mysql then the easist way to go is to use quota clone plugin. It's not
limited to redis. You can use the dict backend for quota-clone to configure
updating the quota to mysql
dict {
mysql = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
plugin {
quota_clone_dict = proxy::mysql
}
An example of the dictionary is:
map {
pattern = priv/quota/storage
table = quota
username_field = username
value_field = bytes
}
map {
pattern = priv/quota/messages
table = quota
username_field = username
value_field = messages
}
Then configure your view with name quota to have username, messages and bytes
fields taken from joins
on your three tables. I'm guessing that that type of view where only one
underlying table is going
to get updated will work with updating in Mysql thoughI didn't verify that. If
you can't get it to work
then why not take the hit and define a quota table with those fields and
mailbox_id? You can then always
retrieve the quota value by joining with mailbox (or define a view for that).
best regards
John
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
