/** * Gets number of rows returned by a resulset * *<code> * $result = $connection->query("SELECT * FROM robots ORDER BY name"); * echo 'There are ', $result->numRows(), ' rows in the resulset'; *</code> * * @return int */ public function numRows() { $rowCount = $this->_rowCount; if ($rowCount === false) { switch ($this->_connection->getType()) { case 'mysql': $rowCount = $this->_pdoStatement->rowCount(); break; case 'pgsql': $rowCount = $this->_pdoStatement->rowCount(); break; } if ($rowCount === false) { //SQLite/Oracle/SQLServer returns resultsets that to the client eyes (PDO) has an //arbitrary number of rows, so we need to perform an extra count to know that $sqlStatement = $this->_sqlStatement; //If the sql_statement starts with SELECT COUNT(*) we don't make the count if (strpos($sqlStatement, 'SELECT COUNT(*) ') !== 0) { $bindParams = $this->_bindParams; $bindTypes = $this->_bindTypes; $matches = null; if (preg_match("/^SELECT\\s+(.*)\$/i", $sqlStatement, $matches) == true) { $rowCount = $this->_connection->query("SELECT COUNT(*) \"numrows\" FROM (SELECT " . $matches[0] . ')', $bindParams, $bindTypes)->fetch()->numRows(); } } else { $rowCount = 1; } } //Update the value to avoid further calculations $this->_rowCount = $rowCount; } return $rowCount; }
/** * (PHP 5 >= 5.0.0)<br/> * Return the current element * @link http://php.net/manual/en/iterator.current.php * @return mixed Can return any type. */ public function current() { $limit_start = $this->position * $this->perSize; $this->dbConnection->connect(); $result = $this->dbConnection->query($this->sql . " LIMIT {$limit_start},{$this->perSize}"); $result->setFetchMode(Db::FETCH_ASSOC); return $result->fetchAll(); }
public static function getCurrentVersion() { if (!is_null(self::$_config->get('migrationsLog')) && 'database' == self::$_config->get('migrationsLog')) { if (is_null(self::$_connection)) { self::connSetup(self::$_config->get('database')); } $initialVersion = self::$_connection->query('SELECT `version` FROM `phalcon_migrations` ORDER BY `version` DESC LIMIT 1;'); if (0 == $initialVersion->numRows()) { self::$_connection->execute('INSERT INTO `phalcon_migrations` (`version`) VALUES ("0.0.0");'); $initialVersion = new VersionItem('0.0.0'); } else { $initialVersion = $initialVersion->fetch(); $initialVersion = new VersionItem($initialVersion['version']); } } else { $initialVersion = new VersionItem(file_exists(self::$_migrationFid) ? file_get_contents(self::$_migrationFid) : null); } return $initialVersion; }
/** * {@inheritdoc} * * @param string $prefix * @return array */ public function queryKeys($prefix = null) { if (!$prefix) { $prefix = $this->_prefix; } else { $prefix = $this->getPrefixedIdentifier($prefix); } if (!empty($prefix)) { $sql = "SELECT key_name FROM {$this->table} WHERE key_name LIKE ? ORDER BY lifetime"; $rs = $this->db->query($sql, [$prefix . '%']); } else { $sql = "SELECT key_name FROM {$this->table} ORDER BY lifetime"; $rs = $this->db->query($sql); } $rs->setFetchMode(Db::FETCH_ASSOC); $keys = []; while ($row = $rs->fetch()) { $keys[] = !empty($prefix) ? str_replace($prefix, '', $row['key_name']) : $row['key_name']; } return $keys; }
/** * Generate specified table migration * * @param $version * @param $table * @param null $exportData * * @return string * @throws \Phalcon\Db\Exception */ public static function generate($version, $table, $exportData = null) { $oldColumn = null; $allFields = []; $numericFields = []; $tableDefinition = []; $snippet = new Snippet(); $defaultSchema = Utils::resolveDbSchema(self::$_databaseConfig); $description = self::$_connection->describeColumns($table, $defaultSchema); foreach ($description as $field) { /** @var \Phalcon\Db\ColumnInterface $field */ $fieldDefinition = []; switch ($field->getType()) { case Column::TYPE_INTEGER: $fieldDefinition[] = "'type' => Column::TYPE_INTEGER"; $numericFields[$field->getName()] = true; break; case Column::TYPE_VARCHAR: $fieldDefinition[] = "'type' => Column::TYPE_VARCHAR"; break; case Column::TYPE_CHAR: $fieldDefinition[] = "'type' => Column::TYPE_CHAR"; break; case Column::TYPE_DATE: $fieldDefinition[] = "'type' => Column::TYPE_DATE"; break; case Column::TYPE_DATETIME: $fieldDefinition[] = "'type' => Column::TYPE_DATETIME"; break; case Column::TYPE_TIMESTAMP: $fieldDefinition[] = "'type' => Column::TYPE_TIMESTAMP"; break; case Column::TYPE_DECIMAL: $fieldDefinition[] = "'type' => Column::TYPE_DECIMAL"; $numericFields[$field->getName()] = true; break; case Column::TYPE_TEXT: $fieldDefinition[] = "'type' => Column::TYPE_TEXT"; break; case Column::TYPE_BOOLEAN: $fieldDefinition[] = "'type' => Column::TYPE_BOOLEAN"; break; case Column::TYPE_FLOAT: $fieldDefinition[] = "'type' => Column::TYPE_FLOAT"; break; case Column::TYPE_DOUBLE: $fieldDefinition[] = "'type' => Column::TYPE_DOUBLE"; break; case Column::TYPE_TINYBLOB: $fieldDefinition[] = "'type' => Column::TYPE_TINYBLOB"; break; case Column::TYPE_BLOB: $fieldDefinition[] = "'type' => Column::TYPE_BLOB"; break; case Column::TYPE_MEDIUMBLOB: $fieldDefinition[] = "'type' => Column::TYPE_MEDIUMBLOB"; break; case Column::TYPE_LONGBLOB: $fieldDefinition[] = "'type' => Column::TYPE_LONGBLOB"; break; case Column::TYPE_JSON: $fieldDefinition[] = "'type' => Column::TYPE_JSON"; break; case Column::TYPE_JSONB: $fieldDefinition[] = "'type' => Column::TYPE_JSONB"; break; case Column::TYPE_BIGINTEGER: $fieldDefinition[] = "'type' => Column::TYPE_BIGINTEGER"; break; default: throw new DbException('Unrecognized data type ' . $field->getType() . ' at column ' . $field->getName()); } if (null !== ($default = $field->getDefault())) { $fieldDefinition[] = "'default' => \"{$default}\""; } //if ($field->isPrimary()) { // $fieldDefinition[] = "'primary' => true"; //} if ($field->isUnsigned()) { $fieldDefinition[] = "'unsigned' => true"; } if ($field->isNotNull()) { $fieldDefinition[] = "'notNull' => true"; } if ($field->isAutoIncrement()) { $fieldDefinition[] = "'autoIncrement' => true"; } if (self::$_databaseConfig->adapter == 'Postgresql' && in_array($field->getType(), [Column::TYPE_BOOLEAN, Column::TYPE_INTEGER, Column::TYPE_BIGINTEGER])) { // nothing } else { if ($field->getSize()) { $fieldDefinition[] = "'size' => " . $field->getSize(); } else { $fieldDefinition[] = "'size' => 1"; } } if ($field->getScale()) { $fieldDefinition[] = "'scale' => " . $field->getScale(); } if ($oldColumn != null) { $fieldDefinition[] = "'after' => '" . $oldColumn . "'"; } else { $fieldDefinition[] = "'first' => true"; } $oldColumn = $field->getName(); $tableDefinition[] = $snippet->getColumnDefinition($field->getName(), $fieldDefinition); $allFields[] = "'" . $field->getName() . "'"; } $indexesDefinition = []; $indexes = self::$_connection->describeIndexes($table, $defaultSchema); foreach ($indexes as $indexName => $dbIndex) { /** @var \Phalcon\Db\Index $dbIndex */ $indexDefinition = []; foreach ($dbIndex->getColumns() as $indexColumn) { $indexDefinition[] = "'" . $indexColumn . "'"; } $indexesDefinition[] = $snippet->getIndexDefinition($indexName, $indexDefinition, $dbIndex->getType()); } $referencesDefinition = []; $references = self::$_connection->describeReferences($table, $defaultSchema); foreach ($references as $constraintName => $dbReference) { $columns = []; foreach ($dbReference->getColumns() as $column) { $columns[] = "'" . $column . "'"; } $referencedColumns = []; foreach ($dbReference->getReferencedColumns() as $referencedColumn) { $referencedColumns[] = "'" . $referencedColumn . "'"; } $referenceDefinition = []; $referenceDefinition[] = "'referencedSchema' => '" . $dbReference->getReferencedSchema() . "'"; $referenceDefinition[] = "'referencedTable' => '" . $dbReference->getReferencedTable() . "'"; $referenceDefinition[] = "'columns' => [" . join(",", $columns) . "]"; $referenceDefinition[] = "'referencedColumns' => [" . join(",", $referencedColumns) . "]"; $referenceDefinition[] = "'onUpdate' => '" . $dbReference->getOnUpdate() . "'"; $referenceDefinition[] = "'onDelete' => '" . $dbReference->getOnDelete() . "'"; $referencesDefinition[] = $snippet->getReferenceDefinition($constraintName, $referenceDefinition); } $optionsDefinition = []; $tableOptions = self::$_connection->tableOptions($table, $defaultSchema); foreach ($tableOptions as $optionName => $optionValue) { if (self::$_skipAI && strtoupper($optionName) == "AUTO_INCREMENT") { $optionValue = ''; } $optionsDefinition[] = "'" . strtoupper($optionName) . "' => '" . $optionValue . "'"; } $classVersion = preg_replace('/[^0-9A-Za-z]/', '', $version); $className = Text::camelize($table) . 'Migration_' . $classVersion; // morph() $classData = $snippet->getMigrationMorph($className, $table, $tableDefinition); if (count($indexesDefinition)) { $classData .= $snippet->getMigrationDefinition('indexes', $indexesDefinition); } if (count($referencesDefinition)) { $classData .= $snippet->getMigrationDefinition('references', $referencesDefinition); } if (count($optionsDefinition)) { $classData .= $snippet->getMigrationDefinition('options', $optionsDefinition); } $classData .= " ]\n );\n }\n"; // up() $classData .= $snippet->getMigrationUp(); if ($exportData == 'always') { $classData .= $snippet->getMigrationBatchInsert($table, $allFields); } $classData .= "\n }\n"; // down() $classData .= $snippet->getMigrationDown(); if ($exportData == 'always') { $classData .= $snippet->getMigrationBatchDelete($table); } $classData .= "\n }\n"; // afterCreateTable() if ($exportData == 'oncreate') { $classData .= $snippet->getMigrationAfterCreateTable($table, $allFields); } // end of class $classData .= "\n}\n"; // dump data if ($exportData == 'always' || $exportData == 'oncreate') { $fileHandler = fopen(self::$_migrationPath . $version . '/' . $table . '.dat', 'w'); $cursor = self::$_connection->query('SELECT * FROM ' . $table); $cursor->setFetchMode(Db::FETCH_ASSOC); while ($row = $cursor->fetchArray()) { $data = []; foreach ($row as $key => $value) { if (isset($numericFields[$key])) { if ($value === '' || is_null($value)) { $data[] = 'NULL'; } else { $data[] = addslashes($value); } } else { $data[] = "'" . addslashes($value) . "'"; } unset($value); } fputs($fileHandler, join('|', $data) . PHP_EOL); unset($row); unset($data); } fclose($fileHandler); } return $classData; }
/** * Allows to executes the statement again. Some database systems don't support scrollable cursors, * So, as cursors are forward only, we need to execute the cursor again to fetch rows from the begining * @return mixed */ public function execute() { $this->_connection->setConsistency($this->_consistency); return $this->_connection->query($this->_clqStatement, $this->_bindParams, $this->_bindTypes); }