well-goknown/migrations/000001_create_initial_tables.up.sql

56 lines
1.2 KiB
MySQL
Raw Permalink Normal View History

2024-08-10 23:55:16 +00:00
BEGIN;
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE,
password TEXT NOT NULL,
enabled BOOLEAN DEFAULT false,
admin BOOLEAN DEFAULT false,
pubkey TEXT UNIQUE NOT NULL,
relays TEXT DEFAULT NULL,
create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);
CREATE FUNCTION users_update_ts()
RETURNS TRIGGER AS $$
BEGIN
NEW.update_ts = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_users_update_ts
BEFORE UPDATE
ON
users
FOR EACH ROW
EXECUTE PROCEDURE users_update_ts();
CREATE TABLE IF NOT EXISTS nip05s (
id SERIAL PRIMARY KEY,
owner_id INT NOT NULL,
name TEXT UNIQUE NOT NULL,
domain TEXT NOT NULL,
create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT fk_owner FOREIGN KEY(owner_id) REFERENCES users(id),
CONSTRAINT ck_name CHECK (name ~ '^[a-z0-9]*$')
);
CREATE FUNCTION nip05s_update_ts()
RETURNS TRIGGER AS $$
BEGIN
NEW.update_ts = now();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_nip05s_update_ts
BEFORE UPDATE
ON
nip05s
FOR EACH ROW
EXECUTE PROCEDURE nip05s_update_ts();
COMMIT;