prototyp/prototyp.sql
author František Kučera <franta-hg@frantovo.cz>
Mon, 01 Sep 2014 22:47:19 +0200
changeset 11 9ff9cd2d677a
parent 10 88bf2cb5e757
child 12 c6688467d03c
permissions -rw-r--r--
prototyp: processes – old version with /proc parsing
franta-hg@6
     1
-- SQL-UNIX-API (prototype)
franta-hg@6
     2
-- Copyright © 2014 František Kučera (frantovo.cz)
franta-hg@6
     3
-- 
franta-hg@6
     4
-- This program is free software: you can redistribute it and/or modify
franta-hg@6
     5
-- it under the terms of the GNU General Public License as published by
franta-hg@6
     6
-- the Free Software Foundation, either version 3 of the License, or
franta-hg@6
     7
-- (at your option) any later version.
franta-hg@6
     8
-- 
franta-hg@6
     9
-- This program is distributed in the hope that it will be useful,
franta-hg@6
    10
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
franta-hg@6
    11
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
franta-hg@6
    12
-- GNU General Public License for more details.
franta-hg@6
    13
-- 
franta-hg@6
    14
-- You should have received a copy of the GNU General Public License
franta-hg@6
    15
-- along with this program. If not, see <http://www.gnu.org/licenses/>.
franta-hg@6
    16
franta-hg@6
    17
franta-hg@6
    18
franta-hg@2
    19
-- CREATE SCHEMA unix_sql_api;
franta-hg@2
    20
franta-hg@2
    21
SET search_path TO unix_sql_api;
franta-hg@2
    22
franta-hg@3
    23
franta-hg@3
    24
-- fstab: --------------------------------------------------------------------
franta-hg@3
    25
franta-hg@2
    26
DROP VIEW IF EXISTS fstab;
franta-hg@2
    27
DROP FUNCTION IF EXISTS fstab();
franta-hg@2
    28
DROP TYPE IF EXISTS unix_sql_api_fstab;
franta-hg@2
    29
franta-hg@2
    30
CREATE TYPE unix_sql_api_fstab AS (
franta-hg@2
    31
	device VARCHAR,
franta-hg@2
    32
	device_type VARCHAR,
franta-hg@2
    33
	device_value VARCHAR,
franta-hg@2
    34
	mount_point VARCHAR,
franta-hg@2
    35
	type VARCHAR,
franta-hg@2
    36
	types VARCHAR[],
franta-hg@2
    37
	options VARCHAR[],
franta-hg@2
    38
	dump INTEGER,
franta-hg@2
    39
	pass INTEGER
franta-hg@2
    40
);
franta-hg@2
    41
franta-hg@2
    42
CREATE OR REPLACE FUNCTION fstab()
franta-hg@2
    43
RETURNS SETOF unix_sql_api_fstab AS $$
franta-hg@2
    44
	use strict;
franta-hg@2
    45
	use warnings;
franta-hg@2
    46
	
franta-hg@2
    47
	open(FSTAB, "<", "/etc/fstab") or die $!;
franta-hg@2
    48
	
