From: Christian Weiske <cweiske@cweiske.de>
Date: Wed, 16 Dec 2015 06:51:40 +0000 (+0100)
Subject: SQL formatting support
X-Git-Tag: v0.1.0~4
X-Git-Url: https://git.cweiske.de/php-sqllint.git/commitdiff_plain/8abc23ae704ce1bf3e929dc9205aa6af42b8dea5

SQL formatting support
---

diff --git a/README.rst b/README.rst
index bca5a30..ef9e165 100644
--- a/README.rst
+++ b/README.rst
@@ -6,12 +6,15 @@ Command line tool to validate (syntax check) SQL files.
 Primarily for MySQL ``.sql`` files.
 
 Can be used in git pre-commit hooks to catch errors.
+Use it from your shell, offline and without any SQL server.
+
+You can also use it to format SQL queries.
 
 
 =====
 Usage
 =====
-::
+Syntax check::
 
     $ php-sqllint tests/files/create-missingcomma.sql 
     Checking SQL syntax of tests/files/create-missingcomma.sql
@@ -27,6 +30,23 @@ Emacs mode::
     tests/files/create-noname.sql:1.13:Error: At least one column definition was expected.
 
 
+Formatting::
+
+    $ php-sqllint --format tests/files/select-unformatted.sql
+    SELECT
+      id,
+      NAME,
+      url
+    FROM
+      users
+    WHERE
+      DATE > NOW() AND id != 0
+    ORDER BY NAME
+    LIMIT 10
+
+You can enable ANSI coloring by passing the ``--colors`` option.
+
+
 ====
 Bugs
 ====
diff --git a/src/phpsqllint/Cli.php b/src/phpsqllint/Cli.php
index 5e4f638..129a925 100644
--- a/src/phpsqllint/Cli.php
+++ b/src/phpsqllint/Cli.php
@@ -28,6 +28,9 @@ class Cli
 {
     protected $renderer;
 
+    protected $format = false;
+    protected $colors = false;
+
     /**
      * Start processing.
      *
@@ -41,7 +44,11 @@ class Cli
 
             $allfine = true;
             foreach ($files as $filename) {
-                $allfine &= $this->checkFile($filename);
+                if ($this->format) {
+                    $allfine &= $this->formatFile($filename);
+                } else {
+                    $allfine &= $this->checkFile($filename);
+                }
             }
 
             if ($allfine == true) {
@@ -65,14 +72,8 @@ class Cli
     public function checkFile($filename)
     {
         $this->renderer->startRendering($filename);
-
-        if ($filename == '-') {
-            $sql = file_get_contents('php://stdin');
-        } else {
-            $sql = file_get_contents($filename);
-        }
-        if (trim($sql) == '') {
-            $this->renderer->displayError('SQL file empty', '', 0, 0);
+        $sql = $this->loadSql($filename);
+        if ($sql === false) {
             return false;
         }
 
@@ -110,6 +111,38 @@ class Cli
         return false;
     }
 
+    /**
+     * Reformat the given file
+     */
+    protected function formatFile($filename)
+    {
+        $this->renderer->startRendering($filename);
+        $sql = $this->loadSql($filename);
+        if ($sql === false) {
+            return false;
+        }
+
+        $options = array(
+            //FIXME: automatically detect if the shell/tool supports colors
+            'type' => $this->colors ? 'cli' : 'text'
+        );
+        echo \SqlParser\Utils\Formatter::format($sql, $options) . "\n";
+    }
+
+    protected function loadSql($filename)
+    {
+        if ($filename == '-') {
+            $sql = file_get_contents('php://stdin');
+        } else {
+            $sql = file_get_contents($filename);
+        }
+        if (trim($sql) == '') {
+            $this->renderer->displayError('SQL file empty', '', 0, 0);
+            return false;
+        }
+        return $sql;
+    }
+
     /**
      * Load parameters for the CLI option parser.
      *
@@ -122,6 +155,26 @@ class Cli
         $parser->version = '0.0.2';
         $parser->avoid_reading_stdin = true;
 
+        $parser->addOption(
+            'format',
+            array(
+                'short_name'  => '-f',
+                'long_name'   => '--format',
+                'description' => 'Reformat SQL instead of checking',
+                'action'      => 'StoreTrue',
+                'default'     => false,
+            )
+        );
+        $parser->addOption(
+            'colors',
+            array(
+                'short_name'  => '-c',
+                'long_name'   => '--colors',
+                'description' => 'Use colors in formatting output',
+                'action'      => 'StoreTrue',
+                'default'     => false,
+            )
+        );
         $parser->addOption(
             'renderer',
             array(
@@ -165,6 +218,9 @@ class Cli
                 . ucfirst($result->options['renderer']);
             $this->renderer = new $rendClass();
 
+            $this->format = $result->options['format'];
+            $this->colors = $result->options['colors'];
+
             foreach ($result->args['sql_files'] as $filename) {
                 if ($filename == '-') {
                     continue;
diff --git a/tests/files/select-unformatted.sql b/tests/files/select-unformatted.sql
new file mode 100644
index 0000000..c8fafca
--- /dev/null
+++ b/tests/files/select-unformatted.sql
@@ -0,0 +1 @@
+SELECT id, name, url FROM users WHERE date > NOW() and id != 0 ORDER BY name LIMIT 10