# HG changeset patch
# User Zoe Hong <zhong@mpiwg-berlin.mpg.de>
# Date 1426847526 -3600
# Node ID bd46d26a7b18db65e457bfa1dc0bd7c5ff4dd6cf
# Parent  1f9d2bfe1d1376162d61ced613aa86e2fd546ce3
adding transaction when updating sections

diff -r 1f9d2bfe1d13 -r bd46d26a7b18 config.php
--- a/config.php	Thu Mar 19 18:36:21 2015 +0100
+++ b/config.php	Fri Mar 20 11:32:06 2015 +0100
@@ -1,6 +1,6 @@
 <?php
 
-$at_local = true;
+$at_local = false;
 
 if (!$at_local) {
 	// host at localgazetteers-dev server
diff -r 1f9d2bfe1d13 -r bd46d26a7b18 edit_section_db.php
--- a/edit_section_db.php	Thu Mar 19 18:36:21 2015 +0100
+++ b/edit_section_db.php	Fri Mar 20 11:32:06 2015 +0100
@@ -77,9 +77,6 @@
 			$condition="";
 		}
 
-		// echo "select from ".$table."\n";
-
-
         $query="SELECT * 
                 FROM ".$table."
                 WHERE books_id=".$bookId." ".$condition."
@@ -99,7 +96,6 @@
         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);
@@ -131,16 +127,18 @@
 }
 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);
-	if(!$result){
+	$result_versions=mysql_query($query);
+	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
@@ -154,26 +152,28 @@
 		$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="(";
@@ -192,27 +192,23 @@
 			$str.=", ";
 		}
 
+		$query.=$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);
+
+	// --- 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();
 		
@@ -227,6 +223,8 @@
 	}else{
 		echo json_encode("Succeeded.");
 	}
+	*/
+	
 
 
 }
@@ -254,7 +252,6 @@
 
 
 	//$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);
@@ -295,20 +292,6 @@
 
 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="(";