0001: /*
0002: * $Id: AbstractDatabaseTest.java,v 1.7 2005/12/20 18:32:44 ahimanikya Exp $
0003: * =======================================================================
0004: * Copyright (c) 2002 Axion Development Team. All rights reserved.
0005: *
0006: * Redistribution and use in source and binary forms, with or without
0007: * modification, are permitted provided that the following conditions
0008: * are met:
0009: *
0010: * 1. Redistributions of source code must retain the above
0011: * copyright notice, this list of conditions and the following
0012: * disclaimer.
0013: *
0014: * 2. Redistributions in binary form must reproduce the above copyright
0015: * notice, this list of conditions and the following disclaimer in
0016: * the documentation and/or other materials provided with the
0017: * distribution.
0018: *
0019: * 3. The names "Tigris", "Axion", nor the names of its contributors may
0020: * not be used to endorse or promote products derived from this
0021: * software without specific prior written permission.
0022: *
0023: * 4. Products derived from this software may not be called "Axion", nor
0024: * may "Tigris" or "Axion" appear in their names without specific prior
0025: * written permission.
0026: *
0027: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
0028: * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
0029: * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
0030: * PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
0031: * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
0032: * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
0033: * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
0034: * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
0035: * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
0036: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
0037: * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
0038: * =======================================================================
0039: */
0040:
0041: package org.axiondb;
0042:
0043: import java.io.Reader;
0044: import java.math.BigInteger;
0045: import java.sql.Clob;
0046: import java.sql.ResultSet;
0047: import java.util.ArrayList;
0048: import java.util.List;
0049:
0050: import org.axiondb.engine.commands.AxionQueryContext;
0051: import org.axiondb.engine.commands.CreateTableCommand;
0052: import org.axiondb.engine.commands.DeleteCommand;
0053: import org.axiondb.engine.commands.InsertCommand;
0054: import org.axiondb.engine.commands.SelectCommand;
0055: import org.axiondb.engine.commands.UpdateCommand;
0056: import org.axiondb.event.BaseDatabaseModificationListener;
0057: import org.axiondb.event.DatabaseModifiedEvent;
0058: import org.axiondb.functions.AndFunction;
0059: import org.axiondb.functions.EqualFunction;
0060: import org.axiondb.functions.GreaterThanFunction;
0061: import org.axiondb.functions.LessThanFunction;
0062: import org.axiondb.types.CharacterVaryingType;
0063: import org.axiondb.types.IntegerType;
0064:
0065: /**
0066: * @version $Revision: 1.7 $ $Date: 2005/12/20 18:32:44 $
0067: * @author Chuck Burdick
0068: * @author Rodney Waldhoff
0069: * @author Doug Sale
0070: * @author Dave Pekarek Krohn
0071: * @author Ahimanikya Satapathy
0072: */
0073: public abstract class AbstractDatabaseTest extends AbstractDbdirTest {
0074:
0075: //------------------------------------------------------------ Conventional
0076:
0077: public AbstractDatabaseTest(String testName) {
0078: super (testName);
0079: }
0080:
0081: //--------------------------------------------------------------- Lifecycle
0082:
0083: private static final String LOBDIR_PROP = "axiondb.lobdir";
0084: private org.axiondb.Database _db = null;
0085: private String _origLobDirVal = null;
0086: protected String _dbName = "dbfoo";
0087:
0088: public void setUp() throws Exception {
0089: super .setUp();
0090: _origLobDirVal = System.getProperty(LOBDIR_PROP);
0091: System.setProperty(LOBDIR_PROP, "testdb");
0092: _db = createDatabase(_dbName);
0093: }
0094:
0095: public void tearDown() throws Exception {
0096: _db.shutdown();
0097: _db = null;
0098: if (_origLobDirVal != null) {
0099: System.getProperties().remove(LOBDIR_PROP);
0100: }
0101: super .tearDown();
0102: }
0103:
0104: //-------------------------------------------------------------------- Util
0105:
0106: protected abstract Database createDatabase(String name)
0107: throws Exception;
0108:
0109: protected Database getDb() {
0110: return _db;
0111: }
0112:
0113: private void createTableFoo() throws Exception {
0114: CreateTableCommand cmd = new CreateTableCommand("FOO");
0115: cmd.addColumn("ID", "integer");
0116: cmd.addColumn("STR", "varchar", "10");
0117: cmd.execute(_db);
0118: }
0119:
0120: private void populateTableFoo() throws Exception {
0121: List columns = new ArrayList(2);
0122: columns.add(new ColumnIdentifier("ID"));
0123: columns.add(new ColumnIdentifier("STR"));
0124: for (int i = 0; i < 10; i++) {
0125: List values = new ArrayList(2);
0126: values.add(new Literal(new Integer(i), new IntegerType()));
0127: values.add(new Literal(String.valueOf(i),
0128: new CharacterVaryingType(10)));
0129: InsertCommand cmd = new InsertCommand(new TableIdentifier(
0130: "FOO"), columns, values);
0131: cmd.executeUpdate(_db);
0132: }
0133: }
0134:
0135: private void createTableBar() throws Exception {
0136: CreateTableCommand cmd = new CreateTableCommand("BAR");
0137: cmd.addColumn("BARID", "integer");
0138: cmd.addColumn("BARSTR", "varchar", "10");
0139: cmd.execute(_db);
0140: }
0141:
0142: private void populateTableBar() throws Exception {
0143: List columns = new ArrayList(2);
0144: columns.add(new ColumnIdentifier("BARID"));
0145: columns.add(new ColumnIdentifier("BARSTR"));
0146: for (int i = 0; i < 10; i++) {
0147: List values = new ArrayList(2);
0148: values.add(new Literal(new Integer(i), new IntegerType()));
0149: values.add(new Literal(String.valueOf(i),
0150: new CharacterVaryingType(10)));
0151: InsertCommand cmd = new InsertCommand(new TableIdentifier(
0152: "BAR"), columns, values);
0153: cmd.executeUpdate(_db);
0154: }
0155: }
0156:
0157: protected class TestListener extends
0158: BaseDatabaseModificationListener {
0159: private DatabaseModifiedEvent _event = null;
0160:
0161: public void tableAdded(DatabaseModifiedEvent e) {
0162: _event = e;
0163: }
0164:
0165: public void tableDropped(DatabaseModifiedEvent e) {
0166: _event = e;
0167: }
0168:
0169: public DatabaseModifiedEvent getEvent() {
0170: return _event;
0171: }
0172: }
0173:
0174: protected boolean findStringInTable(String value, Table t,
0175: String colName) throws Exception {
0176: boolean found = false;
0177: RowIterator it = t.getRowIterator(true);
0178: int colIdx = t.getColumnIndex(colName);
0179: while (!found && it.hasNext()) {
0180: Row cur = it.next();
0181: Object rowVal = cur.get(colIdx);
0182: DataType type = t.getColumn(colIdx).getDataType();
0183: String sVal = type.toString(rowVal);
0184: found = sVal.equals(value);
0185: }
0186: return found;
0187: }
0188:
0189: //------------------------------------------------------------------- Tests
0190:
0191: public void testGetName() throws Exception {
0192: assertEquals(_dbName, _db.getName());
0193: }
0194:
0195: public void testLoadProperties() throws Exception {
0196: assertNotNull("Should find type", _db.getDataType("integer"));
0197: Table types = _db.getTable("AXION_TYPES");
0198: assertNotNull("Should find types table", types);
0199: boolean found = findStringInTable("INTEGER", types, "TYPE_NAME");
0200: assertTrue("Should find type in system table", found);
0201: }
0202:
0203: public void testCreateTableGetTable() throws Exception {
0204: CreateTableCommand cmd = new CreateTableCommand("FOO");
0205: cmd.addColumn("ID", "INTEGER");
0206:
0207: TestListener listener = new TestListener();
0208: int baseListenCount = _db.getDatabaseModificationListeners()
0209: .size();
0210: _db.addDatabaseModificationListener(listener);
0211: assertEquals("Should find another listener",
0212: baseListenCount + 1, _db
0213: .getDatabaseModificationListeners().size());
0214:
0215: assertNull("Should not indicate table added", listener
0216: .getEvent());
0217: cmd.execute(_db);
0218: assertEquals("Should indicate table added", "FOO", listener
0219: .getEvent().getTable().getName());
0220: assertNotNull("Should get a table back", _db.getTable("FOO"));
0221: assertNotNull("Should get a table back", _db.getTable("FOO"));
0222:
0223: Table sysTable = getDb().getTable("AXION_TABLES");
0224: assertNotNull("Should have system table of tables", sysTable);
0225: boolean found = findStringInTable("FOO", sysTable, "TABLE_NAME");
0226: assertTrue("Should find entry for table in system table", found);
0227:
0228: Table sysCol = getDb().getTable("AXION_COLUMNS");
0229: assertNotNull("Should have system table of columns", sysCol);
0230: found = findStringInTable("ID", sysCol, "COLUMN_NAME");
0231: assertTrue("Should find entry for column in system table",
0232: found);
0233:
0234: Table foo = getDb().getTable("FOO");
0235: foo.addColumn(new Column("text", new CharacterVaryingType(10)));
0236: found = findStringInTable("TEXT", sysCol, "COLUMN_NAME");
0237: assertTrue("Should find entry for new column in system table",
0238: found);
0239: }
0240:
0241: public void testDropTable() throws Exception {
0242:
0243: CreateTableCommand cmd = new CreateTableCommand("FOO1");
0244: cmd.addColumn("ID", "integer");
0245: cmd.execute(_db);
0246:
0247: assertNotNull("Should get a table back", _db.getTable("FOO1"));
0248: TestListener listener = new TestListener();
0249: _db.addDatabaseModificationListener(listener);
0250: assertNull("Should not indicate table dropped", listener
0251: .getEvent());
0252: int before = getDb().getTable("AXION_TABLES").getRowCount();
0253: _db.dropTable("FOO1");
0254: assertEquals("Should indicate table dropped", "FOO1", listener
0255: .getEvent().getTable().getName());
0256: assertNull(_db.getTable("foo1"));
0257: int after = getDb().getTable("AXION_TABLES").getRowCount();
0258: assertEquals("Should have one fewer row", before - 1, after);
0259: }
0260:
0261: public void testDropTable2() throws Exception {
0262: CreateTableCommand cmd = new CreateTableCommand("FOO2");
0263: cmd.addColumn("id", "integer");
0264: cmd.execute(_db);
0265:
0266: assertNotNull("Should get a table back", _db.getTable("FOO2"));
0267: _db.dropTable("FOO2");
0268: assertNull(_db.getTable("FOO2"));
0269: }
0270:
0271: public void testCantCreateDuplicateTable() throws Exception {
0272: CreateTableCommand cmd = new CreateTableCommand("FOO");
0273: cmd.addColumn("ID", "integer");
0274: cmd.execute(_db);
0275: assertNotNull("Should get a table back", _db.getTable("FOO"));
0276: try {
0277: cmd.execute(_db);
0278: fail("Expected AxionException");
0279: } catch (AxionException e) {
0280: // expected
0281: }
0282: }
0283:
0284: public void testGetBadName() throws Exception {
0285: assertNull(_db.getTable("BOGUS"));
0286: }
0287:
0288: public void testSimpleInsert() throws Exception {
0289: createTableFoo();
0290: Table foo = _db.getTable("FOO");
0291: List columns = new ArrayList(2);
0292: columns.add(new ColumnIdentifier("ID"));
0293: columns.add(new ColumnIdentifier("STR"));
0294: for (int i = 0; i < 10; i++) {
0295: assertEquals(i, foo.getRowCount());
0296: List values = new ArrayList(2);
0297: values.add(new Literal(new Integer(i), new IntegerType()));
0298: values.add(new Literal(String.valueOf(i),
0299: new CharacterVaryingType(10)));
0300: InsertCommand cmd = new InsertCommand(new TableIdentifier(
0301: "FOO"), columns, values);
0302: assertEquals(1, cmd.executeUpdate(_db));
0303: assertEquals(i + 1, foo.getRowCount());
0304: }
0305: }
0306:
0307: public void testInsertWithNullColumn() throws Exception {
0308: createTableFoo();
0309: Table foo = _db.getTable("FOO");
0310: List columns = new ArrayList(2);
0311: columns.add(new ColumnIdentifier("ID"));
0312: for (int i = 0; i < 10; i++) {
0313: assertEquals(i, foo.getRowCount());
0314: List values = new ArrayList(2);
0315: values.add(new Literal(new Integer(i), new IntegerType()));
0316: InsertCommand cmd = new InsertCommand(new TableIdentifier(
0317: "FOO"), columns, values);
0318: assertEquals(1, cmd.executeUpdate(_db));
0319: assertEquals(i + 1, foo.getRowCount());
0320: }
0321: }
0322:
0323: public void testUpdateNone() throws Exception {
0324: createTableFoo();
0325: populateTableFoo();
0326:
0327: UpdateCommand cmd = new UpdateCommand();
0328: cmd.setTable(new TableIdentifier("FOO"));
0329: cmd.addColumn(new ColumnIdentifier("STR"));
0330: cmd.addValue(new Literal("GREATER_THAN_11",
0331: new CharacterVaryingType(10)));
0332: cmd.addColumn(new ColumnIdentifier("ID"));
0333: cmd.addValue(new Literal(new Integer(11), new IntegerType()));
0334:
0335: Selectable where = makeLeafWhereNode(new GreaterThanFunction(),
0336: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0337: new Literal(new Integer(11), new IntegerType()));
0338: cmd.setWhere(where);
0339:
0340: assertEquals(0, cmd.executeUpdate(_db));
0341: }
0342:
0343: public void testUpdateOne() throws Exception {
0344: createTableFoo();
0345: populateTableFoo();
0346:
0347: UpdateCommand cmd = new UpdateCommand();
0348: cmd.setTable(new TableIdentifier("FOO"));
0349: cmd.addColumn(new ColumnIdentifier("STR"));
0350: cmd.addValue(new Literal("VII", new CharacterVaryingType(10)));
0351: Selectable where = makeLeafWhereNode(new EqualFunction(),
0352: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0353: new Literal(new Integer(7), new IntegerType()));
0354: cmd.setWhere(where);
0355:
0356: assertEquals(1, cmd.executeUpdate(_db));
0357:
0358: AxionQueryContext ctx = new AxionQueryContext();
0359:
0360: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0361: "STR"));
0362: ctx.addFrom(new TableIdentifier("FOO"));
0363: ctx.setWhere(where);
0364: SelectCommand select = new SelectCommand(ctx);
0365: ResultSet rset = select.executeQuery(_db);
0366: assertNotNull(rset);
0367: assertTrue(rset.next());
0368: assertEquals("VII", rset.getString(1));
0369: assertTrue(!rset.wasNull());
0370: assertTrue(!rset.next());
0371: }
0372:
0373: public void testUpdateSome() throws Exception {
0374: createTableFoo();
0375: populateTableFoo();
0376:
0377: UpdateCommand cmd = new UpdateCommand();
0378: cmd.setTable(new TableIdentifier("FOO"));
0379: cmd.addColumn(new ColumnIdentifier("STR"));
0380: cmd.addValue(new Literal("X", new CharacterVaryingType(10)));
0381: Selectable where = makeLeafWhereNode(new LessThanFunction(),
0382: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0383: new Literal(new Integer(5), new IntegerType()));
0384: cmd.setWhere(where);
0385:
0386: assertEquals(5, cmd.executeUpdate(_db));
0387:
0388: AxionQueryContext ctx = new AxionQueryContext();
0389: SelectCommand select = new SelectCommand(ctx);
0390: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0391: "STR"));
0392: ctx.addFrom(new TableIdentifier("FOO"));
0393: ResultSet rset = select.executeQuery(_db);
0394: assertNotNull(rset);
0395: for (int i = 0; i < 10; i++) {
0396: assertTrue(rset.next());
0397: if (i < 5) {
0398: assertEquals("X", rset.getString(1));
0399: } else {
0400: assertEquals(String.valueOf(i), rset.getString(1));
0401: }
0402: assertTrue(!rset.wasNull());
0403: }
0404: assertTrue(!rset.next());
0405: }
0406:
0407: public void testUpdateAll() throws Exception {
0408: createTableFoo();
0409: populateTableFoo();
0410:
0411: UpdateCommand cmd = new UpdateCommand();
0412: cmd.setTable(new TableIdentifier("FOO"));
0413: cmd.addColumn(new ColumnIdentifier("STR"));
0414: cmd.addValue(new Literal("X", new CharacterVaryingType(10)));
0415:
0416: assertEquals(10, cmd.executeUpdate(_db));
0417:
0418: AxionQueryContext ctx = new AxionQueryContext();
0419: SelectCommand select = new SelectCommand(ctx);
0420: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0421: "STR"));
0422: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0423: "ID"));
0424: ctx.addFrom(new TableIdentifier("FOO"));
0425: ResultSet rset = select.executeQuery(_db);
0426: assertNotNull(rset);
0427: for (int i = 0; i < 10; i++) {
0428: assertTrue(rset.next());
0429: assertEquals("X", rset.getString(1));
0430: assertTrue(!rset.wasNull());
0431: assertEquals(i, rset.getInt(2));
0432: assertTrue(!rset.wasNull());
0433: }
0434: assertTrue(!rset.next());
0435: }
0436:
0437: public void testDeleteNone() throws Exception {
0438: createTableFoo();
0439: populateTableFoo();
0440:
0441: Selectable where = makeLeafWhereNode(new GreaterThanFunction(),
0442: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0443: new Literal(new Integer(11), new IntegerType()));
0444:
0445: DeleteCommand cmd = new DeleteCommand("FOO", where);
0446: assertEquals(0, cmd.executeUpdate(_db));
0447:
0448: AxionQueryContext ctx = new AxionQueryContext();
0449: SelectCommand select = new SelectCommand(ctx);
0450: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0451: "STR"));
0452: ctx.addFrom(new TableIdentifier("FOO"));
0453: ResultSet rset = select.executeQuery(_db);
0454: assertNotNull(rset);
0455: for (int i = 0; i < 10; i++) {
0456: assertTrue(rset.next());
0457: assertEquals(String.valueOf(i), rset.getString(1));
0458: assertTrue(!rset.wasNull());
0459: }
0460: assertTrue(!rset.next());
0461: }
0462:
0463: public void testDeleteOne() throws Exception {
0464: createTableFoo();
0465: populateTableFoo();
0466:
0467: Selectable where = makeLeafWhereNode(new EqualFunction(),
0468: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0469: new Literal(new Integer(7), new IntegerType()));
0470:
0471: DeleteCommand cmd = new DeleteCommand("FOO", where);
0472: assertEquals(1, cmd.executeUpdate(_db));
0473:
0474: AxionQueryContext ctx = new AxionQueryContext();
0475: SelectCommand select = new SelectCommand(ctx);
0476: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0477: "STR"));
0478: ctx.addFrom(new TableIdentifier("FOO"));
0479: ResultSet rset = select.executeQuery(_db);
0480: assertNotNull(rset);
0481: for (int i = 0; i < 10; i++) {
0482: if (i != 7) {
0483: assertTrue(rset.next());
0484: assertEquals(String.valueOf(i), rset.getString(1));
0485: assertTrue(!rset.wasNull());
0486: }
0487: }
0488: assertTrue(!rset.next());
0489: }
0490:
0491: public void testDeleteSome() throws Exception {
0492: createTableFoo();
0493: populateTableFoo();
0494:
0495: Selectable where = makeLeafWhereNode(new LessThanFunction(),
0496: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0497: new Literal(new Integer(5), new IntegerType()));
0498:
0499: DeleteCommand cmd = new DeleteCommand("FOO", where);
0500: assertEquals(5, cmd.executeUpdate(_db));
0501:
0502: AxionQueryContext ctx = new AxionQueryContext();
0503: SelectCommand select = new SelectCommand(ctx);
0504: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0505: "STR"));
0506: ctx.addFrom(new TableIdentifier("FOO"));
0507: ResultSet rset = select.executeQuery(_db);
0508: assertNotNull(rset);
0509: for (int i = 5; i < 10; i++) {
0510: assertTrue(rset.next());
0511: assertEquals(String.valueOf(i), rset.getString(1));
0512: assertTrue(!rset.wasNull());
0513: }
0514: assertTrue(!rset.next());
0515: }
0516:
0517: public void testSelectOneColumnFromOneTable() throws Exception {
0518: createTableFoo();
0519: populateTableFoo();
0520:
0521: // execute a select upon it
0522: AxionQueryContext ctx = new AxionQueryContext();
0523: SelectCommand select = new SelectCommand(ctx);
0524: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0525: "STR"));
0526: ctx.addFrom(new TableIdentifier("FOO"));
0527: ResultSet rset = select.executeQuery(_db);
0528: assertNotNull(rset);
0529: for (int i = 0; i < 10; i++) {
0530: assertTrue(rset.next());
0531: assertEquals(String.valueOf(i), rset.getString(1));
0532: assertTrue(!rset.wasNull());
0533: assertEquals(String.valueOf(i), rset.getString("STR"));
0534: assertTrue(!rset.wasNull());
0535: }
0536: assertTrue(!rset.next());
0537: }
0538:
0539: public void testSelectClobReader() throws Exception {
0540:
0541: // create a long string
0542: String text = null;
0543: {
0544: StringBuffer buf = new StringBuffer();
0545: for (int i = 0; i < 10; i++) {
0546: buf
0547: .append("The quick brown fox jumped over the lazy dogs.");
0548: }
0549: text = buf.toString();
0550: }
0551:
0552: // create the table
0553: {
0554: CreateTableCommand cmd = new CreateTableCommand("FOO");
0555: cmd.addColumn("ID", "integer");
0556: cmd.addColumn("CLOBSTR", "varchar", text.length() + "");
0557: cmd.execute(_db);
0558: }
0559:
0560: // insert into the table
0561: {
0562: List columns = new ArrayList(2);
0563: columns.add(new ColumnIdentifier("ID"));
0564: columns.add(new ColumnIdentifier("CLOBSTR"));
0565: IntegerType intType = new IntegerType();
0566: CharacterVaryingType varcharType = new CharacterVaryingType(
0567: text.length());
0568: for (int i = 0; i < 10; i++) {
0569: List values = new ArrayList(2);
0570: values.add(new Literal(new Integer(i), intType));
0571: values.add(new Literal(text, varcharType));
0572: InsertCommand cmd = new InsertCommand(
0573: new TableIdentifier("FOO"), columns, values);
0574: assertEquals(1, cmd.executeUpdate(_db));
0575: }
0576: }
0577:
0578: // select from the table, and grab the clobs
0579: AxionQueryContext ctx = new AxionQueryContext();
0580: SelectCommand select = new SelectCommand(ctx);
0581: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0582: "CLOBSTR"));
0583: ctx.addFrom(new TableIdentifier("FOO"));
0584: ResultSet rset = select.executeQuery(_db);
0585: assertNotNull(rset);
0586: for (int i = 0; i < 10; i++) {
0587: assertTrue(rset.next());
0588: Clob clob = rset.getClob(1);
0589: assertTrue(!rset.wasNull());
0590: assertNotNull(clob);
0591: {
0592: Reader in = clob.getCharacterStream();
0593: assertNotNull(in);
0594: StringBuffer buf = new StringBuffer();
0595: for (int c = in.read(); c != -1; c = in.read()) {
0596: buf.append((char) c);
0597: }
0598: assertEquals(text, buf.toString());
0599: }
0600:
0601: clob = rset.getClob("CLOBSTR");
0602: assertTrue(!rset.wasNull());
0603: assertNotNull(clob);
0604: {
0605: Reader in = clob.getCharacterStream();
0606: assertNotNull(in);
0607: StringBuffer buf = new StringBuffer();
0608: for (int c = in.read(); c != -1; c = in.read()) {
0609: buf.append((char) c);
0610: }
0611: assertEquals(text, buf.toString());
0612: }
0613: }
0614: assertTrue(!rset.next());
0615: }
0616:
0617: public void testSelectTwoColumnsFromOneTable() throws Exception {
0618: createTableFoo();
0619: populateTableFoo();
0620:
0621: // execute a select upon it
0622: AxionQueryContext ctx = new AxionQueryContext();
0623: SelectCommand select = new SelectCommand(ctx);
0624: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0625: "STR"));
0626: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0627: "ID"));
0628: ctx.addFrom(new TableIdentifier("FOO"));
0629: ResultSet rset = select.executeQuery(_db);
0630: assertNotNull(rset);
0631: for (int i = 0; i < 10; i++) {
0632: assertTrue(rset.next());
0633: assertEquals(String.valueOf(i), rset.getString(1));
0634: assertTrue(!rset.wasNull());
0635: assertEquals(String.valueOf(i), rset.getString("STR"));
0636: assertTrue(!rset.wasNull());
0637: assertEquals(i, rset.getInt(2));
0638: assertTrue(!rset.wasNull());
0639: assertEquals(i, rset.getInt("ID"));
0640: assertTrue(!rset.wasNull());
0641: }
0642: assertTrue(!rset.next());
0643: }
0644:
0645: public void testSelectAllColumnsFromOneTable() throws Exception {
0646: createTableFoo();
0647: populateTableFoo();
0648:
0649: // execute a select upon it
0650: AxionQueryContext ctx = new AxionQueryContext();
0651: SelectCommand select = new SelectCommand(ctx);
0652: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0653: "*"));
0654: ctx.addFrom(new TableIdentifier("FOO"));
0655: ResultSet rset = select.executeQuery(_db);
0656: assertNotNull(rset);
0657: for (int i = 0; i < 10; i++) {
0658: assertTrue(rset.next());
0659: assertEquals(i, rset.getInt(1));
0660: assertTrue(!rset.wasNull());
0661: assertEquals(i, rset.getInt("ID"));
0662: assertTrue(!rset.wasNull());
0663: assertEquals(String.valueOf(i), rset.getString(2));
0664: assertTrue(!rset.wasNull());
0665: assertEquals(String.valueOf(i), rset.getString("STR"));
0666: assertTrue(!rset.wasNull());
0667: }
0668: assertTrue(!rset.next());
0669: }
0670:
0671: public void testSelectSomeFromTwoTables() throws Exception {
0672: createTableFoo();
0673: populateTableFoo();
0674: createTableBar();
0675: populateTableBar();
0676:
0677: // execute a select upon it
0678: AxionQueryContext ctx = new AxionQueryContext();
0679: SelectCommand select = new SelectCommand(ctx);
0680: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0681: "BARSTR"));
0682: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0683: "ID"));
0684: ctx.addFrom(new TableIdentifier("BAR"));
0685: ctx.addFrom(new TableIdentifier("FOO"));
0686: ResultSet rset = select.executeQuery(_db);
0687: assertNotNull(rset);
0688: for (int i = 0; i < 10; i++) {
0689: for (int j = 0; j < 10; j++) {
0690: assertTrue(rset.next());
0691: assertEquals(String.valueOf(i), rset.getString(1));
0692: assertTrue(!rset.wasNull());
0693: assertEquals(String.valueOf(i), rset
0694: .getString("BARSTR"));
0695: assertTrue(!rset.wasNull());
0696: assertEquals(j, rset.getInt(2));
0697: assertTrue(!rset.wasNull());
0698: assertEquals(j, rset.getInt("ID"));
0699: assertTrue(!rset.wasNull());
0700: }
0701: }
0702: assertTrue(!rset.next());
0703: }
0704:
0705: public void testSelectAllFromTwoTables() throws Exception {
0706: createTableFoo();
0707: populateTableFoo();
0708: createTableBar();
0709: populateTableBar();
0710:
0711: // execute a select upon it
0712: AxionQueryContext ctx = new AxionQueryContext();
0713: SelectCommand select = new SelectCommand(ctx);
0714: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0715: "BARSTR"));
0716: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0717: "ID"));
0718: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0719: "STR"));
0720: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0721: "BARID"));
0722: ctx.addFrom(new TableIdentifier("BAR"));
0723: ctx.addFrom(new TableIdentifier("FOO"));
0724: ResultSet rset = select.executeQuery(_db);
0725: assertNotNull(rset);
0726: for (int i = 0; i < 10; i++) {
0727: for (int j = 0; j < 10; j++) {
0728: assertTrue(rset.next());
0729:
0730: assertEquals(String.valueOf(i), rset.getString(1));
0731: assertTrue(!rset.wasNull());
0732: assertEquals(String.valueOf(i), rset
0733: .getString("BARSTR"));
0734: assertTrue(!rset.wasNull());
0735:
0736: assertEquals(j, rset.getInt(2));
0737: assertTrue(!rset.wasNull());
0738: assertEquals(j, rset.getInt("ID"));
0739: assertTrue(!rset.wasNull());
0740:
0741: assertEquals(String.valueOf(j), rset.getString(3));
0742: assertTrue(!rset.wasNull());
0743: assertEquals(String.valueOf(j), rset.getString("STR"));
0744: assertTrue(!rset.wasNull());
0745:
0746: assertEquals(i, rset.getInt(4));
0747: assertTrue(!rset.wasNull());
0748: assertEquals(i, rset.getInt("BARID"));
0749: assertTrue(!rset.wasNull());
0750:
0751: }
0752: }
0753: assertTrue(!rset.next());
0754: }
0755:
0756: public void testSimpleSelectWithWhere() throws Exception {
0757: createTableFoo();
0758: populateTableFoo();
0759:
0760: // execute a select upon it
0761: AxionQueryContext ctx = new AxionQueryContext();
0762: SelectCommand select = new SelectCommand(ctx);
0763: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0764: "ID"));
0765: ctx.addFrom(new TableIdentifier("FOO"));
0766: Selectable where = makeLeafWhereNode(new LessThanFunction(),
0767: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0768: new Literal(new Integer(5), new IntegerType()));
0769: ctx.setWhere(where);
0770:
0771: ResultSet rset = select.executeQuery(_db);
0772: assertNotNull(rset);
0773:
0774: for (int i = 0; i < 5; i++) {
0775: assertTrue(rset.next());
0776: assertEquals(i, rset.getInt(1));
0777: assertTrue(!rset.wasNull());
0778: assertEquals(i, rset.getInt("ID"));
0779: assertTrue(!rset.wasNull());
0780: }
0781: assertTrue(!rset.next());
0782: }
0783:
0784: public void testSelectWithSimpleJoin() throws Exception {
0785: createTableFoo();
0786: populateTableFoo();
0787: createTableBar();
0788: populateTableBar();
0789:
0790: // execute a select upon it
0791: AxionQueryContext ctx = new AxionQueryContext();
0792: SelectCommand select = new SelectCommand(ctx);
0793: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0794: "BARSTR"));
0795: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0796: "ID"));
0797: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0798: "STR"));
0799: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0800: "BARID"));
0801: ctx.addFrom(new TableIdentifier("FOO"));
0802: ctx.addFrom(new TableIdentifier("BAR"));
0803: Selectable where = makeLeafWhereNode(new EqualFunction(),
0804: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0805: new ColumnIdentifier(new TableIdentifier("BAR"),
0806: "BARID"));
0807: ctx.setWhere(where);
0808:
0809: ResultSet rset = select.executeQuery(_db);
0810: assertNotNull(rset);
0811: for (int i = 0; i < 10; i++) {
0812: assertTrue(rset.next());
0813:
0814: assertEquals(String.valueOf(i), rset.getString(1));
0815: assertTrue(!rset.wasNull());
0816: assertEquals(String.valueOf(i), rset.getString("barstr"));
0817: assertTrue(!rset.wasNull());
0818:
0819: assertEquals(i, rset.getInt(2));
0820: assertTrue(!rset.wasNull());
0821: assertEquals(i, rset.getInt("id"));
0822: assertTrue(!rset.wasNull());
0823:
0824: assertEquals(String.valueOf(i), rset.getString(3));
0825: assertTrue(!rset.wasNull());
0826: assertEquals(String.valueOf(i), rset.getString("str"));
0827: assertTrue(!rset.wasNull());
0828:
0829: assertEquals(i, rset.getInt(4));
0830: assertTrue(!rset.wasNull());
0831: assertEquals(i, rset.getInt("barid"));
0832: assertTrue(!rset.wasNull());
0833: }
0834: assertTrue(!rset.next());
0835: }
0836:
0837: public void testSelectJoinWithMultipartWhere() throws Exception {
0838: createTableFoo();
0839: populateTableFoo();
0840: createTableBar();
0841: populateTableBar();
0842:
0843: // execute a select upon it
0844: AxionQueryContext ctx = new AxionQueryContext();
0845: SelectCommand select = new SelectCommand(ctx);
0846: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0847: "BARSTR"));
0848: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0849: "ID"));
0850: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0851: "STR"));
0852: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("BAR"),
0853: "BARID"));
0854: ctx.addFrom(new TableIdentifier("BAR"));
0855: ctx.addFrom(new TableIdentifier("FOO"));
0856:
0857: AndFunction where = new AndFunction();
0858: where.addArgument(makeLeafWhereNode(new EqualFunction(),
0859: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0860: new ColumnIdentifier(new TableIdentifier("BAR"),
0861: "BARID")));
0862: where.addArgument(makeLeafWhereNode(new LessThanFunction(),
0863: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0864: new Literal(new Integer(5), new IntegerType())));
0865: ctx.setWhere(where);
0866:
0867: ResultSet rset = select.executeQuery(_db);
0868: assertNotNull(rset);
0869: for (int i = 0; i < 5; i++) {
0870: assertTrue(rset.next());
0871:
0872: assertEquals(String.valueOf(i), rset.getString(1));
0873: assertTrue(!rset.wasNull());
0874: assertEquals(String.valueOf(i), rset.getString("barstr"));
0875: assertTrue(!rset.wasNull());
0876:
0877: assertEquals(i, rset.getInt(2));
0878: assertTrue(!rset.wasNull());
0879: assertEquals(i, rset.getInt("id"));
0880: assertTrue(!rset.wasNull());
0881:
0882: assertEquals(String.valueOf(i), rset.getString(3));
0883: assertTrue(!rset.wasNull());
0884: assertEquals(String.valueOf(i), rset.getString("str"));
0885: assertTrue(!rset.wasNull());
0886:
0887: assertEquals(i, rset.getInt(4));
0888: assertTrue(!rset.wasNull());
0889: assertEquals(i, rset.getInt("barid"));
0890: assertTrue(!rset.wasNull());
0891: }
0892: assertTrue(!rset.next());
0893: }
0894:
0895: public void testSelectWithMultipartWhere() throws Exception {
0896: createTableFoo();
0897: populateTableFoo();
0898:
0899: AxionQueryContext ctx = new AxionQueryContext();
0900: SelectCommand select = new SelectCommand(ctx);
0901: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0902: "ID"));
0903: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0904: "STR"));
0905: ctx.addFrom(new TableIdentifier("FOO"));
0906:
0907: AndFunction where = new AndFunction();
0908: where
0909: .addArgument(makeLeafWhereNode(new EqualFunction(),
0910: new ColumnIdentifier(
0911: new TableIdentifier("FOO"), "ID"),
0912: new ColumnIdentifier(
0913: new TableIdentifier("FOO"), "ID")));
0914: where.addArgument(makeLeafWhereNode(new LessThanFunction(),
0915: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0916: new Literal(new Integer(5), new IntegerType())));
0917: ctx.setWhere(where);
0918:
0919: ResultSet rset = select.executeQuery(_db);
0920: assertNotNull(rset);
0921: for (int i = 0; i < 5; i++) {
0922: assertTrue(rset.next());
0923:
0924: assertEquals(i, rset.getInt(1));
0925: assertTrue(!rset.wasNull());
0926: assertEquals(i, rset.getInt("id"));
0927: assertTrue(!rset.wasNull());
0928:
0929: assertEquals(String.valueOf(i), rset.getString(2));
0930: assertTrue(!rset.wasNull());
0931: assertEquals(String.valueOf(i), rset.getString("str"));
0932: assertTrue(!rset.wasNull());
0933:
0934: }
0935: assertTrue(!rset.next());
0936: }
0937:
0938: public void testSelectWithWhereOnAnotherColumn() throws Exception {
0939: createTableFoo();
0940: populateTableFoo();
0941:
0942: // execute a select upon it
0943: AxionQueryContext ctx = new AxionQueryContext();
0944: SelectCommand select = new SelectCommand(ctx);
0945: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0946: "STR"));
0947: ctx.addFrom(new TableIdentifier("FOO"));
0948: Selectable where = makeLeafWhereNode(new LessThanFunction(),
0949: new ColumnIdentifier(new TableIdentifier("FOO"), "ID"),
0950: new Literal(new Integer(5), new IntegerType()));
0951: ctx.setWhere(where);
0952:
0953: ResultSet rset = select.executeQuery(_db);
0954: assertNotNull(rset);
0955:
0956: for (int i = 0; i < 5; i++) {
0957: assertTrue(rset.next());
0958: assertEquals(String.valueOf(i), rset.getString(1));
0959: assertTrue(!rset.wasNull());
0960: assertEquals(String.valueOf(i), rset.getString("str"));
0961: assertTrue(!rset.wasNull());
0962: }
0963: assertTrue(!rset.next());
0964: }
0965:
0966: public void testSelectWithOrderBy() throws Exception {
0967: createTableFoo();
0968: populateTableFoo();
0969:
0970: // execute a select upon it
0971: AxionQueryContext ctx = new AxionQueryContext();
0972: SelectCommand select = new SelectCommand(ctx);
0973: ctx.addSelect(new ColumnIdentifier(new TableIdentifier("FOO"),
0974: "STR"));
0975: ctx.addFrom(new TableIdentifier("FOO"));
0976: ctx.addOrderBy(new OrderNode(new ColumnIdentifier(
0977: new TableIdentifier("FOO"), "ID"), true));
0978: ResultSet rset = select.executeQuery(_db);
0979: assertNotNull(rset);
0980: for (int i = 9; i >= 0; i--) {
0981: assertTrue(rset.next());
0982: assertEquals(String.valueOf(i), rset.getString(1));
0983: assertTrue(!rset.wasNull());
0984: assertEquals(String.valueOf(i), rset.getString("STR"));
0985: assertTrue(!rset.wasNull());
0986: }
0987: assertTrue(!rset.next());
0988: }
0989:
0990: public void testSequence() throws Exception {
0991: assertNull("Should not find sequence", getDb().getSequence(
0992: "SEQ1"));
0993: getDb().createSequence(new Sequence("SEQ1", 0));
0994: assertNotNull("Should get sequence", getDb()
0995: .getSequence("SEQ1"));
0996: getDb().getSequence("SEQ1").evaluate();
0997: getDb().getSequence("SEQ1").evaluate();
0998: assertEquals("Should have correct value",
0999: BigInteger.valueOf(2), getDb().getSequence("SEQ1")
1000: .getValue());
1001:
1002: Table seqTable = getDb().getTable("AXION_SEQUENCES");
1003: assertNotNull("Should find sequence table", seqTable);
1004: RowIterator it = seqTable.getRowIterator(true);
1005: Object seqVal = null;
1006: while (seqVal == null && it.hasNext()) {
1007: Row row = it.next();
1008: if ("SEQ1".equals(row.get(0))) {
1009: seqVal = row.get(1);
1010: }
1011: }
1012: assertNotNull("Should find value", seqVal);
1013: assertEquals("Value should be updated", new Integer(2), seqVal);
1014: }
1015:
1016: private Selectable makeLeafWhereNode(Function fun, Selectable left,
1017: Selectable right) {
1018: fun.addArgument(left);
1019: if (null != right) {
1020: fun.addArgument(right);
1021: }
1022: return fun;
1023: }
1024:
1025: }
|