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 . 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@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 () { 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@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@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@8: my $i = 0; franta-hg@3: 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@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@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@8: my $i = 0; franta-hg@8: 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@8: u1.id AS uid, franta-hg@8: u1.gid AS gid, franta-hg@8: u1.name AS user, franta-hg@8: g1.name AS group, franta-hg@9: true AS initial franta-hg@8: FROM users AS u1 franta-hg@8: JOIN groups AS g1 ON (u1.gid = g1.id) franta-hg@9: UNION franta-hg@8: SELECT franta-hg@8: u2.id AS uid, franta-hg@8: g2.*, franta-hg@9: false AS initial franta-hg@8: FROM franta-hg@8: (SELECT franta-hg@8: id AS gid, franta-hg@8: unnest(members) AS user, franta-hg@8: name AS group franta-hg@8: FROM groups) AS g2 franta-hg@8: JOIN users AS u2 ON (u2.name = g2.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: