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.BorderLayout;
033import java.awt.Component;
034import java.awt.event.ActionEvent;
035import java.awt.event.MouseAdapter;
036import java.awt.event.MouseEvent;
037import java.awt.event.MouseListener;
038import java.util.Enumeration;
039import java.util.Hashtable;
040import java.util.Vector;
041
042import javax.swing.AbstractAction;
043import javax.swing.DefaultListModel;
044import javax.swing.JButton;
045import javax.swing.JComponent;
046import javax.swing.JDesktopPane;
047import javax.swing.JLabel;
048import javax.swing.JList;
049import javax.swing.JPanel;
050import javax.swing.JScrollPane;
051import javax.swing.JSplitPane;
052import javax.swing.JTabbedPane;
053import javax.swing.ListSelectionModel;
054import javax.swing.event.ListSelectionEvent;
055import javax.swing.event.ListSelectionListener;
056import javax.swing.event.TableModelListener;
057
058import org.kepler.objectmanager.data.db.DSSchemaIFace;
059import org.kepler.objectmanager.data.db.DSTableIFace;
060
061/**
062 * This class shows a split view with a set of table schemas as draggable tables
063 * in the upper pane. The the "from" clause of the select can be created by
064 * dragging fields from one table to the next to form the "join" relationships.
065 * And a two tab control in the lower pane. The Select pane enables the user to
066 * indicate which fields will be displayed and what the conditions will be for
067 * each of the fields.
068 * 
069 * The Where pane enables the user to specify a complex conditional.
070 */
071public class QBSplitPaneAdvanced extends JPanel implements
072                ListSelectionListener, QBBuilderInterface {
073        protected JSplitPane mSplitPane = null;
074        protected DSSchemaIFace mSchema = null;
075        protected DBTableDesktopPane mDesktop = null;
076        protected DBTableJoin mTableJoins = null;
077        protected DBWherePanel mWherePanel = null;
078        protected JPanel mSelectPanel = null;
079        protected DBSelectTableUIAdv mTableView = null;
080        protected DBSelectTableModelAdv mTableModel = null;
081        protected JButton mAddBtn = null;
082        protected JList mTableList = null;
083        protected TableModelListener mTableModelListener = null;
084
085        /**
086         * QBSplitPaneAdvanced Constructor
087         * 
088         * @param aSchema
089         *            the schema
090         * @param aListener
091         *            a listener of changes to the overall model
092         */
093        public QBSplitPaneAdvanced(DSSchemaIFace aSchema,
094                        TableModelListener aListener) {
095                mSchema = aSchema;
096
097                setLayout(new BorderLayout());
098
099                mSplitPane = new JSplitPane(JSplitPane.VERTICAL_SPLIT,
100                                createUpperPanel(aListener), createLowerPanel());
101                mSplitPane.setContinuousLayout(true);
102                mSplitPane.setOneTouchExpandable(true);
103                mSplitPane.setDividerLocation(230);
104
105                add(mSplitPane, BorderLayout.CENTER);
106        }
107
108        /**
109         * Sets the Model Listener appropriately
110         * 
111         * @param aTblModelListener
112         *            a listener
113         */
114        public void setTableModelListener(TableModelListener aTblModelListener) {
115                mTableModelListener = aTblModelListener;
116                mTableModel.addTableModelListener(aTblModelListener);
117                mWherePanel.setModelListener(aTblModelListener);
118        }
119
120        /**
121         * Do Clean up
122         */
123        public void shutdown() {
124                mTableList.removeListSelectionListener(this);
125                mTableView.removeFieldChangeListener(mDesktop);
126                mTableModel.removeTableModelListener(mTableModelListener);
127                mWherePanel.setModelListener(null);
128                mDesktop.setTableModelListener(null);
129
130                mTableView.setModel(null);
131                Vector tables = mDesktop.getTables();
132                if (tables != null && tables.size() > 0) {
133                        for (Enumeration et = tables.elements(); et.hasMoreElements();) {
134                                mTableModel.removeTableModelListener((TableModelListener) et
135                                                .nextElement());
136                        }
137                }
138                // Clean up
139                mSplitPane = null;
140                mSchema = null;
141                mDesktop = null;
142                mTableJoins = null;
143                mWherePanel = null;
144                mSelectPanel = null;
145                mTableView = null;
146                mTableModel = null;
147                mAddBtn = null;
148                mTableList = null;
149                mTableModelListener = null;
150        }
151
152        /**
153         * Return Schema
154         * 
155         * @return the schema
156         */
157        public DSSchemaIFace getSchema() {
158                return mDesktop;
159        }
160
161        /**
162         * Creates the DesktopPane that contains all the tables with "links" or
163         * joins.
164         * 
165         * @param aListener
166         *            the listener for the overall model changes
167         * @return the component representing the upper pane
168         */
169        public JComponent createUpperPanel(TableModelListener aListener) {
170                mDesktop = new DBTableDesktopPane();
171                mDesktop.setSchema(mSchema);
172                mDesktop.setTableModelListener(aListener);
173
174                // Create the mDesktop pane
175                mTableJoins = mDesktop.getTableJoins();
176
177                JScrollPane desktopScroller = new JScrollPane();
178                desktopScroller.getViewport().add(mDesktop);
179
180                DefaultListModel listModel = new DefaultListModel();
181                Vector tables = mSchema.getTables();
182                if (tables != null && tables.size() > 0) {
183                        for (Enumeration et = tables.elements(); et.hasMoreElements();) {
184                                DSTableIFace table = (DSTableIFace) et.nextElement();
185                                listModel.addElement(table.getName());
186                        }
187                }
188
189                JPanel rightSidePanel = new JPanel(new BorderLayout());
190                mTableList = new JList(listModel);
191                mTableList.addListSelectionListener(this);
192                mTableList.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
193
194                MouseListener mouseListener = new MouseAdapter() {
195                        public void mouseClicked(MouseEvent e) {
196                                if (e.getClickCount() == 2) {
197                                        mDesktop.addTableToWorkspace((String) mTableList
198                                                        .getSelectedValue());
199                                }
200                        }
201                };
202                mTableList.addMouseListener(mouseListener);
203
204                JScrollPane scroller = new JScrollPane();
205                scroller.getViewport().add(mTableList);
206
207                rightSidePanel.add(scroller, BorderLayout.CENTER);
208                rightSidePanel.add(new JLabel("Available Tables:"), BorderLayout.NORTH);
209
210                mAddBtn = new JButton("<- Add");
211                rightSidePanel.add(mAddBtn, BorderLayout.SOUTH);
212
213                valueChanged(null); // enables/disables the add button
214
215                JSplitPane splitPane = new JSplitPane(JSplitPane.HORIZONTAL_SPLIT,
216                                desktopScroller, rightSidePanel);
217                splitPane.setContinuousLayout(true);
218                splitPane.setOneTouchExpandable(true);
219
220                mAddBtn.addActionListener(new AbstractAction() {
221                        public void actionPerformed(ActionEvent e) {
222                                mDesktop.addTableToWorkspace((String) mTableList
223                                                .getSelectedValue());
224                        }
225                });
226
227                return splitPane;
228        }
229
230        /**
231         * Creates the "Select" panel for discribing what fields will have their
232         * values displayed
233         * 
234         * @param aSchema
235         *            the schema
236         * @return the component representing the select pane
237         */
238        public JPanel createSelectForDisplayPanel(DSSchemaIFace aSchema) {
239                JPanel panel = new JPanel(new BorderLayout());
240
241                // Create table mode and view for the DB Tables
242                mTableView = new DBSelectTableUIAdv();
243                mTableModel = new DBSelectTableModelAdv(aSchema);
244                mTableView.setColumnSelectionAllowed(false);
245                mTableView.setRowSelectionAllowed(true);
246                mTableView.setCellSelectionEnabled(true);
247
248                Vector tables = mDesktop.getTables();
249                mTableView.setModel(mTableModel);
250                for (int i = 0; i < tables.size(); i++) {
251                        mTableModel.addTableModelListener((TableModelListener) tables
252                                        .elementAt(i));
253                }
254                mTableView.installEditors();
255
256                JScrollPane scrollpane = new JScrollPane(mTableView);
257
258                panel.add(scrollpane, BorderLayout.CENTER);
259                return panel;
260        }
261
262        /**
263         * Creates the lower panel that contains the tabs "Select" and "Where"
264         * 
265         * @param aSchema
266         *            the schema
267         * @return the component representing the lower pane
268         */
269        public JComponent createLowerPanel() {
270                // create tab
271                JTabbedPane tabbedpane = new JTabbedPane();
272                mWherePanel = new DBWherePanel(mDesktop);
273                mSelectPanel = createSelectForDisplayPanel(mDesktop);
274                tabbedpane.add("Select", mSelectPanel);
275                tabbedpane.add("Where", mWherePanel);
276                return tabbedpane;
277        }
278
279        /**
280         * (future work)
281         * 
282         * @param aList
283         * @param aTableName
284         *       */
285        /*
286         * private DBJoinPrcTable getTable(Vector aList, String aTableName) { for
287         * (Enumeration et = aList.elements(); et.hasMoreElements();) {
288         * DBJoinPrcTable item = (DBJoinPrcTable)et.nextElement(); if
289         * (item.getTable().getName().equals(aTableName)) { return item; } } return
290         * null; }
291         */
292
293        /**
294         * (future work) Adds a "join" condition
295         * 
296         * @param aList
297         * @param aField
298         * @param aJoinItem
299         */
300        /*
301         * private void addJoinToTables(Vector aList, DBTableField aField,
302         * DBTableJoinItem aJoinItem) { DBJoinPrcTable item = getTable(aList,
303         * aField.getTable().getName()); if (item == null) { item = new
304         * DBJoinPrcTable(aField.getTable()); aList.add(item); }
305         * item.add(aJoinItem); }
306         */
307
308        /**
309         * Create SQL string
310         */
311        public String createSQL() {
312                Hashtable tableNames = new Hashtable();
313                StringBuffer strBuf = new StringBuffer("SELECT ");
314                DBSelectTableModelAdv model = (DBSelectTableModelAdv) mTableView
315                                .getModel();
316                int displayCnt = 0;
317                for (int i = 0; i < model.getRowCount(); i++) {
318                        DBSelectTableModelItem item = (DBSelectTableModelItem) model
319                                        .getFieldForRow(i);
320                        if (item.isDisplayed()) {
321                                tableNames.put(item.getTableName(), item.getTableName());
322                                displayCnt++;
323                        }
324                }
325                if (displayCnt == 0)
326                        return "No valid SQL to generate";
327
328                displayCnt = 0;
329                for (int i = 0; i < model.getRowCount(); i++) {
330                        DBSelectTableModelItem item = (DBSelectTableModelItem) model
331                                        .getFieldForRow(i);
332                        if (item.isDisplayed()) {
333                                if (displayCnt > 0) {
334                                        strBuf.append(", ");
335                                }
336                                displayCnt++;
337                                strBuf.append(DBUIUtils.getFullFieldName(item.getTableName(),
338                                                item.getName()));
339                                tableNames.put(item.getTableName(), item.getTableName());
340                        }
341                }
342                strBuf.append(" FROM ");
343
344                StringBuffer whereStr = new StringBuffer();
345                Vector joins = mTableJoins.getJoins();
346                int cnt = 0;
347                for (Enumeration et = joins.elements(); et.hasMoreElements();) {
348                        if (cnt > 0) {
349                                whereStr.append(" AND ");
350                        }
351                        cnt++;
352                        DBTableJoinItem joinItem = (DBTableJoinItem) et.nextElement();
353                        whereStr.append(DBUIUtils.getFullFieldName(joinItem.getItemLeft()));
354                        whereStr.append(" = ");
355                        whereStr
356                                        .append(DBUIUtils.getFullFieldName(joinItem.getItemRight()));
357                        String tblName = joinItem.getItemLeft().getTable().getName();
358                        tableNames.put(tblName, tblName);
359                        tblName = joinItem.getItemRight().getTable().getName();
360                        tableNames.put(tblName, tblName);
361                }
362
363                displayCnt = 0;
364                for (Enumeration et = tableNames.elements(); et.hasMoreElements();) {
365                        String tableName = (String) et.nextElement();
366                        if (tableName.indexOf(' ') != -1) {
367                                tableName = "[" + tableName + "]";
368                        }
369                        if (displayCnt > 0) {
370                                strBuf.append(", ");
371                        }
372                        displayCnt++;
373                        strBuf.append(tableName);
374                }
375                strBuf.append(" WHERE ");
376                strBuf.append(whereStr);
377                String wherePanelStr = mWherePanel.generateWhereSQL(true);
378                String noSpaces = wherePanelStr.trim();
379                if (noSpaces.length() > 0) {
380                        strBuf.append(" AND ");
381                        strBuf.append(wherePanelStr);
382                }
383
384                return strBuf.toString();
385
386        }
387
388        /**
389         * Makes the entire Desktop object repaint itself
390         * 
391         */
392        protected void refresh() {
393                // mDesktop.refresh();
394                if (mDesktop != null)
395                        mDesktop.makeDirty();
396        }
397
398        /**
399         * Fill the hastable with the table names
400         * 
401         * @param aWhereObj
402         *            the where object
403         * @param aHashTable
404         *            the hastable
405         */
406        protected void fillHashWithTableNamesForWhere(DBWhereIFace aWhereObj,
407                        Hashtable aHashTable) {
408                if (aWhereObj == null)
409                        return;
410
411                if (aWhereObj instanceof DBWhereCondition) {
412                        String tblName = ((DBWhereCondition) aWhereObj).getTableName();
413                        if (tblName.length() > 0)
414                                aHashTable.put(tblName, tblName);
415
416                } else {
417                        DBWhereOperator whereOper = (DBWhereOperator) aWhereObj;
418                        for (Enumeration e = whereOper.getEnumeration(); e
419                                        .hasMoreElements();) {
420                                fillHashWithTableNamesForWhere((DBWhereIFace) e.nextElement(),
421                                                aHashTable);
422                        }
423                }
424        }
425
426        // ---------------------------------------------------
427        // -- ListSelectionListener
428        // ---------------------------------------------------
429        public void valueChanged(ListSelectionEvent e) {
430                mAddBtn.setEnabled(mTableList.getSelectedIndex() != -1);
431        }
432
433        // ---------------------------------------------------
434        // -- QBBuilderInterface
435        // ---------------------------------------------------
436
437        /**
438         * 
439         * @return returns the "type" of builder it is as defined by the constants
440         *         in this interface
441         */
442        public int getType() {
443                return QBBuilderInterface.ADVANCED;
444        }
445
446        /**
447         * A textual name for this builder
448         * 
449         * @return string of the name
450         */
451        public String getName() {
452                return "Advanced";
453        }
454
455        /**
456         * Returns whether their will be data loss if this query is converted to a
457         * "standard" query meaning we have defined some "where" items, but have not
458         * created any "display" items
459         * 
460         * @return true if possible data loss
461         */
462        public boolean possibleDataLoss() {
463                boolean atLeastOneForDisplay = false;
464                for (int i = 0; i < mTableModel.getRowCount(); i++) {
465                        DBSelectTableModelItem item = (DBSelectTableModelItem) mTableModel
466                                        .getFieldForRow(i);
467                        if (item.isDisplayed()) {
468                                atLeastOneForDisplay = true;
469                        }
470                }
471
472                return mTableModel.getRowCount() > 1 && !atLeastOneForDisplay
473                                && mWherePanel.getModel().getSize() > 0;
474        }
475
476        /**
477         * This checks to see if this type of builder can convert the internal SQL
478         * to a more complex or less complex form.
479         * 
480         * This is typically called when switching from a more complex builder to a
481         * less complex builder
482         * 
483         * @param aBldr
484         *            The "receiving" builder, in other words can this builder
485         *            convert the SQL to the new builder
486         * @return true if it can convert it, false if it can not
487         */
488        public boolean canConvertTo(QBBuilderInterface aBldr) {
489                switch (aBldr.getType()) {
490                case QBBuilderInterface.STANDARD:
491                        return mTableJoins.mJoinItems.size() == 0
492                                        && !mWherePanel.isComplex();
493
494                case QBBuilderInterface.INTERMEDIATE:
495                        return !mWherePanel.isComplex();
496
497                case QBBuilderInterface.ADVANCED:
498                        return true;
499                }
500
501                return false;
502        }
503
504        /**
505         * Build UI from the Query Definition Object
506         * 
507         * @param aQueryDef
508         *            the query
509         */
510        public int buildFromQueryDef(DBQueryDef aQueryDef) {
511                if (aQueryDef != null) {
512                        mWherePanel.getModel().initialize(aQueryDef.getWhere());
513                        mWherePanel.generateAndSetWhereText();
514                        mWherePanel.valueChanged(null);
515                        mTableModel.buildFromQueryDef(aQueryDef);
516
517                        mDesktop.clearTables();
518                        for (Enumeration e = aQueryDef.getTables().elements(); e
519                                        .hasMoreElements();) {
520                                mDesktop.addTableToWorkspace((DBQueryDefTable) e.nextElement());
521                        }
522
523                        // clear and build joins
524                        mTableJoins.clear();
525                        if (aQueryDef.getJoins() != null) {
526                                for (Enumeration e = aQueryDef.getJoins().elements(); e
527                                                .hasMoreElements();) {
528                                        DBSelectTableModelItem left = (DBSelectTableModelItem) e
529                                                        .nextElement();
530                                        DBSelectTableModelItem right = (DBSelectTableModelItem) e
531                                                        .nextElement();
532                                        mTableJoins.addJoin(left, right);
533                                }
534                        }
535
536                        refresh();
537                        return DBQueryDef.BUILD_OK;
538                }
539                return DBQueryDef.BUILD_ERROR;
540        }
541
542        /**
543         * Fill the QueryDef from the Model
544         * 
545         * @param aQueryDef
546         *            the query
547         */
548        public void fillQueryDef(DBQueryDef aQueryDef) {
549                if (aQueryDef == null) {
550                        return;
551                }
552                aQueryDef.setIsAdv(true);
553                mTableModel.fillQueryDef(aQueryDef);
554                mWherePanel.fillQueryDef(aQueryDef);
555                Hashtable tableNamesHash = new Hashtable();
556                fillHashWithTableNamesForWhere(aQueryDef.getWhere(), tableNamesHash);
557                Vector tables = new Vector();
558
559                Component tableFrames[] = mDesktop
560                                .getComponentsInLayer(JDesktopPane.DEFAULT_LAYER.intValue());
561                for (int i = 0; i < tableFrames.length; i++) {
562                        if (tableFrames[i] instanceof DBTableFrame) {
563                                DBTableFrame tbl = (DBTableFrame) tableFrames[i];
564                                aQueryDef.addTable(tbl.getId(), tbl.getName(), tbl
565                                                .getLocation().x, tbl.getLocation().y);
566                                // remove a duplicate table name
567                                tableNamesHash.remove(tbl.getName());
568                        }
569                }
570
571                // add any table names that where used in a where clause but not in the
572                // joins
573                for (Enumeration e = tableNamesHash.elements(); e.hasMoreElements();) {
574                        aQueryDef.addTable((String) e.nextElement());
575                }
576
577                if (mTableJoins.getJoins().size() > 0) {
578                        Vector joins = new Vector();
579                        for (Enumeration e = mTableJoins.getJoins().elements(); e
580                                        .hasMoreElements();) {
581                                DBTableJoinItem joinItem = (DBTableJoinItem) e.nextElement();
582                                DBSelectTableModelItem item = new DBSelectTableModelItem();
583                                item.setTableName(joinItem.getItemLeft().getTable().getName());
584                                item.setName(joinItem.getItemLeft().getName());
585                                item.setTableId(joinItem.getItemLeft().getTable().getId());
586                                joins.add(item);
587
588                                item = new DBSelectTableModelItem();
589                                item.setTableName(joinItem.getItemRight().getTable().getName());
590                                item.setName(joinItem.getItemRight().getName());
591                                item.setTableId(joinItem.getItemRight().getTable().getId());
592                                joins.add(item);
593                        }
594                        if (joins.size() > 0) {
595                                aQueryDef.setJoins(joins);
596                        }
597                }
598        }
599
600}