PHP code example of aimeos / upscheme

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

aimeos / upscheme example snippets

$dbalManager = $conn->createSchemaManager();
$from = $manager->createSchema();
$to = $manager->createSchema();

if( $to->hasTable( 'test' ) ) {
	$table = $to->getTable( 'test' );
} else {
	$table = $to->createTable( 'test' );
}

$table->addOption( 'engine', 'InnoDB' );

$table->addColumn( 'id', 'integer', ['autoincrement' => true] );
$table->addColumn( 'domain', 'string', ['length' => 32] );

if( $conn->getDatabasePlatform()->getName() === 'mysql' ) {
	$table->addColumn( 'code', 'string', ['length' => 64, 'customSchemaOptions' => ['charset' => 'binary']] );
} else {
	$table->addColumn( 'code', 'string', ['length' => 64]] );
}

$table->addColumn( 'label', 'string', ['length' => 255] );
$table->addColumn( 'pos', 'integer', ['default' => 0] );
$table->addColumn( 'status', 'smallint', [] );
$table->addColumn( 'mtime', 'datetime', [] );
$table->addColumn( 'ctime', 'datetime', [] );
$table->addColumn( 'editor', 'string', ['length' => 255] );

$table->setPrimaryKey( ['id'] );
$table->addUniqueIndex( ['domain', 'code'] );
$table->addIndex( ['status', 'pos'] );

foreach( $from->getMigrateToSql( $to, $conn->getDatabasePlatform() ) as $sql ) {
	$conn->executeStatement( $sql );
}

$this->db()->table( 'test', function( $t ) {
	$t->engine = 'InnoDB';

	$t->id();
	$t->string( 'domain', 32 );
	$t->string( 'code', 64 )->opt( 'charset', 'binary', 'mysql' );
	$t->string( 'label', 255 );
	$t->int( 'pos' )->default( 0 );
	$t->smallint( 'status' );
	$t->default();

	$t->unique( ['domain', 'code'] );
	$t->index( ['status', 'pos'] );
} );

$config = [
	'driver' => 'pdo_mysql',
	'host' => '127.0.0.1',
	'dbname' => '<database>',
	'user' => '<dbuser>',
	'password' => '<secret>'
];

\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->up();

$config = [
	'driver' => 'pdo_sqlite',
	'path' => 'path/to/file.sq3'
];

$config = [
	'driver' => 'pdo_oci',
	'host' => '<host or IP>',
	'dbname' => '<SID or service name (Oracle 18+)>',
	'service' => true, // for Oracle 18+ only
	'user' => '<dbuser>',
	'password' => '<secret>'
];

\Aimeos\Upscheme\Up::macro( 'connect', function( array $cfg ) {

	return \Doctrine\DBAL\DriverManager::getConnection( [
		'driver' => $cfg['adapter'],
		'host' => $cfg['host'],
		'dbname' => $cfg['database'],
		'user' => $cfg['username'],
		'password' => $cfg['password']
	] );
} );

$config = [
	'db' => [
		'driver' => 'pdo_mysql',
		'host' => '127.0.0.1',
		'dbname' => '<database>',
		'user' => '<dbuser>',
		'password' => '<secret>'
	],
	'temp' => [
		'driver' => 'pdo_sqlite',
		'path' => '/tmp/mydb.sqlite'
	]
];

\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->up();

\Aimeos\Upscheme\Up::use( $config, ['src/migrations', 'ext/migrations'] )->up();

\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose()->up(); // most important only
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose( 'vv' )->up(); // more verbose
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose( 'vvv' )->up(); // debugging

namespace Aimeos\Upscheme\Task;
use Aimeos\Upscheme\Schema\Table;


class TestTable extends Base
{
	public function up()
	{
		$this->db()->table( 'test', function( Table $t ) {
			$t->id();
			$t->string( 'label' );
			$t->bool( 'status' );
		} );
	}
}

class TestTable extends Base
{
	public function after() : array
	{
		return ['CreateRefTable'];
	}

	public function before() : array
	{
		return ['InsertTestData'];
	}
}

