public function clearNotificationMatches($username, $personal, $team)
{
$db = $this->connect();
// Clean input.
$personal = Database_SQLiteInjection::no($personal);
$team = Database_SQLiteInjection::no($team);
// Select all identifiers of the personal change proposals.
$query = "SELECT identifier FROM notifications WHERE username = '******' AND category = '{$personal}';";
$personals = array();
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$personals[] = $row[0];
}
// Matches to be deleted.
$deletes = array();
// Go through each of the personal change proposals.
foreach ($personals as $personalID) {
$bible = $this->getNotificationBible($personalID);
$passage = $this->getNotificationPassage($personalID);
$book = $passage['book'];
$chapter = $passage['chapter'];
$verse = $passage['verse'];
$modification = $this->getNotificationModification($personalID);
$modification = Database_SQLiteInjection::no($modification);
// Get all matching identifiers from the team's change notifications.
$query = "SELECT identifier FROM notifications WHERE username = '******' AND category = '{$team}' AND bible = '{$bible}' AND book = {$book} AND chapter = {$chapter} AND verse = {$verse} AND modification = '{$modification}';";
$teamMatches = array();
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$teamMatches[] = $row[0];
}
// There should be exactly one candidate for the matches to be removed.
// If there are none, it means that the personal change didn't make it to the team's text.
// If there are two or more matching changes, then one could have undone the other, so should not be automatically removed.
if (count($teamMatches) == 1) {
foreach ($teamMatches as $teamMatches) {
// Check there are only two change notifications for this user / Bible / book / chapter / verse.
// If there are more, we can't be sure that the personal change proposal was not overwritten somehow.
$passageMatches = array();
$query = "SELECT identifier FROM notifications WHERE username = '******' AND bible = '{$bible}' AND book = {$book} AND chapter = {$chapter} AND verse = {$verse};";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$passageMatches[] = $row[0];
}
if (count($passageMatches) == 2) {
// Store the personal change proposal to be deleted.
// Store the matching change notification to be deleted also.
foreach ($passageMatches as $passageMatche) {
$deletes[] = $passageMatche;
}
}
}
}
}
// Delete all stored identifiers to be deleted.
foreach ($deletes as $delete) {
$this->deleteNotification($delete, $db);
}
unset($db);
}
/**
* Connect to database server using data from config file.
*/
function database_connect()
{
global $db_type, $db, $db_config;
$result = false;
// create database object
switch ($db_type) {
case DatabaseType::MYSQL:
$db = new Database_MySQL();
$connected = $db->connect($db_config);
$selected = $db->select($db_config['name']);
$result = $connected && $selected;
// connection was successful but database doesn't exist
if ($connected && (!$selected || $selected && !ModuleManager::getInstance()->tableExists())) {
$result = database_initialize(!$selected);
}
break;
case DatabaseType::PGSQL:
break;
case DatabaseType::SQLITE:
$db = new Database_SQLite();
$result = $db->connect($db_config);
// try to initialize database
if (!$result && !$db->exists($db_config['name'])) {
$result = $db->create($db_config['name']);
if ($result) {
$result = database_initialize();
}
}
break;
}
return $result;
}
public function setValue($id, $key, $value)
{
$id = Database_SQLiteInjection::no($id);
$key = Database_SQLiteInjection::no($key);
$value = Database_SQLiteInjection::no($value);
$query = "DELETE FROM volatile WHERE id = {$id} AND key = '{$key}';";
Database_SQLite::exec($this->db, $query);
$query = "INSERT INTO volatile VALUES ({$id}, '{$key}', '{$value}');";
Database_SQLite::exec($this->db, $query);
}
public function get($bible)
{
$bible = Database_SQLiteInjection::no($bible);
$commits = array();
$query = "SELECT sha1 FROM commits WHERE bible = '{$bible}';";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$commits[] = $row[0];
}
return $commits;
}
public function testExport()
{
$database_styles = Database_Styles::getInstance();
$database_styles->createSheet("phpunit");
$markers = $database_styles->getMarkers("phpunit");
Database_SQLite::exec($database_styles->db, "BEGIN;");
foreach ($markers as $marker) {
if ($marker != "add") {
$database_styles->deleteMarker("phpunit", $marker);
}
}
Database_SQLite::exec($database_styles->db, "COMMIT;");
$xml = $database_styles->exportXml("phpunit");
$standard = <<<'EOD'
<?xml version="1.0" encoding="UTF-8"?>
<stylesheet>
<style>
<marker>add</marker>
<name>* Translational Addition</name>
<info>For a translational addition to the text</info>
<category>st</category>
<type>4</type>
<subtype>0</subtype>
<fontsize>60.0</fontsize>
<italic>1</italic>
<bold>0</bold>
<underline>0</underline>
<smallcaps>0</smallcaps>
<superscript>0</superscript>
<justification>0</justification>
<spacebefore>0.0</spacebefore>
<spaceafter>0.0</spaceafter>
<leftmargin>0.0</leftmargin>
<rightmargin>0.0</rightmargin>
<firstlineindent>0.0</firstlineindent>
<spancolumns>0</spancolumns>
<color>000000</color>
<print>1</print>
<userbool1>0</userbool1>
<userbool2>0</userbool2>
<userbool3>0</userbool3>
<userint1>0</userint1>
<userint2>0</userint2>
<userint3>0</userint3>
<userstring1/>
<userstring2/>
<userstring3/>
</style>
</stylesheet>
EOD;
$this->assertEquals(trim($standard), trim($xml));
}
/**
* Initializes a database connection.
* It returns the connection, if successful.
*
* @param string $db_server
* @param string $db_name
* @param string $db_user
* @param string $db_passwd
* @param string $db_prefix
* @param array $db_options
*
* @return resource
*/
static function initiate($db_server, $db_name, $db_user, $db_passwd, $db_prefix, $db_options = array())
{
global $db_in_transact, $sqlite_error;
if (substr($db_name, -3) != '.db') {
$db_name .= '.db';
}
// initialize the instance... if not done already!
if (self::$_db === null) {
self::$_db = new self();
}
if (!empty($db_options['persist'])) {
$connection = @sqlite_popen($db_name, 0666, $sqlite_error);
} else {
$connection = @sqlite_open($db_name, 0666, $sqlite_error);
}
// Something's wrong, show an error if its fatal (which we assume it is)
if (!$connection) {
if (!empty($db_options['non_fatal'])) {
return null;
} else {
display_db_error();
}
}
$db_in_transact = false;
// This is frankly stupid - stop SQLite returning alias names!
@sqlite_query('PRAGMA short_column_names = 1', $connection);
// Make some user defined functions!
sqlite_create_function($connection, 'unix_timestamp', 'elk_udf_unix_timestamp', 0);
sqlite_create_function($connection, 'inet_aton', 'elk_udf_inet_aton', 1);
sqlite_create_function($connection, 'inet_ntoa', 'elk_udf_inet_ntoa', 1);
sqlite_create_function($connection, 'find_in_set', 'elk_udf_find_in_set', 2);
sqlite_create_function($connection, 'year', 'elk_udf_year', 1);
sqlite_create_function($connection, 'month', 'elk_udf_month', 1);
sqlite_create_function($connection, 'dayofmonth', 'elk_udf_dayofmonth', 1);
sqlite_create_function($connection, 'concat', 'elk_udf_concat');
sqlite_create_function($connection, 'locate', 'elk_udf_locate', 2);
sqlite_create_function($connection, 'regexp', 'elk_udf_regexp', 2);
return $connection;
}
/**
* handleEmail - handles a confirmation email received from $from with subject $subject and body $body.
* Returns true if the mail was handled, else false.
*/
public function handleEmail($from, $subject, $body)
{
// Find out in the confirmation database whether the $subject line contains an active ID.
// If not, bail out.
$database_confirm = Database_Confirm::getInstance();
$id = $database_confirm->searchID($subject);
if ($id == 0) {
return false;
}
// An active ID was found: Execute the associated database query.
$query = $database_confirm->getQuery($id);
$database_users = Database_Users::getInstance();
Database_SQLite::exec($database_users->db, $query);
// Send confirmation mail.
$mailto = $database_confirm->getMailTo($id);
$subject = $database_confirm->getSubject($id);
$body = $database_confirm->getBody($id);
$database_mail = Database_Mail::getInstance();
$database_mail->send($mailto, $subject, $body);
// Delete the confirmation record.
$database_confirm->delete($id);
// Job done.
return true;
}
public function searchStrong($strong)
{
$strong = Database_SQLiteInjection::no($strong);
$query = "SELECT DISTINCT book, chapter, verse FROM kjv WHERE strong = '{$strong}';";
$hits = array();
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
unset($row[2]);
$hits[] = $row;
}
return $hits;
}
public function getVerses($name, $book, $chapter)
{
$id = $this->getID($name);
$book = Database_SQLiteInjection::no($book);
$chapter = Database_SQLiteInjection::no($chapter);
$query = "SELECT DISTINCT verse FROM data WHERE system = {$id} AND book = {$book} AND chapter = {$chapter} ORDER BY verse ASC;";
$result = Database_SQLite::query($this->db, $query);
$verses = array();
foreach ($result as $row) {
$verse = $row[0];
for ($i = 0; $i <= $verse; $i++) {
$verses[] = $i;
}
}
// Put verse 0 in chapter 0.
if ($chapter == 0) {
$verses[] = 0;
}
return $verses;
}
public function authors($bibles)
{
$authors = array();
$bibleCondition = " WHERE ";
foreach ($bibles as $offset => $bible) {
if ($offset) {
$bibleCondition .= " OR ";
}
$bible = Database_SQLiteInjection::no($bible);
$bibleCondition .= " bible = '{$bible}' ";
}
$query = "SELECT author, COUNT(*) as count FROM history {$bibleCondition} GROUP BY author ORDER BY count DESC;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$authors[] = $row[0];
}
return $authors;
}
private function getNextId($user)
{
$user = Database_SQLiteInjection::no($user);
// Get the ID of the active entry for the user.
$id = 0;
$query = "SELECT rowid FROM navigation WHERE username = '******' AND active = 1;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$id = $row[0];
}
// If no active ID was found, return NULL.
if ($id == 0) {
return NULL;
}
// Get the ID of the entry just after the active entry.
$query = "SELECT rowid FROM navigation WHERE rowid > {$id} AND username = '******' ORDER BY rowid ASC LIMIT 1;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row[0];
}
// Nothing found.
return NULL;
}
public function getResult($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "SELECT result FROM jobs WHERE id = {$id};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row['result'];
}
return false;
}
public function translate($input, $output, $book, $chapter, $verse)
{
// Care for situation that the input and output are the same.
if ($input == $output) {
$passage = array($book, $chapter, $verse);
return array($passage);
}
// Get the $input mapping for the passage from the database.
// This maps the $input to the Hebrew/Greek versification system.
// Skip this phase if the $input mapping is Hebrew / Greek.
$origpassage = array();
if ($input != $this->original()) {
$input = Database_SQLiteInjection::no($input);
$book = (int) $book;
$chapter = (int) $chapter;
$verse = (int) $verse;
$query = "SELECT origbook, origchapter, origverse FROM maps WHERE name = '{$input}' AND book = {$book} AND chapter = {$chapter} AND verse = {$verse};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row['origbook']);
unset($row['origchapter']);
unset($row['origverse']);
$origpassage[] = $row;
}
}
// Check that the search yields a passage.
// If there is none, it means that the $input passage is the same as in Hebrew/Greek.
if (empty($origpassage)) {
$passage = array($book, $chapter, $verse);
$origpassage = array($passage);
}
// If the $output mapping is Hebrew/Greek, then we're done.
if ($output == $this->original()) {
return $origpassage;
}
// Get the $output mapping for the passage or two passages from the database.
// This is a translation from Hebrew/Greek to the $output system.
$targetpassage = array();
$output = Database_SQLiteInjection::no($output);
foreach ($origpassage as $passage) {
$origbook = $passage[0];
$origchapter = $passage[1];
$origverse = $passage[2];
$query = "SELECT book, chapter, verse FROM maps WHERE name = '{$output}' AND origbook = {$origbook} AND origchapter = {$origchapter} AND origverse = {$origverse};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row['book']);
unset($row['chapter']);
unset($row['verse']);
$row[0] = (int) $row[0];
$row[1] = (int) $row[1];
$row[2] = (int) $row[2];
if (array_search($row, $targetpassage) === false) {
$targetpassage[] = $row;
}
}
}
// Check that the search yields a passage.
// If none, it means that the $output passage is the same as in Hebrew/Greek.
if (empty($targetpassage)) {
$targetpassage = $origpassage;
}
// Result.
return $targetpassage;
}
public function delete($bible, $book, $chapter)
{
$bible = Database_SQLiteInjection::no($bible);
$book = Database_SQLiteInjection::no($book);
$chapter = Database_SQLiteInjection::no($chapter);
$query = "DELETE FROM bibleactions WHERE bible = '{$bible}' AND book = {$book} AND chapter = {$chapter};";
Database_SQLite::exec($this->db, $query);
}
public function debug()
{
$result = Database_SQLite::query($this->db, "SELECT * from shell;");
foreach ($result as $row) {
for ($i = 0; $i <= 3; $i++) {
unset($row[$i]);
}
var_dump($row);
}
}
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 5, 14);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 6, 17);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 7, 17);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 8, 13);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 9, 21);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 10, 11);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 11, 19);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 12, 18);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 13, 18);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 14, 20);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 15, 8);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 16, 21);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 17, 18);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 18, 24);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 19, 21);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 20, 15);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 21, 27);");
Database_SQLite::exec($database_versifications->db, "INSERT INTO data (system, book, chapter, verse) VALUES (10, 66, 22, 21);");
Database_SQLite::exec($database_versifications->db, "COMMIT;");
$okay = !ob_get_flush();
display_header($okay);
if ($okay) {
display_okay();
display_paragraph("Versifications database okay.");
} else {
display_paragraph("Errors creating or upgrading the versifications database");
open_paragraph();
display_link("versifications1.php", "Retry");
close_paragraph();
}
display_footer();
public function healthy($file)
{
if (file_exists($file)) {
$healthy = false;
$db = $this->connect($file);
$query = "PRAGMA integrity_check;";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
if ($row[0] == "ok") {
$healthy = true;
}
}
unset($db);
if (!$healthy) {
unlink($file);
}
}
}
public function searchHebrew($hebrew)
{
$hebrew = Database_SQLiteInjection::no($hebrew);
$query = "SELECT DISTINCT book, chapter, verse FROM morphhb WHERE hebrew = '{$hebrew}';";
$hits = array();
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
unset($row[2]);
$hits[] = $row;
}
return $hits;
}
public function trim()
{
// Leave entries for 30 days.
$time = time() - 2592000;
$query = "DELETE FROM confirm WHERE timestamp < {$time};";
Database_SQLite::exec($this->db, $query);
}
public function postpone($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "UPDATE mail SET retry = retry + 1 WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
$query = "UPDATE mail SET timestamp = timestamp + retry * 900 WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function delete($rowid)
{
$rowid = Database_SQLiteInjection::no($rowid);
$query = "DELETE FROM noteactions where rowid = {$rowid};";
Database_SQLite::exec($this->db, $query);
}
public function clearHistory($bible, $year, $month)
{
$bible = Database_SQLiteInjection::no($bible);
$year = Database_SQLiteInjection::no($year);
$month = Database_SQLiteInjection::no($month);
$query = "DELETE FROM sprinthistory WHERE bible = '{$bible}' AND year = {$year} AND month = {$month};";
Database_SQLite::exec($this->db, $query);
}
public function hasWriteAccess($user, $sheet)
{
$query = "SELECT rowid FROM users WHERE user = '******' AND sheet = '{$sheet}';";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return true;
}
return false;
}
public function debug()
{
$query = "SELECT * FROM users;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
var_dump($row);
}
}
public function release($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "DELETE FROM suppress WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function get($day)
{
$day = (int) $day;
//$firstsecond = strtotime ("today") - ($day * 86400);
// A day is considered not a natural day, but a period of 24 hours from now.
$day++;
$firstsecond = time() - $day * 86400;
$lastsecond = $firstsecond + 86400 - 1;
$firstfilename = $firstsecond . str_repeat("0", 8);
$lastfilename = $lastsecond . str_repeat("9", 8);
$lastentry = $firstfilename;
$entries = array();
// Read them from the database.
$db = self::connect();
$firsttimestamp = Database_SQLiteInjection::no($firstfilename);
$lasttimestamp = Database_SQLiteInjection::no($lastfilename);
$query = "SELECT entry FROM logs WHERE timestamp >= {$firsttimestamp} AND timestamp <= {$lasttimestamp} ORDER BY rowid ASC;";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$entries[] = $row[0];
}
unset($db);
// Add them from the filesystem.
$files = scandir($this->folder());
$files = Filter_Folders::cleanup($files);
foreach ($files as $file) {
if ($file >= $firstfilename && $file <= $lastfilename) {
$lastentry = $file;
$entries[] = file_get_contents($this->folder() . "/{$file}");
}
}
return array($lastentry, $entries);
}
public function getType($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "SELECT type FROM books WHERE id = {$id};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row[0];
}
return "";
}
public function getNotesInRangeForBibles($lowId, $highId, $bibles)
{
$db = self::connect();
$identifiers = array();
$lowId = Database_SQLiteInjection::no($lowId);
$highId = Database_SQLiteInjection::no($highId);
$bibleSelector = $this->getBibleSelector($bibles);
$query = "SELECT identifier FROM notes WHERE identifier >= {$lowId} AND identifier <= {$highId} {$bibleSelector} ORDER BY identifier;";
$result = Database_SQLite::query($db, $query);
foreach ($result as $row) {
$identifiers[] = $row[0];
}
unset($db);
return $identifiers;
}
public function searchGreek($greek)
{
$greek = Database_SQLiteInjection::no($greek);
$query = "SELECT DISTINCT book, chapter, verse FROM sblgnt WHERE greek = '{$greek}';";
$hits = array();
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row[0]);
unset($row[1]);
unset($row[2]);
$hits[] = $row;
}
return $hits;
}
public function getVerseCount($bible)
{
$bible = Database_SQLiteInjection::no($bible);
$query = "SELECT count(*) FROM bibles WHERE bible = '{$bible}';";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
return $row[0];
}
return 0;
}
