1 files changed, 149 insertions, 0 deletions
diff --git a/packages/db/src/test.ts b/packages/db/src/test.ts
new file mode 100644
index 0000000..6767c13
--- /dev/null
+++ b/packages/db/src/test.ts
@@ -0,0 +1,149 @@
+import ndb from ".";
+import toneDb from "@/lib/db/prosodydb";
+
+// function test() {
+//   const tones = ["low", "mid"];
+//   const ndb = new BigQueries();
+//   const nq = ndb.fetchWordsByToneAndSyls(tones);
+//   const oq = toneDb.fetchWordsByToneAndSyls(tones);
+//   // const senses = JSON.parse(res[0].senses_array);
+//   console.log({ nq });
+//   console.log(nq.length, oq.length);
+// }
+function test() {
+  const tones = ["low", "mid"];
+  console.log("wtf");
+  // const qq = ndb.fetchWordsByToneAndSyls1(tones);
+  // const oq = ndb.fetchWordsByToneAndSylsO(tones);
+  const nq = ndb.fetchWordsByToneAndSyls(tones);
+  // const roq = toneDb.fetchWordsByToneAndSyls(tones);
+  // const senses = JSON.parse(res[0].senses_array);
+  // console.log({ nq });
+  // console.log("old", oq.length);
+  // console.log("old db", roq.length);
+  console.log("new", nq);
+  // console.log("test", qq.length);
+}
+test();
+
+// `
+// `
+//       WITH word_tone_sequences AS (
+//           SELECT
+//               w.id as word_id,
+//               w.spelling,
+//               wp.ipa,
+//               w.frequency,
+//               GROUP_CONCAT(sy.text ORDER BY sw.idx) as syl_seq,
+//               GROUP_CONCAT(t.name ORDER BY sw.idx) as tone_sequence,
+//               COUNT(sw.syl_id) as syllable_count,
+//               (SELECT
+//                 json_group_array(json_object(
+//                   'id', s.id,
+//                   'pos', s.pos,
+//                   'etymology', s.etymology,
+//                   'confidence', s.confidence,
+//                   'subsenses', (
+//                     SELECT json_group_array(json_object(
+//                       'id', ss.id,
+//                       'gloss', ss.gloss
+//                     ))
+//                     FROM subsenses ss
+//                     WHERE ss.sid = s.id
+//                   ),
+//                   'examples', (
+//                     SELECT json_group_array(json_object(
+//                       'id', ex.id,
+//                       'example', ex.example,
+//                       'ref', ex.ref
+//                     ))
+//                     FROM examples ex
+//                     WHERE ex.sid = s.id
+//                   ),
+//                   'derivation', (
+//                     SELECT json_group_array(json_object(
+//                       'id', d.id,
+//                       'type', d.type,
+//                       'text', d.text,
+//                       'tags', d.tags
+//                     ))
+//                     FROM derivation d
+//                     WHERE d.sid = s.id
+//                   ),
+//                   'categories', (
+//                     SELECT json_group_array(wc.category)
+//                     FROM word_categories wc
+//                     WHERE wc.word_id = s.id
+//                   )
+//                 ))
+//                 FROM senses s
+//                 WHERE s.parent_id = w.id
+//               ) as senses_array
+//           FROM expressions w
+//           JOIN word_phonetics wp ON w.id = wp.word_id
+//           JOIN syllables_words sw ON wp.id = sw.word_id
+//           JOIN syllables sy ON sw.syl_id = sy.id
+//           JOIN tones t ON sy.tone = t.id
+//           GROUP BY w.id, w.spelling, w.lang, w.frequency
+//       )
+//         SELECT *
+//         FROM word_tone_sequences
+//         WHERE tone_sequence LIKE ?
+//         AND syllable_count = ?
+//         ORDER BY frequency ASC NULLS LAST;
+//       `
+//
+//
+//
+// `
+//       WITH word_tone_sequences AS (
+//           SELECT
+//               w.id as word_id,
+//               w.spelling,
+//               wp.ipa,
+//               w.frequency,
+//               GROUP_CONCAT(sy.text ORDER BY sw.idx) as syl_seq,
+//               GROUP_CONCAT(t.name ORDER BY sw.idx) as tone_sequence,
+//               COUNT(sw.syl_id) as syllable_count
+//           FROM expressions w
+//           JOIN word_phonetics wp ON w.id = wp.word_id
+//           JOIN syllables_words sw ON wp.id = sw.word_id
+//           JOIN syllables sy ON sw.syl_id = sy.id
+//           JOIN tones t ON sy.tone = t.id
+//           GROUP BY w.id, w.spelling, w.lang, w.frequency, wp.ipa
+//       ),
+//       sense_data AS (
+//         SELECT
+//           s.*,
+//           GROUP_CONCAT(DISTINCT ss.id || ':' || ss.gloss, '|') as subsenses_data,
+//           GROUP_CONCAT(DISTINCT ex.id || ':' || ex.example || ':' || COALESCE(ex.ref, ''), '|') as examples_data,
+//           GROUP_CONCAT(DISTINCT d.id || ':' || d.type || ':' || d.text, '|') as derivation_data,
+//           GROUP_CONCAT(DISTINCT wc.category, '|') as categories_data
+//         FROM senses s
+//         LEFT JOIN subsenses ss ON ss.sid = s.id
+//         LEFT JOIN examples ex ON ex.sid = s.id
+//         LEFT JOIN derivation d ON d.sid = s.id
+//         LEFT JOIN word_categories wc ON wc.word_id = s.id
+//         GROUP BY s.id
+//       )
+//       SELECT
+//         wts.*,
+//         (SELECT
+//           json_group_array(json_object(
+//             'id', sd.id,
+//             'pos', sd.pos,
+//             'etymology', sd.etymology,
+//             'confidence', sd.confidence,
+//             'subsenses_data', sd.subsenses_data,
+//             'examples_data', sd.examples_data,
+//             'derivation_data', sd.derivation_data,
+//             'categories_data', sd.categories_data
+//           ))
+//           FROM sense_data sd
+//           WHERE sd.parent_id = wts.word_id
+//         ) as senses_array
+//       FROM word_tone_sequences wts
+//       WHERE wts.tone_sequence LIKE ?
+//       AND wts.syllable_count = ?
+//       ORDER BY wts.frequency ASC NULLS LAST;
+//       `