summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--backend.php60
-rw-r--r--config.php-dist7
-rw-r--r--include/db.php151
-rw-r--r--include/functions.php26
-rw-r--r--index.php26
-rw-r--r--js/read.js11
-rw-r--r--read.html9
-rw-r--r--schema.sql13
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);
- }
- }
- }
-
diff --git a/index.php b/index.php
index 81fd2ed..15aa896 100644
--- a/index.php
+++ b/index.php
@@ -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;
diff --git a/js/read.js b/js/read.js
index c6da974..61adda0 100644
--- a/js/read.js
+++ b/js/read.js
@@ -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);
});
}
}
diff --git a/read.html b/read.html
index 19b53f0..d64bc3f 100644
--- a/read.html
+++ b/read.html
@@ -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;
}
diff --git a/schema.sql b/schema.sql
index cd9cf95..457b499 100644
--- a/schema.sql
+++ b/schema.sql
@@ -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);
generated by cgit v1.2.3 (git 2.25.1) at 2024-07-03 06:24:47 +0000