[Devel] r269 - branches/dui

[svn AT agendadulibre.org]

Author: ldayot
Date: Tue May  1 23:16:12 2007
New Revision: 269

Log:
Le script et le commentaire pour passer de l'ancienne version (les tags 
dans un champ texte) a la nouvelle (table tags).
Une requete sql simple ne suffisant pas, ajout du script reclamant 
authentification d'un moderateur, et aussi quelques tests pour voir 
s'il n'y a pas de betises.




Added:
   branches/dui/upgrade.php
Modified:
   branches/dui/UPGRADE

Modified: branches/dui/UPGRADE
==============================================================================
--- branches/dui/UPGRADE        (original)
+++ branches/dui/UPGRADE        Tue May  1 23:16:12 2007
@@ -42,3 +42,47 @@
 REPLACE INTO `regions` VALUES (25, 'Martinique', NULL);
 REPLACE INTO `regions` VALUES (26, 'R�union', NULL);
 REPLACE INTO `regions` VALUES (27, 'Autre pays', NULL);
+
+Revision lower than 2007-05-30
+==============================
+
+Execute *upgrade.php* script
+
+This script creates the three folowwing table
+
+CREATE TABLE `tags` (
+  `id` int(11) NOT NULL auto_increment,
+  `category_id` int(11) NOT NULL,
+  `name` varchar(255) NOT NULL,
+  `description` text,
+  PRIMARY KEY  (`id`),
+  KEY `name` (`name`)
+) TYPE=MyISAM;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `tags_categories` (
+  `id` int(11) NOT NULL auto_increment,
+  `name` varchar(255) NOT NULL,
+  `description` text,
+  PRIMARY KEY  (`id`),
+  KEY `name` (`name`)
+) TYPE=MyISAM;
+
+-- --------------------------------------------------------
+
+CREATE TABLE `tags_events` (
+  `event_id` int(11) NOT NULL,
+  `tag_id` int(11) NOT NULL,
+  PRIMARY KEY  (`event_id`,`tag_id`),
+  KEY `tag_id` (`tag_id`)
+) TYPE=MyISAM;
+
+
+Transfert informations
+
+And delete one field.
+
+ALTER TABLE `events` DROP `tags`;
+
+Don't make all manualy, use upgrade.php !

