some code for main UI: it can show list of folders

[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:

// 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 std.digest.ripemd;
private import iv.strex;
private import iv.sq3;
private import iv.timer;
private import iv.vfs.io;
private import iv.vfs.util;
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 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


/*
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;";
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 */
  );
`;

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 INDEX IF NOT EXISTS msgidtwits_msgid ON msgidtwits(msgid);
`;


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
  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
  -- appearance is:
  --  -1: can be used to ignore messages in thread view
  --   0: unread
  --   1: read
  --   2: soft-delete from filter
  --   3: soft-delete by user (will be purged on folder change)
  --   4: muted (by filter)
  --   5: self-highlight
  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 */
  );


  -- 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 */
  );


  -- 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 */
  );


  -- 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; 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)
    ;

  -- old joins
      /*
      LEFT OUTER JOIN content AS content_text ON
        content_text.uid = info.uid AND content_text.mime = 'text/plain'
      LEFT OUTER JOIN content AS content_html ON
        content_html.uid = info.uid AND content_html.mime = 'text/html'
      */

`;


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);
  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);
`;

static immutable string schemaSupport = schemaSupportTable~schemaSupportIndex;


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 = 'porter unicode61 remove_diacritics 2'
    , 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
  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!
  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
`;


// ////////////////////////////////////////////////////////////////////////// //
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) {

/*
** 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";
    }
    uint xszlen = encodeUInt(xsz[5..$], cast(uint)sz);
    if (xszlen) {
      xszlen += 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);
      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;
          int zres = compress2(cast(ubyte *)(cbuf+xszlen), &dsize, cast(const(ubyte) *)vs, sz, packlevel);
          if (zres == Z_OK && dsize+xszlen < cast(usize)sz) {
            sqlite3_result_blob(ctx, cbuf, dsize+xszlen, &free);
            return;
          }
        }
        free(cbuf);
      }
    }
  }

  if (isBadPrefix(vs[0..cast(uint)sz])) {
    char *res = cast(char *)malloc(sz+4);
    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]));
}


/*
** 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_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") {
    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_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; }

  ubyte[20] hash = ripemd160Of(vs[0..cast(uint)sz]);
  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").decodeSubj.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 subj = findHeaderField(hdrs, "Subject").decodeSubj.subjRemoveRe;
  auto to = findHeaderField(hdrs, "To").decodeSubj.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);
  }
}


/*
** 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);
}


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


// ////////////////////////////////////////////////////////////////////////// //
private void registerFunctions (ref Database db) {
  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("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("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("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);
  registerFunctions(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);
  registerFunctions(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);
  registerFunctions(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);
  registerFunctions(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);
  registerFunctions(dbView);
  if (!readonly) 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);
  registerFunctions(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) {
  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 chiroSetOption(T) (const(char)[] name, T value)
if (__traits(isIntegral, T) || is(T:const(char)[]))
{
  assert(name.length != 0);
  auto stat = dbConf.statement(`
    INSERT INTO options
            ( name, value)
      VALUES(:name,:value)
    ON CONFLICT(name)
    DO UPDATE SET value=:value
  ;`).bindText(":name", name, transient:false);
  static if (is(T == typeof(null))) {
    stat.bindText(":value", "", transient:false);
  } else static if (__traits(isIntegral, T)) {
    stat.bind(":value", value);
  } else static if (is(T:const(char)[])) {
    stat.bindText(":value", value);
  } else {
    static assert(0, "oops");
  }
  stat.doAll();
}


public T chiroGetOption(T) (const(char)[] name, T defval=T.init)
if (__traits(isIntegral, T) || is(T:const(char)[]))
{
  assert(name.length != 0);
  foreach (auto row; dbConf.statement(`SELECT value AS value FROM options WHERE name=:name;`).bindText(":name", name, transient:false).range) {
    return row.value!T;
  }
  return defval;
}


// ////////////////////////////////////////////////////////////////////////// //
/**
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 updates 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);
    if (tuid == 0) continue;

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

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

    stInsThreads
      .bind(":uid", uid)
      .bind(":tagid", tuid)
      .bind(":time", msgtime)
      .bind(":appearance", appearance)
      .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)
          .bindText(":msgid", id, transient:false)
          .doAll();
      }
    }
    // if there is no msgid, create one
    if (!hasmsgid) {
      RIPEMD160 hash;
      hash.start();
      hash.put(cast(const(ubyte)[])hdrs);
      hash.put(cast(const(ubyte)[])body);
      ubyte[20] digest = hash.finish();
      char[20*2+2+16] buf;
      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)
        .bindText(":msgid", buf[0..20*2], transient:false)
        .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)
          .bindText(":format", cc.format, transient:false)
          .bindBlob(":content", cc.data, transient:false)
          .doAll();
      } else if (!wasHtml && cc.mime == "text/html") {
        wasHtml = true;
        stInsContentHtml
          .bind(":uid", uid)
          .bindText(":format", cc.format, transient:false)
          .bindBlob(":content", cc.data, transient:false)
          .doAll();
      }
    }
    if (!wasText) {
        stInsContentText
          .bind(":uid", uid)
          .bindText(":format", "", transient:false)
          .bindBlob(":content", "", transient:false)
          .doAll();
    }
    if (!wasHtml) {
        stInsContentHtml
          .bind(":uid", uid)
          .bindText(":format", "", transient:false)
          .bindBlob(":content", "", transient:false)
          .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++)
        .bindText(":mime", cc.mime, transient:false)
        .bindText(":name", cc.name, transient:false)
        .bindText(":format", cc.name, transient:false)
        .bindBlob(":content", (noattaches ? null : cc.data), transient:false, 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").decodeSubj;
    auto to = findHeaderField(hdrs, "To").decodeSubj;
    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

  dbView.execute("BEGIN TRANSACTION;");
  scope(success) dbView.execute("COMMIT TRANSACTION;");
  scope(failure) dbView.execute("ROLLBACK TRANSACTION;");

  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?
  }

  uint[] uptagids;
  if (relink) uptagids.reserve(128);
  scope(exit) delete uptagids;

  uint count = 0;

  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) {
  foreach (auto row; dbConf.statement(`SELECT accid AS accid FROM accounts WHERE name=:accname LIMIT 1;`)
                           .bindText(":accname", accname, transient:false).range)
  {
    return row.accid!uint;
  }
  return 0;
}


/**
returns list of known tags, sorted by name.
*/
public string[] chiroGetTagList () {
  string[] res;
  foreach (auto row; dbView.statement(`SELECT tag AS tagname FROM tagnames WHERE hidden=0 ORDER BY tag;`).range) {
    res ~= row.tagname!string;
  }
  return res;
}


/**
returns tag uid (tagid) or 0.
*/
public uint chiroGetTagUid (const(char)[] tagname) {
  foreach (auto row; dbView.statement(`SELECT tagid AS tagid FROM tagnames WHERE tag=:tagname LIMIT 1;`)
                           .bindText(":tagname", tagname, transient:false).range)
  {
    return row.tagid!uint;
  }
  return 0;
}


/**
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)[])) {
    foreach (auto row; dbView.statement(`SELECT threading AS threading FROM tagnames WHERE tag=:tagname LIMIT 1;`)
                             .bindText(":tagname", tagnameid, transient:false).range)
    {
      return (row.threading!uint == 1);
    }
  } else {
    foreach (auto row; dbView.statement(`SELECT threading AS threading FROM tagnames WHERE tagid=:tagid LIMIT 1;`)
                             .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)[])) {
    foreach (auto row; dbView.statement(`SELECT threading AS threading FROM tagnames WHERE tag=:tagname LIMIT 1;`)
                             .bindText(":tagname", tagnameid, transient:false).range)
    {
      return (row.threading!uint == 1);
    }
  } else {
    foreach (auto row; dbView.statement(`SELECT threading AS threading FROM tagnames WHERE tagid=:tagid LIMIT 1;`)
                             .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;
  }

  if (!chiroIsTagThreaded(tid)) {
    // clear parents (just in case)
    dbView.statement(`
      UPDATE threads
      SET
        parent = 0
      WHERE
        tagid = :tagid AND parent <> 0
    ;`).bind(":tagid", tid).doAll();
  } else {
    // yeah, that's it: a single SQL statement
    dbView.statement(`
      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
    ;`).bind(":tagid", tid).doAll();
  }
}


/**
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))
{
  // 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 treepane (
        iid INTEGER PRIMARY KEY
      , uid INTEGER
      , level INTEGER
      -- to make joins easier
      , tagid INTEGER
    )
  ;`);

  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
      // yes, i am THAT lazy
      while (lastmonthes > 1) {
        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();
    }
  }

  // this "%08X" will do up to 2038; i'm fine with it
  if (allowThreading) {
    dbView.statement(`
      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
    ;`).bind(":tagidname", tid).bind(":starttime", startTime).doAll();
  } else {
    dbView.statement(`
      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
    ;`).bind(":tagidname", tid).bind(":starttime", startTime).doAll();
  }

  return cast(uint)dbView.lastRowId;
}


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

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


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