$this->info( 'some message' );
$this->info( 'more verbose message', 'vv' );
$this->info( 'very verbose debug message', 'vvv' );

$this->info( 'some message' );
$this->info( 'second level message', 'v', 1 );
$this->info( 'third level message', 'v', 2 );

\Aimeos\Upscheme\Up::use( $config, '...' )->verbose()->up();

// $config = ['db' => [...], 'temp' => [...]];
// \Aimeos\Upscheme\Up::use( $config, '...' )->up();

$this->db();
$this->db( 'db' );
$this->db( 'temp' );

$db1 = $this->db();
$db2 = $this->db( 'db', true );

foreach( $db1->select( 'users', ['status' => false] ) as $row ) {
	$db2->insert( 'oldusers', $row );
}

$db2->delete( 'users', ['status' => false] );

$db2->close();

$table = $this->db()->table( 'users' );
$seq = $this->db()->sequence( 'seq_users' );

$db = $this->db();

if( $db->hasTable( 'users' ) ) {
    // The "users" table exists
}

if( $db->hasColumn( 'users', 'name' ) ) {
    // The "name" column in the "users" table exists
}

if( $db->hasIndex( 'users', 'idx_name' ) ) {
    // The "idx_name" index in the "users" table exists
}

if( $db->hasForeign( 'users_address', 'fk_users_id' ) ) {
    // The foreign key "fk_users_id" in the "users_address" table exists
}

if( $db->hasSequence( 'seq_users' ) ) {
    // The "seq_users" sequence exists
}

if( $db->hasView( 'testview' ) ) {
    // The "testview" view exists
}

$db = $this->db();

// Renames the table "users" to "accounts"
$db->renameTable( 'users', 'account' );

// Renames the column "label" to "name" in the "users" table
$db->renameColumn( 'users', 'label', 'name' );

// Renames the column "idx_label" to "idx_name" in the "users" table
$db->renameIndex( 'users', 'idx_label', 'idx_name' );

$db = $this->db();

// Drops the foreign key "fk_users_id" from the "users_address" table
$db->dropForeign( 'users_address', 'fk_users_id' );

// Drops the "idx_name" index from the "users" table
$db->dropIndex( 'users', 'idx_name' );

// Drops the "name" column from the "users" table
$db->dropColumn( 'users', 'name' );

// Drops the "seq_users" sequence
$db->dropSequence( 'seq_users' );

// Drops the "users" table
$db->dropTable( 'users' );

// Drops the "testview" view
$db->dropView( 'testview' );

$db1 = $this->db();
$db2 = $this->db( 'db', true );

$db2->transaction( function( $db2 ) use ( $db1 ) {

	foreach( $db1->select( 'users', ['status' => false] ) as $row )
	{
		$db2->insert( 'newusers', ['userid' => $row['id'], 'status' => true] );
		$db2->update( 'users', ['refid' => $db2->lastId()], ['id' => $row['id']] );
	}

	$db2->delete( 'newusers', ['status' => false] );
	$db2->close();
} );

$this->db()->transaction( function( $db ) {
	// $db->insert( ... )
	// $db->update( ... )
	// $db->delete( ... )
} );

$userid = 123;
$this->db()->transaction( function( $db ) use ( $userid ) {
	$db->insert( 'newusers', ['userid' => userid, 'status' => true] );
} );

$db = $this->db();

$result = $db->stmt()->select( 'id', 'name' )
	->from( 'users' )
	->where( 'status != ?' )
	->setParameter( 0, false )
	->execute();

$db->stmt()->delete( 'users' )
	->where( 'status != ?' )
	->setParameter( 0, false )
	->execute();

$db->stmt()->update( 'users' )
	->set( 'status', '?' )
	->where( 'status != ?' )
	->setParameters( [true, false] )
	->execute();

$db = $this->db();

$result = $db->stmt()->select( '*' )->from( 'products' )
	->where( 'status = ' . $db->q( $_GET['status'] ) )->execute();

$db = $this->db();

$result = $db->stmt()->select( $db->qi( 'key' ) )->from( 'products' )->execute();

