Support for new database design, i.e. person.id as foreign key

diff --git a/src/LinuxTag/2005/supporter/edit_comment.wml b/src/LinuxTag/2005/supporter/edit_comment.wml
index 2c2346b..e73fa09 100644 (file)
--- a/src/LinuxTag/2005/supporter/edit_comment.wml
+++ b/src/LinuxTag/2005/supporter/edit_comment.wml
@@ -50,11 +50,11 @@
   if (strlen ($oid) == 0) {
     if (strlen ($row['name']) > 0) {
       $query = sprintf ("SELECT person.name,person.oid FROM person,junction "
-                       ."WHERE junction.person = person.name AND junction.project = '%s' ORDER BY name",
+                       ."WHERE person = id AND junction.project = '%s' ORDER BY name",
                       $row['name']);
     } else {
       $query = "SELECT person.name,junction.project,person.oid FROM person,junction "
-             ."WHERE junction.person = person.name ORDER BY project,name";
+             ."WHERE person = id ORDER BY project,name";
     }
     $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
 

diff --git a/src/LinuxTag/2005/supporter/job.wml b/src/LinuxTag/2005/supporter/job.wml
index 1770625..bef1be4 100644 (file)
--- a/src/LinuxTag/2005/supporter/job.wml
+++ b/src/LinuxTag/2005/supporter/job.wml
@@ -62,11 +62,11 @@
                                     'oid' => $row['oid']);
     }
 
-    $query = sprintf ("SELECT assignment.starttime,assignment.endtime,assignment.person,assignment.oid,person.oid as peroid "
+    $query = sprintf ("SELECT assignment.starttime,assignment.endtime,assignment.person,name,assignment.oid,person.oid as peroid "
                     ."FROM assignment,fragment,job,person WHERE job.id = %d "
                     ."AND assignment.fragment = fragment.id "
                     ."AND fragment.job = job.id "
-                    ."AND person.name = assignment.person "
+                    ."AND person.id = assignment.person "
                     ."ORDER BY starttime", $id);
     $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
 
@@ -76,7 +76,7 @@
       $foo = normal_time ($row['starttime'], $row['endtime']);
       $assignment[$foo[0]][] = array ('start' => $foo[1],
                                      'end'   => $foo[2],
-                                     'person' => $row['person'],
+                                     'person' => $row['name'],
                                      'oid'   => $row['oid'],
                                      'peroid'   => $row['peroid']);
     }
@@ -127,9 +127,9 @@
     if ($start == "24:00") { $start = "23:59"; }
     if ($end == "24:00") { $end = "23:59"; }
 
-    $query = sprintf ("SELECT name,person.oid FROM person,availability "
+    $query = sprintf ("SELECT id,name,person.oid FROM person,availability "
                     ."WHERE starttime <= '%s %s' AND endtime >= '%s %s' "
-                    ."AND person.name = availability.person ORDER BY name",
+                    ."AND person.id = availability.person ORDER BY name",
                     $date, $start, $date, $end);
     $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
 
@@ -145,11 +145,11 @@
 <protect>
 
        $query = sprintf ("SELECT person FROM assignment "
-                        ."WHERE person = '%s' "
+                        ."WHERE person = %d "
                         ."AND ( starttime <= '%s %s' AND endtime >= '%s %s' "
                         ."OR  starttime >= '%s %s' AND endtime <= '%s %s' "
                         ."OR  starttime <= '%s %s' AND endtime >= '%s %s' ) ",
-                        $row['name'],
+                        $row['id'],
                         $date, $start, $date, $start,
                         $date, $start, $date, $end,
                         $date, $end, $date, $end);
@@ -159,9 +159,9 @@
          echo ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;");
 
          printf (" %s-%s: <a href=\"supporter.php3?oid=%d\">%s</a> "
-                ."(<a href=\"update.php3?type=assign&fragment=%d&person=%s\">assign</a>)",
+                ."(<a href=\"update.php3?type=assign&fragment=%d&person=%d\">assign</a>)",
                  $start, $end, $row['oid'], $row['name'],
-                 $fragment, urlencode ($row['name']));
+                 $fragment, urlencode ($row['id']));
        }
       }
     }

diff --git a/src/LinuxTag/2005/supporter/supporter.wml b/src/LinuxTag/2005/supporter/supporter.wml
index b28bb96..c2ea315 100644 (file)
--- a/src/LinuxTag/2005/supporter/supporter.wml
+++ b/src/LinuxTag/2005/supporter/supporter.wml
@@ -21,11 +21,12 @@
   pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");
 
   if (isset ($oid)) {
-    $query = sprintf ("SELECT name,email,comment,mobile,phone FROM person WHERE oid = %d", $oid);
+    $query = sprintf ("SELECT name,email,comment,mobile,phone,id 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);
+      $id = $row['id'];
       $name = $row['name'];
       $email = $row['email'];
       $comment = $row['comment'];
@@ -38,7 +39,7 @@
   }
 
   if (strlen ($name) > 0) {
-    $query = sprintf ("SELECT * FROM availability WHERE person = '%s' ORDER BY starttime", $name);
+    $query = sprintf ("SELECT * FROM availability WHERE person = %d ORDER BY starttime", $id);
     $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
 
     for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
@@ -57,9 +58,9 @@
     }
 
     $query = sprintf ("SELECT assignment.starttime,assignment.endtime,job.title,job.id,assignment.oid "
-                    ."FROM assignment,fragment,job WHERE person = '%s' "
+                    ."FROM assignment,fragment,job WHERE person = %d "
                     ."AND fragment=fragment.id AND fragment.job = job.id "
-                    ."ORDER BY starttime", $name);
+                    ."ORDER BY starttime", $id);
     $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
 
     for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
@@ -124,7 +125,7 @@
       printf ("<br><a href=\"edit.php3?oid=%d\">Edit</a>", $oid);
     }
 
-  function display_jobs ($dbh, $date, $start, $end, $name)
+  function display_jobs ($dbh, $date, $start, $end, $id)
   {
     if ($start == "24:00") { $start = "23:59"; }
     if ($end == "24:00") { $end = "23:59"; }
@@ -146,9 +147,9 @@
          echo ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt;");
 
          printf (" %s-%s: <a href=\"job.php3?id=%d\">%s</a> "
-                ."(<a href=\"update.php3?type=assign&fragment=%d&person=%s\">assign</a>)",
+                ."(<a href=\"update.php3?type=assign&fragment=%d&person=%d\">assign</a>)",
                  $foo[1], $foo[2], $row['job'], $row['title'],
-                 $row['id'], urlencode ($name));
+                 $row['id'], $id);
         }
       }
     }
@@ -178,12 +179,12 @@
 
        while (!($av_pivot >= count ($around) && $as_pivot >= count ($assign))) {
          if ($as_pivot >= count ($assign)) {
-           display_jobs ($dbh, $date, $slot, $around[$av_pivot++]['end'], $name);
+           display_jobs ($dbh, $date, $slot, $around[$av_pivot++]['end'], $id);
            if ($av_pivot < count ($around)) {
              $slot = $around[$av_pivot]['start'];
            }
          } elseif (time_lt ($slot, $assign[$as_pivot]['start'])) {
-           display_jobs ($dbh, $date, $slot, $assign[$as_pivot]['start'], $name);
+           display_jobs ($dbh, $date, $slot, $assign[$as_pivot]['start'], $id);
            $slot = $assign[$as_pivot]['start'];
          } else {
            printf ("<br>%s-%s: <a href=\"job.php3?id=%d\">%s</a> "