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

DROP FUNCTION IF EXISTS user_group();

DROP TYPE IF EXISTS unix_sql_api_user_group;

CREATE TYPE unix_sql_api_user_group AS (

	id INTEGER,

	name VARCHAR,

	members VARCHAR[]

);

CREATE OR REPLACE FUNCTION user_group()

RETURNS SETOF unix_sql_api_user_group AS $$

	use strict;

	use warnings;

	use User::grent;

	while (my $group = getgrent) {

		return_next({

			id => $group->gid,

			name => $group->name,

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

		});

	}

	return undef;

$$ LANGUAGE plperlu;

CREATE OR REPLACE VIEW user_group AS

	SELECT * FROM user_group()

;