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,
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 ?
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
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.
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.
You shouldn't need to write any queries to update the quota: Dovecot will generate the queries.
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/
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.
John
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
On 15/01/2024 01:10, m87562378@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
On 15/01/2024 01:10, m87562378@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
participants (3)
-
John Fawcett
-
m87562378＠gmail.com
-
Matt