Upscheme

https://upscheme.org
Admin
Upscheme is an easy-to-use PHP package for updating the database schema of your application and...
Tokens:47,663
Snippets:464
Trust Score:6
Update:4 months ago
Show doc for...
Tokens:
Raw
### Get and Set Sequence Start Value

Source: https://upscheme.org/

Retrieve the current start value of a sequence or set a new one. The `start()` method returns the current value when called without parameters.

```php
$value = $sequence->start();
$sequence->start( 1000 );
```

--------------------------------

### Multiple Migration Paths with Upscheme

Source: https://upscheme.org/

This example shows how to specify multiple directories containing migration tasks for Upscheme to process.

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


```

--------------------------------

### Install Upscheme using Composer

Source: https://upscheme.org/

Use Composer to add the Upscheme package to your project.

```bash
composer req aimeos/upscheme

```

--------------------------------

### Set table engine option

Source: https://upscheme.org/

Specify the storage engine for a table. This example sets the engine to 'InnoDB' for the 'test' table.

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

```

--------------------------------

### Multiple Database Connections with Upscheme

Source: https://upscheme.org/

Example of configuring and using Upscheme with multiple named database connections. The 'db' and 'temp' keys distinguish between the connections.

```php
$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();


```

--------------------------------

### Get Sequence Properties

Source: https://upscheme.org/

Retrieves properties of a sequence, such as its cache size, starting value, and step width. These values are accessed within a callback function provided to the sequence method.

```php
$this->db()->sequence( 'seq_test', function( $seq ) {
	// returns how many generated numbers are cached
	$cache = $seq->cache;

	// returns the number the sequence has started from
	$start = $seq->start;

	// returns the step width for newly generated numbers
	$step = $seq->step;
} );
```

--------------------------------

### Get Sequence Initial Value

Source: https://upscheme.org/

Retrieves the initial value from which the sequence starts generating numbers. This is an example of accessing sequence properties using magic getter methods.

```php
$value = $sequence->getInitialValue();
// same as
$value = $sequence->start();
```

--------------------------------

### Create a Database Sequence

Source: https://upscheme.org/

Creates a new database sequence with a specified name. Custom start values and step widths can be defined using callback functions.

```php
$this->db()->sequence( 'seq_test' );
```

```php
$this->db()->sequence( 'seq_test', function( $seq ) {
	$seq->start( 1000 )->step( 2 );
} );
```

--------------------------------

### Oracle Database Configuration for Upscheme

Source: https://upscheme.org/

Example of configuring Upscheme for an Oracle database. This includes specific parameters like 'dbname', 'service', and 'user'.

```php
$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>'
];


```

--------------------------------

### Set Foreign Key Update Action Example

Source: https://upscheme.org/

Demonstrates how to set the 'onUpdate' action for a foreign key constraint. It shows direct assignment, method calls, and chaining with other methods like 'onDelete'.

```php
$value = $foreign->onUpdate();

$foreign->onUpdate( 'SET NULL' );
// same as
$foreign->onUpdate = 'SET NULL';
// same as
$foreign->opt( 'onUpdate', 'SET NULL' );

$foreign->onUpdate( 'SET NULL' )->onDelete( 'SET NULL' );
```

--------------------------------

### SQLite Database Configuration for Upscheme

Source: https://upscheme.org/

Example of configuring Upscheme for an SQLite database. Note the use of the 'path' key for the database file location.

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


```

--------------------------------

### Get or Create DB Table

Source: https://upscheme.org/

Returns the table object for a given name. If the table doesn't exist, it will be created. Call `up()` to persist changes.

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

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

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

--------------------------------

### Create GUID Column

Source: https://upscheme.org/

Creates a new column of type 'guid'. If the column does not exist, it will be created.

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

--------------------------------

### DB::name()

Source: https://upscheme.org/

Gets the name of the current database.

```APIDOC
## DB::name()

### Description
Returns the name of the database.

### Method
`name() : string`

### Return Value
- **string** Database name

