PHP SQLSelect::execute方法代码示例

 public function getMap()
 {
     $this->getDimensions();
     if (empty($this->map)) {
         $sel = new SQLSelect($this->table);
         $res = $sel->execute();
         $this->map = parallelToAssoc($res[strtoupper($this->idfields[0])], $res[strtoupper($this->idfields[1])]);
     }
     return $this->map;
 }

 public function getPossLanguages()
 {
     if (empty($this->posslanguages)) {
         $getl = new SQLSelect("translation_language");
         $getl->fetchas = "row";
         $res = $getl->execute();
         $this->posslanguages = $res;
     }
     return $this->posslanguages;
 }

 public function processFields()
 {
     if (!isset($this->fields["language_keycode"]["options"])) {
         $opsel = new SQLSelect("translation_language");
         $opsel->selectfields = array("id" => "keycode", "label" => "name_def");
         $opsel->fetchas = "row";
         $res = $opsel->execute();
         $options = $res;
         $this->fields["language_keycode"]["options"] = $options;
     }
 }

 /**
  * Attempt to find an old/renamed page from some given the URL as an array
  *
  * @param array $params The array of URL, e.g. /foo/bar as array('foo', 'bar')
  * @param SiteTree $parent The current parent in the recursive flow
  * @param boolean $redirect Whether we've found an old page worthy of a redirect
  *
  * @return string|boolean False, or the new URL
  */
 public static function find_old_page($params, $parent = null, $redirect = false)
 {
     $parent = is_numeric($parent) ? SiteTree::get()->byId($parent) : $parent;
     $params = (array) $params;
     $URL = rawurlencode(array_shift($params));
     if (empty($URL)) {
         return false;
     }
     if ($parent) {
         $page = SiteTree::get()->filter(array('ParentID' => $parent->ID, 'URLSegment' => $URL))->First();
     } else {
         $page = SiteTree::get()->filter(array('URLSegment' => $URL))->First();
     }
     if (!$page) {
         // If we haven't found a candidate, lets resort to finding an old page with this URL segment
         $oldFilter = array('"SiteTree_versions"."URLSegment"' => $URL, '"SiteTree_versions"."WasPublished"' => true);
         if ($parent) {
             $oldFilter[] = array('"SiteTree_versions"."ParentID"' => $parent->ID);
         }
         $query = new SQLSelect('"RecordID"', '"SiteTree_versions"', $oldFilter, '"LastEdited" DESC', null, null, 1);
         $record = $query->execute()->first();
         if ($record) {
             $page = SiteTree::get()->byID($record['RecordID']);
             $redirect = true;
         }
     }
     if ($page && $page->canView()) {
         if (count($params)) {
             // We have to go deeper!
             $ret = self::find_old_page($params, $page, $redirect);
             if ($ret) {
                 // A valid child page was found! We can return it
                 return $ret;
             } else {
                 // No valid page found.
                 if ($redirect) {
                     // If we had some redirect to be done, lets do it. imagine /foo/action -> /bar/action, we still want this redirect to happen if action isn't a page
                     return $page->Link() . implode('/', $params);
                 }
             }
         } else {
             // We've found the final, end all, page.
             return $page->Link();
         }
     }
     return false;
 }

 public function build_query()
 {
     $setstring = "";
     // If insertIfAbsent is set, check for the row
     if ($this->insertIfAbsent) {
         $sel = new SQLSelect($this->table, $this->schema);
         $sel->selectfields[] = "1";
         $sel->wherearray = $this->selectors;
         $val = $sel->execute();
         if ($val == NULL || empty($val) || $val[1] == array()) {
             $ins = new SQLInsert($this->table, $this->values);
             $q = $ins->build_query();
             $this->query = $q;
             return $this->query;
         }
     }
     // Perform the UPDATE
     foreach ($this->values as $key => $value) {
         // Skip selectors
         if (isset($this->selectors[$key])) {
             continue;
         }
         // Build 'SET' string
         if ($setstring != "") {
             $setstring .= ",";
         }
         $setstring .= $key . "=" . dbize($value, $this->get_column_datatype($key));
     }
     if ($setstring != "") {
         $setstring = " SET " . $setstring;
     }
     // Add selectors to WHERE clause
     foreach ($this->selectors as $key => $value) {
         $this->wherefields[] = "{$key} = " . dbize($value, $this->get_column_datatype($key));
     }
     $wherestring = $this->wherefields_to_string();
     if ($setstring != "" && $wherestring != "") {
         $query = "UPDATE " . $this->table_with_schema() . $setstring . $wherestring;
         $this->query = $query;
     }
     return $this->query;
 }

 /**
  *	Return all data object visible attributes of the specified type, with optional filters.
  *
  *	@parameter <{DATA_OBJECT_NAME}> string
  *	@parameter <{LIMIT}> integer
  *	@parameter <{SORT}> array(string, string)
  *	@parameter <{FILTERS}> array
  *	@return array
  */
 public function retrieveValidated($class, $limit = null, $sort = null, $filters = null)
 {
     // Validate the data object class.
     $class = strtolower($class);
     if (in_array($class, array_map('strtolower', ClassInfo::subclassesFor('DataObject'))) && ($configuration = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', array('LOWER(IsFor) = ?' => $class))) && ($temporaryClass = DataObject::get_one($class))) {
         $class = ClassInfo::baseDataClass($temporaryClass->ClassName);
         $visibility = $configuration->APIwesomeVisibility ? explode(',', $configuration->APIwesomeVisibility) : null;
         // Validate the sort and filters.
         $where = array();
         $sortValid = is_array($sort) && count($sort) === 2 && ($order = strtoupper($sort[1])) && ($order === 'ASC' || $order === 'DESC');
         $filterValid = is_array($filters) && count($filters);
         $sorting = array();
         $filtering = array();
         // Grab the appropriate attributes for this data object.
         $columns = array();
         $from = array();
         foreach (ClassInfo::subclassesFor($class) as $subclass) {
             // Determine the tables to join.
             $subclassFields = DataObject::database_fields($subclass);
             if (ClassInfo::hasTable($subclass)) {
                 // Determine the versioned table.
                 $same = $subclass === $class;
                 if ($subclass::has_extension('Versioned')) {
                     $subclass = "{$subclass}_Live";
                 }
                 if (!$same) {
                     $from[] = $subclass;
                 }
             }
             // Prepend the table names.
             $subclassColumns = array();
             foreach ($subclassFields as $column => $type) {
                 $subclassColumn = "{$subclass}.{$column}";
                 $subclassColumns[$subclassColumn] = $type;
                 // Determine the tables to sort and filter on.
                 if ($sortValid && $sort[0] === $column) {
                     $sorting[] = "{$subclassColumn} {$order}";
                 }
                 if ($filterValid && isset($filters[$column])) {
                     $filtering[$subclassColumn] = $filters[$column];
                 }
             }
             $columns = array_merge($columns, $subclassColumns);
         }
         array_shift($columns);
         // Determine the versioned table.
         if ($class::has_extension('Versioned')) {
             $class = "{$class}_Live";
         }
         // Determine ID based sorting and filtering, as these aren't considered database fields.
         if ($sortValid && $sort[0] === 'ID') {
             $sorting[] = "{$class}.ID {$order}";
         }
         if ($filterValid && isset($filters['ID'])) {
             $where["{$class}.ID = ?"] = $filters['ID'];
         }
         // Make sure this data object type has visibility customisation.
         if ($visibility && count($visibility) === count($columns) && in_array('1', $visibility)) {
             // Apply any visibility customisation.
             $select = ' ';
             $iteration = 0;
             foreach ($columns as $attribute => $type) {
                 if (isset($visibility[$iteration]) && $visibility[$iteration]) {
                     $select .= $attribute . ', ';
                     if (isset($filtering[$attribute])) {
                         // Apply the filter if the matching attribute is visible.
                         $column = is_numeric($filtering[$attribute]) ? $attribute : "LOWER({$attribute})";
                         $where["{$column} = ?"] = strtolower($filtering[$attribute]);
                     }
                 }
                 $iteration++;
             }
             if (isset($filtering["{$class}.ClassName"])) {
                 $where["LOWER({$class}.ClassName) = ?"] = strtolower($filtering["{$class}.ClassName"]);
             }
             // Grab all data object visible attributes.
             $query = new SQLSelect("{$class}.ClassName,{$select}{$class}.ID", $class, $where, $sorting, array(), array(), is_numeric($limit) ? $limit : array());
             // Determine the tables with visible attributes to join.
             foreach ($from as $join) {
                 if (strpos($select, " {$join}.") !== false) {
                     $query->addLeftJoin($join, "{$class}.ID = {$join}.ID");
                 }
             }
             $objects = array();
             foreach ($query->execute() as $temporary) {
                 // Return an array of data object maps.
                 $object = array();
                 foreach ($temporary as $attribute => $value) {
                     if ($value) {
                         $object[$attribute] = $value;
                     }
//.........这里部分代码省略.........

 public function testDbDatetimeDifference()
 {
     $offset = $this->checkPreconditions();
     $clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
     $result = DB::query('SELECT ' . $clause)->value();
     $this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset);
     $clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
     $result = DB::query('SELECT ' . $clause)->value();
     $this->matchesRoughly($result, -15, '15 seconds ago - now', $offset);
     $clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
     $result = DB::query('SELECT ' . $clause)->value();
     $this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset);
     $query = new SQLSelect();
     $query->setSelect(array());
     $query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1);
     $result = $query->execute()->value();
     $lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
     $created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
     $this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset);
 }

 /**
  * Link this group set to a specific member.
  *
  * Recursively selects all groups applied to this member, as well as any
  * parent groups of any applied groups
  *
  * @param array|integer $id (optional) An ID or an array of IDs - if not provided, will use the current
  * ids as per getForeignID
  * @return array Condition In array(SQL => parameters format)
  */
 public function foreignIDFilter($id = null)
 {
     if ($id === null) {
         $id = $this->getForeignID();
     }
     // Find directly applied groups
     $manyManyFilter = parent::foreignIDFilter($id);
     $query = new SQLSelect('"Group_Members"."GroupID"', '"Group_Members"', $manyManyFilter);
     $groupIDs = $query->execute()->column();
     // Get all ancestors, iteratively merging these into the master set
     $allGroupIDs = array();
     while ($groupIDs) {
         $allGroupIDs = array_merge($allGroupIDs, $groupIDs);
         $groupIDs = DataObject::get("Group")->byIDs($groupIDs)->column("ParentID");
         $groupIDs = array_filter($groupIDs);
     }
     // Add a filter to this DataList
     if (!empty($allGroupIDs)) {
         $allGroupIDsPlaceholders = DB::placeholders($allGroupIDs);
         return array("\"Group\".\"ID\" IN ({$allGroupIDsPlaceholders})" => $allGroupIDs);
     } else {
         return array('"Group"."ID"' => 0);
     }
 }

 /**
  * Find the extra field data for a single row of the relationship join
  * table, given the known child ID.
  *
  * @param string $componentName The name of the component
  * @param int $itemID The ID of the child for the relationship
  *
  * @return array Map of fieldName => fieldValue
  */
 public function getExtraData($componentName, $itemID)
 {
     $result = array();
     // Skip if no extrafields or unsaved record
     if (empty($this->extraFields) || empty($itemID)) {
         return $result;
     }
     if (!is_numeric($itemID)) {
         user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
     }
     $cleanExtraFields = array();
     foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
         $cleanExtraFields[] = "\"{$fieldName}\"";
     }
     $query = new SQLSelect($cleanExtraFields, "\"{$this->joinTable}\"");
     $filter = $this->foreignIDWriteFilter($this->getForeignID());
     if ($filter) {
         $query->setWhere($filter);
     } else {
         user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
     }
     $query->addWhere(array("\"{$this->localKey}\"" => $itemID));
     $queryResult = $query->execute()->current();
     if ($queryResult) {
         foreach ($queryResult as $fieldName => $value) {
             $result[$fieldName] = $value;
         }
     }
     return $result;
 }

 /**
  *	Recursively retrieve the entire URL for the given record.
  *
  *	@parameter <{RECORD}> array
  *	@parameter <{RECURSIVE_URL}> string
  *	@return string/boolean
  */
 protected function getURLForRecord($record = null, $URL = null)
 {
     if (!$record) {
         return false;
     }
     $parentID = $record['ParentID'];
     $seg = $record['URLSegment'];
     $URL = !$URL ? $seg : "{$seg}/{$URL}";
     if ($parentID == 0) {
         // The top of the chain has been reached.
         return $URL;
     } else {
         // Retrieve the parent element which was most recently published.
         $parentQuery = new SQLSelect('ID, ParentID, URLSegment, Version', $this->replayTable, "ID = {$parentID}", null, null, null, 1);
         $parent = $parentQuery->execute()->first();
         return $this->getURLForRecord($parent, $URL);
     }
 }

 /**
  * Get a list of languages with at least one element translated in (including the default language)
  *
  * @param string $className Look for languages in elements of this class
  * @param string $where Optional SQL WHERE statement
  * @return array Map of languages in the form locale => langName
  */
 public static function get_existing_content_languages($className = 'SiteTree', $where = '')
 {
     $baseTable = ClassInfo::baseDataClass($className);
     $query = new SQLSelect("Distinct \"Locale\"", "\"{$baseTable}\"", $where, '', "\"Locale\"");
     $dbLangs = $query->execute()->column();
     $langlist = array_merge((array) Translatable::default_locale(), (array) $dbLangs);
     $returnMap = array();
     $allCodes = array_merge(Config::inst()->get('i18n', 'all_locales'), Config::inst()->get('i18n', 'common_locales'));
     foreach ($langlist as $langCode) {
         if ($langCode && isset($allCodes[$langCode])) {
             if (is_array($allCodes[$langCode])) {
                 $returnMap[$langCode] = $allCodes[$langCode]['name'];
             } else {
                 $returnMap[$langCode] = $allCodes[$langCode];
             }
         }
     }
     return $returnMap;
 }

 /**
  * Are new posts available?
  *
  * @param int $id
  * @param array $data Optional: If an array is passed, the timestamp of
  *                    the last created post and it's ID will be stored in
  *                    it (keys: 'last_id', 'last_created')
  * @param int $lastVisit Unix timestamp of the last visit (GMT)
  * @param int $lastPostID ID of the last read post
  * @param int $thread ID of the relevant topic (set to NULL for all
  *                     topics)
  * @return bool Returns TRUE if there are new posts available, otherwise
  *              FALSE.
  */
 public static function new_posts_available($id, &$data = array(), $lastVisit = null, $lastPostID = null, $forumID = null, $threadID = null)
 {
     // last post viewed
     $query = new SQLSelect(array('LastID' => 'MAX("Post"."ID")', 'LastCreated' => 'MAX("Post"."Created")'), '"Post"', array('"ForumPage"."ParentID" = ?' => $id));
     $query->addInnerJoin(ForumHolder::baseForumTable(), '"Post"."ForumID" = "ForumPage"."ID"', 'ForumPage');
     // Filter by parameters specified
     if ($lastPostID) {
         $query->addWhere(array('"Post"."ID" > ?' => $lastPostID));
     }
     if ($lastVisit) {
         $query->addWhere(array('"Post"."Created" > ?' => $lastVisit));
     }
     if ($forumID) {
         $query->addWhere(array('"Post"."ForumID" = ?' => $forumID));
     }
     if ($threadID) {
         $query->addWhere(array('"Post"."ThreadID" = ?' => $threadID));
     }
     // Run
     $version = $query->execute()->first();
     if (!$version) {
         return false;
     }
     if ($data) {
         $data['last_id'] = (int) $version['LastID'];
         $data['last_created'] = strtotime($version['LastCreated']);
     }
     $lastVisit = (int) $lastVisit;
     if ($lastVisit <= 0) {
         $lastVisit = false;
     }
     $lastPostID = (int) $lastPostID;
     if ($lastPostID <= 0) {
         $lastPostID = false;
     }
     if (!$lastVisit && !$lastPostID) {
         return true;
     }
     if ($lastVisit && strtotime($version['LastCreated']) > $lastVisit) {
         return true;
     }
     if ($lastPostID && (int) $version['LastID'] > $lastPostID) {
         return true;
     }
     return false;
 }

 /**
  * Returns true if the member is allowed to do the given action.
  * See {@link extendedCan()} for a more versatile tri-state permission control.
  *
  * @param string $perm The permission to be checked, such as 'View'.
  * @param Member $member The member whose permissions need checking.  Defaults to the currently logged
  * in user.
  *
  * @return boolean True if the the member is allowed to do the given action
  */
 public function can($perm, $member = null)
 {
     if (!isset($member)) {
         $member = Member::currentUser();
     }
     if (Permission::checkMember($member, "ADMIN")) {
         return true;
     }
     if ($this->many_many('Can' . $perm)) {
         if ($this->ParentID && $this->SecurityType == 'Inherit') {
             if (!($p = $this->Parent)) {
                 return false;
             }
             return $this->Parent->can($perm, $member);
         } else {
             $permissionCache = $this->uninherited('permissionCache');
             $memberID = $member ? $member->ID : 'none';
             if (!isset($permissionCache[$memberID][$perm])) {
                 if ($member->ID) {
                     $groups = $member->Groups();
                 }
                 $groupList = implode(', ', $groups->column("ID"));
                 // TODO Fix relation table hardcoding
                 $query = new SQLSelect("\"Page_Can{$perm}\".PageID", array("\"Page_Can{$perm}\""), "GroupID IN ({$groupList})");
                 $permissionCache[$memberID][$perm] = $query->execute()->column();
                 if ($perm == "View") {
                     // TODO Fix relation table hardcoding
                     $query = new SQLSelect("\"SiteTree\".\"ID\"", array("\"SiteTree\"", "LEFT JOIN \"Page_CanView\" ON \"Page_CanView\".\"PageID\" = \"SiteTree\".\"ID\""), "\"Page_CanView\".\"PageID\" IS NULL");
                     $unsecuredPages = $query->execute()->column();
                     if ($permissionCache[$memberID][$perm]) {
                         $permissionCache[$memberID][$perm] = array_merge($permissionCache[$memberID][$perm], $unsecuredPages);
                     } else {
                         $permissionCache[$memberID][$perm] = $unsecuredPages;
                     }
                 }
                 Config::inst()->update($this->class, 'permissionCache', $permissionCache);
             }
             if ($permissionCache[$memberID][$perm]) {
                 return in_array($this->ID, $permissionCache[$memberID][$perm]);
             }
         }
     } else {
         return parent::can($perm, $member);
     }
 }

 /**
  * Test that multiple order elements are maintained in the given order
  */
 public function testOrderByMultiple()
 {
     if (DB::get_conn() instanceof MySQLDatabase) {
         $query = new SQLSelect();
         $query->setSelect(array('"Name"', '"Meta"'));
         $query->setFrom('"SQLQueryTest_DO"');
         $query->setOrderBy(array('MID("Name", 8, 1) DESC', '"Name" ASC'));
         $records = array();
         foreach ($query->execute() as $record) {
             $records[] = $record;
         }
         $this->assertCount(2, $records);
         $this->assertEquals('Object 2', $records[0]['Name']);
         $this->assertEquals('2', $records[0]['_SortColumn0']);
         $this->assertEquals('Object 1', $records[1]['Name']);
         $this->assertEquals('1', $records[1]['_SortColumn0']);
     }
 }

 /**
  * Copies all values from one table to another. Will override any existing values with matching ID's.
  *
  * @param   string      $fromTable      Name of SOURCE table to copy values from.
  * @param   string      $toTable        Name of DESTINATION table to copy values to.
  * @param   array|null  $fieldMapping   Array of fields to copy (and ONLY these fields). Can also specify key => value
  *                                      pairs to map between old/new names (instead of just values). Note: Leave
  *                                      empty (or pass null) to automatically assume ALL fields from source table (including ID).
  * @param   bool        $purgeDest      Ensures all data in the DESTINATION table matches the source.
  * @param   mixed|null  $where          An optional filter passed directly to ->setWhere() method on SQLSelect.
  * @throws  MigrationException
  */
 public static function copyTable($fromTable, $toTable, array $fieldMapping = null, $purgeDest = false, $where = null)
 {
     if (!static::tableExists($fromTable)) {
         throw new MigrationException("Table '{$fromTable}' does not exist.");
     }
     if (!static::tableExists($toTable)) {
         throw new MigrationException("Table '{$fromTable}' does not exist.");
     }
     // Initialize defaults.
     if ($fieldMapping === null) {
         $fieldMapping = array();
     }
     // Normalize to empty.
     if ($fieldMapping === array()) {
         // If empty: Use all fields from the source.
         $fieldMapping = array_keys(static::getTableColumns($fromTable));
     }
     // Since an ID is required to prevent duplication of data, add it now if it's not already setup.
     // TODO: Should this be optional?
     if (!in_array('ID', $fieldMapping)) {
         $fieldMapping[] = 'ID';
     }
     // Separate out the source/destination fields from the field mapping to help with selection and validation (correspondingly).
     $sourceFields = array_map(function ($key, $value) {
         if (!is_numeric($key)) {
             return $key;
         }
         return $value;
     }, array_keys($fieldMapping), array_values($fieldMapping));
     $destFields = array_values($fieldMapping);
     // Validate columns in the destination first and ensure they exist first before moving forward, since you
     // don't want to perform a DELETE on an entire table unless you're sure the entire operation will complete.
     $destActualFields = array_keys(self::getTableColumns($toTable));
     $destFieldDiff = array_diff($destFields, $destActualFields);
     if (count($destFieldDiff) !== 0) {
         throw new MigrationException("The field(s) '" . join(', ', $destFieldDiff) . "' do not exist in the destination table '{$toTable}'.");
     }
     // Purge now, if specified.
     if ($purgeDest) {
         $delete = new SQLDelete($toTable);
         $delete->execute();
     }
     // Begin fetching rows and copying them over now.
     $select = new SQLSelect($sourceFields, $fromTable);
     if ($where !== null) {
         $select->setWhere($where);
     }
     $result = $select->execute();
     while ($sourceRow = $result->next()) {
         // Convert row fields based on our mapping.
         $destRow = array();
         foreach ($sourceRow as $field => $value) {
             if (array_key_exists($field, $fieldMapping)) {
                 $field = $fieldMapping[$field];
             }
             $destRow[$field] = $value;
         }
         // Update table.
         static::setRowValuesOnTable($toTable, $destRow, null, true);
     }
 }