001: /*
002: * SQLeonardo :: java database frontend
003: * Copyright (C) 2004 nickyb@users.sourceforge.net
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
007: * as published by the Free Software Foundation; either version 2
008: * of the License, or (at your option) any later version.
009: *
010: * This program is distributed in the hope that it will be useful,
011: * but WITHOUT ANY WARRANTY; without even the implied warranty of
012: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
013: * GNU General Public License for more details.
014: *
015: * You should have received a copy of the GNU General Public License
016: * along with this program; if not, write to the Free Software
017: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
018: *
019: */
020:
021: package nickyb.sqleonardo.querybuilder.syntax;
022:
023: import java.sql.Types;
024:
025: import nickyb.sqleonardo.querybuilder.QueryBuilder;
026:
027: public class SQLFormatter implements _ReservedWords {
028: public static final char BREAK = '\n';
029: public static final char COMMA = ',';
030: public static final char DOT = '.';
031: public static final char SPACE = ' ';
032:
033: private static final String INDENT = " ";
034:
035: public static String concatCommaDelimited(Object tokens[],
036: boolean wrap) {
037: if (tokens.length == 0)
038: return "<empty>";
039:
040: String delimiter = String.valueOf(COMMA)
041: + (wrap ? String.valueOf(BREAK) : String.valueOf(SPACE));
042: StringBuffer buffer = new StringBuffer();
043:
044: for (int i = 0; i < tokens.length; i++) {
045: if (wrap)
046: buffer.append(INDENT);
047: buffer.append(tokens[i] + delimiter);
048: }
049:
050: return buffer
051: .substring(0, buffer.length() - delimiter.length());
052: }
053:
054: public static String concat(QueryTokens._Expression tokens[],
055: boolean wrap) {
056: return concatCommaDelimited(tokens, wrap);
057: }
058:
059: public static String concat(QueryTokens.Group tokens[], boolean wrap) {
060: return concatCommaDelimited(tokens, wrap);
061: }
062:
063: public static String concat(QueryTokens.Sort tokens[], boolean wrap) {
064: return concatCommaDelimited(tokens, wrap);
065: }
066:
067: public static String concat(QueryTokens.Condition tokens[],
068: boolean wrap) {
069: if (tokens.length == 0)
070: return "<empty>";
071:
072: String delimiter = (wrap ? String.valueOf(BREAK) : String
073: .valueOf(SPACE));
074: StringBuffer buffer = new StringBuffer();
075:
076: for (int i = 0; i < tokens.length; i++) {
077: if (wrap) {
078: String indentation = INDENT;
079:
080: if (tokens[i].toString().startsWith("AND "))
081: indentation = " ";
082: else if (tokens[i].toString().startsWith("OR "))
083: indentation = " ";
084:
085: buffer.append(indentation);
086: }
087: buffer.append(tokens[i] + delimiter);
088: }
089:
090: return buffer
091: .substring(0, buffer.length() - delimiter.length());
092: }
093:
094: public static String concat(QueryTokens._TableReference tokens[],
095: boolean wrap) {
096: if (tokens.length == 0)
097: return "<empty>";
098:
099: String delimiter = (wrap ? String.valueOf(BREAK) + INDENT
100: : String.valueOf(SPACE));
101: StringBuffer buffer = new StringBuffer();
102:
103: sort(tokens);
104:
105: for (int i = 0; i < tokens.length; i++) {
106: if (tokens[i] instanceof QueryTokens.Join) {
107: QueryTokens.Join token = (QueryTokens.Join) tokens[i];
108:
109: QueryTokens.Column cL = token.getPrimary();
110: QueryTokens.Column cR = token.getForeign();
111:
112: boolean bLeft = buffer.toString().indexOf(
113: cL.getTable().toString() + SPACE) == 0
114: || buffer.toString().indexOf(
115: SPACE + cL.getTable().toString()
116: + SPACE) > 0;
117: boolean bRight = buffer.toString().indexOf(
118: cR.getTable().toString() + SPACE) == 0
119: || buffer.toString().indexOf(
120: SPACE + cR.getTable().toString()
121: + SPACE) > 0;
122:
123: if (!bLeft && !bRight) {
124: if (buffer.length() > 0)
125: buffer.insert(buffer.toString().lastIndexOf(
126: delimiter), COMMA);
127: else if (wrap)
128: buffer.append(INDENT);
129:
130: buffer.append(token.toString() + delimiter);
131: } else if (bLeft && bRight) {
132: buffer.append(_ReservedWords.AND + SPACE
133: + token.getCondition().toString()
134: + delimiter);
135: } else {
136: if (!bLeft) {
137: if (token.getType() == QueryTokens.Join.LEFT_OUTER)
138: token.setType(QueryTokens.Join.RIGHT_OUTER);
139: else if (token.getType() == QueryTokens.Join.RIGHT_OUTER)
140: token.setType(QueryTokens.Join.LEFT_OUTER);
141:
142: token.getCondition().setLeft(cR);
143: token.getCondition().setRight(cL);
144:
145: cR = cL;
146: }
147: buffer.append(token.getTypeName() + SPACE
148: + cR.getTable().toString() + SPACE
149: + _ReservedWords.ON + SPACE
150: + token.getCondition() + delimiter);
151: }
152: }
153: }
154:
155: for (int i = 0; i < tokens.length; i++) {
156: if (tokens[i] instanceof QueryTokens.Table) {
157: if (buffer.length() > 0)
158: buffer.insert(buffer.toString().lastIndexOf(
159: delimiter), COMMA);
160: else if (wrap)
161: buffer.append(INDENT);
162:
163: buffer.append(tokens[i] + delimiter);
164: }
165: }
166:
167: return buffer.length() > 0 ? buffer.substring(0, buffer
168: .length()
169: - delimiter.length()) : "<empty>";
170: }
171:
172: private static void sort(QueryTokens._TableReference tokens[]) {
173: for (int i = 0; i < tokens.length - 1; i++) {
174: if (tokens[i] instanceof QueryTokens.Join) {
175: QueryTokens.Join joinI = (QueryTokens.Join) tokens[i];
176: for (int j = i + 1, k = i; j < tokens.length; j++) {
177: if (tokens[j] instanceof QueryTokens.Join) {
178: QueryTokens.Join joinJ = (QueryTokens.Join) tokens[j];
179:
180: if ((joinI.getPrimary().getTable()
181: .getReference().equals(
182: joinJ.getPrimary().getTable()
183: .getReference()) && joinI
184: .getForeign().getTable().getReference()
185: .equals(
186: joinJ.getForeign().getTable()
187: .getReference()))
188: || (joinI
189: .getPrimary()
190: .getTable()
191: .getReference()
192: .equals(
193: joinJ.getForeign()
194: .getTable()
195: .getReference()) && joinI
196: .getForeign()
197: .getTable()
198: .getReference()
199: .equals(
200: joinJ.getPrimary()
201: .getTable()
202: .getReference()))) {
203: moveUp(tokens, j, i + 1);
204: k++;
205: } else if (joinI.getPrimary().getTable()
206: .getReference().equals(
207: joinJ.getPrimary().getTable()
208: .getReference())
209: || joinI
210: .getForeign()
211: .getTable()
212: .getReference()
213: .equals(
214: joinJ.getForeign()
215: .getTable()
216: .getReference())
217: || joinI
218: .getPrimary()
219: .getTable()
220: .getReference()
221: .equals(
222: joinJ.getForeign()
223: .getTable()
224: .getReference())
225: || joinI
226: .getForeign()
227: .getTable()
228: .getReference()
229: .equals(
230: joinJ.getPrimary()
231: .getTable()
232: .getReference())) {
233: moveUp(tokens, j, ++k);
234: }
235: }
236: }
237: }
238: }
239: }
240:
241: private static void moveUp(Object tokens[], int idxOld, int idxNew) {
242: Object token = tokens[idxOld];
243: for (int i = idxOld - 1; i >= idxNew; i--) {
244: tokens[i + 1] = tokens[i];
245: }
246: tokens[idxNew] = token;
247: }
248:
249: public static String ensureQuotes(String identifier,
250: boolean asNeeded) {
251: return ensureQuotes(identifier,
252: QueryBuilder.identifierQuoteString, asNeeded);
253: }
254:
255: public static String ensureQuotes(String identifier,
256: String quoteString, boolean asNeeded) {
257: if (quoteString.equals(String.valueOf(SPACE)))
258: return identifier;
259:
260: /* clean quote chars */
261: if (identifier.startsWith(quoteString))
262: identifier = identifier.substring(quoteString.length());
263: if (identifier.endsWith(quoteString))
264: identifier = identifier.substring(0, identifier.length()
265: - quoteString.length());
266: for (int i; (i = identifier.indexOf(quoteString)) != -1;) {
267: String left = identifier.substring(0, i);
268: String right = identifier.substring(i + 1);
269:
270: identifier = left + right;
271: }
272:
273: /* put quote chars */
274: StringBuffer quoted = new StringBuffer(identifier);
275: if (asNeeded) {
276: int iQuote = 0;
277: int iSpace = 0;
278: while ((iSpace = quoted.toString().indexOf(SPACE, iQuote)) != -1) {
279: int iDot = quoted.toString().indexOf(DOT, iQuote);
280: if (iDot == -1) {
281: quoted.insert(iQuote, quoteString);
282: quoted.append(quoteString);
283: break;
284: } else if (iSpace < iDot) {
285: quoted.insert(iQuote, quoteString);
286: quoted.insert(++iDot, quoteString);
287: iQuote = iDot + 2;
288: } else {
289: iQuote = iDot + 1;
290: }
291: }
292: } else {
293: quoted.insert(0, quoteString);
294: quoted.append(quoteString);
295:
296: if (identifier.indexOf(DOT) != -1) {
297: int point = identifier.indexOf(DOT);
298: quoted.insert(point + 1, quoteString);
299: quoted.insert(point + 3, quoteString);
300: }
301: }
302:
303: return quoted.toString();
304: }
305:
306: public static String toJdbcValue(Object value, int sqltype) {
307: if (value == null)
308: return "null";
309:
310: switch (sqltype) {
311: case Types.CHAR:
312: case Types.VARCHAR:
313: return "'" + value.toString() + "'";
314: case Types.DATE:
315: return "{d '" + value.toString() + "'}";
316: case Types.TIME:
317: return "{t '" + value.toString() + "'}";
318: case Types.TIMESTAMP:
319: return "{ts '" + value.toString() + "'}";
320: default:
321: return value.toString();
322: }
323: }
324: }
|