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 java.util.TreeMap;
023:
024: /**
025: * This type was created in VisualAge.
026: */
027: class DSDataSourcePostGres extends DSDataSourceJDBC {
028:
029: public String getAliaseTableName(DataStore ds, String aliasName) {
030: String retString = null;
031: int no = ds.getAliasCount();
032: DSDataStoreDescriptor desc = ds.getDescriptor();
033: for (int i = 0; i < no; i++) {
034: DSTableAliasDescriptor d = desc.getAlias(i);
035: if (aliasName.equals(d.getAlias())) {
036: String table = d.getTable();
037: retString = table + " as " + aliasName;
038: }
039: }
040: if (retString == null)
041: retString = aliasName;
042: return retString;
043: }
044:
045: /**
046: * This method was created in VisualAge.
047: * @return java.lang.String
048: * @param ds com.salmonllc.sql.DataStore
049: */
050: public String generateSelect(DataStore ds, String criteria,
051: boolean countOnly) throws DataStoreException {
052: //build the column list
053: StringBuffer colList = new StringBuffer();
054: colList.append("SELECT ");
055: if (ds.getDistinct())
056: colList.append("DISTINCT ");
057:
058: if (countOnly)
059: colList.append("count(*)");
060: else {
061: for (int i = 0; i < ds.getColumnCount(); i++) {
062: String dbName = ds.getColumnDatabaseName(i);
063: if (dbName != null) {
064: colList.append(dbName);
065: colList.append(",");
066: }
067: }
068: colList.setCharAt(colList.length() - 1, ' ');
069: }
070:
071: //build the from clause
072: StringBuffer fromClause = new StringBuffer();
073: fromClause.append(" FROM ");
074: TreeMap tmap = new TreeMap();
075:
076: if (ds.getJoinCount() > 0) {
077: for (int i = 0; i < ds.getJoinCount(); i++) {
078: String LeftTable = ds.getJoinLeftColumn(i, 0);
079: String RightTable = ds.getJoinRightColumn(i, 0);
080: String Table = null;
081:
082: int pos = LeftTable.indexOf(".");
083: if (pos > -1)
084: LeftTable = LeftTable.substring(0, pos);
085: pos = RightTable.indexOf(".");
086: if (pos > -1)
087: RightTable = RightTable.substring(0, pos);
088:
089: if (i == 0) {
090: fromClause
091: .append(getAliaseTableName(ds, LeftTable));
092: tmap.put(LeftTable, "1");
093:
094: }
095: if (ds.getJoinOuter(i))
096: fromClause.append(" left outer join ");
097: else
098: fromClause.append(" inner join ");
099:
100: if (!tmap.containsKey(LeftTable)) {
101: Table = LeftTable;
102: tmap.put(Table, "1");
103: }
104: if (!tmap.containsKey(RightTable)) {
105: Table = RightTable;
106: tmap.put(Table, "1");
107: }
108: fromClause.append(getAliaseTableName(ds, Table));
109: fromClause.append(" on (");
110:
111: for (int j = 0; j < ds.getJoinColumnCount(i); j++) {
112: String LeftColumn = ds.getJoinLeftColumn(i, j);
113: String RightColumn = ds.getJoinRightColumn(i, j);
114:
115: if (j > 0)
116: fromClause.append(" AND ");
117:
118: fromClause.append(LeftColumn);
119: fromClause.append(" = ");
120: fromClause.append(RightColumn);
121: }
122: fromClause.append(" ) ");
123: }
124: } else {
125: if (ds.getDefaultTable() != null)
126: fromClause.append(ds.getDefaultTable() + ",");
127: for (int i = 0; i < ds.getAliasCount(); i++) {
128: fromClause.append(ds.getTable(i));
129: if (ds.getAlias(i) != null)
130: fromClause.append(" " + ds.getAlias(i));
131: fromClause.append(",");
132: }
133: }
134:
135: fromClause.setCharAt(fromClause.length() - 1, ' ');
136:
137: //build the where clause
138: StringBuffer whereClause = new StringBuffer();
139: // fc: 07/18/02 Added check for empty criteria to stop a bad sql statement from being generated.
140: if (criteria != null && !criteria.trim().equals("")
141: && ds.getCriteria() != null
142: && !ds.getCriteria().trim().equals(""))
143: criteria = "(" + criteria + ") AND (" + ds.getCriteria()
144: + ")";
145: else if (criteria == null)
146: criteria = ds.getCriteria();
147:
148: if (criteria != null)
149: if (criteria.trim().equals(""))
150: criteria = "";
151:
152: /*BALU
153: if (criteria != null || ds.getJoinCount() > 0) {
154: whereClause.append(" WHERE ");
155: if (ds.getJoinCount() > 0) {
156: whereClause.append("(");
157: for (int i = 0; i < ds.getJoinCount(); i ++) {
158: for (int j = 0; j < ds.getJoinColumnCount(i);j++) {
159: whereClause.append(ds.getJoinLeftColumn(i,j));
160: whereClause.append('=');
161: whereClause.append(ds.getJoinRightColumn(i,j));
162: if (ds.getJoinOuter(i))
163: whereClause.append("(+)");
164: if (j < (ds.getJoinColumnCount(i) - 1))
165: whereClause.append(" AND ");
166: }
167: if (i < (ds.getJoinCount() - 1))
168: whereClause.append(" AND ");
169: }
170: whereClause.append(")");
171: if (criteria != null)
172: whereClause.append(" AND (" + criteria + ")");
173: }
174: else if (criteria != null)
175: whereClause.append(criteria);
176: }
177: */
178: if (criteria != null)
179: whereClause.append(" WHERE " + criteria);
180:
181: //finish it up and return
182: String retVal = colList.toString() + fromClause.toString()
183: + whereClause.toString();
184:
185: if (ds.getGroupBy() != null)
186: retVal += " GROUP BY " + ds.getGroupBy();
187:
188: if (ds.getHaving() != null)
189: retVal += " HAVING " + ds.getHaving();
190:
191: if (ds.getOrderBy() != null && !countOnly)
192: retVal += " ORDER BY " + ds.getOrderBy();
193:
194: return retVal;
195:
196: }
197: }
|