--- a/models/extractapp.php	Mon Nov 23 16:24:57 2015 +0100
+++ b/models/extractapp.php	Tue Feb 16 15:07:43 2016 +0100
@@ -191,7 +191,8 @@
         if (isset($_postdata['topic'])) {
             $this->topic = $_postdata['topic'];
         }
-        
+      
+
         if (isset($_postdata['sectionName'])) {
             $this->section_name = $_postdata['sectionName'];
         }
@@ -372,6 +373,7 @@
         $this->messages['info'] .= ", section name: ".$this->section_name;
         $this->messages['info'] .= ", branch id: ".$this->branch_id;
         $this->messages['info'] .= ", file id: ".$this->file_id;
+        $this->messages['info'] .= ", topic id: ".$this->topic;
         $this->messages['info'] .= "<br>";
 
 
@@ -617,10 +619,10 @@
 
     // --- for regex ----
     private function GetRegexFilenameById($topic_id) {
-        $query = sprintf("SELECT regex_filename FROM `topic_regex_relation` WHERE  topic_id=\"%s\"", $topic_id);
+        $query = sprintf("SELECT regex_filename FROM `TopicRegexRelation` WHERE  topic_id=\"%s\"", $topic_id);
         $result = mysql_query($query);
         if (!$result) {
-            return json_encode("Failed during selecting topic_regex_relation table.");
+            return json_encode("Failed during selecting TopicRegexRelation table.");
         }
 
         $filenames = array();
@@ -662,7 +664,7 @@
             $filename = $_postdata['filename'].'.txt';
             
             // check if already existing a record for this regex file
-            $query = "SELECT * FROM topic_regex_relation WHERE topic_id=".$topic_id." AND regex_filename='".$filename."'";
+            $query = "SELECT * FROM TopicRegexRelation WHERE topic_id=".$topic_id." AND regex_filename='".$filename."'";
             $result = mysql_query($query);
             if (!$result) {
                 echo json_encode("ErrorDB");
@@ -677,7 +679,7 @@
 
         
             if (!$_postdata['forcesave']) {
-                $query = "INSERT INTO topic_regex_relation (topic_id, regex_filename) VALUES (".$topic_id.",'".$filename."')";
+                $query = "INSERT INTO TopicRegexRelation (topic_id, regex_filename) VALUES (".$topic_id.",'".$filename."')";
                 $result = mysql_query($query);
                 if (!$result) {
                     echo json_encode("ErrorDB");
@@ -841,7 +843,7 @@
         /**
         */
 
-        $query = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='taglist'";
+        $query = "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Taglist'";
         $result = mysql_query($query);
         if (!$result) {
             echo json_encode(mysql_error());
@@ -862,7 +864,7 @@
 
         $topic_tag_name = $this->GetTopicTag($topic_id);
 
-        $query = "SELECT id FROM taglist WHERE taglist.tag='".$topic_tag_name."'";
+        $query = "SELECT id FROM Taglist WHERE Taglist.tag='".$topic_tag_name."'";
         $result = mysql_query($query);
         if (!$result) {
             echo json_encode(mysql_error());
@@ -900,12 +902,18 @@
         if ($postdata['id']){   
             list($id, $name, $tag, $color) = $this->GetTag($postdata);
 
-            $query1 = sprintf("INSERT INTO `taglist` (`id`, `name`, `tag`, `color`, `systemName`) VALUES (%s, %s, %s, %s, %s)",
+             // for creationDate and lastChangeDate
+            $date = date('Y-m-d H:i:s', time());
+
+            $query1 = sprintf("INSERT INTO `Taglist` (`id`, `name`, `tag`, `color`, `creationDate`, `lastChangeDate`) VALUES (%s, %s, %s, %s, %s, %s)",
                                 $this->GetSQLValueString($id, "int"),
                                 $this->GetSQLValueString($name, "text"),
                                 $this->GetSQLValueString($tag, "text"),
                                 $this->GetSQLValueString($color, "text"),
-                                $this->GetSQLValueString($this->systemNAME, "text"));
+                                $this->GetSQLValueString($date, "date"),
+                                $this->GetSQLValueString($date, "date"));
+                                //$this->GetSQLValueString($this->systemNAME, "text"));
+            
             $result1 = mysql_query($query1);
 
 
@@ -913,10 +921,18 @@
             $topic_id = $postdata['topic_id'];
 
             // tag_id is $id;
-            $query = sprintf("INSERT INTO `topic_tag_relation` (`topic_id`,`tag_id`) VALUES (%s,%s)", $topic_id, $id);
+            //$query = sprintf("INSERT INTO `TopicTagRelation` (`topicId`,`tagId`) VALUES (%s,%s)", $topic_id, $id);
+
+            $query = sprintf("INSERT INTO TopicTagRelation (`topicId`, `tagId`, `creationDate`, `lastChangeDate`) VALUES (%s, %s, %s, %s)",
+                    $this->GetSQLValueString($topic_id, "int"),
+                    $this->GetSQLValueString($id, "int"),
+                    $this->GetSQLValueString($date, "date"),
+                    $this->GetSQLValueString($date, "date"));
+
+
             $result = mysql_query($query);
             if (!$result) {
-                echo json_encode("error when insert into topic_tag_relation table");
+                echo json_encode("error when insert into TopicTagRelation table");
             }
         }
 
@@ -926,10 +942,14 @@
         if ($postdata['id']){   
             list($id, $name, $tag, $color) = $this->GetTag($postdata);
             
-            $query = sprintf("UPDATE taglist SET `name`=%s, `tag`=%s, `color`=%s WHERE `id`=%s",
+            // for lastChangeDate
+            $date = date('Y-m-d H:i:s', time());
+
+            $query = sprintf("UPDATE Taglist SET `name`=%s, `tag`=%s, `color`=%s , `lastChangeDate`=%s WHERE `id`=%s",
                                 $this->GetSQLValueString($name, "text"),
                                 $this->GetSQLValueString($tag, "text"),
                                 $this->GetSQLValueString($color, "text"),
+                                $this->GetSQLValueString($date, "date"),
                                 $this->GetSQLValueString($id, "int"));
             $result = mysql_query($query);
                      
@@ -942,12 +962,12 @@
             $tag_id = $postdata['id'];
 
             // delete record in 'taglist' table
-            $query = sprintf("DELETE FROM `taglist` WHERE `id` = %s", stripslashes($tag_id));
+            $query = sprintf("DELETE FROM `Taglist` WHERE `id` = %s", stripslashes($tag_id));
             $result = mysql_query($query);
 
             // delete record in 'topic_tag_relation' table
             $topic_id = $postdata['topic_id'];
-            $query = sprintf("DELETE FROM `topic_tag_relation` WHERE `tag_id` = %s and `topic_id` = %s", stripslashes($tag_id), stripcslashes($topic_id));
+            $query = sprintf("DELETE FROM `TopicTagRelation` WHERE `tagId` = %s and `topicId` = %s", stripslashes($tag_id), stripcslashes($topic_id));
             $result = mysql_query($query);
 
         }
@@ -989,7 +1009,7 @@
         */
 
         // new record in topics table           
-        $query="INSERT INTO topics (tag, name_en, name_ch, name_pinyin) VALUES ('".$new_topic_tag."','".$new_topic_name_en."','".$new_topic_name_ch."','".$new_topic_name_pi."')";  
+        $query="INSERT INTO Topic (tag, name_en, name_ch, name_pinyin) VALUES ('".$new_topic_tag."','".$new_topic_name_en."','".$new_topic_name_ch."','".$new_topic_name_pi."')";  
         $result_topics=mysql_query($query);
         if($result_topics){
             $new_topic_id = mysql_insert_id();
@@ -998,7 +1018,7 @@
         }
 
         // new record in taglist table
-        $query="INSERT INTO taglist (name, tag, color, systemName) VALUES ('".$new_topic_tag_name."','".$new_topic_tag."','rgb(255, 0, 174)','interface')";  
+        $query="INSERT INTO Taglist (name, tag, color) VALUES ('".$new_topic_tag_name."','".$new_topic_tag."','rgb(255, 0, 174)')";  
         $result_taglist=mysql_query($query);
         if($result_taglist){
             $new_tag_id = mysql_insert_id();
@@ -1007,7 +1027,7 @@
         }
 
         // new record in topic_tag_relation
-        $query="INSERT INTO topic_tag_relation (topic_id, tag_id) VALUES ('".$new_topic_id."','".$new_tag_id."')";  
+        $query="INSERT INTO TopicTagRelation (topicId, tagId) VALUES ('".$new_topic_id."','".$new_tag_id."')";  
         $result_topic_tag_relation=mysql_query($query);
         if($result_topic_tag_relation){
             
@@ -1038,9 +1058,17 @@
         $topic = $_postdata['topic'];
         $result = $this->GetTopicById($topic);
         $row = mysql_fetch_assoc($result);
+        $topic_name_en = $row['nameEn'];
+        $topic_name_ch = $row['nameCh'];
+        $topic_name_pinyin = $row['namePinyin'];
+        $topic_tag = $row['tag'];
+
+
+        /*
         $topic_name_en = $row['name_en'];
         $topic_name_ch = $row['name_ch'];
         $topic_name_pinyin = $row['name_pinyin'];
+        */
 
         /*
         $query = "SELECT taglist.*, topic_tag_relation.topic_id FROM taglist LEFT JOIN topic_tag_relation ON taglist.id = topic_tag_relation.tag_id ORDER BY `topic_id`";
@@ -1055,20 +1083,23 @@
         */
 
         // ------
-        $query = "SELECT taglist.*, topic_tag_relation.topic_id FROM taglist LEFT JOIN topic_tag_relation ON taglist.id = topic_tag_relation.tag_id ORDER BY `topic_id`";
+        $query = "SELECT Taglist.*, TopicTagRelation.topicId FROM Taglist LEFT JOIN TopicTagRelation ON Taglist.id = TopicTagRelation.tagId ORDER BY `topicId`";
         $result = mysql_query($query);
         if (!$result) {
-            return json_encode("Failed during selecting/joining taglist and topic_tag_relation table.");
+            return json_encode("Failed during selecting/joining Taglist and TopicTagRelation table.");
         }
-
+        
         $tag_intopic = array();
         $tag_others = array();
         $tag_tmp_others = array();
         while ($row = mysql_fetch_assoc($result)) {
-            if ($row['topic_id'] == $topic) {
-                array_push($tag_intopic, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id']));
+            // should exclude the topic tag, which is in either $topic_intopic nor $tag_tmp_others
+            if ($row['tag'] == $topic_tag) {
+                // escape for the topic tag
+            } else if ($row['topicId'] == $topic) {
+                array_push($tag_intopic, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topicId']));
             } else {
-                array_push($tag_tmp_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id']));
+                array_push($tag_tmp_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topicId']));
             }
         }
 
@@ -1085,7 +1116,7 @@
             }
             if ($cnt == ($num_others-$i-1) ) {
                 $row = $tag_tmp_others[$i];
-                array_push($tag_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topic_id']) );
+                array_push($tag_others, array('id'=>$row['id'],'name'=>$row['name'], 'tag'=>$row['tag'], 'color'=>$row['color'], 'topic_id'=>$row['topicId']) );
             }
         }
 
@@ -1129,43 +1160,61 @@
         $tag_ids = json_decode(str_replace('\\', '', $_postdata['ids']));
 
         // update topic_tag_relation by tags_ids array as `tag_id` and topic_id as `topic_id`
+
+        
         // --- add new topic_tag_relation ---
         foreach ($tag_ids as $tag_id) {
-            $query = "SELECT * FROM topic_tag_relation WHERE tag_id=".$tag_id;
+            $query = "SELECT * FROM TopicTagRelation WHERE tagId=".$tag_id;
             $result = mysql_query($query);
             if (!$result) {
-                echo json_encode("error when select from topic_tag_relation");
+                echo json_encode("error when select from TopicTagRelation");
             }
-            $topic_tag = array();
+            //$topic_tag = array();
             $flag = false;
             while ($row = mysql_fetch_assoc($result)) {
-                if ($row['topic_id'] == $topic_id) {
+                if ($row['topicId'] == $topic_id) {
                     $flag = true;
                     break;
                 }
             }
             if (!$flag) {
                 // insert a row into topic_tag_relation table
-                $queryUpdate = "INSERT INTO topic_tag_relation (topic_id, tag_id) VALUES (".$topic_id.",".$tag_id.")";
+                // $queryUpdate = "INSERT INTO TopicTagRelation (topicId, tagId) VALUES (".$topic_id.",".$tag_id.")";
+
+                $date = date('Y-m-d H:i:s', time());    
+                $queryUpdate = sprintf("INSERT INTO TopicTagRelation (`topicId`, `tagId`, `creationDate`, `lastChangeDate`) VALUES (%s, %s, %s, %s)",
+                    $this->GetSQLValueString($topic_id, "int"),
+                    $this->GetSQLValueString($tag_id, "int"),
+                    $this->GetSQLValueString($date, "date"),
+                    $this->GetSQLValueString($date, "date"));
+
                 $resultUpdate = mysql_query($queryUpdate); 
                 if (!$resultUpdate) {
-                    return json_encode("error when insert topic_tag_relation table");
+                    return json_encode("error when insert TopicTagRelation table");
                 }   
             }
 
         }
 
+
         // --- remove tags from this topic ---
-        $query = "SELECT * FROM topic_tag_relation WHERE topic_id=".$topic_id;
+        // bug: this remove the topic tag as well?
+
+        // don't delete the topic tag!
+        $topic_tag = $this->GetTopic($topic_id)["tag"];
+        $topic_tag_id = $this->GetTagIdByTag($topic_tag);
+
+        //$query = "SELECT * FROM TopicTagRelation WHERE topicId=".$topic_id;
+        $query = "SELECT * FROM TopicTagRelation WHERE topicId=".$topic_id." and tagId!=".$topic_tag_id;
         $result = mysql_query($query);
         if (!$result) {
-            echo json_encode("error when select from topic_tag_relation");
+            echo json_encode("error when select from TopicTagRelation");
         }
 
         while ($row = mysql_fetch_assoc($result)) {
             $cnt = 0;
             foreach ($tag_ids as $tag_id) {
-                if ($row['tag_id'] == $tag_id) {
+                if ($row['tagId'] == $tag_id) {
                     break;
                 } else {
                     $cnt ++;
@@ -1174,52 +1223,14 @@
             $_id = $row['id'];
             if ($cnt == count($tag_ids)) {
                 // delete row with (topic_id, tag_ids)
-                $queryDelete = "DELETE FROM topic_tag_relation WHERE id=".$_id;
+                $queryDelete = "DELETE FROM TopicTagRelation WHERE id=".$_id;
                 $resultDelete = mysql_query($queryDelete);
                 if (!$resultDelete) {
-                    echo json_encode("error when delete from topic_tag_relation");
+                    echo json_encode("error when delete from TopicTagRelation");
                 }
             }
         }
 
-    
-
-        /*
-        $query = "SELECT * FROM topic_tag_relation";
-        $result = mysql_query($query);
-        if (!$result) {
-            echo json_encode("error when select from topic_tag_relation");
-        }
-        $topic_tag = array();
-        while ($row = mysql_fetch_assoc($result)) {
-            array_push($topic_tag, array('tag_id'=>$row['tag_id'], 'topic_id'=>$row['topic_id']));
-        }
-
-
-        foreach ($topic_tag as $value) {
-            $flag = false;
-            foreach ($tag_ids as $tag_id) {
-                if ($value['tag_id'] == $tag_id) {
-                    //update its topic_id to $topic_id
-                    $queryUpdate = "UPDATE topic_tag_relation SET topic_id=".$topic_id." WHERE tag_id=".$tag_id;
-                    $resultUpdate = mysql_query($queryUpdate); 
-                    if (!$resultUpdate) {
-                        return json_encode("error when update topic_tag_relation table");
-                    }   
-                    $flag = true;
-                    break;
-                }
-            }
-            if (!$flag && $value['topic_id'] == $topic_id) {
-                // set its topic_id to 0, indicating unsigned
-                $queryUpdate = "UPDATE topic_tag_relation SET topic_id=0 WHERE tag_id=".$value['tag_id'];
-                $resultUpdate = mysql_query($queryUpdate); 
-                if (!$resultUpdate) {
-                    return json_encode("error when update topic_tag_relation table");
-                }   
-            }
-        }
-        */
 
     }
 
@@ -1227,24 +1238,24 @@
     private function GetTaglistByTopicId($topic_id) {
         $taglistArray = array();
         // select taglist ids from topic_tag_relation table
-        $query = sprintf("SELECT * FROM `topic_tag_relation` WHERE `topic_id`='%s'", $topic_id);
+        $query = sprintf("SELECT * FROM `TopicTagRelation` WHERE `topicId`='%s'", $topic_id);
         $result = mysql_query($query);
         if (!$result) {
-            return json_encode("Failed during selecting topic_tag_relation table.");
+            return json_encode("Failed during selecting TopicTagRelation table.");
         }
         $taglist_ids = array();
 
         while ($row = mysql_fetch_assoc($result)) {
-            array_push($taglist_ids, $row['tag_id']);
+            array_push($taglist_ids, $row['tagId']);
         }
 
         // select taglist by tag ids
         foreach ($taglist_ids as $tag_id) {
-            $query = sprintf("SELECT * FROM `taglist` WHERE `id`='%s'", $tag_id);
+            $query = sprintf("SELECT * FROM `Taglist` WHERE `id`='%s'", $tag_id);
             $result = mysql_query($query);
             if (!$result) {
                 echo mysql_error();
-                return json_encode("Failed during selecting taglist table.");
+                return json_encode("Failed during selecting Taglist table.");
             }
 
             $row = mysql_fetch_assoc($result);
@@ -1267,7 +1278,8 @@
         $result = $this->GetTopicList();
         while ($row = mysql_fetch_assoc($result)) {
             //array_push($topiclistArray, array('id'=>$row['id'],'name'=>$row['name'],'tag'=>$row['tag']));
-            array_push($topiclistArray, array('id'=>$row['id'],'tag'=>$row['tag'],'name_en'=>$row['name_en'],'name_ch'=>$row['name_ch'],'name_pinyin'=>$row['name_pinyin'],));
+            //array_push($topiclistArray, array('id'=>$row['id'],'tag'=>$row['tag'],'name_en'=>$row['name_en'],'name_ch'=>$row['name_ch'],'name_pinyin'=>$row['name_pinyin'],));
+            array_push($topiclistArray, array('id'=>$row['id'],'tag'=>$row['tag'],'name_en'=>$row['nameEn'],'name_ch'=>$row['nameCh'],'name_pinyin'=>$row['namePinyin'],));
         }
         return $topiclistArray;
     }
@@ -1283,12 +1295,12 @@
         $result = $this->GetTopicById($topic_id);
         $row = mysql_fetch_assoc($result);
         //$name = $row['name'];
-        $name = array('name_en'=>$row['name_en'], 'name_ch'=>$row['name_ch'], 'name_pinyin'=>$row['name_pinyin']);
+        $name = array('name_en'=>$row['nameEn'], 'name_ch'=>$row['nameCh'], 'name_pinyin'=>$row['namePinyin']);
         return $name;
     }
     private function GetTopicTagName($topic_id) {
         $topic_tag = $this->GetTopicTag($topic_id);
-        $query = "SELECT * FROM `taglist` WHERE tag='".$topic_tag."'";
+        $query = "SELECT * FROM `Taglist` WHERE tag='".$topic_tag."'";
         $result = mysql_query($query);
         $row = mysql_fetch_assoc($result);
 
@@ -1327,9 +1339,9 @@
                 $name = $postdata['text'];
             }
 
-            $query1 = sprintf("INSERT INTO `wordlist` (`name`, `systemName`) VALUES (%s, %s)",
-                            $this->GetSQLValueString($name, "text"),
-                            $this->GetSQLValueString($this->systemNAME, "text"));
+            $query1 = sprintf("INSERT INTO `Wordlist` (`name`) VALUES (%s,)",
+                            $this->GetSQLValueString($name, "text"));
+                            //$this->GetSQLValueString($this->systemNAME, "text"));
             $result1 = mysql_query($query1);
             file_put_contents( "data/wordlist/".mysql_insert_id().".txt", "(empty now)");
         }
@@ -1396,6 +1408,9 @@
         }
     }
     
+
+    /* only used locally */
+    /*
     private function GetSectionInfo() {
         $section_id = $this->GetSectionId();
         if (!is_numeric($section_id)){
@@ -1421,6 +1436,7 @@
 
         return $data;
     }
+    */
 
     private function ParseDataInFile($filename) {
         $text = file_get_contents($filename);
@@ -1481,9 +1497,10 @@
     }
 
 
+    /** This is only been called by SetTextFromLocal().
+    */        
+    /*
     private function GetSectionContent() {  
-        /** This is only been called by SetTextFromLocal().
-        */
 
         $section_id = $this->GetSectionId();
         $section_info = $this->GetSectionInfo();
@@ -1528,19 +1545,11 @@
             $stringInput = preg_replace("/【(.*?)】/u", "【<a href=\"review_index_xml_images.php?books_id=".$bookId."&pages=\\1&entry=0\" target=\"_bookImg\">\\1</a>】", $stringInput);
         }
 
-        /*
-        // get book_meta from books table in db on localhost
-        $book_meta = array();
-        $books_result = $this->GetBooksById($bookId);
-        while ($row = mysql_fetch_assoc($books_result)) {
-            array_push($book_meta, array($row['id'],$row['name'],$row['author'],(string)$row['start_year'],(string)$row['line'],(string)$row['dynasty']));
-                                    // use 'start_year' as year, 'line' is pagenumber
-        }
-        $this->book_meta = $book_meta;
-        */
+        return $stringInput;
 
-        return $stringInput;
     }
+    */
+
     
     private function GetDataPath() {
         return getcwd()."/data/"; // get the current file path, which is getcwd(), and concatenate with "/data/"
@@ -1587,19 +1596,7 @@
 
     }
 
-    private function GetBooksInfo($bookId) {
-        $result = $this->GetSectionsById($bookId);
-        while ($row = mysql_fetch_assoc($result)) {
-            $bookName = $row['name'];
-        }
-
-        $data = array();
-        $data['bookName'] = $bookName;
-
-        return $data;
-    }
-
-    
+     
 }