CzProject\SqlGenerator

, _(*1)

, _(*2)

Installation

Download a latest package or use Composer:, _(*3)

composer require czproject/sql-generator

CzProject\SqlGenerator requires PHP 8.0 or later., _(*4)

Usage

``` php use CzProject\SqlGenerator\Drivers; use CzProject\SqlGenerator\SqlDocument;, _(*5)

$sql = new SqlDocument; $driver = new Drivers\MysqlDriver;, _(*6)

$contactTable = $sql->createTable('contact') ->setComment('Clients table.') ->setOption('ENGINE', 'InnoDB'); $contactTable->addColumn('id', 'INT', NULL, array('UNSIGNED' => NULL)) ->setAutoIncrement(); $contactTable->addColumn('name', 'VARCHAR(100)') ->setComment('Client name'); $contactTable->addColumn('surname', 'VARCHAR(100)'); $contactTable->addColumn('active', 'unsigned TINYINT') ->setDefaultValue(TRUE); $contactTable->addColumn('created', 'DATETIME'); $contactTable->addColumn('removed', 'DATETIME') ->setNullable();, _(*7)

$contactTable->addIndex(NULL, IndexDefinition::TYPE_PRIMARY) ->addColumn('id');, _(*8)

$contactTable->addIndex('name_surname', IndexDefinition::TYPE_UNIQUE) ->addColumn('name', 'ASC', 100) ->addColumn('surname', 'DESC', 100);, _(*9)

$output = $sql->toSql($driver);, _(*10)


Outputs:

``` sql
CREATE TABLE `contact` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL COMMENT 'Client name',
    `surname` VARCHAR(100) NOT NULL,
    `active` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
    `created` DATETIME NOT NULL,
    `removed` DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `name_surname` (`name` (100), `surname` (100) DESC)
)
COMMENT 'Clients table.'
ENGINE=InnoDB;

Statements

There is few predefined statements:, _(*11)

$sql->createTable($tableName);
$sql->dropTable($tableName);
$sql->renameTable($old, $new);
$sql->alterTable($tableName);
$sql->insert($tableName, $data);
$sql->command($command); // for example $sql->command('SET NAMES "utf8"');
$sql->comment($comment);

You can add custom statements:, _(*12)

$sql->addStatement(new Statements\CreateTable($tableName));

Check if is SQL document empty:, _(*13)

$sql->isEmpty();

Generate SQL:, _(*14)

$sql->toSql($driver); // returns string
$sql->getSqlQueries($driver); // returns string[]
$sql->save($file, $driver); // saves SQL into file

Special Values

There are value objects for specific cases:, _(*15)

TableName

Delimited table name., _(*16)

use CzProject\SqlGenerator\TableName;

$table = $sql->createTable(TableName::create('schema.table'))
$table->addForeignKey('fk_table_id', 'id', TableName::create('schema2.table2'), 'id');
// and more ($sql->renameTable(),...)

Value

Scalar/stringable/datetime value. It can be used in option values., _(*17)

use CzProject\SqlGenerator\Value;

$table->setOption('AUTO_INCREMENT', Value::create(123)); // generates AUTO_INCREMENT=123
$table->setOption('CHECKSUM', Value::create(FALSE)); // generates CHECKSUM=0
$table->setOption('COMPRESSION', Value::create('NONE')); // generates COMPRESSION='NONE'