sqbase_experiment/zsq_20_rebuild_support.d

   1 /* E-Mail Client
   2  * coded by Ketmar // Invisible Vector <ketmar@ketmar.no-ip.org>
   3  * Understanding is not required. Only obedience.
   4  *
   5  * This program is free software: you can redistribute it and/or modify
   6  * it under the terms of the GNU General Public License as published by
   7  * the Free Software Foundation, version 3 of the License ONLY.
   8  *
   9  * This program is distributed in the hope that it will be useful,
  10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  * GNU General Public License for more details.
  13  *
  14  * You should have received a copy of the GNU General Public License
  15  * along with this program. If not, see <http://www.gnu.org/licenses/>.
  16  */
  17 // this rebuilds supplementary tables in SQLite mail database.
  18 // it parses the messages, extract mime parts, and populates
  19 // "headers", "content", and "attaches" tables.
  20 // those tables are required for the main program to work properly,
  21 // but they can be dropped and recreated at any time.
  22 module zsq_rebuild_support is aliced;
  23
  24 import iv.encoding;
  25 import iv.sq3;
  26 import iv.strex;
  27 import iv.timer;
  28 import iv.vfs;
  29 import iv.vfs.io;
  30
  31 import chibackend;
  32
  33
  34 // ////////////////////////////////////////////////////////////////////////// //
  35 const(char)[] extractAccount (const(char)[] tags) {
  36   auto stp = tags.indexOf("account:");
  37   while (stp >= 0) {
  38     if (stp == 0 || tags[stp-1] == '|') {
  39       tags = tags[stp+8..$];
  40       stp = tags.indexOf('|');
  41       if (stp >= 0) tags = tags[0..stp];
  42       return tags;
  43     }
  44   }
  45   return null;
  46 }
  47
  48
  49 // ////////////////////////////////////////////////////////////////////////// //
  50 void main (string[] args) {
  51   ChiroTimerEnabled = true;
  52   chiroParseCommonCLIArgs(args);
  53
  54   // here, i don't have any threads at all
  55   if (sqlite3_config(SQLITE_CONFIG_SINGLETHREAD) != SQLITE_OK) throw new Exception("cannot switch SQLite to multi-threaded mode");
  56
  57   // we will need to update "nntplastindex"
  58   chiroOpenConfDB();
  59
  60   // updating indicies each time is not the best idea
  61   // let's collect them first
  62   dbConf.execute(`
  63     CREATE TEMP TABLE nntpidc (
  64         nntpidx INTEGER
  65       , accname TEXT NOT NULL UNIQUE  /* autoindex */
  66     );
  67
  68     -- reset indicies
  69     UPDATE accounts SET nntplastindex=0 WHERE nntplastindex<>0;
  70   `);
  71
  72   auto stInsNNTPIdx = dbConf.persistentStatement(`
  73     INSERT INTO nntpidc (accname, nntpidx) VALUES(:accname,:nntpidx)
  74     ON CONFLICT(accname)
  75     DO UPDATE SET nntpidx = max(nntpidx, :nntpidx)
  76   ;`);
  77
  78   // and this will update the accounts
  79   auto stUpdateNNTP = dbConf.persistentStatement(`
  80     UPDATE accounts
  81     SET
  82       --nntplastindex = MAX(nntplastindex, nntpidc.nntpidx)
  83       nntplastindex = nntpidc.nntpidx
  84     FROM
  85       (SELECT accname AS accname, nntpidx AS nntpidx FROM nntpidc) AS nntpidc
  86     WHERE
  87       accounts.name = nntpidc.accname
  88   ;`);
  89
  90   chiroOpenStorageDB(readonly:true);
  91
  92   bool[string] hiddentags;
  93   try {
  94     foreach (string s; VFile("zhidden.rc").byLineCopy) {
  95       s = s.xstrip;
  96       if (s.length == 0 || s[0] == '#') continue;
  97       hiddentags[s] = true;
  98     }
  99   } catch (Exception) {}
 100
 101
 102   writeln("creating message support db (compression level ", ChiroCompressionLevel, ")...");
 103   chiroRecreateViewDB();
 104
 105   uint total = 0;
 106   foreach (auto row; dbStore.persistentStatement(`SELECT count(uid) AS total FROM messages WHERE tags <> '';`).range) {
 107     total = row.total!uint;
 108     break;
 109   }
 110   writeln("found ", total, " messages to process.");
 111
 112   // this monstrosity selects all unique tags
 113   auto stAllTags = dbStore.persistentStatement(`
 114     WITH RECURSIVE tagtable(tag, rest) AS (
 115       VALUES('', (SELECT group_concat(tags,'|') FROM (SELECT DISTINCT(tags) AS tags FROM messages WHERE tags <> ''))||'|')
 116      UNION ALL
 117       SELECT
 118         SUBSTR(rest, 0, INSTR(rest, '|')),
 119         SUBSTR(rest, INSTR(rest, '|')+1)
 120       FROM tagtable
 121       WHERE rest <> '')
 122     SELECT DISTINCT(tag) AS tag
 123     FROM tagtable
 124     WHERE tag <> ''
 125     ORDER BY tag
 126   ;`);
 127
 128
 129   auto stInsTagName = dbView.persistentStatement("INSERT INTO tagnames (tag, hidden, threading, noattaches) VALUES(:tag,:hidden,:threading,:noattaches);");
 130
 131   bool[string] noattachtags;
 132
 133   // insert all unique tags
 134   dbView.execute("BEGIN TRANSACTION;");
 135   foreach (auto row; stAllTags.range) {
 136     auto tag = row.tag!SQ3Text;
 137     assert(tag.length);
 138     // all non-folder tags are hidden
 139     bool isHidden = (tag[0] != '/');
 140     immutable bool isSpam = tag.startsWith("#spam");
 141     immutable bool noattaches =
 142       isSpam ||
 143       tag.startsWith("/notifications/") || tag == "/notifications" ||
 144       tag.startsWith("/lists/") || tag == "/lists" ||
 145       tag.startsWith("/lj/") || tag == "/lj" ||
 146       tag.startsWith("/ljross/") || tag == "/ljross" ||
 147       false;
 148     immutable bool nothreads =
 149       isHidden || isSpam ||
 150       tag.startsWith("/notifications/") || tag == "/notifications" ||
 151       false;
 152     if (noattaches) noattachtags[tag.idup] = true;
 153     if (tag in hiddentags) isHidden = true;
 154     stInsTagName
 155       .bindConstText(":tag", tag)
 156       .bind(":hidden", (isHidden ? 1 : 0))
 157       .bind(":threading", (nothreads ? 0 : 1))
 158       .bind(":noattaches", (noattaches ? 1 : 0))
 159       .doAll();
 160   }
 161   dbView.execute("COMMIT TRANSACTION;");
 162
 163   Timer ctm = Timer(true);
 164   RunningAverageExp ravg;
 165   //ravg.progressThreshold = 1024;
 166   ravg.startTimer(0);
 167   uint count = chiroParseAndInsertMessages(0, relink:false, asread:true, //emsgid:616241,
 168     progresscb:delegate (uint count, uint total, uint nntpidx, const(char)[] tags) {
 169       if (count == 1) ravg.timerTotal = total;
 170
 171       if (nntpidx > 0) {
 172         auto accname = extractAccount(tags);
 173         if (accname.length) {
 174           stInsNNTPIdx
 175             .bindConstText(":accname", accname)
 176             .bind(":nntpidx", nntpidx)
 177             .doAll();
 178         }
 179       }
 180
 181       ravg.updateProcessedWithProgress(1);
 182       /*
 183       if (count%1024 == 1) {
 184         write(" ", count, " ", count*100u/total, "%  ", tags, "\x1b[K\r");
 185       }
 186
 187       if (nntpidx > 0) {
 188         while (tags.length) {
 189           auto eep = tags.indexOf('|');
 190           auto tagname = (eep >= 0 ? tags[0..eep] : tags[0..$]);
 191           tags = (eep >= 0 ? tags[eep+1..$] : tags[0..0]);
 192           if (tagname.length == 0) continue;
 193           if (nntpidx > 0 && tagname.startsWith("account:")) {
 194             auto accname = tagname[8..$];
 195             stInsNNTPIdx
 196               .bindConstText(":accname", accname)
 197               .bind(":nntpidx", nntpidx)
 198               .doAll();
 199           }
 200         }
 201       }
 202       */
 203     }
 204   );
 205   ctm.stop;
 206   writeln("time: ", ctm.toString, "\x1b[K");
 207   writeln(count, " messages processed.\x1b[K");
 208
 209   dbStore.close();
 210
 211   // update NNTP indicies
 212   stUpdateNNTP.doAll();
 213   dbConf.close();
 214
 215   writeln("creating indicies...");
 216   ctm.restart;
 217   chiroCreateViewIndiciesDB();
 218   ctm.stop;
 219   writeln("time (indexing): ", ctm.toString, "\x1b[K");
 220
 221   ctm.restart;
 222   dbView.execute("ANALYZE;");
 223   ctm.stop;
 224   writeln("time (analyzing): ", ctm.toString, "\x1b[K");
 225
 226   writeln("linking threads...");
 227   ctm.restart;
 228   chiroSupportRelinkAllThreads();
 229   ctm.stop;
 230   writeln("time: ", ctm.toString, "\x1b[K");
 231   uint ttotal = 0;
 232   foreach (auto row; dbView.statement("SELECT count(*) AS total FROM threads WHERE EXISTS(SELECT threading FROM tagnames WHERE tagnames.tagid=threads.tagid AND threading=1);").range) {
 233     ttotal = row.total!uint;
 234   }
 235   if (ttotal == 0) { writeln("WUTAFUCK!?"); ttotal = 1; }
 236   foreach (auto row; dbView.statement("SELECT count(*) AS parented FROM threads WHERE parent<>0;").range) {
 237     writeln(row.parented!uint, " of ", ttotal, " messages with parents found (", row.parented!uint*100/ttotal, "%).");
 238   }
 239
 240   writeln("closing the db");
 241   ctm.restart;
 242   dbView.close();
 243   ctm.stop;
 244   writeln("time (analyzing): ", ctm.toString, "\x1b[K");
 245 }