view release on metacpan or  search on metacpan

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

CREATE USER sdnfw;
CREATE LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION pop_last(text) RETURNS text
    AS ' SELECT x.str[array_upper(x.str,1)] FROM (SELECT string_to_array($1,'','') as str) x; '
    LANGUAGE sql;

--DROP AGGREGATE concat(int4);
--DROP AGGREGATE concat(varchar);
--DROP AGGREGATE concat(float8);

CREATE OR REPLACE FUNCTION join_array(anyarray) RETURNS text
    AS ' SELECT array_to_string($1,'',''); '
    LANGUAGE sql;

CREATE AGGREGATE concat (
    BASETYPE = anyelement,
    SFUNC = array_append,
    STYPE = anyarray,
    FINALFUNC = join_array,
    INITCOND = '{}'
);

CREATE OR REPLACE FUNCTION sum_array(anyarray,anyelement) RETURNS anyarray
    AS ' SELECT array_append($1,COALESCE($1[array_upper($1,1)],0)+$2); '
    LANGUAGE sql;

--DROP AGGREGATE concat_sum(int4);
--DROP AGGREGATE concat_sum(varchar);
--DROP AGGREGATE concat_sum(float8);

CREATE AGGREGATE concat_sum (
    BASETYPE = anyelement,
    SFUNC = sum_array,
    STYPE = anyarray,
    FINALFUNC = join_array,
    INITCOND = '{}'
);

-------------------------------
-- EMPLOYEE
-------------------------------
CREATE TABLE employees (
	employee_id		serial primary key,
	login			varchar(255) unique not null,
	passwd			varchar(32) not null,
	name			varchar(255) not null,
	email			varchar(255),
	passwd_expire	timestamp,
	locked_out		boolean,
	remote_addr		inet,
	last_bad_pass	date,
	bad_pass_count	int4,
	cookie			varchar(32) unique,
	created_ts		timestamp not null default now(),
	expired_ts		timestamp);

GRANT INSERT,UPDATE,SELECT,DELETE ON employees TO sdnfw;
GRANT ALL ON employees_employee_id_seq TO sdnfw;

SELECT setval('employees_employee_id_seq',1000,'false');

CREATE TABLE employee_sessions (
	employee_id		int4 unique references employees,
	last_update_ts	timestamp not null default now(),
	data			text);
GRANT ALL ON employee_sessions TO sdnfw;

CREATE VIEW employees_v (employee_id, login, passwd, name, email) AS
SELECT employee_id, login, passwd, name, email
FROM employees
ORDER BY name;
GRANT ALL ON employees_v TO sdnfw;

CREATE VIEW employees_v_keyval (id, name) AS
SELECT employee_id, name
FROM employees
ORDER BY name;
GRANT ALL ON employees_v_keyval TO sdnfw;

-------------------------------
-- LOCATION
-------------------------------
CREATE TABLE locations (
	location_id		serial primary key,
	employee_id		int4 references employees,
	name			varchar(255) not null,
	b_address			varchar(255),
	b_address2		varchar(255),
	b_city			varchar(255),
	b_state			varchar(2),
	b_zipcode			varchar(12),
	s_address			varchar(255),
	s_address2		varchar(255),
	s_city			varchar(255),
	s_state			varchar(2),
	s_zipcode			varchar(12),
	phone			int8,
	fax				int8,
	email			varchar(255),
	website			varchar(255),
	created_ts		timestamp not null default now(),
	closed_ts		timestamp);
GRANT INSERT,UPDATE,SELECT ON locations TO sdnfw;
GRANT ALL ON locations_location_id_seq TO sdnfw;

SELECT setval('locations_location_id_seq',1000,'false');

CREATE VIEW locations_v (location_id, name,
	b_address, b_address2, b_city, b_state, b_zipcode, 

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;
INSERT INTO states (state, country, name) VALUES