receiver: better filter processing

[chiroptera.git] / chibackend / sqbase.d

/* E-Mail Client
 * coded by Ketmar // Invisible Vector <ketmar@ketmar.no-ip.org>
 * Understanding is not required. Only obedience.
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, version 3 of the License ONLY.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
module chibackend.sqbase is aliced;
private:

//version = fts5_use_porter;

// do not use, for testing only!
// and it seems to generate bigger files, lol
//version = use_balz;

// use libdeflate instead of zlib
// see https://github.com/ebiggers/libdeflate
// around 2 times slower on level 9 than zlib, resulting size is 5MB less
// around 3 times slower on level 12, resulting size it 10MB less
// totally doesn't worth it
//version = use_libdeflate;

// use libxpack instead of zlib
// see https://github.com/ebiggers/xpack
// it supports buffers up to 2^19 (524288) bytes (see https://github.com/ebiggers/xpack/issues/1)
// therefore it is useless (the resulting file is bigger than with zlib)
//version = use_libxpack;

// just for fun
// see https://github.com/jibsen/brieflz
// it has spurious slowdowns, and so is 4 times slower than zlib, with worser compression
//version = use_libbrieflz;

// apple crap; i just wanted to see how bad it is ;-)
// speed is comparable with zlib, compression is shittier by 60MB; crap
//version = use_liblzfse;

// just for fun
//version = use_lzjb;

// just for fun, slightly better than lzjb
//version = use_lz4;

// some compressors from wimlib
// see https://wimlib.net/
// only one can be selected!
// 15 times slower than zlib, much worser compression (~100MB bigger)
//version = use_libwim_lzms; // this supports chunks up to our maximum blob size
// two times faster than lzms, compression is still awful
//version = use_libwim_lzx; // this supports chunks up to 2MB; more-or-less useful
// quite fast (because it refuses to compress anything bigger than 64KB); compression is most awful
//version = use_libwim_xpress; // this supports chunks up to 64KB; useless

// oh, because why not?
// surprisingly good (but not as good as zlib), and lightning fast on default compression level
// sadly, requires external lib
//version = use_zstd;

private import iv.encoding;
private import iv.cmdcon;
private import iv.ripemd160;
private import iv.strex;
private import iv.sq3;
private import iv.timer;
private import iv.vfs.io;
private import iv.vfs.util;

private import iv.dlzma;

private import chibackend.mbuilder : DynStr;
private import chibackend.parse;
private import chibackend.decode;
//private import iv.utfutil;
//private import iv.vfs.io;

version(use_libdeflate) private import chibackend.pack.libdeflate;
else version(use_balz) private import iv.balz;
else version(use_libxpack) private import chibackend.pack.libxpack;
else version(use_libbrieflz) private import chibackend.pack.libbrieflz;
else version(use_liblzfse) private import chibackend.pack.liblzfse;
else version(use_lzjb) private import chibackend.pack.lzjb;
else version(use_libwim_lzms) private import chibackend.pack.libwim;
else version(use_libwim_lzx) private import chibackend.pack.libwim;
else version(use_libwim_xpress) private import chibackend.pack.libwim;
else version(use_lz4) private import chibackend.pack.liblz4;
else version(use_zstd) private import chibackend.pack.libzstd;


version(use_zstd) {
  public enum ChiroDefaultPackLevel = 6;
} else {
  public enum ChiroDefaultPackLevel = 9;
}


// use `MailDBPath()` to get/set it
private __gshared string ExpandedMailDBPath = null;
public __gshared int ChiroCompressionLevel = ChiroDefaultPackLevel;
public __gshared bool ChiroSQLiteSilent = false;

// if `true`, will try both ZLib and LZMA
public __gshared bool ChiroPackTryHard = false;

public __gshared bool ChiroTimerEnabled = false;
private __gshared Timer chiTimer = Timer(false);
private __gshared char[] chiTimerMsg = null;

// opened databases
public __gshared Database dbStore; // message store db
public __gshared Database dbView; // message view db
public __gshared Database dbConf; // config/options db


public enum Appearance {
  Ignore = -1, // can be used to ignore messages in thread view
  Unread = 0,
  Read = 1,
  SoftDeleteFilter = 2, // soft-delete from filter
  SoftDeleteUser = 3, // soft-delete by user
  SoftDeletePurge = 4, // soft-delete by user (will be purged on folder change)
}

public enum Mute {
  Never = -1,
  Normal = 0,
  Message = 1, /* single message */
  ThreadStart = 2, /* all children starting from this */
  ThreadOther = 3, /* muted by some parent */
}

public bool isSoftDeleted (const int appearance) pure nothrow @safe @nogc {
  pragma(inline, true);
  return
    appearance >= Appearance.SoftDeleteFilter &&
    appearance <= Appearance.SoftDeletePurge;
}


/*
There are several added SQLite functions:

ChiroPack(data[, compratio])
===============

This tries to compress the given data, and returns a compressed blob.
If `compratio` is negative or zero, do not compress anything.


ChiroUnpack(data)
=================

This decompresses the blob compressed with `ChiroPack()`. It is (usually) safe to pass
non-compressed data to this function.


ChiroNormCRLF(content)
======================

Replaces CR/LF with LF, `\x7f` with `~`, control chars (except TAB and CR) with spaces.
Removes trailing blanks.


ChiroNormHeaders(content)
=========================

Replaces CR/LF with LF, `\x7f` with `~`, control chars (except CR) with spaces.
Then replaces 'space, LF' with a single space (joins multiline headers).
Removes trailing blanks.


ChiroExtractHeaders(content)
============================

Can be used to extract headers from the message.
Replaces CR/LF with LF, `\x7f` with `~`, control chars (except CR) with spaces.
Then replaces 'space, LF' with a single space (joins multiline headers).
Removes trailing blanks.


ChiroExtractBody(content)
=========================

Can be used to extract body from the message.
Replaces CR/LF with LF, `\x7f` with `~`, control chars (except TAB and CR) with spaces.
Then replaces 'space, LF' with a single space (joins multiline headers).
Removes trailing blanks and final dot.
*/


public enum OptionsDBName = "chiroptera.db";
public enum StorageDBName = "chistore.db";
public enum SupportDBName = "chiview.db";


// ////////////////////////////////////////////////////////////////////////// //
private enum CommonPragmas = `
  PRAGMA case_sensitive_like = OFF;
  PRAGMA foreign_keys = OFF;
  PRAGMA locking_mode = NORMAL; /*EXCLUSIVE;*/
  PRAGMA secure_delete = OFF;
  PRAGMA threads = 3;
  PRAGMA trusted_schema = OFF;
  PRAGMA writable_schema = OFF;
`;

enum CommonPragmasRO = CommonPragmas~`
  PRAGMA temp_store = MEMORY; /*DEFAULT*/ /*FILE*/
`;

enum CommonPragmasRW = CommonPragmas~`
  PRAGMA application_id = 1128810834; /*CHIR*/
  PRAGMA auto_vacuum = NONE;
  PRAGMA encoding = "UTF-8";
  PRAGMA temp_store = DEFAULT;
  --PRAGMA journal_mode = WAL; /*OFF;*/
  --PRAGMA journal_mode = DELETE; /*OFF;*/
  PRAGMA synchronous = NORMAL; /*OFF;*/
`;

enum CommonPragmasRecreate = `
  PRAGMA locking_mode = EXCLUSIVE;
  PRAGMA journal_mode = OFF;
  PRAGMA synchronous = OFF;
`;

static immutable dbpragmasRO = CommonPragmasRO;

// we aren't expecting to change things much, so "DELETE" journal seems to be adequate
// use the smallest page size, because we don't need to perform alot of selects here
static immutable dbpragmasRWStorage = "PRAGMA page_size = 512;"~CommonPragmasRW~"PRAGMA journal_mode = DELETE;";
static immutable dbpragmasRWStorageRecreate = dbpragmasRWStorage~CommonPragmasRecreate;

// use slightly bigger pages
// funny, smaller pages leads to bigger files
static immutable dbpragmasRWSupport = "PRAGMA page_size = 4096;"~CommonPragmasRW~"PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;";
static immutable dbpragmasRWSupportRecreate = dbpragmasRWSupport~CommonPragmasRecreate;

// smaller page size is ok
// we aren't expecting to change things much, so "DELETE" journal seems to be adequate
static immutable dbpragmasRWOptions = "PRAGMA page_size = 512;"~CommonPragmasRW~"PRAGMA journal_mode = /*DELETE*/WAL; PRAGMA synchronous = NORMAL;";
static immutable dbpragmasRWOptionsRecreate = dbpragmasRWOptions~CommonPragmasRecreate;


enum msgTagNameCheckSQL = `
  WITH RECURSIVE tagtable(tag, rest) AS (
    VALUES('', NEW.tags||'|')
   UNION ALL
    SELECT
      SUBSTR(rest, 0, INSTR(rest, '|')),
      SUBSTR(rest, INSTR(rest, '|')+1)
    FROM tagtable
    WHERE rest <> '')
  SELECT
    (CASE
      WHEN tag = '/' THEN RAISE(FAIL, 'tag name violation (root tags are not allowed)')
      WHEN LENGTH(tag) = 1 THEN RAISE(FAIL, 'tag name violation (too short tag name)')
      WHEN SUBSTR(tag, LENGTH(tag)) = '/' THEN RAISE(FAIL, 'tag name violation (tag should not end with a slash)')
    END)
  FROM tagtable
  WHERE tag <> '';
`;

// main storage and support databases will be in different files
static immutable string schemaStorage = `
  -- deleted messages have empty headers and body
  -- this is so uids will remain unique on inserting
  -- tags are used to associate the message with various folders, and stored here for rebuild purposes
  -- the frontend will use the separate "tags" table to select messages
  -- deleted messages must not have any tags, and should contain no other data
  -- (keeping the data is harmless, it simply sits there and takes space)
  CREATE TABLE IF NOT EXISTS messages (
      uid INTEGER PRIMARY KEY  /* rowid, never zero */
    , tags TEXT DEFAULT NULL   /* associated message tags, '|'-separated; case-sensitive, no extra whitespaces or '||'! */
    -- article data; MUST contain the ending dot, and be properly dot-stuffed
    -- basically, this is "what we had received, as is" (*WITH* the ending dot!)
    -- there is no need to normalize it in any way (and you *SHOULD NOT* do it!)
    -- it should be compressed with "ChiroPack()", and extracted with "ChiroUnpack()"
    , data BLOB
  );

  -- check tag constraints
  CREATE TRIGGER IF NOT EXISTS fix_message_hashes_insert
  BEFORE INSERT ON messages
  FOR EACH ROW
  BEGIN`~msgTagNameCheckSQL~`
  END;

  CREATE TRIGGER IF NOT EXISTS fix_message_hashes_update_tags
  BEFORE UPDATE OF tags ON messages
  FOR EACH ROW
  BEGIN`~msgTagNameCheckSQL~`
  END;
`;

static immutable string schemaStorageIndex = `
`;


static immutable string schemaOptions = `
  -- use "autoincrement" to allow account deletion
  CREATE TABLE IF NOT EXISTS accounts (
      accid INTEGER PRIMARY KEY AUTOINCREMENT   /* unique, never zero */
    , checktime INTEGER NOT NULL DEFAULT 15     /* check time, in minutes */
    , nosendauth INTEGER NOT NULL DEFAULT 0     /* turn off authentication on sending? */
    , debuglog INTEGER NOT NULL DEFAULT 0       /* do debug logging? */
    , nocheck INTEGER NOT NULL DEFAULT 0        /* disable checking? */
    , nntplastindex INTEGER NOT NULL DEFAULT 0  /* last seen article index for NNTP groups */
    , name TEXT NOT NULL UNIQUE                 /* account name; lowercase alphanum, '_', '-', '.' */
    , recvserver TEXT NOT NULL                  /* server for receiving messages */
    , sendserver TEXT NOT NULL                  /* server for sending messages */
    , user TEXT NOT NULL                        /* pop3 user name */
    , pass TEXT NOT NULL                        /* pop3 password, empty for no authorisation */
    , realname TEXT NOT NULL                    /* user name for e-mail headers */
    , email TEXT NOT NULL                       /* account e-mail address (full, name@host) */
    , inbox TEXT NOT NULL                       /* inbox tag, usually "/accname/inbox", or folder for nntp */
    , nntpgroup TEXT NOT NULL DEFAULT ''        /* nntp group name for NNTP accounts; if empty, this is POP3 account */
  );


  CREATE TABLE IF NOT EXISTS options (
      name TEXT NOT NULL UNIQUE
    , value TEXT
  );


  CREATE TABLE IF NOT EXISTS addressbook (
      nick TEXT NOT NULL UNIQUE      /* short nick for this address book entry */
    , name TEXT NOT NULL DEFAULT ''
    , email TEXT NOT NULL
    , notes TEXT DEFAULT NULL
  );


  -- twits by email/name
  CREATE TABLE IF NOT EXISTS emailtwits (
      etwitid INTEGER PRIMARY KEY
    , tagglob TEXT NOT NULL  /* pattern for "GLOB" */
    , email TEXT             /* if both name and email present, use only email */
    , name TEXT              /* name to twit by */
    , title TEXT             /* optional title */
    , notes TEXT             /* notes; often URL */
  );

  -- twits by msgids
  CREATE TABLE IF NOT EXISTS msgidtwits (
      mtwitid INTEGER PRIMARY KEY
    , etwitid INTEGER              /* parent mail twit, if any */
    , automatic INTEGER DEFAULT 1  /* added by message filtering, not from .rc? */
    , tagglob TEXT NOT NULL        /* pattern for "GLOB" */
    , msgid TEXT                   /* message used to set twit */
  );


  -- message filters
  CREATE TABLE IF NOT EXISTS filters (
      filterid INTEGER PRIMARY KEY
    , valid INTEGER NOT NULL DEFAULT 1     /* is this filter valid? used to skip bad filters */
    , idx INTEGER NOT NULL DEFAULT 0       /* used for ordering */
    , post INTEGER NOT NULL DEFAULT 0      /* post-spamcheck filter? */
    , hitcount INTEGER NOT NULL DEFAULT 0  /* for statistics */
    , name TEXT NOT NULL UNIQUE            /* filter name */
    , body TEXT                            /* filter text */
  );

  CREATE TRIGGER IF NOT EXISTS filters_new_index
  AFTER INSERT ON filters
  FOR EACH ROW
  BEGIN
    UPDATE filters SET idx=(SELECT MAX(idx)+10 FROM filters)
    WHERE NEW.idx=0 AND filterid=NEW.filterid;
  END;
`;

static immutable string schemaOptionsIndex = `
  -- no need to, "UNIQUE" automaptically creates it
  --CREATE INDEX IF NOT EXISTS accounts_name ON accounts(name);

  -- this index in implicit
  --CREATE INDEX IF NOT EXISTS options_name ON options(name);

  CREATE INDEX IF NOT EXISTS emailtwits_email ON emailtwits(email);
  CREATE INDEX IF NOT EXISTS emailtwits_name ON emailtwits(name);
  CREATE UNIQUE INDEX IF NOT EXISTS emailtwits_email_name ON emailtwits(email, name);

  CREATE INDEX IF NOT EXISTS msgidtwits_msgid ON msgidtwits(msgid);

  CREATE INDEX IF NOT EXISTS filters_idx_post_valid ON filters(idx, post, valid);
`;


enum schemaSupportTable = `
  -- tag <-> messageid correspondence
  -- note that one message can be tagged with more than one tag
  -- there is always tag with "uid=0", to keep all tags alive
  -- special tags:
  --   account:name -- received via this account
  --   #spam -- this is spam message
  --   #hobo -- will be autoassigned to messages without any tags (created on demand)
  CREATE TABLE IF NOT EXISTS tagnames (
      tagid INTEGER PRIMARY KEY
    , hidden INTEGER NOT NULL DEFAULT 0      /* deleting tags may cause 'tagid' reuse, so it's better to hide them instead */
    , threading INTEGER NOT NULL DEFAULT 1   /* enable threaded view? */
    , noattaches INTEGER NOT NULL DEFAULT 0  /* ignore non-text attachments? */
    , tag TEXT NOT NULL UNIQUE
  );

  -- it is here, because we don't have a lot of tags, and inserts are slightly faster this way
  -- it's not required, because "UNIQUE" constraint will create automatic index
  --CREATE INDEX IF NOT EXISTS tagname_tag ON tagnames(tag);

  --CREATE INDEX IF NOT EXISTS tagname_tag_uid ON tagnames(tag, tagid);


  -- each tag has its own unique threads (so uids can be duplicated, but (uid,tagid) paris cannot
  -- see above for "apearance" and "mute" values
  CREATE TABLE IF NOT EXISTS threads (
      uid INTEGER                   /* rowid, corresponds to "id" in "messages", never zero */
    , tagid INTEGER                 /* we need separate threads for each tag */
    , time INTEGER DEFAULT 0        /* unixtime -- creation/send/receive */
    /* threading info */
    , parent INTEGER DEFAULT 0      /* uid: parent message in thread, or 0 */
    /* flags */
    , appearance INTEGER DEFAULT 0  /* how the message should look */
    , mute INTEGER DEFAULT 0  /* 1: only this message, 2: the whole thread */
    , title TEXT DEFAULT NULL  /* title from the filter */
  );


  -- WARNING!
  --   for FTS5 to work, this table must be:
  --     updated LAST on INSERT
  --     updated FIRST on DELETE
  --   this is due to FTS5 triggers
  --   message texts should NEVER be updated!
  --   if you want to do update a message:
  --     first, DELETE the old one from this table
  --     then, update textx
  --     then, INSERT here again
  --   doing it like that will keep FTS5 in sync
  CREATE TABLE IF NOT EXISTS info (
      uid INTEGER PRIMARY KEY  /* rowid, corresponds to "id" in "messages", never zero */
    , from_name TEXT           /* can be empty */
    , from_mail TEXT           /* can be empty */
    , subj TEXT                /* can be empty */
    , to_name TEXT             /* can be empty */
    , to_mail TEXT             /* can be empty */
  );


  -- this holds msgid
  -- moved to separate table, because this info is used only when inserting new messages
  CREATE TABLE IF NOT EXISTS msgids (
      uid INTEGER PRIMARY KEY  /* rowid, corresponds to "id" in "messages", never zero */
    , time INTEGER             /* so we can select the most recent message */
    , msgid TEXT               /* message id */
  );


  -- this holds in-reply-to, and references
  -- moved to separate table, because this info is used only when inserting new messages
  CREATE TABLE IF NOT EXISTS refids (
      uid INTEGER  /* rowid, corresponds to "id" in "messages", never zero */
    , idx INTEGER  /* internal index in headers, cannot have gaps, starts from 0 */
    , msgid TEXT   /* message id */
  );


  -- this ALWAYS contain an entry (yet content may be empty string)
  CREATE TABLE IF NOT EXISTS content_text (
      uid INTEGER PRIMARY KEY  /* owner message uid */
    , format TEXT NOT NULL     /* optional format, like 'flowed' */
    , content TEXT NOT NULL    /* properly decoded; packed */
  );


  -- this ALWAYS contain an entry (yet content may be empty string)
  CREATE TABLE IF NOT EXISTS content_html (
      uid INTEGER PRIMARY KEY  /* owner message uid */
    , format TEXT NOT NULL     /* optional format, like 'flowed' */
    , content TEXT NOT NULL    /* properly decoded; packed */
  );


  -- this DOES NOT include text and html contents (and may exclude others)
  CREATE TABLE IF NOT EXISTS attaches (
      uid INTEGER           /* owner message uid */
    , idx INTEGER           /* code should take care of proper autoincrementing this */
    , mime TEXT NOT NULL    /* always lowercased */
    , name TEXT NOT NULL    /* attachment name; always empty for inline content, never empty for non-inline content */
    , format TEXT NOT NULL  /* optional format, like 'flowed' */
    , content BLOB          /* properly decoded; packed; NULL if the attach was dropped */
  );


  -- this view is used for FTS5 content queries
  -- it is harmless to keep it here even if FTS5 is not used
  --DROP VIEW IF EXISTS fts5_msgview;
  CREATE VIEW IF NOT EXISTS fts5_msgview (uid, sender, subj, text, html)
    AS
      SELECT
          info.uid AS uid
        , info.from_name||' '||CHAR(26)||' '||info.from_mail AS sender
        , info.subj AS subj
        , ChiroUnpack(content_text.content) AS text
        , ChiroUnpack(content_html.content) AS html
      FROM info
      INNER JOIN content_text USING(uid)
      INNER JOIN content_html USING(uid)
    ;


  -- this table holds all unsent messages
  -- they are put in the storage and properly inserted,
  -- but also put in this table, for the receiver to send them
  -- also note that NNTP messages will be put in the storage without any tags, but with a content
  -- (this is because we will receive them back from NNTP server later)
  -- succesfully sent messages will be marked with non-zero sendtime
  CREATE TABLE IF NOT EXISTS unsent (
      uid INTEGER PRIMARY KEY  /* the same as in the storage, not automatic */
    , accid INTEGER            /* account from which this message should be sent */
    , from_pop3 TEXT           /* "from" for POP3 */
    , to_pop3 TEXT             /* "to" for POP3 */
    , data TEXT                /* PACKED data to send */
    , sendtime INTEGER DEFAULT 0      /* 0: not yet; unixtime */
    , lastsendtime INTEGER DEFAULT 0  /* when we last tried to send it? 0 means "not yet" */
  );
`;