$sql = 'SELECT id, label, status FROM product WHERE label LIKE ?';
$result = $this->db()->query( $sql, ['test%'] );

foreach( $result->iterateAssociative() as $row ) {
	// ...
}

$sql = 'UPDATE product SET status=? WHERE status=?';
$num = $this->db()->exec( $sql, [1, 0] );

$this->db()->for( 'mysql', 'CREATE FULLTEXT INDEX idx_text ON product (text)' );

public function __call( string $method, array $args )

\Aimeos\Upscheme\Schema\DB::macro( 'hasFkIndexes', function( $val ) {
	return $this->to->hasExplicitForeignKeyIndexes();
} );

$db->hasFkIndexes();
// returns true/false

$db->hasExplicitForeignKeyIndexes();

public function close() : void

$db = $this->db( 'temp', true );
$db->dropTable( 'test' );
$db->close();

public function delete( string $table, array $conditions = [] ) : self

$db->delete( 'test', ['status' => false, 'type' => 'old'] );
$db->delete( 'test' );

public function dropColumn( string $table, $name ) : self

$db->dropColumn( 'test', 'oldcol' );
$db->dropColumn( 'test', ['oldcol', 'oldcol2'] );

public function dropForeign( string $table, $name ) : self

$db->dropForeign( 'test', 'fk_old' );
$db->dropForeign( 'test', ['fk_old', 'fk_old2'] );

public function dropIndex( string $table, $name ) : self

$db->dropIndex( 'test', 'idx_old' );
$db->dropIndex( 'test', ['idx_old', 'idx_old2'] );

public function dropSequence( $name ) : self

$db->dropSequence( 'seq_old' );
$db->dropSequence( ['seq_old', 'seq_old2'] );

public function dropTable( $name ) : self

$db->dropTable( 'test' );
$db->dropTable( ['test', 'test2'] );

public function dropView( $name ) : self

$db->dropView( 'test' );
$db->dropView( ['test', 'test2'] );

public function exec( string $sql, array $params = [], array $types = [] ) : int

$sql = 'UPDATE product SET status=? WHERE status=?';
$num = $this->db()->exec( $sql, [1, 0] );

public function for( $type, $sql ) : self

$db->for( 'mysql', 'CREATE INDEX idx_test_label ON test (label(16))' );

$db->for( ['mysql', 'sqlite'], [
	'DROP INDEX unq_test_status',
	'UPDATE test SET status = 0 WHERE status IS NULL',
] );

public function hasColumn( string $table, $name ) : bool

$db->hasColumn( 'test', 'testcol' );
$db->hasColumn( 'test', ['testcol', 'testcol2'] );

public function hasForeign( string $table, $name ) : bool

$db->hasForeign( 'test', 'fk_testcol' );
$db->hasForeign( 'test', ['fk_testcol', 'fk_testcol2'] );

public function hasIndex( string $table, $name ) : bool

$db->hasIndex( 'test', 'idx_test_col' );
$db->hasIndex( 'test', ['idx_test_col', 'idx_test_col2'] );

public function hasSequence( $name ) : bool

$db->hasSequence( 'seq_test' );
$db->hasSequence( ['seq_test', 'seq_test2'] );

public function hasTable( $name ) : bool

$db->hasTable( 'test' );
$db->hasTable( ['test', 'test2'] );

public function hasView( $name ) : bool

$db->hasView( 'test' );
$db->hasView( ['test', 'test2'] );

	public function insert( string $table, array $data ) : self

$db->insert( 'test', ['label' => 'myvalue', 'status' => true] );

public function lastId( string $seq = null ) : string

$db->lastId();
$db->lastId( 'seq_test' );

public function name() : string

$db->name();

public function q( $value, $type = \Doctrine\DBAL\ParameterType::STRING ) : string

$result = $db->stmt()->select( '*' )->from( 'products' )
	->where( 'status = ' . $db->q( $_GET['status'] ) )->execute();

public function qi( string $identifier ) : string

$result = $db->stmt()->select( $db->qi( 'key' ) )->from( 'products' )->execute();

