view release on metacpan or  search on metacpan

lib/Apache/SdnFw/db/install/pg.sql  view on Meta::CPAN

FROM locations l
	JOIN employees e ON COALESCE(e.expired_ts,now()) >= now()
	LEFT JOIN employee_locations el ON l.location_id=el.location_id
		AND e.employee_id=el.employee_id
ORDER BY employee_name;
GRANT ALL ON employee_locations_v_location TO sdnfw;

-------------------------------
-- GROUP ACTION
-------------------------------
CREATE TABLE group_actions (
	group_id	int4 not null references groups,
	action_id	int4 not null references actions);
CREATE UNIQUE INDEX group_actions_idx ON group_actions (group_id, action_id);
GRANT ALL ON group_actions TO sdnfw;
CREATE INDEX group_actions_group_id ON group_actions (group_id);
CREATE INDEX group_actions_action_id ON group_actions (action_id);

INSERT INTO group_actions (group_id, action_id) VALUES (1000,1000);

CREATE VIEW group_actions_v (group_id, action_id, group_name, action_name) AS
SELECT ga.group_id, ga.action_id, g.name as group_name, a.name as action_name
FROM group_actions ga
	JOIN groups g ON ga.group_id=g.group_id
	JOIN actions a ON a.action_id=ga.action_id
ORDER BY group_name, action_name;
GRANT ALL ON group_actions_v TO sdnfw;

CREATE VIEW group_actions_v_group (group_id, action_id, action_name, checked) AS
SELECT g.group_id, a.action_id, a.name as action_name,
	CASE WHEN ga.group_id IS NOT NULL THEN TRUE ELSE FALSE END AS checked
FROM groups g
	JOIN actions a ON a.action_id>0
	LEFT JOIN group_actions ga ON a.action_id=ga.action_id
		AND g.group_id=ga.group_id
ORDER BY a.name;
GRANT ALL ON group_actions_v_group TO sdnfw;

CREATE VIEW group_actions_v_action (action_id, group_id, group_name, checked) AS
SELECT a.action_id, g.group_id, g.name as group_name,
	CASE WHEN ga.action_id IS NOT NULL THEN TRUE ELSE FALSE END AS checked
FROM actions a
	JOIN groups g ON g.group_id>0
	LEFT JOIN group_actions ga ON a.action_id=ga.action_id
		AND g.group_id=ga.group_id
ORDER BY g.name;
GRANT ALL ON group_actions_v_action TO sdnfw;

-------------------------------
-- EMPLOYEE GROUP
-------------------------------
CREATE TABLE employee_groups (
	employee_id		int4 not null references employees,
	group_id		int4 not null references groups);
CREATE UNIQUE INDEX employee_groups_idx ON employee_groups (employee_id, group_id);
GRANT ALL ON employee_groups TO sdnfw;
CREATE INDEX employee_groups_employee_id ON employee_groups (employee_id);
CREATE INDEX employee_groups_group_id ON employee_groups (group_id);

CREATE VIEW employees_v_login (employee_id, login, cookie, passwd, name, email,
	password_expired, locked_out, account_expired, groups, admin) AS
SELECT e.employee_id, e.login, e.cookie, e.passwd, e.name, e.email,
	CASE WHEN passwd_expire < now() THEN TRUE ELSE NULL END as password_expired,
	e.locked_out,
	CASE WHEN expired_ts < now() THEN TRUE ELSE NULL END as account_expired,
	concat(eg.group_id) as groups,
	CASE WHEN count(g.admin) > 0 THEN TRUE ELSE NULL END as admin
FROM employees e
	LEFT JOIN employee_groups eg ON e.employee_id=eg.employee_id
	LEFT JOIN groups g ON eg.group_id=g.group_id
GROUP BY 1,2,3,4,5,6,7,8,9;
GRANT ALL ON employees_v_login TO sdnfw;

CREATE VIEW employee_groups_v (employee_id, group_id,
	employee_name, group_name) AS
SELECT eg.employee_id, eg.group_id, 
	e.name as employee_name, g.name as group_name
FROM employee_groups eg
	JOIN employees e ON eg.employee_id=e.employee_id
	JOIN groups g ON eg.group_id=g.group_id
ORDER BY employee_name, group_name;
GRANT ALL ON employee_groups_v TO sdnfw;

CREATE VIEW employee_groups_v_employee (employee_id, group_id, group_name, checked) AS
SELECT e.employee_id, g.group_id, g.name as group_name,
	CASE WHEN eg.employee_id IS NOT NULL THEN TRUE ELSE FALSE END as checked
FROM employees e
	JOIN groups g ON g.group_id>0
	LEFT JOIN employee_groups eg ON e.employee_id=eg.employee_id
		AND eg.group_id=g.group_id
ORDER BY g.name;
GRANT ALL ON employee_groups_v_employee TO sdnfw;

CREATE VIEW employee_groups_v_group (group_id, employee_id, employee_name, checked) AS
SELECT g.group_id, e.employee_id, e.name as employee_name,
	CASE WHEN eg.group_id IS NOT NULL THEN TRUE ELSE FALSE END as checked
FROM groups g
	JOIN employees e ON COALESCE(e.expired_ts,now()) >= now()
	LEFT JOIN employee_groups eg ON e.employee_id=eg.employee_id
		AND eg.group_id=g.group_id
ORDER BY e.name;
GRANT ALL ON employee_groups_v_group TO sdnfw;

-------------------------------
-- COUNTRY
-------------------------------
CREATE TABLE countries (
	country		varchar(2) primary key,
	name		varchar(255) not null,
	sort_order	int4 not null default 99);
GRANT SELECT ON countries TO sdnfw;
INSERT INTO countries (country, name, sort_order) VALUES
('US','United States',1),
('CA','Canada',2);

-------------------------------
-- STATE
-------------------------------
CREATE TABLE states (
	state		varchar(2) primary key,
	country		varchar(2) not null references countries,
	name		varchar(255));
GRANT SELECT ON states TO sdnfw;