001: //** Copyright Statement ***************************************************
002: //The Salmon Open Framework for Internet Applications (SOFIA)
003: // Copyright (C) 1999 - 2002, Salmon LLC
004: //
005: // This program is free software; you can redistribute it and/or
006: // modify it under the terms of the GNU General Public License version 2
007: // as published by the Free Software Foundation;
008: //
009: // This program is distributed in the hope that it will be useful,
010: // but WITHOUT ANY WARRANTY; without even the implied warranty of
011: // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: // GNU General Public License for more details.
013: //
014: // You should have received a copy of the GNU General Public License
015: // along with this program; if not, write to the Free Software
016: // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: //
018: // For more information please visit http://www.salmonllc.com
019: //** End Copyright Statement ***************************************************
020: package com.salmonllc.sql;
021:
022: import com.salmonllc.util.MessageLog;
023:
024: import java.sql.*;
025:
026: /**
027: * DataStore driver for Ansi SQL 92 databases.
028: */
029: public class DSDataSourceANSISQL92 extends DSDataSourceJDBC {
030:
031: public String generateSelect(DataStore ds, String criteria,
032: boolean countOnly) throws DataStoreException {
033: //build the column list
034: StringBuffer colList = new StringBuffer();
035: colList.append("SELECT ");
036: if (ds.getDistinct())
037: colList.append("DISTINCT ");
038: if (countOnly)
039: colList.append("count(*)");
040: else {
041: for (int i = 0; i < ds.getColumnCount(); i++) {
042: String databaseName = ds.getColumnDatabaseName(i);
043: if (databaseName != null) {
044: colList.append(databaseName);
045: colList.append(",");
046: }
047: }
048: colList.setCharAt(colList.length() - 1, ' ');
049: }
050:
051: //build the join portion of the from clause
052: StringBuffer joinClause = new StringBuffer();
053: boolean aliasUsed[] = new boolean[ds.getAliasCount() + 1];
054: for (int i = 0; i < ds.getJoinCount(); i++) {
055: String join = " inner join ";
056: if (ds.getJoinOuter(i))
057: join = " left outer join ";
058: String leftTable = ds.getJoinLeftColumn(i, 0);
059: String rightTable = ds.getJoinRightColumn(i, 0);
060: int pos = leftTable.indexOf(".");
061: if (pos > -1)
062: leftTable = leftTable.substring(0, pos);
063: pos = rightTable.indexOf(".");
064: if (pos > -1)
065: rightTable = rightTable.substring(0, pos);
066: int index = findAlias(ds, leftTable);
067: if (index > -1) {
068: if (aliasUsed[index])
069: leftTable = "";
070: else {
071: if (ds.getAlias(index) != null)
072: leftTable = ds.getTable(index) + " "
073: + leftTable;
074:
075: }
076: aliasUsed[index] = true;
077: }
078: index = findAlias(ds, rightTable);
079: if (index > -1) {
080: if (aliasUsed[index] && !ds.getJoinOuter(i)) {
081: if (leftTable.length() > 0) {
082: rightTable = leftTable;
083: leftTable = "";
084: }
085: } else {
086: if (ds.getAlias(index) != null)
087: rightTable = ds.getTable(index) + " "
088: + rightTable;
089: }
090: aliasUsed[index] = true;
091: }
092: joinClause.append(" " + leftTable + join + rightTable
093: + " ON ");
094: for (int j = 0; j < ds.getJoinColumnCount(i); j++) {
095: if (j > 0)
096: joinClause.append(" AND ");
097: joinClause.append(ds.getJoinLeftColumn(i, j) + " = "
098: + ds.getJoinRightColumn(i, j));
099: }
100: }
101: StringBuffer fromClause = new StringBuffer();
102: fromClause.append(" FROM ");
103: if ((ds.getDefaultTable() != null)
104: && (!aliasUsed[ds.getAliasCount() + 1]))
105: fromClause.append(ds.getDefaultTable() + ",");
106: for (int i = 0; i < ds.getAliasCount(); i++) {
107: if (!aliasUsed[i]) {
108: fromClause.append(ds.getTable(i));
109: if (ds.getAlias(i) != null)
110: fromClause.append(" " + ds.getAlias(i));
111: fromClause.append(",");
112: }
113: }
114: if (fromClause.length() > 0 && joinClause.length() == 0)
115: fromClause.setCharAt(fromClause.length() - 1, ' ');
116: fromClause.append(joinClause);
117:
118: //build the where clause
119:
120: StringBuffer whereClause = new StringBuffer();
121: // fc: 07/18/02 Added check for empty criteria to stop a bad sql statement from being generated.
122: if (criteria != null && !criteria.trim().equals("")
123: && ds.getCriteria() != null
124: && !ds.getCriteria().trim().equals(""))
125: criteria = "(" + criteria + ") AND (" + ds.getCriteria()
126: + ")";
127: else if (criteria == null)
128: criteria = ds.getCriteria();
129: if (criteria != null)
130: if (!criteria.trim().equals(""))
131: whereClause.append(" WHERE " + criteria);
132:
133: //finish it up and return
134: String retVal = colList.toString() + fromClause.toString()
135: + whereClause.toString();
136: if (ds.getGroupBy() != null)
137: retVal += " GROUP BY " + ds.getGroupBy();
138: if (ds.getHaving() != null)
139: retVal += " HAVING " + ds.getHaving();
140: if (ds.getOrderBy() != null && !countOnly)
141: retVal += " ORDER BY " + ds.getOrderBy();
142: return retVal;
143:
144: }
145:
146: private int findAlias(DataStore ds, String table) {
147: try {
148: int count = ds.getAliasCount();
149:
150: for (int i = 0; i < count; i++) {
151: if (ds.getAlias(i) != null)
152: if (ds.getAlias(i).equalsIgnoreCase(table))
153: return i;
154:
155: if (ds.getTable(i).equalsIgnoreCase(table))
156: return i;
157: }
158:
159: if (ds.getDefaultTable() != null) {
160: if (ds.getDefaultTable().equalsIgnoreCase(table))
161: return count;
162: }
163: } catch (Exception e) {
164: }
165:
166: return -1;
167: }
168:
169: protected void populateAutoIncrementValue(DataStoreRow row,
170: DBConnection conn, int colNo) {
171: try {
172: Statement st = conn.createStatement();
173: ResultSet r = st.executeQuery("select LAST_INSERT_ID()");
174: if (r.next()) {
175: int colStat = row.getDSDataRow().getColumnStatus(colNo);
176: int rowStat = row.getDSDataRow().getRowStatus();
177: Object val = null;
178: if (row.getDataType(colNo) == DataStore.DATATYPE_LONG)
179: val = new Long(r.getLong(1));
180: else if (row.getDataType(colNo) == DataStore.DATATYPE_INT)
181: val = new Integer(r.getInt(1));
182: else if (row.getDataType(colNo) == DataStore.DATATYPE_SHORT)
183: val = new Short(r.getShort(1));
184:
185: row.setData(colNo, val);
186: row.getDSDataRow().setColumnStatus(colNo, colStat);
187: row.getDSDataRow().setRowStatus(rowStat);
188: }
189: r.close();
190: st.close();
191: } catch (Exception e) {
192: MessageLog.writeErrorMessage(
193: "Error getting last auto increment value", e, this);
194: }
195: }
196: }
|