Skip to content

Library for generating of SQL in PHP.

License

Notifications You must be signed in to change notification settings

czproject/sql-generator

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

44 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

CzProject\SqlGenerator

Build Status Downloads this Month Latest Stable Version License

Donate

Installation

Download a latest package or use Composer:

composer require czproject/sql-generator

CzProject\SqlGenerator requires PHP 5.6.0 or later.

Usage

use CzProject\SqlGenerator\Drivers;
use CzProject\SqlGenerator\SqlDocument;

$sql = new SqlDocument;
$driver = new Drivers\MysqlDriver;

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

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

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

$output = $sql->toSql($driver);

Outputs:

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:

$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:

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

Check if is SQL document empty:

$sql->isEmpty();

Generate SQL:

$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:

TableName

Delimited table name.

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.

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'

Supported database

Currently is supported common SQL and MySQL.


License: New BSD License
Author: Jan Pecha, https://www.janpecha.cz/