Extra newlines between CREATE statements

[reedy]

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

[greg0ire]

So what would you like? To preserve new lines between queries? 42 newlines in, 42 newlines out? Or just 1?

[reedy]

A newline between ; and CREATE would be good.

In MediaWiki, the current "hacks" we have to get it somewhat like we want we do:

		$sql = str_replace( "\n/*_*/\n", " /*_*/", $sql );
		$sql = str_replace( "; CREATE ", ";\nCREATE ", $sql );
		$sql = str_replace(
			"\n" . '/*$wgDBTableOptions*/' . ";",
			' /*$wgDBTableOptions*/;' . "\n",
			$sql
		);
		$sql = str_replace(
			"\n" . '/*$wgDBTableOptions*/' . "\n;",
			' /*$wgDBTableOptions*/;' . "\n",
			$sql
		);

The first removes the extra newline after /*_*/.

The second adds the newline between ; and CREATE

The last two move /*$wgDBTableOptions*/; onto one line

So we end up with:

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*/;

The comments are our other hacks for inserting table prefixes and setting ENGINE etc, both of which are configurable (inside MediaWiki). So this is probably non standard behaviour, so don't really expect you to output those like we want.

But if you can do the minimum to put/preserve the newlines between statements etc for readability, that'd be appreciate