franta-hg@6: -- SQL-UNIX-API (prototype)
franta-hg@6: -- Copyright © 2014 František Kučera (frantovo.cz)
franta-hg@6: -- 
franta-hg@6: -- This program is free software: you can redistribute it and/or modify
franta-hg@6: -- it under the terms of the GNU General Public License as published by
franta-hg@6: -- the Free Software Foundation, either version 3 of the License, or
franta-hg@6: -- (at your option) any later version.
franta-hg@6: -- 
franta-hg@6: -- This program is distributed in the hope that it will be useful,
franta-hg@6: -- but WITHOUT ANY WARRANTY; without even the implied warranty of
franta-hg@6: -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
franta-hg@6: -- GNU General Public License for more details.
franta-hg@6: -- 
franta-hg@6: -- You should have received a copy of the GNU General Public License
franta-hg@6: -- along with this program. If not, see <http://www.gnu.org/licenses/>.
franta-hg@6: 
franta-hg@6: 
franta-hg@6: 
franta-hg@2: -- CREATE SCHEMA unix_sql_api;
franta-hg@2: 
franta-hg@2: SET search_path TO unix_sql_api;
franta-hg@2: 
franta-hg@3: 
franta-hg@3: -- fstab: --------------------------------------------------------------------
franta-hg@3: 
franta-hg@2: DROP VIEW IF EXISTS fstab;
franta-hg@2: DROP FUNCTION IF EXISTS fstab();
franta-hg@2: DROP TYPE IF EXISTS unix_sql_api_fstab;
franta-hg@2: 
franta-hg@2: CREATE TYPE unix_sql_api_fstab AS (
franta-hg@2: 	device VARCHAR,
franta-hg@2: 	device_type VARCHAR,
franta-hg@2: 	device_value VARCHAR,
franta-hg@2: 	mount_point VARCHAR,
franta-hg@2: 	type VARCHAR,
franta-hg@2: 	types VARCHAR[],
franta-hg@2: 	options VARCHAR[],
franta-hg@2: 	dump INTEGER,
franta-hg@2: 	pass INTEGER
franta-hg@2: );
franta-hg@2: 
franta-hg@2: CREATE OR REPLACE FUNCTION fstab()
franta-hg@2: RETURNS SETOF unix_sql_api_fstab AS $$
franta-hg@2: 	use strict;
franta-hg@2: 	use warnings;
franta-hg@2: 	
franta-hg@2: 	open(FSTAB, "<", "/etc/fstab") or die $!;
franta-hg@2: 	
franta-hg@2: 	while (<FSTAB>) {
franta-hg@2: 		if (/^([^\s#]+)\s+([^\s]+)\s+([^\s]+)\s+([^\s]+)\s+(\d+)\s+(\d+)\s*$/) {
franta-hg@2: 			
franta-hg@2: 			my $device_spec_type;
franta-hg@2: 			my $device_spec_value;
franta-hg@2: 			{
franta-hg@2: 				if ($1 =~ /([^=]+)=(.*)/) {
franta-hg@2: 					($device_spec_type, $device_spec_value) = ($1, $2);
franta-hg@2: 				}
franta-hg@2: 			}
franta-hg@2: 			
franta-hg@2: 			return_next({
franta-hg@2: 				device => $1,
franta-hg@2: 				device_type => $device_spec_type,
franta-hg@2: 				device_value => $device_spec_value,
franta-hg@2: 				mount_point => $2,
franta-hg@2: 				type => $3,
franta-hg@2: 				types => [split(",", $3)],
franta-hg@2: 				options => [split(",", $4)],
franta-hg@2: 				dump => $5,
franta-hg@2: 				pass => $6
franta-hg@2: 			});
franta-hg@2: 		}
franta-hg@2: 	}
franta-hg@2: 	
franta-hg@2: 	return undef;
franta-hg@2: $$ LANGUAGE plperlu;
franta-hg@2: 
franta-hg@2: CREATE OR REPLACE VIEW fstab AS
franta-hg@2: 	SELECT * FROM fstab()
franta-hg@3: ;
franta-hg@3: 
franta-hg@3: 
franta-hg@3: -- user groups: --------------------------------------------------------------
franta-hg@3: 
franta-hg@8: DROP VIEW IF EXISTS users_groups;
franta-hg@8: 
franta-hg@4: DROP VIEW IF EXISTS groups;
franta-hg@4: DROP FUNCTION IF EXISTS groups();
franta-hg@4: DROP TYPE IF EXISTS unix_sql_api_groups;
franta-hg@3: 
franta-hg@4: CREATE TYPE unix_sql_api_groups AS (
franta-hg@3: 	id INTEGER,
franta-hg@3: 	name VARCHAR,
franta-hg@3: 	members VARCHAR[]
franta-hg@3: );
franta-hg@3: 
franta-hg@4: CREATE OR REPLACE FUNCTION groups()
franta-hg@8: RETURNS SETOF unix_sql_api_groups STABLE AS $$
franta-hg@3: 	use strict;
franta-hg@3: 	use warnings;
franta-hg@5: 
franta-hg@3: 	use User::grent;
franta-hg@8: 	
franta-hg@8: 	my $i = 0;
franta-hg@3: 
franta-hg@9: 	setgrent();
franta-hg@5: 	while (my $group = getgrent()) {
franta-hg@3: 		return_next({
franta-hg@3: 			id => $group->gid,
franta-hg@3: 			name => $group->name,
franta-hg@3: 			members => [@{$group->members}]
franta-hg@3: 		});
franta-hg@3: 	}
franta-hg@9: 	endgrent();
franta-hg@5: 	
franta-hg@3: 	return undef;
franta-hg@3: $$ LANGUAGE plperlu;
franta-hg@3: 
franta-hg@4: CREATE OR REPLACE VIEW groups AS
franta-hg@4: 	SELECT * FROM groups()
franta-hg@3: ;
franta-hg@9: COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one – see view: users_groups';
franta-hg@3: 
franta-hg@5: 
franta-hg@8: -- users: --------------------------------------------------------------------
franta-hg@5: 
franta-hg@5: DROP VIEW IF EXISTS users;
franta-hg@5: DROP FUNCTION IF EXISTS users();
franta-hg@5: DROP TYPE IF EXISTS unix_sql_api_users;
franta-hg@5: 
franta-hg@5: CREATE TYPE unix_sql_api_users AS (
franta-hg@5: 	id INTEGER,
franta-hg@5: 	gid INTEGER,
franta-hg@5: 	name VARCHAR,
franta-hg@5: 	-- comment VARCHAR,
franta-hg@5: 	gecos VARCHAR[],
franta-hg@5: 	home VARCHAR,
franta-hg@5: 	shell VARCHAR
franta-hg@5: 	-- expire VARCHAR
franta-hg@5: );
franta-hg@5: 
franta-hg@5: CREATE OR REPLACE FUNCTION users()
franta-hg@8: RETURNS SETOF unix_sql_api_users STABLE AS $$
franta-hg@5: 	use strict;
franta-hg@5: 	use warnings;
franta-hg@5: 	
franta-hg@7: 	use encoding "UTF-8";
franta-hg@5: 	use User::pwent;
franta-hg@5: 	
franta-hg@8: 	my $i = 0;
franta-hg@8: 	
franta-hg@9: 	setpwent();
franta-hg@5: 	while (my $user = getpwent()) {
franta-hg@5: 		return_next({
franta-hg@5: 			id => $user->uid,
franta-hg@5: 			gid => $user->gid,
franta-hg@5: 			name => $user->name,
franta-hg@5: 			# comment => $user->comment,
franta-hg@7: 			gecos => [split(",", $user->gecos)],
franta-hg@5: 			home => $user->dir,
franta-hg@5: 			shell => $user->shell,
franta-hg@5: 			# expire => $user->expire
franta-hg@5: 		});
franta-hg@5: 	}
franta-hg@9: 	endpwent();
franta-hg@8: 	
franta-hg@5: 	return undef;
franta-hg@5: $$ LANGUAGE plperlu;
franta-hg@5: 
franta-hg@5: CREATE OR REPLACE VIEW users AS
franta-hg@5: 	SELECT * FROM users()
franta-hg@5: ;
franta-hg@5: 
franta-hg@8: 
franta-hg@8: -- users_groups: -------------------------------------------------------------
franta-hg@8: 
franta-hg@9: CREATE OR REPLACE VIEW users_groups AS
franta-hg@8: 	SELECT
franta-hg@8: 		u1.id AS uid,
franta-hg@8: 		u1.gid AS gid,
franta-hg@8: 		u1.name AS user,
franta-hg@8: 		g1.name AS group,
franta-hg@9: 		true AS initial
franta-hg@8: 	FROM users AS u1
franta-hg@8: 	JOIN groups AS g1 ON (u1.gid = g1.id)
franta-hg@9: 	UNION
franta-hg@8: 	SELECT
franta-hg@8: 		u2.id AS uid,
franta-hg@8: 		g2.*,
franta-hg@9: 		false AS initial
franta-hg@8: 	FROM
franta-hg@8: 		(SELECT
franta-hg@8: 			id AS gid,
franta-hg@8: 			unnest(members) AS user,
franta-hg@8: 			name AS group
franta-hg@8: 		FROM groups) AS g2
franta-hg@8: 	JOIN users AS u2 ON (u2.name = g2.user)
franta-hg@8: ;
franta-hg@9: COMMENT ON COLUMN users_groups.initial IS 'whether this group is the „initial login group“ of given user (the primary group)';
franta-hg@8: