implement notes

1 files changed, 54 insertions, 0 deletions

diff --git a/migrations/20241208_111936_create_note.php b/migrations/20241208_111936_create_note.php
new file mode 100644
index 0000000..a8471e7
--- /dev/null
+++ b/migrations/20241208_111936_create_note.php
@@ -0,0 +1,54 @@
+<?php
+
+use \WpfTest\Db\Migrator;
+
+Migrator::getInstance()->register(20241208_111936, function (PDO $db) {
+    // create notes and their related tables
+    $db->exec(<<<END
+    CREATE TABLE note (
+        id bigserial primary key not null,
+        uri text unique not null,
+        created timestamp with time zone not null,
+        modified timestamp with time zone,
+        author bigint not null references actor(id),
+        summary text,
+        plain_content text not null,
+        language text not null,
+        in_reply_to text,
+        thread_apex bigint references note(id)
+    );
+    CREATE TABLE note_formatted_content (
+        note_id bigint not null references note(id),
+        mimetype text not null,
+        body text not null,
+        unique(note_id, mimetype)
+    );
+    CREATE TYPE note_privacy_scope AS ENUM (
+        'public', 'followers', 'mutuals', 'none'
+    );
+    CREATE TYPE note_privacy_interactors AS ENUM (
+        'all', 'followers', 'mutuals', 'none'
+    );
+    CREATE TABLE note_privacy (
+        note_id bigint unique not null references note(id),
+        scope note_privacy_scope not null,
+        indexable boolean not null,
+        can_reshare note_privacy_interactors not null default 'all',
+        can_reply note_privacy_interactors not null default 'all',
+        can_other_interact note_privacy_interactors not null default 'all'
+    );
+    CREATE TABLE note_attachment (
+        note_id bigint not null references note(id),
+        index int not null,
+        href text not null,
+        description text,
+        unique(note_id, index)
+    );
+    CREATE TABLE note_extension (
+        note_id bigint not null references note(id),
+        uri text not null,
+        data jsonb not null,
+        unique(note_id, uri)
+    );
+    END);
+});