You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by ev...@apache.org on 2009/12/31 09:20:40 UTC
svn commit: r894792 - in
/cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src:
main/java/org/apache/cayenne/access/QueryFormatter.java
main/java/org/apache/cayenne/access/QueryLogger.java
test/java/org/apache/cayenne/access/QueryFormatterTest.java
Author: evgeny
Date: Thu Dec 31 08:20:39 2009
New Revision: 894792
URL: http://svn.apache.org/viewvc?rev=894792&view=rev
Log:
CAY-1300 Format queries in QueryLogger
Add QueryFormatter and allow to enable it with -Dcayenne.query.formatting=true option
Added:
cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryFormatter.java
cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/access/QueryFormatterTest.java
Modified:
cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryLogger.java
Added: cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryFormatter.java
URL: http://svn.apache.org/viewvc/cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryFormatter.java?rev=894792&view=auto
==============================================================================
--- cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryFormatter.java (added)
+++ cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryFormatter.java Thu Dec 31 08:20:39 2009
@@ -0,0 +1,114 @@
+/*****************************************************************
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ ****************************************************************/
+package org.apache.cayenne.access;
+
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.Map;
+import java.util.TreeMap;
+
+/**
+ * QueryFormatter is utility class for formatting queries.
+ */
+public final class QueryFormatter {
+
+ private QueryFormatter() {
+ // no instances
+ }
+
+ private final static Map<String, String> KEY_WORDS = new HashMap<String, String>();
+
+ static {
+ KEY_WORDS.put(" select ", "SELECT");
+ KEY_WORDS.put(" from ", "FROM");
+ KEY_WORDS.put(" where ", "WHERE");
+ KEY_WORDS.put(" order by ", "ORDER BY");
+ KEY_WORDS.put(" group by ", "GROUP BY");
+ KEY_WORDS.put(" update ", "UPDATE");
+ KEY_WORDS.put(" exec ", "EXEC");
+ KEY_WORDS.put(" set ", "SET");
+ KEY_WORDS.put(" insert ", "INSERT");
+ KEY_WORDS.put(" values ", "VALUES");
+ KEY_WORDS.put(" delete ", "DELETE");
+ KEY_WORDS.put(" declare ", "DECLARE");
+ KEY_WORDS.put(" case ", "CASE");
+ }
+
+ public static String formatQuery(String sql) {
+ Map<Integer, String> scanResult = scanQuery(sql);
+ Iterator<Integer> iter = scanResult.keySet().iterator();
+ int nextKeyIdx = (iter.hasNext()) ? iter.next() : -1;
+
+ StringBuffer buffer = new StringBuffer();
+ int apixCount = 0;
+ int bufferPos = 0;
+ for (int pos = 0; pos < sql.length(); pos++) {
+ if (sql.charAt(pos) == '\'') {
+ apixCount++;
+ if (pos > 0 && sql.charAt(pos - 1) == '\'')
+ apixCount = apixCount - 2;
+ }
+ if (apixCount % 2 != 0) {
+ continue;
+ }
+ if (pos == nextKeyIdx) {
+ buffer.append(sql.substring(bufferPos, pos + 1));
+ buffer.append("\n");
+ String shiftedKeyWrd = scanResult.get(nextKeyIdx);
+ nextKeyIdx = (iter.hasNext()) ? iter.next() : -1;
+ buffer.append(shiftedKeyWrd);
+ pos = pos + shiftedKeyWrd.length();
+ bufferPos = pos + 1;
+ }
+ else if (sql.charAt(pos) == ','
+ || sql.charAt(pos) == ')'
+ || sql.charAt(pos) == '(') {
+ buffer.append(sql.substring(bufferPos, pos + 1));
+ buffer.append("\n\t");
+ bufferPos = pos + 1;
+ }
+ }
+ buffer.append(sql.substring(bufferPos));
+ buffer.append("\n");
+ String result = buffer.toString();
+ while (result.contains(" ")) {
+ result = result.replaceAll(" ", " ");
+ }
+ return result;
+ }
+
+ private static Map<Integer, String> scanQuery(String sql) {
+ Map<Integer, String> result = new TreeMap<Integer, String>();
+ String sql2Lower = sql.toLowerCase();
+ for (String keyWrd : KEY_WORDS.keySet()) {
+ int prevIdx = 0;
+ while (true) {
+ int idx = sql2Lower.indexOf(keyWrd, prevIdx);
+ if (idx >= 0) {
+ result.put(idx, KEY_WORDS.get(keyWrd));
+ prevIdx = idx + 1;
+ }
+ else {
+ break;
+ }
+ }
+ }
+ return result;
+ }
+}
Modified: cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryLogger.java
URL: http://svn.apache.org/viewvc/cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryLogger.java?rev=894792&r1=894791&r2=894792&view=diff
==============================================================================
--- cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryLogger.java (original)
+++ cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/main/java/org/apache/cayenne/access/QueryLogger.java Thu Dec 31 08:20:39 2009
@@ -49,6 +49,16 @@
public static final int TRIM_VALUES_THRESHOLD = 30;
+ private static boolean useQueryFormatting = false;
+
+ static {
+ // here we are enabling QueryFormatter
+ String useFormattingProp = System.getProperty("cayenne.query.formatting");
+ if ("TRUE".equalsIgnoreCase(useFormattingProp)) {
+ useQueryFormatting = true;
+ }
+ }
+
/**
* @since 1.2
*/
@@ -346,7 +356,8 @@
*/
public static void logQuery(String queryStr, List<DbAttribute> attrs, List<?> params, long time) {
if (isLoggable()) {
- StringBuffer buf = new StringBuffer(queryStr);
+ StringBuffer buf = new StringBuffer((useQueryFormatting) ?
+ QueryFormatter.formatQuery(queryStr) : queryStr);
buildLog(buf, " [bind: ", "]", attrs, params, isInserting(queryStr));
// log preparation time only if it is something significant
Added: cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/access/QueryFormatterTest.java
URL: http://svn.apache.org/viewvc/cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/access/QueryFormatterTest.java?rev=894792&view=auto
==============================================================================
--- cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/access/QueryFormatterTest.java (added)
+++ cayenne/main/trunk/framework/cayenne-jdk1.5-unpublished/src/test/java/org/apache/cayenne/access/QueryFormatterTest.java Thu Dec 31 08:20:39 2009
@@ -0,0 +1,71 @@
+/*****************************************************************
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ ****************************************************************/
+package org.apache.cayenne.access;
+
+import junit.framework.TestCase;
+
+public class QueryFormatterTest extends TestCase {
+
+ /**
+ * Check if we have lose some words after formatting.
+ */
+ public void testQueryFormattingNotLosingWords() {
+
+ String notFormattedQuery = "declare @Amount MONEY, @BatchBrief BRIEFNAME, "
+ + " @BatchID IDENTIFIER, @BranchBrief ACCNUMBER, @BranchID IDENTIFIER, "
+ + " @Comment COMMENT, @CurrencyID IDENTIFIER, @CurrencyNumber BRIEFNAME, "
+ + " @DateEnd OPERDAY, @DateOpen OPERDAY, @DateStart OPERDAY, "
+ + " @DepositProductBrief BRIEFNAME, @DepositProductID IDENTIFIER,"
+ + " @GiverBrief USERNAME, @GiverID IDENTIFIER, @InterestRateValue FLOAT, "
+ + " @Number NUMBER20, @OwnerAgentBrief USERNAME, @OwnerAgentID IDENTIFIER,"
+ + " @OwnerBrief USERNAME, @OwnerID IDENTIFIER, @TermDay INT_KEY, "
+ + " @TermDepositID IDENTIFIER, @TermID IDENTIFIER, @TermMonth INT_KEY, "
+ + " @UserFIOBrief USERNAME, @UserID IDENTIFIER, "
+ + "@ReturnCode IDENTIFIER select @Amount = ?, "
+ + " @BatchBrief = ?, @BatchID = ?, @BranchBrief = ?, "
+ + " @BranchID = ?, @Comment = ?, @CurrencyID = ?, @CurrencyNumber = ?, "
+ + " @DateEnd = ?, @DateOpen = ?, @DateStart = ?, @DepositProductBrief = ?, "
+ + " @DepositProductID = ?, @GiverBrief = ?, @GiverID = ?, @InterestRateValue = ?, "
+ + " @Number = ?, @OwnerAgentBrief = ?, @OwnerAgentID = ?, @OwnerBrief = ?, "
+ + " @OwnerID = ?, @TermDay = ?, @TermID = ?, @TermMonth = ?, @UserFIOBrief = ?,"
+ + " @UserID = ?, @ReturnCode = -1 exec @ReturnCode = MY_STORED_PROCEDURE @Amount "
+ + " = @Amount , @BatchBrief = @BatchBrief , @BatchID = @BatchID , @BranchBrief "
+ + " = @BranchBrief , @BranchID = @BranchID , @Comment = @Comment , @CurrencyID "
+ + " = @CurrencyID , @CurrencyNumber = @CurrencyNumber , @DateEnd = @DateEnd ,"
+ + " @DateOpen = @DateOpen , @DateStart = @DateStart , @DepositProductBrief ="
+ + " @DepositProductBrief , @DepositProductID = @DepositProductID , @GiverBrief = "
+ + " @GiverBrief , @GiverID = @GiverID , @InterestRateValue = @InterestRateValue "
+ + " , @Number = @Number , @OwnerAgentBrief = @OwnerAgentBrief , @OwnerAgentID "
+ + " = @OwnerAgentID , @OwnerBrief = @OwnerBrief , @OwnerID = @OwnerID , "
+ + " @TermDay = @TermDay , @TermDepositID = @TermDepositID out , @TermID = "
+ + " @TermID , @TermMonth = @TermMonth , @UserFIOBrief = @UserFIOBrief ,"
+ + " @UserID = @UserID select @TermDepositID AS TermDepositID, "
+ + " @ReturnCode AS ReturnCode";
+ String[] wordsNFQ = notFormattedQuery.split("\\s+");
+ String formattedQuery = QueryFormatter.formatQuery(notFormattedQuery);
+ String[] wordsFQ = formattedQuery.split("\\s+");
+ assertEquals(wordsNFQ.length, wordsFQ.length);
+ for (int i = 0; i < wordsNFQ.length; i++) {
+ assertTrue(formattedQuery.contains(wordsNFQ[i])
+ || formattedQuery.contains(wordsNFQ[i].toUpperCase()));
+ }
+ System.out.println(formattedQuery);
+ }
+
+}