Index: ossp-pkg/sdb/.cvsignore RCS File: /v/ossp/cvs/ossp-pkg/sdb/.cvsignore,v co -q -kk -p'1.1' '/v/ossp/cvs/ossp-pkg/sdb/.cvsignore,v' | diff -u /dev/null - -L'ossp-pkg/sdb/.cvsignore' 2>/dev/null --- ossp-pkg/sdb/.cvsignore +++ - 2025-04-04 17:34:14.383294356 +0200 @@ -0,0 +1 @@ +sdb.db Index: ossp-pkg/sdb/Makefile RCS File: /v/ossp/cvs/ossp-pkg/sdb/Makefile,v co -q -kk -p'1.1' '/v/ossp/cvs/ossp-pkg/sdb/Makefile,v' | diff -u /dev/null - -L'ossp-pkg/sdb/Makefile' 2>/dev/null --- ossp-pkg/sdb/Makefile +++ - 2025-04-04 17:34:14.385973233 +0200 @@ -0,0 +1,8 @@ + +sdb.db: sdb.sql + sqlite sdb.db /dev/null --- ossp-pkg/sdb/sdb.cgi +++ - 2025-04-04 17:34:14.388613932 +0200 @@ -0,0 +1,961 @@ +#!/usr/opkg/bin/perl +## +## OSSP sdb -- Skill Database +## Copyright (c) 2003 The OSSP Project +## Copyright (c) 2003 Cable & Wireless Deutschland +## Copyright (c) 2003 Ralf S. Engelschall +## +## This file is part of OSSP sdb, a small skill database Web UI +## which can be found at http://www.ossp.org/pkg/tool/sdb/ +## +## This program is free software; you can redistribute it and/or +## modify it under the terms of the GNU General Public License +## as published by the Free Software Foundation; either version +## 2.0 of the License, or (at your option) any later version. +## +## This program is distributed in the hope that it will be useful, +## but WITHOUT ANY WARRANTY; without even the implied warranty of +## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +## General Public License for more details. +## +## You should have received a copy of the GNU General Public License +## along with this program; if not, write to the Free Software +## Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 +## USA, or contact The OSSP Project . +## +## sdb.cgi: skill database CGI (language: Perl) +## + +# requirements +require 5.006; +use POSIX; +use IO; +use CGI qw(:standard -nosticky -no_undef_params -oldstyle_urls -no_debug); +use DBI; +use DBD::SQLite; +use String::Divert; +use Data::Dumper; + +# program configuration +my $my = { + PROG_NAME => 'sdb', + PROG_VERS => '0.0.1', + PROG_DATE => '09-May-2003', + PROG_DESC => 'Skill Database', + TEMPLATE => "\n\n\@HEAD\@\n\n\n\@BODY\@\n" +}; + +# switch to unbuffered output +$|++; + +## _________________________________________________________________________ +## +## Helper Functions +## _________________________________________________________________________ +## + +# (un)escape URL chars +sub url_escape { + my ($text) = @_; + $text =~ s|([ \t&+?:/=\n\r])|sprintf("%%%02x", ord($1))|sge; + return $text; +} +sub url_unescape { + my ($text) = @_; + $text =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack('C', hex($1))/eg; + return $text; +} + +# (un)escape SQL chars +sub sql_escape { + my ($text) = @_; + $text =~ s|(.)|POSIX::isprint($1) ? $1 : "."|sge; + $text =~ s|([''])|$1$1|sg; + $text = "'" . $text . "'"; + return $text; +} +sub sql_unescape { + my ($text) = @_; + if ($text =~ m|^'(.*)'$|) { + $text = $1; + $text =~ s/''/'/sg; + } + return $text; +} + +## _________________________________________________________________________ +## +## Initialize Environment +## _________________________________________________________________________ +## + +# remove and remember GET query strings +my $qs = ''; +if ($ENV{QUERY_STRING} ne '') { + $qs = $ENV{QUERY_STRING}; + delete $ENV{QUERY_STRING}; +} + +# open CGI environment +my $cgi = new CGI; +$cgi->autoEscape(undef); + +# re-insert GET query strings into CGI environment +if ($qs ne '') { + foreach my $kv (split(/\&/, $qs)) { + if ($kv =~ m|^([^=]+)(?:=([^=]+))?$|s) { + my ($key, $val) = ($1, $2); + $val ||= 1; + $key = &url_unescape($key); + $val = &url_unescape($val); + $cgi->param($key, $val); + } + } +} + +# remember self-referencing URL +$my->{URL} = $cgi->url(-full => 1); + +# generate an HTTP response +sub http_response { + my ($head, $body) = @_; + + # fill in template page + my $response = $my->{TEMPLATE}; + if ($response =~ m|\n([^\n]*)\@HEAD\@|s) { + my $prefix = " " x length($1); + $head =~ s|\n(.)|\n$prefix$1|sg; + } + if ($response =~ m|\@HEAD\@[ \t]*\n|s) { + $head =~ s|\n$||s; + } + $response =~ s|\@HEAD\@|$head|sg; + if ($response =~ m|\n([^\n]*)\@BODY\@|s) { + my $prefix = " " x length($1); + $body =~ s|\n(.)|\n$prefix$1|sg; + } + if ($response =~ m|\@BODY\@[ \t]*\n|s) { + $body =~ s|\n$||s; + } + $response =~ s|\@BODY\@|$body|sg; + + # prepend with HTTP header + $response = $cgi->header( + -type => 'text/html', + -expires => 'now', + '-Content-length' => length($response) + ) . $response; + + return $response; +} + +# start output generation +my $head = ''; +my $body = ''; + +# optionally read HTML page template +if (-f "sdb.html") { + my $io = new IO::File "{TEMPLATE} = ''; + $my->{TEMPLATE} .= $_ while (<$io>); + $io->close(); +} + +# optionally activate CSS +if (-f "sdb.css") { + $head .= ""; +} + +# activate a general error handler +$SIG{__DIE__} = sub { + my ($err) = @_; + + $err =~ s|at\s+\S+\s+line\s+(\d+)|(sdb.cgi:$1)|s; + $err =~ s|\n|
\n|sg; + $err =~ s|\n+$||s; + $head .= "".$my->{PROG_NAME}.": ERROR"; + $body .= "

