1 25 26 package com.sqlmagic.tinysql; 27 28 import java.util.*; 29 import java.lang.*; 30 import java.io.*; 31 import java.text.*; 32 import java.sql.Types ; 33 34 42 class tsColumn 43 { 44 public String name = null; public String alias = null; public int type = -1; public int size = 0; public int decimalPlaces = 0; public String defaultVal = null; public boolean notNull = false; public int position = 0; public String table = null; public String newLine = System.getProperty("line.separator"); 60 String functionName = (String )null; String functionArgString = (String )null; Vector functionArgs = (Vector)null; boolean isNotNull = false; 64 String stringValue = (String )null; 65 int intValue = Integer.MIN_VALUE; 66 float floatValue = Float.MIN_VALUE; 67 SimpleDateFormat fmtyyyyMMdd = new SimpleDateFormat("yyyyMMdd"); 68 Calendar today = Calendar.getInstance(); 69 boolean debug = false; 70 boolean isConstant = false; 71 boolean groupedColumn = false; 72 76 tsColumn (String s) throws tinySQLException 77 { 78 this(s,(Hashtable)null); 79 } 80 tsColumn (String s, Hashtable tableDefs) throws tinySQLException 81 { 82 FieldTokenizer ft,ftArgs; 83 int i,j,numericType,nameLength,dotAt,argIndex; 84 String upperName,tableName,checkName,nextArg; 85 tinySQLTable jtbl,foundTable; 86 tsColumn tcol; 87 Vector t; 88 Enumeration col_keys; 89 name = s; 90 nameLength = name.length(); 91 ft = new FieldTokenizer(name,'(',false); 92 if ( ft.countFields() == 2 ) 93 { 94 97 functionName = ft.getField(0).toUpperCase(); 98 if ( functionName.equals("COUNT") ) 99 { 100 type = Types.INTEGER; 101 size = 10; 102 intValue = 0; 103 groupedColumn = true; 104 } else if ( functionName.equals("SUM") ) { 105 type = Types.FLOAT; 106 size = 10; 107 groupedColumn = true; 108 } else if ( functionName.equals("CONCAT") | 109 functionName.equals("UPPER") | 110 functionName.equals("SUBSTR") ) { 111 type = Types.CHAR; 112 } 113 functionArgString = ft.getField(1); 114 ftArgs = new FieldTokenizer(functionArgString,',',false); 115 functionArgs = new Vector(); 116 argIndex = 0; 117 while ( ftArgs.hasMoreFields() ) 118 { 119 nextArg = ftArgs.nextField(); 120 tcol = new tsColumn(nextArg,tableDefs); 121 if ( tcol.isGroupedColumn() ) groupedColumn = true; 122 126 if ( functionName.equals("MAX") | functionName.equals("MIN") ) 127 { 128 if ( argIndex > 0 ) 129 throw new tinySQLException("Function can only have 1 argument"); 130 groupedColumn = true; 131 type = tcol.type; 132 size = tcol.size; 133 } else if ( functionName.equals("CONCAT") ) { 134 type = Types.CHAR; 135 size += tcol.size; 136 } else if ( functionName.equals("UPPER") ) { 137 type = Types.CHAR; 138 size = tcol.size; 139 } else if ( functionName.equals("SUBSTR") ) { 140 type = Types.CHAR; 141 if ( argIndex == 0 & tcol.type != Types.CHAR ) 142 { 143 throw new tinySQLException("SUBSTR first argument must be character"); 144 } else if ( argIndex == 1 ) { 145 if ( tcol.type != Types.INTEGER | tcol.intValue < 1 ) 146 throw new tinySQLException("SUBSTR second argument " 147 + tcol.getString() + " must be integer > 0"); 148 } else if ( argIndex == 2 ) { 149 if ( tcol.type != Types.INTEGER | tcol.intValue < 1) 150 throw new tinySQLException("SUBSTR third argument " 151 + tcol.getString() + " must be integer > 0"); 152 size = tcol.intValue; 153 } 154 } 155 argIndex++; 156 functionArgs.addElement(tcol); 157 } 158 } else { 159 162 if ( name.toUpperCase().equals("SYSDATE") ) 163 { 164 isConstant = true; 165 type = Types.DATE; 166 isNotNull = true; 167 stringValue = fmtyyyyMMdd.format(today.getTime()); 168 171 } else if ( UtilString.isQuotedString(name) ) { 172 isConstant = true; 173 type = Types.CHAR; 174 stringValue = UtilString.removeQuotes(name); 175 if ( stringValue != (String )null ) 176 { 177 size = stringValue.length(); 178 isNotNull = true; 179 } 180 } else { 181 184 numericType = UtilString.getValueType(name); 185 if ( numericType == Types.INTEGER ) 186 { 187 intValue = Integer.valueOf(name).intValue(); 188 size = 10; 189 type = numericType; 190 isConstant = true; 191 isNotNull = true; 192 } else if ( numericType == Types.FLOAT ) { 193 floatValue = Float.valueOf(name).floatValue(); 194 size = 10; 195 type = numericType; 196 isConstant = true; 197 isNotNull = true; 198 } else { 199 202 foundTable = (tinySQLTable)null; 203 upperName = name.toUpperCase(); 204 if ( debug ) 205 System.out.println("Trying to find table for " + upperName); 206 dotAt = upperName.indexOf("."); 207 if ( dotAt > -1 ) 208 { 209 tableName = upperName.substring(0,dotAt); 210 if ( tableDefs != (Hashtable)null & 211 tableName.indexOf("->") < 0 ) 212 { 213 t = (Vector)tableDefs.get("TABLE_SELECT_ORDER"); 214 tableName = UtilString.findTableAlias(tableName,t); 215 } 216 table = tableName; 217 upperName = upperName.substring(dotAt + 1); 218 foundTable = (tinySQLTable)tableDefs.get(tableName); 219 } else if ( tableDefs != (Hashtable)null ) { 220 224 t = (Vector)tableDefs.get("TABLE_SELECT_ORDER"); 225 for ( j = 0; j < t.size(); j++ ) 226 { 227 tableName = (String )t.elementAt(j); 228 jtbl = (tinySQLTable)tableDefs.get(tableName); 229 col_keys = jtbl.column_info.keys(); 230 233 while (col_keys.hasMoreElements()) 234 { 235 checkName = (String )col_keys.nextElement(); 236 if ( checkName.equals(upperName) | 237 upperName.equals("*") ) 238 { 239 upperName = checkName; 240 foundTable = jtbl; 241 break; 242 } 243 } 244 if ( foundTable != (tinySQLTable)null ) break; 245 } 246 } else { 247 if ( debug ) System.out.println("No table definitions."); 248 } 249 if ( foundTable != (tinySQLTable)null ) 250 { 251 name = foundTable.table + "->" + foundTable.tableAlias 252 + "." + upperName; 253 type = foundTable.ColType(upperName); 254 size = foundTable.ColSize(upperName); 255 decimalPlaces = foundTable.ColDec(upperName); 256 table = foundTable.table; 257 } 258 } 259 } 260 } 261 } 262 public void update(String inputColumnName,String inputColumnValue) 263 throws tinySQLException 264 { 265 int i,startAt,charCount; 266 tsColumn argColumn; 267 StringBuffer concatBuffer; 268 if ( isConstant ) return; 269 if ( functionName == (String )null ) 270 { 271 if ( inputColumnName.equals(name) ) 272 { 273 if ( debug ) System.out.println("Update " + name + " with column " 274 + inputColumnName + "=" + inputColumnValue); 275 279 isNotNull = false; 280 stringValue = (String )null; 281 intValue = Integer.MIN_VALUE; 282 floatValue = Float.MIN_VALUE; 283 if ( inputColumnValue != (String )null ) 284 if ( inputColumnValue.trim().length() > 0 ) isNotNull = true; 285 if ( type == Types.CHAR | type == Types.DATE ) 286 { 287 stringValue = inputColumnValue; 288 } else if ( type == Types.INTEGER & isNotNull ) { 289 try 290 { 291 intValue = Integer.parseInt(inputColumnValue.trim()); 292 } catch (Exception ex) { 293 throw new tinySQLException(inputColumnValue + " is not an integer."); 294 } 295 } else if ( type == Types.FLOAT & isNotNull ) { 296 try 297 { 298 floatValue = Float.valueOf(inputColumnValue.trim()).floatValue(); 299 } catch (Exception ex) { 300 throw new tinySQLException(inputColumnValue + " is not a Float."); 301 } 302 } 303 } 304 } else if ( functionName.equals("CONCAT") ) { 305 concatBuffer = new StringBuffer (); 306 for ( i = 0; i < functionArgs.size(); i++ ) 307 { 308 argColumn = (tsColumn)functionArgs.elementAt(i); 309 argColumn.update(inputColumnName,inputColumnValue); 310 if ( argColumn.isNotNull ) 311 { 312 concatBuffer.append(argColumn.getString()); 313 isNotNull = true; 314 } 315 } 316 stringValue = concatBuffer.toString(); 317 } else if ( functionName.equals("UPPER") ) { 318 argColumn = (tsColumn)functionArgs.elementAt(0); 319 argColumn.update(inputColumnName,inputColumnValue); 320 if ( argColumn.isNotNull ) 321 { 322 stringValue = argColumn.getString().toUpperCase(); 323 isNotNull = true; 324 } 325 } else if ( functionName.equals("SUBSTR") ) { 326 if ( functionArgs.size() != 3 ) 327 throw new tinySQLException("Wrong number of arguments for SUBSTR"); 328 argColumn = (tsColumn)functionArgs.elementAt(1); 329 startAt = argColumn.intValue; 330 argColumn = (tsColumn)functionArgs.elementAt(2); 331 charCount = argColumn.intValue; 332 argColumn = (tsColumn)functionArgs.elementAt(0); 333 argColumn.update(inputColumnName,inputColumnValue); 334 if ( argColumn.isNotNull ) 335 { 336 stringValue = argColumn.stringValue; 337 if ( startAt < stringValue.length() - 1 & charCount > 0 ) 338 { 339 stringValue = stringValue.substring(startAt - 1 ,startAt + charCount - 1); 340 isNotNull = true; 341 } else { 342 stringValue = (String )null; 343 } 344 } 345 } else if ( functionName.equals("COUNT") ) { 346 argColumn = (tsColumn)functionArgs.elementAt(0); 347 if ( argColumn.name.equals(inputColumnName) & argColumn.isNotNull ) 348 { 349 isNotNull = true; 350 if ( intValue == Integer.MIN_VALUE ) 351 { 352 intValue = 0; 353 } else { 354 intValue = intValue + 1; 355 } 356 } 357 } else if ( functionName.equals("SUM") ) { 358 argColumn = (tsColumn)functionArgs.elementAt(0); 359 argColumn.update(inputColumnName,inputColumnValue); 360 if ( argColumn.type == Types.CHAR | argColumn.type == Types.DATE ) 361 throw new tinySQLException(argColumn.name + " is not numeric!"); 362 if ( argColumn.name.equals(inputColumnName) & argColumn.isNotNull ) 363 { 364 isNotNull = true; 365 if ( floatValue == Float.MIN_VALUE ) 366 { 367 floatValue = (float)0.0; 368 } else { 369 if ( argColumn.type == Types.INTEGER ) 370 floatValue += new Integer (argColumn.intValue).floatValue(); 371 else 372 floatValue += argColumn.floatValue; 373 } 374 } 375 } else if ( functionName.equals("MAX") | functionName.equals("MIN") ) { 376 argColumn = (tsColumn)functionArgs.elementAt(0); 377 argColumn.update(inputColumnName,inputColumnValue); 378 if ( argColumn.name.equals(inputColumnName) & argColumn.isNotNull ) 379 { 380 isNotNull = true; 381 if ( argColumn.type == Types.CHAR | argColumn.type == Types.DATE ) 382 { 383 if ( stringValue == null ) 384 { 385 stringValue = argColumn.stringValue; 386 } else { 387 390 if ( functionName.equals("MAX") & 391 ( argColumn.stringValue.compareTo(stringValue) > 0 ) ) 392 { 393 stringValue = argColumn.stringValue; 394 } else if ( functionName.equals("MIN") & 395 ( argColumn.stringValue.compareTo(stringValue) < 0 ) ) { 396 stringValue = argColumn.stringValue; 397 } 398 } 399 } else if ( argColumn.type == Types.INTEGER ) { 400 403 if ( intValue == Integer.MIN_VALUE ) 404 { 405 intValue = argColumn.intValue; 406 } else { 407 if ( functionName.equals("MIN") & 408 argColumn.intValue < intValue ) 409 intValue = argColumn.intValue; 410 else if ( functionName.equals("MAX") & 411 argColumn.intValue > intValue ) 412 intValue = argColumn.intValue; 413 } 414 } else if ( argColumn.type == Types.FLOAT ) { 415 if ( floatValue == Float.MIN_VALUE ) 416 { 417 floatValue = argColumn.floatValue; 418 } else { 419 if ( functionName.equals("MIN") & 420 argColumn.floatValue < floatValue ) 421 floatValue = argColumn.floatValue; 422 else if ( functionName.equals("MAX") & 423 argColumn.floatValue > floatValue ) 424 floatValue = argColumn.floatValue; 425 } 426 } 427 } 428 } 429 } 430 public boolean isGroupedColumn() 431 { 432 return groupedColumn; 433 } 434 public String getString() 435 { 436 if ( type == Types.CHAR | type == Types.DATE ) { 437 return stringValue; 438 } else if ( type == Types.INTEGER ) { 439 if ( intValue == Integer.MIN_VALUE ) return (String )null; 440 return Integer.toString(intValue); 441 } else if ( type == Types.FLOAT ) { 442 if ( floatValue == Float.MIN_VALUE ) return (String )null; 443 return Float.toString(floatValue); 444 } 445 return (String )null; 446 } 447 public String toString() 448 { 449 int i; 450 StringBuffer outputBuffer = new StringBuffer (); 451 if ( functionName == (String )null ) 452 { 453 outputBuffer.append("-----------------------------------" + newLine 454 + "Column Name: " + name + newLine 455 + "Table: " + table + newLine 456 + "IsNotNull: " + isNotNull + newLine 457 + "Type: " + type + newLine 458 + "Size: " + size + newLine 459 + "Value: " + getString()); 460 } else { 461 outputBuffer.append("Function: " + functionName + newLine 462 + "Type: " + type + newLine 463 + "Size: " + size + newLine 464 + "Value: " + getString()); 465 for ( i = 0; i < functionArgs.size(); i++ ) 466 { 467 outputBuffer.append(newLine + "Argument " + i + " follows" + newLine 468 + ((tsColumn)functionArgs.elementAt(i)).toString() + newLine); 469 } 470 } 471 return outputBuffer.toString(); 472 } 473 } 474 | Popular Tags |