public function up(MetadataInterface $schema)
{
$create_repositories = new CreateTable('repository');
$create_repositories->addColumn(new Integer('id'));
$create_repositories->addConstraint(new PrimaryKey('id'));
$create_repositories->addColumn(new Varchar('url', 128));
$this->addSql($create_repositories->getSqlString($this->adapter->getPlatform()));
}
/**
* Create one table. if it is exsist, check the columns. if the column is exsist, change it's type, else create the column.
* @param unknown $tableName
* @param array $table
*/
private function create_table($tableName, $tableStructureData)
{
$adapter = $this->adapter;
$sql = new Sql($adapter);
if (!in_array($tableName, $this->tables)) {
// Create the $table
$CreateTable = new Ddl\CreateTable($tableName);
foreach ($tableStructureData['column'] as $column) {
$CreateTable->addColumn($column);
}
foreach ($tableStructureData['constraint'] as $constraint) {
$CreateTable->addConstraint($constraint);
}
$adapter->query($sql->getSqlStringForSqlObject($CreateTable), $adapter::QUERY_MODE_EXECUTE);
} else {
// Check the columns
$columns = $this->getColumns($tableName);
$constraints = $this->getConstraints($tableName);
$AlterTable = new Ddl\AlterTable($tableName);
foreach ($tableStructureData['column'] as $createColumn) {
$column_exsist = false;
foreach ($columns as $column) {
if ($createColumn->getName() == $column->getName()) {
$column_exsist = true;
}
}
if ($column_exsist) {
// Alter the table, change the column.
$AlterTable->changeColumn($createColumn->getName(), $createColumn);
} else {
// Alter the table, add the column.
$AlterTable->addColumn($createColumn);
}
}
// Delete exsisted constraints(mysql index) but PRIMARY KEY
$exsisted_constraints = $this->getConstraints($tableName);
foreach ($exsisted_constraints as $exsisted_constraint) {
if ($exsisted_constraint->getType() != 'PRIMARY KEY') {
$adapter->query('ALTER TABLE `' . $tableName . '`
DROP index `' . str_replace('_zf_' . $tableName . '_', '', $exsisted_constraint->getName()) . '`', $adapter::QUERY_MODE_EXECUTE);
}
}
// Add all constraints but PRIMARY KEY
foreach ($tableStructureData['constraint'] as $constraint) {
if ($constraint instanceof Constraint\PrimaryKey) {
// Do nothing
} else {
// Add to DB
$AlterTable->addConstraint($constraint);
}
}
$adapter->query($sql->getSqlStringForSqlObject($AlterTable), $adapter::QUERY_MODE_EXECUTE);
}
}
public function up(MetadataInterface $schema)
{
$create_test = new CreateTable('test');
$create_test->addColumn(new Integer('a'));
$sql = $create_test->getSqlString($this->adapter->getPlatform());
// attempt to drop a non-existing table on second statement
$drop_fake = new DropTable('fake');
$sql .= '; ' . $drop_fake->getSqlString($this->adapter->getPlatform());
// execute multi-statement sql
$this->addSql($sql);
}
public function install($config)
{
$dbinstall = new Dbinstall($config);
$table_account = new CreateTable('account');
$table_account->addColumn(new Column\Integer('id', FALSE, NULL, array('autoincrement' => true)))->addConstraint(new Constraint\PrimaryKey('id'))->addColumn(new Column\Varchar('username', 50, false))->addColumn(new Column\Varchar('password', 50, false))->addColumn(new Column\Varchar('email', 50, false))->addConstraint(new Constraint\UniqueKey('email'))->addColumn(new Column\Varchar('openid_qq', 100, true))->addConstraint(new Constraint\UniqueKey('openid_qq'))->addColumn(new Column\Varchar('openid_sina', 100, true))->addConstraint(new Constraint\UniqueKey('openid_sina'))->addColumn(new Column\Varchar('openid_wechat', 100, true))->addConstraint(new Constraint\UniqueKey('openid_wechat'))->addColumn(new Column\Integer('status', false, 0));
$dbinstall->addCreateTable($table_account);
$insert_account = new Insert('account');
$insert_account->values(array('username' => 'admin', 'password' => 'admin', 'email' => '*****@*****.**', 'status' => 1));
$dbinstall->addInsert($insert_account);
$dbinstall->install();
}
/**
* Create a new table
*
* @param string $name
* @return void
*/
public static function createTable($name)
{
$table = new CreateTable($name);
// Primary column
$primaryColumn = new Integer('id');
$primaryColumn->setOption('autoincrement', '');
$table->addColumn($primaryColumn);
$table->addConstraint(new PrimaryKey('id'));
// Status column
$statusColumn = new Boolean(STATUS_COLUMN_NAME, false, STATUS_DRAFT_NUM);
$table->addColumn($statusColumn);
$connection = Bootstrap::get('ZendDb');
$sql = new Sql($connection);
$connection->query($sql->getSqlStringForSqlObject($table), $connection::QUERY_MODE_EXECUTE);
}
/**
* Create a migration table
*
* @return bool
*/
public function createTable()
{
$sql = new Sql($this->adapter);
try {
$select = $sql->select(self::TABLE);
$queryString = $sql->getSqlStringForSqlObject($select);
$this->adapter->query($queryString, Adapter::QUERY_MODE_EXECUTE);
} catch (\Exception $err) {
$table = new Ddl\CreateTable(self::TABLE);
$table->addColumn(new Ddl\Column\Char('migration', 255));
$table->addColumn(new Ddl\Column\Text('up'));
$table->addColumn(new Ddl\Column\Text('down'));
$queryString = $sql->getSqlStringForSqlObject($table);
$this->adapter->query($queryString, Adapter::QUERY_MODE_EXECUTE);
}
}
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);
}
/**
* @param null|PlatformInterface $platform
* @return string
*/
public function getSqlString(PlatformInterface $platform = null)
{
// localize variables
foreach (get_object_vars($this->createTable) as $name => $value) {
$this->{$name} = $value;
}
return parent::getSqlString($platform);
}
/**
* Create migrations table of not exists
*/
protected function checkCreateMigrationTable()
{
$table = new Ddl\CreateTable($this->getMigrationTableName($this->config['prefix']));
$table->addColumn(new Ddl\Column\BigInteger('version'));
if ($this->adapter->platform->getName() == 'PostgreSQL') {
$table->addColumn(new Ddl\Column\Integer('id', true));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('version'));
} else {
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
}
$table->addConstraint(new Ddl\Constraint\UniqueKey('version'));
$sql = new Sql($this->adapter);
try {
$this->adapter->query($sql->getSqlStringForSqlObject($table), Adapter::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 createTableCities()
{
$table = new Ddl\CreateTable('cities');
$table->addColumn(new Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Column\Integer('region_id', false, null));
$table->addColumn(new Column\Integer('country_id', false, null));
$table->addColumn(new Column\Varchar('name', 255));
$table->addConstraint(new Constraint\PrimaryKey('id'));
$this->create($table);
return true;
}
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
}
}
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;
}
}
public function up(MetadataInterface $schema)
{
$create_commit = new CreateTable('commit');
$create_commit->addColumn(new Integer('commit_id'));
$create_commit->addConstraint(new PrimaryKey('commit_id'));
$create_commit->addColumn(new Integer('repository_id'));
$create_commit->addConstraint(new ForeignKey('fk_repository', 'repository_id', 'repository', 'id'));
$create_commit->addColumn(new Varchar('commit_hash', 40));
$create_commit->addColumn(new Datetime('commit_date'));
$create_commit->addColumn(new Varchar('commit_author', 48, true));
$create_commit->addColumn(new Text('commit_message'));
$this->addSql($create_commit->getSqlString($this->adapter->getPlatform()));
$create_commit_file_status = new CreateTable('commit_file_status');
$create_commit_file_status->addColumn(new Integer('commit_file_status_id'));
$create_commit_file_status->addConstraint(new Primarykey('commit_file_status_id'));
$create_commit_file_status->addColumn(new Integer('commit_id'));
$create_commit_file_status->addConstraint(new ForeignKey('fk_commit', 'commit_id', 'commit', 'commit_id'));
$create_commit_file_status->addColumn(new Char('status', 1));
$create_commit_file_status->addColumn(new Varchar('name', 512));
$this->addSql($create_commit_file_status->getSqlString($this->adapter->getPlatform()));
}
/**
* Create migrations table of not exists
*/
public function checkCreateMigrationTable()
{
$table = new Ddl\CreateTable(MigrationVersion::TABLE_NAME);
$table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
$table->addColumn(new Ddl\Column\BigInteger('version'));
$table->addColumn(new Ddl\Column\Varchar('source', 64));
$table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$table->addConstraint(new Ddl\Constraint\UniqueKey('version'));
$sql = new Sql($this->adapter);
try {
$this->adapter->query($sql->getSqlStringForSqlObject($table), Adapter::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
}
}
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
}
}
/**
* @return Adapter
*/
protected function getAdapter()
{
global $globalTestConfiguration;
if (!isset($globalTestConfiguration) || !isset($globalTestConfiguration['zenddb']) || !isset($globalTestConfiguration['zenddb']['driver'])) {
$this->markTestIncomplete('Invalid configuration found in test.config.php. Make sure "zenddb" is set and contains' . 'a valid config array for Zend\\Db\\Adapter\\Adapter');
}
$this->adapter = $adapter = new Adapter($globalTestConfiguration['zenddb']);
// attempt to connect
$adapter->getDriver()->getConnection()->connect();
$this->assertTrue($adapter->getDriver()->getConnection()->isConnected(), 'DB connection established.');
$meta = new Metadata($adapter);
$sql = new Sql($adapter);
static::$schemaCleanup = true;
// drop previous tables if needed
if (in_array('model', $meta->getTableNames())) {
$ddl = new Ddl\DropTable('model');
$adapter->query($sql->getSqlStringForSqlObject($ddl))->execute();
}
if (in_array('minimalmodel', $meta->getTableNames())) {
$ddl = new Ddl\DropTable('minimalmodel');
$adapter->query($sql->getSqlStringForSqlObject($ddl))->execute();
}
// create test tables
$ddl = new Ddl\CreateTable('model');
$ddl->addColumn(new Ddl\Column\Integer('id', true, null, ['auto_increment' => true]));
$ddl->addColumn((new Ddl\Column\Varchar('magicProperty', 255))->setNullable(true));
$ddl->addColumn((new Ddl\Column\Varchar('protectedProperty', 255))->setNullable(true));
$ddl->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$adapter->query($sql->getSqlStringForSqlObject($ddl), $adapter::QUERY_MODE_EXECUTE);
$ddl = new Ddl\CreateTable('minimalmodel');
$ddl->addColumn(new Ddl\Column\Integer('id', true, null, ['auto_increment' => true]));
$ddl->addColumn((new Ddl\Column\Varchar('name', 255))->setNullable(true));
$ddl->addColumn((new Ddl\Column\Varchar('value', 255))->setNullable(true));
$ddl->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$adapter->query($sql->getSqlStringForSqlObject($ddl), $adapter::QUERY_MODE_EXECUTE);
// return the adapter
return $adapter;
}
<?php
/**
* Created by PhpStorm.
* User: Glenn
* Date: 2016-04-21
* Time: 1:50 PM
*/
use Zend\Db\Sql\Ddl\CreateTable;
use Zend\Db\Sql\Ddl\Column;
use Zend\Db\Sql\Ddl\Constraint;
use Zend\Db\Sql\Ddl\DropTable;
require "./vendor/autoload.php";
$container = array();
include "./config/db.env.php";
$adapter = new \Zend\Db\Adapter\Adapter($container['settings']['db']);
$adapter->query("DROP TABLE IF EXISTS `users`", $adapter::QUERY_MODE_EXECUTE);
$table = new CreateTable('users');
$idColumn = new Column\Integer('id', false, NULL, array('autoincrement' => true));
$table->addColumn($idColumn);
$table->addColumn(new Column\Varchar('name', 255));
$table->addColumn(new Column\Varchar('username', 255));
$table->addColumn(new Column\Varchar('password', 255));
$table->addColumn(new Column\Varchar('email', 255));
$table->addConstraint(new Constraint\PrimaryKey('id'));
$adapter->query($table->getSqlString(new Zend\Db\Adapter\Platform\Mysql()), $adapter::QUERY_MODE_EXECUTE);
$adapter->query("ALTER TABLE `users` MODIFY COLUMN id INT auto_increment", $adapter::QUERY_MODE_EXECUTE);
/**
* 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);
}
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
}
}
public function up(MetadataInterface $schema)
{
$create_test = new CreateTable('test');
$create_test->addColumn(new Integer('a'));
$this->addSql($create_test->getSqlString($this->adapter->getPlatform()));
}
<?php
use DbuMigration\MigrateManagerInterface;
use DbuMigration\MigrateManager;
use Zend\Db\Sql\Ddl\Column;
use Zend\Db\Sql\Ddl\Constraint;
use Zend\Db\Sql\Ddl\CreateTable;
/**
* @var $this DbuMigration\Migration
*/
$this->on($this::ACTION_UP, function (MigrateManagerInterface $manager) {
$tableName = $manager instanceof MigrateManager ? $manager->getStoreTableName() : $manager->getServiceLocator()->get('Config')['migrate_manager']['db_table_name'];
if ($manager->isTableExists($tableName)) {
throw new Exception(sprintf('Migration history store table "%s" already exists.', $tableName));
}
$table = new CreateTable($tableName);
$table->addColumn(new Column\Varchar('version', 255));
$table->addColumn(new Column\Integer('created_at'));
$table->addConstraint(new Constraint\PrimaryKey('version'));
$manager->createTable($table);
return true;
});
$this->on($this::ACTION_DOWN, function (MigrateManagerInterface $manager) {
$tableName = $manager instanceof MigrateManager ? $manager->getStoreTableName() : $manager->getServiceLocator()->get('Config')['migrate_manager']['db_table_name'];
$manager->dropTable($tableName);
return true;
});
/**
* 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;
}
protected function _createTables()
{
$existingTables = $this->_metadata->getTableNames();
// Xhb
$table = Xhb::MAIN_TABLE;
if (!in_array($table, $existingTables)) {
$createStmt = new CreateTable($table);
$createStmt->addColumn(new Varchar('id', 64, false))->addColumn(new Varchar('title', 128))->addColumn(new Integer('car_category', true))->addColumn(new Integer('auto_smode', true, 0))->addColumn(new Integer('auto_weekday', true, 1))->addColumn(new Timestamp('updated_at'))->addConstraint(new PrimaryKey('id'));
$this->_db->query($this->_sql->buildSqlString($createStmt), Adapter::QUERY_MODE_EXECUTE);
}
// Accounts
$table = Account::MAIN_TABLE;
if (!in_array($table, $existingTables)) {
$createStmt = new CreateTable($table);
$createStmt->addColumn(new Integer('xhb_id', false))->addColumn(new Integer('key', false))->addColumn(new Integer('pos', true))->addColumn(new Integer('type', true))->addColumn(new Varchar('name', 128))->addColumn(new Varchar('number', 128, true))->addColumn(new Varchar('bankname', 128, true))->addColumn(new Floating('initial', 10, 4, false, 0))->addColumn(new Floating('minimum', 10, 4, false, 0))->addColumn(new Timestamp('updated_at'))->addConstraint(new PrimaryKey(array('xhb_id', 'key')))->addConstraint(new ForeignKey('FK_XHB_ID', 'xhb_id', Xhb::MAIN_TABLE, 'id', 'CASCADE', 'CASCADE'));
$this->_db->query($this->_sql->buildSqlString($createStmt), Adapter::QUERY_MODE_EXECUTE);
}
// Categories
$table = Category::MAIN_TABLE;
if (!in_array($table, $existingTables)) {
$createStmt = new CreateTable($table);
$createStmt->addColumn(new Integer('xhb_id', false))->addColumn(new Integer('key', false))->addColumn(new Integer('parent', true))->addColumn(new Varchar('name', 128))->addColumn(new Integer('flags', true))->addColumn(new Floating('b0', 10, 4, false, 0))->addColumn(new Timestamp('updated_at'))->addConstraint(new PrimaryKey(array('xhb_id', 'key')))->addConstraint(new ForeignKey('FK_XHB_ID', 'xhb_id', Xhb::MAIN_TABLE, 'id', 'CASCADE', 'CASCADE'));
$this->_db->query($this->_sql->buildSqlString($createStmt), Adapter::QUERY_MODE_EXECUTE);
}
// Payees
$table = Payee::MAIN_TABLE;
if (!in_array($table, $existingTables)) {
$createStmt = new CreateTable($table);
$createStmt->addColumn(new Integer('xhb_id', false))->addColumn(new Integer('key', false))->addColumn(new Varchar('name', 128))->addColumn(new Timestamp('updated_at'))->addConstraint(new PrimaryKey(array('xhb_id', 'key')))->addConstraint(new ForeignKey('FK_XHB_ID', 'xhb_id', Xhb::MAIN_TABLE, 'id', 'CASCADE', 'CASCADE'));
$this->_db->query($this->_sql->buildSqlString($createStmt), Adapter::QUERY_MODE_EXECUTE);
}
// Operations
$table = Operation::MAIN_TABLE;
if (!in_array($table, $existingTables)) {
$createStmt = new CreateTable($table);
$createStmt->addColumn(new Integer('xhb_id', false))->addColumn(new Integer('id', false, null, array('auto_increment' => true)))->addColumn(new Date('date'))->addColumn(new Integer('account', false))->addColumn(new Varchar('info', 128, true))->addColumn(new Floating('amount', 10, 4, false, 0))->addColumn(new Integer('dst_account', true))->addColumn(new Integer('paymode', true))->addColumn(new Integer('st', false, 0))->addColumn(new Integer('flags', true))->addColumn(new Integer('payee', true))->addColumn(new Integer('category', true))->addColumn(new Varchar('wording', 128, true))->addColumn(new Varchar('tags', 128, true))->addColumn(new Varchar('scat', 128, true))->addColumn(new Varchar('samt', 128, true))->addColumn(new Varchar('smem', 128, true))->addColumn(new Integer('kxfer', true))->addColumn(new Floating('account_balance', 10, 4, false, 0))->addColumn(new Floating('general_balance', 10, 4, false, 0))->addColumn(new Timestamp('updated_at'))->addConstraint(new PrimaryKey(array('xhb_id', 'id')))->addConstraint(new ForeignKey('FK_XHB_ID', 'xhb_id', Xhb::MAIN_TABLE, 'id', 'CASCADE', 'CASCADE'));
$this->_db->query($this->_sql->buildSqlString($createStmt), Adapter::QUERY_MODE_EXECUTE);
}
// Operations - Split amount
$table = Operation::SPLIT_AMOUNT_TABLE;
if (!in_array($table, $existingTables)) {
$createStmt = new CreateTable($table);
$createStmt->addColumn(new Integer('xhb_id', false))->addColumn(new Integer('operation_id', false))->addColumn(new Floating('amount', 10, 4, true, 0))->addColumn(new Integer('category', true))->addColumn(new Varchar('wording', 128, true))->addConstraint(new ForeignKey('FK_XHB_ID', 'xhb_id', Xhb::MAIN_TABLE, 'id', 'CASCADE', 'CASCADE'))->addConstraint(new ForeignKey('FK_OPERATION_ID', 'operation_id', Operation::MAIN_TABLE, 'id'));
$this->_db->query($this->_sql->buildSqlString($createStmt), Adapter::QUERY_MODE_EXECUTE);
}
}
/**
* @param StreamName $streamName
* @param array $metadata
* @param bool $returnSql
* @return string|null Whether $returnSql is true or not function will return generated sql or execute it directly
*/
public function createSchemaFor(StreamName $streamName, array $metadata = array(), $returnSql = false)
{
$createTable = new CreateTable($this->getTable($streamName));
$createTable->addColumn(new Varchar('event_id', 100))->addColumn(new Integer('version'))->addColumn(new Varchar('event_name', 100))->addColumn(new Varchar('event_class', 100))->addColumn(new Text('payload'))->addColumn(new Varchar('created_at', 50));
foreach ($metadata as $key => $value) {
$createTable->addColumn(new Varchar($key, 100));
}
$createTable->addConstraint(new PrimaryKey('event_id'));
if ($returnSql) {
return $createTable->getSqlString($this->dbAdapter->getPlatform());
}
$this->dbAdapter->getDriver()->getConnection()->execute($createTable->getSqlString($this->dbAdapter->getPlatform()));
}
/**
* @covers Zend\Db\Sql\Ddl\CreateTable::getSqlString
*/
public function testGetSqlString()
{
$ct = new CreateTable('foo');
$this->assertEquals("CREATE TABLE \"foo\" (\n)", $ct->getSqlString());
$ct = new CreateTable('foo', true);
$this->assertEquals("CREATE TEMPORARY TABLE \"foo\" (\n)", $ct->getSqlString());
$ct = new CreateTable('foo');
$ct->addColumn(new Column('bar'));
$this->assertEquals("CREATE TABLE \"foo\" (\n \"bar\" INTEGER NOT NULL\n)", $ct->getSqlString());
$ct = new CreateTable('foo', true);
$ct->addColumn(new Column('bar'));
$this->assertEquals("CREATE TEMPORARY TABLE \"foo\" (\n \"bar\" INTEGER NOT NULL\n)", $ct->getSqlString());
}
/**
* Creates the table structure for you
*
* NOTE: This code should fully work when ZendFramework 2.4.0 is released, since then DDL supports auto_increment
* @see https://github.com/zendframework/zf2/pull/6257
*
* @return PersistenceHandlerInterface
*/
public function createTable()
{
$sql = $this->getSql();
$createTable = new CreateTable($this->getTableName());
$tableStructure = $this->getTableStructure();
foreach ($tableStructure['columns'] as $column) {
$createTable->addColumn($column);
}
foreach ($tableStructure['constraints'] as $constraint) {
$createTable->addConstraint($constraint);
}
$sql->getAdapter()->query($sql->getSqlStringForSqlObject($createTable), Adapter::QUERY_MODE_EXECUTE);
return $this;
}
