1. tbrugz
  2. sqldump

Commits

tbrugz  committed e7026c5

resultset-pivot: added PivotResultSet flags ; changed PivotQueryParser

  • Participants
  • Parent commits e998baa
  • Branches default

Comments (0)

Files changed (6)

File src/tbrugz/sqldump/resultset/pivot/PivotResultSet.java

View file
 	
 	final static String MEASURES_COLNAME = "Measure"; 
 	
+	public static final int SHOW_MEASURES_IN_ROWS = 0x01;
+	public static final int SHOW_MEASURES_LAST = 0x02;
+	public static final int SHOW_MEASURES_ALLWAYS = 0x04;
+	
 	// original ResultSet properties
 	final ResultSet rs;
 	final int rsColsCount;
 	int position = -1;
 	int rowCount = 0;
 	Key currentNonPivotKey = null;
-	public static boolean showMeasuresInColumns = true;
-	public static boolean showMeasuresFirst = true;
-	public boolean alwaysShowMeasures = false;
+	boolean showMeasuresInColumns = true;
+	boolean showMeasuresFirst = true;
+	boolean alwaysShowMeasures = false;
 
 	// colsNotToPivot+colsToPivot - key cols ?
 	
 	}
 
 	public PivotResultSet(ResultSet rs, List<String> colsNotToPivot, List<String> colsToPivot, boolean doProcess) throws SQLException {
-		this(rs, colsNotToPivot, list2Map(colsToPivot), doProcess);
+		this(rs, colsNotToPivot, list2Map(colsToPivot), doProcess, 0);
 	}
 	
 	public PivotResultSet(ResultSet rs, List<String> colsNotToPivot, Map<String, Comparable> colsToPivot,
 			boolean doProcess) throws SQLException {
+		this(rs, colsNotToPivot, colsToPivot, doProcess, 0);
+	}
+	
+	public PivotResultSet(ResultSet rs, List<String> colsNotToPivot, Map<String, Comparable> colsToPivot,
+			boolean doProcess, int flags) throws SQLException {
 		this.rs = rs;
 		this.colsNotToPivot = colsNotToPivot;
 		this.colsToPivot = colsToPivot;
+		setFlags(flags);
+		
 		for(int i=0;i<colsNotToPivot.size();i++) {
 			this.colsNotToPivotType.add(null);
 			//this.colsNotToPivotIndex.add(null);
 		if(doProcess) { process(); }
 	}
 	
+	void setFlags(int flags) {
+		//log.debug("flags: "+flags);
+		alwaysShowMeasures = (flags & SHOW_MEASURES_ALLWAYS) != 0;
+		showMeasuresFirst = (flags & SHOW_MEASURES_LAST) == 0;
+		showMeasuresInColumns = (flags & SHOW_MEASURES_IN_ROWS) == 0;
+	}
+	
 	//XXX: addObservers, ...
 	
 	/*

File src_pivot/tbrugz/sqldump/pivot/PivotPreparedStatement.java

View file
 	//XXX
 	public ResultSet executeQuery() throws SQLException {
 		PivotQueryParser parser = new PivotQueryParser(sql);
-		return new PivotResultSet(statement.executeQuery(), parser.colsNotToPivot, parser.colsToPivot, true);
+		return new PivotResultSet(statement.executeQuery(), parser.colsNotToPivot, parser.colsToPivot, true, parser.flags);
 		//return statement.executeQuery();
 	}
 

File src_pivot/tbrugz/sqldump/pivot/PivotQueryParser.java

View file
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 
+import tbrugz.sqldump.resultset.pivot.PivotResultSet;
+
+/*
+ * TODO: columns may have spaces if enclosed by '"'
+ */
 public class PivotQueryParser {
 
-	/*static PivotQueryParser parser = new PivotQueryParser();
-	
-	public static PivotQueryParser instance() {
-		return parser;
-	}
-	
-	public ResultSet parse(String sql) {
-		return null;
-	}*/
-	
 	static final String PATTERN_PIVOT_CLAUSE_STR = "/\\*\\s*pivot\\s+(.*)\\s+nonpivot\\s+(.*)\\s*\\*/";
 	static final Pattern PATTERN_PIVOT_CLAUSE = Pattern.compile(PATTERN_PIVOT_CLAUSE_STR, Pattern.CASE_INSENSITIVE);
+	
+	static final String MEASURES_SHOW_LAST = "+measures-show-last";
+	static final String MEASURES_SHOW_INROWS = "+measures-show-inrows";
+	static final String MEASURES_SHOW_ALLWAYS = "+measures-show-allways";
 
 	final Map<String, Comparable> colsToPivot;
 	final List<String> colsNotToPivot;
+	final int flags;
 	
 	public PivotQueryParser(String sql) {
 		Matcher m = PATTERN_PIVOT_CLAUSE.matcher(sql);
 		if(!found) {
 			colsToPivot = null;
 			colsNotToPivot = null;
+			flags = 0;
 			return;
 		}
 		colsToPivot = new LinkedHashMap<String, Comparable>();
 		}
 		
 		String nonPivotColsStr = m.group(2); 
-		String[] nonPivotCols = nonPivotColsStr.split(",");
+		String[] nonPivotCols = nonPivotColsStr.split("[, ]");
+		int flags = 0;
 		for(String s: nonPivotCols) {
 			s = s.trim();
-			if(!"".equals(s)) {
+			if("".equals(s)) { continue; }
+			if(s.startsWith("+")) {
+				if(MEASURES_SHOW_LAST.equals(s)) {
+					flags |= PivotResultSet.SHOW_MEASURES_LAST;
+				}
+				else if(MEASURES_SHOW_INROWS.equals(s)) {
+					flags |= PivotResultSet.SHOW_MEASURES_IN_ROWS;
+				}
+				else if(MEASURES_SHOW_ALLWAYS.equals(s)) {
+					flags |= PivotResultSet.SHOW_MEASURES_ALLWAYS;
+				}
+				else {
+					throw new IllegalArgumentException("unknown control argument: "+s);
+				}
+			}
+			else {
 				colsNotToPivot.add(s);
 			}
 		}
+		this.flags = flags;
+		//System.out.println("flags: "+flags);
 	}
 	
 }

File src_pivot/tbrugz/sqldump/pivot/PivotStatement.java

View file
 		}
 		PivotQueryParser parser = new PivotQueryParser(sql);
 		if(parser.colsToPivot!=null) {
-			return new PivotResultSet(statement.executeQuery(sql), parser.colsNotToPivot, parser.colsToPivot, true);
+			return new PivotResultSet(statement.executeQuery(sql), parser.colsNotToPivot, parser.colsToPivot, true, parser.flags);
 		}
 		return statement.executeQuery(sql);
 	}

