sqbase_experiment/zsq_show_threads_v2_small_tbl_stable.d

   1 // this rebuilds supplementary tables in SQLite mail database.
   2 // it parses the messages, extract mime parts, and populates
   3 // "headers", "content", and "attaches" tables.
   4 // those tables are required for the main program to work properly,
   5 // but they can be dropped and recreated at any time.
   6 module zsq_show_threads_slow is aliced;
   7
   8 import iv.encoding;
   9 import iv.sq3;
  10 import iv.strex;
  11 import iv.timer;
  12 import iv.vfs;
  13 import iv.vfs.io;
  14
  15 import chiro_sqbase;
  16 import chiro_decode;
  17 import chiro_parse;
  18
  19
  20 // ////////////////////////////////////////////////////////////////////////// //
  21 void main (string[] args) {
  22   // here, i don't have any threads at all
  23   if (sqlite3_config(SQLITE_CONFIG_SINGLETHREAD) != SQLITE_OK) throw new Exception("cannot switch SQLite to multi-threaded mode");
  24
  25   writeln("opening message support db...");
  26   auto db = chiroOpenSupportDB();
  27
  28   string tagname = "/dmars_ng/general";
  29
  30   Timer ctm = Timer(true);
  31
  32   void createMsgTempTable (const(char)[] tagname) {
  33     uint tagid = 0;
  34     foreach (auto trow; db.statement(`SELECT uid AS tagid FROM tagnames WHERE tag=:tagname LIMIT 1`).bindText(":tagname", tagname, transient:false).range) {
  35       tagid = trow.tagid!uint;
  36     }
  37     //writeln(tagid);
  38     db.execute(`
  39       CREATE TEMP TABLE tempmsglist (
  40           uid INTEGER PRIMARY KEY
  41         , parent INTEGER
  42         , time INTEGER)
  43     ;`);
  44
  45     db.statement(`
  46       INSERT INTO tempmsglist
  47       SELECT uid AS uid, parent AS parent, time AS time
  48       FROM threads
  49       WHERE tagid=:tagid AND appearance <> -1
  50     ;`).bind(":tagid", tagid).doAll();
  51
  52     db.execute(`
  53       CREATE INDEX tempmsglist_parent_time ON tempmsglist(parent, time);
  54     ;`);
  55   }
  56
  57   void dropMsgTempTable () {
  58     db.execute(`DROP TABLE IF EXISTS tempmsglist;`);
  59   }
  60
  61   writeln("collecting messages...");
  62   ctm.restart;
  63   createMsgTempTable(tagname);
  64   ctm.stop;
  65   writeln("preliminary filtering time: ", ctm);
  66
  67   db.execute(`
  68     CREATE TEMP TABLE treepane (
  69         uid INTEGER
  70       , parent INTEGER
  71       , level INTEGER
  72       , time INTEGER)
  73   ;`);
  74
  75   auto selMsgs = db.persistentStatement(`
  76 INSERT INTO treepane
  77 WITH tree(uid, parent, level, time, path) AS (
  78   WITH RECURSIVE fulltree(uid, parent, level, time, path) AS (
  79     SELECT tm.uid AS uid, tm.parent AS parent, 1 AS level, tm.time AS time, printf('%08X', tm.time) AS path
  80     FROM tempmsglist tm
  81     WHERE parent=0
  82   UNION ALL
  83     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
  84     FROM tempmsglist t, fulltree ft
  85     WHERE t.parent=ft.uid
  86   )
  87   SELECT * FROM fulltree
  88 )
  89 SELECT
  90     tree.uid AS uid
  91   , tree.parent AS parent
  92   , tree.level AS level
  93   --, datetime(tree.time, 'unixepoch') AS time
  94   , tree.time AS time
  95 FROM tree
  96 ORDER BY path
  97   ;`);
  98
  99   ctm.restart;
 100   selMsgs.doAll();
 101   ctm.stop;
 102   dropMsgTempTable();
 103
 104   uint ncount = 0;
 105   foreach (auto row; db.statement(`SELECT max(rowid) AS ncount FROM treepane;`).range) ncount = row.ncount!uint;
 106   writeln("collect time (", ncount, " messages): ", ctm);
 107
 108
 109   writeln("closing the db");
 110   db.close();
 111 }