static immutable string schemaSupportTempTables = `
  --DROP TABLE IF EXISTS treepane;
  CREATE TEMP TABLE IF NOT EXISTS treepane (
      iid INTEGER PRIMARY KEY
    , uid INTEGER
    , level INTEGER
    -- to make joins easier
    , tagid INTEGER
  );

  CREATE INDEX IF NOT EXISTS treepane_uid ON treepane(uid);
`;

enum schemaSupportIndex = `
  CREATE UNIQUE INDEX IF NOT EXISTS trd_by_tag_uid ON threads(tagid, uid);
  CREATE UNIQUE INDEX IF NOT EXISTS trd_by_uid_tag ON threads(uid, tagid);

  -- this is for views where threading is disabled
  CREATE INDEX IF NOT EXISTS trd_by_tag_time ON threads(tagid, time);
  --CREATE INDEX IF NOT EXISTS trd_by_tag_time_parent ON threads(tagid, time, parent);
  --CREATE INDEX IF NOT EXISTS trd_by_tag_parent_time ON threads(tagid, parent, time);
  --CREATE INDEX IF NOT EXISTS trd_by_tag_parent ON threads(tagid, parent);
  CREATE INDEX IF NOT EXISTS trd_by_parent_tag ON threads(parent, tagid);

  -- this is for test if we have any unread articles (we don't mind the exact numbers, tho)
  CREATE INDEX IF NOT EXISTS trd_by_appearance ON threads(appearance);
  -- this is for removing purged messages
  CREATE INDEX IF NOT EXISTS trd_by_tag_appearance ON threads(tagid, appearance);
  -- was used in table view creation, not used anymore
  --CREATE INDEX IF NOT EXISTS trd_by_parent_tag_appearance ON threads(parent, tagid, appearance);

  -- for theadmsgview
  -- was used in table view creation, not used anymore
  --CREATE INDEX IF NOT EXISTS trd_by_tag_appearance_time ON threads(tagid, appearance, time);

  CREATE INDEX IF NOT EXISTS msgid_by_msgid_time ON msgids(msgid, time DESC);

  CREATE INDEX IF NOT EXISTS refid_by_refids_idx ON refids(msgid, idx);
  CREATE INDEX IF NOT EXISTS refid_by_uid_idx ON refids(uid, idx);

  CREATE INDEX IF NOT EXISTS content_text_by_uid ON content_text(uid);
  CREATE INDEX IF NOT EXISTS content_html_by_uid ON content_html(uid);

  CREATE INDEX IF NOT EXISTS attaches_by_uid_name ON attaches(uid, name);
  CREATE INDEX IF NOT EXISTS attaches_by_uid_idx ON attaches(uid, idx);

  -- "info" indicies for twits
  CREATE INDEX IF NOT EXISTS info_by_from_mail_name ON info(from_mail, from_name);
  --CREATE INDEX IF NOT EXISTS info_by_from_mail ON info(from_mail);
  CREATE INDEX IF NOT EXISTS info_by_from_name ON info(from_name);


  CREATE INDEX IF NOT EXISTS unsent_by_accid ON unsent(accid);
`;

static immutable string schemaSupport = schemaSupportTable~schemaSupportIndex;


version(fts5_use_porter) {
  enum FTS5_Tokenizer = "porter unicode61 remove_diacritics 2";
} else {
  enum FTS5_Tokenizer = "unicode61 remove_diacritics 2";
}

static immutable string recreateFTS5 = `
  DROP TABLE IF EXISTS fts5_messages;
  CREATE VIRTUAL TABLE fts5_messages USING fts5(
      sender         /* sender name and email, separated by " \x1a " (dec 26) (substitute char) */
    , subj           /* email subject */
    , text           /* email body, text/plain */
    , html           /* email body, text/html */
    --, uid UNINDEXED  /* message uid this comes from (not needed, use "rowid" instead */
    , tokenize = '`~FTS5_Tokenizer~`'
    , content = 'fts5_msgview'
    , content_rowid = 'uid'
  );
  /* sender, subj, text, html */
  INSERT INTO fts5_messages(fts5_messages, rank) VALUES('rank', 'bm25(1.0, 3.0, 10.0, 6.0)');
`;

static immutable string repopulateFTS5 = `
  SELECT ChiroTimerStart('updating FTS5');
  BEGIN TRANSACTION;

  INSERT INTO fts5_messages(rowid, sender, subj, text, html)
  SELECT uid, sender, subj, text, html
  FROM fts5_msgview
  WHERE
    EXISTS (
      SELECT threads.tagid FROM threads
      INNER JOIN tagnames USING(tagid)
      WHERE
        threads.uid=fts5_msgview.uid AND
        tagnames.hidden=0 AND SUBSTR(tagnames.tag, 1, 1)='/'
    );

  COMMIT TRANSACTION;
  SELECT ChiroTimerStop();
`;


static immutable string recreateFTS5Triggers = `
  -- triggers to keep the FTS index up to date

  -- this rely on the proper "info" table update order
  -- info must be inserted LAST
  DROP TRIGGER IF EXISTS fts5xtrig_insert;
  CREATE TRIGGER fts5xtrig_insert
  AFTER INSERT ON info
  BEGIN
    INSERT INTO fts5_messages(rowid, sender, subj, text, html)
    SELECT uid, sender, subj, text, html FROM fts5_msgview WHERE uid=NEW.uid LIMIT 1;
  END;

  -- not AFTER, because we still need a valid view!
  -- this rely on the proper "info" table update order
  -- info must be deleted FIRST
  DROP TRIGGER IF EXISTS fts5xtrig_delete;
  CREATE TRIGGER fts5xtrig_delete
  BEFORE DELETE ON info
  BEGIN
    INSERT INTO fts5_messages(fts5_messages, rowid, sender, subj, text, html)
    SELECT 'delete', uid, sender, subj, text, html FROM fts5_msgview WHERE uid=OLD.uid LIMIT 1;
  END;

  -- message texts should NEVER be updated, so no ON UPDATE trigger
`;


// ////////////////////////////////////////////////////////////////////////// //
// not properly implemented yet
//version = lazy_mt_safe;

version(lazy_mt_safe) {
  enum lazy_mt_safe_flag = true;
} else {
  enum lazy_mt_safe_flag = false;
}

public struct LazyStatement(string dbname) {
public:
  enum DB {
    Store,
    View,
    Conf,
  }

private:
  static struct Data {
    DBStatement st = void;
    version(lazy_mt_safe) {
      sqlite3_mutex* mutex = void;
    }
    char* sql = void;
    usize sqlsize = void;
    uint compiled = void;
  }

private:
  usize udata = 0;
  DB dbtype;
  string delayInit = null;

private:
  inout(Data)* datap () inout pure nothrow @trusted @nogc { pragma(inline, true); return cast(Data*)udata; }
  void datap (Data *v) pure nothrow @trusted @nogc { pragma(inline, true); udata = cast(usize)v; }

public:
  //@disable this ();
  @disable this (this);

  this (string sql) {
    delayInit = sql;
    /*
    assert(sql.length);
         static if (dbname == "View" || dbname == "view") dbtype = DB.View;
    else static if (dbname == "Store" || dbname == "store") dbtype = DB.Store;
    else static if (dbname == "Conf" || dbname == "conf") dbtype = DB.Conf;
    else static assert(0, "invalid db name: '"~dbname~"'");
    import core.stdc.stdlib : calloc;
    Data* dp = cast(Data*)calloc(1, Data.sizeof);
    if (dp is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
    datap = dp;
    dp.sql = cast(char*)calloc(1, sql.length);
    if (dp.sql is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
    dp.sql[0..sql.length] = sql[];
    dp.sqlsize = sql.length;
    version(lazy_mt_safe) {
      dp.mutex = sqlite3_mutex_alloc(SQLITE_MUTEX_FAST);
      if (dp.mutex is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
    }
    */
    //dbtype = adb;
    //{ import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "===INIT===\n%s\n==========\n", dp.sql); }
  }

  ~this () {
    import core.stdc.stdlib : free;
    if (!udata) return;
    Data* dp = datap;
    //{ import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "===DEINIT===\n%s\n============\n", dp.sql); }
    dp.st = DBStatement.init;
    free(dp.sql);
    version(lazy_mt_safe) {
      sqlite3_mutex_free(dp.mutex);
    }
    free(dp);
    udata = 0;
  }

  bool valid () pure nothrow @safe @nogc { pragma(inline, true); return (udata != 0 || delayInit.length); }

  private void setupWith (const(char)[] sql) {
    if (udata) throw new Exception("statement already inited");
    assert(sql.length);
         static if (dbname == "View" || dbname == "view") dbtype = DB.View;
    else static if (dbname == "Store" || dbname == "store") dbtype = DB.Store;
    else static if (dbname == "Conf" || dbname == "conf") dbtype = DB.Conf;
    else static assert(0, "invalid db name: '"~dbname~"'");
    import core.stdc.stdlib : calloc;
    Data* dp = cast(Data*)calloc(1, Data.sizeof);
    if (dp is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
    datap = dp;
    dp.sql = cast(char*)calloc(1, sql.length);
    if (dp.sql is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
    dp.sql[0..sql.length] = sql[];
    dp.sqlsize = sql.length;
    version(lazy_mt_safe) {
      dp.mutex = sqlite3_mutex_alloc(SQLITE_MUTEX_FAST);
      if (dp.mutex is null) { import core.exception : onOutOfMemoryErrorNoGC; onOutOfMemoryErrorNoGC(); }
    }
    //dbtype = adb;
    //{ import core.stdc.stdio : stderr, fprintf; fprintf(stderr, "===INIT===\n%s\n==========\n", dp.sql); }
  }

  ref DBStatement st () {
    if (!udata) {
      //throw new Exception("no statement set");
      setupWith(delayInit);
    }
    Data* dp = datap;
    if (!dp.compiled) {
      version(lazy_mt_safe) {
        sqlite3_mutex_enter(dp.mutex);
      }
      scope(exit) {
        version(lazy_mt_safe) {
          sqlite3_mutex_leave(dp.mutex);
        }
      }
      //{ import core.stdc.stdio : printf; printf("***compiling:\n%s\n=====\n", dp.sql); }
      final switch (dbtype) {
        case DB.Store: dp.st = dbStore.persistentStatement(dp.sql[0..dp.sqlsize]); break;
        case DB.View: dp.st = dbView.persistentStatement(dp.sql[0..dp.sqlsize]); break;
        case DB.Conf: dp.st = dbConf.persistentStatement(dp.sql[0..dp.sqlsize]); break;
      }
      dp.compiled = 1;
      //assert(dp.st.valid);
    }
    //assert(dp.st.valid);
    return dp.st;
  }
}


// ////////////////////////////////////////////////////////////////////////// //
private bool isGoodText (const(void)[] buf) pure nothrow @safe @nogc {
  foreach (immutable ubyte ch; cast(const(ubyte)[])buf) {
    if (ch < 32) {
      if (ch != 9 && ch != 10 && ch != 13 && ch != 27) return false;
    } else {
      if (ch == 127) return false;
    }
  }
  return true;
  //return utf8ValidText(buf);
}


// ////////////////////////////////////////////////////////////////////////// //
private bool isBadPrefix (const(char)[] buf) pure nothrow @trusted @nogc {
  if (buf.length < 5) return false;
  return
    buf.ptr[0] == '\x1b' &&
    buf.ptr[1] >= 'A' && buf.ptr[1] <= 'Z' &&
    buf.ptr[2] >= 'A' && buf.ptr[2] <= 'Z' &&
    buf.ptr[3] >= 'A' && buf.ptr[3] <= 'Z' &&
    buf.ptr[4] >= 'A' && buf.ptr[4] <= 'Z';
}


/* two high bits of the first byte holds the size:
   00: fit into  6 bits: [0..       0x3f] (1 byte)
   01: fit into 14 bits: [0..     0x3fff] (2 bytes)
   10: fit into 22 bits: [0..  0x3f_ffff] (3 bytes)
   11: fit into 30 bits: [0..0x3fff_ffff] (4 bytes)

number is stored as big-endian.
will not write anything to `dest` if there is not enough room.

returns number of bytes, or 0 if the number is too big.
*/
private uint encodeUInt (void[] dest, uint v) nothrow @trusted @nogc {
  if (v > 0x3fff_ffffU) return 0;
  ubyte[] d = cast(ubyte[])dest;
  // 4 bytes?
  if (v > 0x3f_ffffU) {
    v |= 0xc000_0000U;
    if (d.length >= 4) {
      d.ptr[0] = cast(ubyte)(v>>24);
      d.ptr[1] = cast(ubyte)(v>>16);
      d.ptr[2] = cast(ubyte)(v>>8);
      d.ptr[3] = cast(ubyte)v;
    }
    return 4;
  }
  // 3 bytes?
  if (v > 0x3fffU) {
    v |= 0x80_0000U;
    if (d.length >= 3) {
      d.ptr[0] = cast(ubyte)(v>>16);
      d.ptr[1] = cast(ubyte)(v>>8);
      d.ptr[2] = cast(ubyte)v;
    }
    return 3;
  }
  // 2 bytes?
  if (v > 0x3fU) {
    v |= 0x4000U;
    if (d.length >= 2) {
      d.ptr[0] = cast(ubyte)(v>>8);
      d.ptr[1] = cast(ubyte)v;
    }
    return 2;
  }
  // 1 byte
  if (d.length >= 1) d.ptr[0] = cast(ubyte)v;
  return 1;
}


private uint decodeUIntLength (const(void)[] dest) pure nothrow @trusted @nogc {
  const(ubyte)[] d = cast(const(ubyte)[])dest;
  if (d.length == 0) return 0;
  switch (d.ptr[0]&0xc0) {
    case 0x00: return 1;
    case 0x40: return (d.length >= 2 ? 2 : 0);
    case 0x80: return (d.length >= 3 ? 3 : 0);
    default:
  }
  return (d.length >= 4 ? 4 : 0);
}


// returns uint.max on error (impossible value)
private uint decodeUInt (const(void)[] dest) pure nothrow @trusted @nogc {
  const(ubyte)[] d = cast(const(ubyte)[])dest;
  if (d.length == 0) return uint.max;
  uint res = void;
  switch (d.ptr[0]&0xc0) {
    case 0x00:
      res = d.ptr[0];
      break;
    case 0x40:
      if (d.length < 2) return uint.max;
      res = ((d.ptr[0]&0x3fU)<<8)|d.ptr[1];
      break;
    case 0x80:
      if (d.length < 3) return uint.max;
      res = ((d.ptr[0]&0x3fU)<<16)|(d.ptr[1]<<8)|d.ptr[2];
      break;
    default:
      if (d.length < 4) return uint.max;
      res = ((d.ptr[0]&0x3fU)<<24)|(d.ptr[1]<<16)|(d.ptr[2]<<8)|d.ptr[3];
      break;
  }
  return res;
}


// returns position AFTER the headers (empty line is skipped too)
// returned value is safe for slicing
private int sq3Supp_FindHeadersEnd (const(char)* vs, const int sz) {
  import core.stdc.string : memchr;
  if (sz <= 0) return 0;
  const(char)* eptr = cast(const(char)*)memchr(vs, '\n', cast(uint)sz);
  while (eptr !is null) {
    ++eptr;
    int epos = cast(int)cast(usize)(eptr-vs);
    if (sz-epos < 1) break;
    if (*eptr == '\r') {
      if (sz-epos < 2) break;
      ++epos;
      ++eptr;
    }
    if (*eptr == '\n') return epos+1;
    assert(epos < sz);
    eptr = cast(const(char)*)memchr(eptr, '\n', cast(uint)(sz-epos));
  }
  return sz;
}


// hack for some invalid dates
uint parseMailDate (const(char)[] s) nothrow {
  import std.datetime;
  if (s.length == 0) return 0;
  try {
    return cast(uint)(parseRFC822DateTime(s).toUTC.toUnixTime);
  } catch (Exception) {}
  // sometimes this helps
  usize dcount = 0;
  foreach_reverse (immutable char ch; s) {
    if (ch < '0' || ch > '9') break;
    ++dcount;
  }
  if (dcount > 4) return 0;
  s ~= "0000"[0..4-dcount];
  try {
    return cast(uint)(parseRFC822DateTime(s).toUTC.toUnixTime);
  } catch (Exception) {}
  return 0;
}


// ////////////////////////////////////////////////////////////////////////// //
extern(C) {

/*
** ChiroPackLZMA(content)
** ChiroPackLZMA(content, packflag)
**
** second form accepts int flag; 0 means "don't pack"
*/
private void sq3Fn_ChiroPackLZMA (sqlite3_context *ctx, int argc, sqlite3_value **argv) nothrow @trusted {
  if (argc < 1 || argc > 2) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroPackLZMA()`", -1); return; }
  int packlevel = (argc > 1 ? sqlite3_value_int(argv[1]) : ChiroDefaultPackLevel);
  if (packlevel < 0) packlevel = 5/*lzma default*/; else if (packlevel > 9) packlevel = 9;

  sqlite3_value *val = argv[0];

  immutable int sz = sqlite3_value_bytes(val);
  if (sz < 0 || sz > 0x3fffffff-4) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(val);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroPackLZMA()`", -1); return; }

  if (sz >= 0x3fffffff-8) {
    if (isBadPrefix(vs[0..cast(uint)sz])) { sqlite3_result_error_toobig(ctx); return; }
    sqlite3_result_value(ctx, val);
    return;
  }

  import core.stdc.stdlib : malloc, free;
  import core.stdc.string : memcpy;

  if (packlevel > 0 && sz > 8) {
    import core.stdc.stdio : snprintf;
    char[16] xsz = void;
    xsz[0..5] = "\x1bLZMA";
    uint xszlen = encodeUInt(xsz[5..$], cast(uint)sz);
    if (xszlen) {
      xszlen += 5;
      immutable uint bsz = cast(uint)sz;
      char* cbuf = cast(char*)malloc(bsz+xszlen+LZMA_PROPS_SIZE+1+16);
      if (cbuf is null) {
        if (isBadPrefix(vs[0..cast(uint)sz])) { sqlite3_result_error_nomem(ctx); return; }
      } else {
        cbuf[0..xszlen] = xsz[0..xszlen];
        usize destLen = bsz;
        ubyte[LZMA_PROPS_SIZE+8] hdr = void;
        uint hdrSize = cast(uint)hdr.sizeof;

        CLzmaEncProps props;
        props.level = packlevel;
        props.dictSize = 1<<22; //4MB
        props.reduceSize = bsz;

        SRes res = LzmaEncode(cast(ubyte*)cbuf+xszlen+LZMA_PROPS_SIZE+1, &destLen, cast(const(ubyte)*)vs, bsz, &props, hdr.ptr, &hdrSize, 0/*writeEndMark*/, null, &lzmaDefAllocator, &lzmaDefAllocator);
        assert(hdrSize == LZMA_PROPS_SIZE);
        if (res == SZ_OK && destLen+xszlen+LZMA_PROPS_SIZE+1 < cast(usize)sz) {
          import core.stdc.string : memcpy;
          cbuf[xszlen] = LZMA_PROPS_SIZE;
          memcpy(cbuf+xszlen+1, hdr.ptr, LZMA_PROPS_SIZE);
          sqlite3_result_blob(ctx, cbuf, destLen+xszlen+LZMA_PROPS_SIZE+1, &free);
          return;
        }
        free(cbuf);
      }
    }
  }

  if (isBadPrefix(vs[0..cast(uint)sz])) {
    char *res = cast(char *)malloc(sz+5);
    if (res is null) { sqlite3_result_error_nomem(ctx); return; }
    res[0..5] = "\x1bRAWB";
    res[5..sz+5] = vs[0..sz];
    if (isGoodText(vs[0..cast(usize)sz])) {
      sqlite3_result_text(ctx, res, sz+5, &free);
    } else {
      sqlite3_result_blob(ctx, res, sz+5, &free);
    }
  } else {
    immutable bool wantBlob = !isGoodText(vs[0..cast(usize)sz]);
    immutable int tp = sqlite3_value_type(val);
    if ((wantBlob && tp == SQLITE_BLOB) || (!wantBlob && tp == SQLITE3_TEXT)) {
      sqlite3_result_value(ctx, val);
    } else if (wantBlob) {
      sqlite3_result_blob(ctx, vs, sz, SQLITE_TRANSIENT);
    } else {
      sqlite3_result_text(ctx, vs, sz, SQLITE_TRANSIENT);
    }
  }
}


/*
** ChiroPack(content)
** ChiroPack(content, packflag)
**
** second form accepts int flag; 0 means "don't pack"
*/
private void sq3Fn_ChiroPackCommon (sqlite3_context *ctx, sqlite3_value *val, int packlevel) nothrow @trusted {
  immutable int sz = sqlite3_value_bytes(val);
  if (sz < 0 || sz > 0x3fffffff-4) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(val);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroPack()`", -1); return; }

  if (sz >= 0x3fffffff-8) {
    if (isBadPrefix(vs[0..cast(uint)sz])) { sqlite3_result_error_toobig(ctx); return; }
    sqlite3_result_value(ctx, val);
    return;
  }

  import core.stdc.stdlib : malloc, free;
  import core.stdc.string : memcpy;

  if (packlevel > 0 && sz > 8) {
    import core.stdc.stdio : snprintf;
    char[16] xsz = void;
    version(use_balz) {
      xsz[0..5] = "\x1bBALZ";
    } else version(use_libxpack) {
      xsz[0..5] = "\x1bXPAK";
    } else version(use_libbrieflz) {
      xsz[0..5] = "\x1bBRLZ";
    } else version(use_liblzfse) {
      xsz[0..5] = "\x1bLZFS";
    } else version(use_lzjb) {
      xsz[0..5] = "\x1bLZJB";
    } else version(use_libwim_lzms) {
      xsz[0..5] = "\x1bLZMS";
    } else version(use_libwim_lzx) {
      xsz[0..5] = "\x1bLZMX";
    } else version(use_libwim_xpress) {
      xsz[0..5] = "\x1bXPRS";
    } else version(use_lz4) {
      xsz[0..5] = "\x1bLZ4D";
    } else version(use_zstd) {
      xsz[0..5] = "\x1bZSTD";
    } else {
      xsz[0..5] = "\x1bZLIB";
    }
    immutable uint xszlenNum = encodeUInt(xsz[5..$], cast(uint)sz);
    if (xszlenNum) {
      immutable uint xszlen = xszlenNum+5;
      //xsz[xszlen++] = ':';
      version(use_libbrieflz) {
        immutable usize bsz = blz_max_packed_size(cast(usize)sz);
      } else version(use_lzjb) {
        immutable uint bsz = cast(uint)sz+1024;
      } else version(use_lz4) {
        immutable uint bsz = cast(uint)LZ4_compressBound(sz)+1024;
      } else {
        immutable uint bsz = cast(uint)sz;
      }
      char* cbuf = cast(char*)malloc(bsz+xszlen+64);
      if (cbuf is null) {
        if (isBadPrefix(vs[0..cast(uint)sz])) { sqlite3_result_error_nomem(ctx); return; }
      } else {
        cbuf[0..xszlen] = xsz[0..xszlen];
        version(use_balz) {
          Balz bz;
          usize spos = 0;
          usize dpos = xszlen;
          try {
            bz.compress(
              // reader
              (buf) {
                if (spos >= cast(usize)sz) return 0;
                usize left = cast(usize)sz-spos;
                if (left > buf.length) left = buf.length;
                if (left) memcpy(buf.ptr, vs+spos, left);
                spos += left;
                return left;
              },
              // writer
              (buf) {
                if (dpos+buf.length >= cast(usize)sz) throw new Exception("uncompressible");
                memcpy(cbuf+dpos, buf.ptr, buf.length);
                dpos += buf.length;
              },
              // maximum compression?
              true
            );
          } catch(Exception) {
            dpos = usize.max;
          }
          if (dpos < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dpos, &free);
            return;
          }
        } else version(use_libdeflate) {
          if (packlevel > 12) packlevel = 12;
          libdeflate_compressor *cpr = libdeflate_alloc_compressor(packlevel);
          if (cpr is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
          usize dsize = libdeflate_zlib_compress(cpr, vs, cast(usize)sz, cbuf+xszlen, bsz);
          libdeflate_free_compressor(cpr);
          if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        } else version(use_libxpack) {
          // 2^19 (524288) bytes. This is definitely a big problem and I am planning to address it.
          // https://github.com/ebiggers/xpack/issues/1
          if (sz < 524288-64) {
            if (packlevel > 9) packlevel = 9;
            xpack_compressor *cpr = xpack_alloc_compressor(cast(usize)sz, packlevel);
            if (cpr is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
            usize dsize = xpack_compress(cpr, vs, cast(usize)sz, cbuf+xszlen, bsz);
            xpack_free_compressor(cpr);
            if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
              sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
              return;
            }
          }
        } else version(use_libbrieflz) {
          if (packlevel > 10) packlevel = 10;
          immutable usize wbsize = blz_workmem_size_level(cast(usize)sz, packlevel);
          void* wbuf = cast(void*)malloc(wbsize+!wbsize);
          if (wbuf is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
          uint dsize = blz_pack_level(vs, cbuf+xszlen, cast(uint)sz, wbuf, packlevel);
          free(wbuf);
          if (dsize+xszlen < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        } else version(use_liblzfse) {
          immutable usize wbsize = lzfse_encode_scratch_size();
          void* wbuf = cast(void*)malloc(wbsize+!wbsize);
          if (wbuf is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
          usize dsize = lzfse_encode_buffer(cbuf+xszlen, bsz, vs, cast(uint)sz, wbuf);
          free(wbuf);
          if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        } else version(use_lzjb) {
          usize dsize = lzjb_compress(vs, cast(usize)sz, cbuf+xszlen, bsz);
          if (dsize == usize.max) dsize = 0;
          if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
          //{ import core.stdc.stdio : fprintf, stderr; fprintf(stderr, "LZJB FAILED!\n"); }
        } else version(use_libwim_lzms) {
          wimlib_compressor* cpr;
          uint clevel = (packlevel < 10 ? 50 : 1000);
          int rc = wimlib_create_compressor(WIMLIB_COMPRESSION_TYPE_LZMS, cast(usize)sz, clevel, &cpr);
          if (rc != 0) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
          usize dsize = wimlib_compress(vs, cast(usize)sz, cbuf+xszlen, bsz, cpr);
          wimlib_free_compressor(cpr);
          if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        } else version(use_libwim_lzx) {
          if (sz <= WIMLIB_LZX_MAX_CHUNK) {
            wimlib_compressor* cpr;
            uint clevel = (packlevel < 10 ? 50 : 1000);
            int rc = wimlib_create_compressor(WIMLIB_COMPRESSION_TYPE_LZX, cast(usize)sz, clevel, &cpr);
            if (rc != 0) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
            usize dsize = wimlib_compress(vs, cast(usize)sz, cbuf+xszlen, bsz, cpr);
            wimlib_free_compressor(cpr);
            if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
              sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
              return;
            }
          }
        } else version(use_libwim_xpress) {
          if (sz <= WIMLIB_XPRESS_MAX_CHUNK) {
            wimlib_compressor* cpr;
            uint clevel = (packlevel < 10 ? 50 : 1000);
            uint csz = WIMLIB_XPRESS_MIN_CHUNK;
            while (csz < WIMLIB_XPRESS_MAX_CHUNK && csz < cast(uint)sz) csz *= 2U;
            int rc = wimlib_create_compressor(WIMLIB_COMPRESSION_TYPE_XPRESS, csz, clevel, &cpr);
            if (rc != 0) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
            usize dsize = wimlib_compress(vs, cast(usize)sz, cbuf+xszlen, bsz, cpr);
            wimlib_free_compressor(cpr);
            if (dsize > 0 && dsize+xszlen < cast(usize)sz) {
              sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
              return;
            }
          }
        } else version(use_lz4) {
          int dsize = LZ4_compress_default(vs, cbuf+xszlen, sz, cast(int)bsz);
          if (dsize > 0 && dsize+xszlen < sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        } else version(use_zstd) {
          immutable int clev =
            packlevel <= 3 ? ZSTD_minCLevel() :
            packlevel <= 6 ? ZSTD_defaultCLevel() :
            packlevel < 10 ? 19 :
            ZSTD_maxCLevel();
          usize dsize = ZSTD_compress(cbuf+xszlen, cast(int)bsz, vs, sz, clev);
          if (!ZSTD_isError(dsize) && dsize > 0 && dsize+xszlen < sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        } else {
          import etc.c.zlib : /*compressBound,*/ compress2, Z_OK;
          //uint bsz = cast(uint)compressBound(cast(uint)sz);
          if (packlevel > 9) packlevel = 9;
          usize dsize = bsz;
          immutable int zres = compress2(cast(ubyte *)(cbuf+xszlen), &dsize, cast(const(ubyte) *)vs, sz, packlevel);
          if (zres == Z_OK && dsize+xszlen < cast(usize)sz) {
            if (!ChiroPackTryHard) {
              sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
              return;
            }
          } else {
            free(cbuf);
            cbuf = null;
          }
          // try LZMA?
          if (ChiroPackTryHard) {
            char* lzmabuf = cast(char*)malloc(bsz+xszlen+LZMA_PROPS_SIZE+1+64);
            if (lzmabuf !is null) {
              lzmabuf[0..xszlen] = xsz[0..xszlen];
              lzmabuf[1..5] = "LZMA";
              usize destLen = (cbuf is null ? bsz : dsize); // do not take more than zlib
              if (destLen > bsz) destLen = bsz; // just in case
              ubyte[LZMA_PROPS_SIZE+8] hdr = void;
              uint hdrSize = cast(uint)hdr.sizeof;

              CLzmaEncProps props;
              props.level = packlevel;
              props.dictSize = 1<<22; //4MB
              props.reduceSize = bsz;

              immutable SRes nres = LzmaEncode(cast(ubyte*)(lzmabuf+xszlen+LZMA_PROPS_SIZE+1), &destLen, cast(const(ubyte)*)vs, bsz, &props, hdr.ptr, &hdrSize, 0/*writeEndMark*/, null, &lzmaDefAllocator, &lzmaDefAllocator);
              assert(hdrSize == LZMA_PROPS_SIZE);
              if (nres == SZ_OK && destLen+xszlen+LZMA_PROPS_SIZE+1 < cast(usize)sz) {
                if (cbuf is null || destLen+xszlen+LZMA_PROPS_SIZE+1 < dsize+xszlen) {
                  if (cbuf !is null) free(cbuf); // free zlib result
                  import core.stdc.string : memcpy;
                  lzmabuf[xszlen] = LZMA_PROPS_SIZE;
                  memcpy(lzmabuf+xszlen+1, hdr.ptr, LZMA_PROPS_SIZE);
                  sqlite3_result_blob(ctx, lzmabuf, destLen+xszlen+LZMA_PROPS_SIZE+1, &free);
                  return;
                }
              }
              free(lzmabuf);
            }
          }
          // return zlib result?
          if (cbuf !is null) {
            assert(dsize < bsz);
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        }
        if (cbuf !is null) free(cbuf);
      }
    }
  }

  if (isBadPrefix(vs[0..cast(uint)sz])) {
    char *res = cast(char *)malloc(sz+5);
    if (res is null) { sqlite3_result_error_nomem(ctx); return; }
    res[0..5] = "\x1bRAWB";
    res[5..sz+5] = vs[0..sz];
    if (isGoodText(vs[0..cast(usize)sz])) {
      sqlite3_result_text(ctx, res, sz+5, &free);
    } else {
      sqlite3_result_blob(ctx, res, sz+5, &free);
    }
  } else {
    immutable bool wantBlob = !isGoodText(vs[0..cast(usize)sz]);
    immutable int tp = sqlite3_value_type(val);
    if ((wantBlob && tp == SQLITE_BLOB) || (!wantBlob && tp == SQLITE3_TEXT)) {
      sqlite3_result_value(ctx, val);
    } else if (wantBlob) {
      sqlite3_result_blob(ctx, vs, sz, SQLITE_TRANSIENT);
    } else {
      sqlite3_result_text(ctx, vs, sz, SQLITE_TRANSIENT);
    }
  }
}


/*
** ChiroPack(content)
*/
private void sq3Fn_ChiroPack (sqlite3_context *ctx, int argc, sqlite3_value **argv) nothrow @trusted {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroPack()`", -1); return; }
  return sq3Fn_ChiroPackCommon(ctx, argv[0], ChiroCompressionLevel);
}


/*
** ChiroPack(content, packlevel)
**
** `packlevel` == 0 means "don't pack"
** `packlevel` == 9 means "maximum compression"
*/
private void sq3Fn_ChiroPackDPArg (sqlite3_context *ctx, int argc, sqlite3_value **argv) nothrow @trusted {
  if (argc != 2) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroPack()`", -1); return; }
  return sq3Fn_ChiroPackCommon(ctx, argv[0], sqlite3_value_int(argv[1]));
}


/*
** ChiroGetPackType(content)
*/
private void sq3Fn_ChiroGetPackType (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroGetPackType()`", -1); return; }

  int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 5 || sz > 0x3fffffff-4) { sqlite3_result_text(ctx, "RAWB", 4, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroUnpack()`", -1); return; }

  if (!isBadPrefix(vs[0..cast(uint)sz])) { sqlite3_result_text(ctx, "RAWB", 4, SQLITE_STATIC); return; }

  sqlite3_result_text(ctx, vs+1, 4, SQLITE_TRANSIENT);
}


/*
** ChiroUnpack(content)
**
** it is (almost) safe to pass non-packed content here
*/
private void sq3Fn_ChiroUnpack (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  //{ import core.stdc.stdio : fprintf, stderr; fprintf(stderr, "!!!000\n"); }
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroUnpack()`", -1); return; }

  int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0 || sz > 0x3fffffff-4) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroUnpack()`", -1); return; }

  if (!isBadPrefix(vs[0..cast(uint)sz])) { sqlite3_result_value(ctx, argv[0]); return; }
  if (vs[0..5] == "\x1bRAWB") { sqlite3_result_blob(ctx, vs+5, sz-5, SQLITE_TRANSIENT); return; }
  if (sz < 6) { sqlite3_result_error(ctx, "invalid data in `ChiroUnpack()`", -1); return; }

  enum {
    Codec_ZLIB,
    Codec_LZMA,
    Codec_BALZ,
    Codec_XPAK,
    Codec_BRLZ,
    Codec_LZFS,
    Codec_LZJB,
    Codec_LZMS,
    Codec_LZMX,
    Codec_XPRS,
    Codec_LZ4D,
    Codec_ZSTD,
  }

  int codec = Codec_ZLIB;
  if (vs[0..5] != "\x1bZLIB") {
    if (vs[0..5] == "\x1bLZMA") codec = Codec_LZMA;
    version(use_balz) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bBALZ") codec = Codec_BALZ;
    }
    version(use_libxpack) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bXPAK") codec = Codec_XPAK;
    }
    version(use_libxpack) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bXPAK") codec = Codec_XPAK;
    }
    version(use_libbrieflz) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bBRLZ") codec = Codec_BRLZ;
    }
    version(use_liblzfse) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bLZFS") codec = Codec_LZFS;
    }
    version(use_lzjb) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bLZJB") codec = Codec_LZJB;
    }
    version(use_libwim_lzms) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bLZMS") codec = Codec_LZMS;
    }
    version(use_libwim_lzx) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bLZMX") codec = Codec_LZMX;
    }
    version(use_libwim_xpress) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bXPRS") codec = Codec_XPRS;
    }
    version(use_lz4) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bLZ4D") codec = Codec_LZ4D;
    }
    version(use_zstd) {
      if (codec == Codec_ZLIB && vs[0..5] == "\x1bZSTD") codec = Codec_ZSTD;
    }
    if (codec == Codec_ZLIB) { sqlite3_result_error(ctx, "invalid codec in `ChiroUnpack()`", -1); return; }
  }

  // skip codec id
  // size is guaranteed to be at least 6 here
  vs += 5;
  sz -= 5;

  immutable uint numsz = decodeUIntLength(vs[0..cast(uint)sz]);
  //{ import core.stdc.stdio : printf; printf("sz=%d; numsz=%u; %02X %02X %02X %02X\n", sz, numsz, cast(uint)vs[5], cast(uint)vs[6], cast(uint)vs[7], cast(uint)vs[8]); }
  //writeln("sq3Fn_ChiroUnpack: nsz=", sz-5);
  if (numsz == 0 || numsz > cast(uint)sz) { sqlite3_result_error(ctx, "invalid data in `ChiroUnpack()`", -1); return; }
  //{ import core.stdc.stdio : fprintf, stderr; fprintf(stderr, "!!!100\n"); }
  immutable uint rsize = decodeUInt(vs[0..cast(uint)sz]);
  if (rsize == uint.max) { sqlite3_result_error(ctx, "invalid data in `ChiroUnpack()`", -1); return; }
  //{ import core.stdc.stdio : fprintf, stderr; fprintf(stderr, "!!!101:rsize=%u\n", rsize); }
  if (rsize == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }
  // skip number
  vs += numsz;
  sz -= cast(int)numsz;
  //{ import core.stdc.stdio : printf; printf("sz=%d; rsize=%u\n", sz, rsize, dpos); }

  import core.stdc.stdlib : malloc, free;
  import core.stdc.string : memcpy;

  char* cbuf = cast(char*)malloc(rsize);
  if (cbuf is null) { sqlite3_result_error_nomem(ctx); return; }
  //writeln("sq3Fn_ChiroUnpack: rsize=", rsize, "; left=", sz-dpos);

  usize dsize = rsize;
  final switch (codec) {
    case Codec_ZLIB:
      version(use_libdeflate) {
        libdeflate_decompressor *dcp = libdeflate_alloc_decompressor();
        if (dcp is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
        auto rc = libdeflate_zlib_decompress(dcp, vs, cast(usize)sz, cbuf, rsize, null);
        if (rc != LIBDEFLATE_SUCCESS) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        import etc.c.zlib : uncompress, Z_OK;
        int zres = uncompress(cast(ubyte *)cbuf, &dsize, cast(const(ubyte) *)vs, sz);
        //writeln("sq3Fn_ChiroUnpack: rsize=", rsize, "; left=", sz, "; dsize=", dsize, "; zres=", zres);
        if (zres != Z_OK || dsize != rsize) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      }
      break;
    case Codec_LZMA:
      {
        if (sz < LZMA_PROPS_SIZE+1 || vs[0] != LZMA_PROPS_SIZE) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken LZMA data in `ChiroUnpack()`", -1);
          return;
        }

        usize srcSize = sz-vs[0]-1;
        ELzmaStatus status;
        SRes zres = LzmaDecode(cast(ubyte *)cbuf, &dsize, cast(const(ubyte) *)vs+vs[0]+1, &srcSize,
                               cast(const(ubyte)*)(vs+1)/*propData*/, vs[0]/*propSize*/, LZMA_FINISH_ANY, &status, &lzmaDefAllocator);
        if (zres != SZ_OK || dsize != rsize || status == LZMA_STATUS_FINISHED_WITH_MARK || status == LZMA_STATUS_NEEDS_MORE_INPUT) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken LZMA data in `ChiroUnpack()`", -1);
          return;
        }
      }
      break;
    case Codec_BALZ:
      version(use_balz) {
        uint spos = 0;
        uint outpos = 0;
        try {
          Unbalz bz;
          auto dc = bz.decompress(
            // reader
            (buf) {
              uint left = cast(uint)sz-spos;
              if (left > buf.length) left = cast(uint)buf.length;
              if (left != 0) memcpy(buf.ptr, vs, left);
              spos += left;
              return left;
            },
            // writer
            (buf) {
              uint left = rsize-outpos;
              if (left == 0) throw new Exception("broken data");
              if (left > buf.length) left = cast(uint)buf.length;
              if (left) memcpy(cbuf+outpos, buf.ptr, left);
              outpos += left;
            },
          );
          if (dc != rsize) throw new Exception("broken data");
        } catch (Exception) {
          outpos = uint.max;
        }
        if (outpos == uint.max) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
        dsize = outpos;
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_XPAK:
      version(use_libxpack) {
        xpack_decompressor *dcp = xpack_alloc_decompressor();
        if (dcp is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
        auto rc = xpack_decompress(dcp, vs, cast(usize)sz, cbuf, rsize, null);
        if (rc != DECOMPRESS_SUCCESS) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_BRLZ:
      version(use_libbrieflz) {
        dsize = blz_depack_safe(vs, cast(uint)sz, cbuf, rsize);
        if (dsize != rsize) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_LZFS:
      version(use_liblzfse) {
        immutable usize wbsize = lzfse_decode_scratch_size();
        void* wbuf = cast(void*)malloc(wbsize+!wbsize);
        if (wbuf is null) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
        dsize = lzfse_decode_buffer(cbuf, cast(usize)rsize, vs, cast(usize)sz, wbuf);
        free(wbuf);
        if (dsize == 0 || dsize != rsize) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_LZJB:
      version(use_lzjb) {
        dsize = lzjb_decompress(vs, cast(usize)sz, cbuf, rsize);
        if (dsize != rsize) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_LZMS:
      version(use_libwim_lzms) {
        wimlib_decompressor* dpr;
        int rc = wimlib_create_decompressor(WIMLIB_COMPRESSION_TYPE_LZMS, rsize, &dpr);
        if (rc != 0) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
        rc = wimlib_decompress(vs, cast(usize)sz, cbuf, rsize, dpr);
        wimlib_free_decompressor(dpr);
        if (rc != 0) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_LZMX:
      version(use_libwim_lzx) {
        wimlib_decompressor* dpr;
        int rc = wimlib_create_decompressor(WIMLIB_COMPRESSION_TYPE_LZX, rsize, &dpr);
        if (rc != 0) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
        rc = wimlib_decompress(vs, cast(usize)sz, cbuf, rsize, dpr);
        wimlib_free_decompressor(dpr);
        if (rc != 0) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_XPRS:
      version(use_libwim_xpress) {
        wimlib_decompressor* dpr;
        uint csz = WIMLIB_XPRESS_MIN_CHUNK;
        while (csz < WIMLIB_XPRESS_MAX_CHUNK && csz < rsize) csz *= 2U;
        int rc = wimlib_create_decompressor(WIMLIB_COMPRESSION_TYPE_XPRESS, csz, &dpr);
        if (rc != 0) { free(cbuf); sqlite3_result_error_nomem(ctx); return; }
        rc = wimlib_decompress(vs, cast(usize)sz, cbuf, rsize, dpr);
        wimlib_free_decompressor(dpr);
        if (rc != 0) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_LZ4D:
      version(use_lz4) {
        dsize = LZ4_decompress_safe(vs, cbuf, sz, rsize);
        if (dsize != rsize) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
    case Codec_ZSTD:
      version(use_zstd) {
        dsize = ZSTD_decompress(cbuf, rsize, vs, sz);
        if (ZSTD_isError(dsize) || dsize != rsize) {
          free(cbuf);
          sqlite3_result_error(ctx, "broken data in `ChiroUnpack()`", -1);
          return;
        }
      } else {
        free(cbuf);
        sqlite3_result_error(ctx, "unsupported compression in `ChiroUnpack()`", -1);
        return;
      }
      break;
  }

  if (isGoodText(cbuf[0..dsize])) {
    sqlite3_result_text(ctx, cbuf, cast(int)dsize, &free);
  } else {
    sqlite3_result_blob(ctx, cbuf, cast(int)dsize, &free);
  }
}


/*
** ChiroNormCRLF(content)
**
** Replaces CR/LF with LF, `\x7f` with `~`, control chars (except TAB and CR) with spaces.
** Removes trailing blanks.
*/
private void sq3Fn_ChiroNormCRLF (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroNormCRLF()`", -1); return; }

  int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0 || sz > 0x3fffffff) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroNormCRLF()`", -1); return; }

  // check if we have something to do, and calculate new string size
  bool needwork = false;
  if (vs[cast(uint)sz-1] <= 32) {
    needwork = true;
    while (sz > 0 && vs[cast(uint)sz-1] <= 32) --sz;
    if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }
  }
  uint newsz = cast(uint)sz;
  foreach (immutable idx, immutable char ch; vs[0..cast(uint)sz]) {
    if (ch == 13) {
      needwork = true;
      if (idx+1 < cast(uint)sz && vs[idx+1] == 10) --newsz;
    } else if (!needwork) {
      needwork = ((ch < 32 && ch != 9 && ch != 10) || ch == 127);
    }
  }

  if (!needwork) {
    if (sqlite3_value_type(argv[0]) == SQLITE3_TEXT) sqlite3_result_value(ctx, argv[0]);
    else sqlite3_result_text(ctx, vs, sz, SQLITE_TRANSIENT);
    return;
  }

  assert(newsz && newsz <= cast(uint)sz);

  // need a new string
  import core.stdc.stdlib : malloc, free;
  char* newstr = cast(char*)malloc(newsz);
  if (newstr is null) { sqlite3_result_error_nomem(ctx); return; }
  char* dest = newstr;
  foreach (immutable idx, immutable char ch; vs[0..cast(uint)sz]) {
    if (ch == 13) {
      if (idx+1 < cast(uint)sz && vs[idx+1] == 10) {} else *dest++ = ' ';
    } else {
           if (ch == 127) *dest++ = '~';
      else if (ch == 11 || ch == 12) *dest++ = '\n';
      else if (ch < 32 && ch != 9 && ch != 10) *dest++ = ' ';
      else *dest++ = ch;
    }
  }
  assert(dest == newstr+newsz);

  sqlite3_result_text(ctx, newstr, cast(int)newsz, &free);
}


/*
** ChiroNormHeaders(content)
**
** Replaces CR/LF with LF, `\x7f` with `~`, control chars (except CR) with spaces.
** Then replaces 'space, LF' with a single space (joins multiline headers).
** Removes trailing blanks.
*/
private void sq3Fn_ChiroNormHeaders (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroNormHeaders()`", -1); return; }

  int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0 || sz > 0x3fffffff) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroNormHeaders()`", -1); return; }

  // check if we have something to do, and calculate new string size
  bool needwork = false;
  if (vs[cast(uint)sz-1] <= 32) {
    needwork = true;
    while (sz > 0 && vs[cast(uint)sz-1] <= 32) --sz;
    if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }
  }
  uint newsz = cast(uint)sz;
  foreach (immutable idx, immutable char ch; vs[0..cast(uint)sz]) {
    if (ch == 13) {
      needwork = true;
      if (idx+1 < cast(uint)sz && vs[idx+1] == 10) --newsz;
    } else if (ch == 10) {
      if (idx+1 < cast(uint)sz && vs[idx+1] <= 32) { needwork = true; --newsz; }
    } else if (!needwork) {
      needwork = ((ch < 32 && ch != 10) || ch == 127);
    }
  }

  if (!needwork) {
    if (sqlite3_value_type(argv[0]) == SQLITE3_TEXT) sqlite3_result_value(ctx, argv[0]);
    else sqlite3_result_text(ctx, vs, sz, SQLITE_TRANSIENT);
    return;
  }

  assert(newsz && newsz <= cast(uint)sz);

  // need a new string
  import core.stdc.stdlib : malloc, free;
  char* newstr = cast(char*)malloc(newsz);
  if (newstr is null) { sqlite3_result_error_nomem(ctx); return; }
  char* dest = newstr;
  foreach (immutable idx, immutable char ch; vs[0..cast(uint)sz]) {
    if (ch == 13) {
      if (idx+1 < cast(uint)sz && vs[idx+1] == 10) {} else *dest++ = ' ';
    } else if (ch == 10) {
      if (idx+1 < cast(uint)sz && vs[idx+1] <= 32) {} else *dest++ = '\n';
    } else {
           if (ch == 127) *dest++ = '~';
      else if (ch < 32 && ch != 10) *dest++ = ' ';
      else *dest++ = ch;
    }
  }
  assert(dest == newstr+newsz);

  sqlite3_result_text(ctx, newstr, cast(int)newsz, &free);
}


/*
** ChiroExtractHeaders(content)
**
** Replaces CR/LF with LF, `\x7f` with `~`, control chars (except CR) with spaces.
** Then replaces 'space, LF' with a single space (joins multiline headers).
** Removes trailing blanks.
*/
private void sq3Fn_ChiroExtractHeaders (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroExtractHeaders()`", -1); return; }

  int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0 || sz > 0x3fffffff) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroExtractHeaders()`", -1); return; }

  // slice headers
  sz = sq3Supp_FindHeadersEnd(vs, sz);

  // strip trailing blanks
  while (sz > 0 && vs[cast(uint)sz-1U] <= 32) --sz;
  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  // allocate new string (it can be smaller, but will never be bigger)
  import core.stdc.stdlib : malloc, free;
  char* newstr = cast(char*)malloc(cast(uint)sz);
  if (newstr is null) { sqlite3_result_error_nomem(ctx); return; }
  char* dest = newstr;
  foreach (immutable idx, immutable char ch; vs[0..cast(uint)sz]) {
    if (ch == 13) {
      if (idx+1 < cast(uint)sz && vs[idx+1] == 10) {} else *dest++ = ' ';
    } else if (ch == 10) {
      if (idx+1 < cast(uint)sz && vs[idx+1] <= 32) {} else *dest++ = '\n';
    } else {
           if (ch == 127) *dest++ = '~';
      else if (ch < 32 && ch != 10) *dest++ = ' ';
      else *dest++ = ch;
    }
  }
  assert(dest <= newstr+cast(uint)sz);
  sz = cast(int)cast(usize)(dest-newstr);
  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }
  sqlite3_result_text(ctx, newstr, sz, &free);
}


/*
** ChiroExtractBody(content)
**
** Replaces CR/LF with LF, `\x7f` with `~`, control chars (except TAB and CR) with spaces.
** Then replaces 'space, LF' with a single space (joins multiline headers).
** Removes trailing blanks and final dot.
*/
private void sq3Fn_ChiroExtractBody (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroExtractHeaders()`", -1); return; }

  int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0 || sz > 0x3fffffff) { sqlite3_result_error_toobig(ctx); return; }

  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroExtractHeaders()`", -1); return; }

  // slice body
  immutable int bstart = sq3Supp_FindHeadersEnd(vs, sz);
  if (bstart >= sz) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }
  vs += bstart;
  sz -= bstart;

  // strip trailing dot
       if (sz >= 2 && vs[cast(uint)sz-2U] == '\r' && vs[cast(uint)sz-1U] == '\n') sz -= 2;
  else if (sz >= 1 && vs[cast(uint)sz-1U] == '\n') --sz;
       if (sz == 1 && vs[0] == '.') sz = 0;
  else if (sz >= 2 && vs[cast(uint)sz-2U] == '\n' && vs[cast(uint)sz-1U] == '.') --sz;
  else if (sz >= 2 && vs[cast(uint)sz-2U] == '\r' && vs[cast(uint)sz-1U] == '.') --sz;

  // strip trailing blanks
  while (sz > 0 && vs[cast(uint)sz-1U] <= 32) --sz;
  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }

  // allocate new string (it can be smaller, but will never be bigger)
  import core.stdc.stdlib : malloc, free;
  char* newstr = cast(char*)malloc(cast(uint)sz);
  if (newstr is null) { sqlite3_result_error_nomem(ctx); return; }
  char* dest = newstr;
  foreach (immutable idx, immutable char ch; vs[0..cast(uint)sz]) {
    if (ch == 13) {
      if (idx+1 < cast(uint)sz && vs[idx+1] == 10) {} else *dest++ = ' ';
    } else {
           if (ch == 127) *dest++ = '~';
      else if (ch == 11 || ch == 12) *dest++ = '\n';
      else if (ch < 32 && ch != 9 && ch != 10) *dest++ = ' ';
      else *dest++ = ch;
    }
  }
  assert(dest <= newstr+cast(uint)sz);
  sz = cast(int)cast(usize)(dest-newstr);
  if (sz == 0) { sqlite3_result_text(ctx, "", 0, SQLITE_STATIC); return; }
  sqlite3_result_text(ctx, newstr, sz, &free);
}


/*
** ChiroRIPEMD160(content)
**
** Calculates RIPEMD160 hash over the given content.
**
** Returns BINARY BLOB! You can use `tolower(hex(ChiroRIPEMD160(contents)))`
** to get lowercased hex hash string.
*/
private void sq3Fn_ChiroRIPEMD160 (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to `ChiroRIPEMD160()`", -1); return; }

  immutable int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0) { sqlite3_result_error_toobig(ctx); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs && sz == 0) vs = "";
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in `ChiroRIPEMD160()`", -1); return; }

  RIPEMD160_Ctx rmd;
  ripemd160_put(ref rmd, vs[0..cast(uint)sz]);
  ubyte[RIPEMD160_BYTES] hash = ripemd160_finish(ref rmd);
  sqlite3_result_blob(ctx, cast(const(char)*)hash.ptr, cast(int)hash.length, SQLITE_TRANSIENT);
}


enum HeaderProcStartTpl(string fnname) = `
  if (argc != 1) { sqlite3_result_error(ctx, "invalid number of arguments to \"`~fnname~`()\"", -1); return; }

  immutable int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0) { sqlite3_result_error_toobig(ctx); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs && sz == 0) vs = "";
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in \"`~fnname~`()\"", -1); return; }

  const(char)[] hdrs = vs[0..cast(usize)sq3Supp_FindHeadersEnd(vs, sz)];