File src_test/tbrugz/sqldump/pivot/QueryTest.java

View file
 	
 	@Before
 	public void before() throws SQLException, ClassNotFoundException {
-		PivotResultSet.showMeasuresInColumns = true;
-		PivotResultSet.showMeasuresFirst = true;
-		
 		Class.forName(pivotDriverClass); //"tbrugz.sqldump.pivot.SQLPivotDriver");
 		
 		String url = "jdbc:sqlpivot:h2:mem:abc";
 	@Test
 	public void testQ4Pivot2Measures() throws SQLException, ClassNotFoundException, IOException {
 		String sql = prop.getProperty("q4");
-		ResultSet rs = conn.createStatement().executeQuery(sql);
+		String sql1 = sql + " /* pivot B nonpivot A */";
+		ResultSet rs = conn.createStatement().executeQuery(sql1);
 		QueryDumper.simplerRSDump(rs);
 		
 		rs.absolute(1);
 		Assert.assertEquals("false", rs.getString("BOOL_AND|B:false"));
 		Assert.assertEquals("true", rs.getString("BOOL_OR|B:true"));
 		
-		PivotResultSet.showMeasuresFirst = false;
-		//((PivotResultSet)rs).showMeasuresFirst = false;
+		String sql2 = sql + " /* pivot B nonpivot A +measures-show-last */";
+		rs = conn.createStatement().executeQuery(sql2);
 		((PivotResultSet)rs).processMetadata();
-		//rs.absolute(0);
 		QueryDumper.simplerRSDump(rs);
 
 		rs.absolute(1);
 	@Test
 	public void testQ4MeasuresInColumnsFirst() throws SQLException, ClassNotFoundException, IOException {
 		String sql = prop.getProperty("q4");
-		ResultSet rs = conn.createStatement().executeQuery(sql);
+		String sql1 = sql + " /* pivot B nonpivot A */";
+		ResultSet rs = conn.createStatement().executeQuery(sql1);
 		QueryDumper.simplerRSDump(rs);
 		
 		rs.absolute(1);
 		Assert.assertEquals("false", rs.getString("BOOL_AND|B:false"));
 		Assert.assertEquals("true", rs.getString("BOOL_OR|B:true"));
 		
-		//((PivotResultSet)rs).showMeasuresInColumns = false;
-		PivotResultSet.showMeasuresInColumns = false;
-		PivotResultSet.showMeasuresFirst = true; //both ways must be tested
-		rs = conn.createStatement().executeQuery(sql);
-		//((PivotResultSet)rs).showMeasuresFirst = true; //both ways must be tested
-		//((PivotResultSet)rs).processMetadata();
+		String sql2 = sql + " /* pivot B nonpivot A +measures-show-inrows */";
+		rs = conn.createStatement().executeQuery(sql2);
 		QueryDumper.simplerRSDump(rs);
 
 		rs.absolute(1);
 	@Test
 	public void testQ4MeasuresInColumnsLast() throws SQLException, ClassNotFoundException, IOException {
 		String sql = prop.getProperty("q4");
-		ResultSet rs = conn.createStatement().executeQuery(sql);
+		String sql1 = sql + " /* pivot B nonpivot A */";
+		ResultSet rs = conn.createStatement().executeQuery(sql1);
 		QueryDumper.simplerRSDump(rs);
 		
 		rs.absolute(1);
 		Assert.assertEquals("false", rs.getString("BOOL_AND|B:false"));
 		Assert.assertEquals("true", rs.getString("BOOL_OR|B:true"));
 		
-		PivotResultSet.showMeasuresInColumns = false;
-		PivotResultSet.showMeasuresFirst = false;
-		rs = conn.createStatement().executeQuery(sql);
+		String sql2 = sql + " /* pivot B nonpivot A +measures-show-inrows */";
+		rs = conn.createStatement().executeQuery(sql2);
 		QueryDumper.simplerRSDump(rs);
 
 		rs.absolute(1);

File src_test/tbrugz/sqldump/pivot/pivot.properties

View file
 union all select false, true \
 union all select true, false \
 union all select true, true \
-) /* pivot B nonpivot A */
+)
 
 # pivot with "aggregator"
 q5=\