public function query( string $sql, array $params = [], array $types = [] ) : \Doctrine\DBAL\Result

$result = $db->query( 'SELECT id, label, status FROM product WHERE label LIKE ?', ['test%'] );

foreach( $result->iterateAssociative() as $row ) {
	// ...
}

public function renameColumn( string $table, $from, string $to = null ) : self

// single column
$db->renameColumn( 'testtable', 'test_col', 'test_column' );

// rename several columns at once
$db->renameColumn( 'testtable', ['tcol' => 'testcol', 'tcol2' => 'testcol2'] );

public function renameIndex( string $table, $from, string $to = null ) : self

// single index
$db->renameIndex( 'testtable', 'idxcol', 'idx_column' );

// rename several indexes at once
$db->renameIndex( 'testtable', ['idxcol' => 'idx_column', 'idxcol2' => 'idx_column2'] );

public function renameTable( $from, string $to = null ) : self

// single table
$db->renameTable( 'testtable', 'newtable' );

// rename several tables at once
$db->renameTable( ['testtable' => 'newtable', 'oldtable' => 'testtable2'] );

public function select( string $table, array $conditions = null ) : array

$db->select( 'test', ['status' => false, 'type' => 'old'] );
$db->select( 'test' );

public function sequence( string $name, \Closure $fcn = null ) : Sequence

$sequence = $db->sequence( 'seq_test' );

$sequence = $db->sequence( 'seq_test', function( $seq ) {
	$seq->start( 1000 )->step( 2 )->cache( 100 );
} )->up();

public function stmt() : \Doctrine\DBAL\Query\QueryBuilder

$db->stmt()->delete( 'test' )->where( 'stat = ?' )->setParameter( 0, false )->execute();
$db->stmt()->update( 'test' )->set( 'stat', '?' )->setParameter( 0, true )->execute();
$result = $db->stmt()->select( 'id', 'code' )->from( 'test' )->where( 'stat = 1' )->execute();

while( $row = $result->fetchAssociative() ) {
    $id = $row['id'];
}

public function table( string $name, \Closure $fcn = null ) : Table

$table = $db->table( 'test' );

$table = $db->table( 'test', function( $t ) {
	$t->id();
	$t->string( 'label' );
	$t->bool( 'status' );
} )->up();

public function transaction( \Closure $fcn ) : self

$this->db()->transaction( function( $db ) {
	// $db->insert( ... )
	// $db->update( ... )
	// $db->delete( ... )
} );

public function type() : string

$type = $db->type();

public function up() : self

$db->up();

public function update( string $table, array $data, array $conditions = [] ) : self

$db->update( 'test', ['status' => true] );
$db->update( 'test', ['status' => true], ['status' => false, 'type' => 'new'] );

public function view( string $name, string $sql, $for = null ) : self

class TestTable -> TestTable.php

php
$this->db()->table( 'test', function( $table ) {
	$table->id();
	$table->string( 'label' );
	$table->col( 'status', 'tinyint' )->default( 0 );
} );

php
$this->db()->table( 'test', function( $table ) {
	$table->opt( 'engine', 'InnoDB' );
} );

php
$this->db()->table( 'test', function( $table ) {
	$table->engine = 'InnoDB';
} );

php
$this->db()->table( 'test', function( $table ) {
	$table->temporary = true;
} );

php
$this->db()->table( 'test', function( $table ) {
	$table->charset = 'utf8mb4';
	$table->collation = 'utf8mb4_unicode_ci';
} );

php
$this->db()->table( 'test', function( $table ) {
	$table->opt( 'charset', 'utf8mb4', 'mysql' );
	$table->opt( 'collation', 'utf8mb4_unicode_ci', 'mysql' );
} );

php
if( $this->db()->hasTable( 'users' ) ) {
    // The "users" table exists
}

php
if( $this->db()->hasTable( ['users', 'addresses'] ) ) {
    // The "users" and "addresses" tables exist
}

php
$this->db()->table( 'test', function( $table ) {
	$table->id();
	$table->string( 'label' );
	$table->col( 'status', 'tinyint' )->default( 0 );
} );

