Java API By Example, From Geeks To Geeks.

1 /* 2  * The contents of this file are subject to the terms of the Common Development 3  * and Distribution License (the License). You may not use this file except in 4  * compliance with the License. 5  * 6  * You can obtain a copy of the License at http://www.netbeans.org/cddl.html 7  * or http://www.netbeans.org/cddl.txt. 8  * 9  * When distributing Covered Code, include this CDDL Header Notice in each file 10  * and include the License file at http://www.netbeans.org/cddl.txt. 11  * If applicable, add the following below the CDDL Header, with the fields 12  * enclosed by brackets [] replaced by your own identifying information: 13  * "Portions Copyrighted [year] [name of copyright owner]" 14  * 15  * The Original Software is NetBeans. The Initial Developer of the Original 16  * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun 17  * Microsystems, Inc. All Rights Reserved. 18  */ 19 package org.netbeans.modules.db.sql.visualeditor.querybuilder; 20 import org.netbeans.modules.db.sql.visualeditor.ui.ConnectionStatusPanel; 21 22 import java.awt.Component ; 23 import java.awt.Dialog ; 24 import java.awt.Cursor ; 25 26 import java.awt.event.ActionEvent ; 27 import java.awt.event.ActionListener ; 28 import java.awt.event.KeyEvent ; 29 import java.awt.event.KeyListener ; 30 31 import java.awt.datatransfer.Clipboard ; 32 import java.awt.datatransfer.ClipboardOwner ; 33 import java.awt.datatransfer.Transferable ; 34 35 import java.beans.PropertyChangeEvent ; 36 import java.beans.PropertyChangeListener ; 37 import java.sql.Connection ; 38 import java.util.Iterator ; 39 40 import javax.swing.JButton ; 41 import javax.swing.JComponent ; 42 import javax.swing.ImageIcon ; 43 import javax.swing.JDialog ; 44 import javax.swing.JOptionPane ; 45 import javax.swing.SwingUtilities ; 46 import javax.swing.AbstractAction ; 47 import javax.swing.KeyStroke ; 48 import javax.swing.InputMap ; 49 import javax.swing.ActionMap ; 50 import javax.swing.JFrame ; 51 import javax.swing.RepaintManager ; 52 53 import javax.swing.text.DefaultEditorKit ; 54 55 import java.sql.DatabaseMetaData ; 56 import java.sql.ResultSet ; 57 import java.sql.SQLException ; 58 59 import java.util.ArrayList ; 60 import java.util.List ; 61 import java.util.HashMap ; 62 import java.util.Map ; 63 64 import org.openide.DialogDescriptor; 65 import org.openide.ErrorManager; 66 import org.openide.NotifyDescriptor; 67 import org.openide.DialogDisplayer; 68 import org.openide.actions.DeleteAction; 69 import org.openide.nodes.Node; 70 71 import org.openide.util.HelpCtx ; 72 import org.openide.util.NbBundle; 73 import org.openide.util.actions.ActionPerformer; 74 import org.openide.util.actions.SystemAction; 75 76 import org.openide.windows.TopComponent; 77 import org.openide.windows.WindowManager; 78 79 import org.netbeans.modules.db.sql.visualeditor.Log; 80 81 import org.netbeans.modules.db.sql.visualeditor.querymodel.ColumnProvider; 82 import org.netbeans.modules.db.sql.visualeditor.querymodel.Column; 83 import org.netbeans.modules.db.sql.visualeditor.querymodel.JoinTable; 84 import org.netbeans.modules.db.sql.visualeditor.querymodel.OrderBy; 85 86 import org.netbeans.modules.db.sql.visualeditor.parser.ParseException; 87 import org.netbeans.modules.db.sql.visualeditor.parser.TokenMgrError; 88 // import com.sun.rave.designtime.DesignBean; 89 90 // import org.netbeans.modules.visualweb.sql.DatabaseMetaDataHelper; 91 // import org.netbeans.modules.visualweb.sql.TableMetaData; 92 93 import java.sql.PreparedStatement ; 94 import java.sql.ParameterMetaData ; 95 96 /** 97  * The top-level class for the QueryBuilder. 98  * 99  * This is the class that gets invoked from outside, to create QueryBuilder 100  * instances. It also handles communication between the QueryBuilder panes and the 101  * data source (RowSet, Connection), including retrieving metadata, executing 102  * queries, and retrieving result sets. 103  * 104  * @author Sanjay Dhamankar, Jim Davidson, then hacked apart, chewed up, by jfbrown, but still needs more! 105  */ 106 public class QueryBuilder extends TopComponent 107         implements 108         ClipboardOwner , 109         KeyListener , 110         ColumnProvider { 111 112     // this stores the cache of query build objects 113 private static Map qbMap = new HashMap (); 114 115     private String lastQuery; 116 117     // DO NOT CHANGE the next line. Caching should not be implemented in this class. 118 private static final boolean CACHE_QueryBulderInstances = false ; 119 120     // the boolean below is used to determine if we need to store 121 // the query in the backing file. First time when 'generateText' is 122 // called we avoid saving the query in the backing file as 123 // this unnecessarily sets the save buttons ON. 124 private boolean firstTimeGenerateText = true; 125 126     // Provide package access on these, for use by other classes in the Query Builder 127 128     QueryBuilderPane _queryBuilderPane; 129     QueryModel _queryModel; 130     boolean _updateModel = true; 131     boolean _updateText = true; 132     boolean _graphicsEnabled = true; 133 134     // Private variables 135 136     private boolean DEBUG = false; 137     // true if database is connected, false otherwise. 138 139     // private DatabaseMetaData _databaseMetaData; 140 // private DatabaseMetaDataHelper _dbmdh; 141 142     private SqlStatementMetaDataCache metaDataCache = null ; 143 144     // all the schema names in the datasource. 145 // private List _schemaNames = null; 146 // all the table/view names in the datasource. 147 // private List _tableNames = null; 148 // all the column names for each table/view name in the datasource. 149 // private List _columnNames = null; 150 151     // used to make sure that the _tableNames is initialized only once. 152 // private List _tableColumns ; 153 154     private String _parseErrorMessage = null ; 155 156     // used for syntax highlighting 157 public boolean isSchemaName( String schemaName ) { 158         String [] schemas = metaDataCache.getSchemas() ; 159         for (int i = 0 ; i < schemas.length ; i++ ) { 160             if ( schemas[i].equals(schemaName)) { 161                 Log.log(" found schema name "+schemaName) ; 162                 return true ; 163             } 164         } 165         return false ; 166     } 167 168     public boolean isTableName( String tableName ) { 169         try { 170             String x = checkTableName( tableName ) ; 171             if ( x != null ) 172                 return true ; 173         } catch( SQLException se) { 174             // exception handled elsewhere. 175 } 176         return false ; 177     } 178 179     public boolean isColumnName( String columnName ) { 180          181         try { 182             return metaDataCache.getAllColumnNames().containsKey(columnName) ; 183         }catch( SQLException se) { 184             // exception handled elsewhere. 185 } 186         return false ; 187     } 188 189     ///////////////////////////////////////////////////////////////////////// 190 // Delete support 191 ///////////////////////////////////////////////////////////////////////// 192 193     /** delete action performer */ 194     private final transient DeleteActionPerformer deleteActionPerformer = new DeleteActionPerformer(); 195 196     /** copy action performer */ 197     protected final transient CopyCutActionPerformer copyActionPerformer = 198         new CopyCutActionPerformer(true); 199 200     /** cut action performer */ 201     protected final transient CopyCutActionPerformer cutActionPerformer = 202         new CopyCutActionPerformer(false); 203 204      205     // Implements ClipboardOwner 206 public void lostOwnership(Clipboard clipboard, Transferable contents) { 207     } 208 209     boolean isSelectionEmpty() { 210         Node[] nodes = getActivatedNodes(); 211         if ( (nodes != null) && (nodes.length != 0) ) 212             return false; 213         else 214             return true; 215     } 216 217     private boolean isActivated() { 218         return this == TopComponent.getRegistry().getActivated(); 219     } 220 221     /*** do not save across IDE session ***/ 222     public int getPersistenceType() { 223         return TopComponent.PERSISTENCE_NEVER ; 224     } 225 226     public String preferredID() { 227         return "QueryEditor" ; //NOI18N 228 } 229 230 231 232     /** Called when this window is activated: make delete 233      * sensitive based on whether or not anything is selected and whether 234      * the clipboard contains something we can absorb. */ 235     public void activateActions() { 236         if (isSelectionEmpty()) { 237             disableDelete(); 238         } else { 239             enableDelete(); 240         } 241         // for now cut / copy is always disabled. 242 disableCutCopy(); 243     } 244 245     /** Called when the when the component is deactivated. We no longer 246      * allow our paste types to be invoked so clear it - get rid of 247      * the action performers as well. */ 248     public void deactivateActions() { 249         if (deleteActionPerformer != null) { 250 // delete.setActionPerformer(null); 251 } 252     } 253 254     /** Called when the selection is non zero and the component is active: 255      * enable cut, copy and delete */ 256     void enableDelete() { 257         if (!isActivated()) { 258             return; 259         } 260         deleteActionPerformer.setEnabled(true); 261     } 262 263     /** Called when the selection is removed: disable delete */ 264     void disableDelete() { 265         if (!isActivated()) { 266             return; 267         } 268         deleteActionPerformer.setEnabled(false); 269         DeleteAction da = (DeleteAction)SystemAction.get(DeleteAction.class); 270         da.setEnabled(false); 271     } 272 273     /** Called when the selection is removed: disable cut/copy */ 274     void disableCutCopy() { 275         if ( DEBUG ) 276             System.out.println(" disableCutCopy called. " + "\n" ); // NOI18N 277 // Fix 6265915 Copy/Cut menu items are always enabled 278 cutActionPerformer.setEnabled(false); 279         copyActionPerformer.setEnabled(false); 280     } 281 282     /** Class which performs delete action */ 283     class DeleteActionPerformer extends AbstractAction implements ActionPerformer { 284 285         public void actionPerformed(ActionEvent e) { 286             performAction(null); 287         } 288 289         // Perform delete action. 290 public void performAction(SystemAction action) { 291             // We run into deadlocks without this; !#$!@#!@ ModuleActions thread 292 SwingUtilities.invokeLater(new Runnable () { 293                 public void run() { 294                     deleteSelection(); 295                 } 296             } 297             ); 298         } 299     } 300 301     /** Class which performs copy and cut actions */ 302     class CopyCutActionPerformer extends AbstractAction implements ActionPerformer { 303         /** determine if adapter is used for copy or cut action. */ 304         boolean isCopy; 305 306         /** Create new adapter */ 307         public CopyCutActionPerformer(boolean b) { 308             isCopy = b; 309         } 310 311         public void actionPerformed(ActionEvent e) { 312             performAction(null); 313         } 314 315         /** Perform copy or cut action. */ 316         public void performAction(SystemAction action) { 317             // for now do nothing 318 } 319     } 320 321 322     /** Remove the currently selected components */ 323     void deleteSelection() { 324         if ( DEBUG ) 325             System.out.println(" deleteSelection called. " + "\n" ); // NOI18N 326 java.awt.KeyboardFocusManager kbfm = java.awt.KeyboardFocusManager.getCurrentKeyboardFocusManager(); 327         java.awt.Component c = kbfm.getFocusOwner(); 328         if ( c != null ) { 329             java.awt.Container p = c.getParent(); 330             while ( p != null ) { 331                 if ( p instanceof QueryBuilderGraphFrame ) { 332 333                     Node[] nodes = getActivatedNodes(); 334                     if (nodes == null || nodes.length == 0) { 335                         return; 336                     } 337                     for (int i = 0; i < nodes.length; i++) { 338                         if ( ( nodes[i] instanceof CondNode ) ) { 339                             _queryBuilderPane.getQueryBuilderGraphFrame().removeNode((CondNode)nodes[i]); 340                         } else if ( ( nodes[i] instanceof JoinNode ) ) { 341                             _queryBuilderPane.getQueryBuilderGraphFrame().removeNode((JoinNode)nodes[i]); 342                         } else if ( ( nodes[i] instanceof TableNode ) ) { 343                             _queryBuilderPane.getQueryBuilderGraphFrame().removeNode((TableNode)nodes[i]); 344                         } 345                     } 346                 } 347             p = p.getParent(); 348             } 349         } 350     } 351 352     protected void installActions(ActionMap map, InputMap keys) { 353        /* 354         map.put(DefaultEditorKit.copyAction, copyActionPerformer); 355         map.put(DefaultEditorKit.cutAction, cutActionPerformer); 356         // Paste still done the old way... 357         //map.put(DefaultEditorKit.pasteAction, pasteActionPerformer); 358         */ 359         map.put("delete", deleteActionPerformer); // or false 360 map.put(DefaultEditorKit.copyAction, copyActionPerformer); 361         map.put(DefaultEditorKit.cutAction, cutActionPerformer); 362 363         /* 364         // Popup menu from the keyboard 365         map.put ("org.openide.actions.PopupAction", 366                 new AbstractAction() { 367             public void actionPerformed(ActionEvent evt) { 368                 SwingUtilities.invokeLater(new Runnable() { 369                     public void run() { 370                         showKeyboardPopup(); 371                     } 372                 }); 373             } 374         }); 375 376         keys.put(KeyStroke.getKeyStroke("control C"), DefaultEditorKit.copyAction); 377         keys.put(KeyStroke.getKeyStroke("control X"), DefaultEditorKit.cutAction); 378         keys.put(KeyStroke.getKeyStroke("control V"), DefaultEditorKit.pasteAction); 379          */ 380         keys.put(KeyStroke.getKeyStroke("DELETE"), "delete"); 381     } 382 383 384     /** 385      * Static factory method, added for access from RowSet 386      * @Return a QueryBuilder instance, either new or retrieved from the Map 387      */ 388     public static Component openCustomizerPanel( SqlStatement sqlStatement) { 389 390         Log.err.log(ErrorManager.INFORMATIONAL, 391                 "Entering QueryBuilder.openCustomizerPanel"); // NOI18N 392 393     showBusyCursor( true ); 394 395         QueryBuilder qb ; 396         try { 397             qb = new QueryBuilder(sqlStatement); 398         } catch (SQLException sqle ) { 399             qb = null ; 400             // TODO: popup an error dialog. 401 ConnectionStatusPanel csp = new ConnectionStatusPanel() ; 402             csp.configureDisplay(sqlStatement.getConnectionInfo(), false,sqle.getLocalizedMessage(), "", 0, false ) ; 403             // csp.setGeneralInfo("") ; 404 csp.displayDialog( sqlStatement.getConnectionInfo() ) ; 405         } 406         final QueryBuilder queryBuilder = qb ; 407         SwingUtilities.invokeLater( new Runnable () { 408             public void run() { 409                 if ( queryBuilder != null) { 410                     queryBuilder.open(); 411                     queryBuilder.requestActive(); 412                 } 413                 showBusyCursor( false ); 414             } 415         }) ; 416 417         queryBuilder.getTextAreaFocusInvokeLater(); 418 419         return queryBuilder; 420     } 421 422     // --- changes for designtime/insync avoidance. 423 private SqlStatement sqlStatement = null ; 424     // private Connection connection = null ; 425 426     private QueryBuilder(SqlStatement sqlStatement) throws SQLException { 427 428         Log.err.log(ErrorManager.INFORMATIONAL, "Entering QueryBuilder ctor"); // NOI18N 429 430         // Record the bean that created us, and the unique name 431 this.sqlStatement = sqlStatement ; 432         433         this.metaDataCache = sqlStatement.getMetaDataCache() ; 434          435         String title = sqlStatement.getTitle() ; 436          437         // Set the name to display 438 setName(title); 439         setDisplayName(title); 440 441         setLayout(new java.awt.BorderLayout ()); 442 443         ImageIcon imgIcon = 444                 new ImageIcon (getClass().getResource("/org/netbeans/modules/db/sql/visualeditor/resources/query-editor-tab.png")); // NOI18N 445 if (imgIcon != null) 446             setIcon(imgIcon.getImage()); 447 448         _queryBuilderPane = new QueryBuilderPane(this); 449 450         // Add the pane to the end of the QueryBuilder container 451 add(_queryBuilderPane); 452 453         addKeyListener(this); 454     } 455 456     void getGraphFrameCanvasFocus() { 457         _queryBuilderPane.getQueryBuilderGraphFrame().getCanvasFocus (); 458     } 459 460     void getTextAreaFocusInvokeLater() { 461         javax.swing.SwingUtilities.invokeLater(new Runnable () { 462             public void run() { 463                 _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocus( true ); 464                 _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocusInWindow (); 465             } 466         }) ; 467     } 468     /** ignore */ 469     public void keyTyped(KeyEvent e) { 470     } 471 472     /** ignore */ 473     public void keyReleased(KeyEvent e) { 474     } 475 476     /** Handle the key pressed event and change the focus if a particular 477      * key combination is pressed. */ 478     public void keyPressed(KeyEvent e) { 479         handleKeyPress(e); 480     } 481 482     public void handleKeyPress(KeyEvent e) { 483         if( e.isAltDown() ) { 484             int code = e.getKeyCode(); 485             switch(code) { 486                 // diagram pane 487 case KeyEvent.VK_1: 488                     if ( DEBUG ) 489                         System.out.println(" Alt + 1 pressed. "); // NOI18N 490 // _queryBuilderPane.getQueryBuilderGraphFrame().getFocus (); 491 getGraphFrameCanvasFocus(); 492                     break; 493                     // grid pane 494 case KeyEvent.VK_2: 495                     if ( DEBUG ) 496                         System.out.println(" Alt + 2 pressed. "); // NOI18N 497 if ( _queryBuilderPane.getQueryBuilderInputTable().getRowCount() > 0 ) { 498                         _queryBuilderPane.getQueryBuilderInputTable().setRowSelectionInterval(0, 0); 499                         _queryBuilderPane.getQueryBuilderInputTable().requestFocus( true ); 500                     } 501                     break; 502                     // SQL text pane 503 case KeyEvent.VK_3: 504                     if ( DEBUG ) 505                         System.out.println(" Alt + 3 pressed. "); // NOI18N 506 _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocus( true ); 507                     break; 508                     // Result Pane 509 case KeyEvent.VK_4: 510                     if ( DEBUG ) 511                         System.out.println(" Alt + 4 pressed. "); // NOI18N 512 _queryBuilderPane.getQueryBuilderResultTable().requestFocus( true ); 513                     break; 514             } 515         } 516     } 517 518     public SqlStatement getSqlStatement() { 519         return this.sqlStatement ; 520     } 521      522     List getCachedAllTablesInDataSource() throws SQLException { 523         return metaDataCache.getTables() ; 524         /* 525         if (_tableNames == null ) { 526             _tableColumns = createTableColumns() ; 527         } 528         return _tableNames; 529          **/ 530     } 531 532     /** 533      * Check if the table name exists 534      * Case is ignored while searching 535      * If table does not exist then return null 536      * else return the same name if it exactly matches 537      * else return the name from the database 538      * 539      * tableName can be schema.table or just table - look for both. 540      */ 541 542     String checkTableName( String tableName ) throws SQLException { 543 544         if ( tableName == null || tableName.length() < 1) { 545             return tableName ; 546         } 547         Log.log("checkTableName called. tableName = " + tableName); // NOI18N 548 549         String [] descrip = parseTableName(tableName) ; 550         String paramSchemaName = descrip[0] ; 551         String paramTableName = descrip[1] ; 552          553         if ( paramSchemaName != null ) { 554             return checkFullTableName( tableName ) ; 555         } 556 557         String returnTable = null ; 558          559         List tables = metaDataCache.getTables() ; 560         // now search for the tablename in the list. 561 for ( Iterator i = tables.iterator(); i.hasNext() ; ) { 562             // first check if the table name exists as is 563 String fullNameDb = (String )i.next() ; 564             String tableNameDb = parseTableName(fullNameDb)[1] ; 565             if ( tableNameDb.equalsIgnoreCase( paramTableName ) ) { 566                 returnTable = fullNameDb ; 567                 break ; 568             } 569         } 570          571         if ( returnTable == null ) { 572             String fullAliasTableName = _queryModel.getFullTableName(paramTableName) ; 573             if ( fullAliasTableName != null && tableName.equals(fullAliasTableName)) { 574                 return null; 575 576             } else if ( fullAliasTableName != null ) { 577                  return checkTableName( fullAliasTableName ) ; 578             } 579         } 580 581         if ( returnTable != null ) 582             metaDataCache.getColumnNames(returnTable ) ; 583 584         // table name was not found 585 return returnTable ; 586     } 587 588     /** 589      * Check if the full table name exists in the _tableColumns 590      * Case is ignored while searching 591      * If table does not exist then return null 592      * 593      * return the same name if it exactly matches 594      * if case differs, return the full name from the database 595      * if it's an alias and there's a cases-insenstive match, return the full name from the database 596      * 597      * This function is called only from checkFrom. 598      * Cases to be considered : 599      * case 1 : <schema_name>.<table_name> 600      * case 2 : <table_name> SHOULD NOT EXIST. 601      * case 3 : <alias_table_name> 602      * 603      * return the 604      * 605      */ 606 607     String checkFullTableName( String fullTableName ) throws SQLException { 608 609         Log.log("checkFullTableName called. tableName = " + fullTableName ); // NOI18N 610 611         String returnTable = null ; 612 613         if ( parseTableName(fullTableName)[0] == null ) { 614             // no schema name, so fullTableName is really just a tableName. 615 return checkTableName(fullTableName) ; 616         } 617 618         List tables = metaDataCache.getTables() ; 619         for ( Iterator i = tables.iterator(); i.hasNext() ; ) { 620             // first check if the table name exists as is 621 String fullNameDb = (String )i.next() ; 622             if ( fullNameDb.equalsIgnoreCase( fullTableName ) ) { 623                 returnTable = fullNameDb ; 624                 break ; 625             } 626         } 627          628         // Load the column cache for this table. 629 if ( returnTable != null ) metaDataCache.getColumnNames(returnTable ) ; 630          631         return returnTable ; 632     } 633 634 635     /** 636      * Check if the column name exists in the _tableColumns 637      * case is ignored while searching 638      * if column does not exist then return null 639      * else return the same name if it exactly matches 640      * else return the name from the database 641      */ 642     String checkColumnName( String tableName, String columnName ) throws SQLException { 643         Log.log("checkColumnName() " + tableName + ", " + columnName ); // NOI18N 644 645         String tabName = checkTableName( tableName ) ; 646          647         List columns = metaDataCache.getColumnNames(tabName) ; 648          649         if ( columns == null ) return null ; 650          651         for ( int k = 0; k < columns.size(); k++ ) { 652             String columnDB = (String ) columns.get(k); 653             // first check if the column name exists "as is" 654 if ( columnName.equals( columnDB ) ) { 655                 return columnName; 656             } 657             // otherwise compare ignoring case, if matched return the 658 // name from database 659 else if ( columnName.equalsIgnoreCase( columnDB ) ) { 660                 return columnDB ; 661             } 662         } 663          664         // column name was not found 665 return null; 666     } 667 668     /** 669      * Given a column and table name checks if the table name stored in the 670      * column matches with the one in the database. 671      * Updates the column name with the one in the database and returns true 672      * false otherwise. 673      */ 674 675     boolean checkColumnNameForTable( Column col, String tableName ) { 676         String columnName = col.getColumnName(); 677          678         Log.log("checkColumnNameForTable for " + tableName + " . " + columnName ); // NOI18N 679 680         String fullTableNameFromAlias = _queryModel.getFullTableName( tableName ); 681         if ( fullTableNameFromAlias != null ) { 682             tableName = fullTableNameFromAlias ; 683         } 684         boolean retVal = false ; 685          686         // TODO JFB should not catch this. 687 List cols ; 688         String checkedTable ; 689         try { 690             checkedTable = checkTableName( tableName ) ; 691             if (checkedTable == null) return false ; 692             cols = metaDataCache.getColumnNames(checkedTable) ; 693         } catch (SQLException sqle) { 694             Log.log(" ** problems getting metadata " + sqle.getMessage()) ; 695             return false ; 696 697         } 698         if ( "*".equals(columnName)) { // NOI18N 699 retVal = true ; 700             if ( fullTableNameFromAlias == null && ! ( checkedTable.equals(col.getTableSpec()) ) ) { 701                 col.setTableSpec(col.getTableSpec(), checkedTable ) ; 702                 Log.log( " adjust table to " + checkedTable) ; 703             } 704         } else { 705             for ( int icnt = 0 ; icnt < cols.size() ; icnt++ ) { 706                 if ( columnName.equalsIgnoreCase( (String )cols.get(icnt))) { 707                     col.setColumnName(col.getColumnName(), (String )cols.get(icnt) ); 708                     Log.log( " adjust colname to " + (String )cols.get(icnt) ) ; 709                     if ( col.getTableSpec() == null ) { 710                         col.setTableSpec(col.getTableSpec(), checkedTable ) ; 711                         Log.log( " adjust table to " + checkedTable) ; 712                     } 713                     retVal = true ; 714                     break ; 715                 } 716             } 717         } 718          719         Log.log("checkColumnNameForTable found="+retVal ); // NOI18N 720 721         return retVal ; 722          723         /*** 724         for ( int i = 0; i < _tableColumns.size(); i++ ) { 725             TableColumns tableColumn = (TableColumns) _tableColumns.get(i); 726             String _tableName = tableColumn.getTableName(); 727             // first check if the table name exists "as is" 728             // table name must already be valid using checkTableName 729             // reset the column's table spec. 730             if ( _tableName.equals( tableName ) ) { 731 732                 List columns = tableColumn.getColumns(); 733                 for ( int k = 0; k < columns.size(); k++ ) { 734                     String _columnName = (String) columns.get(k); 735                     if ( ( _columnName.equals( columnName ) ) || 736                             ( _columnName.equalsIgnoreCase( columnName ) ) ) { 737                         // change the column's table name 738                         col.setTableSpec(col.getTableSpec(), tableName); 739                         // change the column's name to the correct one. 740                         col.setColumnName(col.getColumnName(), _columnName); 741                         return true; 742                     } 743                 } 744             } else { 745                 // check if the tableName is actually an alias 746                 String fullTableNameFromAlias = 747                         _queryModel.getFullTableName( tableName ); 748                 if ( fullTableNameFromAlias != null ) { 749                     // tableName is an alias 750                     if ( _tableName.equals( fullTableNameFromAlias ) ) { 751                         if ( ! tableColumn.columnsLoaded() ) { 752                             loadColumns( tableColumn ); 753                         } 754                         List columns = tableColumn.getColumns(); 755                         for ( int k = 0; k < columns.size(); k++ ) { 756                             String _columnName = (String) columns.get(k); 757                             if ( ( _columnName.equals( columnName ) ) || 758                                     ( _columnName.equalsIgnoreCase( columnName ) ) ) { 759                                 // change the column's table name 760                                 col.setColumnTableName(fullTableNameFromAlias); 761                                 // change the column's corr name 762                                 col.setColumnCorrName(tableName); 763                                 // change the column's name to the correct one. 764                                 col.setColumnName(col.getColumnName(), _columnName); 765                                 return true; 766                             } 767                         } 768                     } 769                 } 770             } 771         } 772         return false; 773          ***/ 774     } 775 776     // checks the table name and column name given a col. 777 // if possible corrects the column name and table name 778 // otherwise returns false, the caller is supposed to give an error message 779 boolean checkTableColumnName( Column col ) throws SQLException { 780 781         String tableSpec = col.getTableSpec() ; 782         String tableName = col.getFullTableName() ; 783         String colName = col.getColumnName() ; 784          785         Log.log("checkTableColumnName called. " + " tableSpec = " + tableSpec + 786                     " tableName = " + tableName + " . " + colName ); // NOI18N 787 788         if ( "*".equals(col.getColumnName() ) && tableSpec == null ) { 789             // Column name was "*" with no tableSpec - assume it's OK. 790 Log.log("checkTableColunName col=*, notable ") ; 791             return true ; //NOI18N 792 } 793         String checkedTableName = checkTableName( tableSpec ) ; 794 795         String fullTableNameFromAlias = null ; 796 // if ( checkedTableName == null && tableSpec != null ) { 797 // why the above check ? This will not set fullTableNameFromAlias 798 // regression 799 // http://daning.sfbay/cvsweb/queryeditor/src/com/sun/rave/queryeditor/querybuilder/QueryBuilder.java.diff?r1=1.133&r2=1.134&cvsroot=/cvs/rave 800 fullTableNameFromAlias = _queryModel.getFullTableName( tableSpec ); 801 // } 802 Log.log("checkTableColumnName called. " + 803                     " checkedTableName = " + checkedTableName + 804                     " fullTableNameFromAlias = " + fullTableNameFromAlias ); // NOI18N 805 if ( checkedTableName == null ) { 806             // table not found 807 return false; // let the caller display the error 808 } 809 810         // table has alias set corr name and set table name. 811 // Added the following check to fix : 812 // 5061214 Cannot parse a query with alias. 813 else if ( ( fullTableNameFromAlias != null ) && 814                 ( ! fullTableNameFromAlias.equalsIgnoreCase( tableSpec ) ) ) { 815             if (DEBUG) 816                 System.out.println("setColumnTableName called. " + 817                         " checkedTableName = " + checkedTableName + 818                         " tableSpec = " + tableSpec + 819                         " fullTableNameFromAlias = " + fullTableNameFromAlias + "\n" ); // NOI18N 820 col.setColumnTableName( checkedTableName ); 821             col.setColumnCorrName( tableSpec ); 822         } else if ( ! checkedTableName.equals( tableName ) ) { 823             // table found but maybe in a wrong case, replace 824 // it in the querymodel 825 if (DEBUG) 826                 System.out.println("setTableSpec called. " + 827                         " checkedTableName = " + checkedTableName + "\n" ); // NOI18N 828 col.setTableSpec( tableName, checkedTableName ); 829         } 830 831         String columnName = col.getColumnName(); 832 833         if (columnName.equals("*")) return true ; 834          835         String checkedColumnName = checkColumnName( checkedTableName, 836                 columnName ) ; 837         if (DEBUG) 838             System.out.println("column Name = " + columnName + "\n" // NOI18N 839 + "checked column Name = " + checkedColumnName + "\n" ); // NOI18N 840 if ( checkedColumnName == null ) { 841             // column not found 842 return false; // let the caller display the error 843 } else if ( ! checkedColumnName.equals( columnName ) ) { 844             if (DEBUG) 845                 System.out.println("set column name called. oldColumnName = " + columnName + //NOI18N 846 " newColumnName = " + checkedColumnName + "\n" ); // NOI18N 847 // column found but maybe in a wrong case, replace 848 // it in the querymodel 849 col.setColumnName( columnName, checkedColumnName ); 850         } 851 852         return true; 853     } 854 855     /**** 856      * Check the database connection. 857      * If no connection, ask to retry. 858      * if user doesn't want to retry, 859      * then disable the query editor 860      */ 861     public boolean checkDatabaseAndDisable(String query) { 862         if ( query == null ) query = _queryBuilderPane.getQueryBuilderSqlTextArea().getText() ; 863         if ( checkDatabaseConnection() == false ) { 864             Log.log("checkDatabaseConnection returns false ... \n " ); // NOI18N 865 // If we don't have a valid connection, disable all visual editing. 866 disableVisualEditing(query); 867             return false; 868         } 869         return true ; 870     } 871     /** 872      * Parse the query and regenerate all the panes 873      * If parsing fails, raise an notification and do nothing else 874      * If parsing succeeds, return true, false otherwise. 875      * @param text query to parse 876      */ 877     boolean populate(String query) { 878         return populate( query, false) ; 879     } 880 881     boolean populate(String query, boolean forceParse ) { 882 883         Log.log("Entering populate, forceParse: " + forceParse); // NOI18N 884 885         if ( ! forceParse ) { 886             if ( query.trim().equals( _queryBuilderPane.getQueryBuilderSqlTextArea().getText().trim())) { 887                 // no change, just return. 888 Log.log(" skipping populate(), no change") ; //NOI18N 889 return true ; 890             } 891         } 892 893         // Fix CR 6275870 Error when parsing invalid SQL 894 if ( query.trim().equals( lastQuery ) ) { 895             // no change, just return. 896 Log.log(" skipping populate(), no change") ; //NOI18N 897 return true ; 898         } 899         else { 900             lastQuery = new String (query.trim()); 901         } 902 903         // if ( ! checkDatabaseAndDisable( query )) return false ; 904 905         // First parse the query, and report any exception 906 try { 907             parseQuery(query); 908 909             // if the parsing is successful and if the user modified query 910 // by hand, then check for all the table names and column names. 911 // if there is an error, give a message to the user and return false 912 // else if there is only error in case and/or ommission of 913 // tablename etc fix the query model with the correct values. 914 915             if (!checkQuery()) 916                 return false; 917 918             _queryBuilderPane.getQueryBuilderGraphFrame().setQBGFEnabled( true ) ; 919             _queryBuilderPane.setQueryBuilderInputTableEnabled( true ) ; 920             _queryBuilderPane.getQueryBuilderGraphFrame().setTableColumnValidity(false) ; 921             _queryBuilderPane.getQueryBuilderGraphFrame().setGroupBy(_queryModel.hasGroupBy() ); 922             _graphicsEnabled=true; 923             _queryBuilderPane.getQueryBuilderSqlTextArea().setQueryText(query); 924         } catch (ParseException pe) { 925 926             Log.err.log(ErrorManager.ERROR, "Parse error: " + pe.getLocalizedMessage()); // NOI18N 927 promptForContinuation(pe.getMessage(), query); 928             return false; 929         } catch (TokenMgrError tme) { 930 931             Log.err.log(ErrorManager.ERROR, "Parse error: " + tme.getLocalizedMessage()); // NOI18N 932 promptForContinuation(tme.getMessage(), query); 933             return false; 934 935         } catch (SQLException sqe) { 936             lastException = sqe ; 937             Log.err.log(ErrorManager.ERROR, "Parse error: " + sqe.getLocalizedMessage()); // NOI18N 938 promptForContinuation(sqe.getMessage(), query); 939             return false; 940         } 941 942         _parseErrorMessage = null ; 943         // If parsing was successful... 944 945         // ...generate the editor panes 946 this.generate(); 947 948         // ...save the sql command. 949 saveSqlCommand(query); 950 951         _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocus(); 952 953         return true; 954     } 955 956 957     // Ask the user whether to Retry&Continue or Cancel&Continue 958 959     private boolean promptForContinuation(String msg, String query) { 960 961         // There could be an error or the typed SQL may not be SQL-92 962 // compliant. Give the user an option to keep the query and test by 963 // running it. If the user is satisfied then this could reflect in the 964 // backing file. In that case the tables may not be displayed in the 965 // graph properly. If the user thinks that there is a genuine error, 966 // then the previous good query will be restored. 967 968         Object [] options = { 969             NbBundle.getMessage(QueryBuilder.class, "CONTINUE"), // "Continue" 970 NbBundle.getMessage(QueryBuilder.class, "CANCEL") // "Cancel" 971 }; 972         if ( _queryBuilderPane.getQueryBuilderSqlTextArea().queryChanged() ) { 973             int val = JOptionPane.showOptionDialog( 974                     this, 975                     (msg + "\n\n" + 976                     NbBundle.getMessage(QueryBuilder.class, "PARSE_ERROR_MESSAGE") 977                     + 978                     NbBundle.getMessage(QueryBuilder.class, "PARSE_ERROR_MESSAGE_PROMPT")), 979                     NbBundle.getMessage(QueryBuilder.class, "PARSE_ERROR"), 980                     JOptionPane.YES_NO_OPTION, 981                     JOptionPane.QUESTION_MESSAGE, 982                     null, 983                     options, 984                     options[0]); 985             if (val==JOptionPane.NO_OPTION) { // Cancel - Revert to previous 986 Log.err.log(ErrorManager.WARNING, "Query execution canceled"); // NOI18N 987 _queryBuilderPane.getQueryBuilderSqlTextArea().restoreLastGoodQuery(); 988                 _queryBuilderPane.getQueryBuilderGraphFrame().setQBGFEnabled( true ) ; 989                 _queryBuilderPane.setQueryBuilderInputTableEnabled( true ) ; 990                 _graphicsEnabled=true; 991                 _parseErrorMessage = null ; 992             } else { // Continue - Disable visual editing 993 _parseErrorMessage = NbBundle.getMessage(QueryBuilder.class, "PARSE_ERROR_MESSAGE"); // NOI18N 994 disableVisualEditing(query); 995             } 996         } else { 997             // display the message in the graph area 998 _parseErrorMessage = NbBundle.getMessage(QueryBuilder.class, "PARSE_ERROR_MESSAGE"); // NOI18N 999 disableVisualEditing(query); 1000        } 1001        return false; 1002    } 1003 1004 1005    // Disable the graph and grid panes, leaving only the text pane. 1006 // Used when either a parse fails, or the database is down 1007 void disableVisualEditing(String query) { 1008        _graphicsEnabled=false; 1009        _queryBuilderPane.clear(); 1010        _queryBuilderPane.getQueryBuilderGraphFrame().setQBGFEnabled( false ) ; 1011        _queryBuilderPane.setQueryBuilderInputTableEnabled( false ) ; 1012 1013        String command = sqlStatement.getCommand(); 1014        if ( query != null && query.trim().length() != 0) { 1015            _queryBuilderPane.getQueryBuilderSqlTextArea().setQueryText(query); 1016            setSqlCommand(query) ; 1017        } 1018        else { 1019            _queryBuilderPane.getQueryBuilderSqlTextArea().setQueryText(command); 1020        } 1021    } 1022 1023    void showTableColumnNameError( String error ) { 1024        String msg = NbBundle.getMessage(QueryBuilder.class, "TABLE_COLUMN_NAME_ERROR"); 1025        NotifyDescriptor d = 1026            new NotifyDescriptor.Message( error + " : " + msg + "\n\n", NotifyDescriptor.ERROR_MESSAGE); 1027        DialogDisplayer.getDefault().notify(d); 1028        _parseErrorMessage = error + " : " + msg + "\n\n" ; 1029        String query = getUnformattedSqlString(); 1030        disableVisualEditing(query); 1031    } 1032 1033 1034    boolean checkQuery() throws SQLException { 1035 1036        if ( ( getUnformattedSqlString() != null ) || 1037             ( _queryBuilderPane.getQueryBuilderGraphFrame().checkTableColumnValidity() ) ) { 1038 1039            // from 1040 if ( ! checkFrom() ) return false; 1041 1042            // We were calling a function to replace the "*" with the 1043 // column names of the tables immediately after parsing the 1044 // query. With the introduction of routines to check the 1045 // table and column names and to resolve them properly, 1046 // if the from clause contains the table names not as they 1047 // appear in database, we change them to match with those 1048 // in the database. e.g. trip becomes "TRAVEL.TRIP". This 1049 // is done in function checkFrom which gets called after 1050 // parsing is done. In the case of replaceStar being called 1051 // immediately after parsing, the column names and table 1052 // names were resolved without checking for their validity 1053 // in the database. I have changed this sequence as follows: 1054 // 1. First parse the query. 1055 // 2. Check for table names in the from clause. If the names 1056 // only differ in case or if they are missing schema name 1057 // then they are corrected in the datamodel. 1058 // 3. These corrected table names are used to resolve the 1059 // column names in SQLs like "select * from trip" when we 1060 // call replaceStar() function. This should fix the problem 1061 // where "select * from TRIP" was parsed properly but 1062 // "select * from trip" used to give errors. 1063 // Bug Id : 4962093 1064 1065 1066            // we need to replace star after we validate all the table names 1067 // in the from list. 1068 _queryModel.replaceStar(this); 1069 1070            // select 1071 if ( ! checkSelect() ) return false; 1072 1073            // where 1074 if ( ! checkWhere() ) return false; 1075             1076            // groupby 1077 if ( ! checkGroupBy() ) return false; 1078 1079            // having 1080 if ( ! checkHaving() ) return false; 1081 1082            // orderby 1083 if ( ! checkOrderBy() ) return false; 1084 1085        } else { 1086            // we are not validating the fromList as the query has not been 1087 // changed by the user. 1088 _queryModel.replaceStar(this); 1089        } 1090        return true; 1091    } 1092 1093 1094    // Check the tables specified in the FROM clause (plus any columns 1095 // specified in join conditions), against the DB Schema 1096 1097    boolean checkFrom() throws SQLException { 1098 1099        if (DEBUG) 1100            System.out.println("checkFrom called... \n " ); // NOI18N 1101 1102        // we could reuse this to find the tablename if the user 1103 // only specifies "select 'column_name' from 'table_name'" 1104 List fromTables; 1105        // from 1106 if ( _queryModel.getFrom() != null ) { 1107 1108            fromTables = _queryModel.getFrom().getTableList(); 1109            for ( int i = 0; i < fromTables.size(); i++ ) { 1110 1111                String fromTableName = (String ) 1112                ( (JoinTable) fromTables.get(i) ).getFullTableName(); 1113                String fromTableSpec = (String ) 1114                ( (JoinTable) fromTables.get(i) ).getTableSpec(); 1115                String checkedFullTableName = checkFullTableName( fromTableName ) ; 1116 1117                if (DEBUG) 1118                    System.out.println("checkFullTableName called fromTableName = " + fromTableName + " returns " + 1119                                       checkedFullTableName + " \n " ); // NOI18N 1120 if ( checkedFullTableName == null ) { 1121                    // table not found, give an error 1122 showTableColumnNameError( fromTableName ) ; 1123                    return false; 1124                } else if ( ! checkedFullTableName.equals( fromTableName ) ) { 1125                    // table found but maybe in a wrong case, replace 1126 // it in the querymodel's from 1127 if (DEBUG) 1128                        System.out.println( 1129                                " fromTableName = " + fromTableName + // NOI18N 1130 " fromTableSpec = " + fromTableSpec + // NOI18N 1131 " \n" ); // NOI18N 1132 _queryModel.getFrom().setTableSpec( 1133                            fromTableSpec, checkedFullTableName ); 1134                } 1135 1136                // now check the columns in the condition if any. 1137 List fromColumns = new ArrayList (); 1138                ( (JoinTable) fromTables.get(i) ).getReferencedColumns(fromColumns); 1139                for ( int j = 0; j < fromColumns.size(); j++ ) { 1140                    Column fromColumn = (Column) fromColumns.get(j); 1141                    if (! checkTableColumnName( fromColumn)) { 1142                        showTableColumnNameError( fromColumn.getColumnName() ); 1143                        return false; 1144                    } 1145                } 1146            } 1147        } 1148        return true; 1149    } 1150 1151    // Check the tables specified in the SELECT clause, against the DB Schema 1152 1153    boolean checkSelect() throws SQLException { 1154        if (DEBUG) 1155            System.out.println("checkSelect called. _queryModel.getSelect() = " + _queryModel.getSelect() + "\n " ); // NOI18N 1156 if ( _queryModel.getSelect() != null ) { 1157            ArrayList selectColumns = new ArrayList (); 1158            _queryModel.getSelect().getReferencedColumns(selectColumns); 1159            if ( ! checkColumns( selectColumns ) ) 1160                return false; 1161        } 1162        return true; 1163    } 1164 1165    boolean checkWhere() throws SQLException { 1166        if (DEBUG) 1167            System.out.println("checkWhere called... \n " ); // NOI18N 1168 if ( _queryModel.getWhere() != null ) { 1169            ArrayList whereColumns = new ArrayList (); 1170            _queryModel.getWhere().getReferencedColumns(whereColumns); 1171            if ( ! checkColumns( whereColumns ) ) return false; 1172        } 1173        return true; 1174    } 1175 1176    boolean checkGroupBy() throws SQLException { 1177        if (DEBUG) 1178            System.out.println("checkGroupBy called... \n " ); // NOI18N 1179 if ( _queryModel.getGroupBy() != null ) { 1180            ArrayList groupByColumns = new ArrayList (); 1181            _queryModel.getGroupBy().getReferencedColumns(groupByColumns); 1182            if ( ! checkColumns( groupByColumns ) ) return false; 1183        } 1184        return true; 1185    } 1186 1187 1188    boolean checkHaving() throws SQLException { 1189        if (DEBUG) 1190            System.out.println("checkHaving called... \n " ); // NOI18N 1191 if ( _queryModel.getHaving() != null ) { 1192            ArrayList havingColumns = new ArrayList (); 1193            _queryModel.getHaving().getReferencedColumns(havingColumns); 1194            if ( ! checkColumns( havingColumns ) ) return false; 1195        } 1196        return true; 1197    } 1198 1199 1200    boolean checkOrderBy() throws SQLException { 1201        if (DEBUG) 1202            System.out.println("checkOrderBy called... \n " ); // NOI18N 1203 OrderBy orderBy = _queryModel.getOrderBy(); 1204        if ( orderBy != null ) { 1205            ArrayList orderByColumns = new ArrayList (); 1206            for ( int i = 0; i < orderBy.getSortSpecificationCount(); i++ ) { 1207                Column sortColumn = orderBy.getSortSpecification(i).getColumn(); 1208                orderByColumns.add( sortColumn); 1209            } 1210            if ( ! checkColumns( orderByColumns ) ) return false; 1211        } 1212        return true; 1213    } 1214 1215    // Check and correct any columns that may have wrong or missing table 1216 // specifications. If the column is not found in the database it displays 1217 // an error message and returns false. If there is just case mismatch 1218 // this function corrects the column name. 1219 // The column name could be : 1220 // case 1 : <schema_name>.<table_name>.<column_name> 1221 // case 2 : <table_name>.<column_name> 1222 // case 3 : <alias_table_name>.<column_name> 1223 // case 4 : <column_name> 1224 // 1225 1226    boolean checkColumns( ArrayList columns ) throws SQLException { 1227        Log.log ( " checkColumns called. columns.size = " + columns.size() ); // NOI18N 1228 for ( int i = 0; i < columns.size(); i++ ) { 1229            Column column = (Column) columns.get(i); 1230            String columnTableSpec = column.getTableSpec(); 1231            String columnFullTableName = column.getFullTableName(); 1232 1233            // If the user has specified a column without tablename resolve it 1234 // from the from_table_list. 1235 // Reversed the first test, switched || to &&. 1236 if ( ( columnFullTableName == null ) && 1237                 ( _queryModel.getFrom() != null ) ) 1238            { 1239                // Check every table in the From list, to see if any have 1240 // this column 1241 List fromTables = _queryModel.getFrom().getTableList(); 1242                boolean found=false; 1243                for ( int j = 0; j < fromTables.size(); j++ ) { 1244                    String fromTableName = (String ) ( (JoinTable) fromTables.get(j) ).getFullTableName(); 1245                    // this could be an alias 1246 String fromTableSpec = (String ) 1247                    ( (JoinTable) fromTables.get(j) ).getTableSpec(); 1248 1249                    if (DEBUG) 1250                        System.out.println( 1251                                " checkColumns called " + // NOI18N 1252 " fromTableName = " + fromTableName + // NOI18N 1253 " fromTableSpec = " + fromTableSpec + // NOI18N 1254 " \n" ); // NOI18N 1255 // use the following function to check if fromTableSpec 1256 // is in the database. If it is found update the column. 1257 if ( checkColumnNameForTable( column, fromTableSpec )) { 1258                        found=true; 1259                        break; 1260                    } 1261                } 1262                // Give an error only if all the columns have been checked in all tables 1263 if (!found) { 1264                    // table not found, give an error 1265 showTableColumnNameError( column.getColumnName() ) ; 1266                    return false; 1267                } 1268            } 1269 1270            if (! checkTableColumnName(column) 1271                // Not clear what this test was for; it meant that we only reported an error if 1272 // the column that failed was the last one 1273 // && ( i == ( columns.size() - 1 ) ) 1274 ) 1275            { 1276                showTableColumnNameError( column.getColumnName() ) ; 1277                return false; 1278            } 1279            // the table has an alias, do not check the table name, just 1280 // check column name 1281 } 1282        return true; 1283    } 1284 1285    /** 1286     * Parse the current query (obtained from the RowSet). 1287     * @param the current query 1288     */ 1289    void parseQuery(String query) throws ParseException { 1290 1291        Log.err.log(ErrorManager.INFORMATIONAL, "Entering parseQuery, query: " + query); // NOI18N 1292 1293        // Initialize the QueryModel object if necessary 1294 if (_queryModel==null) 1295            _queryModel = new QueryModel(); 1296 1297        _queryModel.parse(query); 1298    } 1299 1300 1301    // Update the command property of the calling thingy. 1302 1303    public void saveSqlCommand(String query) { 1304 1305        _queryBuilderPane.getQueryBuilderSqlTextArea().saveLastGoodQuery (_queryBuilderPane.getSqlTextAreaText()); 1306 1307        // Comapare the new query to the existing property value; don't do the 1308 // setValue operation if there has been no change 1309 1310        String newQuery = getUnformattedSqlString(); 1311        if (!newQuery.equals( sqlStatement.getCommand())) { 1312            Log.log("QB: setting sql command to: " + newQuery) ; //NOI18N 1313 setSqlCommand( newQuery ) ; 1314        } 1315    } 1316 1317    void setSqlCommand(String query) { 1318        sqlStatement.setCommand(query) ; 1319    } 1320/*** 1321    private void refreshDataBaseMetaData() { 1322        if (DEBUG) { 1323            System.out.println(" refreshDataBaseMetaData() called " + "\n" ); // NOI18N 1324        } 1325        try { 1326            _databaseMetaData = null; 1327            checkMetaData(); 1328        } catch (SQLException e) { 1329            reportDatabaseError("DATABASE_ERROR", e); // NOI18N 1330        } 1331    } 1332***/ 1333    /** 1334     * Executes the specified query against the database 1335     * 1336     */ 1337 1338    public void executeQuery(String query) { 1339 1340        Log.err.log(ErrorManager.INFORMATIONAL, 1341                    "Entering QueryBuilder.executeQuery, query:\n" + query); // NOI18N 1342 1343        String sqlCommand = _queryBuilderPane.getSqlTextAreaText() ; // why not "query"? 1344 ResultSet result = null ; // value to be returned. 1345 1346        Connection connection = null ; 1347        PreparedStatement myStatement = null ; 1348         1349        showBusyCursor ( true ); 1350         1351        boolean canExecute = true ; 1352 1353        ParameterMetaData pmd = null; 1354        int paramCount = 0; 1355 1356        try { 1357            connection = sqlStatement.getConnection() ; 1358            myStatement = connection.prepareStatement(sqlCommand) ; 1359            pmd = myStatement.getParameterMetaData(); 1360            paramCount = pmd.getParameterCount(); 1361            if (DEBUG) { 1362                System.out.println(" Parameter Count = " + paramCount + "\n"); 1363                for (int i = 1; i <= paramCount; i++) { 1364                    System.out.println(" Parameter Type = " + pmd.getParameterType(i) + "\n"); 1365                    System.out.println(" Parameter Type Name = " + pmd.getParameterTypeName(i) + "\n"); 1366                } 1367            } 1368        } catch ( SQLException e) { 1369            reportDatabaseError(e); // NOI18N 1370 canExecute = false ; 1371        } catch ( AbstractMethodError e) { 1372            // Certain drivers (e.g., Sybase 5.5) can throw Errors because of incompatibility. Catch and report. 1373 Log.log(ErrorManager.ERROR, 1374                    "Error occurred when trying to retrieve table information: " + e); // NOI18N 1375 String title = NbBundle.getMessage(QueryBuilder.class, "PROCESSING_ERROR"); 1376            JOptionPane.showMessageDialog( this, e.toString() + "\n\n", title, JOptionPane.ERROR_MESSAGE ); 1377            canExecute = false ; 1378        } 1379 1380        // Deal with any query parameters if we know about them. 1381 if ( canExecute && ( _queryModel != null ) ) 1382        { 1383            if ( getParseErrorMessage() == null && _queryModel.isParameterized()) { 1384 1385                ArrayList list = new ArrayList (); 1386                _queryModel.getParameterizedPredicates(list); 1387                String [] parameters = new String [list.size()]; 1388                String [] values = new String [list.size()]; 1389 1390                for (int i = 0; i < parameters.length; i++) { 1391                    parameters[i] = new String ((String ) list.get(i)); 1392                } 1393                ParameterizedQueryDialog pqDlg = new ParameterizedQueryDialog( 1394                    parameters, true); 1395                // System.out.println(pqDlg.getReturnStatus()); 1396 if (pqDlg.getReturnStatus() == ParameterizedQueryDialog.RETURNED_OK) { 1397                    values = pqDlg.getParameterValues(); 1398     1399                    try { 1400                        for (int i = 0; i < values.length; i++) { 1401                            if (DEBUG) { 1402                                System.out.println(" command = " + sqlCommand + "\n"); 1403                                System.out.println("PreparedStatement i = " + i + " values = " + values[i] + "\n"); 1404                            } 1405                            myStatement.setObject(i+1, ((String )values[i]), 1406                                                  pmd.getParameterType(i+1) ); 1407                        } 1408     1409                    } catch ( SQLException e) { 1410                        reportDatabaseError(e); // NOI18N 1411 canExecute = false ; 1412 1413                    } 1414                } else { 1415                    // cancelled the dialog. 1416 canExecute = false ; 1417                } 1418            } 1419            else if (paramCount != 0) { 1420                // we have a query which can not be parsed. 1421 ArrayList list = new ArrayList (paramCount); 1422                if (DEBUG) { 1423                    System.out.println(" param count = " + paramCount + "\n"); 1424                    System.out.println(" list size = " + list.size()+ "\n"); 1425                } 1426                String [] parameters = new String [paramCount]; 1427                String [] values = new String [paramCount]; 1428                if (DEBUG) { 1429                    System.out.println(" parameters size = " + parameters.length+ "\n"); 1430                    System.out.println(" values size = " + values.length+ "\n"); 1431                } 1432     1433                for (int i = 0; i < paramCount; i++) { 1434                    parameters[i] = new String ( "Parameter " + new Integer (i).toString()); 1435                } 1436                if (DEBUG) { 1437                    for (int i = 0; i < parameters.length; i++) { 1438                        System.out.println(" parameter = " + parameters[i] + "\n"); 1439                    } 1440                } 1441                ParameterizedQueryDialog pqDlg = new ParameterizedQueryDialog( 1442                    parameters, true); 1443                // System.out.println(pqDlg.getReturnStatus()); 1444 if (pqDlg.getReturnStatus() == ParameterizedQueryDialog.RETURNED_OK) { 1445                    values = pqDlg.getParameterValues(); 1446     1447                    try { 1448                        for (int i = 0; i < values.length; i++) { 1449                            if (DEBUG) { 1450                                System.out.println(" command = " + sqlCommand + "\n"); 1451                                System.out.println("PreparedStatement i = " + i + " values = " + values[i] + "\n"); 1452                            } 1453                            myStatement.setObject(i+1, ((String )values[i]), 1454                                                  pmd.getParameterType(i+1) ); 1455                        } 1456     1457                    } catch ( SQLException e) { 1458                        reportDatabaseError(e); // NOI18N 1459 canExecute = false ; 1460                    } 1461                } else { 1462                    // cancelled the dialog. 1463 canExecute = false ; 1464                } 1465            } 1466        } 1467 1468        // Now execute the query 1469 if ( canExecute ) { 1470 1471            try { 1472                boolean hasResults = myStatement.execute() ; 1473                if ( hasResults ) { 1474                    result = myStatement.getResultSet() ; 1475                    _queryBuilderPane.getQueryBuilderResultTable().displayResultSet(result); 1476                    result.close() ; 1477                } 1478                 1479            } catch (SQLException e) { 1480                reportDatabaseError(e); // NOI18N 1481 } 1482        } 1483         1484        // clean up stuff. 1485 try { 1486            if ( myStatement != null ) { 1487                myStatement.close() ; 1488            } 1489        } catch( SQLException se) { 1490            Log.err.log(ErrorManager.INFORMATIONAL, "Error Closing statement: " + se.getLocalizedMessage()); // NOI18N 1491 } 1492 1493        try { 1494            if ( connection != null ) { 1495                connection.close() ; 1496            } 1497        } catch( SQLException se) { 1498            Log.err.log(ErrorManager.INFORMATIONAL, "Error Closing connection: " + se.getLocalizedMessage()); // NOI18N 1499 } 1500         1501        showBusyCursor ( false ); 1502        Log.err.log(ErrorManager.INFORMATIONAL, "Returning from QueryBuilder.executeQuery"); // NOI18N 1503 1504    } 1505 1506 1507    /** 1508     * Initialize all the panes based on a new query passed in from the RowSet 1509     */ 1510    void generate() { 1511 1512        Log.err.log(ErrorManager.INFORMATIONAL, "Entering QueryBuilder.generate"); // NOI18N 1513 1514        // Suppress updating of the text pane until we're ready 1515 _updateText = false; 1516        try { 1517 1518            // Clear the current state. It might be easier to recreate each of 1519 // the panes, as we did at startup. 1520 _queryBuilderPane.clear(); 1521 1522            // Generate the Diagram Pane and Grid Pane 1523 this.generateGraph(); 1524 1525        } finally { 1526            _updateText = true; 1527        } 1528 1529        // Generate the Text Pane 1530 // It's not clear whether we should use the passed-in command, 1531 // or generate it from the model 1532 // _queryBuilderSqlTextArea.setText(command); 1533 this.generateText(); 1534    } 1535 1536 1537    /** 1538     * Generate the query graph and tables that correspond to the current query model 1539     */ 1540    void generateGraph() { 1541        // If this is false, graphics and model are disabled. Don't build graph/table. 1542 if (_graphicsEnabled) 1543            _queryBuilderPane.getQueryBuilderGraphFrame().generateGraph(_queryModel); 1544    } 1545 1546    /** 1547     * Build the SQL query from the current model, and update the text pane 1548     */ 1549    void generateText() { 1550        // If this is false, graphics and model are disabled. Don't generate text. 1551 if (_graphicsEnabled) { 1552            _queryBuilderPane.getQueryBuilderSqlTextArea().setQueryText(_queryModel.genText()); 1553            if ( ! firstTimeGenerateText ) { 1554                saveSqlCommand(_queryBuilderPane.getSqlTextAreaText()); 1555            } else { 1556                firstTimeGenerateText = false; 1557            } 1558        } 1559    } 1560 1561 1562    void displayResultSet(ResultSet rs) { 1563        _queryBuilderPane.getQueryBuilderResultTable().displayResultSet(rs); 1564    } 1565 1566 1567// /** 1568// * Disable everything except the SQL Text Pane 1569// */ 1570// void disable() { 1571// } 1572 1573// /** 1574// * Re-enable the panes that were disabled 1575// */ 1576// void enable() { 1577// } 1578 1579 1580    // Accessors/Mutators 1581 1582    /** 1583     * Return the current query model 1584     */ 1585    QueryModel getQueryModel() { 1586        return _queryModel; 1587    } 1588 1589    /** 1590     * Restore the last good query. 1591     */ 1592 1593    QueryBuilderPane getQueryBuilderPane () 1594    { 1595        return ( _queryBuilderPane ); 1596    } 1597 1598    /** 1599     * Returns true if the current query is parameterized 1600     */ 1601    boolean isParameterized() { 1602        return _queryModel.isParameterized(); 1603    } 1604 1605 1606    /** 1607     * Returns the list of tables and views 1608     */ 1609    List getAllTables() throws SQLException { 1610        /* 1611        List tables = getTablesInternal("TABLE"); 1612        tables.addAll(getTablesInternal("VIEW")); 1613        return tables; 1614        */ 1615        return metaDataCache.getTables() ; 1616    } 1617 1618    /* ===== JFB 1619    private List getTablesInternal(String type) { 1620        List tableNames = new ArrayList(); 1621        if ( checkDatabaseConnection() == false ) { 1622            return tableNames; 1623        } 1624        boolean firstTime = true; 1625        while ( true ) { 1626            try { 1627                checkMetaData(); 1628 1629                TableMetaData[] tmd; 1630                if ( Log.isLoggable()) Log.log("start get"+type+"MetaData") ; 1631                tmd = (type.equals("TABLE")) ? _dbmdh.getTableMetaData() : _dbmdh.getViewMetaData(); 1632                if ( Log.isLoggable()) Log.log("end get"+type+"MetaData") ; 1633                for (int i=0; i<tmd.length; i++) 1634                    tableNames.add(getFullTableName(tmd[i])); 1635                break; 1636            } catch (SQLException sqle) { 1637                if ( firstTime ) { 1638                    refreshDataBaseMetaData(); 1639                    firstTime = false; 1640                } else { 1641                    reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 1642                    break; 1643                } 1644            } 1645        } 1646        return tableNames; 1647    } 1648     *****/ 1649 1650 1651// private String getFullTableName(TableMetaData tmd) throws SQLException { 1652// if (DEBUG) { 1653// System.out.println(" getFullTableName() called " + "\n" ); // NOI18N 1654// } 1655// String schema = tmd.getMetaInfo(TableMetaData.TABLE_SCHEM); 1656// /* 1657// * !JK always show schema 1658// * if (schema == null || schema.trim().equals("") || isSchemaInPath(schema)) { 1659// } 1660// */ 1661// if (schema == null || schema.trim().equals("")) { 1662// schema = ""; 1663// } else { 1664// schema += "."; 1665// } 1666// String tableName = tmd.getMetaInfo(TableMetaData.TABLE_NAME); 1667// 1668// // if table name does not contain spaces 1669// if (tableName.indexOf(' ') == -1 ) { 1670// return schema + tableName; 1671// } else { 1672// return schema + "\"" + tableName + "\""; 1673// } 1674// } 1675 1676    /** 1677     * Returns the list of tables and views in all schemas that are accessible 1678     * through the DataSource associated with this QE 1679     */ 1680    List getAllTablesInDataSource() throws SQLException { 1681 1682        // Log.log(" getAllTablesInDataSource() called " + "\n" ); // NOI18N 1683 1684        return metaDataCache.getTables() ; 1685         1686        /* 1687        try { 1688            checkMetaData(); 1689        } catch (SQLException sqle) { 1690            reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 1691        } 1692 1693        // Get list of schemas in the datasource 1694        String[] schemaNames = sqlStatement.getSchemas(); 1695        if (schemaNames == null || schemaNames.length == 0) 1696            return getAllTables(); 1697        else { 1698            _schemaNames = new ArrayList(); 1699            List tables = new ArrayList(); 1700            for (int i=0; i<schemaNames.length; i++) { 1701                tables.addAll(getTablesInternal("TABLE", schemaNames[i])); 1702                tables.addAll(getTablesInternal("VIEW", schemaNames[i])); 1703                _schemaNames.add(schemaNames[i]); 1704            } 1705            return tables; 1706        } 1707        */ 1708    } 1709 1710 1711    /** 1712     * Returns the list of table names in the specified schema 1713     */ 1714    /**** JFB private List getTablesInternal(String type, String schemaName) { 1715        if (Log.isLoggable() ) Log.log("enter tablesInternal "+type+","+schemaName) ; 1716        List tableNames = new ArrayList(); 1717        if ( checkDatabaseConnection() == false ) 1718            return tableNames; 1719        boolean firstTime = true; 1720        while ( true ) { 1721            try { 1722                checkMetaData(); 1723                String[] tables = 1724                        (type.equals("TABLE")) ?_dbmdh.getTables(schemaName) : _dbmdh.getViews(schemaName); // NOI18N 1725 1726                // Convert to ArrayList, because caller expects it 1727                for (int i=0; i<tables.length; i++) { 1728                    tableNames.add(tables[i]); 1729                    if (DEBUG) 1730                        System.out.println(" getAllTablesInternal() tables [ " + i + " ] = " + tables[i] + "\n" ); // NOI18N 1731                } 1732                break; 1733            } catch (SQLException sqle) { 1734                if ( firstTime ) { 1735                    refreshDataBaseMetaData(); 1736                    firstTime = false; 1737                } else { 1738                    reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 1739                    break; 1740                } 1741            } 1742        } 1743 1744        if (Log.isLoggable() ) Log.log("exit tablesInternal, cnt= " + tableNames.size() ) ; 1745        return tableNames; 1746    } 1747    ***/ 1748 1749    /** 1750     * Returns the set of columns in the specified table. 1751     * This is obtained from the DbMetaData. 1752     */ 1753    // SCH: Modified to use schema if available 1754 /* JFB 1755    public List getColumnNames(String tableName) throws SQLException { 1756 1757        Log.err.log(ErrorManager.INFORMATIONAL, 1758                "Entering QueryBuilder.getColumnNames, tableName: " + tableName); // NOI18N 1759 1760        return metaDataCache.getColumnNames(tableName)) ; 1761       1762        boolean firstTime = true; 1763        while ( true ) { 1764            try { 1765                checkMetaData(); 1766 1767                ResultSet rs = _dbmdh.getMetaData().getColumns(null, null, tableName, "%"); // NOI18N 1768                if (rs != null) { 1769                    while (rs.next()) { 1770                        columnNames.add(rs.getString("COLUMN_NAME")); // NOI18N 1771                    } 1772                    rs.close(); 1773                } 1774                break; 1775            } catch (SQLException sqle) { 1776                if ( firstTime ) { 1777                    refreshDataBaseMetaData(); 1778                    firstTime = false; 1779                } else { 1780                    reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 1781                    break; 1782                } 1783            } 1784        } 1785        if (DEBUG) 1786            for (int j=0; j<columnNames.size(); j++) 1787                System.out.println("Column ["+j+"] : " + (String) columnNames.get(j) + "\n" ); // NOI18N 1788         1789    } 1790    */ 1791    // Get the list of column names associated with the specified table name 1792 1793    public void getColumnNamesFull(String fullTableName, List columnNames) { 1794        try { 1795            columnNames.addAll( getColumnNames(fullTableName)) ; 1796        } catch(SQLException sqle) { 1797            // can't do anything. 1798 } 1799    } 1800    public List getColumnNames(String fullTableName) throws SQLException { 1801 1802        Log.err.log(ErrorManager.INFORMATIONAL, 1803                "Entering QueryBuilder.getColumnNames, fullTableName: " + fullTableName); // NOI18N 1804 1805        return metaDataCache.getColumnNames( fullTableName ) ; 1806        /* 1807        String[] table = fullTableName.split("\\."); 1808        if (table.length==1) // no schema -- use the old method 1809            getColumnNames(fullTableName, columnNames); 1810        else { 1811            String[] colNames=null; 1812            boolean firstTime = true; 1813            try { 1814                checkMetaData(); 1815                // hack, getColumns throws an exception if table name has 1816                // spaces. 1817                colNames = _dbmdh.getColumns(fullTableName.replaceAll("\"", "") ); 1818            } catch (SQLException sqle) { 1819                // First time we catch an error, try resetting the RowSet 1820                refreshDataBaseMetaData(); 1821                try { 1822                    checkMetaData(); 1823                    colNames = _dbmdh.getColumns(fullTableName); 1824                } catch (SQLException sqle2) { 1825                    // We must have a real error. Report it. 1826                    reportDatabaseError("DATABASE_ERROR", sqle2); // NOI18N 1827                } 1828            } 1829 1830            // Convert to ArrayList because caller expects it 1831            if (colNames!=null) 1832                for (int i=0; i<colNames.length; i++) 1833                    columnNames.add(colNames[i]); 1834        } 1835        */ 1836    } 1837 1838 1839    /** 1840     * Returns the primary key columns of the specified table 1841     */ 1842    List getPrimaryKeys(String fullTableName) throws SQLException { 1843 1844        Log.log(ErrorManager.INFORMATIONAL, 1845                "Entering QueryBuilder.getPrimaryKeys, fullTableName: " + fullTableName ); // NOI18N 1846 1847        return metaDataCache.getPrimaryKeys(fullTableName) ; 1848        /* 1849        List primaryKeys = new ArrayList(); 1850 1851        String tableName, schemaName=null; 1852        String[] table = fullTableName.split("\\."); // NOI18N 1853        if (table.length>1) { 1854            schemaName=table[0]; 1855            tableName = table[1]; 1856        } else 1857            tableName=table[0]; 1858 1859        boolean firstTime = true; 1860        while ( true ) { 1861            try { 1862                checkMetaData(); 1863 1864                ResultSet rs = _databaseMetaData.getPrimaryKeys(null, schemaName, tableName); 1865                if (rs != null) { 1866                    String name; 1867                    while (rs.next()) { 1868                        name = rs.getString("COLUMN_NAME"); // NOI18N 1869                        primaryKeys.add(name); 1870                    } 1871                    rs.close(); 1872                } 1873                break; 1874 1875            } catch (SQLException sqle) { 1876                if ( firstTime ) { 1877                    refreshDataBaseMetaData(); 1878                    firstTime = false; 1879                } else { 1880                    reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 1881                    break; 1882                } 1883            } 1884        } 1885 1886        return primaryKeys; 1887         **/ 1888    } 1889 1890 1891    /** 1892     * Returns the Foreign Key Constraints that apply to the specified table 1893     * 1894     * Result is an a-list of <foreignTable, foreignCol, primTable, primCol>. 1895     */ 1896    List getForeignKeys(String fullTableName) throws SQLException { 1897 1898        Log.err.log(ErrorManager.INFORMATIONAL, 1899                "Entering QB.getForeignKeys, fullTableName: " + fullTableName); // NOI18N 1900 1901         1902        // keys.add(new String[] {"travel.trip", "personid", "travel.person", "personid"}); 1903 1904        // We get the exported keys (foreign tables that reference this one), then 1905 // imported keys (foreign tables that this one references). 1906 /* 1907        List keys = getForeignKeys1(fullTableName, true); 1908        keys.addAll(getForeignKeys1(fullTableName, false)); 1909        */ 1910        List keys = metaDataCache.getForeignKeys(fullTableName, true); 1911        keys.addAll( metaDataCache.getForeignKeys(fullTableName, false)); 1912 1913        return keys; 1914         1915    } 1916 1917    /** 1918     * Returns either the exported or imported keys for this table, depending on the flag 1919     */ 1920    /* 1921    List getForeignKeys1(String fullTableName, boolean exported) { 1922 1923        String tableName, schemaName=null; 1924        String[] table = fullTableName.split("\\."); // NOI18N 1925        if (table.length>1) { 1926            schemaName=table[0]; 1927            tableName = table[1]; 1928        } else 1929            tableName=table[0]; 1930 1931        Log.log(" getForeignKeys1 schemaName = " + schemaName + " tableName = " + tableName + "\n" ); // NOI18N 1932        List keys = new ArrayList(); 1933        boolean firstTime = true; 1934        while ( true ) { 1935            try { 1936                checkMetaData(); 1937 1938                ResultSet rs = 1939                        exported ? 1940                            _databaseMetaData.getExportedKeys(null, schemaName, tableName) : 1941                            _databaseMetaData.getImportedKeys(null, schemaName, tableName); 1942                if (rs != null) { 1943                    while (rs.next()) { 1944                        String fschem = rs.getString("FKTABLE_SCHEM"); // NOI18N 1945                        String pschem = rs.getString("PKTABLE_SCHEM"); // NOI18N 1946                        String[] key = new String[] { 1947                            ((fschem!=null) ? fschem+"." : "") + rs.getString("FKTABLE_NAME"), // NOI18N 1948                                    rs.getString("FKCOLUMN_NAME"), // NOI18N 1949                                    ((pschem!=null) ? pschem+"." : "") + rs.getString("PKTABLE_NAME"), // NOI18N 1950                                    rs.getString("PKCOLUMN_NAME") }; // NOI18N 1951                                    keys.add(key); 1952                    } 1953                    rs.close(); 1954                } 1955                break; 1956            } catch (SQLException sqle) { 1957                if ( firstTime ) { 1958                    refreshDataBaseMetaData(); 1959                    firstTime = false; 1960                } else { 1961                    reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 1962                    break; 1963                } 1964            } 1965        } 1966        return keys; 1967    } 1968 1969    */ 1970    /** 1971     * Returns the imported key columns for this table -- i.e., the columns 1972     * whose value is a foreign key for another table. These columns are 1973     * displayed with a special icon in the Query Builder. 1974     */ 1975    List getImportedKeyColumns(String fullTableName) throws SQLException { 1976 1977        return metaDataCache.getImportedKeyColumns(fullTableName) ; 1978        /* 1979        List keys = new ArrayList(); 1980        String tableName, schemaName=null; 1981        String[] table = fullTableName.split("\\."); // NOI18N 1982        if (table.length>1) { 1983            schemaName=table[0]; 1984            tableName = table[1]; 1985        } else 1986            tableName=table[0]; 1987 1988        boolean firstTime = true; 1989        while ( true ) { 1990            try { 1991                checkMetaData(); 1992 1993                ResultSet rs = _databaseMetaData.getImportedKeys(null, schemaName, tableName); 1994                if (rs != null) { 1995                    while (rs.next()) { 1996                        keys.add(rs.getString("FKCOLUMN_NAME")); // NOI18N 1997                    } 1998                    rs.close(); 1999                } 2000                break; 2001            } catch (SQLException sqle) { 2002                if ( firstTime ) { 2003                    refreshDataBaseMetaData(); 2004                    firstTime = false; 2005                } else { 2006                    reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 2007                    break; 2008                } 2009            } 2010        } 2011 2012        Log.err.log(ErrorManager.INFORMATIONAL, "Imported key columns for table " + fullTableName); // NOI18N 2013        if (keys!= null) 2014            for (int i=0; i<keys.size(); i++) 2015                Log.err.log(ErrorManager.INFORMATIONAL, "Keys("+i+"): " + keys.get(i)); // NOI18N 2016 2017        return keys; 2018        */ 2019    } 2020 2021    /** 2022     * Returns a FK between this pair of tables if there is one, else null 2023     * Note that the set of FKs is passed in from the caller, to avoid having to make multiple 2024     * fetches from the dbmetedata when we're adding a new table 2025     */ 2026    String [] findForeignKey(String oldFullTableName, String newFullTableName, List foreignKeys) { 2027 2028        Log.err.log(ErrorManager.INFORMATIONAL, 2029                "Entering QB.findForeignKey, t1: "+oldFullTableName + " t2: "+newFullTableName); // NOI18N 2030 2031        if (foreignKeys!=null) { 2032            for (int i=0; i<foreignKeys.size(); i++) { 2033                String [] key = (String [])foreignKeys.get(i); 2034                if ((key[0].equalsIgnoreCase(newFullTableName)&&key[2].equalsIgnoreCase(oldFullTableName)) || 2035                        (key[0].equalsIgnoreCase(oldFullTableName)&&key[2].equalsIgnoreCase(newFullTableName))) 2036                    return (String []) foreignKeys.get(i); 2037            } 2038        } 2039        Log.err.log(ErrorManager.INFORMATIONAL, "No key found"); // NOI18N 2040 return null; 2041    } 2042 2043 2044    /** 2045     * Returns a FK between this pair of tables and columnsif there is one, else null 2046     */ 2047    String [] findForeignKey(String fullTableName1, String colName1, 2048            String fullTableName2, String colName2) throws SQLException { 2049 2050        Log.err.log(ErrorManager.INFORMATIONAL, 2051                "Entering findForeignKey, t1: " + fullTableName1 + " c1: " + colName1 // NOI18N 2052 + " t2: " + fullTableName2 + " c2: " + colName2); // NOI18N 2053 2054        // Get the complete list of keys for one of the tables; we use table1 2055 List foreignKeys = getForeignKeys(fullTableName1); 2056        if (foreignKeys!=null) { 2057            for (int i=0; i<foreignKeys.size(); i++) { 2058                String [] key = (String [])foreignKeys.get(i); 2059                if ((key[0].equalsIgnoreCase(fullTableName1) && key[1].equalsIgnoreCase(colName1) 2060                && key[2].equalsIgnoreCase(fullTableName2) && key[3].equalsIgnoreCase(colName2)) || 2061                        (key[0].equalsIgnoreCase(fullTableName2) && key[1].equalsIgnoreCase(colName2) 2062                        && key[2].equalsIgnoreCase(fullTableName1) && key[3].equalsIgnoreCase(colName1))) 2063                    return (String []) foreignKeys.get(i); 2064            } 2065        } 2066        Log.err.log(ErrorManager.INFORMATIONAL, "No key found"); // NOI18N 2067 return null; 2068    } 2069 2070    public String getConnectionInfo() { 2071        return sqlStatement.getConnectionInfo() ; 2072    } 2073 2074    // Methods inherited from org.openide.windows.TopComponent 2075 2076    /** 2077     * About to shown. Could have been previously hidden ( e.g., 2078     * when the user tabs back to the QueryEditor) or on first showing. 2079     * 2080     * We need to flush all the current information (model, graph, metadata), 2081     * and read the command, as if we were starting for the first time. 2082     */ 2083    protected void componentShowing() { 2084 2085        Log.err.log(ErrorManager.INFORMATIONAL, 2086                "Entering QueryBuilder.componentShowing()"); // NOI18N 2087 2088        String command = sqlStatement.getCommand(); 2089 2090        if (_queryModel==null) 2091            _queryModel = new QueryModel(); 2092 2093        Log.log(" * command=" + command) ; 2094 2095        // Parse the current query, in case it has changed 2096 2097        // Special case for handling null queries -- prompt for an initial table 2098 // We should probably allow this, since the user can delete the last table in the 2099 // editor anyway, so we need to be able to deal with empty queries as a special case. 2100 if ((command==null) || (command.trim().length()==0)) { 2101            Log.log("QBShowing command is null") ; 2102            setVisible(true); 2103            this.repaint(); 2104            String msg = NbBundle.getMessage(QueryBuilder.class, "EMPTY_QUERY_ADD_TABLE"); 2105            NotifyDescriptor d = 2106                    new NotifyDescriptor.Message(msg + "\n\n", NotifyDescriptor.ERROR_MESSAGE); // NOI18N 2107 DialogDisplayer.getDefault().notify(d); 2108 2109            _queryBuilderPane.getQueryBuilderGraphFrame().addTable() ; 2110 2111        } else { 2112 2113            /* TODO JOEL: why get column names here? why now in the 2114             * else for populate? 2115 2116            if (_tableNames == null ) { 2117                _tableColumns = createTableColumns() ; 2118            } 2119             **/ 2120            String queryText = getUnformattedSqlString(); 2121            // parse and populate only if the query has changed. 2122 if ( queryText == null || (! command.trim().equalsIgnoreCase( queryText.trim() ) ) ) { 2123                this.populate(command); 2124                setVisible(true); 2125                this.repaint(); 2126            } 2127        } 2128        activateActions(); 2129 2130        _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocus(); 2131 2132        if ( DEBUG ) 2133            System.out.println(" _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocus () called. " + "\n" ); // NOI18N 2134 2135    } 2136 2137    /** 2138     * Component is about to be shown. 2139     * Called when the user moves to another tab. 2140     * If we have an associated rowset, update it with current text query. 2141     */ 2142    protected void componentHidden() { 2143        Log.err.log(ErrorManager.INFORMATIONAL, 2144                "QB.componentHidden, updating command to: " + _queryBuilderPane.getSqlTextAreaText()); // NOI18N 2145 String command = sqlStatement.getCommand(); 2146        if ((command!=null) && (command.trim().length()!=0)) { 2147            String queryText = getUnformattedSqlString(); 2148 2149            // parse and populate only if the query has changed. 2150 if ( queryText == null || (! command.trim().equalsIgnoreCase( queryText.trim() ) ) ) { 2151                if ( _graphicsEnabled ) { 2152                    boolean good = this.populate(queryText, true); 2153            if ( !good) { 2154                        setSqlCommand(queryText); //HACK, temporary (jfb) 2155 } 2156                } else { 2157                    setSqlCommand(queryText) ; 2158                } 2159            } 2160        } 2161        deactivateActions(); 2162    } 2163 2164    private String getUnformattedSqlString() { 2165        return _queryBuilderPane.getQueryBuilderSqlTextArea().getText() ; 2166        // return (_queryBuilderPane.getQueryBuilderSqlTextArea().getText().replace('\n', ' ').replaceAll(" "," ") ); // NOI18N 2167 } 2168 2169    /** Opened for the first time */ 2170    protected void componentOpened() { 2171        Log.err.log(ErrorManager.INFORMATIONAL, 2172                "Entering QueryBuilder.componentOpened()"); // NOI18N 2173 2174        activateActions(); 2175        ActionMap map = getActionMap(); 2176        InputMap keys = getInputMap(JComponent.WHEN_ANCESTOR_OF_FOCUSED_COMPONENT); 2177        installActions(map, keys); 2178        sqlStatement.addPropertyChangeListener(sqlStatementListener) ; 2179 2180        // do NOT force a parse here. It's done componentShowing(). 2181 // populate( sqlStatement.getCommand()) ; 2182 2183    } 2184 2185    /* closed - not visible anywhere) 2186     */ 2187    protected void componentClosed() { 2188        Log.err.log(ErrorManager.INFORMATIONAL, 2189                "QueryBuilder.componentClosed()"); // NOI18N 2190 2191        deactivateActions(); 2192 2193        sqlStatement.close() ; 2194        lastQuery = null; 2195    } 2196 2197    /***** 2198     * listener for changes in the sqlStatement - either the 2199     * command changed or the connection changed (e.g., datasource changed). 2200     */ 2201    private PropertyChangeListener sqlStatementListener = new PropertyChangeListener () { 2202        public void propertyChange(PropertyChangeEvent evt) { 2203            // what property? 2204 String propName = evt.getPropertyName() ; 2205            Log.log("QB sqlStatement property change: " + propName ) ; 2206            if ( propName.equals(SqlStatement.COMMAND)) { 2207                Log.err.log(" newValue=" + sqlStatement.getCommand()) ; 2208                populate( sqlStatement.getCommand() ) ; 2209                _queryBuilderPane.getQueryBuilderSqlTextArea().requestFocus(); 2210 2211            } else if ( propName.equals(SqlStatement.CONNECTION_INFO)) { 2212                Log.err.log(" resetting connections") ; 2213                // _dbmdh.refresh() ; 2214 2215            } else if ( propName.equals(SqlStatement.TITLE) ) { 2216                Log.err.log(" title to " + sqlStatement.getTitle()) ; // NOI18N 2217 SwingUtilities.invokeLater(new Runnable () { 2218                    public void run() { 2219                        setDisplayName(sqlStatement.getTitle()) ; 2220                    } 2221                }) ; 2222 2223            } else if ( propName.equals(SqlStatement.CLOSING)) { 2224                Log.err.log(" closing...") ; // NOI18N 2225 SwingUtilities.invokeLater(new Runnable () { 2226                    public void run() { 2227                        close() ; 2228                    } 2229                }) ; 2230            } 2231        } 2232    } ; 2233 2234    // Return true if there is a connection to the database, false otherwise 2235 private SQLException lastException = null ; 2236 2237 2238    // Return true if we have a database connection, false otherwise. 2239 // If connection is down, allow user to Retry (loop) or Cancel&Continue 2240 protected boolean checkDatabaseConnection() { 2241 2242        Log.err.log( "checkDatabaseConnection()" ); // NOI18N 2243 boolean keepChecking = true ; 2244        boolean connected = false ; 2245        while ( keepChecking ) { 2246 2247            try { 2248                metaDataCache.checkDataBaseConnection() ; 2249                connected = true ; 2250            } catch (SQLException sqle ) { 2251                lastException = sqle ; 2252                connected = false ; 2253            } 2254             2255            if ( ! connected ) { 2256                // either the verify failed or the connect failed. 2257 boolean retry = showRetryDialog() ; 2258                if (! retry) { 2259                    keepChecking = false ; 2260                } 2261            } else keepChecking = false ; 2262        } 2263        return connected ; 2264    } 2265     2266    /*** 2267    private final JButton retryButton = new JButton(NbBundle.getMessage(QueryBuilder.class, "RETRY_AND_CONTINUE")) ; 2268    private final JButton cancelButton = new JButton(NbBundle.getMessage(QueryBuilder.class, "CANCEL_AND_CONTINUE")) ; 2269    **/ 2270    /****** 2271     * show a dialog with the a message saying the database connection is hosed. 2272     * It has a Retry and Cancel button. 2273     * returns true if the Retry is the closing action. 2274     */ 2275    int ii = 0 ; 2276    public boolean showRetryDialog() { 2277        Log.log("enter showRetryDialog() " + ii++ ) ; 2278        ConnectionStatusPanel csp = new ConnectionStatusPanel() ; 2279        csp.configureDisplay(sqlStatement.getConnectionInfo(), false, lastException.getLocalizedMessage(), "", 0, false ) ; 2280        csp.setGeneralInfo(NbBundle.getMessage(QueryBuilder.class, "DATABASE_CONNECTION_ERROR") ) ; // NOI18N 2281 csp.setFooterInfo(NbBundle.getMessage(QueryBuilder.class, "NO_DATABASE_CONNECTION") ) ; // NOI18N 2282 2283        final JButton retryButton = new JButton (NbBundle.getMessage(QueryBuilder.class, "RETRY_AND_CONTINUE")) ; 2284        final JButton cancelButton = new JButton (NbBundle.getMessage(QueryBuilder.class, "CANCEL_AND_CONTINUE")) ; 2285 2286        // this listener is for the dialog. 2287 final Object [] retVal = new Object [1] ; 2288        ActionListener listener = new ActionListener () { 2289            public void actionPerformed(java.awt.event.ActionEvent evt) { 2290                Log.log(" retry dialog event: " + evt) ; 2291                retVal[0] = evt.getSource() ; 2292            } 2293 2294        }; 2295 2296        DialogDescriptor dlg = new DialogDescriptor(csp, 2297                NbBundle.getMessage(ConnectionStatusPanel.class, "ConStat_title", sqlStatement.getConnectionInfo()), // NOI18N 2298 true/*modal*/, 2299                new Object [] {retryButton, cancelButton}, cancelButton, 2300                        DialogDescriptor.DEFAULT_ALIGN, null, listener); 2301 2302        dlg.setClosingOptions( null ); 2303 2304        Dialog dialog = (Dialog ) DialogDisplayer.getDefault().createDialog(dlg); 2305        dialog.setResizable(true); 2306        dialog.pack() ; 2307 2308        // present dialog, waits for it to be disposed. 2309 dialog.show(); 2310 2311        boolean val = ( retVal[0] == retryButton ) ; 2312        Log.err.log( " * dlg says: Retry=" + val ) ; 2313        return val ; 2314    } 2315 2316 2317    // Utility methods 2318 private void checkMetaData() throws SQLException { 2319/** 2320        if (_databaseMetaData == null) { 2321            try { 2322                if ( checkDatabaseConnection() ) { 2323                    // _dbmdh = new DatabaseMetaDataHelper(connection); 2324                    // _databaseMetaData = _dbmdh.getMetaData(); 2325                } 2326 2327            } catch (SQLException sqle) { 2328                reportDatabaseError("DATABASE_ERROR", sqle); // NOI18N 2329            } 2330        } 2331 ***/ 2332    } 2333 2334    public void reportDatabaseError(SQLException e) { 2335 2336        Log.log(ErrorManager.ERROR, 2337                "Error occurred when trying to retrieve table information: " + e); // NOI18N 2338 2339        String msg = 2340            (e.getErrorCode() == 17023) ? 2341            // Oracle "Unsupported feature" error 2342 NbBundle.getMessage(QueryBuilder.class, "UNSUPPORTED_FEATURE") : 2343            e.getLocalizedMessage(); 2344 2345        // NbBundle.getMessage(QueryBuilder.class, key); 2346 String title = NbBundle.getMessage(QueryBuilder.class, "PROCESSING_ERROR"); 2347 2348        JOptionPane.showMessageDialog( this, msg + "\n\n", title, JOptionPane.ERROR_MESSAGE ); 2349        /* 2350        ConnectionStatusPanel csp = new ConnectionStatusPanel() ; 2351        csp.configureDisplay(sqlStatement.getConnectionInfo(), false, e.getLocalizedMessage(), "", 0, false ) ; 2352        csp.setGeneralInfo(msg) ; 2353        csp.displayDialog( sqlStatement.getConnectionInfo() ) ; 2354        */ 2355 2356    } 2357 2358    String getParseErrorMessage() { 2359        return _parseErrorMessage; 2360    } 2361    /* ================================================================ */ 2362    /***** 2363     * parse a full table name, e.g. Schema.Table or Table 2364     * and returns an array where 2365     * [0] = schema (or null if none found) 2366     * [1] = table name. 2367     */ 2368    private static String [] parseTableName(String fullTableName) { 2369         2370        String [] retVal = new String [2] ; 2371         2372        String [] table = fullTableName.split("\\."); // NOI18N 2373 if (table.length>1) { 2374            retVal[0] = table[0]; 2375            retVal[1] = table[1]; 2376        } else { 2377            retVal[0] = null ; 2378            retVal[1] = table[0]; 2379        } 2380        return retVal ; 2381    } 2382     2383    /** 2384     * Showing/hiding busy cursor, before this funcionality was in Rave winsys, 2385     * the code is copied from that module. 2386     * It needs to be called from event-dispatching thread to work synch, 2387     * otherwise it is scheduled into that thread. */ 2388    static void showBusyCursor(final boolean busy) { 2389        if(SwingUtilities.isEventDispatchThread()) { 2390            doShowBusyCursor(busy); 2391        } else { 2392            SwingUtilities.invokeLater(new Runnable () { 2393                public void run() { 2394                    doShowBusyCursor(busy); 2395                } 2396            }); 2397        } 2398    } 2399 2400    private static void doShowBusyCursor(boolean busy) { 2401        JFrame mainWindow = (JFrame )WindowManager.getDefault().getMainWindow(); 2402        if(busy){ 2403            RepaintManager.currentManager(mainWindow).paintDirtyRegions(); 2404            mainWindow.getGlassPane().setCursor(Cursor.getPredefinedCursor(Cursor.WAIT_CURSOR)); 2405            mainWindow.getGlassPane().setVisible(true); 2406            mainWindow.repaint(); 2407        } else { 2408            mainWindow.getGlassPane().setVisible(false); 2409            mainWindow.getGlassPane().setCursor(null); 2410            mainWindow.repaint(); 2411        } 2412    } 2413 2414    public HelpCtx getHelpCtx() { 2415        return new HelpCtx( "projrave_ui_elements_editors_about_query_editor" ); // NOI18N 2416 } 2417     2418} 2419

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Popular Tags