-- SQL-UNIX-API (prototype)

-- Copyright © 2014 František Kučera (frantovo.cz)

-- 

-- This program is free software: you can redistribute it and/or modify

-- it under the terms of the GNU General Public License as published by

-- the Free Software Foundation, either version 3 of the License, or

-- (at your option) any later version.

-- 

-- This program is distributed in the hope that it will be useful,

-- but WITHOUT ANY WARRANTY; without even the implied warranty of

-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

-- GNU General Public License for more details.

-- 

-- You should have received a copy of the GNU General Public License

-- along with this program. If not, see <http://www.gnu.org/licenses/>.

-- CREATE SCHEMA unix_sql_api;

SET search_path TO unix_sql_api;

-- fstab: --------------------------------------------------------------------

DROP VIEW IF EXISTS fstab;

DROP FUNCTION IF EXISTS fstab();

DROP TYPE IF EXISTS unix_sql_api_fstab;

CREATE TYPE unix_sql_api_fstab AS (

	device VARCHAR,

	device_type VARCHAR,

	device_value VARCHAR,

	mount_point VARCHAR,

	type VARCHAR,

	types VARCHAR[],

	options VARCHAR[],

	dump INTEGER,

	pass INTEGER

);

CREATE OR REPLACE FUNCTION fstab()

RETURNS SETOF unix_sql_api_fstab AS $$

	use strict;

	use warnings;

	open(FSTAB, "<", "/etc/fstab") or die $!;

	while (<FSTAB>) {

		if (/^([^\s#]+)\s+([^\s]+)\s+([^\s]+)\s+([^\s]+)\s+(\d+)\s+(\d+)\s*$/) {

			my $device_spec_type;

			my $device_spec_value;

			{

				if ($1 =~ /([^=]+)=(.*)/) {

					($device_spec_type, $device_spec_value) = ($1, $2);

				}

			}

			return_next({

				device => $1,

				device_type => $device_spec_type,

				device_value => $device_spec_value,

				mount_point => $2,

				type => $3,

				types => [split(",", $3)],

				options => [split(",", $4)],

				dump => $5,

				pass => $6

			});

		}

	}

	return undef;

$$ LANGUAGE plperlu;

CREATE OR REPLACE VIEW fstab AS

	SELECT * FROM fstab()

;

-- user groups: --------------------------------------------------------------

DROP VIEW IF EXISTS users_groups;

DROP VIEW IF EXISTS groups;

DROP FUNCTION IF EXISTS groups();

DROP TYPE IF EXISTS unix_sql_api_groups;

CREATE TYPE unix_sql_api_groups AS (

	id INTEGER,

	name VARCHAR,

	members VARCHAR[]

);

CREATE OR REPLACE FUNCTION groups()

RETURNS SETOF unix_sql_api_groups STABLE AS $$

	use strict;

	use warnings;

	use User::grent;

	my $i = 0;

	setgrent();

	while (my $group = getgrent()) {

		return_next({

			id => $group->gid,

			name => $group->name,

			members => [@{$group->members}]

		});

	}

	endgrent();

	return undef;

$$ LANGUAGE plperlu;

CREATE OR REPLACE VIEW groups AS

	SELECT * FROM groups()

;

COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one – see view: users_groups';

-- users: --------------------------------------------------------------------

DROP VIEW IF EXISTS users;

DROP FUNCTION IF EXISTS users();

DROP TYPE IF EXISTS unix_sql_api_users;

CREATE TYPE unix_sql_api_users AS (

	id INTEGER,

	gid INTEGER,

	name VARCHAR,

	-- comment VARCHAR,

	gecos VARCHAR[],

	home VARCHAR,

	shell VARCHAR

	-- expire VARCHAR

);

CREATE OR REPLACE FUNCTION users()

RETURNS SETOF unix_sql_api_users STABLE AS $$

	use strict;

	use warnings;

	use encoding "UTF-8";

	use User::pwent;

	my $i = 0;

	setpwent();

	while (my $user = getpwent()) {

		return_next({

			id => $user->uid,

			gid => $user->gid,

			name => $user->name,

			# comment => $user->comment,

			gecos => [split(",", $user->gecos)],

			home => $user->dir,

			shell => $user->shell,

			# expire => $user->expire

		});

	}

	endpwent();

	return undef;

$$ LANGUAGE plperlu;

CREATE OR REPLACE VIEW users AS

	SELECT * FROM users()

;

-- users_groups: -------------------------------------------------------------

CREATE OR REPLACE VIEW users_groups AS

	SELECT

		u1.id AS uid,

		u1.gid AS gid,

		u1.name AS user,

		g1.name AS group,

		true AS initial

	FROM users AS u1

	JOIN groups AS g1 ON (u1.gid = g1.id)

	UNION

	SELECT

		u2.id AS uid,

		g2.*,

		false AS initial

	FROM

		(SELECT

			id AS gid,

			unnest(members) AS user,

			name AS group

		FROM groups) AS g2

	JOIN users AS u2 ON (u2.name = g2.user)

;

COMMENT ON COLUMN users_groups.initial IS 'whether this group is the „initial login group“ of given user (the primary group)';