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@13: 	device         VARCHAR,
franta-hg@13: 	device_type    VARCHAR,
franta-hg@13: 	device_value   VARCHAR,
franta-hg@13: 	mount_point    VARCHAR,
franta-hg@13: 	type           VARCHAR,
franta-hg@13: 	types          VARCHAR[],
franta-hg@13: 	options        VARCHAR[],
franta-hg@13: 	dump           INTEGER,
franta-hg@13: 	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@13: 	id        INTEGER,
franta-hg@13: 	name      VARCHAR,
franta-hg@13: 	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@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@13: 	id           INTEGER,
franta-hg@13: 	gid          INTEGER,
franta-hg@13: 	name         VARCHAR,
franta-hg@13: 	-- comment   VARCHAR,
franta-hg@13: 	gecos        VARCHAR[],
franta-hg@13: 	home         VARCHAR,
franta-hg@13: 	shell        VARCHAR
franta-hg@13: 	-- 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@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@13: 		u.id     AS uid,
franta-hg@13: 		u.gid    AS gid,
franta-hg@13: 		u.name   AS user,
franta-hg@13: 		g.name   AS group,
franta-hg@13: 		true     AS initial
franta-hg@13: 	FROM users AS u
franta-hg@13: 	JOIN groups AS g ON (u.gid = g.id)
franta-hg@9: 	UNION
franta-hg@8: 	SELECT
franta-hg@13: 		u.id     AS uid,
franta-hg@13: 		g.*,
franta-hg@13: 		false    AS initial
franta-hg@8: 	FROM
franta-hg@8: 		(SELECT
franta-hg@13: 			id                AS gid,
franta-hg@13: 			unnest(members)   AS user,
franta-hg@13: 			name              AS group
franta-hg@13: 		FROM groups) AS g
franta-hg@13: 	JOIN users AS u ON (u.name = g.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: 
franta-hg@10: 
franta-hg@10: -- processes: ----------------------------------------------------------------
franta-hg@10: 
franta-hg@10: DROP VIEW IF EXISTS processes;
franta-hg@10: DROP FUNCTION IF EXISTS processes();
franta-hg@10: DROP TYPE IF EXISTS unix_sql_api_processes;
franta-hg@10: 
franta-hg@10: CREATE TYPE unix_sql_api_processes AS (
franta-hg@13: 	id            INTEGER,
franta-hg@13: 	owner         INTEGER,
franta-hg@13: 	command       VARCHAR,
franta-hg@13: 	arguments     VARCHAR[],
franta-hg@13: 	working_dir   VARCHAR
franta-hg@10: );
franta-hg@10: 
franta-hg@10: CREATE OR REPLACE FUNCTION processes()
franta-hg@10: RETURNS SETOF unix_sql_api_processes STABLE AS $$
franta-hg@10: 	use strict;
franta-hg@10: 	use warnings;
franta-hg@10: 	
franta-hg@12: 	# There is a library…
franta-hg@11: 	# aptitude install libproc-processtable-perl
franta-hg@12: 	# use Proc::ProcessTable::Process;
franta-hg@12: 	# But there can be found:
franta-hg@12: 	# /* replace all '\0' with spaces (except for the last one */
franta-hg@12: 	# for (cur = cmndline_text; cur < cmndline_text + cmndline_off - 1; cur++) {
franta-hg@12: 	# if (*cur == '\0')
franta-hg@12: 	# 	*cur = ' ';
franta-hg@12: 	# }
franta-hg@12: 	# which wipes boundaries between arguments (that can also contain spaces)
franta-hg@12: 	# So wait until the patch https://rt.cpan.org/Public/Bug/Display.html?id=52442 will be integrated
franta-hg@10: 	
franta-hg@11: 	my $dir = "/proc";
franta-hg@11: 	
franta-hg@11: 	opendir(DIR, $dir) or die $!;
franta-hg@11: 	while (readdir(DIR)) {
franta-hg@11: 		if (/\d+/) {
franta-hg@11: 			my $pid = $_;
franta-hg@11: 			my @process_dir_stat = stat("$dir/$pid") or next;
franta-hg@11: 			my $uid = $process_dir_stat[4];
franta-hg@11: 			
franta-hg@11: 			open(CMDLINE, "<$dir/$pid/cmdline");
franta-hg@11: 			binmode CMDLINE;
franta-hg@12: 			my @cmdline = split("\x00", <CMDLINE>);
franta-hg@11: 			
franta-hg@11: 			return_next({
franta-hg@11: 				id => $pid,
franta-hg@11: 				owner => $uid,
franta-hg@12: 				command => shift(@cmdline),
franta-hg@12: 				arguments => [@cmdline],
franta-hg@11: 				working_dir => readlink("$dir/$pid/cwd")
franta-hg@11: 			});
franta-hg@11: 		}
franta-hg@11: 	}
franta-hg@10: 	
franta-hg@10: 	return undef;
franta-hg@10: $$ LANGUAGE plperlu;
franta-hg@10: 
franta-hg@10: CREATE OR REPLACE VIEW processes AS
franta-hg@10: 	SELECT * FROM processes()
franta-hg@10: ;
franta-hg@10: