001: /*
002: * SqlFormatterTest.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.sql.formatter;
013:
014: import junit.framework.TestCase;
015: import workbench.TestUtil;
016: import workbench.resource.Settings;
017:
018: /**
019: *
020: * @author support@sql-workbench.net
021: */
022: public class SqlFormatterTest extends TestCase {
023: public SqlFormatterTest(String testName) {
024: super (testName);
025: }
026:
027: public void setUp() throws Exception {
028: TestUtil util = new TestUtil(this .getName());
029: util.prepareEnvironment();
030: }
031:
032: public void testFileParam() {
033: try {
034: String sql = "wbexport -file=\"c:\\Documents and Settings\\test.txt\" -type=text";
035: SqlFormatter f = new SqlFormatter(sql, 100);
036: String formatted = f.getFormattedSql().toString();
037: assertTrue(formatted
038: .indexOf("\"c:\\Documents and Settings\\test.txt\"") > 0);
039: } catch (Exception e) {
040: e.printStackTrace();
041: fail(e.getMessage());
042: }
043: }
044:
045: public void testWbConfirm() {
046: try {
047: String sql = "wbconfirm 'my message'";
048: SqlFormatter f = new SqlFormatter(sql, 100);
049: CharSequence formatted = f.getFormattedSql();
050: String expected = "WbConfirm 'my message'";
051: assertEquals("WbConfirm not formatted correctly", expected,
052: formatted);
053: } catch (Exception e) {
054: e.printStackTrace();
055: fail(e.getMessage());
056: }
057: }
058:
059: public void testAliasForSubselect() {
060: try {
061: String sql = "select a,b, (select a,b from t2) col4 from t1";
062: SqlFormatter f = new SqlFormatter(sql, 100);
063: CharSequence formatted = f.getFormattedSql();
064: String expected = "SELECT a,\n" + " b,\n"
065: + " (SELECT a, b FROM t2) col4\n" + "FROM t1";
066: assertEquals("SELECT in VALUES not formatted", expected,
067: formatted);
068: } catch (Exception e) {
069: e.printStackTrace();
070: fail(e.getMessage());
071: }
072: }
073:
074: public void testAsInFrom() {
075: try {
076: String sql = "select t1.a, t2.b from bla as t1, t2";
077: SqlFormatter f = new SqlFormatter(sql, 100);
078: CharSequence formatted = f.getFormattedSql();
079: String expected = "SELECT t1.a,\n" + " t2.b\n"
080: + "FROM bla AS t1,\n" + " t2";
081: // Thread.yield();
082: // System.out.println("sql=" + formatted);
083: assertEquals("SELECT in VALUES not formatted", expected,
084: formatted);
085: } catch (Exception e) {
086: e.printStackTrace();
087: fail(e.getMessage());
088: }
089: }
090:
091: public void testInsertWithSubselect() {
092: try {
093: String sql = "insert into tble (a,b) values ( (select max(x) from y), 'bla')";
094: String expected = "INSERT INTO tble\n" + "(\n" + " a,\n"
095: + " b\n" + ") \n" + "VALUES\n" + "(\n"
096: + " (SELECT MAX(x) FROM y),\n" + " 'bla'\n"
097: + ")";
098: SqlFormatter f = new SqlFormatter(sql, 100);
099: CharSequence formatted = f.getFormattedSql();
100: // System.out.println("*** got ***");
101: // System.out.println(formatted);
102: // System.out.println("*** expected ***");
103: // System.out.println(expected);
104: // System.out.println("**************");
105: assertEquals("SELECT in VALUES not formatted", expected,
106: formatted);
107: } catch (Exception e) {
108: e.printStackTrace();
109: }
110: }
111:
112: public void testLowerCaseFunctions() {
113: try {
114: String sql = "select col1, MAX(col2) from theTable group by col1;";
115: String expected = "SELECT col1,\n max(col2)\nFROM theTable\nGROUP BY col1;";
116: SqlFormatter f = new SqlFormatter(sql, 100);
117: f.setUseLowerCaseFunctions(true);
118: CharSequence formatted = f.getFormattedSql();
119: // System.out.println("*** got ***");
120: // System.out.println(formatted);
121: // System.out.println("*** expected ***");
122: // System.out.println(expected);
123: // System.out.println("**************");
124: assertEquals("SELECT in VALUES not formatted", expected,
125: formatted);
126: } catch (Exception e) {
127: e.printStackTrace();
128: }
129: }
130:
131: public void testCase() {
132: try {
133: String sql = "SELECT col1 as bla, case when x = 1 then 2 else 3 end AS y FROM table";
134: String expected = "SELECT col1 AS bla,\n CASE\n WHEN x=1 THEN 2\n ELSE 3\n END AS y\nFROM TABLE";
135: SqlFormatter f = new SqlFormatter(sql, 100);
136: CharSequence formatted = f.getFormattedSql();
137: assertEquals("CASE alias not formatted", expected,
138: formatted);
139:
140: sql = "SELECT case when x = 1 then 2 else 3 end AS y FROM table";
141: expected = "SELECT CASE\n WHEN x=1 THEN 2\n ELSE 3\n END AS y\nFROM TABLE";
142: f = new SqlFormatter(sql, 100);
143: formatted = f.getFormattedSql();
144: assertEquals("CASE alias not formatted", expected,
145: formatted);
146:
147: sql = "SELECT a,b,c from table order by b,case when a=1 then 2 when a=2 then 1 else 3 end";
148: expected = "SELECT a,\n" + " b,\n" + " c\n"
149: + "FROM TABLE\n" + "ORDER BY b,\n"
150: + " CASE \n"
151: + " WHEN a=1 THEN 2\n"
152: + " WHEN a=2 THEN 1\n"
153: + " ELSE 3\n" + " END";
154: f = new SqlFormatter(sql, 100);
155: formatted = f.getFormattedSql();
156: // System.out.println("=================");
157: // System.out.println(formatted);
158: // System.out.println("=================");
159: // System.out.println(expected);
160: // System.out.println("=================");
161: assertEquals("CASE alias not formatted", expected,
162: formatted);
163: } catch (Exception e) {
164: e.printStackTrace();
165: fail(e.getMessage());
166: }
167: }
168:
169: public void testWhitespace() {
170: try {
171: String sql = "alter table epg_value add constraint fk_value_attr foreign key (id_attribute) references attribute(id);";
172: String expected = "ALTER TABLE epg_value ADD CONSTRAINT fk_value_attr FOREIGN KEY (id_attribute) REFERENCES attribute(id);";
173: SqlFormatter f = new SqlFormatter(sql, 100);
174: CharSequence formatted = f.getFormattedSql();
175: assertEquals("ALTER TABLE not correctly formatted",
176: expected, formatted.toString().trim());
177: } catch (Exception e) {
178: e.printStackTrace();
179: }
180: }
181:
182: public void testColumnThreshold() throws Exception {
183: try {
184: String sql = "SELECT a,b,c from mytable";
185: SqlFormatter f = new SqlFormatter(sql, 100);
186: f.setMaxColumnsPerSelect(5);
187: CharSequence formatted = f.getFormattedSql();
188: String expected = "SELECT a, b, c\nFROM mytable";
189:
190: sql = "SELECT a,b,c,d,e,f,g,h,i from mytable";
191: f = new SqlFormatter(sql, 100);
192: f.setMaxColumnsPerSelect(5);
193: formatted = f.getFormattedSql();
194: expected = "SELECT a, b, c, d, e,\n f, g, h, i\nFROM mytable";
195: assertEquals(expected, formatted);
196: } catch (Exception e) {
197: e.printStackTrace();
198: fail(e.getMessage());
199: }
200: }
201:
202: public void testBracketIdentifier() throws Exception {
203: try {
204: String sql = "SELECT a,b,[MyCol] from mytable";
205: SqlFormatter f = new SqlFormatter(sql, 100);
206: CharSequence formatted = f.getFormattedSql();
207: String expected = "SELECT a,\n b,\n [MyCol]\nFROM mytable";
208: assertEquals(expected, formatted);
209: } catch (Exception e) {
210: e.printStackTrace();
211: fail(e.getMessage());
212: }
213: }
214:
215: public void testDecode() {
216: try {
217: String sql = "SELECT DECODE((MOD(INPUT-4,12)+1),1,'RAT',2,'OX',3,'TIGER',4,'RABBIT',5,'DRAGON',6,'SNAKE',7,'HORSE',8,'SHEEP/GOAT',9,'MONKEY',10,'ROOSTER',11,'DOG',12,'PIG') YR FROM DUAL";
218:
219: String expected = "SELECT DECODE((MOD(INPUT-4,12)+1),\n"
220: + " 1,'RAT',\n"
221: + " 2,'OX',\n"
222: + " 3,'TIGER',\n"
223: + " 4,'RABBIT',\n"
224: + " 5,'DRAGON',\n"
225: + " 6,'SNAKE',\n"
226: + " 7,'HORSE',\n"
227: + " 8,'SHEEP/GOAT',\n"
228: + " 9,'MONKEY',\n"
229: + " 10,'ROOSTER',\n"
230: + " 11,'DOG',\n"
231: + " 12,'PIG'\n" + " ) YR\n"
232: + "FROM DUAL";
233:
234: SqlFormatter f = new SqlFormatter(sql, 100);
235: CharSequence formatted = f.getFormattedSql();
236:
237: // System.out.println(StringUtil.escapeUnicode(expected));
238: // System.out.println(StringUtil.escapeUnicode(formatted));
239: // System.out.println(formatted);
240: assertEquals("Complex DECODE not formatted correctly",
241: expected, formatted);
242:
243: sql = "select decode(col1, 'a', 1, 'b', 2, 'c', 3, 99) from dual";
244: f = new SqlFormatter(sql, 100);
245: formatted = f.getFormattedSql();
246: expected = "SELECT decode(col1,\n"
247: + " 'a', 1,\n"
248: + " 'b', 2,\n"
249: + " 'c', 3,\n" + " 99\n"
250: + " ) \n" + "FROM dual";
251:
252: // System.out.println(StringUtil.escapeUnicode(expected));
253: // System.out.println(StringUtil.escapeUnicode(formatted));
254: assertEquals("DECODE not formatted correctly", expected,
255: formatted);
256: } catch (Exception e) {
257: e.printStackTrace();
258: fail(e.getMessage());
259: }
260: }
261:
262: public void testQuotedIdentifier() throws Exception {
263: try {
264: String sql = "SELECT a,b,\"c d\" from mytable";
265: SqlFormatter f = new SqlFormatter(sql, 100);
266: CharSequence formatted = f.getFormattedSql();
267: String expected = "SELECT a,\n b,\n \"c d\"\nFROM mytable";
268: assertEquals(expected, formatted);
269: } catch (Exception e) {
270: e.printStackTrace();
271: fail(e.getMessage());
272: }
273: }
274:
275: public void testGetFormattedSql() throws Exception {
276: try {
277: String sql = "--comment\nselect * from blub;";
278: Settings.getInstance().setInternalEditorLineEnding(
279: Settings.UNIX_LINE_TERMINATOR_PROP_VALUE);
280:
281: SqlFormatter f = new SqlFormatter(sql, 100);
282: String nl = f.getLineEnding();
283: CharSequence formatted = f.getFormattedSql();
284: // System.out.println(formatted);
285: String expected = "--comment\nSELECT *\nFROM blub;";
286: assertEquals("Not correctly formatted", expected, formatted);
287:
288: sql = "select x from y union all select y from x";
289: f = new SqlFormatter(sql, 100);
290: formatted = f.getFormattedSql();
291: expected = "SELECT x\nFROM y\nUNION ALL\nSELECT y\nFROM x";
292: assertEquals(expected, formatted);
293:
294: sql = "select x,y from y order by x\n--trailing comment";
295: f = new SqlFormatter(sql, 100);
296: formatted = f.getFormattedSql();
297: expected = "SELECT x,\n y\nFROM y\nORDER BY x\n--trailing comment";
298: assertEquals(expected, formatted.toString().trim());
299:
300: sql = "select x,y,z from y where a = 1 and b = 2";
301: f = new SqlFormatter(sql, 100);
302: formatted = f.getFormattedSql();
303: expected = "SELECT x,\n y,\n z\nFROM y\nWHERE a = 1\nAND b = 2";
304: assertEquals(expected, formatted);
305:
306: sql = "select x,y,z from y where a = 1 and b = (select min(x) from y)";
307: f = new SqlFormatter(sql, 100);
308: formatted = f.getFormattedSql();
309: expected = "SELECT x,\n y,\n z\nFROM y\nWHERE a = 1\nAND b = (SELECT MIN(x) FROM y)";
310: assertEquals(expected, formatted);
311:
312: sql = "select x,y,z from y where a = 1 and b = (select min(x) from y)";
313: f = new SqlFormatter(sql, 10);
314: formatted = f.getFormattedSql();
315: // System.out.println(formatted);
316: expected = "SELECT x,\n y,\n z\nFROM y\nWHERE a = 1\nAND b = (SELECT MIN(x)\n FROM y)";
317: // System.out.println(expected);
318: assertEquals(expected, formatted);
319:
320: sql = "UPDATE customer "
321: + " SET duplicate_flag = CASE (SELECT COUNT(*) FROM customer c2 WHERE c2.f_name = customer.f_name AND c2.s_name = customer.s_name GROUP BY f_name,s_name) \n"
322: + " WHEN 1 THEN 0 "
323: + " ELSE 1 "
324: + " END";
325: expected = "UPDATE customer\n"
326: + " SET duplicate_flag = CASE (SELECT COUNT(*)\n"
327: + " FROM customer c2\n"
328: + " WHERE c2.f_name = customer.f_name\n"
329: + " AND c2.s_name = customer.s_name\n"
330: + " GROUP BY f_name,\n"
331: + " s_name)\n"
332: + " WHEN 1 THEN 0\n"
333: + " ELSE 1\n"
334: + " END";
335: f = new SqlFormatter(sql, 10);
336:
337: formatted = f.getFormattedSql();
338: // System.out.println(StringUtil.escapeUnicode(expected, CharacterRange.RANGE_NONE));
339: // System.out.println("-------");
340: // System.out.println(formatted);
341: // System.out.println(StringUtil.escapeUnicode(formatted));
342: assertEquals(expected, formatted.toString().trim());
343:
344: sql = "SELECT ber.nachname AS ber_nachname, \n"
345: + " ber.nummer AS ber_nummer \n"
346: + "FROM table a WHERE (x in (select bla,bla,alkj,aldk,alkjd,dlaj,alkjdaf from blub 1, blub2, blub3 where x=1 and y=2 and z=3 and a=b and c=d) or y = 5)"
347: + " and a *= b and b = c (+)";
348: f = new SqlFormatter(sql, 10);
349: formatted = f.getFormattedSql();
350: expected = "SELECT ber.nachname AS ber_nachname,\n"
351: + " ber.nummer AS ber_nummer\n"
352: + "FROM TABLE a\n" + "WHERE (x IN (SELECT bla,\n"
353: + " bla,\n"
354: + " alkj,\n"
355: + " aldk,\n"
356: + " alkjd,\n"
357: + " dlaj,\n"
358: + " alkjdaf\n"
359: + " FROM blub 1,\n"
360: + " blub2,\n"
361: + " blub3\n"
362: + " WHERE x = 1\n"
363: + " AND y = 2\n"
364: + " AND z = 3\n"
365: + " AND a = b\n"
366: + " AND c = d) OR y = 5)\n"
367: + "AND a *= b\n" + "AND b = c (+)";
368: // System.out.println(formatted);
369: // System.out.println("---------");
370: // System.out.println(expected);
371: assertEquals(expected, formatted.toString().trim());
372:
373: sql = "update x set (a,b) = (select x,y from k);";
374: f = new SqlFormatter(sql, 50);
375: formatted = f.getFormattedSql();
376: expected = "UPDATE x\n SET (a,b) = (SELECT x, y FROM k);";
377: assertEquals(expected, formatted.toString().trim());
378: } catch (Exception e) {
379: e.printStackTrace();
380: fail(e.getMessage());
381: }
382: }
383: }
|