### Examples
```php
$db->name();
```
```

--------------------------------

### Get or Create DB Sequence

Source: https://upscheme.org/

Returns the sequence object for a given name. If the sequence doesn't exist, it will be created. Call `up()` to persist changes.

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

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

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

--------------------------------

### Add multiple columns with modifiers and indexes

Source: https://upscheme.org/

Example demonstrating how to add multiple columns to a table within a closure, including defining their types, modifiers (like unsigned), and indexes. This showcases a common pattern for defining table structures.

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

--------------------------------

### Get Table Name

Source: https://upscheme.org/

Retrieves the name of the table.

```php
public function name() : string
```

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

--------------------------------

### Sequence Information Retrieval

Source: https://upscheme.org/

Retrieves configuration details of a sequence, such as cache size, start value, and step width.

```APIDOC
## Sequence Information Retrieval

### Description
Retrieves configuration details of a sequence, such as cache size, start value, and step width.

### Method
`sequence( string $name, callable $callback )` (used within the callback)

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Accessing Properties within Callback
Inside the sequence configuration callback, the following properties are available:
- `$seq->cache` (int): Returns how many generated numbers are cached.
- `$seq->start` (int): Returns the number the sequence has started from.
- `$seq->step` (int): Returns the step width for newly generated numbers.

### Request Example
```php
$this->db()->sequence( 'seq_test', function( $seq ) {
    // returns how many generated numbers are cached
    $cache = $seq->cache;

    // returns the number the sequence has started from
    $start = $seq->start;

    // returns the step width for newly generated numbers
    $step = $seq->step;
} );
```
```

--------------------------------

### Get table engine option using __get

Source: https://upscheme.org/

Retrieves the 'engine' option for a table using the magic __get method. This is equivalent to calling the opt() method.

```php
$engine = $table->engine;

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


```

--------------------------------

### Get Database Name in PHP

Source: https://upscheme.org/

Retrieves the name of the currently connected database.

```php
$db->name();
```

--------------------------------

### Check Column Modifiers (Nullability)

Source: https://upscheme.org/

Inspect column modifiers to determine specific attributes. This example checks if a column is nullable.

```php
if( $table->string( 'code' )->null() ) {
	// The "code" columns is nullable
}

```

--------------------------------

### Set default charset and collation

Source: https://upscheme.org/

Configure the default character set and collation for string and text columns in a table. This example sets them to 'utf8mb4' and 'utf8mb4_unicode_ci'.

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

```

--------------------------------

### Table::guid()

Source: https://upscheme.org/

Creates a new column of type “guid” or returns the existing one. If the column doesn’t exist yet, it will be created.

```APIDOC
## Table::guid()

### Description
Creates a new column of type "guid" or returns the existing one.

### Method
`guid( string $name )`

### Parameters
#### Path Parameters
- **name** (string) - Required - Name of the column

### Response
#### Success Response
- **
	Aimeos\Upscheme\Schema\Column
** - Column object

### Examples
```php
$table->guid( 'testcol' );
```
```

--------------------------------

### Set Custom Schema Options for a Column

Source: https://upscheme.org/

Use the `opt()` method to set custom schema options for a column. This example sets the collation for a 'code' string column to 'utf8mb4'.

```php
$this->db()->table( 'test', function( $table ) {
	$table->string( 'code' )->opt( 'collation', 'utf8mb4' );
} );

```

--------------------------------

### Accessing Schema Objects (Table, Sequence)

Source: https://upscheme.org/

Get access to specific schema objects like tables and sequences using the table() and sequence() methods of the database schema object. If an object does not exist, it will be created.

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

--------------------------------

### Dual Database Connections for Operations

Source: https://upscheme.org/

Use two different database connections simultaneously for operations like SELECT from one and INSERT/UPDATE/DELETE from another. Pass TRUE as the second parameter to db() to get a schema with a new connection. Remember to close the new connection using close() when done.

```php
$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();
```

--------------------------------

### Get or Set Column Option

Source: https://upscheme.org/

This method allows you to retrieve or set specific options for a column, potentially for different database types. Provide the option name and optionally a value and target database type.

```php
public function opt( string $option, $value = null, $for = null )
```

```php
$value = $column->opt( 'length' );
$column->opt( 'length', 64 );
```

--------------------------------

### Set or get onDelete action for foreign key

Source: https://upscheme.org/

The `onDelete()` method can be used to set the action for deletion or to retrieve the current action. It supports fluent chaining with `onUpdate()`.

```php
$value = $foreign->onDelete();

$foreign->onDelete( 'SET NULL' );
// same as
$foreign->onDelete = 'SET NULL';
// same as
$foreign->opt( 'onDelete', 'SET NULL' );

$foreign->onDelete( 'SET NULL' )->onUpdate( 'SET NULL' );
```

