diff options
-rw-r--r-- | backend.php | 60 | ||||
-rw-r--r-- | config.php-dist | 7 | ||||
-rw-r--r-- | include/db.php | 151 | ||||
-rw-r--r-- | include/functions.php | 26 | ||||
-rw-r--r-- | index.php | 26 | ||||
-rw-r--r-- | js/read.js | 11 | ||||
-rw-r--r-- | read.html | 9 | ||||
-rw-r--r-- | schema.sql | 13 |
8 files changed, 62 insertions, 241 deletions
diff --git a/backend.php b/backend.php index 83977b7..4a19916 100644 --- a/backend.php +++ b/backend.php @@ -1,16 +1,15 @@ <?php require_once "config.php"; - require_once "include/functions.php"; $op = $_REQUEST["op"]; header("Content-type: text/json"); - $link = db_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME); - init_connection($link); + $ldb = new SQLite3(SCRATCH_DB); + $ldb->busyTimeout(10*1000); - $owner = db_escape_string($_SERVER["PHP_AUTH_USER"]); + $owner = SQLite3::escapeString($_SERVER["PHP_AUTH_USER"]); if (!$owner) { print json_encode(["error" => "NOT_AUTHENTICATED"]); @@ -72,10 +71,10 @@ $bookid = (int) $_REQUEST["id"]; if ($bookid) { - $result = db_query($link, "SELECT pagination FROM epube_pagination WHERE bookid = '$bookid' LIMIT 1"); + $result = $ldb->query("SELECT pagination FROM epube_pagination WHERE bookid = '$bookid' LIMIT 1"); - if (db_num_rows($result) != 0) { - print db_fetch_result($result, 0, "pagination"); + if ($line = $result->fetchArray()) { + print $line["pagination"]; } else { print json_encode(["error" => "NOT_FOUND"]); } @@ -84,80 +83,83 @@ break; case "storepagination": $bookid = (int) $_REQUEST["id"]; - $payload = db_escape_string($_REQUEST["payload"]); + $payload = SQLite3::escapeString($_REQUEST["payload"]); $total_pages = (int) $_REQUEST["total"]; if ($bookid && $payload && $total_pages) { - db_query($link, "BEGIN"); + $ldb->query("BEGIN"); - $result = db_query($link, "SELECT id FROM epube_pagination WHERE bookid = '$bookid' LIMIT 1"); + $result = $ldb->query("SELECT id FROM epube_pagination WHERE bookid = '$bookid' LIMIT 1"); - if (db_num_rows($result) != 0) { - $id = db_fetch_result($result, 0, "id"); + if ($line = $result->fetchArray()) { + $id = $line["id"]; - db_query($link, "UPDATE epube_pagination SET pagination = '$payload', + $ldb->query("UPDATE epube_pagination SET pagination = '$payload', total_pages = '$total_pages' WHERE id = '$id'"); } else { - db_query($link, "INSERT INTO epube_pagination (bookid, pagination, total_pages) VALUES + $ldb->query("INSERT INTO epube_pagination (bookid, pagination, total_pages) VALUES ('$bookid', '$payload', '$total_pages')"); } - db_query($link, "COMMIT"); + $ldb->query("COMMIT"); } break; case "getlastread": $bookid = (int) $_REQUEST["id"]; $lastread = 0; + $lastcfi = ""; if ($bookid) { - $result = db_query($link, "SELECT id, lastread FROM epube_books + $result = $ldb->query("SELECT id, lastread, lastcfi FROM epube_books WHERE bookid = '$bookid' AND owner = '$owner' LIMIT 1"); - if (db_num_rows($result) != 0) { - $lastread = (int) db_fetch_result($result, 0, "lastread"); + if ($line = $result->fetchArray()) { + $lastread = (int) $line["lastread"]; + $lastcfi = $line["lastcfi"]; } } - print json_encode(["lastread" => $lastread]); + print json_encode(["page" => $lastread, "cfi" => $lastcfi]); break; case "storelastread": $page = (int) $_REQUEST["page"]; $bookid = (int) $_REQUEST["id"]; + $cfi = SQLite3::escapeString($_REQUEST["cfi"]); if ($page && $bookid) { - db_query($link, "BEGIN"); + $ldb->query("BEGIN"); - $result = db_query($link, "SELECT id, lastread FROM epube_books + $result = $ldb->query("SELECT id, lastread, lastcfi FROM epube_books WHERE bookid = '$bookid' AND owner = '$owner' LIMIT 1"); - if (db_num_rows($result) != 0) { - $id = db_fetch_result($result, 0, "id"); - $lastread = (int) db_fetch_result($result, 0, "lastread"); + if ($line = $result->fetchArray()) { + $id = $line["id"]; + $lastread = (int) $line["lastread"]; if ($lastread < $page || $page == -1) { if ($page == -1) $page = 0; - db_query($link, "UPDATE epube_books SET lastread = '$page' WHERE id = '$id'"); + $ldb->query("UPDATE epube_books SET lastread = '$page', lastcfi = '$cfi' WHERE id = '$id'"); } } else { - db_query($link, "INSERT INTO epube_books (bookid, owner, lastread) VALUES - ('$bookid', '$owner', '$page')"); + $ldb->query("INSERT INTO epube_books (bookid, owner, lastread, lastcfi) VALUES + ('$bookid', '$owner', '$page', '$cfi')"); } - db_query($link, "COMMIT"); + $ldb->query("COMMIT"); } - print json_encode(["lastread" => $page]); + print json_encode(["page" => $page, "cfi" => $cfi]); break; diff --git a/config.php-dist b/config.php-dist index 676d72f..7f6d737 100644 --- a/config.php-dist +++ b/config.php-dist @@ -1,12 +1,7 @@ <?php // vim:ft=php: - define('DB_TYPE', "pgsql"); // or mysql - define('DB_HOST', "localhost"); - define('DB_USER', "fox"); - define('DB_NAME', "fox"); - define('DB_PASS', "pass"); - + define('SCRATCH_DB', 'db/scratch.db'); define('BOOKS_DIR', '/home/fox/Books'); define('CALIBRE_DB', BOOKS_DIR . "/metadata.db"); diff --git a/include/db.php b/include/db.php deleted file mode 100644 index 3d96b50..0000000 --- a/include/db.php +++ /dev/null @@ -1,151 +0,0 @@ -<?php - -require_once "config.php"; - -function db_reconnect($link, $host, $user, $pass, $db) { - $attempts = 0; - - while ($attempts < 10) { - - if (!$link) $link = db_connect($host, $user, $pass, $db); - - $result = db_query($link, "SELECT NOW()", false); - - if (db_num_rows($result) == 1) { -// echo "[db] connection successful.\n"; - return $link; - } else { -// echo "[db] connection failed; reconnect attempt $attempts.\n"; - $link = false; - $attempts++; - sleep(1); - } - } - - return false; -} - -function db_connect($host, $user, $pass, $db) { - if (DB_TYPE == "pgsql") { - - $string = "dbname=$db user=$user password=$pass"; - - if ($host) { - $string .= " host=$host"; - } - - if (defined('DB_PORT')) { - $string = "$string port=" . DB_PORT; - } - - $link = pg_connect($string); - - if (!$link) { - die("Connection failed: " . pg_last_error($link)); - } - - return $link; - - } else if (DB_TYPE == "mysql") { - $link = mysql_connect($host, $user, $pass); - if ($link) { - $result = mysql_select_db($db, $link); - if (!$result) { - die("Can't select DB: " . mysql_error($link)); - } - return $link; - } else { - die("Connection failed: " . mysql_error($link)); - } - } -} - -function db_escape_string($s) { - if (DB_TYPE == "pgsql") { - return pg_escape_string($s); - } else { - return mysql_real_escape_string($s); - } -} - -function db_query($link, $query, $die_on_error = true) { - if (DB_TYPE == "pgsql") { - $result = pg_query($link, $query); - if (!$result) { - $query = htmlspecialchars($query); // just in case - if ($die_on_error) { - die("Query <i>$query</i> failed [$result]: " . pg_last_error($link) . "\n"); - } - } - return $result; - } else if (DB_TYPE == "mysql") { - $result = mysql_query($query, $link); - if (!$result) { - $query = htmlspecialchars($query); - if ($die_on_error) { - die("Query <i>$query</i> failed: " . mysql_error($link) . "\n"); - } - } - return $result; - } -} - -function db_fetch_assoc($result) { - if (DB_TYPE == "pgsql") { - return pg_fetch_assoc($result); - } else if (DB_TYPE == "mysql") { - return mysql_fetch_assoc($result); - } -} - - -function db_num_rows($result) { - if (DB_TYPE == "pgsql") { - return pg_num_rows($result); - } else if (DB_TYPE == "mysql") { - return mysql_num_rows($result); - } -} - -function db_fetch_result($result, $row, $param) { - if (DB_TYPE == "pgsql") { - return pg_fetch_result($result, $row, $param); - } else if (DB_TYPE == "mysql") { - // I hate incoherent naming of PHP functions - return mysql_result($result, $row, $param); - } -} - -function db_unescape_string($str) { - $tmp = str_replace("\\\"", "\"", $str); - $tmp = str_replace("\\'", "'", $tmp); - return $tmp; -} - -function db_close($link) { - if (DB_TYPE == "pgsql") { - - return pg_close($link); - - } else if (DB_TYPE == "mysql") { - return mysql_close($link); - } -} - -function db_affected_rows($link, $result) { - if (DB_TYPE == "pgsql") { - return pg_affected_rows($result); - } else if (DB_TYPE == "mysql") { - return mysql_affected_rows($link); - } -} - -function db_last_error($link) { - if (DB_TYPE == "pgsql") { - return pg_last_error($link); - } else if (DB_TYPE == "mysql") { - return mysql_error($link); - } -} - -?> diff --git a/include/functions.php b/include/functions.php deleted file mode 100644 index 9563190..0000000 --- a/include/functions.php +++ /dev/null @@ -1,26 +0,0 @@ -<?php - require_once "db.php"; - - function init_connection($link) { - - if (!$link) { - if (DB_TYPE == "mysql") { - print mysql_error(); - } - // PG seems to display its own errors just fine by default. - die("Connection failed."); - } - - if (DB_TYPE == "pgsql") { - pg_query($link, "set client_encoding = 'UTF-8'"); - pg_set_client_encoding("UNICODE"); - pg_query($link, "set datestyle = 'ISO, european'"); - pg_query($link, "set time zone '".DB_TIMEZONE."'"); - } else { - if (defined('MYSQL_CHARSET') && MYSQL_CHARSET) { - db_query($link, "SET NAMES " . MYSQL_CHARSET); - // db_query($link, "SET CHARACTER SET " . MYSQL_CHARSET); - } - } - } - @@ -1,9 +1,16 @@ <?php require_once "config.php"; - require_once "include/functions.php"; - $link = db_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME); - init_connection($link); + if (!is_writable(SCRATCH_DB)) { + die(SCRATCH_DB . " is not writable"); + } + + if (!is_writable(dirname(SCRATCH_DB))) { + die(dirname(SCRATCH_DB) . " directory is not writable"); + } + + $ldb = new SQLite3(SCRATCH_DB); + $ldb->busyTimeout(10*1000); ?> <!DOCTYPE html> <html> @@ -64,9 +71,8 @@ <?php require_once "config.php"; - require_once "include/functions.php"; - $owner = db_escape_string($_SERVER["PHP_AUTH_USER"]); + $owner = SQLite3::escapeString($_SERVER["PHP_AUTH_USER"]); if (!$owner) { print "<h1>Not authenticated</h1>"; @@ -76,7 +82,7 @@ $db = new SQLite3(CALIBRE_DB, SQLITE3_OPEN_READONLY); if ($query) { - $query_esc = db_escape_string($query); + $query_esc = SQLite3::escapeString($query); $search_qpart = "(LOWER(books.author_sort) LIKE LOWER('%$query_esc%') OR LOWER(books.title) LIKE LOWER('%$query_esc%') OR LOWER(series_name) LIKE LOWER('%$query_esc%'))"; @@ -111,13 +117,13 @@ if ($line["epub_id"]) { $read_link = "read.html?" . http_build_query(["id" => $line["epub_id"]]); - $lastread_result = db_query($link, "SELECT lastread, total_pages FROM epube_books, epube_pagination + $lastread_result = $ldb->query("SELECT lastread, total_pages FROM epube_books, epube_pagination WHERE epube_pagination.bookid = epube_books.bookid AND epube_books.bookid = " . $line["epub_id"] . " AND owner = '$owner'"); - if (db_num_rows($lastread_result) > 0) { - $lastread = db_fetch_result($lastread_result, 0, "lastread"); - $total_pages = db_fetch_result($lastread_result, 0, "total_pages"); + if ($lastread_line = $lastread_result->fetchArray(SQLITE3_ASSOC)) { + $lastread = $lastread_line["lastread"]; + $total_pages = $lastread_line["total_pages"]; $is_read = $total_pages - $lastread < 5; $in_progress = $lastread > 1; @@ -91,9 +91,9 @@ function zoom(incr) { } function clear_lastread() { - if (confirm("Clear stored last read page?")) { - $.post("backend.php", { op: "storelastread", page: -1, id: $.urlParam("id") }, function(data) { - $(".lastread_input").val(data.lastread); + if (confirm("Clear stored last read location?")) { + $.post("backend.php", { op: "storelastread", page: -1, cfi: "", id: $.urlParam("id") }, function(data) { + $(".lastread_input").val(data.page); }); } } @@ -101,9 +101,10 @@ function clear_lastread() { function mark_as_read() { if (confirm("Mark book as read?")) { var total = window.book.pagination.totalPages; + var lastCfi = book.pagination.cfiFromPage(total); - $.post("backend.php", { op: "storelastread", page: total, id: $.urlParam("id") }, function(data) { - $(".lastread_input").val(data.lastread); + $.post("backend.php", { op: "storelastread", page: total, cfi: lastCfi, id: $.urlParam("id") }, function(data) { + $(".lastread_input").val(data.page); }); } } @@ -63,7 +63,7 @@ <hr/> <div class="form-group"> - <label class="col-sm-3 control-label">Last read page</label> + <label class="col-sm-3 control-label">Last location</label> <div class="col-sm-9"> <div class="input-group"> @@ -195,7 +195,7 @@ $.get("backend.php", { op: "getlastread", id: $.urlParam("id") }, function(data) { - $(".lastread_input").val(data.lastread); + $(".lastread_input").val(data.page); }); @@ -260,7 +260,7 @@ } $.get("backend.php", { op: "getlastread", id: $.urlParam("id") }, function(data) { - if (data.lastread) book.gotoPage(data.lastread); + if (data.cfi) book.gotoCfi(data.cfi); }); $("#total_pages").html(book.pagination.totalPages); @@ -292,7 +292,8 @@ if (_store_position && new Date().getTime()/1000 - _last_position_sync > 60) { console.log("storing lastread"); - $.post("backend.php", { op: "storelastread", id: $.urlParam("id"), page: location.anchorPage }); + $.post("backend.php", { op: "storelastread", id: $.urlParam("id"), page: location.anchorPage, + cfi: book.getCurrentLocationCfi() }); _store_position = 0; _last_position_sync = new Date().getTime()/1000; } @@ -1,22 +1,15 @@ -drop table if exists epube_settings; drop table if exists epube_pagination; drop table if exists epube_books; ---create table epube_settings( --- id serial not null primary key, --- owner varchar(200) not null unique, --- font_size integer not null, --- font_family varchar(200) not null, --- line_height integer not null); - create table epube_pagination( - id serial not null primary key, + id integer not null primary key autoincrement, bookid integer not null, total_pages integer not null, pagination text not null); create table epube_books( - id serial not null primary key, + id integer not null primary key autoincrement, bookid integer not null, owner varchar(200) not null, + lastcfi varchar(200) not null, lastread integer not null); |