LGToc: edit_section_db.php comparison

comparison edit_section_db.php @ 2:bd46d26a7b18

adding transaction when updating sections

author	Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date	Fri, 20 Mar 2015 11:32:06 +0100
parents	1f9d2bfe1d13
children	5d0bfd909857

comparison

equal deleted inserted replaced

-:1f9d2bfe1d13
+:bd46d26a7b18
 			// $table="sections";
 			$table="sections_index";
 			$condition="";
 		}
-		// echo "select from ".$table."\n";
 $query="SELECT *
 FROM ".$table."
 WHERE books_id=".$bookId." ".$condition."
 GROUP BY books_id, start_page, end_page
 HAVING COUNT( * ) >=".$count."
 $i++;
 }
 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_index WHERE books_id=".$bookId." ORDER BY id ASC";
 }
 $result=mysql_query($query);
 $i=0;
 }
 return $sectionArray;
 }
 function updateSectionArray($bookId,$sectionArray,$version,$editor){
-	// TODO: ---transaction ---
+	// -- Start Transaction --
+	mysql_query("BEGIN");
 	$query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')";
-	$result=mysql_query($query);
+	$result_versions=mysql_query($query);
-	if(!$result){
+	if(!$result_versions){
 		echo json_encode("Failed during inserting sections_version records."); // .mysql_error();
-		//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'];
 		$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
+		// --- update to section_index table, which is the up-to-date section table ---
+		$qry = "";
 		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());
+		$rst = mysql_query($qry);
-		    }
+		if (!$rst) {
+			break;	// handle to ROLLBACK
+		}
+		if ($section_id == 0) {
+			// set section_id from section_index if this is a new section
+			$section_id = mysql_insert_id();
 		}
 		$str="(";
 		$str.="'".$name."',";
 		$str.="'".$books_id."',";
 		$str.=")";
 		if($idx!=sizeof($sectionArray)-1){
 			$str.=", ";
 		}
-		/*
-		$str="(";
-		$str.="'".$row['name']."',";
-		$str.="'".sprintf("%05d",$row['booksId'])."',";
-		$str.="'".$row['sectionAfter']."',";
-		$str.=$row['startPage'].",";
-		$str.=$row['endPage'].",";
-		$str.=$row['level'].",";
-		$str.=$row['splitFrom'].",";
-		$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);
+	}
+	$result=mysql_query($query);
+	// --- End Transaction ---
+	if ($result and $rst) {
+		mysql_query("COMMIT");
+		echo json_encode("Succeeded."); // important returning info to js
+	} else {
+		mysql_query("ROLLBACK");
+		echo json_encode("Failed during updating sections.");
+		return;
+	}
+	/*
 	if(!$result){
 		echo json_encode("Failed during inserting sections_revisions records."); // .mysql_error();
 		// Delete the sections_versions for this user
 		// delete $versionId in table sections_versions
 		}
 		return;
 	}else{
 		echo json_encode("Succeeded.");
 	}
+	*/
 }
 	echo '\n';
 	*/
 	//$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_index ON books.id=sections_index.books_id WHERE sections_index.books_id IS NULL";
 	$result=mysql_query($query);
 	if(!$result){
 		echo json_encode("Failed during querying records.");
 }
 function _add_book_to_sections($name, $bookId){
-	/*
-	// get Auto_increment
-	$query = mysql_query("SHOW TABLE STATUS WHERE name='sections'");
-	if (mysql_num_rows($query)) {
-		$result = mysql_fetch_assoc($query);
-		echo $result['Auto_increment'];
-	} else {//error
-		//error control here
-		echo "error in getting Auto_increment";
-	}
-	*/
-	// id should be autoincremated
-	// $query="INSERT INTO sections (name,books_id,section_after) VALUES ";
 	$query="INSERT INTO sections_index (name,books_id,section_after) VALUES ";
 	$str="(";
 	$str.="'".$name."','";
 	$str.=$bookId."',";

Mercurial > hg > LGToc

comparison edit_section_db.php @ 2:bd46d26a7b18