--------------------------------

### Use Custom Column Methods in Table Creation

Source: https://upscheme.org/

Demonstrates using custom 'defaults' and 'utinyint' methods when creating or updating a table. This showcases the practical application of custom column shortcuts.

```php
$this->db()->table( 'test', function( $table ) {
	$table->defaults();
	$table->utinyint( 'status' );
} );

```

--------------------------------

### Get foreign key option value

Source: https://upscheme.org/

Access foreign key options like `onDelete` or `onUpdate` using magic getter `__get()`. This is equivalent to calling the `opt()` method.

```php
$value = $foreign->onDelete;
// same as
$value = $foreign->opt( 'onDelete' );
```

--------------------------------

### Get and Set Sequence Step Value

Source: https://upscheme.org/

Retrieve the current step value of a sequence or set a new one. The `step()` method returns the current value when called without parameters.

```php
$value = $sequence->step();
$sequence->step( 2 );
```

--------------------------------

### Get Sequence Name

Source: https://upscheme.org/

Retrieves the name of the sequence. This is a direct method call to get the sequence's identifier.

```php
$name = $sequence->name();
```

--------------------------------

### Apply Multiple Column Modifiers

Source: https://upscheme.org/

Chain multiple column modifier methods to define column properties. This example sets an 'int' column named 'number' to be nullable and unsigned.

```php
$this->db()->table( 'test', function( $table ) {
	$table->int( 'number' )->null( true )->unsigned( true );
} );

```

--------------------------------

### Basic Upscheme Migration Execution

Source: https://upscheme.org/

This snippet shows the basic usage of the Upscheme Up class to execute migration tasks. It requires a database configuration array and the path to migration files.

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

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

```

--------------------------------

### Table::uuid()

Source: https://upscheme.org/

Creates a new column of type 'guid' or returns the existing one. This is an alias for guid(). If the column doesn't exist, it will be created.

```APIDOC
## Table::uuid()

### Description
Creates a new column of type "guid" or returns the existing one. This method is an alias for guid(). If the column doesn’t exist yet, it will be created.

### Method
`uuid( string $name ) : Column`

### Parameters
#### Path Parameters
- **name** (string) - Required - Name of the column

### Response
#### Success Response
- **Column** - Column object

### Request Example
```php
$table->uuid( 'testcol' );
```
```

--------------------------------

### Get or Set Unsigned Flag

Source: https://upscheme.org/

Use this method to check if a column is unsigned or to set it. Pass a boolean to set, or call without arguments to get the current value.

```php
public function unsigned( bool $value = null )
```

```php
$value = $column->unsigned();
$column->unsigned( true );
```

--------------------------------

### Generate Migrations for Multiple Databases

Source: https://upscheme.org/

Create migration files for multiple databases at once by passing an array of connection keys from the configuration to the create() method.

```php
$config = [
	'db' => [
		'driver' => 'pdo_mysql',
		// ...
	],
	'order' => [
		'driver' => 'pdo_oci',
		// ...
	]
];

\Aimeos\Upscheme\Up::use( $config, 'migrations' )->create( ['db', 'order'] );
```

--------------------------------

### Get or Set Autoincrement Flag

Source: https://upscheme.org/

Use this method to check if a column is set as autoincrement or to set it. Pass a boolean to set, or call without arguments to get the current value.

```php
public function seq( bool $value = null )
```

```php
$value = $column->seq();
$column->seq( true );
```

--------------------------------

### Get or Set Column Nullability

Source: https://upscheme.org/

Use this method to check if a column can be null or to set its nullability. Pass a boolean to set, or call without arguments to get the current value.

```php
public function null( bool $value = null )
```

```php
$value = $column->null();
$column->null( true );
```

--------------------------------

### Get or Set Column Type

Source: https://upscheme.org/

Use this method to retrieve the current type of a column or set a new type. Pass a string to set, or call without arguments to get the current value.

```php
public function type( string $value = null )
```

```php
$value = $column->type();
$column->type( 'tinyint' );
```

--------------------------------

### Get or Set Column Scale

Source: https://upscheme.org/

Use this method to retrieve the current scale of a column or set a new scale. Pass an integer to set, or call without arguments to get the current value.

```php
public function scale( int $value = null )
```

```php
$value = $column->scale();
$column->scale( 3 );
```

--------------------------------

### Enabling Verbose Output for Upscheme Migrations

Source: https://upscheme.org/

Demonstrates how to enable verbose output for Upscheme migrations using the `verbose()` method. Different levels ('v', 'vv', 'vvv') control the verbosity.

```php
\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


