public static function getDefinition() { $def = new DefinitionBuilder(); $def->addAutoIncrementId(); // Autoincrementing INT id $def->username = DataType::string(255); // Username VARCHAR(255) $def->password = DataType::string(255); // Password VARCHAR(255) $def->addtimeStamps(); // Timestamps: 'created' and 'updated' $def->addUnique('username', 'username'); // A unique index on the username field return $def; }
/** * {@inheritdoc} */ public function getDefinition($table) { $result = $this->db->query('SHOW COLUMNS FROM `' . $this->db->tableName($table) . '`'); $schema = new DefinitionBuilder(); while ($row = $result->fetchAssoc()) { $column = $row['Field']; $schema->{$column} = $this->toDataType($row); } $result = $this->db->query('SHOW INDEX FROM `' . $this->db->tableName($table) . '`'); $keys = array(); while ($row = $result->fetchAssoc()) { $key = $row['Key_name']; $column = $row['Column_name']; $unique = $row['Non_unique'] == 0 ? true : false; if (isset($keys[$key])) { $keys[$key]['columns'][] = $column; } else { $keys[$key] = array('columns' => array($column), 'unique' => $unique); } } foreach ($keys as $name => $key) { if ($key['unique']) { $schema->addUnique($key['columns'], $name); } else { $schema->addKey($key['columns'], $name); } } return $schema; }
use Jivoo\Data\Database\DatabaseSchema; use Jivoo\Data\Database\Loader; use Jivoo\Data\DefinitionBuilder; use Jivoo\Log\CallbackHandler; use Jivoo\Log\Logger; // Include Jivoo by using composer: require '../vendor/autoload.php'; // Initialize database loader $loader = new Loader(); // Log database queries to output $logger = new Logger(); $logger->addHandler(new CallbackHandler(function (array $record) { if (isset($record['context']['query'])) { echo 'query: ' . $record['context']['query'] . PHP_EOL; } })); $loader->setLogger($logger); // Initialize definition for User-table $definition = new DatabaseDefinitionBuilder(); $definition->addDefinition('User', DefinitionBuilder::auto(['username', 'created'])); // Connect to database $schema = new DatabaseSchema($loader->connect(['driver' => 'PdoMysql', 'server' => 'localhost', 'username' => 'jivoo', 'database' => 'jivoo'], $definition)); echo '<pre>'; // Get data for root user: print_r($schema->User->where('username = %s', 'root')->first()->getData()); // List names of users created after 2015-01-01 $users = $schema->User->where('created > %d', '2015-01-01')->orderBy('created'); foreach ($users as $user) { echo $user->username . PHP_EOL; } echo '</pre>';
/** * {@inheritdoc} */ public function renameColumn($table, $column, $newName) { try { $definition = $this->db->getDefinition()->getDefinition($table); $this->db->beginTransaction(); $tempName = $table . '_MigrationBackup'; $this->createTable($tempName, $definition); $sql = 'INSERT INTO ' . $this->db->quoteModel($tempName); $sql .= ' SELECT * FROM ' . $this->db->quoteModel($table); $this->db->execute($sql); $this->dropTable($table); $newDefinition = new DefinitionBuilder($definition); $type = $newDefinition->getType($column); unset($newDefinition->{$column}); $newDefinition->{$newName} = $type; $this->createTable($table, $newDefinition); $columns = array(); foreach ($definition->getFields() as $field) { if ($field != $column) { $columns[] = $field; } } $columns[] = $column; $sql = 'INSERT INTO ' . $this->db->quoteModel($table); $sql .= ' SELECT ' . implode(', ', $columns) . ' FROM ' . $this->db->quoteModel($tempName); $this->db->execute($sql); $this->dropTable($tempName); $this->db->commit(); } catch (\Exception $e) { $this->db->rollback(); throw $e; } }
/** * {@inheritdoc} */ public function getDefinition($table) { $result = $this->db->query("SELECT * FROM information_schema.columns WHERE table_name = '" . $this->db->tableName($table) . "'"); $definition = new DefinitionBuilder(); while ($row = $result->fetchAssoc()) { $column = $row['column_name']; $definition->{$column} = $this->toDataType($row); } $sql = 'SELECT i.relname AS index_name, a.attname AS column_name, indisunique, indisprimary FROM'; $sql .= ' pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE'; $sql .= ' t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid'; $sql .= " AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r'"; $sql .= " AND t.relname = '" . $this->db->tableName($table) . "'"; $result = $this->db->query($sql); $keys = array(); while ($row = $result->fetchAssoc()) { $key = $row['index_name']; $column = $row['column_name']; $unique = $row['indisunique'] != 0; if (isset($keys[$key])) { $keys[$key]['columns'][] = $column; } else { $keys[$key] = array('columns' => array($column), 'unique' => $unique); } } foreach ($keys as $name => $key) { $name = preg_replace('/^' . preg_quote($this->db->tableName($table) . '_', '/') . '/', '', $name, 1, $count); if ($count == 0) { continue; } if ($key['unique']) { $definition->addUnique($key['columns'], $name); } else { $definition->addKey($key['columns'], $name); } } return $definition; }