001package gudusoft.gsqlparser.sqlenv; 002 003import gudusoft.gsqlparser.*; 004import gudusoft.gsqlparser.compiler.TFunction; 005import gudusoft.gsqlparser.nodes.TObjectName; 006import gudusoft.gsqlparser.nodes.TTypeName; 007import gudusoft.gsqlparser.util.SQLUtil; 008 009import java.util.ArrayList; 010import java.util.HashMap; 011import java.util.List; 012 013import static gudusoft.gsqlparser.sqlenv.ESQLDataObjectType.*; 014 015/** 016 * SQL environment includes the metadata of a list of databases. The typical scenario is there is one database includes 017 * some schemas, and each schema includes some tables, views, procedures and etc. 018 * <br> 019 * <br>Database known as catalog in ANSI SQL. 020 * <br>Each catalog including a list of schemas. 021 * <br>Each schema including a list of schema objects such as table, procedure, function, trigger and more. 022 * <br> 023 * Implement your own concrete class derived from this class to get the metadata from a real database. 024 * Usually, this is done by querying the INFORMATION_SCHEMA in the {@link #initSQLEnv()} method which should be override 025 * in your own class. 026 * <br><br> 027 * 028 */ 029public abstract class TSQLEnv { 030 031 public static boolean[] columnCollationCaseSensitive = { 032 false, // dbvaccess, 033 false, // dbvansi, 034 false, // dbvathena 035 false, // dbvazuresql, 036 false, // dbvbigquery, 037 false, // dbvcouchbase, 038 false, // dbvdax, 039 false, // dbvdb2, 040 false, // dbvexasol, 041 false, // dbvfirebird, 042 false, // dbvgeneric, 043 false, // dbvgreenplum, 044 false, // dbvhana, 045 false, // dbvhive, 046 false, // dbvimpala, 047 false, // dbvinformix, 048 false, // dbvmdx, 049 false, // dbvmysql, 050 false, // dbvmssql, 051 false, // dbvnetezza, 052 false, // dbvodbc, 053 false, // dbvopenedge, 054 true, // dbvoracle, 055 false, // dbvpostgresql, 056 false,// dbvpresto 057 false, // dbvredshift, 058 false, // dbvsnowflake, 059 false, // dbvsoql, 060 false, // dbvsparksql, 061 false, // dbvsybase, 062 false, // dbvteradata, 063 false, // dbvtrino 064 false, // dbvvertica, 065 false, // dbvdatabricks 066 false, // dbvgaussdb, 067 }; 068 069 public static boolean[] functionCollationCaseSensitive = { 070 false, // dbvaccess, 071 false, // dbvansi, 072 false,// dbvathena 073 false, // dbvazuresql, 074 false, // dbvbigquery, 075 false, // dbvcouchbase, 076 false, // dbvdax, 077 false, // dbvdb2, 078 false, // dbvexasol, 079 false, // dbvfirebird, 080 false, // dbvgeneric, 081 false, // dbvgreenplum, 082 false, // dbvhana, 083 false, // dbvhive, 084 false, // dbvimpala, 085 false, // dbvinformix, 086 false, // dbvmdx, 087 false, // dbvmysql, 088 false, // dbvmssql, 089 false, // dbvnetezza, 090 false, // dbvodbc, 091 false, // dbvopenedge, 092 true, // dbvoracle, 093 true, // dbvpostgresql, 094 false,// dbvpresto 095 false, // dbvredshift, 096 false, // dbvsnowflake, 097 false, // dbvsoql, 098 false, // dbvsparksql, 099 false, // dbvsybase, 100 false, // dbvteradata, 101 false, // dbvtrino 102 false, // dbvvertica, 103 false, // dbvdatabricks, 104 false, // dbvgaussdb, 105 }; 106 public static boolean[] tableCollationCaseSensitive = { 107 false, // dbvaccess, 108 false, // dbvansi, 109 false,// dbvathena 110 false, // dbvazuresql, 111 false, // dbvbigquery, 112 false, // dbvcouchbase, 113 false, // dbvdax, 114 true, // dbvdb2, 115 false, // dbvexasol, 116 false, // dbvfirebird, 117 false, // dbvgeneric, 118 false, // dbvgreenplum, 119 false, // dbvhana, 120 false, // dbvhive, 121 false, // dbvimpala, 122 false, // dbvinformix, 123 false, // dbvmdx, 124 false, // dbvmysql, 125 false, // dbvmssql, 126 false, // dbvnetezza, 127 false, // dbvodbc, 128 false, // dbvopenedge, 129 true, // dbvoracle, 130 true, // dbvpostgresql, 131 false,// dbvpresto 132 false, // dbvredshift, 133 false, // dbvsnowflake, 134 false, // dbvsoql, 135 false, // dbvsparksql, 136 false, // dbvsybase, 137 false, // dbvteradata, 138 false, // dbvtrino 139 false, // dbvvertica, 140 false, // dbvdatabricks, 141 false, // dbvgaussdb, 142 }; 143 144 public static boolean[] catalogCollationCaseSensitive = { 145 false, // dbvaccess, 146 false, // dbvansi, 147 false,// dbvathena 148 false, // dbvazuresql, 149 false, // dbvbigquery, 150 false, // dbvcouchbase, 151 false, // dbvdax, 152 true, // dbvdb2, 153 false, // dbvexasol, 154 false, // dbvfirebird, 155 false, // dbvgeneric, 156 false, // dbvgreenplum, 157 false, // dbvhana, 158 false, // dbvhive, 159 false, // dbvimpala, 160 false, // dbvinformix, 161 false, // dbvmdx, 162 true, // dbvmysql, 163 false, // dbvmssql, 164 false, // dbvnetezza, 165 false, // dbvodbc, 166 false, // dbvopenedge, 167 false, // dbvoracle, 168 true, // dbvpostgresql, 169 false,// dbvpresto 170 false, // dbvredshift, 171 false, // dbvsnowflake, 172 false, // dbvsoql, 173 false, // dbvsparksql, 174 false, // dbvsybase, 175 false, // dbvteradata, 176 false, // dbvtrino 177 false, // dbvvertica, 178 false, // dbvdatabricks, 179 false, // dbvgaussdb, 180 }; 181 182 public static boolean[] defaultCollationCaseSensitive = { 183 false, // dbvaccess, 184 false, // dbvansi, 185 false,// dbvathena 186 false, // dbvazuresql, 187 false, // dbvbigquery, 188 false, // dbvcouchbase, 189 false, // dbvdax, 190 false, // dbvdb2, 191 false, // dbvexasol, 192 false, // dbvfirebird, 193 false, // dbvgeneric, 194 false, // dbvgreenplum, 195 false, // dbvhana, 196 false, // dbvhive, 197 false, // dbvimpala, 198 false, // dbvinformix, 199 false, // dbvmdx, 200 false, // dbvmysql, 201 false, // dbvmssql, 202 false, // dbvnetezza, 203 false, // dbvodbc, 204 false, // dbvopenedge, 205 false, // dbvoracle, 206 false, // dbvpostgresql, 207 false, // dbvpresto 208 false, // dbvredshift, 209 false, // dbvsnowflake, 210 false, // dbvsoql, 211 false, // dbvsparksql, 212 false, // dbvsybase, 213 false, // dbvteradata, 214 false, // dbvtrino 215 false, // dbvvertica, 216 false, // dbvdatabricks, 217 false, // dbvgaussdb, 218 }; 219 220 // public static boolean[] isAliasReferenceForbidden = { 221 // false, // dbvaccess - MODIFIED: set to false (original value: false) 222 // false, // dbvansi - MODIFIED: set to false (original value: false) 223 // false, // dbvathena - MODIFIED: set to false (original value: false) 224 // false, // dbvazuresql - MODIFIED: set to false (original value: true) 225 // false, // dbvbigquery - MODIFIED: set to false (original value: true) 226 // false, // dbvcouchbase - MODIFIED: set to false (original value: false) 227 // false, // dbvdax - MODIFIED: set to false (original value: false) 228 // false, // dbvdb2 - MODIFIED: set to false (original value: true) 229 // false, // dbvexasol - MODIFIED: set to false (original value: true) 230 // false, // dbvfirebird - MODIFIED: set to false (original value: true) 231 // false, // dbvgeneric - MODIFIED: set to false (original value: false) 232 // false, // dbvgreenplum - MODIFIED: set to false (original value: true) 233 // false, // dbvhana - MODIFIED: set to false (original value: true) 234 // false, // dbvhive - keeping as true per requirement 235 // false, // dbvimpala - MODIFIED: set to false (original value: true) 236 // false, // dbvinformix - MODIFIED: set to false (original value: true) 237 // false, // dbvmdx - MODIFIED: set to false (original value: false) 238 // false, // dbvmysql - MODIFIED: set to false (original value: true) 239 // false, // dbvmssql - MODIFIED: set to false (original value: true) 240 // false, // dbvnetezza - MODIFIED: set to false (original value: true) 241 // false, // dbvodbc - MODIFIED: set to false (original value: false) 242 // false, // dbvopenedge - MODIFIED: set to false (original value: false) 243 // false, // dbvoracle - MODIFIED: set to false (original value: true) 244 // false, // dbvpostgresql - MODIFIED: set to false (original value: true) 245 // false, // dbvpresto - MODIFIED: set to false (original value: true) 246 // false, // dbvredshift - MODIFIED: set to false (original value: true) 247 // false, // dbvsnowflake - MODIFIED: set to false (original value: true) 248 // false, // dbvsoql - MODIFIED: set to false (original value: false) 249 // false, // dbvsparksql - keeping as true per requirement 250 // false, // dbvsybase - MODIFIED: set to false (original value: true) 251 // false, // dbvteradata - MODIFIED: set to false (original value: true) 252 // false, // dbvtrino - MODIFIED: set to false (original value: true) 253 // false, // dbvvertica - MODIFIED: set to false (original value: true) 254 // false, // dbvdatabricks - MODIFIED: set to false (original value: true) 255 // false, // dbvgaussdb - MODIFIED: set to false (original value: true) 256 // }; 257 258 public static boolean[] isAliasReferenceForbidden = { 259 false, // dbvaccess - correct 260 false, // dbvansi - correct 261 false, // dbvathena - correct 262 true, // dbvazuresql - correct 263 true, // dbvbigquery - correct 264 false, // dbvcouchbase - correct 265 false, // dbvdax - correct 266 true, // dbvdb2 - correct 267 false, // dbvexasol - CHANGE to true (Exasol disallows forward references) 268 false, // dbvfirebird - CHANGE to true (Firebird follows standard SQL behavior) 269 false, // dbvgeneric - correct 270 true, // dbvgreenplum - correct 271 false, // dbvhana - CHANGE to true (SAP HANA follows standard SQL behavior) 272 true, // dbvhive - correct 273 false, // dbvimpala - CHANGE to true (Impala follows Hive's behavior) 274 true, // dbvinformix - correct 275 false, // dbvmdx - correct 276 true, // dbvmysql - correct 277 true, // dbvmssql - correct 278 true, // dbvnetezza - correct 279 false, // dbvodbc - correct 280 false, // dbvopenedge - correct 281 true, // dbvoracle - correct 282 true, // dbvpostgresql - correct 283 true, // dbvpresto - correct 284 true, // dbvredshift - correct 285 false, // dbvsnowflake - CHANGE to true (Snowflake disallows column alias references) 286 false, // dbvsoql - correct 287 true, // dbvsparksql - correct 288 true, // dbvsybase - correct 289 false, // dbvteradata - CHANGE to true (Teradata follows standard SQL behavior) 290 true, // dbvtrino - correct 291 false, // dbvvertica - CHANGE to true (Vertica disallows forward references) 292 true, // dbvdatabricks - correct 293 true, // dbvgaussdb - correct 294 }; 295 296 public static String getStmtSeparatorChar(EDbVendor dbVendor){ 297 String ret = ";"; 298 switch (dbVendor){ 299 case dbvoracle: 300 case dbvteradata: 301 case dbvpostgresql: 302 case dbvredshift: 303 case dbvgreenplum: 304 ret = "/"; 305 break; 306 case dbvdb2: 307 ret = "@"; 308 break; 309 case dbvmysql: 310 ret = "$"; 311 break; 312 default: 313 break; 314 } 315 return ret; 316 } 317 318 319 /** 320 * Whether this database support schema or not? 321 * 322 * @param dbVendor 323 * @return 324 */ 325 public static boolean supportSchema(EDbVendor dbVendor){ 326 return (!((dbVendor == EDbVendor.dbvmysql) 327 ||(dbVendor == EDbVendor.dbvteradata)||(dbVendor == EDbVendor.dbvhive)||(dbVendor == EDbVendor.dbvimpala) 328 )); 329 } 330 331 /** 332 * Whether this database support catalog or not? 333 * 334 * @param dbVendor 335 * @return 336 */ 337 public static boolean supportCatalog(EDbVendor dbVendor) { 338// return (!((dbVendor == EDbVendor.dbvoracle) 339// )); 340 return true; 341 } 342 343 /** 344 * used to delimit a database identifier, such as [ used in SQL Server, ` used in MySQL 345 * 346 * @param dbVendor 347 * @return 348 */ 349 public static String delimitedChar(EDbVendor dbVendor){ 350 String ret = "\""; 351 switch (dbVendor){ 352 case dbvmssql: 353 case dbvazuresql: 354 ret = "["; 355 break; 356 case dbvathena: 357 case dbvmysql: 358 case dbvbigquery: 359 case dbvcouchbase: 360 case dbvhive: 361 case dbvimpala: 362 case dbvdatabricks: 363 ret = "`"; 364 break; 365 case dbvdax: 366 ret = "'"; 367 break; 368 default: 369 break; 370 } 371 return ret; 372 } 373 374 public static boolean isDelimitedIdentifier(EDbVendor dbVendor, String identifier){ 375 boolean ret = false; 376 switch (dbVendor){ 377 case dbvmssql: 378 case dbvazuresql: 379 ret = identifier.startsWith("[")||identifier.startsWith("\"")||identifier.startsWith("'"); 380 break; 381 case dbvmysql: 382 case dbvbigquery: 383 case dbvcouchbase: 384 case dbvhive: 385 case dbvimpala: 386 ret = identifier.startsWith("`"); 387 break; 388 case dbvdax: 389 ret = identifier.startsWith("'"); 390 break; 391 default: 392 ret = identifier.startsWith("\""); 393 break; 394 } 395 return ret; 396 } 397 398 public static boolean endsWithDelimitedIdentifier(EDbVendor dbVendor, String identifier){ 399 boolean ret = false; 400 switch (dbVendor){ 401 case dbvmssql: 402 case dbvazuresql: 403 ret = identifier.endsWith("]")||identifier.endsWith("\""); 404 break; 405 case dbvmysql: 406 case dbvbigquery: 407 case dbvcouchbase: 408 case dbvhive: 409 case dbvimpala: 410 ret = identifier.endsWith("`"); 411 break; 412 case dbvdax: 413 ret = identifier.endsWith("'"); 414 break; 415 default: 416 ret = identifier.endsWith("\""); 417 break; 418 } 419 return ret; 420 } 421 422 public boolean isDelimitedIdentifier(String identifier){ 423 return TSQLEnv.isDelimitedIdentifier(this.getDBVendor(),identifier); 424 } 425 426 427 public boolean compareColumn(String ident1, String ident2){ 428 return compareIdentifier(this.getDBVendor(),ESQLDataObjectType.dotColumn,ident1,ident2); 429 } 430 431 public boolean compareTable(String ident1, String ident2){ 432 return compareIdentifier(this.getDBVendor(),ESQLDataObjectType.dotTable,ident1,ident2); 433 } 434 435 public boolean compareIdentifier(ESQLDataObjectType objectType, String ident1, String ident2){ 436 switch (objectType){ 437 case dotTable: 438 return compareTable(ident1,ident2); 439 case dotColumn: 440 return compareColumn(ident1,ident2); 441 default: 442 return compareIdentifier(this.getDBVendor(),objectType,ident1,ident2); 443 } 444 } 445 446 public static boolean compareColumn( EDbVendor dbVendor, TObjectName sourceColumn, TObjectName targetColumn){ 447 return compareQualifiedColumn(dbVendor,sourceColumn.getColumnNameOnly(),targetColumn.getColumnNameOnly(), 448 sourceColumn.getTableString(),targetColumn.getTableString(), 449 sourceColumn.getSchemaString(),targetColumn.getSchemaString(), 450 sourceColumn.getDatabaseString(),targetColumn.getDatabaseString()); 451 } 452 453 public static boolean compareTable( EDbVendor dbVendor, TObjectName sourceTable, TObjectName targetTable){ 454 return compareQualifiedTable(dbVendor, 455 sourceTable.getTableString(),targetTable.getTableString(), 456 sourceTable.getSchemaString(),targetTable.getSchemaString(), 457 sourceTable.getDatabaseString(),targetTable.getDatabaseString() 458 ); 459 } 460 461 public static boolean compareQualifiedTable( EDbVendor dbVendor, String sourceTable, String targetTable, String sourceSchema, String targetSchema, String sourceDatabase, String targetDatabase){ 462 boolean ret = compareIdentifier(dbVendor,dotTable,sourceTable,targetTable); 463 if (!ret) return ret; 464 465 // compare schema 466 ret = compareIdentifier(dbVendor,dotSchema,sourceSchema,targetSchema); 467 if (!ret) return ret; 468 469 // compare database 470 return compareIdentifier(dbVendor,dotCatalog,sourceDatabase,targetDatabase); 471 } 472 473 public static boolean compareQualifiedColumn( EDbVendor dbVendor, String sourceColumn, String targetColumn, String sourceTable, String targetTable, String sourceSchema, String targetSchema, String sourceDatabase, String targetDatabase){ 474 475 boolean ret = compareIdentifier(dbVendor,dotColumn,sourceColumn,targetColumn); 476 if (!ret) return ret; 477 478 return compareQualifiedTable(dbVendor,sourceTable,targetTable,sourceSchema,targetSchema,sourceDatabase,targetDatabase); 479 } 480 481 public static boolean compareIdentifier( EDbVendor dbVendor, ESQLDataObjectType objectType, TObjectName source, TObjectName target){ 482 return compareIdentifier(dbVendor,objectType,source.toString(),target.toString()); 483 } 484 485 public static boolean compareIdentifier( EDbVendor dbVendor, ESQLDataObjectType objectType, String ident1, String ident2){ 486 ident1 = normalizeIdentifier(dbVendor,objectType,ident1); 487 ident2 = normalizeIdentifier(dbVendor,objectType,ident2); 488 boolean collationSensitive = false; 489 switch (objectType){ 490 case dotCatalog: 491 case dotSchema: 492 collationSensitive = catalogCollationCaseSensitive[dbVendor.ordinal()]; 493 break; 494 case dotOraclePackage: 495 case dotProcedure: 496 case dotTrigger: 497 case dotTable: 498 collationSensitive = tableCollationCaseSensitive[dbVendor.ordinal()]; 499 break; 500 case dotFunction: 501 collationSensitive = functionCollationCaseSensitive[dbVendor.ordinal()]; 502 break; 503 case dotColumn: 504 collationSensitive = columnCollationCaseSensitive[dbVendor.ordinal()]; 505 break; 506 case dotDblink: // oracle dblink 507 collationSensitive = false; 508 break; 509 default: 510 collationSensitive = defaultCollationCaseSensitive[dbVendor.ordinal()]; 511 break; 512 } 513 514 if (collationSensitive) { 515 return ident1.equals(ident2); 516 } else { 517 return ident1.equalsIgnoreCase(ident2); 518 } 519 } 520 521 /** 522 * 1. remove delimited char if it's delimited/quoted identifier 523 * 2. change the case of the name in the same way as it saved to the information_schema 524 * 525 * @param dbVendor 526 * @param sqlDataObjectType 527 * @param identifier 528 * @return 529 */ 530 public static String normalizeIdentifier(EDbVendor dbVendor, ESQLDataObjectType sqlDataObjectType, String identifier){ 531 String ret; 532 if (identifier == null) return identifier; 533 if (identifier.length() == 0) return identifier; 534 if (TSQLEnv.isDelimitedIdentifier(dbVendor,identifier)){ 535 ret = TBaseType.getTextWithoutQuoted(identifier); 536 }else { 537 switch (dbVendor){ 538 case dbvdb2: 539 case dbvoracle: 540 ret = identifier.toUpperCase(); 541 break; 542 case dbvpostgresql: 543 case dbvgreenplum: 544 case dbvredshift: 545 ret = identifier.toLowerCase(); 546 break; 547 default: 548 ret = identifier; 549 break; 550 } 551 } 552 return ret; 553 } 554 555 /** 556 * 比较 一个数据库对象名是否等于或者属于另一个对象 557 * 等于 就是完全相等（根据不同数据库的比较规则） 558 * 属于 表示如下情况： 559 * 1. column1 -> 属于 -> table1.column1 560 * 2. table1 -> 属于 -> db1.schema1.table1 561 * 3. `schema1.table1` -> 属于 -> `db1`.`schema1`.`table1` 562 * 4. `schema1.table1` -> 不属于 -> `db1`.`schema2`.`table1` 563 * 564 * @param sub 565 * @param whole 566 * @return 567 */ 568 public static boolean matchSubObjectNameToWhole(EDbVendor dbVendor, ESQLDataObjectType sqlDataObjectType,String sub, String whole){ 569 List<String> subParts = SQLUtil.parseNames(sub); 570 // `data.RETAIL_PROD_EXCEPTIONS_SOURCE` 没有被分开，需要去掉 `` 后再次拆分 571 if ((subParts.size() == 1)&&(sub.indexOf(".") != -1)){ 572 subParts = SQLUtil.parseNames(TSQLEnv.normalizeIdentifier(dbVendor,sqlDataObjectType,sub)); 573 } 574 575 List<String> wholeParts = SQLUtil.parseNames(whole); 576 if ((wholeParts.size() == 1)&&(whole.indexOf(".") != -1)){ 577 wholeParts = SQLUtil.parseNames(TSQLEnv.normalizeIdentifier(dbVendor,sqlDataObjectType,whole)); 578 } 579 580 581 if(subParts.size() >wholeParts.size()) return false; 582 int k=0; 583 for(String s:subParts){ 584 subParts.set(k,TBaseType.removePrefixOrSuffixQuoteChar(normalizeIdentifier(dbVendor,sqlDataObjectType,s))); 585 k++; 586 } 587 k=0; 588 for(String s:wholeParts){ 589 wholeParts.set(k, TBaseType.removePrefixOrSuffixQuoteChar(normalizeIdentifier(dbVendor,sqlDataObjectType,s))); 590 k++; 591 } 592 593 boolean ret = false; 594 int i= subParts.size() -1; 595 int j = wholeParts.size() -1; 596 while (i >= 0){ 597 if ( !subParts.get(i).toUpperCase().equals(wholeParts.get(j).toUpperCase())) break; 598 if (i == 0) ret = true; 599 i--; 600 j--; 601 } 602 603 return ret; 604 } 605 606 private boolean enableGetMetadataFromDDL = true; 607 608 public void setEnableGetMetadataFromDDL(boolean enableGetMetadataFromDDL) { 609 this.enableGetMetadataFromDDL = enableGetMetadataFromDDL; 610 } 611 612 /** 613 * If this option is enabled, SQLEnv will collect table/view/function/procedure metadata 614 * from the create table/create view/create function/create procedure statement during the parse of the SQL script. 615 * <br>A TSQLEnv object instance must be passed to {@link TGSqlParser} before parsing the SQL script. And this TSQLEnv 616 * instance can be passed to another {@link TGSqlParser} object with the collected database metadata. 617 * <br>Default value is true. 618 * 619 * @return 620 */ 621 public boolean isEnableGetMetadataFromDDL() { 622 return enableGetMetadataFromDDL; 623 } 624 625 /** 626 * create a SQL environment. 627 * 628 * @param dbVendor the database vendor 629 */ 630 public TSQLEnv(EDbVendor dbVendor){ 631 this.dbVendor = dbVendor; 632 } 633 634 private EDbVendor dbVendor; 635 636 /** 637 * the database vendor where this SQL environment is generated from. 638 * 639 * @return the database vendor 640 */ 641 public EDbVendor getDBVendor() { 642 return dbVendor; 643 } 644 645 /** 646 * This method must be override in the subclass to build a SQL environment with real metadata. 647 * <br>this usually done by querying the INFORMATION_SCHEMA 648 */ 649 public abstract void initSQLEnv(); 650 651 /** 652 * a list of catalog/database in this SQL environment. 653 * 654 * @return a list of catalog/database 655 */ 656 public List<TSQLCatalog> getCatalogList() { 657 return catalogList; 658 } 659 660 private String serverName = null; // SQL Server 661 662 private List<TSQLCatalog> catalogList = new ArrayList<TSQLCatalog>( ); 663 664 /** 665 * add a catalog to the SQL environment, called internally. 666 * 667 * @param sqlCatalog catalog 668 * @return return false if a catalog with the same name already exists. 669 */ 670 protected boolean doAddCatalog(TSQLCatalog sqlCatalog){ 671 boolean isFound = false; 672 for(TSQLCatalog c : catalogList){ 673 if ( sqlCatalog.getName().compareTo(c.getName()) == 0){ 674 isFound = true; 675 break; 676 } 677 } 678 if (!isFound){ 679 catalogList.add(sqlCatalog); 680 } 681 return !isFound; 682 } 683 684 private HashMap<String, TSQLSchemaObject> schemaObjectList = new HashMap<String, TSQLSchemaObject>( ); 685 686 /** 687 * put a schema object into the hashmap which can be used to find a schema object in a more efficient way. 688 * 689 * @param schemaObjectName name of schema object 690 * @param schemaObject instance of a schema object 691 * @return always return true. 692 */ 693 protected boolean putSchemaObject(String schemaObjectName, TSQLSchemaObject schemaObject){ 694 String newSchemaName = schemaObjectName; 695 if (schemaObject.getDataObjectType() == dotTable){ 696 if (!tableCollationCaseSensitive[this.getDBVendor().ordinal()]){ 697 newSchemaName = newSchemaName.toUpperCase(); 698 } 699 }else { 700 if (!defaultCollationCaseSensitive[this.getDBVendor().ordinal()]){ 701 newSchemaName = newSchemaName.toUpperCase(); 702 } 703 } 704 705 if (schemaObject.getDataObjectType() == dotFunction){ 706 newSchemaName = newSchemaName+"$function"; 707 }else if (schemaObject.getDataObjectType() == dotProcedure){ 708 newSchemaName = newSchemaName+"$procedure"; 709 } 710 711 schemaObjectList.put(newSchemaName,schemaObject); 712 return true; 713 } 714 715 /** 716 * add a table 717 * 718 * @param qualifiedTableName, must be in syntax like: catalog.schema.table 719 * @param sqlTable, table instance 720 */ 721// public void addSQLTable(String qualifiedTableName, TSQLTable sqlTable){ 722// schemaObjectList.put(qualifiedTableName,sqlTable); 723// } 724// 725// public void addRoutine(String qualifiedRoutineName, TSQLRoutine sqlRoutine){ 726// schemaObjectList.put(qualifiedRoutineName,sqlRoutine); 727// } 728 729 private String defaultCatalogName = null; 730 private String defaultSchemaName = null; 731 private String defaultServerName = null; 732 733 protected TSQLSchemaObject doSearchSchemaObject(String catalog, String schema, String table, ESQLDataObjectType objectType){ 734 TSQLSchemaObject result = null; 735 736 String normalizedCurrentCatalogName = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotCatalog, defaultCatalogName); 737 String normalizedCurrentSchemaName = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotSchema, defaultSchemaName); 738 739 if ((catalog.length()>0)&&(schema.length()>0)){ // catalog.schema.table 740 result = doSearchSchemaObject(catalog+"."+schema+"."+table,objectType); 741 }else if (schema.length()>0){ //.schema.table 742 if (defaultCatalogName != null){ 743 result = doSearchSchemaObject(normalizedCurrentCatalogName+"."+schema+"."+table,objectType); 744 }else{ 745 for(TSQLCatalog c : catalogList){ 746 result = doSearchSchemaObject(c.name+"."+schema+"."+table,objectType); 747 if (result != null) break; 748 } 749 } 750 }else if (catalog.length()>0){ // catalog..table 751 if (defaultSchemaName != null){ 752 result = doSearchSchemaObject(catalog+"."+normalizedCurrentSchemaName+"."+table,objectType); 753 }else{ 754 for(TSQLCatalog c : catalogList){ 755 if ( c.compareTo(catalog) != 0) continue; 756 for(TSQLSchema s: c.getSchemaList()){ 757 result = doSearchSchemaObject(s.getQualifiedName()+"."+table,objectType); 758 if (result != null) break; 759 } 760 if (result != null) break; 761 } 762 } 763 }else{ // ..table, search under the current default database and schema. 764 // The current default database and schema can be set by the user. 765 // if current default database and schema is not set, then search in all databases and schemas 766 for(TSQLCatalog c : catalogList){ 767 if ((defaultCatalogName != null) && ( c.compareTo(defaultCatalogName) != 0)) continue; 768 for(TSQLSchema s: c.getSchemaList()){ 769 if ((defaultSchemaName != null)&&(s.compareTo(defaultSchemaName) != 0)) continue; 770 result = doSearchSchemaObject(s.getQualifiedName()+"."+table,objectType); 771 if (result != null) break; 772 } 773 if (result != null) break; 774 } 775 } 776 777 return result; 778 } 779 780 protected TSQLSchemaObject searchSchemaObject(TObjectName qualifiedName, ESQLDataObjectType objectType){ 781 782 String catalog = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotCatalog, qualifiedName.getDatabaseString()); 783 String schema = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotSchema, qualifiedName.getSchemaString()); 784 String table = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotTable, qualifiedName.getTableString()); 785 786 schema = (schema == null)?getDefaultSchemaName():schema; 787 schema = (schema == null)?"":schema; 788 789 catalog = (catalog == null)?getDefaultCatalogName():catalog; 790 catalog = (catalog == null)?"":catalog; 791 792 return doSearchSchemaObject(catalog,schema,table,objectType); 793 } 794 795 public TSQLSchemaObject searchSchemaObject(String qualifiedName, ESQLDataObjectType objectType){ 796 List<String> parts = SQLUtil.parseNames(qualifiedName); 797 if (parts.size() < 3) { 798 return null; 799 } 800 801 boolean supportCatalog = TSQLEnv.supportCatalog(dbVendor); 802 boolean supportSchema = TSQLEnv.supportSchema(dbVendor); 803 804 String catalog = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotCatalog, parts.get(0)); 805 String schema = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotSchema, parts.get(1)); 806 String table = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotTable, SQLUtil.mergeSegments(parts, 2)); 807 TSQLSchemaObject object = doSearchSchemaObject(catalog,schema,table,objectType); 808 809 //如果不是同时支持database和schema，且没有找到object，交换database和schema顺序查找 810 if (!(supportCatalog && supportSchema) && object == null) { 811 catalog = TSQLObject.normalizeIdentifier(this, ESQLDataObjectType.dotCatalog, parts.get(1)); 812 schema = TSQLObject.normalizeIdentifier(this, ESQLDataObjectType.dotSchema, parts.get(0)); 813 object = doSearchSchemaObject(catalog, schema, table, objectType); 814 } 815 816 return object; 817 818// String normalizedCurrentCatalogName = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotCatalog, defaultCatalogName); 819// String normalizedCurrentSchemaName = TSQLObject.normalizeIdentifier(this,ESQLDataObjectType.dotSchema, defaultSchemaName); 820// 821// if ((catalog.length()>0)&&(schema.length()>0)){ // catalog.schema.table 822// result = doSearchSchemaObject(catalog+"."+schema+"."+table,objectType); 823// }else if (schema.length()>0){ //.schema.table 824// if (defaultCatalogName != null){ 825// result = doSearchSchemaObject(normalizedCurrentCatalogName+"."+schema+"."+table,objectType); 826// }else{ 827// for(TSQLCatalog c : catalogList){ 828// result = doSearchSchemaObject(c.name+"."+schema+"."+table,objectType); 829// if (result != null) break; 830// } 831// } 832// }else if (catalog.length()>0){ // catalog..table 833// if (defaultSchemaName != null){ 834// result = doSearchSchemaObject(catalog+"."+normalizedCurrentSchemaName+"."+table,objectType); 835// }else{ 836// for(TSQLCatalog c : catalogList){ 837// if ( c.compareTo(catalog) != 0) continue; 838// for(TSQLSchema s: c.getSchemaList()){ 839// result = doSearchSchemaObject(s.getQualifiedName()+"."+table,objectType); 840// if (result != null) break; 841// } 842// if (result != null) break; 843// } 844// } 845// }else{ // ..table 846// for(TSQLCatalog c : catalogList){ 847// if ((defaultCatalogName != null) && ( c.compareTo(defaultCatalogName) != 0)) continue; 848// for(TSQLSchema s: c.getSchemaList()){ 849// if ((defaultSchemaName != null)&&(s.compareTo(defaultSchemaName) != 0)) continue; 850// result = doSearchSchemaObject(s.getQualifiedName()+"."+table,objectType); 851// if (result != null) break; 852// } 853// if (result != null) break; 854// } 855// } 856// 857// return result; 858 859 } 860 /** 861 * find a table in the SQL environment by using a qualified table name: catalogName.schemaName.tableName 862 * 863 * @param qualifiedTablename, can be catalog.schema.table, 864 * or .schema.table, use currentCatalogName or iterate all catalogs 865 * or catalog..table, use current schema name or iterate all schema under catalog 866 * or ..table, use currentCatalogName or iterate all catalogs, use current schema 867 * or iterate all schema 868 * @return a table 869 */ 870 public TSQLTable searchTable(String qualifiedTablename){ 871 TSQLSchemaObject result = searchSchemaObject(qualifiedTablename,dotTable); 872 873 if (result instanceof TSQLTable){ 874 return (TSQLTable)result; 875 }else return null; 876 877 } 878 879 public TSQLTable searchTable(TObjectName tableName){ 880 if ((tableName.getSchemaToken() == null) && (tableName.getDatabaseToken() == null)) return searchTable(".."+tableName.getTableString()); 881 if (tableName.getDatabaseToken() == null) return searchTable("."+tableName.getSchemaString()+"."+tableName.getTableString()); 882 if (tableName.getSchemaToken() == null) return searchTable(tableName.getDatabaseString()+".."+tableName.getTableString()); 883 return searchTable(tableName.toString()); 884 } 885 /** 886 * called by {@link #searchTable(String)} method internally. 887 * 888 * @param qualifiedName table name 889 * @return return a table instance if found, otherwise, return null 890 */ 891// TSQLTable doSearchTable(String qualifiedTablename){ 892// TSQLTable result = null; 893// TSQLSchemaObject schemaObject = schemaObjectList.get(qualifiedTablename); 894// 895// if (schemaObject instanceof TSQLTable){ 896// result = (TSQLTable)schemaObject; 897// } 898// return result; 899// } 900 901 private TSQLSchemaObject doSearchSchemaObject( String qualifiedName, ESQLDataObjectType objectType){ 902 String newSchemaName = qualifiedName; 903 if (objectType == dotTable){ 904 if (!tableCollationCaseSensitive[this.getDBVendor().ordinal()]){ 905 newSchemaName = newSchemaName.toUpperCase(); 906 } 907 }else { 908 if (!defaultCollationCaseSensitive[this.getDBVendor().ordinal()]){ 909 newSchemaName = newSchemaName.toUpperCase(); 910 } 911 } 912 913 if (objectType == dotFunction){ 914 newSchemaName = newSchemaName+"$function"; 915 }else if (objectType == dotProcedure){ 916 newSchemaName = newSchemaName+"$procedure"; 917 } 918 return schemaObjectList.get(newSchemaName); 919 } 920 921 /** 922 * the current active catalog/database in the SQL environment. 923 * If search a table in syntax like this: .schemaName.tableName and this current catalog name is not null, 924 * then, search the table in this syntax: currentCatalogName.schemaName.tableName. 925 * If the current catalog name is null, search all catalogs in the catalog list. 926 * 927 * @return the name of the current active catalog/database 928 */ 929 public String getDefaultCatalogName() { 930 return defaultCatalogName; 931 } 932 933 /** 934 * the current schema name in the SQL environment. 935 * <br> 936 * If search a table in syntax like this: catalogname..tableName and this current schema name is not null, 937 * then, search the table in this syntax: catalogName.currentSchemaName.tableName. 938 * If the current schema name is null, search all schemas under catalogName 939 * 940 * @return the default schema name 941 */ 942 public String getDefaultSchemaName() { 943 return defaultSchemaName; 944 } 945 946 public void setDefaultCatalogName(String defaultCatalogName) { 947 this.defaultCatalogName = defaultCatalogName; 948 } 949 950 public void setDefaultSchemaName(String defaultSchemaName) { 951 this.defaultSchemaName = defaultSchemaName; 952 } 953 954 public String getDefaultServerName() { 955 return defaultServerName; 956 } 957 958 public void setDefaultServerName(String defaultServerName) { 959 this.defaultServerName = defaultServerName; 960 } 961 962 963 964 /** 965 * create a new catalog/database in the SQL environment. 966 * 967 * @param catalogName catalog name 968 * @return instance of the created catalog 969 */ 970 public TSQLCatalog createSQLCatalog(String catalogName){ 971 return getSQLCatalog(catalogName,true); 972 } 973 974 /** 975 * get a catalog from the SQL environment if already exists, otherwise, create a new catalog. 976 * 977 * @param catalogName catalog name 978 * @param createIfNotExist if this value is true, then create a new catalog if it's not exists 979 * @return a catalog instance 980 */ 981 public TSQLCatalog getSQLCatalog(String catalogName, boolean createIfNotExist){ 982 TSQLCatalog result = searchCatalog(catalogName); 983 984 if ((createIfNotExist)&&(result == null)){ 985 result = new TSQLCatalog(this,catalogName); 986 } 987 return result; 988 } 989 990 /** 991 * search catalog in the catalog list, return null if not found. 992 * 993 * @param catalogName catalog name 994 * @return null if not found. 995 */ 996 public TSQLCatalog searchCatalog(String catalogName){ 997 TSQLCatalog result = null; 998 for(TSQLCatalog c : catalogList){ 999 if (c.compareTo(catalogName)==0){ 1000 result = c; 1001 break; 1002 } 1003 } 1004 return result; 1005 } 1006 1007 /** 1008 * create a new schema and add to the catalog 1009 * 1010 * @param qualifiedSchemaName must be a qualified name like: catalog.schema. Otherwise, a null exception will be raised. 1011 * @return a new schema instance 1012 */ 1013 public TSQLSchema createSQLSchema(String qualifiedSchemaName){ 1014 return getSQLSchema(qualifiedSchemaName,true); 1015 } 1016 1017 /** 1018 * get a schema from the specified catalog, if not exists, create a new schema in the catalog. 1019 * 1020 * @param qualifiedSchemaName must be a qualified name like: catalog.schema. Otherwise, a null exception will be raised. 1021 * @param createIfNotExist if this value is true, then create a new schema if it's not exists 1022 * @return a schema instance 1023 */ 1024 public TSQLSchema getSQLSchema(String qualifiedSchemaName, boolean createIfNotExist){ 1025 TSQLSchema result = null; 1026 String[] parts = SQLUtil.parseNames(qualifiedSchemaName).toArray(new String[0]); 1027 String catalogName = parts[0]; 1028 String schemaName = parts[1]; 1029 TSQLCatalog catalog = getSQLCatalog(catalogName,createIfNotExist); 1030 return catalog.getSchema(schemaName,createIfNotExist); 1031 } 1032 1033 1034 /** 1035 * 1036 * @param qualifiedTablename 需要完整的tablename，例如 catalog.schema.table, 如果仅传入 table name， 1037 * 需要利用 default catalog, default schema 拼接完整的名称： catalog.schema.table 1038 * @param columnNameOnly 1039 * @return 1040 */ 1041 public ArrayList<String> getColumnsInTable(String qualifiedTablename,boolean columnNameOnly){ 1042 1043 //TSQLTable tsqlTable = searchTable(getAFullQualifiedSchemaObjectName(qualifiedTablename)); 1044 TSQLTable tsqlTable = searchTable(qualifiedTablename); 1045 if (tsqlTable == null) return null; 1046 //return tsqlTable.searchColumn(columnName); 1047 return tsqlTable.getColumns(columnNameOnly); 1048 } 1049 1050 public boolean columnInTable(String qualifiedTablename, String columnName){ 1051 TSQLTable tsqlTable = searchTable(qualifiedTablename); 1052 if (tsqlTable == null) return false; 1053 return tsqlTable.searchColumn(columnName); 1054 } 1055 1056 public TSQLColumn getColumnInTable(String qualifiedTablename, String columnName){ 1057 StringBuilder builder = new StringBuilder(); 1058 String db = (getDatabaseName(qualifiedTablename) == null)?getDefaultCatalogName():getDatabaseName(qualifiedTablename); 1059 if((db == null) || (db.length() == 0)) { 1060 builder.append(DEFAULT_DB_NAME).append("."); 1061 } 1062 1063 String schema = (getSchemaName(qualifiedTablename) == null)?getDefaultSchemaName():getSchemaName(qualifiedTablename); 1064 if ((schema == null)||(schema.length() == 0)) { 1065 builder.append(DEFAULT_SCHEMA_NAME).append("."); 1066 } 1067 1068 builder.append(qualifiedTablename); 1069 1070 TSQLTable tsqlTable = searchTable(builder.toString()); 1071 if (tsqlTable == null) return null; 1072 return tsqlTable.getColumn(columnName); 1073 } 1074 1075// String[] getCatalogSchemaNameWithDefault(String qualifiedObjectName){ 1076// String[] catalogSchemaName = new String[2]; 1077// 1078// if (qualifiedObjectName.startsWith("`") && qualifiedObjectName.endsWith("`")){ 1079// qualifiedObjectName = qualifiedObjectName.substring(1,qualifiedObjectName.length()-1); 1080// } 1081// 1082// String db = (getDatabaseName(qualifiedObjectName) == null)?getDefaultCatalogName():getDatabaseName(qualifiedObjectName); 1083// if((db == null) || (db.length() == 0)) db = DEFAULT_DB_NAME; 1084// String schema = (getSchemaName(qualifiedObjectName) == null)?getDefaultSchemaName():getSchemaName(qualifiedObjectName); 1085// if ((schema == null)||(schema.length() == 0)) schema = DEFAULT_SCHEMA_NAME; 1086// 1087// catalogSchemaName[0] = db; 1088// catalogSchemaName[1] = schema; 1089// 1090// return catalogSchemaName; 1091// } 1092 public TSQLSchemaObject doAddSchemaObject(String qualifiedObjectName,ESQLDataObjectType objectType){ 1093 1094 if (qualifiedObjectName.startsWith("`") && qualifiedObjectName.endsWith("`")){ 1095 qualifiedObjectName = SQLUtil.trimColumnStringQuote(qualifiedObjectName); 1096 } 1097 1098 String db = (getDatabaseName(qualifiedObjectName) == null)?getDefaultCatalogName():getDatabaseName(qualifiedObjectName); 1099 if((db == null) || (db.length() == 0)) db = DEFAULT_DB_NAME; 1100 String schema = (getSchemaName(qualifiedObjectName) == null)?getDefaultSchemaName():getSchemaName(qualifiedObjectName); 1101 if ((schema == null)||(schema.length() == 0)) schema = DEFAULT_SCHEMA_NAME; 1102 1103 // getCatalogSchemaNameWithDefault 这个函数有问题，为导致测试用例失败 1104 // String[] catalogSchemaName = getCatalogSchemaNameWithDefault(qualifiedObjectName); 1105// String db = catalogSchemaName[0]; 1106// String schema = catalogSchemaName[1]; 1107 1108 TSQLCatalog sqlCatalog = createSQLCatalog(db); 1109 TSQLSchema sqlSchema = sqlCatalog.createSchema(schema); 1110 return sqlSchema.createSchemaObject(getObjectName(qualifiedObjectName),objectType); 1111 } 1112 1113 public TSQLSchemaObject doAddSchemaObject(TObjectName qualifiedObjectName,ESQLDataObjectType objectType){ 1114 String db = (getDatabaseName(qualifiedObjectName) == null)?getDefaultCatalogName():getDatabaseName(qualifiedObjectName); 1115 if((db == null) || (db.length() == 0)) db = DEFAULT_DB_NAME; 1116 String schema = (getSchemaName(qualifiedObjectName) == null)?getDefaultSchemaName():getSchemaName(qualifiedObjectName); 1117 if ((schema == null)||(schema.length() == 0)) schema = DEFAULT_SCHEMA_NAME; 1118 TSQLCatalog sqlCatalog = createSQLCatalog(db); 1119 TSQLSchema sqlSchema = sqlCatalog.createSchema(schema); 1120 return sqlSchema.createSchemaObject(getObjectName(qualifiedObjectName),objectType); 1121 } 1122 1123 /** 1124 * Add a function to SQLEnv, if a function with the same already exists, just return the existing one. 1125 * 1126 * @param qualifiedFunctionName 1127 * @return function 1128 */ 1129 public TSQLFunction addFunction(String qualifiedFunctionName, boolean fromDDL){ 1130 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1131 return (TSQLFunction)doAddSchemaObject(qualifiedFunctionName,ESQLDataObjectType.dotFunction); 1132 } 1133 1134 public TSQLFunction addFunction(TObjectName qualifiedFunctionName, boolean fromDDL){ 1135 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1136 return (TSQLFunction)doAddSchemaObject(qualifiedFunctionName,ESQLDataObjectType.dotFunction); 1137 } 1138 1139 public TSQLProcedure addOraclePackage(String qualifiedProcedureName, boolean fromDDL){ 1140 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1141 return (TSQLProcedure)doAddSchemaObject(qualifiedProcedureName, ESQLDataObjectType.dotOraclePackage); 1142 } 1143 1144 public TSQLProcedure addProcedure(String qualifiedProcedureName, boolean fromDDL){ 1145 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1146 return (TSQLProcedure)doAddSchemaObject(qualifiedProcedureName,ESQLDataObjectType.dotProcedure); 1147 } 1148 1149 public TSQLRoutine addSQLRoutine(String qualifiedProcedureName, boolean fromDDL, ESQLDataObjectType type){ 1150 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1151 return (TSQLRoutine)doAddSchemaObject(qualifiedProcedureName,type); 1152 } 1153 1154 public TSQLTrigger addTrigger(String qualifiedTriggerName, boolean fromDDL){ 1155 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1156 return (TSQLTrigger)doAddSchemaObject(qualifiedTriggerName,ESQLDataObjectType.dotTrigger); 1157 } 1158 1159 /** 1160 * Add a new table to the SQLEnv. If the table with the same name already exists in the SQLEnv, 1161 * the existing table will be returned. 1162 * <br>dbName.schemaName.tablename. If dbName is not specified, {@link #getDefaultCatalogName()} will be used to put this table in. 1163 * If schemaName is not specified, {@link #getDefaultSchemaName()} will be used to put this table in. 1164 * <br>If the specified database and schema are not already exists, a new database/schema will be generated automatically. 1165 * 1166 * @param qualifiedTableName qualified table name 1167 * @param fromDDL is this table generated from a create table statement 1168 * 1169 * @return SQL Table 1170 */ 1171 public TSQLTable addTable(String qualifiedTableName, boolean fromDDL){ 1172 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1173 return (TSQLTable)doAddSchemaObject(qualifiedTableName,ESQLDataObjectType.dotTable); 1174 } 1175 1176 public TSQLTable addView(String qualifiedViewName, boolean fromDDL){ 1177 if ((fromDDL)&&(!isEnableGetMetadataFromDDL())) return null; 1178 TSQLTable tsqlTable = (TSQLTable)doAddSchemaObject(qualifiedViewName,ESQLDataObjectType.dotTable); 1179 tsqlTable.setView(true); 1180 return tsqlTable; 1181 } 1182 1183 public static String getObjectName(TObjectName schemaObjectName){ 1184 if (schemaObjectName.getObjectString().length() == 0) return null; 1185 else 1186 return schemaObjectName.getObjectString(); 1187 } 1188 1189 public static String getObjectName(String schemaObjectName){ 1190 String[] names = schemaObjectName.split("[.]"); 1191 return names[names.length-1]; 1192 } 1193 1194 public static String getDatabaseName(TObjectName schemaObjectName){ 1195 if (schemaObjectName.getDatabaseString().length() == 0) 1196 return null; 1197 else 1198 return schemaObjectName.getDatabaseString(); 1199 } 1200 1201 public static String getDatabaseName(String schemaObjectName){ 1202 String[] names = schemaObjectName.split("[.]"); 1203 if (names.length == 3){ 1204 return names[0]; 1205 }else if (names.length == 4){ 1206 return names[1]; 1207 }else{ 1208 return null; 1209 } 1210 } 1211 1212 public static String getSchemaName(TObjectName schemaObjectName){ 1213 if (schemaObjectName.getSchemaString().length() == 0) return null; 1214 else 1215 return schemaObjectName.getSchemaString(); 1216 } 1217 1218 public static String getSchemaName(String schemaObjectName){ 1219 String[] names = schemaObjectName.split("[.]"); 1220 if (names.length == 2){ 1221 return names[0]; 1222 }else if (names.length == 3){ 1223 return names[1]; 1224 }else if (names.length == 4){ 1225 return names[2]; 1226 }else{ 1227 return null; 1228 } 1229 } 1230 1231 public TSQLFunction searchFunction(TObjectName qualifiedTablename) { 1232 TSQLSchemaObject result = searchSchemaObject(qualifiedTablename,dotFunction); 1233 1234 if (result instanceof TSQLFunction) { 1235 return (TSQLFunction) result; 1236 } else return null; 1237 } 1238 1239 public TSQLFunction searchFunction(String qualifiedTablename) { 1240 TSQLSchemaObject result = searchSchemaObject( getAFullQualifiedSchemaObjectName(qualifiedTablename),dotFunction); 1241 1242 if (result instanceof TSQLFunction) { 1243 return (TSQLFunction) result; 1244 } else return null; 1245 } 1246 1247 /** 1248 * If the input name is fully qualified with db and schema, just return it without any modification. 1249 * If the schema name is missed, use {@link #getDefaultSchemaName()} instead 1250 * If the database name is missed, use {@link #getDefaultCatalogName()} instead 1251 * 1252 * some sample result: 1253 * <br> 1254 * db.schema.table 1255 * .schema.table, {@link #getDefaultCatalogName()} is null 1256 * ..table, both {@link #getDefaultCatalogName()} and {@link #getDefaultSchemaName()} are null. 1257 * 1258 * @param schemaObjectName 1259 * @return 1260 */ 1261 public String getAFullQualifiedSchemaObjectName(String schemaObjectName){ 1262 if (schemaObjectName.split("[.]").length == 3) return schemaObjectName; 1263 String schema = (getDefaultSchemaName() == null)?"":getDefaultSchemaName(); 1264 String db = (getDefaultCatalogName() == null)?"":getDefaultCatalogName(); 1265 1266 String[] names = schemaObjectName.split("[.]"); 1267 if (names.length == 1){ // no prefixed schema, db 1268 return db+"."+schema+"."+schemaObjectName; 1269 }else if (names.length == 2){ // prefix with schema, no database 1270 return db+"."+names[0]+"."+names[1]; 1271 }else { 1272 return schemaObjectName; 1273 } 1274 } 1275 1276 public static final String DEFAULT_SERVER_NAME = "DEFAULT_SERVER"; 1277 public static final String DEFAULT_DB_NAME = "DEFAULT"; 1278 public static final String DEFAULT_SCHEMA_NAME = "DEFAULT"; 1279 1280 public int getNumberOfTables(){ 1281 int numOfTables = 0; 1282 for(int i=0;i<getCatalogList().size();i++){ 1283 TSQLCatalog tsqlCatalog = getCatalogList().get(i); 1284 for(TSQLSchema tsqlSchema: tsqlCatalog.getSchemaList()){ 1285 for(TSQLSchemaObject schemaObject: tsqlSchema.getSchemaObjectList()){ 1286 switch (schemaObject.getDataObjectType()){ 1287 case dotTable: 1288 numOfTables++; 1289 break; 1290 case dotProcedure: 1291 break; 1292 case dotFunction: 1293 break; 1294 case dotOraclePackage: 1295 break; 1296 } 1297 } //schema object list 1298 } // schema list 1299 } //catalog list 1300 return numOfTables; 1301 } 1302 1303 public String toString(){ 1304 String lcResult = ""; 1305 StringBuilder tables = new StringBuilder(); 1306 StringBuilder views = new StringBuilder(); 1307 StringBuilder functions = new StringBuilder(); 1308 StringBuilder oraclePackages = new StringBuilder(); 1309 StringBuilder procedures = new StringBuilder(); 1310 for(int i=0;i<getCatalogList().size();i++){ 1311 TSQLCatalog tsqlCatalog = getCatalogList().get(i); 1312 lcResult = lcResult+"\ndatabase:"+tsqlCatalog.getName(); 1313 for(TSQLSchema tsqlSchema: tsqlCatalog.getSchemaList()){ 1314 lcResult = lcResult+"\n\t"+"schema:"+tsqlSchema.getName(); 1315 for(TSQLSchemaObject schemaObject: tsqlSchema.getSchemaObjectList()){ 1316 switch (schemaObject.getDataObjectType()){ 1317 case dotTable: 1318 TSQLTable tsqlTable = (TSQLTable)schemaObject; 1319 if (tsqlTable.isView()){ 1320 views.append("\n\t\t\t"+schemaObject.getName()); 1321 for(TSQLColumn column: tsqlTable.getColumnList()){ 1322 views.append("\n\t\t\t\t"+column.getName()); 1323 1324 } 1325 }else{ 1326 tables.append("\n\t\t\t"+schemaObject.getName()); 1327 for(TSQLColumn column: tsqlTable.getColumnList()){ 1328 tables.append("\n\t\t\t\t"+column.getName()); 1329 if (column.getColumnDataType() != null){ 1330 tables.append(",\t"+column.getColumnDataType().toString()); 1331 TTypeName subType; 1332 switch (column.getColumnDataType().getDataType()){ 1333 case array_t: 1334 subType = column.getColumnDataType().getTypeOfList(); 1335 1336 if (subType.getDataType() == EDataType.struct_t){ 1337 1338 for(int k=0;k<subType.getColumnDefList().size();k++){ 1339 //System.out.println(subType.getColumnDefList().getColumn(k).getColumnName()); 1340 //System.out.println(subType.getColumnDefList().getColumn(k).getDatatype()); 1341 tables.append("\n\t\t\t\t\t\t"+ subType.getColumnDefList().getColumn(k).getColumnName().toString() 1342 +",\t"+ subType.getColumnDefList().getColumn(k).getDatatype().toString()); 1343 } 1344 } 1345 break; 1346 case struct_t: 1347 subType = column.getColumnDataType(); 1348 for(int k=0;k<subType.getColumnDefList().size();k++){ 1349 tables.append("\n\t\t\t\t\t\t"+ subType.getColumnDefList().getColumn(k).getColumnName().toString() 1350 +",\t"+ subType.getColumnDefList().getColumn(k).getDatatype().toString()); 1351 } 1352 break; 1353 } 1354 } 1355 } 1356 } 1357 1358 break; 1359 case dotProcedure: 1360 procedures.append("\n\t\t\t"+schemaObject.getName()); 1361 break; 1362 case dotFunction: 1363 functions.append("\n\t\t\t"+schemaObject.toString()); 1364 //TSQLFunction f = (TSQLFunction) schemaObject; 1365 1366 break; 1367 case dotOraclePackage: 1368 oraclePackages.append("\n\t\t\t"+schemaObject.getName()); 1369 break; 1370 } 1371 } //schema object list 1372 lcResult = lcResult+"\n\t\t"+"tables:"+tables.toString(); 1373 lcResult = lcResult+"\n\t\t"+"views:"+views.toString(); 1374 if(oraclePackages.length()>0) { 1375 lcResult = lcResult + "\n\t\t" + "oracle package:" + oraclePackages.toString(); 1376 } 1377 lcResult = lcResult+"\n\t\t"+"procedure:"+procedures.toString(); 1378 lcResult = lcResult+"\n\t\t"+"function:"+functions.toString(); 1379 tables.setLength(0); 1380 views.setLength(0); 1381 procedures.setLength(0); 1382 functions.setLength(0); 1383 } // schema list 1384 } //catalog list 1385 return lcResult; 1386 } 1387 1388 public String getProcedureParameterValue(String procedureName, int paramPos){ 1389 return ""; 1390 } 1391 public void getVariableValue(String variableName, String variableValue){} 1392 1393 private static boolean usedBySqlflow = false; 1394 1395 public static boolean isUsedBySqlflow() { 1396 return usedBySqlflow; 1397 } 1398}