`;


/*
** ChiroHdr_NNTPIndex(headers)
**
** The content must be email with headers (or headers only).
** Returns "NNTP-Index" field or zero (int).
*/
private void sq3Fn_ChiroHdr_NNTPIndex (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  mixin(HeaderProcStartTpl!"ChiroHdr_NNTPIndex");

  uint nntpidx = 0;

  auto nntpidxfld = findHeaderField(hdrs, "NNTP-Index");
  if (nntpidxfld.length) {
    auto id = nntpidxfld.getFieldValue;
    if (id.length) {
      foreach (immutable ch; id) {
        if (ch < '0' || ch > '9') { nntpidx = 0; break; }
        if (nntpidx == 0 && ch == '0') continue;
        immutable uint nn = nntpidx*10u+(ch-'0');
        if (nn <= nntpidx) nntpidx = 0x7fffffff; else nntpidx = nn;
      }
    }
  }

  // it is safe, it can't overflow
  sqlite3_result_int(ctx, cast(int)nntpidx);
}


/*
** ChiroHdr_RecvTime(headers)
**
** The content must be email with headers (or headers only).
** Returns unixtime (can be zero).
*/
private void sq3Fn_ChiroHdr_RecvTime (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  mixin(HeaderProcStartTpl!"ChiroHdr_RecvTime");

  uint msgtime = 0; // message receiving time

  auto datefld = findHeaderField(hdrs, "Injection-Date");
  if (datefld.length != 0) {
    auto v = datefld.getFieldValue;
    try {
      msgtime = parseMailDate(v);
    } catch (Exception) {
      //writeln("UID=", uid, ": FUCKED INJECTION-DATE: |", v, "|");
      msgtime = 0; // just in case
    }
  }

  if (!msgtime) {
    // obsolete NNTP date field, because why not?
    datefld = findHeaderField(hdrs, "NNTP-Posting-Date");
    if (datefld.length != 0) {
      auto v = datefld.getFieldValue;
      try {
        msgtime = parseMailDate(v);
      } catch (Exception) {
        //writeln("UID=", uid, ": FUCKED NNTP-POSTING-DATE: |", v, "|");
        msgtime = 0; // just in case
      }
    }
  }

  if (!msgtime) {
    datefld = findHeaderField(hdrs, "Date");
    if (datefld.length != 0) {
      auto v = datefld.getFieldValue;
      try {
        msgtime = parseMailDate(v);
      } catch (Exception) {
        //writeln("UID=", uid, ": FUCKED DATE: |", v, "|");
        msgtime = 0; // just in case
      }
    }
  }

  // finally, try to get time from "Received:"
  //Received: from dns9.fly.us ([131.103.96.154]) by np5-d2.fly.us with Microsoft SMTPSVC(5.0.2195.6824); Tue, 21 Mar 2017 17:35:54 -0400
  if (!msgtime) {
    //writeln("!!! --- !!!");
    uint lowesttime = uint.max;
    foreach (uint fidx; 0..uint.max) {
      auto recvfld = findHeaderField(hdrs, "Received", fidx);
      if (recvfld.length == 0) break;
      auto lsemi = recvfld.lastIndexOf(';');
      if (lsemi >= 0) recvfld = recvfld[lsemi+1..$].xstrip;
      if (recvfld.length != 0) {
        auto v = recvfld.getFieldValue;
        uint tm = 0;
        try {
          tm = parseMailDate(v);
        } catch (Exception) {
          //writeln("UID=", uid, ": FUCKED RECV DATE: |", v, "|");
          tm = 0; // just in case
        }
        //writeln(tm, " : ", lowesttime);
        if (tm && tm < lowesttime) lowesttime = tm;
      }
    }
    if (lowesttime != uint.max) msgtime = lowesttime;
  }

  sqlite3_result_int64(ctx, msgtime);
}


/*
** ChiroHdr_FromEmail(headers)
**
** The content must be email with headers (or headers only).
** Returns email "From" field.
*/
private void sq3Fn_ChiroHdr_FromEmail (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  mixin(HeaderProcStartTpl!"ChiroHdr_FromEmail");
  auto from = findHeaderField(hdrs, "From").extractMail;
  if (from.length == 0) {
    sqlite3_result_text(ctx, "nobody@nowhere", -1, SQLITE_STATIC);
  } else {
    sqlite3_result_text(ctx, from.ptr, cast(int)from.length, SQLITE_TRANSIENT);
  }
}


/*
** ChiroHdr_ToEmail(headers)
**
** The content must be email with headers (or headers only).
** Returns email "From" field.
*/
private void sq3Fn_ChiroHdr_ToEmail (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  mixin(HeaderProcStartTpl!"ChiroHdr_ToEmail");
  auto to = findHeaderField(hdrs, "To").extractMail;
  if (to.length == 0) {
    sqlite3_result_text(ctx, "nobody@nowhere", -1, SQLITE_STATIC);
  } else {
    sqlite3_result_text(ctx, to.ptr, cast(int)to.length, SQLITE_TRANSIENT);
  }
}


/*
** ChiroHdr_Subj(headers)
**
** The content must be email with headers (or headers only).
** Returns email "From" field.
*/
private void sq3Fn_ChiroHdr_Subj (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  mixin(HeaderProcStartTpl!"sq3Fn_ChiroHdr_Subj");
  auto subj = findHeaderField(hdrs, "Subject").decodeSubj.subjRemoveRe;
  if (subj.length == 0) {
    sqlite3_result_text(ctx, "", 0, SQLITE_STATIC);
  } else {
    sqlite3_result_text(ctx, subj.ptr, cast(int)subj.length, SQLITE_TRANSIENT);
  }
}


/*
** ChiroHdr_Field(headers, fieldname)
**
** The content must be email with headers (or headers only).
** Returns field value as text, or NULL if there is no such field.
*/
private void sq3Fn_ChiroHdr_Field (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc != 2) { sqlite3_result_error(ctx, "invalid number of arguments to \"ChiroHdr_Field()\"", -1); return; }

  immutable int sz = sqlite3_value_bytes(argv[0]);
  if (sz < 0) { sqlite3_result_error_toobig(ctx); return; }

  const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
  if (!vs && sz == 0) vs = "";
  if (!vs) { sqlite3_result_error(ctx, "cannot get blob data in \"ChiroHdr_Field()\"", -1); return; }

  immutable int fldsz = sqlite3_value_bytes(argv[1]);
  if (fldsz < 0) { sqlite3_result_error_toobig(ctx); return; }

  const(char)* fldname = cast(const(char) *)sqlite3_value_blob(argv[1]);
  if (!fldname && fldsz == 0) fldname = "";
  if (!fldname) { sqlite3_result_error(ctx, "cannot get blob data in \"ChiroHdr_Field()\"", -1); return; }

  const(char)[] hdrs = vs[0..cast(usize)sq3Supp_FindHeadersEnd(vs, sz)];
  auto value = findHeaderField(hdrs, fldname[0..fldsz]);
  if (value is null) {
    sqlite3_result_null(ctx);
  } else if (value.length == 0) {
    sqlite3_result_text(ctx, "", 0, SQLITE_STATIC);
  } else {
    sqlite3_result_text(ctx, value.ptr, cast(int)value.length, SQLITE_TRANSIENT);
  }
}


/*
** ChiroTimerStart([msg])
**
** The content must be email with headers (or headers only).
** Returns email "From" field.
*/
private void sq3Fn_ChiroTimerStart (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  if (argc > 1) { sqlite3_result_error(ctx, "invalid number of arguments to \"ChiroTimerStart()\"", -1); return; }

  delete chiTimerMsg;

  if (argc == 1) {
    immutable int sz = sqlite3_value_bytes(argv[0]);
    if (sz > 0) {
      const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
      if (vs) {
        chiTimerMsg = new char[cast(usize)sz];
        chiTimerMsg[0..cast(usize)sz] = vs[0..cast(usize)sz];
        writeln("started ", chiTimerMsg, "...");
      }
    }
  }

  sqlite3_result_int(ctx, 1);
  chiTimer.restart();
}


/*
** ChiroTimerStop([msg])
**
** The content must be email with headers (or headers only).
** Returns email "From" field.
*/
private void sq3Fn_ChiroTimerStop (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  chiTimer.stop;
  if (argc > 1) { sqlite3_result_error(ctx, "invalid number of arguments to \"ChiroTimerStop()\"", -1); return; }

  if (ChiroTimerEnabled) {
    if (argc == 1) {
      delete chiTimerMsg;
      immutable int sz = sqlite3_value_bytes(argv[0]);
      if (sz > 0) {
        const(char)* vs = cast(const(char) *)sqlite3_value_blob(argv[0]);
        if (vs) {
          chiTimerMsg = new char[cast(usize)sz];
          chiTimerMsg[0..cast(usize)sz] = vs[0..cast(usize)sz];
        }
      }
    }

    char[128] buf;
    auto tstr = chiTimer.toBuffer(buf[]);
    if (chiTimerMsg.length) {
      writeln("done ", chiTimerMsg, ": ", tstr);
    } else {
      writeln("time: ", tstr);
    }
  }

  delete chiTimerMsg;

  sqlite3_result_int(ctx, 1);
}


/*
** ChiroGlob(pat, str)
**
** GLOB replacement, with extended word matching.
*/
private void sq3Fn_ChiroGlob_common (sqlite3_context *ctx, int argc, sqlite3_value **argv, int casesens,
                                     uint stridx=1, uint patidx=0)
{
  if (argc != 2) { sqlite3_result_error(ctx, "invalid number of arguments to \"ChiroGlob()\"", -1); return; }

  immutable int patsz = sqlite3_value_bytes(argv[patidx]);
  if (patsz < 0) { sqlite3_result_error_toobig(ctx); return; }

  const(char)* pat = cast(const(char) *)sqlite3_value_blob(argv[patidx]);
  if (!pat && patsz == 0) pat = "";
  if (!pat) { sqlite3_result_error(ctx, "cannot get blob data in \"ChiroGlob()\"", -1); return; }

  immutable int strsz = sqlite3_value_bytes(argv[stridx]);
  if (strsz < 0) { sqlite3_result_error_toobig(ctx); return; }

  const(char)* str = cast(const(char) *)sqlite3_value_blob(argv[stridx]);
  if (!str && strsz == 0) str = "";
  if (!str) { sqlite3_result_error(ctx, "cannot get blob data in \"ChiroGlob()\"", -1); return; }

  immutable bool res =
    casesens ?
      globmatch(str[0..cast(usize)strsz], pat[0..cast(usize)patsz]) :
      globmatchCI(str[0..cast(usize)strsz], pat[0..cast(usize)patsz]);

  sqlite3_result_int(ctx, (res ? 1 : 0));
}


/*
** ChiroGlobSQL(pat, str)
**
** GLOB replacement, with extended word matching.
*/
private void sq3Fn_ChiroGlobSQL (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  sq3Fn_ChiroGlob_common(ctx, argc, argv, casesens:1);
}

/*
** ChiroGlob(str, pat)
**
** GLOB replacement, with extended word matching.
*/
private void sq3Fn_ChiroGlob (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  sq3Fn_ChiroGlob_common(ctx, argc, argv, casesens:1, stridx:0, patidx:1);
}

/*
** ChiroGlobCI(str, pat)
**
** GLOB replacement, with extended word matching.
*/
private void sq3Fn_ChiroGlobCI (sqlite3_context *ctx, int argc, sqlite3_value **argv) {
  sq3Fn_ChiroGlob_common(ctx, argc, argv, casesens:0, stridx:0, patidx:1);
}


// ////////////////////////////////////////////////////////////////////////// //
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroRegisterSQLite3Functions (ref Database db) {
  sqlite3_busy_timeout(db.getHandle, 20000); // busy timeout: 20 seconds

  immutable int rc = sqlite3_extended_result_codes(db.getHandle, 1);
  if (rc != SQLITE_OK) {
    import core.stdc.stdio : stderr, fprintf;
    fprintf(stderr, "SQLITE WARNING: cannot enable extended result codes (this is harmless).\n");
  }
  db.createFunction("glob", 2, &sq3Fn_ChiroGlobSQL, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroGlob", 2, &sq3Fn_ChiroGlob, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroGlobCI", 2, &sq3Fn_ChiroGlobCI, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);

  db.createFunction("ChiroPack", 1, &sq3Fn_ChiroPack, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroPack", 2, &sq3Fn_ChiroPackDPArg, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroUnpack", 1, &sq3Fn_ChiroUnpack, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);

  db.createFunction("ChiroPackLZMA", 1, &sq3Fn_ChiroPackLZMA, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroPackLZMA", 2, &sq3Fn_ChiroPackLZMA, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);

  db.createFunction("ChiroGetPackType", 1, &sq3Fn_ChiroGetPackType, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);

  db.createFunction("ChiroNormCRLF", 1, &sq3Fn_ChiroNormCRLF, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroNormHeaders", 1, &sq3Fn_ChiroNormHeaders, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroExtractHeaders", 1, &sq3Fn_ChiroExtractHeaders, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroExtractBody", 1, &sq3Fn_ChiroExtractBody, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroRIPEMD160", 1, &sq3Fn_ChiroRIPEMD160, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);

  db.createFunction("ChiroHdr_NNTPIndex", 1, &sq3Fn_ChiroHdr_NNTPIndex, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroHdr_RecvTime", 1, &sq3Fn_ChiroHdr_RecvTime, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroHdr_FromEmail", 1, &sq3Fn_ChiroHdr_FromEmail, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroHdr_ToEmail", 1, &sq3Fn_ChiroHdr_ToEmail, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroHdr_Subj", 1, &sq3Fn_ChiroHdr_Subj, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroHdr_Field", 2, &sq3Fn_ChiroHdr_Field, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);

  db.createFunction("ChiroTimerStart", 0, &sq3Fn_ChiroTimerStart, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroTimerStart", 1, &sq3Fn_ChiroTimerStart, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroTimerStop", 0, &sq3Fn_ChiroTimerStop, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
  db.createFunction("ChiroTimerStop", 1, &sq3Fn_ChiroTimerStop, moreflags:/*SQLITE_DIRECTONLY*/SQLITE_INNOCUOUS);
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroRecreateStorageDB (const(char)[] dbname=ExpandedMailDBPath~StorageDBName) {
  try { import std.file : remove; remove(dbname); } catch (Exception) {}
  dbStore = Database(dbname, Database.Mode.ReadWriteCreate, dbpragmasRWStorageRecreate, schemaStorage);
  chiroRegisterSQLite3Functions(dbStore);
  dbStore.setOnClose(schemaStorageIndex~dbpragmasRWStorage~"ANALYZE;");
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroRecreateViewDB (const(char)[] dbname=ExpandedMailDBPath~SupportDBName) {
  try { import std.file : remove; remove(dbname); } catch (Exception) {}
  dbView = Database(dbname, Database.Mode.ReadWriteCreate, dbpragmasRWSupportRecreate, schemaSupportTable);
  chiroRegisterSQLite3Functions(dbView);
  dbView.setOnClose(schemaSupportIndex~dbpragmasRWSupport~"ANALYZE;");
}


public void chiroCreateViewIndiciesDB () {
  dbView.setOnClose(dbpragmasRWSupport~"ANALYZE;");
  dbView.execute(schemaSupportIndex);
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroRecreateConfDB (const(char)[] dbname=ExpandedMailDBPath~OptionsDBName) {
  try { import std.file : remove; remove(dbname); } catch (Exception) {}
  dbConf = Database(dbname, Database.Mode.ReadWriteCreate, dbpragmasRWOptionsRecreate, schemaOptions);
  chiroRegisterSQLite3Functions(dbConf);
  dbConf.setOnClose(schemaOptionsIndex~dbpragmasRWOptions~"ANALYZE;");
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroOpenStorageDB (const(char)[] dbname=ExpandedMailDBPath~StorageDBName, bool readonly=false) {
  dbStore = Database(dbname, (readonly ? Database.Mode.ReadOnly : Database.Mode.ReadWrite), (readonly ? dbpragmasRO : dbpragmasRWStorage), schemaStorage);
  chiroRegisterSQLite3Functions(dbStore);
  if (!readonly) dbStore.setOnClose("PRAGMA optimize;");
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroOpenViewDB (const(char)[] dbname=ExpandedMailDBPath~SupportDBName, bool readonly=false) {
  dbView = Database(dbname, (readonly ? Database.Mode.ReadOnly : Database.Mode.ReadWrite), (readonly ? dbpragmasRO : dbpragmasRWSupport), schemaSupport);
  chiroRegisterSQLite3Functions(dbView);
  if (!readonly) {
    dbView.execute(schemaSupportTempTables);
    dbView.setOnClose("PRAGMA optimize;");
  }
}


// ////////////////////////////////////////////////////////////////////////// //
public void chiroOpenConfDB (const(char)[] dbname=ExpandedMailDBPath~OptionsDBName, bool readonly=false) {
  dbConf = Database(dbname, (readonly ? Database.Mode.ReadOnly : Database.Mode.ReadWrite), (readonly ? dbpragmasRO : dbpragmasRWOptions), schemaOptions);
  chiroRegisterSQLite3Functions(dbConf);
  if (!readonly) dbConf.setOnClose("PRAGMA optimize;");
}


// ////////////////////////////////////////////////////////////////////////// //
/**
recreates FTS5 (full-text search) info.
*/
public void chiroRecreateFTS5 (bool repopulate=true) {
  dbView.execute(recreateFTS5);
  if (repopulate) dbView.execute(repopulateFTS5);
  dbView.execute(recreateFTS5Triggers);
}


// ////////////////////////////////////////////////////////////////////////// //
extern(C) {
static void errorLogCallback (void *pArg, int rc, const char *zMsg) {
  if (ChiroSQLiteSilent) return;
  import core.stdc.stdio : stderr, fprintf;
  switch (rc) {
    case SQLITE_NOTICE: fprintf(stderr, "***SQLITE NOTICE: %s\n", zMsg); break;
    case SQLITE_NOTICE_RECOVER_WAL: fprintf(stderr, "***SQLITE NOTICE (WAL RECOVER): %s\n", zMsg); break;
    case SQLITE_NOTICE_RECOVER_ROLLBACK: fprintf(stderr, "***SQLITE NOTICE (ROLLBACK RECOVER): %s\n", zMsg); break;
    /* */
    case SQLITE_WARNING: fprintf(stderr, "***SQLITE WARNING: %s\n", zMsg); break;
    case SQLITE_WARNING_AUTOINDEX: fprintf(stderr, "***SQLITE AUTOINDEX WARNING: %s\n", zMsg); break;
    /* */
    case SQLITE_CANTOPEN:
    case SQLITE_SCHEMA:
      break; // ignore those
    /* */
    default: fprintf(stderr, "***SQLITE LOG(%d) [%s]: %s\n", rc, sqlite3_errstr(rc), zMsg); break;
  }
}
}


static string sqerrstr (immutable int rc) nothrow @trusted {
  const(char)* msg = sqlite3_errstr(rc);
  if (!msg || !msg[0]) return null;
  import core.stdc.string : strlen;
  return msg[0..strlen(msg)].idup;
}


static void sqconfigcheck (immutable int rc, string msg, bool fatal) {
  if (rc == SQLITE_OK) return;
  if (fatal) {
    string errmsg = sqerrstr(rc);
    throw new Exception("FATAL: "~msg~": "~errmsg);
  } else {
    if (msg is null) msg = "";
    import core.stdc.stdio : stderr, fprintf;
    fprintf(stderr, "SQLITE WARNING: %.*s (this is harmless): %s\n", cast(uint)msg.length, msg.ptr, sqlite3_errstr(rc));
  }
}


// call this BEFORE opening any SQLite database connection!
public void chiroSwitchToSingleThread () {
  sqconfigcheck(sqlite3_config(SQLITE_CONFIG_SINGLETHREAD), "cannot set single-threaded mode", fatal:false);
}


public string MailDBPath () nothrow @trusted @nogc { return ExpandedMailDBPath; }


public void MailDBPath(T:const(char)[]) (T mailpath) nothrow @trusted {
  while (mailpath.length > 1 && mailpath[$-1] == '/') mailpath = mailpath[0..$-1];

  if (mailpath.length == 0 || mailpath == ".") {
    ExpandedMailDBPath = "";
    return;
  }

  if (mailpath[0] == '~') {
    char[] dpath = new char[mailpath.length+4096];
    dpath = expandTilde(dpath, mailpath);

    while (dpath.length > 1 && dpath[$-1] == '/') dpath = dpath[0..$-1];
    dpath ~= '/';
    ExpandedMailDBPath = cast(string)dpath; // it is safe to cast here
  } else {
    char[] dpath = new char[mailpath.length+1];
    dpath[0..$-1] = mailpath[];
    dpath[$-1] = '/';
    ExpandedMailDBPath = cast(string)dpath; // it is safe to cast here
  }
}


shared static this () {
  enum {
    SQLITE_CONFIG_STMTJRNL_SPILL = 26,  /* int nByte */
    SQLITE_CONFIG_SMALL_MALLOC = 27,  /* boolean */
  }

  if (!sqlite3_threadsafe()) {
    throw new Exception("FATAL: SQLite must be compiled with threading support!");
  }

  // we are interested in all errors
  sqlite3_config(SQLITE_CONFIG_LOG, &errorLogCallback, null);

  sqconfigcheck(sqlite3_config(SQLITE_CONFIG_SERIALIZED), "cannot set SQLite serialized threading mode", fatal:true);
  sqconfigcheck(sqlite3_config(SQLITE_CONFIG_SMALL_MALLOC, 0), "cannot enable SQLite unrestriced malloc mode", fatal:false);
  sqconfigcheck(sqlite3_config(SQLITE_CONFIG_URI, 1), "cannot enable SQLite URI handling", fatal:false);
  sqconfigcheck(sqlite3_config(SQLITE_CONFIG_COVERING_INDEX_SCAN, 1), "cannot enable SQLite covering index scan", fatal:false);
  sqconfigcheck(sqlite3_config(SQLITE_CONFIG_STMTJRNL_SPILL, 512*1024), "cannot set SQLite statement journal spill threshold", fatal:false);

  MailDBPath = "~/Mail";
}


shared static ~this () {
  dbConf.close();
  dbView.close();
  dbStore.close();
}


// ////////////////////////////////////////////////////////////////////////// //
public void transacted(string dbname) (void delegate () dg) {
  if (dg is null) return;
       static if (dbname == "View" || dbname == "view") alias db = dbView;
  else static if (dbname == "Store" || dbname == "store") alias db = dbStore;
  else static if (dbname == "Conf" || dbname == "conf") alias db = dbConf;
  else static assert(0, "invalid db name: '"~dbname~"'");
  db.transacted(dg);
}


// ////////////////////////////////////////////////////////////////////////// //
public DynStr chiroGetTagMonthLimitEx(T) (T tagnameid, out int val, int defval=6)
if (is(T:const(char)[]) || is(T:uint))
{
  static if (is(T:const(char)[])) {
    alias tagname = tagnameid;
  } else {
    DynStr tagnameStr;
    static auto stGetTagName = LazyStatement!"View"(`SELECT tag AS tagname FROM tagnames WHERE tagid=:tagid LIMIT 1;`);
    foreach (auto row; stGetTagName .st.bind(":tagid", tagnameid).range) {
      tagnameStr = row.tagname!SQ3Text;
    }
    const(char)[] tagname = tagnameStr.getData;
  }

  static auto stGetMLimit = LazyStatement!"Conf"(`
    WITH RECURSIVE pth(path) AS (
      VALUES('/mainpane/msgview/monthlimit'||:tagslash||:tagname)
    UNION ALL
      SELECT
        SUBSTR(path, 1, LENGTH(path)-LENGTH(REPLACE(path, RTRIM(path, REPLACE(path, '/', '')), ''))-1)
      FROM pth
      WHERE path LIKE '/mainpane/msgview/monthlimit%'
    )
    SELECT
      --  pth.path AS path
        opt.name AS name
      , opt.value AS value
    FROM pth
    INNER JOIN options AS opt ON opt.name=pth.path
    WHERE pth.path LIKE '/mainpane/msgview/monthlimit%'
    LIMIT 1
  ;`);

  stGetMLimit.st
    .bindConstText(":tagslash", (tagname.length && tagname[0] != '/' ? "/" : ""))
    .bindConstText(":tagname", tagname);

  foreach (auto row; stGetMLimit.st.range) {
    //conwriteln("TAGNAME=<", tagname, ">; val=", row.value!int, "; sres=<", row.name!SQ3Text, ">");
    val = row.value!int;
    DynStr sres = row.name!SQ3Text;
    return sres;
  }

  val = defval;
  return DynStr();
}


public int chiroGetTagMonthLimit(T) (T tagnameid, int defval=6)
if (is(T:const(char)[]) || is(T:uint))
{
  static if (is(T:const(char)[])) {
    alias tagname = tagnameid;
  } else {
    DynStr tagnameStr;
    static auto stGetTagName = LazyStatement!"View"(`SELECT tag AS tagname FROM tagnames WHERE tagid=:tagid LIMIT 1;`);
    foreach (auto row; stGetTagName .st.bind(":tagid", tagnameid).range) {
      tagnameStr = row.tagname!SQ3Text;
    }
    const(char)[] tagname = tagnameStr.getData;
  }

  static auto stGetMLimit = LazyStatement!"Conf"(`
    WITH RECURSIVE pth(path) AS (
      VALUES('/mainpane/msgview/monthlimit'||:tagslash||:tagname)
    UNION ALL
      SELECT
        SUBSTR(path, 1, LENGTH(path)-LENGTH(REPLACE(path, RTRIM(path, REPLACE(path, '/', '')), ''))-1)
      FROM pth
      WHERE path LIKE '/mainpane/msgview/monthlimit%'
    )
    SELECT
      --  pth.path AS path
      --  opt.name AS name
        opt.value AS value
    FROM pth
    INNER JOIN options AS opt ON opt.name=pth.path
    WHERE pth.path LIKE '/mainpane/msgview/monthlimit%'
    LIMIT 1
  ;`);

  stGetMLimit.st
    .bindConstText(":tagslash", (tagname.length && tagname[0] != '/' ? "/" : ""))
    .bindConstText(":tagname", tagname);

  foreach (auto row; stGetMLimit.st.range) return row.value!int;

  return defval;
}


public void chiroDeleteOption (const(char)[] name) {
  assert(name.length != 0);
  static auto stat = LazyStatement!"Conf"(`DELETE FROM options WHERE name=:name;`);
  stat.st.bindConstText(":name", name).doAll();
}

public void chiroSetOption(T) (const(char)[] name, T value)
if (!is(T:const(DynStr)) && (__traits(isIntegral, T) || is(T:const(char)[])))
{
  assert(name.length != 0);
  static auto stat = LazyStatement!"Conf"(`
    INSERT INTO options
            ( name, value)
      VALUES(:name,:value)
    ON CONFLICT(name)
    DO UPDATE SET value=:value
  ;`);
  stat.st.bindConstText(":name", name);
  static if (is(T == typeof(null))) {
    stat.st.bindConstText(":value", "");
  } else static if (__traits(isIntegral, T)) {
    stat.st.bind(":value", value);
  } else static if (is(T:const(char)[])) {
    stat.st.bindConstText(":value", value);
  } else {
    static assert(0, "oops");
  }
  stat.st.doAll();
}

public void chiroSetOption (const(char)[] name, DynStr value) {
  assert(name.length != 0);
  //{ import std.stdio; writeln("SETOPTION(", name, "): <", value.getData, ">"); }
  static auto stat = LazyStatement!"Conf"(`
    INSERT INTO options
            ( name, value)
      VALUES(:name,:value)
    ON CONFLICT(name)
    DO UPDATE SET value=:value
  ;`);
  stat.st
    .bindConstText(":name", name)
    .bindConstText(":value", value.getData)
    .doAll();
}


public void chiroSetOptionUInts (const(char)[] name, uint v0, uint v1) {
  assert(name.length != 0);
  static auto stat = LazyStatement!"Conf"(`
    INSERT INTO options
            ( name, value)
      VALUES(:name,:value)
    ON CONFLICT(name)
    DO UPDATE SET value=:value
  ;`);
  import core.stdc.stdio : snprintf;
  char[64] value = void;
  auto vlen = snprintf(value.ptr, value.sizeof, "%u,%u", v0, v1);
  stat.st
    .bindConstText(":name", name)
    .bindConstText(":value", value[0..vlen])
    .doAll();
}


public T chiroGetOptionEx(T) (const(char)[] name, out bool exists, T defval=T.init)
if (!is(T:const(DynStr)) && (__traits(isIntegral, T) || is(T:const(char)[])))
{
  static auto stat = LazyStatement!"Conf"(`SELECT value AS value FROM options WHERE name=:name LIMIT 1;`);
  assert(name.length != 0);
  exists = false;
  foreach (auto row; stat.st.bindConstText(":name", name).range) {
    exists = true;
    return row.value!T;
  }
  return defval;
}

public T chiroGetOption(T) (const(char)[] name, T defval=T.init)
if (!is(T:const(DynStr)) && (__traits(isIntegral, T) || is(T:const(char)[])))
{
  static auto stat = LazyStatement!"Conf"(`SELECT value AS value FROM options WHERE name=:name LIMIT 1;`);
  assert(name.length != 0);
  foreach (auto row; stat.st.bindConstText(":name", name).range) {
    return row.value!T;
  }
  return defval;
}

public void chiroGetOption (ref DynStr s, const(char)[] name, const(char)[] defval=null) {
  static auto stat = LazyStatement!"Conf"(`SELECT value AS value FROM options WHERE name=:name LIMIT 1;`);
  assert(name.length != 0);
  foreach (auto row; stat.st.bindConstText(":name", name).range) {
    s = row.value!SQ3Text;
    return;
  }
  s = defval;
}


private uint parseUInt (ref SQ3Text s) {
  s = s.xstrip;
  if (s.length == 0 || !isdigit(s[0])) return uint.max;
  uint res = 0;
  while (s.length) {
    immutable int dg = s[0].digitInBase(10);
    if (dg < 0) break;
    immutable uint nr = res*10U+cast(uint)dg;
    if (nr < res) return uint.max;
    res = nr;
    s = s[1..$];
  }
  if (s.length && s[0] == ',') s = s[1..$];
  s = s.xstrip;
  return res;
}


public void chiroGetOptionUInts (ref uint v0, ref uint v1, const(char)[] name) {
  static auto stat = LazyStatement!"Conf"(`SELECT value AS value FROM options WHERE name=:name LIMIT 1;`);
  assert(name.length != 0);
  foreach (auto row; stat.st.bindConstText(":name", name).range) {
    auto s = row.value!SQ3Text;
    immutable uint rv0 = parseUInt(s);
    immutable uint rv1 = parseUInt(s);
    if (rv0 != uint.max && rv1 != uint.max && s.length == 0) {
      v0 = rv0;
      v1 = rv1;
    }
    return;
  }
}


// ////////////////////////////////////////////////////////////////////////// //
// append tag if necessary, return tagid
// tag name must be valid: not empty, and not end with a '/'
// returns 0 on invalid tag name
public uint chiroAppendTag (const(char)[] tagname, int hidden=0) {
  tagname = tagname.xstrip;
  while (tagname.length && tagname[$-1] == '/') tagname = tagname[0..$-1];
  tagname = tagname.xstrip;
  if (tagname.length == 0) return 0;
  if (tagname.indexOf('|') >= 0) return 0;

  static auto stAppendTag = LazyStatement!"View"(`
    INSERT INTO tagnames(tag, hidden, threading) VALUES(:tagname,:hidden,:threading)
    ON CONFLICT(tag)
    DO UPDATE SET hidden=hidden -- this is for "returning"
    RETURNING tagid AS tagid
  ;`);

  // alphanum tags must start with '/'
  DynStr tn;
  if (tagname[0].isalnum && tagname.indexOf(':') < 0) {
    tn = "/";
    tn ~= tagname;
    stAppendTag.st.bindConstText(":tagname", tn);
  } else {
    stAppendTag.st.bindConstText(":tagname", tagname);
  }
  stAppendTag.st
    .bind(":hidden", hidden)
    .bind(":threading", (hidden ? 0 : 1));
  foreach (auto row; stAppendTag.st.range) return row.tagid!uint;

  return 0;
}


// ////////////////////////////////////////////////////////////////////////// //
/// returns `true` if we need to update pane
/// if message is left without any tags, it will be tagged with "#hobo"
public bool chiroMessageRemoveTag (uint uid, const(char)[] tagname) {
  if (uid == 0) return false;
  tagname = tagname.xstrip;
  while (tagname.length && tagname[$-1] == '/') tagname = tagname[0..$-1];
  tagname = tagname.xstrip;
  if (tagname.length == 0) return false;
  if (tagname.indexOf('|') >= 0) return false;

  immutable tagid = chiroGetTagUid(tagname);
  if (tagid == 0) return false;

  static auto stUpdateStorageTags = LazyStatement!"Store"(`
    UPDATE messages SET tags=:tags WHERE uid=:uid
  ;`);

  static auto stUidHasTag = LazyStatement!"View"(`
    SELECT uid AS uid FROM threads WHERE tagid=:tagid AND uid=:uid LIMIT 1
  ;`);

  static auto stInsertIntoThreads = LazyStatement!"View"(`
    INSERT INTO threads(uid, tagid,appearance,time)
      VALUES(:uid, :tagid, :appr, (SELECT time FROM info WHERE uid=:uid LIMIT 1))
  ;`);

  // delete message from threads
  static auto stClearThreads = LazyStatement!"View"(`
    DELETE FROM threads WHERE tagid=:tagid AND uid=:uid
  ;`);

  static auto stGetMsgTags = LazyStatement!"View"(`
    SELECT DISTINCT(tagid) AS tagid, tt.tag AS name
    FROM threads
    INNER JOIN tagnames AS tt USING(tagid)
    WHERE uid=:uid
  ;`);


  immutable bool updatePane = (chiroGetTreePaneTableTagId() == tagid);
  bool wasChanges = false;

  transacted!"View"{
    // get tagid (possibly appending the tag)
    bool hasit = false;
    foreach (auto row; stUidHasTag.st.bind(":uid", uid).bind(":tagid", tagid).range) hasit = true;
    if (!hasit) return;

    stClearThreads.st.bind(":uid", uid).bind(":tagid", tagid).doAll((stmt) { wasChanges = true; });

    // if there were any changes, rebuild message tags
    if (!wasChanges) return;

    DynStr newtags;
    foreach (auto trow; stGetMsgTags.st.bind(":uid", uid).range) {
      auto tname = trow.name!SQ3Text;
      if (tname.length == 0) continue;
      if (newtags.length) newtags ~= "|";
      newtags ~= tname;
    }

    // if there is no tags, assign "#hobo"
    // this should not happen, but...
    if (newtags.length == 0) {
      newtags = "#hobo";
      auto hobo = chiroAppendTag(newtags, hidden:1);
      assert(hobo != 0);
      // append record for this tag to threads
      // note that there is no need to relink hobos, they should not be threaded
      //FIXME: this clears message appearance
      stInsertIntoThreads.st
        .bind(":uid", uid)
        .bind(":tagid", hobo)
        .bind(":appr", Appearance.Read)
        .doAll();
    }

    // update storage with new tag names
    assert(newtags.length);
    stUpdateStorageTags.st.bindConstText(":tags", newtags).doAll();

    // and relink threads for this tagid
    chiroSupportRelinkTagThreads(tagid);
  };

  return (wasChanges && updatePane);
}


// ////////////////////////////////////////////////////////////////////////// //
/// returns `true` if we need to update pane
public bool chiroMessageAddTag (uint uid, const(char)[] tagname) {
  if (uid == 0) return false;
  tagname = tagname.xstrip;
  while (tagname.length && tagname[$-1] == '/') tagname = tagname[0..$-1];
  tagname = tagname.xstrip;
  if (tagname.length == 0) return false;
  if (tagname.indexOf('|') >= 0) return false;

  static auto stUpdateStorageTags = LazyStatement!"Store"(`
    UPDATE messages SET tags=tags||'|'||:tagname WHERE uid=:uid
  ;`);

  static auto stUidExists = LazyStatement!"View"(`
    SELECT 1 FROM threads WHERE uid=:uid LIMIT 1
  ;`);

  static auto stUidHasTag = LazyStatement!"View"(`
    SELECT uid AS uid FROM threads WHERE tagid=:tagid AND uid=:uid LIMIT 1
  ;`);

  static auto stInsertIntoThreads = LazyStatement!"View"(`
    INSERT INTO threads(uid, tagid, appearance, time)
      VALUES(:uid, :tagid, :appr, (SELECT time FROM threads WHERE uid=:uid LIMIT 1))
  ;`);

  static auto stUnHobo = LazyStatement!"View"(`
    DELETE FROM threads WHERE tagid=:tagid AND uid=:uid
  ;`);

  bool hasuid = false;
  foreach (auto row; stUidExists.st.bind(":uid", uid).range) hasuid = true;
  if (!hasuid) return false; // nothing to do

  immutable paneTagId = chiroGetTreePaneTableTagId();
  bool updatePane = false;

  transacted!"View"{
    // get tagid (possibly appending the tag)
    uint tagid = chiroAppendTag(tagname);
    if (tagid == 0) {
      conwriteln("ERROR: cannot append tag name '", tagname, "'!");
      return;
    }

    bool hasit = false;
    foreach (auto row; stUidHasTag.st.bind(":uid", uid).bind(":tagid", tagid).range) hasit = true;
    if (hasit) return;

    // append this tag to the message in the storage
    stUpdateStorageTags.st.bind(":uid", uid).bindConstText(":tagname", tagname).doAll();

    // append record for this tag to threads
    stInsertIntoThreads.st
      .bind(":uid", uid)
      .bind(":tagid", tagid)
      .bind(":appr", Appearance.Read)
      .doAll();

    // and relink threads for this tagid
    chiroSupportRelinkTagThreads(tagid);

    // remove this message from "#hobo", if there is any
    auto hobo = chiroGetTagUid("#hobo");
    if (hobo && hobo != tagid) {
      stUnHobo.st.bind(":tagid", hobo).bind(":uid", uid).doAll();
      // there's no need to relink hobos, because they should have no links
    }

    updatePane = (tagid == paneTagId);
  };

  return updatePane;
}


/**
inserts the one message from the message storage with the given id into view storage.
parses it and such, and optionally updates threads.

doesn't update NNTP indicies and such, never relinks anything.

invalid (unknown) tags will be ignored.

returns number of processed messages.

doesn't start/end any transactions, so wrap it yourself.
*/
public bool chiroParseAndInsertOneMessage (uint uid, uint msgtime, int appearance,
                                           const(char)[] hdrs, const(char)[] body, const(char)[] tags)
{
  auto stInsThreads = dbView.statement(`
    INSERT INTO threads
            ( uid, tagid, time, appearance)
      VALUES(:uid,:tagid,:time,:appearance)
  ;`);

  auto stInsInfo = dbView.statement(`
    INSERT INTO info
            ( uid, from_name, from_mail, subj, to_name, to_mail)
      VALUES(:uid,:from_name,:from_mail,:subj,:to_name,:to_mail)
  ;`);

  auto stInsMsgId = dbView.statement(`
    INSERT INTO msgids
            ( uid, msgid, time)
      VALUES(:uid,:msgid,:time)
  ;`);

  auto stInsMsgRefId = dbView.statement(`
    INSERT INTO refids
            ( uid, idx, msgid)
      VALUES(:uid,:idx,:msgid)
  ;`);

  auto stInsContentText = dbView.statement(`
    INSERT INTO content_text
            ( uid, format, content)
      VALUES(:uid,:format, ChiroPack(:content))
  ;`);

  auto stInsContentHtml = dbView.statement(`
    INSERT INTO content_html
            ( uid, format, content)
      VALUES(:uid,:format, ChiroPack(:content))
  ;`);

  auto stInsAttach = dbView.statement(`
    INSERT INTO attaches
            ( uid, idx, mime, name, format, content)
      VALUES(:uid,:idx,:mime,:name,:format, ChiroPack(:content))
  ;`);

  bool noattaches = false; // do not store attaches?

  // create thread record for each tag (and update max nntp index)
  int tagCount = 0;
  int noAttachCount = 0;
  while (tags.length) {
    auto eep = tags.indexOf('|');
    auto tagname = (eep >= 0 ? tags[0..eep] : tags[0..$]);
    tags = (eep >= 0 ? tags[eep+1..$] : tags[0..0]);
    if (tagname.length == 0) continue;

    //immutable uint tuid = chiroGetTagUid(tagname);
    immutable uint tuid = chiroAppendTag(tagname, (tagname == "#hobo" ? 1 : 0));
    if (tuid == 0) continue;

    /* nope
    if (nntpidx > 0 && tagname.startsWith("account:")) {
      auto accname = tagname[8..$];
      stInsNNTPIdx
        .bindConstText(":accname", accname)
        .bind(":nntpidx", nntpidx)
        .doAll();
    }
    */

    if (!chiroIsTagAllowAttaches(tuid)) ++noAttachCount;
    ++tagCount;

    int app = appearance;
    if (app == Appearance.Unread) {
      if (tagname.startsWith("account:") ||
          tagname.startsWith("#spam") ||
          tagname.startsWith("#hobo"))
      {
        app = Appearance.Read;
      }
    }

    stInsThreads
      .bind(":uid", uid)
      .bind(":tagid", tuid)
      .bind(":time", msgtime)
      .bind(":appearance", app)
      .doAll();
  }
  if (!tagCount) return false;
  noattaches = (noAttachCount && noAttachCount == tagCount);

  // insert msgid
  {
    bool hasmsgid = false;
    auto msgidfield = findHeaderField(hdrs, "Message-Id");
    if (msgidfield.length) {
      auto id = msgidfield.getFieldValue;
      if (id.length) {
        hasmsgid = true;
        stInsMsgId
          .bind(":uid", uid)
          .bind("time", msgtime)
          .bindConstText(":msgid", id)
          .doAll();
      }
    }
    // if there is no msgid, create one
    if (!hasmsgid) {
      RIPEMD160_Ctx rmd;
      ripemd160_put(ref rmd, hdrs[]);
      ripemd160_put(ref rmd, body[]);
      ubyte[RIPEMD160_BYTES] digest = ripemd160_finish(ref rmd);
      char[20*2+2+16] buf = 0;
      import core.stdc.stdio : snprintf;
      import core.stdc.string : strcat;
      foreach (immutable idx, ubyte b; digest[]) snprintf(buf.ptr+idx*2, 3, "%02x", b);
      strcat(buf.ptr, "@artificial"); // it is safe, there is enough room for it
      stInsMsgId
        .bind(":uid", uid)
        .bind("time", msgtime)
        .bindConstText(":msgid", buf[0..20*2])
        .doAll();
    }
  }

  // insert references
  {
    uint refidx = 0;
    auto inreplyfld = findHeaderField(hdrs, "In-Reply-To");
    while (inreplyfld.length) {
      auto id = getNextFieldValue(inreplyfld);
      if (id.length) {
        stInsMsgRefId
          .bind(":uid", uid)
          .bind(":idx", refidx++)
          .bind(":msgid", id)
          .doAll();
      }
    }

    inreplyfld = findHeaderField(hdrs, "References");
    while (inreplyfld.length) {
      auto id = getNextFieldValue(inreplyfld);
      if (id.length) {
        stInsMsgRefId
          .bind(":uid", uid)
          .bind(":idx", refidx++)
          .bind(":msgid", id)
          .doAll();
      }
    }
  }

  // insert base content and attaches
  {
    Content[] content;
    parseContent(ref content, hdrs, body, noattaches);
    // insert text and html
    bool wasText = false, wasHtml = false;
    foreach (const ref Content cc; content) {
      if (cc.name.length) continue;
      if (noattaches && !cc.mime.startsWith("text/")) continue;
      if (!wasText && cc.mime == "text/plain") {
        wasText = true;
        stInsContentText
          .bind(":uid", uid)
          .bindConstText(":format", cc.format)
          .bindConstBlob(":content", cc.data)
          .doAll();
      } else if (!wasHtml && cc.mime == "text/html") {
        wasHtml = true;
        stInsContentHtml
          .bind(":uid", uid)
          .bindConstText(":format", cc.format)
          .bindConstBlob(":content", cc.data)
          .doAll();
      }
    }
    if (!wasText) {
        stInsContentText
          .bind(":uid", uid)
          .bindConstText(":format", "")
          .bindConstBlob(":content", "")
          .doAll();
    }
    if (!wasHtml) {
        stInsContentHtml
          .bind(":uid", uid)
          .bindConstText(":format", "")
          .bindConstBlob(":content", "")
          .doAll();
    }
    // insert everything
    uint cidx = 0;
    foreach (const ref Content cc; content) {
      if (cc.name.length == 0 && cc.mime.startsWith("text/")) continue;
      // for "no attaches" mode, still record the attach, but ignore its contents
      stInsAttach
        .bind(":uid", uid)
        .bind(":idx", cidx++)
        .bindConstText(":mime", cc.mime)
        .bindConstText(":name", cc.name)
        .bindConstText(":format", cc.name)
        .bindConstBlob(":content", (noattaches ? null : cc.data), allowNull:true)
        .doAll();
    }
  }

  // insert from/to/subj info
  // this must be done last to keep FTS5 in sync
  {
    auto subj = findHeaderField(hdrs, "Subject").decodeSubj.subjRemoveRe;
    auto from = findHeaderField(hdrs, "From");
    auto to = findHeaderField(hdrs, "To");
    stInsInfo
      .bind(":uid", uid)
      .bind(":from_name", from.extractName)
      .bind(":from_mail", from.extractMail)
      .bind(":subj", subj)
      .bind(":to_name", to.extractName)
      .bind(":to_mail", to.extractMail)
      .doAll();
  }

  return true;
}


/**
inserts the messages from the message storage with the given id into view storage.
parses it and such, and optionally updates threads.

WARNING! DOESN'T UPDATE NNTP INDICIES! this should be done by the downloader.

invalid (unknown) tags will be ignored.

returns number of processed messages.
*/
public uint chiroParseAndInsertMessages (uint stmsgid,
                                         void delegate (uint count, uint total, uint nntpidx, const(char)[] tags) progresscb=null,
                                         uint emsgid=uint.max, bool relink=true, bool asread=false)
{
  if (emsgid < stmsgid) return 0; // nothing to do

  uint count = 0;
  uint total = 0;
  if (progresscb !is null) {
    // find total number of messages to process
    foreach (auto row; dbStore.statement(`
      SELECT count(uid) AS total FROM messages WHERE uid BETWEEN :msglo AND :msghi AND tags <> ''
    ;`).bind(":msglo", stmsgid).bind(":msghi", emsgid).range)
    {
      total = row.total!uint;
      break;
    }
    if (total == 0) return 0; // why not?
  }

  transacted!"View"{
    uint[] uptagids;
    if (relink) uptagids.reserve(128);
    scope(exit) delete uptagids;

    foreach (auto mrow; dbStore.statement(`
      -- this should cache unpack results
      WITH msgunpacked(msguid, msgdata, msgtags) AS (
        SELECT uid AS msguid, ChiroUnpack(data) AS msgdata, tags AS msgtags
        FROM messages
        WHERE uid BETWEEN :msglo AND :msghi AND tags <> ''
        ORDER BY uid
      )
      SELECT
          msguid AS uid
        , msgtags AS tags
        , ChiroExtractHeaders(msgdata) AS headers
        , ChiroExtractBody(msgdata) AS body
        , ChiroHdr_NNTPIndex(msgdata) AS nntpidx
        , ChiroHdr_RecvTime(msgdata) AS msgtime
      FROM msgunpacked
    ;`).bind(":msglo", stmsgid).bind(":msghi", emsgid).range)
    {
      ++count;
      auto hdrs = mrow.headers!SQ3Text;
      auto body = mrow.body!SQ3Text;
      auto tags = mrow.tags!SQ3Text;
      uint uid = mrow.uid!uint;
      uint nntpidx = mrow.nntpidx!uint;
      uint msgtime = mrow.msgtime!uint;
      assert(tags.length);

      chiroParseAndInsertOneMessage(uid, msgtime, (asread ? 1 : 0), hdrs, body, tags);

      if (progresscb !is null) progresscb(count, total, nntpidx, tags);

      if (relink) {
        while (tags.length) {
          auto eep = tags.indexOf('|');
          auto tagname = (eep >= 0 ? tags[0..eep] : tags[0..$]);
          tags = (eep >= 0 ? tags[eep+1..$] : tags[0..0]);
          if (tagname.length == 0) continue;

          immutable uint tuid = chiroGetTagUid(tagname);
          if (tuid == 0) continue;

          bool found = false;
          foreach (immutable n; uptagids) if (n == tuid) { found = true; break; }
          if (!found) uptagids ~= tuid;
        }
      }
    }

    if (relink && uptagids.length) {
      foreach (immutable tagid; uptagids) chiroSupportRelinkTagThreads(tagid);
    }
  };

  return count;
}


/**
returns accouint uid (accid) or 0.
*/
public uint chiroGetAccountUid (const(char)[] accname) {
  static auto stat = LazyStatement!"Conf"(`SELECT accid AS accid FROM accounts WHERE name=:accname LIMIT 1;`);
  foreach (auto row; stat.st.bindConstText(":accname", accname).range) return row.accid!uint;
  return 0;
}


/**
returns accouint name, or empty string.
*/
public DynStr chiroGetAccountName (uint accid) {
  static auto stat = LazyStatement!"Conf"(`SELECT name AS name FROM accounts WHERE accid=:accid LIMIT 1;`);
  DynStr res;
  if (accid == 0) return res;
  foreach (auto row; stat.st.bind(":accid", accid).range) {
    res = row.name!SQ3Text;
    break;
  }
  return res;
}


public struct AccountInfo {
  uint accid;
  DynStr name;
  DynStr realname;
  DynStr email;
  DynStr nntpgroup;
  DynStr inbox;

  @property bool isValid () const pure nothrow @safe @nogc { return (accid != 0); }
}

public bool chiroGetAccountInfo (uint accid, out AccountInfo nfo) {
  static auto stat = LazyStatement!"Conf"(`
    SELECT name AS name, realname AS realname, email AS email, nntpgroup AS nntpgroup, inbox AS inbox
    FROM accounts
    WHERE accid=:accid LIMIT 1
  ;`);
  if (accid == 0) return false;
  foreach (auto row; stat.st.bind(":accid", accid).range) {
    nfo.accid = accid;
    nfo.name = row.name!SQ3Text;
    nfo.realname = row.realname!SQ3Text;
    nfo.email = row.email!SQ3Text;
    nfo.nntpgroup = row.nntpgroup!SQ3Text;
    nfo.inbox = row.inbox!SQ3Text;
    return true;
  }
  return false;
}

public bool chiroGetAccountInfo (const(char)[] accname, out AccountInfo nfo) {
  static auto stat = LazyStatement!"Conf"(`
    SELECT accid AS accid, name AS name, realname AS realname, email AS email, nntpgroup AS nntpgroup, inbox AS inbox
    FROM accounts
    WHERE name=:name LIMIT 1
  ;`);
  if (accname.length == 0) return false;
  foreach (auto row; stat.st.bindConstText(":name", accname).range) {
    nfo.accid = row.accid!uint;
    nfo.name = row.name!SQ3Text;
    nfo.realname = row.realname!SQ3Text;
    nfo.email = row.email!SQ3Text;
    nfo.nntpgroup = row.nntpgroup!SQ3Text;
    nfo.inbox = row.inbox!SQ3Text;
    return true;
  }
  return false;
}


/**
returns list of known tags, sorted by name.
*/
public DynStr[] chiroGetTagList () {
  static auto stat = LazyStatement!"View"(`SELECT tag AS tagname FROM tagnames WHERE hidden=0 ORDER BY tag;`);
  DynStr[] res;
  foreach (auto row; stat.st.range) res ~= DynStr(row.tagname!SQ3Text);
  return res;
}


/**
returns tag uid (tagid) or 0.
*/
public uint chiroGetTagUid (const(char)[] tagname) {
  static auto stat = LazyStatement!"View"(`SELECT tagid AS tagid FROM tagnames WHERE tag=:tagname LIMIT 1;`);
  foreach (auto row; stat.st.bindConstText(":tagname", tagname).range) {
    return row.tagid!uint;
  }
  return 0;
}


/**
returns tag name or empty string.
*/
public DynStr chiroGetTagName (uint tagid) {
  static auto stat = LazyStatement!"View"(`SELECT tag AS tagname FROM tagnames WHERE tagid=:tagid LIMIT 1;`);
  DynStr s;
  foreach (auto row; stat.st.bind(":tagid", tagid).range) {
    s = row.tagname!SQ3Text;
    break;
  }
  return s;
}


/**
returns `true` if the given tag supports threads.

this is used only when adding new messages, to set all parents to 0.
*/
public bool chiroIsTagThreaded(T) (T tagnameid)
if (is(T:const(char)[]) || is(T:uint))
{
  static if (is(T:const(char)[])) {
    static auto stat = LazyStatement!"View"(`SELECT threading AS threading FROM tagnames WHERE tag=:tagname LIMIT 1;`);
    foreach (auto row; stat.st.bindConstText(":tagname", tagnameid).range) {
      return (row.threading!uint == 1);
    }
  } else {
    static auto xstat = LazyStatement!"View"(`SELECT threading AS threading FROM tagnames WHERE tagid=:tagid LIMIT 1;`);
    foreach (auto row; xstat.st.bind(":tagid", tagnameid).range) {
      return (row.threading!uint == 1);
    }
  }
  return false;
}


/**
returns `true` if the given tag allows attaches.

this is used only when adding new messages, to set all parents to 0.
*/
public bool chiroIsTagAllowAttaches(T) (T tagnameid)
if (is(T:const(char)[]) || is(T:uint))
{
  static if (is(T:const(char)[])) {
    static auto stat = LazyStatement!"View"(`SELECT threading AS threading FROM tagnames WHERE tag=:tagname LIMIT 1;`);
    foreach (auto row; stat.st.bindConstText(":tagname", tagnameid).range) {
      return (row.threading!uint == 1);
    }
  } else {
    static auto xstat = LazyStatement!"View"(`SELECT threading AS threading FROM tagnames WHERE tagid=:tagid LIMIT 1;`);
    foreach (auto row; xstat.st.bind(":tagid", tagnameid).range) {
      return (row.threading!uint == 1);
    }
  }
  return false;
}


/**
relinks all messages in all threads suitable for relinking, and
sets parents to zero otherwise.
*/
public void chiroSupportRelinkAllThreads () {
  // yeah, that's it: a single SQL statement
  dbView.execute(`
    -- clear parents where threading is disabled
    SELECT ChiroTimerStart('clearing parents');
    UPDATE threads
    SET
      parent = 0
    WHERE
      EXISTS (SELECT threading FROM tagnames WHERE tagnames.tagid=threads.tagid AND threading=0)
      AND parent <> 0
    ;
    SELECT ChiroTimerStop();

    SELECT ChiroTimerStart('relinking threads');
    UPDATE threads
    SET
      parent=ifnull(
        (
          SELECT uid FROM msgids
          WHERE
            -- find MSGID for any of our current references
            msgids.msgid IN (SELECT msgid FROM refids WHERE refids.uid=threads.uid ORDER BY idx) AND
            -- check if UID for that MSGID has the valid tag
            EXISTS (SELECT uid FROM threads AS tt WHERE tt.uid=msgids.uid AND tt.tagid=threads.tagid)
          ORDER BY time DESC
          LIMIT 1
        )
      , 0)
    WHERE
      -- do not process messages with non-threading tags
      EXISTS (SELECT threading FROM tagnames WHERE tagnames.tagid=threads.tagid AND threading=1)
    ;
    SELECT ChiroTimerStop();
  `);
}


/**
relinks all messages for the given tag, or sets parents to zero if
threading for that tag is disabled.
*/
public void chiroSupportRelinkTagThreads(T) (T tagnameid)
if (is(T:const(char)[]) || is(T:uint))
{
  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagnameid);
    if (!tid) return;
  } else {
    alias tid = tagnameid;
  }

  static auto statNoTrd = LazyStatement!"View"(`
    UPDATE threads
    SET
      parent = 0
    WHERE
      tagid = :tagid AND parent <> 0
  ;`);

  static auto statTrd = LazyStatement!"View"(`
    UPDATE threads
    SET
      parent=ifnull(
        (
          SELECT uid FROM msgids
          WHERE
            -- find MSGID for any of our current references
            msgids.msgid IN (SELECT msgid FROM refids WHERE refids.uid=threads.uid ORDER BY idx) AND
            -- check if UID for that MSGID has the valid tag
            EXISTS (SELECT uid FROM threads AS tt WHERE tt.uid=msgids.uid AND tt.tagid=:tagid)
          ORDER BY time DESC
          LIMIT 1
        )
      , 0)
    WHERE
      threads.tagid = :tagid
  ;`);

  if (!chiroIsTagThreaded(tid)) {
    // clear parents (just in case)
    statNoTrd.st.bind(":tagid", tid).doAll();
  } else {
    // yeah, that's it: a single SQL statement
    statTrd.st.bind(":tagid", tid).doAll();
  }
}


/**
* get "from info" for the given message.
*
* returns `false` if there is no such message.
*/
public bool chiroGetMessageFrom (uint uid, ref DynStr fromMail, ref DynStr fromName) {
  static auto statGetFrom = LazyStatement!"View"(`
    SELECT
        from_name AS fromName
      , from_mail AS fromMail
    FROM info
    WHERE uid=:uid
    LIMIT 1
  ;`);
  fromMail.clear();
  fromName.clear();
  foreach (auto row; statGetFrom.st.bind(":uid", uid).range) {
    fromMail = row.fromMail!SQ3Text;
    fromName = row.fromName!SQ3Text;
    return true;
  }
  return false;
}


/**
gets twit title and state for the given (tagid, uid) message.

returns -666 if there is no such message.
*/
public DynStr chiroGetMessageTwit(T) (T tagidname, uint uid, out bool twited)
if (is(T:const(char)[]) || is(T:uint))
{
  twited = false;
  DynStr res;
  if (!uid) return res;

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return res;

  static auto statGetTwit = LazyStatement!"View"(`
    SELECT title AS title
    FROM threads
    WHERE uid=:uid AND tagid=:tagid AND mute>0
    LIMIT 1
  ;`);

  statGetTwit.st
    .bind(":uid", uid)
    .bind(":tagid", tid);
  foreach (auto row; statGetTwit.st.range) {
    twited = true;
    res = row.title!SQ3Text;
  }

  return res;
}


/**
gets mute state for the given (tagid, uid) message.

returns -666 if there is no such message.
*/
public int chiroGetMessageMute(T) (T tagidname, uint uid)
if (is(T:const(char)[]) || is(T:uint))
{
  if (!uid) return -666;

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return -666;

  static auto statGetApp = LazyStatement!"View"(`
    SELECT mute AS mute
    FROM threads
    WHERE uid=:uid AND tagid=:tagid
    LIMIT 1
  ;`);

  statGetApp.st
    .bind(":uid", uid)
    .bind(":tagid", tid);
  foreach (auto row; statGetApp.st.range) return row.mute!int;
  return -666;
}


/**
sets mute state the given (tagid, uid) message.

doesn't change children states.
*/
public void chiroSetMessageMute(T) (T tagidname, uint uid, Mute mute)
if (is(T:const(char)[]) || is(T:uint))
{
  if (!uid) return;

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return;

  static auto statSetApp = LazyStatement!"View"(`
    UPDATE threads
    SET
      mute=:mute
    WHERE
      uid=:uid AND tagid=:tagid
  ;`);

  static auto statSetAppRead = LazyStatement!"View"(`
    UPDATE threads
    SET
        mute=:mute
      , appearance=iif(appearance=0,1,appearance)
    WHERE
      uid=:uid AND tagid=:tagid
  ;`);

  if (mute > Mute.Normal) {
    statSetAppRead.st
      .bind(":mute", cast(int)mute)
      .bind(":uid", uid)
      .bind(":tagid", tid)
      .doAll();
  } else {
    statSetApp.st
      .bind(":mute", cast(int)mute)
      .bind(":uid", uid)
      .bind(":tagid", tid)
      .doAll();
  }
}


/**
gets appearance for the given (tagid, uid) message.

returns -666 if there is no such message.
*/
public int chiroGetMessageAppearance(T) (T tagidname, uint uid)
if (is(T:const(char)[]) || is(T:uint))
{
  if (!uid) return -666;

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return -666;

  static auto statGetApp = LazyStatement!"View"(`
    SELECT appearance AS appearance
    FROM threads
    WHERE uid=:uid AND tagid=:tagid
    LIMIT 1
  ;`);

  statGetApp.st
    .bind(":uid", uid)
    .bind(":tagid", tid);
  foreach (auto row; statGetApp.st.range) return row.appearance!int;
  return -666;
}


/**
gets appearance for the given (tagid, uid) message.
*/
public bool chiroGetMessageUnread(T) (T tagidname, uint uid)
if (is(T:const(char)[]) || is(T:uint))
{
  return (chiroGetMessageAppearance(tagidname, uid) == Appearance.Unread);
}


/**
gets appearance for the given (tagid, uid) message.
*/
public bool chiroGetMessageExactRead(T) (T tagidname, uint uid)
if (is(T:const(char)[]) || is(T:uint))
{
  return (chiroGetMessageAppearance(tagidname, uid) == Appearance.Read);
}


/**
sets appearance for the given (tagid, uid) message.
*/
public void chiroSetMessageAppearance(T) (T tagidname, uint uid, Appearance appearance)
if (is(T:const(char)[]) || is(T:uint))
{
  if (!uid) return;

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return;

  static auto statSetApp = LazyStatement!"View"(`
    UPDATE threads
    SET
      appearance=:appearance
    WHERE
      uid=:uid AND tagid=:tagid
  ;`);

  statSetApp.st
    .bind(":appearance", cast(int)appearance)
    .bind(":uid", uid)
    .bind(":tagid", tid)
    .doAll();
}


/**
mark (tagid, uid) message as read.
*/
public void chiroSetReadOrUnreadMessageAppearance(T) (T tagidname, uint uid, Appearance appearance)
if (is(T:const(char)[]) || is(T:uint))
{
  if (!uid) return;

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return;

  static auto statSetApp = LazyStatement!"View"(`
    UPDATE threads
    SET
      appearance=:setapp
    WHERE
      uid=:uid AND tagid=:tagid AND (appearance=:checkapp0 OR appearance=:checkapp1)
  ;`);

  statSetApp.st
    .bind(":uid", uid)
    .bind(":tagid", tid)
    .bind(":setapp", cast(int)appearance)
    .bind(":checkapp0", Appearance.Read)
    .bind(":checkapp1", Appearance.Unread)
    .doAll();
}


/**
mark (tagid, uid) message as read.
*/
public void chiroSetMessageRead(T) (T tagidname, uint uid)
if (is(T:const(char)[]) || is(T:uint))
{
  chiroSetReadOrUnreadMessageAppearance(tagidname, uid, Appearance.Read);
}


public void chiroSetMessageUnread(T) (T tagidname, uint uid)
if (is(T:const(char)[]) || is(T:uint))
{
  chiroSetReadOrUnreadMessageAppearance(tagidname, uid, Appearance.Unread);
}


/**
purge all messages with the given tag.

this removes messages from all view tables, removes content from
the "messages" table, and sets "messages" table tags to NULL.
*/
public void chiroDeletePurgedWithTag(T) (T tagidname)
if (is(T:const(char)[]) || is(T:uint))
{
  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  if (!tid) return;

  static auto statCountPurged = LazyStatement!"View"(`
    SELECT COUNT(uid) AS pcount FROM threads
    WHERE tagid=:tagid AND appearance=:appr
  `);

  uint purgedCount = 0;
  foreach (auto row; statCountPurged.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).range) {
    purgedCount = row.pcount!uint;
  }
  if (!purgedCount) return;

  // we will need this to clear storage
  uint[] plist;
  scope(exit) delete plist;
  plist.reserve(purgedCount);

  static auto statListPurged = LazyStatement!"View"(`
    SELECT uid AS uid FROM threads
    WHERE tagid=:tagid AND appearance=:appr
    ORDER BY uid
  `);

  foreach (auto row; statListPurged.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).range) {
    plist ~= row.uid!uint;
  }
  if (plist.length == 0) return; // just in case

  static auto statClearStorage = LazyStatement!"Store"(`
    UPDATE messages
    SET tags=NULL, data=NULL
    WHERE uid=:uid
  ;`);

  enum BulkClearSQL(string table) = `
    DELETE FROM `~table~`
    WHERE
      uid IN (SELECT uid FROM threads WHERE tagid=:tagid AND appearance=:appr)
  ;`;

  // bulk clearing of info
  static auto statClearInfo = LazyStatement!"View"(BulkClearSQL!"info");
  // bulk clearing of msgids
  static auto statClearMsgids = LazyStatement!"View"(BulkClearSQL!"msgids");
  // bulk clearing of refids
  static auto statClearRefids = LazyStatement!"View"(BulkClearSQL!"refids");
  // bulk clearing of text
  static auto statClearText = LazyStatement!"View"(BulkClearSQL!"content_text");
  // bulk clearing of html
  static auto statClearHtml = LazyStatement!"View"(BulkClearSQL!"content_html");
  // bulk clearing of attaches
  static auto statClearAttach = LazyStatement!"View"(BulkClearSQL!"attaches");
  // bulk clearing of threads
  static auto statClearThreads = LazyStatement!"View"(`
    DELETE FROM threads
    WHERE tagid=:tagid AND appearance=:appr
  ;`);

  static if (is(T:const(char)[])) {
    conwriteln("removing ", plist.length, " message", (plist.length != 1 ? "s" : ""), " from '", tagidname, "'...");
  } else {
    DynStr tname = chiroGetTagName(tid);
    conwriteln("removing ", plist.length, " message", (plist.length != 1 ? "s" : ""), " from '", tname.getData, "'...");
  }

  // WARNING! "info" must be cleared FIRST, and "threads" LAST
  transacted!"View"{
    statClearInfo.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    statClearMsgids.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    statClearRefids.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    statClearText.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    statClearHtml.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    statClearAttach.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    statClearThreads.st.bind(":tagid", tid).bind(":appr", Appearance.SoftDeletePurge).doAll();
    // relink tag threads
    chiroSupportRelinkTagThreads(tid);
  };

  // now clear the storage
  conwriteln("clearing the storage...");
  transacted!"Store"{
    foreach (immutable uint uid; plist) {
      statClearStorage.st.bind(":uid", uid).doAll();
    }
  };

  conwriteln("done purging.");
}


/**
creates "treepane" table for the given tag. that table can be used to
render threaded listview.

returns max id of the existing item. can be used for pagination.
item ids are guaranteed to be sequential, and without any holes.
the first id is `1`.

returned table has "rowid", and two integer fields: "uid" (message uid), and
"level" (message depth, starting from 0).
*/
public uint chiroCreateTreePaneTable(T) (T tagidname, int lastmonthes=12, bool allowThreading=true)
if (is(T:const(char)[]) || is(T:uint))
{
  auto ctm = Timer(true);

  // shrink temp table to the bare minimum, because each field costs several msecs
  // we don't need parent and time here, because we can easily select them with inner joins
  /*
  dbView.execute(`
    DROP TABLE IF EXISTS treepane;
    CREATE TEMP TABLE IF NOT EXISTS treepane (
        iid INTEGER PRIMARY KEY
      , uid INTEGER
      , level INTEGER
      -- to make joins easier
      , tagid INTEGER
    );
  `);
  */

  // this need to add answers to some ancient crap
  static auto statFirstUnreadTime = LazyStatement!"View"(`
    SELECT MIN(time) AS time, parent AS parent
    FROM threads
    WHERE tagid=:tagidname AND appearance=:app
  ;`);

  static auto statFindParentFor = LazyStatement!"View"(`
    SELECT time AS time, parent AS parent
    FROM threads
    WHERE tagid=:tagidname AND uid=:uid
    LIMIT 1
  ;`);

  // clear it (should be faster than dropping and recreating)
  dbView.execute(`DELETE FROM treepane;`);

  // this "%08X" will do up to 2038; i'm fine with it
  static auto statTrd = LazyStatement!"View"(`
    INSERT INTO treepane
      (uid, level, tagid)
    WITH tree(uid, parent, level, time, path) AS (
      WITH RECURSIVE fulltree(uid, parent, level, time, path) AS (
        SELECT t.uid AS uid, t.parent AS parent, 1 AS level, t.time AS time, printf('%08X', t.time) AS path
        FROM threads t
        WHERE t.time>=:starttime AND parent=0 AND t.tagid=:tagidname AND t.appearance <> -1
      UNION ALL
        SELECT t.uid AS uid, t.parent AS parent, ft.level+1 AS level, t.time AS time, printf('%s|%08X', ft.path, t.time) AS path
        FROM threads t, fulltree ft
        WHERE t.time>=:starttime AND t.parent=ft.uid AND t.tagid=:tagidname AND t.appearance <> -1
      )
      SELECT * FROM fulltree
    )
    SELECT
        tree.uid AS uid
      , tree.level-1 AS level
      , :tagidname AS tagid
    FROM tree
    ORDER BY path
  ;`);

  static auto statNoTrd = LazyStatement!"View"(`
    INSERT INTO treepane
      (uid, level, tagid)
    SELECT
        threads.uid AS uid
      , 0 AS level
      , :tagidname AS tagid
    FROM threads
    WHERE
      threads.time>=:starttime AND threads.tagid=:tagidname AND threads.appearance <> -1
    ORDER BY
      threads.time
  ;`);

  static if (is(T:const(char)[])) {
    immutable uint tid = chiroGetTagUid(tagidname);
    if (!tid) return 0;
    enum selHdr = ``;
  } else {
    alias tid = tagidname;
  }

  uint startTime = 0;

  if (lastmonthes > 0) {
    if (lastmonthes > 12*100) {
      startTime = 0;
    } else {
      // show last `lastmonthes` (full monthes)
      import std.datetime;
      import core.time : Duration;

      SysTime now = Clock.currTime().toUTC();
      int year = now.year;
      int month = now.month; // from 1
      --lastmonthes;
      // yes, i am THAT lazy
      while (lastmonthes > 0) {
        if (month > lastmonthes) { month -= lastmonthes; break; }
        lastmonthes -= month;
        month = 12;
        --year;
      }
      // construct unix time
      now.fracSecs = Duration.zero;
      now.second = 0;
      now.hour = 0;
      now.minute = 0;
      now.day = 1;
      now.month = cast(Month)month;
      now.year = year;
      startTime = cast(uint)now.toUnixTime();
    }
  }

  // check if we need to fix unread time
  // required to show the whole ancient thread if somebody answered
  if (startTime > 0) {
    uint unTime = 0;
    uint unParent = 0;
    foreach (auto row; statFirstUnreadTime.st.bind(":tagidname", tid).bind(":app", Appearance.Unread).range) {
      unTime = row.time!uint;
      unParent = row.parent!uint;
    }
    if (unTime > 0 && unTime < startTime) {
      // find root message, and start from it
      startTime = unTime;
      while (unParent && allowThreading) {
        statFindParentFor.st
          .bind(":tagidname", tid)
          .bind(":uid", unParent);
        unParent = 0;
        unTime = 0;
        foreach (auto row; statFindParentFor.st.range) {
          unTime = row.time!uint;
          unParent = row.parent!uint;
        }
        if (unTime > 0 && unTime < startTime) startTime = unTime;
      }
    }
  }

  if (allowThreading) {
    statTrd.st.bind(":tagidname", tid).bind(":starttime", startTime).doAll();
  } else {
    statNoTrd.st.bind(":tagidname", tid).bind(":starttime", startTime).doAll();
  }
  ctm.stop;
  if (ChiroTimerEnabled) writeln("creating treepane time: ", ctm);

  immutable uint res = cast(uint)dbView.lastRowId;

  version(chidb_drop_pane_table) {
    dbView.execute(`CREATE INDEX treepane_uid ON treepane(uid);`);
  }

  return res;
}


/**
returns current treepane tagid.
*/
public uint chiroGetTreePaneTableTagId () {
  static auto stat = LazyStatement!"View"(`SELECT tagid AS tagid FROM treepane WHERE iid=1 LIMIT 1;`);
  foreach (auto row; stat.st.range) return row.tagid!uint;
  return 0;
}


/**
returns current treepane max uid.
*/
public uint chiroGetTreePaneTableMaxUId () {
  static auto stat = LazyStatement!"View"(`SELECT MAX(uid) AS uid FROM treepane LIMIT 1;`);
  foreach (auto row; stat.st.range) return row.uid!uint;
  return 0;
}


/**
returns number of items in the current treepane.
*/
public uint chiroGetTreePaneTableCount () {
  static auto stat = LazyStatement!"View"(`SELECT COUNT(*) AS total FROM treepane;`);
  foreach (auto row; stat.st.range) return row.total!uint;
  return 0;
}


/**
returns index of the given uid in the treepane.
*/
public bool chiroIsTreePaneTableUidValid (uint uid) {
  static auto stat = LazyStatement!"View"(`SELECT iid AS idx FROM treepane WHERE uid=:uid LIMIT 1;`);
  if (uid == 0) return false;
  foreach (auto row; stat.st.bind(":uid", uid).range) return true;
  return false;
}


/**
returns first treepane uid.
*/
public uint chiroGetTreePaneTableFirstUid () {
  static auto stmt = LazyStatement!"View"(`SELECT uid AS uid FROM treepane WHERE iid=1 LIMIT 1;`);
  foreach (auto row; stmt.st.range) return row.uid!uint;
  return 0;
}


/**
returns last treepane uid.
*/
public uint chiroGetTreePaneTableLastUid () {
  static auto stmt = LazyStatement!"View"(`SELECT MAX(iid), uid AS uid FROM treepane LIMIT 1;`);
  foreach (auto row; stmt.st.range) return row.uid!uint;
  return 0;
}


/**
returns index of the given uid in the treepane.
*/
public int chiroGetTreePaneTableUid2Index (uint uid) {
  static auto stmt = LazyStatement!"View"(`SELECT iid-1 AS idx FROM treepane WHERE uid=:uid LIMIT 1;`);
  if (uid == 0) return -1;
  foreach (auto row; stmt.st.bind(":uid", uid).range) return row.idx!int;
  return -1;
}


/**
returns uid of the given index in the treepane.
*/
public uint chiroGetTreePaneTableIndex2Uid (int index) {
  static auto stmt = LazyStatement!"View"(`SELECT uid AS uid FROM treepane WHERE iid=:idx+1 LIMIT 1;`);
  if (index < 0 || index == int.max) return 0;
  foreach (auto row; stmt.st.bind(":idx", index).range) return row.uid!uint;
  return 0;
}


/**
returns previous uid in the treepane.
*/
public uint chiroGetTreePaneTablePrevUid (uint uid) {
  static auto stmt = LazyStatement!"View"(`
    SELECT uid AS uid FROM treepane
    WHERE iid IN (SELECT iid-1 FROM treepane WHERE uid=:uid LIMIT 1)
    LIMIT 1
  ;`);
  if (uid == 0) return chiroGetTreePaneTableFirstUid();
  foreach (auto row; stmt.st.bind(":uid", uid).range) return row.uid!uint;
  return 0;
}


/**
returns uid of the given index in the treepane.
*/
public uint chiroGetTreePaneTableNextUid (uint uid) {
  static auto stmt = LazyStatement!"View"(`
    SELECT uid AS uid FROM treepane
    WHERE iid IN (SELECT iid+1 FROM treepane WHERE uid=:uid LIMIT 1)
    LIMIT 1
  ;`);
  if (uid == 0) return chiroGetTreePaneTableFirstUid();
  foreach (auto row; stmt.st.bind(":uid", uid).range) return row.uid!uint;
  return 0;
}


/**
releases (drops) "treepane" table.

can be called several times, but usually you don't need to call this at all.
*/
public void chiroClearTreePaneTable () {
  //dbView.execute(`DROP TABLE IF EXISTS treepane;`);
  dbView.execute(`DELETE FROM treepane;`);
}


/**
return next unread message uid in treepane, or 0.
*/
public uint chiroGetPaneNextUnread (uint curruid) {
  static auto stmtNext = LazyStatement!"View"(`
    SELECT treepane.uid AS uid FROM treepane
    INNER JOIN threads USING(uid, tagid)
    WHERE treepane.iid-1 > :cidx AND threads.appearance=:appr
    ORDER BY iid
    LIMIT 1
  ;`);
  immutable int cidx = chiroGetTreePaneTableUid2Index(curruid);
  foreach (auto row; stmtNext.st.bind(":cidx", cidx).bind(":appr", Appearance.Unread).range) return row.uid!uint;
  if (curruid) {
    // try from the beginning
    foreach (auto row; stmtNext.st.bind(":cidx", -1).bind(":appr", Appearance.Unread).range) return row.uid!uint;
  }
  return 0;
}


/**
selects given number of items starting with the given item id.

returns numer of selected items.

`stiid` counts from zero

WARNING! "treepane" table must be prepared with `chiroCreateTreePaneTable()`!

WARNING! [i]dup `SQ3Text` arguments if necessary, they won't survive the `cb` return!
*/
public int chiroGetPaneTablePage (int stiid, int limit,
  void delegate (int pgofs, /* offset from the page start, from zero and up to `limit` */
                 int iid, /* item id, counts from zero*/
                 uint uid, /* msguid, never zero */
                 uint parentuid, /* parent msguid, may be zero */
                 uint level, /* threading level, from zero */
                 Appearance appearance, /* see above */
                 Mute mute, /* see above */
                 SQ3Text date, /* string representation of receiving date and time */
                 SQ3Text subj, /* message subject, can be empty string */
                 SQ3Text fromName, /* message sender name, can be empty string */
                 SQ3Text fromMail, /* message sender email, can be empty string */
                 SQ3Text title) cb /* title from twiting */
) {
  static auto stat = LazyStatement!"View"(`
    SELECT
        treepane.iid AS iid
      , treepane.uid AS uid
      , treepane.level AS level
      , threads.parent AS parent
      , threads.appearance AS appearance
      , threads.mute AS mute
      , datetime(threads.time, 'unixepoch') AS time
      , info.subj AS subj
      , info.from_name AS from_name
      , info.from_mail AS from_mail
      , threads.title AS title
    FROM treepane
    INNER JOIN info USING(uid)
    INNER JOIN threads USING(uid, tagid)
    WHERE treepane.iid >= :stiid
    ORDER BY treepane.iid
    LIMIT :limit
  `);

  if (limit <= 0) return 0;
  if (stiid < 0) {
    if (stiid == int.min) return 0;
    limit += stiid;
    if (limit <= 0) return 0;
    stiid = 0;
  }
  int total = 0;
  foreach (auto row; stat.st.bind(":stiid", stiid+1).bind(":limit", limit).range)
  {
    if (cb !is null) {
      cb(total, row.iid!int, row.uid!uint, row.parent!uint, row.level!uint,
         cast(Appearance)row.appearance!int, cast(Mute)row.mute!int,
         row.time!SQ3Text, row.subj!SQ3Text, row.from_name!SQ3Text, row.from_mail!SQ3Text, row.title!SQ3Text);
    }
    ++total;
  }
  return total;
}


// ////////////////////////////////////////////////////////////////////////// //
/** returns full content of the messare or `null` if no message found (or it was deleted).
*/
public DynStr chiroGetFullMessageContent (uint uid) {
  DynStr res;
  if (uid == 0) return res;
  foreach (auto row; dbStore.statement(`SELECT ChiroUnpack(data) AS result FROM messages WHERE uid=:uid LIMIT 1;`).bind(":uid", uid).range) {
    res = row.result!SQ3Text;
    return res;
  }
  return res;
}


/** returns full content of the messare or `null` if no message found (or it was deleted).
*/
public DynStr chiroMessageHeaders (uint uid) {
  DynStr res;
  if (uid == 0) return res;
  foreach (auto row; dbStore.statement(`SELECT ChiroExtractHeaders(ChiroUnpack(data)) AS result FROM messages WHERE uid=:uid LIMIT 1;`).bind(":uid", uid).range) {
    res = row.result!SQ3Text;
    return res;
  }
  return res;
}


/** returns full content of the messare or `null` if no message found (or it was deleted).
*/
public DynStr chiroMessageBody (uint uid) {
  DynStr res;
  if (uid == 0) return res;
  foreach (auto row; dbStore.statement(`SELECT ChiroExtractBody(ChiroUnpack(data)) AS result FROM messages WHERE uid=:uid LIMIT 1;`).bind(":uid", uid).range) {
    res = row.result!SQ3Text;
    return res;
  }
  return res;
}


// ////////////////////////////////////////////////////////////////////////// //
public enum Bogo {
  Error, // some error occured
  Ham,
  Unsure,
  Spam,
}

public Bogo messageBogoCheck (uint uid) {
  if (uid == 0) return Bogo.Error;
  DynStr content = chiroGetFullMessageContent(uid);
  if (content.length == 0) return Bogo.Error;

  try {
    import std.process;
    //{ auto fo = VFile("/tmp/zzzz", "w"); fo.rawWriteExact(art.data); }
    auto pipes = pipeProcess(["/usr/bin/bogofilter", "-T"]);
    //foreach (string s; art.headers) pipes.stdin.writeln(s);
    //pipes.stdin.writeln();
    //foreach (string s; art.text) pipes.stdin.writeln(s);
    pipes.stdin.writeln(content.getData.xstripright);
    pipes.stdin.flush();
    pipes.stdin.close();
    auto res = pipes.stdout.readln();
    wait(pipes.pid);
    //conwriteln("RESULT: [", res, "]");
    if (res.length == 0) {
      //conwriteln("ERROR: bogofilter returned nothing");
      return Bogo.Error;
    }
    if (res[0] == 'H') return Bogo.Ham;
    if (res[0] == 'U') return Bogo.Unsure;
    if (res[0] == 'S') return Bogo.Spam;
    //while (res.length && res[$-1] <= ' ') res = res[0..$-1];
    //conwriteln("ERROR: bogofilter returned some shit: [", res, "]");
  } catch (Exception e) { // sorry
    //conwriteln("ERROR bogofiltering: ", e.msg);
  }

  return Bogo.Error;
}


// ////////////////////////////////////////////////////////////////////////// //
private void messageBogoMarkSpamHam(bool spam) (uint uid) {
  if (uid == 0) return;
  DynStr content = chiroGetFullMessageContent(uid);
  if (content.length == 0) return;

  static if (spam) enum arg = "-s"; else enum arg = "-n";
  try {
    import std.process;
    auto pipes = pipeProcess(["/usr/bin/bogofilter", arg]);
    //foreach (string s; art.headers) pipes.stdin.writeln(s);
    //pipes.stdin.writeln();
    //foreach (string s; art.text) pipes.stdin.writeln(s);
    pipes.stdin.writeln(content.getData.xstripright);
    pipes.stdin.flush();
    pipes.stdin.close();
    wait(pipes.pid);
  } catch (Exception e) { // sorry
    //conwriteln("ERROR bogofiltering: ", e.msg);
  }
}


public void messageBogoMarkHam (uint uid) { messageBogoMarkSpamHam!false(uid); }
public void messageBogoMarkSpam (uint uid) { messageBogoMarkSpamHam!true(uid); }


// ////////////////////////////////////////////////////////////////////////// //
public alias TwitProcessCallback = void delegate (const(char)[] msg, uint curr, uint total);

void processEmailTwits (TwitProcessCallback cb) {
  enum Message = "processing email twits";

  auto stFindTwitNameEmail = LazyStatement!"View"(`
    SELECT
        threads.uid AS uid
      , threads.tagid AS tagid
    FROM threads
    INNER JOIN info AS ii ON
      ii.uid=threads.uid AND
      ii.from_mail=:email AND
      ii.from_name=:name
    WHERE mute=0
  ;`);

  auto stFindTwitEmail = LazyStatement!"View"(`
    SELECT
        threads.uid AS uid
      , threads.tagid AS tagid
    FROM threads
    INNER JOIN info AS ii ON
      ii.uid=threads.uid AND
      ii.from_mail=:email
    WHERE mute=0
  ;`);

  auto stFindTwitName = LazyStatement!"View"(`
    SELECT
        threads.uid AS uid
      , threads.tagid AS tagid
    FROM threads
    INNER JOIN info AS ii ON
      ii.uid=threads.uid AND
      ii.from_name=:name
    WHERE mute=0
  ;`);


  auto stFindTwitNameEmailMasked = LazyStatement!"View"(`
    SELECT
        threads.uid AS uid
      , threads.tagid AS tagid
    FROM threads
    INNER JOIN info AS ii ON
      ii.uid=threads.uid AND
      ii.from_name=:name AND
      ii.from_mail GLOB :email
    WHERE mute=0
  ;`);

  auto stFindTwitEmailMasked = LazyStatement!"View"(`
    SELECT
        threads.uid AS uid
      , threads.tagid AS tagid
    FROM threads
    INNER JOIN info AS ii ON
      ii.uid=threads.uid AND
      ii.from_mail GLOB :email
    WHERE mute=0
  ;`);


  auto stUpdateMute = LazyStatement!"View"(`
    UPDATE threads
    SET mute=:mute, title=:title
    WHERE uid=:uid AND tagid=:tagid AND mute=0
  ;`);

  static struct UidTag {
    uint uid;
    uint tagid;
  }

  uint twitcount = 0;
  foreach (auto trow; dbConf.statement(`SELECT COUNT(*) AS twitcount FROM emailtwits;`).range) twitcount = trow.twitcount!uint;

  if (cb !is null) cb(Message, 0, twitcount);

  dbView.execute(`
    CREATE TEMP TABLE IF NOT EXISTS disemails(
      email TEXT NOT NULL UNIQUE
    );
  `);


  scope(exit) {
    if (cb !is null) cb("dropping temp tables", twitcount, twitcount);
    dbView.execute(`DROP TABLE IF EXISTS disemails;`);
  }

  transacted!"View"{
    uint twitdone = 0;
    foreach (auto trow; dbConf.statement(`
        SELECT
            tagglob AS tagglob
          , email AS email
          , name AS name
          , title AS title
        FROM emailtwits
        WHERE email NOT LIKE '%*%'
      ;`).range)
    {
      ++twitdone;
      auto title = trow.title!SQ3Text;
      if (title.length == 0) continue;
      auto email = trow.email!SQ3Text;
      auto name = trow.name!SQ3Text;
      assert(email.indexOf('*') < 0);
      DBStatement st;
      if (email.length && name.length) {
        st = stFindTwitNameEmail.st;
        st.bindConstText(":email", email).bindConstText(":name", name);
      } else if (email.length) {
        st = stFindTwitEmail.st;
        st.bindConstText(":email", email);
      } else if (name.length) {
        st = stFindTwitName.st;
        st.bindConstText(":name", name);
      } else {
        continue;
      }
      UidTag[] msguids;
      msguids.reserve(128);
      scope(exit) delete msguids;
      //writeln("::: ", email, " : ", name);
      foreach (auto mrow; st.range) {
        auto tname = chiroGetTagName(mrow.tagid!uint);
        if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue;
        //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint);
        msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint);
      }
      if (msguids.length == 0) continue;
      //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">");
      immutable bool muteAllow = title.startsWith("!"); // allow this
      //transacted!"View"{
        foreach (immutable pair; msguids) {
          stUpdateMute.st
            .bind(":uid", pair.uid)
            .bind(":tagid", pair.tagid)
            .bind(":mute", (muteAllow ? Mute.Never : Mute.ThreadStart))
            .bindConstText(":title", title)
            .doAll();
        }
      //};
      if (cb !is null) cb(Message, twitdone, twitcount);
    }

    if (cb !is null) cb("selecting distinct emails", twitdone, twitcount);
    dbView.execute(`
      DELETE FROM disemails;
      INSERT INTO disemails
      SELECT DISTINCT(from_mail) FROM info WHERE from_mail<>'' AND from_mail NOT NULL;
    `);
    if (cb !is null) cb(Message, twitdone, twitcount);

    version(all) {
      foreach (auto trow; dbConf.statement(`
          SELECT
              tagglob AS tagglob
            , email AS email
            , name AS name
            , title AS title
          FROM emailtwits
          WHERE email LIKE '%*%'
        ;`).range)
      {
        ++twitdone;
        auto title = trow.title!SQ3Text;
        if (title.length == 0) continue;
        auto email = trow.email!SQ3Text;
        auto name = trow.name!SQ3Text;
        assert(email.indexOf('*') >= 0);
        assert(email.length);

        foreach (auto drow; dbView.statement(`SELECT email AS demail FROM disemails WHERE email GLOB :email;`)
                              .bindConstText(":email", email).range)
        {
          DBStatement st;
          if (name.length) {
            st = stFindTwitNameEmail.st;
            st.bindConstText(":email", drow.demail!SQ3Text).bindConstText(":name", name);
          } else {
            st = stFindTwitEmail.st;
            st.bindConstText(":email", drow.demail!SQ3Text);
          }
          UidTag[] msguids;
          msguids.reserve(128);
          scope(exit) delete msguids;
          //writeln("::: ", email, " : ", name);
          foreach (auto mrow; st.range) {
            auto tname = chiroGetTagName(mrow.tagid!uint);
            if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue;
            //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint);
            msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint);
          }
          if (msguids.length == 0) continue;
          //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">");
          immutable bool muteAllow = title.startsWith("!"); // allow this
          //transacted!"View"{
            foreach (immutable pair; msguids) {
              stUpdateMute.st
                .bind(":uid", pair.uid)
                .bind(":tagid", pair.tagid)
                .bind(":mute", (muteAllow ? Mute.Never : Mute.ThreadStart))
                .bindConstText(":title", title)
                .doAll();
            }
          //};
        }

        if (cb !is null) cb(Message, twitdone, twitcount);
      }
    } else {
      foreach (auto trow; dbConf.statement(`
          SELECT
              tagglob AS tagglob
            , email AS email
            , name AS name
            , title AS title
          FROM emailtwits
          WHERE email LIKE '%*%'
        ;`).range)
      {
        ++twitdone;
        auto title = trow.title!SQ3Text;
        if (title.length == 0) continue;
        auto email = trow.email!SQ3Text;
        auto name = trow.name!SQ3Text;
        assert(email.indexOf('*') >= 0);
        assert(email.length);
        DBStatement st;
        if (email.length && name.length) {
          st = stFindTwitNameEmailMasked.st;
          st.bindConstText(":email", email).bindConstText(":name", name);
        } else {
          st = stFindTwitEmailMasked.st;
          st.bindConstText(":email", email);
        }
        UidTag[] msguids;
        msguids.reserve(128);
        scope(exit) delete msguids;
        //writeln("::: ", email, " : ", name);
        foreach (auto mrow; st.range) {
          auto tname = chiroGetTagName(mrow.tagid!uint);
          if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue;
          //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint);
          msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint);
        }
        if (msguids.length == 0) continue;
        //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">");
        immutable bool muteAllow = title.startsWith("!"); // allow this
        //transacted!"View"{
          foreach (immutable pair; msguids) {
            stUpdateMute.st
              .bind(":uid", pair.uid)
              .bind(":tagid", pair.tagid)
              .bind(":mute", (muteAllow ? Mute.Never : Mute.ThreadStart))
              .bindConstText(":title", title)
              .doAll();
          }
        //};
        if (cb !is null) cb(Message, twitdone, twitcount);
      }
    }
  };

  //if (cb !is null) cb(Message, twitcount, twitcount);
}


void processMsgidTwits (TwitProcessCallback cb) {
  enum Message = "processing msgid twits";

  auto stUpdateMute = LazyStatement!"View"(`
    UPDATE threads
    SET mute=:mute, title=NULL
    WHERE uid=:uid AND tagid=:tagid AND mute=0
  ;`);

  static struct UidTag {
    uint uid;
    uint tagid;
  }

  uint twitcount = 0;
  foreach (auto trow; dbConf.statement(`SELECT COUNT(*) AS twitcount FROM msgidtwits;`).range) twitcount = trow.twitcount!uint;

  if (cb !is null) cb(Message, 0, twitcount);

  transacted!"View"{
    uint twitdone = 0;
    foreach (auto trow; dbConf.statement(`SELECT msgid AS msgid, tagglob AS tagglob FROM msgidtwits;`).range) {
      ++twitdone;
      UidTag[] msguids;
      msguids.reserve(128);
      scope(exit) delete msguids;

      foreach (auto mrow; dbView.statement(`
          SELECT threads.uid AS uid, threads.tagid AS tagid
          FROM threads
          INNER JOIN msgids AS mm
            ON mm.msgid=:msgid AND mm.uid=threads.uid
          WHERE mute=0
        ;`).bindConstText(":msgid", trow.msgid!SQ3Text).range)
      {
        auto tname = chiroGetTagName(mrow.tagid!uint);
        if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue;
        //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint);
        msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint);
      }
      if (msguids.length == 0) continue;
      //conwriteln("updating ", msguids.length, " messages for msgid <", trow.msgid!SQ3Text, ">");
      //transacted!"View"{
        foreach (immutable pair; msguids) {
          stUpdateMute.st
            .bind(":uid", pair.uid)
            .bind(":tagid", pair.tagid)
            .bind(":mute", Mute.ThreadStart)
            .doAll();
        }
      //};
      if (cb !is null) cb(Message, twitdone, twitcount);
    }
  };

  if (cb !is null) cb(Message, twitcount, twitcount);
}


void processThreadMutes (TwitProcessCallback cb) {
  enum Message = "processing thread mutes";

  if (cb !is null) cb(Message, 0, 0);

  dbConf.execute(`