```

--------------------------------

### Generate Migration Files from Database

Source: https://upscheme.org/

Automatically generate migration files for all sequences, tables, and views in a specified directory. Provide the database configuration and the target directory to the create() method.

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

\Aimeos\Upscheme\Up::use( $config, 'migrations' )->create();
```

--------------------------------

### Get or Set Column Precision

Source: https://upscheme.org/

Use this method to retrieve the current precision of a column or set a new precision. Pass an integer to set, or call without arguments to get the current value.

```php
public function precision( int $value = null )
```

```php
$value = $column->precision();
$column->precision( 10 );
```

--------------------------------

### Set Foreign Key Actions with Shortcut

Source: https://upscheme.org/

A shortcut method 'do()' can be used to set both 'onDelete' and 'onUpdate' actions to the same value.

```PHP
$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( 'parentid', 'users' )->do( 'SET NULL' );
} );

```

--------------------------------

### Get or Set Column Length

Source: https://upscheme.org/

Use this method to retrieve the current length of a column or set a new length. Pass an integer to set, or call without arguments to get the current value.

```php
public function length( int $value = null )
```

```php
$value = $column->length();
$column->length( 32 );
```

--------------------------------

### Create a new table with columns

Source: https://upscheme.org/

Define a new table named 'test' and add an auto-incrementing ID, a string 'label' column, and a tinyint 'status' column with a default value of 0.

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

```

--------------------------------

### Create a new table

Source: https://upscheme.org/

Creates a new table named 'test' with an ID, a label string column, and a tinyint status column with a default value of 0.

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


```

--------------------------------

### Get Column Name

Source: https://upscheme.org/

Retrieves the name of the column. This method takes no arguments.

```php
public function name() : string
```

```php
$name = $column->name();
```

--------------------------------

### Implement Custom Column Shortcut

Source: https://upscheme.org/

Create a 'utinyint' shortcut method for defining unsigned tinyint columns. This simplifies column creation by chaining the 'col' and 'unsigned' methods.

```php
\Aimeos\Upscheme\Schema\Table::marco( 'utinyint', function( string $name ) {
	return $this->col( $name, 'tinyint' )->unsigned( true );
} );

```

--------------------------------

### Sequence Creation and Configuration

Source: https://upscheme.org/

Methods for creating and configuring database sequences, which are used for generating sequential numbers in databases like Oracle and PostgreSQL.

```APIDOC
## Sequence Creation and Configuration

### Description
Methods for creating and configuring database sequences, which are used for generating sequential numbers in databases like Oracle and PostgreSQL.

### Methods
#### `sequence( string $name, ?callable $callback = null )`
Creates a new sequence or configures an existing one.

- `$name` (string): The name of the sequence.
- `$callback` (callable, optional): A callback function to configure the sequence using its methods like `start()` and `step()`.

### Request Example
```php
// Create a sequence with default settings
$this->db()->sequence( 'seq_test' );

// Create a sequence with custom start and step values
$this->db()->sequence( 'seq_test', function( $seq ) {
    $seq->start( 1000 )->step( 2 );
} );
```
```

--------------------------------

### Accessing Database Schemas

Source: https://upscheme.org/

Access different database schemas using the db() method. If multiple configurations are provided, specify the configuration key. If no key or an invalid key is given, the first configuration is returned.

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

--------------------------------

### Outputting Messages in Migrations

Source: https://upscheme.org/

Use the `info()` method to display messages during migration execution. The second parameter controls verbosity (`v`, `vv`, `vvv`), and the third parameter allows for indentation.

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

```

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

```

--------------------------------

### Set or Get Column Fixed Flag

Source: https://upscheme.org/

Set or retrieve the fixed flag for a column. A fixed column's size does not change.

```php
public function fixed( bool $value = null )

```

```php
$value = $column->fixed();
$column->fixed( true );

```

--------------------------------

### Registering a Custom DBAL Connection Method

Source: https://upscheme.org/

