SQLite Examples

Copy-paste queries against the GOI Bible database

These examples assume a table (or view) shaped like the project's v_effective_rendering view — one row per verse, with columns goi (a global ordinal across the whole Bible), book, chapter, verse, lang, and rendering (the verse text). Open GOI_bible.sqlite3 with the sqlite3 CLI, or a GUI tool such as DB Browser for SQLite, and run any of these directly.

1. How to generate the entire Bible

Every verse, in canonical order, for one language edition:

SELECT book, chapter, verse, rendering
FROM v_effective_rendering
WHERE lang = 'en'
ORDER BY goi;

goi ("global ordinal index") already encodes the correct Genesis-to-Revelation reading order, so a plain ORDER BY goi is all that's needed — no book-number lookup table required.

2. How to pick just one chapter, with verse numbers

A single chapter, formatted for study/reference with verse numbers alongside each line:

SELECT verse, rendering
FROM v_effective_rendering
WHERE book = 'JHN' AND chapter = 3 AND lang = 'en'
ORDER BY verse;

Drop the verse column and use group_concat(rendering, ' ') instead for a flowing "reading view" of the same chapter with no verse numbers.

3. How to create a heatmap

Count how often a word appears in each chapter, to drive a per-chapter heatmap (darker = more occurrences). This example counts "love":

SELECT book, chapter,
       (LENGTH(LOWER(group_concat(rendering, ' ')))
        - LENGTH(REPLACE(LOWER(group_concat(rendering, ' ')), 'love', '')))
       / LENGTH('love') AS hits
FROM v_effective_rendering
WHERE lang = 'en'
GROUP BY book, chapter
ORDER BY hits DESC;

The result is one row per chapter with a hits count — feed that straight into any charting library (e.g. a calendar-style heatmap keyed on book/chapter) to visualize where a word concentrates across the text.

4. How to search for "light" within 10 verses of "dark"

Plain LIKE queries can't express "near" — for that, build an FTS5 virtual table whose "documents" are sliding windows of neighboring verses, then use FTS5's NEAR operator:

-- One-time setup: a window of ±10 verses around each verse, as one FTS5 document
CREATE VIRTUAL TABLE verse_windows USING fts5(window_text, goi UNINDEXED);

INSERT INTO verse_windows(goi, window_text)
SELECT v.goi,
       (SELECT group_concat(w.rendering, ' ')
        FROM v_effective_rendering w
        WHERE w.lang = 'en' AND w.goi BETWEEN v.goi - 10 AND v.goi + 10)
FROM v_effective_rendering v
WHERE v.lang = 'en';

-- Query: verses where "light" occurs within 10 tokens of "dark"
-- in that same ±10-verse window
SELECT vr.book, vr.chapter, vr.verse, vr.rendering
FROM verse_windows fw
JOIN v_effective_rendering vr ON vr.goi = fw.goi AND vr.lang = 'en'
WHERE fw.window_text MATCH 'light NEAR/10 dark';

verse_windows only needs to be built once (or refreshed when the text changes) — after that, the MATCH query above runs instantly even across the whole Bible.

← Back to GOI Bible