001/* 002 * Copyright (c) 2003-2010 The Regents of the University of California. 003 * All rights reserved. 004 * 005 * '$Author: welker $' 006 * '$Date: 2010-05-06 05:21:26 +0000 (Thu, 06 May 2010) $' 007 * '$Revision: 24234 $' 008 * 009 * Permission is hereby granted, without written agreement and without 010 * license or royalty fees, to use, copy, modify, and distribute this 011 * software and its documentation for any purpose, provided that the above 012 * copyright notice and the following two paragraphs appear in all copies 013 * of this software. 014 * 015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY 016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES 017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF 018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF 019 * SUCH DAMAGE. 020 * 021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, 022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF 023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE 024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF 025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, 026 * ENHANCEMENTS, OR MODIFICATIONS. 027 * 028 */ 029 030package org.ecoinformatics.seek.querybuilder; 031 032import java.util.Enumeration; 033import java.util.Hashtable; 034import java.util.Vector; 035 036import org.kepler.objectmanager.data.db.DSSchemaIFace; 037import org.kepler.objectmanager.data.db.DSTableFieldIFace; 038 039/** 040 * This class extends DBSelectTableModelBase and is used to enabled the user to 041 * indicate which items will be displayed, the boolean operator for the criteria 042 * and the criteria. It's JTable (view) is displayed in the lower panel of the 043 * "Standard" tab. 044 */ 045public class DBSelectTableModelStd extends DBSelectTableModelBase { 046 static final String INCLUDESELECTION = "Include in Selection"; 047 048 protected Hashtable mModelHash = null; 049 050 protected String[] COLUMN_TITLES = { "Table", "Field", "Data Type", 051 INCLUDESELECTION, "Operator", "Criteria" }; 052 053 /** 054 * Constructor for Table Model 055 * 056 * @param aSchema 057 * the schema object 058 * @param aModelHash 059 * a hashtable containing allthe schema's of all tables 060 */ 061 public DBSelectTableModelStd(DSSchemaIFace aSchema, Hashtable aModelHash) { 062 super(aSchema); 063 mModelHash = aModelHash; 064 } 065 066 /** 067 * This takes the table name and field name and looks up the 068 * DBSelectTableModelItem from the cahced Model, so both UIs are working 069 * from the same data model item. 070 * 071 * @param aField 072 * filed 073 * @param aValue 074 * new field name 075 */ 076 protected void setFieldName(DBSelectTableModelItem aField, Object aValue) { 077 // If "*" is selected then it will be of type DBTableField 078 if (!(aValue instanceof String)) { 079 aValue = aValue.toString(); 080 } 081 082 // Find the current Model item (aField) and then replace it the proper 083 // one 084 // from the hashtable of models 085 int inx = mItems.indexOf(aField); 086 if (inx != -1) { 087 // The rows in this model contain DBSelectTableModelItem items 088 // so when the name changes we need to "look up" the new item 089 // and replace the existing one. 090 // NOTE: We set the diaply to false for the one we are removing. 091 // if it is in the list twice (and it shouldn't be) then that is the 092 // way it goes. 093 DBDisplayItemIFace displayItem = getDisplayItem(aField 094 .getTableName(), (String) aValue); 095 if (displayItem != null 096 && displayItem instanceof DBSelectTableModelItem) { 097 // aField.setDisplayed(false); 098 setDisplayListCell(aField, false); 099 DBSelectTableOverviewModel model = (DBSelectTableOverviewModel) mModelHash 100 .get(aField.getTableName()); 101 model.fireTableDataChanged(); 102 mItems.remove(inx); 103 mItems.insertElementAt(displayItem, inx); 104 } 105 } 106 } 107 108 /** 109 * Look up in the Schema for the field in it's table Then checks to see if 110 * it is an instanceof DBTableField and returns that 111 * 112 * @param aItemCell 113 * TableMode cell item 114 * @return List Cell Item (hopefully), or null if it isn't an instance of 115 * DBTableField 116 */ 117 protected DBTableField getFieldFor(DBSelectTableModelItem aItemCell) { 118 DSTableFieldIFace fieldIFace = DBUIUtils.getFieldByName(mSchema, 119 aItemCell.getTableName(), aItemCell.getName()); 120 if (fieldIFace instanceof DBTableField) { 121 return (DBTableField) fieldIFace; 122 } 123 return null; 124 } 125 126 /** 127 * Sets Display attr in UI List cell 128 * 129 * @param aFieldCell 130 * the field 131 * @param aFlag 132 * whether it is displayed 133 */ 134 protected void setDisplayListCell(DBSelectTableModelItem aFieldCell, 135 boolean aFlag) { 136 DBSelectTableOverviewModel model = (DBSelectTableOverviewModel) mModelHash 137 .get(aFieldCell.getTableName()); 138 if (model != null) { 139 // For the Intermediate Pane the Upper model is different than when 140 // it is a Standard Pane. 141 // For a Standard pane the upper and lower panes use the same model 142 // For an Intermediate Pane the the Upper model is built from the 143 // Desktop GUI 144 // and the lower model is from the original schema 145 // 146 // So here we such the Hashtable which are from the upper model and 147 // get the appropriate fild and make sure it is updated as well 148 DBTableField field = getFieldFor(aFieldCell); 149 if (field != null) { 150 field.setDisplayed(aFlag); 151 if (mSchema instanceof DBTableDesktopPane) { 152 ((DBTableDesktopPane) mSchema).makeDirty(); 153 } 154 } 155 aFieldCell.setDisplayed(aFlag); 156 model.fireTableDataChanged(); 157 if (model.getTableView() != null) { 158 model.getTableView().repaint(); 159 } 160 fireTableDataChanged(); 161 } 162 } 163 164 /** 165 * Returns the Class object for a column 166 * 167 * @param aCol 168 * index of column 169 * @return the Class of the column 170 */ 171 public Class getColumnClass(int aCol) { 172 return getValueAt(0, aCol).getClass(); 173 } 174 175 /** 176 * Get the column name 177 */ 178 public String getColumnName(int column) { 179 return COLUMN_TITLES[column]; 180 } 181 182 /** 183 * Returns the number of columns 184 * 185 * @return Number of columns 186 */ 187 public int getColumnCount() { 188 return COLUMN_TITLES.length; 189 } 190 191 /** 192 * Return the model hashtable 193 * 194 * */ 195 public Hashtable getModelHashtable() { 196 return mModelHash; 197 } 198 199 /** 200 * Indicates if col and row is editable 201 * 202 * @param aRow 203 * index of row 204 * @param aCol 205 * index of column 206 * @return true if edittable, otherwise false 207 */ 208 public boolean isCellEditable(int aRow, int aCol) { 209 boolean tableNameOK = isTableNameOK(aRow); 210 switch (aCol) { 211 case 0: 212 return true; 213 case 1: 214 return tableNameOK; 215 case 2: 216 return false; 217 case 3: 218 return tableNameOK; 219 case 4: 220 return tableNameOK; 221 case 5: { 222 DBSelectTableModelItem field = getFieldForRow(aRow); 223 return tableNameOK && field != null 224 && !field.getName().equals(DBUIUtils.ALL_FIELDS) 225 && !field.getOperator().equals(DBUIUtils.NO_NAME) 226 && field.getOperator().length() > 0; 227 } 228 } 229 return false; 230 } 231 232 /** 233 * Gets the value of the row, col 234 * 235 * @param aRow 236 * index of row 237 * @param aCol 238 * index of column 239 * @return the object 240 */ 241 public Object getValueAt(int aRow, int aCol) { 242 DBSelectTableModelItem field = getFieldForRow(aRow); 243 if (field != null) { 244 switch (aCol) { 245 case 0: 246 return field.getTableName(); 247 case 1: 248 return field.getName(); 249 case 2: 250 return field.getDataType(); 251 case 3: 252 return new Boolean(field.isDisplayed()); 253 case 4: 254 return field.getOperator(); 255 case 5: 256 return field.getCriteria(); 257 default: 258 return "N/A"; 259 } 260 } 261 return ""; 262 } 263 264 /** 265 * Retrieves the Model that represents this table, then looks up the Field 266 * (DBSelectTableModelItem) and returns the item 267 * 268 * @param aTableName 269 * name of table model 270 * @param aFieldName 271 * name of feild to be looked up 272 * @return the display item 273 */ 274 protected DBSelectTableModelItem getDisplayItem(String aTableName, 275 String aFieldName) { 276 DBSelectTableOverviewModel model = (DBSelectTableOverviewModel) mModelHash 277 .get(aTableName); 278 if (model != null) { 279 return model.getItemByName(aFieldName); 280 } 281 return null; 282 } 283 284 /** 285 * Sets a new value into the Model 286 * 287 * @param aValue 288 * value 289 * @param aRow 290 * index of row 291 * @param aCol 292 * index of column 293 */ 294 public void setValueAt(Object aValue, int aRow, int aCol) { 295 if (aValue == null) 296 return; 297 298 DBSelectTableModelItem fieldCell = null; 299 if (aRow < mItems.size()) { 300 fieldCell = (DBSelectTableModelItem) mItems.elementAt(aRow); 301 switch (aCol) { 302 case 0: { 303 String tableName = (String) aValue; 304 if (!tableName.equals(DBUIUtils.NO_NAME)) { 305 String fieldName = (String) mAvailFieldNames.elementAt(0); 306 fieldCell.setDisplayItem(getDisplayItem(tableName, 307 fieldName)); 308 fieldCell.setName((String) mAvailFieldNames.elementAt(0)); 309 } 310 setTableName(fieldCell, aRow, aValue); 311 } 312 break; 313 314 case 1: 315 setFieldName(fieldCell, aValue); 316 break; 317 318 case 2: 319 // shouldn't happen 320 break; 321 322 case 3: 323 setDisplay(fieldCell, aValue); 324 break; 325 326 case 4: 327 if (aValue instanceof String) { 328 fieldCell.setOperator((String) aValue); 329 if (aValue.equals(DBUIUtils.NO_NAME)) { 330 setCriteria(fieldCell, ""); 331 } 332 } 333 break; 334 335 case 5: 336 setCriteria(fieldCell, aValue); 337 break; 338 } 339 this.fireTableDataChanged(); 340 } 341 } 342 343 /** 344 * Adds a condition 345 * 346 * @param aSelectHash 347 * the hashtable of table/field names 348 * @param aCond 349 * the condition of class DBSelectTableModelItem 350 */ 351 private void addCondition(Hashtable aSelectHash, Object aCond, 352 Hashtable aItemsToDelete) { 353 if (aCond instanceof DBSelectTableModelItem) { 354 DBSelectTableModelItem modelItem = (DBSelectTableModelItem) aCond; 355 mItems.add(modelItem); 356 357 int newInx = mItems.size() - 1; 358 modelItem.setDisplayItem(getDisplayItem(modelItem.getTableName(), 359 modelItem.getName())); 360 361 boolean isDisplayed = false; 362 if (aSelectHash != null) { 363 String key = DBUIUtils.getFullFieldName(modelItem 364 .getTableName(), modelItem.getName()); 365 if (aSelectHash.get(key) != null) { 366 isDisplayed = true; 367 aItemsToDelete.put(key, key); 368 } 369 } else { 370 isDisplayed = true; 371 } 372 373 DBDisplayItemIFace item = modelItem.getDisplayItem(); 374 if (item instanceof DBSelectTableModelItem) { 375 setDisplayListCell((DBSelectTableModelItem) item, isDisplayed); 376 } 377 } 378 } 379 380 /** 381 * Used to recurse the where object tree, to determine it's complexity 382 * 383 * @param aSelectHash 384 * the hashtable of table/field names 385 * @param aOper 386 * a where object 387 * @param aCurrOper 388 * a string representing the operator 389 */ 390 private int recurseOperator(Hashtable aSelectHash, DBWhereOperator aOper, 391 String aCurrOper, Hashtable aItemsToDelete) { 392 for (Enumeration e = aOper.getEnumeration(); e.hasMoreElements();) { 393 DBWhereIFace item = (DBWhereIFace) e.nextElement(); 394 if (item instanceof DBWhereOperator) { 395 DBWhereOperator oper = (DBWhereOperator) item; 396 if (oper.getOperator().equals(aCurrOper)) { 397 int status = recurseOperator(aSelectHash, oper, aCurrOper, 398 aItemsToDelete); 399 if (status != DBQueryDef.BUILD_OK) { 400 return DBQueryDef.BUILD_TOO_COMPLEX_WHERE; 401 } 402 } else { 403 return DBQueryDef.BUILD_TOO_COMPLEX_WHERE; 404 } 405 } else { 406 addCondition(aSelectHash, item, aItemsToDelete); 407 } 408 } 409 410 return DBQueryDef.BUILD_OK; 411 } 412 413 /** 414 * Build UI from the Query Definition Object 415 * 416 * @param aQueryDef 417 * the QueryDef to build the UI from 418 * @param aStrBuf 419 * the out put string buffer 420 * @return returns the type of operator it is "AND"/"OR" 421 */ 422 public int buildFromQueryDef(DBQueryDef aQueryDef, StringBuffer aStrBuf, 423 boolean aSkipJoins) { 424 if (aQueryDef == null) 425 return DBQueryDef.BUILD_ERROR; 426 427 int status = DBQueryDef.BUILD_OK; 428 mItems.clear(); 429 430 Hashtable selectHash = new Hashtable(); 431 Vector selects = aQueryDef.getSelects(); 432 String operStr = null; 433 434 // Build hash of item in the "display" portion 435 for (Enumeration e = selects.elements(); e.hasMoreElements();) { 436 DBSelectTableModelItem item = (DBSelectTableModelItem) e 437 .nextElement(); 438 selectHash.put(DBUIUtils.getFullFieldName(item.getTableName(), item 439 .getName()), item); 440 } 441 442 // Recurse the where clause adding them to the model 443 // each one will check the has to determine whether the "display" bool 444 // should be flipped 445 // if so, then it removes it from the hash 446 // 447 // NOTE: The selectHash contains all the table.fieldNames for those 448 // fields 449 // in the select statement. Since this is the "Standard" tab and all the 450 // conditions end up 451 // in the lower panel with all the select fields we need to make sure we 452 // don't put in select fields 453 // when they can be "covered" by a condition field. 454 // 455 // Sooooo, the itemsToDelete hashtable contains all the select field 456 // names that were used by the 457 // condition fields and no longer need to be added on their own. The 458 // addCondition and recurseOperator 459 // methods will fill the itemsToDelete hashtable as it adds the 460 // conditional fields to the bottom panel table. 461 Hashtable itemsToDelete = new Hashtable(); 462 DBWhereIFace whereObj = aQueryDef.getWhere(); 463 if (whereObj != null) { 464 if (whereObj instanceof DBWhereCondition) { 465 addCondition(selectHash, (DBWhereCondition) whereObj, 466 itemsToDelete); 467 } else { 468 DBWhereOperator operator = (DBWhereOperator) whereObj; 469 operStr = operator.getOperator(); 470 status = recurseOperator(selectHash, operator, operStr, 471 itemsToDelete); 472 } 473 } 474 475 if (status != DBQueryDef.BUILD_OK) 476 return status; 477 478 // Now delete all the select fields from the hashtable because they have 479 // all ready 480 // been added via the conditions. If it is still in the selectHash 481 // hastable then 482 // it will need to be added with the "display" attribute checked 483 for (Enumeration e = itemsToDelete.elements(); e.hasMoreElements();) { 484 String fullName = (String) e.nextElement(); 485 if (selectHash.get(fullName) != null) { 486 selectHash.remove(fullName); 487 } 488 } 489 490 // Any items left in the hash should be added with the display set 491 // pass in null for the hash 492 for (Enumeration e = selectHash.elements(); e.hasMoreElements();) { 493 DBSelectTableModelItem item = (DBSelectTableModelItem) e 494 .nextElement(); 495 addCondition(null, item, itemsToDelete); 496 } 497 498 // make sure this gets set in case the where clause is empty and 499 // there are no joins 500 if (operStr == null || operStr.length() == 0) { 501 operStr = DBWhereOperator.AND_OPER; 502 } 503 504 if (!aSkipJoins && aQueryDef.getJoins() != null 505 && aQueryDef.getJoins().size() > 0) { 506 if (operStr.equals(DBWhereOperator.AND_OPER)) { 507 // Build hash of item in the "display" portion 508 for (Enumeration e = aQueryDef.getJoins().elements(); e 509 .hasMoreElements();) { 510 DBSelectTableModelItem leftItem = (DBSelectTableModelItem) e 511 .nextElement(); 512 DBSelectTableModelItem rightItem = (DBSelectTableModelItem) e 513 .nextElement(); 514 // if (leftItem == null ||leftItem.getTableName() == null) 515 // { 516 // int x = 0; 517 // } 518 DBSelectTableOverviewModel model = (DBSelectTableOverviewModel) mModelHash 519 .get(leftItem.getTableName()); 520 if (model != null) { 521 DBSelectTableModelItem modelItem = model 522 .getItemByName(leftItem.getName()); 523 if (modelItem != null) { 524 DBSelectTableModelItem item = new DBSelectTableModelItem( 525 leftItem.getTableName(), 526 leftItem.getName(), 527 modelItem.getDataType(), 528 false, 529 DBUIUtils.getFullFieldName(rightItem 530 .getTableName(), rightItem 531 .getName()), 532 DBSelectTableUIStd.OPERS_TXT[DBSelectTableUIStd.EQUALS_INX], 533 leftItem.getMissingValueCode()); 534 item.setDisplayItem(modelItem); 535 mItems.add(item); 536 } 537 } 538 } 539 } else { 540 status = DBQueryDef.BUILD_TOO_COMPLEX_JOINS; 541 } 542 } 543 544 // make sure there is always an empty "row" at the bottom 545 mItems.add(new DBSelectTableModelItem()); 546 547 // tell the UI what kind of operator we have 548 aStrBuf.setLength(0); 549 aStrBuf.append(operStr); 550 551 return status; 552 } 553 554}