This snippet demonstrates how to register a custom connection method using `Up::macro` to transform a non-DBAL configuration into a Doctrine DBAL connection object.

```php
\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']
	] );
} );


```

--------------------------------

### Add Primary, Unique, and Spatial Indexes

Source: https://upscheme.org/

Demonstrates adding primary, unique, and spatial indexes to a single column, including custom naming for unique and spatial indexes. Note that custom names for primary keys might be ignored by some database systems.

```php
$this->db()->table( 'test', function( $table ) {
	// primary key
	$table->int( 'id' )->primary();
	$table->int( 'id' )->primary( 'pk_test_id' ); // ignored by MySQL, MariaDB, etc.

	// unique key
	$table->string( 'code' )->unique();
	$table->string( 'code' )->unique( 'unq_test_code' );

	// spatial index
	$table->col( 'location', 'point' )->spatial();
	$table->col( 'location', 'point' )->spatial( 'idx_test_location' );
} );
```

--------------------------------

### Set or Get Column Comment

Source: https://upscheme.org/

Set a descriptive comment for a column or retrieve its existing comment. This is useful for documenting column purposes.

```php
public function comment( string $value = null )

```

```php
$comment = $column->comment();
$column->comment( 'column comment' );

```

--------------------------------

### Set or Get Autoincrement Flag

Source: https://upscheme.org/

Configure a column as autoincrement or retrieve its current autoincrement status. This method is an alias for the `seq()` method.

```php
public function autoincrement( bool $value = null )

```

```php
$value = $column->autoincrement();
$column->autoincrement( true );

```

--------------------------------

### Migration Task Dependencies

Source: https://upscheme.org/

Specify execution order for migration tasks using `after()` and `before()` methods. Task names correspond to filenames without the `.php` extension. This ensures migrations are applied in the correct sequence.

```php
return new class( $this ) extends Base {

	public function after() : array
	{
		return ['CreateRefTable'];
	}

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

```

--------------------------------

### Set or Get Column Charset

Source: https://upscheme.org/

Configure the character set for a column or retrieve its current charset. This method is used for character encoding settings.

```php
public function charset( string $value = null )

```

```php
$comment = $column->charset();
$column->charset( 'utf8' );

```

--------------------------------

### Implement Custom Default Columns Shortcut

Source: https://upscheme.org/

Define a 'defaults' shortcut method to add common columns like id, ctime, mtime, and editor. This promotes consistency and reduces boilerplate code when creating tables.

```php
\Aimeos\Upscheme\Schema\Table::marco( 'defaults', function() {
	$this->id();
	$this->datetime( 'ctime' );
	$this->datetime( 'mtime' );
	$this->string( 'editor' );
	return $this;
} );

```

--------------------------------

### Enabling Verbose Output for Migrations

Source: https://upscheme.org/

Call the `verbose()` method on the `Up` class instance before executing migrations to enable the display of messages with different verbosity levels.

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

```

--------------------------------

### Set database-specific table options

Source: https://upscheme.org/

Use the opt() method to set database-specific options like charset and collation, providing the database type as the third argument to ensure compatibility across different servers.

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

```

--------------------------------

### Create a temporary table

Source: https://upscheme.org/

Mark a table as temporary by setting the 'temporary' property to true.

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

```

--------------------------------

### Create UUID column

Source: https://upscheme.org/

Creates a new column of type 'guid' (aliased as uuid) or returns the existing one. The column will be created if it does not already exist.

```php
public function uuid( string $name ) : Column
```

```php
$table->uuid( 'testcol' );
```

--------------------------------

### Build SQL Statement with Query Builder

Source: https://upscheme.org/

Returns a query builder for creating new SQL statements. Remember to quote table and column names using `$db->qi()`.

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

```php
$db->stmt()->delete( $db->qi( 'test' ) )
	->where( $db->qi( 'stat' ) . ' = ?' )->setParameter( 0, false )
	->executeStatement();
```

```php
$db->stmt()->update( $db->qi( 'test' ) )
	->where( $db->qi( 'stat' ) . '', '?' )->setParameter( 0, true )
	->executeStatement();
```

```php
$result = $db->stmt()->select( $db->qi( 'id' ), $db->qi( 'code' ) )
	->from( $db->qi( 'test' ) )
	->where( $db->qi( 'stat' ) . ' = 1' )
	->executeQuery();

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

