aboutsummaryrefslogtreecommitdiff
path: root/src/Schema.php
diff options
context:
space:
mode:
authorLaria Carolin Chabowski <laria@laria.me>2020-02-07 09:44:59 +0100
committerLaria Carolin Chabowski <laria@laria.me>2020-02-07 09:44:59 +0100
commit2eb5a432d2229788ce2fdb09f36c6f4bebdea813 (patch)
treeab57978bdda34c82b025b897cfb6825b1fd1e654 /src/Schema.php
downloadmicropoly-2eb5a432d2229788ce2fdb09f36c6f4bebdea813.tar.gz
micropoly-2eb5a432d2229788ce2fdb09f36c6f4bebdea813.tar.bz2
micropoly-2eb5a432d2229788ce2fdb09f36c6f4bebdea813.zip
Initial commit
Diffstat (limited to 'src/Schema.php')
-rw-r--r--src/Schema.php80
1 files changed, 80 insertions, 0 deletions
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 @@
+<?php
+
+namespace Micropoly;
+
+use SQLite3;
+
+class Schema
+{
+ private SQLite3 $db;
+
+ /**
+ * @param SQLite3 $db
+ */
+ public function __construct(SQLite3 $db) { $this->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
+ ");
+ }
+}