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); } }
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(); }