returns numer of selected items.

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

WARNING! [i]dup `SQ3Text` arguments if necessary, they won't survive the `cb` return!
*/
public uint chiroGetPaneTablePage (uint stiid, uint limit,
  void delegate (uint pgofs, /* offset from the page start, from zero and up to `limit` */
                 uint iid, /* item id, never zero */
                 uint uid, /* msguid, never zero */
                 uint parentuid, /* parent msguid, may be zero */
                 uint level, /* threading level, from zero */
                 int appearance, /* flags, etc. */
                 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) cb /* message sender email, can be empty string */
) {
  if (stiid < 1) stiid = 1;
  if (limit == 0) return 0;
  uint total = 0;
  foreach (auto row; dbView.statement(`
    SELECT
        treepane.iid AS iid
      , treepane.uid AS uid
      , treepane.level AS level
      , threads.parent AS parent
      , threads.appearance AS appearance
      , datetime(threads.time, 'unixepoch') AS time
      , info.subj AS subj
      , info.from_name AS from_name
      , info.from_mail AS from_mail
    FROM treepane
    INNER JOIN info
      USING(uid)
    INNER JOIN threads
      USING(uid, tagid)
    WHERE
      treepane.iid >= :stiid
    ORDER BY treepane.iid
    LIMIT :limit
  `).bind(":stiid", stiid).bind(":limit", limit).range)
  {
    if (cb !is null) {
      cb(total, row.iid!uint, row.uid!uint, row.parent!uint, row.level!uint, row.appearance!int,
         row.time!SQ3Text, row.subj!SQ3Text, row.from_name!SQ3Text, row.from_mail!SQ3Text);
    }
    ++total;
  }
  return total;
}


// ////////////////////////////////////////////////////////////////////////// //
/** returns full content of the messare or `null` if no message found (or it was deleted).
*
* you can safely `delete` the result
*/
public char[] GetFullMessageContent (uint uid) {
  if (uid == 0) return null;
  foreach (auto row; dbStore.statement(`SELECT ChiroUnpack(data) AS content FROM messages WHERE uid=:uid LIMIT 1;`).bind(":uid", uid).range) {
    auto ct = row.content!SQ3Text;
    if (!ct.length) return null;
    char[] content = new char[ct.length];
    content[] = ct[];
    return content;
  }
  return null;
}


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

public Bogo messageBogoCheck (uint uid) {
  if (uid == 0) return Bogo.Error;
  char[] content = GetFullMessageContent(uid);
  scope(exit) delete content;
  if (content is null) 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.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;
  char[] content = GetFullMessageContent(uid);
  scope(exit) delete content;
  if (content is null) 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.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); }