. The whole DELETE mechanism became more complicated:

   1. Only delete the person record if this was the last project the
      person was a member of.

   2. If the person is a member of more than one projects, only remove
      one junction record.

   3. Take care of the case where the primary project is the one the
      person is removed from.  In this case the person record needs to
      be updated.  Here, another project, randomly choosen, will be
      used as new primary project.

 . The whole INSERT mechanism became more complicated:

   1. Only insert a new person record if the person does not exist
      already, hence, this needs to invesigated first.

   2. Only insert a new junction record if the person is not a member
      of that particular project (= Supporter) already.  This needs to
      be invesigated first as well.

   3. Insert a junction record if the person is not already a member.
      This is important since the system believes that such an item
      exists for all projects a person belongs to.

diff --git a/src/LinuxTag/2003/projects/update.wml b/src/LinuxTag/2003/projects/update.wml
index 849ca14..de8a503 100644 (file)
--- a/src/LinuxTag/2003/projects/update.wml
+++ b/src/LinuxTag/2003/projects/update.wml
@@ -166,21 +166,47 @@
     pg_exec($dbh, "BEGIN TRANSACTION") or die ("Could not start transaction.");
 
     if ($delete == 1) {
-      $query = sprintf ("SELECT name FROM person WHERE oid = %d", $oid);
+      $query = sprintf ("SELECT name,project FROM person WHERE oid = %d", $oid);
       $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
       if (pg_NumRows ($sth) > 0) {
         $row = pg_fetch_array ($sth, 0);
        $name = $row['name'];
+       $primproject = $row['project'];
       }
-      $query = sprintf ("DELETE FROM person WHERE oid = %d", $oid);
-      pg_exec($dbh, $query) or die ("Cannot delete person");
+
+      $query = sprintf ("SELECT project FROM junction WHERE person = '%s' AND project <> '%s'",
+                       addslashes($name),
+                       addslashes($context));
+      pg_exec($dbh, $query) or die ("Cannot investigate junction.");
+
+      if (pg_NumRows ($sth) == 0) {
+
+        $query = sprintf ("DELETE FROM junction WHERE person = '%s'", addslashes ($name));
+        pg_exec($dbh, $query) or die ("Cannot delete person");
+
+        $query = sprintf ("DELETE FROM person WHERE oid = %d", $oid);
+        pg_exec($dbh, $query) or die ("Cannot delete person");
 
 <when <not <string-eq "$(NILI)" "" />>>
-      $query = sprintf("DELETE from nili where person = '%s'", addslashes($name));
-      pg_exec($dbh, $query) or die ("Cannot delete nilis");
+        $query = sprintf("DELETE from nili where person = '%s'", addslashes($name));
+        pg_exec($dbh, $query) or die ("Cannot delete nilis");
 </when>                
 
-      $query = '';
+      } else { # pg_NumRows() > 0 ==> user is member of >1 projects
+       $query = sprintf("DELETE from junction where person = '%s' AND project = '%s'",
+                        addslashes($name),
+                        addslashes($context));
+       pg_exec($dbh, $query) or die ("Cannot delete junction.");
+
+        if ($primproject == $context) {
+          $row = pg_fetch_array($sth, 0);
+        
+          $query = sprintf ("UPDATE person SET project = '%s' WHERE name = '%s'",
+                          addslashes ($row['project']),
+                          addslashes ($name));
+          pg_exec($dbh, $query) or die ("Cannot adjust person.");
+        }
+      }
     } else {
       $newpass = '';
       $oldmail = $email;
@@ -204,7 +230,10 @@
       }
 
       if ($oid == 0) {
-        $query = sprintf ("INSERT INTO person (name,email,project,nightplace,infomail,insurance,socialevent,list,admin,"
+       $query = sprintf ("SELECT name FROM person WHERE name = '%s'", addslashes ($name));
+       $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
+       if (pg_NumRows ($sth) == 0) {
+          $query = sprintf ("INSERT INTO person (name,email,project,nightplace,infomail,insurance,socialevent,list,admin,"
                       ."login,password,comment) "
                       ."VALUES ('%s',%s,'%s','%s',%d,%d,%d,%d,%d,'%s','%s')",
                       addslashes ($name),
@@ -214,7 +243,20 @@
                       $infomail, $insurance, $socialevent, $list,
                       $admin, $login, strlen ($newpass)?md5 ($newpass):"",
                       addslashes ($comment));
-       pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
+         pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
+       }
+
+       # Check if the person is already a member of this project
+       $query = sprintf ("SELECT person FROM junction WHERE person = '%s' AND project = '%s'",
+                         addslashes ($name),
+                         addslashes ($project));
+       $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
+       if (pg_NumRows ($sth) == 0) {
+         $query = sprintf ("INSERT INTO junction (person,project) VALUES ('%s', '%s')",
+                          addslashes ($name),
+                          addslashes ($project));
+         pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
+       }
       } else {
         $query = sprintf ("UPDATE person SET name='%s',email=%s,project='%s',"
                       ."nightplace='%s',infomail=%d,insurance=%d,socialevent=%d,list=%d,admin=%d,login=%d,comment='%s'",