New SQL table for SQL backends: account_settings. Used by iRedAdmin(-Pro).

Issue #123 new
Zhang Huangbin
repo owner created an issue

New SQL table for SQL backends: account_settings. Used by iRedAdmin(-Pro).

Issue Description

It's not "easy" to update per-account settings stored in columns below, we need a easier way to update them.

domain.settings
mailbox.settings
alias.settings
maillists.settings        # `maillists` table was introduced in iRedMail-0.9.8

Format of column value is:

<key>:<value>;<key2>:<value2>;<key3>:<value3>;

For example:

pgsql> select domain,settings from domain;
 domain |                    settings                     
--------+-------------------------------------------------
 a.io   | default_groups:l1@a.io;default_user_quota:1024;

iRedAdmin is wrote in Python programming language, to read them, we need to:

  1. query sql server to get per-account settings first. e.g. default_groups:l1@a.io;default_user_quota:1024;.
  2. convert it to a python dict object.
  3. do some add/delete/modify operation with this python dict.
  4. convert python dict to string, replace old value stored in sql column.

Proposed SQL table structure (use MySQL for example):

CREATE TABLE IF NOT EXISTS account_settings (
    account VARCHAR(255) NOT NULL,
    k VARCHAR(255) NOT NULL,
    v VARCHAR(255) NOT NULL,    -- store short text
    v_text TEXT,    -- used to store long text
    PRIMARY KEY (account),
    INDEX (account, k),
    UNIQUE INDEX (account, k, v)
) ENGINE=InnoDB;

Sample values:

INSERT INTO account_settings (account, k, v) VALUES
    ('domain.com', 'default_groups', 'list-1@domain.com'),
    ('domain.com', 'default_groups', 'list-2@domain.com'),
    ('domain.com', 'default_user_quota', '1024'),
    ('user@domain.com', 'timezone', 'America/Moncton');

One parameter can have multiple values with multiple sql records.

With this sql table, it's easier to update per-account setting with SQL commands:

DELETE FROM account_settings
      WHERE account='domain.com'
            AND k='default_lists'
            AND v='list-2@domain.com';

DELETE FROM account_settings
      WHERE account='user@domain.com' AND k='timezone';

No more Python trick to manage them.

Comments (1)

  1. Log in to comment