--------------------------------

### DB::view()

Source: https://upscheme.org/

Creates a database view with the specified name and SQL query, optionally for a specific database type.

```APIDOC
## DB::view()

### Description
Creates a view with the given name if it doesn’t exist yet. If the view already exists, this method does nothing.

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

### Parameters
#### Path Parameters
- **$name** (string) - Required - The name of the view to create.
- **$sql** (string) - Required - The SELECT statement that defines the view's content.
- **$for** (array<string>|string|null) - Optional - Specifies the database type(s) for which this SQL statement is intended (e.g., "mysql", "postgresql", "sqlite"). If null, it applies to all supported types.

### Returns
- **self** The same object instance for fluid method calls.

### Examples
```php
// Create a simple view
$db->view( 'testview', 'SELECT * FROM testtable' );

// Create a view with a WHERE clause
$db->view( 'testview', 'SELECT id, label, status FROM testtable WHERE status = 1' );

// Create a view specifically for MySQL
$db->view( 'testview', 'SELECT * FROM `testtable` WHERE `status` = 1', 'mysql' );
```
```

--------------------------------

### Set or Get Column Collation

Source: https://upscheme.org/

Configure the collation for a column or retrieve its current collation. This method is used for character set ordering and comparison rules.

```php
public function collation( string $value = null )

```

```php
$comment = $column->collation();
$column->collation( 'binary' );

```

--------------------------------

### DB::up()

Source: https://upscheme.org/

Applies any pending schema changes to the database.

```APIDOC
## DB::up()

### Description
Applies the changes to the database schema.

### Method
```
public function up() : self
```

### Returns
- **self** The same object instance for fluid method calls.

### Examples
```php
$db->up();
```
```

--------------------------------

### Get Column Option Value

Source: https://upscheme.org/

Retrieve the value of a specific column option. This method provides an alternative to direct property access for column options.

```php
public function __get( string $name )

```

```php
$charset = $column->charset;

// same as
$charset = $column->opt( 'charset' );

```

--------------------------------

### Equivalent Database Table Definition using Doctrine DBAL

Source: https://upscheme.org/

This code shows the equivalent, more verbose, definition of the 'test' table using Doctrine DBAL directly. It highlights the complexity Upscheme abstracts away.

```php
$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] );

$platform = $conn->getDatabasePlatform();
if( $platform instanceof 
	Doctrine\DBAL
Platform
MySQLPlatform
	|| $platform instanceof 
	Doctrine
	DBAL
	Platform
	MariaDBPlatform
) {
	$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 );
}


```

--------------------------------

### Foreign Key Methods

Source: https://upscheme.org/

Details on magic methods and specific methods for interacting with foreign key constraints.

```APIDOC
## Foreign Key Methods

### Foreign::__call()

#### Description
Calls custom methods on the Foreign object. This allows for extending functionality with custom logic.

#### Method Signature
`public function __call( string $method, array $args )`

#### Parameters
- **method** (string) - The name of the custom method to call.
- **args** (array<mixed>) - An array of arguments to pass to the custom method.

#### Return Value
- **mixed** - The return value of the called custom method.

#### Examples
```php
// Registering a custom macro
\Aimeos\Upscheme\Schema\Foreign::macro( 'default', function() {
	$this->opts = ['onDelete' => 'SET NULL', 'onUpdate' => 'SET NULL'];
}
 );

