public function test_migration_initializes_migration_table()
{
// because Migration was instantiated in setup, the version table should exist
$metadata = new Metadata($this->adapter);
$tableNames = $metadata->getTableNames();
$this->assertContains(MigrationVersion::TABLE_NAME, $tableNames);
}
protected function ensureMigrationsTableExists()
{
$metadata = new Metadata($this->adapter);
if (!in_array($this->migrationsTableName, $metadata->getTableNames())) {
$schema = new Schema\Schema($this->adapter);
$schema->createTable($this->migrationsTableName, ['id' => false], function ($t) {
$t->string('version');
});
$schema->addIndex($this->migrationsTableName, 'version', ['unique' => true]);
}
}
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);
}
}
/**
* Can the factory create an instance for the service?
*
* For Service manager V3
* Edit 'use' section if need:
* Change:
* 'use Zend\ServiceManager\AbstractFactoryInterface;' for V2 to
* 'use Zend\ServiceManager\Factory\AbstractFactoryInterface;' for V3
*
* @param Interop\Container\ContainerInterface $container
* @param string $requestedName
* @return bool
*/
public function canCreate(ContainerInterface $container, $requestedName)
{
if (!isset($this->tableNames)) {
$db = $container->has('db') ? $container->get('db') : null;
if (isset($db)) {
$dbMetadata = new Metadata($db);
$this->tableNames = $dbMetadata->getTableNames();
} else {
$this->tableNames = false;
}
}
//is there table with same name?
if (is_array($this->tableNames) && in_array($requestedName, $this->tableNames, true)) {
return true;
} else {
return false;
}
}
/**
* @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;
}
/**
* Função que gera um print dos metadados
*/
public function getPrintMetadata()
{
$metadata = new Metadata($this->getDbAdapter());
foreach ($metadata->getSchemas() as $valueSchema) {
echo 'In Schema ' . $valueSchema . PHP_EOL;
// get the table names
$tableNames = $metadata->getTableNames($valueSchema);
foreach ($tableNames as $tableName) {
echo 'In Table ' . $tableName . PHP_EOL;
$table = $metadata->getTable($tableName, $valueSchema);
echo ' With columns: ' . PHP_EOL;
foreach ($table->getColumns() as $column) {
echo ' ' . $column->getName() . ' -> ' . $column->getDataType() . PHP_EOL;
}
echo PHP_EOL;
echo ' With constraints: ' . PHP_EOL;
foreach ($metadata->getConstraints($tableName, $valueSchema) as $constraint) {
/** @var $constraint Zend\Db\Metadata\Object\ConstraintObject */
echo ' ' . $constraint->getName() . ' -> ' . $constraint->getType() . PHP_EOL;
if (!$constraint->hasColumns()) {
continue;
}
echo ' column: ' . implode(', ', $constraint->getColumns());
if ($constraint->isForeignKey()) {
$fkCols = array();
foreach ($constraint->getReferencedColumns() as $refColumn) {
$fkCols[] = $constraint->getReferencedTableName() . '.' . $refColumn;
}
echo ' => ' . implode(', ', $fkCols);
}
echo PHP_EOL;
}
echo '----' . PHP_EOL;
}
echo '-------------------------------------------' . PHP_EOL;
}
}
/**
* @param $module
* @param $version
* @param $adapter_name
* @return array
*/
public function fetchColumns($module, $version, $adapter_name)
{
$tables = [];
if (!isset($this->config['db']['adapters'])) {
// error
}
$config = $this->config['db']['adapters'];
$adapter = new Adapter($config[$adapter_name]);
try {
$metadata = new Metadata($adapter);
} catch (InvalidArgumentException $e) {
if (strpos($e->getMessage(), 'Unknown adapter platform') === false) {
throw $e;
}
return [];
}
$tableNames = $metadata->getTableNames(null, true);
foreach ($tableNames as $tableName) {
if ($this->moduleHasService($module, $version, $tableName)) {
continue;
}
$tableData = ['table_name' => $tableName];
$table = $metadata->getTable($tableName);
$tableData['columns'] = [];
$constraints = $this->getConstraints($metadata, $tableName);
/** @var \Zend\Db\Metadata\Object\ColumnObject $column */
foreach ($table->getColumns() as $column) {
$item = ['name' => $column->getName(), 'type' => $column->getDataType(), 'required' => !$column->isNullable(), 'filters' => [], 'validators' => [], 'constraints' => []];
foreach ($constraints as $constraint) {
if ($column->getName() == $constraint['column']) {
$item['constraints'][] = ucfirst(strtolower($constraint['type']));
switch (strtoupper($constraint['type'])) {
case 'PRIMARY KEY':
break;
case 'FOREIGN KEY':
$constraintObj = $this->getConstraintForColumn($metadata, $tableName, $column->getName());
$validator = $this->validators['foreign_key'];
$referencedColumns = $constraintObj->getReferencedColumns();
$validator['options'] = ['adapter' => $adapter_name, 'table' => $constraintObj->getReferencedTableName(), 'field' => $referencedColumns[0]];
$item['validators'][] = $validator;
break;
case 'UNIQUE':
$validator = $this->validators['unique'];
$validator['options'] = ['adapter' => $adapter_name, 'table' => $tableName, 'field' => $column->getName()];
$item['validators'][] = $validator;
break;
}
}
}
if (in_array(strtolower($column->getDataType()), ['varchar', 'text'])) {
$item['length'] = $column->getCharacterMaximumLength();
if (in_array('Primary key', array_values($item['constraints']))) {
unset($item['filters']);
unset($item['validators']);
$tableData['columns'][] = $item;
continue;
}
$item['filters'] = $this->filters['text'];
$validator = $this->validators['text'];
$validator['options']['max'] = $column->getCharacterMaximumLength();
$item['validators'][] = $validator;
} elseif (in_array(strtolower($column->getDataType()), ['tinyint', 'smallint', 'mediumint', 'int', 'bigint'])) {
$item['length'] = $column->getNumericPrecision();
if (in_array('Primary key', array_values($item['constraints']))) {
unset($item['filters']);
unset($item['validators']);
$tableData['columns'][] = $item;
continue;
}
$item['filters'] = $this->filters['integer'];
}
$tableData['columns'][] = $item;
}
$tables[] = $tableData;
}
return $tables;
}
/**
* For static tables set
*
* @param ContainerInterface $container
* @return array|false
*/
protected function getCachedTables(ContainerInterface $container)
{
if (!isset($this->tableNames)) {
if ($this->setDbAdapter($container)) {
$dbMetadata = new Metadata($this->db);
$this->tableNames = $dbMetadata->getTableNames();
} else {
$this->tableNames = false;
}
}
return $this->tableNames;
}
/**
* Initialize migration history table
*
* @return $this
*/
protected function init()
{
if (!$this->isInited) {
$metadata = new Metadata($this->adapter);
if (!in_array($this->getStoreTableName(), $metadata->getTableNames())) {
$this->runMigration($this->getInitialMigrationFile(), Migration::ACTION_UP);
}
$this->isInited = true;
}
return $this;
}
/**
* @param string $name
* @return bool
*/
public function isTableExists($name)
{
$metadata = new Metadata($this->adapter);
return in_array($name, $metadata->getTableNames());
}