OpenPKG CVS Repository
http://cvs.openpkg.org/
____________________________________________________________________________
Server: cvs.openpkg.org Name: Thomas Lotterer
Root: /v/openpkg/cvs Email: [EMAIL PROTECTED]
Module: openpkg-registry Date: 07-Mar-2006 14:09:22
Branch: HEAD Handle: 2006030713092200
Modified files:
openpkg-registry registry-db.pl
Log:
ftp statistics require changes and additions to the database schema
and we need to support initial installations and migrations
Summary:
Revision Changes Path
1.19 +95 -22 openpkg-registry/registry-db.pl
____________________________________________________________________________
patch -p0 <<'@@ .'
Index: openpkg-registry/registry-db.pl
============================================================================
$ cvs diff -u -r1.18 -r1.19 registry-db.pl
--- openpkg-registry/registry-db.pl 23 Feb 2006 13:22:15 -0000 1.18
+++ openpkg-registry/registry-db.pl 7 Mar 2006 13:09:22 -0000 1.19
@@ -37,13 +37,13 @@
# program name, version and date
my $progname="registry-db.pl";
-my $progvers="0.2.7";
+my $progvers="0.3.0";
my $progdate="23-Feb-2006";
# derive data version from program version
my $datavers={};
$datavers->{value} = $progvers;
-if ($datavers->{value} =~ m/^([0-9]+)\.([0-9]+)\.([0-9]+)$/) {
+if ($datavers->{value} =~ m/^[^0-9]*([0-9]+)\.([0-9]+)\.([0-9]+)/) {
$datavers->{major}=$1;
$datavers->{minor}=$2;
$datavers->{patch}=$3;
@@ -58,13 +58,12 @@
# configuration
my $cfg = {};
-$cfg->{db}->{host}="127.0.0.1";
-$cfg->{db}->{template1}->{username}="postgresql";
-$cfg->{db}->{template1}->{password}="postgresql";
-$cfg->{db}->{registry}->{username}="registry";
-$cfg->{db}->{registry}->{password}="registry";
-$cfg->{db}->{registry}->{tablespace}="registry";
$cfg->{db}->{registry}->{host}="127.0.0.1";
+$cfg->{db}->{registry}->{superuser}->{username}="postgresql";
+$cfg->{db}->{registry}->{superuser}->{password}="postgresql";
+$cfg->{db}->{registry}->{workhorse}->{username}="registry";
+$cfg->{db}->{registry}->{workhorse}->{password}="registry";
+$cfg->{db}->{registry}->{tablespace}="registry";
$cfg->{db}->{session}->{dbfile}="$PREFIX/var/openpkg-registry/ui/session.db";
# database handle and scratch variables
@@ -83,11 +82,35 @@
$dbi = undef;
print "==== processing Database $db ====\n";
- if ($db eq "template1" or $db eq "registry") {
+ if ($db eq "template1") {
+ $dbi = DBI->connect (
+
"DBI:Pg:dbname=template1".";host=".$cfg->{db}->{registry}->{host},
+ $cfg->{db}->{registry}->{superuser}->{username},
+ $cfg->{db}->{registry}->{superuser}->{password},
+ {
+ PrintError => 0,
+ RaiseError => 0,
+ AutoCommit => 1
+ }
+ );
+ }
+ elsif ($db eq "registry/superuser") {
$dbi = DBI->connect (
"DBI:Pg:dbname=".$cfg->{db}->{registry}->{tablespace}.";host=".$cfg->{db}->{registry}->{host},
- $cfg->{db}->{$db}->{username},
- $cfg->{db}->{$db}->{password},
+ $cfg->{db}->{registry}->{superuser}->{username},
+ $cfg->{db}->{registry}->{superuser}->{password},
+ {
+ PrintError => 0,
+ RaiseError => 0,
+ AutoCommit => 1
+ }
+ );
+ }
+ elsif ($db eq "registry/workhorse") {
+ $dbi = DBI->connect (
+
"DBI:Pg:dbname=".$cfg->{db}->{registry}->{tablespace}.";host=".$cfg->{db}->{registry}->{host},
+ $cfg->{db}->{registry}->{workhorse}->{username},
+ $cfg->{db}->{registry}->{workhorse}->{password},
{
PrintError => 0,
RaiseError => 0,
@@ -118,21 +141,35 @@
$sql="CREATE USER registry ENCRYPTED PASSWORD 'registry' NOCREATEDB
NOCREATEUSER;";
$rv = $dbh->do($sql);
-print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n";
+print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n" if($dbh->errstr !~
/(user|role) .* already exists/);
$sql="CREATE TABLESPACE registry OWNER registry LOCATION
'$PREFIX/var/openpkg-registry/db';";
$rv = $dbh->do($sql);
-print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n";
+print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n" if($dbh->errstr !~
/tablespace .* already exists/);
$sql="CREATE DATABASE registry OWNER registry TABLESPACE registry;";
$rv = $dbh->do($sql);
-print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n";
+print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n" if($dbh->errstr !~
/database .* already exists/);
+
+$dbh->disconnect(); undef $dbh;
+
+# enaple Pg/SQL
+#
+$dbh = &dbopen("registry/superuser");
+die "ERROR:$0: DataBase error: ".$DBI::errstr."\n" if (not defined $dbh);
+
+$sql="";
+$sql.="CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
'\$libdir/plpgsql' LANGUAGE C;\n";
+$sql.="COMMENT ON FUNCTION plpgsql_call_handler () IS 'PL/pgSQL Call
Handler';\n";
+$sql.="CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER
plpgsql_call_handler;\n";
+$rv = $dbh->do($sql);
+print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n" if($dbh->errstr !~
/function .* already exists/);
$dbh->disconnect(); undef $dbh;
# registry
#
-$dbh = &dbopen("registry");
+$dbh = &dbopen("registry/workhorse");
die "ERROR:$0: DataBase error: ".$DBI::errstr."\n" if (not defined $dbh);
while(&upgraderegistry()) {};
$dbh->disconnect(); undef $dbh;
@@ -205,11 +242,11 @@
return 1;
}
- # 0.[01].x upgrade to 0.2.0 is a nop
+ # 0.[012].x upgrade to 0.3.0 is a nop
#
elsif ( $dbv->{version}->{major} == 0
- and $dbv->{version}->{minor} < 2 ) {
- $sql = sprintf("UPDATE config SET value = '0.2.0';");
+ and $dbv->{version}->{minor} < 3 ) {
+ $sql = sprintf("UPDATE config SET value = '0.3.0';");
$rv = $dbh->do($sql);
print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n"; die if
($dbh->errstr =~ m|ERROR|);
return 1;
@@ -301,7 +338,9 @@
print "initializing database\n";
$dbh->{AutoCommit} = 0;
$dbh->begin_work;
- $sql = &schemaregistry();
+ $sql = "";
+ $sql .= &schemaregistry();
+ $sql .= &funcregistry();
$sql .= "INSERT INTO reg_config VALUES ( 'version', '".$progvers."'
);";
$sql .= &sample() if ($progvers =~ m/^0\.0\./);
$rv = $dbh->do($sql);
@@ -337,6 +376,21 @@
return 1;
}
+ # 0.2.x upgrade to 0.3.0
+ #
+ elsif ( $dbv->{version}->{major} == 0
+ and $dbv->{version}->{minor} == 2 ) {
+ $sql="";
+ $sql .= sprintf("ALTER TABLE reg_statistic ALTER COLUMN time_first
SET DEFAULT CURRENT_TIMESTAMP;\n");
+ $sql .= sprintf("ALTER TABLE reg_statistic ALTER COLUMN time_last
SET DEFAULT CURRENT_TIMESTAMP;\n");
+ $sql .= sprintf("ALTER TABLE reg_statistic ALTER COLUMN count SET
DEFAULT 1;\n");
+ $sql .= &funcregistry();
+ $sql .= sprintf("UPDATE reg_config SET value = '0.3.0';\n");
+ $rv = $dbh->do($sql);
+ print "$sql\nrv=".$rv." message=".$dbh->errstr."\n\n"; die if
($dbh->errstr =~ m|ERROR|);
+ return 1;
+ }
+
# program data version and database version only differ in patchlevel
- just uprev the number
#
elsif ( $dbv->{version}->{major} == $datavers->{major}
@@ -537,16 +591,18 @@
-- remote peer IP address
-- [1.2.3.4]
time_first TIMESTAMP (0) WITHOUT TIME ZONE
- NOT NULL,
+ NOT NULL
+ DEFAULT CURRENT_TIMESTAMP,
-- time login+addr was seen first
-- [2005-11-08 00:00:00]
time_last TIMESTAMP (0) WITHOUT TIME ZONE
- NOT NULL,
+ NOT NULL
+ DEFAULT CURRENT_TIMESTAMP,
-- time login+addr was seen last
-- [2005-11-08 00:00:00]
count INTEGER
NOT NULL
- DEFAULT '0',
+ DEFAULT '1',
-- number login+addr was seen at all
-- [0]
PRIMARY KEY (login, addr)
@@ -555,6 +611,23 @@
EOT
}
+sub funcregistry()
+{
+ return <<'EOT'
+ -- Stored Procedure: update statistics and fallback to insert in case
this becomes a new entry
+ CREATE OR REPLACE FUNCTION reg_statisticlog(TEXT, TEXT)
+ RETURNS BOOL LANGUAGE 'plpgsql' AS '
+ BEGIN
+ UPDATE reg_statistic SET time_last = DEFAULT, count = count+1 WHERE
( login = $1 AND addr = $2 );
+ IF NOT FOUND THEN
+ INSERT INTO reg_statistic (login, addr) VALUES ($1, $2);
+ END IF;
+ RETURN FOUND;
+ END;
+ ';
+EOT
+}
+
sub sample()
{
return <<'EOT'
@@ .
______________________________________________________________________
The OpenPKG Project www.openpkg.org
CVS Repository Commit List [email protected]