// Calling the custom macro
$foreign->default();
```

#### Available Class Properties
- `$this->dbaltable`: Doctrine table schema.
- `$this->table`: Upscheme Table object.
- `$this->localcol`: Local column name or names.
- `$this->fktable`: Foreign table name.
- `$this->fkcol`: Foreign column name or names.
- `$this->name`: Foreign key name.
- `$this->opts`: Associative list of foreign key options (e.g., 'onDelete', 'onUpdate').

### Foreign::__get()

#### Description
Retrieves the value of a specified foreign key option.

#### Method Signature
`public function __get( string $name )`

#### Parameters
- **name** (string) - The name of the foreign key option (e.g., 'onDelete', 'onUpdate').

#### Return Value
- **mixed** - The value of the requested foreign key option.

#### Available Foreign Key Options
- `onDelete`: Action to take when the referenced row is deleted.
- `onUpdate`: Action to take when the referenced row is updated.

#### Possible Values for Options
- `CASCADE`: Update referenced value.
- `NO ACTION`: No change in referenced value (same as RESTRICT).
- `RESTRICT`: Forbid changing values.
- `SET DEFAULT`: Set referenced value to the default value.
- `SET NULL`: Set referenced value to NULL.

#### Examples
```php
$value = $foreign->onDelete;
// Equivalent to:
$value = $foreign->opt( 'onDelete' );
```

### Foreign::__set()

#### Description
Sets a new value for a specified foreign key option.

#### Method Signature
`public function __set( string $name, $value )`

#### Parameters
- **name** (mixed) - The name of the foreign key option (e.g., 'onDelete', 'onUpdate').
- **value** (mixed) - The new value for the foreign key option.

#### Available Foreign Key Options
- `onDelete`: Action to take when the referenced row is deleted.
- `onUpdate`: Action to take when the referenced row is updated.

#### Possible Values for Options
- `CASCADE`: Update referenced value.
- `NO ACTION`: No change in referenced value (same as RESTRICT).
- `RESTRICT`: Forbid changing values.
- `SET DEFAULT`: Set referenced value to the default value.
- `SET NULL`: Set referenced value to NULL.

#### Examples
```php
$foreign->onDelete = 'SET NULL';
// Equivalent to:
$foreign->onDelete( 'SET NULL' );
$foreign->opt( 'onDelete', 'SET NULL' );
```

### Foreign::do()

#### Description
Sets the action for a foreign key constraint. This method is used for setting actions like CASCADE, RESTRICT, etc.

#### Method Signature
`public function do( string $action ) : self`

#### Parameters
- **action** (string) - The action to perform (e.g., 'CASCADE', 'RESTRICT', 'SET NULL').

#### Return Value
- **self** - The current object instance for fluid method calls.

#### Possible Actions
- `CASCADE`: Delete or update referenced value.
- `NO ACTION`: No change in referenced value (same as RESTRICT).
- `RESTRICT`: Forbid changing values.
- `SET DEFAULT`: Set referenced value to the default value.
- `SET NULL`: Set referenced value to NULL.

#### Examples
```php
$foreign->do( 'RESTRICT' );
```

### Foreign::name()

#### Description
Retrieves the current name of the foreign key constraint.

#### Method Signature
`public function name() : string|null`

#### Return Value
- **string|null** - The name of the constraint, or NULL if no name is available.

#### Examples
```php
$fkname = $foreign->name();
```

### Foreign::onDelete()

#### Description
Sets the action to perform when the referenced row is deleted, or returns the current action if no value is provided.

#### Method Signature
`public function onDelete( string $value = null )`

#### Parameters
- **value** (string|null) - The action to set (e.g., 'SET NULL'), or NULL to retrieve the current value.

#### Return Value
- **self|string** - The current object instance for setting the value, or the current action string if no parameter is provided.

#### Available Actions
- `CASCADE`: Delete referenced value.
- `NO ACTION`: No change in referenced value.
- `RESTRICT`: Forbid changing values.
- `SET DEFAULT`: Set referenced value to the default value.
- `SET NULL`: Set referenced value to NULL.

#### Examples
```php
// Get current onDelete action
$value = $foreign->onDelete();

// Set onDelete action
$foreign->onDelete( 'SET NULL' );

// Equivalent to:
$foreign->onDelete = 'SET NULL';
$foreign->opt( 'onDelete', 'SET NULL' );

// Chaining multiple actions
$foreign->onDelete( 'SET NULL' )->onUpdate( 'SET NULL' );
```
```

--------------------------------

### Define a Database Table with Upscheme

Source: https://upscheme.org/

This snippet demonstrates how to define a database table, including columns, types, constraints, and indexes, using Upscheme's fluent API. It automatically handles schema updates and migrations.

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

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

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


```

--------------------------------

### Set table engine using property

Source: https://upscheme.org/

A shortcut to set the table engine by assigning a value directly to the 'engine' property.

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

```

--------------------------------

### Get foreign key constraint name

Source: https://upscheme.org/

Retrieve the name of the foreign key constraint using the `name()` method. It returns the name or NULL if no name is set.

```php
$fkname = $foreign->name();
```

--------------------------------

### Table::up()

Source: https://upscheme.org/

Applies the changes to the database schema. Returns the same object for fluid method calls.

