public static function getAllAdvanced($libraryID, $onlyTopLevel = false, $params) { $results = array('collections' => array(), 'total' => 0); $shardID = Zotero_Shards::getByLibraryID($libraryID); $sql = "SELECT SQL_CALC_FOUND_ROWS collectionID FROM collections\n\t\t\t\tWHERE libraryID=? "; if ($onlyTopLevel) { $sql .= "AND parentCollectionID IS NULL "; } if (!empty($params['order'])) { $order = $params['order']; if ($order == 'title') { $order = 'collectionName'; } $sql .= "ORDER BY {$order} "; if (!empty($params['sort'])) { $sql .= $params['sort'] . " "; } } $sqlParams = array($libraryID); if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } $ids = Zotero_DB::columnQuery($sql, $sqlParams, $shardID); if ($ids) { $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID); $collections = array(); foreach ($ids as $id) { $collections[] = self::get($libraryID, $id); } $results['collections'] = $collections; } return $results; }
public static function search($libraryID, $params) { // Default empty library if ($libraryID === 0) { return []; } $sql = "SELECT name FROM settings WHERE libraryID=?"; $params = array($libraryID); if (!empty($params['since'])) { $sql .= "AND version > ? "; $sqlParams[] = $params['since']; } // TEMP: for sync transition if (!empty($params['sincetime'])) { $sql .= "AND lastUpdated >= FROM_UNIXTIME(?) "; $sqlParams[] = $params['sincetime']; } $names = Zotero_DB::columnQuery($sql, $params, Zotero_Shards::getByLibraryID($libraryID)); if (!$names) { $names = array(); } $settings = array(); foreach ($names as $name) { $setting = new Zotero_Setting(); $setting->libraryID = $libraryID; $setting->name = $name; $settings[] = $setting; } return $settings; }
public static function getUserKeys($userID) { $keys = array(); $keyIDs = Zotero_DB::columnQuery("SELECT keyID FROM `keys` WHERE userID=?", $userID); if ($keyIDs) { foreach ($keyIDs as $keyID) { $keyObj = new Zotero_Key(); $keyObj->id = $keyID; $keys[] = $keyObj; } } return $keys; }
public static function getCreatorsWithData($libraryID, $creator, $sortByItemCountDesc = false) { $sql = "SELECT creatorID FROM creators "; if ($sortByItemCountDesc) { $sql .= "LEFT JOIN itemCreators USING (creatorID) "; } $sql .= "WHERE libraryID=? AND firstName COLLATE utf8_bin = ? " . "AND lastName COLLATE utf8_bin = ? AND fieldMode=?"; if ($sortByItemCountDesc) { $sql .= " ORDER BY IFNULL(COUNT(*), 0) DESC"; } $ids = Zotero_DB::columnQuery($sql, array($libraryID, $creator->firstName, $creator->lastName, $creator->fieldMode), Zotero_Shards::getByLibraryID($libraryID)); return $ids; }
public static function getUserKeysWithLibrary($userID, $libraryID) { $libraryType = Zotero_Libraries::getType($libraryID); $sql = "SELECT keyID FROM `keys` JOIN keyPermissions USING (keyID) " . "WHERE userID=? AND (libraryID=?"; // If group library, include keys with access to all groups if ($libraryType == 'group') { $sql .= " OR libraryID=0"; } $sql .= ") AND permission='library' AND granted=1"; $keyIDs = Zotero_DB::columnQuery($sql, [$userID, $libraryID]); $keys = []; if ($keyIDs) { foreach ($keyIDs as $keyID) { $keyObj = new Zotero_Key(); $keyObj->id = $keyID; $keys[] = $keyObj; } } return $keys; }
public static function purgeUnusedFiles() { throw new Exception("Now sharded"); self::requireLibrary(); // Get all used files and files that were last deleted more than a month ago $sql = "SELECT MD5(CONCAT(hash, filename, zip)) AS file FROM storageFiles\n\t\t\t\t\tJOIN storageFileItems USING (storageFileID)\n\t\t\t\tUNION\n\t\t\t\tSELECT MD5(CONCAT(hash, filename, zip)) AS file FROM storageFiles\n\t\t\t\t\tWHERE lastDeleted > NOW() - INTERVAL 1 MONTH"; $files = Zotero_DB::columnQuery($sql); S3::setAuth(Z_CONFIG::$S3_ACCESS_KEY, Z_CONFIG::$S3_SECRET_KEY); $s3Files = S3::getBucket(Z_CONFIG::$S3_BUCKET); $toPurge = array(); foreach ($s3Files as $s3File) { preg_match('/^([0-9a-g]{32})\\/(c\\/)?(.+)$/', $s3File['name'], $matches); if (!$matches) { throw new Exception("Invalid filename '" . $s3File['name'] . "'"); } $zip = $matches[2] ? '1' : '0'; // Compressed file $hash = md5($matches[1] . $matches[3] . $zip); if (!in_array($hash, $files)) { $toPurge[] = array('hash' => $matches[1], 'filename' => $matches[3], 'zip' => $zip); } } Zotero_DB::beginTransaction(); foreach ($toPurge as $info) { S3::deleteObject(Z_CONFIG::$S3_BUCKET, self::getPathPrefix($info['hash'], $info['zip']) . $info['filename']); $sql = "DELETE FROM storageFiles WHERE hash=? AND filename=? AND zip=?"; Zotero_DB::query($sql, array($info['hash'], $info['filename'], $info['zip'])); // TODO: maybe check to make sure associated files haven't just been created? } Zotero_DB::commit(); return sizeOf($toPurge); }
/** * Delete data from memcached */ public static function deleteCachedData($libraryID) { $shardID = Zotero_Shards::getByLibraryID($libraryID); // Clear itemID-specific memcache values $sql = "SELECT itemID FROM items WHERE libraryID=?"; $itemIDs = Zotero_DB::columnQuery($sql, $libraryID, $shardID); if ($itemIDs) { $cacheKeys = array("itemCreators", "itemIsDeleted", "itemRelated", "itemUsedFieldIDs", "itemUsedFieldNames"); foreach ($itemIDs as $itemID) { foreach ($cacheKeys as $key) { Z_Core::$MC->delete($key . '_' . $itemID); } } } /*foreach (Zotero_DataObjects::$objectTypes as $type=>$arr) { $className = "Zotero_" . $arr['plural']; call_user_func(array($className, "clearPrimaryDataCache"), $libraryID); }*/ }
public static function getOldErrorProcesses($host, $seconds = 60) { $sql = "SELECT syncProcessID FROM syncUploadQueue\n\t\t\t\tWHERE started < NOW() - INTERVAL ? SECOND AND errorCheck=1"; $params = array($seconds); if ($host) { $sql .= " AND processorHost=INET_ATON(?)"; $params[] = $host; } return Zotero_DB::columnQuery($sql, $params); }
public function erase() { if (!$this->loaded) { Z_Core::debug("Not deleting unloaded group {$this->id}"); return; } Zotero_DB::beginTransaction(); $userIDs = self::getUsers(); $this->logGroupLibraryRemoval(); Zotero_Libraries::deleteCachedData($this->libraryID); Zotero_Libraries::clearAllData($this->libraryID); $sql = "DELETE FROM shardLibraries WHERE libraryID=?"; $deleted = Zotero_DB::query($sql, $this->libraryID, Zotero_Shards::getByLibraryID($this->libraryID)); if (!$deleted) { throw new Exception("Group not deleted"); } $sql = "DELETE FROM libraries WHERE libraryID=?"; $deleted = Zotero_DB::query($sql, $this->libraryID); if (!$deleted) { throw new Exception("Group not deleted"); } // Delete key permissions for this library, and then delete any keys // that had no other permissions $sql = "SELECT keyID FROM keyPermissions WHERE libraryID=?"; $keyIDs = Zotero_DB::columnQuery($sql, $this->libraryID); if ($keyIDs) { $sql = "DELETE FROM keyPermissions WHERE libraryID=?"; Zotero_DB::query($sql, $this->libraryID); $sql = "DELETE K FROM `keys` K LEFT JOIN keyPermissions KP USING (keyID)\n\t\t\t\t\tWHERE keyID IN (" . implode(', ', array_fill(0, sizeOf($keyIDs), '?')) . ") AND KP.keyID IS NULL"; Zotero_DB::query($sql, $keyIDs); } // If group is locked by a sync, flag group for a timestamp update // once the sync is done so that the uploading user gets the change try { foreach ($userIDs as $userID) { if ($syncUploadQueueID = Zotero_Sync::getUploadQueueIDByUserID($userID)) { Zotero_Sync::postWriteLog($syncUploadQueueID, 'group', $this->id, 'delete'); } } } catch (Exception $e) { Z_Core::logError($e); } Zotero_Notifier::trigger('delete', 'library', $this->libraryID); Zotero_DB::commit(); $this->erased = true; }
protected function loadChildItems($reload = false) { if ($this->loaded['childItems'] && !$reload) { return; } Z_Core::debug("Loading child items for collection {$this->id}"); if (!$this->id) { trigger_error('$this->id not set', E_USER_ERROR); } $sql = "SELECT itemID FROM collectionItems WHERE collectionID=?"; $ids = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID)); $this->childItems = $ids ? $ids : []; $this->loaded['childItems'] = true; $this->clearChanged('childItems'); }
public function getLinkedItems() { if (!$this->id) { return array(); } $items = array(); $sql = "SELECT itemID FROM itemCreators WHERE creatorID=?"; $itemIDs = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID)); if (!$itemIDs) { return $items; } foreach ($itemIDs as $itemID) { $items[] = Zotero_Items::get($this->libraryID, $itemID); } return $items; }
public static function search($libraryID, $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 { if ($params['format'] == 'versions') { $sql .= "`key`, version"; } else { $sql .= "searchID"; } } $sql .= " FROM savedSearches WHERE libraryID=? "; $sqlParams = array($libraryID); // Pass a list of searchIDs, for when the initial search is done via SQL $searchIDs = !empty($params['searchIDs']) ? $params['searchIDs'] : array(); // Or keys, for the searchKey parameter $searchKeys = $params['searchKey']; 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 ($searchIDs) { $sql .= "AND searchID IN (" . implode(', ', array_fill(0, sizeOf($searchIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $searchIDs); } if ($searchKeys) { $sql .= "AND `key` IN (" . implode(', ', array_fill(0, sizeOf($searchKeys), '?')) . ") "; $sqlParams = array_merge($sqlParams, $searchKeys); } if (!empty($params['sort'])) { switch ($params['sort']) { case 'title': $orderSQL = 'searchName'; break; case 'searchKeyList': $orderSQL = "FIELD(`key`," . implode(',', array_fill(0, sizeOf($searchKeys), '?')) . ")"; $sqlParams = array_merge($sqlParams, $searchKeys); 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") . ", searchID " . (!empty($params['direction']) ? $params['direction'] : "ASC") . " "; if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } if ($params['format'] == 'versions') { $rows = Zotero_DB::query($sql, $sqlParams, $shardID); } else { $rows = Zotero_DB::columnQuery($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 { $searches = array(); foreach ($rows as $id) { $searches[] = self::get($libraryID, $id); } $results['results'] = $searches; } } } return $results; }
public static function getDeleteLogKeys($libraryID, $version, $versionIsTimestamp = false) { // Default empty library if ($libraryID === 0) { return []; } $type = self::$objectType; // TEMP: until classic syncing is deprecated and the objectType // 'tagName' is changed to 'tag' if ($type == 'tag') { $type = 'tagName'; } $sql = "SELECT `key` FROM syncDeleteLogKeys " . "WHERE objectType=? AND libraryID=? AND "; // TEMP: sync transition $sql .= $versionIsTimestamp ? "timestamp>=FROM_UNIXTIME(?)" : "version>?"; $keys = Zotero_DB::columnQuery($sql, array($type, $libraryID, $version), Zotero_Shards::getByLibraryID($libraryID)); if (!$keys) { return array(); } return $keys; }
public static function search($libraryID, $params) { $results = array('results' => array(), 'total' => 0); // Default empty library if ($libraryID === 0) { return $results; } $shardID = Zotero_Shards::getByLibraryID($libraryID); $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT tagID FROM tags " . "JOIN itemTags USING (tagID) WHERE libraryID=? "; $sqlParams = array($libraryID); // Pass a list of tagIDs, for when the initial search is done via SQL $tagIDs = !empty($params['tagIDs']) ? $params['tagIDs'] : array(); // Filter for specific tags with "?tag=foo || bar" $tagNames = !empty($params['tag']) ? explode(' || ', $params['tag']) : array(); if ($tagIDs) { $sql .= "AND tagID IN (" . implode(', ', array_fill(0, sizeOf($tagIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $tagIDs); } if ($tagNames) { $sql .= "AND `name` IN (" . implode(', ', array_fill(0, sizeOf($tagNames), '?')) . ") "; $sqlParams = array_merge($sqlParams, $tagNames); } if (!empty($params['q'])) { if (!is_array($params['q'])) { $params['q'] = array($params['q']); } foreach ($params['q'] as $q) { $sql .= "AND name LIKE ? "; $sqlParams[] = "%{$q}%"; } } $tagTypeSets = Zotero_API::getSearchParamValues($params, 'tagType'); if ($tagTypeSets) { $positives = array(); $negatives = array(); foreach ($tagTypeSets as $set) { if ($set['negation']) { $negatives = array_merge($negatives, $set['values']); } else { $positives = array_merge($positives, $set['values']); } } if ($positives) { $sql .= "AND type IN (" . implode(',', array_fill(0, sizeOf($positives), '?')) . ") "; $sqlParams = array_merge($sqlParams, $positives); } if ($negatives) { $sql .= "AND type NOT IN (" . implode(',', array_fill(0, sizeOf($negatives), '?')) . ") "; $sqlParams = array_merge($sqlParams, $negatives); } } if (!empty($params['since'])) { $sql .= "AND version > ? "; $sqlParams[] = $params['since']; } if (!empty($params['sort'])) { $order = $params['sort']; if ($order == 'title') { // Force a case-insensitive sort $sql .= "ORDER BY name COLLATE utf8_unicode_ci "; } else { if ($order == 'numItems') { $sql .= "GROUP BY tags.tagID ORDER BY COUNT(tags.tagID)"; } else { $sql .= "ORDER BY {$order} "; } } if (!empty($params['direction'])) { $sql .= " " . $params['direction'] . " "; } } if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } $ids = Zotero_DB::columnQuery($sql, $sqlParams, $shardID); $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID); if ($ids) { $tags = array(); foreach ($ids as $id) { $tags[] = Zotero_Tags::get($libraryID, $id); } $results['results'] = $tags; } return $results; }
public static function getAllShards($state = false) { $sql = "SELECT shardID FROM shards S JOIN shardHosts SH USING (shardHostID)"; if ($state) { $sql .= " WHERE SH.state=? AND S.state=?"; $params = array($state, $state); } else { $params = array(); } return Zotero_DB::columnQuery($sql, $params); }
/** * Returns all tags assigned to an item * * @return array Array of Zotero.Tag objects */ public function getTags($asIDs = false) { if (!$this->id) { return array(); } $sql = "SELECT tagID FROM tags JOIN itemTags USING (tagID)\n\t\t\t\tWHERE itemID=? ORDER BY name"; $tagIDs = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID)); if (!$tagIDs) { return array(); } if ($asIDs) { return $tagIDs; } $tagObjs = array(); foreach ($tagIDs as $tagID) { $tag = Zotero_Tags::get($this->libraryID, $tagID, true); $tagObjs[] = $tag; } return $tagObjs; }
/** * Delete any relations that have the URI as either the subject * or the object */ public static function eraseByURI($libraryID, $uri, $ignorePredicates = false) { Zotero_DB::beginTransaction(); $sql = "SELECT relationID FROM relations WHERE libraryID=? AND subject=?"; $params = [$libraryID, $uri]; if ($ignorePredicates) { foreach ($ignorePredicates as $ignorePredicate) { $sql .= " AND predicate != ?"; $params[] = $ignorePredicate; } } $sql .= " UNION SELECT relationID FROM relations WHERE libraryID=? AND object=?"; $params = array_merge($params, [$libraryID, $uri]); if ($ignorePredicates) { foreach ($ignorePredicates as $ignorePredicate) { $sql .= " AND predicate != ?"; $params[] = $ignorePredicate; } } $ids = Zotero_DB::columnQuery($sql, $params, Zotero_Shards::getByLibraryID($libraryID)); if ($ids) { foreach ($ids as $id) { $relation = self::get($libraryID, $id); Zotero_Relations::delete($libraryID, $relation->key); } } Zotero_DB::commit(); }
public static function getAllAdvanced($libraryID, $params) { $results = array('objects' => array(), 'total' => 0); $sql = "SELECT SQL_CALC_FOUND_ROWS tagID FROM tags "; if (!empty($params['order']) && $params['order'] == 'numItems') { $sql .= " LEFT JOIN itemTags USING (tagID)"; } $sql .= "WHERE libraryID=? "; $sqlParams = array($libraryID); if (!empty($params['q'])) { if (!is_array($params['q'])) { $params['q'] = array($params['q']); } foreach ($params['q'] as $q) { $sql .= "AND name LIKE ? "; $sqlParams[] = "%{$q}%"; } } $tagTypeSets = Zotero_API::getSearchParamValues($params, 'tagType'); if ($tagTypeSets) { $positives = array(); $negatives = array(); foreach ($tagTypeSets as $set) { if ($set['negation']) { $negatives = array_merge($negatives, $set['values']); } else { $positives = array_merge($positives, $set['values']); } } if ($positives) { $sql .= "AND type IN (" . implode(',', array_fill(0, sizeOf($positives), '?')) . ") "; $sqlParams = array_merge($sqlParams, $positives); } if ($negatives) { $sql .= "AND type NOT IN (" . implode(',', array_fill(0, sizeOf($negatives), '?')) . ") "; $sqlParams = array_merge($sqlParams, $negatives); } } if (!empty($params['order'])) { $order = $params['order']; if ($order == 'title') { // Force a case-insensitive sort $sql .= "ORDER BY name COLLATE utf8_unicode_ci "; } else { if ($order == 'numItems') { $sql .= "GROUP BY tags.tagID ORDER BY COUNT(tags.tagID)"; } else { $sql .= "ORDER BY {$order} "; } } if (!empty($params['sort'])) { $sql .= " " . $params['sort'] . " "; } } if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } $shardID = Zotero_Shards::getByLibraryID($libraryID); $ids = Zotero_DB::columnQuery($sql, $sqlParams, $shardID); if ($ids) { $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID); $tags = array(); foreach ($ids as $id) { $tags[] = Zotero_Tags::get($libraryID, $id); } $results['objects'] = $tags; } return $results; }
private function loadChildItems() { Z_Core::debug("Loading child items for collection {$this->id}"); if ($this->childItemsLoaded) { trigger_error("Child items for collection {$this->id} already loaded", E_USER_ERROR); } if (!$this->id) { trigger_error('$this->id not set', E_USER_ERROR); } $sql = "SELECT itemID FROM collectionItems WHERE collectionID=?"; $ids = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID)); $this->childItems = $ids ? $ids : array(); $this->childItemsLoaded = true; }
public static function getTypeFieldsFromBase($baseField, $asNames = false) { $baseFieldID = self::getID($baseField); if (!$baseFieldID) { throw new Exception("Invalid base field '{$baseField}'"); } if ($asNames) { if (isset(self::$typeFieldNamesByBaseCache[$baseFieldID])) { return self::$typeFieldNamesByBaseCache[$baseFieldID]; } $cacheKey = "itemTypeFieldNamesByBase_" . $baseFieldID; $fieldNames = Z_Core::$MC->get($cacheKey); if ($fieldNames) { self::$typeFieldNamesByBaseCache[$baseFieldID] = $fieldNames; return $fieldNames; } $sql = "SELECT fieldName FROM fields WHERE fieldID IN (\n\t\t\t\tSELECT fieldID FROM baseFieldMappings\n\t\t\t\tWHERE baseFieldID=?)"; $fieldNames = Zotero_DB::columnQuery($sql, $baseFieldID); if (!$fieldNames) { $fieldNames = array(); } self::$typeFieldNamesByBaseCache[$baseFieldID] = $fieldNames; Z_Core::$MC->set($cacheKey, $fieldNames); return $fieldNames; } // TEMP if ($baseFieldID == 14) { return array(96, 52, 100, 10008); } if (isset(self::$typeFieldIDsByBaseCache[$baseFieldID])) { return self::$typeFieldIDsByBaseCache[$baseFieldID]; } $cacheKey = "itemTypeFieldIDsByBase_" . $baseFieldID; $fieldIDs = Z_Core::$MC->get($cacheKey); if ($fieldIDs) { self::$typeFieldIDsByBaseCache[$baseFieldID] = $fieldIDs; return $fieldIDs; } $sql = "SELECT DISTINCT fieldID FROM baseFieldMappings WHERE baseFieldID=?"; $fieldIDs = Zotero_DB::columnQuery($sql, $baseFieldID); if (!$fieldIDs) { $fieldIDs = array(); } self::$typeFieldIDsByBaseCache[$baseFieldID] = $fieldIDs; Z_Core::$MC->set($cacheKey, $fieldIDs); return $fieldIDs; }
private function getRecentIPs() { $sql = "SELECT INET_NTOA(ipAddress) FROM keyAccessLog WHERE keyID=?\n\t\t\t\tORDER BY timestamp DESC LIMIT 5"; $ips = Zotero_DB::columnQuery($sql, $this->id); if (!$ips) { return array(); } return $ips; }
public static function getUserGroupLibraries($userID) { $sql = "SELECT libraryID FROM groupUsers JOIN groups USING (groupID) WHERE userID=?"; $libraryIDs = Zotero_DB::columnQuery($sql, $userID); if (!$libraryIDs) { return array(); } return $libraryIDs; }
public function updated() { if (empty($_REQUEST['lastsync'])) { $this->error(400, 'NO_LAST_SYNC_TIME', 'Last sync time not provided'); } $lastsync = false; if (is_numeric($_REQUEST['lastsync'])) { $lastsync = (int) $_REQUEST['lastsync']; } else { $this->error(400, 'INVALID_LAST_SYNC_TIME', 'Last sync time is invalid'); } $this->sessionCheck(); if (isset($_SERVER['HTTP_X_ZOTERO_VERSION'])) { require_once '../model/ToolkitVersionComparator.inc.php'; if (ToolkitVersionComparator::compare($_SERVER['HTTP_X_ZOTERO_VERSION'], "2.0.4") < 0) { $futureUsers = Z_Core::$MC->get('futureUsers'); if (!$futureUsers) { $futureUsers = Zotero_DB::columnQuery("SELECT userID FROM futureUsers"); Z_Core::$MC->set('futureUsers', $futureUsers, 1800); } if (in_array($this->userID, $futureUsers)) { Z_Core::logError("Blocking sync for future user " . $this->userID . " with version " . $_SERVER['HTTP_X_ZOTERO_VERSION']); $upgradeMessage = "Due to improvements made to sync functionality, you must upgrade to Zotero 2.0.6 or later (via Firefox's Tools menu -> Add-ons -> Extensions -> Find Updates or from zotero.org) to continue syncing your Zotero library."; $this->error(400, 'UPGRADE_REQUIRED', $upgradeMessage); } } } $doc = new DOMDocument(); $domResponse = dom_import_simplexml($this->responseXML); $domResponse = $doc->importNode($domResponse, true); $doc->appendChild($domResponse); try { $result = Zotero_Sync::getSessionDownloadResult($this->sessionID); } catch (Exception $e) { $this->handleUpdatedError($e); } // XML response if (is_string($result)) { $this->clearWaitTime($this->sessionID); $this->responseXML = new SimpleXMLElement($result); $this->end(); } // Queued if ($result === false) { $queued = $this->responseXML->addChild('locked'); $queued['wait'] = $this->getWaitTime($this->sessionID); $this->end(); } // Not queued if ($result == -1) { // See if we're locked Zotero_DB::beginTransaction(); if (Zotero_Sync::userIsWriteLocked($this->userID) || !empty($_REQUEST['upload']) && Zotero_Sync::userIsReadLocked($this->userID)) { Zotero_DB::commit(); $locked = $this->responseXML->addChild('locked'); $locked['wait'] = $this->getWaitTime($this->sessionID); $this->end(); } Zotero_DB::commit(); $queue = true; if (Z_ENV_TESTING_SITE && !empty($_GET['noqueue'])) { $queue = false; } // TEMP $cacheKeyExtra = (!empty($_POST['ft']) ? json_encode($_POST['ft']) : "") . (!empty($_POST['ftkeys']) ? json_encode($_POST['ftkeys']) : ""); // If we have a cached response, return that try { $startedTimestamp = microtime(true); $cached = Zotero_Sync::getCachedDownload($this->userID, $lastsync, $this->apiVersion, $cacheKeyExtra); // Not locked, so clear wait index $this->clearWaitTime($this->sessionID); if ($cached) { $this->responseXML = simplexml_load_string($cached, "SimpleXMLElement", LIBXML_COMPACT | LIBXML_PARSEHUGE); // TEMP if (!$this->responseXML) { error_log("Invalid cached XML data -- stripping control characters"); // Strip control characters in XML data $cached = preg_replace('/[\\x00-\\x08\\x0B\\x0C\\x0E-\\x1F\\x7F]/', '', $cached); $this->responseXML = simplexml_load_string($cached, "SimpleXMLElement", LIBXML_COMPACT | LIBXML_PARSEHUGE); } $duration = round((double) microtime(true) - $startedTimestamp, 2); Zotero_Sync::logDownload($this->userID, round($lastsync), strlen($cached), $this->ipAddress ? $this->ipAddress : 0, 0, $duration, $duration, (int) (!$this->responseXML)); StatsD::increment("sync.process.download.cache.hit"); if (!$this->responseXML) { $msg = "Error parsing cached XML for user " . $this->userID; error_log($msg); $this->handleUpdatedError(new Exception($msg)); } $this->end(); } } catch (Exception $e) { $msg = $e->getMessage(); if (strpos($msg, "Too many connections") !== false) { $msg = "'Too many connections' from MySQL"; } else { $msg = "'{$msg}'"; } Z_Core::logError("Warning: {$msg} getting cached download"); StatsD::increment("sync.process.download.cache.error"); } try { $num = Zotero_Items::countUpdated($this->userID, $lastsync, 5); } catch (Exception $e) { // We can get a MySQL lock timeout here if the upload starts // after the write lock check above but before we get here $this->handleUpdatedError($e); } // If nothing updated, or if just a few objects and processing is enabled, process synchronously if ($num == 0 || $num < 5 && Z_CONFIG::$PROCESSORS_ENABLED) { $queue = false; } $params = []; if (isset($_POST['ft'])) { $params['ft'] = $_POST['ft']; } if (isset($_POST['ftkeys'])) { $queue = true; $params['ftkeys'] = $_POST['ftkeys']; } if ($queue) { Zotero_Sync::queueDownload($this->userID, $this->sessionID, $lastsync, $this->apiVersion, $num, $params); try { Zotero_Processors::notifyProcessors('download'); } catch (Exception $e) { Z_Core::logError($e); } $locked = $this->responseXML->addChild('locked'); $locked['wait'] = 1000; } else { try { Zotero_Sync::processDownload($this->userID, $lastsync, $doc, $params); $this->responseXML = simplexml_import_dom($doc); StatsD::increment("sync.process.download.immediate.success"); } catch (Exception $e) { StatsD::increment("sync.process.download.immediate.error"); $this->handleUpdatedError($e); } } $this->end(); } throw new Exception("Unexpected session result {$result}"); }
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; }
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 "; } if (!$includeTrashed) { $sql .= "AND DI.itemID IS NULL "; } // Search on title and creators if (!empty($params['q'])) { $sql .= "AND ("; $sql .= "IDT.value LIKE ? "; $sqlParams[] = '%' . $params['q'] . '%'; $sql .= "OR title LIKE ? "; $sqlParams[] = '%' . $params['q'] . '%'; $sql .= "OR TRIM(CONCAT(firstName, ' ', lastName)) LIKE ?"; $sqlParams[] = '%' . $params['q'] . '%'; $sql .= ") "; } // Search on itemType if (!empty($params['itemType'])) { $itemTypes = Zotero_API::getSearchParamValues($params, 'itemType'); if ($itemTypes) { if (sizeOf($itemTypes) > 1) { throw new Exception("Cannot specify 'itemType' more than once", Z_ERROR_INVALID_INPUT); } $itemTypes = $itemTypes[0]; $itemTypeIDs = array(); foreach ($itemTypes['values'] as $itemType) { $itemTypeID = Zotero_ItemTypes::getID($itemType); if (!$itemTypeID) { throw new Exception("Invalid itemType '{$itemType}'", Z_ERROR_INVALID_INPUT); } $itemTypeIDs[] = $itemTypeID; } $sql .= "AND I.itemTypeID " . ($itemTypes['negation'] ? "NOT " : "") . "IN (" . implode(',', array_fill(0, sizeOf($itemTypeIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $itemTypeIDs); } } // Tags // // ?tag=foo // ?tag=foo bar // phrase // ?tag=-foo // negation // ?tag=\-foo // literal hyphen (only for first character) // ?tag=foo&tag=bar // AND // ?tag=foo&tagType=0 // ?tag=foo bar || bar&tagType=0 $tagSets = Zotero_API::getSearchParamValues($params, 'tag'); if ($tagSets) { $sql2 = "SELECT itemID FROM items WHERE 1 "; $sqlParams2 = array(); if ($tagSets) { foreach ($tagSets as $set) { $positives = array(); $negatives = array(); $tagIDs = array(); foreach ($set['values'] as $tag) { $ids = Zotero_Tags::getIDs($libraryID, $tag); if (!$ids) { $ids = array(0); } $tagIDs = array_merge($tagIDs, $ids); } $tagIDs = array_unique($tagIDs); if ($set['negation']) { $negatives = array_merge($negatives, $tagIDs); } else { $positives = array_merge($positives, $tagIDs); } if ($positives) { $sql2 .= "AND itemID IN (SELECT itemID FROM items JOIN itemTags USING (itemID)\n\t\t\t\t\t\t\t\tWHERE tagID IN (" . implode(',', array_fill(0, sizeOf($positives), '?')) . ")) "; $sqlParams2 = array_merge($sqlParams2, $positives); } if ($negatives) { $sql2 .= "AND itemID NOT IN (SELECT itemID FROM items JOIN itemTags USING (itemID)\n\t\t\t\t\t\t\t\tWHERE tagID IN (" . implode(',', array_fill(0, sizeOf($negatives), '?')) . ")) "; $sqlParams2 = array_merge($sqlParams2, $negatives); } } } $tagItems = Zotero_DB::columnQuery($sql2, $sqlParams2, $shardID); // No matches if (!$tagItems) { return $results; } // Combine with passed keys if ($itemIDs) { $itemIDs = array_intersect($itemIDs, $tagItems); // None of the tag matches match the passed keys if (!$itemIDs) { return $results; } } else { $itemIDs = $tagItems; } } if ($itemIDs) { $sql .= "AND I.itemID IN (" . implode(', ', array_fill(0, sizeOf($itemIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $itemIDs); } if ($keys) { $sql .= "AND `key` IN (" . implode(', ', array_fill(0, sizeOf($keys), '?')) . ") "; $sqlParams = array_merge($sqlParams, $keys); } $sql .= "ORDER BY "; if (!empty($params['order'])) { switch ($params['order']) { case 'dateAdded': case 'dateModified': case 'serverDateModified': $orderSQL = "I." . $params['order']; break; case 'itemType': $orderSQL = "TITN.itemTypeName"; break; case 'title': $orderSQL = "IFNULL(COALESCE(sortTitle, IDT.value, INo.title), '')"; break; case 'creator': $orderSQL = "ISF.creatorSummary"; break; // TODO: generic base field mapping-aware sorting // TODO: generic base field mapping-aware sorting case 'date': $orderSQL = "IDD.value"; break; case 'addedBy': if ($isGroup && $createdByUserIDs) { $orderSQL = "TCBU.username"; } else { $orderSQL = "1"; } break; default: $fieldID = Zotero_ItemFields::getID($params['order']); if (!$fieldID) { throw new Exception("Invalid order field '" . $params['order'] . "'"); } $orderSQL = "(SELECT value FROM itemData WHERE itemID=I.itemID AND fieldID=?)"; if (!$params['emptyFirst']) { $sqlParams[] = $fieldID; } $sqlParams[] = $fieldID; } if (!empty($params['sort'])) { $dir = $params['sort']; } else { $dir = "ASC"; } if (!$params['emptyFirst']) { $sql .= "IFNULL({$orderSQL}, '') = '' {$dir}, "; } $sql .= $orderSQL; $sql .= " {$dir}, "; } $sql .= "I.itemID " . (!empty($params['sort']) ? $params['sort'] : "ASC") . " "; if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } $itemIDs = Zotero_DB::columnQuery($sql, $sqlParams, $shardID); $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID); if ($itemIDs) { if ($asKeys) { $results['keys'] = $itemIDs; } else { $results['items'] = Zotero_Items::get($libraryID, $itemIDs); } } if (!empty($deleteTempTable['tmpCreatedByUsers'])) { $sql = "DROP TEMPORARY TABLE IF EXISTS tmpCreatedByUsers{$rnd}"; Zotero_DB::query($sql, false, $shardID); } if (!empty($deleteTempTable['tmpItemTypeNames'])) { $sql = "DROP TEMPORARY TABLE IF EXISTS tmpItemTypeNames{$rnd}"; Zotero_DB::query($sql, false, $shardID); } return $results; }
protected function loadRelations($reload = false) { if ($this->loaded['relations'] && !$reload) return; if (!$this->id) { return; } Z_Core::debug("Loading relations for item $this->id"); $this->loadPrimaryData(false, true); $itemURI = Zotero_URI::getItemURI($this); $relations = Zotero_Relations::getByURIs($this->libraryID, $itemURI); $relations = array_map(function ($rel) { return [$rel->predicate, $rel->object]; }, $relations); // Related items are bidirectional, so include any with this item as the object $reverseRelations = Zotero_Relations::getByURIs( $this->libraryID, false, Zotero_Relations::$relatedItemPredicate, $itemURI ); foreach ($reverseRelations as $rel) { $r = [$rel->predicate, $rel->subject]; // Only add if not already added in other direction if (!in_array($r, $relations)) { $relations[] = $r; } } // Also include any owl:sameAs relations with this item as the object // (as sent by client via classic sync) $reverseRelations = Zotero_Relations::getByURIs( $this->libraryID, false, Zotero_Relations::$linkedObjectPredicate, $itemURI ); foreach ($reverseRelations as $rel) { $relations[] = [$rel->predicate, $rel->subject]; } // TEMP: Get old-style related items // // Add related items $sql = "SELECT `key` FROM itemRelated IR " . "JOIN items I ON (IR.linkedItemID=I.itemID) " . "WHERE IR.itemID=?"; $relatedItemKeys = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID)); if ($relatedItemKeys) { $prefix = Zotero_URI::getLibraryURI($this->libraryID) . "/items/"; $predicate = Zotero_Relations::$relatedItemPredicate; foreach ($relatedItemKeys as $key) { $relations[] = [$predicate, $prefix . $key]; } } // Reverse as well $sql = "SELECT `key` FROM itemRelated IR JOIN items I USING (itemID) WHERE IR.linkedItemID=?"; $reverseRelatedItemKeys = Zotero_DB::columnQuery( $sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID) ); if ($reverseRelatedItemKeys) { $prefix = Zotero_URI::getLibraryURI($this->libraryID) . "/items/"; $predicate = Zotero_Relations::$relatedItemPredicate; foreach ($reverseRelatedItemKeys as $key) { $relations[] = [$predicate, $prefix . $key]; } } $this->relations = $relations; $this->loaded['relations'] = true; $this->clearChanged('relations'); }
/** * Get items associated with a unique file on S3 */ public static function getFileItems($hash, $filename, $zip) { throw new Exception("Unimplemented"); // would need to work across shards $sql = "SELECT itemID FROM storageFiles JOIN storageFileItems USING (storageFileID)\n\t\t\t\tWHERE hash=? AND filename=? AND zip=?"; $itemIDs = Zotero_DB::columnQuery($sql, array($hash, $filename, (int) $zip)); if (!$itemIDs) { return array(); } return $itemIDs; }
public static function search($libraryID, $onlyTopLevel = false, $params = array(), $includeTrashed = false, Zotero_Permissions $permissions = null) { $rnd = "_" . uniqid($libraryID . "_"); $results = array('results' => array(), 'total' => 0); // Default empty library if ($libraryID === 0) { return $results; } $shardID = Zotero_Shards::getByLibraryID($libraryID); $includeNotes = true; if ($permissions && !$permissions->canAccess($libraryID, 'notes')) { $includeNotes = false; } // Pass a list of itemIDs, for when the initial search is done via SQL $itemIDs = !empty($params['itemIDs']) ? $params['itemIDs'] : array(); $itemKeys = $params['itemKey']; $titleSort = !empty($params['sort']) && $params['sort'] == 'title'; $parentItemSort = !empty($params['sort']) && in_array($params['sort'], ['itemType', 'dateAdded', 'dateModified', 'serverDateModified', 'addedBy']); $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT "; // In /top mode, use the parent item's values for most joins if ($onlyTopLevel) { $itemIDSelector = "COALESCE(IA.sourceItemID, INo.sourceItemID, I.itemID)"; $itemKeySelector = "COALESCE(IP.key, I.key)"; $itemVersionSelector = "COALESCE(IP.version, I.version)"; $itemTypeIDSelector = "COALESCE(IP.itemTypeID, I.itemTypeID)"; } else { $itemIDSelector = "I.itemID"; $itemKeySelector = "I.key"; $itemVersionSelector = "I.version"; $itemTypeIDSelector = "I.itemTypeID"; } if ($params['format'] == 'keys' || $params['format'] == 'versions') { // In /top mode, display the parent item of matching items $sql .= "{$itemKeySelector} AS `key`"; if ($params['format'] == 'versions') { $sql .= ", {$itemVersionSelector} AS version"; } } else { $sql .= "{$itemIDSelector} AS itemID"; } $sql .= " FROM items I "; $sqlParams = array($libraryID); // For /top, we need the parent itemID if ($onlyTopLevel) { $sql .= "LEFT JOIN itemAttachments IA ON (IA.itemID=I.itemID) "; } // For /top, we need the parent itemID; for 'q' we need the note; for sorting by title, // we need the note title if ($onlyTopLevel || !empty($params['q']) || $titleSort) { $sql .= "LEFT JOIN itemNotes INo ON (INo.itemID=I.itemID) "; } // For some /top requests, pull in the parent item's items row if ($onlyTopLevel && ($params['format'] == 'keys' || $params['format'] == 'versions' || $parentItemSort)) { $sql .= "LEFT JOIN items IP ON ({$itemIDSelector}=IP.itemID) "; } // Pull in titles 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) . ")) "; } // When sorting by title in /top mode, we need the title of the parent item if ($onlyTopLevel && $titleSort) { $titleSortDataTable = "IDTSort"; $titleSortNoteTable = "INoSort"; $sql .= "LEFT JOIN itemData IDTSort ON (IDTSort.itemID={$itemIDSelector} AND " . "IDTSort.fieldID IN (" . implode(',', $titleFieldIDs) . ")) " . "LEFT JOIN itemNotes INoSort ON (INoSort.itemID={$itemIDSelector}) "; } else { $titleSortDataTable = "IDT"; $titleSortNoteTable = "INo"; } if (!empty($params['q'])) { // Pull in creators $sql .= "LEFT JOIN itemCreators IC ON (IC.itemID=I.itemID) " . "LEFT JOIN creators C ON (C.creatorID=IC.creatorID) "; // Pull in dates $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) . ")) "; } if ($includeTrashed) { if (!empty($params['trashedItemsOnly'])) { $sql .= "JOIN deletedItems DI ON (DI.itemID=I.itemID) "; } } else { $sql .= "LEFT JOIN deletedItems DI ON (DI.itemID=I.itemID) "; // In /top mode, we don't want to show results for deleted parents or children if ($onlyTopLevel) { $sql .= "LEFT JOIN deletedItems DIP ON (DIP.itemID={$itemIDSelector}) "; } } if (!empty($params['sort'])) { switch ($params['sort']) { case 'title': case 'creator': $sql .= "LEFT JOIN itemSortFields ISF ON (ISF.itemID={$itemIDSelector}) "; break; case 'date': // When sorting by date in /top mode, we need the date of the parent item if ($onlyTopLevel) { $sortTable = "IDDSort"; // Pull in dates $dateFieldIDs = array_merge(array(Zotero_ItemFields::getID('date')), Zotero_ItemFields::getTypeFieldsFromBase('date')); $sql .= "LEFT JOIN itemData IDDSort ON (IDDSort.itemID={$itemIDSelector} AND " . "IDDSort.fieldID IN (" . implode(',', $dateFieldIDs) . ")) "; } else { $sortTable = "IDD"; if (empty($params['q'])) { $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': $locale = 'en-US'; $types = Zotero_ItemTypes::getAll($locale); // TEMP: get localized string // DEBUG: Why is attachment skipped in getAll()? $types[] = array('id' => 14, 'localized' => 'Attachment'); foreach ($types as $type) { $sql2 = "INSERT IGNORE INTO tmpItemTypeNames VALUES (?, ?, ?)"; Zotero_DB::query($sql2, array($type['id'], $locale, $type['localized']), $shardID); } // Join temp table to query $sql .= "JOIN tmpItemTypeNames TITN ON (TITN.itemTypeID={$itemTypeIDSelector}) "; break; case 'addedBy': $isGroup = Zotero_Libraries::getType($libraryID) == 'group'; if ($isGroup) { $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 VALUES "; Zotero_DB::bulkInsert($sql2, $toAdd, 50, false, $shardID); // Join temp table to query $sql .= "LEFT JOIN groupItems GI ON (GI.itemID=I.itemID)\n\t\t\t\t\t\t\t\t\tLEFT JOIN tmpCreatedByUsers TCBU ON (TCBU.userID=GI.createdByUserID) "; } } break; } } $sql .= "WHERE I.libraryID=? "; if (!$includeTrashed) { $sql .= "AND DI.itemID IS NULL "; // Hide deleted parents in /top mode if ($onlyTopLevel) { $sql .= "AND DIP.itemID IS NULL "; } } // Search on title, creators, and dates if (!empty($params['q'])) { $sql .= "AND ("; $sql .= "IDT.value LIKE ? "; $sqlParams[] = '%' . $params['q'] . '%'; $sql .= "OR INo.title LIKE ? "; $sqlParams[] = '%' . $params['q'] . '%'; $sql .= "OR TRIM(CONCAT(firstName, ' ', lastName)) LIKE ? "; $sqlParams[] = '%' . $params['q'] . '%'; $sql .= "OR SUBSTR(IDD.value, 1, 4) = ?"; $sqlParams[] = $params['q']; // Full-text search if ($params['qmode'] == 'everything') { $ftKeys = Zotero_FullText::searchInLibrary($libraryID, $params['q']); if ($ftKeys) { $sql .= " OR I.key IN (" . implode(', ', array_fill(0, sizeOf($ftKeys), '?')) . ") "; $sqlParams = array_merge($sqlParams, $ftKeys); } } $sql .= ") "; } // Search on itemType if (!empty($params['itemType'])) { $itemTypes = Zotero_API::getSearchParamValues($params, 'itemType'); if ($itemTypes) { if (sizeOf($itemTypes) > 1) { throw new Exception("Cannot specify 'itemType' more than once", Z_ERROR_INVALID_INPUT); } $itemTypes = $itemTypes[0]; $itemTypeIDs = array(); foreach ($itemTypes['values'] as $itemType) { $itemTypeID = Zotero_ItemTypes::getID($itemType); if (!$itemTypeID) { throw new Exception("Invalid itemType '{$itemType}'", Z_ERROR_INVALID_INPUT); } $itemTypeIDs[] = $itemTypeID; } $sql .= "AND I.itemTypeID " . ($itemTypes['negation'] ? "NOT " : "") . "IN (" . implode(',', array_fill(0, sizeOf($itemTypeIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $itemTypeIDs); } } if (!$includeNotes) { $sql .= "AND I.itemTypeID != 1 "; } if (!empty($params['since'])) { $sql .= "AND {$itemVersionSelector} > ? "; $sqlParams[] = $params['since']; } // TEMP: for sync transition if (!empty($params['sincetime']) && $params['sincetime'] != 1) { $sql .= "AND I.serverDateModified >= FROM_UNIXTIME(?) "; $sqlParams[] = $params['sincetime']; } // Tags // // ?tag=foo // ?tag=foo bar // phrase // ?tag=-foo // negation // ?tag=\-foo // literal hyphen (only for first character) // ?tag=foo&tag=bar // AND $tagSets = Zotero_API::getSearchParamValues($params, 'tag'); if ($tagSets) { $sql2 = "SELECT itemID FROM items WHERE libraryID=?\n"; $sqlParams2 = array($libraryID); $positives = array(); $negatives = array(); foreach ($tagSets as $set) { $tagIDs = array(); foreach ($set['values'] as $tag) { $ids = Zotero_Tags::getIDs($libraryID, $tag, true); if (!$ids) { $ids = array(0); } $tagIDs = array_merge($tagIDs, $ids); } $tagIDs = array_unique($tagIDs); $tmpSQL = "SELECT itemID FROM items JOIN itemTags USING (itemID) " . "WHERE tagID IN (" . implode(',', array_fill(0, sizeOf($tagIDs), '?')) . ")"; $ids = Zotero_DB::columnQuery($tmpSQL, $tagIDs, $shardID); if (!$ids) { // If no negative tags, skip this tag set if ($set['negation']) { continue; } // If no positive tags, return no matches return $results; } $ids = $ids ? $ids : array(); $sql2 .= " AND itemID " . ($set['negation'] ? "NOT " : "") . " IN (" . implode(',', array_fill(0, sizeOf($ids), '?')) . ")"; $sqlParams2 = array_merge($sqlParams2, $ids); } $tagItems = Zotero_DB::columnQuery($sql2, $sqlParams2, $shardID); // No matches if (!$tagItems) { return $results; } // Combine with passed ids if ($itemIDs) { $itemIDs = array_intersect($itemIDs, $tagItems); // None of the tag matches match the passed ids if (!$itemIDs) { return $results; } } else { $itemIDs = $tagItems; } } if ($itemIDs) { $sql .= "AND I.itemID IN (" . implode(', ', array_fill(0, sizeOf($itemIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $itemIDs); } if ($itemKeys) { $sql .= "AND I.key IN (" . implode(', ', array_fill(0, sizeOf($itemKeys), '?')) . ") "; $sqlParams = array_merge($sqlParams, $itemKeys); } $sql .= "ORDER BY "; if (!empty($params['sort'])) { switch ($params['sort']) { case 'dateAdded': case 'dateModified': case 'serverDateModified': if ($onlyTopLevel) { $orderSQL = "IP." . $params['sort']; } else { $orderSQL = "I." . $params['sort']; } break; case 'itemType': $orderSQL = "TITN.itemTypeName"; /* // Optional method for sorting by localized item type name, which would avoid // the INSERT and JOIN above and allow these requests to use DB read replicas $locale = 'en-US'; $types = Zotero_ItemTypes::getAll($locale); // TEMP: get localized string // DEBUG: Why is attachment skipped in getAll()? $types[] = [ 'id' => 14, 'localized' => 'Attachment' ]; usort($types, function ($a, $b) { return strcasecmp($a['localized'], $b['localized']); }); // Pass order of localized item type names for sorting // e.g., FIELD(14, 12, 14, 26...) for sorting "Attachment" after "Artwork" $orderSQL = "FIELD($itemTypeIDSelector, " . implode(", ", array_map(function ($x) { return $x['id']; }, $types)) . ")"; // If itemTypeID isn't found in passed list (currently only for NSF Reviewer), // sort last $orderSQL = "IFNULL(NULLIF($orderSQL, 0), 99999)"; // All items have types, so no need to check for empty sort values $params['emptyFirst'] = true; */ break; case 'title': $orderSQL = "IFNULL(COALESCE(sortTitle, {$titleSortDataTable}.value, {$titleSortNoteTable}.title), '')"; break; case 'creator': $orderSQL = "ISF.creatorSummary"; break; // TODO: generic base field mapping-aware sorting // TODO: generic base field mapping-aware sorting case 'date': $orderSQL = "{$sortTable}.value"; break; case 'addedBy': if ($isGroup && $createdByUserIDs) { $orderSQL = "TCBU.username"; } else { $orderSQL = ($onlyTopLevel ? "IP" : "I") . ".dateAdded"; } break; case 'itemKeyList': $orderSQL = "FIELD(I.key," . implode(',', array_fill(0, sizeOf($itemKeys), '?')) . ")"; $sqlParams = array_merge($sqlParams, $itemKeys); break; default: $fieldID = Zotero_ItemFields::getID($params['sort']); if (!$fieldID) { throw new Exception("Invalid order field '" . $params['sort'] . "'"); } $orderSQL = "(SELECT value FROM itemData WHERE itemID=I.itemID AND fieldID=?)"; if (!$params['emptyFirst']) { $sqlParams[] = $fieldID; } $sqlParams[] = $fieldID; } if (!empty($params['direction'])) { $dir = $params['direction']; } else { $dir = "ASC"; } if (!$params['emptyFirst']) { $sql .= "IFNULL({$orderSQL}, '') = '' {$dir}, "; } $sql .= $orderSQL . " {$dir}, "; } $sql .= "I.version " . (!empty($params['direction']) ? $params['direction'] : "ASC") . ", I.itemID " . (!empty($params['direction']) ? $params['direction'] : "ASC") . " "; if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } // Log SQL statement with embedded parameters /*if (true || !empty($_GET['sqldebug'])) { error_log($onlyTopLevel); $debugSQL = ""; $parts = explode("?", $sql); $debugSQLParams = $sqlParams; foreach ($parts as $part) { $val = array_shift($debugSQLParams); $debugSQL .= $part; if (!is_null($val)) { $debugSQL .= is_int($val) ? $val : '"' . $val . '"'; } } error_log($debugSQL . ";"); }*/ if ($params['format'] == 'versions') { $rows = Zotero_DB::query($sql, $sqlParams, $shardID); } else { $rows = Zotero_DB::columnQuery($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 { $results['results'] = Zotero_Items::get($libraryID, $rows); } } } return $results; }
public function testPreparedStatement() { Zotero_DB::query("CREATE TABLE test (foo INTEGER NULL, foo2 INTEGER NULL DEFAULT NULL)"); $stmt = Zotero_DB::getStatement("INSERT INTO test (foo) VALUES (?)"); $stmt->execute(array(1)); $stmt->execute(array(2)); $result = Zotero_DB::columnQuery("SELECT foo FROM test"); $this->assertEquals($result[0], 1); $this->assertEquals($result[1], 2); }