Script to read Bible Hub database; Readme

2 files changed, 134 insertions, 0 deletions

diff --git a/README.md b/README.md
index 5c4fd7c..7038cb3 100644
--- a/README.md
+++ b/README.md
@@ -1,2 +1,47 @@
 # pyble
 Python Bible tools
+
+This is a Python toolbox to analyse the Bible.
+
+Copyright © 2015 Camil Staps. See license details below.
+
+## Overview
+
+The Bible is stored in an SQLite database with the structure:
+
+Table        | Column        | Data type | Specials
+-------------|---------------|-----------|---------------------------
+book         | name          | TEXT      | PK
+reference    | book          | TEXT      | PK, FK book.name
+reference    | chapter       | INTEGER   | PK
+reference    | verse         | INTEGER   | PK
+translation  | name          | TEXT      | PK
+text         | translation   | TEXT      | PK, FK translation.name
+text         | book          | TEXT      | PK, FK reference.book
+text         | chapter       | INTEGER   | PK, FK reference.chapter
+text         | verse         | INTEGER   | PK, FK reference.verse
+text         | text          | TEXT      |
+
+<sup>(PK: primary key; FK: foreign key)</sup>
+
+## Setting up
+
+You're going to need one or several translations. One possibility is to [get them from Bible Hub][biblehubdb]. Download and unzip the zipped text file, then run `read-biblehub-db.py`.
+
+## To do
+
+Add a table for cross references, and a script to read in the database from [OpenBible.info][openbibledb].
+
+Make a web frontend for anyone to search, add cross references, and vote for cross references.
+
+## Troubleshooting
+
+In case anything goes wrong, first check you're using **Python 3**.
+
+## License
+
+This project is licensed under the GPLv2 license. 
+
+[biblehubdb]: http://biblehub.net/database/
+[openbibledb]: http://www.openbible.info/labs/cross-references/
+
diff --git a/read-biblehub-db.py b/read-biblehub-db.py
new file mode 100644
index 0000000..451852d
--- /dev/null
+++ b/read-biblehub-db.py
@@ -0,0 +1,89 @@
+import re
+import sqlite3
+
+filename = 'bibles.txt'
+dbname = 'bibles.db'
+
+def connect():
+    return sqlite3.connect(dbname)
+
+def create_tables(conn):
+    conn.execute('CREATE TABLE IF NOT EXISTS book '
+                    '(name TEXT PRIMARY KEY)')
+    conn.execute('CREATE TABLE IF NOT EXISTS reference '
+                    '(book TEXT REFERENCES book (name) '
+                        'ON DELETE RESTRICT ON UPDATE CASCADE, '
+                    'chapter INTEGER, '
+                    'verse INTEGER, '
+                    'PRIMARY KEY (book, chapter, verse))')
+    conn.execute('CREATE TABLE IF NOT EXISTS translation '
+                    '(name TEXT PRIMARY KEY)')
+    conn.execute('CREATE TABLE IF NOT EXISTS text '
+                    '(translation TEXT REFERENCES translation (name) '
+                        'ON DELETE RESTRICT ON UPDATE CASCADE, '
+                    'book TEXT, '
+                    'chapter INTEGER, '
+                    'verse INTEGER, '
+                    'text TEXT, '
+                    'PRIMARY KEY (translation, book, chapter, verse), '
+                    'FOREIGN KEY (book, chapter, verse) '
+                        'REFERENCES reference (book, chapter, verse) '
+                        'ON DELETE RESTRICT ON UPDATE RESTRICT)')
+    conn.commit()
+
+def read_file():
+    content = []
+    with open(filename) as f:
+        content = f.read().splitlines()
+    columns = content[0]
+    return (columns.split('\t'), content[1:])
+
+def read_verse(columns, line):
+    pieces = line.split('\t')
+    ref = pieces[0]
+    refm = re.search('([\w ]+?)\s*(\d+):(\d+)', ref)
+    try:
+        parsed = { 'book' : refm.group(1),
+                'chapter' : int(refm.group(2)),
+                'verse' : int(refm.group(3)),
+                'translations' : {} }
+        for i, col in enumerate(columns[1:]):
+            parsed['translations'][col] = pieces[i + 1].strip()
+        return parsed
+    except AttributeError:
+        print('Something wrong with your regex.\n', str(pieces))
+
+def main():
+    conn = connect()
+    create_tables(conn)
+
+    columns, verses = read_file()
+
+    for translation in columns[1:]:
+        conn.execute('INSERT OR IGNORE INTO translation (name) VALUES (?)', 
+                (translation,))
+
+    cur_book, cur_chapter = '', 0
+    for v in verses:
+        v = read_verse(columns, v)
+                
+        if cur_book != v['book'] or cur_chapter != v['chapter']:
+            print(v['book'], v['chapter'])
+            cur_book, cur_chapter = v['book'], v['chapter']
+        
+        conn.execute('INSERT OR IGNORE INTO book (name) VALUES (?)', 
+                (v['book'],))
+        conn.execute('INSERT OR IGNORE INTO reference (book, chapter, verse) '
+                    'VALUES (?, ?, ?)', (v['book'], v['chapter'], v['verse']))
+
+        for translation, text in v['translations'].items():
+            conn.execute('INSERT OR IGNORE INTO text '
+                    '(translation, book, chapter, verse, text) '
+                    'VALUES (?, ?, ?, ?, ?)',
+                    (translation, v['book'], v['chapter'], v['verse'], text))
+    
+    conn.commit()
+    conn.close()
+
+if __name__ == '__main__':
+    main()