91 lines
3.0 KiB
SQL
91 lines
3.0 KiB
SQL
-- C4's OAuth2 session tables and qbit-user-role-permissions tables.
|
|
-- Names follow QInstanceEnricher's camelCase-to-snake_case backend mapping.
|
|
CREATE TABLE IF NOT EXISTS user_session (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
uuid VARCHAR(40) UNIQUE,
|
|
access_token TEXT,
|
|
user_id VARCHAR(100)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS redirect_state (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
state VARCHAR(45) UNIQUE,
|
|
redirect_uri VARCHAR(4096),
|
|
create_date TIMESTAMPTZ
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS "user" (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
email VARCHAR(250),
|
|
full_name VARCHAR(250)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS role (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
description VARCHAR(500)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS permission (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
name VARCHAR(250) NOT NULL UNIQUE,
|
|
description VARCHAR(500),
|
|
object_type VARCHAR(50),
|
|
object_label VARCHAR(250)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS role_permission_int (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
role_id INTEGER NOT NULL REFERENCES role(id),
|
|
permission_id INTEGER NOT NULL REFERENCES permission(id),
|
|
UNIQUE (role_id, permission_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_role_int (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
user_id INTEGER NOT NULL REFERENCES "user"(id),
|
|
role_id INTEGER NOT NULL REFERENCES role(id),
|
|
UNIQUE (user_id, role_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_permission_int (
|
|
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
create_date TIMESTAMPTZ,
|
|
modify_date TIMESTAMPTZ,
|
|
user_id INTEGER NOT NULL REFERENCES "user"(id),
|
|
permission_id INTEGER NOT NULL REFERENCES permission(id),
|
|
UNIQUE (user_id, permission_id)
|
|
);
|
|
|
|
INSERT INTO role (name, description) VALUES
|
|
('admin', 'All QQQ All demo tables'),
|
|
('viewer', 'Read-only QQQ All demo tables')
|
|
ON CONFLICT (name) DO NOTHING;
|
|
|
|
INSERT INTO permission (name, object_type, object_label)
|
|
SELECT table_name || '.' || action, 'table', table_name
|
|
FROM (VALUES
|
|
('customer'), ('order'), ('orderLine'), ('product'),
|
|
('warehouseCustomer'), ('supplierOrder'), ('shipment'), ('document'), ('importFile')
|
|
) AS tables(table_name)
|
|
CROSS JOIN (VALUES ('read'), ('insert'), ('edit'), ('delete')) AS actions(action)
|
|
ON CONFLICT (name) DO NOTHING;
|
|
|
|
INSERT INTO role_permission_int (role_id, permission_id)
|
|
SELECT role.id, permission.id
|
|
FROM role CROSS JOIN permission
|
|
WHERE role.name = 'admin' OR (role.name = 'viewer' AND permission.name LIKE '%.read')
|
|
ON CONFLICT (role_id, permission_id) DO NOTHING;
|