book_info

[zjkuang]

Adding a new table book_info by book_info.sql providing some more information about each book such as its full title, abbreviation, category, OT/NT, etc. for the convenience of app developers.

[scrollmapper]

Great contribution. I will review this soon. Thank you again.

[perlun]

@scrollmapper Any chance we could get this merged btw? It seems like a nice addition. :smile:

Note that the otnt fields seem to be a bit of a duplication of the t field in key_english:

MariaDB [bible]> select  * from key_english limit 10;
+----+-------------+----+---+
| b  | n           | t  | g |
+----+-------------+----+---+
|  1 | Genesis     | OT | 1 |
|  2 | Exodus      | OT | 1 |
|  3 | Leviticus   | OT | 1 |
|  4 | Numbers     | OT | 1 |
|  5 | Deuteronomy | OT | 1 |
|  6 | Joshua      | OT | 2 |
|  7 | Judges      | OT | 2 |
|  8 | Ruth        | OT | 2 |
|  9 | 1 Samuel    | OT | 2 |
| 10 | 2 Samuel    | OT | 2 |
+----+-------------+----+---+
10 rows in set (0.001 sec)

I was about to say the same of the genre (that it was the same as category added here), but I realize it's actually quite different data now when I look at it a bit closer.

Anyway, to query out the human readable form of the genre, a query like this can be used:

MariaDB [bible]> select key_english.b, key_english.n, key_english.t, genre.n from key_english 
                 inner join key_genre_english genre on key_english.g = genre.g;
+----+-----------------+----+-------------+
| b  | n               | t  | n           |
+----+-----------------+----+-------------+
|  1 | Genesis         | OT | Law         |
|  2 | Exodus          | OT | Law         |
|  3 | Leviticus       | OT | Law         |
|  4 | Numbers         | OT | Law         |
|  5 | Deuteronomy     | OT | Law         |
|  6 | Joshua          | OT | History     |
|  7 | Judges          | OT | History     |
|  8 | Ruth            | OT | History     |
|  9 | 1 Samuel        | OT | History     |
| 10 | 2 Samuel        | OT | History     |
| 11 | 1 Kings         | OT | History     |
| 12 | 2 Kings         | OT | History     |
| 13 | 1 Chronicles    | OT | History     |
| 14 | 2 Chronicles    | OT | History     |
| 15 | Ezra            | OT | History     |
| 16 | Nehemiah        | OT | History     |
| 17 | Esther          | OT | History     |
| 18 | Job             | OT | Wisdom      |
| 19 | Psalms          | OT | Wisdom      |
| 20 | Proverbs        | OT | Wisdom      |
| 21 | Ecclesiastes    | OT | Wisdom      |
| 22 | Song of Solomon | OT | Wisdom      |
| 23 | Isaiah          | OT | Prophets    |
| 24 | Jeremiah        | OT | Prophets    |
| 25 | Lamentations    | OT | Prophets    |
| 26 | Ezekiel         | OT | Prophets    |
| 27 | Daniel          | OT | Prophets    |
| 28 | Hosea           | OT | Prophets    |
| 29 | Joel            | OT | Prophets    |
| 30 | Amos            | OT | Prophets    |
| 31 | Obadiah         | OT | Prophets    |
| 32 | Jonah           | OT | Prophets    |
| 33 | Micah           | OT | Prophets    |
| 34 | Nahum           | OT | Prophets    |
| 35 | Habakkuk        | OT | Prophets    |
| 36 | Zephaniah       | OT | Prophets    |
| 37 | Haggai          | OT | Prophets    |
| 38 | Zechariah       | OT | Prophets    |
| 39 | Malachi         | OT | Prophets    |
| 40 | Matthew         | NT | Gospels     |
| 41 | Mark            | NT | Gospels     |
| 42 | Luke            | NT | Gospels     |
| 43 | John            | NT | Gospels     |
| 44 | Acts            | NT | Acts        |
| 45 | Romans          | NT | Epistles    |
| 46 | 1 Corinthians   | NT | Epistles    |
| 47 | 2 Corinthians   | NT | Epistles    |
| 48 | Galatians       | NT | Epistles    |
| 49 | Ephesians       | NT | Epistles    |
| 50 | Philippians     | NT | Epistles    |
| 51 | Colossians      | NT | Epistles    |
| 52 | 1 Thessalonians | NT | Epistles    |
| 53 | 2 Thessalonians | NT | Epistles    |
| 54 | 1 Timothy       | NT | Epistles    |
| 55 | 2 Timothy       | NT | Epistles    |
| 56 | Titus           | NT | Epistles    |
| 57 | Philemon        | NT | Epistles    |
| 58 | Hebrews         | NT | Epistles    |
| 59 | James           | NT | Epistles    |
| 60 | 1 Peter         | NT | Epistles    |
| 61 | 2 Peter         | NT | Epistles    |
| 62 | 1 John          | NT | Epistles    |
| 63 | 2 John          | NT | Epistles    |
| 64 | 3 John          | NT | Epistles    |
| 65 | Jude            | NT | Epistles    |
| 66 | Revelation      | NT | Apocalyptic |
+----+-----------------+----+-------------+
66 rows in set (0.002 sec)