ERROR

" . + "The following error occured:

" . + "

$err
\n"; + # debugging + $body .= "
";
+    my @names = $cgi->param;
+    foreach my $name (@names) {
+        my $value = $cgi->param($name);
+        $body .= "$name=\"$value\"\n";
+    }
+    $body .= "
"; + print STDOUT &http_response($head, $body); + exit(0); +}; + +# open DB environment +my $db; +($db = DBI->connect("dbi:SQLite:dbname=sdb.db", "", "")) + || die "unable to SQLite database sdb.db"; +$db->{AutoCommit} = 1; +$db->{RaiseError} = 1; + +## _________________________________________________________________________ +## +## Stage 0: Determine Parameters +## _________________________________________________________________________ +## + +# list of UI display pages +my @ui_pages = qw(main person); + +# initialize UI display parameters +my $ui = {}; +foreach my $page (@ui_pages) { + my $found = 0; + foreach my $sub ( + "ui_${page}_init", + "ui_init" + ) { + if (defined(&$sub)) { + &$sub($my, $cgi, $db, $ui, $page); + $found = 1; + last; + } + } + die "no initialization handler found for page \"$page\"" + if (not $found); +} + +# determine UI display parameters +my @names = $cgi->param; +foreach my $name (@names) { + if ($name =~ m|^([^.]+)(?:\.([^.]+)(?:\.([^.]+))?)?$|s) { + my ($page, $area, $elem) = ($1, $2, $3); + if (defined($page)) { + $ui->{$page} = { -is => 'disable', -with => 'default' } + if (not defined($ui->{$page})); + } + if (defined($area)) { + $ui->{$page}->{$area} = { -is => 'disable', -with => 'default' } + if (not defined($ui->{$page}->{$area})); + } + if (defined($elem)) { + if ($name =~ m|\+$|s) { + my @values = $cgi->param($name); + $ui->{$page}->{$area}->{$elem} = [ @values ]; + } + else { + my $value = $cgi->param($name); + $ui->{$page}->{$area}->{$elem} = $value; + } + } + elsif (defined($area)) { + my $value = $cgi->param($name); + $value = "visible" if ($value eq "1"); + $ui->{$page}->{$area}->{-is} = $value; + } + elsif (defined($page)) { + my $value = $cgi->param($name); + $value = "visible" if ($value eq "1"); + $ui->{$page}->{-is} = $value; + } + $cgi->param($name, undef); + } +} + +## _________________________________________________________________________ +## +## Stage 1: Process Last Action +## _________________________________________________________________________ +## + +# FIXME +# determine default UI display +my $page = undef; +foreach my $p (keys(%{$ui})) { + if ($ui->{$p}->{-is} eq 'visible') { + $page = $p; + last; + } +} +if (not defined($page)) { + $ui->{main}->{-is} = 'visible'; + $ui->{main}->{all}->{-is} = 'visible'; +} + +# determine which button was pressed and +# perform an associated action (if defined) +foreach my $page (keys(%{$ui})) { + foreach my $area (keys(%{$ui->{$page}})) { + foreach my $elem (keys(%{$ui->{$page}->{$area}})) { + if ($elem =~ m|^[A-Z][A-Z0-9_-]*$|) { + if ($ui->{$page}->{$area}->{$elem}) { + my $found = 0; + foreach my $sub ( + "ui_${page}_${area}_${elem}_action", + "ui_${page}_${area}_action", + "ui_${page}_action", + "ui_action" + ) { + if (defined(&$sub)) { + &$sub($my, $cgi, $db, $ui, $page, $area, $elem); + $found = 1; + last; + } + } + die "no action handler found for element \"$page.$area.$elem\"" + if (not $found); + } + delete $ui->{$page}->{$area}->{$elem}; + } + } + } +} + +## _________________________________________________________________________ +## +## Stage 2: Output New Display +## _________________________________________________________________________ +## + +# start output generation +my $html = new String::Divert; +$html->overload(1); + +# generate outmost class +$html .= "\n"; +$html .= "
\n"; +$html .= " "; $html *= q{outmost}; +$html .= "
\n"; +$html .= "\n"; +$html >> q{outmost}; + +# generate outmost self-referencing form +$html .= $cgi->startform( + -method => "POST", + -action => $my->{URL}, +); +$html .= " "; $html *= q{form}; +$html .= $cgi->endform() . "\n"; +$html >> q{form}; + +# generate top-level header & footer +$html .= "

Skill Database

\n"; +$html .= ""; $html *= q{page}; +$html .= "