php
$this->db()->table( 'test', function( $table ) {
	$table->string( 'code' );
	$table->col( 'status', 'tinyint' )->default( 1 );
} );

php
// Renames the table "users" to "accounts"
$this->db()->renameTable( 'users', 'account' );

php
// Renames the table "users" to "accounts" and "blog" to "posts"
$this->db()->renameTable( ['users' => 'account', 'blog' => 'posts'] );

php
$this->db()->dropTable( 'users' );

php
$this->db()->dropTable( ['users', 'addresses'] );

php
$this->db()->dropTable( 'notexist' );

php
public function __call( string $method, array $args )

php
\Aimeos\Upscheme\Schema\Table::macro( 'addConstraint', function( array $columns ) {
	return $this->to->addUniqueConstraint( $columns );
} );

$table->addConstraint( ['col1', 'col2'] );

php
$table->addUniqueConstraint( ['col1', 'col2'] );

php
public function __get( string $name )

php
$engine = $table->engine;

// same as
$engine = $table->opt( 'engine' );

php
public function __set( string $name, $value )

php
$table->engine = 'InnoDB';

// same as
$table->opt( 'engine', 'InnoDB' );

php
public function bigid( string $name = null ) : Column

php
$table->bigid();
$table->bigid( 'uid' );

php
public function bigint( string $name ) : Column

php
$table->bigint( 'testcol' );

php
public function binary( string $name, int $length = 255 ) : Column

php
$table->binary( 'testcol' );
$table->binary( 'testcol', 32 );

php
public function blob( string $name, int $length = 0x7fff ) : Column

php
$table->blob( 'testcol' );
$table->blob( 'testcol', 0x7fffffff );

php
$table->bool( 'testcol' );

php
$table->boolean( 'testcol' );

php
$table->char( 'testcol', 3 );

php
$table->col( 'testcol' );
$table->col( 'testcol', 'tinyint' );

php
public function date( string $name ) : Column

php
$table->date( 'testcol' );

php
public function datetime( string $name ) : Column

php
$table->datetime( 'testcol' );

php
public function datetimetz( string $name ) : Column

php
$table->datetimetz( 'testcol' );

php
public function dropColumn( $name ) : self

php
$table->dropColumn( 'testcol' );
$table->dropColumn( ['testcol', 'testcol2'] );

php
public function dropIndex( $name ) : self

php
public function dropForeign( $name ) : self

php
$table->dropForeign( 'fk_test_col' );
$table->dropForeign( ['fk_test_col', 'fk_test_col2'] );

php
public function dropPrimary() : self

php
$table->dropPrimary();

php
public function float( string $name ) : Column

php
$table->float( 'testcol' );

php
public function guid( string $name ) : Column

php
$table->guid( 'testcol' );

php
public function hasColumn( $name ) : bool

php
$table->hasColumn( 'testcol' );
$table->hasColumn( ['testcol', 'testcol2'] );

php
public function hasIndex( $name ) : bool

php
public function hasForeign( $name ) : bool

php
$table->hasForeign( 'fk_test_col' );
$table->hasForeign( ['fk_test_col', 'fk_test_col2'] );

php
public function id( string $name = null ) : Column

php
$table->id();
$table->id( 'uid' );

php
public function index( $columns, string $name = null ) : self

php
public function int( string $name ) : Column

php
$table->int( 'testcol' );

php
public function integer( string $name ) : Column

php
$table->integer( 'testcol' );

php
public function json( string $name ) : Column

php
$table->json( 'testcol' );

php
public function name() : string

php
$tablename = $table->name();

php
public function opt( string $name, $value = null )

php
public function primary( $columns, string $name = null ) : self

php
public function renameColumn( $from, string $to = null ) : self

php
public function renameIndex( $from, string $to = null ) : self

php
public function smallint( string $name ) : Column

php
$table->smallint( 'testcol' );

php
public function spatial( $columns, string $name = null ) : self

php
$table->string( 'testcol' );
$table->string( 'testcol', 32 );

php
public function text( string $name, int $length = 0xffff ) : Column