35 lines
897 B
SQL
35 lines
897 B
SQL
CREATE TABLE users_t (
|
|
id INTEGER AUTO_INCREMENT,
|
|
username VARCHAR(25) NOT NULL,
|
|
pw VARCHAR(512) NOT NULL,
|
|
super INT(1) NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (id)
|
|
);
|
|
CREATE UNIQUE INDEX users_username ON users_t (username);
|
|
|
|
CREATE OR REPLACE VIEW users AS
|
|
SELECT username, pw, super
|
|
FROM users_t;
|
|
|
|
CREATE TABLE acls_t (
|
|
id INTEGER AUTO_INCREMENT,
|
|
user INTEGER NOT NULL,
|
|
topic VARCHAR(256) NOT NULL,
|
|
-- rw, bitmask: 1 is read, 2 is write, 3 is readwrite, 4 is subscribe
|
|
rw INTEGER(1) NOT NULL DEFAULT 1,
|
|
PRIMARY KEY (id),
|
|
CONSTRAINT `fk_user_acl`
|
|
FOREIGN KEY (user) REFERENCES users_t (id)
|
|
ON DELETE CASCADE
|
|
ON UPDATE CASCADE
|
|
);
|
|
CREATE UNIQUE INDEX acls_user_topic ON acls_t (user, topic);
|
|
|
|
CREATE OR REPLACE VIEW acls AS
|
|
SELECT a.topic, a.rw,
|
|
u.username
|
|
FROM users_t u, acls_t a
|
|
WHERE a.user = u.id;
|
|
|
|
|