```APIDOC
## Table::up()

### Description
Applies the changes to the database schema.

### Method
`up() : self`

### Response
#### Success Response
- **self** - Same object for fluid method calls

### Request Example
```php
$table->up();
```
```

--------------------------------

### Set or Get Column Default Value

Source: https://upscheme.org/

Configure the default value for a column or retrieve its current default value. This applies when no explicit value is provided during insertion.

```php
public function default( $value = null )

```

```php
$value = $column->default();
$column->default( 0 );

```

--------------------------------

### Quoting Values for SQL Statements

Source: https://upscheme.org/

Use the `q()` method to safely quote values before including them directly in SQL statements. This is crucial for preventing SQL injection vulnerabilities.

```php
$db = $this->db();

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

```

--------------------------------

### Perform a foreign key action

Source: https://upscheme.org/

Use the `do()` method to specify an action for the foreign key constraint, such as 'RESTRICT'. This method returns the object for fluid calls.

```php
$foreign->do( 'RESTRICT' );
```

--------------------------------

### Get Database Type

Source: https://upscheme.org/

The type method returns a string indicating the type of the database currently in use. This can be useful for conditional logic based on the database system.

```php
public function type() : string
```

```php
$type = $db->type();
```

--------------------------------

### Set table engine option using __set

Source: https://upscheme.org/

Sets the 'engine' option for a table to 'InnoDB' using the magic __set method. This is equivalent to calling the opt() method with a value.

```php
$table->engine = 'InnoDB';

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


```

--------------------------------

### Register Custom DB Method with Macro

Source: https://upscheme.org/

Extend the Upscheme DB object by registering custom methods using the macro() static method. These methods can access internal DB object properties.

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

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

```

--------------------------------

### Add Database Specific Column Type

Source: https://upscheme.org/

Use the `col()` method to add database-specific column types. This example adds a 'tinyint' status column to the 'test' table.

```php
$this->db()->table( 'test', function( $table ) {
	$table->col( 'status', 'tinyint' );
} );

```

--------------------------------

### Set or Get Sequence Cache Size

Source: https://upscheme.org/

Sets the number of sequence IDs to be cached by the client or retrieves the current cache size. Returns the object for chaining when setting the value.

```php
$value = $sequence->cache();
$sequence->cache( 100 );
```

--------------------------------

### Create Regular Index for Column

Source: https://upscheme.org/

Create a regular index on the column. An optional name can be provided; otherwise, it's generated automatically.

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

```

```php
$column->index();
$column->index( 'idx_col' );

```

--------------------------------

### Check for Database Sequence Existence in PHP

Source: https://upscheme.org/

Use this method to verify if one or more database sequences exist. Pass a string for a single sequence or an array for multiple.

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

--------------------------------

### Table::binary()

Source: https://upscheme.org/

Creates a new column of type 'binary' or returns the existing one. The column will be created if it doesn't already exist.

```APIDOC
## Table::binary()

### Description
Creates a new column of type "binary" or returns the existing one. If the column doesn’t exist yet, it will be created.

### Method
`public function binary( string $name, int $length = 255 ) : Column`

### Parameters
#### Path Parameters
- **name** (string) - Required - Name of the column
- **length** (int) - Optional - Length of the column in bytes (default: 255)

### Response
#### Success Response
- **Column object** (`
eturn **
Aimeos
Upscheme
Schema
Column**`)

### Request Example
```php
$table->binary( 'testcol' );
$table->binary( 'testcol', 32 );
```
```

--------------------------------

### Check for Database Table Existence in PHP

Source: https://upscheme.org/

Use this method to verify if one or more database tables exist. Pass a string for a single table or an array for multiple.

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

--------------------------------

### Anonymous Class Migration Task

Source: https://upscheme.org/

Implement a migration using an anonymous class. Ensure the namespace statement is first, and the class extends `Base` or implements `Iface`. This is suitable for simple, self-contained migrations.

```php
<?php

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


return new class( $this ) extends Base {

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

```

--------------------------------

### Register Custom Sequence Methods

Source: https://upscheme.org/

Allows registering custom methods (macros) for the Sequence class, providing access to its properties. These macros can then be called on sequence objects.

```php
\Aimeos\Upscheme\Schema\Sequence::macro( 'default', function() {
	$this->start( 1 )->step( 2 );
} );

$sequence->default();
```
Tokens:
Raw