001: /*
002: * StatementContextTest.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.gui.completion;
013:
014: import java.sql.SQLException;
015: import java.sql.Statement;
016: import java.util.List;
017: import junit.framework.TestCase;
018: import workbench.TestUtil;
019: import workbench.db.ColumnIdentifier;
020: import workbench.db.ConnectionMgr;
021: import workbench.db.TableIdentifier;
022: import workbench.db.WbConnection;
023: import workbench.util.SqlUtil;
024:
025: /**
026: *
027: * @author thomas
028: */
029: public class StatementContextTest extends TestCase {
030: private TestUtil util;
031: private WbConnection con;
032:
033: public StatementContextTest(String testName) {
034: super (testName);
035: }
036:
037: protected void setUp() throws Exception {
038: super .setUp();
039: util = new TestUtil("InsertAnalyzerTest");
040: con = util.getConnection("completion_test");
041: prepareDatabase(con);
042: }
043:
044: protected void tearDown() throws Exception {
045: util.emptyBaseDirectory();
046: ConnectionMgr.getInstance().disconnectAll();
047: super .tearDown();
048: }
049:
050: public void testSubSelect() {
051: try {
052: StatementContext context = new StatementContext(con,
053: "select * from one where x in (select from two)",
054: 36);
055: BaseAnalyzer analyzer = context.getAnalyzer();
056: assertTrue(analyzer instanceof SelectAnalyzer);
057: List columns = analyzer.getData();
058: assertNotNull(columns);
059: assertEquals(3, columns.size());
060: Object o = columns.get(1);
061: assertTrue(o instanceof ColumnIdentifier);
062: ColumnIdentifier t = (ColumnIdentifier) o;
063: assertEquals("id2", t.getColumnName().toLowerCase());
064: } catch (Exception e) {
065: e.printStackTrace();
066: fail(e.getMessage());
067: }
068: }
069:
070: public void testCombinedSubSelect() {
071: try {
072: String sql = "select * from one o where x in (select id2 from two where o. )";
073: int pos = sql.indexOf("o.") + 2;
074: StatementContext context = new StatementContext(con, sql,
075: pos);
076: BaseAnalyzer analyzer = context.getAnalyzer();
077: assertTrue(analyzer instanceof SelectAnalyzer);
078: TableIdentifier tbl = analyzer.getTableForColumnList();
079: assertEquals("one", tbl.getTableName().toLowerCase());
080: } catch (Exception e) {
081: e.printStackTrace();
082: fail(e.getMessage());
083: }
084: }
085:
086: public void testUpdateSubSelect() {
087: try {
088: String sql = "update one set firstname = 'xx' where id1 in (select id2 from two where one. )";
089: int pos = sql.indexOf("one.") + 4;
090: StatementContext context = new StatementContext(con, sql,
091: pos);
092: BaseAnalyzer analyzer = context.getAnalyzer();
093: assertTrue(analyzer instanceof SelectAnalyzer);
094: TableIdentifier tbl = analyzer.getTableForColumnList();
095: assertEquals("one", tbl.getTableName().toLowerCase());
096: } catch (Exception e) {
097: e.printStackTrace();
098: fail(e.getMessage());
099: }
100: }
101:
102: public void testDeleteSubSelect() {
103: try {
104: String sql = "delete from one where id1 in (select id2 from two where one. )";
105: int pos = sql.indexOf("one.") + 4;
106: StatementContext context = new StatementContext(con, sql,
107: pos);
108: BaseAnalyzer analyzer = context.getAnalyzer();
109: assertTrue(analyzer instanceof SelectAnalyzer);
110: TableIdentifier tbl = analyzer.getTableForColumnList();
111: assertEquals("one", tbl.getTableName().toLowerCase());
112: } catch (Exception e) {
113: e.printStackTrace();
114: fail(e.getMessage());
115: }
116: }
117:
118: public void testSelectColumnList() {
119: try {
120: StatementContext context = new StatementContext(con,
121: "select from one", 7);
122: BaseAnalyzer analyzer = context.getAnalyzer();
123: assertTrue(analyzer instanceof SelectAnalyzer);
124: List objects = analyzer.getData();
125: assertNotNull(objects);
126: assertEquals(4, objects.size());
127: Object o = objects.get(1);
128: assertTrue(o instanceof ColumnIdentifier);
129: ColumnIdentifier c = (ColumnIdentifier) o;
130: assertEquals("firstname", c.getColumnName().toLowerCase());
131:
132: context = new StatementContext(con,
133: "select * from one where ", 24);
134: analyzer = context.getAnalyzer();
135: assertTrue(analyzer instanceof SelectAnalyzer);
136: objects = analyzer.getData();
137: assertNotNull(objects);
138: assertEquals(3, objects.size());
139: o = objects.get(0);
140: assertTrue(o instanceof ColumnIdentifier);
141: c = (ColumnIdentifier) o;
142: assertEquals("firstname", c.getColumnName().toLowerCase());
143:
144: } catch (Exception e) {
145: e.printStackTrace();
146: fail(e.getMessage());
147: }
148: }
149:
150: public void testSelectTableList() {
151:
152: try {
153: StatementContext context = new StatementContext(con,
154: "select * from ", 14);
155: BaseAnalyzer analyzer = context.getAnalyzer();
156: assertTrue(analyzer instanceof SelectAnalyzer);
157: List objects = analyzer.getData();
158: assertNotNull(objects);
159: assertEquals(3, objects.size());
160: Object o = objects.get(0);
161: assertTrue(o instanceof TableIdentifier);
162: TableIdentifier t = (TableIdentifier) o;
163: assertEquals("one", t.getTableName().toLowerCase());
164: } catch (Exception e) {
165: e.printStackTrace();
166: fail(e.getMessage());
167: }
168: }
169:
170: public void testDeleteTableList() {
171: try {
172: StatementContext context = new StatementContext(con,
173: "delete from where ", 12);
174: BaseAnalyzer analyzer = context.getAnalyzer();
175: assertTrue(analyzer instanceof DeleteAnalyzer);
176: List objects = analyzer.getData();
177: assertNotNull(objects);
178: assertEquals(3, objects.size());
179: Object o = objects.get(0);
180: assertTrue(o instanceof TableIdentifier);
181: TableIdentifier t = (TableIdentifier) o;
182: assertEquals("one", t.getTableName().toLowerCase());
183: } catch (Exception e) {
184: e.printStackTrace();
185: fail(e.getMessage());
186: }
187: }
188:
189: public void createView() {
190: try {
191: StatementContext context = new StatementContext(con,
192: "create view v_test as select * from ", 36);
193: BaseAnalyzer analyzer = context.getAnalyzer();
194: assertTrue(analyzer instanceof SelectAnalyzer);
195: List objects = analyzer.getData();
196: assertNotNull(objects);
197: assertEquals(3, objects.size());
198: Object o = objects.get(0);
199: assertTrue(o instanceof TableIdentifier);
200: TableIdentifier t = (TableIdentifier) o;
201: assertEquals("one", t.getTableName().toLowerCase());
202:
203: context = new StatementContext(con,
204: "reate or replace view v_test as select * from ",
205: 48);
206: analyzer = context.getAnalyzer();
207: assertTrue(analyzer instanceof SelectAnalyzer);
208: objects = analyzer.getData();
209: assertNotNull(objects);
210: assertEquals(3, objects.size());
211: o = objects.get(0);
212: assertTrue(o instanceof TableIdentifier);
213: t = (TableIdentifier) o;
214: assertEquals("one", t.getTableName().toLowerCase());
215: } catch (Exception e) {
216: e.printStackTrace();
217: fail(e.getMessage());
218: }
219: }
220:
221: public void testDeleteColumnList() {
222: try {
223: StatementContext context = new StatementContext(con,
224: "delete from two where ", 22);
225: BaseAnalyzer analyzer = context.getAnalyzer();
226: assertTrue(analyzer instanceof DeleteAnalyzer);
227: List objects = analyzer.getData();
228: assertNotNull(objects);
229: assertEquals(2, objects.size());
230: Object o = objects.get(0);
231: assertTrue(o instanceof ColumnIdentifier);
232: ColumnIdentifier t = (ColumnIdentifier) o;
233: assertEquals("id2", t.getColumnName().toLowerCase());
234: } catch (Exception e) {
235: e.printStackTrace();
236: fail(e.getMessage());
237: }
238: }
239:
240: private void prepareDatabase(WbConnection con) throws SQLException {
241: Statement stmt = null;
242: try {
243: stmt = con.createStatement();
244: stmt
245: .executeUpdate("create table one (id1 integer, firstname varchar(100), lastname varchar(100))");
246: stmt
247: .executeUpdate("create table two (id2 integer, some_data varchar(100))");
248: stmt
249: .executeUpdate("create table three (id3 integer, more_data varchar(100))");
250: } finally {
251: SqlUtil.closeStatement(stmt);
252: }
253: }
254:
255: }
|