💾 Archived View for gmn.clttr.info › sources › gmnifaq.git › tree › scripts.sql.txt captured on 2021-12-03 at 14:04:38.

View Raw

More Information

➡️ Next capture (2023-01-29)

-=-=-=-=-=-=-

select DISTINCT tags.*, count(tags_questions.q_id) as count from fts_data inner join tags on tags.id = fts_data.t_id
left join tags_questions on tags_questions.t_id = tags.id
where fts_data match '"text": "gemin"'
ORDER BY rank

select DISTINCT q_id, questions.question from fts_data inner join questions on questions.id = q_id
where fts_data match '"text": "linux"'
ORDER BY rank;

-- fill fts_data
INSERT INTO fts_data (q_id, text) SELECT id, question FROM questions;
INSERT INTO fts_data (q_id, text) SELECT id, answer FROM questions;
INSERT INTO fts_data (t_id, text) SELECT id, name FROM tags;

-- create fts_data table
CREATE VIRTUAL TABLE fts_data
USING FTS5(t_id, q_id, text, tokenize = "porter unicode61")