\n" . + "\n" . + " $my->{PROG_NAME} $my->{PROG_VERS} ($my->{PROG_DATE})\n" . + "\n"; +$html >> q{page}; + +# generate page contents +foreach my $page (sort(keys(%{$ui}))) { + if ($ui->{$page}->{-is} =~ m/^(visible|hidden)$/) { + my $found = 0; + foreach my $sub ( + "ui_${page}_render", + "ui_render" + ) { + if (defined(&$sub)) { + $html .= &$sub($my, $cgi, $db, $ui, $page); + $found = 1; + last; + } + } + die "no rendering handler found for page \"$page\"" + if (not $found); + } +} + +# insert html into output HTTP response body +$html->undivert(0); +$body .= $html->string(); + +# optional debugging +$body .= "

\n";
+my @names = $cgi->param;
+foreach my $name (@names) {
+    my $value = $cgi->param($name);
+    $body .= "  $name=\"$value\"\n";
+}
+$body .= "
\n"; + +# generate output +print STDOUT &http_response($head, $body); + +exit(0); + +## _________________________________________________________________________ +## +## Page: main +## _________________________________________________________________________ +## + +sub ui_main_init { + my ($my, $cgi, $db, $ui, $page) = @_; + + $ui->{main} = { -is => 'disable' }; + $ui->{main}->{all} = { -is => 'disable' }; +} + +sub ui_main_action { + my ($my, $cgi, $db, $ui, $page, $area, $elem) = @_; + + die "invalid action \"$page.$area.$elem\""; +} + +sub ui_main_render { + my ($my, $cgi, $db, $ui, $page) = @_; + + # start generating HTML + my $html = new String::Divert; + $html->overload(1); + + # generate outer class + $html .= "\n"; + $html .= " "; $html *= q{body}; + $html .= "\n"; + $html >> q{body}; + + # generate header + $html .= "

Main Menu

\n"; + + # generate main menu + $html .= + "\n"; + + # return unfolded HTML + $html->undivert(0); + return $html->string(); +} + +## _________________________________________________________________________ +## +## Page: person +## _________________________________________________________________________ +## + +sub ui_person_init { + my ($my, $cgi, $db, $ui, $page) = @_; + + $ui->{person} = { -is => 'disable' }; + $ui->{person}->{select} = { -is => 'disable' }; + $ui->{person}->{detail} = { -is => 'disable' }; + $ui->{person}->{skill} = { -is => 'disable' }; +} + +sub ui_person_action { + my ($my, $cgi, $db, $ui, $page, $area, $elem) = @_; + + # actions on select box + if ($ui->{person}->{select}->{ADD}) { + # just open detail area for addition + $ui->{person}->{detail}->{id} = undef; + $ui->{person}->{detail}->{-is} = 'visible'; + $ui->{person}->{detail}->{-with} = 'add'; + } + elsif ($ui->{person}->{select}->{VIEW}) { + # just open detail area for viewing + $ui->{person}->{detail}->{-is} = 'visible'; + $ui->{person}->{detail}->{-with} = 'view'; + } + elsif ($ui->{person}->{select}->{EDIT}) { + # just open detail area for editing + $ui->{person}->{detail}->{-is} = 'visible'; + $ui->{person}->{detail}->{-with} = 'edit'; + } + elsif ($ui->{person}->{select}->{DELETE}) { + # delete person from database + my $pe_id = $ui->{person}->{select}->{id}; + if ($pe_id eq '') { + die "no person selected"; + } + my @rv = $db->selectrow_array("SELECT pe_id FROM sdb_person WHERE pe_id = '$pe_id';"); + if (@rv == 0) { + die "invalid person \"$pe_id\" selected"; + } + $db->do(sprintf( + "DELETE FROM sdb_person WHERE pe_id = %s;", + &sql_escape($pe_id) + )); + $ui->{person}->{select}->{id} = ''; + $ui->{person}->{detail}->{-is} = 'remove'; + $ui->{person}->{skills}->{-is} = 'remove'; + } + + # actions on detail box + if ($ui->{person}->{detail}->{'SKILL-VIEW'}) { + # just open skill area for viewing + $ui->{person}->{skill}->{-is} = 'visible'; + $ui->{person}->{skill}->{-with} = 'view'; + } + elsif ($ui->{person}->{detail}->{'SKILL-EDIT'}) { + # just open skill area for editing + $ui->{person}->{skill}->{-is} = 'visible'; + $ui->{person}->{skill}->{-with} = 'edit'; + } + elsif ( $ui->{person}->{detail}->{CLOSE} + or $ui->{person}->{detail}->{CANCEL}) { + # just close detail area + $ui->{person}->{detail}->{-is} = 'remove'; + } + elsif ($ui->{person}->{detail}->{SAVE}) { + # save person details to database + my $pe = {}; + $pe->{id} = $ui->{person}->{select}->{id}; + foreach my $a (qw(name email phone)) { + $pe->{$a} = $ui->{person}->{detail}->{$a}; + } + my $te_ids = $ui->{person}->{detail}->{"membership+"}; + if ($pe->{id} eq '') { + # add new entry + my @rv = $db->selectrow_array(sprintf( + "SELECT pe_id FROM sdb_person WHERE pe_name = %s;", + &sql_escape($pe->{name}) + )); + if (@rv > 0) { + die "person \"$pe->{name}\" already existing -- delete first, please"; + } + $db->do(sprintf( + "INSERT INTO sdb_person (pe_name, pe_email, pe_phone) VALUES (%s, %s, %s);", + &sql_escape($pe->{name}), + &sql_escape($pe->{email}), + &sql_escape($pe->{phone}) + )); + foreach my $te_id (@{$te_ids}) { + $db->do(sprintf( + "INSERT INTO sdb_member (ms_pe_id, ms_te_id) VALUES (%s, %s);", + &sql_escape($pe->{id}), &sql_escape($te_id) + )); + } + } + else { + # modify existing entry + my @rv = $db->selectrow_array(sprintf( + "SELECT pe_id FROM sdb_person WHERE pe_id = %s;", + &sql_escape($pe->{id}) + )); + if (@rv == 0) { + die "person with id \"$pe->{id}\" not exists"; + } + $db->do(sprintf( + "UPDATE sdb_person" . + " SET pe_name = %s, pe_email = %s, pe_phone = %s" . + " WHERE pe_id = %s;", + &sql_escape($pe->{name}), + &sql_escape($pe->{email}), + &sql_escape($pe->{phone}), + &sql_escape($pe->{id}) + )); + $db->do(sprintf( + "DELETE FROM sdb_member WHERE ms_pe_id = %s;", + &sql_escape($pe->{id}) + )); + foreach my $te_id (@{$te_ids}) { + $db->do(sprintf( + "INSERT INTO sdb_member (ms_pe_id, ms_te_id) " . + " VALUES (%s, %s);", + &sql_escape($pe->{id}), + &sql_escape($te_id) + )); + } + } + } +} + +sub ui_person_render { + my ($my, $cgi, $db, $ui, $page) = @_; + + # start output generation + my $html = new String::Divert; + $html->overload(1); + + if ($ui->{person}->{-is} eq 'visible') { + # generate outer page CSS class + $html .= "\n"; + $html .= " "; $html *= q{person}; + $html .= "\n"; + $html >> q{person}; + + # generate header + $html .= "

