string Database_MorphGnt::get_item (const char * item, int rowid)
{
// The $rowid refers to the main table.
// Update it so it refers to the sub table.
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT");
sql.add (item);
sql.add ("FROM morphgnt WHERE rowid =");
sql.add (rowid);
sql.add (";");
vector <string> result = sql.query () [item];
rowid = 0;
if (!result.empty ()) rowid = convert_to_int (result [0]);
// Retrieve the requested value from the sub table.
sql.clear ();
sql.add ("SELECT");
sql.add (item);
sql.add ("FROM");
sql.add (item);
sql.add ("WHERE rowid =");
sql.add (rowid);
sql.add (";");
result = sql.query () [item];
if (!result.empty ()) return result [0];
// Not found.
return "";
}
int Database_MorphGnt::get_id (const char * table_row, string item)
{
SqliteDatabase sql = SqliteDatabase (filename ());
// Two iterations to be sure a rowid can be returned.
for (unsigned int i = 0; i < 2; i++) {
// Check on the rowid and return it if it's there.
sql.clear ();
sql.add ("SELECT rowid FROM");
sql.add (table_row);
sql.add ("WHERE");
sql.add (table_row);
sql.add ("=");
sql.add (item);
sql.add (";");
vector <string> result = sql.query () ["rowid"];
if (!result.empty ()) return convert_to_int (result [0]);
// The rowid was not found: Insert the word into the table.
// The rowid will now be found during the second iteration.
sql.clear ();
sql.add ("INSERT INTO");
sql.add (table_row);
sql.add ("VALUES (");
sql.add (item);
sql.add (");");
sql.execute ();
}
return 0;
}
// Returns the username that matches the cookie sent by the browser.
// Once a day, $daily will be set true.
string Database_Login::getUsername (string cookie, bool & daily)
{
SqliteDatabase sql (database ());
sql.add ("SELECT rowid, timestamp, username FROM logins WHERE cookie =");
sql.add (cookie);
sql.add (";");
map <string, vector <string> > result = sql.query ();
if (result.empty()) return "";
string username = result ["username"][0];
int stamp = convert_to_int (result ["timestamp"] [0]);
if (stamp != timestamp ()) {
// Touch the timestamp. This occurs once a day.
int rowid = convert_to_int (result ["rowid"] [0]);
sql.clear ();
sql.add ("UPDATE logins SET timestamp =");
sql.add (timestamp ());
sql.add ("WHERE rowid =");
sql.add (rowid);
sql.execute ();
daily = true;
} else {
daily = false;
}
return username;
}
// Returns the username that matches the remote IP $address and the browser's user $agent,
// and the other fingerprints from the user.
string Database_Login::getUsername (string address, string agent, string fingerprint)
{
address = md5 (address);
agent = md5 (agent);
fingerprint = md5 (fingerprint);
SqliteDatabase sql (database ());
sql.add ("SELECT rowid, timestamp, username FROM logins WHERE address =");
sql.add (address);
sql.add ("AND agent =");
sql.add (agent);
sql.add ("AND fingerprint =");
sql.add (fingerprint);
sql.add (";");
map <string, vector <string> > result = sql.query ();
if (result.empty()) return "";
string username = result ["username"][0];
int stamp = convert_to_int (result ["timestamp"] [0]);
if (stamp != timestamp ()) {
// Touch the timestamp. This occurs once a day.
int rowid = convert_to_int (result ["rowid"] [0]);
sql.clear ();
sql.add ("UPDATE logins SET timestamp =");
sql.add (timestamp ());
sql.add ("WHERE rowid =");
sql.add (rowid);
sql.execute ();
}
return username;
}
int Database_Privileges::getBibleBookCount ()
{
SqliteDatabase sql (database ());
sql.add ("SELECT count(*) FROM bibles;");
vector <string> result = sql.query () ["count(*)"];
if (result.empty ()) return 0;
return convert_to_int (result [0]);
}
// Returns true if the $id exists
bool Database_Confirm::IDExists (unsigned int id)
{
SqliteDatabase sql (filename ());
sql.add ("SELECT id FROM confirm WHERE id =");
sql.add (id);
sql.add (";");
vector <string> ids = sql.query () ["id"];
return !ids.empty ();
}
string Database_HebrewLexicon::getbdb (string id)
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT definition FROM bdb WHERE id =");
sql.add (id);
sql.add (";");
vector <string> result = sql.query () ["definition"];
if (!result.empty ()) return result [0];
return "";
}
void Database_Privileges::getBible (string username, string bible, bool & read, bool & write)
{
SqliteDatabase sql (database ());
sql.add ("SELECT write FROM bibles WHERE username ="******"AND bible =");
sql.add (bible);
sql.add (";");
vector <string> result = sql.query () ["write"];
read = (!result.empty());
sql.clear ();
sql.add ("SELECT write FROM bibles WHERE username ="******"AND bible =");
sql.add (bible);
sql.add ("AND write;");
result = sql.query () ["write"];
write = (!result.empty());
}
string Database_HebrewLexicon::getstrong (string strong)
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT definition FROM strong WHERE strong =");
sql.add (strong);
sql.add (";");
vector <string> result = sql.query () ["definition"];
if (!result.empty ()) return result [0];
return "";
}
string Database_HebrewLexicon::getpos (string code)
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT name FROM pos WHERE code =");
sql.add (code);
sql.add (";");
vector <string> result = sql.query () ["name"];
if (!result.empty ()) return result [0];
return "";
}
string Database_HebrewLexicon::getmap (string id)
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT bdb FROM map WHERE id =");
sql.add (id);
sql.add (";");
vector <string> result = sql.query () ["bdb"];
if (!result.empty ()) return result [0];
return "";
}
// Returns whether the device, that matches the cookie it sent, is touch-enabled.
bool Database_Login::getTouchEnabled (string cookie)
{
SqliteDatabase sql (database ());
sql.add ("SELECT touch FROM logins WHERE cookie =");
sql.add (cookie);
sql.add (";");
vector <string> result = sql.query () ["touch"];
if (!result.empty()) return convert_to_bool (result [0]);
return false;
}
string Database_HebrewLexicon::getaug (string aug)
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT target FROM aug WHERE aug =");
sql.add (aug);
sql.add (";");
vector <string> result = sql.query () ["target"];
if (!result.empty ()) return result [0];
return "";
}
// Returns the email's body for $id.
string Database_Confirm::getBody (unsigned int id)
{
SqliteDatabase sql (filename ());
sql.add ("SELECT body FROM confirm WHERE id =");
sql.add (id);
sql.add (";");
vector <string> result = sql.query () ["body"];
if (!result.empty ()) return result [0];
return "";
}
string Database_Privileges::save (string username)
{
SqliteDatabase sql (database ());
vector <string> lines;
lines.push_back (bibles_start ());
sql.add ("SELECT bible, book, write FROM bibles WHERE username ="******";");
map <string, vector <string> > result = sql.query ();
vector <string> bible = result ["bible"];
vector <string> book = result ["book"];
vector <string> write = result ["write"];
for (size_t i = 0; i < bible.size (); i++) {
lines.push_back (bible [i]);
lines.push_back (book [i]);
// It could have just stored 0 or 1 for the boolean values.
// But if that were done, then there would be no change in the length of the file
// when changing only boolean values.
// And then the client would not re-download that file.
// To use "on" and "off", that solves the issue.
bool b = convert_to_bool (write[i]);
if (b) lines.push_back (on ());
else lines.push_back (off ());
}
lines.push_back (bibles_end ());
lines.push_back (features_start ());
sql.clear ();
sql.add ("SELECT feature FROM features WHERE username ="******";");
result = sql.query ();
vector <string> feature = result ["feature"];
for (size_t i = 0; i < feature.size (); i++) {
lines.push_back (feature [i]);
}
lines.push_back (features_end ());
return filter_string_implode (lines, "\n");
}
bool Database_Privileges::getFeature (string username, int feature)
{
SqliteDatabase sql (database ());
sql.add ("SELECT rowid FROM features WHERE username ="******"AND feature =");
sql.add (feature);
sql.add (";");
vector <string> result = sql.query () ["rowid"];
if (result.empty()) return false;
return true;
}
// Search the database for an existing ID in $subject.
// If it exists, it returns the ID number, else it returns 0.
unsigned int Database_Confirm::searchID (string subject)
{
SqliteDatabase sql (filename ());
sql.add ("SELECT id FROM confirm;");
vector <string> ids = sql.query () ["id"];
for (string id : ids) {
size_t pos = subject.find (id);
if (pos != string::npos) {
return convert_to_int (id);
}
}
return 0;
}
// Returns true if a record for $username / $bible / $book exists in the database.
// When the $book = 0, it takes any book.
bool Database_Privileges::getBibleBookExists (string username, string bible, int book)
{
SqliteDatabase sql (database ());
sql.add ("SELECT rowid FROM bibles WHERE username ="******"AND bible =");
sql.add (bible);
if (book) {
sql.add ("AND book =");
sql.add (book);
}
sql.add (";");
vector <string> result = sql.query () ["rowid"];
return !result.empty();
}
vector <int> Database_MorphGnt::rowids (int book, int chapter, int verse)
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT rowid FROM morphgnt WHERE book =");
sql.add (book);
sql.add ("AND chapter =");
sql.add (chapter);
sql.add ("AND verse =");
sql.add (verse);
sql.add ("ORDER BY rowid;");
vector <string> result = sql.query () ["rowid"];
vector <int> rowids;
for (auto rowid : result) rowids.push_back (convert_to_int (rowid));
return rowids;
}
// Returns whether the device that matches the remote IP $address and the browser's user $agent,
// and the other fingerprint, is touch-enabled.
bool Database_Login::getTouchEnabled (string address, string agent, string fingerprint)
{
address = md5 (address);
agent = md5 (agent);
fingerprint = md5 (fingerprint);
SqliteDatabase sql (database ());
sql.add ("SELECT touch FROM logins WHERE address =");
sql.add (address);
sql.add ("AND agent =");
sql.add (agent);
sql.add ("AND fingerprint =");
sql.add (fingerprint);
sql.add (";");
vector <string> result = sql.query () ["touch"];
if (!result.empty()) return convert_to_bool (result [0]);
return false;
}
// Get array of book / chapter / verse of all passages that contain a $hebrew word.
vector <Passage> Database_MorphHb::searchHebrew (string hebrew)
{
int word_id = get_id ("word", hebrew);
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT DISTINCT book, chapter, verse FROM morphhb WHERE word =");
sql.add (word_id);
sql.add ("ORDER BY rowid;");
vector <Passage> hits;
map <string, vector <string> > result = sql.query ();
vector <string> books = result ["book"];
vector <string> chapters = result ["chapter"];
vector <string> verses = result ["verse"];
for (unsigned int i = 0; i < books.size (); i++) {
Passage passage = Passage ();
passage.book = convert_to_int (books [i]);
passage.chapter = convert_to_int (chapters [i]);
passage.verse = verses [i];
hits.push_back (passage);
}
return hits;
}
// Get all passages that contain a strong's number.
vector <Passage> Database_Kjv::searchStrong (string strong)
{
int strongid = get_id ("strong", strong);
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("SELECT DISTINCT book, chapter, verse FROM kjv2 WHERE strong =");
sql.add (strongid);
sql.add ("ORDER BY rowid;");
vector <Passage> hits;
map <string, vector <string> > result = sql.query ();
vector <string> books = result ["book"];
vector <string> chapters = result ["chapter"];
vector <string> verses = result ["verse"];
for (unsigned int i = 0; i < books.size (); i++) {
Passage passage = Passage ();
passage.book = convert_to_int (books [i]);
passage.chapter = convert_to_int (chapters [i]);
passage.verse = verses [i];
hits.push_back (passage);
}
return hits;
}
// Read the privilege from the database whether $username has access to $bible $book.
// The privileges are stored in $read for read-only access,
// and in $write for write access.
void Database_Privileges::getBibleBook (string username, string bible, int book, bool & read, bool & write)
{
SqliteDatabase sql (database ());
sql.add ("SELECT write FROM bibles WHERE username ="******"AND bible =");
sql.add (bible);
sql.add ("AND book =");
sql.add (book);
sql.add (";");
vector <string> result = sql.query () ["write"];
if (result.empty()) {
// Not in database: No access.
read = false;
write = false;
} else {
// Occurs in database: Read access.
read = true;
// Take write access from the database field.
write = convert_to_bool (result [0]);
}
}