1<?php 2// updateschema.php -- HotCRP function for updating old schemata 3// Copyright (c) 2006-2018 Eddie Kohler; see LICENSE. 4 5function update_schema_create_review_form($conf) { 6 if (!($result = $conf->ql("select * from ReviewFormField where fieldName!='outcome'"))) 7 return false; 8 $rfj = (object) array(); 9 while (($row = edb_orow($result))) { 10 $field = (object) array(); 11 $field->name = $row->shortName; 12 if (trim($row->description) != "") 13 $field->description = trim($row->description); 14 if ($row->sortOrder >= 0) 15 $field->position = $row->sortOrder + 1; 16 if ($row->rows > 3) 17 $field->display_space = (int) $row->rows; 18 $field->view_score = (int) $row->authorView; 19 if (in_array($row->fieldName, ["overAllMerit", "technicalMerit", "novelty", 20 "grammar", "reviewerQualification", "potential", 21 "fixability", "interestToCommunity", "longevity", 22 "likelyPresentation", "suitableForShort"])) { 23 $field->options = array(); 24 if ((int) $row->levelChar > 1) 25 $field->option_letter = (int) $row->levelChar; 26 } 27 $fname = $row->fieldName; 28 $rfj->$fname = $field; 29 } 30 31 if (!($result = $conf->ql("select * from ReviewFormOptions where fieldName!='outcome' order by level asc"))) 32 return false; 33 while (($row = edb_orow($result))) { 34 $fname = $row->fieldName; 35 if (isset($rfj->$fname) && isset($rfj->$fname->options)) 36 $rfj->$fname->options[$row->level - 1] = $row->description; 37 } 38 39 return $conf->save_setting("review_form", 1, $rfj); 40} 41 42function update_schema_create_options($conf) { 43 if (!($result = $conf->ql("select * from OptionType"))) 44 return false; 45 $opsj = (object) array(); 46 $byabbr = array(); 47 while (($row = edb_orow($result))) { 48 // backward compatibility with old schema versions 49 if (!isset($row->optionValues)) 50 $row->optionValues = ""; 51 if (!isset($row->type) && $row->optionValues == "\x7Fi") 52 $row->type = 2; 53 else if (!isset($row->type)) 54 $row->type = ($row->optionValues ? 1 : 0); 55 56 $opj = (object) array(); 57 $opj->id = $row->optionId; 58 $opj->name = $row->optionName; 59 60 if (trim($row->description) != "") 61 $opj->description = trim($row->description); 62 63 if ($row->pcView == 2) 64 $opj->view_type = "nonblind"; 65 else if ($row->pcView == 0) 66 $opj->view_type = "admin"; 67 68 $opj->position = (int) $row->sortOrder; 69 if ($row->displayType == 1) 70 $opj->highlight = true; 71 else if ($row->displayType == 2) 72 $opj->near_submission = true; 73 74 switch ($row->type) { 75 case 0: 76 $opj->type = "checkbox"; 77 break; 78 case 1: 79 $opj->type = "selector"; 80 $opj->selector = explode("\n", rtrim($row->optionValues)); 81 break; 82 case 2: 83 $opj->type = "numeric"; 84 break; 85 case 3: 86 $opj->type = "text"; 87 $opj->display_space = 1; 88 break; 89 case 4: 90 $opj->type = "pdf"; 91 break; 92 case 5: 93 $opj->type = "slides"; 94 break; 95 case 6: 96 $opj->type = "video"; 97 break; 98 case 7: 99 $opj->type = "radio"; 100 $opj->selector = explode("\n", rtrim($row->optionValues)); 101 break; 102 case 8: 103 $opj->type = "text"; 104 $opj->display_space = 5; 105 break; 106 case 9: 107 $opj->type = "attachments"; 108 break; 109 case 100: 110 $opj->type = "pdf"; 111 $opj->final = true; 112 break; 113 case 101: 114 $opj->type = "slides"; 115 $opj->final = true; 116 break; 117 case 102: 118 $opj->type = "video"; 119 $opj->final = true; 120 break; 121 } 122 123 $oid = $opj->id; 124 $opsj->$oid = $opj; 125 } 126 127 return $conf->save_setting("options", 1, $opsj); 128} 129 130function update_schema_transfer_address($conf) { 131 $result = $conf->ql("select * from ContactAddress"); 132 while (($row = edb_orow($result))) 133 if (($c = $conf->user_by_id($row->contactId))) { 134 $x = (object) array(); 135 if ($row->addressLine1 || $row->addressLine2) 136 $x->address = array(); 137 foreach (array("addressLine1", "addressLine2") as $k) 138 if ($row->$k) 139 $x->address[] = $row->$k; 140 foreach (array("city" => "city", "state" => "state", 141 "zipCode" => "zip", "country" => "country") as $k => $v) 142 if ($row->$k) 143 $x->$v = $row->$k; 144 $c->merge_and_save_data($x); 145 } 146 return true; 147} 148 149function update_schema_unaccented_name($conf) { 150 if (!$conf->ql("alter table ContactInfo add `unaccentedName` varchar(120) NOT NULL DEFAULT ''")) 151 return false; 152 153 $result = $conf->ql("select contactId, firstName, lastName from ContactInfo"); 154 if (!$result) 155 return false; 156 157 $qs = $qv = array(); 158 while ($result && ($x = $result->fetch_row())) { 159 $qs[] = "update ContactInfo set unaccentedName=? where contactId=$x[0]"; 160 $qv[] = Text::unaccented_name($x[1], $x[2]); 161 } 162 Dbl::free($result); 163 164 $q = Dbl::format_query_apply($conf->dblink, join(";", $qs), $qv); 165 if (!$conf->dblink->multi_query($q)) 166 return false; 167 do { 168 if (($result = $conf->dblink->store_result())) 169 $result->free(); 170 } while ($conf->dblink->more_results() && $conf->dblink->next_result()); 171 return true; 172} 173 174function update_schema_transfer_country($conf) { 175 $result = $conf->ql("select * from ContactInfo where `data` is not null and `data`!='{}'"); 176 while ($result && ($c = Contact::fetch($result, $conf))) { 177 if (($country = $c->data("country"))) 178 $conf->ql("update ContactInfo set country=? where contactId=?", $country, $c->contactId); 179 } 180 return true; 181} 182 183function update_schema_review_word_counts($conf) { 184 $rf = new ReviewForm($conf->review_form_json(), $conf); 185 do { 186 $q = array(); 187 $result = $conf->ql("select * from PaperReview where reviewWordCount is null limit 32"); 188 while (($rrow = edb_orow($result))) 189 $q[] = "update PaperReview set reviewWordCount=" 190 . $rf->word_count($rrow) . " where reviewId=" . $rrow->reviewId; 191 Dbl::free($result); 192 $conf->dblink->multi_query(join(";", $q)); 193 while ($conf->dblink->more_results()) 194 Dbl::free($conf->dblink->next_result()); 195 } while (count($q) == 32); 196} 197 198function update_schema_bad_comment_timeDisplayed($conf) { 199 $badids = Dbl::fetch_first_columns($conf->dblink, "select a.commentId from PaperComment a join PaperComment b where a.paperId=b.paperId and a.commentId<b.commentId and a.timeDisplayed>b.timeDisplayed"); 200 return !count($badids) || $conf->ql("update PaperComment set timeDisplayed=0 where commentId ?a", $badids); 201} 202 203function update_schema_drop_keys_if_exist($conf, $table, $key) { 204 $indexes = Dbl::fetch_first_columns($conf->dblink, "select distinct index_name from information_schema.statistics where table_schema=database() and `table_name`='$table'"); 205 $drops = []; 206 foreach (is_array($key) ? $key : [$key] as $k) 207 if (in_array($k, $indexes)) 208 $drops[] = ($k === "PRIMARY" ? "drop primary key" : "drop key `$k`"); 209 if (count($drops)) 210 return $conf->ql("alter table `$table` " . join(", ", $drops)); 211 else 212 return true; 213} 214 215function update_schema_mimetype_extensions($conf) { 216 if (!($result = $conf->ql("select * from Mimetype where extension is null"))) 217 return false; 218 $qv = []; 219 while (($row = $result->fetch_object())) 220 if (($extension = Mimetype::extension($row->mimetype))) 221 $qv[] = [$row->mimetypeid, $row->mimetype, $extension]; 222 Dbl::free($result); 223 return empty($qv) || $conf->ql("insert into Mimetype (mimetypeid, mimetype, extension) values ?v on duplicate key update extension=values(extension)", $qv); 224} 225 226function update_schema_paper_review_tfields(Conf $conf) { 227 if (!$conf->ql("alter table PaperReview add `tfields` longblob") 228 || !$conf->ql("alter table PaperReview add `sfields` varbinary(2048) DEFAULT NULL")) 229 return false; 230 $cleanf = Dbl::make_multi_ql_stager($conf->dblink); 231 $result = $conf->ql("select * from PaperReview"); 232 while (($row = ReviewInfo::fetch($result, $conf))) { 233 $data = $row->unparse_tfields(); 234 if ($data !== null) 235 $cleanf("update PaperReview set `tfields`=? where paperId=? and reviewId=?", [$data, $row->paperId, $row->reviewId]); 236 } 237 Dbl::free($result); 238 $cleanf(true); 239 return true; 240} 241 242function update_schema_paper_review_null_main_fields(Conf $conf) { 243 $rid = []; 244 $result = $conf->ql("select * from PaperReview"); 245 while (($rrow = ReviewInfo::fetch($result, $conf))) { 246 $tfields = $rrow->tfields ? json_decode($rrow->tfields, true) : []; 247 $any = false; 248 foreach (ReviewInfo::$text_field_map as $kmain => $kjson) { 249 $mainval = (string) get($rrow, $kmain); 250 $jsonval = (string) get($tfields, $kjson); 251 if ($mainval !== $jsonval) { 252 error_log("{$conf->dbname}: #{$rrow->paperId}/{$rrow->reviewId}: {$kmain} [" 253 . simplify_whitespace(UnicodeHelper::utf8_abbreviate($mainval === "" ? "EMPTY" : $mainval, 20)) 254 . "] != tf/{$kjson} [" 255 . simplify_whitespace(UnicodeHelper::utf8_abbreviate($jsonval === "" ? "EMPTY" : $jsonval, 20)) 256 . "]"); 257 return false; 258 } 259 } 260 } 261 Dbl::free($result); 262 $kf = array_map(function ($k) { return "$k=null"; }, array_keys(ReviewInfo::$text_field_map)); 263 return $conf->ql("update PaperReview set " . join(", ", $kf)); 264} 265 266function update_schema_paper_review_drop_main_fields(Conf $conf) { 267 $rid = []; 268 $kf = array_map(function ($k) { return "$k is not null"; }, array_keys(ReviewInfo::$text_field_map)); 269 if (!$conf->ql("lock tables PaperReview write")) 270 return false; 271 $result = $conf->ql("select * from PaperReview where " . join(" or ", $kf)); 272 $rrow = ReviewInfo::fetch($result, $conf); 273 Dbl::free($result); 274 if ($rrow) { 275 error_log("{$conf->dbname}: #{$rrow->paperId}/{$rrow->reviewId}: nonnull main field cancels schema upgrade"); 276 $ok = false; 277 } else { 278 $ok = true; 279 foreach (ReviewInfo::$text_field_map as $kmain => $kjson) 280 $ok = $ok && $conf->ql("alter table PaperReview drop column `$kmain`"); 281 } 282 $conf->ql("unlock tables"); 283 return $ok; 284} 285 286function update_schema_split_review_request_name(Conf $conf) { 287 if (!$conf->ql("alter table ReviewRequest add `firstName` varbinary(120) DEFAULT NULL") 288 || !$conf->ql("alter table ReviewRequest add `lastName` varbinary(120) DEFAULT NULL") 289 || !$conf->ql("lock tables ReviewRequest write")) 290 return false; 291 $result = $conf->ql("select * from ReviewRequest"); 292 $cleanf = Dbl::make_multi_ql_stager($conf->dblink); 293 while ($result && ($row = $result->fetch_object())) { 294 list($first, $last) = Text::split_name($row->name); 295 $cleanf("update ReviewRequest set firstName=?, lastName=? where paperId=? and email=?", [(string) $first === "" ? null : $first, 296 (string) $last === "" ? null : $last, 297 $row->paperId, $row->email]); 298 } 299 Dbl::free($result); 300 $cleanf(true); 301 $conf->ql("unlock tables"); 302 return $conf->ql("alter table ReviewRequest drop column `name`"); 303} 304 305function update_schema_missing_sha1($conf) { 306 $result = $conf->ql("select * from PaperStorage where sha1='' and paper is not null and paper!='' and paperStorageId>1"); 307 $cleanf = Dbl::make_multi_ql_stager($conf->dblink); 308 while (($doc = DocumentInfo::fetch($result, $conf))) { 309 $hash = $doc->content_binary_hash(); 310 $cleanf("update PaperStorage set sha1=? where paperId=? and paperStorageId=?", [$hash, $doc->paperId, $doc->paperStorageId]); 311 if ($doc->documentType == DTYPE_SUBMISSION) 312 $cleanf("update Paper set sha1=? where paperId=? and paperStorageId=? and finalPaperStorageId<=0", [$hash, $doc->paperId, $doc->paperStorageId]); 313 else if ($doc->documentType == DTYPE_FINAL) 314 $cleanf("update Paper set sha1=? where paperId=? and finalPaperStorageId=?", [$hash, $doc->paperId, $doc->paperStorageId]); 315 } 316 Dbl::free($result); 317 $cleanf(true); 318} 319 320function update_schema_selector_options($conf) { 321 $oids = []; 322 foreach ($conf->paper_opts->full_option_list() as $opt) 323 if ($opt->has_selector()) 324 $oids[] = $opt->id; 325 return empty($oids) 326 || $conf->ql("update PaperOption set value=value+1 where optionId?a", $oids); 327} 328 329function updateSchema($conf) { 330 // avoid error message about timezone, set to $Opt 331 // (which might be overridden by database values later) 332 if (function_exists("date_default_timezone_set") && $conf->opt("timezone")) 333 date_default_timezone_set($conf->opt("timezone")); 334 while (($result = $conf->ql("insert into Settings set name='__schema_lock', value=1 on duplicate key update value=1")) 335 && $result->affected_rows == 0) 336 time_nanosleep(0, 200000000); 337 $conf->update_schema_version(null); 338 $old_conf_g = Conf::$g; 339 Conf::$g = $conf; 340 341 error_log($conf->dbname . ": updating schema from version " . $conf->sversion); 342 343 if ($conf->sversion == 6 344 && $conf->ql("alter table ReviewRequest add `reason` text")) 345 $conf->update_schema_version(7); 346 if ($conf->sversion == 7 347 && $conf->ql("alter table PaperReview add `textField7` mediumtext NOT NULL") 348 && $conf->ql("alter table PaperReview add `textField8` mediumtext NOT NULL") 349 && $conf->ql("insert into ReviewFormField set fieldName='textField7', shortName='Additional text field'") 350 && $conf->ql("insert into ReviewFormField set fieldName='textField8', shortName='Additional text field'")) 351 $conf->update_schema_version(8); 352 if ($conf->sversion == 8 353 && $conf->ql("alter table ReviewFormField add `levelChar` tinyint(1) NOT NULL default '0'") 354 && $conf->ql("alter table PaperReviewArchive add `textField7` mediumtext NOT NULL") 355 && $conf->ql("alter table PaperReviewArchive add `textField8` mediumtext NOT NULL")) 356 $conf->update_schema_version(9); 357 if ($conf->sversion == 9 358 && $conf->ql("alter table Paper add `sha1` varbinary(20) NOT NULL default ''")) 359 $conf->update_schema_version(10); 360 if ($conf->sversion == 10 361 && $conf->ql("alter table PaperReview add `reviewRound` tinyint(1) NOT NULL default '0'") 362 && $conf->ql("alter table PaperReviewArchive add `reviewRound` tinyint(1) NOT NULL default '0'") 363 && $conf->ql("alter table PaperReview add key `reviewRound` (`reviewRound`)") 364 && $conf->update_schema_version(11)) { 365 if (count($conf->round_list()) > 1) { 366 // update review rounds (XXX locking) 367 $result = $conf->ql("select paperId, tag from PaperTag where tag like '%~%'"); 368 $rrs = array(); 369 while (($row = edb_row($result))) { 370 list($contact, $round) = explode("~", $row[1]); 371 if (($round = array_search($round, $conf->round_list()))) { 372 if (!isset($rrs[$round])) 373 $rrs[$round] = array(); 374 $rrs[$round][] = "(contactId=$contact and paperId=$row[0])"; 375 } 376 } 377 foreach ($rrs as $round => $pairs) { 378 $q = "update PaperReview set reviewRound=$round where " . join(" or ", $pairs); 379 $conf->ql($q); 380 } 381 $x = trim(preg_replace('/(\S+)\s*/', "tag like '%~\$1' or ", $conf->setting_data("tag_rounds"))); 382 $conf->ql("delete from PaperTag where " . substr($x, 0, strlen($x) - 3)); 383 } 384 } 385 if ($conf->sversion == 11 386 && $conf->ql("DROP TABLE IF EXISTS `ReviewRating`") 387 && $conf->ql("create table `ReviewRating` ( 388 `reviewId` int(11) NOT NULL, 389 `contactId` int(11) NOT NULL, 390 `rating` tinyint(1) NOT NULL default '0', 391 UNIQUE KEY `reviewContact` (`reviewId`,`contactId`), 392 UNIQUE KEY `reviewContactRating` (`reviewId`,`contactId`,`rating`) 393) ENGINE=MyISAM DEFAULT CHARSET=utf8")) 394 $conf->update_schema_version(12); 395 if ($conf->sversion == 12 396 && $conf->ql("alter table PaperReview add `reviewToken` int(11) NOT NULL default '0'")) 397 $conf->update_schema_version(13); 398 if ($conf->sversion == 13 399 && $conf->ql("alter table OptionType add `optionValues` text NOT NULL default ''")) 400 $conf->update_schema_version(14); 401 if ($conf->sversion == 14 402 && $conf->ql("insert into Settings (name, value) select 'rev_tokens', count(reviewId) from PaperReview where reviewToken!=0 on duplicate key update value=values(value)")) 403 $conf->update_schema_version(15); 404 if ($conf->sversion == 15) { 405 // It's OK if this fails! Update 11 added reviewRound to 406 // PaperReviewArchive (so old databases have the column), but I forgot 407 // to upgrade schema.sql (so new databases lack the column). 408 $old_nerrors = Dbl::$nerrors; 409 $conf->ql("alter table PaperReviewArchive add `reviewRound` tinyint(1) NOT NULL default '0'"); 410 Dbl::$nerrors = $old_nerrors; 411 $conf->update_schema_version(16); 412 } 413 if ($conf->sversion == 16 414 && $conf->ql("alter table PaperReview add `reviewEditVersion` int(1) NOT NULL default '0'")) 415 $conf->update_schema_version(17); 416 if ($conf->sversion == 17 417 && $conf->ql("alter table PaperReviewPreference add key `paperId` (`paperId`)") 418 && $conf->ql("DROP TABLE IF EXISTS `PaperRank`") 419 && $conf->ql("create table PaperRank ( 420 `paperId` int(11) NOT NULL, 421 `contactId` int(11) NOT NULL, 422 `rank` int(11) NOT NULL, 423 UNIQUE KEY `contactPaper` (`contactId`,`paperId`), 424 KEY `paperId` (`paperId`) 425) ENGINE=MyISAM DEFAULT CHARSET=utf8;")) 426 $conf->update_schema_version(18); 427 if ($conf->sversion == 18 428 && $conf->ql("alter table PaperComment add `replyTo` int(11) NOT NULL")) 429 $conf->update_schema_version(19); 430 if ($conf->sversion == 19 431 && $conf->ql("drop table PaperRank")) 432 $conf->update_schema_version(20); 433 if ($conf->sversion == 20 434 && $conf->ql("alter table PaperComment add `timeNotified` int(11) NOT NULL default '0'")) 435 $conf->update_schema_version(21); 436 if ($conf->sversion == 21 437 && $conf->ql("update PaperConflict set conflictType=8 where conflictType=3")) 438 $conf->update_schema_version(22); 439 if ($conf->sversion == 22 440 && $conf->ql("insert into ChairAssistant (contactId) select contactId from Chair on duplicate key update ChairAssistant.contactId=ChairAssistant.contactId") 441 && $conf->ql("update ContactInfo set roles=roles+2 where roles=5")) 442 $conf->update_schema_version(23); 443 if ($conf->sversion == 23) 444 $conf->update_schema_version(24); 445 if ($conf->sversion == 24 446 && $conf->ql("alter table ContactInfo add `preferredEmail` varchar(120)")) 447 $conf->update_schema_version(25); 448 if ($conf->sversion == 25) { 449 if ($conf->settings["final_done"] > 0 450 && !isset($conf->settings["final_soft"]) 451 && $conf->ql("insert into Settings (name, value) values ('final_soft', " . $conf->settings["final_done"] . ") on duplicate key update value=values(value)")) 452 $conf->settings["final_soft"] = $conf->settings["final_done"]; 453 $conf->update_schema_version(26); 454 } 455 if ($conf->sversion == 26 456 && $conf->ql("alter table PaperOption add `data` text") 457 && $conf->ql("alter table OptionType add `type` tinyint(1) NOT NULL default '0'") 458 && $conf->ql("update OptionType set type=2 where optionValues='\x7Fi'") 459 && $conf->ql("update OptionType set type=1 where type=0 and optionValues!=''")) 460 $conf->update_schema_version(27); 461 if ($conf->sversion == 27 462 && $conf->ql("alter table PaperStorage add `sha1` varbinary(20) NOT NULL default ''") 463 && $conf->ql("alter table PaperStorage add `documentType` int(3) NOT NULL default '0'") 464 && $conf->ql("update PaperStorage s, Paper p set s.sha1=p.sha1 where s.paperStorageId=p.paperStorageId and p.finalPaperStorageId=0 and s.paperStorageId>0") 465 && $conf->ql("update PaperStorage s, Paper p set s.sha1=p.sha1, s.documentType=" . DTYPE_FINAL . " where s.paperStorageId=p.finalPaperStorageId and s.paperStorageId>0")) 466 $conf->update_schema_version(28); 467 if ($conf->sversion == 28 468 && $conf->ql("alter table OptionType add `sortOrder` tinyint(1) NOT NULL default '0'")) 469 $conf->update_schema_version(29); 470 if ($conf->sversion == 29 471 && $conf->ql("delete from Settings where name='pldisplay_default'")) 472 $conf->update_schema_version(30); 473 if ($conf->sversion == 30 474 && $conf->ql("DROP TABLE IF EXISTS `Formula`") 475 && $conf->ql("CREATE TABLE `Formula` ( 476 `formulaId` int(11) NOT NULL auto_increment, 477 `name` varchar(200) NOT NULL, 478 `heading` varchar(200) NOT NULL default '', 479 `headingTitle` text NOT NULL default '', 480 `expression` text NOT NULL, 481 `authorView` tinyint(1) NOT NULL default '1', 482 PRIMARY KEY (`formulaId`), 483 UNIQUE KEY `formulaId` (`formulaId`), 484 UNIQUE KEY `name` (`name`) 485) ENGINE=MyISAM DEFAULT CHARSET=utf8")) 486 $conf->update_schema_version(31); 487 if ($conf->sversion == 31 488 && $conf->ql("alter table Formula add `createdBy` int(11) NOT NULL default '0'") 489 && $conf->ql("alter table Formula add `timeModified` int(11) NOT NULL default '0'") 490 && $conf->ql("alter table Formula drop index `name`")) 491 $conf->update_schema_version(32); 492 if ($conf->sversion == 32 493 && $conf->ql("alter table PaperComment add key `timeModified` (`timeModified`)")) 494 $conf->update_schema_version(33); 495 if ($conf->sversion == 33 496 && $conf->ql("alter table PaperComment add `paperStorageId` int(11) NOT NULL default '0'")) 497 $conf->update_schema_version(34); 498 if ($conf->sversion == 34 499 && $conf->ql("alter table ContactInfo add `contactTags` text")) 500 $conf->update_schema_version(35); 501 if ($conf->sversion == 35 502 && $conf->ql("alter table ContactInfo modify `defaultWatch` int(11) NOT NULL default '2'") 503 && $conf->ql("alter table PaperWatch modify `watch` int(11) NOT NULL default '0'")) 504 $conf->update_schema_version(36); 505 if ($conf->sversion == 36 506 && $conf->ql("alter table PaperReview add `reviewNotified` int(1) default NULL") 507 && $conf->ql("alter table PaperReviewArchive add `reviewNotified` int(1) default NULL")) 508 $conf->update_schema_version(37); 509 if ($conf->sversion == 37 510 && $conf->ql("alter table OptionType add `displayType` tinyint(1) NOT NULL default '0'")) 511 $conf->update_schema_version(38); 512 if ($conf->sversion == 38 513 && $conf->ql("update PaperComment set forReviewers=1 where forReviewers=-1")) 514 $conf->update_schema_version(39); 515 if ($conf->sversion == 39 516 && $conf->ql("DROP TABLE IF EXISTS `MailLog`") 517 && $conf->ql("CREATE TABLE `MailLog` ( 518 `mailId` int(11) NOT NULL auto_increment, 519 `recipients` varchar(200) NOT NULL, 520 `paperIds` text, 521 `cc` text, 522 `replyto` text, 523 `subject` text, 524 `emailBody` text, 525 PRIMARY KEY (`mailId`) 526) ENGINE=MyISAM DEFAULT CHARSET=utf8")) 527 $conf->update_schema_version(40); 528 if ($conf->sversion == 40 529 && $conf->ql("alter table Paper add `capVersion` int(1) NOT NULL default '0'")) 530 $conf->update_schema_version(41); 531 if ($conf->sversion == 41 532 && $conf->ql("alter table Paper modify `mimetype` varchar(80) NOT NULL default ''") 533 && $conf->ql("alter table PaperStorage modify `mimetype` varchar(80) NOT NULL default ''")) 534 $conf->update_schema_version(42); 535 if ($conf->sversion == 42 536 && $conf->ql("alter table PaperComment add `ordinal` int(11) NOT NULL default '0'")) 537 $conf->update_schema_version(43); 538 if ($conf->sversion == 42 539 && ($result = $conf->ql("describe PaperComment `ordinal`")) 540 && ($o = edb_orow($result)) 541 && substr($o->Type, 0, 3) == "int" 542 && (!$o->Null || $o->Null == "NO") 543 && (!$o->Default || $o->Default == "0")) 544 $conf->update_schema_version(43); 545 if ($conf->sversion == 43 546 && $conf->ql("alter table Paper add `withdrawReason` text")) 547 $conf->update_schema_version(44); 548 if ($conf->sversion == 44 549 && $conf->ql("alter table PaperStorage add `filename` varchar(255)")) 550 $conf->update_schema_version(45); 551 if ($conf->sversion == 45 552 && $conf->ql("alter table PaperReview add `timeRequested` int(11) NOT NULL DEFAULT '0'") 553 && $conf->ql("alter table PaperReview add `timeRequestNotified` int(11) NOT NULL DEFAULT '0'") 554 && $conf->ql("alter table PaperReviewArchive add `timeRequested` int(11) NOT NULL DEFAULT '0'") 555 && $conf->ql("alter table PaperReviewArchive add `timeRequestNotified` int(11) NOT NULL DEFAULT '0'") 556 && $conf->ql("alter table PaperReview drop column `requestedOn`") 557 && $conf->ql("alter table PaperReviewArchive drop column `requestedOn`")) 558 $conf->update_schema_version(46); 559 if ($conf->sversion == 46 560 && $conf->ql("alter table ContactInfo add `disabled` tinyint(1) NOT NULL DEFAULT '0'")) 561 $conf->update_schema_version(47); 562 if ($conf->sversion == 47 563 && $conf->ql("delete from ti using TopicInterest ti left join TopicArea ta using (topicId) where ta.topicId is null")) 564 $conf->update_schema_version(48); 565 if ($conf->sversion == 48 566 && $conf->ql("alter table PaperReview add `reviewAuthorNotified` int(11) NOT NULL DEFAULT '0'") 567 && $conf->ql("alter table PaperReviewArchive add `reviewAuthorNotified` int(11) NOT NULL DEFAULT '0'") 568 && $conf->ql("alter table PaperReviewArchive add `reviewToken` int(11) NOT NULL DEFAULT '0'")) 569 $conf->update_schema_version(49); 570 if ($conf->sversion == 49 571 && $conf->ql("alter table PaperOption drop index `paperOption`") 572 && $conf->ql("alter table PaperOption add index `paperOption` (`paperId`,`optionId`,`value`)")) 573 $conf->update_schema_version(50); 574 if ($conf->sversion == 50 575 && $conf->ql("alter table Paper add `managerContactId` int(11) NOT NULL DEFAULT '0'")) 576 $conf->update_schema_version(51); 577 if ($conf->sversion == 51 578 && $conf->ql("alter table Paper drop column `numComments`") 579 && $conf->ql("alter table Paper drop column `numAuthorComments`")) 580 $conf->update_schema_version(52); 581 // Due to a bug in the schema updater, some databases might have 582 // sversion>=53 but no `PaperComment.commentType` column. Fix them. 583 if (($conf->sversion == 52 584 || ($conf->sversion >= 53 585 && ($result = $conf->ql("show columns from PaperComment like 'commentType'")) 586 && edb_nrows($result) == 0)) 587 && $conf->ql("lock tables PaperComment write, Settings write") 588 && $conf->ql("alter table PaperComment add `commentType` int(11) NOT NULL DEFAULT '0'")) { 589 $new_sversion = max($conf->sversion, 53); 590 $result = $conf->ql("show columns from PaperComment like 'forAuthors'"); 591 if (!$result 592 || edb_nrows($result) == 0 593 || ($conf->ql("update PaperComment set commentType=" . (COMMENTTYPE_AUTHOR | COMMENTTYPE_RESPONSE) . " where forAuthors=2") 594 && $conf->ql("update PaperComment set commentType=commentType|" . COMMENTTYPE_DRAFT . " where forAuthors=2 and forReviewers=0") 595 && $conf->ql("update PaperComment set commentType=" . COMMENTTYPE_ADMINONLY . " where forAuthors=0 and forReviewers=2") 596 && $conf->ql("update PaperComment set commentType=" . COMMENTTYPE_PCONLY . " where forAuthors=0 and forReviewers=0") 597 && $conf->ql("update PaperComment set commentType=" . COMMENTTYPE_REVIEWER . " where forAuthors=0 and forReviewers=1") 598 && $conf->ql("update PaperComment set commentType=" . COMMENTTYPE_AUTHOR . " where forAuthors!=0 and forAuthors!=2") 599 && $conf->ql("update PaperComment set commentType=commentType|" . COMMENTTYPE_BLIND . " where blind=1"))) 600 $conf->update_schema_version($new_sversion); 601 } 602 if ($conf->sversion < 53) 603 Dbl::qx_raw($conf->dblink, "alter table PaperComment drop column `commentType`"); 604 $conf->ql("unlock tables"); 605 if ($conf->sversion == 53 606 && $conf->ql("alter table PaperComment drop column `forReviewers`") 607 && $conf->ql("alter table PaperComment drop column `forAuthors`") 608 && $conf->ql("alter table PaperComment drop column `blind`")) 609 $conf->update_schema_version(54); 610 if ($conf->sversion == 54 611 && $conf->ql("alter table PaperStorage add `infoJson` varchar(255) DEFAULT NULL")) 612 $conf->update_schema_version(55); 613 if ($conf->sversion == 55 614 && $conf->ql("alter table ContactInfo modify `password` varbinary(2048) NOT NULL")) 615 $conf->update_schema_version(56); 616 if ($conf->sversion == 56 617 && $conf->ql("alter table Settings modify `data` blob")) 618 $conf->update_schema_version(57); 619 if ($conf->sversion == 57 620 && $conf->ql("DROP TABLE IF EXISTS `Capability`") 621 && $conf->ql("CREATE TABLE `Capability` ( 622 `capabilityId` int(11) NOT NULL AUTO_INCREMENT, 623 `capabilityType` int(11) NOT NULL, 624 `contactId` int(11) NOT NULL, 625 `paperId` int(11) NOT NULL, 626 `timeExpires` int(11) NOT NULL, 627 `salt` varbinary(255) NOT NULL, 628 `data` blob, 629 PRIMARY KEY (`capabilityId`), 630 UNIQUE KEY `capabilityId` (`capabilityId`) 631) ENGINE=MyISAM DEFAULT CHARSET=utf8") 632 && $conf->ql("DROP TABLE IF EXISTS `CapabilityMap`") 633 && $conf->ql("CREATE TABLE `CapabilityMap` ( 634 `capabilityValue` varbinary(255) NOT NULL, 635 `capabilityId` int(11) NOT NULL, 636 `timeExpires` int(11) NOT NULL, 637 PRIMARY KEY (`capabilityValue`), 638 UNIQUE KEY `capabilityValue` (`capabilityValue`) 639) ENGINE=MyISAM DEFAULT CHARSET=utf8")) 640 $conf->update_schema_version(58); 641 if ($conf->sversion == 58 642 && $conf->ql("alter table PaperReview modify `paperSummary` mediumtext DEFAULT NULL") 643 && $conf->ql("alter table PaperReview modify `commentsToAuthor` mediumtext DEFAULT NULL") 644 && $conf->ql("alter table PaperReview modify `commentsToPC` mediumtext DEFAULT NULL") 645 && $conf->ql("alter table PaperReview modify `commentsToAddress` mediumtext DEFAULT NULL") 646 && $conf->ql("alter table PaperReview modify `weaknessOfPaper` mediumtext DEFAULT NULL") 647 && $conf->ql("alter table PaperReview modify `strengthOfPaper` mediumtext DEFAULT NULL") 648 && $conf->ql("alter table PaperReview modify `textField7` mediumtext DEFAULT NULL") 649 && $conf->ql("alter table PaperReview modify `textField8` mediumtext DEFAULT NULL") 650 && $conf->ql("alter table PaperReviewArchive modify `paperSummary` mediumtext DEFAULT NULL") 651 && $conf->ql("alter table PaperReviewArchive modify `commentsToAuthor` mediumtext DEFAULT NULL") 652 && $conf->ql("alter table PaperReviewArchive modify `commentsToPC` mediumtext DEFAULT NULL") 653 && $conf->ql("alter table PaperReviewArchive modify `commentsToAddress` mediumtext DEFAULT NULL") 654 && $conf->ql("alter table PaperReviewArchive modify `weaknessOfPaper` mediumtext DEFAULT NULL") 655 && $conf->ql("alter table PaperReviewArchive modify `strengthOfPaper` mediumtext DEFAULT NULL") 656 && $conf->ql("alter table PaperReviewArchive modify `textField7` mediumtext DEFAULT NULL") 657 && $conf->ql("alter table PaperReviewArchive modify `textField8` mediumtext DEFAULT NULL")) 658 $conf->update_schema_version(59); 659 if ($conf->sversion == 59 660 && $conf->ql("alter table ActionLog modify `action` varbinary(4096) NOT NULL") 661 && $conf->ql("alter table ContactInfo modify `note` varbinary(4096) DEFAULT NULL") 662 && $conf->ql("alter table ContactInfo modify `collaborators` varbinary(32767) DEFAULT NULL") 663 && $conf->ql("alter table ContactInfo modify `contactTags` varbinary(4096) DEFAULT NULL") 664 && $conf->ql("alter table Formula modify `headingTitle` varbinary(4096) NOT NULL") 665 && $conf->ql("alter table Formula modify `expression` varbinary(4096) NOT NULL") 666 && $conf->ql("alter table OptionType modify `description` varbinary(8192) DEFAULT NULL") 667 && $conf->ql("alter table OptionType modify `optionValues` varbinary(8192) NOT NULL") 668 && $conf->ql("alter table PaperReviewRefused modify `reason` varbinary(32767) DEFAULT NULL") 669 && $conf->ql("alter table ReviewFormField modify `description` varbinary(32767) DEFAULT NULL") 670 && $conf->ql("alter table ReviewFormOptions modify `description` varbinary(32767) DEFAULT NULL") 671 && $conf->ql("alter table ReviewRequest modify `reason` varbinary(32767) DEFAULT NULL") 672 && $conf->ql("alter table Settings modify `data` varbinary(32767) DEFAULT NULL") 673 && $conf->ql("alter table ContactAddress modify `addressLine1` varchar(2048) NOT NULL") 674 && $conf->ql("alter table ContactAddress modify `addressLine2` varchar(2048) NOT NULL") 675 && $conf->ql("alter table ContactAddress modify `city` varchar(2048) NOT NULL") 676 && $conf->ql("alter table ContactAddress modify `state` varchar(2048) NOT NULL") 677 && $conf->ql("alter table ContactAddress modify `zipCode` varchar(2048) NOT NULL") 678 && $conf->ql("alter table ContactAddress modify `country` varchar(2048) NOT NULL") 679 && $conf->ql("alter table PaperTopic modify `topicId` int(11) NOT NULL") 680 && $conf->ql("alter table PaperTopic modify `paperId` int(11) NOT NULL") 681 && $conf->ql("drop table if exists ChairTag")) 682 $conf->update_schema_version(60); 683 if ($conf->sversion == 60) { 684 foreach (["conflictdef", "home"] as $k) 685 if ($conf->setting_data("{$k}msg", false) !== false) { 686 $conf->save_setting("msg.$k", 1, $conf->setting_data("{$k}msg")); 687 $conf->save_setting("{$k}msg", null); 688 } 689 $conf->update_schema_version(61); 690 } 691 if ($conf->sversion == 61 692 && $conf->ql("alter table Capability modify `data` varbinary(4096) DEFAULT NULL")) 693 $conf->update_schema_version(62); 694 if (!isset($conf->settings["outcome_map"])) { 695 $ojson = array(); 696 $result = $conf->ql("select * from ReviewFormOptions where fieldName='outcome'"); 697 while (($row = edb_orow($result))) 698 $ojson[$row->level] = $row->description; 699 $conf->save_setting("outcome_map", 1, $ojson); 700 } 701 if ($conf->sversion == 62 702 && isset($conf->settings["outcome_map"])) 703 $conf->update_schema_version(63); 704 if (!isset($conf->settings["review_form"]) 705 && $conf->sversion < 65) 706 update_schema_create_review_form($conf); 707 if ($conf->sversion == 63 708 && isset($conf->settings["review_form"])) 709 $conf->update_schema_version(64); 710 if ($conf->sversion == 64 711 && $conf->ql("drop table if exists `ReviewFormField`") 712 && $conf->ql("drop table if exists `ReviewFormOptions`")) 713 $conf->update_schema_version(65); 714 if (!isset($conf->settings["options"]) 715 && $conf->sversion < 67) 716 update_schema_create_options($conf); 717 if ($conf->sversion == 65 718 && isset($conf->settings["options"])) 719 $conf->update_schema_version(66); 720 if ($conf->sversion == 66 721 && $conf->ql("drop table if exists `OptionType`")) 722 $conf->update_schema_version(67); 723 if ($conf->sversion == 67 724 && $conf->ql("alter table PaperComment modify `comment` varbinary(32767) DEFAULT NULL") 725 && $conf->ql("alter table PaperComment add `commentTags` varbinary(1024) DEFAULT NULL")) 726 $conf->update_schema_version(68); 727 if ($conf->sversion == 68 728 && $conf->ql("alter table PaperReviewPreference add `expertise` int(4) DEFAULT NULL")) 729 $conf->update_schema_version(69); 730 if ($conf->sversion == 69 731 && $conf->ql("alter table Paper drop column `pcPaper`")) 732 $conf->update_schema_version(70); 733 if ($conf->sversion == 70 734 && $conf->ql("alter table ContactInfo modify `voicePhoneNumber` varbinary(256) DEFAULT NULL") 735 && $conf->ql("alter table ContactInfo modify `faxPhoneNumber` varbinary(256) DEFAULT NULL") 736 && $conf->ql("alter table ContactInfo modify `collaborators` varbinary(8192) DEFAULT NULL") 737 && $conf->ql("alter table ContactInfo drop column `note`") 738 && $conf->ql("alter table ContactInfo add `data` varbinary(32767) DEFAULT NULL")) 739 $conf->update_schema_version(71); 740 if ($conf->sversion == 71 741 && $conf->ql("alter table Settings modify `name` varbinary(256) DEFAULT NULL") 742 && $conf->ql("update Settings set name=rtrim(name)")) 743 $conf->update_schema_version(72); 744 if ($conf->sversion == 72 745 && $conf->ql("update TopicInterest set interest=-2 where interest=0") 746 && $conf->ql("update TopicInterest set interest=4 where interest=2") 747 && $conf->ql("delete from TopicInterest where interest=1")) 748 $conf->update_schema_version(73); 749 if ($conf->sversion == 73 750 && $conf->ql("alter table PaperStorage add `size` bigint(11) DEFAULT NULL") 751 && $conf->ql("update PaperStorage set `size`=length(paper) where paper is not null")) 752 $conf->update_schema_version(74); 753 if ($conf->sversion == 74 754 && $conf->ql("alter table ContactInfo drop column `visits`")) 755 $conf->update_schema_version(75); 756 if ($conf->sversion == 75) { 757 foreach (array("capability_gc", "s3_scope", "s3_signing_key") as $k) 758 if ($conf->setting($k)) { 759 $conf->save_setting("__" . $k, $conf->setting($k), $conf->setting_data($k)); 760 $conf->save_setting($k, null); 761 } 762 $conf->update_schema_version(76); 763 } 764 if ($conf->sversion == 76 765 && $conf->ql("update PaperReviewPreference set expertise=-expertise")) 766 $conf->update_schema_version(77); 767 if ($conf->sversion == 77 768 && $conf->ql("alter table MailLog add `q` varchar(4096)")) 769 $conf->update_schema_version(78); 770 if ($conf->sversion == 78 771 && $conf->ql("alter table MailLog add `t` varchar(200)")) 772 $conf->update_schema_version(79); 773 if ($conf->sversion == 79 774 && $conf->ql("alter table ContactInfo add `passwordTime` int(11) NOT NULL DEFAULT '0'")) 775 $conf->update_schema_version(80); 776 if ($conf->sversion == 80 777 && $conf->ql("alter table PaperReview modify `reviewRound` int(11) NOT NULL DEFAULT '0'") 778 && $conf->ql("alter table PaperReviewArchive modify `reviewRound` int(11) NOT NULL DEFAULT '0'")) 779 $conf->update_schema_version(81); 780 if ($conf->sversion == 81 781 && $conf->ql("alter table PaperStorage add `filterType` int(3) DEFAULT NULL") 782 && $conf->ql("alter table PaperStorage add `originalStorageId` int(11) DEFAULT NULL")) 783 $conf->update_schema_version(82); 784 if ($conf->sversion == 82 785 && $conf->ql("update Settings set name='msg.resp_instrux' where name='msg.responseinstructions'")) 786 $conf->update_schema_version(83); 787 if ($conf->sversion == 83 788 && $conf->ql("alter table PaperComment add `commentRound` int(11) NOT NULL DEFAULT '0'")) 789 $conf->update_schema_version(84); 790 if ($conf->sversion == 84 791 && $conf->ql("insert ignore into Settings (name, value) select 'resp_active', value from Settings where name='resp_open'")) 792 $conf->update_schema_version(85); 793 if ($conf->sversion == 85 794 && $conf->ql("DROP TABLE IF EXISTS `PCMember`") 795 && $conf->ql("DROP TABLE IF EXISTS `ChairAssistant`") 796 && $conf->ql("DROP TABLE IF EXISTS `Chair`")) 797 $conf->update_schema_version(86); 798 if ($conf->sversion == 86 799 && update_schema_transfer_address($conf)) 800 $conf->update_schema_version(87); 801 if ($conf->sversion == 87 802 && $conf->ql("DROP TABLE IF EXISTS `ContactAddress`")) 803 $conf->update_schema_version(88); 804 if ($conf->sversion == 88 805 && $conf->ql("alter table ContactInfo drop key `name`") 806 && $conf->ql("alter table ContactInfo drop key `affiliation`") 807 && $conf->ql("alter table ContactInfo drop key `email_3`") 808 && $conf->ql("alter table ContactInfo drop key `firstName_2`") 809 && $conf->ql("alter table ContactInfo drop key `lastName`")) 810 $conf->update_schema_version(89); 811 if ($conf->sversion == 89 812 && update_schema_unaccented_name($conf)) 813 $conf->update_schema_version(90); 814 if ($conf->sversion == 90 815 && $conf->ql("alter table PaperReview add `reviewAuthorSeen` int(11) DEFAULT NULL")) 816 $conf->update_schema_version(91); 817 if ($conf->sversion == 91 818 && $conf->ql("alter table PaperReviewArchive add `reviewAuthorSeen` int(11) DEFAULT NULL")) 819 $conf->update_schema_version(92); 820 if ($conf->sversion == 92 821 && $conf->ql("alter table Paper drop key `titleAbstractText`") 822 && $conf->ql("alter table Paper drop key `allText`") 823 && $conf->ql("alter table Paper drop key `authorText`") 824 && $conf->ql("alter table Paper modify `authorInformation` varbinary(8192) DEFAULT NULL") 825 && $conf->ql("alter table Paper modify `abstract` varbinary(16384) DEFAULT NULL") 826 && $conf->ql("alter table Paper modify `collaborators` varbinary(8192) DEFAULT NULL") 827 && $conf->ql("alter table Paper modify `withdrawReason` varbinary(1024) DEFAULT NULL")) 828 $conf->update_schema_version(93); 829 if ($conf->sversion == 93 830 && $conf->ql("alter table TopicArea modify `topicName` varchar(200) DEFAULT NULL")) 831 $conf->update_schema_version(94); 832 if ($conf->sversion == 94 833 && $conf->ql("alter table PaperOption modify `data` varbinary(32768) DEFAULT NULL")) { 834 foreach ($conf->paper_opts->nonfixed_option_list() as $xopt) 835 if ($xopt->type === "text") 836 $conf->ql("delete from PaperOption where optionId={$xopt->id} and data=''"); 837 $conf->update_schema_version(95); 838 } 839 if ($conf->sversion == 95 840 && $conf->ql("alter table Capability add unique key `salt` (`salt`)") 841 && $conf->ql("update Capability join CapabilityMap using (capabilityId) set Capability.salt=CapabilityMap.capabilityValue") 842 && $conf->ql("drop table if exists `CapabilityMap`")) 843 $conf->update_schema_version(96); 844 if ($conf->sversion == 96 845 && $conf->ql("alter table ContactInfo add `passwordIsCdb` tinyint(1) NOT NULL DEFAULT '0'")) 846 $conf->update_schema_version(97); 847 if ($conf->sversion == 97 848 && $conf->ql("alter table PaperReview add `reviewWordCount` int(11) DEFAULT NULL") 849 && $conf->ql("alter table PaperReviewArchive add `reviewWordCount` int(11) DEFAULT NULL") 850 && $conf->ql("alter table PaperReviewArchive drop key `reviewId`") 851 && $conf->ql("alter table PaperReviewArchive drop key `contactPaper`") 852 && $conf->ql("alter table PaperReviewArchive drop key `reviewSubmitted`") 853 && $conf->ql("alter table PaperReviewArchive drop key `reviewNeedsSubmit`") 854 && $conf->ql("alter table PaperReviewArchive drop key `reviewType`") 855 && $conf->ql("alter table PaperReviewArchive drop key `requestedBy`")) 856 $conf->update_schema_version(98); 857 if ($conf->sversion == 98) { 858 update_schema_review_word_counts($conf); 859 $conf->update_schema_version(99); 860 } 861 if ($conf->sversion == 99 862 && $conf->ql("alter table ContactInfo ENGINE=InnoDB") 863 && $conf->ql("alter table Paper ENGINE=InnoDB") 864 && $conf->ql("alter table PaperComment ENGINE=InnoDB") 865 && $conf->ql("alter table PaperConflict ENGINE=InnoDB") 866 && $conf->ql("alter table PaperOption ENGINE=InnoDB") 867 && $conf->ql("alter table PaperReview ENGINE=InnoDB") 868 && $conf->ql("alter table PaperStorage ENGINE=InnoDB") 869 && $conf->ql("alter table PaperTag ENGINE=InnoDB") 870 && $conf->ql("alter table PaperTopic ENGINE=InnoDB") 871 && $conf->ql("alter table Settings ENGINE=InnoDB")) 872 $conf->update_schema_version(100); 873 if ($conf->sversion == 100 874 && $conf->ql("alter table ActionLog ENGINE=InnoDB") 875 && $conf->ql("alter table Capability ENGINE=InnoDB") 876 && $conf->ql("alter table Formula ENGINE=InnoDB") 877 && $conf->ql("alter table MailLog ENGINE=InnoDB") 878 && $conf->ql("alter table PaperReviewArchive ENGINE=InnoDB") 879 && $conf->ql("alter table PaperReviewPreference ENGINE=InnoDB") 880 && $conf->ql("alter table PaperReviewRefused ENGINE=InnoDB") 881 && $conf->ql("alter table PaperWatch ENGINE=InnoDB") 882 && $conf->ql("alter table ReviewRating ENGINE=InnoDB") 883 && $conf->ql("alter table ReviewRequest ENGINE=InnoDB") 884 && $conf->ql("alter table TopicArea ENGINE=InnoDB") 885 && $conf->ql("alter table TopicInterest ENGINE=InnoDB")) 886 $conf->update_schema_version(101); 887 if ($conf->sversion == 101 888 && $conf->ql("alter table ActionLog modify `ipaddr` varbinary(32) DEFAULT NULL") 889 && $conf->ql("alter table MailLog modify `recipients` varbinary(200) NOT NULL") 890 && $conf->ql("alter table MailLog modify `q` varbinary(4096) DEFAULT NULL") 891 && $conf->ql("alter table MailLog modify `t` varbinary(200) DEFAULT NULL") 892 && $conf->ql("alter table Paper modify `mimetype` varbinary(80) NOT NULL DEFAULT ''") 893 && $conf->ql("alter table PaperStorage modify `mimetype` varbinary(80) NOT NULL DEFAULT ''") 894 && $conf->ql("alter table PaperStorage modify `filename` varbinary(255) DEFAULT NULL") 895 && $conf->ql("alter table PaperStorage modify `infoJson` varbinary(8192) DEFAULT NULL")) 896 $conf->update_schema_version(102); 897 if ($conf->sversion == 102 898 && $conf->ql("alter table PaperReview modify `paperSummary` mediumblob") 899 && $conf->ql("alter table PaperReview modify `commentsToAuthor` mediumblob") 900 && $conf->ql("alter table PaperReview modify `commentsToPC` mediumblob") 901 && $conf->ql("alter table PaperReview modify `commentsToAddress` mediumblob") 902 && $conf->ql("alter table PaperReview modify `weaknessOfPaper` mediumblob") 903 && $conf->ql("alter table PaperReview modify `strengthOfPaper` mediumblob") 904 && $conf->ql("alter table PaperReview modify `textField7` mediumblob") 905 && $conf->ql("alter table PaperReview modify `textField8` mediumblob") 906 && $conf->ql("alter table PaperReviewArchive modify `paperSummary` mediumblob") 907 && $conf->ql("alter table PaperReviewArchive modify `commentsToAuthor` mediumblob") 908 && $conf->ql("alter table PaperReviewArchive modify `commentsToPC` mediumblob") 909 && $conf->ql("alter table PaperReviewArchive modify `commentsToAddress` mediumblob") 910 && $conf->ql("alter table PaperReviewArchive modify `weaknessOfPaper` mediumblob") 911 && $conf->ql("alter table PaperReviewArchive modify `strengthOfPaper` mediumblob") 912 && $conf->ql("alter table PaperReviewArchive modify `textField7` mediumblob") 913 && $conf->ql("alter table PaperReviewArchive modify `textField8` mediumblob")) 914 $conf->update_schema_version(103); 915 if ($conf->sversion == 103 916 && $conf->ql("alter table Paper modify `title` varbinary(256) DEFAULT NULL") 917 && $conf->ql("alter table Paper drop key `title`")) 918 $conf->update_schema_version(104); 919 if ($conf->sversion == 104 920 && $conf->ql("alter table PaperReview add `reviewFormat` tinyint(1) DEFAULT NULL") 921 && $conf->ql("alter table PaperReviewArchive add `reviewFormat` tinyint(1) DEFAULT NULL")) 922 $conf->update_schema_version(105); 923 if ($conf->sversion == 105 924 && $conf->ql("alter table PaperComment add `commentFormat` tinyint(1) DEFAULT NULL")) 925 $conf->update_schema_version(106); 926 if ($conf->sversion == 106 927 && $conf->ql("alter table PaperComment add `authorOrdinal` int(11) NOT NULL default '0'") 928 && $conf->ql("update PaperComment set authorOrdinal=ordinal where commentType>=" . COMMENTTYPE_AUTHOR)) 929 $conf->update_schema_version(107); 930 931 // repair missing comment ordinals; reset incorrect `ordinal`s for 932 // author-visible comments 933 if ($conf->sversion == 107) { 934 $result = $conf->ql("select paperId, commentId from PaperComment where ordinal=0 and (commentType&" . (COMMENTTYPE_RESPONSE | COMMENTTYPE_DRAFT) . ")=0 and commentType>=" . COMMENTTYPE_PCONLY . " and commentType<" . COMMENTTYPE_AUTHOR . " order by commentId"); 935 while (($row = edb_row($result))) { 936 $conf->ql("update PaperComment, 937(select coalesce(count(commentId),0) commentCount from Paper 938 left join PaperComment on (PaperComment.paperId=Paper.paperId and (commentType&" . (COMMENTTYPE_RESPONSE | COMMENTTYPE_DRAFT) . ")=0 and commentType>=" . COMMENTTYPE_PCONLY . " and commentType<" . COMMENTTYPE_AUTHOR . " and commentId<$row[1]) 939 where Paper.paperId=$row[0] group by Paper.paperId) t 940set ordinal=(t.commentCount+1) where commentId=$row[1]"); 941 } 942 943 $result = $conf->ql("select paperId, commentId from PaperComment where ordinal=0 and (commentType&" . (COMMENTTYPE_RESPONSE | COMMENTTYPE_DRAFT) . ")=0 and commentType>=" . COMMENTTYPE_AUTHOR . " order by commentId"); 944 while (($row = edb_row($result))) { 945 $conf->ql("update PaperComment, 946(select coalesce(count(commentId),0) commentCount from Paper 947 left join PaperComment on (PaperComment.paperId=Paper.paperId and (commentType&" . (COMMENTTYPE_RESPONSE | COMMENTTYPE_DRAFT) . ")=0 and commentType>=" . COMMENTTYPE_AUTHOR . " and commentId<$row[1]) 948 where Paper.paperId=$row[0] group by Paper.paperId) t 949set authorOrdinal=(t.commentCount+1) where commentId=$row[1]"); 950 } 951 952 $result = $conf->ql("select paperId, commentId from PaperComment where ordinal=authorOrdinal and (commentType&" . (COMMENTTYPE_RESPONSE | COMMENTTYPE_DRAFT) . ")=0 and commentType>=" . COMMENTTYPE_AUTHOR . " order by commentId"); 953 while (($row = edb_row($result))) { 954 $conf->ql("update PaperComment, 955(select coalesce(max(ordinal),0) maxOrdinal from Paper 956 left join PaperComment on (PaperComment.paperId=Paper.paperId and (commentType&" . (COMMENTTYPE_RESPONSE | COMMENTTYPE_DRAFT) . ")=0 and commentType>=" . COMMENTTYPE_PCONLY . " and commentType<" . COMMENTTYPE_AUTHOR . " and commentId<$row[1]) 957 where Paper.paperId=$row[0] group by Paper.paperId) t 958set ordinal=(t.maxOrdinal+1) where commentId=$row[1]"); 959 } 960 961 $conf->update_schema_version(108); 962 } 963 964 // contact tags format change 965 if ($conf->sversion == 108 966 && $conf->ql("update ContactInfo set contactTags=substr(replace(contactTags, ' ', '#0 ') from 3)") 967 && $conf->ql("update ContactInfo set contactTags=replace(contactTags, '#0#0 ', '#0 ')")) 968 $conf->update_schema_version(109); 969 if ($conf->sversion == 109 970 && $conf->ql("alter table PaperTag modify `tagIndex` float NOT NULL DEFAULT '0'")) 971 $conf->update_schema_version(110); 972 if ($conf->sversion == 110 973 && $conf->ql("alter table ContactInfo drop `faxPhoneNumber`") 974 && $conf->ql("alter table ContactInfo add `country` varbinary(256) default null") 975 && update_schema_transfer_country($conf)) 976 $conf->update_schema_version(111); 977 if ($conf->sversion == 111) { 978 update_schema_review_word_counts($conf); 979 $conf->update_schema_version(112); 980 } 981 if ($conf->sversion == 112 982 && $conf->ql("alter table ContactInfo add `passwordUseTime` int(11) NOT NULL DEFAULT '0'") 983 && $conf->ql("alter table ContactInfo add `updateTime` int(11) NOT NULL DEFAULT '0'") 984 && $conf->ql("update ContactInfo set passwordUseTime=lastLogin where passwordUseTime=0")) 985 $conf->update_schema_version(113); 986 if ($conf->sversion == 113 987 && $conf->ql("drop table if exists `PaperReviewArchive`")) 988 $conf->update_schema_version(114); 989 if ($conf->sversion == 114 990 && $conf->ql("alter table PaperReview add `timeDisplayed` int(11) NOT NULL DEFAULT '0'") 991 && $conf->ql("alter table PaperComment add `timeDisplayed` int(11) NOT NULL DEFAULT '0'")) 992 $conf->update_schema_version(115); 993 if ($conf->sversion == 115 994 && $conf->ql("alter table Formula drop column `authorView`")) 995 $conf->update_schema_version(116); 996 if ($conf->sversion == 116 997 && $conf->ql("alter table PaperComment add `commentOverflow` longblob DEFAULT NULL")) 998 $conf->update_schema_version(117); 999 if ($conf->sversion == 117 1000 && update_schema_drop_keys_if_exist($conf, "PaperTopic", ["paperTopic", "PRIMARY"]) 1001 && $conf->ql("alter table PaperTopic add primary key (`paperId`,`topicId`)") 1002 && update_schema_drop_keys_if_exist($conf, "TopicInterest", ["contactTopic", "PRIMARY"]) 1003 && $conf->ql("alter table TopicInterest add primary key (`contactId`,`topicId`)")) 1004 $conf->update_schema_version(118); 1005 if ($conf->sversion == 118 1006 && update_schema_drop_keys_if_exist($conf, "PaperTag", ["paperTag", "PRIMARY"]) 1007 && $conf->ql("alter table PaperTag add primary key (`paperId`,`tag`)") 1008 && update_schema_drop_keys_if_exist($conf, "PaperReviewPreference", ["paperId", "PRIMARY"]) 1009 && $conf->ql("alter table PaperReviewPreference add primary key (`paperId`,`contactId`)") 1010 && update_schema_drop_keys_if_exist($conf, "PaperConflict", ["contactPaper", "contactPaperConflict", "PRIMARY"]) 1011 && $conf->ql("alter table PaperConflict add primary key (`contactId`,`paperId`)") 1012 && $conf->ql("alter table MailLog modify `paperIds` blob") 1013 && $conf->ql("alter table MailLog modify `cc` blob") 1014 && $conf->ql("alter table MailLog modify `replyto` blob") 1015 && $conf->ql("alter table MailLog modify `subject` blob") 1016 && $conf->ql("alter table MailLog modify `emailBody` blob")) 1017 $conf->update_schema_version(119); 1018 if ($conf->sversion == 119 1019 && update_schema_drop_keys_if_exist($conf, "PaperWatch", ["contactPaper", "contactPaperWatch", "PRIMARY"]) 1020 && $conf->ql("alter table PaperWatch add primary key (`paperId`,`contactId`)")) 1021 $conf->update_schema_version(120); 1022 if ($conf->sversion == 120 1023 && $conf->ql("alter table Paper add `paperFormat` tinyint(1) DEFAULT NULL")) 1024 $conf->update_schema_version(121); 1025 if ($conf->sversion == 121 1026 && $conf->ql_raw("update PaperReview r, Paper p set r.reviewNeedsSubmit=1 where p.paperId=r.paperId and p.timeSubmitted<=0 and r.reviewSubmitted is null") 1027 && $conf->ql_raw("update PaperReview r, Paper p, PaperReview rq set r.reviewNeedsSubmit=0 where p.paperId=r.paperId and p.paperId=rq.paperId and p.timeSubmitted<=0 and r.reviewType=" . REVIEW_SECONDARY . " and r.contactId=rq.requestedBy and rq.reviewType<" . REVIEW_SECONDARY . " and rq.reviewSubmitted is not null") 1028 && $conf->ql_raw("update PaperReview r, Paper p, PaperReview rq set r.reviewNeedsSubmit=-1 where p.paperId=r.paperId and p.paperId=rq.paperId and p.timeSubmitted<=0 and r.reviewType=" . REVIEW_SECONDARY . " and r.contactId=rq.requestedBy and rq.reviewType<" . REVIEW_SECONDARY . " and r.reviewNeedsSubmit=0")) 1029 $conf->update_schema_version(122); 1030 if ($conf->sversion == 122 1031 && $conf->ql("alter table ReviewRequest add `reviewRound` int(1) DEFAULT NULL")) 1032 $conf->update_schema_version(123); 1033 if ($conf->sversion == 123 1034 && $conf->ql("update ContactInfo set disabled=1 where password='' and email regexp '^anonymous[0-9]*\$'")) 1035 $conf->update_schema_version(124); 1036 if ($conf->sversion == 124 1037 && $conf->ql("update ContactInfo set password='' where password='*' or passwordIsCdb")) 1038 $conf->update_schema_version(125); 1039 if ($conf->sversion == 125 1040 && $conf->ql("alter table ContactInfo drop column `passwordIsCdb`")) 1041 $conf->update_schema_version(126); 1042 if ($conf->sversion == 126 1043 && $conf->ql("update ContactInfo set disabled=1, password='' where email regexp '^anonymous[0-9]*\$'")) 1044 $conf->update_schema_version(127); 1045 if ($conf->sversion == 127 1046 && $conf->ql("update PaperReview set reviewWordCount=null")) 1047 $conf->update_schema_version(128); 1048 if ($conf->sversion == 128 1049 && update_schema_bad_comment_timeDisplayed($conf)) 1050 $conf->update_schema_version(129); 1051 if ($conf->sversion == 129 1052 && $conf->ql("update PaperComment set timeDisplayed=1 where timeDisplayed=0 and timeNotified>0")) 1053 $conf->update_schema_version(130); 1054 if ($conf->sversion == 130 1055 && $conf->ql("DROP TABLE IF EXISTS `PaperTagAnno`") 1056 && $conf->ql("CREATE TABLE `PaperTagAnno` ( 1057 `tag` varchar(40) NOT NULL, # see TAG_MAXLEN in header.php 1058 `annoId` int(11) NOT NULL, 1059 `tagIndex` float NOT NULL DEFAULT '0', 1060 `heading` varbinary(8192) DEFAULT NULL, 1061 `annoFormat` tinyint(1) DEFAULT NULL, 1062 `infoJson` varbinary(32768) DEFAULT NULL, 1063 PRIMARY KEY (`tag`,`annoId`) 1064) ENGINE=InnoDB DEFAULT CHARSET=utf8")) 1065 $conf->update_schema_version(131); 1066 if ($conf->sversion == 131 1067 && $conf->ql("alter table PaperStorage modify `infoJson` varbinary(32768) DEFAULT NULL")) 1068 $conf->update_schema_version(132); 1069 if ($conf->sversion == 132 1070 && $conf->ql("DROP TABLE IF EXISTS `Mimetype`") 1071 && $conf->ql("CREATE TABLE `Mimetype` ( 1072 `mimetypeid` int(11) NOT NULL, 1073 `mimetype` varbinary(200) NOT NULL, 1074 `extension` varbinary(10) DEFAULT NULL, 1075 `description` varbinary(200) DEFAULT NULL, 1076 `inline` tinyint(1) NOT NULL DEFAULT '0', 1077 PRIMARY KEY (`mimetypeid`), 1078 UNIQUE KEY `mimetypeid` (`mimetypeid`), 1079 UNIQUE KEY `mimetype` (`mimetype`) 1080) ENGINE=InnoDB DEFAULT CHARSET=utf8")) 1081 $conf->update_schema_version(133); 1082 if ($conf->sversion == 133) 1083 $conf->update_schema_version(134); 1084 if ($conf->sversion == 134) { 1085 foreach (Dbl::fetch_first_columns($conf->dblink, "select distinct mimetype from PaperStorage") as $mt) 1086 Mimetype::lookup($mt); 1087 if (!Dbl::has_error()) 1088 $conf->update_schema_version(135); 1089 } 1090 if ($conf->sversion == 135 1091 && $conf->ql("alter table PaperStorage add `mimetypeid` int(11) NOT NULL DEFAULT '0'") 1092 && $conf->ql("update PaperStorage, Mimetype set PaperStorage.mimetypeid=Mimetype.mimetypeid where PaperStorage.mimetype=Mimetype.mimetype")) 1093 $conf->update_schema_version(136); 1094 if ($conf->sversion == 136 1095 && $conf->ql("alter table PaperStorage drop key `paperId`") 1096 && $conf->ql("alter table PaperStorage drop key `mimetype`") 1097 && $conf->ql("alter table PaperStorage add key `byPaper` (`paperId`,`documentType`,`timestamp`,`paperStorageId`)")) 1098 $conf->update_schema_version(137); 1099 if ($conf->sversion == 137) 1100 $conf->update_schema_version(138); 1101 if (($conf->sversion == 138 || $conf->sversion == 139) 1102 && $conf->ql("DROP TABLE IF EXISTS `FilteredDocument`") 1103 && $conf->ql("CREATE TABLE `FilteredDocument` ( 1104 `inDocId` int(11) NOT NULL, 1105 `filterType` int(11) NOT NULL, 1106 `outDocId` int(11) NOT NULL, 1107 `createdAt` int(11) NOT NULL, 1108 PRIMARY KEY (`inDocId`,`filterType`), 1109 UNIQUE KEY `inDocFilter` (`inDocId`,`filterType`) 1110) ENGINE=InnoDB DEFAULT CHARSET=utf8")) 1111 $conf->update_schema_version(140); 1112 if ($conf->sversion == 140 1113 && $conf->ql("update Paper p join PaperStorage ps on (p.paperStorageId>1 and p.finalPaperStorageId<=0 and p.paperStorageId=ps.paperStorageId) set p.sha1=ps.sha1, p.timestamp=ps.timestamp, p.mimetype=ps.mimetype, p.size=ps.size where p.sha1!=ps.sha1 or p.timestamp!=ps.timestamp or p.mimetype!=ps.mimetype or p.size!=ps.size") 1114 && $conf->ql("update Paper p join PaperStorage ps on (p.finalPaperStorageId>0 and p.finalPaperStorageId=ps.paperStorageId) set p.sha1=ps.sha1, p.timestamp=ps.timestamp, p.mimetype=ps.mimetype, p.size=ps.size where p.sha1!=ps.sha1 or p.timestamp!=ps.timestamp or p.mimetype!=ps.mimetype or p.size!=ps.size")) 1115 $conf->update_schema_version(141); 1116 if ($conf->sversion == 141 1117 && $conf->ql("delete from Settings where name='pc'")) 1118 $conf->update_schema_version(142); 1119 if ($conf->sversion == 142 1120 && $conf->ql("alter table PaperReview add `reviewAuthorModified` int(1) DEFAULT NULL")) 1121 $conf->update_schema_version(143); 1122 if ($conf->sversion == 143 1123 && $conf->ql("alter table PaperReview add `timeApprovalRequested` int(11) NOT NULL DEFAULT '0'")) 1124 $conf->update_schema_version(144); 1125 if ($conf->sversion == 144 1126 && $conf->ql("alter table Paper add `pdfFormatStatus` int(11) NOT NULL DEFAULT '0'")) 1127 $conf->update_schema_version(145); 1128 if ($conf->sversion == 145 1129 && $conf->ql("alter table MailLog add `fromNonChair` tinyint(1) NOT NULL DEFAULT '0'")) 1130 $conf->update_schema_version(146); 1131 if ($conf->sversion == 146 1132 && $conf->ql("alter table Paper add `timeModified` int(11) NOT NULL DEFAULT '0'")) 1133 $conf->update_schema_version(147); 1134 if ($conf->sversion == 147 1135 && $conf->ql("alter table Capability change `capabilityId` `capabilityId` int(11) NOT NULL") 1136 && update_schema_drop_keys_if_exist($conf, "Capability", ["capabilityId", "PRIMARY"]) 1137 && $conf->ql("alter table Capability add primary key (`salt`)") 1138 && $conf->ql("alter table Capability drop column `capabilityId`")) 1139 $conf->update_schema_version(148); 1140 if ($conf->sversion == 148 1141 && $conf->ql("alter table ReviewRating add `paperId` int(11) NOT NULL DEFAULT '0'") 1142 && $conf->ql("update ReviewRating join PaperReview using (reviewId) set ReviewRating.paperId=PaperReview.paperId") 1143 && $conf->ql("alter table ReviewRating change `paperId` `paperId` int(11) NOT NULL") 1144 && update_schema_drop_keys_if_exist($conf, "ReviewRating", ["reviewContact", "reviewContactRating"]) 1145 && $conf->ql("alter table ReviewRating add primary key (`paperId`,`reviewId`,`contactId`)")) 1146 $conf->update_schema_version(149); 1147 if ($conf->sversion == 149 1148 && update_schema_drop_keys_if_exist($conf, "PaperReview", ["PRIMARY"]) 1149 && $conf->ql("alter table PaperReview add primary key (`paperId`,`reviewId`)")) 1150 $conf->update_schema_version(150); 1151 if ($conf->sversion == 150 1152 && update_schema_drop_keys_if_exist($conf, "PaperComment", ["PRIMARY"]) 1153 && $conf->ql("alter table PaperComment add primary key (`paperId`,`commentId`)") 1154 && update_schema_drop_keys_if_exist($conf, "PaperStorage", ["PRIMARY"]) 1155 && $conf->ql("alter table PaperStorage add primary key (`paperId`,`paperStorageId`)")) 1156 $conf->update_schema_version(151); 1157 if ($conf->sversion == 151 1158 && update_schema_drop_keys_if_exist($conf, "ContactInfo", ["rolesCid", "rolesContactId", "contactIdRoles"]) 1159 && $conf->ql("alter table ContactInfo add key `rolesContactId` (`roles`,`contactId`)")) 1160 $conf->update_schema_version(152); 1161 if ($conf->sversion == 152 1162 && update_schema_drop_keys_if_exist($conf, "PaperReview", ["reviewSubmitted"]) 1163 && update_schema_drop_keys_if_exist($conf, "PaperComment", ["timeModified", "paperId", "contactPaper"]) 1164 && $conf->ql("alter table PaperComment add key `timeModifiedContact` (`timeModified`,`contactId`)") 1165 && $conf->ql("alter table PaperReview add key `reviewSubmittedContact` (`reviewSubmitted`,`contactId`)")) 1166 $conf->update_schema_version(153); 1167 if ($conf->sversion == 153 1168 && update_schema_mimetype_extensions($conf)) 1169 $conf->update_schema_version(154); 1170 if ($conf->sversion == 154) { 1171 if ($conf->fetch_value("select tag from PaperTag where tag like ':%:' limit 1")) 1172 $conf->save_setting("has_colontag", 1); 1173 $conf->update_schema_version(155); 1174 } 1175 if ($conf->sversion == 155) { 1176 if ($conf->fetch_value("select tag from PaperTag where tag like '%:' limit 1")) 1177 $conf->save_setting("has_colontag", 1); 1178 $conf->update_schema_version(156); 1179 } 1180 if ($conf->sversion == 156 1181 && $conf->ql("delete from TopicInterest where interest is null") 1182 && $conf->ql("alter table TopicInterest change `interest` `interest` int(1) NOT NULL") 1183 && $conf->ql("update TopicInterest set interest=1 where interest=2") 1184 && $conf->ql("update TopicInterest set interest=2 where interest=4") 1185 && $conf->ql("delete from TopicInterest where interest=0")) 1186 $conf->update_schema_version(157); 1187 if ($conf->sversion == 157 1188 && $conf->ql("alter table PaperOption drop key `paperOption`") 1189 && $conf->ql("alter table PaperOption add primary key (`paperId`,`optionId`,`value`)") 1190 && $conf->ql("alter table PaperOption change `data` `data` varbinary(32767) DEFAULT NULL") 1191 && $conf->ql("alter table PaperOption add `dataOverflow` longblob DEFAULT NULL")) 1192 $conf->update_schema_version(158); 1193 if ($conf->sversion == 158 1194 && $conf->ql("alter table ContactInfo drop key `rolesContactId`") 1195 && $conf->ql("alter table ContactInfo add unique key `rolesContactId` (`roles`,`contactId`)")) 1196 $conf->update_schema_version(159); 1197 if ($conf->sversion == 159 1198 && $conf->ql("alter table ActionLog drop key `logId`") 1199 && $conf->ql("alter table Capability drop key `salt`") 1200 && $conf->ql("alter table ContactInfo drop key `contactId`") 1201 && $conf->ql("alter table FilteredDocument drop key `inDocFilter`") 1202 && $conf->ql("alter table Formula drop key `formulaId`") 1203 && $conf->ql("alter table Mimetype drop key `mimetypeid`") 1204 && $conf->ql("alter table Paper drop key `paperId`") 1205 && $conf->ql("alter table TopicArea drop key `topicId`")) 1206 $conf->update_schema_version(160); 1207 if ($conf->sversion == 160 1208 && $conf->ql("alter table Paper change `sha1` `sha1` varbinary(64) NOT NULL DEFAULT ''") 1209 && $conf->ql("alter table PaperStorage change `sha1` `sha1` varbinary(64) NOT NULL DEFAULT ''")) 1210 $conf->update_schema_version(161); 1211 if ($conf->sversion == 161 1212 && $conf->ql("alter table PaperTag change `tag` `tag` varbinary(80) NOT NULL") 1213 && $conf->ql("alter table PaperTagAnno change `tag` `tag` varbinary(80) NOT NULL")) 1214 $conf->update_schema_version(162); 1215 if ($conf->sversion == 162 1216 && $conf->ql("alter table PaperTag change `tag` `tag` varchar(80) NOT NULL") 1217 && $conf->ql("alter table PaperTagAnno change `tag` `tag` varchar(80) NOT NULL")) 1218 $conf->update_schema_version(163); 1219 if ($conf->sversion == 163 1220 && $conf->ql("alter table Capability change `timeExpires` `timeExpires` bigint(11) NOT NULL") 1221 && $conf->ql("alter table ContactInfo change `passwordTime` `passwordTime` bigint(11) NOT NULL DEFAULT '0'") 1222 && $conf->ql("alter table ContactInfo change `passwordUseTime` `passwordUseTime` bigint(11) NOT NULL DEFAULT '0'") 1223 && $conf->ql("alter table ContactInfo change `creationTime` `creationTime` bigint(11) NOT NULL DEFAULT '0'") 1224 && $conf->ql("alter table ContactInfo change `updateTime` `updateTime` bigint(11) NOT NULL DEFAULT '0'") 1225 && $conf->ql("alter table ContactInfo change `lastLogin` `lastLogin` bigint(11) NOT NULL DEFAULT '0'") 1226 && $conf->ql("alter table FilteredDocument change `createdAt` `createdAt` bigint(11) NOT NULL") 1227 && $conf->ql("alter table Formula change `timeModified` `timeModified` bigint(11) NOT NULL DEFAULT '0'") 1228 && $conf->ql("alter table Paper change `timeSubmitted` `timeSubmitted` bigint(11) NOT NULL DEFAULT '0'") 1229 && $conf->ql("alter table Paper change `timeWithdrawn` `timeWithdrawn` bigint(11) NOT NULL DEFAULT '0'") 1230 && $conf->ql("alter table Paper change `timeFinalSubmitted` `timeFinalSubmitted` bigint(11) NOT NULL DEFAULT '0'") 1231 && $conf->ql("alter table Paper change `timeModified` `timeModified` bigint(11) NOT NULL DEFAULT '0'") 1232 && $conf->ql("alter table Paper change `timestamp` `timestamp` bigint(11) NOT NULL DEFAULT '0'") 1233 && $conf->ql("alter table Paper change `pdfFormatStatus` `pdfFormatStatus` bigint(11) NOT NULL DEFAULT '0'") 1234 && $conf->ql("alter table PaperComment change `timeModified` `timeModified` bigint(11) NOT NULL") 1235 && $conf->ql("alter table PaperComment change `timeNotified` `timeNotified` bigint(11) NOT NULL DEFAULT '0'") 1236 && $conf->ql("alter table PaperComment change `timeDisplayed` `timeDisplayed` bigint(11) NOT NULL DEFAULT '0'") 1237 && $conf->ql("alter table PaperOption change `value` `value` bigint(11) NOT NULL DEFAULT '0'") 1238 && $conf->ql("alter table PaperReview change `timeRequested` `timeRequested` bigint(11) NOT NULL DEFAULT '0'") 1239 && $conf->ql("alter table PaperReview change `timeRequestNotified` `timeRequestNotified` bigint(11) NOT NULL DEFAULT '0'") 1240 && $conf->ql("alter table PaperReview change `reviewModified` `reviewModified` bigint(1) DEFAULT NULL") 1241 && $conf->ql("alter table PaperReview change `reviewAuthorModified` `reviewAuthorModified` bigint(1) DEFAULT NULL") 1242 && $conf->ql("alter table PaperReview change `reviewSubmitted` `reviewSubmitted` bigint(1) DEFAULT NULL") 1243 && $conf->ql("alter table PaperReview change `reviewNotified` `reviewNotified` bigint(1) DEFAULT NULL") 1244 && $conf->ql("alter table PaperReview change `reviewAuthorNotified` `reviewAuthorNotified` bigint(11) NOT NULL DEFAULT '0'") 1245 && $conf->ql("alter table PaperReview change `reviewAuthorSeen` `reviewAuthorSeen` bigint(1) DEFAULT NULL") 1246 && $conf->ql("alter table PaperReview change `timeDisplayed` `timeDisplayed` bigint(11) NOT NULL DEFAULT '0'") 1247 && $conf->ql("alter table PaperReview change `timeApprovalRequested` `timeApprovalRequested` bigint(11) NOT NULL DEFAULT '0'") 1248 && $conf->ql("alter table PaperStorage change `timestamp` `timestamp` bigint(11) NOT NULL") 1249 && $conf->ql("alter table Settings change `value` `value` bigint(11) NOT NULL")) 1250 $conf->update_schema_version(164); 1251 if ($conf->sversion == 164 1252 && $conf->ql("alter table Paper change `title` `title` varbinary(512) DEFAULT NULL")) 1253 $conf->update_schema_version(165); 1254 if ($conf->sversion == 165 1255 && $conf->ql("alter table TopicArea drop key `topicName`") 1256 && $conf->ql("alter table TopicArea change `topicName` `topicName` varbinary(1024) DEFAULT NULL")) 1257 $conf->update_schema_version(166); 1258 if ($conf->sversion == 166 1259 && $conf->ql("alter table PaperReviewPreference drop key `contactPaper`")) 1260 $conf->update_schema_version(167); 1261 if ($conf->sversion == 167 1262 && $conf->ql("update PaperReview set reviewOrdinal=0 where reviewOrdinal is null") 1263 && $conf->ql("alter table PaperReview change `reviewOrdinal` `reviewOrdinal` int(1) NOT NULL DEFAULT '0'")) 1264 $conf->update_schema_version(168); 1265 if ($conf->sversion == 168 1266 && $conf->ql("update PaperReview set reviewModified=0 where reviewModified is null") 1267 && $conf->ql("alter table PaperReview change `reviewModified` `reviewModified` bigint(1) NOT NULL DEFAULT '0'")) 1268 $conf->update_schema_version(169); 1269 if ($conf->sversion == 169) { 1270 if ($conf->fetch_ivalue("select exists (select * from TopicArea)")) 1271 $conf->save_setting("has_topics", 1); 1272 $conf->update_schema_version(170); 1273 } 1274 if ($conf->sversion == 170 1275 && $conf->ql("alter table ActionLog drop key `contactId`") 1276 && $conf->ql("alter table ActionLog drop key `paperId`") 1277 && $conf->ql("alter table ActionLog add `destContactId` int(11) NOT NULL DEFAULT '0'")) 1278 $conf->update_schema_version(171); 1279 if ($conf->sversion == 171 1280 && $conf->ql("DROP TABLE IF EXISTS `DeletedContactInfo`") 1281 && $conf->ql("CREATE TABLE `DeletedContactInfo` ( 1282 `contactId` int(11) NOT NULL, 1283 `firstName` varchar(60) NOT NULL, 1284 `lastName` varchar(60) NOT NULL, 1285 `email` varchar(120) NOT NULL 1286) ENGINE=InnoDB DEFAULT CHARSET=utf8")) 1287 $conf->update_schema_version(172); 1288 if ($conf->sversion == 172 1289 && $conf->ql("alter table DeletedContactInfo add `unaccentedName` varchar(120) NOT NULL DEFAULT ''") 1290 && $conf->ql("alter table DeletedContactInfo change `unaccentedName` `unaccentedName` varchar(120) NOT NULL")) 1291 $conf->update_schema_version(173); 1292 if ($conf->sversion == 173 1293 && update_schema_paper_review_tfields($conf)) 1294 $conf->update_schema_version(174); 1295 if ($conf->sversion == 174 1296 && update_schema_paper_review_null_main_fields($conf)) 1297 $conf->update_schema_version(175); 1298 if ($conf->sversion == 175 1299 && update_schema_paper_review_drop_main_fields($conf)) 1300 $conf->update_schema_version(176); 1301 if ($conf->sversion == 176) { 1302 if (($x = $conf->setting_data("scoresort_default"))) { 1303 $conf->save_setting("scoresort_default", null); 1304 $conf->save_setting("opt.defaultScoreSort", 1, $x); 1305 } 1306 $conf->update_schema_version(177); 1307 } 1308 if ($conf->sversion == 177 1309 && $conf->ql("alter table PaperStorage drop `mimetypeid`") 1310 && $conf->ql("drop table if exists `Mimetype`")) 1311 $conf->update_schema_version(178); 1312 if ($conf->sversion == 178 1313 && $conf->ql("delete from Settings where name='papersub'")) { 1314 $conf->update_papersub_setting(0); 1315 $conf->update_schema_version(179); 1316 } 1317 if ($conf->sversion == 179 1318 && $conf->ql("alter table ContactInfo change `affiliation` `affiliation` varbinary(2048) NOT NULL DEFAULT ''") 1319 && $conf->ql("alter table ContactInfo change `voicePhoneNumber` `voicePhoneNumber` varbinary(256) DEFAULT NULL")) 1320 $conf->update_schema_version(180); 1321 if ($conf->sversion == 180 1322 && $conf->ql("alter table ActionLog change `ipaddr` `ipaddr` varbinary(39) DEFAULT NULL")) 1323 $conf->update_schema_version(181); 1324 if ($conf->sversion == 181 1325 && $conf->ql("alter table ContactInfo change `firstName` `firstName` varbinary(120) NOT NULL DEFAULT ''") 1326 && $conf->ql("alter table ContactInfo change `lastName` `lastName` varbinary(120) NOT NULL DEFAULT ''") 1327 && $conf->ql("alter table ContactInfo change `unaccentedName` `unaccentedName` varbinary(240) NOT NULL DEFAULT ''") 1328 && $conf->ql("alter table DeletedContactInfo change `firstName` `firstName` varbinary(120) NOT NULL") 1329 && $conf->ql("alter table DeletedContactInfo change `lastName` `lastName` varbinary(120) NOT NULL") 1330 && $conf->ql("alter table DeletedContactInfo change `unaccentedName` `unaccentedName` varbinary(240) NOT NULL")) 1331 $conf->update_schema_version(182); 1332 if ($conf->sversion == 182 1333 && $conf->ql("alter table ContactInfo add `birthday` int(11) DEFAULT NULL") 1334 && $conf->ql("alter table ContactInfo add `gender` varbinary(24) DEFAULT NULL")) 1335 $conf->update_schema_version(183); 1336 if ($conf->sversion == 183 1337 // good=1,1; too short=0,4; too vague=-1,8; too narrow=-4,16; 1338 // not constructive=-2,32; not correct=-3,64 1339 && $conf->ql("update ReviewRating set rating=case rating when 0 then 4 when -1 then 8 when -4 then 16 when -2 then 32 when -3 then 64 else if(rating<0,2,1) end")) 1340 $conf->update_schema_version(184); 1341 if ($conf->sversion == 184 1342 && $conf->ql("alter table PaperReview drop key `reviewSubmittedContact`")) 1343 $conf->update_schema_version(185); 1344 if ($conf->sversion == 185 1345 && $conf->ql("alter table ContactInfo change `voicePhoneNumber` `phone` varbinary(64) DEFAULT NULL")) 1346 $conf->update_schema_version(186); 1347 if ($conf->sversion == 186 1348 && $conf->ql("alter table PaperReviewRefused add primary key (`paperId`,`contactId`)") 1349 && $conf->ql("alter table PaperReviewRefused drop key `paperId`") 1350 && $conf->ql("alter table PaperReviewRefused drop key `contactId`") 1351 && $conf->ql("alter table PaperReviewRefused drop key `requestedBy`")) 1352 $conf->update_schema_version(187); 1353 if ($conf->sversion == 187 1354 && $conf->ql("alter table ReviewRequest change `email` `email` varchar(120) NOT NULL") 1355 && $conf->ql("alter table ReviewRequest add primary key (`paperId`,`email`)") 1356 && $conf->ql("alter table ReviewRequest drop key `paperEmail`") 1357 && $conf->ql("alter table ReviewRequest drop key `paperId`") 1358 && $conf->ql("alter table ReviewRequest drop key `requestedBy`")) 1359 $conf->update_schema_version(188); 1360 if ($conf->sversion == 188 1361 && update_schema_split_review_request_name($conf)) 1362 $conf->update_schema_version(189); 1363 if ($conf->sversion == 189 1364 && $conf->ql("alter table ReviewRequest add `affiliation` varbinary(2048) DEFAULT NULL")) 1365 $conf->update_schema_version(190); 1366 if ($conf->sversion == 190) { 1367 if ($conf->setting("rev_notifychair") > 0) { 1368 $conf->ql("update ContactInfo set defaultWatch=defaultWatch|" . Contact::WATCH_REVIEW_ALL . " where roles!=0 and (roles&" . Contact::ROLE_CHAIR . ")!=0"); 1369 $conf->ql("delete from Settings where name=?", "rev_notifychair"); 1370 } 1371 $conf->update_schema_version(191); 1372 } 1373 if ($conf->sversion == 191) { 1374 update_schema_missing_sha1($conf); 1375 $conf->update_schema_version(192); 1376 } 1377 if ($conf->sversion == 192 1378 && $conf->ql("alter table PaperStorage drop key `byPaper`")) 1379 $conf->update_schema_version(193); 1380 if ($conf->sversion == 193 1381 && $conf->ql("alter table Settings change `name` `name` varbinary(256) NOT NULL") 1382 && $conf->ql("alter table Settings add primary key (`name`)") 1383 && $conf->ql("alter table Settings drop key `name`")) 1384 $conf->update_schema_version(194); 1385 if ($conf->sversion == 194 1386 && $conf->ql("alter table ContactInfo drop key `rolesContactId`") 1387 && $conf->ql("alter table ContactInfo add key `roles` (`roles`)") 1388 && $conf->ql("alter table ContactInfo drop key `fullName`") 1389 && $conf->ql("alter table PaperReview drop key `contactPaper`") 1390 && $conf->ql("alter table PaperReview add key `contactId` (`contactId`)") 1391 && $conf->ql("alter table PaperReview drop key `reviewNeedsSubmit`") 1392 && $conf->ql("alter table PaperReview drop key `paperId`")) 1393 $conf->update_schema_version(195); 1394 if ($conf->sversion == 195 1395 && $conf->ql("alter table PaperStorage add `inactive` tinyint(1) NOT NULL DEFAULT '0'") 1396 && $conf->ql("update PaperStorage set inactive=1") 1397 && $conf->ql("update PaperStorage join Paper on (Paper.paperId=PaperStorage.paperId and Paper.paperStorageId=PaperStorage.paperStorageId) set PaperStorage.inactive=0") 1398 && $conf->ql("update PaperStorage join Paper on (Paper.paperId=PaperStorage.paperId and Paper.finalPaperStorageId=PaperStorage.paperStorageId) set PaperStorage.inactive=0") 1399 && $conf->ql("update PaperStorage join PaperOption on (PaperOption.paperId=PaperStorage.paperId and PaperOption.value=PaperStorage.paperStorageId) set PaperStorage.inactive=0")) 1400 $conf->update_schema_version(196); 1401 if ($conf->sversion == 196 1402 && $conf->ql("drop table if exists `DocumentLink`") 1403 && $conf->ql("create table `DocumentLink` ( 1404 `paperId` int(11) NOT NULL, 1405 `linkId` int(11) NOT NULL, 1406 `linkType` int(11) NOT NULL, 1407 `documentId` int(11) NOT NULL, 1408 PRIMARY KEY (`paperId`,`linkId`,`linkType`) 1409) ENGINE=InnoDB DEFAULT CHARSET=utf8")) 1410 $conf->update_schema_version(197); 1411 if ($conf->sversion == 197 1412 && $conf->ql("alter table PaperConflict add key `paperId` (`paperId`)")) 1413 $conf->update_schema_version(198); 1414 if ($conf->sversion == 198 1415 && update_schema_selector_options($conf)) 1416 $conf->update_schema_version(199); 1417 1418 $conf->ql("delete from Settings where name='__schema_lock'"); 1419 Conf::$g = $old_conf_g; 1420} 1421