From a26367578335f7a616d8c96abb5ddd379b35b8f6 Mon Sep 17 00:00:00 2001 From: ketmar Date: Sat, 30 Oct 2021 02:29:08 +0000 Subject: [PATCH] slightly faster twit rebuilding FossilOrigin-Name: 5a24faf0f8b2a69a7fb1bd57db77e43342d78edcf46325e38468d034982f4dd7 --- chibackend/sqbase.d | 262 ++++++++++++++++++++++++++-------------------------- 1 file changed, 133 insertions(+), 129 deletions(-) diff --git a/chibackend/sqbase.d b/chibackend/sqbase.d index 26f8b80..431ba59 100644 --- a/chibackend/sqbase.d +++ b/chibackend/sqbase.d @@ -4148,106 +4148,108 @@ void processEmailTwits (TwitProcessCallback cb) { if (cb !is null) cb(Message, 0, twitcount); - uint twitdone = 0; - foreach (auto trow; dbConf.statement(` - SELECT - tagglob AS tagglob - , email AS email - , name AS name - , title AS title - FROM emailtwits - WHERE email NOT LIKE '%*%' - ;`).range) - { - ++twitdone; - auto email = trow.email!SQ3Text; - auto name = trow.name!SQ3Text; - assert(email.indexOf('*') < 0); - DBStatement st; - if (email.length && name.length) { - st = stFindTwitNameEmail.st; - st.bindConstText(":email", email).bindConstText(":name", name); - } else if (email.length) { - st = stFindTwitEmail.st; - st.bindConstText(":email", email); - } else if (name.length) { - st = stFindTwitName.st; - st.bindConstText(":name", name); - } else { - continue; - } - UidTag[] msguids; - msguids.reserve(128); - scope(exit) delete msguids; - //writeln("::: ", email, " : ", name); - foreach (auto mrow; st.range) { - auto tname = chiroGetTagName(mrow.tagid!uint); - if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue; - //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint); - msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint); - } - if (msguids.length == 0) continue; - //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">"); - transacted!"View"{ - foreach (immutable pair; msguids) { - stUpdateMute.st - .bind(":uid", pair.uid) - .bind(":tagid", pair.tagid) - .bind(":mute", Mute.ThreadStart) - .bindConstText(":title", trow.title!SQ3Text) - .doAll(); + transacted!"View"{ + uint twitdone = 0; + foreach (auto trow; dbConf.statement(` + SELECT + tagglob AS tagglob + , email AS email + , name AS name + , title AS title + FROM emailtwits + WHERE email NOT LIKE '%*%' + ;`).range) + { + ++twitdone; + auto email = trow.email!SQ3Text; + auto name = trow.name!SQ3Text; + assert(email.indexOf('*') < 0); + DBStatement st; + if (email.length && name.length) { + st = stFindTwitNameEmail.st; + st.bindConstText(":email", email).bindConstText(":name", name); + } else if (email.length) { + st = stFindTwitEmail.st; + st.bindConstText(":email", email); + } else if (name.length) { + st = stFindTwitName.st; + st.bindConstText(":name", name); + } else { + continue; } - }; - if (cb !is null) cb(Message, twitdone, twitcount); - } - - foreach (auto trow; dbConf.statement(` - SELECT - tagglob AS tagglob - , email AS email - , name AS name - , title AS title - FROM emailtwits - WHERE email LIKE '%*%' - ;`).range) - { - ++twitdone; - auto email = trow.email!SQ3Text; - auto name = trow.name!SQ3Text; - assert(email.indexOf('*') >= 0); - assert(email.length); - DBStatement st; - if (email.length && name.length) { - st = stFindTwitNameEmailMasked.st; - st.bindConstText(":email", email).bindConstText(":name", name); - } else { - st = stFindTwitEmailMasked.st; - st.bindConstText(":email", email); - } - UidTag[] msguids; - msguids.reserve(128); - scope(exit) delete msguids; - //writeln("::: ", email, " : ", name); - foreach (auto mrow; st.range) { - auto tname = chiroGetTagName(mrow.tagid!uint); - if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue; - //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint); - msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint); + UidTag[] msguids; + msguids.reserve(128); + scope(exit) delete msguids; + //writeln("::: ", email, " : ", name); + foreach (auto mrow; st.range) { + auto tname = chiroGetTagName(mrow.tagid!uint); + if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue; + //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint); + msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint); + } + if (msguids.length == 0) continue; + //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">"); + //transacted!"View"{ + foreach (immutable pair; msguids) { + stUpdateMute.st + .bind(":uid", pair.uid) + .bind(":tagid", pair.tagid) + .bind(":mute", Mute.ThreadStart) + .bindConstText(":title", trow.title!SQ3Text) + .doAll(); + } + //}; + if (cb !is null) cb(Message, twitdone, twitcount); } - if (msguids.length == 0) continue; - //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">"); - transacted!"View"{ - foreach (immutable pair; msguids) { - stUpdateMute.st - .bind(":uid", pair.uid) - .bind(":tagid", pair.tagid) - .bind(":mute", Mute.ThreadStart) - .bindConstText(":title", trow.title!SQ3Text) - .doAll(); + + foreach (auto trow; dbConf.statement(` + SELECT + tagglob AS tagglob + , email AS email + , name AS name + , title AS title + FROM emailtwits + WHERE email LIKE '%*%' + ;`).range) + { + ++twitdone; + auto email = trow.email!SQ3Text; + auto name = trow.name!SQ3Text; + assert(email.indexOf('*') >= 0); + assert(email.length); + DBStatement st; + if (email.length && name.length) { + st = stFindTwitNameEmailMasked.st; + st.bindConstText(":email", email).bindConstText(":name", name); + } else { + st = stFindTwitEmailMasked.st; + st.bindConstText(":email", email); } - }; - if (cb !is null) cb(Message, twitdone, twitcount); - } + UidTag[] msguids; + msguids.reserve(128); + scope(exit) delete msguids; + //writeln("::: ", email, " : ", name); + foreach (auto mrow; st.range) { + auto tname = chiroGetTagName(mrow.tagid!uint); + if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue; + //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint); + msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint); + } + if (msguids.length == 0) continue; + //conwriteln("updating ", msguids.length, " messages for email=<", email, ">; name=<", name, ">; title={", trow.title!SQ3Text.recodeToKOI8, ">"); + //transacted!"View"{ + foreach (immutable pair; msguids) { + stUpdateMute.st + .bind(":uid", pair.uid) + .bind(":tagid", pair.tagid) + .bind(":mute", Mute.ThreadStart) + .bindConstText(":title", trow.title!SQ3Text) + .doAll(); + } + //}; + if (cb !is null) cb(Message, twitdone, twitcount); + } + }; if (cb !is null) cb(Message, twitcount, twitcount); } @@ -4272,39 +4274,41 @@ void processMsgidTwits (TwitProcessCallback cb) { if (cb !is null) cb(Message, 0, twitcount); - uint twitdone = 0; - foreach (auto trow; dbConf.statement(`SELECT msgid AS msgid, tagglob AS tagglob FROM msgidtwits;`).range) { - ++twitdone; - UidTag[] msguids; - msguids.reserve(128); - scope(exit) delete msguids; - - foreach (auto mrow; dbView.statement(` - SELECT threads.uid AS uid, threads.tagid AS tagid - FROM threads - INNER JOIN msgids AS mm - ON mm.msgid=:msgid AND mm.uid=threads.uid - WHERE mute=0 - ;`).bindConstText(":msgid", trow.msgid!SQ3Text).range) - { - auto tname = chiroGetTagName(mrow.tagid!uint); - if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue; - //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint); - msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint); - } - if (msguids.length == 0) continue; - //conwriteln("updating ", msguids.length, " messages for msgid <", trow.msgid!SQ3Text, ">"); - transacted!"View"{ - foreach (immutable pair; msguids) { - stUpdateMute.st - .bind(":uid", pair.uid) - .bind(":tagid", pair.tagid) - .bind(":mute", Mute.ThreadStart) - .doAll(); + transacted!"View"{ + uint twitdone = 0; + foreach (auto trow; dbConf.statement(`SELECT msgid AS msgid, tagglob AS tagglob FROM msgidtwits;`).range) { + ++twitdone; + UidTag[] msguids; + msguids.reserve(128); + scope(exit) delete msguids; + + foreach (auto mrow; dbView.statement(` + SELECT threads.uid AS uid, threads.tagid AS tagid + FROM threads + INNER JOIN msgids AS mm + ON mm.msgid=:msgid AND mm.uid=threads.uid + WHERE mute=0 + ;`).bindConstText(":msgid", trow.msgid!SQ3Text).range) + { + auto tname = chiroGetTagName(mrow.tagid!uint); + if (tname.length == 0 || !globmatch(tname, trow.tagglob!SQ3Text)) continue; + //writeln("tag ", mrow.tagid!uint, " (", tname.getData, "); uid=", mrow.uid!uint); + msguids ~= UidTag(mrow.uid!uint, mrow.tagid!uint); } - }; - if (cb !is null) cb(Message, twitdone, twitcount); - } + if (msguids.length == 0) continue; + //conwriteln("updating ", msguids.length, " messages for msgid <", trow.msgid!SQ3Text, ">"); + //transacted!"View"{ + foreach (immutable pair; msguids) { + stUpdateMute.st + .bind(":uid", pair.uid) + .bind(":tagid", pair.tagid) + .bind(":mute", Mute.ThreadStart) + .doAll(); + } + //}; + if (cb !is null) cb(Message, twitdone, twitcount); + } + }; if (cb !is null) cb(Message, twitcount, twitcount); } -- 2.11.4.GIT