Beispiel #1
0
  public QuerySessionWithViews(QuerySession underlyingQuerySession) {
    this.underlyingQuerySession = underlyingQuerySession;

    this.correspondingViews = new ArrayList<SymbolicDBView>();

    for (int i = 0; i < underlyingQuerySession.numSteps(); i++) {

      List<QueryFeature> step = underlyingQuerySession.getSteps().get(i);
      assert (step.size() == 1);

      StringBuilder viewDefinition = new StringBuilder("CREATE VIEW V" + i + " AS \nSELECT ");
      List<String> viewColumns = new ArrayList<String>();

      QueryFeature addedSnippet = step.get(0);
      if (i == 0) {
        F_TableInFrom table = (F_TableInFrom) addedSnippet;
        RelationSchema tableSchema = underlyingQuerySession.getSchema().get(table.getTableName());

        for (ColumnSchema col : tableSchema.getAttributes()) {
          String fullColName = tableSchema.getRelationName() + "." + col;
          viewDefinition.append(
              fullColName + " AS " + fullColName.replace(".", "") + "_" + i + ",");
          viewColumns.add(fullColName.replace(".", "") + "_" + i);
        }
        viewDefinition.deleteCharAt(viewDefinition.length() - 1); // remove last comma
        viewDefinition.append("\nFROM " + table.getTableName() + ";");

      } else if (addedSnippet instanceof F_TableInFrom) {
        F_TableInFrom addedTable = (F_TableInFrom) addedSnippet;
        RelationSchema tableSchema =
            underlyingQuerySession.getSchema().get(addedTable.getTableName());

        // columns from V_i-1
        for (String oldColName : correspondingViews.get(i - 1).columnNames) {
          viewDefinition.append(oldColName + " AS " + oldColNameToNewColName(oldColName, i) + ",");
          viewColumns.add(oldColNameToNewColName(oldColName, i));
        }

        // columns from addedTable
        for (ColumnSchema col : tableSchema.getAttributes()) {
          String fullColName = tableSchema.getRelationName() + "." + col;
          viewDefinition.append(
              fullColName + " AS " + fullColName.replace(".", "") + "_" + i + ",");
          viewColumns.add(fullColName.replace(".", "") + "_" + i);
        }
        viewDefinition.deleteCharAt(viewDefinition.length() - 1); // remove last comma
        viewDefinition.append("\nFROM V" + (i - 1) + "," + addedTable.getTableName() + ";");

      } else if (addedSnippet instanceof F_PredicateInWhere) {
        F_PredicateInWhere addedPred = (F_PredicateInWhere) addedSnippet;
        // columns from V_i-1

        for (String oldColName : correspondingViews.get(i - 1).columnNames) {
          viewDefinition.append(oldColName + " AS " + oldColNameToNewColName(oldColName, i) + ",");
          viewColumns.add(oldColNameToNewColName(oldColName, i));
        }
        viewDefinition.deleteCharAt(viewDefinition.length() - 1); // remove last comma
        viewDefinition.append("\nFROM V" + (i - 1));
        viewDefinition.append("\nWHERE " + predicateToViewPredicate(addedPred, (i - 1)) + ";");
      } else if (addedSnippet instanceof F_ColumnInGroupBy) {
        F_ColumnInGroupBy addedGroupbyCol = (F_ColumnInGroupBy) addedSnippet;

        viewDefinition.append(
            colNameToViewColName(
                    addedGroupbyCol.getTableName(), addedGroupbyCol.getColumnName(), i - 1)
                + " AS "
                + colNameToViewColName(
                    addedGroupbyCol.getTableName(), addedGroupbyCol.getColumnName(), i));

        viewDefinition.append(", count(*)");
        viewDefinition.append("\nFROM V" + (i - 1));
        viewDefinition.append(
            "\nGROUP BY "
                + colNameToViewColName(
                    addedGroupbyCol.getTableName(), addedGroupbyCol.getColumnName(), i - 1));
        viewColumns.add(
            colNameToViewColName(
                addedGroupbyCol.getTableName(), addedGroupbyCol.getColumnName(), i));
      }

      SymbolicDBView sv = new SymbolicDBView("V" + i, viewDefinition.toString(), viewColumns);
      if (i > 0) {
        sv.setBackRule(
            createBackRule(
                underlyingQuerySession.getSchema(),
                addedSnippet,
                sv,
                correspondingViews.get(i - 1),
                i));
      } else {
        sv.setBackRule(
            createBackRule(underlyingQuerySession.getSchema(), addedSnippet, sv, null, i));
      }
      correspondingViews.add(sv);
    }
  }
