[JAXME-93] union statements as views

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: JaxMeJS
• Assignee: Unassigned
• Reporter: Tobias Hühner
• Created: 20/Aug/07 13:54
• Updated: 20/Aug/07 13:55

Description

Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SelectCombinationStatementImpl.java
===================================================================
— D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SelectCombinationStatementImpl.java (revision 567598)
+++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SelectCombinationStatementImpl.java (working copy)
@@ -7,6 +7,7 @@
import org.apache.ws.jaxme.sqls.SQLFactory;
import org.apache.ws.jaxme.sqls.SelectCombinationStatement;
import org.apache.ws.jaxme.sqls.SelectStatement;
+import org.apache.ws.jaxme.sqls.Table;

/**
@@ -24,6 +25,17 @@
}

public Iterator getSelectStatements() {

• return statements.iterator();
  + if(statements.isEmpty()) { + throw new IllegalStateException("No Statement defined"); + }

  + return statements.iterator();
  }
  +
  + public Table createView(Table.Name pName)

  { + return getSQLFactory().getObjectFactory().newView(this,pName); + }

  +
  + public Table createView(String pName)

  { + return createView(pName == null ? null : new TableImpl.NameImpl(pName)); + }

  }
  Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SelectCombinationViewImpl.java
  ===================================================================

  • • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SelectCombinationViewImpl.java (revision 0)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SelectCombinationViewImpl.java (revision 0)
      @@ -0,0 +1,126 @@
      +package org.apache.ws.jaxme.sqls.impl;
      +
      +
      +import java.util.ArrayList;
      +import java.util.Iterator;
      +import java.util.List;
      +
      +import org.apache.ws.jaxme.sqls.Column;
      +import org.apache.ws.jaxme.sqls.ColumnReference;
      +import org.apache.ws.jaxme.sqls.DeleteStatement;
      +import org.apache.ws.jaxme.sqls.ForeignKey;
      +import org.apache.ws.jaxme.sqls.Index;
      +import org.apache.ws.jaxme.sqls.InsertStatement;
      +import org.apache.ws.jaxme.sqls.SelectCombinationStatement;
      +import org.apache.ws.jaxme.sqls.SelectStatement;
      +import org.apache.ws.jaxme.sqls.Table;
      +import org.apache.ws.jaxme.sqls.UpdateStatement;
      +import org.apache.ws.jaxme.sqls.Column.Type;
      +
      +public class SelectCombinationViewImpl extends TableImpl implements Table {
      + private final SelectCombinationStatement statement;
      +
      + protected SelectCombinationViewImpl(SelectCombinationStatement pStatement, Name pName) { + super((getFirstStatement(pStatement)).getTableReference().getTable().getSchema(), + pName == null ? (getFirstStatement(pStatement)).getTableReference().getTable().getName() : pName); + statement = pStatement; + }

      +
      + private static SelectStatement getFirstStatement(SelectCombinationStatement pStatement)

      { + return (SelectStatement)pStatement.getSelectStatements().next(); + }

      +
      + public final SelectCombinationStatement getSelectCombinationStatement()

      { + return statement; + }

      + private class ViewColumnImpl implements Column

      Unknown macro: {+ private final Column.Name colName;+ private final Column col;+ private Object data;+ private ViewColumnImpl(ColumnReference pColumn) { + col = pColumn.getColumn(); + Column.Name alias = pColumn.getAlias(); + colName = alias == null ? col.getName() : alias; + }+ public Table getTable() { return SelectCombinationViewImpl.this; }+ public Name getName() { return colName; }+ public String getQName() { return getTable() + "." + getName(); }+ public Type getType() { return col.getType(); }+ public boolean isPrimaryKeyPart() { return false; }
      + public void setNullable(boolean pNullable) { + throw new IllegalStateException("Unable to set a view columns 'Nullable' property."); + }
      + public boolean isNullable() { return col.isNullable(); }
      + public boolean isStringColumn() { return col.isStringColumn(); }
      + public boolean isBinaryColumn() { return col.isBinaryColumn(); }
      + public void setCustomData(Object pData) { data = pData; }
      + public Object getCustomData() { return data; }
      + public boolean isVirtual() { return false; }+ }

      +
      + public Iterator getColumns()

      Unknown macro: {+ List result = new ArrayList();+ for (Iterator iter = getFirstStatement(statement).getResultColumns(); iter.hasNext(); ) { + ColumnReference col = (ColumnReference) iter.next(); + result.add(new ViewColumnImpl(col)); + }+ return result.iterator();+ }

      +
      + public Column getColumn(Column.Name pName) {
      + if (pName == null)

      { + throw new NullPointerException("Column names must not be null."); + }

      + for (Iterator iter = getFirstStatement(statement).getResultColumns(); iter.hasNext(); )

      Unknown macro: {+ ColumnReference col = (ColumnReference) iter.next();+ Column.Name alias = col.getAlias();+ if (alias == null) { + alias = col.getColumn().getName(); + }+ if (alias.equals(pName)) { + return new ViewColumnImpl(col); + }+ }

      + return null;
      + }
      +
      + public Column newColumn(Column.Name pName, Type pType)

      { + throw new IllegalStateException("A views columns cannot be changed."); + }
      +
      + public Column newColumn(String pName, Type pType) {+ throw new IllegalStateException("A views columns cannot be changed.");+ }

      +
      + public Index newKey()

      { + throw new IllegalStateException("A view cannot have keys."); + }

      + public Index newIndex()

      { + throw new IllegalStateException("A view cannot have indexes."); + }
      + public Index newPrimaryKey() { + throw new IllegalStateException("A view cannot have a primary key."); + }
      + public ForeignKey newForeignKey(Table pReferencedTable) { + throw new IllegalStateException("A view cannot have foreign keys."); + }
      + public InsertStatement getInsertStatement() { + throw new IllegalStateException("A view is not updateable."); + }
      + public UpdateStatement getUpdateStatement() {+ throw new IllegalStateException("A view is not updateable.");+ }
      + public DeleteStatement getDeleteStatement() { + throw new IllegalStateException("A view is not updateable."); + }
      + public Index getPrimaryKey() {+ throw new IllegalStateException("A view cannot have a primary key.");+ }
      + public Iterator getIndexes() {+ throw new IllegalStateException("A view cannot have indexes.");+ }

      + public Iterator getForeignKeys()

      { + throw new IllegalStateException("A view cannot have foreign keys."); + }

      +
      +}
      Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/ObjectFactoryImpl.java
      ===================================================================

    • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/ObjectFactoryImpl.java (revision 567598)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/ObjectFactoryImpl.java (working copy)
      @@ -32,6 +32,7 @@
      import org.apache.ws.jaxme.sqls.Table;
      import org.apache.ws.jaxme.sqls.TableReference;
      import org.apache.ws.jaxme.sqls.CombinedConstraint.Type;
      +import org.apache.ws.jaxme.sqls.Table.Name;

/** <p>Default implementation of the object factory.</p>
@@ -58,6 +59,12 @@
public Table newView(SelectStatement pSelectStatement, Table.Name pName)

{ return new ViewImpl(pSelectStatement, pName); }

+
+ public Table newView(
+ SelectCombinationStatementImpl selectCombinationStatementImpl,
+ Name name)

{ + return new SelectCombinationViewImpl(selectCombinationStatementImpl,name); + }

public CombinedConstraint newCombinedConstraint(ConstrainedStatement pStatement, Type pType) {
return new CombinedConstraintImpl(pStatement, pType);
@@ -74,4 +81,5 @@
public Expression createExpression(Statement pStatement, org.apache.ws.jaxme.sqls.Expression.Type pType)

{ return new ExpressionImpl(pStatement, pType); }

+
}
Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SQLGeneratorImpl.java
===================================================================
— D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SQLGeneratorImpl.java (revision 567598)
+++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/SQLGeneratorImpl.java (working copy)
@@ -592,6 +592,9 @@
if (t instanceof ViewImpl)

{ ViewImpl v = (ViewImpl) t; tableName = "(" + getSelectQuery(v.getViewStatement(), pData) + ")"; + }

else if (t instanceof SelectCombinationViewImpl)

{ + SelectCombinationViewImpl view = (SelectCombinationViewImpl) t; + tableName = "(" + getQuery(view.getSelectCombinationStatement()) + ")"; }

else

{ tableName = pTable.getTable().getQName(); }

@@ -1034,6 +1037,11 @@
checkSameStructure(first, other);
sb.append(' ');
sb.append(pSep);
+ if(pStatement instanceof UnionStatement) {
+ if(((UnionStatement)pStatement).isAll(other))

{ + sb.append(" ALL"); + }

+ };
sb.append(" (");
sb.append(getQuery(other));
sb.append(')');
@@ -1052,9 +1060,7 @@
} else if (pStatement instanceof SelectStatement)

{ s = getSelectQuery((SelectStatement) pStatement); }

else if (pStatement instanceof UnionStatement)

{ - final UnionStatement us = (UnionStatement) pStatement; - final boolean all = us.isAll(); - s = getSelectCombinationStatement(us, all ? "UNION ALL" : "UNION"); + s = getSelectCombinationStatement((UnionStatement) pStatement, "UNION"); }

else if (pStatement instanceof IntersectionStatement)

{ s = getSelectCombinationStatement((IntersectionStatement) pStatement, "INTERSECT"); }

else if (pStatement instanceof MinusStatement) {
Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/UnionStatementImpl.java
===================================================================
— D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/UnionStatementImpl.java (revision 567598)
+++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/impl/UnionStatementImpl.java (working copy)
@@ -1,6 +1,9 @@
package org.apache.ws.jaxme.sqls.impl;

+import java.util.HashMap;
+
import org.apache.ws.jaxme.sqls.SQLFactory;
+import org.apache.ws.jaxme.sqls.SelectStatement;
import org.apache.ws.jaxme.sqls.UnionStatement;

@@ -8,17 +11,28 @@

• Default implementation of {@link UnionStatement}.
  */
  public class UnionStatementImpl extends SelectCombinationStatementImpl implements UnionStatement {
  - private boolean all;
  -
  + private final HashMap all = new HashMap();
  + private boolean first = true;
  +
  protected UnionStatementImpl(SQLFactory pFactory) { super(pFactory); }
  
  - public boolean isAll() { - return all; - }
  + public void addStatement(SelectStatement statement, boolean pAll) {
  + if(first && pAll){ + throw new IllegalArgumentException(" pAll=true for first Statement is not allowed"); + }
  + super.addStatement(statement);
  + all.put(statement,new Boolean(pAll));
  + first = false;
  + }
  +
  + public void addStatement(SelectStatement statement) { + addStatement(statement,false); + }
  
  - public void setAll(boolean pAll) { - all = pAll; - }
  + public boolean isAll(SelectStatement statement) { + return ((Boolean)all.get(statement)).booleanValue(); + }
  +
  }
  Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/SelectCombinationStatement.java
  ===================================================================
  — D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/SelectCombinationStatement.java (revision 567598)
  +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/SelectCombinationStatement.java (working copy)
  @@ -7,10 +7,9 @@
  * Common base interface for {@link UnionStatement}

  , and

