/* This file is part of Ezra Bible App.
Copyright (C) 2019 - 2023 Ezra Bible App Development Team <contact@ezrabibleapp.net>
Ezra Bible App is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 2 of the License, or
(at your option) any later version.
Ezra Bible App is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with Ezra Bible App. See the file LICENSE.
If not, see <http://www.gnu.org/licenses/>. */
'use strict';
/**
* The VerseReference model is used to manage verse references.
* @typedef VerseReference
* @category Model
*/
module.exports = (sequelize, DataTypes) => {
const VerseReference = sequelize.define('VerseReference', {
bibleBookId: DataTypes.INTEGER,
chapter: DataTypes.INTEGER,
verseNr: DataTypes.INTEGER,
absoluteVerseNrEng: DataTypes.INTEGER,
absoluteVerseNrHeb: DataTypes.INTEGER,
bibleBookShortTitle: DataTypes.VIRTUAL,
bibleBookLongTitle: DataTypes.VIRTUAL,
tagList: DataTypes.VIRTUAL,
tagGroupList: DataTypes.VIRTUAL,
noteText: DataTypes.VIRTUAL,
}, {
timestamps: false
});
VerseReference.associate = function(models) {
VerseReference.belongsToMany(models.Tag, {through: 'VerseTags'});
VerseReference.hasOne(models.Note);
};
VerseReference.prototype.getBibleBook = function() {
return global.models.BibleBook.findByPk(this.bibleBookId);
};
VerseReference.prototype.getOrCreateNote = async function() {
var note = await this.getNote();
if (note == null) { // create the note if it does not exist yet
note = await this.createNote();
}
return note;
};
VerseReference.findOrCreateFromVerseObject = async function(verseObject, versification) {
var bibleBook = null;
var bibleBookId = null;
var bibleBookShortTitle = null;
var conditions = null;
var chapter = null;
var verseNr = null;
var absoluteVerseNrs = null;
if (verseObject._isBookNoteVerse) {
bibleBookShortTitle = verseObject._bibleBookShortTitle;
bibleBook = await global.models.BibleBook.findOne({ where: { shortTitle: bibleBookShortTitle } });
if (bibleBook == null) {
throw `Could not get Bible book with title ${bibleBookShortTitle}`;
}
conditions = { bibleBookId: bibleBook.id, chapter: 0, verseNr: 0 };
chapter = 0;
verseNr = 0;
absoluteVerseNrs = {};
absoluteVerseNrs['absoluteVerseNrEng'] = 0;
absoluteVerseNrs['absoluteVerseNrHeb'] = 0;
} else {
bibleBookShortTitle = verseObject._bibleBookShortTitle;
bibleBookId = verseObject._bibleBookId;
var absoluteVerseNr = verseObject._absoluteVerseNr;
chapter = verseObject._chapter;
verseNr = verseObject._verseNr;
bibleBook = await global.models.BibleBook.findOne({ where: { shortTitle: bibleBookShortTitle } });
if (bibleBook == null) {
throw `Could not get Bible book with title ${bibleBookShortTitle}`;
}
bibleBookId = bibleBook.id;
absoluteVerseNrs = global.models.VerseReference.getAbsoluteVerseNrs(versification, bibleBookShortTitle, absoluteVerseNr, chapter, verseNr);
conditions = { bibleBookId: bibleBookId, absoluteVerseNrEng: absoluteVerseNr };
if (versification == 'HEBREW') {
conditions = { bibleBookId: bibleBookId, absoluteVerseNrHeb: absoluteVerseNr };
}
}
// eslint-disable-next-line no-unused-vars
const [ verseReference, created ] = await global.models.VerseReference.findOrCreate({
where: conditions,
defaults: {
bibleBookId: bibleBook.id,
chapter: chapter,
verseNr: verseNr,
absoluteVerseNrEng: absoluteVerseNrs["absoluteVerseNrEng"],
absoluteVerseNrHeb: absoluteVerseNrs["absoluteVerseNrHeb"]
}
});
if (!created) {
// Rewrite the chapter and verseNr in case the existing values are null
// This may be the case due to a bug that existed in the frontend (see #979)
// We cannot undo the wrongful saving of verse references, but we can rewrite the verse reference
// if we encounter one that has missing chapter / verseNr attributes.
if (verseReference.dataValues.chapter == null) {
verseReference.chapter = chapter;
} else if (verseReference.dataValues.verseNr == null) {
verseReference.verseNr = verseNr;
}
await verseReference.save();
}
return verseReference;
};
VerseReference.getBookReference = async function(bookShortTitle) {
var bibleBook = await global.models.BibleBook.findOne({ where: { shortTitle: bookShortTitle } });
var bibleBookId = bibleBook.id;
return await this.findOne({
where: { bibleBookId: bibleBookId, chapter: 0, verseNr: 0 }
});
};
VerseReference.findByBookAndAbsoluteVerseNumber = function(bookShortTitle, absoluteVerseNr, versification) {
var absoluteVerseNrField = (versification == 'eng' ? 'absoluteVerseNrEng' : 'absoluteVerseNrHeb');
var query = "SELECT vr.*, " +
" b.shortTitle as bibleBookShortTitle, " +
" b.longTitle AS bibleBookLongTitle" +
" FROM VerseReferences vr" +
" INNER JOIN BibleBooks b ON" +
" vr.bibleBookId = b.id" +
" WHERE b.shortTitle = '" + bookShortTitle + "'" +
" AND vr." + absoluteVerseNrField + "=" + absoluteVerseNr +
" ORDER BY vr.absoluteVerseNrEng ASC";
return sequelize.query(query, { model: global.models.VerseReference });
};
VerseReference.findByTagIds = function(tagIds) {
var query = "SELECT vr.*, " +
" b.shortTitle as bibleBookShortTitle, " +
" b.longTitle AS bibleBookLongTitle" +
" FROM VerseReferences vr" +
" INNER JOIN BibleBooks b ON" +
" vr.bibleBookId = b.id" +
" INNER JOIN VerseTags vt ON" +
" vt.verseReferenceId = vr.id" +
" WHERE vt.tagId IN (" + tagIds + ")" +
" ORDER BY vr.absoluteVerseNrEng ASC";
return sequelize.query(query, { model: global.models.VerseReference });
};
VerseReference.findByXrefs = async function(xrefs) {
var verseReferences = [];
for (var i = 0; i < xrefs.length; i++) {
var splittedReference = xrefs[i].split('.');
var book = splittedReference[0];
var chapter = splittedReference[1];
var verseNr = splittedReference[2];
var currentReferenceQuery = "SELECT vr.*, " +
" b.shortTitle as bibleBookShortTitle, " +
" b.longTitle as bibleBookLongTitle" +
" FROM VerseReferences vr" +
" INNER JOIN BibleBooks b ON" +
" vr.bibleBookId = b.id" +
" WHERE vr.bibleBookId = b.id" +
" AND vr.chapter = " + chapter +
" AND vr.verseNr = " + verseNr +
" AND b.shortTitle = '" + book + "'";
//console.log("CURRENT REFERENCE: ");
//console.log(currentReferenceQuery);
var currentDbReferenceList = await sequelize.query(currentReferenceQuery, { model: global.models.VerseReference });
if (currentDbReferenceList.length > 0) {
verseReferences.push(currentDbReferenceList[0]);
}
}
return verseReferences;
};
VerseReference.findAllWithUserData = function() {
var verseReferences = null;
var query = `SELECT vr.*,
b.shortTitle as bibleBookShortTitle,
b.longTitle AS bibleBookLongTitle,
REPLACE(GROUP_CONCAT(DISTINCT t.title), ',', ';') AS tagList,
REPLACE(GROUP_CONCAT(DISTINCT tg.title), ',', ';') AS tagGroupList,
n.text AS noteText
FROM VerseReferences vr
INNER JOIN BibleBooks b ON
vr.bibleBookId = b.id
LEFT JOIN VerseTags vt ON
vt.verseReferenceId = vr.id
LEFT JOIN Tags t ON
vt.verseReferenceId = vr.id AND vt.tagId = t.id
LEFT JOIN TagGroupMembers tgm ON
tgm.tagId = t.id
LEFT JOIN TagGroups tg ON
tg.id = tgm.tagGroupId
LEFT JOIN Notes n ON n.verseReferenceId = vr.id
GROUP BY vr.id
ORDER BY b.number ASC, vr.absoluteVerseNrEng ASC`;
verseReferences = sequelize.query(query, { model: global.models.VerseReference });
return verseReferences;
};
VerseReference.getAbsoluteVerseNrs = function(versification, bibleBook, absoluteVerseNr, chapter, verseNr) {
var absoluteVerseNrEng = null;
var absoluteVerseNrHeb = null;
if (versification == 'HEBREW') {
absoluteVerseNrHeb = absoluteVerseNr;
absoluteVerseNrEng = this.getAbsoluteVerseNrEngFromHeb(bibleBook, absoluteVerseNrHeb, chapter, verseNr);
} else {
absoluteVerseNrEng = absoluteVerseNr;
absoluteVerseNrHeb = this.getAbsoluteVerseNrHebFromEng(bibleBook, absoluteVerseNrEng, chapter, verseNr);
}
return {
"absoluteVerseNrEng": absoluteVerseNrEng,
"absoluteVerseNrHeb": absoluteVerseNrHeb
};
};
VerseReference.getAbsoluteVerseNrHebFromEng = function(bibleBookShortTitle, absoluteVerseNrEng, chapter, verseNr) {
var offset = global.models.VerseReference.getOffsetForVerseReference(bibleBookShortTitle, chapter, verseNr);
var absoluteVerseNrHeb = absoluteVerseNrEng + offset;
return absoluteVerseNrHeb;
};
VerseReference.getAbsoluteVerseNrEngFromHeb = function(bibleBookShortTitle, absoluteVerseNrHeb, chapter, verseNr) {
var offset = global.models.VerseReference.getOffsetForVerseReference(bibleBookShortTitle, chapter, verseNr);
var absoluteVerseNrEng = absoluteVerseNrHeb - offset;
return absoluteVerseNrEng;
};
VerseReference.getOffsetTable = function(bibleBookShortTitle) {
var offset_tables = global.models.VerseReference.getAllOffsetTables();
for (var key in offset_tables) {
if (key.toLowerCase() == bibleBookShortTitle.toLowerCase()) {
return offset_tables[key];
}
}
return null;
};
VerseReference.getOffsetForVerseReference = function(bibleBookShortTitle, chapter, verseNr) {
var offset = 0;
var offset_table = global.models.VerseReference.getOffsetTable(bibleBookShortTitle);
if (offset_table != undefined) {
for (var i = 0; i < offset_table.length; i++) {
var currentSection = offset_table[i];
if (global.models.VerseReference.isInVerseRange(currentSection['start'], currentSection['end'], chapter, verseNr)) {
offset = currentSection['offset'];
break;
}
}
}
return offset;
};
VerseReference.getAllOffsetTables = function() {
// The following mapping tables define offsets for various books.
// The defined offsets are always from the perspective of the ENGLISH versification compared to the HEBREW versification
var offset_table_1_samuel = [
{ 'start' : '21:1',
'end' : '26:25',
'offset' : +1
}
];
var offset_table_1_kings = [
{ 'start' : '22:44',
'end' : '22:54',
'offset' : +1
}
];
var offset_table_2_kings = [
{ 'start' : '15:39',
'end' : '25:30',
'offset' : +1
}
];
var offset_table_1_chronicles = [
{ 'start' : '12:5',
'end' : '29:30',
'offset' : +1
}
];
var offset_table_nehemiah = [
{ 'start' : '9:38',
'end' : '13:31',
'offset' : -1 // TODO
}
];
var offset_table_psalms = [
{ 'start' : "3:2", 'end' : "4:1", 'offset' : +1 },
{ 'start' : "4:2", 'end' : "5:1", 'offset' : +2 },
{ 'start' : "5:2", 'end' : "6:1", 'offset' : +3 },
{ 'start' : "6:2", 'end' : "7:1", 'offset' : +4 },
{ 'start' : "7:2", 'end' : "8:1", 'offset' : +5 },
{ 'start' : "8:2", 'end' : "9:1", 'offset' : +6 },
{ 'start' : "9:2", 'end' : "12:1", 'offset' : +7 },
{ 'start' : "12:2", 'end' : "13:1", 'offset' : +8 },
{ 'start' : "13:2", 'end' : "13:5", 'offset' : +9 },
{ 'start' : "13:6", 'end' : "18:1", 'offset' : +8 },
{ 'start' : "18:2", 'end' : "19:1", 'offset' : +9 },
{ 'start' : "19:2", 'end' : "20:1", 'offset' : +10 },
{ 'start' : "20:2", 'end' : "21:1", 'offset' : +11 },
{ 'start' : "21:2", 'end' : "22:1", 'offset' : +12 },
{ 'start' : "22:2", 'end' : "30:1", 'offset' : +13 },
{ 'start' : "30:2", 'end' : "31:1", 'offset' : +14 },
{ 'start' : "31:2", 'end' : "34:1", 'offset' : +15 },
{ 'start' : "34:2", 'end' : "36:1", 'offset' : +16 },
{ 'start' : "36:2", 'end' : "38:1", 'offset' : +17 },
{ 'start' : "38:2", 'end' : "39:1", 'offset' : +18 },
{ 'start' : "39:2", 'end' : "40:1", 'offset' : +19 },
{ 'start' : "40:2", 'end' : "41:1", 'offset' : +20 },
{ 'start' : "41:2", 'end' : "42:1", 'offset' : +21 },
{ 'start' : "42:2", 'end' : "44:1", 'offset' : +22 },
{ 'start' : "44:2", 'end' : "45:1", 'offset' : +23 },
{ 'start' : "45:2", 'end' : "46:1", 'offset' : +24 },
{ 'start' : "46:2", 'end' : "47:1", 'offset' : +25 },
{ 'start' : "47:2", 'end' : "48:1", 'offset' : +26 },
{ 'start' : "48:2", 'end' : "49:1", 'offset' : +27 },
{ 'start' : "49:2", 'end' : "51:1", 'offset' : +28 },
{ 'start' : "51:2", 'end' : "51:2", 'offset' : +29 },
{ 'start' : "51:3", 'end' : "52:1", 'offset' : +30 },
{ 'start' : "52:2", 'end' : "52:2", 'offset' : +31 },
{ 'start' : "52:3", 'end' : "53:1", 'offset' : +32 },
{ 'start' : "53:2", 'end' : "54:1", 'offset' : +33 },
{ 'start' : "54:2", 'end' : "54:2", 'offset' : +34 },
{ 'start' : "54:3", 'end' : "55:1", 'offset' : +35 },
{ 'start' : "55:2", 'end' : "56:1", 'offset' : +36 },
{ 'start' : "56:2", 'end' : "57:1", 'offset' : +37 },
{ 'start' : "57:2", 'end' : "58:1", 'offset' : +38 },
{ 'start' : "58:2", 'end' : "59:1", 'offset' : +39 },
{ 'start' : "59:2", 'end' : "60:1", 'offset' : +40 },
{ 'start' : "60:2", 'end' : "60:2", 'offset' : +41 },
{ 'start' : "60:3", 'end' : "61:1", 'offset' : +42 },
{ 'start' : "61:2", 'end' : "62:1", 'offset' : +43 },
{ 'start' : "62:2", 'end' : "63:1", 'offset' : +44 },
{ 'start' : "63:2", 'end' : "64:1", 'offset' : +45 },
{ 'start' : "64:2", 'end' : "65:1", 'offset' : +46 },
{ 'start' : "65:2", 'end' : "67:1", 'offset' : +47 },
{ 'start' : "67:2", 'end' : "68:1", 'offset' : +48 },
{ 'start' : "68:2", 'end' : "69:1", 'offset' : +49 },
{ 'start' : "69:2", 'end' : "70:1", 'offset' : +50 },
{ 'start' : "70:2", 'end' : "75:1", 'offset' : +51 },
{ 'start' : "75:2", 'end' : "76:1", 'offset' : +52 },
{ 'start' : "76:2", 'end' : "77:1", 'offset' : +53 },
{ 'start' : "77:2", 'end' : "80:1", 'offset' : +54 },
{ 'start' : "80:2", 'end' : "81:1", 'offset' : +55 },
{ 'start' : "81:2", 'end' : "83:1", 'offset' : +56 },
{ 'start' : "83:2", 'end' : "84:1", 'offset' : +57 },
{ 'start' : "84:2", 'end' : "85:1", 'offset' : +58 },
{ 'start' : "85:2", 'end' : "88:1", 'offset' : +59 },
{ 'start' : "88:2", 'end' : "89:1", 'offset' : +60 },
{ 'start' : "89:2", 'end' : "92:1", 'offset' : +61 },
{ 'start' : "92:2", 'end' : "102:1", 'offset' : +62 },
{ 'start' : "102:2", 'end' : "108:1", 'offset' : +63 },
{ 'start' : "108:2", 'end' : "140:1", 'offset' : +64 },
{ 'start' : "140:2", 'end' : "142:1", 'offset' : +65 },
{ 'start' : "142:2", 'end' : "150:6", 'offset' : +66 },
];
var offset_table_isaiah = [
{ 'start' : '64:1',
'end' : '66:24',
'offset' : -1
}
];
var offset_table_acts = [
{ 'start' : '19:41',
'end' : '28:31',
'offset' : -1
}
];
var offset_table_2co = [
{ 'start' : '13:13',
'end' : '13:14',
'offset' : -1
}
];
var offset_table_revelation = [
{ 'start' : '12:17',
'end' : '22:21',
'offset' : +1
}
];
var offset_tables = {
'1Sam' : offset_table_1_samuel,
'1Kgs' : offset_table_1_kings,
'2Kgs' : offset_table_2_kings,
'1Chr' : offset_table_1_chronicles,
'Neh' : offset_table_nehemiah,
'Ps' : offset_table_psalms,
'Isa' : offset_table_isaiah,
'Acts' : offset_table_acts,
'2Cor' : offset_table_2co,
'Rev' : offset_table_revelation
};
return offset_tables;
};
VerseReference.isInVerseRange = function(startReference, endReference, chapter, verseNr) {
var startChapter = parseInt(startReference.split(':')[0]);
var startVerse = parseInt(startReference.split(':')[1]);
var endChapter = parseInt(endReference.split(':')[0]);
var endVerse = parseInt(endReference.split(':')[1]);
var inChapterRange = false;
var inVerseRange = false;
if (chapter >= startChapter &&
chapter <= endChapter) {
inChapterRange = true;
}
if (chapter == startChapter &&
chapter == endChapter &&
verseNr >= startVerse &&
verseNr <= endVerse) {
inVerseRange = true;
} else if (chapter == startChapter &&
chapter != endChapter &&
verseNr >= startVerse) {
inVerseRange = true;
} else if (chapter == endChapter &&
chapter != startChapter &&
verseNr <= endVerse) {
inVerseRange = true;
} else if (chapter > startChapter &&
chapter < endChapter) {
inVerseRange = true;
}
return inChapterRange && inVerseRange;
};
return VerseReference;
};
Source