Persons

\n"; + $html .= "{URL}\">← Back to Main Menu"; + + # generate page canvas + # +-------+-------+ + # | area1 | area2 | + # +-------+-------+ + # | area3 | + # +---------------+ + $html .= "

\n"; + $html .= "\n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= "
\n"; + $html .= " "; $html *= q{area1}; + $html .= " \n"; + $html .= "   \n"; + $html .= " \n"; + $html .= " "; $html *= q{area2}; + $html .= "
\n"; + $html .= "  \n"; + $html .= "
\n"; + $html .= " "; $html *= q{area3}; + $html .= "
\n"; + } + else { + $html *= q{area1}; + $html *= q{area2}; + $html *= q{area3}; + } + + ## + ## generate area: Person Selection + ## + if ($ui->{person}->{-is} eq 'visible') { + # force selection box to be always visible + # if whole area is visible + $ui->{person}->{select}->{-is} = 'visible'; + } + #$html->storage($ui->{person}->{select}->{-is} eq 'visible' ? 1 : 0); + if ($ui->{person}->{select}->{-is} eq 'visible') { + # generate CSS class + $html >> q{area1}; + $html .= "\n"; + $html .= " "; $html *= q{select}; + $html .= "\n"; + $html >> q{select}; + + # generate inner header + $html .= "

Select Person and Action

\n"; + + # generate inner canvas + $html .= "\n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= "
\n"; + $html .= " "; $html *= q{select-list}; + $html .= " \n"; + $html .= " "; $html *= q{select-buttons}; + $html .= "
\n"; + + # generate the selection list widget + $html >> q{select-list}; + my $rv = $db->selectall_arrayref( + "SELECT pe_id,pe_name FROM sdb_person ORDER BY pe_name;" + ); + my $pe_values = []; + my $pe_labels = {}; + foreach my $r (@{$rv}) { + push(@{$pe_values}, $r->[0]); + $pe_labels->{$r->[0]} = $r->[1]; + } + my $pe_default = $ui->{person}->{select}->{id} || $pe_values->[0]; + if (@{$pe_values} > 0) { + $html .= $cgi->scrolling_list( + -override => 1, + -name => 'person.select.id', + -values => $pe_values, + -labels => $pe_labels, + -default => $pe_default, + -size => 20, + -class => 'id', + ) . "\n"; + } + $html << q{select-list}; + + # generate the selection list attached buttons + $html >> "select-buttons"; + $html .= $cgi->submit( + -name => 'person.select.ADD', + -value => 'Add Person →', + -class => 'ADD' + ) . "
"; + if (@{$pe_values} > 0) { + $html .= $cgi->submit( + -name => 'person.select.VIEW', + -value => 'View Person →', + -class => 'VIEW' + ) . "
"; + $html .= $cgi->submit( + -name => 'person.select.EDIT', + -value => "Edit Person →", + -class => "EDIT" + ) . "
"; + $html .= $cgi->submit( + -name => 'person.select.DELETE', + -value => '← Delete Person', + -class => 'DELETE' + ) . "
"; + } + $html << q{select-buttons}; + + $html << q{area1}; + } + else { + $html >> q{area1}; + $html << q{area1}; + } + + # generate area: BEGIN PERSON DETAIL BOX + if ($ui->{person}->{detail}->{-is} eq 'visible') { + $html .= ""; + # determine content variant + my $action = $ui->{person}->{detail}->{-with}; + $html .= "

