Spaces:

Aniruddha7
/

QueryLensV2b

Running on Zero

QueryLensV2b / employee-sample-database /postgres /dataset_small /01_employee.sql

Full deploy of QueryLensV2 (code + demo db) to a fresh Space

19dfd32 verified 2 days ago

6.33 kB

--  Sample employee database for PostgreSQL derived from MySQL version
--  ----------------------Original license BEGIN----------------------
--  Sample employee database 
--  See changelog table for details
--  Copyright (C) 2007,2008, MySQL AB
--  
--  Original data created by Fusheng Wang and Carlo Zaniolo
--  http://www.cs.aau.dk/TimeCenter/software.htm
--  http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
-- 
--  Current schema by Giuseppe Maxia 
--  Data conversion from XML to relational by Patrick Crews
-- 
-- This work is licensed under the 
-- Creative Commons Attribution-Share Alike 3.0 Unported License. 
-- To view a copy of this license, visit 
-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to 
-- Creative Commons, 171 Second Street, Suite 300, San Francisco, 
-- California, 94105, USA.
-- 
--  DISCLAIMER
--  To the best of our knowledge, this data is fabricated, and
--  it does not correspond to real people. 
--  Any similarity to existing people is purely coincidental.
-- 

\echo 'CREATING DATABASE STRUCTURE'

DROP TABLE IF EXISTS dept_emp,
                     dept_manager,
                     title,
                     salary, 
                     employee, 
                     department,
					 audit CASCADE;

CREATE TABLE employee (
	emp_no      SERIAL NOT NULL,
	birth_date  DATE NOT NULL,
	first_name  TEXT NOT NULL,
	last_name   TEXT NOT NULL,
	gender      TEXT NOT NULL CHECK (gender IN('M', 'F')) NOT NULL,
	hire_date   DATE NOT NULL,
	PRIMARY KEY (emp_no)
);

CREATE INDEX idx_employee_hire_date ON employee (hire_date);

CREATE TABLE department (
	dept_no     TEXT NOT NULL,
	dept_name   TEXT NOT NULL,
	PRIMARY KEY (dept_no),
	UNIQUE      (dept_name)
);

CREATE TABLE dept_manager (
	emp_no      INT NOT NULL,
	dept_no     TEXT NOT NULL,
	from_date   DATE NOT NULL,
	to_date     DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, dept_no)
);

CREATE TABLE dept_emp (
	emp_no      INT NOT NULL,
	dept_no     TEXT NOT NULL,
	from_date   DATE NOT NULL,
	to_date     DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, dept_no)
);

CREATE TABLE title (
	emp_no      INT NOT NULL,
	title       TEXT NOT NULL,
	from_date   DATE NOT NULL,
	to_date     DATE,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, title, from_date)
); 

CREATE TABLE salary (
	emp_no      INT NOT NULL,
	salary      INT NOT NULL,
	from_date   DATE NOT NULL,
	to_date     DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, from_date)
);

CREATE INDEX idx_salary_salary ON salary (salary);

