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 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 function removestoragefiles()
{
$this->allowMethods(array('POST'));
$sql = "DELETE SFI FROM storageFileItems SFI JOIN items USING (itemID) WHERE libraryID=?";
Zotero_DB::query($sql, $this->objectLibraryID, Zotero_Shards::getByLibraryID($this->objectLibraryID));
header("HTTP/1.1 204 No Content");
exit;
}
public static function 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 getUserUsage($userID)
{
$usage = array();
$libraryID = Zotero_Users::getLibraryIDFromUserID($userID);
$sql = "SELECT SUM(size) AS bytes FROM storageFileItems\n\t\t\t\tJOIN items USING (itemID) WHERE libraryID=?";
$libraryBytes = Zotero_DB::valueQuery($sql, $libraryID, Zotero_Shards::getByLibraryID($libraryID));
$usage['library'] = round($libraryBytes / 1024 / 1024, 1);
$groupBytes = 0;
$usage['groups'] = array();
$ownedLibraries = Zotero_Groups::getUserOwnedGroupLibraries($userID);
if ($ownedLibraries) {
$shardIDs = Zotero_Groups::getUserGroupShards($userID);
foreach ($shardIDs as $shardID) {
$sql = "SELECT libraryID, SUM(size) AS `bytes` FROM storageFileItems\n\t\t\t\t\t\tJOIN items I USING (itemID)\n\t\t\t\t\t\tWHERE libraryID IN\n\t\t\t\t\t\t(" . implode(', ', array_fill(0, sizeOf($ownedLibraries), '?')) . ")\n\t\t\t\t\t\tGROUP BY libraryID WITH ROLLUP";
$libraries = Zotero_DB::query($sql, $ownedLibraries, $shardID);
if ($libraries) {
foreach ($libraries as $library) {
if ($library['libraryID']) {
$usage['groups'][] = array('id' => Zotero_Groups::getGroupIDFromLibraryID($library['libraryID']), 'usage' => round($library['bytes'] / 1024 / 1024, 1));
} else {
$groupBytes += $library['bytes'];
}
}
}
}
}
$usage['total'] = round(($libraryBytes + $groupBytes) / 1024 / 1024, 1);
return $usage;
}
private static function getDeletedObjectIDs($userID, $timestamp, $includeAllUserObjects = false)
{
/*
$sql = "SELECT version FROM version WHERE schema='syncdeletelog'";
$syncLogStart = Zotero_DB::valueQuery($sql);
if (!$syncLogStart) {
throw ('Sync log start time not found');
}
*/
/*
// Last sync time is before start of log
if ($lastSyncDate && new Date($syncLogStart * 1000) > $lastSyncDate) {
return -1;
}
*/
// Personal library
$shardID = Zotero_Shards::getByUserID($userID);
$libraryID = Zotero_Users::getLibraryIDFromUserID($userID);
$shardLibraryIDs[$shardID] = array($libraryID);
// Group libraries
if ($includeAllUserObjects) {
$groupIDs = Zotero_Groups::getUserGroups($userID);
if ($groupIDs) {
// Separate groups into shards for querying
foreach ($groupIDs as $groupID) {
$libraryID = Zotero_Groups::getLibraryIDFromGroupID($groupID);
$shardID = Zotero_Shards::getByLibraryID($libraryID);
if (!isset($shardLibraryIDs[$shardID])) {
$shardLibraryIDs[$shardID] = array();
}
$shardLibraryIDs[$shardID][] = $libraryID;
}
}
}
// Send query at each shard
$rows = array();
foreach ($shardLibraryIDs as $shardID => $libraryIDs) {
$sql = "SELECT libraryID, objectType, id, timestamp\n\t\t\t\t\tFROM syncDeleteLogIDs WHERE libraryID IN (" . implode(', ', array_fill(0, sizeOf($libraryIDs), '?')) . ")";
$params = $libraryIDs;
if ($timestamp) {
// Send any entries from before these were being properly sent
if ($timestamp < 1260778500) {
$sql .= " AND (timestamp >= FROM_UNIXTIME(?) OR timestamp BETWEEN 1257968068 AND FROM_UNIXTIME(?))";
$params[] = $timestamp;
$params[] = 1260778500;
} else {
$sql .= " AND timestamp >= FROM_UNIXTIME(?)";
$params[] = $timestamp;
}
}
$sql .= " ORDER BY timestamp";
$shardRows = Zotero_DB::query($sql, $params, $shardID);
if ($shardRows) {
$rows = array_merge($rows, $shardRows);
}
}
if (!$rows) {
return false;
}
$deletedIDs = array('groups' => array());
foreach ($rows as $row) {
$type = $row['objectType'] . 's';
$deletedIDs[$type][] = $row['id'];
}
return $deletedIDs;
}
private function load()
{
//Z_Core::debug("Loading data for search $this->id");
if (!$this->libraryID) {
throw new Exception("Library ID not set");
}
if (!$this->id && !$this->key) {
throw new Exception("ID or key not set");
}
$shardID = Zotero_Shards::getByLibraryID($this->libraryID);
$sql = "SELECT searchID AS id, searchName AS name, dateAdded, dateModified, libraryID, `key`,\n\t\t\t\tMAX(searchConditionID) AS maxSearchConditionID FROM savedSearches\n\t\t\t\tLEFT JOIN savedSearchConditions USING (searchID) WHERE ";
if ($this->id) {
$sql .= "searchID=?";
$params = $this->id;
} else {
$sql .= "libraryID=? AND `key`=?";
$params = array($this->libraryID, $this->key);
}
$sql .= " GROUP BY searchID";
$data = Zotero_DB::rowQuery($sql, $params, $shardID);
$this->loaded = true;
if (!$data) {
return;
}
foreach ($data as $key => $val) {
$this->{$key} = $val;
}
$sql = "SELECT * FROM savedSearchConditions\n\t\t\t\tWHERE searchID=? ORDER BY searchConditionID";
$conditions = Zotero_DB::query($sql, $this->id, $shardID);
foreach ($conditions as $condition) {
/*
if (!Zotero.SearchConditions.get(condition)){
Zotero.debug("Invalid saved search condition '"
+ condition + "' -- skipping", 2);
continue;
}
*/
$searchConditionID = $condition['searchConditionID'];
$this->conditions[$searchConditionID] = array('id' => $searchConditionID, 'condition' => $condition['condition'], 'mode' => $condition['mode'], 'operator' => $condition['operator'], 'value' => $condition['value'], 'required' => $condition['required']);
}
}
public static function deleteUser($userID)
{
if (empty($userID)) {
throw new Exception("userID not provided");
}
$username = Zotero_Users::getUsername($userID, true);
$sql = "SELECT LUM_Role.Name FROM LUM_User JOIN LUM_Role USING (RoleID) WHERE UserID=?";
try {
$role = Zotero_WWW_DB_2::valueQuery($sql, $userID);
} catch (Exception $e) {
Z_Core::logError("WARNING: {$e} -- retrying on primary");
$role = Zotero_WWW_DB_1::valueQuery($sql, $userID);
}
if ($role != 'Deleted') {
throw new Exception("User '{$username}' does not have role 'Deleted'");
}
Zotero_DB::beginTransaction();
if (Zotero_Groups::getUserOwnedGroups($userID)) {
throw new Exception("Cannot delete user '{$username}' with owned groups");
}
// Remove user from any groups they're a member of
//
// This isn't strictly necessary thanks to foreign key cascades,
// but it removes some extra keyPermissions rows
$groupIDs = Zotero_Groups::getUserGroups($userID);
foreach ($groupIDs as $groupID) {
$group = Zotero_Groups::get($groupID, true);
$group->removeUser($userID);
}
// Remove all data
Zotero_Users::clearAllData($userID);
// Remove user publications library
$libraryID = self::getLibraryIDFromUserID($userID, 'publications');
if ($libraryID) {
$shardID = Zotero_Shards::getByLibraryID($libraryID);
Zotero_DB::query("DELETE FROM shardLibraries WHERE libraryID=?", $libraryID, $shardID);
Zotero_DB::query("DELETE FROM libraries WHERE libraryID=?", $libraryID);
}
// Remove user/library rows
$libraryID = self::getLibraryIDFromUserID($userID);
$shardID = Zotero_Shards::getByLibraryID($libraryID);
Zotero_DB::query("DELETE FROM shardLibraries WHERE libraryID=?", $libraryID, $shardID);
Zotero_DB::query("DELETE FROM libraries WHERE libraryID=?", $libraryID);
Zotero_DB::commit();
}
public static function loadHashes($libraryID)
{
$sql = "SELECT itemID, hash FROM itemNotes JOIN items USING (itemID) WHERE libraryID=?";
$hashes = Zotero_DB::query($sql, $libraryID, Zotero_Shards::getByLibraryID($libraryID));
if (!$hashes) {
return;
}
if (!isset(self::$hashCache[$libraryID])) {
self::$hashCache[$libraryID] = array();
}
foreach ($hashes as $hash) {
if ($hash['hash']) {
self::$hashCache[$libraryID][$hash['itemID']] = $hash['hash'];
}
}
}
public function logTotalRequestTime()
{
if (!Z_CONFIG::$STATSD_ENABLED) {
return;
}
try {
if (!empty($this->objectLibraryID)) {
$shardID = Zotero_Shards::getByLibraryID($this->objectLibraryID);
$shardInfo = Zotero_Shards::getShardInfo($shardID);
$shardHostID = (int) $shardInfo['shardHostID'];
StatsD::timing("api.request.total_by_shard.{$shardHostID}", (microtime(true) - $this->startTime) * 1000, 0.25);
}
} catch (Exception $e) {
error_log("WARNING: " . $e);
}
StatsD::timing("api.memcached", Z_Core::$MC->requestTime * 1000, 0.25);
StatsD::timing("api.request.total", (microtime(true) - $this->startTime) * 1000, 0.25);
}
private function loadRelatedItems()
{
if (!$this->id) {
return;
}
Z_Core::debug("Loading related items for item {$this->id}");
if ($this->loaded['relatedItems']) {
trigger_error("Related items for item {$this->id} already loaded", E_USER_ERROR);
}
if (!$this->loaded['primaryData']) {
$this->loadPrimaryData(true);
}
// TODO: use a prepared statement
if (!is_numeric($this->id)) {
trigger_error("Invalid itemID '{$this->id}'", E_USER_ERROR);
}
$cacheKey = $this->getCacheKey("itemRelated");
//$ids = Z_Core::$MC->get($cacheKey);
$ids = false;
if ($ids === false) {
$sql = "SELECT linkedItemID FROM itemRelated WHERE itemID=?";
$stmt = Zotero_DB::getStatement($sql, true, Zotero_Shards::getByLibraryID($this->libraryID));
$ids = Zotero_DB::columnQueryFromStatement($stmt, $this->id);
Z_Core::$MC->set($cacheKey, $ids ? $ids : array());
}
$this->relatedItems = $ids ? $ids : array();
$this->loaded['relatedItems'] = true;
}
public function save()
{
if (!$this->libraryID) {
trigger_error("Library ID must be set before saving", E_USER_ERROR);
}
Zotero_Creators::editCheck($this);
// If empty, move on
if ($this->firstName === '' && $this->lastName === '') {
throw new Exception('First and last name are empty');
}
if ($this->fieldMode == 1 && $this->firstName !== '') {
throw new Exception('First name must be empty in single-field mode');
}
if (!$this->hasChanged()) {
Z_Core::debug("Creator {$this->id} has not changed");
return false;
}
Zotero_DB::beginTransaction();
try {
$creatorID = $this->id ? $this->id : Zotero_ID::get('creators');
$isNew = !$this->id;
Z_Core::debug("Saving creator {$this->id}");
$key = $this->key ? $this->key : $this->generateKey();
$timestamp = Zotero_DB::getTransactionTimestamp();
$dateAdded = $this->dateAdded ? $this->dateAdded : $timestamp;
$dateModified = $this->changed['dateModified'] ? $this->dateModified : $timestamp;
$fields = "firstName=?, lastName=?, fieldMode=?,\n\t\t\t\t\t\tlibraryID=?, `key`=?, dateAdded=?, dateModified=?, serverDateModified=?";
$params = array($this->firstName, $this->lastName, $this->fieldMode, $this->libraryID, $key, $dateAdded, $dateModified, $timestamp);
$shardID = Zotero_Shards::getByLibraryID($this->libraryID);
try {
if ($isNew) {
$sql = "INSERT INTO creators SET creatorID=?, {$fields}";
$stmt = Zotero_DB::getStatement($sql, true, $shardID);
Zotero_DB::queryFromStatement($stmt, array_merge(array($creatorID), $params));
// Remove from delete log if it's there
$sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='creator' AND `key`=?";
Zotero_DB::query($sql, array($this->libraryID, $key), $shardID);
} else {
$sql = "UPDATE creators SET {$fields} WHERE creatorID=?";
$stmt = Zotero_DB::getStatement($sql, true, $shardID);
Zotero_DB::queryFromStatement($stmt, array_merge($params, array($creatorID)));
}
} catch (Exception $e) {
if (strpos($e->getMessage(), " too long") !== false) {
if (strlen($this->firstName) > 255) {
throw new Exception("=First name '" . mb_substr($this->firstName, 0, 50) . "…' too long");
}
if (strlen($this->lastName) > 255) {
if ($this->fieldMode == 1) {
throw new Exception("=Last name '" . mb_substr($this->lastName, 0, 50) . "…' too long");
} else {
throw new Exception("=Name '" . mb_substr($this->lastName, 0, 50) . "…' too long");
}
}
}
throw $e;
}
// The client updates the mod time of associated items here, but
// we don't, because either A) this is from syncing, where appropriate
// mod times come from the client or B) the change is made through
// $item->setCreator(), which updates the mod time.
//
// If the server started to make other independent creator changes,
// linked items would need to be updated.
Zotero_DB::commit();
Zotero_Creators::cachePrimaryData(array('id' => $creatorID, 'libraryID' => $this->libraryID, 'key' => $key, 'dateAdded' => $dateAdded, 'dateModified' => $dateModified, 'firstName' => $this->firstName, 'lastName' => $this->lastName, 'fieldMode' => $this->fieldMode));
} catch (Exception $e) {
Zotero_DB::rollback();
throw $e;
}
// If successful, set values in object
if (!$this->id) {
$this->id = $creatorID;
}
if (!$this->key) {
$this->key = $key;
}
$this->init();
if ($isNew) {
Zotero_Creators::cache($this);
Zotero_Creators::cacheLibraryKeyID($this->libraryID, $key, $creatorID);
}
// TODO: invalidate memcache?
return $this->id;
}
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');
}
private static function loadItems($libraryID, $itemIDs = array())
{
$shardID = Zotero_Shards::getByLibraryID($libraryID);
$sql = 'SELECT I.*,
(SELECT COUNT(*) FROM itemNotes INo
WHERE sourceItemID=I.itemID AND INo.itemID NOT IN
(SELECT itemID FROM deletedItems)) AS numNotes,
(SELECT COUNT(*) FROM itemAttachments IA
WHERE sourceItemID=I.itemID AND IA.itemID NOT IN
(SELECT itemID FROM deletedItems)) AS numAttachments
FROM items I WHERE 1';
// TODO: optimize
if ($itemIDs) {
foreach ($itemIDs as $itemID) {
if (!is_int($itemID)) {
throw new Exception("Invalid itemID {$itemID}");
}
}
$sql .= ' AND I.itemID IN (' . implode(',', array_fill(0, sizeOf($itemIDs), '?')) . ')';
}
$stmt = Zotero_DB::getStatement($sql, "loadItems_" . sizeOf($itemIDs), $shardID);
$itemRows = Zotero_DB::queryFromStatement($stmt, $itemIDs);
$loadedItemIDs = array();
if ($itemRows) {
foreach ($itemRows as $row) {
if ($row['libraryID'] != $libraryID) {
throw new Exception("Item {$itemID} isn't in library {$libraryID}", Z_ERROR_OBJECT_LIBRARY_MISMATCH);
}
$itemID = $row['itemID'];
$loadedItemIDs[] = $itemID;
// Item isn't loaded -- create new object and stuff in array
if (!isset(self::$itemsByID[$itemID])) {
$item = new Zotero_Item();
$item->loadFromRow($row, true);
self::$itemsByID[$itemID] = $item;
} else {
self::$itemsByID[$itemID]->loadFromRow($row, true);
}
}
}
if (!$itemIDs) {
// If loading all items, remove old items that no longer exist
$ids = array_keys(self::$itemsByID);
foreach ($ids as $id) {
if (!in_array($id, $loadedItemIDs)) {
throw new Exception("Unimplemented");
//$this->unload($id);
}
}
/*
_cachedFields = ['itemID', 'itemTypeID', 'dateAdded', 'dateModified',
'numNotes', 'numAttachments', 'numChildren'];
*/
//this._reloadCache = false;
}
}
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;
}
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 purge($libraryID)
{
$sql = "SELECT subject FROM relations " . "WHERE libraryID=? AND predicate!=? " . "UNION " . "SELECT object FROM relations " . "WHERE libraryID=? AND predicate!=?";
$uris = Zotero . DB . columnQuery($sql, array($libraryID, self::$deletedItemPredicate, $libraryID, self::$deletedItemPredicate), Zotero_Shards::getByLibraryID($libraryID));
if ($uris) {
$prefix = Zotero_URI::getBaseURI();
Zotero_DB::beginTransaction();
foreach ($uris as $uri) {
// Skip URIs that don't begin with the default prefix,
// since they don't correspond to local items
if (strpos($uri, $prefix) === false) {
continue;
}
if (preg_match('/\\/items\\//', $uri) && !Zotero_URI::getURIItem($uri)) {
self::eraseByURI($uri);
}
if (preg_match('/\\/collections\\//', $uri) && !Zotero_URI::getURICollection($uri)) {
self::eraseByURI($uri);
}
}
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;
}
public static function deleteByLibraryMySQL($libraryID)
{
$sql = "DELETE IFT FROM itemFulltext IFT JOIN items USING (itemID) WHERE libraryID=?";
Zotero_DB::query($sql, $libraryID, Zotero_Shards::getByLibraryID($libraryID));
}
private static function load($libraryID, $ids = [], array $options = [])
{
$loaded = [];
if (!$libraryID) {
throw new Exception("libraryID must be provided");
}
if ($libraryID !== false && !empty(self::$loadedLibraries[$libraryID])) {
return $loaded;
}
$sql = self::getPrimaryDataSQL() . ' AND O.libraryID=?';
$params = [$libraryID];
if ($ids) {
$sql .= ' AND O.' . self::$idColumn . ' IN (' . implode(',', $ids) . ')';
}
$t = microtime();
$rows = Zotero_DB::query($sql, $params, Zotero_Shards::getByLibraryID($libraryID));
foreach ($rows as $row) {
$id = $row['id'];
// Existing object -- reload in place
if (isset(self::$objectCache[$id])) {
self::$objectCache[$id]->loadFromRow($row, true);
$obj = self::$objectCache[$id];
} else {
$class = "Zotero_" . self::$ObjectType;
$obj = new $class();
$obj->loadFromRow($row, true);
if (!$options || !$options->noCache) {
self::registerObject($obj);
}
}
$loaded[$id] = $obj;
}
Z_Core::debug("Loaded " . self::$objectTypePlural . " in " . (microtime() - $t) . "ms");
if (!$ids) {
self::$loadedLibraries[$libraryID] = true;
// If loading all objects, remove cached objects that no longer exist
foreach (self::$objectCache as $obj) {
if ($libraryID !== false && obj . libraryID !== libraryID) {
continue;
}
if (empty($loaded[$obj->id])) {
self::unload($obj->id);
}
}
}
return $loaded;
}
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 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;
}
private function loadLinkedItems()
{
Z_Core::debug("Loading linked items for tag {$this->id}");
if (!$this->id && !$this->key) {
$this->linkedItemsLoaded = true;
return;
}
if (!$this->loaded) {
$this->load();
}
if (!$this->id) {
$this->linkedItemsLoaded = true;
return;
}
$sql = "SELECT itemID FROM itemTags WHERE tagID=?";
$stmt = Zotero_DB::getStatement($sql, true, Zotero_Shards::getByLibraryID($this->libraryID));
$ids = Zotero_DB::columnQueryFromStatement($stmt, $this->id);
$this->linkedItems = array();
if ($ids) {
$this->linkedItems = Zotero_Items::get($this->libraryID, $ids);
}
$this->linkedItemsLoaded = true;
}
private function load($allowFail = false)
{
if (!$this->libraryID) {
throw new Exception("Library ID not set");
}
if (!$this->id) {
throw new Exception("ID not set");
}
//Z_Core::debug("Loading data for relation $this->id");
$sql = "SELECT * FROM relations WHERE relationID=?";
$data = Zotero_DB::rowQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID));
$this->loaded = true;
if (!$data) {
return;
}
foreach ($data as $key => $val) {
// TEMP
if ($key == 'serverDateModifiedMS') {
continue;
}
if ($key == 'relationID') {
$this->id = $val;
continue;
}
$this->{$key} = $val;
}
}
public static function delete($libraryID, $key, $updateLibrary = false)
{
$table = static::field('table');
$id = static::field('id');
$type = static::field('object');
$types = static::field('objects');
if (!$key) {
throw new Exception("Invalid key {$key}");
}
// Get object (and trigger caching)
$obj = static::getByLibraryAndKey($libraryID, $key);
if (!$obj) {
return;
}
static::editCheck($obj);
Z_Core::debug("Deleting {$type} {$libraryID}/{$key}", 4);
$shardID = Zotero_Shards::getByLibraryID($libraryID);
Zotero_DB::beginTransaction();
// Needed for API deletes to get propagated via sync
if ($updateLibrary) {
$timestamp = Zotero_Libraries::updateTimestamps($obj->libraryID);
Zotero_DB::registerTransactionTimestamp($timestamp);
}
// Delete child items
if ($type == 'item') {
if ($obj->isRegularItem()) {
$children = array_merge($obj->getNotes(), $obj->getAttachments());
if ($children) {
$children = Zotero_Items::get($libraryID, $children);
foreach ($children as $child) {
static::delete($child->libraryID, $child->key);
}
}
}
}
if ($type == 'relation') {
// TODO: add key column to relations to speed this up
$sql = "DELETE FROM {$table} WHERE libraryID=? AND MD5(CONCAT(subject, '_', predicate, '_', object))=?";
$deleted = Zotero_DB::query($sql, array($libraryID, $key), $shardID);
} else {
$sql = "DELETE FROM {$table} WHERE libraryID=? AND `key`=?";
$deleted = Zotero_DB::query($sql, array($libraryID, $key), $shardID);
}
unset(self::$idCache[$type][$libraryID][$key]);
static::uncachePrimaryData($libraryID, $key);
if ($deleted) {
$sql = "INSERT INTO syncDeleteLogKeys (libraryID, objectType, `key`, timestamp)\n\t\t\t\t\t\tVALUES (?, '{$type}', ?, ?) ON DUPLICATE KEY UPDATE timestamp=?";
$timestamp = Zotero_DB::getTransactionTimestamp();
$params = array($libraryID, $key, $timestamp, $timestamp);
Zotero_DB::query($sql, $params, $shardID);
}
Zotero_DB::commit();
}
/**
* Save the setting to the DB
*/
public function save($userID = false)
{
if (!$this->libraryID) {
throw new Exception("libraryID not set");
}
if (!isset($this->name) || $this->name === '') {
throw new Exception("Setting name not provided");
}
try {
Zotero_Settings::editCheck($this, $userID);
} catch (Exception $e) {
error_log("WARNING: " . $e);
return false;
}
if (!$this->changed) {
Z_Core::debug("Setting {$this->libraryID}/{$this->name} has not changed");
return false;
}
$shardID = Zotero_Shards::getByLibraryID($this->libraryID);
Zotero_DB::beginTransaction();
$isNew = !$this->exists();
try {
Z_Core::debug("Saving setting {$this->libraryID}/{$this->name}");
$params = array(json_encode($this->value), Zotero_Libraries::getUpdatedVersion($this->libraryID), Zotero_DB::getTransactionTimestamp());
$params = array_merge(array($this->libraryID, $this->name), $params, $params);
$shardID = Zotero_Shards::getByLibraryID($this->libraryID);
$sql = "INSERT INTO settings (libraryID, name, value, version, lastUpdated) " . "VALUES (?, ?, ?, ?, ?) " . "ON DUPLICATE KEY UPDATE value=?, version=?, lastUpdated=?";
Zotero_DB::query($sql, $params, $shardID);
// Remove from delete log if it's there
$sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='setting' AND `key`=?";
Zotero_DB::query($sql, array($this->libraryID, $this->name), $shardID);
Zotero_DB::commit();
} catch (Exception $e) {
Zotero_DB::rollback();
throw $e;
}
return true;
}
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');
}
private function load()
{
$libraryID = $this->libraryID;
$id = $this->id;
$key = $this->key;
Z_Core::debug("Loading data for search " . ($id ? $id : $key));
if (!$libraryID) {
throw new Exception("Library ID not set");
}
if (!$id && !$key) {
throw new Exception("ID or key not set");
}
$shardID = Zotero_Shards::getByLibraryID($libraryID);
$sql = "SELECT searchID AS id, searchName AS name, dateAdded,\n\t\t\t\tdateModified, libraryID, `key`, version\n\t\t\t\tFROM savedSearches WHERE ";
if ($id) {
$sql .= "searchID=?";
$params = $id;
} else {
$sql .= "libraryID=? AND `key`=?";
$params = array($libraryID, $key);
}
$sql .= " GROUP BY searchID";
$data = Zotero_DB::rowQuery($sql, $params, $shardID);
$this->loaded = true;
if (!$data) {
return;
}
foreach ($data as $key => $val) {
$this->{$key} = $val;
}
$sql = "SELECT * FROM savedSearchConditions\n\t\t\t\tWHERE searchID=? ORDER BY searchConditionID";
$conditions = Zotero_DB::query($sql, $this->id, $shardID);
foreach ($conditions as $condition) {
$searchConditionID = $condition['searchConditionID'];
$this->conditions[$searchConditionID] = array('id' => $searchConditionID, 'condition' => $condition['condition'], 'mode' => $condition['mode'], 'operator' => $condition['operator'], 'value' => $condition['value'], 'required' => $condition['required']);
}
}
private static function loadItems($libraryID, $itemIDs = array())
{
$shardID = Zotero_Shards::getByLibraryID($libraryID);
$sql = self::getPrimaryDataSQL() . "1";
// TODO: optimize
if ($itemIDs) {
foreach ($itemIDs as $itemID) {
if (!is_int($itemID)) {
throw new Exception("Invalid itemID {$itemID}");
}
}
$sql .= ' AND itemID IN (' . implode(',', array_fill(0, sizeOf($itemIDs), '?')) . ')';
}
$stmt = Zotero_DB::getStatement($sql, "loadItems_" . sizeOf($itemIDs), $shardID);
$itemRows = Zotero_DB::queryFromStatement($stmt, $itemIDs);
$loadedItemIDs = array();
if ($itemRows) {
foreach ($itemRows as $row) {
if ($row['libraryID'] != $libraryID) {
throw new Exception("Item {$itemID} isn't in library {$libraryID}", Z_ERROR_OBJECT_LIBRARY_MISMATCH);
}
$itemID = $row['id'];
$loadedItemIDs[] = $itemID;
// Item isn't loaded -- create new object and stuff in array
if (!isset(self::$objectCache[$itemID])) {
$item = new Zotero_Item();
$item->loadFromRow($row, true);
self::$objectCache[$itemID] = $item;
} else {
self::$objectCache[$itemID]->loadFromRow($row, true);
}
}
}
if (!$itemIDs) {
// If loading all items, remove old items that no longer exist
$ids = array_keys(self::$objectCache);
foreach ($ids as $id) {
if (!in_array($id, $loadedItemIDs)) {
throw new Exception("Unimplemented");
//$this->unload($id);
}
}
}
}
/**
* 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);
}*/
}