select PaperComment.*, substring(PaperComment.comment from 1 for ?) as shortComment, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) join Paper on (Paper.paperId=PaperComment.paperId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) where PaperComment.timeModified<? order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc limit ?
select PaperComment.*, substring(PaperComment.comment from 1 for ?) as shortComment, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) join Paper on (Paper.paperId=PaperComment.paperId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) where (PaperComment.timeModified<? or (PaperComment.timeModified=? and PaperComment.contactId>?) or (PaperComment.timeModified=? and PaperComment.contactId=? and PaperComment.paperId>?)) order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc limit ?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select name, value, data from Settings
select ContactInfo.contactDbId, Conferences.confid, roles, password from ContactInfo left join Conferences on (Conferences.`dbname`=?) left join Roles on (Roles.contactDbId=ContactInfo.contactDbId and Roles.confid=Conferences.confid) where email=?
select * from ContactInfo where email=?
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where leadContactId=u.contactId and conflictType is null) numLeads, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where shepherdContactId=u.contactId and conflictType is null) numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select r.contactId, r.reviewId, r.reviewSubmitted, r.reviewNeedsSubmit, r.overAllMerit, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) left join ReviewRating rr on (rr.paperId=r.paperId and rr.reviewId=r.reviewId) where (pc.conflictType is null or pc.conflictType=0 or r.contactId=?) and (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select count(reviewId) num_submitted, group_concat(overAllMerit) scores from ContactInfo left join PaperReview on (PaperReview.contactId=ContactInfo.contactId and PaperReview.reviewSubmitted is not null) where roles!=0 and (roles&1)!=0 group by ContactInfo.contactId
select (select max(conflictType) from PaperConflict where contactId=?), (select paperId from PaperReview where contactId=? limit 1)
alter table `PaperComment` drop key `timeModified`, drop key `paperId`, drop key `contactPaper`
select PaperReview.*, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) join Paper on (Paper.paperId=PaperReview.paperId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) where PaperReview.reviewSubmitted<? and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
select PaperReview.*, ContactInfo.firstName, ContactInfo.lastName, ContactInfo.email, ContactInfo.roles as contactRoles, ContactInfo.contactTags, ReqCI.firstName as reqFirstName, ReqCI.lastName as reqLastName, ReqCI.email as reqEmail, (select group_concat(rating) from ReviewRating where paperId=PaperReview.paperId and reviewId=PaperReview.reviewId) allRatings, (select rating from ReviewRating where paperId=PaperReview.paperId and reviewId=PaperReview.reviewId and contactId=?) myRating from PaperReview join ContactInfo using (contactId) left join ContactInfo as ReqCI on (ReqCI.contactId=PaperReview.requestedBy) where PaperReview.paperId=? group by PaperReview.reviewId order by PaperReview.paperId, reviewOrdinal, timeRequested, reviewType desc, reviewId
select infoJson from PaperStorage where paperId=? and paperStorageId=?
select (select max(conflictType) from PaperConflict where contactId=?), (select paperId from PaperReview where contactId=? or reviewToken?a limit 1)
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, (select count(paperId) from Paper where leadContactId=u.contactId) numLeads, (select count(paperId) from Paper where shepherdContactId=u.contactId) numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select r.*, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join ReviewRating rr on (rr.paperId=r.paperId and rr.reviewId=r.reviewId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, (select count(paperId) from Paper where leadContactId=u.contactId) numLeads, (select count(paperId) from Paper where shepherdContactId=u.contactId) numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select r.*, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join ReviewRating rr on (rr.reviewId=r.reviewId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select outcome, count(paperId) from Paper where timeSubmitted>0 or (timeSubmitted=? and timeWithdrawn>=?) group by outcome
select firstName, lastName, affiliation, email, contactId, roles, contactTags, disabled from ContactInfo where roles!=0 and (roles&1)!=0
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, topicIds, topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, leadPaperIds, numLeads, shepherdPaperIds, numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select contactId, group_concat(topicId) as topicIds, group_concat(interest) as topicInterest from TopicInterest group by contactId) as ti on (ti.contactId=u.contactId) left join (select r.*, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join ReviewRating rr on (rr.reviewId=r.reviewId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) left join (select p.leadContactId, group_concat(p.paperId) as leadPaperIds, count(p.paperId) as numLeads from Paper p where leadContactId is not null group by p.leadContactId) as lead on (lead.leadContactId=u.contactId) left join (select p.shepherdContactId, group_concat(p.paperId) as shepherdPaperIds, count(p.paperId) as numShepherds from Paper p where shepherdContactId is not null group by p.shepherdContactId) as shep on (shep.shepherdContactId=u.contactId) where (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, topicIds, topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, leadPaperIds, numLeads, shepherdPaperIds, numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select contactId, group_concat(topicId) as topicIds, group_concat(interest) as topicInterest from TopicInterest group by contactId) as ti on (ti.contactId=u.contactId) left join (select r.*, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join ReviewRating rr on (rr.reviewId=r.reviewId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) left join (select p.leadContactId, group_concat(p.paperId) as leadPaperIds, count(p.paperId) as numLeads from Paper p where leadContactId is not null group by p.leadContactId) as lead on (lead.leadContactId=u.contactId) left join (select p.shepherdContactId, group_concat(p.paperId) as shepherdPaperIds, count(p.paperId) as numShepherds from Paper p where shepherdContactId is not null group by p.shepherdContactId) as shep on (shep.shepherdContactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, topicIds, topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, leadPaperIds, numLeads, shepherdPaperIds, numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select contactId, group_concat(topicId) as topicIds, group_concat(interest) as topicInterest from TopicInterest group by contactId) as ti on (ti.contactId=u.contactId) left join (select r.*, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) left join ReviewRating rr on (rr.reviewId=r.reviewId and not (rr.reviewId in (???))) where (pc.conflictType is null or pc.conflictType=0 or r.contactId=?) and (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) left join (select p.leadContactId, group_concat(p.paperId) as leadPaperIds, count(p.paperId) as numLeads from Paper p left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) where leadContactId is not null and (conflictType is null or conflictType=0) group by p.leadContactId) as lead on (lead.leadContactId=u.contactId) left join (select p.shepherdContactId, group_concat(p.paperId) as shepherdPaperIds, count(p.paperId) as numShepherds from Paper p left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) where shepherdContactId is not null and (conflictType is null or conflictType=0) group by p.shepherdContactId) as shep on (shep.shepherdContactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
alter table PaperReview add key `reviewSubmittedContact` (`reviewSubmitted`,`contactId`)
alter table PaperComment add key `timeModifiedContact` (`timeModified`,`contactId`)
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, coalesce(PaperReviewPreference.preference, 0) as reviewerPreference, PaperReviewPreference.expertise as reviewerExpertise from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join PaperReviewPreference on (PaperReviewPreference.paperId=Paper.paperId and PaperReviewPreference.contactId=?) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select ContactInfo.contactId, firstName, lastName, email, password, contactTags, roles, defaultWatch, PaperReview.reviewType myReviewType, PaperReview.reviewSubmitted myReviewSubmitted, PaperReview.reviewNeedsSubmit myReviewNeedsSubmit, conflictType, watch, preferredEmail, disabled from ContactInfo left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=ContactInfo.contactId) left join PaperWatch on (PaperWatch.paperId=? and PaperWatch.contactId=ContactInfo.contactId) left join PaperReview on (PaperReview.paperId=? and PaperReview.contactId=ContactInfo.contactId) left join PaperComment on (PaperComment.paperId=? and PaperComment.contactId=ContactInfo.contactId) where watch is not null or conflictType>=? or reviewType is not null or commentId is not null or (defaultWatch & ?)!=0 order by conflictType
update ContactInfo set lastLogin=? where contactId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select contactId, conflictType from PaperConflict where paperId=?
select PaperComment.*, firstName reviewFirstName, lastName reviewLastName, email reviewEmail from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) where PaperComment.paperId=? order by commentId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where true and PaperConflict.conflictType>=? group by Paper.paperId
select count(reviewSubmitted) num_submitted, count(if(reviewNeedsSubmit=0,reviewSubmitted,1)) num_needs_submit, group_concat(if(reviewSubmitted is not null,overAllMerit,null)) scores, group_concat(distinct if(reviewNeedsSubmit!=0 and reviewSubmitted is null,reviewRound,null)) unsubmitted_rounds from PaperReview join Paper using (paperId) where (PaperReview.contactId=?) and (reviewSubmitted is not null or timeSubmitted>0) group by PaperReview.reviewId>0
select ContactInfo.contactId, reviewType, commentId, conflictType, watch from ContactInfo left join PaperReview on (PaperReview.paperId=? and PaperReview.contactId=ContactInfo.contactId) left join PaperComment on (PaperComment.paperId=? and PaperComment.contactId=ContactInfo.contactId) left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=ContactInfo.contactId) left join PaperWatch on (PaperWatch.paperId=? and PaperWatch.contactId=ContactInfo.contactId) where ContactInfo.contactId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and (PaperConflict.conflictType>=? or (Paper.timeWithdrawn<=0 and MyReview.reviewType is not null)) group by Paper.paperId
select MPR.reviewId from PaperReview as MPR left join (select paperId, count(reviewId) as numReviews from PaperReview where reviewType>1 and reviewNeedsSubmit=0 group by paperId) as NPR on (NPR.paperId=MPR.paperId) left join (select paperId, count(rating) as numRatings from PaperReview join ReviewRating using (paperId,reviewId) group by paperId) as NRR on (NRR.paperId=MPR.paperId) where MPR.contactId=? and numReviews<=? and numRatings<=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where true and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select PaperReview.*, ContactInfo.firstName, ContactInfo.lastName, ContactInfo.email, ContactInfo.roles as contactRoles, ContactInfo.contactTags, ReqCI.firstName as reqFirstName, ReqCI.lastName as reqLastName, ReqCI.email as reqEmail from PaperReview join ContactInfo using (contactId) left join ContactInfo as ReqCI on (ReqCI.contactId=PaperReview.requestedBy) where PaperReview.paperId=? group by PaperReview.reviewId order by PaperReview.paperId, reviewOrdinal, timeRequested, reviewType desc, reviewId
alter table `PaperReview` drop key `reviewSubmitted`
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.title title, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.abstract abstract, Paper.authorInformation authorInformation, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((true) or (true) or (true)) and Paper.timeSubmitted>0 group by Paper.paperId
select r.reviewId from PaperReview r join Paper p on (p.paperId=r.paperId and p.timeSubmitted>0) where (r.contactId=?) and r.reviewNeedsSubmit!=0 limit 1
select Paper.paperId, reviewId, reviewType, reviewSubmitted, reviewModified, timeApprovalRequested, reviewNeedsSubmit, reviewRound, reviewOrdinal, timeRequested, PaperReview.contactId, lastName, firstName, email from Paper join PaperReview using (paperId) join ContactInfo on (PaperReview.contactId=ContactInfo.contactId) where paperId?a
insert into ActionLog set ipaddr=?, contactId=?, paperId=NULL, action=?
select topicId, topicName from TopicArea order by topicName
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, (select count(paperId) from Paper where leadContactId=u.contactId) numLeads, (select count(paperId) from Paper where shepherdContactId=u.contactId) numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select r.contactId, r.reviewId, r.reviewSubmitted, r.reviewNeedsSubmit, r.overAllMerit, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join ReviewRating rr on (rr.paperId=r.paperId and rr.reviewId=r.reviewId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select reviewId from PaperReview where reviewRound=0 limit 1
select firstName, lastName, '' affiliation, email, contactId from ContactInfo where contactId?a
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select PaperReview.*, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) join Paper on (Paper.paperId=PaperReview.paperId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and PaperReview.reviewSubmitted<? and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select ContactInfo.contactId, conflictType, email, firstName, lastName, affiliation from PaperConflict join ContactInfo using (contactId) where paperId=? and conflictType>=?
insert into PaperReviewPreference (paperId,contactId,preference,expertise) values (???) on duplicate key update preference=values(preference), expertise=values(expertise)
insert into ActionLog set ipaddr=?, contactId=?, paperId=?, action=?
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select ContactInfo.contactId, conflictType, email from PaperConflict join ContactInfo using (contactId) where paperId=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where paperId=? group by paperId
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where leadContactId=u.contactId and conflictType is null) numLeads, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where shepherdContactId=u.contactId and conflictType is null) numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select r.contactId, r.reviewId, r.reviewSubmitted, r.reviewNeedsSubmit, r.overAllMerit from PaperReview r join Paper p on (p.paperId=r.paperId) left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) where (pc.conflictType is null or pc.conflictType=0 or r.contactId=?) and (p.timeSubmitted>0 or r.reviewSubmitted>0)) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select requestedBy from PaperReview where requestedBy=? and contactId!=? limit 1
select topicId, interest from TopicInterest where contactId=? and interest!=0
insert into Settings (name,value,data) values (???) on duplicate key update value=?, data=?
update Settings set value=value-1 where name=? and data=?
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, numReviews, numReviewsSubmitted, numRatings, sumRatings, (select count(paperId) from Paper where leadContactId=u.contactId) numLeads, (select count(paperId) from Paper where shepherdContactId=u.contactId) numShepherds, overAllMerit from ContactInfo u left join (select contactId, count(if(reviewNeedsSubmit<=0,reviewSubmitted,reviewId)) numReviews, count(reviewSubmitted) numReviewsSubmitted, group_concat(if(reviewSubmitted>0,overAllMerit,null)) overAllMerit from PaperReview r join Paper p on (p.paperId=r.paperId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.contactId) as r on (r.contactId=u.contactId) left join (select PaperReview.contactId, count(rating) numRatings, sum(if(rating>0,1,0)) sumRatings from ReviewRating join PaperReview on (PaperReview.paperId=ReviewRating.paperId and PaperReview.reviewId=ReviewRating.reviewId) group by PaperReview.contactId) as rr on (rr.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 order by lastName, firstName, email
select paperStorageId, paperId, timestamp, mimetype, mimetypeid, sha1, documentType, filename, infoJson, size, filterType, originalStorageId from PaperStorage where paperId=? and paperStorageId?a
insert into ContactInfo set lastName=?, firstName=?, email=?, unaccentedName=?, disabled=?, updateTime=?, creationTime=?, password=?, passwordTime=?
update ContactInfo set passwordUseTime=? where contactDbId=?
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select PaperReview.*, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) join Paper on (Paper.paperId=PaperReview.paperId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and (PaperReview.reviewSubmitted<? or (PaperReview.reviewSubmitted=? and PaperReview.contactId>?) or (PaperReview.reviewSubmitted=? and PaperReview.contactId=? and PaperReview.paperId>?)) and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
select optionId, value, data from PaperOption where paperId=?
insert into Settings set name=?, value=? on duplicate key update value=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.fixabilityScores, PaperReview.fixability, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(fixability order by reviewId) fixabilityScores, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeSubmitted>0 group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.fixabilityScores, PaperReview.fixability, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(fixability order by reviewId) fixabilityScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeSubmitted>0 group by Paper.paperId order by Paper.paperId
update Settings set value=? where name=?
select PRP.paperId, PRP.contactId, PRP.preference from PaperReviewPreference PRP join ContactInfo c on (c.contactId=PRP.contactId and c.roles!=0 and (c.roles&1)!=0) join Paper P on (P.paperId=PRP.paperId) left join PaperConflict PC on (PC.paperId=PRP.paperId and PC.contactId=PRP.contactId) where PRP.preference<=? and coalesce(PC.conflictType,0)<=0 and P.timeWithdrawn<=0 and P.timeSubmitted>0 limit 1
select ContactInfo.* from ContactInfo where contactId=?
select Paper.*, PaperConflict.conflictType, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=? and PaperConflict.conflictType>=?) group by Paper.paperId order by Paper.paperId
select PaperComment.*, substring(PaperComment.comment from 1 for ?) as shortComment, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) join Paper on (Paper.paperId=PaperComment.paperId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and PaperComment.timeModified<? order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc limit ?
update Paper set authorInformation=?, blind=?, timeModified=? where paperId=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
delete from PaperConflict where paperId=?
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where leadContactId=u.contactId and conflictType is null) numLeads, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where shepherdContactId=u.contactId and conflictType is null) numShepherds from ContactInfo u left join (select r.contactId, r.reviewId, r.reviewSubmitted, r.reviewNeedsSubmit, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) left join ReviewRating rr on (rr.paperId=r.paperId and rr.reviewId=r.reviewId) where (pc.conflictType is null or pc.conflictType=0 or r.contactId=?) and (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select Paper.*, PaperConflict.conflictType, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=? and PaperConflict.conflictType>=?) group by Paper.paperId order by Paper.paperId
insert into Roles set contactDbId=?, confid=?, roles=?, updated_at=? on duplicate key update roles=values(roles), updated_at=values(updated_at)
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.reviewerQualification as reviewerQualification, PaperReview.commentsToAddress as commentsToAddress, PaperReview.paperSummary as paperSummary, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.grammar as grammar, PaperReview.fixability as fixability, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.textField7 as textField7, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, sum(r.numRatings) as numRatings, sum(r.sumRatings) as sumRatings, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where leadContactId=u.contactId and conflictType is null) numLeads, (select count(p.paperId) from Paper p left join PaperConflict c on (c.paperId=p.paperId and c.contactId=?) where shepherdContactId=u.contactId and conflictType is null) numShepherds, group_concat(if(r.reviewSubmitted>0,r.fixability,null)) as fixability from ContactInfo u left join (select r.contactId, r.reviewId, r.reviewSubmitted, r.reviewNeedsSubmit, r.fixability, count(rating) as numRatings, sum(if(rating>0,1,0)) as sumRatings from PaperReview r join Paper p on (p.paperId=r.paperId) left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) left join ReviewRating rr on (rr.paperId=r.paperId and rr.reviewId=r.reviewId) where (pc.conflictType is null or pc.conflictType=0 or r.contactId=?) and (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.reviewId) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select PaperComment.*, substring(PaperComment.comment from 1 for ?) as shortComment, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) join Paper on (Paper.paperId=PaperComment.paperId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and (PaperComment.timeModified<? or (PaperComment.timeModified=? and PaperComment.contactId>?) or (PaperComment.timeModified=? and PaperComment.contactId=? and PaperComment.paperId>?)) order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc limit ?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=0)) where true and (PaperConflict.conflictType>=? or Paper.paperId=?) group by Paper.paperId
update PaperReview set overAllMerit=?, reviewerQualification=?, commentsToAuthor=?, commentsToPC=?, reviewSubmitted=?, reviewNeedsSubmit=?, reviewOrdinal=coalesce(reviewOrdinal,?), timeDisplayed=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=?, reviewNotified=? where paperId=? and reviewId=?
select paperId from Paper where managerContactId=? limit 1
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, PaperReview.commentsToAddress as commentsToAddress, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeSubmitted>0 group by Paper.paperId order by Paper.paperId
update ContactInfo set activity_at=? where contactDbId=?
select Paper.*, PaperConflict.conflictType, null reviewType, null reviewId, null myReviewType, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
update PaperStorage set infoJson=?{desired} where paperId=? and paperStorageId=? and infoJson?{expected}e
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeSubmitted>0 group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=0)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=0))) where true and (PaperConflict.conflictType>=? or Paper.paperId=?) group by Paper.paperId
insert into PaperStorage set `paperId`=?,`timestamp`=?,`mimetype`=?,`sha1`=?,`documentType`=?,`mimetypeid`=?,`filename`=?,`size`=?
update Paper set finalPaperStorageId=?, blind=?, size=?, mimetype=?, sha1=?, timestamp=?, pdfFormatStatus=?, timeModified=? where paperId=?
select ContactInfo.contactId, PaperConflict.conflictType, PaperReview.reviewType, coalesce(preference,0) as reviewerPreference, expertise as reviewerExpertise, coalesce(allReviews,'') as allReviews, coalesce(PRR.paperId,0) as refused from ContactInfo left join PaperConflict on (PaperConflict.contactId=ContactInfo.contactId and PaperConflict.paperId=?) left join PaperReview on (PaperReview.contactId=ContactInfo.contactId and PaperReview.paperId=?) left join PaperReviewPreference on (PaperReviewPreference.contactId=ContactInfo.contactId and PaperReviewPreference.paperId=?) left join (select PaperReview.contactId, group_concat(reviewType separator '') as allReviews from PaperReview join Paper on (Paper.paperId=PaperReview.paperId and timeWithdrawn<=0) group by PaperReview.contactId) as AllReviews on (AllReviews.contactId=ContactInfo.contactId) left join PaperReviewRefused PRR on (PRR.paperId=? and PRR.contactId=ContactInfo.contactId) where ContactInfo.roles!=0 and (ContactInfo.roles&1)!=0 group by ContactInfo.contactId
alter table ContactInfo add key `rolesContactId` (`roles`,`contactId`)
insert into PaperConflict (paperId,contactId,conflictType) values (???)...
select rating, count(PaperReview.reviewId) from PaperReview join ReviewRating on (PaperReview.contactId=? and PaperReview.reviewId=ReviewRating.reviewId) group by rating order by rating desc
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(paperId) from PaperConflict where contactId=u.contactId and conflictType>=?) paperIds from ContactInfo u group by u.contactId order by lastName, firstName, email
select conflictType as conflict_type, reviewType as review_type, reviewSubmitted as review_submitted, reviewNeedsSubmit as review_needs_submit, PaperReview.contactId as review_token_cid, ContactInfo.contactId from (select ? paperId) P join ContactInfo left join PaperReview on (PaperReview.paperId=? and PaperReview.contactId=ContactInfo.contactId) left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=ContactInfo.contactId) where roles!=0 and (roles&1)!=0
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_AllComplete.info Reviews_AllComplete_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where (reviewSubmitted>0) group by paperId) as Reviews_AllComplete on (Reviews_AllComplete.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_AllComplete.count,0)>=0) and Paper.timeSubmitted>0 group by Paper.paperId
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled from ContactInfo u where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
insert into PaperTag set paperId=?, tag=?, tagIndex=? on duplicate key update tagIndex=values(tagIndex)
select ContactInfo.contactId, firstName, lastName, email, preferredEmail, password, roles, disabled, contactTags, conflictType, 0 myReviewType from ContactInfo join PaperConflict using (contactId) where paperId=? and conflictType>=? group by ContactInfo.contactId
select paperId from Paper where leadContactId=? limit 1
update PaperReview set overAllMerit=?, reviewerQualification=?, commentsToAuthor=?, commentsToPC=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=? where paperId=? and reviewId=?
insert into Settings (name, value) values (???) on duplicate key update value=values(value)
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=?, password=? on duplicate key update firstName=firstName
update ContactInfo set firstName=?, lastName=?, affiliation=?, country=?, unaccentedName=?, updateTime=? where contactId=?
insert into Settings (name, value, data) values (???)... on duplicate key update value=values(value), data=values(data)
show variables like ?
select Paper.*, PaperConflict.conflictType, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, count(Tag_1.tag) Tag_1_ct, Reviews_All.info Reviews_All_info, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperTag as Tag_1 on (Tag_1.paperId=Paper.paperId and (Tag_1.tag=?)) left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r group by paperId) as Reviews_All on (Reviews_All.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((Tag_1.tag is not null) and (coalesce(Reviews_All.count,0)>=?)) and Paper.timeSubmitted>0 group by Paper.paperId
update ContactInfo set passwordUseTime=? where contactId=?
alter table `ContactInfo` drop key `contactIdRoles`
insert into Settings (name, value, data) values (???) on duplicate key update value=values(value), data=values(data)
delete from Settings where name=?
insert into ContactInfo set lastName=?, firstName=?, email=?, affiliation=?, unaccentedName=?, disabled=?, updateTime=?, creationTime=?, password=?, passwordTime=?
select Paper.*, PaperConflict.conflictType, R_submitted.fixabilityScores, PaperReview.fixability, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, APRP.allReviewerPreference, AllConflict.allConflictType from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(fixability order by reviewId) fixabilityScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) left join (select paperId, group_concat(concat(contactId,' ',preference,' ',coalesce(expertise,?)) separator ?) as allReviewerPreference from PaperReviewPreference where paperId in (???) and true group by paperId) as APRP on (APRP.paperId=Paper.paperId) left join (select paperId, group_concat(concat(contactId,' ',conflictType) separator ?) as allConflictType from PaperConflict where paperId in (???) and conflictType>0 group by paperId) as AllConflict on (AllConflict.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
insert into Settings (name, value) select ?, count(paperId) from Paper where leadContactId>0 or shepherdContactId>0 limit 1 on duplicate key update value=values(value)
select ContactInfo.contactId, PaperConflict.conflictType, PaperReview.reviewType, coalesce(preference,0) as reviewerPreference, expertise as reviewerExpertise, coalesce(allReviews,'') as allReviews, coalesce(PRR.paperId,0) as refused from ContactInfo left join PaperConflict on (PaperConflict.contactId=ContactInfo.contactId and PaperConflict.paperId=?) left join PaperReview on (PaperReview.contactId=ContactInfo.contactId and PaperReview.paperId=?) left join PaperReviewPreference on (PaperReviewPreference.contactId=ContactInfo.contactId and PaperReviewPreference.paperId=?) left join (select PaperReview.contactId, group_concat(reviewType separator '') as allReviews from PaperReview join Paper on (Paper.paperId=PaperReview.paperId and timeWithdrawn<=0) group by PaperReview.contactId) as AllReviews on (AllReviews.contactId=ContactInfo.contactId) left join PaperReviewRefused PRR on (PRR.paperId=? and PRR.contactId=ContactInfo.contactId) where (ContactInfo.roles&1)!=0 group by ContactInfo.contactId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.fixabilityScores, PaperReview.fixability, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(fixability order by reviewId) fixabilityScores, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeSubmitted>0 and outcome>0 group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, coalesce(PaperReviewPreference.preference, 0) as reviewerPreference, PaperReviewPreference.expertise as reviewerExpertise, RPC.conflictType reviewerConflictType, RPR.reviewType reviewerReviewType from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join PaperReviewPreference on (PaperReviewPreference.paperId=Paper.paperId and PaperReviewPreference.contactId=?) left join PaperConflict RPC on (RPC.paperId=Paper.paperId and RPC.contactId=?) left join PaperReview RPR on (RPR.paperId=Paper.paperId and RPR.contactId=?) where timeSubmitted>0 group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.suitableForShort as suitableForShort, PaperReview.fixability as fixability, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
update Paper set outcome=? where paperId=?
insert into TopicInterest (contactId,topicId,interest) values (???)...
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where true and (PaperConflict.conflictType>=? or Paper.paperId=?) group by Paper.paperId
update ContactInfo set password=?, passwordTime=? where contactDbId=?
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select paperId, capVersion from Paper where paperId=?
insert into Paper set title=?, abstract=?, collaborators=?, authorInformation=?, paperStorageId=?, finalPaperStorageId=?, blind=?, size=?, mimetype=?, sha1=?, timestamp=?, pdfFormatStatus=?, timeModified=?
select contactId, topicId, interest from TopicInterest where interest!=0 order by contactId
select Paper.paperId, reviewType, reviewId, reviewModified, reviewSubmitted, timeApprovalRequested, reviewNeedsSubmit, reviewOrdinal, reviewBlind, PaperReview.contactId reviewContactId, requestedBy, reviewToken, reviewRound, conflictType from Paper left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) where Paper.paperId in (???) and (PaperReview.contactId is not null or PaperConflict.contactId is not null)
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where true and (PaperConflict.conflictType>=? or Paper.paperId=?) group by Paper.paperId
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=? on duplicate key update firstName=if(coalesce(firstName,'')='',?,firstName), lastName=if(coalesce(lastName,'')='',?,lastName), affiliation=if(coalesce(affiliation,'')='',?,affiliation), updateTime=?
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.fixability as fixability, PaperReview.grammar as grammar, PaperReview.interestToCommunity as interestToCommunity, PaperReview.likelyPresentation as likelyPresentation, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.textField8 as textField8, PaperReview.commentsToPC as commentsToPC, PaperReview.textField7 as textField7, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select straight_join PaperReview.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperReview.paperId) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and PaperReview.reviewSubmitted<? and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
select conflictType as conflict_type, reviewType as review_type, reviewSubmitted as review_submitted, reviewNeedsSubmit as review_needs_submit, PaperReview.contactId as review_token_cid, ? contactId from (select ? paperId) P left join PaperReview on (PaperReview.paperId=P.paperId and (PaperReview.contactId=?)) left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=?)
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.fixabilityScores, PaperReview.fixability, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(fixability order by reviewId) fixabilityScores, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (Paper.outcome>0) and Paper.timeSubmitted>0 and Paper.outcome>0 group by Paper.paperId
update ContactInfo set roles=? where contactId=?
select * from Mimetype where mimetype=?
update ContactInfo set collaborators=?, country=?, updateTime=?, contactTags=? where contactId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome from Paper where true and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.reviewerQualification as reviewerQualification, PaperReview.commentsToAddress as commentsToAddress, PaperReview.paperSummary as paperSummary, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.grammar as grammar, PaperReview.fixability as fixability, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.textField7 as textField7, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_ExternalApprovable.info Reviews_ExternalApprovable_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where reviewType=? and ((reviewSubmitted is null and timeApprovalRequested>0)) group by paperId) as Reviews_ExternalApprovable on (Reviews_ExternalApprovable.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_ExternalApprovable.count,0)>0) and Paper.timeSubmitted>0 group by Paper.paperId
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=?, password=? on duplicate key update firstName=?, lastName=?, affiliation=?, country=?, updateTime=?
select ContactInfo.contactId, firstName, lastName, email, password, roles, contactTags, preferredEmail, PaperConflict.conflictType, Paper.paperId, Paper.title, Paper.abstract, Paper.authorInformation, Paper.outcome, Paper.blind, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.shepherdContactId, Paper.capVersion, Paper.managerContactId from ContactInfo join Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=ContactInfo.contactId) where email not regexp ? and Paper.paperId in (???) and Paper.timeSubmitted>0 and Paper.outcome=? and PaperConflict.conflictType>=? order by Paper.paperId, email
select ContactInfo.contactId, firstName, lastName, email, password, roles, contactTags, preferredEmail, PaperConflict.conflictType, Paper.paperId, Paper.title, Paper.abstract, Paper.authorInformation, Paper.outcome, Paper.blind, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.shepherdContactId, Paper.capVersion, Paper.managerContactId from ContactInfo join Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=ContactInfo.contactId) where email not regexp ? and Paper.paperId in (???) and Paper.timeSubmitted>0 and PaperConflict.conflictType>=? order by Paper.paperId, email
select distinct tag from PaperTag t join Paper p on (p.paperId=t.paperId) where p.timeSubmitted>0
update PaperReview set overAllMerit=?, reviewerQualification=?, textField7=?, paperSummary=?, strengthOfPaper=?, commentsToAuthor=?, commentsToAddress=?, commentsToPC=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=?, reviewNotified=? where paperId=? and reviewId=?
select straight_join PaperReview.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperReview.paperId) where PaperReview.reviewSubmitted<? and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
select PaperReview.*, ContactInfo.firstName, ContactInfo.lastName, ContactInfo.email, ContactInfo.roles as contactRoles, ContactInfo.contactTags, ReqCI.firstName as reqFirstName, ReqCI.lastName as reqLastName, ReqCI.email as reqEmail from PaperReview join ContactInfo using (contactId) left join ContactInfo as ReqCI on (ReqCI.contactId=PaperReview.requestedBy) where PaperReview.paperId=? and PaperReview.reviewId=? group by PaperReview.reviewId order by PaperReview.paperId, reviewOrdinal, timeRequested, reviewType desc, reviewId
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, numReviews, numReviewsSubmitted, numRatings, sumRatings, (select count(paperId) from Paper where leadContactId=u.contactId) numLeads, (select count(paperId) from Paper where shepherdContactId=u.contactId) numShepherds, fixability from ContactInfo u left join (select contactId, count(if(reviewNeedsSubmit<=0,reviewSubmitted,reviewId)) numReviews, count(reviewSubmitted) numReviewsSubmitted, group_concat(if(reviewSubmitted>0,fixability,null)) fixability from PaperReview r join Paper p on (p.paperId=r.paperId) where (p.timeSubmitted>0 or r.reviewSubmitted>0) group by r.contactId) as r on (r.contactId=u.contactId) left join (select PaperReview.contactId, count(rating) numRatings, sum(if(rating>0,1,0)) sumRatings from ReviewRating join PaperReview on (PaperReview.paperId=ReviewRating.paperId and PaperReview.reviewId=ReviewRating.reviewId) group by PaperReview.contactId) as rr on (rr.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 order by lastName, firstName, email
select topicId from PaperTopic where paperId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where true and (PaperConflict.conflictType>=? or (Paper.timeWithdrawn<=0 and MyReview.reviewType is not null)) group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select Paper.*, PaperConflict.conflictType, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, APRP.allReviewerPreference from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) left join (select paperId, group_concat(concat(contactId,' ',preference,' ',coalesce(expertise,?)) separator ?) as allReviewerPreference from PaperReviewPreference where true group by paperId) as APRP on (APRP.paperId=Paper.paperId) where timeWithdrawn<=0 group by Paper.paperId order by Paper.paperId
select paperId, tag, tagIndex from PaperTag
select firstName, lastName, affiliation, email, contactId, roles, contactTags, disabled from ContactInfo where (roles&1)!=0
update ContactInfo set firstName=?, lastName=?, affiliation=?, unaccentedName=?, updateTime=? where contactId=?
select paperId, authorInformation from Paper where authorInformation like _utf8mb4? collate utf8mb4_general_ci
update ContactInfo set password=?, passwordTime=? where contactId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where true and (PaperConflict.conflictType>=? or (Paper.timeWithdrawn<=0 and MyReview.reviewType is not null)) group by Paper.paperId
select Paper.*, PaperConflict.conflictType, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, APRP.allReviewerPreference from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) left join (select paperId, group_concat(concat(contactId,' ',preference,' ',coalesce(expertise,?)) separator ?) as allReviewerPreference from PaperReviewPreference where paperId in (???) and true group by paperId) as APRP on (APRP.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
update ContactInfo set firstName=?, lastName=?, affiliation=?, country=?, updateTime=? where contactDbId=?
select any_newpcrev, any_lead, any_shepherd from (select PaperReview.paperId any_newpcrev from PaperReview where reviewType>=? and reviewSubmitted is null and reviewNeedsSubmit!=0 and timeRequested>timeRequestNotified limit 1) a left join (select paperId any_lead from Paper where timeSubmitted>0 and leadContactId!=0 limit 1) b on (true) left join (select paperId any_shepherd from Paper where timeSubmitted>0 and shepherdContactId!=0 limit 1) c on (true)
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.fixability as fixability, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select value from Settings where name=?
select distinct index_name from information_schema.statistics where table_schema=database() and `table_name`=?
insert into PaperComment (contactId, paperId, commentType, comment, commentOverflow, timeModified, replyTo, timeDisplayed, timeNotified) select ?, ?, ?, ?, ?, ?, 0, ?, ?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.title title, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, Paper.abstract abstract, Paper.authorInformation authorInformation, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((true) or (true) or (true)) and Paper.timeSubmitted>0 group by Paper.paperId
select p.paperId, pt.paperTags, r.reviewType from Paper p left join (select paperId, group_concat(' ', tag, '#', tagIndex order by tag separator '') as paperTags from PaperTag where tag ?a group by paperId) as pt on (pt.paperId=p.paperId) left join PaperReview r on (r.paperId=p.paperId and r.contactId=?) left join PaperConflict pc on (pc.paperId=p.paperId and pc.contactId=?) where p.timeSubmitted>0 and (pc.conflictType is null or p.managerContactId=?)
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and Paper.timeSubmitted>0 group by Paper.paperId
show columns from PaperComment like ?
update ContactInfo set collaborators=?, country=?, updateTime=?, defaultWatch=? where contactId=?
insert into PaperReviewPreference (paperId,contactId,preference,expertise) values (???)... on duplicate key update preference=values(preference), expertise=values(expertise)
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=?, password=? on duplicate key update firstName=if(coalesce(firstName,'')='',?,firstName), lastName=if(coalesce(lastName,'')='',?,lastName), affiliation=if(coalesce(affiliation,'')='',?,affiliation), collaborators=if(coalesce(collaborators,'')='',?,collaborators), country=if(coalesce(country,'')='',?,country), updateTime=?
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled from ContactInfo u where u.roles!=0 and (u.roles&1)!=0 order by lastName, firstName, email
insert into PaperTopic (topicId, paperId) values (???)...
select outcome, count(*) from Paper where timeSubmitted>0 group by outcome
update PaperComment, (select coalesce(max(PaperComment.ordinal),0) maxOrdinal from Paper left join PaperComment on (PaperComment.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId) t set ordinal=(t.maxOrdinal+1) where commentId=?
delete from PaperReviewPreference where contactId=? and (paperId between 1 and ? or paperId between ? and ? or paperId=? or paperId=? or paperId between ? and ?)
select contactId, topicId, interest from TopicInterest
select paperId from PaperReview where reviewType>? and timeRequested>timeRequestNotified and reviewSubmitted is null and reviewNeedsSubmit!=0 limit 1
select u.contactId, group_concat(r.reviewType separator '') from ContactInfo u left join PaperReview r on (r.contactId=u.contactId) left join Paper p on (p.paperId=r.paperId) where p.timeWithdrawn<=0 and p.timeSubmitted>0 and u.roles!=0 and (u.roles&1)!=0 group by u.contactId
select Paper.*, PaperConflict.conflictType, null reviewType, null reviewId, null myReviewType from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select firstName, lastName, unaccentedName, email, contactId, roles from ContactInfo where email like ?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.title title, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.abstract abstract, Paper.authorInformation authorInformation, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (((true) or (true) or (true)) and ((true) or (true) or (true))) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and Paper.timeWithdrawn<=0 and MyReview.reviewType is not null group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=-1 and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=-1 group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, count(Tag_1.tag) Tag_1_ct, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperTag as Tag_1 on (Tag_1.paperId=Paper.paperId and (Tag_1.tag=?)) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (Tag_1.tag is not null) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, count(Tag_1.tag) Tag_1_ct, count(Tag_2.tag) Tag_2_ct, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperTag as Tag_1 on (Tag_1.paperId=Paper.paperId and (Tag_1.tag=?)) left join PaperTag as Tag_2 on (Tag_2.paperId=Paper.paperId and (Tag_2.tag=?)) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((Tag_1.tag is not null) and (Tag_2.tag is not null)) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, count(Tag_3.tag) Tag_3_ct, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) left join PaperTag as Tag_3 on (Tag_3.paperId=Paper.paperId and (Tag_3.tag=?)) where (Tag_3.tag is not null) and Paper.timeSubmitted>0 group by Paper.paperId
update PaperReview set overAllMerit=?, reviewerQualification=?, textField7=?, paperSummary=?, strengthOfPaper=?, commentsToAuthor=?, commentsToAddress=?, commentsToPC=?, reviewBlind=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=? where paperId=? and reviewId=?
delete from TopicInterest where contactId=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select distinct tag from PaperTag t where t.paperId ?a
select paperId from PaperConflict where contactId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where true and Paper.timeWithdrawn<=0 and MyReview.reviewType is not null group by Paper.paperId
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=?, password=? on duplicate key update firstName=?, lastName=?, affiliation=?, collaborators=?, country=?, updateTime=?
insert into PaperConflict (paperId,contactId,conflictType) values (???)
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.fixability as fixability, PaperReview.paperSummary as paperSummary, PaperReview.strengthOfPaper as strengthOfPaper, PaperReview.textField7 as textField7, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToAddress as commentsToAddress, PaperReview.commentsToPC as commentsToPC, PaperReview.textField8 as textField8, PaperReview.weaknessOfPaper as weaknessOfPaper, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select straight_join PaperReview.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperReview.paperId) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and (PaperReview.reviewSubmitted<? or (PaperReview.reviewSubmitted=? and PaperReview.contactId>?) or (PaperReview.reviewSubmitted=? and PaperReview.contactId=? and PaperReview.paperId>?)) and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
insert into PaperConflict (paperId, contactId, conflictType) values (???) on duplicate key update conflictType=greatest(conflictType, ?)
delete from PaperReview where reviewId=?
delete from Capability where timeExpires>0 and timeExpires<?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, R_submitted.reviewerQualificationScores, R_submitted.reviewContactIds, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select mailId, subject, emailBody from MailLog where fromNonChair=0 order by mailId desc limit ?
update PaperReview set overAllMerit=?, reviewerQualification=?, paperSummary=?, commentsToAuthor=?, commentsToPC=?, reviewSubmitted=?, reviewNeedsSubmit=?, reviewOrdinal=coalesce(reviewOrdinal,?), timeDisplayed=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=?, reviewNotified=? where paperId=? and reviewId=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select straight_join PaperComment.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperComment.paperId) where (PaperConflict.conflictType>=? or MyPaperReview.reviewId is not null) and PaperComment.timeModified<? order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc limit ?
select straight_join PaperComment.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperComment.paperId) where PaperComment.timeModified<? order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc limit ?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
update PaperReview set overAllMerit=?, reviewerQualification=?, paperSummary=?, commentsToAuthor=?, commentsToPC=?, commentsToAddress=?, reviewSubmitted=?, reviewNeedsSubmit=?, reviewOrdinal=coalesce(reviewOrdinal,?), timeDisplayed=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=?, reviewNotified=? where paperId=? and reviewId=?
insert into FilteredDocument set inDocId=?, filterType=?, outDocId=?, createdAt=? on duplicate key update outDocId=values(outDocId), createdAt=values(createdAt)
select count(reviewId) num_submitted, group_concat(overAllMerit) scores from ContactInfo left join PaperReview on (PaperReview.contactId=ContactInfo.contactId and PaperReview.reviewSubmitted is not null) where (roles&1)!=0 group by ContactInfo.contactId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.fixabilityScores, PaperReview.fixability, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(fixability order by reviewId) fixabilityScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.title title, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.abstract abstract, Paper.authorInformation authorInformation from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (((true) or (true) or (true)) and ((true) or (true) or (true))) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where true and PaperConflict.conflictType>=? group by Paper.paperId
select PRP.paperId, PRP.contactId, PRP.preference from PaperReviewPreference PRP join ContactInfo c on (c.contactId=PRP.contactId and (c.roles&1)!=0) join Paper P on (P.paperId=PRP.paperId) left join PaperConflict PC on (PC.paperId=PRP.paperId and PC.contactId=PRP.contactId) where PRP.preference<=? and coalesce(PC.conflictType,0)<=0 and P.timeWithdrawn<=0 and P.timeSubmitted>0 limit 1
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId in (???) group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.reviewerQualification as reviewerQualification, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
update Paper set finalPaperStorageId=?, timeFinalSubmitted=?, blind=?, size=?, mimetype=?, sha1=?, timestamp=?, pdfFormatStatus=?, timeModified=? where paperId=?
update Paper set abstract=?, blind=?, timeModified=? where paperId=?
insert into ContactInfo set lastName=?, firstName=?, email=?, affiliation=?, country=?, unaccentedName=?, disabled=?, updateTime=?, defaultWatch=?, creationTime=?, password=?, passwordTime=?
unlock tables
insert into PaperStorage set `paperId`=?,`timestamp`=?,`mimetype`=?,`sha1`=?,`documentType`=?,`mimetypeid`=?,`infoJson`=?,`size`=?,`filterType`=?,`originalStorageId`=?
update PaperReview set overAllMerit=?, fixability=?, grammar=?, reviewerQualification=?, suitableForShort=?, paperSummary=?, commentsToAuthor=?, commentsToPC=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=? where paperId=? and reviewId=?
update Paper set outcome=-1 where paperId=?
update PaperReview set overAllMerit=?, reviewerQualification=?, paperSummary=?, commentsToAuthor=?, commentsToPC=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=? where paperId=? and reviewId=?
insert into MailLog set recipients=?, cc=?, replyto=?, subject=?, emailBody=?, q=?, t=?
update PaperReview set overAllMerit=?, reviewerQualification=?, textField7=?, paperSummary=?, strengthOfPaper=?, commentsToAuthor=?, commentsToAddress=?, commentsToPC=?, reviewSubmitted=?, reviewNeedsSubmit=?, reviewOrdinal=coalesce(reviewOrdinal,?), timeDisplayed=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=?, reviewNotified=? where paperId=? and reviewId=?
update Paper set shepherdContactId=? where paperId=?
insert into PaperReview set paperId=?, contactId=?, reviewType=?, reviewRound=0, timeRequested=?, requestedBy=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select straight_join PaperReview.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperReview.paperId) where (PaperReview.reviewSubmitted<? or (PaperReview.reviewSubmitted=? and PaperReview.contactId>?) or (PaperReview.reviewSubmitted=? and PaperReview.contactId=? and PaperReview.paperId>?)) and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
update PaperReview set overAllMerit=?, fixability=?, grammar=?, reviewerQualification=?, suitableForShort=?, paperSummary=?, commentsToAuthor=?, commentsToPC=?, reviewSubmitted=?, reviewNeedsSubmit=?, reviewOrdinal=coalesce(reviewOrdinal,?), timeDisplayed=?, reviewBlind=?, reviewEditVersion=?, reviewWordCount=?, reviewModified=?, reviewAuthorModified=?, reviewNotified=?, reviewAuthorNotified=? where paperId=? and reviewId=?
update Paper set timeWithdrawn=0, timeSubmitted=if(timeSubmitted=?,?,0), withdrawReason=null where paperId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and Paper.timeWithdrawn<=0 and MyReview.reviewType is not null group by Paper.paperId
insert into PaperReview set paperId=?, contactId=?, reviewType=?, reviewRound=?, timeRequested=?, requestedBy=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=0)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=0))) where true and (PaperConflict.conflictType>=? or Paper.paperId=? or Paper.paperId=? or Paper.paperId=? or Paper.paperId=?) group by Paper.paperId
select paperStorageId, sha1, timestamp, size, mimetype from PaperStorage where paperId=? and documentType=? and sha1=?
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=? on duplicate key update firstName=?, lastName=?, affiliation=?, country=?, updateTime=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=-1 and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=-1 group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where true and (Paper.managerContactId=? or Paper.managerContactId=0) and Paper.timeSubmitted>0 group by Paper.paperId
select email from ContactInfo where contactId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where true and (PaperConflict.conflictType>=? or Paper.paperId=? or Paper.paperId=?) group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where timeSubmitted>0 and outcome>0 group by Paper.paperId order by Paper.paperId
select coalesce(max(reviewOrdinal), 0) from PaperReview where paperId=? group by paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where true and (PaperConflict.conflictType>=? or Paper.paperId=? or Paper.paperId=?) group by Paper.paperId
select count(*) from PaperReview where paperId=? and (reviewSubmitted>0 or reviewNeedsSubmit>0)
insert into ContactInfo set firstName=?, lastName=?, email=?, affiliation=?, country=?, collaborators=?, password=? on duplicate key update firstName=if(coalesce(firstName,'')='',?,firstName), lastName=if(coalesce(lastName,'')='',?,lastName), affiliation=if(coalesce(affiliation,'')='',?,affiliation), updateTime=?
select PaperReview.*, Paper.title, substring(Paper.title from 1 for ?) as shortTitle, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.blind as paperBlind, Paper.outcome, Paper.managerContactId, Paper.leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) join Paper on (Paper.paperId=PaperReview.paperId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) where (PaperReview.reviewSubmitted<? or (PaperReview.reviewSubmitted=? and PaperReview.contactId>?) or (PaperReview.reviewSubmitted=? and PaperReview.contactId=? and PaperReview.paperId>?)) and PaperReview.reviewSubmitted>0 order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc limit ?
select Paper.paperId, count(pc.contactId) from Paper join PaperConflict c on (c.paperId=Paper.paperId and c.contactId=? and c.conflictType>=?) join PaperConflict pc on (pc.paperId=Paper.paperId and pc.conflictType>=?) group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.reviewerQualification as reviewerQualification, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAddress as commentsToAddress, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select name, ReviewRequest.email, firstName as reqFirstName, lastName as reqLastName, ContactInfo.email as reqEmail, requestedBy, reason, reviewRound from ReviewRequest join ContactInfo on (ContactInfo.contactId=ReviewRequest.requestedBy) where ReviewRequest.paperId=?
select Paper.*, PaperConflict.conflictType, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, coalesce(PaperReviewPreference.preference, 0) as reviewerPreference, PaperReviewPreference.expertise as reviewerExpertise, APRP.allReviewerPreference, coalesce(APRP.desirability,0) as desirability, AllConflict.allConflictType, RPC.conflictType reviewerConflictType, RPR.reviewType reviewerReviewType from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) left join PaperReviewPreference on (PaperReviewPreference.paperId=Paper.paperId and PaperReviewPreference.contactId=?) left join (select paperId, group_concat(concat(contactId,' ',preference,' ',coalesce(expertise,?)) separator ?) as allReviewerPreference, sum(if(preference<=?,0,greatest(least(preference,1),-1))) as desirability from PaperReviewPreference where true group by paperId) as APRP on (APRP.paperId=Paper.paperId) left join (select paperId, group_concat(concat(contactId,' ',conflictType) separator ?) as allConflictType from PaperConflict where conflictType>0 group by paperId) as AllConflict on (AllConflict.paperId=Paper.paperId) left join PaperConflict RPC on (RPC.paperId=Paper.paperId and RPC.contactId=?) left join PaperReview RPR on (RPR.paperId=Paper.paperId and RPR.contactId=?) where timeSubmitted>0 group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, count(Tag_1.tag) Tag_1_ct, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperTag as Tag_1 on (Tag_1.paperId=Paper.paperId and (Tag_1.tag=?)) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((Paper.outcome>0) or (Tag_1.tag is not null)) and Paper.timeSubmitted>0 group by Paper.paperId
select count(ta.topicId), count(ti.topicId) from TopicArea ta left join TopicInterest ti on (ti.contactId=? and ti.topicId=ta.topicId)
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and (PaperConflict.conflictType>=? or (Paper.timeWithdrawn<=0 and MyReview.reviewType is not null)) group by Paper.paperId
select outcome, count(paperId) from Paper where timeSubmitted>0 group by outcome
select paperId from PaperTag where tag=? limit 1
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome from Paper where (Paper.paperId in (???)) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=? and PaperConflict.conflictType>=?) left join (select paperId, count(*) count from PaperReview where reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) group by Paper.paperId order by Paper.paperId
update PaperReview set reviewNeedsSubmit=0 where paperId=? and contactId=? and reviewType=? and reviewSubmitted is null
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, R_submitted.reviewerQualificationScores, R_submitted.reviewContactIds, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId in (???) and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select straight_join PaperComment.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from (select paperId, commentId from PaperComment where timeModified<? order by timeModified desc, contactId asc, paperId asc limit ?) x join PaperComment using (paperId,commentId) join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) left join PaperConflict on (PaperConflict.paperId=PaperComment.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperComment.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperComment.paperId) order by PaperComment.timeModified desc, PaperComment.contactId asc, PaperComment.paperId asc
select straight_join PaperReview.*, title, timeSubmitted, timeWithdrawn, Paper.blind paperBlind, outcome, managerContactId, leadContactId, ContactInfo.firstName as reviewFirstName, ContactInfo.lastName as reviewLastName, ContactInfo.email as reviewEmail, PaperConflict.conflictType, MyPaperReview.reviewType as myReviewType, MyPaperReview.reviewSubmitted as myReviewSubmitted, MyPaperReview.reviewNeedsSubmit as myReviewNeedsSubmit, MyPaperReview.contactId as myReviewContactId from (select paperId, reviewId from PaperReview where reviewSubmitted>0 and reviewSubmitted<? order by reviewSubmitted desc, contactId asc, paperId asc limit ?) x join PaperReview using (paperId,reviewId) join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) left join PaperConflict on (PaperConflict.paperId=PaperReview.paperId and PaperConflict.contactId=?) left join PaperReview as MyPaperReview on (MyPaperReview.paperId=PaperReview.paperId and MyPaperReview.contactId=?) join Paper on (Paper.paperId=PaperReview.paperId) order by PaperReview.reviewSubmitted desc, PaperReview.contactId asc, PaperReview.paperId asc
select conflictType as conflict_type, reviewType as review_type, reviewSubmitted as review_submitted, reviewNeedsSubmit as review_needs_submit, PaperReview.contactId as review_token_cid, ContactInfo.contactId from (select ? paperId) P join ContactInfo left join PaperReview on (PaperReview.paperId=? and PaperReview.contactId=ContactInfo.contactId) left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=ContactInfo.contactId) where (roles&1)!=0
select PaperReview.*, ContactInfo.firstName, ContactInfo.lastName, ContactInfo.email from PaperReview join ContactInfo on (ContactInfo.contactId=PaperReview.contactId) where PaperReview.paperId=? order by reviewOrdinal
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, PaperReview.reviewEditVersion as reviewEditVersion, PaperReview.reviewFormat as reviewFormat, PaperReview.overAllMerit as overAllMerit, PaperReview.fixability as fixability, PaperReview.grammar as grammar, PaperReview.reviewerQualification as reviewerQualification, PaperReview.suitableForShort as suitableForShort, PaperReview.paperSummary as paperSummary, PaperReview.commentsToAuthor as commentsToAuthor, PaperReview.commentsToPC as commentsToPC, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId, PaperReview.reviewId order by Paper.paperId, PaperReview.reviewOrdinal
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where true and MyReview.reviewNeedsSubmit!=0 group by Paper.paperId
select distinct tag from PaperTag t join Paper p on (p.paperId=t.paperId) left join PaperConflict pc on (pc.paperId=t.paperId and pc.contactId=?) where p.timeSubmitted>0 and (p.managerContactId=? or pc.conflictType is null)
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r left join (select reviewId, count(rating) as nrate_good from ReviewRating where rating>0 group by reviewId) as Ratings_good on (Ratings_good.reviewId=r.reviewId) where nrate_good>0 and r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((coalesce(Reviews_1.count,0)>0)) and Paper.timeSubmitted>0 group by Paper.paperId
select distinct tag from PaperTag t join Paper p on (p.paperId=t.paperId) left join PaperConflict pc on (pc.paperId=t.paperId and pc.contactId=?) where p.timeSubmitted>0 and (p.managerContactId=0 or p.managerContactId=? or pc.conflictType is null)
select u.contactId, firstName, lastName, email, affiliation, roles, contactTags, voicePhoneNumber, u.collaborators, lastLogin, disabled, (select group_concat(topicId, ' ', interest) from TopicInterest where contactId=u.contactId) topicInterest, count(if(r.reviewNeedsSubmit<=0,r.reviewSubmitted,r.reviewId)) as numReviews, count(r.reviewSubmitted) as numReviewsSubmitted, (select count(paperId) from Paper where leadContactId=u.contactId) numLeads, (select count(paperId) from Paper where shepherdContactId=u.contactId) numShepherds, group_concat(if(r.reviewSubmitted>0,r.overAllMerit,null)) as overAllMerit from ContactInfo u left join (select r.contactId, r.reviewId, r.reviewSubmitted, r.reviewNeedsSubmit, r.overAllMerit from PaperReview r join Paper p on (p.paperId=r.paperId) where (p.timeSubmitted>0 or r.reviewSubmitted>0)) as r on (r.contactId=u.contactId) where u.roles!=0 and (u.roles&1)!=0 group by u.contactId order by lastName, firstName, email
select contactId, count(preference) from PaperReviewPreference where preference!=0 group by contactId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags, APRP.allReviewerPreference from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) left join (select paperId, group_concat(concat(contactId,' ',preference,' ',coalesce(expertise,?)) separator ?) as allReviewerPreference from PaperReviewPreference where paperId=? and true group by paperId) as APRP on (APRP.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(topicId) from PaperTopic where PaperTopic.paperId=Paper.paperId) topicIds, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select contactId, email from ContactInfo where email?a
lock tables PaperReview write
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select Paper.paperId, authorInformation from Paper join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=? and PaperConflict.conflictType>=?)
select paperId from PaperReview where PaperReview.contactId=? group by paperId order by paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r left join (select reviewId, count(rating) as nrate_0 from ReviewRating where rating in (???) group by reviewId) as Ratings_0 on (Ratings_0.reviewId=r.reviewId) where nrate_0>0 and r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((coalesce(Reviews_1.count,0)>0)) and Paper.timeSubmitted>0 group by Paper.paperId
select paperId from PaperComment where PaperComment.contactId=? group by paperId order by paperId
select ContactInfo.contactId, PaperConflict.conflictType, reviewType, reviewModified, reviewId from ContactInfo left join PaperConflict on (PaperConflict.contactId=ContactInfo.contactId and PaperConflict.paperId=?) left join PaperReview on (PaperReview.contactId=ContactInfo.contactId and PaperReview.paperId=?) where ContactInfo.roles!=0 and (ContactInfo.roles&1)!=0
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Reviews_1.info Reviews_1_info, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join (select r.paperId, count(r.reviewId) count, group_concat(r.reviewId, ' ', r.contactId, ' ', r.reviewType, ' ', coalesce(r.reviewSubmitted,0), ' ', r.reviewNeedsSubmit, ' ', r.requestedBy, ' ', r.reviewToken, ' ', r.reviewBlind) info from PaperReview r where r.contactId=? group by paperId) as Reviews_1 on (Reviews_1.paperId=Paper.paperId) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (coalesce(Reviews_1.count,0)>0) and MyReview.reviewNeedsSubmit!=0 group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select topicId, interest from TopicInterest where contactId=?
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.fixabilityScores, R_submitted.overAllMeritScores, R_submitted.reviewContactIds, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) left join (select paperId, count(*) count, group_concat(fixability order by reviewId) fixabilityScores, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.overAllMeritScores, PaperReview.overAllMerit, R_submitted.reviewerQualificationScores, PaperReview.reviewerQualification, R_submitted.reviewContactIds, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
select ContactInfo.contactId, firstName, lastName, email, password, roles, contactTags, preferredEmail, 0 as conflictType, Paper.paperId, Paper.title, Paper.abstract, Paper.authorInformation, Paper.outcome, Paper.blind, Paper.timeSubmitted, Paper.timeWithdrawn, Paper.shepherdContactId, Paper.capVersion, Paper.managerContactId, PaperReview.reviewType, PaperReview.reviewType as myReviewType from ContactInfo join Paper join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=ContactInfo.contactId) where email not regexp ? and Paper.paperId=PaperReview.paperId and PaperReview.reviewSubmitted is null and PaperReview.reviewNeedsSubmit!=0 and Paper.timeSubmitted>0 order by Paper.paperId, email
select conflictType as conflict_type, reviewType as review_type, reviewSubmitted as review_submitted, reviewNeedsSubmit as review_needs_submit, PaperReview.contactId as review_token_cid, ContactInfo.contactId from (select 1 paperId) P join ContactInfo left join PaperReview on (PaperReview.paperId=? and PaperReview.contactId=ContactInfo.contactId) left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=ContactInfo.contactId) where roles!=0 and (roles&1)!=0
select PaperStorage.* from FilteredDocument join PaperStorage on (PaperStorage.paperStorageId=FilteredDocument.outDocId) where inDocId=? and FilteredDocument.filterType=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, count(Tag_3.tag) Tag_3_ct from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) left join PaperTag as Tag_3 on (Tag_3.paperId=Paper.paperId and (Tag_3.tag=?)) where (Tag_3.tag is not null) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, R_submitted.reviewerQualificationScores, R_submitted.overAllMeritScores, R_submitted.reviewContactIds, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) left join (select paperId, count(*) count, group_concat(reviewerQualification order by reviewId) reviewerQualificationScores, group_concat(overAllMerit order by reviewId) overAllMeritScores, group_concat(contactId order by reviewId) reviewContactIds from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
update Paper set pdfFormatStatus=? where paperId=?
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, count(Tag_1.tag) Tag_1_ct, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join PaperTag as Tag_1 on (Tag_1.paperId=Paper.paperId and (Tag_1.tag=?)) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (Tag_1.tag is not null) group by Paper.paperId
select contactId from ContactInfo where roles!=0 and (roles&1)!=0 order by lastName, firstName, email
select Paper.*, PaperConflict.conflictType, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewNeedsSubmit>0)) startedReviewCount, (select count(*) from PaperReview where paperId=Paper.paperId and (reviewSubmitted>0 or reviewModified>0)) inProgressReviewCount, coalesce(R_submitted.count,0) reviewCount, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) left join (select paperId, count(*) count from PaperReview where paperId=? and reviewSubmitted>0 group by paperId) R_submitted on (R_submitted.paperId=Paper.paperId) where Paper.paperId=? group by Paper.paperId order by Paper.paperId
delete from PaperTopic where paperId=?
select reviewRound, count(*) from PaperReview group by reviewRound
select PRP.paperId, PRP.contactId, PRP.preference from PaperReviewPreference PRP join ContactInfo c on (c.contactId=PRP.contactId and c.roles!=0 and (c.roles&1)!=0) join Paper P on (P.paperId=PRP.paperId) left join PaperConflict PC on (PC.paperId=PRP.paperId and PC.contactId=PRP.contactId) where PRP.preference<=? and coalesce(PC.conflictType,0)<=0 and P.timeWithdrawn<=0 limit 1
select paperId from PaperReview where reviewId=?
lock tables ContactInfo read, Paper read, PaperConflict read, PaperTag write
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds, (select group_concat(' ', tag, '#', tagIndex order by tag separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where timeWithdrawn<=0 group by Paper.paperId order by Paper.paperId
select reviewId, reviewType, reviewRound, reviewModified, reviewToken, requestedBy, reviewSubmitted from PaperReview where paperId=? and contactId=?
select ContactInfo.contactId, firstName, lastName, email, password, roles, contactTags, preferredEmail, 0 as conflictType, -1 as paperId from ContactInfo where email not regexp ? and (ContactInfo.roles&1)!=0 order by email
select paperId from Paper where timeSubmitted>0 limit 1
lock tables Settings write
select PaperComment.*, firstName reviewFirstName, lastName reviewLastName, email reviewEmail from PaperComment join ContactInfo on (ContactInfo.contactId=PaperComment.contactId) where commentId=? order by commentId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, PaperConflict.conflictType conflictType from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=0)) where true and PaperConflict.conflictType>=? group by Paper.paperId
select Paper.*, PaperConflict.conflictType, null reviewType, null reviewId, null myReviewType, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=0) where Paper.paperId in (???) group by Paper.paperId order by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, count(Tag_1.tag) Tag_1_ct, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted from Paper left join PaperTag as Tag_1 on (Tag_1.paperId=Paper.paperId and (Tag_1.tag=?)) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where (Tag_1.tag is not null) and Paper.timeSubmitted>0 group by Paper.paperId
lock tables PaperReview write, PaperReviewRefused write, PaperConflict write, ContactInfo read, ActionLog write, Settings write
select paperStorageId from PaperStorage where paperId=? and documentType=? and sha1=?
select Paper.*, PaperConflict.conflictType, PaperReview.reviewType, PaperReview.reviewId, PaperReview.reviewModified, PaperReview.reviewSubmitted, PaperReview.timeApprovalRequested, PaperReview.reviewNeedsSubmit, PaperReview.reviewOrdinal, PaperReview.reviewBlind, PaperReview.reviewToken, PaperReview.timeRequested, PaperReview.contactId as reviewContactId, PaperReview.requestedBy, max(PaperReview.reviewType) as myReviewType, max(PaperReview.reviewSubmitted) as myReviewSubmitted, min(PaperReview.reviewNeedsSubmit) as myReviewNeedsSubmit, PaperReview.contactId as myReviewContactId, PaperReview.reviewRound, (select group_concat(PaperOption.optionId, '#', value) from PaperOption where paperId=Paper.paperId) optionIds from Paper left join PaperConflict on (PaperConflict.paperId=Paper.paperId and PaperConflict.contactId=?) left join PaperReview on (PaperReview.paperId=Paper.paperId and PaperReview.contactId=?) where Paper.paperId=-1 group by Paper.paperId order by Paper.paperId
delete from PaperReviewRefused where paperId=? and contactId=?
select count(topicId) from TopicInterest where contactId=?
select ContactInfo.contactId, count(reviewId) from ContactInfo left join PaperReview on (PaperReview.contactId=ContactInfo.contactId and PaperReview.reviewType>=?) where roles!=0 and (roles&1)!=0 group by ContactInfo.contactId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, Paper.title title, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.abstract abstract from Paper left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) where ((true) or (true)) and Paper.timeSubmitted>0 group by Paper.paperId
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome, MyReview.reviewType myReviewType, MyReview.reviewNeedsSubmit myReviewNeedsSubmit, MyReview.reviewSubmitted myReviewSubmitted, Paper.managerContactId managerContactId, Paper.leadContactId leadContactId, PaperConflict.conflictType conflictType, (select group_concat(' ', tag, '#', tagIndex separator '') from PaperTag where PaperTag.paperId=Paper.paperId) paperTags from Paper left join PaperReview as MyReview on (MyReview.paperId=Paper.paperId and ((MyReview.contactId=?))) left join PaperConflict as PaperConflict on (PaperConflict.paperId=Paper.paperId and (PaperConflict.contactId=?)) where (Paper.paperId in (???)) and Paper.timeWithdrawn<=0 and MyReview.reviewType is not null group by Paper.paperId
select PaperReview.contactId, timeRequested, reviewSubmitted, reviewRound, 0 conflictType from PaperReview where reviewType>? or (reviewType=? and timeRequested>0 and reviewSubmitted>0)
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome from Paper where true group by Paper.paperId
lock tables Settings write, PaperOption write, TopicArea write, PaperTopic write, TopicInterest write
select Paper.paperId paperId, Paper.timeSubmitted timeSubmitted, Paper.timeWithdrawn timeWithdrawn, Paper.outcome outcome from Paper where (Paper.outcome in (???)) and Paper.timeSubmitted>0 group by Paper.paperId
select name, ReviewRequest.email, firstName as reqFirstName, lastName as reqLastName, ContactInfo.email as reqEmail, requestedBy, reason, reviewRound from ReviewRequest join ContactInfo on (ContactInfo.contactId=ReviewRequest.requestedBy) where ReviewRequest.paperId=? and requestedBy=?
select conflictType as conflict_type, reviewType as review_type, reviewSubmitted as review_submitted, reviewNeedsSubmit as review_needs_submit, PaperReview.contactId as review_token_cid, ? contactId from (select 1 paperId) P left join PaperReview on (PaperReview.paperId=P.paperId and (PaperReview.contactId=?)) left join PaperConflict on (PaperConflict.paperId=? and PaperConflict.contactId=?)
select min(Paper.paperId) from Paper where timeSubmitted>0
select * from ContactInfo where contactId=?
select topicId, if(interest>0,1,0), count(*) from TopicInterest where interest!=0 group by topicId, interest>0
select * from Capability where salt=?
select * from Formula order by lower(name)
select * from ContactInfo where contactDbId=?
select paperStorageId, paperId, timestamp, mimetype, mimetypeid, sha1, documentType, filename, infoJson, size, filterType, originalStorageId from PaperStorage where paperStorageId?a
select * from PaperTagAnno where tag=?
select * from MailLog where mailId=?
select topicId, interest from TopicArea join TopicInterest using (topicId) where contactId=?
select min(Paper.paperId) from Paper join ContactInfo on (ContactInfo.paperId=Paper.paperId and ContactInfo.contactId=? and ContactInfo.conflictType>=?)
select min(Paper.paperId) from Paper join ContactInfo on (ContactInfo.paperId=Paper.paperId and ContactInfo.contactId=0 and ContactInfo.conflictType>=?)
select firstName, lastName, affiliation, collaborators from ContactInfo where contactId=?
select contactId from ContactInfo where email=?