56 lines
1.2 KiB
MySQL
56 lines
1.2 KiB
MySQL
|
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;
|