void Database_MorphGnt::store (int book, int chapter, int verse,
string pos, string parsing, string word, string lemma)
{
int pos_id = get_id ("pos", pos);
int parsing_id = get_id ("parsing", parsing);
int word_id = get_id ("word", word);
int lemma_id = get_id ("lemma", lemma);
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("PRAGMA temp_store = MEMORY;");
sql.execute ();
sql.clear ();
sql.add ("PRAGMA synchronous = OFF;");
sql.execute ();
sql.clear ();
sql.add ("PRAGMA journal_mode = OFF;");
sql.execute ();
sql.clear ();
sql.add ("INSERT INTO morphgnt VALUES (");
sql.add (book);
sql.add (",");
sql.add (chapter);
sql.add (",");
sql.add (verse);
sql.add (",");
sql.add (pos_id);
sql.add (",");
sql.add (parsing_id);
sql.add (",");
sql.add (word_id);
sql.add (",");
sql.add (lemma_id);
sql.add (");");
sql.execute ();
}
void Database_MorphHb::create ()
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("DROP TABLE IF EXISTS morphhb;");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE morphhb (book int, chapter int, verse int, parsing int, word int);");
sql.execute ();
sql.clear ();
sql.add ("DROP TABLE IF EXISTS parsing;");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS parsing (parsing text);");
sql.execute ();
sql.clear ();
sql.add ("DROP TABLE IF EXISTS word;");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS word (word text);");
sql.execute ();
}
void Database_MorphGnt::create ()
{
filter_url_unlink (database_sqlite_file (filename ()));
SqliteDatabase sql = SqliteDatabase (filename ());
sql.clear ();
sql.add ("CREATE TABLE morphgnt (book int, chapter int, verse int, pos int, parsing int, word int, lemma int);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS pos (pos text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS parsing (parsing text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS word (word text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS lemma (lemma text);");
sql.execute ();
}
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;
}
void Database_Kjv::store (int book, int chapter, int verse, string strong, string english)
{
int strongid = get_id ("strong", strong);
int englishid = get_id ("english", english);
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("PRAGMA temp_store = MEMORY;");
sql.execute ();
sql.clear ();
sql.add ("PRAGMA synchronous = OFF;");
sql.execute ();
sql.clear ();
sql.add ("PRAGMA journal_mode = OFF;");
sql.execute ();
sql.clear ();
sql.add ("INSERT INTO kjv2 VALUES (");
sql.add (book);
sql.add (",");
sql.add (chapter);
sql.add (",");
sql.add (verse);
sql.add (",");
sql.add (strongid);
sql.add (",");
sql.add (englishid);
sql.add (");");
sql.execute ();
}
void Database_HebrewLexicon::create ()
{
filter_url_unlink (database_sqlite_file (filename ()));
SqliteDatabase sql = SqliteDatabase (filename ());
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS aug (aug text, target text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS bdb (id text, definition text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS map (id text, bdb text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS pos (code text, name text);");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS strong (strong text, definition text);");
sql.execute ();
}
// 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;
}
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 "";
}
// 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;
}
void Database_Privileges::load (string username, const string & data)
{
// Clear all data for the user.
{
SqliteDatabase sql (database ());
sql.add ("DELETE FROM bibles WHERE username ="******";");
sql.execute ();
sql.clear ();
sql.add ("DELETE FROM features WHERE username ="******";");
sql.execute ();
}
vector <string> lines = filter_string_explode (data, '\n');
bool loading_bibles = false;
string bible_value;
int book_value = 0;
bool write_value = false;
bool loading_features = false;
int counter = 0;
for (auto & line : lines) {
if (line == bibles_end ()) {
loading_bibles = false;
}
if (line == features_end ()) {
loading_features = false;
}
counter++;
if (loading_bibles) {
if (counter == 1) bible_value = line;
if (counter == 2) book_value = convert_to_int (line);
if (counter == 3) {
write_value = (line == on ());
setBibleBook (username, bible_value, book_value, write_value);
counter = 0;
}
}
if (loading_features) {
setFeature (username, convert_to_int (line), true);
}
if (line == bibles_start ()) {
loading_bibles = true;
counter = 0;
}
if (line == features_start ()) {
loading_features = true;
counter = 0;
}
}
}
void Database_Kjv::create ()
{
SqliteDatabase sql = SqliteDatabase (filename ());
sql.add ("DROP TABLE IF EXISTS kjv2;");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE kjv2 (book int, chapter int, verse int, strong int, english int);");
sql.execute ();
sql.clear ();
sql.add ("DROP TABLE IF EXISTS strong;");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS strong (strong text);");
sql.execute ();
sql.clear ();
sql.add ("DROP TABLE IF EXISTS english;");
sql.execute ();
sql.clear ();
sql.add ("CREATE TABLE IF NOT EXISTS english (english text);");
sql.execute ();
sql.clear ();
sql.add ("VACUUM;");
sql.execute ();
}
// Remove privileges for $username from the entire database.
void Database_Privileges::removeUser (string username)
{
SqliteDatabase sql (database ());
sql.add ("DELETE FROM bibles WHERE username ="******";");
sql.execute ();
sql.clear ();
sql.add ("DELETE FROM features WHERE username ="******";");
sql.execute ();
}
void Database_Login::optimize ()
{
if (!healthy ()) {
// (Re)create damaged or non-existing database.
filter_url_unlink (database_sqlite_file (database ()));
create ();
}
// Vacuum it.
SqliteDatabase sql (database ());
// On Android, this pragma prevents the following error: VACUUM; Unable to open database file.
sql.add ("PRAGMA temp_store = MEMORY;");
sql.execute ();
sql.clear ();
sql.add ("VACUUM;");
sql.execute ();
}
void Database_Privileges::setFeature (string username, int feature, bool enabled)
{
SqliteDatabase sql (database ());
sql.add ("DELETE FROM features WHERE username ="******"AND feature =");
sql.add (feature);
sql.add (";");
sql.execute ();
if (enabled) {
sql.clear ();
sql.add ("INSERT INTO features VALUES (");
sql.add (username);
sql.add (",");
sql.add (feature);
sql.add (");");
sql.execute ();
}
}
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_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");
}
