Source code: nectar/data/mysql/MysqlQueryRenderer.java
1 /*
2 Copyright (C) 2003 Kai Schutte
3
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU General Public License as published by
6 the Free Software Foundation; either version 2 of the License, or
7 (at your option) any later version.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program; if not, write to the Free Software
16 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
17
18 * MysqlQueryRenderer.java
19 *
20 * Created on March 13, 2003, 12:33 PM
21 */
22
23 package nectar.data.mysql;
24
25 import nectar.data.*;
26 import java.util.Vector;
27 import java.util.Iterator;
28 import java.util.Collection;
29
30 /**
31 *
32 * @author Administrator
33 */
34 public class MysqlQueryRenderer {
35
36 private String commaList(Collection coll) {
37 String result = new String();
38 for (Iterator iter = coll.iterator(); iter.hasNext();) {
39 result += (String)iter.next();
40 if (iter.hasNext())
41 result += ", ";
42 else
43 result += " ";
44 }
45 return result;
46 }
47
48
49 public String renderSelect(Query q) {
50 MatchConditions mcs = q.getMatchConditions();
51
52 String query = "SELECT ";
53 query += commaList(q.getFields());
54 if (mcs != null) {
55 query += ","+render(mcs)+" as "+mcs.getReturnField()+" ";
56 }
57 query += "FROM ";
58 query += commaList(q.getTables());
59 if (q.getDataConditions() != null) {
60 query += "WHERE (";
61 query += render(q.getDataConditions());
62 if (mcs != null) {
63 query += " AND "+render(mcs);
64 }
65 query += ")";
66 }
67 if (q.getPostElements() != null) {
68 query += render(q.getPostElements());
69 }
70 return query;
71 }
72
73 private String render(DataConditions dcs) {
74 String result = "(";
75 Iterator iter = dcs.getListIterator();
76 while (iter.hasNext()) {
77 Object elm = iter.next();
78 if (elm instanceof Byte) {
79 byte op = ((Byte)elm).byteValue();
80 if (op == DataConditions.AND) {
81 result += " AND ";
82 } else if (op == DataConditions.OR) {
83 result += " OR ";
84 } else if (op == DataConditions.NOT) {
85 result += " NOT ";
86 }
87 } else if (elm instanceof DataConditions) {
88 result += render((DataConditions)elm);
89 } else {
90 result += render((DataCondition)elm);
91 }
92 }
93 result += ")";
94 return result;
95 }
96
97 private String render(DataCondition dc) {
98 if (dc instanceof FunctionCondition) {
99 return render((FunctionCondition) dc);
100 } else if (dc instanceof OperatorCondition) {
101 return render((OperatorCondition) dc);
102 } else {
103 return new String();
104 }
105 }
106
107 private String render(OperatorCondition oc) {
108 String result = new String();
109 ConditionElement ce = oc.getLeftElement();
110 result += render(ce);
111 if (oc.getOperator() == OperatorCondition.EQUAL)
112 result += " = ";
113 else if (oc.getOperator() == OperatorCondition.NOT_EQUAL)
114 result += " != ";
115 else if (oc.getOperator() == OperatorCondition.GREATER)
116 result += " > ";
117 else if (oc.getOperator() == OperatorCondition.GREATER_OR_EQUAL)
118 result += " >= ";
119 else if (oc.getOperator() == OperatorCondition.LIKE)
120 result += " LIKE ";
121 else if (oc.getOperator() == OperatorCondition.SMALLER)
122 result += " < ";
123 else if (oc.getOperator() == OperatorCondition.SMALLER_OR_EQUAL)
124 result += " <= ";
125 result += render(oc.getRightElement());
126 return result;
127 }
128
129 private String render(FunctionCondition fc) {
130 String arguments = new String();
131 ConditionElement[] array = fc.getConditionElements();
132 for (int i=0; i<array.length; i++) {
133 arguments += render(array[i]);
134 if (i + 1 < array.length)
135 arguments += ",";
136 }
137 if (fc.getFunctionType() == FunctionCondition.ISNULL) {
138 return "ISNULL("+arguments+")";
139 } else {
140 return new String();
141 }
142 }
143
144 private String render(ConditionElement ce) {
145 if (ce instanceof FieldElement) {
146 return render((FieldElement) ce);
147 } else if (ce instanceof FunctionElement) {
148 return render((FunctionElement) ce);
149 } else if (ce instanceof ValueElement) {
150 return render((ValueElement) ce);
151 } else if (ce instanceof OperatorElement) {
152 return render((OperatorElement) ce);
153 } else {
154 return new String();
155 }
156 }
157
158 private String render(FieldElement fe) {
159 return fe.getTable()+"."+fe.getField();
160 }
161
162 private String render(ValueElement ve) {
163 if (ve.getValue() == "NULL") {
164 return ve.getValue();
165 } else {
166 return "'"+ve.getValue().replaceAll("'","\\\\'")+"'";
167 }
168 }
169
170 private String render(FunctionElement fe) {
171 String arguments = new String();
172 ConditionElement[] array = fe.getArguments();
173 for (int i=0; i<array.length; i++) {
174 arguments += render(array[i]);
175 if (i + 1 < array.length)
176 arguments += ",";
177 }
178 if (fe.getFunctionType() == FunctionElement.PASSWORD) {
179 return "PASSWORD("+arguments+")";
180 } else {
181 return new String();
182 }
183 }
184
185 private String render(OperatorElement oe) {
186 String result = render(oe.getLeft());
187 if (oe.getOperator() == oe.ADD)
188 result += " + ";
189 else if (oe.getOperator() == oe.MULTIPLY)
190 result += " * ";
191 else if (oe.getOperator() == oe.DIVIDE)
192 result += " / ";
193 else if (oe.getOperator() == oe.SUBTRACT)
194 result += " - ";
195 return "("+result + render(oe.getRight())+")";
196 }
197
198 private String render(MatchConditions mcs) {
199 String res = " (";
200 for (java.util.Iterator i=mcs.getMatchConditions().iterator(); i.hasNext(); ) {
201 res += render((MatchCondition)i.next());
202 if (i.hasNext()) {
203 res += " + ";
204 }
205 }
206 return res + ") ";
207 }
208
209 private String render(MatchCondition mc) {
210 java.util.Collection c = new java.util.Vector();
211 for (java.util.Iterator i = mc.getFields().iterator(); i.hasNext(); ) {
212 c.add( render((FieldElement)i.next()));
213 }
214 String result = "( (MATCH("+commaList(c)+") AGAINST ("+render(new ValueElement(mc.getValue()))+")) ";
215 if (mc.getFactor() != 1.0)
216 result += " * " + Double.toString(mc.getFactor());
217 result += ")";
218 return result;
219 }
220
221 private String render(PostElements pes) {
222 String s = new String();
223 GroupByPostElement g = pes.getGroup();
224 if (g != null)
225 s += " "+render(g);
226 OrderByPostElement o = pes.getOrder();
227 if (o != null)
228 s += " "+render(o);
229 LimitPostElement l = pes.getLimit();
230 if (l != null)
231 s += " "+render(l);
232 return s;
233 }
234
235 private String render(GroupByPostElement g) {
236 String s = new String();
237 Collection fe = g.getFieldElements();
238 if (fe != null && fe.size() > 0) {
239 Vector v = new Vector();
240 for (Iterator i=fe.iterator(); i.hasNext(); ) {
241 v.add(render((FieldElement)i.next()));
242 }
243 s += " GROUP BY "+commaList(v)+" ";
244 }
245 return s;
246 }
247
248 private String render(OrderByPostElement o) {
249 if (o.isEmpty()) {
250 return new String();
251 }
252 String s = new String(" ORDER BY ");
253 Vector v = new Vector();
254 Iterator i = o.getListIterator();
255 while (i.hasNext()) {
256 OrderByPostElement.OrderByElm elm = (OrderByPostElement.OrderByElm)i.next();
257 String orderStr = "ASC";
258 if (elm.sortDirection == OrderByPostElement.DESC) orderStr = "DESC";
259 v.add( render(elm.fe) + " " + orderStr);
260 }
261 s += commaList(v);
262 return s;
263 }
264
265 private String render(LimitPostElement l) {
266 String s = new String();
267 if (l.getCount() != null) {
268 s += " LIMIT ";
269 if (l.getOffset() != null) {
270 s += l.getOffset().toString() + ", ";
271 }
272 s += l.getCount() + " ";
273 }
274 return s;
275 }
276
277 public String renderInsert(Insert insert) {
278 String result = "INSERT INTO ";
279 result += insert.getTable();
280 result += " ("+commaList(insert.getFields())+")";
281 result += " VALUES (";
282 Vector values = new Vector();
283 for (Iterator i=insert.getValues().iterator(); i.hasNext(); ) {
284 String v = (String)i.next();
285 if (v == null || v.compareTo("NULL") == 0)
286 values.add("NULL");
287 else
288 values.add("'"+v.replaceAll("'","\\\\'")+"'");
289 // triple escape single quotes: one for the Java compiler,
290 // one for JDBC, one for MySQL. a non-escaped quote is
291 // eventually stored in the database.
292 }
293 result += commaList(values)+")";
294 return result;
295 }
296
297 public String renderUpdate(Update update) {
298 String result = "UPDATE " + update.getTable();
299 Collection fieldValues = new java.util.Vector();
300 Iterator vi = update.getValues().iterator();
301 for (Iterator fi=update.getFields().iterator(); fi.hasNext(); ) {
302 FieldElement fieldValue = (FieldElement)fi.next();
303 ConditionElement value = (ConditionElement)vi.next();
304 String str = render(fieldValue) + " = " + render(value);
305 fieldValues.add(str);
306 }
307 result += " SET " + commaList(fieldValues);
308 result += " WHERE " + render(update.getDataConditions());
309 return result;
310 }
311
312 public String renderDelete(Delete delete) {
313 String result = "DELETE FROM " + delete.getTable();
314 result += " WHERE " + render(delete.getDataConditions());
315 return result;
316 }
317 }