001: /*
002: * SqlUtilTest.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.util;
013:
014: import java.lang.reflect.Field;
015: import java.util.List;
016: import junit.framework.TestCase;
017:
018: /**
019: *
020: * @author support@sql-workbench.net
021: */
022: public class SqlUtilTest extends TestCase {
023:
024: public SqlUtilTest(String testName) {
025: super (testName);
026: }
027:
028: protected void setUp() throws Exception {
029: }
030:
031: protected void tearDown() throws Exception {
032: }
033:
034: public void testGetCreateType() {
035: try {
036: String sql = "create\n --comment\n table bla (nr integer);";
037: String type = SqlUtil.getCreateType(sql);
038: assertEquals("Wrong type returned", "TABLE", type);
039:
040: sql = "-- comment\ncreate view blub as select * from bla;";
041: type = SqlUtil.getCreateType(sql);
042: assertEquals("Wrong type returned", "VIEW", type);
043:
044: sql = "/* blubber */\ncreate \nor \nreplace -- comment\nview blub as select * from bla;";
045: type = SqlUtil.getCreateType(sql);
046: assertEquals("Wrong type returned", "VIEW", type);
047:
048: sql = "/* blubber */\nrecreate VIEW blub as select * from bla;";
049: type = SqlUtil.getCreateType(sql);
050: assertEquals("Wrong type returned", "VIEW", type);
051:
052: sql = "/* blubber */\ncreate package blub;";
053: type = SqlUtil.getCreateType(sql);
054: assertEquals("Wrong type returned", "PACKAGE", type);
055:
056: sql = "--- do something\ncreate\n or replace\n package body blub;";
057: type = SqlUtil.getCreateType(sql);
058: assertEquals("Wrong type returned", "PACKAGE BODY", type);
059: } catch (Exception e) {
060: e.printStackTrace();
061: fail(e.getMessage());
062: }
063:
064: }
065:
066: public void testGetDeleteTable() {
067: try {
068: String sql = "delete \nfrom mytable";
069: String table = SqlUtil.getDeleteTable(sql);
070: assertEquals("Wrong table returned", "mytable", table);
071:
072: sql = "-- bla\ndelete mytable";
073: table = SqlUtil.getDeleteTable(sql);
074: assertEquals("Wrong table returned", "mytable", table);
075:
076: sql = "delete\n--bla\nmyschema.mytable";
077: table = SqlUtil.getDeleteTable(sql);
078: assertEquals("Wrong table returned", "myschema.mytable",
079: table);
080:
081: sql = "delete from myschema.mytable";
082: table = SqlUtil.getDeleteTable(sql);
083: assertEquals("Wrong table returned", "myschema.mytable",
084: table);
085:
086: sql = "delete \"FROM\"";
087: table = SqlUtil.getDeleteTable(sql);
088: assertEquals("Wrong table returned", "\"FROM\"", table);
089:
090: sql = "delete from \"FROM\"";
091: table = SqlUtil.getDeleteTable(sql);
092: assertEquals("Wrong table returned", "\"FROM\"", table);
093:
094: //sql "table = SqlUtil.getDeleteTable(sql);"
095: } catch (Exception e) {
096: e.printStackTrace();
097: fail(e.getMessage());
098: }
099:
100: }
101:
102: public void testGetInsertTable() {
103: try {
104: String sql = "insert into mytable";
105: String table = SqlUtil.getInsertTable(sql);
106: assertEquals("Wrong table returned", "mytable", table);
107:
108: sql = "insert into theschema.mytable";
109: table = SqlUtil.getInsertTable(sql);
110: assertEquals("Wrong table returned", "theschema.mytable",
111: table);
112:
113: sql = "insert into \"into\"";
114: table = SqlUtil.getInsertTable(sql);
115: assertEquals("Wrong table returned", "\"into\"", table);
116: } catch (Exception e) {
117: e.printStackTrace();
118: fail(e.getMessage());
119: }
120: }
121:
122: public void testCleanSql() {
123: String sql = "select \r\n from project";
124: String clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
125: assertEquals("Not correctly cleaned", clean,
126: "select from project");
127:
128: sql = "select \r\n from project;";
129: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
130: assertEquals("Not correctly cleaned", clean,
131: "select from project");
132:
133: sql = "select *\r\n from project ; ";
134: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
135: assertEquals("Not correctly cleaned", clean,
136: "select * from project");
137:
138: sql = "select * from project\n;\n";
139: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
140: assertEquals("Not correctly cleaned", clean,
141: "select * from project");
142:
143: sql = "select 'some\nvalue' from project";
144: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
145: // nothing should be changed!
146: assertEquals("Not correctly cleaned", clean, sql);
147:
148: sql = "select 'some\nvalue' \nfrom project";
149: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
150: assertEquals("Not correctly cleaned", clean,
151: "select 'some\nvalue' from project");
152:
153: sql = "select\t'some\n\tvalue' from project";
154: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
155: assertEquals("Not correctly cleaned", clean,
156: "select 'some\n\tvalue' from project");
157:
158: sql = "select from \"project\"";
159: clean = SqlUtil.makeCleanSql(sql, false, false, '\'');
160: // nothing should be changed!
161: assertEquals("Not correctly cleaned", clean, sql);
162:
163: }
164:
165: public void testGetSelectColumns() {
166: String sql = "select x,y,z from bla";
167: List<String> l = SqlUtil.getSelectColumns(sql, true);
168: assertEquals("Not enough columns", 3, l.size());
169: assertEquals("x", l.get(0));
170: assertEquals("z", l.get(2));
171:
172: sql = "select x,y,z";
173: l = SqlUtil.getSelectColumns(sql, true);
174: assertEquals("Not enough columns", 3, l.size());
175: assertEquals("x", l.get(0));
176: assertEquals("z", l.get(2));
177:
178: sql = "select x\n ,y\n ,z FROM bla";
179: l = SqlUtil.getSelectColumns(sql, true);
180: assertEquals("Not enough columns", 3, l.size());
181: assertEquals("x", l.get(0));
182: assertEquals("z", l.get(2));
183:
184: sql = "SELECT a.att1\n ,a.att2\nFROM adam a";
185: l = SqlUtil.getSelectColumns(sql, true);
186: assertEquals("Not enough columns", 2, l.size());
187:
188: sql = "SELECT to_char(date_col, 'YYYY-MM-DD'), col2 as \"Comma, column\", func('bla,blub')\nFROM adam a";
189: l = SqlUtil.getSelectColumns(sql, false);
190: assertEquals("Not enough columns", 3, l.size());
191: assertEquals("Wrong first column",
192: "to_char(date_col, 'YYYY-MM-DD')", l.get(0));
193: assertEquals("Wrong third column", "func('bla,blub')", l.get(2));
194:
195: sql = "SELECT extract(year from rec_date) FROM mytable";
196: l = SqlUtil.getSelectColumns(sql, false);
197: assertEquals("Not enough columns", 1, l.size());
198: assertEquals("Wrong first column",
199: "extract(year from rec_date)", l.get(0));
200:
201: sql = "SELECT extract(year from rec_date) FROM mytable";
202: l = SqlUtil.getSelectColumns(sql, true);
203: assertEquals("Not enough columns", 1, l.size());
204: assertEquals("Wrong first column",
205: "extract(year from rec_date)", l.get(0));
206:
207: sql = "SELECT extract(year from rec_date) as rec_year FROM mytable";
208: l = SqlUtil.getSelectColumns(sql, true);
209: assertEquals("Not enough columns", 1, l.size());
210: assertEquals("Wrong first column",
211: "extract(year from rec_date) as rec_year", l.get(0));
212: }
213:
214: public void testStripColumnAlias() {
215: String expression = "p.name as lastname";
216: String col = SqlUtil.striptColumnAlias(expression);
217: assertEquals("p.name", col);
218:
219: expression = "p.name";
220: col = SqlUtil.striptColumnAlias(expression);
221: assertEquals("p.name", col);
222:
223: expression = "p.name as";
224: col = SqlUtil.striptColumnAlias(expression);
225: assertEquals("p.name", col);
226:
227: expression = "to_char(dt, 'YYYY')";
228: col = SqlUtil.striptColumnAlias(expression);
229: assertEquals("to_char(dt, 'YYYY')", col);
230:
231: }
232:
233: public void testGetSqlVerb() {
234: String sql = "-- comment line1\nSELECT * from dummy";
235: String verb = SqlUtil.getSqlVerb(sql);
236: assertEquals("SELECT", verb);
237:
238: sql = "-- comment line1\n-- second line\n\n /* bla */\nSELECT";
239: verb = SqlUtil.getSqlVerb(sql);
240: assertEquals("SELECT", verb);
241:
242: sql = "/* \n"
243: + "* $URL: some_script.sql $ \n"
244: + "* $Revision: 1.5 $ \n"
245: + "* $LastChangedDate: 2006-05-05 20:29:15 -0400 (Fri, 05 May 2006) $ \n"
246: + "*/ \n"
247: + "-- A quis Lorem consequat Aenean tellus risus convallis velit Maecenas arcu. \n"
248: + "-- Suspendisse Maecenas tempor Lorem congue laoreet vel congue sit malesuada nibh. \n"
249: + "-- Lorem ipsum dolor sit amet consectetuer vitae Suspendisse ante Nullam lacinia \n"
250: + " \n"
251: + "-- ############################################# \n"
252: + "-- ## ## \n"
253: + "-- ## Organizations ## \n"
254: + "-- ## ## \n"
255: + "alter table participants drop constraint fk_bla; -- Laoreet laoreet condimentum iaculis commodo dui id quis tempus accumsan wisi. Justo quam Curabitur dictumst non facilisis arcu Morbi semper pretium volutpat. Vestibulum habitasse Donec sapien adipiscing Suspendisse tempus habitant sed consectetuer pellentesque! \n";
256:
257: verb = SqlUtil.getSqlVerb(sql);
258: assertEquals("ALTER", verb);
259:
260: sql = "-- comment\n @bla.sql";
261: verb = SqlUtil.getSqlVerb(sql);
262: assertEquals("@", verb);
263:
264: sql = "-- comment only";
265: verb = SqlUtil.getSqlVerb(sql);
266: assertEquals("None-empty verb returned", true, StringUtil
267: .isEmptyString(verb));
268: }
269:
270: public void testGetTables() {
271: String sql = "select *\nfrom\n-- list the tables here\ntable1 t1, table2 t2, table3 t3";
272: List l = SqlUtil.getTables(sql, false);
273: assertEquals(3, l.size());
274:
275: assertEquals("table1", (String) l.get(0));
276: assertEquals("table2", (String) l.get(1));
277: assertEquals("table3", (String) l.get(2));
278:
279: l = SqlUtil.getTables(sql, true);
280: assertEquals(3, l.size());
281:
282: assertEquals("table1 t1", (String) l.get(0));
283: assertEquals("table2 t2", (String) l.get(1));
284: assertEquals("table3 t3", (String) l.get(2));
285:
286: sql = "SELECT cr.dealid, \n"
287: + " cs.state, \n"
288: + " bq.* \n"
289: + "FROM dbo.tblcreditrow cr \n"
290: + "-- bla blubber \n"
291: + "INNER JOIN bdb_ie.dbo.tblbdbproduct p ON cr.partnumber = p.partnumber \n"
292: + "RIGHT OUTER JOIN tblbidquantity bq ON bq.partnumber LIKE p.mainpartnumber + '%'AND bq.bidid = cr.bidid \n"
293: + "INNER JOIN tblcredit c ON c.creditid = cr.creditid \n"
294: + "INNER JOIN tblcreditstate cs ON cs.creditstateid = c.creditstateid \n"
295: + "WHERE c.arrivaldate >= '2006-04-01'";
296:
297: l = SqlUtil.getTables(sql, true);
298: assertEquals(5, l.size());
299: assertEquals("dbo.tblcreditrow cr", (String) l.get(0));
300: assertEquals("bdb_ie.dbo.tblbdbproduct p", (String) l.get(1));
301: assertEquals("tblbidquantity bq", (String) l.get(2));
302: assertEquals("tblcredit c", (String) l.get(3));
303: assertEquals("tblcreditstate cs", (String) l.get(4));
304:
305: l = SqlUtil.getTables(sql, false);
306: assertEquals(5, l.size());
307: assertEquals("dbo.tblcreditrow", (String) l.get(0));
308: assertEquals("bdb_ie.dbo.tblbdbproduct", (String) l.get(1));
309: assertEquals("tblbidquantity", (String) l.get(2));
310: assertEquals("tblcredit", (String) l.get(3));
311: assertEquals("tblcreditstate", (String) l.get(4));
312:
313: sql = "SELECT c.cntry_name as country, \n"
314: + "case \n"
315: + " when r.ref_name is null then p.plr_name \n"
316: + " else r.ref_name \n"
317: + "end as name, \n"
318: + "case \n"
319: + " when r.ref_name is null then 'PLAYER' \n"
320: + " else 'REF' \n"
321: + "end as type \n"
322: + "from country c right outer join referee r on (c.) \n"
323: + " right outer join \n"
324: + "where c.cntry_id = p.cntry_id (+) \n"
325: + "and c.cntry_id = r.cntry_id (+)";
326: l = SqlUtil.getTables(sql, false);
327: assertEquals(2, l.size());
328:
329: sql = "SELECT DISTINCT CONVERT(VARCHAR(50),an.oid) AS an_oid, \n"
330: + " an.cid AS an_cid, \n"
331: + " CONVERT(VARCHAR(50),an.anrede) AS an_anrede, \n"
332: + " an.titel AS an_titel, \n"
333: + " an.akadgrad AS an_grad, \n"
334: + " an.vorname AS an_vorname, \n"
335: + " an.nachname AS an_nachname, \n"
336: + " an.nummer AS an_nummer, \n"
337: + " an.gebdatum AS an_gdat, \n"
338: + " an_adr.ort AS an_adr_ort, \n"
339: + " an_adr.plz AS an_adr_plz, \n"
340: + " an_adr.strasse AS an_adr_str, \n"
341: + " CONVERT(VARCHAR(50),an_adr.staatoid) AS an_adr_staat, \n"
342: + " CONVERT(VARCHAR(50),an_adr.bland) AS an_adr_land, \n"
343: + " ang.bezeichnung AS ang_bezeichnung, \n"
344: + " CONVERT(VARCHAR(50),ag.oid) AS ag_oid, \n"
345: + " CONVERT(VARCHAR(50),ag.art) AS ag_art, \n"
346: + " ag.name AS ag_name, \n"
347: + " ag.nummer AS ag_nummer, \n"
348: + " ag.gdatum AS ag_gdat, \n"
349: + " CONVERT(VARCHAR(50),ag.rform) AS ag_rechtsform, \n"
350: + " ag_adr.ort AS ag_adr_ort, \n"
351: + " ag_adr.plz AS ag_adr_plz, \n"
352: + " ag_adr.strasse AS ag_adr_str, \n"
353: + " CONVERT(VARCHAR(50),ag_adr.staatoid) AS ag_adr_staat, \n"
354: + " CONVERT(VARCHAR(50),ag_adr.bland) AS ag_adr_land, \n"
355: + " CONVERT(VARCHAR(50),ber.anrede) AS ber_anrede, \n"
356: + " ber.titel AS ber_titel, \n"
357: + " ber.akadgrad AS ber_grad, \n"
358: + " ber.vorname AS ber_vorname, \n"
359: + " ber.nachname AS ber_nachname, \n"
360: + " ber.nummer AS ber_nummer \n"
361: + "FROM (((((((((((accright acc LEFT JOIN \n"
362: + " stuser u_ber ON u_ber.userid = acc.userid AND u_ber.dc = acc.dc) LEFT JOIN \n"
363: + " nperson ber ON u_ber.person_oid = ber.oid AND u_ber.dc = ber.dc) LEFT JOIN \n"
364: + " nperson an ON acc.subject_oid = an.oid AND acc.dc = an.dc) LEFT JOIN \n"
365: + " bavdaten bav ON bav.modeloid = an.oid AND bav.dc = an.dc) LEFT JOIN \n"
366: + " bavangroup ang ON bav.angruppe_oid = ang.oid AND bav.dc = ang.dc) LEFT JOIN \n"
367: + " adresse an_adr ON an_adr.quelleoid = an.oid AND an_adr.dc = an.dc) LEFT OUTER JOIN \n"
368: + " beziehung bez ON bez.zieloid = an.oid AND bez.zielcid = an.cid AND bez.dc = an.dc) LEFT OUTER JOIN \n"
369: + " jperson ag ON ag.oid = bez.quelleoid AND ag.cid = bez.quellecid AND ag.dc = bez.dc) LEFT OUTER JOIN \n"
370: + " bavagdaten bavag ON bavag.modeloid = ag.oid AND bavag.dc = ag.dc) LEFT OUTER JOIN \n"
371: + " adresse ag_adr ON ag_adr.quelleoid = ag.oid AND ag_adr.dc = ag.dc) LEFT JOIN \n"
372: + " accright acc_ag ON acc_ag.subject_oid = ag.oid AND acc_ag.dc = ag.dc) LEFT JOIN \n"
373: + " stuser u_ag ON u_ag.userid = acc_ag.userid AND u_ag.dc = acc_ag.dc \n"
374: + "WHERE ((u_ag.userid = '17564'OR u_ag.bossid IN (SELECT userid \n"
375: + " FROM stuser \n"
376: + " WHERE (userid = '17564'OR bossid = '17564') \n"
377: + " AND deaktiv = '0' \n"
378: + " AND dc = ' ')) \n"
379: + "AND (acc_ag.rolename = 'berater')) AND ('berater'= '' \n"
380: + "OR acc.rolename LIKE 'berater') AND ('CVM02000'= '' \n"
381: + "OR acc.subject_cid = 'CVM02000') AND (bez.bezeichnung = 'B2E5AE00-9050-4401-B8E1-8A3B55B22CA9' \n"
382: + "OR bez.bezeichnung IS NULL) AND ((bavag.anoptok = '1'AND '1'= '1') \n"
383: + "OR ((bavag.anoptok = '0'OR bavag.anoptok IS NULL) AND '1'= '1')) AND an.dc = ' 'AND ber.nummer = '65346' \n"
384: + "ORDER BY an.nachname,an.vorname,an.nummer";
385:
386: l = SqlUtil.getTables(sql, true);
387: assertEquals(13, l.size());
388:
389: sql = "select avg(km_pro_jahr) from ( \n"
390: + "select min(f.km), max(f.km), max(f.km) - min(f.km) as km_pro_jahr, extract(year from e.exp_date) \n"
391: + "from fuel f, expense e \n"
392: + "where f.exp_id = e.exp_id \n"
393: + "group by extract(year from e.exp_date) \n" + ")";
394:
395: l = SqlUtil.getTables(sql, true);
396: assertEquals(0, l.size());
397:
398: sql = "select avg(km_pro_jahr) from ( \n"
399: + "select min(f.km), max(f.km), max(f.km) - min(f.km) as km_pro_jahr, extract(year from e.exp_date) \n"
400: + "from fuel f, expense e \n"
401: + "where f.exp_id = e.exp_id \n"
402: + "group by extract(year from e.exp_date) \n"
403: + ") t, table2";
404:
405: l = SqlUtil.getTables(sql, true);
406: assertEquals(2, l.size());
407: assertEquals("table2", l.get(1));
408:
409: // Make sure the getTables() is case preserving
410: sql = "select * from MyTable";
411: l = SqlUtil.getTables(sql, true);
412: assertEquals(1, l.size());
413: assertEquals("MyTable", l.get(0));
414:
415: // Make sure the getTables() is case preserving
416: sql = "select * from table1 as t1, table2";
417: l = SqlUtil.getTables(sql, true);
418: assertEquals(2, l.size());
419: assertEquals("table1 AS t1", l.get(0));
420: assertEquals("table2", l.get(1));
421: }
422:
423: public static void testDataTypeNames() {
424: try {
425: // System.out.println("Checking if all types defined by java.sql.Types are covered by getTypeName()...");
426: // System.out.println(System.getProperty("java.version"));
427: Field fields[] = java.sql.Types.class.getDeclaredFields();
428: boolean missing = false;
429: for (int i = 0; i < fields.length; i++) {
430: int type = fields[i].getInt(null);
431: if (SqlUtil.getTypeName(type).equals("UNKNOWN")) {
432: System.out.println("Type " + fields[i].getName()
433: + " not included in getTypeName()!");
434: missing = true;
435: }
436: }
437: assertFalse("Not all types mapped!", missing);
438: } catch (Throwable th) {
439: th.printStackTrace();
440: fail(th.getMessage());
441: }
442: }
443:
444: }
|