prototyp/prototyp.sql
author František Kučera <franta-hg@frantovo.cz>
Mon, 01 Sep 2014 18:10:25 +0200
changeset 7 8f0e2d417702
parent 6 dc83d208e862
child 8 0f6df6850ba6
permissions -rw-r--r--
prototyp: UTF-8 encoding (decoding)
     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 groups;
    85 DROP FUNCTION IF EXISTS groups();
    86 DROP TYPE IF EXISTS unix_sql_api_groups;
    87 
    88 CREATE TYPE unix_sql_api_groups AS (
    89 	id INTEGER,
    90 	name VARCHAR,
    91 	members VARCHAR[]
    92 );
    93 
    94 CREATE OR REPLACE FUNCTION groups()
    95 RETURNS SETOF unix_sql_api_groups AS $$
    96 	use strict;
    97 	use warnings;
    98 
    99 	use User::grent;
   100 
   101 	while (my $group = getgrent()) {
   102 		return_next({
   103 			id => $group->gid,
   104 			name => $group->name,
   105 			members => [@{$group->members}]
   106 		});
   107 	}
   108 	
   109 	elog(NOTICE, "members field does not contain users who have this group as primary one");
   110 	
   111 	return undef;
   112 $$ LANGUAGE plperlu;
   113 
   114 CREATE OR REPLACE VIEW groups AS
   115 	SELECT * FROM groups()
   116 ;
   117 COMMENT ON COLUMN groups.members IS 'does not contain users who have this group as primary one';
   118 
   119 
   120 -- user: ---------------------------------------------------------------------
   121 
   122 DROP VIEW IF EXISTS users;
   123 DROP FUNCTION IF EXISTS users();
   124 DROP TYPE IF EXISTS unix_sql_api_users;
   125 
   126 CREATE TYPE unix_sql_api_users AS (
   127 	id INTEGER,
   128 	gid INTEGER,
   129 	name VARCHAR,
   130 	-- comment VARCHAR,
   131 	gecos VARCHAR[],
   132 	home VARCHAR,
   133 	shell VARCHAR
   134 	-- expire VARCHAR
   135 );
   136 
   137 CREATE OR REPLACE FUNCTION users()
   138 RETURNS SETOF unix_sql_api_users AS $$
   139 	use strict;
   140 	use warnings;
   141 	
   142 	use encoding "UTF-8";
   143 	use User::pwent;
   144 	
   145 	while (my $user = getpwent()) {
   146 		return_next({
   147 			id => $user->uid,
   148 			gid => $user->gid,
   149 			name => $user->name,
   150 			# comment => $user->comment,
   151 			gecos => [split(",", $user->gecos)],
   152 			home => $user->dir,
   153 			shell => $user->shell,
   154 			# expire => $user->expire
   155 			
   156 		});
   157 	}
   158 	
   159 	return undef;
   160 $$ LANGUAGE plperlu;
   161 
   162 CREATE OR REPLACE VIEW users AS
   163 	SELECT * FROM users()
   164 ;
   165