001: /*
002: * ScriptParserTest.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;
013:
014: import java.io.BufferedWriter;
015: import java.io.File;
016: import java.io.FileWriter;
017: import java.io.IOException;
018: import java.io.PrintWriter;
019: import java.util.Iterator;
020: import junit.framework.TestCase;
021: import workbench.TestUtil;
022: import workbench.util.SqlUtil;
023: import workbench.util.StringUtil;
024:
025: /**
026: *
027: * @author support@sql-workbench.net
028: */
029: public class ScriptParserTest extends TestCase {
030:
031: public ScriptParserTest(String testName) {
032: super (testName);
033: }
034:
035: public void testEmptyStatement() {
036: try {
037: // Check if a cursorposition at the far end of the statement is detected properly
038: String sql = "select 42 from dual;\n\nselect * \nfrom table\n;;\n";
039: ScriptParser p = new ScriptParser();
040: p.allowEmptyLineAsSeparator(false);
041: p.setScript(sql);
042: assertEquals(2, p.getSize());
043: } catch (Exception e) {
044: e.printStackTrace();
045: fail(e.getMessage());
046: }
047: }
048:
049: public void testCursorInEmptyLine() {
050: try {
051: String sql = "\nselect 42\nfrom dual;\nselect * \nfrom table\n;";
052: ScriptParser p = new ScriptParser();
053: p.allowEmptyLineAsSeparator(false);
054: p.setScript(sql);
055: int index = p.getCommandIndexAtCursorPos(0);
056: assertEquals("Wrong statement index", 0, index);
057: assertEquals(2, p.getSize());
058: } catch (Exception e) {
059: e.printStackTrace();
060: fail(e.getMessage());
061: }
062: }
063:
064: public void testEndPosition() {
065: try {
066: String sql = "select 42 from dual;\n\nselect * \nfrom table\n;";
067: ScriptParser p = new ScriptParser();
068: p.allowEmptyLineAsSeparator(false);
069: p.setScript(sql);
070: int pos = sql.lastIndexOf(";");
071: int index = p.getCommandIndexAtCursorPos(pos);
072: assertEquals(2, p.getSize());
073: assertEquals(1, index);
074: } catch (Exception e) {
075: e.printStackTrace();
076: fail(e.getMessage());
077: }
078: }
079:
080: public void testCursorPosInCommand() {
081: try {
082: String script = "select 42 from dual;\n\nselect x\n from y\n \n \n ;";
083: int pos = script.length() - 3;
084: ScriptParser p = new ScriptParser();
085: p.allowEmptyLineAsSeparator(false);
086: p.setScript(script);
087: int index = p.getCommandIndexAtCursorPos(pos);
088: assertEquals(2, p.getSize());
089: assertEquals(1, index);
090: int sqlPos = p.getIndexInCommand(index, pos);
091: String sql = p.getCommand(index);
092: } catch (Exception e) {
093: e.printStackTrace();
094: fail(e.getMessage());
095: }
096: }
097:
098: public void testEmptyLines() {
099: try {
100: String sql = "select a,b,c\r\nfrom test\r\nwhere x = 1";
101: ScriptParser p = new ScriptParser();
102: p.allowEmptyLineAsSeparator(true);
103: p.setScript(sql);
104: int count = p.getSize();
105: assertEquals("Wrong number of statements", 1, count);
106:
107: sql = "select a,b,c\nfrom test\nwhere x = 1";
108: p.setScript(sql);
109: count = p.getSize();
110: assertEquals("Wrong number of statements", 1, count);
111:
112: sql = "select a,b,c\nfrom test\nwhere x = 1\n\nselect x from y";
113: p.setScript(sql);
114: count = p.getSize();
115: assertEquals("Wrong number of statements", 2, count);
116: String cmd = p.getCommand(1);
117: assertEquals("Wrong statement returned", "select x from y",
118: cmd);
119:
120: sql = "select a,b,c\r\nfrom test\r\nwhere x = 1\r\n\r\nselect x from y";
121: p.setScript(sql);
122: count = p.getSize();
123: assertEquals("Wrong number of statements", 2, count);
124: cmd = p.getCommand(1);
125: assertEquals("Wrong statement returned", "select x from y",
126: cmd);
127: } catch (Exception e) {
128: e.printStackTrace();
129: }
130: }
131:
132: public void testSingleLineDelimiter() {
133: String sql = "DROP\n" + "/\n" + "CREATE\n" + "/\n";
134: try {
135: ScriptParser p = new ScriptParser();
136: p.setAlternateDelimiter(new DelimiterDefinition("/", true));
137: p.setCheckForSingleLineCommands(false);
138: p.setScript(sql);
139: int size = p.getSize();
140: assertEquals("Wrong number of statements", 2, size);
141: assertEquals("Wrong statement returned", "DROP", p
142: .getCommand(0));
143: assertEquals("Wrong statement returned", "CREATE", p
144: .getCommand(1));
145:
146: sql = "DROP\r\n" + "/\r\n" + "CREATE\r\n" + "/\r\n";
147:
148: p.setScript(sql);
149: size = p.getSize();
150: assertEquals("Wrong number of statements", 2, size);
151: assertEquals("Wrong statement returned", "DROP", p
152: .getCommand(0));
153: assertEquals("Wrong statement returned", "CREATE", p
154: .getCommand(1));
155:
156: } catch (Exception e) {
157: e.printStackTrace();
158: fail(e.getMessage());
159: }
160: }
161:
162: public void testAlternateFileParsing() {
163: try {
164: TestUtil util = new TestUtil("alternateFileParsing");
165: util.prepareEnvironment();
166:
167: File scriptFile = new File(util.getBaseDir(),
168: "testscript.sql");
169: PrintWriter writer = new PrintWriter(new FileWriter(
170: scriptFile));
171: writer.println("-- test script");
172: writer
173: .println("CREATE TABLE person (nr integer primary key, firstname varchar(100), lastname varchar(100))");
174: writer.println("/");
175: writer
176: .println("insert into person (nr, firstname, lastname) values (1,'Arthur', 'Dent')");
177: writer.println("/");
178: writer
179: .println("insert into person (nr, firstname, lastname) values (2,'Ford', 'Prefect')");
180: writer.println("/");
181: writer
182: .println("insert into person (nr, firstname, lastname) values (3,'Zaphod', 'Beeblebrox')");
183: writer.println("/");
184: writer.println("commit");
185: writer.println("/");
186: writer.close();
187:
188: // Make sure the iterating parser is used, by setting
189: // a very low max file size
190: ScriptParser p = new ScriptParser(0);
191:
192: p.setDelimiter(new DelimiterDefinition("/", true));
193: p.setSupportOracleInclude(false);
194: p.setCheckForSingleLineCommands(false);
195: p.setCheckEscapedQuotes(false);
196:
197: p.setFile(scriptFile);
198: p.startIterator();
199: int size = 0;
200: while (p.hasNext()) {
201: size++;
202: String sql = p.getNextCommand();
203: if (size == 2) {
204: assertEquals(
205: "insert into person (nr, firstname, lastname) values (1,'Arthur', 'Dent')",
206: sql);
207: }
208: }
209: assertEquals("Wrong number of statements", 5, size);
210: } catch (Exception e) {
211: e.printStackTrace();
212: fail(e.getMessage());
213: }
214: }
215:
216: public void testQuotedDelimiter() {
217: String sql = "SELECT id,';' \n" + "FROM person; \n" + " \n"
218: + "select * \n" + "from country;";
219: try {
220: ScriptParser p = new ScriptParser(sql);
221: int size = p.getSize();
222: assertEquals("Wrong number of statements", 2, size);
223: assertEquals("Wrong statement returned",
224: "SELECT id,';' \nFROM person", p.getCommand(0));
225: } catch (Exception e) {
226: e.printStackTrace();
227: fail(e.getMessage());
228: }
229: }
230:
231: public void testMsGO() {
232: String sql = "SELECT id \n" + "FROM person GO\n" + " GO \n"
233: + " \n" + " \n" + "select * \n" + "from country \n"
234: + " GO \n\n ";
235: try {
236: ScriptParser p = new ScriptParser(sql);
237: // Test if the automatic detection of the MS SQL delimiter works
238: p
239: .setAlternateDelimiter(DelimiterDefinition.DEFAULT_MS_DELIMITER);
240: int size = p.getSize();
241: assertEquals("Wrong number of statements", 2, size);
242: //System.out.println("sql=" + p.getCommand(0));
243: assertEquals("Wrong statement returned",
244: "SELECT id \nFROM person GO", p.getCommand(0));
245: sql = "SELECT id \r\n" + "FROM person GO\r\n"
246: + " GO \r\n" + " \r\n" + "select * \r\n"
247: + "from country \r\n" + " GO \r\n";
248: p.setScript(sql);
249: size = p.getSize();
250: assertEquals("Wrong number of statements", 2, size);
251: assertEquals("Wrong statement returned",
252: "SELECT id \r\nFROM person GO", p.getCommand(0));
253: } catch (Exception e) {
254: e.printStackTrace();
255: fail(e.getMessage());
256: }
257: }
258:
259: public void testAlternateDelimiter() {
260: String sql = "SELECT id \n" + "FROM person \n" + "# \n" + " \n"
261: + " \n" + "select * \n" + "from country \n" + "#";
262: try {
263: ScriptParser p = new ScriptParser(sql);
264: p.setAlternateDelimiter(new DelimiterDefinition("#", true));
265: int size = p.getSize();
266: assertEquals("Wrong number of statements", 2, size);
267:
268: p
269: .setAlternateDelimiter(new DelimiterDefinition(
270: "./", false));
271: size = p.getSize();
272: assertEquals("Wrong number of statements", 1, size);
273:
274: sql = "SELECT id; \n" + "FROM person \n" + "./ \n" + " \n"
275: + "select * \n" + "from country \n" + "./";
276: p.setScript(sql);
277: size = p.getSize();
278: assertEquals("Wrong number of statements", 2, size);
279:
280: sql = "CREATE PROCEDURE remove_emp (employee_id NUMBER) AS\n"
281: + " tot_emps NUMBER;\n"
282: + " BEGIN\n"
283: + " DELETE FROM employees\n"
284: + " WHERE employees.employee_id = remove_emp.employee_id;\n"
285: + " tot_emps := tot_emps - 1;\n"
286: + " END;\n"
287: + "/";
288: p.setScript(sql);
289: p.setAlternateDelimiter(new DelimiterDefinition("/", true));
290: size = p.getSize();
291: assertEquals("Wrong number of statements", 1, size);
292:
293: sql = "DECLARE \n"
294: + " Last_name VARCHAR2(10) \n"
295: + " Cursor c1 IS SELECT last_name \n"
296: + " FROM employees \n"
297: + " WHERE department_id = 20 \n"
298: + "BEGIN \n" + " OPEN c1 \n" + " LOOP \n"
299: + " FETCH c1 INTO Last_name \n"
300: + " EXIT WHEN c1%NOTFOUND \n"
301: + " DBMS_OUTPUT.PUT_LINE(Last_name) \n"
302: + " END LOOP \n" + "END \n" + "/";
303: p.setScript(sql);
304: size = p.getSize();
305: assertEquals("Wrong number of statements", 1, size);
306: } catch (Exception e) {
307: e.printStackTrace();
308: fail(e.getMessage());
309: }
310: }
311:
312: public void testAccessByCursorPos() {
313: try {
314: String sql = "-- comment line 1\n"
315: + "select * from person;\n" + "\n"
316: + "-- next comment\n" + "insert into bla;\n" + "\n"
317: + "/* bla stuff \n" + " bla stuff \n"
318: + " bla stuff */\n" + "-- line comment\n"
319: + "delete from blub;";
320: ScriptParser p = new ScriptParser(sql);
321: assertEquals("Not enough commands", 3, p.getSize());
322:
323: String c = p.getCommand(0);
324: assertEquals("Wrong command at index 0", "SELECT", SqlUtil
325: .getSqlVerb(c));
326:
327: c = p.getCommand(2);
328: assertEquals("Wrong command at index 0", "DELETE", SqlUtil
329: .getSqlVerb(c));
330:
331: int index = p.getCommandIndexAtCursorPos(5);
332: assertEquals("Wrong command at cursor pos", index, 0);
333:
334: index = p.getCommandIndexAtCursorPos(45);
335: assertEquals("Wrong command at cursor pos", index, 1);
336:
337: index = p.getCommandIndexAtCursorPos(99999);
338: assertEquals("Wrong command at cursor pos", index, -1);
339:
340: } catch (Exception e) {
341: e.printStackTrace();
342: fail(e.getMessage());
343: }
344: }
345:
346: public void testShortInclude() {
347: try {
348: String sql = "-- comment line 1\n"
349: + "select * from person where name = 'Dent';\n"
350: + "\n"
351: + "-- next comment\n"
352: + "insert into bla (nr, name) values (1,'laber');\n"
353: + "\n" + "@myfile.sql";
354: ScriptParser p = new ScriptParser(sql);
355: p.setCheckForSingleLineCommands(true);
356: assertEquals("Not enough commands", 3, p.getSize());
357: assertEquals("Wrong command", "@myfile.sql", p
358: .getCommand(2));
359:
360: sql = "-- comment line 1\n"
361: + "select * from person where name = 'Dent';\n"
362: + "\n"
363: + "-- next comment\n"
364: + "insert into bla (nr, name) values (1,'laber');\n"
365: + "\n" + "@myfile.sql\n" + "\n"
366: + "delete from theTable;";
367: p = new ScriptParser(sql);
368: p.setCheckForSingleLineCommands(true);
369: assertEquals("Not enough commands", 4, p.getSize());
370: assertEquals("Wrong command", "@myfile.sql", p
371: .getCommand(2));
372: assertEquals("Wrong command", "delete from theTable", p
373: .getCommand(3));
374: } catch (Exception e) {
375: e.printStackTrace();
376: fail(e.getMessage());
377: }
378: }
379:
380: private File createScript(int counter, String lineEnd)
381: throws IOException {
382: File tempdir = new File(System.getProperty("java.io.tmpdir"));
383: File script = new File(tempdir, "largefile.sql");
384: BufferedWriter out = new BufferedWriter(new FileWriter(script));
385: for (int i = 0; i < counter; i++) {
386: out.write("--- test command");
387: out.write(lineEnd);
388: out.write("insert into test_table");
389: out.write(lineEnd);
390: out.write("col1, col2, col3, col4)");
391: out.write(lineEnd);
392: out.write("values ('1','2''',3,' a two line ");
393: out.write(lineEnd);
394: out.write("; quoted text');");
395: out.write(lineEnd);
396: out.write(lineEnd);
397: }
398: out.close();
399: return script;
400: }
401:
402: public void testFileParsing() {
403: try {
404: int counter = 500;
405: File script = createScript(counter, "\n");
406: ScriptParser p = new ScriptParser(100);
407: p.setFile(script);
408: int count = 0;
409: Iterator itr = p.getIterator();
410: while (itr.hasNext()) {
411: String sql = (String) itr.next();
412: assertNotNull("No SQL returned at " + count, sql);
413: String verb = SqlUtil.getSqlVerb(sql);
414: assertEquals("Wrong statement retrieved using LF",
415: "insert", verb.toLowerCase());
416: count++;
417: }
418: p.done();
419: assertEquals("Wrong number of statements using LF",
420: counter, count);
421: script.delete();
422:
423: script = createScript(counter, "\r\n");
424: p.setFile(script);
425: count = 0;
426: itr = p.getIterator();
427: while (itr.hasNext()) {
428: String sql = (String) itr.next();
429: assertNotNull("No SQL returned at " + count, sql);
430: String verb = SqlUtil.getSqlVerb(sql);
431: assertEquals("Wrong statement retrieved using CRLF",
432: "insert", verb.toLowerCase());
433: count++;
434: }
435: p.done();
436: assertEquals("Wrong number of statements using CRL",
437: counter, count);
438: script.delete();
439:
440: } catch (Exception e) {
441: e.printStackTrace();
442: fail();
443: }
444: }
445:
446: public void testMutliStatements() {
447: String sql = "SELECT '(select l.label from template_field_label l where l.template_field_id = f.id and l.language_code = '''|| l.code ||''') as \"'||l.code||' ('||l.name||')\",' \n"
448: + "FROM (SELECT DISTINCT language_code FROM template_field_label) ll, \n"
449: + " language l \n"
450: + "WHERE ll.language_code = l.code \n"
451: + ";\n"
452: + "select * from template_field_label;\n\n"
453: + "SELECT distinct t.KEY \n"
454: + "FROM translation t, content_folder f \n"
455: + "WHERE t.key = f.folder_name;";
456:
457: ScriptParser p = new ScriptParser(sql);
458: assertEquals(3, p.getSize());
459: assertEquals("select * from template_field_label", p
460: .getCommand(1));
461:
462: sql = "/* \n"
463: + "* comment comment comment \n"
464: + "* comment \n"
465: + "* comment \n"
466: + "*/ \n"
467: + "-- comment comment comment comment comment comment comment. \n"
468: + "-- comment comment comment comment comment comment comment. \n"
469: + "-- comment comment comment comment comment comment comment. \n"
470: + "-- comment comment comment comment comment comment comment. \n"
471: + " \n"
472: + "-- ############################################# \n"
473: + "-- ## ## \n"
474: + "-- ## Stuff ## \n"
475: + "-- ## ## \n"
476: + "alter table participants drop constraint r_05; -- make sure you recreate this foreign key after inserting data! \n"
477: + "drop table organizations;\n" + "@include.sql\n"
478: + "\n" + "select * from bla;";
479:
480: p.setScript(sql);
481: p.setSupportOracleInclude(true);
482: assertEquals(4, p.getSize());
483: String verb = SqlUtil.getSqlVerb(p.getCommand(1));
484: assertEquals("drop", verb.toLowerCase());
485: String s = p.getCommand(0);
486: String clean = SqlUtil.makeCleanSql(s, false, false, '\'');
487: assertEquals("alter table participants drop constraint r_05",
488: clean);
489: s = p.getCommand(2);
490: assertEquals("@include.sql", s);
491:
492: // Now test with Windows linefeeds
493: sql = StringUtil.replace(sql, "\n", "\r\n");
494: p.setScript(sql);
495: assertEquals(4, p.getSize());
496: verb = SqlUtil.getSqlVerb(p.getCommand(1));
497: assertEquals("drop", verb.toLowerCase());
498: s = p.getCommand(0);
499: clean = SqlUtil.makeCleanSql(s, false, false, '\'');
500: assertEquals("alter table participants drop constraint r_05",
501: clean);
502: s = p.getCommand(2);
503: assertEquals("@include.sql", s);
504:
505: sql = "SELECT distinct t.KEY \r\n"
506: + "FROM translation t, content_folder f \r\n"
507: + "WHERE t.key = f.folder_name \r\n"
508: + "--AND LANGUAGE = 'en' \r\n" + ";\r\n" + "\r\n"
509: + "WBDIFF -sourceprofile=\"CMTS\" \r\n"
510: + " -file=c:/temp/test.xml \r\n"
511: + " -includeindex=false \r\n"
512: + " -includeforeignkeys=false \r\n"
513: + " -includeprimarykeys=false \r\n" + ";\r\n";
514: p = new ScriptParser(sql);
515: assertEquals(2, p.getSize());
516: }
517:
518: public void testSingleLineStatements() {
519: try {
520: String sql = "set nocount on\ndeclare @x int\nselect 123;";
521: ScriptParser p = new ScriptParser(sql);
522: p.setCheckForSingleLineCommands(true);
523: assertEquals(3, p.getSize());
524:
525: sql = "declare @x int\nset nocount on\nselect 123;";
526: p = new ScriptParser(sql);
527: p.setCheckForSingleLineCommands(true);
528: assertEquals(3, p.getSize());
529: } catch (Exception e) {
530: e.printStackTrace();
531: fail(e.getMessage());
532: }
533: }
534:
535: public void testAlternateLineComment() {
536: String sql = "# this is a non-standard comment;\n"
537: + "select * from test1;\n"
538: + "# another non-standard comment;\n"
539: + "select * from test2;\n" + "-- standard comment;\n"
540: + "select * from test3;\n";
541: ScriptParser parser = new ScriptParser(sql);
542: parser.setAlternateLineComment("#");
543: int count = parser.getSize();
544: assertEquals("Wrong statement count", count, 3);
545: }
546:
547: public void testUnicodeComments() {
548: String sql = "-- \u00e4\u00b8\u00ad\u00e6\u2013\u2021\u00e6\u00b3\u00a8\u00e9\u2021\u0160\n"
549: + "select * from test;\n"
550: + "-- \u00e4\u00b8\u00ad\u00e6\u2013\u2021\u00e6\u00b3\u00a8\u00e9\u2021\u0160\n"
551: + "select * from test2;\n";
552: //String sql = "/* \uD8D5\uD8D7\uD8D9 */\nINSERT INTO something;\n-- \u4E2D\u6587\u6CE8 \nSELECT * FROM test;";
553: ScriptParser parser = new ScriptParser(sql);
554:
555: int count = parser.getSize();
556: assertEquals("Wrong statement count", count, 2);
557: int pos = sql.indexOf("from test2");
558: int index = parser.getCommandIndexAtCursorPos(pos);
559: assertEquals(1, index);
560: String cmd = parser.getCommand(index);
561: // System.out.println("cmd=" + cmd);
562: }
563:
564: }
|