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;
}
