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);
}
