Skip to content

Commit f258a98

Browse files
jdornjdorn
committed
Add highlighting support for binary/hex numbers and user-defined variables.
Add ":" to the boundary list (fixes ":=" assignment operator). Standardize whitespace around "if" clauses in the php code. Now there is always a space after "if" and before "(". Fixes #50
1 parent 0b30579 commit f258a98

File tree

4 files changed

+70
-20
lines changed

4 files changed

+70
-20
lines changed

‎lib/SqlFormatter.php‎

Lines changed: 67 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
* @copyright 2013 Jeremy Dorn
1010
* @license http://opensource.org/licenses/MIT
1111
* @link http://github.com/jdorn/sql-formatter
12-
* @version 1.2.12
12+
* @version 1.2.13
1313
*/
1414
class SqlFormatter
1515
{
@@ -26,6 +26,7 @@ class SqlFormatter
2626
const TOKEN_TYPE_BLOCK_COMMENT = 9;
2727
const TOKEN_TYPE_NUMBER = 10;
2828
const TOKEN_TYPE_ERROR = 11;
29+
const TOKEN_TYPE_VARIABLE = 12;
2930

3031
// Constants for different components of a token
3132
const TOKEN_TYPE = 0;
@@ -101,7 +102,7 @@ class SqlFormatter
101102
);
102103

103104
// Punctuation that can be used as a boundary between other tokens
104-
protected static $boundaries = array(',', ';', ')', '(', '.', '=', '<', '>', '+', '-', '*', '/', '!', '^', '%', '|', '&', '#');
105+
protected static $boundaries = array(',', ';',':', ')', '(', '.', '=', '<', '>', '+', '-', '*', '/', '!', '^', '%', '|', '&', '#');
105106

106107
// For HTML syntax highlighting
107108
// Styles applied to different token types
@@ -113,6 +114,7 @@ class SqlFormatter
113114
public static $word_attributes = 'style="color: #333;"';
114115
public static $error_attributes = 'style="background-color: red;"';
115116
public static $comment_attributes = 'style="color: #aaa;"';
117+
public static $variable_attributes = 'style="color: orange;"';
116118
public static $pre_attributes = 'style="color: black; background-color: white;"';
117119

118120
// Boolean - whether or not the current environment is the CLI
@@ -130,6 +132,7 @@ class SqlFormatter
130132
public static $cli_error = "\x1b[31;1;7m";
131133
public static $cli_comment = "\x1b[30;1m";
132134
public static $cli_functions = "\x1b[37m";
135+
public static $cli_variable = "\x1b[36;1m";
133136

