test data for database

all_in_one.sql

-- file create_str.sql
-- database creation script
-- central script for creating all database objects

-- set the database name
\set strdbname logincore

\c admin

BEGIN;
\i str_roles.sql
COMMIT;

DROP DATABASE IF EXISTS :strdbname;
CREATE DATABASE :strdbname TEMPLATE template1 OWNER str_db_owner
    ENCODING 'UTF8';
\c :strdbname

SET ROLE str_db_owner;

BEGIN;
\i str.sql
COMMIT;
RESET ROLE;





-- file str_roles.sql
-- create roles for the database

-- owner of the database objects
SELECT create_role('str_db_owner', 'NOINHERIT');

-- role for using
SELECT create_role('str_user');

-- make str_db_owner member in all relevant roles
GRANT str_user TO str_db_owner WITH ADMIN OPTION;





-- file str.sql
-- creation of database

-- prototypes
\i str_prototypes.sql

-- domain for non empty text
CREATE DOMAIN ntext AS text CHECK (VALUE<>'');

-- domain for email addresses
CREATE DOMAIN email AS varchar(252) CHECK (is_email_address(VALUE));

-- domain for phone numbers
CREATE DOMAIN phone AS varchar(60) CHECK (is_phone_number(VALUE));

-- persons
CREATE TABLE persons (
    id    serial       PRIMARY KEY,
    name  varchar(252) NOT NULL,
    email email,
    phone phone
);

GRANT SELECT, INSERT, UPDATE, DELETE ON persons TO str_user;
GRANT USAGE ON SEQUENCE persons_id_seq TO str_user;

CREATE TABLE groups (
    id   integer     PRIMARY KEY,
    name varchar(60) UNIQUE NOT NULL
);

GRANT SELECT ON groups TO str_user;

-- database users
CREATE TABLE users (
    id    integer     PRIMARY KEY REFERENCES persons(id) ON UPDATE CASCADE,
    login varchar(60) UNIQUE NOT NULL
);

GRANT SELECT ON users TO str_user;

-- user <-> groups
CREATE TABLE user_groups (
    user_id  integer NOT NULL REFERENCES users(id)
                              ON UPDATE CASCADE ON DELETE CASCADE,
    group_id integer NOT NULL REFERENCES groups(id)
                              ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (user_id, group_id)
);

-- functions
\i str_functions.sql





-- file str_prototypes.sql
-- prototypes for database

-- simple check for correct email address
CREATE FUNCTION is_email_address(email varchar) RETURNS boolean
    AS $CODE$
    SELECT FALSE
    $CODE$ LANGUAGE sql IMMUTABLE STRICT;

-- simple check for correct phone number
CREATE FUNCTION is_phone_number(nr varchar) RETURNS boolean
    AS $CODE$
    SELECT FALSE
    $CODE$ LANGUAGE sql IMMUTABLE STRICT;




-- file str_functions.sql
-- functions for sample tracking database

-- simple check for correct email address
CREATE OR REPLACE FUNCTION is_email_address(email varchar) RETURNS boolean
	AS $CODE$
	SELECT $1 ~ E'^[A-Za-z0-9.!#$%&\'\*\+\-/=\?\^_\`{\|}\~\.]+@[-a-z0-9\.]+$'
	$CODE$ LANGUAGE sql IMMUTABLE STRICT;

-- simple check for correct phone number
CREATE OR REPLACE FUNCTION is_phone_number(nr varchar) RETURNS boolean
	AS $CODE$
	SELECT $1 ~ E'^[-+0-9\(\)/ ]+$'
	$CODE$ LANGUAGE sql IMMUTABLE STRICT;




'




-- file fill_str_test.sql
-- test data for database
-- between the columns are supposed to be tabs, no spaces !!!

BEGIN;

COPY persons (id, name, email) FROM STDIN;
1	Joseph Schneider	jschneid@lab.uni.de
2	Test User	jschneid@lab.uni.de
3	Hans Dampf	\N
\.
SELECT setval('persons_id_seq', (SELECT max(id) FROM persons));

COPY groups (id, name) FROM STDIN;
1	IT
2	SSG
\.

COPY users (id, login) FROM STDIN;
1	jschneid
2	tuser
3	dummy
\.

COPY user_groups (user_id, group_id) FROM STDIN;
1	1
2	1
3	2
\.

COMMIT;