From 2eb5a432d2229788ce2fdb09f36c6f4bebdea813 Mon Sep 17 00:00:00 2001 From: Laria Carolin Chabowski Date: Fri, 7 Feb 2020 09:44:59 +0100 Subject: Initial commit --- src/BoundVal.php | 36 ++++ src/DBError.php | 31 ++++ src/DbQuery.php | 197 +++++++++++++++++++++ src/Entrypoint.php | 10 ++ src/Env.php | 80 +++++++++ src/Esc.php | 21 +++ src/Handler.php | 10 ++ src/Handlers/ApiTagsHandler.php | 16 ++ src/Handlers/Index.php | 20 +++ src/Handlers/JsonAPIHandler.php | 18 ++ src/Handlers/JsonAPIResult.php | 24 +++ src/Handlers/MethodNotAllowedHandler.php | 14 ++ src/Handlers/NewNote.php | 40 +++++ src/Handlers/NotFoundHandler.php | 15 ++ src/Handlers/NoteHandler.php | 39 ++++ src/Handlers/Search.php | 33 ++++ src/Log.php | 27 +++ src/Main.php | 58 ++++++ src/Models/Note.php | 250 ++++++++++++++++++++++++++ src/Models/Tag.php | 37 ++++ src/Schema.php | 80 +++++++++ src/Search/AbstractFTSExpr.php | 31 ++++ src/Search/CharSource.php | 33 ++++ src/Search/FTSExpr.php | 30 ++++ src/Search/FTSLogicOp.php | 46 +++++ src/Search/FTSNotExpr.php | 29 +++ src/Search/LogicOp.php | 78 ++++++++ src/Search/NotOp.php | 32 ++++ src/Search/Pagination.php | 14 ++ src/Search/ParseError.php | 9 + src/Search/Parser.php | 295 +++++++++++++++++++++++++++++++ src/Search/SQLSearchExpr.php | 11 ++ src/Search/SearchExpr.php | 14 ++ src/Search/SearchResult.php | 160 +++++++++++++++++ src/Search/TagExpr.php | 42 +++++ src/Search/TrueExpr.php | 25 +++ src/Tools/PopulateDevDb.php | 71 ++++++++ 37 files changed, 1976 insertions(+) create mode 100644 src/BoundVal.php create mode 100644 src/DBError.php create mode 100644 src/DbQuery.php create mode 100644 src/Entrypoint.php create mode 100644 src/Env.php create mode 100644 src/Esc.php create mode 100644 src/Handler.php create mode 100644 src/Handlers/ApiTagsHandler.php create mode 100644 src/Handlers/Index.php create mode 100644 src/Handlers/JsonAPIHandler.php create mode 100644 src/Handlers/JsonAPIResult.php create mode 100644 src/Handlers/MethodNotAllowedHandler.php create mode 100644 src/Handlers/NewNote.php create mode 100644 src/Handlers/NotFoundHandler.php create mode 100644 src/Handlers/NoteHandler.php create mode 100644 src/Handlers/Search.php create mode 100644 src/Log.php create mode 100644 src/Main.php create mode 100644 src/Models/Note.php create mode 100644 src/Models/Tag.php create mode 100644 src/Schema.php create mode 100644 src/Search/AbstractFTSExpr.php create mode 100644 src/Search/CharSource.php create mode 100644 src/Search/FTSExpr.php create mode 100644 src/Search/FTSLogicOp.php create mode 100644 src/Search/FTSNotExpr.php create mode 100644 src/Search/LogicOp.php create mode 100644 src/Search/NotOp.php create mode 100644 src/Search/Pagination.php create mode 100644 src/Search/ParseError.php create mode 100644 src/Search/Parser.php create mode 100644 src/Search/SQLSearchExpr.php create mode 100644 src/Search/SearchExpr.php create mode 100644 src/Search/SearchResult.php create mode 100644 src/Search/TagExpr.php create mode 100644 src/Search/TrueExpr.php create mode 100644 src/Tools/PopulateDevDb.php (limited to 'src') diff --git a/src/BoundVal.php b/src/BoundVal.php new file mode 100644 index 0000000..7a3560a --- /dev/null +++ b/src/BoundVal.php @@ -0,0 +1,36 @@ +val = $val; + $this->type = $type; + } + + public function getVal() { return $this->val; } + public function getType() { return $this->type; } + + public static function ofInt($val): self { return new self($val, SQLITE3_INTEGER); } + public static function ofFloat($val): self { return new self($val, SQLITE3_FLOAT); } + public static function ofText($val): self { return new self($val, SQLITE3_TEXT); } + public static function ofBlob($val): self { return new self($val, SQLITE3_BLOB); } + public static function ofNull($val): self { return new self($val, SQLITE3_NULL); } + + public function bind(SQLite3Stmt $stmt, $where): void + { + if ($this->type === null) + $stmt->bindValue($where, $this->val); + else + $stmt->bindValue($where, $this->val, $this->type); + } +} \ No newline at end of file diff --git a/src/DBError.php b/src/DBError.php new file mode 100644 index 0000000..4860d2f --- /dev/null +++ b/src/DBError.php @@ -0,0 +1,31 @@ +msg = $msg; + $this->sql = $sql; + + parent::__construct($this->buildMessage()); + } + + private function buildMessage(): string + { + return "{$this->msg}. SQL was: {$this->sql}"; + } +} \ No newline at end of file diff --git a/src/DbQuery.php b/src/DbQuery.php new file mode 100644 index 0000000..6fd32a1 --- /dev/null +++ b/src/DbQuery.php @@ -0,0 +1,197 @@ +query = $query; + } + + /** + * @param int|array $values + * @return string + */ + public static function valueListPlaceholders($values): string + { + if (is_array($values)) + $num = count($values); + elseif (is_int($values)) + $num = $values; + else + throw new InvalidArgumentException("\$values must be an int or an array"); + + return implode(",", array_fill(0, $num, "?")); + } + + public static function insert(SQLite3 $db, string $table, array $fields, array $records) + { + if (empty($records) || empty($fields)) + return; + + $recordTemplate = "(" . implode(",", array_fill(0, count($fields), "?")) . ")"; + $query = new self("INSERT INTO $table (" . implode(',', $fields) . ") VALUES " . implode(",", array_fill(0, count($records), $recordTemplate))); + + $i = 1; + $fieldCount = count($fields); + foreach ($records as $record) { + if (count($record) !== $fieldCount) + throw new InvalidArgumentException("count of all record fields must match field count!"); + + foreach ($record as $v) { + $query->bind($i, $v); + $i++; + } + } + + $query->exec($db); + } + + public static function insertKV(SQLite3 $db, string $table, array $kv) + { + self::insert($db, $table, array_keys($kv), [array_values($kv)]); + } + + /** + * @param mixed $where Name/Index of parameter + * @param BoundVal|mixed $val + * @return $this + */ + public function bind($where, $val): self + { + if (!($val instanceof BoundVal)) + $val = new BoundVal($val, null); + + + $this->boundVals[$where] = $val; + return $this; + } + + private function bindMulti(array $vals, $type, int $offset): self + { + foreach ($vals as $i => $v) + $this->bind($i + $offset, new BoundVal($v, $type)); + + return $this; + } + + public function bindMultiAuto(array $vals, int $offset = 1): self { return $this->bindMulti($vals, null, $offset); } + public function bindMultiInt(array $vals, int $offset = 1): self { return $this->bindMulti($vals, SQLITE3_INTEGER, $offset); } + public function bindMultiFloat(array $vals, int $offset = 1): self { return $this->bindMulti($vals, SQLITE3_FLOAT, $offset); } + public function bindMultiText(array $vals, int $offset = 1): self { return $this->bindMulti($vals, SQLITE3_TEXT, $offset); } + public function bindMultiBlob(array $vals, int $offset = 1): self { return $this->bindMulti($vals, SQLITE3_BLOB, $offset); } + public function bindMultiNull(array $vals, int $offset = 1): self { return $this->bindMulti($vals, SQLITE3_NULL, $offset); } + + /** + * @param SQLite3 $db + * @param callable|null $cb + * @return mixed Result of callback or null, if none given + * @throws DBError + */ + public function exec(SQLite3 $db, ?callable $cb = null) + { + $stmt = $db->prepare($this->query); + if ($stmt === false) + throw new DBError("Prepare failed", $this->query); + foreach ($this->boundVals as $where => $boundVal) + $boundVal->bind($stmt, $where); + + $res = $stmt->execute(); + if ($res === false) { + throw new DBError("execute failed", $this->query); + } + + $out = $cb ? $cb($res) : null; + + $res->finalize(); + $stmt->close(); + + return $out; + } + + public function fetchRow(SQLite3 $db, int $fetchMode = SQLITE3_NUM): ?array + { + return $this->exec($db, static function (SQLite3Result $res) use ($fetchMode) { + return $res->numColumns() ? $res->fetchArray($fetchMode) : null; + }); + } + + public function fetchRowAssoc(SQLite3 $db): ?array { return $this->fetchRow($db, SQLITE3_ASSOC); } + + public function fetchRows(SQLite3 $db, int $fetchMode = SQLITE3_NUM): array + { + return $this->exec($db, static function (SQLite3Result $res) use ($fetchMode) { + if (!$res->numColumns()) + return []; + + $out = []; + + while (($row = $res->fetchArray($fetchMode))) + $out[] = $row; + + return $out; + }); + } + + public function fetchRowsAssoc(SQLite3 $db): array { return $this->fetchRows($db, SQLITE3_ASSOC); } + + public function fetchIndexedRows(SQLite3 $db, ...$keys): array + { + return $this->exec($db, static function (SQLite3Result $res) use ($keys) { + if (!$res->numColumns()) + return []; + + $out = []; + + while (($row = $res->fetchArray(SQLITE3_ASSOC))) { + $cursor =& $out; + + foreach ($keys as $k) + $cursor =& $cursor[$row[$k]]; + + $cursor = $row; + } + + return $out; + }); + } + + public function fetchIndexedValues(SQLite3 $db, $val, ...$keys): array + { + return array_map(fn ($row) => $row[$val] ?? null, $this->fetchIndexedRows($db, ...$keys)); + } + + public function fetchIndexedAllRows(SQLite3 $db, ...$keys): array + { + return $this->exec($db, static function (SQLite3Result $res) use ($keys) { + if (!$res->numColumns()) + return []; + + $out = []; + + while (($row = $res->fetchArray(SQLITE3_ASSOC))) { + $cursor =& $out; + + foreach ($keys as $k) + $cursor =& $cursor[$row[$k]]; + + $cursor[] = $row; + } + + return $out; + }); + } +} \ No newline at end of file diff --git a/src/Entrypoint.php b/src/Entrypoint.php new file mode 100644 index 0000000..6fe3409 --- /dev/null +++ b/src/Entrypoint.php @@ -0,0 +1,10 @@ +lazyLoaded[$ident])) { + $this->lazyLoaded[$ident] = $callback(); + } + return $this->lazyLoaded[$ident]; + } + + public static function fromConfig(array $config) + { + $env = new self; + $env->config = $config; + return $env; + } + + public function documentRoot(): string { return "/"; } + + public function twig(): Environment + { + return $this->lazy("twig", function () { + $loader = new FilesystemLoader($this->config["templates_path"]); + $env = new Environment($loader, [ + "cache" => $this->config["templates_cache"], + ]); + + $env->addFunction(new TwigFunction("url", function (string $url, ...$args) { + return $this->documentRoot() . sprintf($url, ...$args); + }, ["is_variadic" => true])); + + $env->addFilter(new TwigFilter("search_escape", static function (string $s) { + $s = str_replace("\\", "\\\\", $s); + $s = str_replace("#", "\\#", $s); + $s = str_replace(" ", "\\ ", $s); + $s = str_replace("\t", "\\\t", $s); + $s = str_replace("(", "\\(", $s); + $s = str_replace(")", "\\)", $s); + return $s; + })); + + return $env; + }); + } + + public function rawDbCon(): SQLite3 + { + return $this->lazy("rawDbCon", function () { + return new SQLite3($this->config["sqlitedb"]); + }); + } + + public function db(): SQLite3 + { + return $this->lazy("db", function () { + $db = $this->rawDbCon(); + $db->exec("PRAGMA foreign_keys = ON"); + + (new Schema($db))->migrate(); + + return $db; + }); + } +} diff --git a/src/Esc.php b/src/Esc.php new file mode 100644 index 0000000..5c290b6 --- /dev/null +++ b/src/Esc.php @@ -0,0 +1,21 @@ +db()))); + } +} \ No newline at end of file diff --git a/src/Handlers/Index.php b/src/Handlers/Index.php new file mode 100644 index 0000000..8d0896b --- /dev/null +++ b/src/Handlers/Index.php @@ -0,0 +1,20 @@ +twig()->render("/index.twig", [ + "title" => "hello", + "msg" => "Johoo ", + "tagcloud" => Tag::calcTagCloud(Tag::getTagCounts($env->db())), + ]); + } +} diff --git a/src/Handlers/JsonAPIHandler.php b/src/Handlers/JsonAPIHandler.php new file mode 100644 index 0000000..cc6aa61 --- /dev/null +++ b/src/Handlers/JsonAPIHandler.php @@ -0,0 +1,18 @@ +handleAPIRequest($env, $variables)->send(); + } +} \ No newline at end of file diff --git a/src/Handlers/JsonAPIResult.php b/src/Handlers/JsonAPIResult.php new file mode 100644 index 0000000..905599c --- /dev/null +++ b/src/Handlers/JsonAPIResult.php @@ -0,0 +1,24 @@ +data = $data; + $this->statuscode = $statuscode; + } + + public function send(): void + { + http_response_code($this->statuscode); + header("Content-Type: application/json; charset=UTF-8"); + echo json_encode($this->data); + } +} \ No newline at end of file diff --git a/src/Handlers/MethodNotAllowedHandler.php b/src/Handlers/MethodNotAllowedHandler.php new file mode 100644 index 0000000..53ddb0e --- /dev/null +++ b/src/Handlers/MethodNotAllowedHandler.php @@ -0,0 +1,14 @@ +setContent($content); + $note->setTags($_POST["tag"]); + $note->save($env->db()); + + $url = $env->documentRoot() . "n/" . $note->getId(); + http_response_code(303); + header("Location: {$url}"); + echo 'Note created: '; + } + + echo $env->twig()->render("/new_note.twig", []); + } +} \ No newline at end of file diff --git a/src/Handlers/NotFoundHandler.php b/src/Handlers/NotFoundHandler.php new file mode 100644 index 0000000..1827995 --- /dev/null +++ b/src/Handlers/NotFoundHandler.php @@ -0,0 +1,15 @@ +db(); + + $note = Note::byId($db, $variables["id"]); + if ($note === null) { + (new NotFoundHandler())->handle($env, []); + return; + } + + if ($_SERVER["REQUEST_METHOD"] === "POST") { + if ($_POST["delete"] === "delete") { + $note->delete($db); + http_response_code(303); + $url = $env->documentRoot(); + header("Location: {$url}"); + return; + } + + $note->setContent($_POST["content"]); + $note->setTags($_POST["tag"]); + $note->save($db); + } + + echo $env->twig()->render("/note.twig", ["note" => $note]); + } +} \ No newline at end of file diff --git a/src/Handlers/Search.php b/src/Handlers/Search.php new file mode 100644 index 0000000..30311ea --- /dev/null +++ b/src/Handlers/Search.php @@ -0,0 +1,33 @@ + $_GET["q"] ?? ""]; + + try { + $expr = isset($_GET["q"]) + ? (Parser::parse($_GET["q"]) ?? new TrueExpr()) + : new TrueExpr(); + + $results = SearchResult::search($env->db(), $expr); + $vars["results"] = $results; + } catch (ParseError $e) { + $vars["error"] = $e->getMessage(); + } + + echo $env->twig()->render("/search.twig", $vars); + } +} \ No newline at end of file diff --git a/src/Log.php b/src/Log.php new file mode 100644 index 0000000..fce455f --- /dev/null +++ b/src/Log.php @@ -0,0 +1,27 @@ +pushHandler(new ErrorLogHandler()); + return $logger; + } +} \ No newline at end of file diff --git a/src/Main.php b/src/Main.php new file mode 100644 index 0000000..443960b --- /dev/null +++ b/src/Main.php @@ -0,0 +1,58 @@ +addRoute(["GET"], "/", Index::class); + $r->addRoute(["GET", "POST"], "/new-note", NewNote::class); + $r->addRoute(["GET"], "/search", Search::class); + $r->addRoute(["GET", "POST"], "/n/{id}", NoteHandler::class); + $r->addRoute(["GET"], "/api/tags", ApiTagsHandler::class); + } + + public function run(Env $env) + { + $disp = simpleDispatcher(Closure::fromCallable([self::class, "buildRoutes"])); + + $uri = preg_replace('/\?.*$/', "", $_SERVER["REQUEST_URI"]); + $result = $disp->dispatch($_SERVER["REQUEST_METHOD"], $uri); + switch ($result[0]) { + case Dispatcher::NOT_FOUND: + $handlerCls = NotFoundHandler::class; + $vars = []; + break; + case Dispatcher::FOUND: + [, $handlerCls, $vars] = $result; + break; + case Dispatcher::METHOD_NOT_ALLOWED: + $handlerCls = MethodNotAllowedHandler::class; + $vars = ["allowed" => $result[1]]; + break; + default: + throw new \DomainException("Unexpected routing result: {$result[0]}"); + } + + $handler = new $handlerCls(); + if (!($handler instanceof Handler)) { + throw new \DomainException("handler is not an instance of ".Handler::class); + } + + $handler->handle($env, $vars); + } +} diff --git a/src/Models/Note.php b/src/Models/Note.php new file mode 100644 index 0000000..901f5aa --- /dev/null +++ b/src/Models/Note.php @@ -0,0 +1,250 @@ +id = uniqid("", true); + } + + /** + * @param SQLite3 $db + * @param DbQuery $query + * @return self[] + */ + private static function fromQuery(SQLite3 $db, DbQuery $query): array + { + $out = []; + + foreach ($query->fetchRowsAssoc($db) as $row) { + $note = new self(); + + $note->savedToDb = true; + $note->id = $row["id"]; + $note->content = $row["content"]; + $note->trash = (bool)(int)$row["trash"]; + + $out[$row["id"]] = $note; + } + + if (!empty($out)) { + $q = (new DbQuery("SELECT tag, note_id FROM tags WHERE note_id IN (" . DbQuery::valueListPlaceholders($out) . ")")) + ->bindMultiText(array_keys($out)); + + foreach ($q->fetchRows($db) as [$tag, $id]) { + $out[$id]->tags[] = $tag; + } + } + + return $out; + } + + /** + * @param SQLite3 $db + * @param array $ids + * @return self[] indexes by id + */ + public static function byIds(SQLite3 $db, array $ids): array + { + if (empty($ids)) + return []; + + $query = (new DbQuery(" + SELECT note.id, content.content, note.trash + FROM notes note + INNER JOIN note_contents content + ON content.rowid = note.content_row + WHERE id IN (" . DbQuery::valueListPlaceholders($ids) . ") + "))->bindMultiText($ids); + + return self::fromQuery($db, $query); + } + + public static function byId(SQLite3 $db, string $id): ?self + { + return self::byIds($db, [$id])[$id] ?? null; + } + + /** + * @return string + */ + public function getId(): string + { + return $this->id; + } + + /** + * @return string + */ + public function getContent(): string + { + return $this->content; + } + + /** + * @param string $content + * @return Note + */ + public function setContent(string $content): Note + { + $this->content = $content; + return $this; + } + + /** + * @return array + */ + public function getTags(): array + { + return $this->tags; + } + + /** + * @param array $tags + * @return Note + */ + public function setTags(array $tags): Note + { + $tags = array_map("trim", $tags); + $tags = array_filter($tags); + $tags = array_unique($tags); + + $this->tags = $tags; + return $this; + } + + /** + * @return bool + */ + public function isTrash(): bool + { + return $this->trash; + } + + /** + * @param bool $trash + * @return Note + */ + public function setTrash(bool $trash): Note + { + $this->trash = $trash; + return $this; + } + + private function deleteContent(SQLite3 $db) + { + (new DbQuery("DELETE FROM note_contents WHERE rowid IN (SELECT content_row FROM notes WHERE id = ?)")) + ->bind(1, BoundVal::ofText($this->id)) + ->exec($db); + } + + private function deleteTags(SQLite3 $db) + { + (new DbQuery("DELETE FROM tags WHERE note_id = ?")) + ->bind(1, BoundVal::ofText($this->id)) + ->exec($db); + } + + public function save(SQLite3 $db) + { + if ($this->savedToDb) + $this->update($db); + else + $this->insert($db); + } + + private function insert(SQLite3 $db) + { + $db->exec("BEGIN"); + + $this->deleteContent($db); + + DbQuery::insertKV($db, "note_contents", ["content" => BoundVal::ofText($this->content)]); + $rowid = (new DbQuery("SELECT last_insert_rowid()"))->fetchRow($db)[0]; + + DbQuery::insertKV($db, "notes", [ + "id" => BoundVal::ofText($this->id), + "content_row" => BoundVal::ofInt($rowid), + "trash" => BoundVal::ofInt($this->trash ? 0 : 1), + ]); + + $this->writeTags($db); + + $db->exec("COMMIT"); + } + + private function update(SQLite3 $db) + { + $db->exec("BEGIN"); + + $this->deleteTags($db); + + (new DbQuery(" + UPDATE note_contents + SET content = :content + WHERE rowid = ( + SELECT content_row + FROM notes + WHERE id = :id + ) + ")) + ->bind("content", BoundVal::ofText($this->content)) + ->bind("id", BoundVal::ofText($this->id)) + ->exec($db); + + $this->writeTags($db); + + (new DbQuery(" + UPDATE notes + SET changed_at = CURRENT_TIMESTAMP, + trash = :trash + WHERE id = :id + ")) + ->bind("id", BoundVal::ofText($this->id)) + ->bind("trash", BoundVal::ofInt($this->trash ? 0 : 1)) + ->exec($db); + + $db->exec("COMMIT"); + } + + /** + * @param SQLite3 $db + */ + private function writeTags(SQLite3 $db): void + { + $this->deleteTags($db); + DbQuery::insert($db, + "tags", + ["note_id", "tag"], + array_map(fn($t) => [BoundVal::ofText($this->id), BoundVal::ofText($t)], $this->tags) + ); + } + + public function delete(SQLite3 $db): void + { + $this->deleteTags($db); + $this->deleteContent($db); + (new DbQuery("DELETE FROM notes WHERE id = ?")) + ->bind(1, BoundVal::ofText($this->id)) + ->exec($db); + $this->savedToDb = false; + } +} \ No newline at end of file diff --git a/src/Models/Tag.php b/src/Models/Tag.php new file mode 100644 index 0000000..b119fe8 --- /dev/null +++ b/src/Models/Tag.php @@ -0,0 +1,37 @@ + int count] + * @return array + */ + public static function calcTagCloud(array $tagCounts): array + { + $tagCounts = array_map("intval", $tagCounts); + $tagCounts = array_filter($tagCounts, fn ($count) => $count !== 0); + + if (empty($tagCounts)) + return []; + + $maxCount = max(array_values($tagCounts)); + $tagCounts = array_map(fn ($count) => floor($count / ($maxCount+1) * self::TAGCLOUD_MAGNITUDES) + 1, $tagCounts); + ksort($tagCounts); + return $tagCounts; + } + + public static function getTagCounts(\SQLite3 $db): array + { + return (new DbQuery("SELECT tag, num FROM tagcloud")) + ->fetchIndexedValues($db, "num", "tag"); + } +} \ No newline at end of file diff --git a/src/Schema.php b/src/Schema.php new file mode 100644 index 0000000..bbb47de --- /dev/null +++ b/src/Schema.php @@ -0,0 +1,80 @@ +db = $db; } + + private function getSchemaVersion(): int + { + $n = $this->db->querySingle("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'schema_meta'"); + if ($n !== "schema_meta") + return 0; + + return (int)$this->db->querySingle("SELECT value FROM schema_meta WHERE key = 'version'"); + } + + private function setSchemaVersion(int $v): void + { + (new DbQuery("REPLACE INTO schema_meta (key, value) VALUES ('version', :v)")) + ->bind(":v", $v) + ->exec($this->db); + } + + public function migrate() + { + $version = $this->getSchemaVersion(); + + switch ($version) { + case 0: + $this->v1(); + $this->setSchemaVersion(1); + } + } + + private function v1() + { + $this->db->exec(" + CREATE TABLE schema_meta ( + key VARCHAR(100) NOT NULL PRIMARY KEY, + value + ) WITHOUT ROWID + "); + $this->db->exec(" + CREATE VIRTUAL TABLE note_contents USING fts4 (content TEXT) + "); + $this->db->exec(" + CREATE TABLE notes ( + id VARCHAR(23) NOT NULL PRIMARY KEY, + content_row INT NOT NULL, + created_at BIGINT NOT NULL DEFAULT CURRENT_TIMESTAMP, + changed_at BIGINT NOT NULL DEFAULT CURRENT_TIMESTAMP, + trash INT NOT NULL DEFAULT 0 + ) WITHOUT ROWID + "); + $this->db->exec(" + CREATE TABLE tags ( + note_id VARCHAR(23) NOT NULL REFERENCES notes (id) ON UPDATE CASCADE ON DELETE CASCADE, + tag TEXT NOT NULL, + PRIMARY KEY (note_id, tag) + ) WITHOUT ROWID + "); + $this->db->exec("CREATE INDEX tag ON tags (tag)"); + $this->db->exec(" + CREATE VIEW tagcloud AS + SELECT + tag, + COUNT(*) AS num + FROM tags + GROUP BY tag + "); + } +} diff --git a/src/Search/AbstractFTSExpr.php b/src/Search/AbstractFTSExpr.php new file mode 100644 index 0000000..b72b1b6 --- /dev/null +++ b/src/Search/AbstractFTSExpr.php @@ -0,0 +1,31 @@ +sql = $singleFTS + ? "nc.note_contents MATCH :{$bindPrefix}match" + : "n.content_row IN ( + SELECT rowid + FROM note_contents + WHERE note_contents MATCH :{$bindPrefix}match + )"; + $sqlex->bindings["{$bindPrefix}match"] = $this->fts4Query(); + + return $sqlex; + } + + public function countFTSQueries(): int + { + return 1; + } +} \ No newline at end of file diff --git a/src/Search/CharSource.php b/src/Search/CharSource.php new file mode 100644 index 0000000..165e538 --- /dev/null +++ b/src/Search/CharSource.php @@ -0,0 +1,33 @@ +s = $s; + $this->len = mb_strlen($s); + } + + public function getNext(): ?string + { + if ($this->i >= $this->len) + return null; + + $c = mb_substr($this->s, $this->i, 1); + $this->i++; + return $c; + } + + public function unget(): void + { + $this->i = max(0, $this->i - 1); + } +} \ No newline at end of file diff --git a/src/Search/FTSExpr.php b/src/Search/FTSExpr.php new file mode 100644 index 0000000..1123cf3 --- /dev/null +++ b/src/Search/FTSExpr.php @@ -0,0 +1,30 @@ +term = $term; + } + + public function getTerm(): string + { + return $this->term; + } + + protected function fts4Query(): string + { + return '"' . str_replace('"', '""', $this->term) . '"'; + } + + public function toString(): string + { + return '"' . preg_replace_callback('/(["\\\\])/', fn($s) => "\\$s", $this->term) . '"'; + } +} \ No newline at end of file diff --git a/src/Search/FTSLogicOp.php b/src/Search/FTSLogicOp.php new file mode 100644 index 0000000..452f63b --- /dev/null +++ b/src/Search/FTSLogicOp.php @@ -0,0 +1,46 @@ +op = $op; + $this->a = $a; + $this->b = $b; + } + + private const FTSOPS = [ + LogicOp::OP_AND => "", + LogicOp::OP_OR => "OR", + ]; + + protected function fts4Query(): string + { + $ftsop = self::FTSOPS[$this->op]; + assert($ftsop); + + return "({$this->a->fts4Query()} {$ftsop} {$this->b->fts4Query()})"; + } + + public function toString(): string + { + return "({$this->a->toString()} FTS-{$this->op} {$this->b->toString()})"; + } +} \ No newline at end of file diff --git a/src/Search/FTSNotExpr.php b/src/Search/FTSNotExpr.php new file mode 100644 index 0000000..a4aa219 --- /dev/null +++ b/src/Search/FTSNotExpr.php @@ -0,0 +1,29 @@ +expr = $expr; + } + + protected function fts4Query(): string + { + return "-{$this->expr->fts4Query()}"; + } + + public function toString(): string + { + return "(FTS-NOT {$this->expr->toString()})"; + } +} \ No newline at end of file diff --git a/src/Search/LogicOp.php b/src/Search/LogicOp.php new file mode 100644 index 0000000..85fb8fa --- /dev/null +++ b/src/Search/LogicOp.php @@ -0,0 +1,78 @@ + "AND", + self::OP_OR => "OR", + ]; + + private string $op; + private SearchExpr $a; + private SearchExpr $b; + + public function __construct(string $op, SearchExpr $a, SearchExpr $b) + { + if (!self::checkOp($op)) + throw new \DomainException("{$op} is not a valid operator"); + + $this->op = $op; + $this->a = $a; + $this->b = $b; + } + + public static function build(string $op, SearchExpr $a, SearchExpr $b): SearchExpr + { + return $a instanceof AbstractFTSExpr && $b instanceof AbstractFTSExpr + ? new FTSLogicOp($op, $a, $b) + : new self($op, $a, $b); + } + + /** + * @param string $op + * @return bool + */ + public static function checkOp(string $op): bool + { + return in_array($op, [ + self::OP_AND, + self::OP_OR, + ]); + } + + public function getA(): SearchExpr { return $this->a; } + public function getB(): SearchExpr { return $this->b; } + public function getOp(): string { return $this->op; } + + public function toString(): string + { + return "({$this->a->toString()}) {$this->op} ({$this->b->toString()})"; + } + + public function toSQL($bindPrefix, bool $singleFTS): SQLSearchExpr + { + $sqlex = new SQLSearchExpr(); + + $a = $this->a->toSQL("a_$bindPrefix", $singleFTS); + $b = $this->b->toSQL("b_$bindPrefix", $singleFTS); + $sqlop = self::SQLOPS[$this->op]; + assert($sqlop); + + $sqlex->sql = "(({$a->sql}) {$sqlop} ({$b->sql}))"; + $sqlex->bindings = array_merge($a->bindings, $b->bindings); + + return $sqlex; + } + + public function countFTSQueries(): int + { + return $this->a->countFTSQueries() + $this->b->countFTSQueries(); + } +} \ No newline at end of file diff --git a/src/Search/NotOp.php b/src/Search/NotOp.php new file mode 100644 index 0000000..35fcf1e --- /dev/null +++ b/src/Search/NotOp.php @@ -0,0 +1,32 @@ +expr = $expr; + } + + public function toString(): string + { + return "not ({$this->expr->toString()})"; + } + + public function toSQL(string $bindPrefix, bool $singleFTS): SQLSearchExpr + { + $sqlex = $this->expr->toSQL($bindPrefix, $singleFTS); + $sqlex->sql = "(NOT ({$sqlex->sql}))"; + return $sqlex; + } + + public function countFTSQueries(): int + { + return $this->expr->countFTSQueries(); + } +} \ No newline at end of file diff --git a/src/Search/Pagination.php b/src/Search/Pagination.php new file mode 100644 index 0000000..b4b2447 --- /dev/null +++ b/src/Search/Pagination.php @@ -0,0 +1,14 @@ +valid()) + return null; + $out = $input->current(); + $input->next(); + return $out; + } + + /** + * @return Iterator + * @throws ParseError + */ + private static function tokenize_normal(CharSource $input): Iterator + { + $buf = ""; + + $yieldBufAndClear = function () use (&$buf) { + if ($buf !== "") { + switch ($buf) { + case "and": + case "or": + case "not": + yield [self::TOK_OP, $buf]; + break; + default: + yield [self::TOK_WORD, $buf]; + } + } + $buf = ""; + }; + + for (;;) { + $c = $input->getNext(); + if ($c === null) { + break; + } + + switch ($c) { + case '\\': + $next = $input->getNext(); + if ($next === null) { + $buf .= $c; + break 2; + } + $buf .= $next; + break; + + case ' ': + case "\t": + yield from $yieldBufAndClear(); + break; + + case '"': + yield from $yieldBufAndClear(); + yield from self::tokenize_string($input); + break; + + case ':': + if ($buf !== "") { + yield [self::TOK_PROP, $buf]; + $buf = ""; + } + break; + + case '(': + yield from $yieldBufAndClear(); + yield [self::TOK_PAROPEN, null]; + break; + + case ')': + yield from $yieldBufAndClear(); + yield [self::TOK_PARCLOSE, null]; + break; + + case '#': + yield from $yieldBufAndClear(); + yield from self::tokenize_tag($input); + break; + + default: + $buf .= $c; + } + } + + yield from $yieldBufAndClear(); + return; + } + + /** + * @param string $input + * @return SearchExpr|null + * @throws ParseError + */ + public static function parse(string $input): ?SearchExpr + { + $tokens = self::tokenize($input); + + $stack = []; + $cur = null; + $binOp = null; + $negated = false; + + $putExpr = function (SearchExpr $expr) use (&$cur, &$binOp, &$negated) { + if ($negated) { + $expr = new NotOp($expr); + } + + $cur = $cur === null + ? $expr + : LogicOp::build($binOp ?? LogicOp::OP_AND, $cur, $expr); + + $binOp = null; + $negated = false; + }; + + $setBinOp = function ($op) use (&$binOp) { + if ($binOp !== null) + throw new ParseError("Unexpected logic operator $op"); + + $binOp = $op; + }; + + for (;;) { + $token = self::getItemAndAdvance($tokens); + if ($token === null) + break; + + [$ttyp, $tdata] = $token; + + switch ($ttyp) { + + case self::TOK_TAG: + $putExpr(new TagExpr($tdata)); + break; + case self::TOK_OP: + switch ($tdata) { + case "and": + $setBinOp(LogicOp::OP_AND); + break; + case "or": + $setBinOp(LogicOp::OP_OR); + break; + case "not": + $negated = !$negated; + break; + default: + throw new \DomainException("Unexpected data for TOK_OP: $tdata"); + } + break; + case self::TOK_WORD: + $putExpr(new FTSExpr($tdata)); + break; + case self::TOK_PROP: + // TODO(laria): Implement this + throw new ParseError("Not yet supported"); + case self::TOK_PAROPEN: + $stack[] = [$cur, $binOp, $negated]; + $cur = $binOp = $negated = null; + break; + case self::TOK_PARCLOSE: + if (empty($stack)) + throw new ParseError("Unexpected closing parenthesis"); + + $parContent = $cur; + [$cur, $binOp, $negated] = array_pop($stack); + $putExpr($parContent); + break; + } + } + + if (!empty($stack)) + throw new ParseError("Unclosed parenthesis"); + + return $cur; + } + + /** + * @param CharSource $input + * @return Generator + * @throws ParseError + */ + private static function tokenize_string(CharSource $input): Generator + { + $content = ""; + for (;;) { + $c = $input->getNext(); + if ($c === null) + throw new ParseError("Unclosed string encountered"); + + switch ($c) { + case '\\': + $next = $input->getNext(); + if ($next === null) + throw new ParseError("Unclosed string encountered"); + + $content .= $next; + break; + + case '"': + yield [self::TOK_WORD, $content]; + return; + + default: + $content .= $c; + } + } + } + + /** + * @param CharSource $input + * @return Iterator + */ + private static function tokenize_tag(CharSource $input): Iterator + { + $tag = ""; + + $yieldTag = function () use (&$tag) { + if ($tag === "") + yield [self::TOK_WORD, "#"]; + else + yield [self::TOK_TAG, $tag]; + }; + + for (;;) { + $c = $input->getNext(); + if ($c === null) { + yield from $yieldTag(); + return; + } + + switch ($c) { + case '\\': + $next = $input->getNext(); + if ($c === null) { + $tag .= '\\'; + yield [self::TOK_TAG, $tag]; + return; + } + $tag .= $next; + break; + + case ' ': + case "\t": + yield from $yieldTag(); + return; + + case '(': + case ')': + case '#': + $input->unget(); + yield from $yieldTag(); + return; + + default: + $tag .= $c; + } + } + } +} \ No newline at end of file diff --git a/src/Search/SQLSearchExpr.php b/src/Search/SQLSearchExpr.php new file mode 100644 index 0000000..76306ce --- /dev/null +++ b/src/Search/SQLSearchExpr.php @@ -0,0 +1,11 @@ +note = $note; + $this->highlights = $highlights; + } + + /** + * @param SQLite3 $db + * @param SearchExpr $expr + * @return self[] + */ + public static function search(SQLite3 $db, SearchExpr $expr): array + { + return $expr->countFTSQueries() === 1 + ? self::searchFTS($db, $expr) + : self::searchComplex($db, $expr); + } + + private static function searchComplex(SQLite3 $db, SearchExpr $expr): array + { + $sqlSearchExpr = $expr->toSQL("", false); + + $query = new DbQuery(" + SELECT + n.id + FROM notes n + INNER JOIN note_contents nc + ON nc.rowid = n.content_row + WHERE {$sqlSearchExpr->sql} + "); + + foreach ($sqlSearchExpr->bindings as $k => $v) + $query->bind($k, $v); + + $ids = array_map(fn ($row) => $row[0], $query->fetchRows($db)); + $notes = Note::byIds($db, $ids); + return array_map(fn ($note) => new self($note, []), $notes); + } + + private static function highlightRangeContains(array $range, int $point): bool + { + [$start, $end] = $range; + return $start <= $point && $point <= $end; + } + + private static function areHighlightsOverlapping(array $a, array $b): bool + { + [$aStart, $aEnd] = $a; + [$bStart, $bEnd] = $b; + + return self::highlightRangeContains($a, $bStart) + || self::highlightRangeContains($a, $bEnd) + || self::highlightRangeContains($b, $aStart) + || self::highlightRangeContains($b, $aEnd); + } + + private static function parseOffsetsToHighlights(string $offsets): array + { + $offsets = explode(" ", $offsets); + $offsets = array_map("intval", $offsets); + + $phraseMatches = count($offsets) / 4; + + $highlights = []; + for ($i = 0; $i < $phraseMatches; $i++) { + $off = $offsets[$i * 4 + 2]; + $len = $offsets[$i * 4 + 3]; + + if ($off < 0 || $len === 0) + continue; + + $highlights[] = [$off, $off+$len-1]; + } + + usort($highlights, fn ($a, $b) => ($a[0] <=> $b[0]) ?: ($b[1] <=> $a[1])); + + // merge overlapping areas + for ($i = count($highlights)-1; $i >= 0; $i--) { + for ($j = $i-1; $j >= 0; $j--) { + if (self::areHighlightsOverlapping($highlights[$i], $highlights[$j])) { + [$iStart, $iEnd] = $highlights[$i]; + [$jStart, $jEnd] = $highlights[$j]; + + $highlights[$j] = [min($iStart, $jStart), max($iEnd, $jEnd)]; + unset($highlights[$i]); + break; + } + } + } + + return array_merge($highlights); // array_merge here renumbers the keys + } + + private static function searchFTS(SQLite3 $db, SearchExpr $expr) + { + $sqlSearchExpr = $expr->toSQL("", true); + $query = new DbQuery(" + SELECT + n.id, + offsets(nc.note_contents) AS offsets + FROM notes n + INNER JOIN note_contents nc + ON nc.rowid = n.content_row + WHERE {$sqlSearchExpr->sql} + "); + foreach ($sqlSearchExpr->bindings as $k => $v) + $query->bind($k, $v); + + + $offsets = $query->fetchIndexedValues($db, "offsets", "id"); + + $notes = Note::byIds($db, array_keys($offsets)); + + $out = []; + foreach ($offsets as $id => $offString) { + if (!isset($notes[$id])) + throw new LogicException("Note '{$id}' not loaded but found?"); + + $out[] = new self($notes[$id], self::parseOffsetsToHighlights($offString)); + } + + return $out; + } + + public function renderHighlightedContent(): string + { + $out = ""; + $content = $this->note->getContent(); + $lastOff = 0; + foreach ($this->highlights as [$start, $end]) { + $out .= Esc::e(substr($content, $lastOff, $start - $lastOff), Esc::HTML_WITH_BR); + $out .= '' . Esc::e(substr($content, $start, $end - $start + 1), Esc::HTML_WITH_BR) . ''; + + $lastOff = $end + 1; + } + + $out .= Esc::e(substr($content, $lastOff), Esc::HTML_WITH_BR); + + return $out; + } + + public function getNote(): Note { return $this->note; } +} \ No newline at end of file diff --git a/src/Search/TagExpr.php b/src/Search/TagExpr.php new file mode 100644 index 0000000..b117bbe --- /dev/null +++ b/src/Search/TagExpr.php @@ -0,0 +1,42 @@ +tag = $tag; + } + + public function getTag(): string { return $this->tag; } + + public function toString(): string + { + return "#{$this->tag}"; + } + + public function toSQL(string $bindPrefix, bool $singleFTS): SQLSearchExpr + { + $sqlex = new SQLSearchExpr(); + + $sqlex->sql = "EXISTS ( + SELECT 1 + FROM tags t + WHERE t.tag = :{$bindPrefix}tag + AND t.note_id = n.id + )"; + $sqlex->bindings["{$bindPrefix}tag"] = $this->tag; + + return $sqlex; + } + + public function countFTSQueries(): int + { + return 0; + } +} \ No newline at end of file diff --git a/src/Search/TrueExpr.php b/src/Search/TrueExpr.php new file mode 100644 index 0000000..5f25c7e --- /dev/null +++ b/src/Search/TrueExpr.php @@ -0,0 +1,25 @@ +"; + } + + public function toSQL(string $bindPrefix, bool $singleFTS): SQLSearchExpr + { + $sqlSearchExpr = new SQLSearchExpr(); + $sqlSearchExpr->sql = "1"; + return $sqlSearchExpr; + } + + public function countFTSQueries(): int + { + return 0; + } +} \ No newline at end of file diff --git a/src/Tools/PopulateDevDb.php b/src/Tools/PopulateDevDb.php new file mode 100644 index 0000000..8f1b2b9 --- /dev/null +++ b/src/Tools/PopulateDevDb.php @@ -0,0 +1,71 @@ +words = $words; + } + + public function run(Env $env) + { + $this->readWords(); + + $db = $env->db(); + for ($i = 0; $i < self::NUM_NOTES; $i++) + $this->createTestNote($db); + } + + private function randomWords(int $min, int $max): array + { + $words = []; + $num = mt_rand($min, $max); + for ($i = 0; $i < $num; $i++) + $words[] = $this->words[mt_rand(0, count($this->words)-1)]; + + return $words; + } + + private static function byChance(float $chance): bool + { + return mt_rand() / mt_getrandmax() <= $chance; + } + + private function createTestNote(SQLite3 $db): void + { + $note = new Note(); + $tags = $this->randomWords(self::TAGS_MIN_RAND, self::TAGS_MAX_RAND); + if (self::byChance(self::CHANCE_INBOX)) + $tags[] = "inbox"; + $note->setTags($tags); + $note->setContent(implode(" ", $this->randomWords(self::CONTENT_MIN_WORDS, self::CONTENT_MAX_WORDS))); + $note->setTrash(self::byChance(self::CHANCE_TRASH)); + + $note->save($db); + } +} \ No newline at end of file -- cgit v1.2.3-70-g09d2