Mercurial > hg > LGToc
view edit_section_db.php @ 4:373c8ecad8b4
deploy to development server
author | Zoe Hong <zhong@mpiwg-berlin.mpg.de> |
---|---|
date | Tue, 24 Mar 2015 11:32:31 +0100 |
parents | 5d0bfd909857 |
children | 3abf39f0eb46 |
line wrap: on
line source
<?php include_once('config/Lib_mb_utf8.php'); include_once('config/config.php'); set_time_limit(0); ini_set('memory_limit', '-1'); $link_mysql = mysql_connect($mysql_server, $mysql_user, $mysql_password); mysql_query("SET NAMES utf8"); if (!$link_mysql) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db($mysql_database, $link_mysql); if (!$db_selected) { die ('Can\'t use foo : ' . mysql_error()); } function checkSectionInfo($bookId){ $query="SELECT * FROM sections_versions WHERE books_id=".$bookId." ORDER BY version DESC"; $result=mysql_query($query); if(mysql_num_rows($result)==0){ $row['version']=0; $row['editor']=""; $row['date']=""; return $row; }else{ $row=mysql_fetch_assoc($result); return $row; } } function checkMissingPage($bookInfo,$versionInfo){ $bookId=$bookInfo['id']; $bookPage=$bookInfo['line']; if($versionInfo['version']!=0){ $table="sections_revisions"; $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; $versionId=$versionInfo['id']; }else{ // $table="sections"; $table="sections_index"; $condition=""; } $query="SELECT * FROM ".$table." WHERE books_id=".$bookId." ".$condition." GROUP BY books_id,start_page,end_page ORDER BY books_id,start_page,level,end_page"; $result=mysql_query($query); $lastPage=1; $i=0; $pageArray=array(); while($row=mysql_fetch_assoc($result)){ if($row['start_page']-1>$lastPage){ $pageArray[$i]['start_page']=$lastPage; $pageArray[$i]['end_page']=$row['start_page']; //echo $pageArray[$i]['start_page']." ".$pageArray[$i]['end_page']."<br>"; $i++; } $lastPage=$row['end_page']; } if($bookPage>$lastPage){ $pageArray[$i]['start_page']=$lastPage; $pageArray[$i]['end_page']=$bookPage; } return $pageArray; } function getSectionArray($bookId,$count,$versionInfo){ $str=""; if($versionInfo['version']!=0){ $table="sections_revisions"; $condition="AND deleted=0 "; $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; }else{ // $table="sections"; $table="sections_index"; $condition=""; } $query="SELECT * FROM ".$table." WHERE books_id=".$bookId." ".$condition." GROUP BY books_id, start_page, end_page HAVING COUNT( * ) >=".$count." ORDER BY id ASC"; /*ORDER BY start_page ASC, level ASC, id ASC,end_page DESC";*/ $result=mysql_query($query); $i=0; $pageArray=array(); while($row=mysql_fetch_assoc($result)){ $pageArray[$i]['start_page']=$row['start_page']; $pageArray[$i]['end_page']=$row['end_page']; $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_index WHERE books_id=".$bookId." ORDER BY id ASC"; } $result=mysql_query($query); $i=0; $sectionArray=array(); while($row=mysql_fetch_assoc($result)){ $flag=""; if($i!=sizeof($pageArray) && $pageArray[$i]['start_page']==$row['start_page'] && $pageArray[$i]['end_page']==$row['end_page']){ $flag="overlapper"; $i++; } if($i!=0 && $pageArray[$i-1]['start_page']==$row['start_page'] && $pageArray[$i-1]['end_page']==$row['end_page']){ $flag="overlapper"; } $row['flag']=$flag; if(!isset($row['deleted'])){ $row['deleted']=""; }else{ if($row['deleted']==0){ $row['deleted']=""; }else{ $row['deleted']="deleted"; } } $sectionArray[]=$row; } return $sectionArray; } function updateSectionArray($bookId,$sectionArray,$version,$editor){ // -- Start Transaction -- mysql_query("BEGIN"); $query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')"; $result_versions=mysql_query($query); if(!$result_versions){ echo json_encode("Failed during inserting sections_version records."); // .mysql_error(); } $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 --- $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.")"; } 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) { 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(); } if ($deleted == 1) { // deleted section from sections_index where id = $section_id $qry = "DELETE FROM sections_index WHERE id=".$section_id; $rst = mysql_query($qry); if (!$rst) { break; // handle to ROLLBACK } } else { $qry = "SELECT * FROM sections_index WHERE id=".$section_id; $rst = mysql_query($qry); if (!$rst) { break; // handle to ROLLBACK } if (mysql_num_rows($rst) == 0) { $qry = "INSERT INTO sections_index (id,name,books_id,section_after,start_page,end_page,level,split_from) VALUES (".$section_id.",'".$name."','".$books_id."','".$section_after."',".$start_page. ",".$end_page.",".$level.",".$split_from.")"; // recover section in sections_index table where id = $section_id $rst = mysql_query($qry); if (!$rst) { break; // handle to ROLLBACK } } } // query for sections_revisions table $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.=", "; } $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(); // Delete the sections_versions for this user // delete $versionId in table sections_versions $query="DELETE FROM sections_versions WHERE id=".$versionId; $result_deleting=mysql_query($query); if(!$result_deleting){ echo json_encode("Failed during deleting wrongly inserted sections_version record."); } return; }else{ echo json_encode("Succeeded."); } */ } function _select_distinct(){ /* $query="SELECT DISTINCT (books_id) FROM sections"; $result=mysql_query($query); if(!$result){ echo json_encode("Failed during inserting section records."); return; }else{ echo json_encode("Succeeded."); } while($row=mysql_fetch_assoc($result)){ // echo $row['books_id'], ", "; } echo "section num: ".mysql_num_rows($result); 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_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."); return; } echo "missing books num: ".mysql_num_rows($result); echo '\n'; } function add_missing_books_to_sections(){ // find missing books // Run the following line/mysql to find out what books are missing in sections table // _select_distinct(); // Or load missing_books_id.csv if (($missing_books = fopen("intermediate_results/missing_books.csv", "r")) !== FALSE) { while (($data = fgetcsv($missing_books, ",")) !== FALSE) { $bookId = $data[0]; $name = $data[1]; _add_book_to_sections($name, $bookId); } fclose($missing_books); } } function _add_book_to_sections($name, $bookId){ $query="INSERT INTO sections_index (name,books_id,section_after) VALUES "; $str="("; $str.="'".$name."','"; $str.=$bookId."',"; $str.="''"; $str.=")"; $query.=$str; // echo $name.", ".$bookId."\n"; $result=mysql_query($query); if(!$result){ echo json_encode("Failed during inserting section records."); return; } // echo mysql_insert_id(); } if(isset($_POST['command']) && $_POST['command']!=''){ $command=$_POST['command']; if($command=="write"){ if(isset($_POST['bookId'])&&isset($_POST['sectionArray'])&&isset($_POST['version'])&&isset($_POST['editor'])&&\ is_numeric($_POST['bookId']) && sizeof($_POST['sectionArray'])!=0 && is_numeric($_POST['version']) && $_POST['editor']!=""){ $bookId=$_POST['bookId']; $sectionArray=$_POST['sectionArray']; $version=$_POST['version']; $editor=$_POST['editor']; updateSectionArray($bookId,$sectionArray,$version,$editor); } else{ echo json_encode("Editing info not correct."); } } else{ echo json_encode("Wrong command."); return; } } ?>