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@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@4: RETURNS SETOF unix_sql_api_groups AS $$
franta-hg@3: 	use strict;
franta-hg@3: 	use warnings;
franta-hg@5: 
franta-hg@3: 	use User::grent;
franta-hg@3: 
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@3: 	
franta-hg@5: 	elog(NOTICE, "members field does not contain users who have this group as primary one");
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@5: COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one';
franta-hg@3: 
franta-hg@5: 
franta-hg@5: -- user: ---------------------------------------------------------------------
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@5: RETURNS SETOF unix_sql_api_users AS $$
franta-hg@5: 	use strict;
franta-hg@5: 	use warnings;
franta-hg@5: 	
franta-hg@5: 	use Encode; # FIXME: see below
franta-hg@5: 	use User::pwent;
franta-hg@5: 	
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@5: 			gecos => [split(",", Encode::decode("utf8", $user->gecos))], # FIXME: ugly hack – should be properly decoded in getpwent()
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@5: 	}
franta-hg@5: 	
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: