Mercurial > hg > LGToc

changeset 1:1f9d2bfe1d13

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
handle new section created by user, update sections_index table
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Thu, 19 Mar 2015 18:36:21 +0100
parents 723a162b6627
children bd46d26a7b18
files check_sections.php edit_section_db.php js/check_sections_details.js
diffstat 3 files changed, 98 insertions(+), 14 deletions(-) [+]
line wrap: on
line diff
--- a/check_sections.php	Thu Mar 19 15:06:34 2015 +0100
+++ b/check_sections.php	Thu Mar 19 18:36:21 2015 +0100
@@ -56,20 +56,33 @@
 }
 
 function checkSections($count){
-
-/*	$query="SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.name, start_page, end_page, COUNT( * ) AS count
+	/*
+	$query="(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.name, start_page, end_page, COUNT( * ) AS count
 		FROM sections
 		JOIN books ON sections.books_id = books.id
 		WHERE NOT EXISTS
 		(SELECT 1 FROM sections_versions WHERE books_id=sections.books_id)
 		GROUP BY books_id, start_page, end_page
-		HAVING COUNT( * ) >=".$count."
-		ORDER BY COUNT( * ) DESC ";*/
+		HAVING COUNT( * ) >=".$count.")
+		UNION
+		(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections_revisions.name, start_page, end_page, COUNT( * ) AS count 
+                FROM sections_revisions
+                JOIN books ON sections_revisions.books_id = books.id
+		JOIN (
+			SELECT sections_versions.id 
+			FROM sections_versions WHERE sections_versions.version=(SELECT MAX(version) FROM sections_versions) 
+			GROUP BY books_id
+		) AS t ON t.id=sections_revisions.versions_id
+		WHERE deleted=0 
+                GROUP BY books_id, versions_id,start_page, end_page
+                HAVING COUNT( * ) >=".$count.")
+		ORDER BY count DESC ";
+		*/
 	$query="(SELECT books.name AS book_name, books.level1, books.level2, books.period, books.id, sections.name, start_page, end_page, COUNT( * ) AS count
-		FROM sections
-		JOIN books ON sections.books_id = books.id
+		FROM sections_index
+		JOIN books ON sections_index.books_id = books.id
 		WHERE NOT EXISTS
-		(SELECT 1 FROM sections_versions WHERE books_id=sections.books_id)
+		(SELECT 1 FROM sections_versions WHERE books_id=sections_index.books_id)
 		GROUP BY books_id, start_page, end_page
 		HAVING COUNT( * ) >=".$count.")
 		UNION
--- a/edit_section_db.php	Thu Mar 19 15:06:34 2015 +0100
+++ b/edit_section_db.php	Thu Mar 19 18:36:21 2015 +0100
@@ -36,7 +36,8 @@
 		$condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
 		$versionId=$versionInfo['id'];
 	}else{
-		$table="sections";
+		// $table="sections";
+		$table="sections_index";
 		$condition="";
 	}
 	$query="SELECT *
@@ -71,7 +72,8 @@
 			$condition="AND deleted=0 ";
 			$condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
 		}else{
-			$table="sections";
+			// $table="sections";
+			$table="sections_index";
 			$condition="";
 		}
 
@@ -97,7 +99,8 @@
         if($versionInfo['version']!=0){
                 $query="SELECT id AS revisions_id, name,books_id,section_after,start_page,end_page,level,split_from,sections_id AS id, deleted FROM sections_revisions WHERE versions_id=".$versionInfo['id']." ORDER BY revisions_id ASC";
         }else{
-                $query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC";
+                //$query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC";
+                $query="SELECT * FROM sections_index WHERE books_id=".$bookId." ORDER BY id ASC";
         }
         $result=mysql_query($query);
 
@@ -127,15 +130,70 @@
         return $sectionArray;
 }
 function updateSectionArray($bookId,$sectionArray,$version,$editor){
+	
+	// TODO: ---transaction ---
+
+
 	$query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')";	
 	$result=mysql_query($query);
 	if(!$result){
 		echo json_encode("Failed during inserting sections_version records."); // .mysql_error();
-		return;
+		//return;
 	}
 	$versionId=mysql_insert_id();
 	$query="INSERT INTO sections_revisions (name,books_id,section_after,start_page,end_page,level,split_from,sections_id,versions_id,deleted) VALUES ";
 	foreach($sectionArray as $idx=>$row){
+		// get the section_id
+		$section_id = $row['id'];
+		$name = $row['name'];
+		$books_id = sprintf("%05d",$row['booksId']);
+		$section_after = $row['sectionAfter'];
+		$start_page = $row['startPage'];
+		$end_page = $row['endPage'];
+		$level = $row['level'];
+		$split_from = $row['splitFrom'];
+		$deleted = $row['deleted'];
+
+		// update to section_index table, which is the up-to-date section table
+		if ($section_id == 0) { // section_id = 0 means it is a new record created by user
+			// insert new row to section_index table
+			$qry = "INSERT INTO sections_index (name,books_id,section_after,start_page,end_page,level,split_from) VALUES 
+				('".$name."','".$books_id."','".$section_after."',".$start_page.
+				",".$end_page.",".$level.",".$split_from.")";
+			$rst = mysql_query($qry);
+			if (!$rst) {
+				echo json_encode(mysql_error());
+			}
+			$section_id = mysql_insert_id();
+
+		} else {
+			// update
+			$qry = "UPDATE sections_index SET name='".$name."',books_id='".$books_id."',section_after='".$section_after.
+				"',start_page=".$start_page.",end_page=".$end_page.",level=".$level.",split_from=".$split_from." WHERE id=".$section_id;
+			$rst = mysql_query($qry);
+			if (!$rst){
+				echo json_encode(mysql_error());
+		    }
+		}
+
+		$str="(";
+		$str.="'".$name."',";
+		$str.="'".$books_id."',";
+		$str.="'".$section_after."',";
+		$str.=$start_page.",";
+		$str.=$end_page.",";
+		$str.=$level.",";
+		$str.=$split_from.",";
+		$str.=$section_id.",";	// sections_id should get from section_index table
+		$str.=$versionId.",";
+		$str.=$deleted;
+		$str.=")";
+		if($idx!=sizeof($sectionArray)-1){
+			$str.=", ";
+		}
+
+
+		/*
 		$str="(";
 		$str.="'".$row['name']."',";
 		$str.="'".sprintf("%05d",$row['booksId'])."',";
@@ -144,13 +202,14 @@
 		$str.=$row['endPage'].",";
 		$str.=$row['level'].",";
 		$str.=$row['splitFrom'].",";
-		$str.=$row['id'].",";
+		$str.=$section_id.",";	// sections_id should get from section_index table
 		$str.=$versionId.",";
 		$str.=$row['deleted'];
 		$str.=")";
 		if($idx!=sizeof($sectionArray)-1){
 			$str.=", ";
 		}
+		*/
 		$query.=$str;	
 	}
 	$result=mysql_query($query);
@@ -168,6 +227,8 @@
 	}else{
 		echo json_encode("Succeeded.");
 	}
+
+
 }
 
 
@@ -193,7 +254,8 @@
 
 
 	//$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)"; 
-	$query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL";
+	// $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL";
+	$query="SELECT books.id, books.name FROM books LEFT JOIN sections_index ON books.id=sections_index.books_id WHERE sections_index.books_id IS NULL";
 	
 	$result=mysql_query($query);
 	if(!$result){
@@ -246,7 +308,8 @@
 	*/
 
 	// id should be autoincremated 
-	$query="INSERT INTO sections (name,books_id,section_after) VALUES ";
+	// $query="INSERT INTO sections (name,books_id,section_after) VALUES ";
+	$query="INSERT INTO sections_index (name,books_id,section_after) VALUES ";
 
 	$str="(";
 	$str.="'".$name."','";
--- a/js/check_sections_details.js	Thu Mar 19 15:06:34 2015 +0100
+++ b/js/check_sections_details.js	Thu Mar 19 18:36:21 2015 +0100
@@ -105,6 +105,12 @@
 	var clone=$(".clone").clone(true);
 	clone.removeClass("clone");
 	clone.show();
+
+	clone.children(".hiddenInfo").children(".id").html(0);	// set section id to 0
+
+	// bug: section_after field is not correct
+
+	
 	if(pos==0){
 		clone.insertBefore(obj);
 		//$("<br>").insertAfter(clone);
@@ -112,6 +118,8 @@
 		clone.insertAfter(obj);
 		//$("<br>").insertBefore(clone);
 	}	
+	
+
 	$("#searchResults .sequence").each(function(idx){
 		$(this).html(idx+1);
 	});