PHP Zotero_DB::query方法代码示例

 public function removestoragefiles()
 {
     $this->allowMethods(array('POST'));
     $sql = "DELETE SFI FROM storageFileItems SFI JOIN items USING (itemID) WHERE libraryID=?";
     Zotero_DB::query($sql, $this->objectLibraryID, Zotero_Shards::getByLibraryID($this->objectLibraryID));
     header("HTTP/1.1 204 No Content");
     exit;
 }

 public static function add($userID)
 {
     Z_Core::debug("Creating publications library for user {$userID}");
     Zotero_DB::beginTransaction();
     // Use same shard as user library
     $shardID = Zotero_Shards::getByUserID($userID);
     $libraryID = Zotero_Libraries::add('publications', $shardID);
     $sql = "INSERT INTO userPublications (userID, libraryID) VALUES (?, ?)";
     Zotero_DB::query($sql, [$userID, $libraryID]);
     Zotero_DB::commit();
     return $libraryID;
 }

 public static function deleteUser($userID)
 {
     if (empty($userID)) {
         throw new Exception("userID not provided");
     }
     $username = Zotero_Users::getUsername($userID, true);
     $sql = "SELECT LUM_Role.Name FROM LUM_User JOIN LUM_Role USING (RoleID) WHERE UserID=?";
     try {
         $role = Zotero_WWW_DB_2::valueQuery($sql, $userID);
     } catch (Exception $e) {
         Z_Core::logError("WARNING: {$e} -- retrying on primary");
         $role = Zotero_WWW_DB_1::valueQuery($sql, $userID);
     }
     if ($role != 'Deleted') {
         throw new Exception("User '{$username}' does not have role 'Deleted'");
     }
     Zotero_DB::beginTransaction();
     if (Zotero_Groups::getUserOwnedGroups($userID)) {
         throw new Exception("Cannot delete user '{$username}' with owned groups");
     }
     // Remove user from any groups they're a member of
     //
     // This isn't strictly necessary thanks to foreign key cascades,
     // but it removes some extra keyPermissions rows
     $groupIDs = Zotero_Groups::getUserGroups($userID);
     foreach ($groupIDs as $groupID) {
         $group = Zotero_Groups::get($groupID, true);
         $group->removeUser($userID);
     }
     // Remove all data
     Zotero_Users::clearAllData($userID);
     // Remove user publications library
     $libraryID = self::getLibraryIDFromUserID($userID, 'publications');
     if ($libraryID) {
         $shardID = Zotero_Shards::getByLibraryID($libraryID);
         Zotero_DB::query("DELETE FROM shardLibraries WHERE libraryID=?", $libraryID, $shardID);
         Zotero_DB::query("DELETE FROM libraries WHERE libraryID=?", $libraryID);
     }
     // Remove user/library rows
     $libraryID = self::getLibraryIDFromUserID($userID);
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     Zotero_DB::query("DELETE FROM shardLibraries WHERE libraryID=?", $libraryID, $shardID);
     Zotero_DB::query("DELETE FROM libraries WHERE libraryID=?", $libraryID);
     Zotero_DB::commit();
 }

	public function save($userID=false) {
		if (!$this->_libraryID) {
			trigger_error("Library ID must be set before saving", E_USER_ERROR);
		}
		
		Zotero_Items::editCheck($this, $userID);
		
		if (!$this->hasChanged()) {
			Z_Core::debug("Item $this->id has not changed");
			return false;
		}
		
		$this->cacheEnabled = false;
		
		// Make sure there are no gaps in the creator indexes
		$creators = $this->getCreators();
		$lastPos = -1;
		foreach ($creators as $pos=>$creator) {
			if ($pos != $lastPos + 1) {
				trigger_error("Creator index $pos out of sequence for item $this->id", E_USER_ERROR);
			}
			$lastPos++;
		}
		
		$shardID = Zotero_Shards::getByLibraryID($this->_libraryID);
		
		$env = [];
		
		Zotero_DB::beginTransaction();
		
		try {
			//
			// New item, insert and return id
			//
			if (!$this->id || (empty($this->changed['version']) && !$this->exists())) {
				Z_Core::debug('Saving data for new item to database');
				
				$isNew = $env['isNew'] = true;
				$sqlColumns = array();
				$sqlValues = array();
				
				//
				// Primary fields
				//
				$itemID = $this->_id = $this->_id ? $this->_id : Zotero_ID::get('items');
				$key = $this->_key = $this->_key ? $this->_key : Zotero_ID::getKey();
				
				$sqlColumns = array(
					'itemID',
					'itemTypeID',
					'libraryID',
					'key',
					'dateAdded',
					'dateModified',
					'serverDateModified',
					'version'
				);
				$timestamp = Zotero_DB::getTransactionTimestamp();
				$dateAdded = $this->_dateAdded ? $this->_dateAdded : $timestamp;
				$dateModified = $this->_dateModified ? $this->_dateModified : $timestamp;
				$version = Zotero_Libraries::getUpdatedVersion($this->_libraryID);
				$sqlValues = array(
					$itemID,
					$this->_itemTypeID,
					$this->_libraryID,
					$key,
					$dateAdded,
					$dateModified,
					$timestamp,
					$version
				);
				
				$sql = 'INSERT INTO items (`' . implode('`, `', $sqlColumns) . '`) VALUES (';
				// Insert placeholders for bind parameters
				for ($i=0; $i<sizeOf($sqlValues); $i++) {
					$sql .= '?, ';
				}
				$sql = substr($sql, 0, -2) . ')';
				
				// Save basic data to items table
				try {
					$insertID = Zotero_DB::query($sql, $sqlValues, $shardID);
				}
				catch (Exception $e) {
					if (strpos($e->getMessage(), "Incorrect datetime value") !== false) {
						preg_match("/Incorrect datetime value: '([^']+)'/", $e->getMessage(), $matches);
						throw new Exception("=Invalid date value '{$matches[1]}' for item $key", Z_ERROR_INVALID_INPUT);
					}
					throw $e;
				}
				if (!$this->_id) {
					if (!$insertID) {
						throw new Exception("Item id not available after INSERT");
					}
					$itemID = $insertID;
					$this->_serverDateModified = $timestamp;
				}
				
				// Group item data
				if (Zotero_Libraries::getType($this->_libraryID) == 'group' && $userID) {
//.........这里部分代码省略.........

 public function save($userID = false)
 {
     if (!$this->libraryID) {
         trigger_error("Library ID must be set before saving", E_USER_ERROR);
     }
     Zotero_Items::editCheck($this);
     if (!$this->hasChanged()) {
         Z_Core::debug("Item {$this->id} has not changed");
         return false;
     }
     // Make sure there are no gaps in the creator indexes
     $creators = $this->getCreators();
     $lastPos = -1;
     foreach ($creators as $pos => $creator) {
         if ($pos != $lastPos + 1) {
             trigger_error("Creator index {$pos} out of sequence for item {$this->id}", E_USER_ERROR);
         }
         $lastPos++;
     }
     $shardID = Zotero_Shards::getByLibraryID($this->libraryID);
     Zotero_DB::beginTransaction();
     try {
         //
         // New item, insert and return id
         //
         if (!$this->id || !$this->exists()) {
             Z_Core::debug('Saving data for new item to database');
             $isNew = true;
             $sqlColumns = array();
             $sqlValues = array();
             //
             // Primary fields
             //
             $itemID = $this->id ? $this->id : Zotero_ID::get('items');
             $key = $this->key ? $this->key : $this->generateKey();
             $sqlColumns = array('itemID', 'itemTypeID', 'libraryID', 'key', 'dateAdded', 'dateModified', 'serverDateModified');
             $timestamp = Zotero_DB::getTransactionTimestamp();
             $sqlValues = array($itemID, $this->itemTypeID, $this->libraryID, $key, $this->dateAdded ? $this->dateAdded : $timestamp, $this->dateModified ? $this->dateModified : $timestamp, $timestamp);
             //
             // Primary fields
             //
             $sql = 'INSERT INTO items (`' . implode('`, `', $sqlColumns) . '`) VALUES (';
             // Insert placeholders for bind parameters
             for ($i = 0; $i < sizeOf($sqlValues); $i++) {
                 $sql .= '?, ';
             }
             $sql = substr($sql, 0, -2) . ')';
             // Save basic data to items table
             $insertID = Zotero_DB::query($sql, $sqlValues, $shardID);
             if (!$this->id) {
                 if (!$insertID) {
                     throw new Exception("Item id not available after INSERT");
                 }
                 $itemID = $insertID;
                 $this->serverDateModified = $timestamp;
             }
             // Group item data
             if (Zotero_Libraries::getType($this->libraryID) == 'group' && $userID) {
                 $sql = "INSERT INTO groupItems VALUES (?, ?, ?)";
                 Zotero_DB::query($sql, array($itemID, $userID, null), $shardID);
             }
             //
             // ItemData
             //
             if ($this->changed['itemData']) {
                 // Use manual bound parameters to speed things up
                 $origInsertSQL = "INSERT INTO itemData (itemID, fieldID, value) VALUES ";
                 $insertSQL = $origInsertSQL;
                 $insertParams = array();
                 $insertCounter = 0;
                 $maxInsertGroups = 40;
                 $max = Zotero_Items::$maxDataValueLength;
                 $fieldIDs = array_keys($this->changed['itemData']);
                 foreach ($fieldIDs as $fieldID) {
                     $value = $this->getField($fieldID, true, false, true);
                     if ($value == 'CURRENT_TIMESTAMP' && Zotero_ItemFields::getID('accessDate') == $fieldID) {
                         $value = Zotero_DB::getTransactionTimestamp();
                     }
                     // Check length
                     if (strlen($value) > $max) {
                         $fieldName = Zotero_ItemFields::getLocalizedString($this->itemTypeID, $fieldID);
                         throw new Exception("={$fieldName} field " . "'" . substr($value, 0, 50) . "...' too long");
                     }
                     if ($insertCounter < $maxInsertGroups) {
                         $insertSQL .= "(?,?,?),";
                         $insertParams = array_merge($insertParams, array($itemID, $fieldID, $value));
                     }
                     if ($insertCounter == $maxInsertGroups - 1) {
                         $insertSQL = substr($insertSQL, 0, -1);
                         $stmt = Zotero_DB::getStatement($insertSQL, true, $shardID);
                         Zotero_DB::queryFromStatement($stmt, $insertParams);
                         $insertSQL = $origInsertSQL;
                         $insertParams = array();
                         $insertCounter = -1;
                     }
                     $insertCounter++;
                 }
                 if ($insertCounter > 0 && $insertCounter < $maxInsertGroups) {
                     $insertSQL = substr($insertSQL, 0, -1);
                     $stmt = Zotero_DB::getStatement($insertSQL, true, $shardID);
//.........这里部分代码省略.........

 public static function getAllAdvanced($userID = false, $params = array(), $permissions = null)
 {
     $buffer = 20;
     $maxTimes = 3;
     $groups = array();
     $start = !empty($params['start']) ? $params['start'] : 0;
     $limit = !empty($params['limit']) ? $params['limit'] + $buffer : false;
     $totalResults = null;
     $times = 0;
     while (true) {
         if ($times > 0) {
             Z_Core::logError('Getting more groups in Zotero_Groups::getAllAdvanced()');
         }
         $sql = "SELECT SQL_CALC_FOUND_ROWS G.groupID, GUO.userID AS ownerUserID FROM groups G\n\t\t\t\t\tJOIN groupUsers GUO ON (G.groupID=GUO.groupID AND GUO.role='owner') ";
         $sqlParams = array();
         if ($userID) {
             $sql .= "JOIN groupUsers GUA ON (G.groupID=GUA.groupID) WHERE GUA.userID=? ";
             $sqlParams[] = $userID;
         }
         $paramSQL = "";
         $includeEmpty = false;
         if (!empty($params['q'])) {
             if (!is_array($params['q'])) {
                 $params['q'] = array($params['q']);
             }
             foreach ($params['q'] as $q) {
                 $field = explode(":", $q);
                 if (sizeOf($field) == 2) {
                     switch ($field[0]) {
                         case 'slug':
                             $includeEmpty = true;
                             break;
                         default:
                             throw new Exception("Cannot search by group field '{$field[0]}'", Z_ERROR_INVALID_GROUP_TYPE);
                     }
                     $paramSQL .= "AND " . $field[0];
                     // If first character is '-', negate
                     $paramSQL .= $field[0][0] == '-' ? '!' : '';
                     $paramSQL .= "=? ";
                     $sqlParams[] = $field[1];
                 } else {
                     $paramSQL .= "AND name LIKE ? ";
                     $sqlParams[] = "%{$q}%";
                 }
             }
         }
         if (!$userID) {
             if ($includeEmpty) {
                 $sql .= "WHERE 1 ";
             } else {
                 // Don't include groups that have never had items
                 $sql .= "JOIN libraries L ON (G.libraryID=L.libraryID)\n\t\t\t\t\t\t\tWHERE L.lastUpdated != '0000-00-00 00:00:00' ";
             }
         }
         $sql .= $paramSQL;
         if (!empty($params['fq'])) {
             if (!is_array($params['fq'])) {
                 $params['fq'] = array($params['fq']);
             }
             foreach ($params['fq'] as $fq) {
                 $facet = explode(":", $fq);
                 if (sizeOf($facet) == 2 && preg_match('/-?GroupType/', $facet[0])) {
                     switch ($facet[1]) {
                         case 'PublicOpen':
                         case 'PublicClosed':
                         case 'Private':
                             break;
                         default:
                             throw new Exception("Invalid group type '{$facet[1]}'", Z_ERROR_INVALID_GROUP_TYPE);
                     }
                     $sql .= "AND type";
                     // If first character is '-', negate
                     $sql .= $facet[0][0] == '-' ? '!' : '';
                     $sql .= "=? ";
                     $sqlParams[] = $facet[1];
                 }
             }
         }
         if (!empty($params['order'])) {
             $order = $params['order'];
             if ($order == 'title') {
                 $order = 'name';
             }
             $sql .= "ORDER BY {$order}";
             if (!empty($params['sort'])) {
                 $sql .= " " . $params['sort'] . " ";
             }
         }
         // Set limit higher than the actual limit, in case some groups are
         // removed during access checks
         //
         // Actual limiting is done below
         if ($limit) {
             $sql .= "LIMIT ?, ?";
             $sqlParams[] = $start;
             $sqlParams[] = $limit;
         }
         $rows = Zotero_DB::query($sql, $sqlParams);
         if (!$rows) {
             break;
//.........这里部分代码省略.........

 private static function deleteLibrary($libraryID, $shardID)
 {
     $sql = "DELETE FROM shardLibraries WHERE libraryID=?";
     Zotero_DB::query($sql, $libraryID, $shardID);
 }

 private static function updateLastAdded($storageFileID)
 {
     $sql = "UPDATE storageFiles SET lastAdded=NOW() WHERE storageFileID=?";
     Zotero_DB::query($sql, $storageFileID);
 }

 public function save()
 {
     if (!$this->libraryID) {
         trigger_error("Library ID must be set before saving", E_USER_ERROR);
     }
     Zotero_Creators::editCheck($this);
     // If empty, move on
     if ($this->firstName === '' && $this->lastName === '') {
         throw new Exception('First and last name are empty');
     }
     if ($this->fieldMode == 1 && $this->firstName !== '') {
         throw new Exception('First name must be empty in single-field mode');
     }
     if (!$this->hasChanged()) {
         Z_Core::debug("Creator {$this->id} has not changed");
         return false;
     }
     Zotero_DB::beginTransaction();
     try {
         $creatorID = $this->id ? $this->id : Zotero_ID::get('creators');
         $isNew = !$this->id;
         Z_Core::debug("Saving creator {$this->id}");
         $key = $this->key ? $this->key : $this->generateKey();
         $timestamp = Zotero_DB::getTransactionTimestamp();
         $dateAdded = $this->dateAdded ? $this->dateAdded : $timestamp;
         $dateModified = $this->changed['dateModified'] ? $this->dateModified : $timestamp;
         $fields = "firstName=?, lastName=?, fieldMode=?,\n\t\t\t\t\t\tlibraryID=?, `key`=?, dateAdded=?, dateModified=?, serverDateModified=?";
         $params = array($this->firstName, $this->lastName, $this->fieldMode, $this->libraryID, $key, $dateAdded, $dateModified, $timestamp);
         $shardID = Zotero_Shards::getByLibraryID($this->libraryID);
         try {
             if ($isNew) {
                 $sql = "INSERT INTO creators SET creatorID=?, {$fields}";
                 $stmt = Zotero_DB::getStatement($sql, true, $shardID);
                 Zotero_DB::queryFromStatement($stmt, array_merge(array($creatorID), $params));
                 // Remove from delete log if it's there
                 $sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='creator' AND `key`=?";
                 Zotero_DB::query($sql, array($this->libraryID, $key), $shardID);
             } else {
                 $sql = "UPDATE creators SET {$fields} WHERE creatorID=?";
                 $stmt = Zotero_DB::getStatement($sql, true, $shardID);
                 Zotero_DB::queryFromStatement($stmt, array_merge($params, array($creatorID)));
             }
         } catch (Exception $e) {
             if (strpos($e->getMessage(), " too long") !== false) {
                 if (strlen($this->firstName) > 255) {
                     throw new Exception("=First name '" . mb_substr($this->firstName, 0, 50) . "…' too long");
                 }
                 if (strlen($this->lastName) > 255) {
                     if ($this->fieldMode == 1) {
                         throw new Exception("=Last name '" . mb_substr($this->lastName, 0, 50) . "…' too long");
                     } else {
                         throw new Exception("=Name '" . mb_substr($this->lastName, 0, 50) . "…' too long");
                     }
                 }
             }
             throw $e;
         }
         // The client updates the mod time of associated items here, but
         // we don't, because either A) this is from syncing, where appropriate
         // mod times come from the client or B) the change is made through
         // $item->setCreator(), which updates the mod time.
         //
         // If the server started to make other independent creator changes,
         // linked items would need to be updated.
         Zotero_DB::commit();
         Zotero_Creators::cachePrimaryData(array('id' => $creatorID, 'libraryID' => $this->libraryID, 'key' => $key, 'dateAdded' => $dateAdded, 'dateModified' => $dateModified, 'firstName' => $this->firstName, 'lastName' => $this->lastName, 'fieldMode' => $this->fieldMode));
     } catch (Exception $e) {
         Zotero_DB::rollback();
         throw $e;
     }
     // If successful, set values in object
     if (!$this->id) {
         $this->id = $creatorID;
     }
     if (!$this->key) {
         $this->key = $key;
     }
     $this->init();
     if ($isNew) {
         Zotero_Creators::cache($this);
         Zotero_Creators::cacheLibraryKeyID($this->libraryID, $key, $creatorID);
     }
     // TODO: invalidate memcache?
     return $this->id;
 }

 public static function unregister($mode, $addr, $port)
 {
     $sql = "DELETE FROM processorDaemons WHERE mode=? AND addr=INET_ATON(?) AND port=?";
     Zotero_DB::query($sql, array($mode, $addr, $port));
 }

 private function logGroupLibraryRemoval()
 {
     $users = $this->getUsers();
     $usersByShard = array();
     foreach ($users as $userID) {
         $shardID = Zotero_Shards::getByUserID($userID);
         if (!isset($usersByShard[$shardID])) {
             $usersByShard[$shardID] = array();
         }
         $usersByShard[$shardID][] = $userID;
     }
     foreach ($usersByShard as $shardID => $userIDs) {
         // Add to delete log for all group members
         $sql = "REPLACE INTO syncDeleteLogIDs (libraryID, objectType, id) VALUES ";
         $params = array();
         $sets = array();
         foreach ($userIDs as $userID) {
             $libraryID = Zotero_Users::getLibraryIDFromUserID($userID);
             $sets[] = "(?,?,?)";
             $params = array_merge($params, array($libraryID, 'group', $this->id));
         }
         $sql .= implode(",", $sets);
         Zotero_DB::query($sql, $params, $shardID);
     }
 }

 public static function addCustomType($name)
 {
     if (self::getID($name)) {
         trigger_error("Item type '{$name}' already exists", E_USER_ERROR);
     }
     if (!preg_match('/^[a-z][^\\s0-9]+$/', $name)) {
         trigger_error("Invalid item type name '{$name}'", E_USER_ERROR);
     }
     // TODO: make sure user hasn't added too many already
     Zotero_DB::beginTransaction();
     $sql = "SELECT NEXT_ID(creatorTypeID) FROM creatorTypes";
     $creatorTypeID = Zotero_DB::valueQuery($sql);
     $sql = "INSERT INTO creatorTypes (?, ?, ?)";
     Zotero_DB::query($sql, array($creatorTypeID, $name, 1));
     Zotero_DB::commit();
     return $creatorTypeID;
 }

 public static function search($libraryID, $onlyTopLevel = false, $params = array(), $includeTrashed = false, $asKeys = false)
 {
     $rnd = "_" . uniqid($libraryID . "_");
     if ($asKeys) {
         $results = array('keys' => array(), 'total' => 0);
     } else {
         $results = array('items' => array(), 'total' => 0);
     }
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     $itemIDs = array();
     $keys = array();
     $deleteTempTable = array();
     // Pass a list of itemIDs, for when the initial search is done via SQL
     if (!empty($params['itemIDs'])) {
         $itemIDs = $params['itemIDs'];
     }
     if (!empty($params['itemKey'])) {
         $keys = explode(',', $params['itemKey']);
     }
     $titleSort = !empty($params['order']) && $params['order'] == 'title';
     $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT " . ($asKeys ? "I.key" : "I.itemID") . " FROM items I ";
     $sqlParams = array($libraryID);
     if (!empty($params['q']) || $titleSort) {
         $titleFieldIDs = array_merge(array(Zotero_ItemFields::getID('title')), Zotero_ItemFields::getTypeFieldsFromBase('title'));
         $sql .= "LEFT JOIN itemData IDT ON (IDT.itemID=I.itemID AND IDT.fieldID IN (" . implode(',', $titleFieldIDs) . ")) ";
     }
     if (!empty($params['q'])) {
         $sql .= "LEFT JOIN itemCreators IC ON (IC.itemID=I.itemID)\n\t\t\t\t\tLEFT JOIN creators C ON (C.creatorID=IC.creatorID) ";
     }
     if ($onlyTopLevel || !empty($params['q']) || $titleSort) {
         $sql .= "LEFT JOIN itemNotes INo ON (INo.itemID=I.itemID) ";
     }
     if ($onlyTopLevel) {
         $sql .= "LEFT JOIN itemAttachments IA ON (IA.itemID=I.itemID) ";
     }
     if (!$includeTrashed) {
         $sql .= "LEFT JOIN deletedItems DI ON (DI.itemID=I.itemID) ";
     }
     if (!empty($params['order'])) {
         switch ($params['order']) {
             case 'title':
             case 'creator':
                 $sql .= "LEFT JOIN itemSortFields ISF ON (ISF.itemID=I.itemID) ";
                 break;
             case 'date':
                 $dateFieldIDs = array_merge(array(Zotero_ItemFields::getID('date')), Zotero_ItemFields::getTypeFieldsFromBase('date'));
                 $sql .= "LEFT JOIN itemData IDD ON (IDD.itemID=I.itemID AND IDD.fieldID IN (" . implode(',', $dateFieldIDs) . ")) ";
                 break;
             case 'itemType':
                 // Create temporary table to store item type names
                 //
                 // We use IF NOT EXISTS just to make sure there are
                 // no problems with restoration from the binary log
                 $sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS tmpItemTypeNames{$rnd}\n\t\t\t\t\t\t\t(itemTypeID SMALLINT UNSIGNED NOT NULL,\n\t\t\t\t\t\t\titemTypeName VARCHAR(255) NOT NULL,\n\t\t\t\t\t\t\tPRIMARY KEY (itemTypeID),\n\t\t\t\t\t\t\tINDEX (itemTypeName))";
                 Zotero_DB::query($sql2, false, $shardID);
                 $deleteTempTable['tmpItemTypeNames'] = true;
                 $types = Zotero_ItemTypes::getAll('en-US');
                 foreach ($types as $type) {
                     $sql2 = "INSERT INTO tmpItemTypeNames{$rnd} VALUES (?, ?)";
                     Zotero_DB::query($sql2, array($type['id'], $type['localized']), $shardID);
                 }
                 // Join temp table to query
                 $sql .= "JOIN tmpItemTypeNames{$rnd} TITN ON (TITN.itemTypeID=I.itemTypeID) ";
                 break;
             case 'addedBy':
                 $isGroup = Zotero_Libraries::getType($libraryID) == 'group';
                 if ($isGroup) {
                     // Create temporary table to store usernames
                     //
                     // We use IF NOT EXISTS just to make sure there are
                     // no problems with restoration from the binary log
                     $sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS tmpCreatedByUsers{$rnd}\n\t\t\t\t\t\t\t\t(userID INT UNSIGNED NOT NULL,\n\t\t\t\t\t\t\t\tusername VARCHAR(255) NOT NULL,\n\t\t\t\t\t\t\t\tPRIMARY KEY (userID),\n\t\t\t\t\t\t\t\tINDEX (username))";
                     Zotero_DB::query($sql2, false, $shardID);
                     $deleteTempTable['tmpCreatedByUsers'] = true;
                     $sql2 = "SELECT DISTINCT createdByUserID FROM items\n\t\t\t\t\t\t\t\tJOIN groupItems USING (itemID) WHERE\n\t\t\t\t\t\t\t\tcreatedByUserID IS NOT NULL AND ";
                     if ($itemIDs) {
                         $sql2 .= "itemID IN (" . implode(', ', array_fill(0, sizeOf($itemIDs), '?')) . ") ";
                         $createdByUserIDs = Zotero_DB::columnQuery($sql2, $itemIDs, $shardID);
                     } else {
                         $sql2 .= "libraryID=?";
                         $createdByUserIDs = Zotero_DB::columnQuery($sql2, $libraryID, $shardID);
                     }
                     // Populate temp table with usernames
                     if ($createdByUserIDs) {
                         $toAdd = array();
                         foreach ($createdByUserIDs as $createdByUserID) {
                             $toAdd[] = array($createdByUserID, Zotero_Users::getUsername($createdByUserID));
                         }
                         $sql2 = "INSERT IGNORE INTO tmpCreatedByUsers{$rnd} VALUES ";
                         Zotero_DB::bulkInsert($sql2, $toAdd, 50, false, $shardID);
                         // Join temp table to query
                         $sql .= "JOIN groupItems GI ON (GI.itemID=I.itemID)\n\t\t\t\t\t\t\t\t\tJOIN tmpCreatedByUsers{$rnd} TCBU ON (TCBU.userID=GI.createdByUserID) ";
                     }
                 }
                 break;
         }
     }
     $sql .= "WHERE I.libraryID=? ";
     if ($onlyTopLevel) {
         $sql .= "AND INo.sourceItemID IS NULL AND IA.sourceItemID IS NULL ";
//.........这里部分代码省略.........

 public static function search($libraryID, $onlyTopLevel = false, $params)
 {
     $results = array('results' => array(), 'total' => 0);
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
     if ($params['format'] == 'keys') {
         $sql .= "`key`";
     } else {
         $sql .= "`key`, version";
     }
     $sql .= " FROM collections WHERE libraryID=? ";
     $sqlParams = array($libraryID);
     if ($onlyTopLevel) {
         $sql .= "AND parentCollectionID IS NULL ";
     }
     // Pass a list of collectionIDs, for when the initial search is done via SQL
     $collectionIDs = !empty($params['collectionIDs']) ? $params['collectionIDs'] : array();
     $collectionKeys = $params['collectionKey'];
     if ($collectionIDs) {
         $sql .= "AND collectionID IN (" . implode(', ', array_fill(0, sizeOf($collectionIDs), '?')) . ") ";
         $sqlParams = array_merge($sqlParams, $collectionIDs);
     }
     if ($collectionKeys) {
         $sql .= "AND `key` IN (" . implode(', ', array_fill(0, sizeOf($collectionKeys), '?')) . ") ";
         $sqlParams = array_merge($sqlParams, $collectionKeys);
     }
     if (!empty($params['q'])) {
         $sql .= "AND collectionName LIKE ? ";
         $sqlParams[] = '%' . $params['q'] . '%';
     }
     if (!empty($params['since'])) {
         $sql .= "AND version > ? ";
         $sqlParams[] = $params['since'];
     }
     // TEMP: for sync transition
     if (!empty($params['sincetime'])) {
         $sql .= "AND serverDateModified >= FROM_UNIXTIME(?) ";
         $sqlParams[] = $params['sincetime'];
     }
     if (!empty($params['sort'])) {
         switch ($params['sort']) {
             case 'title':
                 $orderSQL = 'collectionName';
                 break;
             case 'collectionKeyList':
                 $orderSQL = "FIELD(`key`," . implode(',', array_fill(0, sizeOf($collectionKeys), '?')) . ")";
                 $sqlParams = array_merge($sqlParams, $collectionKeys);
                 break;
             default:
                 $orderSQL = $params['sort'];
         }
         $sql .= "ORDER BY {$orderSQL}";
         if (!empty($params['direction'])) {
             $sql .= " {$params['direction']}";
         }
         $sql .= ", ";
     }
     $sql .= "version " . (!empty($params['direction']) ? $params['direction'] : "ASC") . ", collectionID " . (!empty($params['direction']) ? $params['direction'] : "ASC") . " ";
     if (!empty($params['limit'])) {
         $sql .= "LIMIT ?, ?";
         $sqlParams[] = $params['start'] ? $params['start'] : 0;
         $sqlParams[] = $params['limit'];
     }
     if ($params['format'] == 'keys') {
         $rows = Zotero_DB::columnQuery($sql, $sqlParams, $shardID);
     } else {
         $rows = Zotero_DB::query($sql, $sqlParams, $shardID);
     }
     $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID);
     if ($rows) {
         if ($params['format'] == 'keys') {
             $results['results'] = $rows;
         } else {
             if ($params['format'] == 'versions') {
                 foreach ($rows as $row) {
                     $results['results'][$row['key']] = $row['version'];
                 }
             } else {
                 $collections = [];
                 foreach ($rows as $row) {
                     $obj = self::getByLibraryAndKey($libraryID, $row['key']);
                     $obj->setAvailableVersion($row['version']);
                     $collections[] = $obj;
                 }
                 $results['results'] = $collections;
             }
         }
     }
     return $results;
 }

 /**
  * Make sure we have a valid session
  */
 private function sessionCheck()
 {
     if (empty($_REQUEST['sessionid'])) {
         $this->error(403, 'NO_SESSION_ID', "Session ID not provided");
     }
     if (!preg_match('/^[a-f0-9]{32}$/', $_REQUEST['sessionid'])) {
         $this->error($this->apiVersion >= 9 ? 403 : 500, 'INVALID_SESSION_ID', "Invalid session ID");
     }
     $sessionID = $_REQUEST['sessionid'];
     $session = Z_Core::$MC->get("syncSession_{$sessionID}");
     $userID = $session ? $session['userID'] : null;
     // TEMP: can switch to just $session
     $ipAddress = isset($session['ipAddress']) ? $session['ipAddress'] : null;
     if (!$userID) {
         $sql = "SELECT userid, (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(timestamp)) AS age,\n\t\t\t\t\tINET_NTOA(ipAddress) AS ipAddress FROM sessions WHERE sessionID=?";
         $session = Zotero_DB::rowQuery($sql, $sessionID);
         if (!$session) {
             $this->error($this->apiVersion >= 9 ? 403 : 500, 'INVALID_SESSION_ID', "Invalid session ID");
         }
         if ($session['age'] > $this->sessionLifetime) {
             $this->error($this->apiVersion >= 9 ? 403 : 500, 'SESSION_TIMED_OUT', "Session timed out");
         }
         $userID = $session['userid'];
         $ipAddress = $session['ipAddress'];
     }
     $updated = Z_Core::$MC->set("syncSession_{$sessionID}", array('sessionID' => $sessionID, 'userID' => $userID, 'ipAddress' => $ipAddress), $this->sessionLifetime - 1200);
     // Every 20 minutes, update the timestamp in the DB
     if (!Z_Core::$MC->get("syncSession_" . $sessionID . "_dbUpdated")) {
         $sql = "UPDATE sessions SET timestamp=NOW() WHERE sessionID=?";
         Zotero_DB::query($sql, $sessionID);
         Z_Core::$MC->set("syncSession_" . $sessionID . "_dbUpdated", true, 1200);
     }
     $this->sessionID = $sessionID;
     $this->userID = $userID;
     $this->userLibraryID = Zotero_Users::getLibraryIDFromUserID($userID);
     $this->ipAddress = $ipAddress;
 }