franta-hg@2
    49
	while (<FSTAB>) {
franta-hg@2
    50
		if (/^([^\s#]+)\s+([^\s]+)\s+([^\s]+)\s+([^\s]+)\s+(\d+)\s+(\d+)\s*$/) {
franta-hg@2
    51
			
franta-hg@2
    52
			my $device_spec_type;
franta-hg@2
    53
			my $device_spec_value;
franta-hg@2
    54
			{
franta-hg@2
    55
				if ($1 =~ /([^=]+)=(.*)/) {
franta-hg@2
    56
					($device_spec_type, $device_spec_value) = ($1, $2);
franta-hg@2
    57
				}
franta-hg@2
    58
			}
franta-hg@2
    59
			
franta-hg@2
    60
			return_next({
franta-hg@2
    61
				device => $1,
franta-hg@2
    62
				device_type => $device_spec_type,
franta-hg@2
    63
				device_value => $device_spec_value,
franta-hg@2
    64
				mount_point => $2,
franta-hg@2
    65
				type => $3,
franta-hg@2
    66
				types => [split(",", $3)],
franta-hg@2
    67
				options => [split(",", $4)],
franta-hg@2
    68
				dump => $5,
franta-hg@2
    69
				pass => $6
franta-hg@2
    70
			});
franta-hg@2
    71
		}
franta-hg@2
    72
	}
franta-hg@2
    73
	
franta-hg@2
    74
	return undef;
franta-hg@2
    75
$$ LANGUAGE plperlu;
franta-hg@2
    76
franta-hg@2
    77
CREATE OR REPLACE VIEW fstab AS
franta-hg@2
    78
	SELECT * FROM fstab()
franta-hg@3
    79
;
franta-hg@3
    80
franta-hg@3
    81
franta-hg@3
    82
-- user groups: --------------------------------------------------------------
franta-hg@3
    83
franta-hg@8
    84
DROP VIEW IF EXISTS users_groups;
franta-hg@8
    85
franta-hg@4
    86
DROP VIEW IF EXISTS groups;
franta-hg@4
    87
DROP FUNCTION IF EXISTS groups();
franta-hg@4
    88
DROP TYPE IF EXISTS unix_sql_api_groups;
franta-hg@3
    89
franta-hg@4
    90
CREATE TYPE unix_sql_api_groups AS (
franta-hg@3
    91
	id INTEGER,
franta-hg@3
    92
	name VARCHAR,
franta-hg@3
    93
	members VARCHAR[]
franta-hg@3
    94
);
franta-hg@3
    95
franta-hg@4
    96
CREATE OR REPLACE FUNCTION groups()
franta-hg@8
    97
RETURNS SETOF unix_sql_api_groups STABLE AS $$
franta-hg@3
    98
	use strict;
franta-hg@3
    99
	use warnings;
franta-hg@5
   100
franta-hg@3
   101
	use User::grent;
franta-hg@8
   102
	
franta-hg@9
   103
	setgrent();
franta-hg@5
   104
	while (my $group = getgrent()) {
franta-hg@3
   105
		return_next({
franta-hg@3
   106
			id => $group->gid,
franta-hg@3
   107
			name => $group->name,
franta-hg@3
   108
			members => [@{$group->members}]
franta-hg@3
   109
		});
franta-hg@3
   110
	}
franta-hg@9
   111
	endgrent();
franta-hg@5
   112
	
franta-hg@3
   113
	return undef;
franta-hg@3
   114
$$ LANGUAGE plperlu;
franta-hg@3
   115
franta-hg@4
   116
CREATE OR REPLACE VIEW groups AS
franta-hg@4
   117
	SELECT * FROM groups()
franta-hg@3
   118
;
franta-hg@9
   119
COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one – see view: users_groups';
franta-hg@3
   120
franta-hg@5
   121
franta-hg@8
   122
-- users: --------------------------------------------------------------------
franta-hg@5
   123
franta-hg@5
   124
DROP VIEW IF EXISTS users;
franta-hg@5
   125
DROP FUNCTION IF EXISTS users();
franta-hg@5
   126
DROP TYPE IF EXISTS unix_sql_api_users;
franta-hg@5
   127
franta-hg@5
   128
CREATE TYPE unix_sql_api_users AS (
franta-hg@5
   129
	id INTEGER,
franta-hg@5
   130
	gid INTEGER,
franta-hg@5
   131
	name VARCHAR,
franta-hg@5
   132
	-- comment VARCHAR,
franta-hg@5
   133
	gecos VARCHAR[],
franta-hg@5
   134
	home VARCHAR,
franta-hg@5
   135
	shell VARCHAR
franta-hg@5
   136
	-- expire VARCHAR
franta-hg@5
   137
);
franta-hg@5
   138
franta-hg@5
   139
CREATE OR REPLACE FUNCTION users()
franta-hg@8
   140
RETURNS SETOF unix_sql_api_users STABLE AS $$
franta-hg@5
   141
	use strict;
franta-hg@5
   142
	use warnings;
franta-hg@5
   143
	
franta-hg@7
   144
	use encoding "UTF-8";
franta-hg@5
   145
	use User::pwent;
franta-hg@5
   146
	
franta-hg@9
   147
	setpwent();
franta-hg@5
   148
	while (my $user = getpwent()) {
franta-hg@5
   149
		return_next({
franta-hg@5
   150
			id => $user->uid,
franta-hg@5
   151
			gid => $user->gid,
franta-hg@5
   152
			name => $user->name,
franta-hg@5
   153
			# comment => $user->comment,
franta-hg@7
   154
			gecos => [split(",", $user->gecos)],
franta-hg@5
   155
			home => $user->dir,
franta-hg@5
   156
			shell => $user->shell,
franta-hg@5
   157
			# expire => $user->expire
franta-hg@5
   158
		});
franta-hg@5
   159
	}
franta-hg@9
   160
	endpwent();
franta-hg@8
   161
	
franta-hg@5
   162
	return undef;
franta-hg@5
   163
$$ LANGUAGE plperlu;
franta-hg@5
   164
franta-hg@5
   165
CREATE OR REPLACE VIEW users AS
franta-hg@5
   166
	SELECT * FROM users()
franta-hg@5
   167
;
franta-hg@5
   168
franta-hg@8
   169
franta-hg@8
   170
-- users_groups: -------------------------------------------------------------
franta-hg@8
   171
franta-hg@9
   172
CREATE OR REPLACE VIEW users_groups AS
franta-hg@8
   173
	SELECT
franta-hg@8
   174
		u1.id AS uid,
franta-hg@8
   175
		u1.gid AS gid,
franta-hg@8
   176
		u1.name AS user,
franta-hg@8
   177
		g1.name AS group,
franta-hg@9
   178
		true AS initial
franta-hg@8
   179
	FROM users AS u1
franta-hg@8
   180
	JOIN groups AS g1 ON (u1.gid = g1.id)
franta-hg@9
   181
	UNION
franta-hg@8
   182
	SELECT
franta-hg@8
   183
		u2.id AS uid,
franta-hg@8
   184
		g2.*,
franta-hg@9
   185
		false AS initial
franta-hg@8
   186
	FROM
franta-hg@8
   187
		(SELECT
franta-hg@8
   188
			id AS gid,
franta-hg@8
   189
			unnest(members) AS user,
franta-hg@8
   190
			name AS group
franta-hg@8
   191
		FROM groups) AS g2
franta-hg@8
   192
	JOIN users AS u2 ON (u2.name = g2.user)
franta-hg@8
   193
;
franta-hg@9
   194
COMMENT ON COLUMN users_groups.initial IS 'whether this group is the „initial login group“ of given user (the primary group)';
franta-hg@8
   195
franta-hg@10
   196
franta-hg@10
   197
-- processes: ----------------------------------------------------------------
franta-hg@10
   198
franta-hg@10
   199
DROP VIEW IF EXISTS processes;
franta-hg@10
   200
DROP FUNCTION IF EXISTS processes();
franta-hg@10
   201
DROP TYPE IF EXISTS unix_sql_api_processes;
franta-hg@10
   202
franta-hg@10
   203
CREATE TYPE unix_sql_api_processes AS (
franta-hg@10
   204
	id INTEGER,
franta-hg@11
   205
	owner INTEGER,
franta-hg@10
   206
	command VARCHAR,
franta-hg@10
   207
	arguments VARCHAR[],
franta-hg@10
   208
	working_dir VARCHAR
franta-hg@10
   209
);
franta-hg@10
   210
franta-hg@10
   211
CREATE OR REPLACE FUNCTION processes()
franta-hg@10
   212
RETURNS SETOF unix_sql_api_processes STABLE AS $$
franta-hg@10
   213
	use strict;
franta-hg@10
   214
	use warnings;
franta-hg@10
   215
	
franta-hg@11
   216
	# aptitude install libproc-processtable-perl
franta-hg@11
   217
	# TODO: use Proc::ProcessTable::Process;
franta-hg@10
   218
	
franta-hg@11
   219
	my $dir = "/proc";
franta-hg@11
   220
	
franta-hg@11
   221
	opendir(DIR, $dir) or die $!;
franta-hg@11
   222
	while (readdir(DIR)) {
franta-hg@11
   223
		if (/\d+/) {
franta-hg@11
   224
			my $pid = $_;
franta-hg@11
   225
			my @process_dir_stat = stat("$dir/$pid") or next;
franta-hg@11
   226
			my $uid = $process_dir_stat[4];
franta-hg@11
   227
			
franta-hg@11
   228
			open(CMDLINE, "<$dir/$pid/cmdline");
franta-hg@11
   229
			binmode CMDLINE;
franta-hg@11
   230
			my $cmdline;
franta-hg@11
   231
			read(CMDLINE, $cmdline, 65536);
franta-hg@11
   232
			
franta-hg@11
   233
			my @cmdline_parts = split(0x00, $cmdline);
franta-hg@11
   234
			
franta-hg@11
   235
			return_next({
franta-hg@11
   236
				id => $pid,
franta-hg@11
   237
				owner => $uid,
franta-hg@11
   238
				command => readlink("$dir/$pid/exe"),
franta-hg@11
   239
				arguments => ["xxx"],
franta-hg@11
   240
				working_dir => readlink("$dir/$pid/cwd")
franta-hg@11
   241
			});
franta-hg@11
   242
		}
franta-hg@11
   243
	}
franta-hg@10
   244
	
franta-hg@10
   245
	return undef;
franta-hg@10
   246
$$ LANGUAGE plperlu;
franta-hg@10
   247
franta-hg@10
   248
CREATE OR REPLACE VIEW processes AS
franta-hg@10
   249
	SELECT * FROM processes()
franta-hg@10
   250
;
franta-hg@10
   251