Added: branches/dui/upgrade.php
==============================================================================
--- (empty file)
+++ branches/dui/upgrade.php    Tue May  1 23:16:12 2007
@@ -0,0 +1,238 @@
+<?php
+
+/* Copyright 2004-2007
+ * - M�lanie Bats <melanie POINT bats CHEZ utbm POINT fr>
+ * - Thomas Petazzoni <thomas POINT petazzoni CHEZ enix POINT org>
+ *
+ * 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 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.
+ */
+
+include("bd.inc.php");
+include("funcs.inc.php");
+include("session.inc.php");
+include("diff.inc.php");
+
+$db = new db();
+
+function user_find_login ($db, $userid)
+{
+  $sql = "SELECT login FROM users WHERE id=" . $db->quote_smart($userid);
+  $ret = $db->query ($sql);
+  if ($ret == FALSE)
+    {
+      error ("La requ�te <i>" . $sql . "</i> a �chou�");
+      return -1;
+    }
+
+  $row = $db->fetchObject ($ret);
+
+  return $row->login;
+}
+
+/*
+ * Returns a positive ID if user identified, -1 otherwise
+ */
+function user_identify ($db, $login, $password)
+{
+  $sql = "SELECT id FROM users WHERE login=" . $db->quote_smart($login) . " 
AND password=" . $db->quote_smart(md5($password));
+  $ret = $db->query ($sql);
+  if ($ret == FALSE)
+    {
+      error ("Erreur lors de la requ�te <i>" . $sql . "</i>");
+      return -1;
+    }
+
+  if ($db->numRows ($ret) != 1)
+    {
+      return -1;
+    }
+
+  $row = $db->fetchObject($ret);
+
+  return $row->id;
+}
+
+$session = new session();
+
+if (! $session->exists("agenda_libre_id"))
+{
+  if (! isset($_POST['__user_identify']))
+    {
+      put_header("Mod�ration");
+
+      echo "<h2>Identification</h2>";
+      echo "<table align=\"center\">";
+      echo "<form method=\"post\">\n";
+      echo "<tr><td>Login:</td><td><input type=\"text\" 
name=\"__user_login\" size=\"20\"/></td></tr>";
+      echo "<tr><td>Mot de passe:</td><td><input type=\"password\" 
name=\"__user_password\" size=\"20\"/></td></tr>";
+      echo "<tr><td></td><td><input type=\"submit\" name=\"__user_identify\" 
value=\"Identifier\"></td></tr>";
+      echo "</form>";
+      echo "</table>";
+
+      put_footer();
+      exit;
+    }
+  else
+    {
+      if (($ret = user_identify ($db, $_POST['__user_login'], 
$_POST['__user_password'])) > 0)
+       {
+         $session->set("agenda_libre_id", $ret);
+       }
+      else
+       {
+         put_header("Upgrade");
+         echo "Mauvais login/pass";
+         put_footer();
+         exit;
+       }
+    }
+}
+
+put_header("Upgrade");
+
+/*
+ *
+ * Main page
+ *
+ */
+
+if (!isset($_GET["ug"]))
+{
+  // Check if already upgraded
+  // Table exists and events.tags doesn't
+  $query = "SHOW COLUMNS FROM events";
+  $res = $db->query($query);
+  while ($rec = $db->fetchArray($res))
+  {
+    if ($rec[0]=="tags") $toupgrade=true;
+  }
+  $db->freeResult($res);
+  // does new table exist
+  $query = "SHOW TABLES";
+  $res = $db->query($query);
+  while ($rec = $db->fetchArray($res))
+  {
+    if ($rec[0]=="tags_events") $newtable=true;
+  }
+  $db->freeResult($res);
+  if (isset($newtable))
+  {
+    // new table tags_events empty ?
+    $query = "SELECT * FROM tags_events";
+    $res = @$db->query($query);
+    if ($rec = $db->fetchObject($res)) $toupgrade=false;
+    $db->freeResult($res);
+  }
+
+  if (isset($toupgrade) && $toupgrade)
+  {
+    echo "<p>". _("&Ecirc;tes-vous s&ucirc;r de vouloir mettre &agrave; jour 
la base de donn&eacute;es ?"). "</p>";
+    echo "<p>". "<a href='?ug=1'>". _("Oui, mettre &agrave; jour"). 
"</a></p>";
+  }
+  else
+  {
+    echo "<p>". _("Apparemment, la mise &agrave; jour n'est pas 
n&eacute;cessaire"). "</p>";
+  }
+}
+
+if (isset($_GET["ug"]) && $_GET["ug"]==2)
+{ // All is good, delete field
+  $query = "ALTER TABLE `events` DROP `tags`;";
+  $db->query($query);
+  echo "<p>". _("Termin&eacute;"). "</p>";
+}
+
+if (isset($_GET["ug"]) && $_GET["ug"]==1)
+{ // Create tables and transfert contents
+  $res = true;
+  echo "<p>". _("Cr&eacute;ation des tables"). "</p>";
+  $query = "CREATE TABLE IF NOT EXISTS `tags` (
+  `id` int(11) NOT NULL auto_increment,
+  `category_id` int(11) NOT NULL,
+  `name` varchar(255) NOT NULL,
+  `description` text,
+  PRIMARY KEY  (`id`),
+  KEY `name` (`name`)
+) TYPE=MyISAM;";
+  $res &= $db->query($query);
+
+  $query = "CREATE TABLE IF NOT EXISTS `tags_categories` (
+  `id` int(11) NOT NULL auto_increment,
+  `name` varchar(255) NOT NULL,
+  `description` text,
+  PRIMARY KEY  (`id`),
+  KEY `name` (`name`)
+) TYPE=MyISAM;
+";
+  $res &= $db->query($query);
+
+  $query = "CREATE TABLE IF NOT EXISTS `tags_events` (
+  `event_id` int(11) NOT NULL,
+  `tag_id` int(11) NOT NULL,
+  PRIMARY KEY  (`event_id`,`tag_id`),
+  KEY `tag_id` (`tag_id`)
+) TYPE=MyISAM;";
+  $res &= $db->query($query);
+
+  echo "<p>". _("Transfert de tous les mots cl&eacute;s");
+  $query="SELECT id, tags FROM events";
+  $result=$db->query($query);
+  while ($record = $db->fetchObject($result))
+  {
+    $event_tags = $record->tags;
+    if ($event_tags=="") continue;
+    $event_id = $record->id;
+    // manual tags
+    $aTags = explode(" ", $event_tags);
+    $aTagsKnown = array();
+    // get tags which already exist
+    $query1 = "SELECT id, name FROM tags WHERE name IN ('". implode("', '", 
$aTags). "')";
+    $result1 = $db->query($query1);
+    while ($record1 = $db->fetchObject($result1))
+    {
+        $aTagsKnown[]=$record1->name;
+    } // end while
+    $db->freeResult($result1);
+    // Insert new tags
+    if (count($aTagsNew = array_diff($aTags, $aTagsKnown))>0)
+    {
+        $query2 = "INSERT INTO tags (name) VALUES ('". implode("'), ('", 
$aTagsNew). "')";
+        $res &= $db->query($query2);
+    } // end if
+    // make link between event and tags
+    $query3 = "INSERT INTO tags_events (event_id, tag_id) SELECT 
{$event_id}, id FROM tags WHERE name IN ('". implode("','", $aTags). "')";
+    $res &= $db->query($query3);
+    unset($aTags, $aTagsKnown, $aTagsNew);
+    echo " .";
+  } // end while
+  echo " ". _("Fait"). "</p>";
+  $db->freeResult($result);
+
+  if ($res)
+  {
+    // ok
+    echo "<p>". _("Apparemment, le transfert s'est bien pass&eacute;."). 
"</p>";
+    echo "<p>". "<a href='?ug=2'>". _("Supprimer le champ tags des 
&eacute;v&egrave;nements. Pas obligatoire et dangereux s'il y a une 
erreur."). "</a></p>";
+  }
+  else
+  {
+    echo "<p>". _("Probl�me"). "</p>\n";
+  }
+}
+
+put_footer();
+
+?>