PHP Zotero_DB::valueQuery方法代码示例

 public static function getAllAdvanced($libraryID, $onlyTopLevel = false, $params)
 {
     $results = array('collections' => array(), 'total' => 0);
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     $sql = "SELECT SQL_CALC_FOUND_ROWS collectionID FROM collections\n\t\t\t\tWHERE libraryID=? ";
     if ($onlyTopLevel) {
         $sql .= "AND parentCollectionID IS NULL ";
     }
     if (!empty($params['order'])) {
         $order = $params['order'];
         if ($order == 'title') {
             $order = 'collectionName';
         }
         $sql .= "ORDER BY {$order} ";
         if (!empty($params['sort'])) {
             $sql .= $params['sort'] . " ";
         }
     }
     $sqlParams = array($libraryID);
     if (!empty($params['limit'])) {
         $sql .= "LIMIT ?, ?";
         $sqlParams[] = $params['start'] ? $params['start'] : 0;
         $sqlParams[] = $params['limit'];
     }
     $ids = Zotero_DB::columnQuery($sql, $sqlParams, $shardID);
     if ($ids) {
         $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID);
         $collections = array();
         foreach ($ids as $id) {
             $collections[] = self::get($libraryID, $id);
         }
         $results['collections'] = $collections;
     }
     return $results;
 }

 /**
  * Check if search exists in the database
  *
  * @return	bool			TRUE if the item exists, FALSE if not
  */
 public function exists()
 {
     if (!$this->id) {
         trigger_error('$this->id not set');
     }
     $sql = "SELECT COUNT(*) FROM savedSearches WHERE searchID=?";
     return !!Zotero_DB::valueQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID));
 }

 public static function getName($charsetOrCharsetID)
 {
     if (isset(self::$charsets[$charsetOrCharsetID])) {
         return self::$charsets[$charsetOrCharsetID];
     }
     $sql = "(SELECT charset FROM charsets WHERE charsetID=?) UNION\n\t\t\t\t(SELECT charset FROM charsets WHERE charset=?) LIMIT 1";
     $charset = Zotero_DB::valueQuery($sql, array($charsetOrCharsetID, $charsetOrCharsetID));
     self::$charsets[$charsetOrCharsetID] = $charset;
     return $charset;
 }

 public static function generate()
 {
     $tries = 5;
     while ($tries > 0) {
         $str = Zotero_Utilities::randomString(24, 'mixed');
         $sql = "SELECT COUNT(*) FROM `keys` WHERE `key`=?";
         if (Zotero_DB::valueQuery($sql, $str)) {
             $tries--;
             continue;
         }
         return $str;
     }
     throw new Exception("Unique key could not be generated");
 }

 public static function get($libraryID, $creatorID, $skipCheck = false)
 {
     if (!$libraryID) {
         throw new Exception("Library ID not set");
     }
     if (!$creatorID) {
         throw new Exception("Creator ID not set");
     }
     if (!empty(self::$creatorsByID[$creatorID])) {
         return self::$creatorsByID[$creatorID];
     }
     if (!$skipCheck) {
         $sql = 'SELECT COUNT(*) FROM creators WHERE creatorID=?';
         $result = Zotero_DB::valueQuery($sql, $creatorID, Zotero_Shards::getByLibraryID($libraryID));
         if (!$result) {
             return false;
         }
     }
     $creator = new Zotero_Creator();
     $creator->libraryID = $libraryID;
     $creator->id = $creatorID;
     self::$creatorsByID[$creatorID] = $creator;
     return self::$creatorsByID[$creatorID];
 }

 private static function countDeletedObjectKeys($userID, $timestamp, $updatedLibraryIDs)
 {
     /*
     $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;
     }
     */
     $shardLibraryIDs = array();
     // Personal library
     $shardID = Zotero_Shards::getByUserID($userID);
     $libraryID = Zotero_Users::getLibraryIDFromUserID($userID);
     if (in_array($libraryID, $updatedLibraryIDs)) {
         $shardLibraryIDs[$shardID] = array($libraryID);
     }
     // Group libraries
     $groupIDs = Zotero_Groups::getUserGroups($userID);
     if ($groupIDs) {
         // Separate groups into shards for querying
         foreach ($groupIDs as $groupID) {
             $libraryID = Zotero_Groups::getLibraryIDFromGroupID($groupID);
             // If library hasn't changed, skip
             if (!in_array($libraryID, $updatedLibraryIDs)) {
                 continue;
             }
             $shardID = Zotero_Shards::getByLibraryID($libraryID);
             if (!isset($shardLibraryIDs[$shardID])) {
                 $shardLibraryIDs[$shardID] = array();
             }
             $shardLibraryIDs[$shardID][] = $libraryID;
         }
     }
     // Send query at each shard
     $rows = array();
     $count = 0;
     foreach ($shardLibraryIDs as $shardID => $libraryIDs) {
         $sql = "SELECT COUNT(*) FROM syncDeleteLogKeys WHERE libraryID IN (" . implode(', ', array_fill(0, sizeOf($libraryIDs), '?')) . ") " . "AND objectType != 'tagName'";
         $params = $libraryIDs;
         if ($timestamp) {
             $sql .= " AND timestamp >= FROM_UNIXTIME(?)";
             $params[] = $timestamp;
         }
         $count += Zotero_DB::valueQuery($sql, $params, $shardID);
     }
     return $count;
 }

 public static function getLastStorageSync($libraryID)
 {
     $sql = "SELECT UNIX_TIMESTAMP(serverDateModified) AS time FROM items\n\t\t\t\tJOIN storageFileItems USING (itemID) WHERE libraryID=?\n\t\t\t\tORDER BY time DESC LIMIT 1";
     return Zotero_DB::valueQuery($sql, $libraryID, Zotero_Shards::getByLibraryID($libraryID));
 }

 public function numItems($includeDeleted = false)
 {
     $sql = "SELECT COUNT(*) FROM collectionItems ";
     if (!$includeDeleted) {
         $sql .= "LEFT JOIN deletedItems DI USING (itemID)";
     }
     $sql .= "WHERE collectionID=?";
     if (!$includeDeleted) {
         $sql .= " AND DI.itemID IS NULL";
     }
     return Zotero_DB::valueQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID));
 }

 public static function isLocked($libraryID)
 {
     $sql = "SELECT COUNT(*) FROM syncUploadQueueLocks WHERE libraryID=?";
     if (Zotero_DB::valueQuery($sql, $libraryID)) {
         return true;
     }
     $sql = "SELECT COUNT(*) FROM syncProcessLocks WHERE libraryID=?";
     return !!Zotero_DB::valueQuery($sql, $libraryID);
 }

	public function numTags() {
		if (!$this->id) {
			return 0;
		}
		
		$sql = "SELECT COUNT(*) FROM itemTags WHERE itemID=?";
		return (int) Zotero_DB::valueQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID));
	}

 private static function checkForLibrary($libraryID, $shardID)
 {
     $tables = array('collections', 'creators', 'items', 'relations', 'savedSearches', 'shardLibraries', 'tags', 'syncDeleteLogIDs', 'syncDeleteLogKeys');
     foreach ($tables as $table) {
         $sql = "SELECT COUNT(*) FROM {$table} WHERE libraryID=?";
         if (Zotero_DB::valueQuery($sql, $libraryID, $shardID)) {
             return true;
         }
     }
     return false;
 }

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

