PHP Sql::buildSqlString方法代码示例

 public function onDispatch(MvcEvent $e)
 {
     if (!$e->getRequest() instanceof ConsoleRequest) {
         throw new RuntimeException('You can only use this action from a console!');
     }
     $table = new Ddl\CreateTable('queue_messages');
     $table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
     $table->addColumn(new Ddl\Column\Varchar('queue_name', 100));
     $table->addColumn(new Ddl\Column\Integer('status', false));
     $table->addColumn(new Ddl\Column\Varchar('options', 250));
     $table->addColumn(new Ddl\Column\Text('message', null, true));
     $table->addColumn(new Ddl\Column\Text('output', null, true));
     $table->addColumn(new Ddl\Column\Datetime('started_dt', true));
     $table->addColumn(new Ddl\Column\Datetime('finished_dt', true));
     $table->addColumn(new Ddl\Column\Datetime('created_dt', false));
     $table->addColumn(new Ddl\Column\Datetime('updated_dt', true));
     $table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
     $sql = new Sql($this->dbAdapter);
     try {
         $this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
     } catch (\Exception $e) {
         // currently there are no db-independent way to check if table exists
         // so we assume that table exists when we catch exception
     }
 }

 function login($data, $adapter)
 {
     $sql = new Sql($adapter);
     $select = $sql->select()->from('users')->where(array('email' => $data['email'], 'password' => md5($data['password'])))->limit(1);
     $result = $adapter->query($sql->buildSqlString($select), $adapter::QUERY_MODE_EXECUTE);
     return $result;
 }

 private function create(SqlInterface $table)
 {
     try {
         $sql = new Sql($this->dbAdapter);
         $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
     } catch (PDOException $e) {
         $message = $e->getMessage() . PHP_EOL;
     }
 }

 function loginByAdapter($data, $adapter)
 {
     $sql = new Sql($adapter);
     $select = $sql->select();
     $select->from('users');
     $select->where(array('email' => $data->email, 'pass_word' => md5($data->password)));
     $selectString = $sql->buildSqlString($select);
     $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
     return $results;
 }

 public function count($search = null)
 {
     $adapter = $this->getDbAdapter();
     $sql = new Sql($adapter);
     $select = $sql->select();
     $select->columns(array('cnt' => new \Zend\Db\Sql\Expression('COUNT(*)')))->from($this->tableName);
     if ($search) {
         $select->where("header like '%{$search}%'");
     }
     $selectString = $sql->buildSqlString($select);
     return (int) $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE)->current()['cnt'];
 }

 public function getTShirtSizeInchListAvailable()
 {
     $sql = new Sql($this->table_gateway->getAdapter());
     $select = new Select('tshirt_size');
     $select->columns(array('size_inch'));
     $adapter = $this->table_gateway->getAdapter();
     $query = $adapter->query($sql->buildSqlString($select), $adapter::QUERY_MODE_EXECUTE);
     $holder = array();
     foreach ($query as $key => $value) {
         $holder[$key] = $value;
     }
     return $holder;
 }

 private function createTableLanguages()
 {
     $table = new Ddl\CreateTable('languages');
     $id = new Column\Integer('id');
     $id->setOption('autoincrement', true);
     $table->addColumn($id);
     $table->addColumn(new Column\Varchar('code', 2));
     $table->addColumn(new Column\Varchar('locale', 6));
     $table->addColumn(new Column\Varchar('name', 50));
     $table->addColumn(new Column\Integer('default', false, 0));
     $table->addConstraint(new Constraint\PrimaryKey('id'));
     $table->addConstraint(new Constraint\UniqueKey('code'));
     $table->addConstraint(new Constraint\UniqueKey('locale'));
     $sql = new Sql($this->dbAdapter);
     $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
 }

 public function onDispatch(MvcEvent $e)
 {
     if (!$e->getRequest() instanceof ConsoleRequest) {
         throw new RuntimeException('You can only use this action from a console!');
     }
     $table = new Ddl\CreateTable(Version::TABLE_NAME);
     $table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
     $table->addColumn(new Ddl\Column\BigInteger('version'));
     $table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
     $table->addConstraint(new Ddl\Constraint\UniqueKey('version'));
     $sql = new Sql($this->dbAdapter);
     try {
         $this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
     } catch (\Exception $e) {
         // currently there are no db-independent way to check if table exists
         // so we assume that table exists when we catch exception
     }
 }

 private function createTablePages()
 {
     $table = new Ddl\CreateTable('pages');
     $id = new Column\Integer('id');
     $id->setOption('AUTO_INCREMENT', 1);
     $table->addColumn($id);
     $table->addColumn(new Column\Varchar('title', 255));
     $table->addColumn(new Column\Text('body'));
     $table->addColumn(new Column\Datetime('dt_created'));
     $table->addColumn(new Column\Datetime('dt_updated', true));
     $table->addConstraint(new Constraint\PrimaryKey('id'));
     $sql = new Sql($this->dbAdapter);
     try {
         $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
     } catch (PDOException $e) {
         return $e->getMessage() . PHP_EOL;
     }
 }

 protected function _insertInto($table, array $data, $columns = null)
 {
     if (!$data) {
         return;
     }
     if ($columns === null) {
         $columns = $this->_metadata->getColumnNames($table);
     }
     $insertStmt = new InsertMultiple($table);
     if ($columns !== null) {
         $insertStmt->columns($columns);
     }
     foreach ($data as $row) {
         $insertStmt->values($row, InsertMultiple::VALUES_MERGE);
     }
     $sql = $this->_sql->buildSqlString($insertStmt);
     $this->_db->query($sql, Adapter::QUERY_MODE_EXECUTE);
 }

 public function onDispatch(MvcEvent $e)
 {
     if (!$e->getRequest() instanceof ConsoleRequest) {
         throw new RuntimeException('You can only use this action from a console!');
     }
     $table = new Ddl\CreateTable('profiler');
     $table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
     $table->addColumn(new Ddl\Column\Varchar('method', 4));
     $table->addColumn(new Ddl\Column\Varchar('uri', 500));
     $table->addColumn(new Ddl\Column\Integer('response_code', false, null));
     $table->addColumn(new Ddl\Column\Integer('execution_time', false, null));
     $table->addColumn(new Ddl\Column\Datetime('created_dt', false));
     $table->addColumn(new Ddl\Column\Text('timers'));
     $table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
     $sql = new Sql($this->dbAdapter);
     try {
         $this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
     } catch (\Exception $e) {
         // currently there are no db-independent way to check if table exists
         // so we assume that table exists when we catch exception
     }
 }

 /**
  * Extract attributes from product_description
  * @return array
  */
 public function extract()
 {
     $params = ['lang' => 'en'];
     $select = $this->getProductDescSelect($params);
     $sql = new Sql($this->adapter);
     $string = $sql->buildSqlString($select);
     $rows = $this->adapter->query($string, Adapter::QUERY_MODE_EXECUTE);
     $extracted = [];
     $extracted_stats = ['diameter' => 0, 'weight' => 0, 'length' => 0, 'width' => 0, 'height' => 0, 'color' => 0, 'total_extracted' => 0, 'weight_warnings' => 0, 'diameter_warnings' => 0];
     foreach ($rows as $row) {
         $extraction_available = false;
         $product_id = $row['product_id'];
         // Step 1: extract diameter from title or invoice title
         // only if there's only one diameter specified (") to prevent matching 6" x 11"
         if (substr_count($row['title'], '"') == 1) {
             $text = str_replace(',', '.', $row['title']);
             $match = preg_match_all('/(([1-3]?[0-9](\\.[1-9])?)\\ ?")/', $text, $matches);
             if ($match && $matches[2][0] > 0) {
                 $diameter = $matches[2][0];
                 $extracted_stats['diameter']++;
                 $extraction_available = true;
                 // meters to inches = 1m * 39.3700787402
                 //$products[$product_id]['diameter'] = $diameter * 0.0254;
             }
         } else {
             $diameter = null;
         }
         // Step 2: extract product weight from description
         // i.e. Weight: 50 g (1.75 oz.) with cord
         $text = strtolower(str_replace("\n", ' ', $row['description'] . ' ' . $row['characteristic']));
         $text = str_replace(' :', ':', $text);
         $text = str_replace(',', '.', $text);
         if (substr_count($text, ' weight:') == 1 && substr_count($text, ' gross weight') == 0) {
             if (preg_match_all('/(\\ weight:\\ ?(([0-9]+(\\.[0-9]+)?)\\ ?(kilogram|gram|kg|g)))/', $text, $matches)) {
                 $unit = $matches[5][0];
                 if (in_array($unit, ['g', 'gram'])) {
                     $multiplier = 1000;
                 } elseif (in_array($unit, ['kg', 'kilogram'])) {
                     $multiplier = 1;
                 } else {
                     $multiplier = 0;
                     // unsupported unit
                 }
                 $net_weight = $matches[3][0] / $multiplier;
                 $extracted_stats['weight']++;
                 $extraction_available = true;
             }
         } else {
             $net_weight = null;
         }
         // Step 3. Extract length if exists
         $text = strtolower(str_replace("\n", ' ', $row['description'] . ' ' . $row['characteristic']));
         $text = str_replace(' :', ':', $text);
         $text = str_replace(',', '.', $text);
         if (substr_count($text, ' length:') == 1) {
             $matched = preg_match_all('/(\\ length:\\ ?(([0-9]+(\\.[0-9]+)?)\\ ?(meter|millimeter|centimer|cm|mm|m)))/', $text, $matches);
             if ($matched) {
                 $unit = $matches[5][0];
                 if (in_array($unit, ['centimeter', 'cm'])) {
                     $multiplier = 100;
                 } elseif (in_array($unit, ['mm', 'millimeter'])) {
                     $multiplier = 1000;
                 } elseif (in_array($unit, ['m', 'meter'])) {
                     $multiplier = 1;
                 } else {
                     $multiplier = 0;
                     // unsupported unit
                 }
                 $length = $matches[3][0] / $multiplier;
                 $extracted_stats['length']++;
                 $extraction_available = true;
             }
         } else {
             $length = null;
         }
         // Step 4: extract dimensions only if length
         // is not provided. For example the cables
         if (!$length) {
             $text = strtolower(str_replace("\n", ' ', $row['description'] . ' ' . $row['characteristic']));
             $text = str_replace(' :', ':', $text);
             $text = str_replace('*', 'x', $text);
             $text = str_replace(',', '.', $text);
             $text = str_replace(' x ', 'x', $text);
             $matched = preg_match_all('/([0-9]+(\\.[0-9]+)?)x([0-9]+(\\.[0-9]+)?)(x([0-9]+(\\.[0-9]+)?))?\\ ?(meter|millimeter|centimer|cm|mm|m)/', $text, $matches);
             if ($matched == 1) {
                 $unit = $matches[8][0];
                 if (in_array($unit, ['centimeter', 'cm'])) {
                     $multiplier = 100;
                 } elseif (in_array($unit, ['mm', 'millimeter'])) {
                     $multiplier = 1000;
                 } elseif (in_array($unit, ['m', 'meter'])) {
                     $multiplier = 1;
                 } else {
                     $multiplier = 0;
                     // unsupported unit
                 }
//.........这里部分代码省略.........

 /**
  * Creates table by its name and config
  *
  * @param $tableName
  * @param $tableConfig
  * @return Adapter\Driver\StatementInterface|\Zend\Db\ResultSet\ResultSet
  * @throws RestException
  */
 protected function create($tableName, $tableConfig = null)
 {
     $tableConfig = is_null($tableConfig) ? $tableConfig = $tableName : $tableConfig;
     $tableConfigArray = $this->getTableConfig($tableConfig);
     $table = new CreateTable($tableName);
     $alterTable = new AlterTable($tableName);
     $table->addConstraint(new Constraint\PrimaryKey('id'));
     foreach ($tableConfigArray as $fieldName => $fieldData) {
         $fieldType = $fieldData[self::FIELD_TYPE];
         $fieldParams = $this->getFieldParams($fieldData, $fieldType);
         array_unshift($fieldParams, $fieldName);
         $fieldClass = '\\Zend\\Db\\Sql\\Ddl\\Column\\' . $fieldType;
         $reflectionObject = new \ReflectionClass($fieldClass);
         $fieldInstance = $reflectionObject->newInstanceArgs($fieldParams);
         // it' like new class($callParamsArray[1], $callParamsArray[2]...)
         $table->addColumn($fieldInstance);
         if (isset($fieldData[self::UNIQUE_KEY])) {
             $uniqueKeyConstraintName = $fieldData[self::UNIQUE_KEY] === true ? 'UniqueKey_' . $tableName . '_' . $fieldName : $fieldData[self::UNIQUE_KEY];
             $uniqueKeyInstance = new UniqueKey([$fieldName], $uniqueKeyConstraintName);
             $alterTable->addConstraint($uniqueKeyInstance);
         }
         if (isset($fieldData[self::FOREIGN_KEY])) {
             $foreignKeyConstraintName = !isset($fieldData[self::FOREIGN_KEY]['name']) ? 'ForeignKey_' . $tableName . '_' . $fieldName : $fieldData[self::FOREIGN_KEY]['name'];
             $onDeleteRule = isset($fieldData[self::FOREIGN_KEY]['onDeleteRule']) ? $fieldData[self::FOREIGN_KEY]['onDeleteRule'] : null;
             $onUpdateRule = isset($fieldData[self::FOREIGN_KEY]['onUpdateRule']) ? $fieldData[self::FOREIGN_KEY]['onUpdateRule'] : null;
             $foreignKeyInstance = new Constraint\ForeignKey($foreignKeyConstraintName, [$fieldName], $fieldData[self::FOREIGN_KEY]['referenceTable'], $fieldData[self::FOREIGN_KEY]['referenceColumn'], $onDeleteRule, $onUpdateRule, $foreignKeyConstraintName);
             $alterTable->addConstraint($foreignKeyInstance);
         }
     }
     // this is simpler version, not MySQL only, but without options[] support
     //$mySqlPlatformSql = new Sql\Platform\Mysql\Mysql();
     //$sql = new Sql\Sql($this->db, null, $mySqlPlatformSql);
     //$sqlString = $sql->buildSqlString($table);
     $ctdMysql = new Sql\Platform\Mysql\Ddl\CreateTableDecorator();
     $mySqlPlatformDbAdapter = new Adapter\Platform\Mysql();
     $mySqlPlatformDbAdapter->setDriver($this->db->getDriver());
     $sqlStringCreate = $ctdMysql->setSubject($table)->getSqlString($mySqlPlatformDbAdapter);
     $mySqlPlatformSql = new Sql\Platform\Mysql\Mysql();
     $sql = new Sql\Sql($this->db, null, $mySqlPlatformSql);
     $sqlStringAlter = $sql->buildSqlString($alterTable);
     $sqlString = $sqlStringCreate . ';' . PHP_EOL . $sqlStringAlter . ';';
     return $this->db->query($sqlString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
 }

 /**
  * Create Schema
  *
  * @return AdapterInterface
  */
 public function createSchema()
 {
     $ddl = new CreateTable($this->tableName);
     $ddl->addColumn(new Varchar('version', 255));
     $sql = new Sql($this->adapter);
     $this->adapter->query($sql->buildSqlString($ddl), Adapter::QUERY_MODE_EXECUTE);
     return $this;
 }

 /**
  * Execute sql
  *
  * @param Sql          $sql       Sql
  * @param SqlInterface $sqlObject Sql object
  *
  * @return \Zend\Db\Adapter\Driver\StatementInterface|\Zend\Db\ResultSet\ResultSet
  */
 protected function _executeSql(Sql $sql, SqlInterface $sqlObject)
 {
     $sqlString = $sql->buildSqlString($sqlObject);
     return $this->_adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
 }