".uc(substr($action,0,1)).substr($action,1)." Person

"; + $html .= ""; + + # fetch person details + my $pe = undef; + if ($action eq 'view' or $action eq 'edit') { + my $id = $ui->{person}->{select}->{id}; + if ($id eq '') { + die "no person selected"; + } + $pe = $db->selectrow_hashref(sprintf( + "SELECT pe_id AS id, pe_name AS name, pe_email AS email, pe_phone AS phone" . + " FROM sdb_person WHERE pe_id = %s;", + &sql_escape($id) + )); + if (not defined($pe)) { + die "person with id \"$id\" not found"; + } + } + else { + $pe = { id => '', name => '', email => '', phone => '' }; + } + + # display person details + $html .= "\n"; + my $label = { + 'name' => 'Person Name', + 'email' => 'Email Address', + 'phone' => 'Phone Number' + }; + my $i = 0; + foreach my $a (qw(name email phone)) { + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $i = ($i + 1) % 2; + } + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + $html .= " \n"; + + $html .= " \n"; + $html .= " \n"; + $html .= " "; + $html .= " "; + $html .= "
\n"; + $html .= " ".$label->{$a}.":"; + $html .= " \n"; + if ($action eq 'view') { + $html .= $pe->{$a}; + } + else { + $html .= $cgi->textfield( + -override => 1, + -name => "person.detail.$a", + -default => $pe->{$a}, + -size => 40, + -maxlength => 80 + ); + } + $html .= "
Team Membership:\n"; + $html .= " \n"; + if ($action eq 'view') { + my $te_all = $db->selectcol_arrayref(sprintf( + "SELECT te_name FROM sdb_team,sdb_member" . + " WHERE te_id = ms_te_id AND ms_pe_id = %s" . + " ORDER BY te_name;", + &sql_escape($pe->{id}) + )); + if (@{$te_all} == 0) { + $html .= "-none-"; + } + else { + for (my $i = 0; $i < @{$te_all}; $i++) { + my $te_name = $te_all->[$i]; + $html .= ", " if ($i != 0); + $html .= $te_name; + } + } + } + else { + my $te_values = []; + my $te_labels = {}; + my $te_all = $db->selectall_arrayref( + "SELECT te_id, te_name FROM sdb_team ORDER BY te_name;" + ); + foreach my $r (@{$te_all}) { + push(@{$te_values}, $r->[0]); + $te_labels->{$r->[0]} = $r->[1]; + } + my $te_defaults = []; + if ($action eq 'edit') { + $te_defaults = $db->selectcol_arrayref(sprintf( + "SELECT ms_te_id FROM sdb_member WHERE ms_pe_id = %s;", + &sql_escape($pe->{id}) + )); + }; + if (@{$te_values} == 0) { + $html .= "-none-"; + } + else { + $html .= $cgi->scrolling_list( + -override => 1, + -name => 'person.detail.membership+', + -values => $te_values, + -labels => $te_labels, + -default => $te_defaults, + -multiple => 'true', + -size => 10, + -class => 'membership', + ); + } + } + $html .= "
\n"; + + $html .= ""; + if ($action eq 'view') { + $html .= ""; + $html .= ""; + } + else { + $html .= ""; + $html .= ""; + $html .= ""; + } + $html .= "
" . $cgi->submit( + -name => 'person.detail.CLOSE', + -value => '← Close', + -class => 'CLOSE' + ) . "" . $cgi->submit( + -name => 'person.detail.SKILL-VIEW', + -value => 'View Skills ↓', + -class => 'SKILL-VIEW' + ) . "" . $cgi->submit( + -name => 'person.detail.CANCEL', + -value => '← Cancel', + -class => 'CANCEL' + ) . "" . $cgi->submit( + -name => 'person.detail.SAVE', + -value => '↑ Save', + -class => 'SAVE' + ) . "" . $cgi->submit( + -name => 'person.detail.SKILL-EDIT', + -value => 'Edit Skills ↓', + -class => 'SKILL-EDIT' + ) . "
"; + + $html .= "
\n"; + $html .= ""; + $html .= ""; + } + # END PERSON DETAIL BOX + + if ($edit eq 'rate' or $do eq 'view-skill') { + if ($edit eq 'rate') { + $html .= "

Edit Skills

"; + } + else { + $html .= "

View Skills