//.........这里部分代码省略.........
         $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 getLastStorageSync($userID)
 {
     $lastModified = false;
     $libraryIDs = Zotero_Libraries::getUserLibraries($userID);
     $shardIDs = Zotero_Shards::getUserShards($userID);
     foreach ($shardIDs as $shardID) {
         $sql = "SELECT UNIX_TIMESTAMP(serverDateModified) AS time FROM items\n\t\t\t\t\tJOIN storageFileItems USING (itemID)\n\t\t\t\t\tWHERE libraryID IN (" . implode(', ', array_fill(0, sizeOf($libraryIDs), '?')) . ")\n\t\t\t\t\tORDER BY time DESC LIMIT 1";
         $time = Zotero_DB::valueQuery($sql, $libraryIDs, $shardID);
         if ($time > $lastModified) {
             $lastModified = $time;
         }
     }
     return $lastModified;
 }

 public static function getAllAdvanced($libraryID, $params)
 {
     $results = array('objects' => array(), 'total' => 0);
     $sql = "SELECT SQL_CALC_FOUND_ROWS tagID FROM tags ";
     if (!empty($params['order']) && $params['order'] == 'numItems') {
         $sql .= " LEFT JOIN itemTags USING (tagID)";
     }
     $sql .= "WHERE libraryID=? ";
     $sqlParams = array($libraryID);
     if (!empty($params['q'])) {
         if (!is_array($params['q'])) {
             $params['q'] = array($params['q']);
         }
         foreach ($params['q'] as $q) {
             $sql .= "AND name LIKE ? ";
             $sqlParams[] = "%{$q}%";
         }
     }
     $tagTypeSets = Zotero_API::getSearchParamValues($params, 'tagType');
     if ($tagTypeSets) {
         $positives = array();
         $negatives = array();
         foreach ($tagTypeSets as $set) {
             if ($set['negation']) {
                 $negatives = array_merge($negatives, $set['values']);
             } else {
                 $positives = array_merge($positives, $set['values']);
             }
         }
         if ($positives) {
             $sql .= "AND type IN (" . implode(',', array_fill(0, sizeOf($positives), '?')) . ") ";
             $sqlParams = array_merge($sqlParams, $positives);
         }
         if ($negatives) {
             $sql .= "AND type NOT IN (" . implode(',', array_fill(0, sizeOf($negatives), '?')) . ") ";
             $sqlParams = array_merge($sqlParams, $negatives);
         }
     }
     if (!empty($params['order'])) {
         $order = $params['order'];
         if ($order == 'title') {
             // Force a case-insensitive sort
             $sql .= "ORDER BY name COLLATE utf8_unicode_ci ";
         } else {
             if ($order == 'numItems') {
                 $sql .= "GROUP BY tags.tagID ORDER BY COUNT(tags.tagID)";
             } else {
                 $sql .= "ORDER BY {$order} ";
             }
         }
         if (!empty($params['sort'])) {
             $sql .= " " . $params['sort'] . " ";
         }
     }
     if (!empty($params['limit'])) {
         $sql .= "LIMIT ?, ?";
         $sqlParams[] = $params['start'] ? $params['start'] : 0;
         $sqlParams[] = $params['limit'];
     }
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     $ids = Zotero_DB::columnQuery($sql, $sqlParams, $shardID);
     if ($ids) {
         $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID);
         $tags = array();
         foreach ($ids as $id) {
             $tags[] = Zotero_Tags::get($libraryID, $id);
         }
         $results['objects'] = $tags;
     }
     return $results;
 }