• {@link IntersectionStatement}

  .
  */
  -public interface SelectCombinationStatement extends Statement {
  +public interface SelectCombinationStatement extends Statement, ViewableStatement

  { /** - * Adds a select statement to the given "union", or - * "intersection" statement. + * Adds a select statement to the given "union", "intersection", or "minus" statement. */ void addStatement(SelectStatement pStatement); @@ -18,4 +17,5 @@ * Returns an iterator over all the select statements. */ Iterator getSelectStatements(); + }

  Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/ObjectFactory.java
  ===================================================================

  • • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/ObjectFactory.java (revision 567598)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/ObjectFactory.java (working copy)
      @@ -16,6 +16,8 @@
      package org.apache.ws.jaxme.sqls;

import org.apache.ws.jaxme.sqls.Expression.Type;
+import org.apache.ws.jaxme.sqls.Table.Name;
+import org.apache.ws.jaxme.sqls.impl.SelectCombinationStatementImpl;

/** A factory object for creating all the objects used by
@@ -46,6 +48,16 @@
*/
public Table newView(SelectStatement pSelectStatement, Table.Name pName);

+ /** Returns an instance of
+ *

{@link org.apache.ws.jaxme.sqls.Table}

, which allows to embed
+ * the given instance of
+ *

{@link org.apache.ws.jaxme.sqls.SelectCombinationStatement}

into another
+ * SELECT statement.
+ */
+ public Table newView(
+ SelectCombinationStatementImpl selectCombinationStatementImpl,
+ Name name);
+
/** Creates a new instance of

• {@link org.apache.ws.jaxme.sqls.Constraint}

  constraining

• the given {@link org.apache.ws.jaxme.sqls.ConstrainedStatement}

  .
  @@ -64,4 +76,5 @@
  /** Creates a new instance of

  {@link Expression}

  .
  */
  public Expression createExpression(Statement pStatement, Type sum);
  +
  }
  Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/SelectStatement.java
  ===================================================================

  • • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/SelectStatement.java (revision 567598)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/SelectStatement.java (working copy)
      @@ -23,7 +23,7 @@
      *
