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; } /** * @param SQLite3 $db * @return Attachment[] */ public function getAttachments(SQLite3 $db): array { return Attachment::byNoteId($db, $this->id); } }