| 1 | /* |
| 2 | |
| 3 | Derby - Class org.apache.derby.tools.dblook |
| 4 | |
| 5 | Copyright 2003, 2004 The Apache Software Foundation or its licensors, as applicable. |
| 6 | |
| 7 | Licensed under the Apache License, Version 2.0 (the "License"); |
| 8 | you may not use this file except in compliance with the License. |
| 9 | You may obtain a copy of the License at |
| 10 | |
| 11 | http://www.apache.org/licenses/LICENSE-2.0 |
| 12 | |
| 13 | Unless required by applicable law or agreed to in writing, software |
| 14 | distributed under the License is distributed on an "AS IS" BASIS, |
| 15 | WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| 16 | See the License for the specific language governing permissions and |
| 17 | limitations under the License. |
| 18 | |
| 19 | */ |
| 20 | |
| 21 | package org.apache.derby.tools; |
| 22 | |
| 23 | import java.io.BufferedReader; |
| 24 | import java.io.StringReader; |
| 25 | |
| 26 | import java.sql.DriverManager; |
| 27 | import java.sql.ResultSet; |
| 28 | import java.sql.Connection; |
| 29 | import java.sql.Statement; |
| 30 | import java.sql.PreparedStatement; |
| 31 | import java.sql.SQLException; |
| 32 | import java.sql.SQLWarning; |
| 33 | import java.sql.Timestamp; |
| 34 | |
| 35 | import java.util.HashMap; |
| 36 | import java.util.StringTokenizer; |
| 37 | import java.util.ArrayList; |
| 38 | |
| 39 | import org.apache.derby.iapi.tools.i18n.LocalizedResource; |
| 40 | |
| 41 | import org.apache.derby.impl.tools.dblook.DB_Check; |
| 42 | import org.apache.derby.impl.tools.dblook.DB_Index; |
| 43 | import org.apache.derby.impl.tools.dblook.DB_Jar; |
| 44 | import org.apache.derby.impl.tools.dblook.DB_Key; |
| 45 | import org.apache.derby.impl.tools.dblook.DB_Table; |
| 46 | import org.apache.derby.impl.tools.dblook.DB_Schema; |
| 47 | import org.apache.derby.impl.tools.dblook.DB_Alias; |
| 48 | import org.apache.derby.impl.tools.dblook.DB_Trigger; |
| 49 | import org.apache.derby.impl.tools.dblook.DB_View; |
| 50 | import org.apache.derby.impl.tools.dblook.DB_GrantRevoke; |
| 51 | import org.apache.derby.impl.tools.dblook.Logs; |
| 52 | |
| 53 | public final class dblook { |
| 54 | |
| 55 | // DB2 enforces a maximum of 30 tables to be specified as part of |
| 56 | // the table list. |
| 57 | private static final int DB2_MAX_NUMBER_OF_TABLES = 30; |
| 58 | |
| 59 | private Connection conn; |
| 60 | private static PreparedStatement getColNameFromNumberQuery; |
| 61 | |
| 62 | // Mappings from id to name for schemas and tables (for ease |
| 63 | // of reference). |
| 64 | private static HashMap schemaMap; |
| 65 | private static HashMap tableIdToNameMap; |
| 66 | |
| 67 | // Command-line Parameters. |
| 68 | private static String sourceDBUrl; |
| 69 | private static String ddlFileName; |
| 70 | private static String stmtDelimiter; |
| 71 | private static boolean appendLogs; |
| 72 | private static ArrayList tableList; |
| 73 | private static String schemaParam; |
| 74 | private static String targetSchema; |
| 75 | private static boolean skipViews; |
| 76 | private static boolean verbose; |
| 77 | private static String sourceDBName; |
| 78 | |
| 79 | private static String lookLogName = "dblook.log"; |
| 80 | |
| 81 | private static LocalizedResource langUtil; |
| 82 | |
| 83 | private static boolean sqlAuthorization; |
| 84 | |
| 85 | /* ************************************************ |
| 86 | * main: |
| 87 | * Initialize program state by creating a dblook object, |
| 88 | * and then start the DDL generation by calling "go". |
| 89 | * ****/ |
| 90 | |
| 91 | public static void main(String[] args) { |
| 92 | |
| 93 | try { |
| 94 | new dblook(args); |
| 95 | } catch (Exception e) { |
| 96 | // All "normal" errors are logged and printed to |
| 97 | // console according to command line arguments, |
| 98 | // so if we get here, something unexpected must |
| 99 | // have happened; print to error stream. |
| 100 | e.printStackTrace(); |
| 101 | } |
| 102 | |
| 103 | } |
| 104 | |
| 105 | /* ************************************************ |
| 106 | * Constructor: |
| 107 | * Parse the command line, initialize logs, echo program variables, |
| 108 | * and load the Derby driver. |
| 109 | * @param args Array of dblook command-line arguments. |
| 110 | * ****/ |
| 111 | |
| 112 | public dblook(String[] args) throws Exception { |
| 113 | |
| 114 | // Adjust the application in accordance with derby.ui.locale |
| 115 | // and derby.ui.codeset |
| 116 | langUtil = LocalizedResource.getInstance(); |
| 117 | |
| 118 | // Initialize class variables. |
| 119 | initState(); |
| 120 | |
| 121 | // Parse the command line. |
| 122 | if (!parseArgs(args)) { |
| 123 | System.out.println(lookupMessage("DBLOOK_Usage")); |
| 124 | return; |
| 125 | } |
| 126 | |
| 127 | showVariables(); |
| 128 | |
| 129 | if (!loadDriver()) { |
| 130 | // Failed when loading the driver. We already logged |
| 131 | // the exception, so just return. |
| 132 | return; |
| 133 | } |
| 134 | |
| 135 | schemaMap = new HashMap(); |
| 136 | tableIdToNameMap = new HashMap(); |
| 137 | |
| 138 | // Now run the utility. |
| 139 | go(); |
| 140 | |
| 141 | } |
| 142 | |
| 143 | /* ************************************************ |
| 144 | * initState: |
| 145 | * Initialize class variables. |
| 146 | ****/ |
| 147 | |
| 148 | private void initState() { |
| 149 | |
| 150 | sourceDBUrl = null; |
| 151 | ddlFileName = null; |
| 152 | stmtDelimiter = null; |
| 153 | appendLogs = false; |
| 154 | tableList = null; |
| 155 | targetSchema = null; |
| 156 | schemaParam = null; |
| 157 | skipViews = false; |
| 158 | verbose= false; |
| 159 | sourceDBName = null; |
| 160 | return; |
| 161 | |
| 162 | } |
| 163 | |
| 164 | /* ************************************************ |
| 165 | * parseArgs: |
| 166 | * Parse the command-line arguments. |
| 167 | * @param args args[0] is the url for the source database. |
| 168 | * @return true if all parameters were loaded and the output |
| 169 | * files were successfully created; false otherwise. |
| 170 | ****/ |
| 171 | |
| 172 | private boolean parseArgs(String[] args) { |
| 173 | |
| 174 | if (args.length < 2) |
| 175 | // must have minimum of 2 args: "-d" and "<dbUrl>". |
| 176 | return false; |
| 177 | |
| 178 | int st = 0; |
| 179 | for (int i = 0; i < args.length; i++) { |
| 180 | st = loadParam(args, i); |
| 181 | if (st == -1) |
| 182 | return false; |
| 183 | i = st; |
| 184 | } |
| 185 | |
| 186 | if (sourceDBUrl == null) { |
| 187 | // must have at least a database url. |
| 188 | return false; |
| 189 | } |
| 190 | |
| 191 | // At this point, all parameters should have been read into |
| 192 | // their respective class variables. Use those |
| 193 | // variables for some further processing. |
| 194 | |
| 195 | // Setup logs. |
| 196 | boolean okay = Logs.initLogs(lookLogName, ddlFileName, appendLogs, |
| 197 | verbose, (stmtDelimiter == null ? ";" : stmtDelimiter)); |
| 198 | |
| 199 | // Get database name. |
| 200 | sourceDBName = extractDBNameFromUrl(sourceDBUrl); |
| 201 | |
| 202 | // Set up schema restriction. |
| 203 | if ((schemaParam != null) && (schemaParam.length() > 0) && |
| 204 | (schemaParam.charAt(0) != '"')) |
| 205 | // not quoted, so upper case, then add quotes. |
| 206 | { |
| 207 | targetSchema = addQuotes(expandDoubleQuotes( |
| 208 | schemaParam.toUpperCase(java.util.Locale.ENGLISH))); |
| 209 | } |
| 210 | else |
| 211 | targetSchema = addQuotes(expandDoubleQuotes(stripQuotes(schemaParam))); |
| 212 | return okay; |
| 213 | |
| 214 | } |
| 215 | |
| 216 | /* ************************************************ |
| 217 | * loadParam: |
| 218 | * Read in a flag and its corresponding values from |
| 219 | * list of command line arguments, starting at |
| 220 | * the start'th argument. |
| 221 | * @return The position of the argument that was |
| 222 | * most recently processed. |
| 223 | ****/ |
| 224 | |
| 225 | private int loadParam(String [] args, int start) { |
| 226 | |
| 227 | if ((args[start].length() == 0) || args[start].charAt(0) != '-') |
| 228 | // starting argument should be a flag; if it's |
| 229 | // not, ignore it. |
| 230 | return start; |
| 231 | |
| 232 | boolean haveVal = (args.length > start + 1); |
| 233 | switch (args[start].charAt(1)) { |
| 234 | |
| 235 | case 'd': |
| 236 | if (!haveVal) |
| 237 | return -1; |
| 238 | if (args[start].length() == 2) { |
| 239 | sourceDBUrl = stripQuotes(args[++start]); |
| 240 | return start; |
| 241 | } |
| 242 | return -1; |
| 243 | |
| 244 | case 'z': |
| 245 | if (!haveVal) |
| 246 | return -1; |
| 247 | if (args[start].length() == 2) { |
| 248 | schemaParam = args[++start]; |
| 249 | return start; |
| 250 | } |
| 251 | return -1; |
| 252 | |
| 253 | case 't': |
| 254 | if (!haveVal) |
| 255 | return -1; |
| 256 | if (args[start].equals("-td")) { |
| 257 | stmtDelimiter = args[++start]; |
| 258 | return start; |
| 259 | } |
| 260 | else if (args[start].equals("-t")) |
| 261 | // list of tables. |
| 262 | return extractTableNamesFromList(args, start+1); |
| 263 | return -1; |
| 264 | |
| 265 | case 'o': |
| 266 | if (!haveVal) |
| 267 | return -1; |
| 268 | if ((args[start].length() == 2) && (args[start+1].length() > 0)) { |
| 269 | ddlFileName = args[++start]; |
| 270 | return start; |
| 271 | } |
| 272 | return -1; |
| 273 | |
| 274 | case 'a': |
| 275 | if (args[start].equals("-append")) { |
| 276 | appendLogs = true; |
| 277 | return start; |
| 278 | } |
| 279 | return -1; |
| 280 | |
| 281 | case 'n': |
| 282 | if (args[start].equals("-noview")) { |
| 283 | skipViews = true; |
| 284 | return start; |
| 285 | } |
| 286 | return -1; |
| 287 | |
| 288 | case 'v': |
| 289 | if (args[start].equals("-verbose")) { |
| 290 | verbose = true; |
| 291 | return start; |
| 292 | } |
| 293 | return -1; |
| 294 | |
| 295 | default: |
| 296 | return -1; |
| 297 | |
| 298 | } |
| 299 | |
| 300 | } |
| 301 | |
| 302 | /* ************************************************ |
| 303 | * loadDriver: |
| 304 | * Load derby driver. |
| 305 | * @param precondition sourceDBUrl has been loaded. |
| 306 | * @return false if anything goes wrong; true otherwise. |
| 307 | ****/ |
| 308 | |
| 309 | private boolean loadDriver() { |
| 310 | |
| 311 | String derbyDriver = System.getProperty("driver"); |
| 312 | if (derbyDriver == null) { |
| 313 | if (sourceDBUrl.indexOf(":net://") != -1) |
| 314 | derbyDriver = "com.ibm.db2.jcc.DB2Driver"; |
| 315 | else if (sourceDBUrl.startsWith("jdbc:derby://")) |
| 316 | derbyDriver = "org.apache.derby.jdbc.ClientDriver"; |
| 317 | else |
| 318 | derbyDriver = "org.apache.derby.jdbc.EmbeddedDriver"; |
| 319 | } |
| 320 | |
| 321 | try { |
| 322 | Class.forName(derbyDriver).newInstance(); |
| 323 | } |
| 324 | catch (Exception e) |
| 325 | { |
| 326 | Logs.debug(e); |
| 327 | return false; |
| 328 | } |
| 329 | |
| 330 | return true; |
| 331 | } |
| 332 | |
| 333 | /* ************************************************ |
| 334 | * extractDBNameFromUrl: |
| 335 | * Given a database url, parse out the actual name |
| 336 | * of the database. This is required for creation |
| 337 | * the DB2JJARS directory (the database name is part |
| 338 | * of the path to the jar). |
| 339 | * @param dbUrl The database url from which to extract the |
| 340 | * the database name. |
| 341 | * @return the name of the database (including its |
| 342 | * path, if provided) that is referenced by the url. |
| 343 | ****/ |
| 344 | |
| 345 | private String extractDBNameFromUrl(String dbUrl) { |
| 346 | |
| 347 | if (dbUrl == null) |
| 348 | // shouldn't happen; ignore it here, as an error |
| 349 | // will be thrown we try to connect. |
| 350 | return ""; |
| 351 | |
| 352 | int start = dbUrl.indexOf("jdbc:derby:"); |
| 353 | if (start == -1) |
| 354 | // not a valid url; just ignore it (an error |
| 355 | // will be thrown when we try to connect). |
| 356 | return ""; |
| 357 | |
| 358 | start = dbUrl.indexOf("://"); |
| 359 | if (start == -1) |
| 360 | // standard url (jdbc:derby:<dbname>). Database |
| 361 | // name starts right after "derby:". The "6" in |
| 362 | // the following line is the length of "derby:". |
| 363 | start = dbUrl.indexOf("derby:") + 6; |
| 364 | else |
| 365 | // Network Server url. Database name starts right |
| 366 | // after next slash (":net://hostname:port/<dbname>). |
| 367 | // The "3" in the following line is the length of |
| 368 | // "://". |
| 369 | start = dbUrl.indexOf("/", start+3) + 1; |
| 370 | |
| 371 | int stop = -1; |
| 372 | if (dbUrl.charAt(start) == '"') { |
| 373 | // database name is quoted; end of the name is the |
| 374 | // closing quote. |
| 375 | start++; |
| 376 | stop = dbUrl.indexOf("\"", start); |
| 377 | } |
| 378 | else { |
| 379 | // Database name ends with the start of a list of connection |
| 380 | // attributes. This list can begin with either a colon |
| 381 | // or a semi-colon. |
| 382 | stop = dbUrl.indexOf(":", start); |
| 383 | if (stop != -1) { |
| 384 | if ((dbUrl.charAt(stop+1) == '/') || |
| 385 | (dbUrl.charAt(stop+1) == '\\')) |
| 386 | // then this colon is part of the path (ex. "C:"), |
| 387 | // so ignore it. |
| 388 | stop = dbUrl.indexOf(":", stop+2); |
| 389 | } |
| 390 | int stop2 = dbUrl.length(); |
| 391 | if (stop == -1) |
| 392 | // no colons; see if we can find a semi-colon. |
| 393 | stop = dbUrl.indexOf(";", start); |
| 394 | else |
| 395 | stop2 = dbUrl.indexOf(";", start); |
| 396 | stop = (stop <= stop2 ? stop : stop2); |
| 397 | } |
| 398 | |
| 399 | if (stop == -1) |
| 400 | // we have a url that ends with database name (no |
| 401 | // other attributes appended). |
| 402 | stop = dbUrl.length(); |
| 403 | |
| 404 | return dbUrl.substring(start, stop); |
| 405 | |
| 406 | } |
| 407 | |
| 408 | /* ************************************************ |
| 409 | * extractTableNamesFromList: |
| 410 | * Given an array of command line arguments containing |
| 411 | * a list of table names beginning at start'th position, |
| 412 | * read the list of table names and store them as |
| 413 | * our target table list. Names without quotes are |
| 414 | * turned into ALL CAPS and then double quotes are |
| 415 | * added; names whcih already have double quotes are |
| 416 | * stored exactly as they are. NOTE: DB2 enforces |
| 417 | * maximum of 30 tables, and ignores the rest; so |
| 418 | * do we. |
| 419 | * @param args Array of command line arguments. |
| 420 | * @start Position of the start of the list of tables |
| 421 | * with the args array. |
| 422 | * @return The position of the last table name in |
| 423 | * the list of table names. |
| 424 | ****/ |
| 425 | |
| 426 | private int extractTableNamesFromList(String [] args, |
| 427 | int start) |
| 428 | { |
| 429 | |
| 430 | int argIndex = start; |
| 431 | int count = 0; |
| 432 | tableList = new ArrayList(); |
| 433 | while (argIndex < args.length) { |
| 434 | |
| 435 | if (((args[argIndex].length() > 0) && (args[argIndex].charAt(0) == '-')) || |
| 436 | (++count > DB2_MAX_NUMBER_OF_TABLES)) |
| 437 | // we're done with the table list. |
| 438 | break; |
| 439 | |
| 440 | if ((args[argIndex].length() > 0) && (args[argIndex].charAt(0) == '"')) |
| 441 | // it's quoted. |
| 442 | tableList.add(addQuotes(expandDoubleQuotes( |
| 443 | stripQuotes(args[argIndex++])))); |
| 444 | else |
| 445 | // not quoted, so make it all caps, then add |
| 446 | // quotes. |
| 447 | tableList.add(addQuotes( |
| 448 | expandDoubleQuotes(args[argIndex++].toUpperCase( |
| 449 | java.util.Locale.ENGLISH)))); |
| 450 | |
| 451 | } |
| 452 | |
| 453 | if (tableList.size() == 0) |
| 454 | tableList = null; |
| 455 | |
| 456 | return argIndex - 1; |
| 457 | |
| 458 | } |
| 459 | |
| 460 | /* ************************************************ |
| 461 | * showVariables: |
| 462 | * Echo primary variables to output, so user can see |
| 463 | * what s/he specified. |
| 464 | ****/ |
| 465 | |
| 466 | private void showVariables() { |
| 467 | |
| 468 | if (ddlFileName != null) { |
| 469 | Logs.reportString("============================\n"); |
| 470 | Logs.reportMessage("DBLOOK_FileCreation"); |
| 471 | if (verbose) |
| 472 | writeVerboseOutput("DBLOOK_OutputLocation", |
| 473 | ddlFileName); |
| 474 | } |
| 475 | |
| 476 | Logs.reportMessage("DBLOOK_Timestamp", |
| 477 | new Timestamp(System.currentTimeMillis()).toString()); |
| 478 | Logs.reportMessage("DBLOOK_DBName", sourceDBName); |
| 479 | Logs.reportMessage("DBLOOK_DBUrl", sourceDBUrl); |
| 480 | if (tableList != null) |
| 481 | Logs.reportMessage("DBLOOK_TargetTables"); |
| 482 | if (schemaParam != null) |
| 483 | Logs.reportMessage("DBLOOK_TargetSchema", stripQuotes(schemaParam)); |
| 484 | Logs.reportString("appendLogs: " + appendLogs + "\n"); |
| 485 | return; |
| 486 | |
| 487 | } |
| 488 | |
| 489 | /* ************************************************ |
| 490 | * go: |
| 491 | * Connect to the source database, prepare statements, |
| 492 | * and load a list of table id-to-name mappings. Then, |
| 493 | * generate the DDL for the various objects in the |
| 494 | * database by making calls to static methods of helper |
| 495 | * classes (one helper class for each type of database |
| 496 | * object). If a particular object type should not be |
| 497 | * generated (because of the user-specified command- |
| 498 | * line), then we enforce that here. |
| 499 | * @precondition all user-specified parameters have |
| 500 | * been loaded. |
| 501 | * @return DDL for the source database has been |
| 502 | * generated and printed to output, subject to |
| 503 | * user-specified restrictions. |
| 504 | * ****/ |
| 505 | |
| 506 | private void go() |
| 507 | throws Exception |
| 508 | { |
| 509 | |
| 510 | try |
| 511 | { |
| 512 | // Connect to the database, prepare statements, |
| 513 | // and load id-to-name mappings. |
| 514 | this.conn = DriverManager.getConnection(sourceDBUrl); |
| 515 | prepForDump(); |
| 516 | |
| 517 | // Generate DDL. |
| 518 | |
| 519 | // Start with schemas, since we might need them to |
| 520 | // exist for jars to load properly. |
| 521 | DB_Schema.doSchemas(this.conn, |
| 522 | (tableList != null) && (targetSchema == null)); |
| 523 | |
| 524 | if (tableList == null) { |
| 525 | // Don't do these if user just wants table-related objects. |
| 526 | DB_Jar.doJars(sourceDBName, this.conn); |
| 527 | DB_Alias.doProceduresAndFunctions(this.conn); |
| 528 | } |
| 529 | |
| 530 | DB_Table.doTables(this.conn, tableIdToNameMap); |
| 531 | DB_Index.doIndexes(this.conn); |
| 532 | DB_Alias.doSynonyms(this.conn); |
| 533 | DB_Key.doKeys(this.conn); |
| 534 | DB_Check.doChecks(this.conn); |
| 535 | |
| 536 | if (!skipViews) |
| 537 | DB_View.doViews(this.conn); |
| 538 | |
| 539 | DB_Trigger.doTriggers(this.conn); |
| 540 | |
| 541 | DB_GrantRevoke.doAuthorizations(this.conn); |
| 542 | |
| 543 | // That's it; we're done. |
| 544 | if (getColNameFromNumberQuery != null) |
| 545 | getColNameFromNumberQuery.close(); |
| 546 | Logs.cleanup(); |
| 547 | |
| 548 | } |
| 549 | catch (SQLException sqlE) |
| 550 | { |
| 551 | Logs.debug(sqlE); |
| 552 | Logs.debug(Logs.unRollExceptions(sqlE), (String)null); |
| 553 | Logs.cleanup(); |
| 554 | return; |
| 555 | } |
| 556 | catch (Exception e) |
| 557 | { |
| 558 | Logs.debug(e); |
| 559 | Logs.cleanup(); |
| 560 | return; |
| 561 | } |
| 562 | finally { |
| 563 | // Close our connection. |
| 564 | if (conn != null) { |
| 565 | conn.commit(); |
| 566 | conn.close(); |
| 567 | } |
| 568 | } |
| 569 | |
| 570 | } |
| 571 | |
| 572 | /* ************************************************ |
| 573 | * prepForDump: |
| 574 | * Prepare any useful statements (i.e. statements that |
| 575 | * are required by more than one helper class) and load |
| 576 | * the id-to-name mappings for the source database. |
| 577 | ****/ |
| 578 | |
| 579 | private void prepForDump() throws Exception { |
| 580 | |
| 581 | // We're only SELECTing throughout all of this, so no need |
| 582 | // to commit (plus, disabling commit makes it easier to |
| 583 | // have multiple ResultSets open on the same connection). |
| 584 | this.conn.setAutoCommit(false); |
| 585 | |
| 586 | // Prepare statements. |
| 587 | getColNameFromNumberQuery = conn.prepareStatement( |
| 588 | "SELECT COLUMNNAME FROM SYS.SYSCOLUMNS WHERE " + |
| 589 | "REFERENCEID = ? AND COLUMNNUMBER = ?"); |
| 590 | |
| 591 | // Load list of user tables and table ids, for general use. |
| 592 | Statement stmt = conn.createStatement(); |
| 593 | ResultSet rs = stmt.executeQuery("SELECT T.TABLEID, T.TABLENAME, " + |
| 594 | "S.SCHEMANAME FROM SYS.SYSTABLES T, SYS.SYSSCHEMAS S " + |
| 595 | "WHERE T.TABLETYPE = 'T' AND T.SCHEMAID = S.SCHEMAID"); |
| 596 | |
| 597 | while (rs.next()) { |
| 598 | String tableName = addQuotes(expandDoubleQuotes(rs.getString(2))); |
| 599 | String schemaName = addQuotes(expandDoubleQuotes(rs.getString(3))); |
| 600 | tableIdToNameMap.put(rs.getString(1), |
| 601 | schemaName + "." + tableName); |
| 602 | } |
| 603 | |
| 604 | // Load schema id's and names. |
| 605 | rs = stmt.executeQuery("SELECT SCHEMAID, SCHEMANAME FROM " + |
| 606 | "SYS.SYSSCHEMAS"); |
| 607 | while (rs.next()) { |
| 608 | schemaMap.put(rs.getString(1), |
| 609 | addQuotes(expandDoubleQuotes(rs.getString(2)))); |
| 610 | } |
| 611 | |
| 612 | // Check if sqlAuthorization mode is on. If so, need to generate |
| 613 | // authorization statements. |
| 614 | rs = stmt.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY" + |
| 615 | "('derby.database.sqlAuthorization')"); |
| 616 | if (rs.next()) |
| 617 | { |
| 618 | String sqlAuth = rs.getString(1); |
| 619 | if (Boolean.valueOf(sqlAuth).booleanValue()) |
| 620 | sqlAuthorization = true; |
| 621 | } |
| 622 | stmt.close(); |
| 623 | |
| 624 | // Load default property values. |
| 625 | return; |
| 626 | |
| 627 | } |
| 628 | |
| 629 | /* ************************************************ |
| 630 | * getColumnListFromDescription: |
| 631 | * Takes string description of column numbers in the |
| 632 | * form of "(2, 1, 3...)" and the id of the table |
| 633 | * having those columns, and then returns a string |
| 634 | * with the column numbers replaced by their actual |
| 635 | * names ('2' is replaced with the 2nd column in the |
| 636 | * table, '1' with the first column, etc.). |
| 637 | * @param tableId the id of the table to which the column |
| 638 | * numbers should be applied. |
| 639 | * @param description a string holding a list of column |
| 640 | * numbers, enclosed in parentheses and separated |
| 641 | * by commas. |
| 642 | * @return a new string with the column numbers in |
| 643 | * 'description' replaced by their column names; |
| 644 | * also, the parentheses have been stripped off. |
| 645 | ****/ |
| 646 | |
| 647 | public static String getColumnListFromDescription(String tableId, |
| 648 | String description) throws SQLException |
| 649 | { |
| 650 | |
| 651 | StringBuffer sb = new StringBuffer(); |
| 652 | StringTokenizer tokenizer = new StringTokenizer( |
| 653 | description.substring(description.indexOf("(") + 1, |
| 654 | description.lastIndexOf(")")), " ,", true); |
| 655 | |
| 656 | boolean firstCol = true; |
| 657 | while (tokenizer.hasMoreTokens()) { |
| 658 | |
| 659 | String tok = tokenizer.nextToken().trim(); |
| 660 | if (tok.equals("")) |
| 661 | continue; |
| 662 | else if (tok.equals(",")) { |
| 663 | firstCol = false; |
| 664 | continue; |
| 665 | } |
| 666 | try { |
| 667 | String colName = getColNameFromNumber(tableId, |
| 668 | (Integer.valueOf(tok)).intValue()); |
| 669 | if (!firstCol) |
| 670 | sb.append(", "); |
| 671 | sb.append(colName); |
| 672 | } catch (NumberFormatException e) { |
| 673 | // not a number; could be "ASC" or "DESC" tag, |
| 674 | // which is okay; otherwise, something's wrong. |
| 675 | tok = tok.toUpperCase(); |
| 676 | if (tok.equals("DESC") || tok.equals("ASC")) |
| 677 | // then this is okay; just add the token to result. |
| 678 | sb.append(" " + tok); |
| 679 | else |
| 680 | // shouldn't happen. |
| 681 | Logs.debug("INTERNAL ERROR: read a non-number (" + |
| 682 | tok + ") when a column number was expected:\n" + |
| 683 | description, (String)null); |
| 684 | } |
| 685 | |
| 686 | } |
| 687 | |
| 688 | return sb.toString(); |
| 689 | |
| 690 | } |
| 691 | |
| 692 | /* ************************************************ |
| 693 | * getColNameFromNumber: |
| 694 | * Takes a tableid and a column number colNum, and |
| 695 | * returns the name of the colNum'th column in the |
| 696 | * table with tableid. |
| 697 | * @param tableid id of the table. |
| 698 | * @param colNum number of the column for which we want |
| 699 | * the name. |
| 700 | * @return The name of the colNum'th column in the |
| 701 | * table with tableid. |
| 702 | ****/ |
| 703 | |
| 704 | public static String getColNameFromNumber(String tableId, |
| 705 | int colNum) throws SQLException |
| 706 | { |
| 707 | |
| 708 | getColNameFromNumberQuery.setString(1, tableId); |
| 709 | getColNameFromNumberQuery.setInt(2, colNum); |
| 710 | ResultSet rs = getColNameFromNumberQuery.executeQuery(); |
| 711 | |
| 712 | if (!rs.next()) { |
| 713 | // shouldn't happen. |
| 714 | Logs.debug("INTERNAL ERROR: Failed column number " + |
| 715 | "lookup for table " + lookupTableId(tableId) + |
| 716 | ", column " + colNum, (String)null); |
| 717 | rs.close(); |
| 718 | return ""; |
| 719 | } |
| 720 | else { |
| 721 | String colName = addQuotes(expandDoubleQuotes(rs.getString(1))); |
| 722 | rs.close(); |
| 723 | return colName; |
| 724 | } |
| 725 | |
| 726 | } |
| 727 | |
| 728 | /* ************************************************ |
| 729 | * addQuotes: |
| 730 | * Add quotes to the received object name, and return |
| 731 | * the result. |
| 732 | * @param name the name to which to add quotes. |
| 733 | * @return the name with double quotes around it. |
| 734 | ****/ |
| 735 | |
| 736 | public static String addQuotes(String name) { |
| 737 | |
| 738 | if (name == null) |
| 739 | return null; |
| 740 | |
| 741 | return "\"" + name + "\""; |
| 742 | |
| 743 | } |
| 744 | |
| 745 | |
| 746 | public static String addSingleQuotes(String name) { |
| 747 | |
| 748 | if (name == null) |
| 749 | return null; |
| 750 | |
| 751 | return "'" + name + "'"; |
| 752 | } |
| 753 | |
| 754 | /* ************************************************ |
| 755 | * stripQuotes: |
| 756 | * Takes a name and, if the name is enclosed in |
| 757 | * quotes, strips the quotes off. This method |
| 758 | * assumes that the received String either has no quotes, |
| 759 | * or has a quote (double or single) as the very first |
| 760 | * AND very last character. |
| 761 | * @param quotedName a name with quotes as the first |
| 762 | * and last character, or else with no quotes at all. |
| 763 | * @return quotedName, without the quotes. |
| 764 | ****/ |
| 765 | |
| 766 | public static String stripQuotes(String quotedName) { |
| 767 | |
| 768 | if (quotedName == null) |
| 769 | return null; |
| 770 | |
| 771 | if (!(quotedName.startsWith("'") || quotedName.startsWith("\""))) |
| 772 | // name doesn't _start_ with a quote, so we do nothing. |
| 773 | return quotedName; |
| 774 | |
| 775 | if (!(quotedName.endsWith("'") || quotedName.endsWith("\""))) |
| 776 | // name doesn't _end_ with a quote, so we do nothing. |
| 777 | return quotedName; |
| 778 | |
| 779 | // Remove starting and ending quotes. |
| 780 | return quotedName.substring(1, quotedName.length() - 1); |
| 781 | |
| 782 | } |
| 783 | |
| 784 | /* ************************************************ |
| 785 | * isExcludedTable: |
| 786 | * Takes a table name and determines whether or not |
| 787 | * the DDL for objects related to that table should be |
| 788 | * generated. |
| 789 | * @param tableName name of the table to check. |
| 790 | * @return true if 1) the user specified a table list |
| 791 | * and that list does NOT include the received name; or |
| 792 | * 2) if the user specified a schema restriction and |
| 793 | * the received name does NOT have that schema; false |
| 794 | * otherwise. |
| 795 | ****/ |
| 796 | |
| 797 | public static boolean isExcludedTable(String tableName) { |
| 798 | |
| 799 | if (tableName == null) |
| 800 | return true; |
| 801 | |
| 802 | int dot = tableName.indexOf("."); |
| 803 | if (dot != -1) { |
| 804 | // strip off the schema part of the name, and see if we're |
| 805 | // okay to use it. |
| 806 | if (isIgnorableSchema(tableName.substring(0, dot))) |
| 807 | // then we exclude this table. |
| 808 | return true; |
| 809 | tableName = tableName.substring(dot + 1, |
| 810 | tableName.length()); |
| 811 | } |
| 812 | |
| 813 | return ((tableList != null) && !tableList.contains(tableName)); |
| 814 | |
| 815 | } |
| 816 | |
| 817 | /* ************************************************ |
| 818 | * Takes a schema name and determines whether or |
| 819 | * not the DDL for objects with that schema should |
| 820 | * be generated. |
| 821 | * @param schemaName schema name to be checked. |
| 822 | * @return true if 1) the user specified a target |
| 823 | * schema and that target is NOT the same as the |
| 824 | * received schema name, or 2) the schema is a |
| 825 | * system schema (SYS, SYSVISUAL, or SYSIBM); |
| 826 | * false otherwise; |
| 827 | ****/ |
| 828 | |
| 829 | private static final String[] ignorableSchemaNames = { |
| 830 | "SYSIBM", |
| 831 | "SYS", |
| 832 | "SYSVISUAL", |
| 833 | "SYSCAT", |
| 834 | "SYSFUN", |
| 835 | "SYSPROC", |
| 836 | "SYSSTAT", |
| 837 | "NULLID", |
| 838 | "SYSCS_ADMIN", |
| 839 | "SYSCS_DIAG", |
| 840 | "SYSCS_UTIL", |
| 841 | "SQLJ"}; |
| 842 | |
| 843 | public static boolean isIgnorableSchema(String schemaName) { |
| 844 | |
| 845 | if ((targetSchema != null) && (!schemaName.equals(targetSchema))) |
| 846 | return true; |
| 847 | |
| 848 | schemaName = stripQuotes(schemaName); |
| 849 | |
| 850 | boolean ret = false; |
| 851 | |
| 852 | for (int i = ignorableSchemaNames.length - 1; i >= 0;) |
| 853 | { |
| 854 | if ((ret = ignorableSchemaNames[i--].equalsIgnoreCase(schemaName))) |
| 855 | break; |
| 856 | } |
| 857 | |
| 858 | return(ret); |
| 859 | } |
| 860 | |
| 861 | /* ************************************************ |
| 862 | * Takes a string and determines whether or not that |
| 863 | * string makes reference to any of the table names |
| 864 | * in the user-specified table list. |
| 865 | * @param str The string in which to search for table names. |
| 866 | * @return true if 1) the user didn't specify a |
| 867 | * target table list, or 2) the received string |
| 868 | * contains at least one of the table names in the |
| 869 | * user-specified target list; false otherwise. |
| 870 | ****/ |
| 871 | |
| 872 | public static boolean stringContainsTargetTable(String str) { |
| 873 | |
| 874 | if (str == null) |
| 875 | // if the string is null, it can't possibly contain |
| 876 | // any table names. |
| 877 | return false; |
| 878 | |
| 879 | if (tableList == null) |
| 880 | // if we have no target tables, then default to true. |
| 881 | return true; |
| 882 | |
| 883 | int strLen = str.length(); |
| 884 | for (int i = 0; i < tableList.size(); i++) { |
| 885 | |
| 886 | String tableName = (String)tableList.get(i); |
| 887 | tableName = expandDoubleQuotes(stripQuotes(tableName)); |
| 888 | int nameLen = tableName.length(); |
| 889 | String strCopy; |
| 890 | if (tableName.equals(tableName.toUpperCase( |
| 891 | java.util.Locale.ENGLISH))) |
| 892 | // case doesn't matter. |
| 893 | strCopy = str.toUpperCase(); |
| 894 | else |
| 895 | strCopy = str; |
| 896 | int pos = strCopy.indexOf(tableName); |
| 897 | while (pos != -1) { |
| 898 | |
| 899 | // If we found it, make sure it's really a match. |
| 900 | // First, see if it's part of another word. |
| 901 | if (!partOfWord(str, pos, nameLen, strLen)) { |
| 902 | |
| 903 | // See if the match is in quotes--if so, then |
| 904 | // it should match the table name's case. |
| 905 | if ((pos >= 1) && (strCopy.charAt(pos-1) == '"') && |
| 906 | (pos + nameLen < strCopy.length()) && |
| 907 | (strCopy.charAt(pos+nameLen) == '"')) |
| 908 | { // match is quoted; check it's case. |
| 909 | if (str.substring(pos, |
| 910 | pos + nameLen).equals(tableName)) |
| 911 | // everything checks out. |
| 912 | return true; |
| 913 | } |
| 914 | else |
| 915 | // match isn't quoted, so we're okay as is. |
| 916 | return true; |
| 917 | } |
| 918 | |
| 919 | pos = str.indexOf(tableName, pos + nameLen); |
| 920 | |
| 921 | } |
| 922 | } |
| 923 | |
| 924 | // If we get here, we didn't find it. |
| 925 | return false; |
| 926 | |
| 927 | } |
| 928 | |
| 929 | /* ************************************************ |
| 930 | * partOfWord: |
| 931 | * Returns true if the part of the string given by |
| 932 | * str.substring(pos, pos + nameLen) is part of |
| 933 | * another word. |
| 934 | * @param str The string in which we're looking. |
| 935 | * @param pos The position at which the substring in |
| 936 | * question begins. |
| 937 | * @param nameLen the length of the substring in |
| 938 | * question. |
| 939 | * @param strLen The length of the string in which |
| 940 | * we're looking. |
| 941 | * @return true if the substring from pos to |
| 942 | * pos+nameLen is part of larger word (i.e. |
| 943 | * if it has a letter/digit immediately before |
| 944 | * or after); false otherwise. |
| 945 | ****/ |
| 946 | |
| 947 | private static boolean partOfWord (String str, |
| 948 | int pos, int nameLen, int strLen) |
| 949 | { |
| 950 | |
| 951 | boolean somethingBefore = false; |
| 952 | if (pos > 0) { |
| 953 | char c = str.charAt(pos-1); |
| 954 | somethingBefore = ((c == '_') || |
| 955 | Character.isLetterOrDigit(c)); |
| 956 | } |
| 957 | |
| 958 | boolean somethingAfter = false; |
| 959 | if (pos + nameLen < strLen) { |
| 960 | char c = str.charAt(pos + nameLen); |
| 961 | somethingAfter = ((c == '_') || |
| 962 | Character.isLetterOrDigit(c)); |
| 963 | } |
| 964 | |
| 965 | return (somethingBefore || somethingAfter); |
| 966 | |
| 967 | } |
| 968 | |
| 969 | /* ************************************************ |
| 970 | * expandDoubleQuotes: |
| 971 | * If the received SQL id contains a quote, we have |
| 972 | * to expand it into TWO quotes so that it can be |
| 973 | * treated correctly at parse time. |
| 974 | * @param name Id that we want to print. |
| 975 | ****/ |
| 976 | |
| 977 | public static String expandDoubleQuotes(String name) { |
| 978 | |
| 979 | if ((name == null) || (name.indexOf("\"") < 0)) |
| 980 | // nothing to do. |
| 981 | return name; |
| 982 | |
| 983 | char [] cA = name.toCharArray(); |
| 984 | |
| 985 | // Worst (and extremely unlikely) case is every |
| 986 | // character is a double quote, which means the |
| 987 | // escaped string would need to be 2 times as long. |
| 988 | char [] result = new char[2*cA.length]; |
| 989 | |
| 990 | int j = 0; |
| 991 | for (int i = 0; i < cA.length; i++) { |
| 992 | |
| 993 | if (cA[i] == '"') { |
| 994 | result[j++] = '"'; |
| 995 | result[j++] = '"'; |
| 996 | } |
| 997 | else |
| 998 | result[j++] = cA[i]; |
| 999 | |
| 1000 | } |
| 1001 | |
| 1002 | return new String(result, 0, j); |
| 1003 | |
| 1004 | } |
| 1005 | |
| 1006 | /* ************************************************ |
| 1007 | * lookupSchemaId: |
| 1008 | * Return the schema name corresponding to the |
| 1009 | * received schema id. |
| 1010 | * @param schemaId The id to look up. |
| 1011 | * @return the schema name. |
| 1012 | ****/ |
| 1013 | |
| 1014 | public static String lookupSchemaId(String schemaId) { |
| 1015 | |
| 1016 | return (String)(schemaMap.get(schemaId)); |
| 1017 | |
| 1018 | } |
| 1019 | |
| 1020 | /* ************************************************ |
| 1021 | * lookupTableId: |
| 1022 | * Return the table name corresponding to the |
| 1023 | * received table id. |
| 1024 | * @param tableId The id to look up. |
| 1025 | * @return the table name. |
| 1026 | ****/ |
| 1027 | |
| 1028 | public static String lookupTableId(String tableId) { |
| 1029 | |
| 1030 | return (String)(tableIdToNameMap.get(tableId)); |
| 1031 | |
| 1032 | } |
| 1033 | |
| 1034 | /* ************************************************ |
| 1035 | * writeVerboseOutput: |
| 1036 | * Writes the received string as "verbose" output, |
| 1037 | * meaning that we write it to System.err. We |
| 1038 | * choose System.err so that the string doesn't |
| 1039 | * show up if the user pipes dblook output to |
| 1040 | * a file (unless s/he explicitly pipes System.err |
| 1041 | * output to that file, as well). |
| 1042 | * @param key Key for the message to be printed as |
| 1043 | * verbose output. |
| 1044 | * @param value Value to be substituted into the |
| 1045 | * message. |
| 1046 | * @return message for received key has been printed |
| 1047 | * to System.err. |
| 1048 | ****/ |
| 1049 | |
| 1050 | public static void writeVerboseOutput(String key, |
| 1051 | String value) { |
| 1052 | |
| 1053 | if (value == null) |
| 1054 | System.err.println(lookupMessage(key)); |
| 1055 | else |
| 1056 | System.err.println(lookupMessage(key, |
| 1057 | new String [] {value})); |
| 1058 | return; |
| 1059 | |
| 1060 | } |
| 1061 | |
| 1062 | /* ************************************************ |
| 1063 | * lookupMessage: |
| 1064 | * Retrieve a localized message. |
| 1065 | * @param key The key for the localized message. |
| 1066 | * @return the message corresponding to the received |
| 1067 | * key. |
| 1068 | ****/ |
| 1069 | |
| 1070 | public static String lookupMessage(String key) { |
| 1071 | |
| 1072 | return lookupMessage(key, null); |
| 1073 | |
| 1074 | } |
| 1075 | |
| 1076 | /* ************************************************ |
| 1077 | * lookupMessage: |
| 1078 | * Retreive a localized message. |
| 1079 | * @param key The key for the localized message. |
| 1080 | * @param vals Array of values to be used in the |
| 1081 | * message. |
| 1082 | * @return the message corresponding to the received |
| 1083 | * key, with the received values substituted where |
| 1084 | * appropriate. |
| 1085 | ****/ |
| 1086 | |
| 1087 | public static String lookupMessage(String key, String[] vals) { |
| 1088 | |
| 1089 | String msg = ""; |
| 1090 | if (vals == null) |
| 1091 | msg = langUtil.getTextMessage(key); |
| 1092 | else { |
| 1093 | switch (vals.length) { |
| 1094 | case 1: msg = langUtil.getTextMessage( |
| 1095 | key, vals[0]); |
| 1096 | break; |
| 1097 | case 2: msg = langUtil.getTextMessage( |
| 1098 | key, vals[0], vals[1]); |
| 1099 | break; |
| 1100 | default: /* shouldn't happen */ |
| 1101 | break; |
| 1102 | } |
| 1103 | } |
| 1104 | |
| 1105 | return msg; |
| 1106 | |
| 1107 | } |
| 1108 | |
| 1109 | /* ************************************************ |
| 1110 | * removeNewlines: |
| 1111 | * Remove any newline characters from the received |
| 1112 | * string (replace them with spaces). |
| 1113 | * @param str The string from which we are removing |
| 1114 | * all newline characters. |
| 1115 | * @return The string, with all newline characters |
| 1116 | * replaced with spaces. |
| 1117 | ****/ |
| 1118 | |
| 1119 | public static String removeNewlines(String str) { |
| 1120 | |
| 1121 | if (str == null) |
| 1122 | // don't do anything. |
| 1123 | return null; |
| 1124 | |
| 1125 | StringBuffer result = null; |
| 1126 | try { |
| 1127 | |
| 1128 | BufferedReader strVal = new BufferedReader (new StringReader(str)); |
| 1129 | for (String txt = strVal.readLine(); txt != null; |
| 1130 | txt = strVal.readLine()) |
| 1131 | { |
| 1132 | if (result == null) |
| 1133 | result = new StringBuffer(txt); |
| 1134 | else { |
| 1135 | result.append(" "); |
| 1136 | result.append(txt); |
| 1137 | } |
| 1138 | } |
| 1139 | |
| 1140 | return result.toString(); |
| 1141 | |
| 1142 | } catch (Exception e) { |
| 1143 | // if something went wrong, just return the string as is-- |
| 1144 | // worst case is that the generated DDL is correct, it just |
| 1145 | // can't be run in some SQL script apps (because of the newline |
| 1146 | // characters). |
| 1147 | return str; |
| 1148 | } |
| 1149 | |
| 1150 | } |
| 1151 | |
| 1152 | } |
| 1153 | |