"; + } + + my $pe_id = $cgi->param('person.id'); + if ($pe_id eq '') { + die "no person selected"; + } + my $rv = $db->selectall_arrayref( + "SELECT sk_id,sk_name" . + " FROM sdb_skill " . + " ORDER BY sk_name;" + ); + my $deg = $db->selectall_hashref( + "SELECT sk_id,as_degree" . + " FROM sdb_skill,sdb_provide" . + " WHERE as_pe_id = '$pe_id' AND as_sk_id = sk_id;", + 'sk_id' + ); + + $html .= "\n"; + $html .= ""; + $html .= ""; + $html .= "\n"; + $html .= "
"; + + $html .= "\n"; + my $i = 0; + my $n = 0; + my $med = sprintf("%d", ($#{$rv}+1)/2); + $html .= $cgi->hidden(-name => 'rate.pe_id', -default => $pe_id); + foreach my $sk (@{$rv}) { + if ($n == $med) { + $html .= "
"; + $html .= "		"; + $html .= "  "; + $html .= ""; + $html .= "\n"; + } + $html .= "\n"; + $html .= "\n"; + $html .= "\n"; + $html .= "\n"; + $i = ($i + 1) % 2; + $n++; + } + $html .= "\n"; + $html .= "
\n"; + $html .= $sk->[1]."  "; + $html .= "\n"; + my $default = $deg->{$sk->[0]}->{as_degree} || 0; + my $labels = { 0 => 'unknown', 1 => 'beginner', 2 => 'intermediate', 3 => 'advanced', 4 => 'expert' }; + if ($do eq 'view-skill') { + $html .= $labels->{$default}; + } + else { + $html .= $cgi->scrolling_list( + -name => "rate.$sk->[0]", + -values => [ 0, 1, 2, 3, 4 ], + -labels => $labels, + -default => $default, + -size => 1, + -class => "sdb-input-rate-$i", + -style => 'width: 100%;', + ); + } + $html .= "
\n"; + $html .= ""; + if ($do eq 'view-skill') { + $html .= ""; + } + else { + $html .= ""; + $html .= ""; + } + $html .= "
".$cgi->submit(-name => 'do.close-skill', -value => "Close", -class => "sdb-button-view")."".$cgi->submit(-name => 'rate.save', -value => "Save", -class => "sdb-button-save")."".$cgi->submit(-name => 'rate.cancel', -value => "Cancel", -class => "sdb-button-cancel")."
"; + $html .= "
\n"; + + $html .= "
\n"; + + } + + $html->undivert(0); + return $html->string(); +} Index: ossp-pkg/sdb/sdb.css RCS File: /v/ossp/cvs/ossp-pkg/sdb/sdb.css,v co -q -kk -p'1.1' '/v/ossp/cvs/ossp-pkg/sdb/sdb.css,v' | diff -u /dev/null - -L'ossp-pkg/sdb/sdb.css' 2>/dev/null --- ossp-pkg/sdb/sdb.css +++ - 2025-04-04 17:34:14.391880089 +0200 @@ -0,0 +1,101 @@ +/* +** OSSP sdb -- Skill Database +** Copyright (c) 2003 The OSSP Project +** Copyright (c) 2003 Cable & Wireless Deutschland +** Copyright (c) 2003 Ralf S. Engelschall +** +** This file is part of OSSP sdb, a small skill database Web UI +** which can be found at http://www.ossp.org/pkg/tool/sdb/ +** +** This program is free software; you can redistribute it and/or +** modify it under the terms of the GNU General Public License +** as published by the Free Software Foundation; either version +** 2.0 of the License, or (at your option) any later version. +** +** This program is distributed in the hope that it will be useful, +** but WITHOUT ANY WARRANTY; without even the implied warranty of +** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +** General Public License for more details. +** +** You should have received a copy of the GNU General Public License +** along with this program; if not, write to the Free Software +** Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 +** USA, or contact The OSSP Project . +** +** sdb.css: skill database style-sheet (language: CSS) +*/ + +/* general settings */ +.sdb { background: #ffffff; color: #000000; + font-family: helvetica,lucida,arial,sans-serif; } + +/* outmost canvas */ +DIV.sdb { padding: 10px; + padding-left: 40px; + padding-right: 40px; + border-style: solid; + border-width: 1; + border-color: #000000; } + +/* outmost canvas footer */ +.sdb .footer { color: #c0c0c0; } + +/* general headlines */ +.sdb H1 { font-weight: bold; + font-size: 220%; + font-family: tahoma,helvetica,lucida,arial,sans-serif; + color: #666699; } +.sdb H2 { font-weight: bold; + font-size: 160%; + font-family: helvetica,lucida,arial,sans-serif; } +.sdb H3 { font-weight: bold; + font-size: 120%; + font-family: helvetica,lucida,arial,sans-serif; } + +/* anchors */ +.sdb A { text-decoration: none; font-weight: bold; } +.sdb A:link { color: #666699; } +.sdb A:visited { color: #666699; } +.sdb A:hover { color: #666699; text-decoration: underline; } + +/* error page */ +.sdb PRE.error { border-width: 1; border-color: #cc3333; border-style: solid; + padding: 10px; background: #fff0f0; color: #000000; } + +.sdb INPUT { background: #fafafa; + border-color: #999999; + border-width: 1; + /* -moz-border-radius: 6px; */ } + +.sdb SELECT { background: #fafafa; + border-color: #999999; + border-width: 1; } + +.sdb TD.box { border-width: 1; border-color: #dddddd; border-style: dashed; + padding: 10px; } + +/* person.select.* */ +.sdb .person .select SELECT.id { background: #ffffff; width: 100%; } +.sdb .person .select INPUT.ADD { background: #f0f8f0; width: 100%; } +.sdb .person .select INPUT.VIEW { background: #f0f0f0; width: 100%; font-weight: bold; } +.sdb .person .select INPUT.EDIT { background: #f0f0ff; width: 100%; } +.sdb .person .select INPUT.DELETE { background: #fff0f0; width: 100%; } + +/* person.detail.* */ +.sdb .person .detail SELECT.membership { background: #ffffff; width: 100%; } +.sdb .person .detail INPUT.CLOSE { background: #f0f0f0; width: 100%; } +.sdb .person .detail INPUT.SKILL-VIEW { background: #f0f0f0; width: 100%; font-weight: bold; } +.sdb .person .detail INPUT.SAVE { background: #f0f8f0; width: 100%; font-weight: bold; } +.sdb .person .detail INPUT.CANCEL { background: #f8f0f0; width: 100%; } +.sdb .person .detail INPUT.SKILL-EDIT { background: #f0f0f0; width: 100%; } +.sdb .person .detail INPUT { background: #ffffff; } +.sdb .person .detail .label { background: #f8f8f8; padding: 4px; padding-left: 10px; border: 1px solid #dddddd; -moz-border-radius-topleft: 5px; -moz-border-radius-bottomleft: 5px; } +.sdb .person .detail .add .label { background: #f0f8f0; border-color: #cceecc; } +.sdb .person .detail .edit .label { background: #f0f0ff; border-color: #ccccff; } +.sdb .person .detail .content { border-bottom: 1px solid #dddddd; padding-left: 4px; } + +TR.sdb-input-rate-0 { background: #f5f5f5; } +TR.sdb-input-rate-1 { background: #ffffff; } +.sdb-input-rate-0 SELECT { background: #f5f5f5; font-size: 80%; } +.sdb-input-rate-1 SELECT { background: #ffffff; font-size: 80%; } + Index: ossp-pkg/sdb/sdb.html RCS File: /v/ossp/cvs/ossp-pkg/sdb/sdb.html,v co -q -kk -p'1.1' '/v/ossp/cvs/ossp-pkg/sdb/sdb.html,v' | diff -u /dev/null - -L'ossp-pkg/sdb/sdb.html' 2>/dev/null --- ossp-pkg/sdb/sdb.html +++ - 2025-04-04 17:34:14.394557662 +0200 @@ -0,0 +1,8 @@ + + + @HEAD@ + + + @BODY@ + + Index: ossp-pkg/sdb/sdb.sql RCS File: /v/ossp/cvs/ossp-pkg/sdb/sdb.sql,v co -q -kk -p'1.1' '/v/ossp/cvs/ossp-pkg/sdb/sdb.sql,v' | diff -u /dev/null - -L'ossp-pkg/sdb/sdb.sql' 2>/dev/null --- ossp-pkg/sdb/sdb.sql +++ - 2025-04-04 17:34:14.397131141 +0200 @@ -0,0 +1,150 @@ +-- +-- OSSP sdb -- Skill Database +-- Copyright (c) 2003 The OSSP Project +-- Copyright (c) 2003 Cable & Wireless Deutschland +-- Copyright (c) 2003 Ralf S. Engelschall +-- +-- This file is part of OSSP sdb, a small skill database Web UI +-- which can be found at http://www.ossp.org/pkg/tool/sdb/ +-- +-- This program is free software; you can redistribute it and/or +-- modify it under the terms of the GNU General Public License +-- as published by the Free Software Foundation; either version +-- 2.0 of the License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +-- General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 +-- USA, or contact The OSSP Project . +-- +-- sdb.sql: skill database schema (language: SQLite SQL) +-- + +DROP TABLE sdb_person; +DROP TABLE sdb_team; +DROP TABLE sdb_skill; +DROP TABLE sdb_member; +DROP TABLE sdb_provide; + +CREATE TABLE sdb_person ( + pe_id INTEGER PRIMARY KEY, + pe_name TEXT, + pe_email TEXT, + pe_phone TEXT +); + +CREATE TABLE sdb_team ( + te_id INTEGER PRIMARY KEY, + te_name TEXT +); + +CREATE TABLE sdb_skill ( + sk_id INTEGER PRIMARY KEY, + sk_name TEXT +); + +CREATE TABLE sdb_member ( + ms_pe_id INTEGER, + ms_te_id INTEGER +); + +CREATE TABLE sdb_provide ( + as_pe_id INTEGER, + as_sk_id INTEGER, + as_degree INTEGER +); + +INSERT INTO sdb_person VALUES (1, 'Peter Kajinski', 'peter@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (2, 'Ralf S. Engelschall', 'rse@de.cw.com', '+49-89-92699-251'); +INSERT INTO sdb_person VALUES (3, 'Thomas Lotterer', 'thl@.dev.de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (4, 'Michael Schloh', 'ms@.dev.de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (5, 'Michael van Elst', 'mlelstv@.dev.de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (6, 'Christoph Schug', 'cschug@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (7, 'Christian Muschiol', 'cmuschio@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (8, 'Alexander Wägner', 'awaegner@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (9, 'Manuel Hendel', 'mhendel@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (10, 'Jost Blachnitzky', 'scholli@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (11, 'Thomas Rohde', 'rohde@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (12, 'Christian Botta', 'cbotta@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (13, 'Stephan Gans', 'sgans@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (14, 'Sebastian Gierth', 'sgierth@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (15, 'Andrea Sikeler', 'asikeler@de.cw.com', '+49-89-92699-xxx'); +INSERT INTO sdb_person VALUES (16, 'Christian Scheithauer', 'cscheith@de.cw.com', '+49-89-92699-xxx'); + +INSERT INTO sdb_team VALUES (1, 'IS'); +INSERT INTO sdb_team VALUES (2, 'IS/Dev'); +INSERT INTO sdb_team VALUES (3, 'IS/Hst'); +INSERT INTO sdb_team VALUES (4, 'IS/DB'); +INSERT INTO sdb_team VALUES (5, 'IS/Man'); + +INSERT INTO sdb_member VALUES (1, 1); +INSERT INTO sdb_member VALUES (2, 1); +INSERT INTO sdb_member VALUES (3, 1); +INSERT INTO sdb_member VALUES (4, 1); +INSERT INTO sdb_member VALUES (5, 1); +INSERT INTO sdb_member VALUES (6, 1); +INSERT INTO sdb_member VALUES (7, 1); +INSERT INTO sdb_member VALUES (8, 1); +INSERT INTO sdb_member VALUES (9, 1); +INSERT INTO sdb_member VALUES (10, 1); +INSERT INTO sdb_member VALUES (11, 1); +INSERT INTO sdb_member VALUES (12, 1); +INSERT INTO sdb_member VALUES (13, 1); +INSERT INTO sdb_member VALUES (14, 1); +INSERT INTO sdb_member VALUES (15, 1); +INSERT INTO sdb_member VALUES (16, 1); + +INSERT INTO sdb_member VALUES (2, 2); +INSERT INTO sdb_member VALUES (3, 2); +INSERT INTO sdb_member VALUES (4, 2); +INSERT INTO sdb_member VALUES (5, 2); + +INSERT INTO sdb_member VALUES (6, 3); +INSERT INTO sdb_member VALUES (7, 3); +INSERT INTO sdb_member VALUES (8, 3); +INSERT INTO sdb_member VALUES (9, 3); +INSERT INTO sdb_member VALUES (10, 3); +INSERT INTO sdb_member VALUES (11, 3); + +INSERT INTO sdb_member VALUES (12, 4); +INSERT INTO sdb_member VALUES (13, 4); +INSERT INTO sdb_member VALUES (14, 4); +INSERT INTO sdb_member VALUES (15, 4); +INSERT INTO sdb_member VALUES (16, 4); + +INSERT INTO sdb_member VALUES (1, 5); +INSERT INTO sdb_member VALUES (2, 5); +INSERT INTO sdb_member VALUES (6, 5); +INSERT INTO sdb_member VALUES (12, 5); + +INSERT INTO sdb_skill VALUES (1, 'General :: Management'); +INSERT INTO sdb_skill VALUES (2, 'General :: Education'); +INSERT INTO sdb_skill VALUES (3, 'General :: Computer Science'); +INSERT INTO sdb_skill VALUES (4, 'ISP :: Project Management'); +INSERT INTO sdb_skill VALUES (5, 'ISP :: Datacenter Infrastructure'); +INSERT INTO sdb_skill VALUES (6, 'ISP :: Backbone'); +INSERT INTO sdb_skill VALUES (7, 'Languages :: Command'); +INSERT INTO sdb_skill VALUES (8, 'Languages :: Programming'); +INSERT INTO sdb_skill VALUES (9, 'Languages :: Markup'); +INSERT INTO sdb_skill VALUES (10, 'Applications :: Frontend'); +INSERT INTO sdb_skill VALUES (11, 'Applications :: Middleware'); +INSERT INTO sdb_skill VALUES (12, 'Applications :: Backend'); +INSERT INTO sdb_skill VALUES (13, 'Operating Systems :: Unix'); +INSERT INTO sdb_skill VALUES (14, 'Operating Systems :: Windows'); +INSERT INTO sdb_skill VALUES (15, 'Networking :: OSI 5-7'); +INSERT INTO sdb_skill VALUES (16, 'Networking :: OSI 3-4'); +INSERT INTO sdb_skill VALUES (17, 'Networking :: OSI 1-2'); +INSERT INTO sdb_skill VALUES (18, 'Hardware :: Server'); +INSERT INTO sdb_skill VALUES (19, 'Hardware :: Storage'); +INSERT INTO sdb_skill VALUES (20, 'Hardware :: Network'); + +INSERT INTO sdb_provide VALUES (1, 1, 2); +INSERT INTO sdb_provide VALUES (2, 1, 3); +INSERT INTO sdb_provide VALUES (6, 1, 3); +INSERT INTO sdb_provide VALUES (12, 1, 4); + Index: ossp-pkg/sdb/sdb.txt RCS File: /v/ossp/cvs/ossp-pkg/sdb/sdb.txt,v co -q -kk -p'1.1' '/v/ossp/cvs/ossp-pkg/sdb/sdb.txt,v' | diff -u /dev/null - -L'ossp-pkg/sdb/sdb.txt' 2>/dev/null --- ossp-pkg/sdb/sdb.txt +++ - 2025-04-04 17:34:14.399766311 +0200 @@ -0,0 +1,42 @@ + ++------------+ n 1 +------------+ 1 n +------------+ +| group |----------| person |------------| skill | ++------------+ member +------------+ provide +------------+ + + +G < { group } +S < { skill } +P < { person } + +query: (S, G) --> P + +menu + --> edit groups + --> edit persons + --> edit skills + --> find person + +Form: Edit Skill List + " " ADD + skill-1 MODIFY DELETE + skill-2 MODIFY DELETE + skill-3 MODIFY DELETE + +Form: Edit Person List + " " ADD + person-1 MODIFY DELETE + person-2 MODIFY DELETE + person-3 MODIFY DELETE + +Form: Edit Skills Of A Single Person + MODIFY + skill-1 + skill-1-1 "....." + skill-1-2 ..... + skill-2 + skill-2-1 ..... + skill-2-2 ..... + +Form: Enter Required Skills, Group of Persons, Get Possible Person(s) + +