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 } $length = $matches[1][0] / $multiplier; $width = $matches[3][0] / $multiplier; if ($width > $length) { // swap values $tmp = $length; $length = $width; $width = $tmp; } $height = $matches[6][0] / $multiplier; $extraction_available = true; $extracted_stats['length']++; $extracted_stats['width']++; // optional if ($height > 0) { $extracted_stats['height']++; } else { $height = null; } } else { $width = null; $length = null; $height = null; } } else { $width = null; $height = null; } // Step 5: Extract color $text = strtolower(str_replace("\n", '@', $row['title'] . ' ' . str_replace('- ', "\n", $row['description']) . ' ' . str_replace('- ', "\n", $row['characteristic']))); $text = str_replace(' :', ':', $text); $text = strtolower($text); //if (substr_count($text, 'color:') == 1) { $matched = preg_match_all('/(colo(u)?r:\\ ?([^@\\(]+))/', $text, $matches); if ($matched) { $color = $matches[3][0]; $color = str_replace('&', ' and ', $color); $color = str_replace('/', ' and ', $color); $to_remove = ['highloss', 'metalic', 'transparent', 'high-loss', 'semigloss', 'semigloss', 'metallic', 'shiny', 'finish', 'sparkle', 'wood', 'wave', 'gloss', 'semi-', 'sonic', 'gothic', 'high', 'cosmic', 'fiesta', '"']; foreach ($to_remove as $word) { $color = str_replace($word, '', $color); } $color = str_replace('-', ' ', $color); $color = preg_replace('/\\ +/', ' ', $color); $color = trim($color); if (!preg_match('~\\b(with|and|loss|mat|matt|tone)\\b~i', $color)) { $extraction_available = true; $extracted_stats['color']++; } else { $color = null; } } else { $color = null; } //} // // END of various extractions // if ($extraction_available) { $extracted_stats['total_extracted']++; $warnings = []; $diff_bbx_gross_weight = $row['weight_gross'] != '' && $net_weight !== null ? $net_weight - $row['weight_gross'] : null; $diff_bbx_net_weight = $row['weight'] != '' && $net_weight !== null ? $net_weight - $row['weight'] : null; if ($diff_bbx_gross_weight > 0) { $warnings[] = "net({$net_weight})>gross(" . number_format($row['weight_gross'], 2) . ")"; $extracted_stats['weight_warnings']++; $net_weight = null; } elseif ($diff_bbx_gross_weight === 0) { $warnings[] = "net=gross"; $extracted_stats['weight_warnings']++; $net_weight = null; } $extracted[] = ['product_id' => $product_id, 'product_reference' => $row['reference'], 'diameter' => $diameter, 'net_weight' => $net_weight, 'length' => $length, 'width' => $width, 'height' => $height, 'primary-color' => $color, 'warnings' => implode(',', $warnings)]; } } return ['data' => $extracted, 'stats' => [['attribute' => 'total_products', 'total' => count($rows), 'warnings' => ''], ['attribute' => 'total_extracted', 'total' => $extracted_stats['total_extracted'], 'warnings' => ''], ['attribute' => 'net weight (kg)', 'total' => $extracted_stats['weight'], 'warnings' => $extracted_stats['weight_warnings']], ['attribute' => 'diameter (inches)', 'total' => $extracted_stats['diameter'], 'warnings' => $extracted_stats['diameter_warnings']], ['attribute' => 'length (m)', 'total' => $extracted_stats['length'], 'warnings' => ''], ['attribute' => 'width (m)', 'total' => $extracted_stats['width'], 'warnings' => ''], ['attribute' => 'height (m)', 'total' => $extracted_stats['height'], 'warnings' => ''], ['attribute' => 'primary-color', 'total' => $extracted_stats['color'], 'warnings' => '']]]; }
/** * 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); }
public function demoSqlAction() { // see http://framework.zend.com/manual/current/en/modules/zend.db.sql.html $sql = new Sql($this->localAdapter); $select = $sql->select(); // @return Zend\Db\Sql\Select $select->from('test'); // see the Select API http://framework.zend.com/manual/current/en/modules/zend.db.sql.html#zend-db-sql-select $select->where(array('id' => 2)); //Either prepare $statement = $sql->prepareStatementForSqlObject($select); $results = $statement->execute(); foreach ($results as $row) { Debug::dump($row); } // Or execute $sqlString = $sql->buildSqlString($select); print_r($sqlString); $results = $this->localAdapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE); foreach ($results as $row) { Debug::dump($row); } // TODO more examples with UPDATE, INSERT, DELETE // TODO more examples with PREDICATES http://framework.zend.com/manual/current/en/modules/zend.db.sql.html#zend-db-sql-where-zend-db-sql-having // TODO more examples with Ddl http://framework.zend.com/manual/current/en/modules/zend.db.sql.ddl.html exit(0); }
protected function forgetMigration(MigrationInterface $migration) { $name = $migration->getName(); $sql = new Sql($this->adapter); $delete = $sql->delete($this->migrationTable)->where(['name' => $name]); $this->adapter->query($sql->buildSqlString($delete), Adapter::QUERY_MODE_EXECUTE); $this->availableMigrations[$name] = $this->installedMigrations[$name]; unset($this->installedMigrations[$name]); }