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
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