134137
// The tab character to use when formatting SQL
135138
public static $tab = ' ';
@@ -173,7 +176,7 @@ public static function getCacheStats()
173176
*/
174177
protected static function init()
175178
{
176-
if(self::$init) return;
179+
if (self::$init) return;
177180

178181
// Sort reserved word list from longest word to shortest
179182
usort(self::$reserved, array('SqlFormatter', 'sortLength'));
@@ -233,22 +236,40 @@ protected static function getNextToken($string, $previous = null)
233236
if ($string[0]==='"' || $string[0]==='\'' || $string[0]==='`') {
234237
$return = array(
235238
self::TOKEN_TYPE => ($string[0]==='`'? self::TOKEN_TYPE_BACKTICK_QUOTE : self::TOKEN_TYPE_QUOTE),
236-
self::TOKEN_VALUE => $string
239+
self::TOKEN_VALUE => self::getQuotedString($string)
237240
);
238-
239-
// This checks for the following patterns:
240-
// 1. backtick quoted string using `` to escape
241-
// 2. double quoted string using "" or \" to escape
242-
// 3. single quoted string using '' or \' to escape
243-
if ( preg_match('/^(((`[^`]*($|`))+)|(("[^"\\\\]*(?:\\\\.[^"\\\\]*)*("|$))+)|((\'[^\'\\\\]*(?:\\\\.[^\'\\\\]*)*(\'|$))+))/s', $string, $matches)) {
244-
$return[self::TOKEN_VALUE] = $matches[1];
241+
242+
// If a quote was opened, but doesn't have a closing quote, return the remaining string
243+
if ($return[self::TOKEN_VALUE] === null) {
244+
$return[self::TOKEN_VALUE] = $string;
245245
}
246-
246+
247247
return $return;
248248
}
249+
250+
// User-defined Variable
251+
if ($string[0] === '@' && isset($string[1])) {
252+
// If the variable name is quoted
253+
if ($string[1]==='"' || $string[1]==='\'' || $string[1]==='`') {
254+
return array(
255+
self::TOKEN_VALUE => '@'.self::getQuotedString(substr($string,1)),
256+
self::TOKEN_TYPE => self::TOKEN_TYPE_VARIABLE
257+
);
258+
}
259+
// Non-quoted variable name
260+
else {
261+
preg_match('/^(@[a-zA-Z0-9\._\$]+)/',$string,$matches);
262+
if ($matches) {
263+
return array(
264+
self::TOKEN_VALUE => $matches[1],
265+
self::TOKEN_TYPE => self::TOKEN_TYPE_VARIABLE
266+
);
267+
}
268+
}
269+
}
249270

250-
// Number
251-
if (preg_match('/^([0-9]+(\.[0-9]+)?)($|\s|"\'`|'.self::$regex_boundaries.')/',$string,$matches)) {
271+
// Number (decimal, binary, or hex)
272+
if (preg_match('/^([0-9]+(\.[0-9]+)?|0x[0-9a-fA-F]+|0b[01]+)($|\s|"\'`|'.self::$regex_boundaries.')/',$string,$matches)) {
252273
return array(
253274
self::TOKEN_VALUE => $matches[1],
254275
self::TOKEN_TYPE=>self::TOKEN_TYPE_NUMBER
@@ -310,6 +331,17 @@ protected static function getNextToken($string, $previous = null)
310331
);
311332
}
312333

334+
protected static function getQuotedString($string) {
335+
// This checks for the following patterns:
336+
// 1. backtick quoted string using `` to escape
337+
// 2. double quoted string using "" or \" to escape
338+
// 3. single quoted string using '' or \' to escape
339+
if ( preg_match('/^(((`[^`]*($|`))+)|(("[^"\\\\]*(?:\\\\.[^"\\\\]*)*("|$))+)|((\'[^\'\\\\]*(?:\\\\.[^\'\\\\]*)*(\'|$))+))/s', $string, $matches)) {
340+
return $matches[1];
341+
}
342+
return null;
343+
}
344+
313345
/**
314346
* Takes a SQL string and breaks it into tokens.
315347
* Each token is an associative array with type and value.
@@ -500,7 +532,7 @@ public static function format($string, $highlight=true)
500532
$length = 0;
501533
for ($j=1;$j<=250;$j++) {
502534
// Reached end of string
503-
if(!isset($tokens[$i+$j])) break;
535+
if (!isset($tokens[$i+$j])) break;
504536

505537
$next = $tokens[$i+$j];
506538

@@ -823,6 +855,8 @@ protected static function highlightToken($token)
823855
return self::highlightReservedWord($token);
824856
} elseif ($type===self::TOKEN_TYPE_NUMBER) {
825857
return self::highlightNumber($token);
858+
} elseif ($type===self::TOKEN_TYPE_VARIABLE) {
859+
return self::highlightVariable($token);
826860
} elseif ($type===self::TOKEN_TYPE_COMMENT || $type===self::TOKEN_TYPE_BLOCK_COMMENT) {
827861
return self::highlightComment($token);
828862
}
@@ -887,7 +921,7 @@ protected static function highlightReservedWord($value)
887921
*/
888922
protected static function highlightBoundary($value)
889923
{
890-
if($value==='(' || $value===')') return $value;
924+
if ($value==='(' || $value===')') return $value;
891925

892926
if (self::is_cli()) {
893927
return self::$cli_boundary . $value . "\x1b[0m";
@@ -959,6 +993,22 @@ protected static function highlightWord($value)
959993
return '<span ' . self::$word_attributes . '>' . $value . '</span>';
960994
}
961995
}
996+
997+
/**
998+
* Highlights a variable token
999+
*
1000+
* @param String $value The token's value
1001+
*
1002+
* @return String HTML code of the highlighted token.
1003+
*/
1004+
protected static function highlightVariable($value)
1005+
{
1006+
if (self::is_cli()) {
1007+
return self::$cli_variable . $value . "\x1b[0m";
1008+
} else {
1009+
return '<span ' . self::$variable_attributes . '>' . $value . '</span>';
1010+
}
1011+
}
9621012

9631013
/**
9641014
* Helper function for sorting the list of reserved words by length
@@ -1008,7 +1058,7 @@ private static function output($string)
10081058

10091059
private static function is_cli()
10101060
{
1011-
if(isset(self::$cli)) return self::$cli;
1061+
if (isset(self::$cli)) return self::$cli;
10121062
else return php_sapi_name() === 'cli';
10131063
}
10141064

‎tests/clihighlight.html‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -207,7 +207,7 @@
207207
`value` text
208208
)
209209

210-
[37mSET[0m @defaultOOS[0m =[0m ([37mSELECT[0m value[0m [37mFROM[0m [35;1m`PREFIX_configuration`[0m [37mWHERE[0m name[0m =[0m [34;1m'PS_ORDER_OUT_OF_STOCK'[0m)
210+
[37mSET[0m [36;1m@defaultOOS[0m =[0m ([37mSELECT[0m value[0m [37mFROM[0m [35;1m`PREFIX_configuration`[0m [37mWHERE[0m name[0m =[0m [34;1m'PS_ORDER_OUT_OF_STOCK'[0m)
211211

212212
UPDATE `PREFIX_product` p SET `cache_default_attribute` = 0 WHERE `id_product` NOT IN (SELECT `id_product` FROM `PREFIX_product_attribute`)
213213

‎tests/format-highlight.html‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -636,7 +636,7 @@
636636
<pre style="color: black; background-color: white;"><span style="font-weight:bold;">CREATE</span> <span style="font-weight:bold;">TEMPORARY</span> <span style="font-weight:bold;">TABLE</span> <span style="color: purple;">`PREFIX_configuration_tmp`</span> (<span style="color: purple;">`value`</span> <span style="color: #333;">text</span>)</pre>
637637

638638
<pre style="color: black; background-color: white;"><span style="font-weight:bold;">SET</span>
639-
<span style="color: #333;">@defaultOOS</span> <span >=</span> (
639+
<span style="color: orange;">@defaultOOS</span> <span >=</span> (
640640
<span style="font-weight:bold;">SELECT</span>
641641
<span style="color: #333;">value</span>
642642
<span style="font-weight:bold;">FROM</span>

‎tests/highlight.html‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -207,7 +207,7 @@
207207
<span style="color: purple;">`value`</span> <span style="color: #333;">text</span>
208208
)</pre>
209209

210-
<pre style="color: black; background-color: white;"><span style="font-weight:bold;">SET</span> <span style="color: #333;">@defaultOOS</span> <span >=</span> (<span style="font-weight:bold;">SELECT</span> <span style="color: #333;">value</span> <span style="font-weight:bold;">FROM</span> <span style="color: purple;">`PREFIX_configuration`</span> <span style="font-weight:bold;">WHERE</span> <span style="color: #333;">name</span> <span >=</span> <span style="color: blue;">'PS_ORDER_OUT_OF_STOCK'</span>)</pre>
210+
<pre style="color: black; background-color: white;"><span style="font-weight:bold;">SET</span> <span style="color: orange;">@defaultOOS</span> <span >=</span> (<span style="font-weight:bold;">SELECT</span> <span style="color: #333;">value</span> <span style="font-weight:bold;">FROM</span> <span style="color: purple;">`PREFIX_configuration`</span> <span style="font-weight:bold;">WHERE</span> <span style="color: #333;">name</span> <span >=</span> <span style="color: blue;">'PS_ORDER_OUT_OF_STOCK'</span>)</pre>
211211

212212
<pre style="color: black; background-color: white;"><span style="font-weight:bold;">UPDATE</span> <span style="color: purple;">`PREFIX_product`</span> <span style="color: #333;">p</span> <span style="font-weight:bold;">SET</span> <span style="color: purple;">`cache_default_attribute`</span> <span >=</span> <span style="color: green;">0</span> <span style="font-weight:bold;">WHERE</span> <span style="color: purple;">`id_product`</span> <span style="font-weight:bold;">NOT</span> <span style="font-weight:bold;">IN</span> (<span style="font-weight:bold;">SELECT</span> <span style="color: purple;">`id_product`</span> <span style="font-weight:bold;">FROM</span> <span style="color: purple;">`PREFIX_product_attribute`</span>)</pre>
213213

0 commit comments

Comments
 (0)