• @author <a href="mailto:[e-mail]">Jochen Wiedmann</a>
  */
  -public interface SelectStatement extends ConstrainedStatement {
  +public interface SelectStatement extends ConstrainedStatement,ViewableStatement { /** Provides a single column for an <code>ORDER BY</code> * clause. */ @@ -111,14 +111,4 @@ */ public int getSkippedRows(); - /** <p>Creates a view, which may be used to embed the statement into - * a separate query.</p> - */ - public Table createView(Table.Name pName); - - - /** <p>Creates a view, which may be used to embed the statement into - * a separate query.</p> - */ - public Table createView(String pName); }

  Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/junit/CreateTest.java
  ===================================================================

  • • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/junit/CreateTest.java (revision 567598)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/junit/CreateTest.java (working copy)
      @@ -458,7 +458,7 @@
      assertEquals(expect, got);
      }

• /** Test for UNION and UNION ALL.
  + /** Test for UNION.
  */
  public void testUnion() { UnionStatement us = getSQLFactory().newUnionStatement(); @@ -467,7 +467,23 @@ String got = gen.getQuery(us); String expect = "(SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE 'MyIndex'>5) UNION (SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE 'MyName' LIKE 'x%')"; assertEquals(expect, got); - us.setAll(true); + }

  + /** Test for UNION ALL.
  + */
  + public void testUnionAll()

  { + UnionStatement us = getSQLFactory().newUnionStatement(); + Table t = getBasicTable(); + SelectStatement st1 = t.getSelectStatement(); + SelectStatement st2 = t.getSelectStatement(); + BooleanConstraint bc1 = st1.getWhere().createGT(); + bc1.addPart("MyIndex"); + bc1.addPart(5); + BooleanConstraint bc2 = st2.getWhere().createLIKE(); + bc2.addPart("MyName"); + bc2.addPart("x%"); + us.addStatement(st1); + us.addStatement(st2,true); + SQLGenerator gen = getSQLGenerator(); String gotAll = gen.getQuery(us); String expectAll = "(SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE 'MyIndex'>5) UNION ALL (SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE 'MyName' LIKE 'x%')"; assertEquals(expectAll, gotAll); @@ -516,4 +532,21 @@ // Ok }

  }
  +
  + /** Test for UNION and UNION ALL.
  + */
  + public void testUnionView()

  { + UnionStatement us = getSQLFactory().newUnionStatement(); + createSelectCombinationStatement(us); + Table view = us.createView((String)null); + // SelectStatemnt st = SELECT * FROM view ... + SelectStatement st = getSQLFactory().newSelectStatement(); + st.setTable(view); + + SQLGenerator gen = getSQLGenerator(); + String got = gen.getQuery(st); + String expect = "SELECT * FROM ((SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE 'MyIndex'>5) UNION (SELECT MyIndex, MyName, MyDate FROM MySchema.MyTable WHERE 'MyName' LIKE 'x%'))"; + assertEquals(expect, got); + }

  +
  }
  Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/UnionStatement.java
  ===================================================================

  • • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/UnionStatement.java (revision 567598)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/UnionStatement.java (working copy)
      @@ -6,14 +6,15 @@
      */
      public interface UnionStatement extends SelectCombinationStatement { /** + * Adds a select statement to the given "union", "intersection", or "minus" statement. * Sets, whether this is a "union all" statement. By default, * it isn't. */ - void setAll(boolean pAll); - + void addStatement(SelectStatement pStatement,boolean all); + /** - * Returns, whether this is a "union all" statement. By default, - * it isn't. + * is 'union all' Statement */ - boolean isAll(); + boolean isAll(SelectStatement pStatement); + }

      Index: D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/ViewableStatement.java
      ===================================================================

    • D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/ViewableStatement.java (revision 0)
      +++ D:/projekte/justus/ws-jaxme-0.5.3-SNAPSHOT/src/js/org/apache/ws/jaxme/sqls/ViewableStatement.java (revision 0)
      @@ -0,0 +1,18 @@
      +package org.apache.ws.jaxme.sqls;
      +
      +/**
      + * this interface describe the potential use as view for a statement
      + */
      +public interface ViewableStatement { + + /** <p>Creates a view, which may be used to embed the statement into + * a separate query.</p> + */ + public abstract Table createView(Table.Name pName); + + /** <p>Creates a view, which may be used to embed the statement into + * a separate query.</p> + */ + public abstract Table createView(String pName); + +}

      \ No newline at end of file