Migrate Votes

[RexYuan]

遷移舊投票資料

[RexYuan]

• 舊 SQL dump
• 新 SQL dump

以下這小段 script 會將原本的 DB 資料遷移到新 DB，query_old 是原本 query 在 PDO handle 地方改變 database name 為舊名（coursentnu），query_new 是原本 query 在 PDO handle 地方改變 database name 為新名（course_ntnu）。

<?php
require_once("magic.php");
require_once("functions.php");
// update Courses
$old_courses = query_old("SELECT * FROM course");
foreach ($old_courses as $oc)
{
  $olik = $oc["likeit"];
  $odlik = $oc["dislikeit"];
  $ocode = $oc["code"];
  $ncid = query_new("SELECT CourseId FROM Courses WHERE CourseCode = ?", $ocode);
  if ($ncid)
  {
    query_new("UPDATE Courses SET LikeIt = ?, DislikeIt = ? WHERE CourseId = ?", $olik, $odlik, $ncid[0]["CourseId"]);
    echo "found ".$ncid[0]["CourseId"];
  }
  else
  {
    echo "course not found";
  }
}
// update Votes
$old_votes = query_old("SELECT * FROM vote");
foreach ($old_votes as $ov)
{
  $user = query_new("SELECT UserId FROM Users WHERE FBId = ?", $ov["fbid"]);
  if (!$user)
  {
    query_new("INSERT INTO Users (FBId, UserName, Gender) VALUES (?, ?, ?)",
              $ov["fbid"], $ov["fbName"], $ov["fbGender"]);
  }
  $uid = query_new("SELECT UserId FROM Users WHERE FBId = ?", $ov["fbid"])[0]["UserId"];
  $c = query_new("SELECT CourseId FROM Courses WHERE CourseCode = ?", $ov["code"]);
  if ($c)
  {
    $cid = $c[0]["CourseId"];
    query_new("INSERT INTO Votes (CourseId, CourseCode, Decision, UserId, VoteTime)
               VALUES (?, ?, ?, ?, ?)", $cid, $ov["code"], $ov["vote"], $uid, $ov["time"]);
  }
  else
  {
    echo "course not found";
  }
}
?>

不過因為原本的舊 scraper 有在課程地圖爬過，因此非常多資料這次無法轉移，鑑於這個情況，舊資料的 SQL dump 勢必得留下來。