ATTACH DATABASE '`~MailDBPath~`chiview.db' AS chiview;

BEGIN TRANSACTION;

--------------------------------------------------------------------------------
-- create temp table with mute pairs
SELECT ChiroTimerStart('creating mute pairs');
CREATE TEMP TABLE mutepairs AS
     WITH RECURSIVE children(muid, paruid, mtagid) AS (
      SELECT 0, chiview.threads.uid, chiview.threads.tagid
      FROM chiview.threads
      WHERE chiview.threads.parent=0 AND chiview.threads.mute=2
            AND EXISTS (SELECT uid FROM chiview.threads AS tx WHERE tx.tagid=chiview.threads.tagid AND tx.parent=chiview.threads.uid)
     UNION ALL
      SELECT
        tt.uid, tt.uid, mtagid
      FROM children AS cc
      INNER JOIN chiview.threads AS tt
      ON
        tt.tagid=cc.mtagid AND
        tt.parent=cc.paruid AND
        tt.uid<>cc.muid AND
        tt.uid<>cc.paruid
    )
    SELECT
        muid AS muid
      , mtagid AS mtagid
    FROM children
    WHERE muid<>0
;
SELECT ChiroTimerStop();

/*
SELECT 'nested mute pairs to skip:', COUNT(uid)
FROM chiview.threads
INNER JOIN mutepairs AS tt
ON
  tagid=tt.mtagid AND
  uid=tt.muid
WHERE mute<>0
;
*/

