Protocolbot
3386ac6542
- Rewrite db.php to use SQLite via PDO (no external DB server needed) - Rewrite schema.sql for SQLite (AUTOINCREMENT, CHECK constraints, triggers for updated_at, ON CONFLICT instead of ON DUPLICATE KEY) - Fix config.php upsert to use ON CONFLICT syntax - Fix index.php: tag filter via json_each, vote upsert via ON CONFLICT, and fix protocol create route (was returning 405 for POST /api/protocols) - Fix yaml.php seed import: replace invalid false callbacks with fn() => null, fix yaml_parse pos argument (-1 -> 0) - Update Dockerfile: drop pdo_mysql/mysqli (pdo_sqlite is built in) - Update CloudronManifest.json: remove mysql addon, bump to v0.3.0 - Update README for SQLite deployment
94 lines
3.9 KiB
SQL
94 lines
3.9 KiB
SQL
-- Protocol Droid — SQLite Schema
|
|
-- Works with SQLite 3.35+ (for ON CONFLICT support)
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username VARCHAR(32) UNIQUE NOT NULL,
|
|
display_name VARCHAR(128),
|
|
bio TEXT,
|
|
password_hash VARCHAR(255), -- bcrypt; NULL if SSO-only
|
|
sso_provider VARCHAR(64), -- 'oidc', 'github', 'google', etc.
|
|
sso_id VARCHAR(255), -- external identity id
|
|
role VARCHAR(10) DEFAULT 'member' CHECK (role IN ('admin','member','viewer')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS protocols (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
slug VARCHAR(128) UNIQUE NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
source VARCHAR(255), -- free text or @username
|
|
source_url VARCHAR(500),
|
|
tags VARCHAR(500), -- JSON array string
|
|
forked_from VARCHAR(128), -- slug of parent protocol
|
|
image VARCHAR(500),
|
|
steps TEXT, -- JSON array of {headline, description}
|
|
outcome TEXT,
|
|
practice TEXT,
|
|
author_id INTEGER REFERENCES users(id),
|
|
visibility VARCHAR(10) DEFAULT 'public' CHECK (visibility IN ('public','private','unlisted')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_protocols_visibility ON protocols (visibility);
|
|
CREATE INDEX IF NOT EXISTS idx_protocols_author ON protocols (author_id);
|
|
CREATE INDEX IF NOT EXISTS idx_protocols_forked ON protocols (forked_from);
|
|
|
|
-- Trigger to update updated_at on row modification
|
|
CREATE TRIGGER IF NOT EXISTS protocols_updated_at
|
|
AFTER UPDATE ON protocols
|
|
BEGIN
|
|
UPDATE protocols SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
|
|
END;
|
|
|
|
CREATE TABLE IF NOT EXISTS collections (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
slug VARCHAR(128) UNIQUE NOT NULL,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
source VARCHAR(255),
|
|
image VARCHAR(500),
|
|
author_id INTEGER REFERENCES users(id),
|
|
visibility VARCHAR(10) DEFAULT 'public' CHECK (visibility IN ('public','private','unlisted')),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_collections_visibility ON collections (visibility);
|
|
CREATE INDEX IF NOT EXISTS idx_collections_author ON collections (author_id);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS collections_updated_at
|
|
AFTER UPDATE ON collections
|
|
BEGIN
|
|
UPDATE collections SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
|
|
END;
|
|
|
|
CREATE TABLE IF NOT EXISTS collection_items (
|
|
collection_id INTEGER NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
|
|
item_type VARCHAR(10) NOT NULL CHECK (item_type IN ('protocol','collection')),
|
|
item_slug VARCHAR(128) NOT NULL,
|
|
position INTEGER DEFAULT 0,
|
|
PRIMARY KEY (collection_id, item_type, item_slug)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS votes (
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
protocol_slug VARCHAR(128) NOT NULL,
|
|
value INTEGER DEFAULT 1, -- +1 upvote, -1 downvote
|
|
comment TEXT, -- optional evaluation text
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (user_id, protocol_slug)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS config (
|
|
key VARCHAR(64) PRIMARY KEY,
|
|
value TEXT
|
|
);
|
|
|
|
-- Default config entries
|
|
INSERT INTO config (key, value) VALUES
|
|
('site_name', 'Protocol Droid'),
|
|
('site_tagline', 'A commons of social protocols'),
|
|
('registration_enabled', 'true'),
|
|
('require_approval', 'false')
|
|
ON CONFLICT(key) DO UPDATE SET value = excluded.value; |