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