001: /*
002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
003: *
004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
005: *
006: * The contents of this file are subject to the terms of either the GNU
007: * General Public License Version 2 only ("GPL") or the Common
008: * Development and Distribution License("CDDL") (collectively, the
009: * "License"). You may not use this file except in compliance with the
010: * License. You can obtain a copy of the License at
011: * http://www.netbeans.org/cddl-gplv2.html
012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
013: * specific language governing permissions and limitations under the
014: * License. When distributing the software, include this License Header
015: * Notice in each file and include the License file at
016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
017: * particular file as subject to the "Classpath" exception as provided
018: * by Sun in the GPL Version 2 section of the License file that
019: * accompanied this code. If applicable, add the following below the
020: * License Header, with the fields enclosed by brackets [] replaced by
021: * your own identifying information:
022: * "Portions Copyrighted [year] [name of copyright owner]"
023: *
024: * Contributor(s):
025: *
026: * The Original Software is NetBeans. The Initial Developer of the Original
027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
028: * Microsystems, Inc. All Rights Reserved.
029: *
030: * If you wish your version of this file to be governed by only the CDDL
031: * or only the GPL Version 2, indicate your decision by adding
032: * "[Contributor] elects to include this software in this distribution
033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
034: * single choice of license, a recipient has the option to distribute
035: * your version of this file under either the CDDL, the GPL Version 2 or
036: * to extend the choice of license to its licensees as provided above.
037: * However, if you add GPL Version 2 code and therefore, elected the GPL
038: * Version 2 license, then the option applies only if the new code is
039: * made subject to such option by the copyright holder.
040: */
041:
042: package org.netbeans.modules.sql.framework.codegen.oracle9;
043:
044: import java.util.HashMap;
045: import java.util.List;
046: import java.util.Map;
047:
048: import org.netbeans.modules.sql.framework.codegen.AbstractGenerator;
049: import org.netbeans.modules.sql.framework.codegen.StatementContext;
050: import org.netbeans.modules.sql.framework.model.SQLConstants;
051: import org.netbeans.modules.sql.framework.model.SQLGenericOperator;
052: import org.netbeans.modules.sql.framework.model.SQLLiteral;
053: import org.netbeans.modules.sql.framework.model.SQLObject;
054: import org.netbeans.modules.sql.framework.model.SQLOperatorArg;
055: import org.netbeans.modules.sql.framework.model.SQLOperatorDefinition;
056:
057: import com.sun.sql.framework.exception.BaseException;
058: import com.sun.sql.framework.utils.StringUtil;
059:
060: /**
061: * @author Rupesh Ramachandran
062: * @author Ritesh Adval
063: * @version $Revision$
064: */
065: public class Oracle9DateDiffOperatorGenerator extends AbstractGenerator {
066: private static final String SEC_N_MIN_UNIT_CALC = "( ( EXTRACT(SECOND FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $secfact) + "
067: + " ( EXTRACT(MINUTE FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $minfact) + "
068: + " ( EXTRACT(HOUR FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $hrfact) + "
069: + " ( EXTRACT(DAY FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $dayfact) ) ";
070:
071: private static final String HR_UNIT_CALC = "( ( EXTRACT(MINUTE FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $minfact) + "
072: + " ( EXTRACT(HOUR FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $hrfact) + "
073: + " ( EXTRACT(DAY FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $dayfact) ) ";
074:
075: private static final String DAY_UNIT_CALC = "( ( EXTRACT(HOUR FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $hrfact) + "
076: + " ( EXTRACT(DAY FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) DAY TO SECOND) * $dayfact) ) ";
077:
078: private static final String MONTH_N_YEAR_UNIT_CALC = "( (EXTRACT(MONTH FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) YEAR TO MONTH) * $monthfact) + "
079: + " (EXTRACT(YEAR FROM (TO_TIMESTAMP($timestamp1) - TO_TIMESTAMP($timestamp2)) YEAR TO MONTH) * $yearfact) )";
080:
081: private static final String[] SEC_2_DAY_UNIT_CALC_KEYS = new String[] {
082: "secfact", "minfact", "hrfact", "dayfact" };
083:
084: private static final String[] MONTH_N_YEAR_UNIT_CALC_KEYS = new String[] {
085: "monthfact", "yearfact" };
086:
087: private static final String[] SEC_UNIT_CALC_FACT_VALS = new String[] {
088: "1", "60", "3600", // 60 * 60
089: "86400" // 24 * 3600
090: };
091:
092: private static final String[] MIN_UNIT_CALC_FACT_VALS = new String[] {
093: "0.0167", "1", "60", "1440" // 24 * 60
094: };
095:
096: private static final String[] HR_UNIT_CALC_FACT_VALS = new String[] {
097: "0", "0.0167", "1", "24" // 24 * 60
098: };
099:
100: private static final String[] DAY_UNIT_CALC_FACT_VALS = new String[] {
101: "0", "0", "0.0417", "1" // 24 * 60
102: };
103:
104: private static final String[] MONTH_UNIT_CALC_FACT_VALS = new String[] {
105: "1", "12" };
106:
107: private static final String[] YEAR_UNIT_CALC_FACT_VALS = new String[] {
108: "0.084", "1" };
109:
110: public String generate(SQLObject obj, StatementContext context)
111: throws BaseException {
112: String result;
113: SQLGenericOperator operator = (SQLGenericOperator) obj;
114: SQLLiteral literal = (SQLLiteral) operator
115: .getArgumentValue("type");
116: if (literal == null) {
117: throw new BaseException("Failed to evaluate "
118: + operator.getOperatorType()
119: + ", \"type\" is null.");
120: }
121: // get all necessary data.
122: Map params = operator.getSQLObjectMap();
123: String intervalType = literal.getValue();
124: intervalType = intervalType.toLowerCase();
125:
126: // allow overridable operator factory so other db's can override date add
127: SQLOperatorDefinition defn = this .getDB().getOperatorFactory()
128: .getSQLOperatorDefinition(operator.getOperatorType());
129: String script = defn.getScript();
130: String[] factorKeys = null;
131: String[] factorValues = null;
132:
133: //DATEADD: $date + numtodsinterval($interval, $interval_type)
134: //DATEDIFF: extract($interval from $date1 - $date2) day to second
135: //DATEDIFF: extract($interval from $date1 - $date2) year to month
136:
137: if (intervalType.equals("second")) {
138: script = SEC_N_MIN_UNIT_CALC;
139: factorKeys = SEC_2_DAY_UNIT_CALC_KEYS;
140: factorValues = SEC_UNIT_CALC_FACT_VALS;
141: } else if (intervalType.equals("minute")) {
142: script = SEC_N_MIN_UNIT_CALC;
143: factorKeys = SEC_2_DAY_UNIT_CALC_KEYS;
144: factorValues = MIN_UNIT_CALC_FACT_VALS;
145: } else if (intervalType.equals("hour")) {
146: script = HR_UNIT_CALC;
147: factorKeys = SEC_2_DAY_UNIT_CALC_KEYS;
148: factorValues = HR_UNIT_CALC_FACT_VALS;
149: } else if (intervalType.equals("day")
150: || intervalType.equals("week")) {
151: script = DAY_UNIT_CALC;
152: factorKeys = SEC_2_DAY_UNIT_CALC_KEYS;
153: factorValues = DAY_UNIT_CALC_FACT_VALS;
154: } else if (intervalType.equals("month")
155: || intervalType.equals("quarter")) {
156: script = MONTH_N_YEAR_UNIT_CALC;
157: factorKeys = MONTH_N_YEAR_UNIT_CALC_KEYS;
158: factorValues = MONTH_UNIT_CALC_FACT_VALS;
159: } else if (intervalType.equals("year")) {
160: script = MONTH_N_YEAR_UNIT_CALC;
161: factorKeys = MONTH_N_YEAR_UNIT_CALC_KEYS;
162: factorValues = YEAR_UNIT_CALC_FACT_VALS;
163: }
164:
165: Map resolvedparams = new HashMap();
166: List args = defn.getArgList();
167:
168: for (int i = 0; i < args.size(); i++) {
169: String key = ((SQLOperatorArg) args.get(i)).getArgName();
170: SQLObject val = (SQLObject) params.get(key);
171: if (val != null) {
172: String eval = this .getGeneratorFactory().generate(val,
173: context);
174: resolvedparams.put(key, eval);
175: }
176: }
177:
178: if (factorKeys != null) {
179: for (int i = 0; i < factorKeys.length; i++) {
180: resolvedparams.put(factorKeys[i], factorValues[i]);
181: }
182: }
183:
184: result = StringUtil.replace(script, resolvedparams,
185: SQLConstants.OPERATOR_VARIABLE_PREFIX);
186:
187: // replace any other strings as needed
188:
189: if (intervalType.equalsIgnoreCase("week")) {
190: // use week as 7 days
191: result = "ROUND(" + result + " / 7, 3)";
192: } else if (intervalType.equalsIgnoreCase("quarter")) {
193: // use quarter as 3 months
194: result = "ROUND(" + result + " /3, 3)";
195: }
196:
197: return result;
198: }
199: }
|