PHP Zotero_DB::columnQuery方法代码示例

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

 public static function search($libraryID, $params)
 {
     // Default empty library
     if ($libraryID === 0) {
         return [];
     }
     $sql = "SELECT name FROM settings WHERE libraryID=?";
     $params = array($libraryID);
     if (!empty($params['since'])) {
         $sql .= "AND version > ? ";
         $sqlParams[] = $params['since'];
     }
     // TEMP: for sync transition
     if (!empty($params['sincetime'])) {
         $sql .= "AND lastUpdated >= FROM_UNIXTIME(?) ";
         $sqlParams[] = $params['sincetime'];
     }
     $names = Zotero_DB::columnQuery($sql, $params, Zotero_Shards::getByLibraryID($libraryID));
     if (!$names) {
         $names = array();
     }
     $settings = array();
     foreach ($names as $name) {
         $setting = new Zotero_Setting();
         $setting->libraryID = $libraryID;
         $setting->name = $name;
         $settings[] = $setting;
     }
     return $settings;
 }

 public static function getUserKeys($userID)
 {
     $keys = array();
     $keyIDs = Zotero_DB::columnQuery("SELECT keyID FROM `keys` WHERE userID=?", $userID);
     if ($keyIDs) {
         foreach ($keyIDs as $keyID) {
             $keyObj = new Zotero_Key();
             $keyObj->id = $keyID;
             $keys[] = $keyObj;
         }
     }
     return $keys;
 }

 public static function getCreatorsWithData($libraryID, $creator, $sortByItemCountDesc = false)
 {
     $sql = "SELECT creatorID FROM creators ";
     if ($sortByItemCountDesc) {
         $sql .= "LEFT JOIN itemCreators USING (creatorID) ";
     }
     $sql .= "WHERE libraryID=? AND firstName COLLATE utf8_bin = ? " . "AND lastName COLLATE utf8_bin = ? AND fieldMode=?";
     if ($sortByItemCountDesc) {
         $sql .= " ORDER BY IFNULL(COUNT(*), 0) DESC";
     }
     $ids = Zotero_DB::columnQuery($sql, array($libraryID, $creator->firstName, $creator->lastName, $creator->fieldMode), Zotero_Shards::getByLibraryID($libraryID));
     return $ids;
 }

 public static function getUserKeysWithLibrary($userID, $libraryID)
 {
     $libraryType = Zotero_Libraries::getType($libraryID);
     $sql = "SELECT keyID FROM `keys` JOIN keyPermissions USING (keyID) " . "WHERE userID=? AND (libraryID=?";
     // If group library, include keys with access to all groups
     if ($libraryType == 'group') {
         $sql .= " OR libraryID=0";
     }
     $sql .= ") AND permission='library' AND granted=1";
     $keyIDs = Zotero_DB::columnQuery($sql, [$userID, $libraryID]);
     $keys = [];
     if ($keyIDs) {
         foreach ($keyIDs as $keyID) {
             $keyObj = new Zotero_Key();
             $keyObj->id = $keyID;
             $keys[] = $keyObj;
         }
     }
     return $keys;
 }

 public static function purgeUnusedFiles()
 {
     throw new Exception("Now sharded");
     self::requireLibrary();
     // Get all used files and files that were last deleted more than a month ago
     $sql = "SELECT MD5(CONCAT(hash, filename, zip)) AS file FROM storageFiles\n\t\t\t\t\tJOIN storageFileItems USING (storageFileID)\n\t\t\t\tUNION\n\t\t\t\tSELECT MD5(CONCAT(hash, filename, zip)) AS file FROM storageFiles\n\t\t\t\t\tWHERE lastDeleted > NOW() - INTERVAL 1 MONTH";
     $files = Zotero_DB::columnQuery($sql);
     S3::setAuth(Z_CONFIG::$S3_ACCESS_KEY, Z_CONFIG::$S3_SECRET_KEY);
     $s3Files = S3::getBucket(Z_CONFIG::$S3_BUCKET);
     $toPurge = array();
     foreach ($s3Files as $s3File) {
         preg_match('/^([0-9a-g]{32})\\/(c\\/)?(.+)$/', $s3File['name'], $matches);
         if (!$matches) {
             throw new Exception("Invalid filename '" . $s3File['name'] . "'");
         }
         $zip = $matches[2] ? '1' : '0';
         // Compressed file
         $hash = md5($matches[1] . $matches[3] . $zip);
         if (!in_array($hash, $files)) {
             $toPurge[] = array('hash' => $matches[1], 'filename' => $matches[3], 'zip' => $zip);
         }
     }
     Zotero_DB::beginTransaction();
     foreach ($toPurge as $info) {
         S3::deleteObject(Z_CONFIG::$S3_BUCKET, self::getPathPrefix($info['hash'], $info['zip']) . $info['filename']);
         $sql = "DELETE FROM storageFiles WHERE hash=? AND filename=? AND zip=?";
         Zotero_DB::query($sql, array($info['hash'], $info['filename'], $info['zip']));
         // TODO: maybe check to make sure associated files haven't just been created?
     }
     Zotero_DB::commit();
     return sizeOf($toPurge);
 }

 /**
  * Delete data from memcached
  */
 public static function deleteCachedData($libraryID)
 {
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     // Clear itemID-specific memcache values
     $sql = "SELECT itemID FROM items WHERE libraryID=?";
     $itemIDs = Zotero_DB::columnQuery($sql, $libraryID, $shardID);
     if ($itemIDs) {
         $cacheKeys = array("itemCreators", "itemIsDeleted", "itemRelated", "itemUsedFieldIDs", "itemUsedFieldNames");
         foreach ($itemIDs as $itemID) {
             foreach ($cacheKeys as $key) {
                 Z_Core::$MC->delete($key . '_' . $itemID);
             }
         }
     }
     /*foreach (Zotero_DataObjects::$objectTypes as $type=>$arr) {
     			$className = "Zotero_" . $arr['plural'];
     			call_user_func(array($className, "clearPrimaryDataCache"), $libraryID);
     		}*/
 }

 public static function getOldErrorProcesses($host, $seconds = 60)
 {
     $sql = "SELECT syncProcessID FROM syncUploadQueue\n\t\t\t\tWHERE started < NOW() - INTERVAL ? SECOND AND errorCheck=1";
     $params = array($seconds);
     if ($host) {
         $sql .= " AND processorHost=INET_ATON(?)";
         $params[] = $host;
     }
     return Zotero_DB::columnQuery($sql, $params);
 }

 public function erase()
 {
     if (!$this->loaded) {
         Z_Core::debug("Not deleting unloaded group {$this->id}");
         return;
     }
     Zotero_DB::beginTransaction();
     $userIDs = self::getUsers();
     $this->logGroupLibraryRemoval();
     Zotero_Libraries::deleteCachedData($this->libraryID);
     Zotero_Libraries::clearAllData($this->libraryID);
     $sql = "DELETE FROM shardLibraries WHERE libraryID=?";
     $deleted = Zotero_DB::query($sql, $this->libraryID, Zotero_Shards::getByLibraryID($this->libraryID));
     if (!$deleted) {
         throw new Exception("Group not deleted");
     }
     $sql = "DELETE FROM libraries WHERE libraryID=?";
     $deleted = Zotero_DB::query($sql, $this->libraryID);
     if (!$deleted) {
         throw new Exception("Group not deleted");
     }
     // Delete key permissions for this library, and then delete any keys
     // that had no other permissions
     $sql = "SELECT keyID FROM keyPermissions WHERE libraryID=?";
     $keyIDs = Zotero_DB::columnQuery($sql, $this->libraryID);
     if ($keyIDs) {
         $sql = "DELETE FROM keyPermissions WHERE libraryID=?";
         Zotero_DB::query($sql, $this->libraryID);
         $sql = "DELETE K FROM `keys` K LEFT JOIN keyPermissions KP USING (keyID)\n\t\t\t\t\tWHERE keyID IN (" . implode(', ', array_fill(0, sizeOf($keyIDs), '?')) . ") AND KP.keyID IS NULL";
         Zotero_DB::query($sql, $keyIDs);
     }
     // If group is locked by a sync, flag group for a timestamp update
     // once the sync is done so that the uploading user gets the change
     try {
         foreach ($userIDs as $userID) {
             if ($syncUploadQueueID = Zotero_Sync::getUploadQueueIDByUserID($userID)) {
                 Zotero_Sync::postWriteLog($syncUploadQueueID, 'group', $this->id, 'delete');
             }
         }
     } catch (Exception $e) {
         Z_Core::logError($e);
     }
     Zotero_Notifier::trigger('delete', 'library', $this->libraryID);
     Zotero_DB::commit();
     $this->erased = true;
 }

 protected function loadChildItems($reload = false)
 {
     if ($this->loaded['childItems'] && !$reload) {
         return;
     }
     Z_Core::debug("Loading child items for collection {$this->id}");
     if (!$this->id) {
         trigger_error('$this->id not set', E_USER_ERROR);
     }
     $sql = "SELECT itemID FROM collectionItems WHERE collectionID=?";
     $ids = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID));
     $this->childItems = $ids ? $ids : [];
     $this->loaded['childItems'] = true;
     $this->clearChanged('childItems');
 }

 public function getLinkedItems()
 {
     if (!$this->id) {
         return array();
     }
     $items = array();
     $sql = "SELECT itemID FROM itemCreators WHERE creatorID=?";
     $itemIDs = Zotero_DB::columnQuery($sql, $this->id, Zotero_Shards::getByLibraryID($this->libraryID));
     if (!$itemIDs) {
         return $items;
     }
     foreach ($itemIDs as $itemID) {
         $items[] = Zotero_Items::get($this->libraryID, $itemID);
     }
     return $items;
 }

 public static function search($libraryID, $params)
 {
     $results = array('results' => array(), 'total' => 0);
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT ";
     if ($params['format'] == 'keys') {
         $sql .= "`key`";
     } else {
         if ($params['format'] == 'versions') {
             $sql .= "`key`, version";
         } else {
             $sql .= "searchID";
         }
     }
     $sql .= " FROM savedSearches WHERE libraryID=? ";
     $sqlParams = array($libraryID);
     // Pass a list of searchIDs, for when the initial search is done via SQL
     $searchIDs = !empty($params['searchIDs']) ? $params['searchIDs'] : array();
     // Or keys, for the searchKey parameter
     $searchKeys = $params['searchKey'];
     if (!empty($params['since'])) {
         $sql .= "AND version > ? ";
         $sqlParams[] = $params['since'];
     }
     // TEMP: for sync transition
     if (!empty($params['sincetime'])) {
         $sql .= "AND serverDateModified >= FROM_UNIXTIME(?) ";
         $sqlParams[] = $params['sincetime'];
     }
     if ($searchIDs) {
         $sql .= "AND searchID IN (" . implode(', ', array_fill(0, sizeOf($searchIDs), '?')) . ") ";
         $sqlParams = array_merge($sqlParams, $searchIDs);
     }
     if ($searchKeys) {
         $sql .= "AND `key` IN (" . implode(', ', array_fill(0, sizeOf($searchKeys), '?')) . ") ";
         $sqlParams = array_merge($sqlParams, $searchKeys);
     }
     if (!empty($params['sort'])) {
         switch ($params['sort']) {
             case 'title':
                 $orderSQL = 'searchName';
                 break;
             case 'searchKeyList':
                 $orderSQL = "FIELD(`key`," . implode(',', array_fill(0, sizeOf($searchKeys), '?')) . ")";
                 $sqlParams = array_merge($sqlParams, $searchKeys);
                 break;
             default:
                 $orderSQL = $params['sort'];
         }
         $sql .= "ORDER BY {$orderSQL}";
         if (!empty($params['direction'])) {
             $sql .= " {$params['direction']}";
         }
         $sql .= ", ";
     }
     $sql .= "version " . (!empty($params['direction']) ? $params['direction'] : "ASC") . ", searchID " . (!empty($params['direction']) ? $params['direction'] : "ASC") . " ";
     if (!empty($params['limit'])) {
         $sql .= "LIMIT ?, ?";
         $sqlParams[] = $params['start'] ? $params['start'] : 0;
         $sqlParams[] = $params['limit'];
     }
     if ($params['format'] == 'versions') {
         $rows = Zotero_DB::query($sql, $sqlParams, $shardID);
     } else {
         $rows = Zotero_DB::columnQuery($sql, $sqlParams, $shardID);
     }
     $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID);
     if ($rows) {
         if ($params['format'] == 'keys') {
             $results['results'] = $rows;
         } else {
             if ($params['format'] == 'versions') {
                 foreach ($rows as $row) {
                     $results['results'][$row['key']] = $row['version'];
                 }
             } else {
                 $searches = array();
                 foreach ($rows as $id) {
                     $searches[] = self::get($libraryID, $id);
                 }
                 $results['results'] = $searches;
             }
         }
     }
     return $results;
 }

 public static function getDeleteLogKeys($libraryID, $version, $versionIsTimestamp = false)
 {
     // Default empty library
     if ($libraryID === 0) {
         return [];
     }
     $type = self::$objectType;
     // TEMP: until classic syncing is deprecated and the objectType
     // 'tagName' is changed to 'tag'
     if ($type == 'tag') {
         $type = 'tagName';
     }
     $sql = "SELECT `key` FROM syncDeleteLogKeys " . "WHERE objectType=? AND libraryID=? AND ";
     // TEMP: sync transition
     $sql .= $versionIsTimestamp ? "timestamp>=FROM_UNIXTIME(?)" : "version>?";
     $keys = Zotero_DB::columnQuery($sql, array($type, $libraryID, $version), Zotero_Shards::getByLibraryID($libraryID));
     if (!$keys) {
         return array();
     }
     return $keys;
 }

 public static function search($libraryID, $params)
 {
     $results = array('results' => array(), 'total' => 0);
     // Default empty library
     if ($libraryID === 0) {
         return $results;
     }
     $shardID = Zotero_Shards::getByLibraryID($libraryID);
     $sql = "SELECT SQL_CALC_FOUND_ROWS DISTINCT tagID FROM tags " . "JOIN itemTags USING (tagID) WHERE libraryID=? ";
     $sqlParams = array($libraryID);
     // Pass a list of tagIDs, for when the initial search is done via SQL
     $tagIDs = !empty($params['tagIDs']) ? $params['tagIDs'] : array();
     // Filter for specific tags with "?tag=foo || bar"
     $tagNames = !empty($params['tag']) ? explode(' || ', $params['tag']) : array();
     if ($tagIDs) {
         $sql .= "AND tagID IN (" . implode(', ', array_fill(0, sizeOf($tagIDs), '?')) . ") ";
         $sqlParams = array_merge($sqlParams, $tagIDs);
     }
     if ($tagNames) {
         $sql .= "AND `name` IN (" . implode(', ', array_fill(0, sizeOf($tagNames), '?')) . ") ";
         $sqlParams = array_merge($sqlParams, $tagNames);
     }
     if (!empty($params['q'])) {
         if (!is_array($params['q'])) {
             $params['q'] = array($params['q']);
         }
         foreach ($params['q'] as $q) {
             $sql .= "AND name LIKE ? ";
             $sqlParams[] = "%{$q}%";
         }
     }
     $tagTypeSets = Zotero_API::getSearchParamValues($params, 'tagType');
     if ($tagTypeSets) {
         $positives = array();
         $negatives = array();
         foreach ($tagTypeSets as $set) {
             if ($set['negation']) {
                 $negatives = array_merge($negatives, $set['values']);
             } else {
                 $positives = array_merge($positives, $set['values']);
             }
         }
         if ($positives) {
             $sql .= "AND type IN (" . implode(',', array_fill(0, sizeOf($positives), '?')) . ") ";
             $sqlParams = array_merge($sqlParams, $positives);
         }
         if ($negatives) {
             $sql .= "AND type NOT IN (" . implode(',', array_fill(0, sizeOf($negatives), '?')) . ") ";
             $sqlParams = array_merge($sqlParams, $negatives);
         }
     }
     if (!empty($params['since'])) {
         $sql .= "AND version > ? ";
         $sqlParams[] = $params['since'];
     }
     if (!empty($params['sort'])) {
         $order = $params['sort'];
         if ($order == 'title') {
             // Force a case-insensitive sort
             $sql .= "ORDER BY name COLLATE utf8_unicode_ci ";
         } else {
             if ($order == 'numItems') {
                 $sql .= "GROUP BY tags.tagID ORDER BY COUNT(tags.tagID)";
             } else {
                 $sql .= "ORDER BY {$order} ";
             }
         }
         if (!empty($params['direction'])) {
             $sql .= " " . $params['direction'] . " ";
         }
     }
     if (!empty($params['limit'])) {
         $sql .= "LIMIT ?, ?";
         $sqlParams[] = $params['start'] ? $params['start'] : 0;
         $sqlParams[] = $params['limit'];
     }
     $ids = Zotero_DB::columnQuery($sql, $sqlParams, $shardID);
     $results['total'] = Zotero_DB::valueQuery("SELECT FOUND_ROWS()", false, $shardID);
     if ($ids) {
         $tags = array();
         foreach ($ids as $id) {
             $tags[] = Zotero_Tags::get($libraryID, $id);
         }
         $results['results'] = $tags;
     }
     return $results;
 }

 public static function getAllShards($state = false)
 {
     $sql = "SELECT shardID FROM shards S JOIN shardHosts SH USING (shardHostID)";
     if ($state) {
         $sql .= " WHERE SH.state=? AND S.state=?";
         $params = array($state, $state);
     } else {
         $params = array();
     }
     return Zotero_DB::columnQuery($sql, $params);
 }