public function record($bible, $sha1)
{
$bible = Database_SQLiteInjection::no($bible);
$sha1 = Database_SQLiteInjection::no($sha1);
$query = "INSERT INTO commits VALUES ('{$bible}', '{$sha1}');";
Database_SQLite::exec($this->db, $query);
}
public function create()
{
$database_logs = Database_Logs::getInstance();
Database_SQLite::exec($this->db, "PRAGMA temp_store = MEMORY;");
Database_SQLite::exec($this->db, "PRAGMA synchronous = OFF;");
Database_SQLite::exec($this->db, "PRAGMA journal_mode = OFF;");
$sql = <<<'EOD'
CREATE TABLE IF NOT EXISTS morphhb (
book integer,
chapter integer,
verse integer,
hebrew text
);
EOD;
Database_SQLite::exec($this->db, $sql);
$books = array("Gen", "Exod", "Lev", "Num", "Deut", "Josh", "Judg", "Ruth", "1Sam", "2Sam", "1Kgs", "2Kgs", "1Chr", "2Chr", "Ezra", "Neh", "Esth", "Job", "Ps", "Prov", "Eccl", "Song", "Isa", "Jer", "Lam", "Ezek", "Dan", "Hos", "Joel", "Amos", "Obad", "Jonah", "Mic", "Nah", "Hab", "Zeph", "Hag", "Zech", "Mal");
foreach ($books as $book => $osis) {
$book++;
$database_logs->log("Importing Hebrew data for {$book} {$osis}");
$xml = new XMLReader();
$xml->open("../morphhb/{$osis}.xml");
$chapter = 0;
$verse = 0;
$word = false;
$hebrew = "";
while ($xml->read()) {
$nodeType = $xml->nodeType;
$name = $xml->name;
if ($nodeType == XMLReader::ELEMENT) {
if ($name == "verse") {
$osisID = $xml->getAttribute("osisID");
$osisID = explode(".", $osisID);
$chapter = intval($osisID[1]);
$verse = intval($osisID[2]);
}
if ($name == "w") {
$word = true;
}
}
if ($nodeType == XMLReader::TEXT) {
if ($word) {
$hebrew = $xml->value;
$hebrew = trim($hebrew);
$hebrew = str_replace("/", "", $hebrew);
$hebrew = str_replace("'", "''", $hebrew);
$sql = "INSERT INTO morphhb (book, chapter, verse, hebrew) VALUES ({$book}, {$chapter}, {$verse}, '{$hebrew}');";
Database_SQLite::exec($this->db, $sql);
}
}
if ($nodeType == XMLReader::END_ELEMENT) {
if ($name == "w") {
$word = false;
}
}
}
$xml->close();
}
}
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 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));
}
public function record($timestamp, $author, $bible, $book, $chapter, $verse, $oldtext, $modification, $newtext)
{
$timestamp = Database_SQLiteInjection::no($timestamp);
$author = Database_SQLiteInjection::no($author);
$bible = Database_SQLiteInjection::no($bible);
$book = Database_SQLiteInjection::no($book);
$chapter = Database_SQLiteInjection::no($chapter);
$verse = Database_SQLiteInjection::no($verse);
$oldtext = Database_SQLiteInjection::no($oldtext);
$modification = Database_SQLiteInjection::no($modification);
$newtext = Database_SQLiteInjection::no($newtext);
$query = "INSERT INTO history VALUES ({$timestamp}, '{$author}', '{$bible}', {$book}, {$chapter}, {$verse}, '{$oldtext}', '{$modification}', '{$newtext}');";
Database_SQLite::exec($this->db, $query);
}
/**
* 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 createSystem($name)
{
// If the versification system already exists, return its ID.
$id = $this->getID($name);
if ($id > 0) {
return $id;
}
// Get the first free ID starting from 1000.
$id = 0;
$query = "SELECT system FROM names ORDER BY system DESC LIMIT 1;";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
$id = $row[0];
}
$id++;
if ($id < 1000) {
$id = 1000;
}
// Create the empty system.
$name = Database_SQLiteInjection::no($name);
$query = "INSERT INTO names VALUES ({$id}, '{$name}');";
Database_SQLite::exec($this->db, $query);
// Return new ID.
return $id;
}
public function deleteChapter($bible, $book, $chapter)
{
$bible = Database_SQLiteInjection::no($bible);
$book = Database_SQLiteInjection::no($book);
$chapter = Database_SQLiteInjection::no($chapter);
$query = "DELETE FROM bibles WHERE bible = '{$bible}' AND book = {$book} AND chapter = {$chapter};";
Database_SQLite::exec($this->db, $query);
}
public function store($name, $book, $chapter, $verse, $html)
{
$folder = $this->resourceFolder($name);
if (!file_exists($folder)) {
mkdir($folder, 0777, true);
}
$db = null;
$file = $this->databaseFile($name, $book);
$this->healthy($file);
if (!file_exists($file)) {
$db = $this->connect($file);
$sql = <<<'EOD'
CREATE TABLE IF NOT EXISTS offlineresources (
chapter integer,
verse integer,
html text
);
EOD;
Database_SQLite::exec($db, $sql);
}
if ($db == null) {
$db = $this->connect($file);
}
$chapter = Database_SQLiteInjection::no($chapter);
$verse = Database_SQLiteInjection::no($verse);
$html = Database_SQLiteInjection::no($html);
$query = "DELETE FROM offlineresources WHERE chapter = {$chapter} AND verse = {$verse};";
Database_SQLite::exec($db, $query);
$query = "INSERT INTO offlineresources VALUES ({$chapter}, {$verse}, '{$html}');";
Database_SQLite::exec($db, $query);
unset($db);
}
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 setResult($id, $result)
{
$id = Database_SQLiteInjection::no($id);
$result = Database_SQLiteInjection::no($result);
$query = "UPDATE jobs SET result = '{$result}' WHERE id = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function rotate()
{
$db = self::connect();
// Speed up adding and removing records.
Database_SQLite::exec($db, "PRAGMA synchronous = OFF;");
// Transfer the journal entries from the filesystem into the database.
// This speeds up subsequent reading of the Journal by the users.
// Use a mechanism that handles huge amounts of entries.
// Function scandir would choke on this, or take a very long time.
// Function opendir / readdir / closedir could handle it better.
$folder = $this->folder();
$counter = 0;
$files = array();
$handle = opendir($folder);
while (($entry = readdir($handle)) !== false) {
if ($entry != "." && $entry != ".." && $entry != ".htaccess") {
$files[] = $entry;
}
// Read a limited number of entries to avoid getting stuck in them.
$counter++;
if ($counter > 10000) {
break;
}
}
closedir($handle);
sort($files);
foreach ($files as $file) {
$path = $folder . "/" . $file;
$timestamp = $file;
// Some of the code below have suppressed errors.
// This is to ensure that transferring the entries does not generate additional entries entries,
// which could lead to an infinite loop, as has been noticed on shared hosting.
// It has also been observed that huge journal entries keep generating other huge entries,
// which causes the disk to get full.
// Therefore huge journal entries will be truncated, and a short message written to the Journal.
$filesize = filesize($path);
if ($filesize > 10000) {
@($fp = fopen($path, 'r'));
@fseek($fp, 0);
@($entry = fread($fp, 100));
@fclose($fp);
$entry .= "... This entry was too large and has been truncated: {$filesize} bytes";
} else {
@($entry = file_get_contents($path));
}
@unlink($path);
$timestamp = Database_SQLiteInjection::no($timestamp);
$entry = Database_SQLiteInjection::no($entry);
$query = "INSERT INTO logs VALUES ({$timestamp}, '{$entry}');";
Database_SQLite::exec($db, $query);
}
// Remove records older than five days from the database.
$second = strtotime("today") - 6 * 86400;
$timestamp = $second . str_repeat("0", 8);
$timestamp = Database_SQLiteInjection::no($timestamp);
$query = "DELETE FROM logs WHERE timestamp < {$timestamp};";
Database_SQLite::exec($db, $query);
$query = "VACUUM logs;";
Database_SQLite::exec($db, $query);
unset($db);
}
public function addNewUser($username, $password, $level, $email)
{
$query = "INSERT INTO users (username, level, email) VALUES ('{$username}', {$level}, '{$email}');";
Database_SQLite::exec($this->db, $query);
}
public function create()
{
$database_logs = Database_Logs::getInstance();
Database_SQLite::exec($this->db, "PRAGMA temp_store = MEMORY;");
Database_SQLite::exec($this->db, "PRAGMA synchronous = OFF;");
Database_SQLite::exec($this->db, "PRAGMA journal_mode = OFF;");
$sql = <<<'EOD'
CREATE TABLE sblgnt (
book integer,
chapter integer,
verse integer,
greek text
);
EOD;
Database_SQLite::exec($this->db, $sql);
$book = 40 - 1;
$chapter = 0;
$verse = 0;
$word = false;
$greek = "";
$xml = new XMLReader();
$xml->open("../sblgnt/sblgnt.xml");
while ($xml->read()) {
$nodeType = $xml->nodeType;
$name = $xml->name;
if ($nodeType == XMLReader::ELEMENT) {
if ($name == "book") {
$book++;
$chapter = 0;
$verse = 0;
$database_logs->log("Importing Greek data for book {$book}");
$type = $xml->getAttribute("type");
if ($type == "book") {
}
}
if ($name == "verse-number") {
$id = $xml->getAttribute("id");
$id = explode(":", $id);
$chapter = substr($id[0], -2, 2);
$chapter = intval($chapter);
$verse = intval($id[1]);
}
if ($name == "w") {
$word = true;
}
}
if ($nodeType == XMLReader::TEXT) {
if ($word) {
$greek = $xml->value;
$greek = trim($greek);
$greek = str_replace("'", "''", $greek);
$sql = "INSERT INTO sblgnt (book, chapter, verse, greek) VALUES ({$book}, {$chapter}, {$verse}, '{$greek}');";
Database_SQLite::exec($this->db, $sql);
}
}
if ($nodeType == XMLReader::END_ELEMENT) {
if ($name == "w") {
$word = false;
}
}
}
$xml->close();
}
public function setReadOnlyAccess2Bible($user, $bible, $readonly)
{
$user = Database_SQLiteInjection::no($user);
$bible = Database_SQLiteInjection::no($bible);
$readonly = Filter_Bool::int($readonly);
$query = "UPDATE teams SET readonly = {$readonly} WHERE username = '******' AND bible = '{$bible}';";
Database_SQLite::exec($this->db, $query);
}
public function create()
{
$database_logs = Database_Logs::getInstance();
Database_SQLite::exec($this->db, "PRAGMA temp_store = MEMORY;");
Database_SQLite::exec($this->db, "PRAGMA synchronous = OFF;");
Database_SQLite::exec($this->db, "PRAGMA journal_mode = OFF;");
$sql = <<<'EOD'
CREATE TABLE kjv (
book integer,
chapter integer,
verse integer,
strong text,
english text
);
EOD;
Database_SQLite::exec($this->db, $sql);
$book = 0;
$chapter = 0;
$verse = 0;
$lemma = "";
$english = "";
$xml = new XMLReader();
$xml->open("../kjv/kjvfull.xml");
while ($xml->read()) {
$nodeType = $xml->nodeType;
$name = $xml->name;
if ($nodeType == XMLReader::ELEMENT) {
if ($name == "div") {
$type = $xml->getAttribute("type");
if ($type == "book") {
$book++;
$database_logs->log("Importing Strong's data and English glosses for Bible book" . " " . $book);
$chapter = 0;
$verse = 0;
}
}
if ($name == "chapter") {
$chapter++;
$verse = 0;
}
if ($name == "verse") {
$sID = $xml->getAttribute("sID");
if ($sID) {
$verse++;
}
}
if ($name == "w") {
$lemma = $xml->getAttribute("lemma");
$lemma = trim($lemma);
}
}
if ($nodeType == XMLReader::TEXT) {
$value = $xml->value;
$english = trim($value);
}
if ($nodeType == XMLReader::END_ELEMENT) {
if ($name == "w") {
$lemma = explode(" ", $lemma);
foreach ($lemma as $strong) {
if (strpos($strong, "strong") === false) {
continue;
}
$strong = str_replace("strong:", "", $strong);
$strong = str_replace("'", "''", $strong);
$english = str_replace("'", "''", $english);
$sql = "INSERT INTO kjv (book, chapter, verse, strong, english) VALUES ({$book}, {$chapter}, {$verse}, '{$strong}', '{$english}');";
Database_SQLite::exec($this->db, $sql);
}
$lemma = "";
$english = "";
}
}
}
$xml->close();
}
public function trim()
{
// Leave entries for 30 days.
$time = time() - 2592000;
$query = "DELETE FROM confirm WHERE timestamp < {$time};";
Database_SQLite::exec($this->db, $query);
}
/**
* Remove a process' data from the database.
*/
public function removeProcess($name, $pid)
{
$name = Database_SQLiteInjection::no($name);
$pid = Database_SQLiteInjection::no($pid);
$query = "DELETE FROM shell WHERE name = '{$name}' AND pid = '{$pid}';";
Database_SQLite::exec($this->db, $query);
}
public function getNext($user)
{
$id = $this->getNextId($user);
if ($id == NULL) {
return NULL;
}
// Update the 'active' flag.
$query = "UPDATE navigation SET active = 0 WHERE username = '******';";
Database_SQLite::exec($this->db, $query);
$query = "UPDATE navigation SET active = 1 WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
$query = "SELECT book, chapter, verse FROM navigation WHERE rowid = {$id};";
$result = Database_SQLite::query($this->db, $query);
foreach ($result as $row) {
unset($row['book']);
unset($row['chapter']);
unset($row['verse']);
return $row;
}
return array();
}
public function optimize()
{
Database_SQLite::exec($this->db, "REINDEX books;");
Database_SQLite::exec($this->db, "VACUUM books;");
}
public function clearNotificationsUser($username)
{
$identifiers = $this->getNotificationIdentifiers($username);
$db = $this->connect();
// Transaction speeds up the operation.
Database_SQLite::exec($db, "BEGIN;");
foreach ($identifiers as $identifier) {
$this->deleteNotification($identifier, $db);
}
Database_SQLite::exec($db, "COMMIT;");
unset($db);
}
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 release($id)
{
$id = Database_SQLiteInjection::no($id);
$query = "DELETE FROM suppress WHERE rowid = {$id};";
Database_SQLite::exec($this->db, $query);
}
public function delete($name)
{
$name = Database_SQLiteInjection::no($name);
$query = "DELETE FROM maps WHERE name = '{$name}';";
Database_SQLite::exec($this->db, $query);
}
public function deleteChecksum($identifier)
{
$db = self::connect_checksums();
$identifier = Database_SQLiteInjection::no($identifier);
$query = "DELETE FROM checksums WHERE identifier = {$identifier};";
Database_SQLite::exec($db, $query);
unset($db);
}
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 revokeWriteAccess($user, $sheet)
{
$user = Database_SQLiteInjection::no($user);
$sheet = Database_SQLiteInjection::no($sheet);
if ($user != "") {
$query = "DELETE FROM users WHERE user = '******' AND sheet = '{$sheet}';";
} else {
$query = "DELETE FROM users WHERE sheet = '{$sheet}';";
}
Database_SQLite::exec($this->db, $query);
}