CREATE TABLE audit (
    id SERIAL PRIMARY KEY,
    operation TEXT NOT NULL,
    query TEXT,
    user_name TEXT NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_operation ON audit (operation);
CREATE INDEX idx_audit_username ON audit (user_name);
CREATE INDEX idx_audit_changed_at ON audit (changed_at);

-- Enable Row Level Security on audit table
ALTER TABLE audit ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own audit records
CREATE POLICY audit_user_isolation ON audit
    FOR ALL
    TO PUBLIC
    USING (user_name = current_user);

-- Policy: Allow audit system to insert records (bypass RLS for service accounts)
CREATE POLICY audit_insert_system ON audit
    FOR INSERT
    TO PUBLIC
    WITH CHECK (true);

CREATE OR REPLACE PROCEDURE simple_salary_update(
    p_emp_no INT,
    p_salary INT
)
LANGUAGE plpgsql
AS $_$
BEGIN
    -- Simple update of salary amount
    UPDATE salary 
    SET salary = p_salary 
    WHERE emp_no = p_emp_no 
    AND to_date = '9999-01-01';
    
    RAISE NOTICE 'Updated salary for employee % to $%', p_emp_no, p_salary;
END;
$_$;

CREATE OR REPLACE FUNCTION log_dml_operations() RETURNS TRIGGER AS $$
DECLARE
    table_category TEXT;
    log_level TEXT;
BEGIN
    -- Get arguments passed from trigger (if any)
    -- TG_ARGV[0] is the first argument, TG_ARGV[1] is the second
    table_category := COALESCE(TG_ARGV[0], 'default');
    log_level := COALESCE(TG_ARGV[1], 'standard');
    
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO audit (operation, query, user_name)
        VALUES (
            'INSERT [' || table_category || ':' || log_level || ']', 
            current_query(), 
            current_user
        );
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit (operation, query, user_name)
        VALUES (
            'UPDATE [' || table_category || ':' || log_level || ']', 
            current_query(), 
            current_user
        );
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO audit (operation, query, user_name)
        VALUES (
            'DELETE [' || table_category || ':' || log_level || ']', 
            current_query(), 
            current_user
        );
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- only log update and delete, otherwise, it will cause too much change.
CREATE TRIGGER salary_log_trigger
AFTER UPDATE OR DELETE ON salary
FOR EACH ROW
EXECUTE FUNCTION log_dml_operations('payroll', 'high');

CREATE OR REPLACE VIEW dept_emp_latest_date AS
SELECT
	emp_no,
	MAX(
		from_date) AS from_date,
	MAX(
		to_date) AS to_date
FROM
	dept_emp
GROUP BY
	emp_no;

-- shows only the current department for each employee
CREATE OR REPLACE VIEW current_dept_emp AS
SELECT
	l.emp_no,
	dept_no,
	l.from_date,
	l.to_date
FROM
	dept_emp d
	INNER JOIN dept_emp_latest_date l ON d.emp_no = l.emp_no
		AND d.from_date = l.from_date
		AND l.to_date = d.to_date;

\echo 'LOADING department'
\i /docker-entrypoint-initdb.d/load_department.sql
\echo 'LOADING employee'
\i /docker-entrypoint-initdb.d/load_employee.sql
\echo 'LOADING dept_emp'
\i /docker-entrypoint-initdb.d/load_dept_emp.sql
\echo 'LOADING dept_manager'
\i /docker-entrypoint-initdb.d/load_dept_manager.sql
\echo 'LOADING title'
\i /docker-entrypoint-initdb.d/load_title.sql
\echo 'LOADING salary'
\i /docker-entrypoint-initdb.d/load_salary1.sql

	-- Sample employee database for PostgreSQL derived from MySQL version
	-- ----------------------Original license BEGIN----------------------
	-- Sample employee database
	-- See changelog table for details
	-- Copyright (C) 2007,2008, MySQL AB
	--
	-- Original data created by Fusheng Wang and Carlo Zaniolo
	-- http://www.cs.aau.dk/TimeCenter/software.htm
	-- http://www.cs.aau.dk/TimeCenter/Data/employeeTemporalDataSet.zip
	--
	-- Current schema by Giuseppe Maxia
	-- Data conversion from XML to relational by Patrick Crews
	--
	-- This work is licensed under the
	-- Creative Commons Attribution-Share Alike 3.0 Unported License.
	-- To view a copy of this license, visit
	-- http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to
	-- Creative Commons, 171 Second Street, Suite 300, San Francisco,
	-- California, 94105, USA.
	--
	-- DISCLAIMER
	-- To the best of our knowledge, this data is fabricated, and
	-- it does not correspond to real people.
	-- Any similarity to existing people is purely coincidental.
	--

	\echo 'CREATING DATABASE STRUCTURE'

	DROP TABLE IF EXISTS dept_emp,
	dept_manager,
	title,
	salary,
	employee,
	department,
	audit CASCADE;

	CREATE TABLE employee (
	emp_no SERIAL NOT NULL,
	birth_date DATE NOT NULL,
	first_name TEXT NOT NULL,
	last_name TEXT NOT NULL,
	gender TEXT NOT NULL CHECK (gender IN('M', 'F')) NOT NULL,
	hire_date DATE NOT NULL,
	PRIMARY KEY (emp_no)
	);

	CREATE INDEX idx_employee_hire_date ON employee (hire_date);

	CREATE TABLE department (
	dept_no TEXT NOT NULL,
	dept_name TEXT NOT NULL,
	PRIMARY KEY (dept_no),
	UNIQUE (dept_name)
	);

	CREATE TABLE dept_manager (
	emp_no INT NOT NULL,
	dept_no TEXT NOT NULL,
	from_date DATE NOT NULL,
	to_date DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, dept_no)
	);

	CREATE TABLE dept_emp (
	emp_no INT NOT NULL,
	dept_no TEXT NOT NULL,
	from_date DATE NOT NULL,
	to_date DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	FOREIGN KEY (dept_no) REFERENCES department (dept_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, dept_no)
	);

	CREATE TABLE title (
	emp_no INT NOT NULL,
	title TEXT NOT NULL,
	from_date DATE NOT NULL,
	to_date DATE,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, title, from_date)
	);

	CREATE TABLE salary (
	emp_no INT NOT NULL,
	salary INT NOT NULL,
	from_date DATE NOT NULL,
	to_date DATE NOT NULL,
	FOREIGN KEY (emp_no) REFERENCES employee (emp_no) ON DELETE CASCADE,
	PRIMARY KEY (emp_no, from_date)
	);

	CREATE INDEX idx_salary_salary ON salary (salary);

	CREATE TABLE audit (
	id SERIAL PRIMARY KEY,
	operation TEXT NOT NULL,
	query TEXT,
	user_name TEXT NOT NULL,
	changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX idx_audit_operation ON audit (operation);
	CREATE INDEX idx_audit_username ON audit (user_name);
	CREATE INDEX idx_audit_changed_at ON audit (changed_at);

	-- Enable Row Level Security on audit table
	ALTER TABLE audit ENABLE ROW LEVEL SECURITY;

	-- Policy: Users can only see their own audit records
	CREATE POLICY audit_user_isolation ON audit
	FOR ALL
	TO PUBLIC
	USING (user_name = current_user);

	-- Policy: Allow audit system to insert records (bypass RLS for service accounts)
	CREATE POLICY audit_insert_system ON audit
	FOR INSERT
	TO PUBLIC
	WITH CHECK (true);

	CREATE OR REPLACE PROCEDURE simple_salary_update(
	p_emp_no INT,
	p_salary INT
	)
	LANGUAGE plpgsql
	AS $_$
	BEGIN
	-- Simple update of salary amount
	UPDATE salary
	SET salary = p_salary
	WHERE emp_no = p_emp_no
	AND to_date = '9999-01-01';

	RAISE NOTICE 'Updated salary for employee % to $%', p_emp_no, p_salary;
	END;
	$_$;

	CREATE OR REPLACE FUNCTION log_dml_operations() RETURNS TRIGGER AS $$
	DECLARE
	table_category TEXT;
	log_level TEXT;
	BEGIN
	-- Get arguments passed from trigger (if any)
	-- TG_ARGV[0] is the first argument, TG_ARGV[1] is the second
	table_category := COALESCE(TG_ARGV[0], 'default');
	log_level := COALESCE(TG_ARGV[1], 'standard');

	IF (TG_OP = 'INSERT') THEN
	INSERT INTO audit (operation, query, user_name)
	VALUES (
	'INSERT [' \|\| table_category \|\| ':' \|\| log_level \|\| ']',
	current_query(),
	current_user
	);
	RETURN NEW;
	ELSIF (TG_OP = 'UPDATE') THEN
	INSERT INTO audit (operation, query, user_name)
	VALUES (
	'UPDATE [' \|\| table_category \|\| ':' \|\| log_level \|\| ']',
	current_query(),
	current_user
	);
	RETURN NEW;
	ELSIF (TG_OP = 'DELETE') THEN
	INSERT INTO audit (operation, query, user_name)
	VALUES (
	'DELETE [' \|\| table_category \|\| ':' \|\| log_level \|\| ']',
	current_query(),
	current_user
	);
	RETURN OLD;
	END IF;
	RETURN NULL;
	END;
	$$ LANGUAGE plpgsql;

	-- only log update and delete, otherwise, it will cause too much change.
	CREATE TRIGGER salary_log_trigger
	AFTER UPDATE OR DELETE ON salary
	FOR EACH ROW
	EXECUTE FUNCTION log_dml_operations('payroll', 'high');

	CREATE OR REPLACE VIEW dept_emp_latest_date AS
	SELECT
	emp_no,
	MAX(
	from_date) AS from_date,
	MAX(
	to_date) AS to_date
	FROM
	dept_emp
	GROUP BY
	emp_no;

	-- shows only the current department for each employee
	CREATE OR REPLACE VIEW current_dept_emp AS
	SELECT
	l.emp_no,
	dept_no,
	l.from_date,
	l.to_date
	FROM
	dept_emp d
	INNER JOIN dept_emp_latest_date l ON d.emp_no = l.emp_no
	AND d.from_date = l.from_date
	AND l.to_date = d.to_date;

	\echo 'LOADING department'
	\i /docker-entrypoint-initdb.d/load_department.sql
	\echo 'LOADING employee'
	\i /docker-entrypoint-initdb.d/load_employee.sql
	\echo 'LOADING dept_emp'
	\i /docker-entrypoint-initdb.d/load_dept_emp.sql
	\echo 'LOADING dept_manager'
	\i /docker-entrypoint-initdb.d/load_dept_manager.sql
	\echo 'LOADING title'
	\i /docker-entrypoint-initdb.d/load_title.sql
	\echo 'LOADING salary'
	\i /docker-entrypoint-initdb.d/load_salary1.sql