Beispiel #2
0
  private static String createBackRule(
      DBSchema baseSchema,
      QueryFeature addedSnippet,
      SymbolicDBView newView,
      SymbolicDBView underlyingView,
      int newViewIndex) {

    StringBuilder backrule =
        new StringBuilder(
            "CREATE RULE R_"
                + newView.getViewName()
                + " AS \n"
                + "ON INSERT TO "
                + newView.getViewName()
                + " DO INSTEAD \n");

    if (newViewIndex == 0) {
      F_TableInFrom addedTable = (F_TableInFrom) addedSnippet;
      backrule.append("INSERT INTO " + addedTable.getTableName() + " VALUES (");

      // cols from newly added table
      RelationSchema tableSchema = baseSchema.get(addedTable.getTableName());
      for (ColumnSchema col : tableSchema.getAttributes()) {
        backrule.append("NEW." + addedTable.getTableName() + col + "_" + newViewIndex + ",");
      }
      backrule.deleteCharAt(backrule.length() - 1); // remove last comma
      backrule.append(");");

    } else if (addedSnippet instanceof F_TableInFrom) {
      F_TableInFrom addedTable = (F_TableInFrom) addedSnippet;
      backrule.append("(INSERT INTO " + underlyingView.getViewName() + " VALUES (");

      // cols from underlying view
      for (String col : underlyingView.getColumnNames()) {
        backrule.append("NEW." + oldColNameToNewColName(col, newViewIndex) + ",");
      }
      backrule.deleteCharAt(backrule.length() - 1); // remove last comma
      backrule.append("); \nINSERT INTO " + addedTable.getTableName() + " VALUES(");

      // cols from newly added table
      RelationSchema tableSchema = baseSchema.get(addedTable.getTableName());
      for (ColumnSchema col : tableSchema.getAttributes()) {
        backrule.append("NEW." + addedTable.getTableName() + col + "_" + newViewIndex + ",");
      }
      backrule.deleteCharAt(backrule.length() - 1); // remove last comma
      backrule.append("));");
    } else if (addedSnippet instanceof F_PredicateInWhere) {
      F_PredicateInWhere addedPred = (F_PredicateInWhere) addedSnippet;

      backrule.append("(INSERT INTO " + underlyingView.getViewName() + " VALUES (");

      // cols from underlying view
      for (String col : newView.getColumnNames()) {
        backrule.append("NEW." + col + ",");
      }
      backrule.deleteCharAt(backrule.length() - 1); // remove last comma
      backrule.append(");");

      // add constraint

      if (addedPred.rhsIsConst()) {
        backrule.append("\nINSERT INTO Constraints VALUES(");
        backrule.append(
            "NEW."
                + addedPred.getTable1()
                + addedPred.getCol1()
                + "_"
                + newViewIndex
                + ",'"
                + addedPred.getOp()
                + addedPred.getCol2()
                + "')");
      } else if (addedPred.lhsIsConst()) {
        backrule.append("\nINSERT INTO Constraints VALUES(");
        backrule.append(
            "NEW."
                + addedPred.getTable2()
                + addedPred.getCol2()
                + "_"
                + newViewIndex
                + ",'"
                + "reverse("
                + addedPred.getOp()
                + ")"
                + addedPred.getCol1()
                + "')");
      } else {
        backrule.append("\nINSERT INTO VarConstraints VALUES(");
        backrule.append(
            "NEW."
                + addedPred.getTable1()
                + addedPred.getCol1()
                + "_"
                + newViewIndex
                + ","
                + "NEW."
                + addedPred.getTable2()
                + addedPred.getCol2()
                + "_"
                + newViewIndex
                + ",'"
                + addedPred.getOp()
                + "')");
      }

      backrule.append("); ");

    } else if (addedSnippet instanceof F_ColumnInSelect) {
      // direct backrule. since we ignore projects.
      backrule.append("INSERT INTO " + underlyingView.getViewName() + " VALUES (");

      // cols from underlying view
      for (String col : underlyingView.getColumnNames()) {
        backrule.append("NEW." + oldColNameToNewColName(col, newViewIndex) + ",");
      }
      backrule.deleteCharAt(backrule.length() - 1); // remove last comma
      backrule.append(");");
    }

    // problem: grouped by col1 [v1], and then we add col2 to the grouping[v2].
    // v2 can not be computed from v1.

    return backrule.toString();
  }