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