Create the improved SQLite schema.
(db)
| 13 | DB_FILE = "quran.db" |
| 14 | |
| 15 | def create_sqlite_schema(db): |
| 16 | """Create the improved SQLite schema.""" |
| 17 | db.executescript(""" |
| 18 | PRAGMA journal_mode = WAL; |
| 19 | PRAGMA foreign_keys = ON; |
| 20 | PRAGMA encoding = 'UTF-8'; |
| 21 | |
| 22 | CREATE TABLE IF NOT EXISTS surahs ( |
| 23 | id INTEGER PRIMARY KEY, |
| 24 | number INTEGER NOT NULL, |
| 25 | name_ar TEXT NOT NULL, |
| 26 | name_en TEXT NOT NULL, |
| 27 | name_en_translation TEXT NOT NULL, |
| 28 | type TEXT NOT NULL CHECK(type IN ('meccan', 'medinan', 'Meccan', 'Medinan')), |
| 29 | created_at TEXT, |
| 30 | updated_at TEXT |
| 31 | ); |
| 32 | |
| 33 | CREATE TABLE IF NOT EXISTS juzs ( |
| 34 | id INTEGER PRIMARY KEY, |
| 35 | juz_number INTEGER NOT NULL UNIQUE, |
| 36 | name_ar TEXT NOT NULL, |
| 37 | start_ayah_id INTEGER NOT NULL, |
| 38 | end_ayah_id INTEGER NOT NULL |
| 39 | ); |
| 40 | |
| 41 | CREATE TABLE IF NOT EXISTS hizbs ( |
| 42 | id INTEGER PRIMARY KEY, |
| 43 | hizb_number INTEGER NOT NULL UNIQUE, |
| 44 | juz_id INTEGER NOT NULL, |
| 45 | name_ar TEXT NOT NULL, |
| 46 | start_ayah_id INTEGER NOT NULL, |
| 47 | end_ayah_id INTEGER NOT NULL |
| 48 | ); |
| 49 | |
| 50 | CREATE TABLE IF NOT EXISTS ayahs ( |
| 51 | id INTEGER PRIMARY KEY, |
| 52 | number INTEGER NOT NULL, |
| 53 | text TEXT NOT NULL, |
| 54 | number_in_surah INTEGER NOT NULL, |
| 55 | page INTEGER NOT NULL, |
| 56 | surah_id INTEGER NOT NULL REFERENCES surahs(id), |
| 57 | hizb_id INTEGER NOT NULL, |
| 58 | juz_id INTEGER NOT NULL, |
| 59 | sajda INTEGER NOT NULL DEFAULT 0 CHECK(sajda IN (0, 1)), |
| 60 | created_at TEXT, |
| 61 | updated_at TEXT |
| 62 | ); |
| 63 | |
| 64 | CREATE TABLE IF NOT EXISTS editions ( |
| 65 | id INTEGER PRIMARY KEY, |
| 66 | identifier TEXT NOT NULL UNIQUE, |
| 67 | language TEXT NOT NULL, |
| 68 | name TEXT NOT NULL, |
| 69 | english_name TEXT NOT NULL, |
| 70 | format TEXT NOT NULL, |
| 71 | type TEXT NOT NULL, |
| 72 | created_at TEXT, |