Now to the main show, this adds support for common table expressions and window functions.

I would have liked to add additional nesting on new lines for the FILTER() and OVER() clauses, eg:

GROUP_CONCAT(b, '.')
  FILTER (
    WHERE
      c != 'two'
  )
  OVER (
    ORDER BY
      a
  )
  AS filtered,

however without a larger rewrite of the formatter, all I could come up with was pretty hack-ish, and way too much tied to this specific syntax, so for now it would still be a bit squished.

Resolves #37

Hello.

Why not adding an option to all suitable methods which would be an integer defaulting to zero, able to receive an or'ed list of flags, each ones defining an option (chosen among a list provided as define('')'s constants) ? Many options could be defined there :

• Convert keywords to uppercases (or to lowercases)
• Convert function names to uppercases (or to lowercases)
• Add back-ticks wherever suitable
• Add 'AS' keyword before aliases if not already there
• Add line numbering to syntax highlighting
• Etc, etc

This would be largely open to future as an integer could potentially contain up to 32 options.

I found some formatting problems in the current version 1.1.0.

1. MODIFY

Input

ALTER TABLE `table` MODIFY `id` INT(11) UNSIGNED NOT NULL

Result

ALTER TABLE
  `table` MODIFY `id` INT(11) UNSIGNED NOT NULL

Expected result

ALTER TABLE
  `table`
MODIFY
  `id` INT(11) UNSIGNED NOT NULL

2. CHANGE

Input

ALTER TABLE `table` CHANGE `id` `_id` BIGINT(20) UNSIGNED NULL

Result

ALTER TABLE
  `table` CHANGE `id` `_id` BIGINT(20) UNSIGNED NULL

Expected result

ALTER TABLE
  `table`
CHANGE
  `id` `_id` BIGINT(20) UNSIGNED NULL

3. AFTER

Input

ALTER TABLE `table` MODIFY `id` INT(11) UNSIGNED NOT NULL FIRST, CHANGE `name` `firstname` VARCHAR(20) COLLATE 'utf8mb4_unicode_ci' NOT NULL AFTER `id`, CHANGE `name2` `lastname` VARCHAR(30) COLLATE 'utf8mb4_unicode_ci' NOT NULL AFTER `firstname`, ADD `city` VARCHAR(20) COLLATE 'utf8mb4_unicode_ci' AFTER `lastname`

Result

ALTER TABLE
  `table` MODIFY `id` INT(11) UNSIGNED NOT NULL FIRST,
  CHANGE `name` `firstname` VARCHAR(20) COLLATE 'utf8mb4_unicode_ci' NOT NULL
AFTER
  `id`,
  CHANGE `name2` `lastname` VARCHAR(30) COLLATE 'utf8mb4_unicode_ci' NOT NULL
AFTER
  `firstname`,
ADD
  `city` VARCHAR(20) COLLATE 'utf8mb4_unicode_ci'
AFTER
  `lastname`

Expected result

ALTER TABLE
  `table`
MODIFY
  `id` INT(11) UNSIGNED NOT NULL FIRST,
CHANGE
  `name` `firstname` VARCHAR(20) COLLATE 'utf8mb4_unicode_ci' NOT NULL AFTER `id`,
CHANGE
  `name2` `lastname` VARCHAR(30) COLLATE 'utf8mb4_unicode_ci' NOT NULL AFTER `firstname`,
ADD
  `city` VARCHAR(20) COLLATE 'utf8mb4_unicode_ci' AFTER `lastname`

https://packagist.org/packages/doctrine/sql-formatter shows 1.1.0 and 1.1.1 but I don't see any sign of them on https://github.com/doctrine/sql-formatter/tags or https://github.com/doctrine/sql-formatter/releases

Similarly https://github.com/doctrine/sql-formatter/tree/1.1.0 and https://github.com/doctrine/sql-formatter/tree/1.1.1 give a 404

My goal with the second commit was to use generators for getFilteredTokens, and then I realized it would not be possible: there are lookaheads up to 250 tokens ahead to decide whether to stay inline or to add linebreaks. It made me move a lot of logic to the value objects though, and get rid of the $indexedTokens ugly array.

#45 added a missing test for a fix, but only added a comparison for compression, the missing comparisons in the other files will cause newly added comparisons to fail, as they will be compared to the wrong SQL.

The program was written when they did not exist, and it acts on text in a very streamy way (or at least, should). Let's see if we can leverage generators to improve it, be it memory-wise, or just readability-wise: maybe we could get rid of most of the $return .= 'some string' concatenations.

This new configuration has been introduced with Composer 2.2: https://github.com/composer/composer/releases/tag/2.2.0-RC1

See also the documentation: https://getcomposer.org/doc/06-config.md#allow-plugins

Hi, it's often more readable when selected column aliases are aligned vertically. Instead of this:

SELECT   foo as one   foofoo as two   foofoofoo as three

we can have this:

SELECT   foo           as one   foofoo      as two   foofoofoo as three

Configuration option for that would be nice.

This adds a new optional third parameter to the format method, $forceUppercase which defaults to false to maintain backwards compatibility.

When passed as true, all keywords will be uppercased.

There is a new test written in for this functionality that passes.

Fixes: https://github.com/doctrine/sql-formatter/issues/32. Implementation abstracted from the original PR against the forked source: https://github.com/jdorn/sql-formatter/pull/86

I noticed the PostgreSQL cast shortcut is incorrectly split, for example 123::bigint becomes 123: :bigint.

I can look at making a PR to support this, but first I wanted to check if there was interest in this tool supporting platform specific syntax... ?

In this example, why doesn't ct_tag_id end up on a new row?

Given

