-- 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 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 AS $$

	use strict;

	use warnings;

	use User::grent;

	while (my $group = getgrent()) {

		return_next({

			id => $group->gid,

			name => $group->name,

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

		});

	}

	elog(NOTICE, "members field does not contain users who have this group as primary one");

	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';

-- user: ---------------------------------------------------------------------

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 AS $$

	use strict;

	use warnings;

	use Encode; # FIXME: see below

	use User::pwent;

	while (my $user = getpwent()) {

		return_next({

			id => $user->uid,

			gid => $user->gid,

			name => $user->name,

			# comment => $user->comment,

			gecos => [split(",", Encode::decode("utf8", $user->gecos))], # FIXME: ugly hack – should be properly decoded in getpwent()

			home => $user->dir,

			shell => $user->shell,

			# expire => $user->expire

		});

	}

	return undef;

$$ LANGUAGE plperlu;

CREATE OR REPLACE VIEW users AS

	SELECT * FROM users()

;