Mercurial > hg > LGToc

comparison edit_section_db.php @ 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
comparison
equal deleted inserted replaced
0:723a162b6627 1:1f9d2bfe1d13
34 if($versionInfo['version']!=0){ 34 if($versionInfo['version']!=0){
35 $table="sections_revisions"; 35 $table="sections_revisions";
36 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; 36 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
37 $versionId=$versionInfo['id']; 37 $versionId=$versionInfo['id'];
38 }else{ 38 }else{
39 $table="sections"; 39 // $table="sections";
40 $table="sections_index";
40 $condition=""; 41 $condition="";
41 } 42 }
42 $query="SELECT * 43 $query="SELECT *
43 FROM ".$table." 44 FROM ".$table."
44 WHERE books_id=".$bookId." ".$condition." 45 WHERE books_id=".$bookId." ".$condition."
69 if($versionInfo['version']!=0){ 70 if($versionInfo['version']!=0){
70 $table="sections_revisions"; 71 $table="sections_revisions";
71 $condition="AND deleted=0 "; 72 $condition="AND deleted=0 ";
72 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 "; 73 $condition=" AND versions_id=".$versionInfo['id']." AND deleted=0 ";
73 }else{ 74 }else{
74 $table="sections"; 75 // $table="sections";
76 $table="sections_index";
75 $condition=""; 77 $condition="";
76 } 78 }
77 79
78 // echo "select from ".$table."\n"; 80 // echo "select from ".$table."\n";
79 81
95 $i++; 97 $i++;
96 } 98 }
97 if($versionInfo['version']!=0){ 99 if($versionInfo['version']!=0){
98 $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"; 100 $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";
99 }else{ 101 }else{
100 $query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC"; 102 //$query="SELECT * FROM sections WHERE books_id=".$bookId." ORDER BY id ASC";
103 $query="SELECT * FROM sections_index WHERE books_id=".$bookId." ORDER BY id ASC";
101 } 104 }
102 $result=mysql_query($query); 105 $result=mysql_query($query);
103 106
104 $i=0; 107 $i=0;
105 $sectionArray=array(); 108 $sectionArray=array();
125 $sectionArray[]=$row; 128 $sectionArray[]=$row;
126 } 129 }
127 return $sectionArray; 130 return $sectionArray;
128 } 131 }
129 function updateSectionArray($bookId,$sectionArray,$version,$editor){ 132 function updateSectionArray($bookId,$sectionArray,$version,$editor){
133
134 // TODO: ---transaction ---
135
136
130 $query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')"; 137 $query="INSERT INTO sections_versions (version,editor,date,books_id) VALUES (".($version+1).",'".$editor."',NOW(),'".$bookId."')";
131 $result=mysql_query($query); 138 $result=mysql_query($query);
132 if(!$result){ 139 if(!$result){
133 echo json_encode("Failed during inserting sections_version records."); // .mysql_error(); 140 echo json_encode("Failed during inserting sections_version records."); // .mysql_error();
134 return; 141 //return;
135 } 142 }
136 $versionId=mysql_insert_id(); 143 $versionId=mysql_insert_id();
137 $query="INSERT INTO sections_revisions (name,books_id,section_after,start_page,end_page,level,split_from,sections_id,versions_id,deleted) VALUES "; 144 $query="INSERT INTO sections_revisions (name,books_id,section_after,start_page,end_page,level,split_from,sections_id,versions_id,deleted) VALUES ";
138 foreach($sectionArray as $idx=>$row){ 145 foreach($sectionArray as $idx=>$row){
146 // get the section_id
147 $section_id = $row['id'];
148 $name = $row['name'];
149 $books_id = sprintf("%05d",$row['booksId']);
150 $section_after = $row['sectionAfter'];
151 $start_page = $row['startPage'];
152 $end_page = $row['endPage'];
153 $level = $row['level'];
154 $split_from = $row['splitFrom'];
155 $deleted = $row['deleted'];
156
157 // update to section_index table, which is the up-to-date section table
158 if ($section_id == 0) { // section_id = 0 means it is a new record created by user
159 // insert new row to section_index table
160 $qry = "INSERT INTO sections_index (name,books_id,section_after,start_page,end_page,level,split_from) VALUES
161 ('".$name."','".$books_id."','".$section_after."',".$start_page.
162 ",".$end_page.",".$level.",".$split_from.")";
163 $rst = mysql_query($qry);
164 if (!$rst) {
165 echo json_encode(mysql_error());
166 }
167 $section_id = mysql_insert_id();
168
169 } else {
170 // update
171 $qry = "UPDATE sections_index SET name='".$name."',books_id='".$books_id."',section_after='".$section_after.
172 "',start_page=".$start_page.",end_page=".$end_page.",level=".$level.",split_from=".$split_from." WHERE id=".$section_id;
173 $rst = mysql_query($qry);
174 if (!$rst){
175 echo json_encode(mysql_error());
176 }
177 }
178
179 $str="(";
180 $str.="'".$name."',";
181 $str.="'".$books_id."',";
182 $str.="'".$section_after."',";
183 $str.=$start_page.",";
184 $str.=$end_page.",";
185 $str.=$level.",";
186 $str.=$split_from.",";
187 $str.=$section_id.","; // sections_id should get from section_index table
188 $str.=$versionId.",";
189 $str.=$deleted;
190 $str.=")";
191 if($idx!=sizeof($sectionArray)-1){
192 $str.=", ";
193 }
194
195
196 /*
139 $str="("; 197 $str="(";
140 $str.="'".$row['name']."',"; 198 $str.="'".$row['name']."',";
141 $str.="'".sprintf("%05d",$row['booksId'])."',"; 199 $str.="'".sprintf("%05d",$row['booksId'])."',";
142 $str.="'".$row['sectionAfter']."',"; 200 $str.="'".$row['sectionAfter']."',";
143 $str.=$row['startPage'].","; 201 $str.=$row['startPage'].",";
144 $str.=$row['endPage'].","; 202 $str.=$row['endPage'].",";
145 $str.=$row['level'].","; 203 $str.=$row['level'].",";
146 $str.=$row['splitFrom'].","; 204 $str.=$row['splitFrom'].",";
147 $str.=$row['id'].","; 205 $str.=$section_id.","; // sections_id should get from section_index table
148 $str.=$versionId.","; 206 $str.=$versionId.",";
149 $str.=$row['deleted']; 207 $str.=$row['deleted'];
150 $str.=")"; 208 $str.=")";
151 if($idx!=sizeof($sectionArray)-1){ 209 if($idx!=sizeof($sectionArray)-1){
152 $str.=", "; 210 $str.=", ";
153 } 211 }
212 */
154 $query.=$str; 213 $query.=$str;
155 } 214 }
156 $result=mysql_query($query); 215 $result=mysql_query($query);
157 if(!$result){ 216 if(!$result){
158 echo json_encode("Failed during inserting sections_revisions records."); // .mysql_error(); 217 echo json_encode("Failed during inserting sections_revisions records."); // .mysql_error();
166 } 225 }
167 return; 226 return;
168 }else{ 227 }else{
169 echo json_encode("Succeeded."); 228 echo json_encode("Succeeded.");
170 } 229 }
230
231
171 } 232 }
172 233
173 234
174 function _select_distinct(){ 235 function _select_distinct(){
175 /* 236 /*
191 echo '\n'; 252 echo '\n';
192 */ 253 */
193 254
194 255
195 //$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)"; 256 //$query="SELECT id FROM books WHERE id NOT IN (SELECT DISTINCT (books_id) FROM sections)";
196 $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL"; 257 // $query="SELECT books.id, books.name FROM books LEFT JOIN sections ON books.id=sections.books_id WHERE sections.books_id IS NULL";
258 $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";
197 259
198 $result=mysql_query($query); 260 $result=mysql_query($query);
199 if(!$result){ 261 if(!$result){
200 echo json_encode("Failed during querying records."); 262 echo json_encode("Failed during querying records.");
201 return; 263 return;
244 echo "error in getting Auto_increment"; 306 echo "error in getting Auto_increment";
245 } 307 }
246 */ 308 */
247 309
248 // id should be autoincremated 310 // id should be autoincremated
249 $query="INSERT INTO sections (name,books_id,section_after) VALUES "; 311 // $query="INSERT INTO sections (name,books_id,section_after) VALUES ";
312 $query="INSERT INTO sections_index (name,books_id,section_after) VALUES ";
250 313
251 $str="("; 314 $str="(";
252 $str.="'".$name."','"; 315 $str.="'".$name."','";
253 $str.=$bookId."',"; 316 $str.=$bookId."',";
254 $str.="''"; 317 $str.="''";