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 add($userID) { Z_Core::debug("Creating publications library for user {$userID}"); Zotero_DB::beginTransaction(); // Use same shard as user library $shardID = Zotero_Shards::getByUserID($userID); $libraryID = Zotero_Libraries::add('publications', $shardID); $sql = "INSERT INTO userPublications (userID, libraryID) VALUES (?, ?)"; Zotero_DB::query($sql, [$userID, $libraryID]); Zotero_DB::commit(); return $libraryID; }
public static function 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 deleteByLibraryMySQL($libraryID) { $sql = "DELETE IFT FROM itemFulltext IFT JOIN items USING (itemID) WHERE libraryID=?"; Zotero_DB::query($sql, $libraryID, Zotero_Shards::getByLibraryID($libraryID)); }
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 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 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 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; }
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; } }
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']); } }
/** * Returns shardIDs of all shards storing groups this user belongs to */ public static function getUserGroupShards($userID) { $groupIDs = self::getUserGroups($userID); if (!$groupIDs) { return array(); } $shardIDs = array(); foreach ($groupIDs as $groupID) { $shardID = Zotero_Shards::getByGroupID($groupID); $shardIDs[$shardID] = true; } return array_keys($shardIDs); }
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 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(); }
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; }
private function logGroupLibraryRemoval() { $users = $this->getUsers(); $usersByShard = array(); foreach ($users as $userID) { $shardID = Zotero_Shards::getByUserID($userID); if (!isset($usersByShard[$shardID])) { $usersByShard[$shardID] = array(); } $usersByShard[$shardID][] = $userID; } foreach ($usersByShard as $shardID => $userIDs) { // Add to delete log for all group members $sql = "REPLACE INTO syncDeleteLogIDs (libraryID, objectType, id) VALUES "; $params = array(); $sets = array(); foreach ($userIDs as $userID) { $libraryID = Zotero_Users::getLibraryIDFromUserID($userID); $sets[] = "(?,?,?)"; $params = array_merge($params, array($libraryID, 'group', $this->id)); } $sql .= implode(",", $sets); Zotero_DB::query($sql, $params, $shardID); } }
public static function queryFromStatement(Zotero_DB_Statement $stmt, $params = false) { try { // Execute statement if not coming from self::query() if ($params) { self::logQuery($stmt->sql, $params, $stmt->shardID); // If this is a write query, make sure shard is writeable if ($stmt->isWriteQuery && $stmt->shardID && !Zotero_Shards::shardIsWriteable($stmt->shardID)) { throw new Exception("Cannot write to read-only shard {$stmt->shardID}", Z_ERROR_SHARD_READ_ONLY); } $instance = self::getInstance(); $instance->checkShardTransaction($stmt->shardID); if (is_scalar($params)) { $params = array($params); } $stmt->execute($params); } $stmt->setFetchMode(Zend_Db::FETCH_ASSOC); $mystmt = $stmt->getDriverStatement(); // Not a read statement if (!$mystmt->field_count) { // Determine the type of query using first word preg_match('/^[^\\s\\(]*/', $stmt->sql, $matches); $queryMethod = strtolower($matches[0]); if ($queryMethod == "update" || $queryMethod == "delete") { return $stmt->rowCount(); } else { if ($queryMethod == "insert") { $insertID = (int) $stmt->link->lastInsertID(); if ($insertID) { return $insertID; } $affectedRows = $stmt->rowCount(); if (!$affectedRows) { return false; } return $affectedRows; } } return true; } // Cast integers $intFieldNames = self::getIntegerColumns($mystmt); $results = array(); while ($row = $stmt->fetch()) { if ($intFieldNames) { foreach ($intFieldNames as $name) { if (is_null($row[$name])) { $row[$name] = null; } else { if (strlen($row[$name]) < 10) { $row[$name] = (int) $row[$name]; } } } } $results[] = $row; } } catch (Exception $e) { self::error($e, $stmt->sql, $params, $stmt->shardID); } return $results; }
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 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; }
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 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; }
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(); } }
/** * 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 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; }
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'); }
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']; } } }
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); } } } }
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; }