Mercurial > hg > extraction-interface

comparison interface/edit_section_db.php @ 2:da5309d54083

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
add missing books to sections as dummy entries for further modification. (1) Alter "Auto_increment" to 5190896 for sections table in database. (2) Finds missing books (using mysql OR _select_distinct() in add_missing_books_to_sections.php (3) Put the missing_books.csv in intermediate_results. and execute: add_missing_books_to_sections.php
author Zoe Hong <zhong@mpiwg-berlin.mpg.de>
date Fri, 23 Jan 2015 19:00:57 +0100
parents ef6d0c6a13d7
children f196939ccc03
comparison
equal deleted inserted replaced
1:ef6d0c6a13d7 2:da5309d54083
62 $pageArray[$i]['end_page']=$bookPage; 62 $pageArray[$i]['end_page']=$bookPage;
63 } 63 }
64 return $pageArray; 64 return $pageArray;
65 } 65 }
66 function getSectionArray($bookId,$count,$versionInfo){ 66 function getSectionArray($bookId,$count,$versionInfo){
67
67 $str=""; 68 $str="";
68 if($versionInfo['version']!=0){ 69 if($versionInfo['version']!=0){
69 $table="sections_revisions"; 70 $table="sections_revisions";
70 $condition="AND deleted=0 "; 71 $condition="AND deleted=0 ";
71 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; 72 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
72 }else{ 73 }else{
73 $table="sections"; 74 $table="sections";
74 $condition=""; 75 $condition="";
75 } 76 }
77
78 echo "select from ".$table."\n";
79
80
76 $query="SELECT * 81 $query="SELECT *
77 FROM ".$table." 82 FROM ".$table."
78 WHERE books_id=".$bookId." ".$condition." 83 WHERE books_id=".$bookId." ".$condition."
79 GROUP BY books_id, start_page, end_page 84 GROUP BY books_id, start_page, end_page
80 HAVING COUNT( * ) >=".$count." 85 HAVING COUNT( * ) >=".$count."
81 ORDER BY id ASC"; 86 ORDER BY id ASC";
82 /*ORDER BY start_page ASC, level ASC, id ASC,end_page DESC";*/ 87 /*ORDER BY start_page ASC, level ASC, id ASC,end_page DESC";*/
83 $result=mysql_query($query); 88 $result=mysql_query($query);
89
84 $i=0; 90 $i=0;
85 $pageArray=array(); 91 $pageArray=array();
86 while($row=mysql_fetch_assoc($result)){ 92 while($row=mysql_fetch_assoc($result)){
87 $pageArray[$i]['start_page']=$row['start_page']; 93 $pageArray[$i]['start_page']=$row['start_page'];
88 $pageArray[$i]['end_page']=$row['end_page']; 94 $pageArray[$i]['end_page']=$row['end_page'];
153 }else{ 159 }else{
154 echo json_encode("Succeeded."); 160 echo json_encode("Succeeded.");
155 } 161 }
156 } 162 }
157 163
164
165 function _select_distinct(){
166 /*
167 $query="SELECT DISTINCT (books_id) FROM sections";
168
169 $result=mysql_query($query);
170 if(!$result){
171 echo json_encode("Failed during inserting section records.");
172 return;
173 }else{
174 echo json_encode("Succeeded.");
175 }
176
177 while($row=mysql_fetch_assoc($result)){
178 // echo $row['books_id'], ", ";
179 }
180
181 echo "section num: ".mysql_num_rows($result);
182 echo '\n';
183 */
184
185
186 //$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)";
187 $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL";
188
189 $result=mysql_query($query);
190 if(!$result){
191 echo json_encode("Failed during inserting section records.");
192 return;
193 }
194
195 while($row=mysql_fetch_assoc($result)){
196 //echo $row['books_id'], ", ";
197 //echo $row.", ";
198 }
199 echo "missing books num: ".mysql_num_rows($result);
200 echo '\n';
201
202
203
204
205 }
206
207
208
209 function add_missing_books_to_sections(){
210 // find missing books
211 // Run the following line/mysql to find out what books are missing in sections table
212 // _select_distinct();
213 // Or load missing_books_id.csv
214 if (($missing_books = fopen("intermediate_results/missing_books.csv", "r")) !== FALSE) {
215 while (($data = fgetcsv($missing_books, ",")) !== FALSE) {
216
217 $bookId = $data[0];
218 $name = $data[1];
219
220 _add_book_to_sections($name, $bookId);
221
222 }
223 fclose($missing_books);
224 }
225
226 }
227
228
229 function _add_book_to_sections($name, $bookId){
230
231 /*
232 // get Auto_increment
233 $query = mysql_query("SHOW TABLE STATUS WHERE name='sections'");
234 if (mysql_num_rows($query)) {
235 $result = mysql_fetch_assoc($query);
236 echo $result['Auto_increment'];
237 } else {//error
238 //error control here
239 echo "error in getting Auto_increment";
240 }
241 */
242
243 // id should be autoincremated
244 $query="INSERT INTO sections (name,books_id,section_after) VALUES ";
245
246 $str="(";
247 $str.="'".$name."','";
248 $str.=$bookId."',";
249 $str.="''";
250 $str.=")";
251 $query.=$str;
252
253 // echo $name.", ".$bookId."\n";
254
255 $result=mysql_query($query);
256 if(!$result){
257 echo json_encode("Failed during inserting section records.");
258 return;
259 }
260 // echo mysql_insert_id();
261
262 }
263
264
158 if(isset($_POST['command']) && $_POST['command']!=''){ 265 if(isset($_POST['command']) && $_POST['command']!=''){
159 $command=$_POST['command']; 266 $command=$_POST['command'];
160 if($command=="write"){ 267 if($command=="write"){
161
162 $bookId=$_POST['bookId']; 268 $bookId=$_POST['bookId'];
163 $sectionArray=$_POST['sectionArray']; 269 $sectionArray=$_POST['sectionArray'];
164 $version=$_POST['version']; 270 $version=$_POST['version'];
165 $editor=$_POST['editor']; 271 $editor=$_POST['editor'];
166 272