File Content: CreateStatement.php <?php declare(strict_types=1); namespace PhpMyAdmin\SqlParser\Statements; use PhpMyAdmin\SqlParser\Components\ArrayObj; use PhpMyAdmin\SqlParser\Components\CreateDefinition; use PhpMyAdmin\SqlParser\Components\DataType; use PhpMyAdmin\SqlParser\Components\Expression; use PhpMyAdmin\SqlParser\Components\OptionsArray; use PhpMyAdmin\SqlParser\Components\ParameterDefinition; use PhpMyAdmin\SqlParser\Components\PartitionDefinition; use PhpMyAdmin\SqlParser\Parser; use PhpMyAdmin\SqlParser\Statement; use PhpMyAdmin\SqlParser\Token; use PhpMyAdmin\SqlParser\TokensList; use function is_array; use function trim; /** * `CREATE` statement. */ class CreateStatement extends Statement { /** * Options for `CREATE` statements. * * @var array<string, int|array<int, int|string>> * @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> */ public static $OPTIONS = [ // CREATE TABLE 'TEMPORARY' => 1, // CREATE VIEW 'OR REPLACE' => 2, 'ALGORITHM' => [ 3, 'var=', ], // `DEFINER` is also used for `CREATE FUNCTION / PROCEDURE` 'DEFINER' => [ 4, 'expr=', ], // Used in `CREATE VIEW` 'SQL SECURITY' => [ 5, 'var', ], 'DATABASE' => 6, 'EVENT' => 6, 'FUNCTION' => 6, 'INDEX' => 6, 'UNIQUE INDEX' => 6, 'FULLTEXT INDEX' => 6, 'SPATIAL INDEX' => 6, 'PROCEDURE' => 6, 'SERVER' => 6, 'TABLE' => 6, 'TABLESPACE' => 6, 'TRIGGER' => 6, 'USER' => 6, 'VIEW' => 6, 'SCHEMA' => 6, // CREATE TABLE 'IF NOT EXISTS' => 7, ]; /** * All database options. * * @var array<string, int|array<int, int|string>> * @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> */ public static $DB_OPTIONS = [ 'CHARACTER SET' => [ 1, 'var=', ], 'CHARSET' => [ 1, 'var=', ], 'DEFAULT CHARACTER SET' => [ 1, 'var=', ], 'DEFAULT CHARSET' => [ 1, 'var=', ], 'DEFAULT COLLATE' => [ 2, 'var=', ], 'COLLATE' => [ 2, 'var=', ], ]; /** * All table options. * * @var array<string, int|array<int, int|string>> * @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> */ public static $TABLE_OPTIONS = [ 'ENGINE' => [ 1, 'var=', ], 'AUTO_INCREMENT' => [ 2, 'var=', ], 'AVG_ROW_LENGTH' => [ 3, 'var', ], 'CHARACTER SET' => [ 4, 'var=', ], 'CHARSET' => [ 4, 'var=', ], 'DEFAULT CHARACTER SET' => [ 4, 'var=', ], 'DEFAULT CHARSET' => [ 4, 'var=', ], 'CHECKSUM' => [ 5, 'var', ], 'DEFAULT COLLATE' => [ 6, 'var=', ], 'COLLATE' => [ 6, 'var=', ], 'COMMENT' => [ 7, 'var=', ], 'CONNECTION' => [ 8, 'var', ], 'DATA DIRECTORY' => [ 9, 'var', ], 'DELAY_KEY_WRITE' => [ 10, 'var', ], 'INDEX DIRECTORY' => [ 11, 'var', ], 'INSERT_METHOD' => [ 12, 'var', ], 'KEY_BLOCK_SIZE' => [ 13, 'var', ], 'MAX_ROWS' => [ 14, 'var', ], 'MIN_ROWS' => [ 15, 'var', ], 'PACK_KEYS' => [ 16, 'var', ], 'PASSWORD' => [ 17, 'var', ], 'ROW_FORMAT' => [ 18, 'var', ], 'TABLESPACE' => [ 19, 'var', ], 'STORAGE' => [ 20, 'var', ], 'UNION' => [ 21, 'var', ], 'PAGE_COMPRESSED' => [ 22, 'var', ], 'PAGE_COMPRESSION_LEVEL' => [ 23, 'var', ], ]; /** * All function options. * * @var array<string, int|array<int, int|string>> * @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> */ public static $FUNC_OPTIONS = [ 'NOT' => [ 2, 'var', ], 'FUNCTION' => [ 3, 'var=', ], 'PROCEDURE' => [ 3, 'var=', ], 'CONTAINS SQL' => 4, 'NO SQL' => 4, 'READS SQL DATA' => 4, 'MODIFIES SQL DATA' => 4, 'SQL SECURITY' => [ 6, 'var', ], 'LANGUAGE' => [ 7, 'var', ], 'COMMENT' => [ 8, 'var', ], 'CREATE' => 1, 'DETERMINISTIC' => 2, ]; /** * All trigger options. * * @var array<string, int|array<int, int|string>> * @psalm-var array<string, (positive-int|array{positive-int, ('var'|'var='|'expr'|'expr=')})> */ public static $TRIGGER_OPTIONS = [ 'BEFORE' => 1, 'AFTER' => 1, 'INSERT' => 2, 'UPDATE' => 2, 'DELETE' => 2, ]; /** * The name of the entity that is created. * * Used by all `CREATE` statements. * * @var Expression|null */ public $name; /** * The options of the entity (table, procedure, function, etc.). * * Used by `CREATE TABLE`, `CREATE FUNCTION` and `CREATE PROCEDURE`. * * @see static::$TABLE_OPTIONS * @see static::$FUNC_OPTIONS * @see static::$TRIGGER_OPTIONS * * @var OptionsArray|null */ public $entityOptions; /** * If `CREATE TABLE`, a list of columns and keys. * If `CREATE VIEW`, a list of columns. * * Used by `CREATE TABLE` and `CREATE VIEW`. * * @var CreateDefinition[]|ArrayObj|null */ public $fields; /** * If `CREATE TABLE WITH`. * If `CREATE TABLE AS WITH`. * If `CREATE VIEW AS WITH`. * * Used by `CREATE TABLE`, `CREATE VIEW` * * @var WithStatement|null */ public $with; /** * If `CREATE TABLE ... SELECT`. * If `CREATE VIEW AS ` ... SELECT`. * * Used by `CREATE TABLE`, `CREATE VIEW` * * @var SelectStatement|null */ public $select; /** * If `CREATE TABLE ... LIKE`. * * Used by `CREATE TABLE` * * @var Expression|null */ public $like; /** * Expression used for partitioning. * * @var string|null */ public $partitionBy; /** * The number of partitions. * * @var int|null */ public $partitionsNum; /** * Expression used for subpartitioning. * * @var string|null */ public $subpartitionBy; /** * The number of subpartitions. * * @var int|null */ public $subpartitionsNum; /** * The partition of the new table. * * @var PartitionDefinition[]|null */ public $partitions; /** * If `CREATE TRIGGER` the name of the table. * * Used by `CREATE TRIGGER`. * * @var Expression|null */ public $table; /** * The return data type of this routine. * * Used by `CREATE FUNCTION`. * * @var DataType|null */ public $return; /** * The parameters of this routine. * * Used by `CREATE FUNCTION` and `CREATE PROCEDURE`. * * @var ParameterDefinition[]|null */ public $parameters; /** * The body of this function or procedure. * For views, it is the select statement that creates the view. * Used by `CREATE FUNCTION`, `CREATE PROCEDURE` and `CREATE VIEW`. * * @var Token[]|string */ public $body = []; /** * @return string */ public function build() { $fields = ''; if (! empty($this->fields)) { if (is_array($this->fields)) { $fields = CreateDefinition::build($this->fields) . ' '; } elseif ($this->fields instanceof ArrayObj) { $fields = ArrayObj::build($this->fields); } } if ($this->options->has('DATABASE') || $this->options->has('SCHEMA')) { return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . OptionsArray::build($this->entityOptions); } if ($this->options->has('TABLE')) { if ($this->select !== null) { return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . $this->select->build(); } if ($this->like !== null) { return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' LIKE ' . Expression::build($this->like); } if ($this->with !== null) { return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . $this->with->build(); } $partition = ''; if (! empty($this->partitionBy)) { $partition .= "\nPARTITION BY " . $this->partitionBy; } if (! empty($this->partitionsNum)) { $partition .= "\nPARTITIONS " . $this->partitionsNum; } if (! empty($this->subpartitionBy)) { $partition .= "\nSUBPARTITION BY " . $this->subpartitionBy; } if (! empty($this->subpartitionsNum)) { $partition .= "\nSUBPARTITIONS " . $this->subpartitionsNum; } if (! empty($this->partitions)) { $partition .= "\n" . PartitionDefinition::build($this->partitions); } return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . $fields . OptionsArray::build($this->entityOptions) . $partition; } elseif ($this->options->has('VIEW')) { $builtStatement = ''; if ($this->select !== null) { $builtStatement = $this->select->build(); } elseif ($this->with !== null) { $builtStatement = $this->with->build(); } return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . $fields . ' AS ' . $builtStatement . (! empty($this->body) ? TokensList::build($this->body) : '') . ' ' . OptionsArray::build($this->entityOptions); } elseif ($this->options->has('TRIGGER')) { return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . OptionsArray::build($this->entityOptions) . ' ' . 'ON ' . Expression::build($this->table) . ' ' . 'FOR EACH ROW ' . TokensList::build($this->body); } elseif ($this->options->has('PROCEDURE') || $this->options->has('FUNCTION')) { $tmp = ''; if ($this->options->has('FUNCTION')) { $tmp = 'RETURNS ' . DataType::build($this->return); } return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . ParameterDefinition::build($this->parameters) . ' ' . $tmp . ' ' . OptionsArray::build($this->entityOptions) . ' ' . TokensList::build($this->body); } return 'CREATE ' . OptionsArray::build($this->options) . ' ' . Expression::build($this->name) . ' ' . TokensList::build($this->body); } /** * @param Parser $parser the instance that requests parsing * @param TokensList $list the list of tokens to be parsed */ public function parse(Parser $parser, TokensList $list) { ++$list->idx; // Skipping `CREATE`. // Parsing options. $this->options = OptionsArray::parse($parser, $list, static::$OPTIONS); ++$list->idx; // Skipping last option. $isDatabase = $this->options->has('DATABASE') || $this->options->has('SCHEMA'); $fieldName = $isDatabase ? 'database' : 'table'; // Parsing the field name. $this->name = Expression::parse( $parser, $list, [ 'parseField' => $fieldName, 'breakOnAlias' => true, ] ); if (! isset($this->name) || ($this->name === '')) { $parser->error('The name of the entity was expected.', $list->tokens[$list->idx]); } else { ++$list->idx; // Skipping field. } /** * Token parsed at this moment. */ $token = $list->tokens[$list->idx]; $nextidx = $list->idx + 1; while ($nextidx < $list->count && $list->tokens[$nextidx]->type === Token::TYPE_WHITESPACE) { ++$nextidx; } if ($isDatabase) { $this->entityOptions = OptionsArray::parse($parser, $list, static::$DB_OPTIONS); } elseif ($this->options->has('TABLE')) { if (($token->type === Token::TYPE_KEYWORD) && ($token->keyword === 'SELECT')) { /* CREATE TABLE ... SELECT */ $this->select = new SelectStatement($parser, $list); } elseif ($token->type === Token::TYPE_KEYWORD && ($token->keyword === 'WITH')) { /* CREATE TABLE WITH */ $this->with = new WithStatement($parser, $list); } elseif ( ($token->type === Token::TYPE_KEYWORD) && ($token->keyword === 'AS') && ($list->tokens[$nextidx]->type === Token::TYPE_KEYWORD) ) { if ($list->tokens[$nextidx]->value === 'SELECT') { /* CREATE TABLE ... AS SELECT */ $list->idx = $nextidx; $this->select = new SelectStatement($parser, $list); } elseif ($list->tokens[$nextidx]->value === 'WITH') { /* CREATE TABLE WITH */ $list->idx = $nextidx; $this->with = new WithStatement($parser, $list); } } elseif ($token->type === Token::TYPE_KEYWORD && $token->keyword === 'LIKE') { /* CREATE TABLE `new_tbl` LIKE 'orig_tbl' */ $list->idx = $nextidx; $this->like = Expression::parse( $parser, $list, [ 'parseField' => 'table', 'breakOnAlias' => true, ] ); // The 'LIKE' keyword was found, but no table_name was found next to it if ($this->like === null) { $parser->error('A table name was expected.', $list->tokens[$list->idx]); } } else { $this->fields = CreateDefinition::parse($parser, $list); if (empty($this->fields)) { $parser->error('At least one column definition was expected.', $list->tokens[$list->idx]); } ++$list->idx; $this->entityOptions = OptionsArray::parse($parser, $list, static::$TABLE_OPTIONS); /** * The field that is being filled (`partitionBy` or * `subpartitionBy`). * * @var string */ $field = null; /** * The number of brackets. `false` means no bracket was found * previously. At least one bracket is required to validate the * expression. * * @var int|bool */ $brackets = false; /* * Handles partitions. */ for (; $list->idx < $list->count; ++$list->idx) { /** * Token parsed at this moment. */ $token = $list->tokens[$list->idx]; // End of statement. if ($token->type === Token::TYPE_DELIMITER) { break; } // Skipping comments. if ($token->type === Token::TYPE_COMMENT) { continue; } if (($token->type === Token::TYPE_KEYWORD) && ($token->keyword === 'PARTITION BY')) { $field = 'partitionBy'; $brackets = false; } elseif (($token->type === Token::TYPE_KEYWORD) && ($token->keyword === 'SUBPARTITION BY')) { $field = 'subpartitionBy'; $brackets = false; } elseif (($token->type === Token::TYPE_KEYWORD) && ($token->keyword === 'PARTITIONS')) { $token = $list->getNextOfType(Token::TYPE_NUMBER); --$list->idx; // `getNextOfType` also advances one position. $this->partitionsNum = $token->value; } elseif (($token->type === Token::TYPE_KEYWORD) && ($token->keyword === 'SUBPARTITIONS')) { $token = $list->getNextOfType(Token::TYPE_NUMBER); --$list->idx; // `getNextOfType` also advances one position. $this->subpartitionsNum = $token->value; } elseif (! empty($field)) { /* * Handling the content of `PARTITION BY` and `SUBPARTITION BY`. */ // Counting brackets. if ($token->type === Token::TYPE_OPERATOR) { if ($token->value === '(') { // This is used instead of `++$brackets` because, // initially, `$brackets` is `false` cannot be // incremented. $brackets += 1; } elseif ($token->value === ')') { --$brackets; } } // Building the expression used for partitioning. $this->$field .= $token->type === Token::TYPE_WHITESPACE ? ' ' : $token->token; // Last bracket was read, the expression ended. // Comparing with `0` and not `false`, because `false` means // that no bracket was found and at least one must is // required. if ($brackets === 0) { $this->$field = trim($this->$field); $field = null; } } elseif (($token->type === Token::TYPE_OPERATOR) && ($token->value === '(')) { if (! empty($this->partitionBy)) { $this->partitions = ArrayObj::parse( $parser, $list, ['type' => 'PhpMyAdmin\\SqlParser\\Components\\PartitionDefinition'] ); } break; } } } } elseif ($this->options->has('PROCEDURE') || $this->options->has('FUNCTION')) { $this->parameters = ParameterDefinition::parse($parser, $list); if ($this->options->has('FUNCTION')) { $prevToken = $token; $token = $list->getNextOfType(Token::TYPE_KEYWORD); if ($token === null || $token->keyword !== 'RETURNS') { $parser->error('A "RETURNS" keyword was expected.', $token ?? $prevToken); } else { ++$list->idx; $this->return = DataType::parse($parser, $list); } } ++$list->idx; $this->entityOptions = OptionsArray::parse($parser, $list, static::$FUNC_OPTIONS); ++$list->idx; for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; if ($token->type === Token::TYPE_DELIMITER) { break; } $this->body[] = $token; } } elseif ($this->options->has('VIEW')) { /** @var Token $token */ $token = $list->getNext(); // Skipping whitespaces and comments. // Parsing columns list. if (($token->type === Token::TYPE_OPERATOR) && ($token->value === '(')) { --$list->idx; // getNext() also goes forward one field. $this->fields = ArrayObj::parse($parser, $list); ++$list->idx; // Skipping last token from the array. $list->getNext(); } // Parsing the SELECT expression if the view started with it. if ( $token->type === Token::TYPE_KEYWORD && $token->keyword === 'AS' && $list->tokens[$nextidx]->type === Token::TYPE_KEYWORD ) { if ($list->tokens[$nextidx]->value === 'SELECT') { $list->idx = $nextidx; $this->select = new SelectStatement($parser, $list); ++$list->idx; // Skipping last token from the select. } elseif ($list->tokens[$nextidx]->value === 'WITH') { ++$list->idx; $this->with = new WithStatement($parser, $list); } } // Parsing all other tokens for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; if ($token->type === Token::TYPE_DELIMITER) { break; } $this->body[] = $token; } } elseif ($this->options->has('TRIGGER')) { // Parsing the time and the event. $this->entityOptions = OptionsArray::parse($parser, $list, static::$TRIGGER_OPTIONS); ++$list->idx; $list->getNextOfTypeAndValue(Token::TYPE_KEYWORD, 'ON'); ++$list->idx; // Skipping `ON`. // Parsing the name of the table. $this->table = Expression::parse( $parser, $list, [ 'parseField' => 'table', 'breakOnAlias' => true, ] ); ++$list->idx; $list->getNextOfTypeAndValue(Token::TYPE_KEYWORD, 'FOR EACH ROW'); ++$list->idx; // Skipping `FOR EACH ROW`. for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; if ($token->type === Token::TYPE_DELIMITER) { break; } $this->body[] = $token; } } else { for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; if ($token->type === Token::TYPE_DELIMITER) { break; } $this->body[] = $token; } } } } Submit