public function removestoragefiles() { $this->allowMethods(array('POST')); $sql = "DELETE SFI FROM storageFileItems SFI JOIN items USING (itemID) WHERE libraryID=?"; Zotero_DB::query($sql, $this->objectLibraryID, Zotero_Shards::getByLibraryID($this->objectLibraryID)); header("HTTP/1.1 204 No Content"); exit; }
public static function add($userID) { Z_Core::debug("Creating publications library for user {$userID}"); Zotero_DB::beginTransaction(); // Use same shard as user library $shardID = Zotero_Shards::getByUserID($userID); $libraryID = Zotero_Libraries::add('publications', $shardID); $sql = "INSERT INTO userPublications (userID, libraryID) VALUES (?, ?)"; Zotero_DB::query($sql, [$userID, $libraryID]); Zotero_DB::commit(); return $libraryID; }
public static function deleteUser($userID) { if (empty($userID)) { throw new Exception("userID not provided"); } $username = Zotero_Users::getUsername($userID, true); $sql = "SELECT LUM_Role.Name FROM LUM_User JOIN LUM_Role USING (RoleID) WHERE UserID=?"; try { $role = Zotero_WWW_DB_2::valueQuery($sql, $userID); } catch (Exception $e) { Z_Core::logError("WARNING: {$e} -- retrying on primary"); $role = Zotero_WWW_DB_1::valueQuery($sql, $userID); } if ($role != 'Deleted') { throw new Exception("User '{$username}' does not have role 'Deleted'"); } Zotero_DB::beginTransaction(); if (Zotero_Groups::getUserOwnedGroups($userID)) { throw new Exception("Cannot delete user '{$username}' with owned groups"); } // Remove user from any groups they're a member of // // This isn't strictly necessary thanks to foreign key cascades, // but it removes some extra keyPermissions rows $groupIDs = Zotero_Groups::getUserGroups($userID); foreach ($groupIDs as $groupID) { $group = Zotero_Groups::get($groupID, true); $group->removeUser($userID); } // Remove all data Zotero_Users::clearAllData($userID); // Remove user publications library $libraryID = self::getLibraryIDFromUserID($userID, 'publications'); if ($libraryID) { $shardID = Zotero_Shards::getByLibraryID($libraryID); Zotero_DB::query("DELETE FROM shardLibraries WHERE libraryID=?", $libraryID, $shardID); Zotero_DB::query("DELETE FROM libraries WHERE libraryID=?", $libraryID); } // Remove user/library rows $libraryID = self::getLibraryIDFromUserID($userID); $shardID = Zotero_Shards::getByLibraryID($libraryID); Zotero_DB::query("DELETE FROM shardLibraries WHERE libraryID=?", $libraryID, $shardID); Zotero_DB::query("DELETE FROM libraries WHERE libraryID=?", $libraryID); Zotero_DB::commit(); }
public function save($userID=false) { if (!$this->_libraryID) { trigger_error("Library ID must be set before saving", E_USER_ERROR); } Zotero_Items::editCheck($this, $userID); if (!$this->hasChanged()) { Z_Core::debug("Item $this->id has not changed"); return false; } $this->cacheEnabled = false; // Make sure there are no gaps in the creator indexes $creators = $this->getCreators(); $lastPos = -1; foreach ($creators as $pos=>$creator) { if ($pos != $lastPos + 1) { trigger_error("Creator index $pos out of sequence for item $this->id", E_USER_ERROR); } $lastPos++; } $shardID = Zotero_Shards::getByLibraryID($this->_libraryID); $env = []; Zotero_DB::beginTransaction(); try { // // New item, insert and return id // if (!$this->id || (empty($this->changed['version']) && !$this->exists())) { Z_Core::debug('Saving data for new item to database'); $isNew = $env['isNew'] = true; $sqlColumns = array(); $sqlValues = array(); // // Primary fields // $itemID = $this->_id = $this->_id ? $this->_id : Zotero_ID::get('items'); $key = $this->_key = $this->_key ? $this->_key : Zotero_ID::getKey(); $sqlColumns = array( 'itemID', 'itemTypeID', 'libraryID', 'key', 'dateAdded', 'dateModified', 'serverDateModified', 'version' ); $timestamp = Zotero_DB::getTransactionTimestamp(); $dateAdded = $this->_dateAdded ? $this->_dateAdded : $timestamp; $dateModified = $this->_dateModified ? $this->_dateModified : $timestamp; $version = Zotero_Libraries::getUpdatedVersion($this->_libraryID); $sqlValues = array( $itemID, $this->_itemTypeID, $this->_libraryID, $key, $dateAdded, $dateModified, $timestamp, $version ); $sql = 'INSERT INTO items (`' . implode('`, `', $sqlColumns) . '`) VALUES ('; // Insert placeholders for bind parameters for ($i=0; $i<sizeOf($sqlValues); $i++) { $sql .= '?, '; } $sql = substr($sql, 0, -2) . ')'; // Save basic data to items table try { $insertID = Zotero_DB::query($sql, $sqlValues, $shardID); } catch (Exception $e) { if (strpos($e->getMessage(), "Incorrect datetime value") !== false) { preg_match("/Incorrect datetime value: '([^']+)'/", $e->getMessage(), $matches); throw new Exception("=Invalid date value '{$matches[1]}' for item $key", Z_ERROR_INVALID_INPUT); } throw $e; } if (!$this->_id) { if (!$insertID) { throw new Exception("Item id not available after INSERT"); } $itemID = $insertID; $this->_serverDateModified = $timestamp; } // Group item data if (Zotero_Libraries::getType($this->_libraryID) == 'group' && $userID) { $sql = "INSERT INTO groupItems VALUES (?, ?, ?)"; Zotero_DB::query($sql, array($itemID, $userID, $userID), $shardID); } // // ItemData // if (!empty($this->changed['itemData'])) { // Use manual bound parameters to speed things up $origInsertSQL = "INSERT INTO itemData (itemID, fieldID, value) VALUES "; $insertSQL = $origInsertSQL; $insertParams = array(); $insertCounter = 0; $maxInsertGroups = 40; $max = Zotero_Items::$maxDataValueLength; $fieldIDs = array_keys($this->changed['itemData']); foreach ($fieldIDs as $fieldID) { $value = $this->getField($fieldID, true, false, true); if ($value == 'CURRENT_TIMESTAMP' && Zotero_ItemFields::getID('accessDate') == $fieldID) { $value = Zotero_DB::getTransactionTimestamp(); } // Check length if (strlen($value) > $max) { $fieldName = Zotero_ItemFields::getLocalizedString( $this->_itemTypeID, $fieldID ); $msg = "=$fieldName field value " . "'" . mb_substr($value, 0, 50) . "...' too long"; if ($this->_key) { $msg .= " for item '" . $this->_libraryID . "/" . $key . "'"; } throw new Exception($msg, Z_ERROR_FIELD_TOO_LONG); } if ($insertCounter < $maxInsertGroups) { $insertSQL .= "(?,?,?),"; $insertParams = array_merge( $insertParams, array($itemID, $fieldID, $value) ); } if ($insertCounter == $maxInsertGroups - 1) { $insertSQL = substr($insertSQL, 0, -1); $stmt = Zotero_DB::getStatement($insertSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $insertParams); $insertSQL = $origInsertSQL; $insertParams = array(); $insertCounter = -1; } $insertCounter++; } if ($insertCounter > 0 && $insertCounter < $maxInsertGroups) { $insertSQL = substr($insertSQL, 0, -1); $stmt = Zotero_DB::getStatement($insertSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $insertParams); } } // // Creators // if (!empty($this->changed['creators'])) { $indexes = array_keys($this->changed['creators']); // TODO: group queries $sql = "INSERT INTO itemCreators (itemID, creatorID, creatorTypeID, orderIndex) VALUES "; $placeholders = array(); $sqlValues = array(); $cacheRows = array(); foreach ($indexes as $orderIndex) { Z_Core::debug('Adding creator in position ' . $orderIndex, 4); $creator = $this->getCreator($orderIndex); if (!$creator) { continue; } if ($creator['ref']->hasChanged()) { Z_Core::debug("Auto-saving changed creator {$creator['ref']->id}"); try { $creator['ref']->save(); } catch (Exception $e) { // TODO: Provide the item in question /*if (strpos($e->getCode() == Z_ERROR_CREATOR_TOO_LONG)) { $msg = $e->getMessage(); $msg = str_replace( "with this name and shorten it.", "with this name, or paste '$key' into the quick search bar " . "in the Zotero toolbar, and shorten the name." ); throw new Exception($msg, Z_ERROR_CREATOR_TOO_LONG); }*/ throw $e; } } $placeholders[] = "(?, ?, ?, ?)"; array_push( $sqlValues, $itemID, $creator['ref']->id, $creator['creatorTypeID'], $orderIndex ); $cacheRows[] = array( 'creatorID' => $creator['ref']->id, 'creatorTypeID' => $creator['creatorTypeID'], 'orderIndex' => $orderIndex ); } if ($sqlValues) { $sql = $sql . implode(',', $placeholders); Zotero_DB::query($sql, $sqlValues, $shardID); } } // Deleted item if (!empty($this->changed['deleted'])) { $deleted = $this->getDeleted(); if ($deleted) { $sql = "REPLACE INTO deletedItems (itemID) VALUES (?)"; } else { $sql = "DELETE FROM deletedItems WHERE itemID=?"; } Zotero_DB::query($sql, $itemID, $shardID); } // Note if ($this->isNote() || !empty($this->changed['note'])) { if (!is_string($this->noteText)) { $this->noteText = ''; } // If we don't have a sanitized note, generate one if (is_null($this->noteTextSanitized)) { $noteTextSanitized = Zotero_Notes::sanitize($this->noteText); // But if note is sanitized already, store empty string if ($this->noteText === $noteTextSanitized) { $this->noteTextSanitized = ''; } else { $this->noteTextSanitized = $noteTextSanitized; } } $this->noteTitle = Zotero_Notes::noteToTitle( $this->noteTextSanitized === '' ? $this->noteText : $this->noteTextSanitized ); $sql = "INSERT INTO itemNotes (itemID, sourceItemID, note, noteSanitized, title, hash) VALUES (?,?,?,?,?,?)"; $parent = $this->isNote() ? $this->getSource() : null; $hash = $this->noteText ? md5($this->noteText) : ''; $bindParams = array( $itemID, $parent ? $parent : null, $this->noteText !== null ? $this->noteText : '', $this->noteTextSanitized, $this->noteTitle, $hash ); try { Zotero_DB::query($sql, $bindParams, $shardID); } catch (Exception $e) { if (strpos($e->getMessage(), "Incorrect string value") !== false) { throw new Exception("=Invalid character in note '" . Zotero_Utilities::ellipsize($title, 70) . "'", Z_ERROR_INVALID_INPUT); } throw ($e); } Zotero_Notes::updateNoteCache($this->_libraryID, $itemID, $this->noteText); Zotero_Notes::updateHash($this->_libraryID, $itemID, $hash); } // Attachment if ($this->isAttachment()) { $sql = "INSERT INTO itemAttachments (itemID, sourceItemID, linkMode, mimeType, charsetID, path, storageModTime, storageHash) VALUES (?,?,?,?,?,?,?,?)"; $parent = $this->getSource(); if ($parent) { $parentItem = Zotero_Items::get($this->_libraryID, $parent); if (!$parentItem) { throw new Exception("Parent item $parent not found"); } if ($parentItem->getSource()) { $parentKey = $parentItem->key; throw new Exception("=Parent item $parentKey cannot be a child attachment", Z_ERROR_INVALID_INPUT); } } $linkMode = $this->attachmentLinkMode; $charsetID = Zotero_CharacterSets::getID($this->attachmentCharset); $path = $this->attachmentPath; $storageModTime = $this->attachmentStorageModTime; $storageHash = $this->attachmentStorageHash; $bindParams = array( $itemID, $parent ? $parent : null, $linkMode + 1, $this->attachmentMIMEType, $charsetID ? $charsetID : null, $path ? $path : '', $storageModTime ? $storageModTime : null, $storageHash ? $storageHash : null ); Zotero_DB::query($sql, $bindParams, $shardID); } // Sort fields $sortTitle = Zotero_Items::getSortTitle($this->getDisplayTitle(true)); if (mb_substr($sortTitle, 0, 5) == mb_substr($this->getField('title', false, true), 0, 5)) { $sortTitle = null; } $creatorSummary = $this->isRegularItem() ? mb_strcut($this->getCreatorSummary(true), 0, Zotero_Creators::$creatorSummarySortLength) : ''; $sql = "INSERT INTO itemSortFields (itemID, sortTitle, creatorSummary) VALUES (?, ?, ?)"; Zotero_DB::query($sql, array($itemID, $sortTitle, $creatorSummary), $shardID); // // Source item id // if ($sourceItemID = $this->getSource()) { $newSourceItem = Zotero_Items::get($this->_libraryID, $sourceItemID); if (!$newSourceItem) { throw new Exception("Cannot set source to invalid item"); } switch (Zotero_ItemTypes::getName($this->_itemTypeID)) { case 'note': $newSourceItem->incrementNoteCount(); break; case 'attachment': $newSourceItem->incrementAttachmentCount(); break; } } // Collections if (!empty($this->changed['collections'])) { foreach ($this->collections as $collectionKey) { $collection = Zotero_Collections::getByLibraryAndKey($this->_libraryID, $collectionKey); if (!$collection) { throw new Exception("Collection with key '$collectionKey' not found", Z_ERROR_COLLECTION_NOT_FOUND); } $collection->addItem($itemID); $collection->save(); } } // Tags if (!empty($this->changed['tags'])) { foreach ($this->tags as $tag) { $tagID = Zotero_Tags::getID($this->libraryID, $tag->tag, $tag->type); if ($tagID) { $tagObj = Zotero_Tags::get($this->_libraryID, $tagID); } else { $tagObj = new Zotero_Tag; $tagObj->libraryID = $this->_libraryID; $tagObj->name = $tag->tag; $tagObj->type = (int) $tag->type ? $tag->type : 0; } $tagObj->addItem($this->_key); $tagObj->save(); } } // Related items if (!empty($this->changed['relations'])) { $uri = Zotero_URI::getItemURI($this); $sql = "INSERT IGNORE INTO relations " . "(relationID, libraryID, `key`, subject, predicate, object) " . "VALUES (?, ?, ?, ?, ?, ?)"; $insertStatement = Zotero_DB::getStatement($sql, false, $shardID); foreach ($this->relations as $rel) { $insertStatement->execute( array( Zotero_ID::get('relations'), $this->_libraryID, Zotero_Relations::makeKey($uri, $rel[0], $rel[1]), $uri, $rel[0], $rel[1] ) ); } } // Remove from delete log if it's there $sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='item' AND `key`=?"; Zotero_DB::query($sql, array($this->_libraryID, $key), $shardID); } // // Existing item, update // else { Z_Core::debug('Updating database with new item data for item ' . $this->_libraryID . '/' . $this->_key, 4); $isNew = $env['isNew'] = false; // // Primary fields // $sql = "UPDATE items SET "; $sqlValues = array(); $timestamp = Zotero_DB::getTransactionTimestamp(); $version = Zotero_Libraries::getUpdatedVersion($this->_libraryID); $updateFields = array( 'itemTypeID', 'libraryID', 'key', 'dateAdded', 'dateModified' ); if (!empty($this->changed['primaryData'])) { foreach ($updateFields as $updateField) { if (in_array($updateField, $this->changed['primaryData'])) { $sql .= "`$updateField`=?, "; $sqlValues[] = $this->{"_$updateField"}; } } } $sql .= "serverDateModified=?, version=? WHERE itemID=?"; array_push( $sqlValues, $timestamp, $version, $this->_id ); Zotero_DB::query($sql, $sqlValues, $shardID); $this->_serverDateModified = $timestamp; // Group item data if (Zotero_Libraries::getType($this->_libraryID) == 'group' && $userID) { $sql = "INSERT INTO groupItems VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE lastModifiedByUserID=?"; Zotero_DB::query($sql, array($this->_id, null, $userID, $userID), $shardID); } // // ItemData // if (!empty($this->changed['itemData'])) { $del = array(); $origReplaceSQL = "REPLACE INTO itemData (itemID, fieldID, value) VALUES "; $replaceSQL = $origReplaceSQL; $replaceParams = array(); $replaceCounter = 0; $maxReplaceGroups = 40; $max = Zotero_Items::$maxDataValueLength; $fieldIDs = array_keys($this->changed['itemData']); foreach ($fieldIDs as $fieldID) { $value = $this->getField($fieldID, true, false, true); // If field changed and is empty, mark row for deletion if ($value === "") { $del[] = $fieldID; continue; } if ($value == 'CURRENT_TIMESTAMP' && Zotero_ItemFields::getID('accessDate') == $fieldID) { $value = Zotero_DB::getTransactionTimestamp(); } // Check length if (strlen($value) > $max) { $fieldName = Zotero_ItemFields::getLocalizedString( $this->_itemTypeID, $fieldID ); $msg = "=$fieldName field value " . "'" . mb_substr($value, 0, 50) . "...' too long"; if ($this->_key) { $msg .= " for item '" . $this->_libraryID . "/" . $this->_key . "'"; } throw new Exception($msg, Z_ERROR_FIELD_TOO_LONG); } if ($replaceCounter < $maxReplaceGroups) { $replaceSQL .= "(?,?,?),"; $replaceParams = array_merge($replaceParams, array($this->_id, $fieldID, $value) ); } if ($replaceCounter == $maxReplaceGroups - 1) { $replaceSQL = substr($replaceSQL, 0, -1); $stmt = Zotero_DB::getStatement($replaceSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $replaceParams); $replaceSQL = $origReplaceSQL; $replaceParams = array(); $replaceCounter = -1; } $replaceCounter++; } if ($replaceCounter > 0 && $replaceCounter < $maxReplaceGroups) { $replaceSQL = substr($replaceSQL, 0, -1); $stmt = Zotero_DB::getStatement($replaceSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $replaceParams); } // Update memcached with used fields $fids = array(); foreach ($this->itemData as $fieldID=>$value) { if ($value !== false && $value !== null) { $fids[] = $fieldID; } } // Delete blank fields if ($del) { $sql = 'DELETE from itemData WHERE itemID=? AND fieldID IN ('; $sqlParams = array($this->_id); foreach ($del as $d) { $sql .= '?, '; $sqlParams[] = $d; } $sql = substr($sql, 0, -2) . ')'; Zotero_DB::query($sql, $sqlParams, $shardID); } } // // Creators // if (!empty($this->changed['creators'])) { $indexes = array_keys($this->changed['creators']); $sql = "INSERT INTO itemCreators (itemID, creatorID, creatorTypeID, orderIndex) VALUES "; $placeholders = array(); $sqlValues = array(); $cacheRows = array(); foreach ($indexes as $orderIndex) { Z_Core::debug('Creator in position ' . $orderIndex . ' has changed', 4); $creator = $this->getCreator($orderIndex); $sql2 = 'DELETE FROM itemCreators WHERE itemID=? AND orderIndex=?'; Zotero_DB::query($sql2, array($this->_id, $orderIndex), $shardID); if (!$creator) { continue; } if ($creator['ref']->hasChanged()) { Z_Core::debug("Auto-saving changed creator {$creator['ref']->id}"); $creator['ref']->save(); } $placeholders[] = "(?, ?, ?, ?)"; array_push( $sqlValues, $this->_id, $creator['ref']->id, $creator['creatorTypeID'], $orderIndex ); } if ($sqlValues) { $sql = $sql . implode(',', $placeholders); Zotero_DB::query($sql, $sqlValues, $shardID); } } // Deleted item if (!empty($this->changed['deleted'])) { $deleted = $this->getDeleted(); if ($deleted) { $sql = "REPLACE INTO deletedItems (itemID) VALUES (?)"; } else { $sql = "DELETE FROM deletedItems WHERE itemID=?"; } Zotero_DB::query($sql, $this->_id, $shardID); } // In case this was previously a standalone item, // delete from any collections it may have been in if (!empty($this->changed['source']) && $this->getSource()) { $sql = "DELETE FROM collectionItems WHERE itemID=?"; Zotero_DB::query($sql, $this->_id, $shardID); } // // Note or attachment note // if (!empty($this->changed['note'])) { // If we don't have a sanitized note, generate one if (is_null($this->noteTextSanitized)) { $noteTextSanitized = Zotero_Notes::sanitize($this->noteText); // But if note is sanitized already, store empty string if ($this->noteText == $noteTextSanitized) { $this->noteTextSanitized = ''; } else { $this->noteTextSanitized = $noteTextSanitized; } } $this->noteTitle = Zotero_Notes::noteToTitle( $this->noteTextSanitized === '' ? $this->noteText : $this->noteTextSanitized ); // Only record sourceItemID in itemNotes for notes if ($this->isNote()) { $sourceItemID = $this->getSource(); } $sourceItemID = !empty($sourceItemID) ? $sourceItemID : null; $hash = $this->noteText ? md5($this->noteText) : ''; $sql = "INSERT INTO itemNotes (itemID, sourceItemID, note, noteSanitized, title, hash) VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE sourceItemID=?, note=?, noteSanitized=?, title=?, hash=?"; $bindParams = array( $this->_id, $sourceItemID, $this->noteText, $this->noteTextSanitized, $this->noteTitle, $hash, $sourceItemID, $this->noteText, $this->noteTextSanitized, $this->noteTitle, $hash ); Zotero_DB::query($sql, $bindParams, $shardID); Zotero_Notes::updateNoteCache($this->_libraryID, $this->_id, $this->noteText); Zotero_Notes::updateHash($this->_libraryID, $this->_id, $hash); // TODO: handle changed source? } // Attachment if (!empty($this->changed['attachmentData'])) { $sql = "INSERT INTO itemAttachments (itemID, sourceItemID, linkMode, mimeType, charsetID, path, storageModTime, storageHash) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE sourceItemID=VALUES(sourceItemID), linkMode=VALUES(linkMode), mimeType=VALUES(mimeType), charsetID=VALUES(charsetID), path=VALUES(path), storageModTime=VALUES(storageModTime), storageHash=VALUES(storageHash)"; $parent = $this->getSource(); if ($parent) { $parentItem = Zotero_Items::get($this->_libraryID, $parent); if (!$parentItem) { throw new Exception("Parent item $parent not found"); } if ($parentItem->getSource()) { $parentKey = $parentItem->key; throw new Exception("=Parent item $parentKey cannot be a child attachment", Z_ERROR_INVALID_INPUT); } } $linkMode = $this->attachmentLinkMode; $charsetID = Zotero_CharacterSets::getID($this->attachmentCharset); $path = $this->attachmentPath; $storageModTime = $this->attachmentStorageModTime; $storageHash = $this->attachmentStorageHash; $bindParams = array( $this->_id, $parent ? $parent : null, $linkMode + 1, $this->attachmentMIMEType, $charsetID ? $charsetID : null, $path ? $path : '', $storageModTime ? $storageModTime : null, $storageHash ? $storageHash : null ); Zotero_DB::query($sql, $bindParams, $shardID); } // Sort fields if (!empty($this->changed['primaryData']['itemTypeID']) || !empty($this->changed['itemData']) || !empty($this->changed['creators'])) { $sql = "UPDATE itemSortFields SET sortTitle=?"; $params = array(); $sortTitle = Zotero_Items::getSortTitle($this->getDisplayTitle(true)); if (mb_substr($sortTitle, 0, 5) == mb_substr($this->getField('title', false, true), 0, 5)) { $sortTitle = null; } $params[] = $sortTitle; if (!empty($this->changed['creators'])) { $creatorSummary = mb_strcut($this->getCreatorSummary(true), 0, Zotero_Creators::$creatorSummarySortLength); $sql .= ", creatorSummary=?"; $params[] = $creatorSummary; } $sql .= " WHERE itemID=?"; $params[] = $this->_id; Zotero_DB::query($sql, $params, $shardID); } // // Source item id // if (!empty($this->changed['source'])) { $type = Zotero_ItemTypes::getName($this->_itemTypeID); $Type = ucwords($type); // Update DB, if not a note or attachment we already changed above if (empty($this->changed['attachmentData']) && (empty($this->changed['note']) || !$this->isNote())) { $sql = "UPDATE item" . $Type . "s SET sourceItemID=? WHERE itemID=?"; $parent = $this->getSource(); $bindParams = array( $parent ? $parent : null, $this->_id ); Zotero_DB::query($sql, $bindParams, $shardID); } } if (false && !empty($this->changed['source'])) { trigger_error("Unimplemented", E_USER_ERROR); $newItem = Zotero_Items::get($this->_libraryID, $sourceItemID); // FK check if ($newItem) { if ($sourceItemID) { } else { trigger_error("Cannot set $type source to invalid item $sourceItemID", E_USER_ERROR); } } $oldSourceItemID = $this->getSource(); if ($oldSourceItemID == $sourceItemID) { Z_Core::debug("$Type source hasn't changed", 4); } else { $oldItem = Zotero_Items::get($this->_libraryID, $oldSourceItemID); if ($oldSourceItemID && $oldItem) { } else { //$oldItemNotifierData = null; Z_Core::debug("Old source item $oldSourceItemID didn't exist in setSource()", 2); } // If this was an independent item, remove from any collections where it // existed previously and add source instead if there is one if (!$oldSourceItemID) { $sql = "SELECT collectionID FROM collectionItems WHERE itemID=?"; $changedCollections = Zotero_DB::query($sql, $itemID, $shardID); if ($changedCollections) { trigger_error("Unimplemented", E_USER_ERROR); if ($sourceItemID) { $sql = "UPDATE OR REPLACE collectionItems " . "SET itemID=? WHERE itemID=?"; Zotero_DB::query($sql, array($sourceItemID, $this->_id), $shardID); } else { $sql = "DELETE FROM collectionItems WHERE itemID=?"; Zotero_DB::query($sql, $this->_id, $shardID); } } } $sql = "UPDATE item{$Type}s SET sourceItemID=? WHERE itemID=?"; $bindParams = array( $sourceItemID ? $sourceItemID : null, $itemID ); Zotero_DB::query($sql, $bindParams, $shardID); //Zotero.Notifier.trigger('modify', 'item', $this->_id, notifierData); // Update the counts of the previous and new sources if ($oldItem) { /* switch ($type) { case 'note': $oldItem->decrementNoteCount(); break; case 'attachment': $oldItem->decrementAttachmentCount(); break; } */ //Zotero.Notifier.trigger('modify', 'item', oldSourceItemID, oldItemNotifierData); } if ($newItem) { /* switch ($type) { case 'note': $newItem->incrementNoteCount(); break; case 'attachment': $newItem->incrementAttachmentCount(); break; } */ //Zotero.Notifier.trigger('modify', 'item', sourceItemID, newItemNotifierData); } } } // Collections if (!empty($this->changed['collections'])) { $oldCollections = $this->previousData['collections']; $newCollections = $this->collections; $toAdd = array_diff($newCollections, $oldCollections); $toRemove = array_diff($oldCollections, $newCollections); foreach ($toAdd as $collectionKey) { $collection = Zotero_Collections::getByLibraryAndKey($this->_libraryID, $collectionKey); if (!$collection) { throw new Exception("Collection with key '$collectionKey' not found", Z_ERROR_COLLECTION_NOT_FOUND); } $collection->addItem($this->_id); $collection->save(); } foreach ($toRemove as $collectionKey) { $collection = Zotero_Collections::getByLibraryAndKey($this->_libraryID, $collectionKey); $collection->removeItem($this->_id); $collection->save(); } } if (!empty($this->changed['tags'])) { $oldTags = $this->previousData['tags']; $newTags = $this->tags; $toAdd = []; $toRemove = []; // Get new tags not in existing for ($i=0, $len=sizeOf($newTags); $i<$len; $i++) { if (!isset($newTags[$i]->type)) { $newTags[$i]->type = 0; } $name = trim($newTags[$i]->tag); $type = $newTags[$i]->type; foreach ($oldTags as $tag) { // Do a case-insensitive comparison, to match the client if (strtolower($tag->name) == strtolower($name) && $tag->type == $type) { continue 2; } } $toAdd[] = $newTags[$i]; } // Get existing tags not in new for ($i=0, $len=sizeOf($oldTags); $i<$len; $i++) { $name = $oldTags[$i]->name; $type = $oldTags[$i]->type; foreach ($newTags as $tag) { if (strtolower($tag->tag) == strtolower($name) && $tag->type == $type) { continue 2; } } $toRemove[] = $oldTags[$i]; } foreach ($toAdd as $tag) { $name = $tag->tag; $type = $tag->type; $tagID = Zotero_Tags::getID($this->_libraryID, $name, $type, true); if (!$tagID) { $tag = new Zotero_Tag; $tag->libraryID = $this->_libraryID; $tag->name = $name; $tag->type = $type; $tagID = $tag->save(); } $tag = Zotero_Tags::get($this->_libraryID, $tagID); $tag->addItem($this->_key); $tag->save(); } foreach ($toRemove as $tag) { $tag->removeItem($this->_key); $tag->save(); } } // Related items if (!empty($this->changed['relations'])) { $removed = []; $new = []; $current = $this->relations; // TEMP // Convert old-style related items into relations $sql = "SELECT `key` FROM itemRelated IR " . "JOIN items I ON (IR.linkedItemID=I.itemID) " . "WHERE IR.itemID=?"; $toMigrate = Zotero_DB::columnQuery($sql, $this->_id, $shardID); if ($toMigrate) { $prefix = Zotero_URI::getLibraryURI($this->_libraryID) . "/items/"; $new = array_map(function ($key) use ($prefix) { return [ Zotero_Relations::$relatedItemPredicate, $prefix . $key ]; }, $toMigrate); $sql = "DELETE FROM itemRelated WHERE itemID=?"; Zotero_DB::query($sql, $this->_id, $shardID); } foreach ($this->previousData['relations'] as $rel) { if (array_search($rel, $current) === false) { $removed[] = $rel; } } foreach ($current as $rel) { if (array_search($rel, $this->previousData['relations']) !== false) { continue; } $new[] = $rel; } $uri = Zotero_URI::getItemURI($this); if ($removed) { $sql = "DELETE FROM relations WHERE libraryID=? AND `key`=?"; $deleteStatement = Zotero_DB::getStatement($sql, false, $shardID); foreach ($removed as $rel) { $params = [ $this->_libraryID, Zotero_Relations::makeKey($uri, $rel[0], $rel[1]) ]; $deleteStatement->execute($params); // TEMP // For owl:sameAs, delete reverse as well, since the client // can save that way if ($rel[0] == Zotero_Relations::$linkedObjectPredicate) { $params = [ $this->_libraryID, Zotero_Relations::makeKey($rel[1], $rel[0], $uri) ]; $deleteStatement->execute($params); } } } if ($new) { $sql = "INSERT IGNORE INTO relations " . "(relationID, libraryID, `key`, subject, predicate, object) " . "VALUES (?, ?, ?, ?, ?, ?)"; $insertStatement = Zotero_DB::getStatement($sql, false, $shardID); foreach ($new as $rel) { $insertStatement->execute( array( Zotero_ID::get('relations'), $this->_libraryID, Zotero_Relations::makeKey($uri, $rel[0], $rel[1]), $uri, $rel[0], $rel[1] ) ); // If adding a related item, the version on that item has to be // updated as well (if it exists). Otherwise, requests for that // item will return cached data without the new relation. if ($rel[0] == Zotero_Relations::$relatedItemPredicate) { $relatedItem = Zotero_URI::getURIItem($rel[1]); if (!$relatedItem) { Z_Core::debug("Related item " . $rel[1] . " does not exist " . "for item " . $this->_libraryKey); continue; } // If item has already changed, assume something else is taking // care of saving it and don't do so now, to avoid endless loops // with circular relations if ($relatedItem->hasChanged()) { continue; } $relatedItem->updateVersion($userID); } } } } } Zotero_DB::commit(); } catch (Exception $e) { Zotero_DB::rollback(); throw ($e); } $this->cacheEnabled = false; $this->finalizeSave($env); if ($isNew) { Zotero_Notifier::trigger('add', 'item', $this->_libraryID . "/" . $this->_key); return $this->_id; } Zotero_Notifier::trigger('modify', 'item', $this->_libraryID . "/" . $this->_key); return true; }
public function save($userID = false) { if (!$this->libraryID) { trigger_error("Library ID must be set before saving", E_USER_ERROR); } Zotero_Items::editCheck($this); if (!$this->hasChanged()) { Z_Core::debug("Item {$this->id} has not changed"); return false; } // Make sure there are no gaps in the creator indexes $creators = $this->getCreators(); $lastPos = -1; foreach ($creators as $pos => $creator) { if ($pos != $lastPos + 1) { trigger_error("Creator index {$pos} out of sequence for item {$this->id}", E_USER_ERROR); } $lastPos++; } $shardID = Zotero_Shards::getByLibraryID($this->libraryID); Zotero_DB::beginTransaction(); try { // // New item, insert and return id // if (!$this->id || !$this->exists()) { Z_Core::debug('Saving data for new item to database'); $isNew = true; $sqlColumns = array(); $sqlValues = array(); // // Primary fields // $itemID = $this->id ? $this->id : Zotero_ID::get('items'); $key = $this->key ? $this->key : $this->generateKey(); $sqlColumns = array('itemID', 'itemTypeID', 'libraryID', 'key', 'dateAdded', 'dateModified', 'serverDateModified'); $timestamp = Zotero_DB::getTransactionTimestamp(); $sqlValues = array($itemID, $this->itemTypeID, $this->libraryID, $key, $this->dateAdded ? $this->dateAdded : $timestamp, $this->dateModified ? $this->dateModified : $timestamp, $timestamp); // // Primary fields // $sql = 'INSERT INTO items (`' . implode('`, `', $sqlColumns) . '`) VALUES ('; // Insert placeholders for bind parameters for ($i = 0; $i < sizeOf($sqlValues); $i++) { $sql .= '?, '; } $sql = substr($sql, 0, -2) . ')'; // Save basic data to items table $insertID = Zotero_DB::query($sql, $sqlValues, $shardID); if (!$this->id) { if (!$insertID) { throw new Exception("Item id not available after INSERT"); } $itemID = $insertID; $this->serverDateModified = $timestamp; } // Group item data if (Zotero_Libraries::getType($this->libraryID) == 'group' && $userID) { $sql = "INSERT INTO groupItems VALUES (?, ?, ?)"; Zotero_DB::query($sql, array($itemID, $userID, null), $shardID); } // // ItemData // if ($this->changed['itemData']) { // Use manual bound parameters to speed things up $origInsertSQL = "INSERT INTO itemData (itemID, fieldID, value) VALUES "; $insertSQL = $origInsertSQL; $insertParams = array(); $insertCounter = 0; $maxInsertGroups = 40; $max = Zotero_Items::$maxDataValueLength; $fieldIDs = array_keys($this->changed['itemData']); foreach ($fieldIDs as $fieldID) { $value = $this->getField($fieldID, true, false, true); if ($value == 'CURRENT_TIMESTAMP' && Zotero_ItemFields::getID('accessDate') == $fieldID) { $value = Zotero_DB::getTransactionTimestamp(); } // Check length if (strlen($value) > $max) { $fieldName = Zotero_ItemFields::getLocalizedString($this->itemTypeID, $fieldID); throw new Exception("={$fieldName} field " . "'" . substr($value, 0, 50) . "...' too long"); } if ($insertCounter < $maxInsertGroups) { $insertSQL .= "(?,?,?),"; $insertParams = array_merge($insertParams, array($itemID, $fieldID, $value)); } if ($insertCounter == $maxInsertGroups - 1) { $insertSQL = substr($insertSQL, 0, -1); $stmt = Zotero_DB::getStatement($insertSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $insertParams); $insertSQL = $origInsertSQL; $insertParams = array(); $insertCounter = -1; } $insertCounter++; } if ($insertCounter > 0 && $insertCounter < $maxInsertGroups) { $insertSQL = substr($insertSQL, 0, -1); $stmt = Zotero_DB::getStatement($insertSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $insertParams); } } // // Creators // if ($this->changed['creators']) { $indexes = array_keys($this->changed['creators']); // TODO: group queries $sql = "INSERT INTO itemCreators\n\t\t\t\t\t\t\t\t(itemID, creatorID, creatorTypeID, orderIndex) VALUES "; $placeholders = array(); $sqlValues = array(); $cacheRows = array(); foreach ($indexes as $orderIndex) { Z_Core::debug('Adding creator in position ' . $orderIndex, 4); $creator = $this->getCreator($orderIndex); if (!$creator) { continue; } if ($creator['ref']->hasChanged()) { Z_Core::debug("Auto-saving changed creator {$creator['ref']->id}"); $creator['ref']->save(); } $placeholders[] = "(?, ?, ?, ?)"; array_push($sqlValues, $itemID, $creator['ref']->id, $creator['creatorTypeID'], $orderIndex); $cacheRows[] = array('creatorID' => $creator['ref']->id, 'creatorTypeID' => $creator['creatorTypeID'], 'orderIndex' => $orderIndex); } if ($sqlValues) { $sql = $sql . implode(',', $placeholders); Zotero_DB::query($sql, $sqlValues, $shardID); } } // Deleted item if ($this->changed['deleted']) { $deleted = $this->getDeleted(); if ($deleted) { $sql = "REPLACE INTO deletedItems (itemID) VALUES (?)"; } else { $sql = "DELETE FROM deletedItems WHERE itemID=?"; } Zotero_DB::query($sql, $itemID, $shardID); } // Note if ($this->isNote() || $this->changed['note']) { $noteIsSanitized = false; // If we don't have a sanitized note, generate one if (is_null($this->noteTextSanitized)) { $noteTextSanitized = Zotero_Notes::sanitize($this->noteText); // But if the same as original, just use reference if ($this->noteText == $noteTextSanitized) { $this->noteTextSanitized =& $this->noteText; $noteIsSanitized = true; } else { $this->noteTextSanitized = $noteTextSanitized; } } // If note is sanitized already, store empty string // If not, store sanitized version $noteTextSanitized = $noteIsSanitized ? '' : $this->noteTextSanitized; $title = Zotero_Notes::noteToTitle($this->noteTextSanitized); $sql = "INSERT INTO itemNotes\n\t\t\t\t\t\t\t(itemID, sourceItemID, note, noteSanitized, title, hash)\n\t\t\t\t\t\t\tVALUES (?,?,?,?,?,?)"; $parent = $this->isNote() ? $this->getSource() : null; $hash = $this->noteText ? md5($this->noteText) : ''; $bindParams = array($itemID, $parent ? $parent : null, $this->noteText ? $this->noteText : '', $noteTextSanitized, $title, $hash); try { Zotero_DB::query($sql, $bindParams, $shardID); } catch (Exception $e) { if (strpos($e->getMessage(), "Incorrect string value") !== false) { throw new Exception("=Invalid character in note '" . Zotero_Utilities::ellipsize($title, 70) . "'", Z_ERROR_INVALID_INPUT); } throw $e; } Zotero_Notes::updateNoteCache($this->libraryID, $itemID, $this->noteText); Zotero_Notes::updateHash($this->libraryID, $itemID, $hash); } // Attachment if ($this->isAttachment()) { $sql = "INSERT INTO itemAttachments\n\t\t\t\t\t\t\t(itemID, sourceItemID, linkMode, mimeType, charsetID, path, storageModTime, storageHash)\n\t\t\t\t\t\t\tVALUES (?,?,?,?,?,?,?,?)"; $parent = $this->getSource(); if ($parent) { $parentItem = Zotero_Items::get($this->libraryID, $parent); if (!$parentItem) { throw new Exception("Parent item {$parent} not found"); } if ($parentItem->getSource()) { trigger_error("Parent item cannot be a child attachment", E_USER_ERROR); } } $linkMode = $this->attachmentLinkMode; $charsetID = Zotero_CharacterSets::getID($this->attachmentCharset); $path = $this->attachmentPath; $storageModTime = $this->attachmentStorageModTime; $storageHash = $this->attachmentStorageHash; $bindParams = array($itemID, $parent ? $parent : null, $linkMode + 1, $this->attachmentMIMEType, $charsetID ? $charsetID : null, $path ? $path : '', $storageModTime ? $storageModTime : null, $storageHash ? $storageHash : null); Zotero_DB::query($sql, $bindParams, $shardID); } // Sort fields $sortTitle = Zotero_Items::getSortTitle($this->getDisplayTitle(true)); if (mb_substr($sortTitle, 0, 5) == mb_substr($this->getField('title', false, true), 0, 5)) { $sortTitle = null; } $creatorSummary = $this->isRegularItem() ? mb_strcut($this->getCreatorSummary(), 0, Zotero_Creators::$creatorSummarySortLength) : ''; $sql = "INSERT INTO itemSortFields (itemID, sortTitle, creatorSummary) VALUES (?, ?, ?)"; Zotero_DB::query($sql, array($itemID, $sortTitle, $creatorSummary), $shardID); // // Source item id // if ($sourceItemID = $this->getSource()) { $newSourceItem = Zotero_Items::get($this->libraryID, $sourceItemID); if (!$newSourceItem) { throw new Exception("Cannot set source to invalid item"); } switch (Zotero_ItemTypes::getName($this->itemTypeID)) { case 'note': $newSourceItem->incrementNoteCount(); break; case 'attachment': $newSourceItem->incrementAttachmentCount(); break; } } // Related items if (!empty($this->changed['relatedItems'])) { $removed = array(); $newids = array(); $currentIDs = $this->relatedItems; if (!$currentIDs) { $currentIDs = array(); } foreach ($this->previousData['relatedItems'] as $id) { if (!in_array($id, $currentIDs)) { $removed[] = $id; } } foreach ($currentIDs as $id) { if (in_array($id, $this->previousData['relatedItems'])) { continue; } $newids[] = $id; } if ($removed) { $sql = "DELETE FROM itemRelated WHERE itemID=?\n\t\t\t\t\t\t\t\tAND linkedItemID IN ("; $sql .= implode(', ', array_fill(0, sizeOf($removed), '?')) . ")"; Zotero_DB::query($sql, array_merge(array($this->id), $removed), $shardID); } if ($newids) { $sql = "INSERT INTO itemRelated (itemID, linkedItemID)\n\t\t\t\t\t\t\t\tVALUES (?,?)"; $insertStatement = Zotero_DB::getStatement($sql, false, $shardID); foreach ($newids as $linkedItemID) { $insertStatement->execute(array($itemID, $linkedItemID)); } } } // Remove from delete log if it's there $sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='item' AND `key`=?"; Zotero_DB::query($sql, array($this->libraryID, $key), $shardID); } else { Z_Core::debug('Updating database with new item data', 4); $isNew = false; // // Primary fields // $sql = "UPDATE items SET "; $sqlValues = array(); $timestamp = Zotero_DB::getTransactionTimestamp(); $updateFields = array('itemTypeID', 'libraryID', 'key', 'dateAdded', 'dateModified'); foreach ($updateFields as $updateField) { if (in_array($updateField, $this->changed['primaryData'])) { $sql .= "`{$updateField}`=?, "; $sqlValues[] = $this->{$updateField}; } else { if ($updateField == 'dateModified') { $sql .= "`{$updateField}`=?, "; $sqlValues[] = $timestamp; } } } $sql .= "serverDateModified=?, version=IF(version = 65535, 0, version + 1) WHERE itemID=?"; array_push($sqlValues, $timestamp, $this->id); Zotero_DB::query($sql, $sqlValues, $shardID); $this->serverDateModified = $timestamp; // Group item data if (Zotero_Libraries::getType($this->libraryID) == 'group' && $userID) { $sql = "INSERT INTO groupItems VALUES (?, ?, ?)\n\t\t\t\t\t\t\t\tON DUPLICATE KEY UPDATE lastModifiedByUserID=?"; Zotero_DB::query($sql, array($this->id, null, $userID, $userID), $shardID); } // // ItemData // if ($this->changed['itemData']) { $del = array(); $origReplaceSQL = "REPLACE INTO itemData (itemID, fieldID, value) VALUES "; $replaceSQL = $origReplaceSQL; $replaceParams = array(); $replaceCounter = 0; $maxReplaceGroups = 40; $max = Zotero_Items::$maxDataValueLength; $fieldIDs = array_keys($this->changed['itemData']); foreach ($fieldIDs as $fieldID) { $value = $this->getField($fieldID, true, false, true); // If field changed and is empty, mark row for deletion if ($value === "") { $del[] = $fieldID; continue; } if ($value == 'CURRENT_TIMESTAMP' && Zotero_ItemFields::getID('accessDate') == $fieldID) { $value = Zotero_DB::getTransactionTimestamp(); } // Check length if (strlen($value) > $max) { $fieldName = Zotero_ItemFields::getLocalizedString($this->itemTypeID, $fieldID); throw new Exception("={$fieldName} field " . "'" . substr($value, 0, 50) . "...' too long"); } if ($replaceCounter < $maxReplaceGroups) { $replaceSQL .= "(?,?,?),"; $replaceParams = array_merge($replaceParams, array($this->id, $fieldID, $value)); } if ($replaceCounter == $maxReplaceGroups - 1) { $replaceSQL = substr($replaceSQL, 0, -1); $stmt = Zotero_DB::getStatement($replaceSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $replaceParams); $replaceSQL = $origReplaceSQL; $replaceParams = array(); $replaceCounter = -1; } $replaceCounter++; } if ($replaceCounter > 0 && $replaceCounter < $maxReplaceGroups) { $replaceSQL = substr($replaceSQL, 0, -1); $stmt = Zotero_DB::getStatement($replaceSQL, true, $shardID); Zotero_DB::queryFromStatement($stmt, $replaceParams); } // Update memcached with used fields $fids = array(); foreach ($this->itemData as $fieldID => $value) { if ($value !== false && $value !== null) { $fids[] = $fieldID; } } // Delete blank fields if ($del) { $sql = 'DELETE from itemData WHERE itemID=? AND fieldID IN ('; $sqlParams = array($this->id); foreach ($del as $d) { $sql .= '?, '; $sqlParams[] = $d; } $sql = substr($sql, 0, -2) . ')'; Zotero_DB::query($sql, $sqlParams, $shardID); } } // // Creators // if ($this->changed['creators']) { $indexes = array_keys($this->changed['creators']); $sql = "INSERT INTO itemCreators\n\t\t\t\t\t\t\t\t(itemID, creatorID, creatorTypeID, orderIndex) VALUES "; $placeholders = array(); $sqlValues = array(); $cacheRows = array(); foreach ($indexes as $orderIndex) { Z_Core::debug('Creator in position ' . $orderIndex . ' has changed', 4); $creator = $this->getCreator($orderIndex); $sql2 = 'DELETE FROM itemCreators WHERE itemID=? AND orderIndex=?'; Zotero_DB::query($sql2, array($this->id, $orderIndex), $shardID); if (!$creator) { continue; } if ($creator['ref']->hasChanged()) { Z_Core::debug("Auto-saving changed creator {$creator['ref']->id}"); $creator['ref']->save(); } $placeholders[] = "(?, ?, ?, ?)"; array_push($sqlValues, $this->id, $creator['ref']->id, $creator['creatorTypeID'], $orderIndex); } if ($sqlValues) { $sql = $sql . implode(',', $placeholders); Zotero_DB::query($sql, $sqlValues, $shardID); } } // Deleted item if ($this->changed['deleted']) { $deleted = $this->getDeleted(); if ($deleted) { $sql = "REPLACE INTO deletedItems (itemID) VALUES (?)"; } else { $sql = "DELETE FROM deletedItems WHERE itemID=?"; } Zotero_DB::query($sql, $this->id, $shardID); } // In case this was previously a standalone item, // delete from any collections it may have been in if ($this->changed['source'] && $this->getSource()) { $sql = "DELETE FROM collectionItems WHERE itemID=?"; Zotero_DB::query($sql, $this->id, $shardID); } // // Note or attachment note // if ($this->changed['note']) { $noteIsSanitized = false; // If we don't have a sanitized note, generate one if (is_null($this->noteTextSanitized)) { $noteTextSanitized = Zotero_Notes::sanitize($this->noteText); // But if the same as original, just use reference if ($this->noteText == $noteTextSanitized) { $this->noteTextSanitized =& $this->noteText; $noteIsSanitized = true; } else { $this->noteTextSanitized = $noteTextSanitized; } } // If note is sanitized already, store empty string // If not, store sanitized version $noteTextSanitized = $noteIsSanitized ? '' : $this->noteTextSanitized; $title = Zotero_Notes::noteToTitle($this->noteTextSanitized); // Only record sourceItemID in itemNotes for notes if ($this->isNote()) { $sourceItemID = $this->getSource(); } $sourceItemID = !empty($sourceItemID) ? $sourceItemID : null; $hash = $this->noteText ? md5($this->noteText) : ''; $sql = "INSERT INTO itemNotes\n\t\t\t\t\t\t\t(itemID, sourceItemID, note, noteSanitized, title, hash)\n\t\t\t\t\t\t\tVALUES (?,?,?,?,?,?)\n\t\t\t\t\t\t\tON DUPLICATE KEY UPDATE sourceItemID=?, note=?, noteSanitized=?, title=?, hash=?"; $bindParams = array($this->id, $sourceItemID, $this->noteText ? $this->noteText : '', $noteTextSanitized, $title, $hash, $sourceItemID, $this->noteText ? $this->noteText : '', $noteTextSanitized, $title, $hash); Zotero_DB::query($sql, $bindParams, $shardID); Zotero_Notes::updateNoteCache($this->libraryID, $this->id, $this->noteText); Zotero_Notes::updateHash($this->libraryID, $this->id, $hash); // TODO: handle changed source? } // Attachment if ($this->changed['attachmentData']) { $sql = "REPLACE INTO itemAttachments\n\t\t\t\t\t\t(itemID, sourceItemID, linkMode, mimeType, charsetID, path, storageModTime, storageHash)\n\t\t\t\t\t\tVALUES (?,?,?,?,?,?,?,?)"; $parent = $this->getSource(); if ($parent) { $parentItem = Zotero_Items::get($this->libraryID, $parent); if (!$parentItem) { throw new Exception("Parent item {$parent} not found"); } if ($parentItem->getSource()) { trigger_error("Parent item cannot be a child attachment", E_USER_ERROR); } } $linkMode = $this->attachmentLinkMode; $charsetID = Zotero_CharacterSets::getID($this->attachmentCharset); $path = $this->attachmentPath; $storageModTime = $this->attachmentStorageModTime; $storageHash = $this->attachmentStorageHash; $bindParams = array($this->id, $parent ? $parent : null, $linkMode + 1, $this->attachmentMIMEType, $charsetID ? $charsetID : null, $path ? $path : '', $storageModTime ? $storageModTime : null, $storageHash ? $storageHash : null); Zotero_DB::query($sql, $bindParams, $shardID); } // Sort fields if (!empty($this->changed['primaryData']['itemTypeID']) || $this->changed['itemData'] || $this->changed['creators']) { $sql = "UPDATE itemSortFields SET sortTitle=?"; $params = array(); $sortTitle = Zotero_Items::getSortTitle($this->getDisplayTitle(true)); if (mb_substr($sortTitle, 0, 5) == mb_substr($this->getField('title', false, true), 0, 5)) { $sortTitle = null; } $params[] = $sortTitle; if ($this->changed['creators']) { $creatorSummary = mb_strcut($this->getCreatorSummary(), 0, Zotero_Creators::$creatorSummarySortLength); $sql .= ", creatorSummary=?"; $params[] = $creatorSummary; } $sql .= " WHERE itemID=?"; $params[] = $this->id; Zotero_DB::query($sql, $params, $shardID); } // // Source item id // if ($this->changed['source']) { $type = Zotero_ItemTypes::getName($this->itemTypeID); $Type = ucwords($type); // Update DB, if not a note or attachment we already changed above if (!$this->changed['attachmentData'] && (!$this->changed['note'] || !$this->isNote())) { $sql = "UPDATE item" . $Type . "s SET sourceItemID=? WHERE itemID=?"; $parent = $this->getSource(); $bindParams = array($parent ? $parent : null, $this->id); Zotero_DB::query($sql, $bindParams, $shardID); } } if (false && $this->changed['source']) { trigger_error("Unimplemented", E_USER_ERROR); $newItem = Zotero_Items::get($this->libraryID, $sourceItemID); // FK check if ($newItem) { if ($sourceItemID) { } else { trigger_error("Cannot set {$type} source to invalid item {$sourceItemID}", E_USER_ERROR); } } $oldSourceItemID = $this->getSource(); if ($oldSourceItemID == $sourceItemID) { Z_Core::debug("{$Type} source hasn't changed", 4); } else { $oldItem = Zotero_Items::get($this->libraryID, $oldSourceItemID); if ($oldSourceItemID && $oldItem) { } else { //$oldItemNotifierData = null; Z_Core::debug("Old source item {$oldSourceItemID} didn't exist in setSource()", 2); } // If this was an independent item, remove from any collections where it // existed previously and add source instead if there is one if (!$oldSourceItemID) { $sql = "SELECT collectionID FROM collectionItems WHERE itemID=?"; $changedCollections = Zotero_DB::query($sql, $itemID, $shardID); if ($changedCollections) { trigger_error("Unimplemented", E_USER_ERROR); if ($sourceItemID) { $sql = "UPDATE OR REPLACE collectionItems " . "SET itemID=? WHERE itemID=?"; Zotero_DB::query($sql, array($sourceItemID, $this->id), $shardID); } else { $sql = "DELETE FROM collectionItems WHERE itemID=?"; Zotero_DB::query($sql, $this->id, $shardID); } } } $sql = "UPDATE item{$Type}s SET sourceItemID=?\n\t\t\t\t\t\t\t\tWHERE itemID=?"; $bindParams = array($sourceItemID ? $sourceItemID : null, $itemID); Zotero_DB::query($sql, $bindParams, $shardID); //Zotero.Notifier.trigger('modify', 'item', $this->id, notifierData); // Update the counts of the previous and new sources if ($oldItem) { /* switch ($type) { case 'note': $oldItem->decrementNoteCount(); break; case 'attachment': $oldItem->decrementAttachmentCount(); break; } */ //Zotero.Notifier.trigger('modify', 'item', oldSourceItemID, oldItemNotifierData); } if ($newItem) { /* switch ($type) { case 'note': $newItem->incrementNoteCount(); break; case 'attachment': $newItem->incrementAttachmentCount(); break; } */ //Zotero.Notifier.trigger('modify', 'item', sourceItemID, newItemNotifierData); } } } // Related items if (!empty($this->changed['relatedItems'])) { $removed = array(); $newids = array(); $currentIDs = $this->relatedItems; if (!$currentIDs) { $currentIDs = array(); } foreach ($this->previousData['relatedItems'] as $id) { if (!in_array($id, $currentIDs)) { $removed[] = $id; } } foreach ($currentIDs as $id) { if (in_array($id, $this->previousData['relatedItems'])) { continue; } $newids[] = $id; } if ($removed) { $sql = "DELETE FROM itemRelated WHERE itemID=?\n\t\t\t\t\t\t\t\tAND linkedItemID IN ("; $q = array_fill(0, sizeOf($removed), '?'); $sql .= implode(', ', $q) . ")"; Zotero_DB::query($sql, array_merge(array($this->id), $removed), $shardID); } if ($newids) { $sql = "INSERT INTO itemRelated (itemID, linkedItemID)\n\t\t\t\t\t\t\t\tVALUES (?,?)"; $insertStatement = Zotero_DB::getStatement($sql, false, $shardID); foreach ($newids as $linkedItemID) { $insertStatement->execute(array($this->id, $linkedItemID)); } } } } Zotero_DB::commit(); } catch (Exception $e) { Zotero_DB::rollback(); throw $e; } if (!$this->id) { $this->id = $itemID; } if (!$this->key) { $this->key = $key; } if ($isNew) { Zotero_Items::cache($this); Zotero_Items::cacheLibraryKeyID($this->libraryID, $key, $itemID); } // TODO: invalidate memcache Zotero_Items::reload($this->libraryID, $this->id); if ($isNew) { //Zotero.Notifier.trigger('add', 'item', $this->getID()); return $this->id; } //Zotero.Notifier.trigger('modify', 'item', $this->getID(), { old: $this->_preChangeArray }); return true; }
public static function getAllAdvanced($userID = false, $params = array(), $permissions = null) { $buffer = 20; $maxTimes = 3; $groups = array(); $start = !empty($params['start']) ? $params['start'] : 0; $limit = !empty($params['limit']) ? $params['limit'] + $buffer : false; $totalResults = null; $times = 0; while (true) { if ($times > 0) { Z_Core::logError('Getting more groups in Zotero_Groups::getAllAdvanced()'); } $sql = "SELECT SQL_CALC_FOUND_ROWS G.groupID, GUO.userID AS ownerUserID FROM groups G\n\t\t\t\t\tJOIN groupUsers GUO ON (G.groupID=GUO.groupID AND GUO.role='owner') "; $sqlParams = array(); if ($userID) { $sql .= "JOIN groupUsers GUA ON (G.groupID=GUA.groupID) WHERE GUA.userID=? "; $sqlParams[] = $userID; } $paramSQL = ""; $includeEmpty = false; if (!empty($params['q'])) { if (!is_array($params['q'])) { $params['q'] = array($params['q']); } foreach ($params['q'] as $q) { $field = explode(":", $q); if (sizeOf($field) == 2) { switch ($field[0]) { case 'slug': $includeEmpty = true; break; default: throw new Exception("Cannot search by group field '{$field[0]}'", Z_ERROR_INVALID_GROUP_TYPE); } $paramSQL .= "AND " . $field[0]; // If first character is '-', negate $paramSQL .= $field[0][0] == '-' ? '!' : ''; $paramSQL .= "=? "; $sqlParams[] = $field[1]; } else { $paramSQL .= "AND name LIKE ? "; $sqlParams[] = "%{$q}%"; } } } if (!$userID) { if ($includeEmpty) { $sql .= "WHERE 1 "; } else { // Don't include groups that have never had items $sql .= "JOIN libraries L ON (G.libraryID=L.libraryID)\n\t\t\t\t\t\t\tWHERE L.lastUpdated != '0000-00-00 00:00:00' "; } } $sql .= $paramSQL; if (!empty($params['fq'])) { if (!is_array($params['fq'])) { $params['fq'] = array($params['fq']); } foreach ($params['fq'] as $fq) { $facet = explode(":", $fq); if (sizeOf($facet) == 2 && preg_match('/-?GroupType/', $facet[0])) { switch ($facet[1]) { case 'PublicOpen': case 'PublicClosed': case 'Private': break; default: throw new Exception("Invalid group type '{$facet[1]}'", Z_ERROR_INVALID_GROUP_TYPE); } $sql .= "AND type"; // If first character is '-', negate $sql .= $facet[0][0] == '-' ? '!' : ''; $sql .= "=? "; $sqlParams[] = $facet[1]; } } } if (!empty($params['order'])) { $order = $params['order']; if ($order == 'title') { $order = 'name'; } $sql .= "ORDER BY {$order}"; if (!empty($params['sort'])) { $sql .= " " . $params['sort'] . " "; } } // Set limit higher than the actual limit, in case some groups are // removed during access checks // // Actual limiting is done below if ($limit) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $start; $sqlParams[] = $limit; } $rows = Zotero_DB::query($sql, $sqlParams); if (!$rows) { break; } if (!$totalResults) { $foundRows = Zotero_DB::valueQuery("SELECT FOUND_ROWS()"); $totalResults = $foundRows; } // Include only groups with non-banned owners $owners = array(); foreach ($rows as $row) { $owners[] = $row['ownerUserID']; } $owners = Zotero_Users::getValidUsers($owners); $ids = array(); foreach ($rows as $row) { if (!in_array($row['ownerUserID'], $owners)) { $totalResults--; continue; } $ids[] = $row['groupID']; } $batchStartPos = sizeOf($groups); foreach ($ids as $id) { $group = Zotero_Groups::get($id, true); $groups[] = $group; } // Remove groups that can't be accessed if ($permissions) { for ($i = $batchStartPos; $i < sizeOf($groups); $i++) { $libraryID = (int) $groups[$i]->libraryID; // TEMP: casting shouldn't be necessary if (!$permissions->canAccess($libraryID)) { array_splice($groups, $i, 1); $i--; $totalResults--; } } } $times++; if ($times == $maxTimes) { Z_Core::logError('Too many queries in Zotero_Groups::getAllAdvanced()'); break; } if (empty($params['limit'])) { break; } // If we have enough groups to fill the limit, stop if (sizeOf($groups) > $params['limit']) { break; } // If there no more rows, stop if ($start + sizeOf($rows) == $foundRows) { break; } // This shouldn't happen if ($start + sizeOf($rows) > $foundRows) { Z_Core::logError('More rows than $foundRows in Zotero_Groups::getAllAdvanced()'); } $start = $start + sizeOf($rows); // Get number we still need plus the buffer or all remaining, whichever is lower $limit = min($params['limit'] - sizeOf($groups) + $buffer, $foundRows - $start); } // TODO: generate previous start value if (!$groups) { return array('groups' => array(), 'totalResults' => 0); } // Fake limiting -- we can't just use SQL limit because // some groups might be inaccessible if (!empty($params['limit'])) { $groups = array_slice($groups, 0, $params['limit']); } $results = array('groups' => $groups, 'totalResults' => $totalResults); return $results; }
private static function deleteLibrary($libraryID, $shardID) { $sql = "DELETE FROM shardLibraries WHERE libraryID=?"; Zotero_DB::query($sql, $libraryID, $shardID); }
private static function updateLastAdded($storageFileID) { $sql = "UPDATE storageFiles SET lastAdded=NOW() WHERE storageFileID=?"; Zotero_DB::query($sql, $storageFileID); }
public function save() { if (!$this->libraryID) { trigger_error("Library ID must be set before saving", E_USER_ERROR); } Zotero_Creators::editCheck($this); // If empty, move on if ($this->firstName === '' && $this->lastName === '') { throw new Exception('First and last name are empty'); } if ($this->fieldMode == 1 && $this->firstName !== '') { throw new Exception('First name must be empty in single-field mode'); } if (!$this->hasChanged()) { Z_Core::debug("Creator {$this->id} has not changed"); return false; } Zotero_DB::beginTransaction(); try { $creatorID = $this->id ? $this->id : Zotero_ID::get('creators'); $isNew = !$this->id; Z_Core::debug("Saving creator {$this->id}"); $key = $this->key ? $this->key : $this->generateKey(); $timestamp = Zotero_DB::getTransactionTimestamp(); $dateAdded = $this->dateAdded ? $this->dateAdded : $timestamp; $dateModified = $this->changed['dateModified'] ? $this->dateModified : $timestamp; $fields = "firstName=?, lastName=?, fieldMode=?,\n\t\t\t\t\t\tlibraryID=?, `key`=?, dateAdded=?, dateModified=?, serverDateModified=?"; $params = array($this->firstName, $this->lastName, $this->fieldMode, $this->libraryID, $key, $dateAdded, $dateModified, $timestamp); $shardID = Zotero_Shards::getByLibraryID($this->libraryID); try { if ($isNew) { $sql = "INSERT INTO creators SET creatorID=?, {$fields}"; $stmt = Zotero_DB::getStatement($sql, true, $shardID); Zotero_DB::queryFromStatement($stmt, array_merge(array($creatorID), $params)); // Remove from delete log if it's there $sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='creator' AND `key`=?"; Zotero_DB::query($sql, array($this->libraryID, $key), $shardID); } else { $sql = "UPDATE creators SET {$fields} WHERE creatorID=?"; $stmt = Zotero_DB::getStatement($sql, true, $shardID); Zotero_DB::queryFromStatement($stmt, array_merge($params, array($creatorID))); } } catch (Exception $e) { if (strpos($e->getMessage(), " too long") !== false) { if (strlen($this->firstName) > 255) { throw new Exception("=First name '" . mb_substr($this->firstName, 0, 50) . "…' too long"); } if (strlen($this->lastName) > 255) { if ($this->fieldMode == 1) { throw new Exception("=Last name '" . mb_substr($this->lastName, 0, 50) . "…' too long"); } else { throw new Exception("=Name '" . mb_substr($this->lastName, 0, 50) . "…' too long"); } } } throw $e; } // The client updates the mod time of associated items here, but // we don't, because either A) this is from syncing, where appropriate // mod times come from the client or B) the change is made through // $item->setCreator(), which updates the mod time. // // If the server started to make other independent creator changes, // linked items would need to be updated. Zotero_DB::commit(); Zotero_Creators::cachePrimaryData(array('id' => $creatorID, 'libraryID' => $this->libraryID, 'key' => $key, 'dateAdded' => $dateAdded, 'dateModified' => $dateModified, 'firstName' => $this->firstName, 'lastName' => $this->lastName, 'fieldMode' => $this->fieldMode)); } catch (Exception $e) { Zotero_DB::rollback(); throw $e; } // If successful, set values in object if (!$this->id) { $this->id = $creatorID; } if (!$this->key) { $this->key = $key; } $this->init(); if ($isNew) { Zotero_Creators::cache($this); Zotero_Creators::cacheLibraryKeyID($this->libraryID, $key, $creatorID); } // TODO: invalidate memcache? return $this->id; }
public static function unregister($mode, $addr, $port) { $sql = "DELETE FROM processorDaemons WHERE mode=? AND addr=INET_ATON(?) AND port=?"; Zotero_DB::query($sql, array($mode, $addr, $port)); }
private function logGroupLibraryRemoval() { $users = $this->getUsers(); $usersByShard = array(); foreach ($users as $userID) { $shardID = Zotero_Shards::getByUserID($userID); if (!isset($usersByShard[$shardID])) { $usersByShard[$shardID] = array(); } $usersByShard[$shardID][] = $userID; } foreach ($usersByShard as $shardID => $userIDs) { // Add to delete log for all group members $sql = "REPLACE INTO syncDeleteLogIDs (libraryID, objectType, id) VALUES "; $params = array(); $sets = array(); foreach ($userIDs as $userID) { $libraryID = Zotero_Users::getLibraryIDFromUserID($userID); $sets[] = "(?,?,?)"; $params = array_merge($params, array($libraryID, 'group', $this->id)); } $sql .= implode(",", $sets); Zotero_DB::query($sql, $params, $shardID); } }
public static function addCustomType($name) { if (self::getID($name)) { trigger_error("Item type '{$name}' already exists", E_USER_ERROR); } if (!preg_match('/^[a-z][^\\s0-9]+$/', $name)) { trigger_error("Invalid item type name '{$name}'", E_USER_ERROR); } // TODO: make sure user hasn't added too many already Zotero_DB::beginTransaction(); $sql = "SELECT NEXT_ID(creatorTypeID) FROM creatorTypes"; $creatorTypeID = Zotero_DB::valueQuery($sql); $sql = "INSERT INTO creatorTypes (?, ?, ?)"; Zotero_DB::query($sql, array($creatorTypeID, $name, 1)); Zotero_DB::commit(); return $creatorTypeID; }
public static function search($libraryID, $onlyTopLevel = false, $params = array(), $includeTrashed = false, $asKeys = false) { $rnd = "_" . uniqid($libraryID . "_"); if ($asKeys) { $results = array('keys' => array(), 'total' => 0); } else { $results = array('items' => array(), 'total' => 0); } $shardID = Zotero_Shards::getByLibraryID($libraryID); $itemIDs = array(); $keys = array(); $deleteTempTable = array(); // Pass a list of itemIDs, for when the initial search is done via SQL if (!empty($params['itemIDs'])) { $itemIDs = $params['itemIDs']; } if (!empty($params['itemKey'])) { $keys = explode(',', $params['itemKey']); } $titleSort = !empty($params['order']) && $params['order'] == 'title'; $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT " . ($asKeys ? "I.key" : "I.itemID") . " FROM items I "; $sqlParams = array($libraryID); if (!empty($params['q']) || $titleSort) { $titleFieldIDs = array_merge(array(Zotero_ItemFields::getID('title')), Zotero_ItemFields::getTypeFieldsFromBase('title')); $sql .= "LEFT JOIN itemData IDT ON (IDT.itemID=I.itemID AND IDT.fieldID IN (" . implode(',', $titleFieldIDs) . ")) "; } if (!empty($params['q'])) { $sql .= "LEFT JOIN itemCreators IC ON (IC.itemID=I.itemID)\n\t\t\t\t\tLEFT JOIN creators C ON (C.creatorID=IC.creatorID) "; } if ($onlyTopLevel || !empty($params['q']) || $titleSort) { $sql .= "LEFT JOIN itemNotes INo ON (INo.itemID=I.itemID) "; } if ($onlyTopLevel) { $sql .= "LEFT JOIN itemAttachments IA ON (IA.itemID=I.itemID) "; } if (!$includeTrashed) { $sql .= "LEFT JOIN deletedItems DI ON (DI.itemID=I.itemID) "; } if (!empty($params['order'])) { switch ($params['order']) { case 'title': case 'creator': $sql .= "LEFT JOIN itemSortFields ISF ON (ISF.itemID=I.itemID) "; break; case 'date': $dateFieldIDs = array_merge(array(Zotero_ItemFields::getID('date')), Zotero_ItemFields::getTypeFieldsFromBase('date')); $sql .= "LEFT JOIN itemData IDD ON (IDD.itemID=I.itemID AND IDD.fieldID IN (" . implode(',', $dateFieldIDs) . ")) "; break; case 'itemType': // Create temporary table to store item type names // // We use IF NOT EXISTS just to make sure there are // no problems with restoration from the binary log $sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS tmpItemTypeNames{$rnd}\n\t\t\t\t\t\t\t(itemTypeID SMALLINT UNSIGNED NOT NULL,\n\t\t\t\t\t\t\titemTypeName VARCHAR(255) NOT NULL,\n\t\t\t\t\t\t\tPRIMARY KEY (itemTypeID),\n\t\t\t\t\t\t\tINDEX (itemTypeName))"; Zotero_DB::query($sql2, false, $shardID); $deleteTempTable['tmpItemTypeNames'] = true; $types = Zotero_ItemTypes::getAll('en-US'); foreach ($types as $type) { $sql2 = "INSERT INTO tmpItemTypeNames{$rnd} VALUES (?, ?)"; Zotero_DB::query($sql2, array($type['id'], $type['localized']), $shardID); } // Join temp table to query $sql .= "JOIN tmpItemTypeNames{$rnd} TITN ON (TITN.itemTypeID=I.itemTypeID) "; break; case 'addedBy': $isGroup = Zotero_Libraries::getType($libraryID) == 'group'; if ($isGroup) { // Create temporary table to store usernames // // We use IF NOT EXISTS just to make sure there are // no problems with restoration from the binary log $sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS tmpCreatedByUsers{$rnd}\n\t\t\t\t\t\t\t\t(userID INT UNSIGNED NOT NULL,\n\t\t\t\t\t\t\t\tusername VARCHAR(255) NOT NULL,\n\t\t\t\t\t\t\t\tPRIMARY KEY (userID),\n\t\t\t\t\t\t\t\tINDEX (username))"; Zotero_DB::query($sql2, false, $shardID); $deleteTempTable['tmpCreatedByUsers'] = true; $sql2 = "SELECT DISTINCT createdByUserID FROM items\n\t\t\t\t\t\t\t\tJOIN groupItems USING (itemID) WHERE\n\t\t\t\t\t\t\t\tcreatedByUserID IS NOT NULL AND "; if ($itemIDs) { $sql2 .= "itemID IN (" . implode(', ', array_fill(0, sizeOf($itemIDs), '?')) . ") "; $createdByUserIDs = Zotero_DB::columnQuery($sql2, $itemIDs, $shardID); } else { $sql2 .= "libraryID=?"; $createdByUserIDs = Zotero_DB::columnQuery($sql2, $libraryID, $shardID); } // Populate temp table with usernames if ($createdByUserIDs) { $toAdd = array(); foreach ($createdByUserIDs as $createdByUserID) { $toAdd[] = array($createdByUserID, Zotero_Users::getUsername($createdByUserID)); } $sql2 = "INSERT IGNORE INTO tmpCreatedByUsers{$rnd} VALUES "; Zotero_DB::bulkInsert($sql2, $toAdd, 50, false, $shardID); // Join temp table to query $sql .= "JOIN groupItems GI ON (GI.itemID=I.itemID)\n\t\t\t\t\t\t\t\t\tJOIN tmpCreatedByUsers{$rnd} TCBU ON (TCBU.userID=GI.createdByUserID) "; } } break; } } $sql .= "WHERE I.libraryID=? "; if ($onlyTopLevel) { $sql .= "AND INo.sourceItemID IS NULL AND IA.sourceItemID IS NULL "; } 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; }
public static function search($libraryID, $onlyTopLevel = false, $params) { $results = array('results' => array(), 'total' => 0); $shardID = Zotero_Shards::getByLibraryID($libraryID); $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT "; if ($params['format'] == 'keys') { $sql .= "`key`"; } else { $sql .= "`key`, version"; } $sql .= " FROM collections WHERE libraryID=? "; $sqlParams = array($libraryID); if ($onlyTopLevel) { $sql .= "AND parentCollectionID IS NULL "; } // Pass a list of collectionIDs, for when the initial search is done via SQL $collectionIDs = !empty($params['collectionIDs']) ? $params['collectionIDs'] : array(); $collectionKeys = $params['collectionKey']; if ($collectionIDs) { $sql .= "AND collectionID IN (" . implode(', ', array_fill(0, sizeOf($collectionIDs), '?')) . ") "; $sqlParams = array_merge($sqlParams, $collectionIDs); } if ($collectionKeys) { $sql .= "AND `key` IN (" . implode(', ', array_fill(0, sizeOf($collectionKeys), '?')) . ") "; $sqlParams = array_merge($sqlParams, $collectionKeys); } if (!empty($params['q'])) { $sql .= "AND collectionName LIKE ? "; $sqlParams[] = '%' . $params['q'] . '%'; } if (!empty($params['since'])) { $sql .= "AND version > ? "; $sqlParams[] = $params['since']; } // TEMP: for sync transition if (!empty($params['sincetime'])) { $sql .= "AND serverDateModified >= FROM_UNIXTIME(?) "; $sqlParams[] = $params['sincetime']; } if (!empty($params['sort'])) { switch ($params['sort']) { case 'title': $orderSQL = 'collectionName'; break; case 'collectionKeyList': $orderSQL = "FIELD(`key`," . implode(',', array_fill(0, sizeOf($collectionKeys), '?')) . ")"; $sqlParams = array_merge($sqlParams, $collectionKeys); break; default: $orderSQL = $params['sort']; } $sql .= "ORDER BY {$orderSQL}"; if (!empty($params['direction'])) { $sql .= " {$params['direction']}"; } $sql .= ", "; } $sql .= "version " . (!empty($params['direction']) ? $params['direction'] : "ASC") . ", collectionID " . (!empty($params['direction']) ? $params['direction'] : "ASC") . " "; if (!empty($params['limit'])) { $sql .= "LIMIT ?, ?"; $sqlParams[] = $params['start'] ? $params['start'] : 0; $sqlParams[] = $params['limit']; } if ($params['format'] == 'keys') { $rows = Zotero_DB::columnQuery($sql, $sqlParams, $shardID); } else { $rows = Zotero_DB::query($sql, $sqlParams, $shardID); } $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID); if ($rows) { if ($params['format'] == 'keys') { $results['results'] = $rows; } else { if ($params['format'] == 'versions') { foreach ($rows as $row) { $results['results'][$row['key']] = $row['version']; } } else { $collections = []; foreach ($rows as $row) { $obj = self::getByLibraryAndKey($libraryID, $row['key']); $obj->setAvailableVersion($row['version']); $collections[] = $obj; } $results['results'] = $collections; } } } return $results; }
/** * Make sure we have a valid session */ private function sessionCheck() { if (empty($_REQUEST['sessionid'])) { $this->error(403, 'NO_SESSION_ID', "Session ID not provided"); } if (!preg_match('/^[a-f0-9]{32}$/', $_REQUEST['sessionid'])) { $this->error($this->apiVersion >= 9 ? 403 : 500, 'INVALID_SESSION_ID', "Invalid session ID"); } $sessionID = $_REQUEST['sessionid']; $session = Z_Core::$MC->get("syncSession_{$sessionID}"); $userID = $session ? $session['userID'] : null; // TEMP: can switch to just $session $ipAddress = isset($session['ipAddress']) ? $session['ipAddress'] : null; if (!$userID) { $sql = "SELECT userid, (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(timestamp)) AS age,\n\t\t\t\t\tINET_NTOA(ipAddress) AS ipAddress FROM sessions WHERE sessionID=?"; $session = Zotero_DB::rowQuery($sql, $sessionID); if (!$session) { $this->error($this->apiVersion >= 9 ? 403 : 500, 'INVALID_SESSION_ID', "Invalid session ID"); } if ($session['age'] > $this->sessionLifetime) { $this->error($this->apiVersion >= 9 ? 403 : 500, 'SESSION_TIMED_OUT', "Session timed out"); } $userID = $session['userid']; $ipAddress = $session['ipAddress']; } $updated = Z_Core::$MC->set("syncSession_{$sessionID}", array('sessionID' => $sessionID, 'userID' => $userID, 'ipAddress' => $ipAddress), $this->sessionLifetime - 1200); // Every 20 minutes, update the timestamp in the DB if (!Z_Core::$MC->get("syncSession_" . $sessionID . "_dbUpdated")) { $sql = "UPDATE sessions SET timestamp=NOW() WHERE sessionID=?"; Zotero_DB::query($sql, $sessionID); Z_Core::$MC->set("syncSession_" . $sessionID . "_dbUpdated", true, 1200); } $this->sessionID = $sessionID; $this->userID = $userID; $this->userLibraryID = Zotero_Users::getLibraryIDFromUserID($userID); $this->ipAddress = $ipAddress; }
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']); } }
/** * Handle S3 request * * Permission-checking provided by items() */ private function _handleFileRequest($item) { if (!$this->permissions->canAccess($this->objectLibraryID, 'files')) { $this->e403(); } $this->allowMethods(array('HEAD', 'GET', 'POST', 'PATCH')); if (!$item->isAttachment()) { $this->e400("Item is not an attachment"); } // File info for client sync // // Use of HEAD method is deprecated after 2.0.8/2.1b1 due to // compatibility problems with proxies and security software if ($this->method == 'HEAD' || $this->method == 'GET' && $this->fileMode == 'info') { $info = Zotero_S3::getLocalFileItemInfo($item); if (!$info) { $this->e404(); } /* header("Last-Modified: " . gmdate('r', $info['uploaded'])); header("Content-Type: " . $info['type']); */ header("Content-Length: " . $info['size']); header("ETag: " . $info['hash']); header("X-Zotero-Filename: " . $info['filename']); header("X-Zotero-Modification-Time: " . $info['mtime']); header("X-Zotero-Compressed: " . ($info['zip'] ? 'Yes' : 'No')); header_remove("X-Powered-By"); } else { if ($this->method == 'GET' || $this->method == 'POST' && $this->fileView) { if ($this->fileView) { $info = Zotero_S3::getLocalFileItemInfo($item); if (!$info) { $this->e404(); } // For zip files, redirect to files domain if ($info['zip']) { $url = Zotero_Attachments::getTemporaryURL($item, !empty($_GET['int'])); if (!$url) { $this->e500(); } header("Location: {$url}"); exit; } } // For single files, redirect to S3 $url = Zotero_S3::getDownloadURL($item, 60); if (!$url) { $this->e404(); } Zotero_S3::logDownload($item, $this->userID, IPAddress::getIP()); header("Location: {$url}"); exit; } else { if ($this->method == 'POST' || $this->method == 'PATCH') { if (!$item->isImportedAttachment()) { $this->e400("Cannot upload file for linked file/URL attachment item"); } $libraryID = $item->libraryID; $type = Zotero_Libraries::getType($libraryID); if ($type == 'group') { $groupID = Zotero_Groups::getGroupIDFromLibraryID($libraryID); $group = Zotero_Groups::get($groupID); if (!$group->userCanEditFiles($this->userID)) { $this->e403("You do not have file editing access"); } } else { $group = null; } // If not the client, require If-Match or If-None-Match if (!$this->httpAuth) { if (empty($_SERVER['HTTP_IF_MATCH']) && empty($_SERVER['HTTP_IF_NONE_MATCH'])) { $this->e428("If-Match/If-None-Match header not provided"); } if (!empty($_SERVER['HTTP_IF_MATCH'])) { if (!preg_match('/^"?([a-f0-9]{32})"?$/', $_SERVER['HTTP_IF_MATCH'], $matches)) { $this->e400("Invalid ETag in If-Match header"); } if (!$item->attachmentStorageHash) { $info = Zotero_S3::getLocalFileItemInfo($item); $this->e412("ETag set but file does not exist"); } if ($item->attachmentStorageHash != $matches[1]) { $this->e412("ETag does not match current version of file"); } } else { if ($_SERVER['HTTP_IF_NONE_MATCH'] != "*") { $this->e400("Invalid value for If-None-Match header"); } if ($this->attachmentStorageHash) { $this->e412("If-None-Match: * set but file exists"); } } } // // Upload authorization // if (!isset($_POST['update']) && !isset($_REQUEST['upload'])) { $info = new Zotero_StorageFileInfo(); // Validate upload metadata if (empty($_REQUEST['md5'])) { $this->e400('MD5 hash not provided'); } $info->hash = $_REQUEST['md5']; if (!preg_match('/[abcdefg0-9]{32}/', $info->hash)) { $this->e400('Invalid MD5 hash'); } if (empty($_REQUEST['mtime'])) { $this->e400('File modification time not provided'); } $info->mtime = $_REQUEST['mtime']; if (!isset($_REQUEST['filename']) || $_REQUEST['filename'] === "") { $this->e400('File name not provided'); } $info->filename = $_REQUEST['filename']; if (!isset($_REQUEST['filesize'])) { $this->e400('File size not provided'); } $info->size = $_REQUEST['filesize']; if (!is_numeric($info->size)) { $this->e400("Invalid file size"); } $info->contentType = isset($_REQUEST['contentType']) ? $_REQUEST['contentType'] : ""; if (!preg_match("/^[a-zA-Z0-9\\-\\/]+\$/", $info->contentType)) { $info->contentType = ""; } $info->charset = isset($_REQUEST['charset']) ? $_REQUEST['charset'] : ""; if (!preg_match("/^[a-zA-Z0-9\\-]+\$/", $info->charset)) { $info->charset = ""; } $contentTypeHeader = $info->contentType . ($info->contentType && $info->charset ? "; charset=" . $info->charset : ""); $info->zip = !empty($_REQUEST['zip']); // Reject file if it would put account over quota if ($group) { $quota = Zotero_S3::getEffectiveUserQuota($group->ownerUserID); $usage = Zotero_S3::getUserUsage($group->ownerUserID); } else { $quota = Zotero_S3::getEffectiveUserQuota($this->objectUserID); $usage = Zotero_S3::getUserUsage($this->objectUserID); } $total = $usage['total']; $fileSizeMB = round($info->size / 1024 / 1024, 1); if ($total + $fileSizeMB > $quota) { $this->e413("File would exceed quota ({$total} + {$fileSizeMB} > {$quota})"); } Zotero_DB::query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); Zotero_DB::beginTransaction(); // See if file exists with this filename $localInfo = Zotero_S3::getLocalFileInfo($info); if ($localInfo) { $storageFileID = $localInfo['storageFileID']; // Verify file size if ($localInfo['size'] != $info->size) { throw new Exception("Specified file size incorrect for existing file " . $info->hash . "/" . $info->filename . " ({$localInfo['size']} != {$info->size})"); } } else { $oldStorageFileID = Zotero_S3::getFileByHash($info->hash, $info->zip); if ($oldStorageFileID) { // Verify file size $localInfo = Zotero_S3::getFileInfoByID($oldStorageFileID); if ($localInfo['size'] != $info->size) { throw new Exception("Specified file size incorrect for duplicated file " . $info->hash . "/" . $info->filename . " ({$localInfo['size']} != {$info->size})"); } // Create new file on S3 with new name $storageFileID = Zotero_S3::duplicateFile($oldStorageFileID, $info->filename, $info->zip, $contentTypeHeader); if (!$storageFileID) { $this->e500("File duplication failed"); } } } // If we already have a file, add/update storageFileItems row and stop if (!empty($storageFileID)) { Zotero_S3::updateFileItemInfo($item, $storageFileID, $info); Zotero_DB::commit(); if ($this->httpAuth) { header('Content-Type: application/xml'); echo "<exists/>"; } else { header('Content-Type: application/json'); echo json_encode(array('exists' => 1)); } exit; } Zotero_DB::commit(); // Add request to upload queue $uploadKey = Zotero_S3::queueUpload($this->userID, $info); // User over queue limit if (!$uploadKey) { header('Retry-After: ' . Zotero_S3::$uploadQueueTimeout); if ($this->httpAuth) { $this->e413("Too many queued uploads"); } else { $this->e429("Too many queued uploads"); } } // Output XML for client requests (which use HTTP Auth) if ($this->httpAuth) { $params = Zotero_S3::generateUploadPOSTParams($item, $info, true); header('Content-Type: application/xml'); $xml = new SimpleXMLElement('<upload/>'); $xml->url = Zotero_S3::getUploadBaseURL(); $xml->key = $uploadKey; foreach ($params as $key => $val) { $xml->params->{$key} = $val; } echo $xml->asXML(); } else { if (!empty($_REQUEST['params']) && $_REQUEST['params'] == "1") { $params = array("url" => Zotero_S3::getUploadBaseURL(), "params" => array()); foreach (Zotero_S3::generateUploadPOSTParams($item, $info) as $key => $val) { $params['params'][$key] = $val; } } else { $params = Zotero_S3::getUploadPOSTData($item, $info); } $params['uploadKey'] = $uploadKey; header('Content-Type: application/json'); echo json_encode($params); } exit; } // // API partial upload and post-upload file registration // if (isset($_REQUEST['upload'])) { $uploadKey = $_REQUEST['upload']; if (!$uploadKey) { $this->e400("Upload key not provided"); } $info = Zotero_S3::getUploadInfo($uploadKey); if (!$info) { $this->e400("Upload key not found"); } // Partial upload if ($this->method == 'PATCH') { if (empty($_REQUEST['algorithm'])) { throw new Exception("Algorithm not specified", Z_ERROR_INVALID_INPUT); } $storageFileID = Zotero_S3::patchFile($item, $info, $_REQUEST['algorithm'], $this->body); } else { $remoteInfo = Zotero_S3::getRemoteFileInfo($info); if (!$remoteInfo) { error_log("Remote file {$info->hash}/{$info->filename} not found"); $this->e400("Remote file not found"); } if ($remoteInfo['size'] != $info->size) { error_log("Uploaded file size does not match ({$remoteInfo['size']} != {$info->size}) for file {$info->hash}/{$info->filename}"); } } // Set an automatic shared lock in getLocalFileInfo() to prevent // two simultaneous transactions from adding a file Zotero_DB::query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); Zotero_DB::beginTransaction(); if (!isset($storageFileID)) { // Check if file already exists, which can happen if two identical // files are uploaded simultaneously $fileInfo = Zotero_S3::getLocalFileInfo($info); if ($fileInfo) { $storageFileID = $fileInfo['storageFileID']; } else { $storageFileID = Zotero_S3::addFile($info); } } Zotero_S3::updateFileItemInfo($item, $storageFileID, $info); Zotero_S3::logUpload($this->userID, $item, $uploadKey, IPAddress::getIP()); Zotero_DB::commit(); header("HTTP/1.1 204 No Content"); exit; } // // Client post-upload file registration // if (isset($_POST['update'])) { $this->allowMethods(array('POST')); if (empty($_POST['mtime'])) { throw new Exception('File modification time not provided'); } $uploadKey = $_POST['update']; $info = Zotero_S3::getUploadInfo($uploadKey); if (!$info) { $this->e400("Upload key not found"); } $remoteInfo = Zotero_S3::getRemoteFileInfo($info); if (!$remoteInfo) { $this->e400("Remote file not found"); } if (!isset($info->size)) { throw new Exception("Size information not available"); } $info->mtime = $_POST['mtime']; // Set an automatic shared lock in getLocalFileInfo() to prevent // two simultaneous transactions from adding a file Zotero_DB::query("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE"); Zotero_DB::beginTransaction(); // Check if file already exists, which can happen if two identical // files are uploaded simultaneously $fileInfo = Zotero_S3::getLocalFileInfo($info); if ($fileInfo) { $storageFileID = $fileInfo['storageFileID']; } else { $storageFileID = Zotero_S3::addFile($info); } Zotero_S3::updateFileItemInfo($item, $storageFileID, $info); Zotero_S3::logUpload($this->userID, $item, $uploadKey, IPAddress::getIP()); Zotero_DB::commit(); header("HTTP/1.1 204 No Content"); exit; } } } } exit; }
public function getTagItemCounts() { $sql = "SELECT tagID, COUNT(*) AS numItems FROM tags JOIN itemTags USING (tagID)\n\t\t\t\tJOIN collectionItems USING (itemID) WHERE collectionID=? GROUP BY tagID"; $rows = Zotero_DB::query($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID)); if (!$rows) { return false; } $counts = array(); foreach ($rows as $row) { $counts[$row['tagID']] = $row['numItems']; } return $counts; }
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 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(); }
/** * Used for integration tests * * Valid only on testing site */ public function testSetup() { if (!$this->permissions->isSuper()) { $this->e404(); } if (!Z_ENV_TESTING_SITE) { $this->e404(); } $this->allowMethods(['POST']); if (empty($_GET['u'])) { throw new Exception("User not provided (e.g., ?u=1)"); } $userID = $_GET['u']; // Clear keys $keys = Zotero_Keys::getUserKeys($userID); foreach ($keys as $keyObj) { $keyObj->erase(); } $keys = Zotero_Keys::getUserKeys($userID); if ($keys) { throw new Exception("Keys still exist"); } // Create new key $keyObj = new Zotero_Key(); $keyObj->userID = $userID; $keyObj->name = "Tests Key"; $libraryID = Zotero_Users::getLibraryIDFromUserID($userID); $keyObj->setPermission($libraryID, 'library', true); $keyObj->setPermission($libraryID, 'notes', true); $keyObj->setPermission($libraryID, 'write', true); $keyObj->setPermission(0, 'group', true); $keyObj->setPermission(0, 'write', true); $keyObj->save(); $key = $keyObj->key; Zotero_DB::beginTransaction(); // Clear data Zotero_Users::clearAllData($userID); // Delete publications library, so we can test auto-creating it $publicationsLibraryID = Zotero_Users::getLibraryIDFromUserID($userID, 'publications'); if ($publicationsLibraryID) { // Delete user publications shard library $sql = "DELETE FROM shardLibraries WHERE libraryID=?"; Zotero_DB::query($sql, $publicationsLibraryID, Zotero_Shards::getByUserID($userID)); // Delete user publications library $sql = "DELETE FROM libraries WHERE libraryID=?"; Zotero_DB::query($sql, $publicationsLibraryID); Z_Core::$MC->delete('userPublicationsLibraryID_' . $userID); Z_Core::$MC->delete('libraryUserID_' . $publicationsLibraryID); } Zotero_DB::commit(); echo json_encode(["apiKey" => $key]); $this->end(); }
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']); } }
public static function clearAllData($libraryID) { if (empty($libraryID)) { throw new Exception("libraryID not provided"); } Zotero_DB::beginTransaction(); $tables = array('collections', 'creators', 'items', 'relations', 'savedSearches', 'tags', 'syncDeleteLogIDs', 'syncDeleteLogKeys', 'settings'); $shardID = Zotero_Shards::getByLibraryID($libraryID); self::deleteCachedData($libraryID); // Because of the foreign key constraint on the itemID, delete MySQL full-text rows // first, and then clear from Elasticsearch below Zotero_FullText::deleteByLibraryMySQL($libraryID); foreach ($tables as $table) { // Delete notes and attachments first (since they may be child items) if ($table == 'items') { $sql = "DELETE FROM {$table} WHERE libraryID=? AND itemTypeID IN (1,14)"; Zotero_DB::query($sql, $libraryID, $shardID); } $sql = "DELETE FROM {$table} WHERE libraryID=?"; Zotero_DB::query($sql, $libraryID, $shardID); } Zotero_FullText::deleteByLibrary($libraryID); self::updateVersion($libraryID); self::updateTimestamps($libraryID); Zotero_Notifier::trigger("clear", "library", $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 save($full = false) { if (!$this->libraryID) { trigger_error("Library ID must be set before saving", E_USER_ERROR); } Zotero_Tags::editCheck($this); if (!$this->changed) { Z_Core::debug("Tag {$this->id} has not changed"); return false; } $shardID = Zotero_Shards::getByLibraryID($this->libraryID); Zotero_DB::beginTransaction(); try { $tagID = $this->id ? $this->id : Zotero_ID::get('tags'); $isNew = !$this->id; Z_Core::debug("Saving tag {$tagID}"); $key = $this->key ? $this->key : $this->generateKey(); $timestamp = Zotero_DB::getTransactionTimestamp(); $dateAdded = $this->dateAdded ? $this->dateAdded : $timestamp; $dateModified = $this->dateModified ? $this->dateModified : $timestamp; $fields = "name=?, `type`=?, dateAdded=?, dateModified=?,\n\t\t\t\tlibraryID=?, `key`=?, serverDateModified=?"; $params = array($this->name, $this->type ? $this->type : 0, $dateAdded, $dateModified, $this->libraryID, $key, $timestamp); try { if ($isNew) { $sql = "INSERT INTO tags SET tagID=?, {$fields}"; $stmt = Zotero_DB::getStatement($sql, true, $shardID); Zotero_DB::queryFromStatement($stmt, array_merge(array($tagID), $params)); // Remove from delete log if it's there $sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='tag' AND `key`=?"; Zotero_DB::query($sql, array($this->libraryID, $key), $shardID); } else { $sql = "UPDATE tags SET {$fields} WHERE tagID=?"; $stmt = Zotero_DB::getStatement($sql, true, Zotero_Shards::getByLibraryID($this->libraryID)); Zotero_DB::queryFromStatement($stmt, array_merge($params, array($tagID))); } } catch (Exception $e) { // If an incoming tag is the same as an existing tag, but with a different key, // then delete the old tag and add its linked items to the new tag if (preg_match("/Duplicate entry .+ for key 'uniqueTags'/", $e->getMessage())) { // GET existing tag $existing = Zotero_Tags::getIDs($this->libraryID, $this->name); if (!$existing) { throw new Exception("Existing tag not found"); } foreach ($existing as $id) { $tag = Zotero_Tags::get($this->libraryID, $id, true); if ($tag->__get('type') == $this->type) { $linked = $tag->getLinkedItems(true); Zotero_Tags::delete($this->libraryID, $tag->key); break; } } // Save again if ($isNew) { $sql = "INSERT INTO tags SET tagID=?, {$fields}"; $stmt = Zotero_DB::getStatement($sql, true, $shardID); Zotero_DB::queryFromStatement($stmt, array_merge(array($tagID), $params)); // Remove from delete log if it's there $sql = "DELETE FROM syncDeleteLogKeys WHERE libraryID=? AND objectType='tag' AND `key`=?"; Zotero_DB::query($sql, array($this->libraryID, $key), $shardID); } else { $sql = "UPDATE tags SET {$fields} WHERE tagID=?"; $stmt = Zotero_DB::getStatement($sql, true, Zotero_Shards::getByLibraryID($this->libraryID)); Zotero_DB::queryFromStatement($stmt, array_merge($params, array($tagID))); } $new = array_unique(array_merge($linked, $this->getLinkedItems(true))); $this->setLinkedItems($new); } else { throw $e; } } // Linked items if ($full || !empty($this->changed['linkedItems'])) { $removed = array(); $newids = array(); $currentIDs = $this->getLinkedItems(true); if (!$currentIDs) { $currentIDs = array(); } if ($full) { $sql = "SELECT itemID FROM itemTags WHERE tagID=?"; $stmt = Zotero_DB::getStatement($sql, true, $shardID); $dbItemIDs = Zotero_DB::columnQueryFromStatement($stmt, $tagID); if ($dbItemIDs) { $removed = array_diff($dbItemIDs, $currentIDs); $newids = array_diff($currentIDs, $dbItemIDs); } else { $newids = $currentIDs; } } else { if ($this->previousData['linkedItems']) { $removed = array_diff($this->previousData['linkedItems'], $currentIDs); $newids = array_diff($currentIDs, $this->previousData['linkedItems']); } else { $newids = $currentIDs; } } if ($removed) { $sql = "DELETE FROM itemTags WHERE tagID=? AND itemID IN ("; $q = array_fill(0, sizeOf($removed), '?'); $sql .= implode(', ', $q) . ")"; Zotero_DB::query($sql, array_merge(array($this->id), $removed), $shardID); } if ($newids) { $newids = array_values($newids); $sql = "INSERT INTO itemTags (tagID, itemID) VALUES "; $maxInsertGroups = 50; Zotero_DB::bulkInsert($sql, $newids, $maxInsertGroups, $tagID, $shardID); } //Zotero.Notifier.trigger('add', 'collection-item', $this->id . '-' . $itemID); } Zotero_DB::commit(); Zotero_Tags::cachePrimaryData(array('id' => $tagID, 'libraryID' => $this->libraryID, 'key' => $key, 'name' => $this->name, 'type' => $this->type ? $this->type : 0, 'dateAdded' => $dateAdded, 'dateModified' => $dateModified)); } catch (Exception $e) { Zotero_DB::rollback(); throw $e; } // If successful, set values in object if (!$this->id) { $this->id = $tagID; } if (!$this->key) { $this->key = $key; } $this->init(); if ($isNew) { Zotero_Tags::cache($this); Zotero_Tags::cacheLibraryKeyID($this->libraryID, $key, $tagID); } return $this->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; }
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 addCustomType($name) { if (self::getID($name)) { throw new Exception("Item type '{$name}' already exists"); } if (!preg_match('/^[a-z][^\\s0-9]+$/', $name)) { throw new Exception("Invalid item type name '{$name}'"); } // TODO: make sure user hasn't added too many already throw new Exception("Unimplemented"); // TODO: add to cache Zotero_DB::beginTransaction(); $sql = "SELECT NEXT_ID(itemTypeID) FROM itemTypes"; $itemTypeID = Zotero_DB::valueQuery($sql); $sql = "INSERT INTO itemTypes (?, ?, ?)"; Zotero_DB::query($sql, array($itemTypeID, $name, 1)); Zotero_DB::commit(); return $itemTypeID; }
public static function updateVersions($items, $userID = false) { $libraryShards = array(); $libraryIsGroup = array(); $shardItemIDs = array(); $shardGroupItemIDs = array(); $libraryItems = array(); foreach ($items as $item) { $libraryID = $item->libraryID; $itemID = $item->id; // Index items by shard if (isset($libraryShards[$libraryID])) { $shardID = $libraryShards[$libraryID]; $shardItemIDs[$shardID][] = $itemID; } else { $shardID = Zotero_Shards::getByLibraryID($libraryID); $libraryShards[$libraryID] = $shardID; $shardItemIDs[$shardID] = array($itemID); } // Separate out group items by shard if (!isset($libraryIsGroup[$libraryID])) { $libraryIsGroup[$libraryID] = Zotero_Libraries::getType($libraryID) == 'group'; } if ($libraryIsGroup[$libraryID]) { if (isset($shardGroupItemIDs[$shardID])) { $shardGroupItemIDs[$shardID][] = $itemID; } else { $shardGroupItemIDs[$shardID] = array($itemID); } } // Index items by library if (!isset($libraryItems[$libraryID])) { $libraryItems[$libraryID] = array(); } $libraryItems[$libraryID][] = $item; } Zotero_DB::beginTransaction(); foreach ($shardItemIDs as $shardID => $itemIDs) { // Group item data if ($userID && isset($shardGroupItemIDs[$shardID])) { $sql = "UPDATE groupItems SET lastModifiedByUserID=? " . "WHERE itemID IN (" . implode(',', array_fill(0, sizeOf($shardGroupItemIDs[$shardID]), '?')) . ")"; Zotero_DB::query($sql, array_merge(array($userID), $shardGroupItemIDs[$shardID]), $shardID); } } foreach ($libraryItems as $libraryID => $items) { $itemIDs = array(); foreach ($items as $item) { $itemIDs[] = $item->id; } $version = Zotero_Libraries::getUpdatedVersion($libraryID); $sql = "UPDATE items SET version=? WHERE itemID IN " . "(" . implode(',', array_fill(0, sizeOf($itemIDs), '?')) . ")"; Zotero_DB::query($sql, array_merge(array($version), $itemIDs), $shardID); } Zotero_DB::commit(); foreach ($libraryItems as $libraryID => $items) { foreach ($items as $item) { $item->reload(); } $libraryKeys = array_map(function ($item) use($libraryID) { return $libraryID . "/" . $item->key; }, $items); Zotero_Notifier::trigger('modify', 'item', $libraryKeys); } }
/** * 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; }