PHP DibiConnection::select方法代码示例

 public function testLazyHasValue()
 {
     $id = $this->db->select("max(id)")->from("pages")->fetchSingle();
     $page = new Page($id);
     $this->assertTrue($page->hasValue("name"));
     $this->assertFalse($page->hasValue("nesmysl"));
 }

 protected function createComponentGrid($name)
 {
     $grid = new Grid($this, $name);
     $fluent = $this->database->select('u.*, c.title AS country')->from('[user] u')->join('[country] c')->on('u.country_code = c.code');
     $grid->model = $fluent;
     $grid->addColumnText('firstname', 'Firstname')->setFilterText()->setSuggestion();
     $grid->addColumnText('surname', 'Surname')->setSortable()->setFilterText()->setSuggestion();
     $grid->addColumnText('gender', 'Gender')->setSortable()->cellPrototype->class[] = 'center';
     $grid->addColumnDate('birthday', 'Birthday', \Grido\Components\Columns\Date::FORMAT_TEXT)->setSortable()->setFilterDate()->setCondition($grid->birthdayFilterCondition);
     $grid->getColumn('birthday')->cellPrototype->class[] = 'center';
     $customRender = function ($item) {
         $baseUri = $this->getBaseUri();
         $img = Html::el('img')->src("{$baseUri}/img/flags/{$item->country_code}.gif");
         return "{$img} {$item->country}";
     };
     $grid->addColumnText('country', 'Country')->setSortable()->setCustomRender($customRender)->setFilterText()->setColumn('c.title')->setSuggestion('title');
     $grid->addColumnText('card', 'Card')->setSortable()->setColumn('cctype')->setReplacement(array('MasterCard' => Html::el('b')->setText('MasterCard')))->cellPrototype->class[] = 'center';
     $grid->addColumnEmail('emailaddress', 'Email')->setSortable()->setFilterText();
     $grid->getColumn('emailaddress')->cellPrototype->class[] = 'center';
     $grid->addColumnText('centimeters', 'Height')->setSortable()->setFilterNumber();
     $grid->getColumn('centimeters')->cellPrototype->class[] = 'center';
     $grid->addFilterSelect('gender', 'Gender', array('' => '', 'female' => 'female', 'male' => 'male'));
     $grid->addFilterSelect('card', 'Card', array('' => '', 'MasterCard' => 'MasterCard', 'Visa' => 'Visa'))->setColumn('cctype');
     $grid->addFilterCheck('preferred', 'Only preferred girls :)')->setCondition(array(TRUE => array(array('gender', 'AND', 'centimeters'), array('= ?', '>= ?'), array('female', 170))));
     $grid->addActionHref('edit', 'Edit')->setIcon('pencil');
     $grid->addActionHref('delete', 'Delete')->setIcon('trash')->setConfirm(function ($item) {
         return "Are you sure you want to delete {$item->firstname} {$item->surname}?";
     });
     $operation = array('print' => 'Print', 'delete' => 'Delete');
     $grid->setOperation($operation, $this->handleOperations)->setConfirm('delete', 'Are you sure you want to delete %i items?');
     $grid->filterRenderType = $this->filterRenderType;
     $grid->setExport();
     return $grid;
 }

 /**
  * Get valid refresh token
  * @param string $refreshToken
  * @return IRefreshToken|NULL
  */
 public function getValidRefreshToken($refreshToken)
 {
     $row = $this->context->select('*')->from($this->getTable())->where('refresh_token = %s', $refreshToken)->where('TIMEDIFF(expires_at, NOW()) >= 0')->fetch();
     if (!$row) {
         return NULL;
     }
     return new RefreshToken($row['refresh_token'], new \DateTime($row['expires_at']), $row['client_id'], $row['user_id']);
 }

 protected function setUp()
 {
     $this->db = dibi::getConnection("ormion");
     $this->db->delete("pages")->execute();
     // id, name, description, text, created, allowed
     $this->db->query("insert into [pages]", array("name" => "Clanek", "description" => "Popis", "text" => "Text", "visits" => 0, "allowed" => true), array("name" => "Article", "description" => "Description", "text" => "Text emericky.", "visits" => 5, "allowed" => false), array("name" => "Nepovolený článek", "description" => "Popis nepovoleného článku", "text" => "Dlouhý text. By byl delší než tento.", "visits" => 3, "allowed" => false), array("name" => "Jinačí článek", "description" => "Ryze alternativní popis", "text" => "Duchaplný text.", "visits" => 8, "allowed" => true));
     $fluent = $this->db->select("*")->from("pages");
     $this->object = new Ormion\Collection($fluent, "Page");
 }

 /**
  * Validate authorization code
  * @param string $authorizationCode
  * @return IAuthorizationCode
  */
 public function getValidAuthorizationCode($authorizationCode)
 {
     /** @var ActiveRow $row */
     $row = $this->context->select('*')->from($this->getTable())->where('authorization_code = %s', $authorizationCode)->where('TIMEDIFF(expires_at, NOW()) >= 0')->fetch();
     if (!$row) {
         return NULL;
     }
     $scopes = $this->context->select('*')->from($this->getScopeTable())->where('authorization_code = %s', $authorizationCode)->fetchPairs('scope_name');
     return new AuthorizationCode($row['authorization_code'], new \DateTime($row['expires_at']), $row['client_id'], $row['user_id'], array_keys($scopes));
 }

 public function testInsert()
 {
     $page = new Page(array("name" => "Insert test", "description" => "Insert record", "text" => "Insert record into database"));
     $page->allowed = true;
     $this->assertEquals(Ormion\Record::STATE_NEW, $page->getState());
     $this->object->insert($page);
     $this->assertEquals(Ormion\Record::STATE_EXISTING, $page->getState());
     $this->assertType("int", $page->id);
     $res = $this->db->select("*")->from("pages")->where("id = %i", $page->id)->fetch();
     $this->assertEquals("Insert test", $res->name);
     $this->assertEquals("Insert record", $res->description);
     $this->assertEquals(array(), $page->getModified());
 }

 /**
  * Find client by ID and/or secret key
  * @param string $clientId
  * @param string|null $clientSecret
  * @return IClient
  */
 public function getClient($clientId, $clientSecret = NULL)
 {
     if (!$clientId) {
         return NULL;
     }
     $selection = $this->context->select('*')->from($this->getTable())->where('client_id = %s', $clientId);
     if ($clientSecret) {
         $selection->where('secret = %s', $clientSecret);
     }
     $data = $selection->fetch();
     if (!$data) {
         return NULL;
     }
     return new Client($data['client_id'], $data['secret'], $data['redirect_url']);
 }

 public function createCount($table)
 {
     $query = new Query($this->connection->select("*")->from("%n", $table));
     $query->resultCallback = function (Query $query) {
         return $query->fluent->count();
     };
     return $query;
 }

 public function testSet()
 {
     $page = Page::findByName("Clanek");
     $tags = Tag::findAll();
     $page->Tags = $tags;
     $page->save();
     $tagIds = array_map(function ($record) {
         return $record->id;
     }, $tags->toArray());
     $q = $this->db->select("*")->from("connections")->execute();
     $q->detectTypes();
     $conn = $q->fetchAll();
     $this->assertSame(3, count($conn));
     foreach ($conn as $row) {
         $this->assertSame($page->id, $row->pageId);
         $this->assertTrue(in_array($row->tagId, $tagIds));
     }
 }

 /**
  * Helper function for easy overriding of DB query
  *
  * @return DibiRow|FALSE
  */
 protected function fetchUserData(array $credentials)
 {
     $ds = $this->db->select('*')->from($this->tableName);
     if (Strings::contains($credentials[self::USERNAME], '@')) {
         $ds->where('%n = %s', $this->fieldName[self::EMAIL], $credentials[self::USERNAME]);
     } else {
         $ds->where('%n = %s', $this->fieldName[self::USERNAME], $credentials[self::USERNAME]);
     }
     return $ds->fetch();
 }

    /**
     * Performs an authentication against e.g. database.
     * and returns IIdentity on success or throws AuthenticationException
     * @return IIdentity
     * @throws AuthenticationException
     */
    function authenticate(array $credentials)
    {
        list($username, $password) = $credentials;

        $result = $this->database->select('*')
            ->from(self::TABLE)
            ->where('username = %s',$username)
            ->fetch();
        $count = $result->count();

        if ($count < 1) {
            throw new NS\AuthenticationException('User not found.');
        }

        if (!NS\Passwords::verify($password, $result->password)) {
            throw new NS\AuthenticationException('Invalid password.');
        }

        return new NS\Identity($result->id, $result->role, array('username' => $result->username));
    }

 /**
  * Only columns and operations.
  * @param string $name
  * @return Grid
  */
 private function baseGrid($name)
 {
     $grid = new Grid($this, $name);
     $grid->translator->lang = 'cs';
     $grid->defaultPerPage = 4;
     $fluent = $this->database->select('u.*, c.title AS country')->from('[user] u')->join('[country] c')->on('u.country_code = c.code');
     $grid->model = $fluent;
     $grid->addColumnText('firstname', 'Firstname')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('firstname')->cellPrototype->class[] = 'center';
     $grid->addColumnText('surname', 'Surname')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('surname')->cellPrototype->class[] = 'center';
     $grid->addColumnText('gender', 'Gender')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('gender')->cellPrototype->class[] = 'center';
     $grid->addColumnDate('birthday', 'Birthday', \Grido\Components\Columns\Date::FORMAT_TEXT)->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('birthday')->cellPrototype->class[] = 'center';
     $grid->addColumnText('country', 'Country')->setSortable()->setCustomRender(function ($item) {
         $baseUri = $this->getBaseUri();
         $img = Html::el('img')->src("{$baseUri}/img/flags/{$item->country_code}.gif");
         return "{$img} {$item->country}";
     })->headerPrototype->class[] = 'center';
     $grid->getColumn('country')->cellPrototype->class[] = 'center';
     $grid->addColumnText('city', 'City')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('city')->cellPrototype->class[] = 'center';
     $grid->addColumnText('zip', 'ZIP')->setColumn('zipcode')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('zip')->cellPrototype->class[] = 'center';
     $grid->addColumnText('phone', 'Phone')->setColumn('telephonenumber')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('phone')->cellPrototype->class[] = 'center';
     $grid->addColumnEmail('email', 'Email')->setColumn('emailaddress')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('email')->cellPrototype->class[] = 'center';
     $grid->addColumnText('card', 'Card')->setSortable()->setColumn('cctype')->setReplacement(array('MasterCard' => Html::el('b')->setText('MasterCard')))->headerPrototype->class[] = 'center';
     $grid->getColumn('card')->cellPrototype->class[] = 'center';
     $grid->addColumnText('height', 'Height')->setColumn('centimeters')->setSortable()->headerPrototype->class[] = 'center';
     $grid->getColumn('height')->cellPrototype->class[] = 'center';
     $operation = array('print' => 'Print', 'delete' => 'Delete');
     $grid->setOperation($operation, $this->handleOperations)->setConfirm('delete', 'Are you sure you want to delete %i items?');
     $grid->setExport();
     return $grid;
 }

 /**
  * Helper function for easy overriding of DB query
  *
  * @return DibiRow|FALSE
  */
 protected function fetchRecord(array $credentials)
 {
     if (!isset($this->sessionCache->{$credentials[self::PSK]})) {
         $ds = $this->db->select('*')->from($this->tableName);
         $ds->where('%n = %s', $this->fieldName[self::PSK], $credentials[self::PSK]);
         $record = $ds->fetch();
         if ($record !== FALSE) {
             $this->sessionCache->{$credentials[self::PSK]} = $record;
         } else {
             return FALSE;
         }
     }
     return $this->sessionCache->{$credentials[self::PSK]};
 }

 /**
  * Finds an entity by given array
  *
  * @param string $entityName
  * @param array $where
  * @param array @orderBy
  * @throws \RuntimeException
  * @return mixed
  */
 public function findOneBy($entityName, $where = array(), $orderBy = array())
 {
     $this->handleConnection();
     $entityAttributes = $this->createClassMetadata($entityName);
     $fluent = $this->dibiConnection->select(array_keys($entityAttributes->getProperties()))->from($entityAttributes->getTable())->where($where);
     if (!empty($orderBy)) {
         $fluent->orderBy($orderBy);
     }
     $data = $fluent->fetch();
     $instance = DataHelperLoader::CreateFlatClass($this, $entityAttributes, $data);
     if ($instance) {
         $this->registerClass($instance, $entityAttributes, self::FLAG_INSTANCE_UPDATE);
     }
     return $instance;
 }

 /**
  * Creates IIdentity object from obtained user data
  *
  * @param mixed user data
  * @param IAuthenticator authenticator
  *
  * @return IIdentity
  */
 public function createIdentity($userData, $authenticator)
 {
     $uid = NULL;
     $roles = array();
     $profile = array();
     // ---------------------------------------------------------------------
     // DB Password
     if ($authenticator instanceof Authenticators\DatabasePasswordAuthenticator) {
         $uid = (int) $userData->{$authenticator->getColumn($authenticator::ID)};
         $profile = $userData;
     } elseif ($authenticator instanceof Authenticators\LdapBindAuthenticator) {
         $ldapData = (array) $userData;
         $idCol = 'id';
         $tableName = 'security_users';
         // LDAP Binding
         // DB column name -> ldap array key (or callable)
         $binding = array(array('username' => function ($ldapData) use($authenticator) {
             return mb_substr($ldapData['dn'], mb_strlen($authenticator->getQueryPrefix()), 0 - mb_strlen($authenticator->getQuerySuffix()));
         }), array('name' => 'givenName', 'surname' => 'sn', 'email' => function ($ldapData) use(&$binding) {
             $username = $binding[0]['username']($ldapData);
             $tokens = Strings::splitWithEscape($ldapData['dn'], ',dc=');
             array_shift($tokens);
             return $username . '@' . implode($tokens, '.');
         }));
         // Prepare data based on LDAP binding
         $boundData = $this->bindValues($ldapData, $binding[0]);
         $this->db->query('LOCK TABLES %n WRITE', $tableName);
         $ds = $this->db->select('*')->from($tableName);
         foreach ($boundData as $key => $value) {
             $ds->where('%n = %s', $key, $value);
         }
         $profile = $ds->fetch();
         // If profile does not exist yet
         if ($profile === FALSE) {
             $boundData = array_merge($boundData, $this->bindValues($ldapData, $binding[1]));
             $this->db->insert($tableName, $boundData)->execute();
             $boundData[$idCol] = $uid = (int) $this->db->getInsertId();
             $profile = $boundData;
         } else {
             $uid = (int) $profile[$idCol];
         }
         $this->db->query('UNLOCK TABLES');
         // TODO: configurable
         $groupsDn = NULL;
         if ($groupsDn == NULL) {
             $dnTokens = array_reverse($userData->getParsedDn());
             foreach ($dnTokens as $k => $v) {
                 if (!Strings::startsWith($v, 'dc=')) {
                     array_splice($dnTokens, $k, count($dnTokens), array('ou=groups'));
                     break;
                 }
             }
             $groupDn = implode(array_reverse($dnTokens), ',');
         }
         $username = str_replace(array('\\', ')'), array('\\\\', '\\)'), $boundData['username']);
         $userGid = intval($userData->gidNumber);
         $filter = "(&(objectClass=posixGroup)(|(gidNumber={$userGid})(memberUid={$username})))";
         $result = $authenticator->ldapConnection->search($groupsDn, $filter);
         foreach ($result as $record) {
             $roles[] = $record->cn;
         }
     } elseif ($authenticator instanceof Authenticators\DatabasePSKAuthenticator) {
         $uid = Strings::intoParameterizedString('psk', array($userData->key));
         $roles[] = $uid;
         $profile = $userData;
         // Other authenticators
     } else {
         throw new Nette\NotSupportedException("Authenticator " . get_class($authenticator) . " not supported yet");
     }
     // ---------------------------------------------------------------------
     // Remove duplicit roles
     $roles = array_unique($roles);
     // Sanity check
     if (!is_scalar($uid) || $uid == "") {
         throw new Nette\InvalidStateException("User ID has to be non-empty string or number");
     }
     // ---------------------------------------------------------------------
     // Query roles from DB if it's not PSK (has user id)
     if (is_int($uid)) {
         $roles = array_merge($this->getUserRoles($uid), $roles);
     }
     // ---------------------------------------------------------------------
     // Identity
     $identity = new Nette\Security\Identity($uid, $roles, $profile);
     return $identity;
 }