001/*
002 * Copyright (c) 2003-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: welker $'
006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 
007 * '$Revision: 24234 $'
008 * 
009 * Permission is hereby granted, without written agreement and without
010 * license or royalty fees, to use, copy, modify, and distribute this
011 * software and its documentation for any purpose, provided that the above
012 * copyright notice and the following two paragraphs appear in all copies
013 * of this software.
014 *
015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
019 * SUCH DAMAGE.
020 *
021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
026 * ENHANCEMENTS, OR MODIFICATIONS.
027 *
028 */
029
030package org.ecoinformatics.seek.querybuilder;
031
032import java.awt.Point;
033import java.util.Enumeration;
034import java.util.Hashtable;
035import java.util.Vector;
036
037import org.kepler.objectmanager.data.db.DSSchemaIFace;
038import org.kepler.objectmanager.data.db.DSTableFieldIFace;
039import org.kepler.objectmanager.data.db.DSTableIFace;
040import org.w3c.dom.Document;
041import org.w3c.dom.Node;
042import org.w3c.dom.NodeList;
043
044/**
045 * This is class is used to parse XML and create a DBQueryDef object and/or emit
046 * XML from a DBQuery object<br>
047 * Call "readQueryDef" for reading in a query<br>
048 * Call "emitXML" to generate the XML <br>
049 */
050public class DBQueryDefParserEmitter {
051        private static final int SELECT_TYPE = 0;
052        private static final int TABLE_TYPE = 1;
053        private static final int WHERE_TYPE = 2;
054
055        /** query was built OK **/
056        public static final int QUERY_OK = 0;
057        /** error building query **/
058        public static final int QUERY_ERROR = 1;
059        /** the XML was missing a "query" element **/
060        public static final int NO_QUERY_NODE = 2;
061        /** a table name was used that could not be found in the schema **/
062        public static final int BAD_TABLENAME = 3;
063        /** a field name was used that could not be found in the schema **/
064        public static final int BAD_FIELDNAME = 4;
065
066        /** description of the error codes **/
067        private static final String[] ERROR_DESC = { "OK", "Query in invalid",
068                        "No \"query\" element", "Table name is not in schema.",
069                        "Field name is not in tables's schema." };
070        public static final String WILDCARD = "*";
071
072        /** the error code **/
073        private static int mErrorCode = QUERY_OK;
074
075        private static Hashtable mMappedNameHash = null;
076
077        /**
078         * Return the text for the error code
079         * 
080         * @return a string representing the error
081         */
082        public static String getErrorCodeText() {
083                return ERROR_DESC[mErrorCode];
084        }
085
086        // --------------------------------------------------------------
087        // -- XML Consumption
088        // --------------------------------------------------------------
089
090        /**
091         * Processes the where node
092         * 
093         * @param aSchema
094         *            the schema
095         * @param aNode
096         *            the node
097         * @param aParent
098         *            the node's parent
099         * @return a where object (tree)
100         */
101        protected static DBWhereIFace processWhereNode(DSSchemaIFace aSchema,
102                        Node aNode, DBWhereOperator aParent) {
103                DBWhereIFace whereObj = null;
104                String nodeName = aNode.getNodeName();
105                if (nodeName.equals(DBWhereOperator.AND_OPER)
106                                || nodeName.equals(DBWhereOperator.OR_OPER)) {
107                        DBWhereOperator oper = new DBWhereOperator(aParent, false);
108                        if (aParent != null) {
109                                aParent.append(oper);
110                        }
111
112                        oper.setName(nodeName);
113                        whereObj = oper;
114                        NodeList list = aNode.getChildNodes();
115                        if (list != null) {
116                                for (int i = 0; i < list.getLength(); i++) {
117                                        if (list.item(i).getNodeType() != Node.TEXT_NODE) {
118                                                if (processWhereNode(aSchema, list.item(i), oper) == null) {
119                                                        return null;
120                                                }
121                                        }
122                                }
123                        }
124                } else if (nodeName.equals("field")) {
125                        String tblName = DBUIUtils.findAttrValue(aNode, "tableName");
126                        DSTableIFace tblIFace = DBUIUtils.getTableByName(aSchema, tblName);
127                        if (tblIFace == null) {
128                                mErrorCode = BAD_TABLENAME;
129                                return null;
130                        }
131
132                        String fldName = DBUIUtils.findAttrValue(aNode, "fieldName");
133                        DSTableFieldIFace field = DBUIUtils.getFieldByName(tblIFace,
134                                        fldName);
135                        if (field != null) {
136                                DBWhereCondition cond = new DBWhereCondition(aParent,
137                                                getMappedTableName(tblName), fldName, field
138                                                                .getDataType());
139                                String operStr = DBUIUtils.findAttrValue(aNode, "oper");
140                                if (operStr != null)
141                                        cond.setOperator(operStr);
142
143                                String criteriaStr = DBUIUtils.findAttrValue(aNode, "criteria");
144                                if (criteriaStr != null)
145                                        cond.setCriteria(criteriaStr);
146
147                                // If the parent null then this "should" be the only item in the
148                                // where clause
149                                if (aParent != null) {
150                                        aParent.append(cond);
151                                }
152
153                                whereObj = cond; // must set the return var or it will have been
154                                                                        // considered that it failed.
155
156                        } else {
157                                mErrorCode = BAD_FIELDNAME;
158                                return null;
159                        }
160
161                }
162                return whereObj;
163        }
164
165        /**
166         * Creates a new DBTableField item from a DOM Node
167         * 
168         * @param aSchema
169         *            the schema
170         * @param aNode
171         *            creates a model item
172         * @return the DBSelectTableModelItem item
173         */
174        private static DBTableField createFieldFromNode(DSSchemaIFace aSchema,
175                        Node aNode) {
176                String tblName = DBUIUtils.findAttrValue(aNode, "tableName");
177                DSTableIFace tblIFace = DBUIUtils.getTableByName(aSchema, tblName);
178                if (tblIFace == null) {
179                        mErrorCode = BAD_TABLENAME;
180                        return null;
181                }
182                String fldName = DBUIUtils.findAttrValue(aNode, "fieldName");
183                DSTableFieldIFace fldIFace = DBUIUtils
184                                .getFieldByName(tblIFace, fldName);
185                if (fldIFace == null) {
186                        mErrorCode = BAD_TABLENAME;
187                        return null;
188                }
189                DBTableFrame tableFrame = new DBTableFrame(tblIFace, -1);
190                DBTableField field = new DBTableField(fldIFace, tableFrame);
191                return field;
192        }
193
194        /**
195         * Creates a new DBSelectTableModelItem item from a DOM Node
196         * 
197         * @param aSchema
198         *            the schema
199         * @param aNode
200         *            creates a model item
201         * @return the DBSelectTableModelItem item
202         */
203        private static DBSelectTableModelItem createItemFromNode(
204                        DSSchemaIFace aSchema, Node aNode) {
205                String tblName = DBUIUtils.findAttrValue(aNode, "tableName");
206                DSTableIFace tblIFace = DBUIUtils.getTableByName(aSchema, tblName);
207                if (tblIFace == null) {
208                        mErrorCode = BAD_TABLENAME;
209                        return null;
210                }
211                String fldName = DBUIUtils.findAttrValue(aNode, "fieldName");
212                DSTableFieldIFace fldIFace = DBUIUtils
213                                .getFieldByName(tblIFace, fldName);
214                if (fldIFace == null) {
215                        mErrorCode = BAD_TABLENAME;
216                        return null;
217                }
218                DBSelectTableModelItem item = new DBSelectTableModelItem();
219                item.setTableName(getMappedTableName(tblName));
220                item.setName(fldName);
221                item.setTableId(DBUIUtils.getIntAttrId(aNode, "tableId")); // default
222                                                                                                                                        // value is
223                                                                                                                                        // -1 which
224                                                                                                                                        // is ok to
225                                                                                                                                        // set
226                return item;
227        }
228
229        /**
230         * Creates a new DBSelectTableModelItem item from a DOM Node
231         * 
232         * @param aSchema
233         *            the schema
234         * @param aNode
235         *            creates a model item
236         * @return a DBQueryDefTable
237         */
238        private static DBQueryDefTable createTableItem(DSSchemaIFace aSchema,
239                        Node aNode) {
240                String name = DBUIUtils.findAttrValue(aNode, "name");
241                DSTableIFace tblIFace = DBUIUtils.getTableByName(aSchema, name);
242                if (tblIFace == null) {
243                        mErrorCode = BAD_TABLENAME;
244                        return null;
245                }
246
247                DBQueryDefTable item = new DBQueryDefTable(DBUIUtils.getIntAttrId(
248                                aNode, "id"), name == null ? "" : getMappedTableName(name),
249                                DBUIUtils.getIntAttrId(aNode, "x"), DBUIUtils.getIntAttrId(
250                                                aNode, "y"));
251                return item;
252        }
253
254        /**
255         * Reads in a XML document that is a query definition
256         * 
257         * @param aSchema
258         *            the schema
259         * @param aDoc
260         *            the document DOM
261         * @return the query def object
262         */
263        public static DBQueryDef processDOM(DSSchemaIFace aSchema, Document aDoc) {
264                DBQueryDef queryDef = new DBQueryDef();
265                Node queryNode = DBUIUtils.findNode(aDoc, "query");
266                if (queryNode != null) {
267                        queryDef.setIsAdv(DBUIUtils.findAttrValue(queryNode, "advanced")
268                                        .equalsIgnoreCase("true"));
269                } else {
270                        mErrorCode = NO_QUERY_NODE;
271                        queryDef = null;
272                        return null;
273                }
274
275                // process the "select" portion
276                Node selectNode = DBUIUtils.findNode(aDoc, "select");
277                if (selectNode != null) {
278                        NodeList list = selectNode.getChildNodes();
279                        if (list != null) {
280                                for (int i = 0; i < list.getLength(); i++) {
281                                        Node child = list.item(i);
282                                        if (child.getNodeType() != Node.TEXT_NODE) {
283                                                String tblName = DBUIUtils.findAttrValue(child,
284                                                                "tableName");
285                                                DSTableIFace tblIFace = DBUIUtils.getTableByName(
286                                                                aSchema, tblName);
287                                                if (tblIFace == null) {
288                                                        mErrorCode = BAD_TABLENAME;
289                                                        return null;
290                                                }
291                                                String fldName = DBUIUtils.findAttrValue(child,
292                                                                "fieldName");
293
294                                                try {
295                                                        if (fldName != null && fldName.equals(WILDCARD)) {
296                                                                // handle wild card *
297                                                                Vector fields = tblIFace.getFields();
298                                                                if (fields != null) {
299                                                                        for (int j = 0; j < fields.size(); j++) {
300                                                                                DSTableFieldIFace field = (DSTableFieldIFace) fields
301                                                                                                .elementAt(j);
302                                                                                String fieldName = field.getName();
303                                                                                addSelectedItemIntoQueryDef(fieldName,
304                                                                                                tblIFace, queryDef);
305                                                                        }
306                                                                } else {
307                                                                        throw new Exception(
308                                                                                        "no fields in the table " + tblName);
309                                                                }
310                                                        } else {
311                                                                // non wild card - just regular field name
312                                                                addSelectedItemIntoQueryDef(fldName, tblIFace,
313                                                                                queryDef);
314                                                        }
315                                                } catch (Exception e) {
316                                                        e.printStackTrace();
317                                                        mErrorCode = BAD_FIELDNAME;
318                                                        // System.out.println("The bad file name");
319                                                        return null;
320                                                }
321
322                                        }
323                                }
324                        }
325                }
326
327                // process the "tables" portion
328                Node tablesNode = DBUIUtils.findNode(aDoc, "tables");
329                if (tablesNode != null) {
330                        NodeList list = tablesNode.getChildNodes();
331                        if (list != null) {
332                                for (int i = 0; i < list.getLength(); i++) {
333                                        Node child = list.item(i);
334                                        String nodeName = child.getNodeName();
335                                        if (nodeName.equals(DBWhereOperator.AND_OPER)
336                                                        || nodeName.equals(DBWhereOperator.OR_OPER)
337                                                        || nodeName.equals("table")) {
338                                                DBQueryDefTable tableItem = createTableItem(aSchema,
339                                                                child);
340                                                if (tableItem == null) {
341                                                        return null;
342                                                }
343                                                queryDef.addTable(tableItem);
344                                        }
345                                }
346                        }
347                }
348
349                // process the "where" portion
350                Node whereNode = DBUIUtils.findNode(aDoc, "where");
351                if (whereNode != null) {
352                        NodeList list = whereNode.getChildNodes();
353                        if (list != null) {
354                                for (int i = 0; i < list.getLength(); i++) {
355                                        Node child = list.item(i);
356                                        String nodeName = child.getNodeName();
357                                        if (nodeName.equals(DBWhereOperator.AND_OPER)
358                                                        || nodeName.equals(DBWhereOperator.OR_OPER)
359                                                        || nodeName.equals("field")) {
360                                                DBWhereIFace whereObj = processWhereNode(aSchema,
361                                                                child, null);
362                                                if (whereObj == null && mErrorCode != QUERY_OK) {
363                                                        return null;
364                                                }
365                                                queryDef.setWhere(whereObj);
366                                        }
367                                }
368                        }
369                }
370
371                // process the "joins" portion
372                Node joinNode = DBUIUtils.findNode(aDoc, "joins");
373                if (joinNode != null) {
374                        Vector joins = new Vector();
375                        NodeList joinNodeList = joinNode.getChildNodes();
376                        for (int i = 0; i < joinNodeList.getLength(); i++) {
377                                Node child = joinNodeList.item(i);
378                                String nodeName = child.getNodeName();
379                                if (nodeName.equals("join")) {
380                                        DBTableField left = null;
381                                        DBTableField right = null;
382                                        NodeList childList = child.getChildNodes();
383                                        for (int j = 0; j < childList.getLength(); j++) {
384                                                Node joinChild = childList.item(j);
385                                                nodeName = joinChild.getNodeName();
386                                                if (nodeName.equals("left")) {
387                                                        left = createFieldFromNode(aSchema, joinChild);
388                                                        if (left == null) {
389                                                                return null;
390                                                        }
391                                                } else if (nodeName.equals("right")) {
392                                                        right = createFieldFromNode(aSchema, joinChild);
393                                                        if (right == null) {
394                                                                return null;
395                                                        }
396                                                }
397                                        }
398                                        if (left != null && right != null) {
399                                                DBTableJoinItem join = new DBTableJoinItem(left, right);
400                                                joins.add(join);
401                                        }
402                                }
403                        }
404                        if (joins.size() > 0) {
405                                queryDef.setJoins(joins);
406                        }
407                }
408
409                return queryDef;
410        }
411
412        /*
413         * This method will added a selected field into queryDef object
414         */
415        private static void addSelectedItemIntoQueryDef(String fldName,
416                        DSTableIFace tblIFace, DBQueryDef queryDef) throws Exception {
417                DSTableFieldIFace field = DBUIUtils.getFieldByName(tblIFace, fldName);
418                String tblName = tblIFace.getName();
419                if (field != null) {
420                        // System.out.println("field " +fldName +
421                        // " has the missing value in generating field is "+field.getMissingValueCode());
422                        DBSelectTableModelItem item = new DBSelectTableModelItem(
423                                        getMappedTableName(tblName), fldName, field.getDataType(),
424                                        true, "", "", field.getMissingValueCode());
425                        queryDef.addSelectItem(item);
426
427                } else {
428                        throw new Exception("Couldn't find the selected field " + fldName
429                                        + " in table " + tblName);
430                }
431
432        }
433
434        /**
435         * Reads in a Query and returns a Query object
436         * 
437         * @param aSchema
438         *            the schema
439         * @param aFileName
440         *            Name of XML file representing a XML document
441         * @return DBQueryDef object
442         */
443        public static DBQueryDef readQueryDef(DSSchemaIFace aSchema,
444                        String aFileName) {
445
446                DBQueryDef queryDef = null;
447                try {
448                        queryDef = processDOM(aSchema, DBUIUtils.readXMLFile2DOM(aFileName));
449                        // debug code
450                        // if (queryDef != null)
451                        // {
452                        // System.out.println("[\n"+emitXML(queryDef)+"\n]\n");
453                        // }
454                } catch (Exception e) {
455                        System.err.println(e);
456                }
457
458                return queryDef;
459        }
460
461        /**
462         * Parses and creates a DBQueryDef object from an XML String
463         * 
464         * @param aSchema
465         *            the schema
466         * @param aXMLQueryStr
467         *            XML string representing a XML document
468         * @return DBQueryDef object
469         */
470        public static DBQueryDef parseQueryDef(DSSchemaIFace aSchema,
471                        String aXMLQueryStr, Hashtable aMappedNameHash) {
472                mMappedNameHash = aMappedNameHash;
473
474                DBQueryDef queryDef = null;
475                try {
476                        queryDef = processDOM(aSchema, DBUIUtils
477                                        .convertXMLStr2DOM(aXMLQueryStr));
478                } catch (Exception e) {
479                        System.err.println(e);
480                }
481
482                return queryDef;
483
484        }
485
486        /**
487         * Parses and creates a DBQueryDef object from an XML String
488         * 
489         * @param aSchema
490         *            the schema
491         * @param aXMLQueryStr
492         *            XML string representing a XML document
493         * @return DBQueryDef object
494         */
495        public static DBQueryDef parseQueryDef(DSSchemaIFace aSchema,
496                        String aXMLQueryStr) {
497                return parseQueryDef(aSchema, aXMLQueryStr, null);
498        }
499
500        // --------------------------------------------------------------
501        // -- XML Generation
502        // --------------------------------------------------------------
503
504        /**
505         * Returns a mapped name if one exists
506         */
507        private static String getMappedTableName(String aName) {
508                if (mMappedNameHash != null) {
509                        String mappedName = (String) mMappedNameHash.get(aName);
510                        if (mappedName != null && mappedName.length() > 0) {
511                                return mappedName;
512                        }
513                }
514                return aName;
515        }
516
517        /**
518         * Appends the XML generated from a DBWhereCondition
519         * 
520         * @param aStrBuf
521         *            the output buffer
522         * @param aCond
523         *            the condition
524         */
525        private static void appendCondXML(StringBuffer aStrBuf,
526                        DBWhereCondition aCond) {
527                aStrBuf.append("  <field tableName=\"" + aCond.getTableName()
528                                + "\"  fieldName=\"" + aCond.getName() + "\" oper=\""
529                                + aCond.getOperator() + "\" criteria=\"" + aCond.getCriteria()
530                                + "\"/>\n");
531        }
532
533        /**
534         * Recurses through the "tree" of operators and creates a textual rendering
535         * of the operators and conditions
536         * 
537         * @param aStrBuf
538         *            the output buffer
539         * @param aWhereObj
540         *            the where object tree
541         * @param aLevel
542         *            the level within the tree
543         * @return true if done, false if not
544         */
545        protected static boolean recurseWhere(StringBuffer aStrBuf,
546                        DBWhereIFace aWhereObj, int aLevel) {
547                if (aWhereObj == null)
548                        return true;
549
550                if (aWhereObj instanceof DBWhereCondition) {
551                        aStrBuf.append(DBUIUtils.getSpaces(aLevel));
552                        appendCondXML(aStrBuf, (DBWhereCondition) aWhereObj);
553                        return true;
554                }
555
556                DBWhereOperator whereOper = (DBWhereOperator) aWhereObj;
557
558                // Check number of Children
559                if (whereOper.getNumChildern() < 2)
560                        return false;
561
562                int numChildren = 0;
563
564                for (Enumeration e = whereOper.getEnumeration(); e.hasMoreElements();) {
565                        DBWhereIFace item = (DBWhereIFace) e.nextElement();
566                        if (item instanceof DBWhereOperator) {
567                                DBWhereOperator oper = (DBWhereOperator) item;
568                                if (!oper.isClosure() && oper.getNumChildern() > 1) {
569                                        numChildren++;
570                                }
571                        } else {
572                                numChildren++;
573                        }
574                }
575
576                if (numChildren < 2)
577                        return false;
578
579                aStrBuf.append(DBUIUtils.getSpaces(aLevel));
580                aStrBuf.append("<" + whereOper.getName() + ">\n");
581
582                for (Enumeration e = whereOper.getEnumeration(); e.hasMoreElements();) {
583                        DBWhereIFace item = (DBWhereIFace) e.nextElement();
584                        if (item instanceof DBWhereOperator) {
585                                DBWhereOperator oper = (DBWhereOperator) item;
586                                if (!oper.isClosure() && oper.getNumChildern() > 1) {
587                                        boolean status = recurseWhere(aStrBuf, oper, aLevel + 1);
588                                        if (!status)
589                                                return false;
590                                }
591                        } else {
592                                DBWhereCondition cond = (DBWhereCondition) item;
593                                aStrBuf.append(DBUIUtils.getSpaces(aLevel + 1));
594                                appendCondXML(aStrBuf, (DBWhereCondition) item);
595                        }
596                }
597                aStrBuf.append(DBUIUtils.getSpaces(aLevel));
598                aStrBuf.append("</" + whereOper.getName() + ">\n");
599                return true;
600        }
601
602        /**
603         * Appends the generation of the XML for DBSelectTableModelItem item
604         * 
605         * @param aStrBuf
606         *            output buffer
607         * @param aItem
608         *            the item to be ouputted
609         * @param aDepth
610         *            the depth in the tree
611         * @param aType
612         *            the type of item we are working on
613         */
614        private static void generateXMLFor(StringBuffer aStrBuf,
615                        DBSelectTableModelItem aItem, int aDepth, int aType) {
616                String tableName = aItem.getTableName();
617
618                aStrBuf.append(DBUIUtils.getSpaces(aDepth));
619
620                aStrBuf.append("<field");
621
622                if (aItem.getTableId() != -1) {
623                        aStrBuf.append(" tableId=\"" + aItem.getTableId() + "\"");
624                }
625
626                aStrBuf.append(" tableName=\"" + tableName + "\"");
627                aStrBuf.append(" fieldName=\"" + aItem.getName() + "\"");
628
629                if (aType == SELECT_TYPE) {
630                        // aStrBuf.append(" displayed=\""+(isDisplayed?"true":"false")+"\"");
631                } else {
632                        aStrBuf.append(" datatype=\"" + aItem.getDataType() + "\"");
633                        aStrBuf.append(" criteria=\"" + aItem.getCriteria() + "\"");
634                        aStrBuf.append(" operator=\"" + aItem.getOperator() + "\"");
635                }
636                aStrBuf.append("/>\n");
637        }
638
639        /**
640         * Somewhat generic method for outputing either the select or the tables
641         * portion of the query
642         * 
643         * @param aList
644         *            the vector of objects
645         * @param aNodeName
646         *            the node name
647         * @param aDepth
648         *            the depth of the tree (for indentation)
649         * @param aType
650         *            the type of objects being processed
651         * @return a string of XML
652         */
653        private static String enumerateObjs(Vector aList, String aNodeName,
654                        int aDepth, int aType) {
655                if (aList.size() == 0)
656                        return "";
657
658                StringBuffer strBuf = new StringBuffer();
659                strBuf.append(DBUIUtils.getSpaces(aDepth));
660                strBuf.append("<" + aNodeName + ">\n");
661                for (Enumeration et = aList.elements(); et.hasMoreElements();) {
662                        Object obj = et.nextElement();
663                        if (aType == TABLE_TYPE && obj instanceof DBQueryDefTable) {
664                                strBuf.append(DBUIUtils.getSpaces(aDepth + 1));
665                                DBQueryDefTable table = (DBQueryDefTable) obj;
666
667                                strBuf.append("<table name=\"" + table.getName() + "\"");
668
669                                if (table.getId() > -1)
670                                        strBuf.append(" id=\"" + table.getId() + "\"");
671
672                                Point pnt = table.getPnt();
673                                if (pnt.x > -1)
674                                        strBuf.append(" x=\"" + pnt.x + "\"");
675
676                                if (pnt.y > -1)
677                                        strBuf.append(" y=\"" + pnt.y + "\"");
678
679                                strBuf.append("/>\n");
680
681                        } else if (aType == SELECT_TYPE
682                                        && obj instanceof DBSelectTableModelItem) {
683                                generateXMLFor(strBuf, (DBSelectTableModelItem) obj, aDepth,
684                                                aType);
685                        }
686                }
687                strBuf.append(DBUIUtils.getSpaces(aDepth));
688                strBuf.append("</" + aNodeName + ">\n");
689                return strBuf.toString();
690        }
691
692        /**
693         * Appends generated XML for a join item (left or right)
694         * 
695         * @param aStrBuf
696         *            the output string
697         * @param aName
698         *            the name of XML element
699         * @param aItem
700         *            to be generated as XML
701         */
702        private static void processJoinItem(StringBuffer aStrBuf, String aName,
703                        DBSelectTableModelItem aItem) {
704                if (aItem == null)
705                        return;
706
707                aStrBuf.append("      <" + aName + " ");
708                if (aItem.getTableId() > -1) {
709                        aStrBuf.append("tableId=\"" + aItem.getTableId() + "\" ");
710                }
711                aStrBuf.append("tableName=\"" + aItem.getTableName()
712                                + "\" fieldName=\"" + aItem.getName() + "\"/>\n");
713        }
714
715        /**
716         * Appends generated XML for all of the joins
717         * 
718         * @param aStrBuf
719         *            the output string
720         * @param aJoins
721         *            vector of pairs of items representing the joins
722         */
723        private static void processJoins(StringBuffer aStrBuf, Vector aJoins) {
724                // make sure there is an even number
725                if (aJoins != null && aJoins.size() > 0 && aJoins.size() % 2 == 0) {
726                        StringBuffer strBuf = new StringBuffer("  <joins>\n");
727                        for (Enumeration et = aJoins.elements(); et.hasMoreElements();) {
728                                strBuf.append("    <join>\n");
729                                processJoinItem(strBuf, "left", (DBSelectTableModelItem) et
730                                                .nextElement());
731                                processJoinItem(strBuf, "right", (DBSelectTableModelItem) et
732                                                .nextElement());
733                                strBuf.append("    </join>\n");
734                        }
735                        aStrBuf.append(strBuf.toString());
736                        aStrBuf.append("  </joins>\n");
737                }
738        }
739
740        /**
741         * Creates the XML that represents a query
742         * 
743         * @return the XML document
744         */
745        public static String emitXML(DBQueryDef aQueryDef) {
746                if (aQueryDef == null)
747                        return "";
748
749                Vector selects = aQueryDef.getSelects();
750                Vector tables = aQueryDef.getTables();
751                DBWhereIFace whereObj = aQueryDef.getWhere();
752
753                // if this is empty query, return ""
754                if ((selects == null || (selects != null && selects.isEmpty()))
755                                && (tables == null || (tables != null && tables.isEmpty()))) {
756                        return "";
757                }
758
759                StringBuffer strBuf = new StringBuffer("<query advanced=\""
760                                + aQueryDef.isAdv() + "\">\n");
761
762                strBuf.append(enumerateObjs(selects, "select", 1, SELECT_TYPE));
763
764                strBuf.append(enumerateObjs(tables, "tables", 1, TABLE_TYPE));
765
766                processJoins(strBuf, aQueryDef.getJoins());
767
768                // DBWhereIFace whereObj = aQueryDef.getWhere();
769                if (whereObj != null) {
770                        strBuf.append("  <where>\n");
771                        recurseWhere(strBuf, whereObj, 2);
772                        strBuf.append("  </where>\n");
773                }
774
775                strBuf.append("</query>\n");
776
777                return strBuf.toString();
778        }
779
780        /**
781         * Create SQL string
782         */
783        public static String createSQL(DSSchemaIFace aSchemaDef,
784                        DBQueryDef aQueryDef) {
785                if (aQueryDef == null)
786                        return null;
787
788                Hashtable tableNames = new Hashtable();
789                StringBuffer strBuf = new StringBuffer("SELECT ");
790
791                int displayCnt = 0;
792                for (Enumeration et = aQueryDef.getSelects().elements(); et
793                                .hasMoreElements();) {
794                        DBSelectTableModelItem item = (DBSelectTableModelItem) et
795                                        .nextElement();
796                        if (item.isDisplayed()) {
797                                tableNames.put(item.getTableName(), item.getTableName());
798                                displayCnt++;
799                        }
800                }
801                if (displayCnt == 0)
802                        return null;
803
804                displayCnt = 0;
805                for (Enumeration et = aQueryDef.getSelects().elements(); et
806                                .hasMoreElements();) {
807                        DBSelectTableModelItem item = (DBSelectTableModelItem) et
808                                        .nextElement();
809                        if (item.isDisplayed()) {
810                                if (displayCnt > 0) {
811                                        strBuf.append(", ");
812                                }
813                                displayCnt++;
814                                strBuf.append(DBUIUtils.getFullFieldName(item.getTableName(),
815                                                item.getName()));
816                                tableNames.put(item.getTableName(), item.getTableName());
817                        }
818                }
819                strBuf.append(" FROM ");
820
821                StringBuffer whereStr = new StringBuffer();
822                if (aQueryDef.getJoins() != null) {
823                        int cnt = 0;
824                        for (Enumeration et = aQueryDef.getJoins().elements(); et
825                                        .hasMoreElements();) {
826                                if (cnt > 0) {
827                                        whereStr.append(" AND ");
828                                }
829                                cnt++;
830                                DBTableJoinItem joinItem = (DBTableJoinItem) et.nextElement();
831                                whereStr.append(DBUIUtils.getFullFieldName(joinItem
832                                                .getItemLeft()));
833                                whereStr.append(" = ");
834                                whereStr.append(DBUIUtils.getFullFieldName(joinItem
835                                                .getItemRight()));
836                                String tblName = joinItem.getItemLeft().getTable().getName();
837                                tableNames.put(tblName, tblName);
838                                tblName = joinItem.getItemRight().getTable().getName();
839                                tableNames.put(tblName, tblName);
840                        }
841                }
842
843                displayCnt = 0;
844                for (Enumeration et = tableNames.elements(); et.hasMoreElements();) {
845                        String tableName = (String) et.nextElement();
846                        if (tableName.indexOf(' ') != -1) {
847                                tableName = "[" + tableName + "]";
848                        }
849                        if (displayCnt > 0) {
850                                strBuf.append(", ");
851                        }
852                        displayCnt++;
853                        strBuf.append(tableName);
854                }
855
856                // Super cheesey, but we will do this for now
857                DBWherePanel wherePanel = new DBWherePanel(aSchemaDef);
858                wherePanel.getModel().initialize(aQueryDef.getWhere());
859                wherePanel.fillQueryDef(aQueryDef);
860                boolean addedWhere = false;
861                if (aQueryDef.getJoins() != null) {
862                        addedWhere = true;
863                        strBuf.append(" WHERE ");
864                        strBuf.append(whereStr);
865                }
866                String wherePanelStr = wherePanel.generateWhereSQL(true);
867                String noSpaces = wherePanelStr.trim();
868                if (noSpaces.length() > 0) {
869
870                        strBuf.append(addedWhere ? " AND " : " WHERE ");
871                        strBuf.append(wherePanelStr);
872                }
873
874                return strBuf.toString();
875
876        }
877
878}