CREATE TABLE /*_*/change_tag (ct_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ct_rc_id INTEGER UNSIGNED DEFAULT NULL, ct_log_id INTEGER UNSIGNED DEFAULT NULL, ct_rev_id INTEGER U>

CREATE UNIQUE INDEX change_tag_rc_tag_id ON /*_*/change_tag (ct_rc_id, ct_tag_id);

CREATE UNIQUE INDEX change_tag_log_tag_id ON /*_*/change_tag (ct_log_id, ct_tag_id);

CREATE UNIQUE INDEX change_tag_rev_tag_id ON /*_*/change_tag (ct_rev_id, ct_tag_id);

After running ( new SqlFormatter( new NullHighlighter() ) )->format( $sql ) we end up with

CREATE TABLE
/*_*/
change_tag (
  ct_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  ct_rc_id INTEGER UNSIGNED DEFAULT NULL,
  ct_log_id INTEGER UNSIGNED DEFAULT NULL,
  ct_rev_id INTEGER UNSIGNED DEFAULT NULL,
  ct_params BLOB DEFAULT NULL, ct_tag_id INTEGER UNSIGNED NOT NULL
); CREATE UNIQUE INDEX change_tag_rc_tag_id ON
/*_*/
change_tag (ct_rc_id, ct_tag_id); CREATE UNIQUE INDEX change_tag_log_tag_id ON
/*_*/
change_tag (ct_log_id, ct_tag_id); CREATE UNIQUE INDEX change_tag_rev_tag_id ON
/*_*/
change_tag (ct_rev_id, ct_tag_id); CREATE INDEX change_tag_tag_id_id ON
/*_*/
change_tag (
  ct_tag_id, ct_rc_id, ct_rev_id, ct_log_id
);

We should end up with (ignoring #53)

CREATE TABLE
/*_*/
change_tag (
  ct_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  ct_rc_id INTEGER UNSIGNED DEFAULT NULL,
  ct_log_id INTEGER UNSIGNED DEFAULT NULL,
  ct_rev_id INTEGER UNSIGNED DEFAULT NULL,
  ct_params BLOB DEFAULT NULL,
  ct_tag_id INTEGER UNSIGNED NOT NULL
); CREATE UNIQUE INDEX change_tag_rc_tag_id ON
/*_*/
change_tag (ct_rc_id, ct_tag_id); CREATE UNIQUE INDEX change_tag_log_tag_id ON
/*_*/
change_tag (ct_log_id, ct_tag_id); CREATE UNIQUE INDEX change_tag_rev_tag_id ON
/*_*/
change_tag (ct_rev_id, ct_tag_id); CREATE INDEX change_tag_tag_id_id ON
/*_*/
change_tag (
  ct_tag_id, ct_rc_id, ct_rev_id, ct_log_id
);

Given this input (as output from Doctrine, with trailing ; and newlines added)

CREATE TABLE /*_*/actor (actor_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, actor_user INT UNSIGNED DEFAULT NULL, actor_name VARBINARY(255) NOT NULL, UNIQUE INDEX actor_user (actor_user), UNIQUE INDEX actor_name (actor_name), PRIMARY KEY(actor_id)) /*$wgDBTableOptions*/;

CREATE TABLE /*_*/user_former_groups (ufg_user INT UNSIGNED DEFAULT 0 NOT NULL, ufg_group VARBINARY(255) DEFAULT '' NOT NULL, PRIMARY KEY(ufg_user, ufg_group)) /*$wgDBTableOptions*/;

Calling ( new SqlFormatter( new NullHighlighter() ) )->format( $schema ); gives

CREATE TABLE
/*_*/
actor (
  actor_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  actor_user INT UNSIGNED DEFAULT NULL,
  actor_name VARBINARY(255) NOT NULL,
  UNIQUE INDEX actor_user (actor_user),
  UNIQUE INDEX actor_name (actor_name),
  PRIMARY KEY(actor_id)
)
/*$wgDBTableOptions*/
; CREATE TABLE
/*_*/
user_former_groups (
  ufg_user INT UNSIGNED DEFAULT 0 NOT NULL,
  ufg_group VARBINARY(255) DEFAULT '' NOT NULL,
  PRIMARY KEY(ufg_user, ufg_group)
)
/*$wgDBTableOptions*/
;

Is it possible to add some more newlines? https://github.com/doctrine/sql-formatter/blob/1.0.x/src/SqlFormatter.php#L49 obviously doesn't take any options

Similar for some postgres:

CREATE TABLE /*_*/actor (actor_id BIGSERIAL NOT NULL, actor_user INT DEFAULT NULL, actor_name BYTEA NOT NULL, PRIMARY KEY(actor_id));

CREATE UNIQUE INDEX actor_user ON /*_*/actor (actor_user);

CREATE UNIQUE INDEX actor_name ON /*_*/actor (actor_name);

CREATE TABLE /*_*/user_former_groups (ufg_user INT DEFAULT 0 NOT NULL, ufg_group BYTEA DEFAULT '' NOT NULL, PRIMARY KEY(ufg_user, ufg_group));

to

CREATE TABLE
/*_*/
actor (
  actor_id BIGSERIAL NOT NULL,
  actor_user INT DEFAULT NULL,
  actor_name BYTEA NOT NULL,
  PRIMARY KEY(actor_id)
); CREATE UNIQUE INDEX actor_user ON
/*_*/
actor (actor_user); CREATE UNIQUE INDEX actor_name ON
/*_*/
actor (actor_name); CREATE TABLE
/*_*/
user_former_groups (
  ufg_user INT DEFAULT 0 NOT NULL,
  ufg_group BYTEA DEFAULT '' NOT NULL,
  PRIMARY KEY(ufg_user, ufg_group)
);