• R/O
  • SSH
  • HTTPS

format-sql: 提交


Commit MetaInfo

修订版59 (tree)
时间2018-02-17 13:51:15
作者h-sasaki

Log Message

3.04 インターンシップ

更改概述

差异

--- trunk/DMLBreaker3.x/DMLBreaker.js (revision 58)
+++ trunk/DMLBreaker3.x/DMLBreaker.js (revision 59)
@@ -1,5 +1,5 @@
11 /*
2- * Copyright 2009-2017 Cosmos Inc. All right reserved.
2+ * Copyright 2009-2018 Cosmos Inc. All right reserved.
33 * http://www.cos-mos.co.jp/
44 */
55
@@ -24,7 +24,7 @@
2424
2525 // 単独でも稼働させるために設定(内容はconfig.jsを参照)
2626 if (typeof configIndent === "undefined") {
27- var configIndent = 1;
27+ var configIndent = 2;
2828 }
2929 if (typeof configAndor === "undefined") {
3030 var configAndor = 3;
@@ -69,7 +69,7 @@
6969 if (arguments.length > 2) {
7070
7171 // 引数で渡された設定を採用(内容はconfig.jsを参照)
72- if (typeof arguments[2] !== "undefined" && (arguments[2] == 1 || arguments[2] == 2)) {
72+ if (typeof arguments[2] !== "undefined" && (arguments[2] == 1 || arguments[2] == 2 || arguments[2] == 3)) {
7373 configIndent = arguments[2];
7474 }
7575 if (typeof arguments[3] !== "undefined" && (arguments[3] == 1 || arguments[3] == 2 || arguments[3] == 3)) {
@@ -239,8 +239,11 @@
239239 result = result.replace(/__SPACE__/g, " ");
240240 result = result.replace(/ +\n/g, "\n");
241241
242+ // インデントがスペース2の場合
243+ if (configIndent == 1) {
244+ result = result.replace(/\t/g, " ");
242245 // インデントがスペース4の場合
243- if (configIndent == 1) {
246+ } else if (configIndent == 2) {
244247 result = result.replace(/\t/g, " ");
245248 }
246249
@@ -683,9 +686,7 @@
683686 startNo = endNo + 1;
684687 parenthesis = 0;
685688 wkAdd = "";
686-
687689 i = endNo;
688- parenthesis = 0;
689690 tab = tab.replace("\t", "");
690691
691692 } else if (wkAdd.match(/ between /i) != null) {
@@ -774,9 +775,7 @@
774775 startNo = endNo + 1;
775776 parenthesis = 0;
776777 wkAdd = "";
777-
778778 i = endNo;
779- parenthesis = 0;
780779 tab = tab.replace("\t", "");
781780
782781 } else if (wkAdd.match(/ between /i) != null) {
@@ -813,11 +812,11 @@
813812
814813 } else {
815814
816- // AND/OR/ON位置が前2の場合
817- if (configAndor == 2) {
815+ // AND/OR/ON位置が前1の場合
816+ if (configAndor == 1) {
817+ formatSql += "\n" + tab;
818+ } else {
818819 formatSql += "\n\t" + tab;
819- } else {
820- formatSql += "\n" + tab;
821820 }
822821 }
823822
@@ -902,7 +901,7 @@
902901 */
903902 function formatCase(formatSql) {
904903
905- if (wkAdd.match(/ case |^case /i) != null) {
904+ if (wkAdd.match(/ case |^case |\tcase | case$|^case$|\(case |\(case$/i) != null) {
906905
907906 formatSql += wkAdd;
908907 wkAdd = "";
@@ -910,12 +909,13 @@
910909
911910 } else if (wkAdd.match(/ when | else /i) != null) {
912911
913- formatSql += wkAdd.substring(0, wkAdd.length - 6) + "\n\t\t" + tab + wkAdd.slice(-6);
912+ formatSql += wkAdd.substring(0, wkAdd.length - 6) + "\n\t" + tab + wkAdd.slice(-6);
914913 wkAdd = "";
915914
916- } else if (wkAdd.match(/ end /i) != null) {
915+ } else if (wkAdd.match(/ end |^end |\tend | end$/i) != null) {
917916
918- formatSql += wkAdd.substring(0, wkAdd.length - 5) + "\n\t\t" + tab + wkAdd.slice(-5) + "\n\t" + tab;
917+ var len = wkAdd.match(/ end |\tend /i) != null ? -5 : -4;
918+ formatSql += wkAdd.substring(0, wkAdd.length + len) + "\n\t" + tab + wkAdd.slice(len);
919919 wkAdd = "";
920920 tab = tab.replace("\t", "");
921921 }
@@ -952,8 +952,8 @@
952952 */
953953 function convertKeyword(type, targetStr) {
954954
955- // DML予約語(38種類)
956- var DML_KEYWORD = ["ALL", "AND", "ANY", "AS", "ASC", "BETWEEN", "BY", "CASE", "DELETE", "DESC", "DISTINCT", "ELSE", "END", "EXISTS", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "INSERT", "INTO", "IS", "LIKE", "NOT", "NULL", "ON", "OR", "ORDER", "SELECT", "SET", "THEN", "UNION", "UPDATE", "VALUES", "WHERE", "WITH"];
955+ // DML予約語(38種類+WHEN)
956+ var DML_KEYWORD = ["ALL", "AND", "ANY", "AS", "ASC", "BETWEEN", "BY", "CASE", "DELETE", "DESC", "DISTINCT", "ELSE", "END", "EXISTS", "FROM", "GROUP", "HAVING", "IN", "INDEX", "INNER", "INSERT", "INTO", "IS", "LIKE", "NOT", "NULL", "ON", "OR", "ORDER", "SELECT", "SET", "THEN", "UNION", "UPDATE", "VALUES", "WHERE", "WITH", "WHEN"];
957957 // Oracleキーワード(217種類)
958958 var ORACLE_KEYWORD = ["ABS", "ACOS", "ADD_MONTHS", "APPENDCHILDXML", "ATTIMEZONE", "ASCII", "ASCIISTR", "ASIN", "ATAN", "ATAN2", "AVG", "BFILENAME", "BIN_TO_NUM", "BITAND", "CARDINALITY", "CAST", "CEIL", "CHARTOROWID", "CHR", "COALESCE", "COLLECT", "COMPOSE", "CON_DBID_TO_ID", "CON_GUID_TO_ID", "CON_NAME_TO_ID", "CON_UID_TO_ID", "CONCAT", "CONVERT", "CORR", "CORR_K", "CORR_S", "COS", "COSH", "COUNT", "COVAR_POP", "COVAR_SAMP", "CUME_DIST", "CURRENT_DATE", "CURRENT_TIMESTAMP", "CV", "DATAOBJ_TO_PARTITION", "DBTIMEZONE", "DECODE", "DECOMPOSE", "DELETEXML", "DENSE_RANK", "DEPTH", "DEREF", "DUMP", "EMPTY_BLOB", "EMPTY_CLOB", "EXISTSNODE", "EXP", "EXTRACT", "EXTRACTVALUE", "FEATURE_DETAILS", "FEATURE_ID", "FEATURE_SET", "FEATURE_VALUE", "FIRST", "FIRST_VALUE", "FLOOR", "FROM_TZ", "GREATEST", "GROUP_ID", "GROUPING", "GROUPING_ID", "HEXTORAW", "INITCAP", "INSTR", "INSTRB", "IS_DATE", "ITERATION_NUMBER", "LAG", "LAST", "LAST_DAY", "LAST_VALUE", "LEAD", "LEAST", "LENGTH", "LENGTHB", "LISTAGG", "LN", "LNNVL", "LOCALTIMESTAMP", "LOG", "LOWER", "LPAD", "LTRIM", "MAKE_REF", "MAX", "MEDIAN", "MIN", "MOD", "MONTHS_BETWEEN", "NANVL", "NCHR", "NEW_TIME", "NEXT_DAY", "NLS_CHARSET_DECL_LEN", "NLS_CHARSET_ID", "NLS_CHARSET_NAME", "NLS_INITCAP", "NLS_LOWER", "NLS_UPPER", "NLSSORT", "NTH_VALUE", "NTILE", "NULLIF", "NUMTODSINTERVAL", "NUMTOYMINTERVAL", "NVL", "NVL2", "ORA_DST_AFFECTED", "ORA_DST_CONVERT", "ORA_DST_ERROR", "ORA_HASH", "ORA_INVOKING_USER", "ORA_INVOKING_USERID", "PATH", "PERCENT_RANK", "PERCENTILE_CONT", "PERCENTILE_DISC", "POWER", "POWERMULTISET", "POWERMULTISET_BY_CARDINALITY", "PRESENTNNV", "PRESENTV", "PREVIOUS", "RAWTOHEX", "RAWTONHEX", "REF", "REGEXP_COUNT", "REGEXP_INSTR", "REGEXP_LIKE", "REGEXP_REPLACE", "REGEXP_SUBSTR", "REMAINDER", "REPLACE", "ROUND", "ROW_NUMBER", "ROWIDTOCHAR", "ROWIDTONCHAR", "RPAD", "RTRIM", "SCN_TO_TIMESTAMP", "SESSIONTIMEZONE", "SET", "SIGN", "SIN", "SINH", "SOUNDEX", "SQLCODE", "SQLERRM", "SQRT", "STANDARD_HASH", "STRTOKEN", "STDDEV", "STDDEV_POP", "STDDEV_SAMP", "SUBSTR", "SUBSTRB", "SUM", "SYS_CONNECT_BY_PATH", "SYS_CONTEXT", "SYS_DBURIGEN", "SYS_EXTRACT_UTC", "SYS_GUID", "SYS_TYPEID", "SYS_XMLAGG", "SYS_XMLGEN", "SYSDATE", "SYSTIMESTAMP", "TAN", "TANH", "TIMESTAMP_TO_SCN", "TO_BINARY_DOUBLE", "TO_BINARY_FLOAT", "TO_BLOB", "TO_CHAR", "TO_CHAR", "TO_CLOB", "TO_DATE", "TO_DSINTERVAL", "TO_LOB", "TO_MULTI_BYTE", "TO_NCHAR", "TO_NCLOB", "TO_NUMBER", "TO_RAW", "TO_SINGLE_BYTE", "TO_TIMESTAMP", "TO_TIMESTAMP_TZ", "TO_YMINTERVAL", "TRANSLATE", "TREAT", "TRIM", "TRUNC", "TZ_OFFSET", "UID", "UNISTR", "UPDATEXML", "UPPER", "USER", "USERENV", "VALUE", "VAR_POP", "VAR_SAMP", "VARIANCE", "VSIZE", "WIDTH_BUCKET", "PREV_DAY", "IS_DATE", "YEARS_OF_AGE", "ROUNDUP", "STRTOKEN", "CD_MOD10_WEIGHT"];
959959 // JOINキーワード(10種類、予約語ではない)
--- trunk/DMLBreaker3.x/SampleSQL.txt (revision 58)
+++ trunk/DMLBreaker3.x/SampleSQL.txt (revision 59)
@@ -5,8 +5,8 @@
55 delete from db.member where no like '10%' and age>30;
66
77 -- SELECT
8-select count(*),no,f_name,age from db.member where age>30 group by no,name,age;
9-SELECT 番号, 商品コード, 単価, 数量, 金額 FROM DB.売上 WHRERE 単価 < 1000;
8+select count(*),no,f_name,age from db.member where age>30 and no between '11' and '222' group by no,name,age;
9+SELECT 番号, 商品コード, 単価, 数量, 金額 FROM DB.売上 WHERE 単価 < 1000;
1010 select * from LOG_BACKUP;
1111 select color_code,CASE color_code WHEN 'FFF' THEN 'WHITE' WHEN '000' THEN 'BACK' ELSE 'AAAA' END color_name from color_sample;
1212
--- trunk/DMLBreaker3.x/base.js (revision 58)
+++ trunk/DMLBreaker3.x/base.js (revision 59)
@@ -1,5 +1,5 @@
11 /*
2- * Copyright 2009-2017 Cosmos Inc. All right reserved.
2+ * Copyright 2009-2018 Cosmos Inc. All right reserved.
33 * http://www.cos-mos.co.jp/
44 */
55
@@ -6,9 +6,9 @@
66 // 名称
77 var NAME = "DML Breaker";
88 // バージョン
9-var VERSION = "3.03";
9+var VERSION = "3.04";
1010 // コピーライト
11-var COPYRIGHT = "Copyright 2009-2017 <a href=\"http://www.cos-mos.co.jp/\" target=\"_blank\">Cosmos Inc.</a> All right reserved.";
11+var COPYRIGHT = "Copyright 2009-2018 <a href=\"http://www.cos-mos.co.jp/\" target=\"_blank\">Cosmos Inc.</a> All right reserved.";
1212
1313 // ブランク画像(サーバーを指定しない)
1414 Ext.BLANK_IMAGE_URL = "ext/resources/images/default/s.gif";
@@ -56,7 +56,7 @@
5656 },
5757 config: {
5858 message: "初期値はconfig.jsで設定できます。",
59- indent: {name: "インデント", v1: "スペース4", v2: "タブ"},
59+ indent: {name: "インデント", v1: "スペース2", v2: "スペース4", v3: "タブ"},
6060 andor: {name: "AND/OR/ON位置", v1: "前1", v2: "前2", v3: "後"},
6161 comma: {name: "カンマ位置", v1: "前", v2: "後"},
6262 caseFormat: {name: "CASE整形", v1: "有効", v2: "無効"},
@@ -89,7 +89,7 @@
8989 },
9090 config: {
9191 message: "The initial value you can be set in config.js.",
92- indent: {name: "Indent", v1: "Space4", v2: "Tab"},
92+ indent: {name: "Indent", v1: "Space2", v2: "Space4", v3: "Tab"},
9393 andor: {name: "AND/OR/ON", v1: "Front1", v2: "Front2", v3: "Back"},
9494 comma: {name: "Comma", v1: "Front", v2: "Back"},
9595 caseFormat: {name: "CASE", v1: "Enable", v2: "Disable"},
@@ -216,6 +216,7 @@
216216 onClickFileExport("all", "");
217217 }
218218 },
219+ " ",
219220 /*
220221 {
221222 id: "tbarInputText", text: "InputText", handler: function() {
@@ -222,6 +223,7 @@
222223 onClickFileExport(Ext.getCmp("inTextarea").getActiveTab().id, "sql");
223224 }
224225 },
226+ " ",
225227 */
226228 {
227229 id: "tbarPlainText", text: "PlainText", handler: function() {
@@ -228,16 +230,19 @@
228230 onClickFileExport("outPlainText", "sql");
229231 }
230232 },
233+ " ",
231234 {
232235 id: "tbarRichText", text: "RichText", handler: function() {
233236 onClickFileExport("outRichText", "html");
234237 }
235238 },
239+ " ",
236240 {
237241 id: "tbarLineText", text: "LineText", handler: function() {
238242 onClickFileExport("outLineText", "sql");
239243 }
240244 },
245+ " ",
241246 {
242247 id: "tbarHistory", text: "History", handler: function() {
243248 onClickFileExport("outHistory", "txt");
@@ -261,7 +266,7 @@
261266 },
262267 "-",
263268 {
264- text: "【IE】" + constant.ui.formatShortcut, handler: function() {
269+ id: "ieShortcut", text: constant.ui.formatShortcut, handler: function() {
265270
266271 if (window.clipboardData){
267272
@@ -281,6 +286,12 @@
281286 region: "north"
282287 });
283288
289+ // IE用ショートカットを表示しない
290+ if(!window.clipboardData) {
291+
292+ inputPanel["topToolbar"]["items"].removeKey("ieShortcut");
293+ }
294+
284295 // Rich Textタブを表示しない
285296 if (configRichTab == 2) {
286297
@@ -543,25 +554,25 @@
543554 data = dom.innerHTML;
544555 }
545556
546- data = "<html>" +
547- "<head>" +
548- "<meta content=\"text/html; charset=utf-8\" http-equiv=\"Content-Type\">" +
549- "<title>" + document.title + "</title>" +
550- "<style type=\"text/css\">" +
551- "<!--" +
552- "hr {border: 0;border-bottom: solid 1px #999;margin: 0;padding: 0;}" +
553- "ol {margin: 0px;padding: 5px 0px 5px 50px;background: #FFF;list-style-type: decimal-leading-zero;}" +
554- "ol li {padding: 1px 1px 1px 5px;border-left: 3px solid #9BFCA0;}" +
555- "ol li.odd {background-color: #FFF;}" +
556- "ol li.even {background-color: #EEE;}" +
557- "ol li span.highlight {color: #003D84;font-weight: bold;}" +
558- "ol li span.string {color: #ED007F;font-weight: bold;}" +
559- "ol li span.oracle {color: #00582A;font-weight: bold;}" +
560- "-->" +
561- "</style>" +
562- "</head>" +
563- "<body>" + data + "</body>" +
564- "</html>";
557+ data = "<html>\r\n" +
558+ "<head>\r\n" +
559+ "<meta content=\"text/html; charset=utf-8\" http-equiv=\"Content-Type\">\r\n" +
560+ "<title>" + document.title + "</title>\r\n" +
561+ "<style type=\"text/css\">\r\n" +
562+ "<!--\r\n" +
563+ "hr {border: 0;border-bottom: solid 1px #999;margin: 0;padding: 0;}\r\n" +
564+ "ol {margin: 0px;padding: 5px 0px 5px 50px;background: #FFF;list-style-type: decimal-leading-zero;}\r\n" +
565+ "ol li {padding: 1px 1px 1px 5px;border-left: 3px solid #9BFCA0;}\r\n" +
566+ "ol li.odd {background-color: #FFF;}\r\n" +
567+ "ol li.even {background-color: #EEE;}\r\n" +
568+ "ol li span.highlight {color: #003D84;font-weight: bold;}\r\n" +
569+ "ol li span.string {color: #ED007F;font-weight: bold;}\r\n" +
570+ "ol li span.oracle {color: #00582A;font-weight: bold;}\r\n" +
571+ "-->\r\n" +
572+ "</style>\r\n" +
573+ "</head>\r\n" +
574+ "<body>" + data + "</body>\r\n" +
575+ "</html>\r\n";
565576
566577 } else {
567578
@@ -693,9 +704,7 @@
693704 Ext.getCmp("btnConfig").toggle(!this.collapsed);
694705 }
695706
696- /**
697- * Rich Text用のHTML取得
698- */
707+ // Rich Text用のHTML取得
699708 function getRichText(targetStr) {
700709
701710 if (targetStr == null) {
@@ -707,8 +716,11 @@
707716 var key = "";
708717 var regExp = "";
709718
719+ // タブをスペース4に変換
720+ targetStr = targetStr.replace(/\t/g, " ");
721+
710722 // 固定文言を太字の色付けに変換
711- targetStr = targetStr.replace(/'[^']*'|"[^"]*"/g, "<span class=\"string\">$&</span>");
723+ targetStr = targetStr.replace(/'[^']*'|"[^"]*"/g, "<span class=\"string\">$&</span>");//'
712724
713725 // 復帰文字を除去して、SQLで分割(簡易版)
714726 var sqlArray = targetStr.replace(/\r/g, "").replace(/;/g, ";__SEMICOLON__").split("__SEMICOLON__");
@@ -726,10 +738,10 @@
726738
727739 for (var j = 0; j < line.length; j++) {
728740
729- text += "<li class=\"" + (j % 2 ? "even" : "odd") + "\">" + line[j].replace(/\s\s/g, "&nbsp;&nbsp;") + "</li>";
741+ text += "<li class=\"" + (j % 2 ? "even" : "odd") + "\">" + line[j].replace(/\s\s/g, "&nbsp;&nbsp;") + "</li>\r\n";
730742 }
731743
732- richText += "<ol>" + text + "</ol><hr />";
744+ richText += "<ol>" + text + "</ol><hr />\r\n";
733745 }
734746
735747 // 予約語を変換
@@ -749,6 +761,7 @@
749761 "<td>" + constant.config.indent.name + "</td>" +
750762 getRadioHtml("indent", "1", constant.config.indent.v1) +
751763 getRadioHtml("indent", "2", constant.config.indent.v2) +
764+ getRadioHtml("indent", "3", constant.config.indent.v3) +
752765 "</tr>" +
753766 "<tr>" +
754767 "<td>" + constant.config.andor.name + "</td>" +
--- trunk/DMLBreaker3.x/config.js (revision 58)
+++ trunk/DMLBreaker3.x/config.js (revision 59)
@@ -1,5 +1,5 @@
11 /*
2- * Copyright 2009-2017 Cosmos Inc. All right reserved.
2+ * Copyright 2009-2018 Cosmos Inc. All right reserved.
33 * http://www.cos-mos.co.jp/
44 */
55
@@ -27,8 +27,8 @@
2727 var conifgStatus = 2;
2828
2929
30-// インデント(★1:スペース4、2:タブ) ※Rich Textはスペース4で固定
31-var configIndent = 1;
30+// インデント(1:スペース2、★2:スペース4、3:タブ) ※Rich Textはスペース4で固定
31+var configIndent = 2;
3232
3333 // AND/OR/ON位置(1:前1[インデントなし]、2:前2[インデントあり]、★3:後)
3434 var configAndor = 3;
--- trunk/DMLBreaker3.x/readme.txt (revision 58)
+++ trunk/DMLBreaker3.x/readme.txt (revision 59)
@@ -10,18 +10,18 @@
1010 GNU General Public License v3(GPLv3)
1111
1212 [バージョン]
13-3.03
13+3.04
1414
1515 [動作確認済み環境]
16-WindowsXP、Windows7、Windows8.1、Mac OS X 10.10(Yosemite)
16+WindowsXP以降、Mac OS X 10.10(Yosemite)
1717
1818 [OS、配布ファイル以外に必要なソフト]
1919 Webブラウザ(以下のブラウザで動作確認済)
20-- Internet Exproler 7/8/9/10/11
21-- Firefox 37
22-- Google Chrome 42
23-- Safari 6(Windows Safari 5)
24-- Opera 29
20+- Internet Exproler 11
21+- Firefox
22+- Google Chrome
23+- Safari
24+- Opera
2525 ※旧IE、Safari、Operaは、ファイル出力に対応してません。
2626
2727 [DML Breaker uses the following libraries]
@@ -30,7 +30,7 @@
3030 http://www.extjs.com/license
3131
3232 [作品紹介]
33-OracleのDML(SELECT/INSERT/UPDATE/DELETE)を、人が見やすいSQLに整形するツールです。
33+OracleのDML(SELECT/INSERT/UPDATE/DELETE)を、人が見やすいSQLに整形するツールです。(標準版)
3434 設計書等の転記やDML解析する場合に、ご利用ください。
3535 なお、Oracle以外は動作を保障を致しかねますが、基本的なDML構文であれば問題ないと思われます。
3636
@@ -45,7 +45,7 @@
4545 - DML予約語およびOracleキーワードの小文字および大文字変換をサポートします。
4646 - ネットワークに繋がっていなくても、ブラウザだけで動作します。
4747 - セミコロン「;」で、複数のDMLを区切ることにより、一括して整形することができます。
48-- クリップボードを経由した、DMLテキストの入出力に対応します(IE限定機能)
48+- クリップボードを経由した、DMLテキストの入出力に対応します(IE限定機能)
4949 - DMLBreaker.jsを他のシステムへ組み込むことで、DML整形が行えるようになります。
5050
5151 [ファイル説明]
@@ -53,10 +53,10 @@
5353 - base.js 基盤ファイル
5454 - config.js 設定ファイル
5555 - DMLBreaker.html 本体HTMLファイル
56-- DMLBreaker.js DML整形ファイル
57-- license.txt ライセンス
56+- DMLBreaker.js DML整形ファイル(シンプル版と同じ)
57+- license.txt ライセンス(シンプル版と同じ)
5858 - readme.txt 説明書
59-- SampleSQL.txt サンプルSQL
59+- SampleSQL.txt サンプルSQL(シンプル版と同じ)
6060
6161 [他のシステムへ組み込む]
6262 (1) DMLBreaker.jsをシステムへ配置する。
@@ -81,3 +81,4 @@
8181 2016/01/27 3.01 他のシステムへ組み込めるように「DMLBreaker.js」を単独で使用できるようにした
8282 2016/02/18 3.02 デザイン調整、Simple版に「Rich Text」機能を追加
8383 2017/04/01 3.03 カンマ位置が前の場合の整形不備などを修正
84+2018/02/17 3.04 設定「スペース2」を追加、CASEなどの整形不備を修正
Show on old repository browser