SELECT ChiroTimerStart('updating thread mutes');
UPDATE chiview.threads
SET
    mute=3  -- child
  , appearance=(SELECT CASE WHEN appearance=0 THEN 1 ELSE appearance END)
FROM (SELECT muid, mtagid FROM mutepairs) AS cc
WHERE uid=cc.muid AND tagid=cc.mtagid AND mute=0
;
SELECT ChiroTimerStop();

DROP TABLE mutepairs;


--SELECT 'secondary mutes:', COUNT(mute) FROM threads WHERE mute=3;


COMMIT TRANSACTION;

DETACH DATABASE chiview;
`);
}


public void chiroRecalcAllTwits (TwitProcessCallback cb) {
  // clear all twits
  try {
    conwriteln("clearing all mutes...");
    if (cb !is null) cb("clearing mutes", 0, 0);
    dbView.execute(`
      UPDATE threads
      SET mute=0, title=NULL
    ;`);
    conwriteln("processing email twits...");
    processEmailTwits(cb);
    conwriteln("processing msgid twits...");
    processMsgidTwits(cb);
    conwriteln("propagating thread twits...");
    processThreadMutes(cb);
    conwriteln("twit recalculation complete.");
  } catch (Exception e) {
    auto s = e.toString();
    conwriteln("=== FATAL ===", s);
  }
}