sqbase_experiment/zsq_40_rebuild_fts5.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 FTS5 tables in SQLite supplementary mail database.
  18 module zsq_rebuild_fts5 is aliced;
  19
  20 import iv.encoding;
  21 import iv.sq3;
  22 import iv.strex;
  23 import iv.timer;
  24 import iv.vfs;
  25 import iv.vfs.io;
  26
  27 import chibackend;
  28
  29 // ////////////////////////////////////////////////////////////////////////// //
  30 extern(C) int progressCB (void*) nothrow @nogc {
  31   import core.sys.posix.unistd : write, STDOUT_FILENO;
  32   static immutable string rotator = "|/-\\";
  33   static ubyte rpos = 0;
  34   char[4] buf = void;
  35   buf[0] = ' ';
  36   buf[1] = rotator.ptr[rpos++];
  37   buf[2] = '\r';
  38   write(STDOUT_FILENO, buf.ptr, 3);
  39   return 0;
  40 }
  41
  42
  43 // ////////////////////////////////////////////////////////////////////////// //
  44 void main (string[] args) {
  45   ChiroTimerEnabled = true;
  46   chiroParseCommonCLIArgs(args);
  47
  48   // here, i don't have any threads at all
  49   if (sqlite3_config(SQLITE_CONFIG_SINGLETHREAD) != SQLITE_OK) throw new Exception("cannot switch SQLite to multi-threaded mode");
  50
  51   writeln("opening message support db...");
  52   chiroOpenViewDB();
  53
  54   writeln("recreating FTS5 table...");
  55
  56   Timer ctm = Timer(false);
  57
  58   // now update FTS5
  59   if (args.length < 2 || args[1] != "separate") {
  60     chiroRecreateFTS5();
  61
  62     uint total = 0;
  63     foreach (auto row; dbView.statement(`SELECT COUNT(*) AS total FROM info;`).range) total = row.total!uint;
  64
  65     //sqlite3_progress_handler(dbView.getHandle, 2, &progressCB);
  66
  67     uint processed = 0;
  68     foreach (auto row; dbView.statement(`
  69       SELECT COUNT(uid) AS processed
  70       FROM info
  71       WHERE
  72         EXISTS (
  73           SELECT threads.tagid FROM threads
  74           INNER JOIN tagnames USING(tagid)
  75           WHERE
  76             threads.uid=info.uid AND
  77             tagnames.hidden=0 AND SUBSTR(tagnames.tag, 1, 1)='/'
  78         )
  79     ;`).range) processed = row.processed!uint;
  80
  81     writeln("\r", total, " messages processed (", total-processed, " skipped; ", processed, " recorded).\x1b[K");
  82   } else {
  83     chiroRecreateFTS5(repopulate:false);
  84
  85     auto stInsFTS5 = dbView.persistentStatement(`
  86       INSERT INTO fts5_messages
  87               ( rowid, sender, subj, text, html)
  88         VALUES(  :uid,:sender,:subj,:text,:html)
  89     ;`);
  90
  91     auto stSelEmailNeedFTS = dbView.persistentStatement(`
  92       SELECT threads.tagid AS tagid
  93       FROM threads
  94       INNER JOIN tagnames USING(tagid)
  95       WHERE
  96         threads.uid=:uid AND
  97         tagnames.hidden=0 AND SUBSTR(tagnames.tag, 1, 1)='/'
  98       LIMIT 1
  99     ;`);
 100
 101     writeln("populating FTS5 table...");
 102     uint total = 0;
 103     foreach (auto row; dbView.statement(`SELECT count(*) AS total FROM info;`).range) { total = row.total!uint; break; }
 104
 105     uint processed = 0;
 106     ctm.restart;
 107     dbView.execute(`BEGIN TRANSACTION;`);
 108     foreach (auto row; dbView.statement(`
 109       SELECT uid AS uid, sender AS sender, subj AS subj, text AS text, html AS html
 110       FROM fts5_msgview
 111       WHERE
 112         EXISTS (
 113           SELECT threads.tagid FROM threads
 114           INNER JOIN tagnames USING(tagid)
 115           WHERE
 116             threads.uid=fts5_msgview.uid AND
 117             tagnames.hidden=0 AND SUBSTR(tagnames.tag, 1, 1)='/'
 118         )
 119     ;`).range) {
 120       ++processed;
 121       immutable uid = row.uid!uint;
 122
 123       stInsFTS5
 124         .bind(":uid", uid)
 125         .bindConstText(":sender", row.sender!SQ3Text)
 126         .bindConstText(":subj", row.subj!SQ3Text)
 127         .bindConstText(":text", row.text!SQ3Text)
 128         .bindConstText(":html", row.html!SQ3Text)
 129         .doAll();
 130
 131       if (processed%1024 == 1) {
 132         write(" ", processed, " ", processed*100u/total, "%\x1b[K\r");
 133       }
 134     }
 135     dbView.execute(`COMMIT TRANSACTION;`);
 136     ctm.stop;
 137     writeln("time: ", ctm.toString, "\x1b[K");
 138     writeln(total, " messages processed (", total-processed, " skipped, ", processed, " recorded).\x1b[K");
 139   }
 140
 141   ctm.restart;
 142   dbView.close();
 143   writeln("closing time: ", ctm.toString, "\x1b[K");
 144 }