Java API By Example, From Geeks To Geeks.

1 /* Copyright (c) 2001-2005, The HSQL Development Group 2  * All rights reserved. 3  * 4  * Redistribution and use in source and binary forms, with or without 5  * modification, are permitted provided that the following conditions are met: 6  * 7  * Redistributions of source code must retain the above copyright notice, this 8  * list of conditions and the following disclaimer. 9  * 10  * Redistributions in binary form must reproduce the above copyright notice, 11  * this list of conditions and the following disclaimer in the documentation 12  * and/or other materials provided with the distribution. 13  * 14  * Neither the name of the HSQL Development Group nor the names of its 15  * contributors may be used to endorse or promote products derived from this 16  * software without specific prior written permission. 17  * 18  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 19  * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 20  * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 21  * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG, 22  * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 23  * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 24  * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 25  * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 26  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 27  * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 28  * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 29  */ 30 31 32 package org.hsqldb; 33 34 import org.hsqldb.HsqlNameManager.HsqlName; 35 import org.hsqldb.lib.HashMap; 36 import org.hsqldb.lib.HashSet; 37 import org.hsqldb.lib.HsqlArrayList; 38 import org.hsqldb.lib.Iterator; 39 import org.hsqldb.lib.WrapperIterator; 40 import org.hsqldb.persist.HsqlProperties; 41 import org.hsqldb.store.ValuePool; 42 43 // fredt@users - 1.7.2 - structural modifications to allow inheritance 44 // boucherb@users - 1.7.2 - 20020225 45 // - factored out all reusable code into DIXXX support classes 46 // - completed Fred's work on allowing inheritance 47 // boucherb@users - 1.7.2 - 20020304 - bug fixes, refinements, better java docs 48 // fredt@users - 1.8.0 - updated to report latest enhancements and changes 49 50 /** 51  * Produces a collection of tables that form the system data dictionary. <p> 52  * 53  * Implementations use a group of arrays of equal size to store various 54  * attributes or cached instances of system tables.<p> 55  * 56  * Two fixed static lists of reserved table names are kept in String[] and 57  * HsqlName[] forms. These are shared by all implementations of 58  * DatabaseInformtion.<p> 59  * 60  * Each implementation keeps a lookup set of names for those tables whose 61  * contents are never cached (nonCachedTablesSet). <p> 62  * 63  * An instance of this class uses three lists named sysTablexxxx for caching 64  * system tables.<p> 65  * 66  * sysTableSessionDependent indicates which tables contain data that is 67  * dependent on the user rights of the User associatiod with the Session.<p> 68  * 69  * sysTableSessions contains the Session with whose rights each cached table 70  * was built.<p> 71  * 72  * sysTables contains the cached tables.<p> 73  * 74  * At the time of instantiation, which is part of the Database.open() method 75  * invocation, an empty table is created and placed in sysTables with calls to 76  * generateTable(int) for each name in sysTableNames. Some of these 77  * table entries may be null if an implementation does not produce them.<p> 78  * 79  * Calls to getSystemTable(String, Session) return a cached table if various 80  * caching rules are met (see below), or it will delete all rows of the table 81  * and rebuild the contents via generateTable(int).<p> 82  * 83  * generateTable(int) calls the appropriate single method for each table. 84  * These methods either build and return an empty table (if sysTables 85  * contains null for the table slot) or populate the table with up-to-date 86  * rows. <p> 87  * 88  * When the setDirty() call is made externally, the internal isDirty flag 89  * is set. This flag is used next time a call to 90  * getSystemTable(String, Session) is made. <p> 91  * 92  * Rules for caching are applied as follows: <p> 93  * 94  * When a call to getSystemTable(String, Session) is made, if the isDirty flag 95  * is true, then the contents of all cached tables are cleared and the 96  * sysTableUsers slot for all tables is set to null. This also has the 97  * effect of clearing the isDirty and isDirtyNextIdentity flags<p> 98  * 99  * if the isDirtyNextIdentity flag is true at this point, then the contents 100  * of all next identity value dependent cached tables are cleared and the 101  * sysTableUsers slot for these tables are set to null. Currently, 102  * the only member of this set is the SYSTEM_TABLES system table. 103  * 104  * If a table has non-cached contents, its contents are cleared and 105  * rebuilt. <p> 106  * 107  * For the rest of the tables, if the sysTableSessions slot is null or if the 108  * Session parameter is not the same as the Session object 109  * in that slot, the table contents are cleared and rebuilt. <p> 110  * 111  * (fredt@users) <p> 112  * @author boucherb@users 113  * @version 1.8.0 114  * @since 1.7.2 115  */ 116 class DatabaseInformationMain extends DatabaseInformation { 117 118     // HsqlName objects for the system tables 119 120     /** The HsqlNames of the system tables. */ 121     protected static final HsqlName[] sysTableHsqlNames; 122 123     /** Current user for each cached system table */ 124     protected final int[] sysTableSessions = new int[sysTableNames.length]; 125 126     /** true if the contents of a cached system table depends on the session */ 127     protected final boolean[] sysTableSessionDependent = 128         new boolean[sysTableNames.length]; 129 130     /** cache of system tables */ 131     protected final Table[] sysTables = new Table[sysTableNames.length]; 132 133     /** Set: { names of system tables that are not to be cached } */ 134     protected static final HashSet nonCachedTablesSet; 135 136     /** 137      * Map: simple <code>Column</code> name <code>String</code> object => 138      * <code>HsqlName</code> object. 139      */ 140     protected static final HashMap columnNameMap; 141 142     /** 143      * The <code>Session</code> object under consideration in the current 144      * executution context. 145      */ 146     protected Session session; 147 148     /** The table types HSQLDB supports. */ 149     protected static final String [] tableTypes = new String [] { 150         "GLOBAL TEMPORARY", "SYSTEM TABLE", "TABLE", "VIEW" 151     }; 152 153     /** Provides naming support. */ 154     protected DINameSpace ns; 155 156     static { 157         columnNameMap = new HashMap(); 158         nonCachedTablesSet = new HashSet(); 159         sysTableHsqlNames = new HsqlName[sysTableNames.length]; 160 161         for (int i = 0; i < sysTableNames.length; i++) { 162             sysTableHsqlNames[i] = 163                 HsqlNameManager.newHsqlSystemObjectName(sysTableNames[i]); 164             sysTableHsqlNames[i].schema = 165                 SchemaManager.INFORMATION_SCHEMA_HSQLNAME; 166         } 167 168         // build the set of non-cached tables 169 nonCachedTablesSet.add("SYSTEM_CACHEINFO"); 170         nonCachedTablesSet.add("SYSTEM_SESSIONINFO"); 171         nonCachedTablesSet.add("SYSTEM_SESSIONS"); 172         nonCachedTablesSet.add("SYSTEM_PROPERTIES"); 173         nonCachedTablesSet.add("SYSTEM_SEQUENCES"); 174     } 175 176     /** 177      * Constructs a table producer which provides system tables 178      * for the specified <code>Database</code> object. <p> 179      * 180      * <b>Note:</b> before 1.7.2 Alpha N, it was important to observe that 181      * by specifying an instance of this class or one of its descendents to 182      * handle system table production, the new set of builtin permissions 183      * and aliases would overwrite those of an existing database, meaning that 184      * metadata reporting might have been rendered less secure if the same 185      * database were then opened again using a lower numbered system table 186      * producer instance (i.e. one in a 1.7.1 or earlier distribution). 187      * As of 1.7.2 Alpha N, system-generated permissions and aliases are no 188      * longer recorded in the checkpoint script, obseleting this issue. 189      * Checkpointing of system-generated grants and aliases was removed 190      * because their existence is very close to a core requirment for correct 191      * operation and they are reintroduced to the system at each startup. 192      * In a future release, it may even be an exception condition to attempt 193      * to remove or alter system-generated grants and aliases, 194      * respectvely. <p> 195      * 196      * @param db the <code>Database</code> object for which this object 197      * produces system tables 198      * @throws HsqlException if a database access error occurs 199      */ 200     DatabaseInformationMain(Database db) throws HsqlException { 201 202         super(db); 203 204         init(); 205     } 206 207     /** 208      * Adds a <code>Column</code> object with the specified name, data type, 209      * data size and nullability to the specified <code>Table</code> 210      * object. <p> 211      * 212      * @param t the table to which to add the specified column 213      * @param name the name of the column 214      * @param type the data type of the column 215      * @param size the precision/length of the column 216      * @param nullable <code>true</code> if the column is to allow null values, 217      * else <code>false</code> 218      * @throws HsqlException if a problem occurs when adding the 219      * column (e.g. duplicate name) 220      */ 221     protected final void addColumn(Table t, String name, int type, int size, 222                                    boolean nullable) throws HsqlException { 223 224         HsqlName cn; 225         Column c; 226 227         cn = (HsqlName) columnNameMap.get(name); 228 229         if (cn == null) { 230             cn = database.nameManager.newHsqlName(name, false); 231 232             columnNameMap.put(name, cn); 233         } 234 235         c = new Column(cn, nullable, type, size, 0, false, null); 236 237         t.addColumn(c); 238     } 239 240     /** 241      * Adds a <code>Column</code> object with the specified name, data type 242      * and nullability to the specified <code>Table</code> object. <p> 243      * 244      * @param t the table to which to add the specified column 245      * @param name the name of the column 246      * @param type the data type of the column 247      * @param nullable <code>true</code> if the column is to allow null values, 248      * else <code>false</code> 249      * @throws HsqlException if a problem occurs when adding the 250      * column (e.g. duplicate name) 251      */ 252     protected final void addColumn(Table t, String name, int type, 253                                    boolean nullable) throws HsqlException { 254         addColumn(t, name, type, 0, nullable); 255     } 256 257     /** 258      * Adds a nullable <code>Column</code> object with the specified name and 259      * data type to the specified <code>Table</code> object. <p> 260      * 261      * @param t the table to which to add the specified column 262      * @param name the name of the column 263      * @param type the data type of the column 264      * @throws HsqlException if a problem occurs when adding the 265      * column (e.g. duplicate name) 266      */ 267     protected final void addColumn(Table t, String name, 268                                    int type) throws HsqlException { 269         addColumn(t, name, type, true); 270     } 271 272     /** 273      * Retrieves an enumeration over all of the tables in this database. 274      * This means all user tables, views, system tables, system views, 275      * including temporary and text tables. <p> 276      * 277      * @return an enumeration over all of the tables in this database 278      */ 279     protected final Iterator allTables() { 280         return new WrapperIterator(database.schemaManager.allTablesIterator(), 281                                    new WrapperIterator(sysTables, true)); 282     } 283 284     /** 285      * Clears the contents of cached system tables and resets user slots 286      * to null. <p> 287      * 288      * @throws HsqlException if a database access error occurs 289      */ 290     protected final void cacheClear() throws HsqlException { 291 292         int i = sysTables.length; 293 294         while (i-- > 0) { 295             Table t = sysTables[i]; 296 297             if (t != null) { 298                 t.clearAllRows(session); 299             } 300 301             sysTableSessions[i] = -1; 302         } 303 304         isDirty = false; 305     } 306 307     /** 308      * Retrieves the system table corresponding to the specified 309      * tableIndex value. <p> 310      * 311      * @param tableIndex int value identifying the system table to generate 312      * @throws HsqlException if a database access error occurs 313      * @return the system table corresponding to the specified tableIndex value 314      */ 315     protected Table generateTable(int tableIndex) throws HsqlException { 316 317         Table t = sysTables[tableIndex]; 318 319 // Please note that this class produces non-null tables for 320 // just those absolutely essential to the JDBC 1 spec and the 321 // HSQLDB core. Also, all table producing methods except 322 // SYSTEM_PROCEDURES() and SYSTEM_PROCEDURECOLUMNS() are declared final; 323 // this class produces only an empty table for each, as per previous 324 // DatabaseInformation implementations, whereas 325 // DatabaseInformationFull produces comprehensive content for 326 // them). 327 // 328 // This break down of inheritance allows DatabaseInformation and 329 // DatabaseInformationMain (this class) to be made as small as possible 330 // while still meeting their mandates: 331 // 332 // 1.) DatabaseInformation prevents use of reserved system table names 333 // for user tables and views, meaning that even under highly 334 // constrained use cases where the notion of DatabaseMetaData can 335 // be discarded (i.e. the engine operates in a distribution where 336 // DatabaseInforationMain/Full and jdbcDatabaseMetaData have been 337 // dropped from the JAR), it is still impossible to produce a 338 // database which will be incompatible in terms of system table <=> 339 // user table name clashes, if/when imported into a more 340 // capable operating environment. 341 // 342 // 2.) DatabaseInformationMain builds on DatabaseInformation, providing 343 // at minimum what is needed for comprehensive operation under 344 // JDK 1.1/JDBC 1 and provides, at minimum, what was provided under 345 // earlier implementations. 346 // 347 // 3.) descendents of DatabaseInformationMain (such as the current 348 // DatabaseInformationFull) need not (and indeed: now cannot) 349 // override most of the DatabaseInformationMain table producing 350 // methods, as for the most part they are expected to be already 351 // fully comprehensive, security aware and accessible to all users. 352 switch (tableIndex) { 353 354             case SYSTEM_BESTROWIDENTIFIER : 355                 return SYSTEM_BESTROWIDENTIFIER(); 356 357             case SYSTEM_CATALOGS : 358                 return SYSTEM_CATALOGS(); 359 360             case SYSTEM_COLUMNPRIVILEGES : 361                 return SYSTEM_COLUMNPRIVILEGES(); 362 363             case SYSTEM_COLUMNS : 364                 return SYSTEM_COLUMNS(); 365 366             case SYSTEM_CROSSREFERENCE : 367                 return SYSTEM_CROSSREFERENCE(); 368 369             case SYSTEM_INDEXINFO : 370                 return SYSTEM_INDEXINFO(); 371 372             case SYSTEM_PRIMARYKEYS : 373                 return SYSTEM_PRIMARYKEYS(); 374 375             case SYSTEM_PROCEDURECOLUMNS : 376                 return SYSTEM_PROCEDURECOLUMNS(); 377 378             case SYSTEM_PROCEDURES : 379                 return SYSTEM_PROCEDURES(); 380 381             case SYSTEM_SCHEMAS : 382                 return SYSTEM_SCHEMAS(); 383 384             case SYSTEM_TABLEPRIVILEGES : 385                 return SYSTEM_TABLEPRIVILEGES(); 386 387             case SYSTEM_TABLES : 388                 return SYSTEM_TABLES(); 389 390             case SYSTEM_TABLETYPES : 391                 return SYSTEM_TABLETYPES(); 392 393             case SYSTEM_TYPEINFO : 394                 return SYSTEM_TYPEINFO(); 395 396             case SYSTEM_USERS : 397                 return SYSTEM_USERS(); 398 399             // required by SYSTEM_TYPEINFRO 400 case SYSTEM_ALLTYPEINFO : 401                 return SYSTEM_ALLTYPEINFO(); 402 403             case SYSTEM_CHECK_CONSTRAINTS : 404                 return SYSTEM_CHECK_CONSTRAINTS(); 405 406             case SYSTEM_SEQUENCES : 407                 return SYSTEM_SEQUENCES(); 408 409             default : 410                 return null; 411         } 412     } 413 414     /** 415      * One time initialisation of instance attributes 416      * at construction time. <p> 417      * 418      * @throws HsqlException if a database access error occurs 419      */ 420     protected final void init() throws HsqlException { 421 422         ns = new DINameSpace(database); 423 424         // flag the Session-dependent cached tables 425 sysTableSessionDependent[SYSTEM_ALIASES] = 426             sysTableSessionDependent[SYSTEM_CLASSPRIVILEGES] = 427             sysTableSessionDependent[SYSTEM_BESTROWIDENTIFIER] = 428             sysTableSessionDependent[SYSTEM_COLUMNPRIVILEGES] = 429             sysTableSessionDependent[SYSTEM_COLUMNS] = 430             sysTableSessionDependent[SYSTEM_CROSSREFERENCE] = 431             sysTableSessionDependent[SYSTEM_INDEXINFO] = 432             sysTableSessionDependent[SYSTEM_PRIMARYKEYS] = 433             sysTableSessionDependent[SYSTEM_PROCEDURES] = 434             sysTableSessionDependent[SYSTEM_PROCEDURECOLUMNS] = 435             sysTableSessionDependent[SYSTEM_TABLEPRIVILEGES] = 436             sysTableSessionDependent[SYSTEM_TABLES] = 437             sysTableSessionDependent[SYSTEM_TRIGGERCOLUMNS] = 438             sysTableSessionDependent[SYSTEM_TRIGGERS] = 439             sysTableSessionDependent[SYSTEM_VIEWS] = 440             sysTableSessionDependent[SYSTEM_TEXTTABLES] = 441             sysTableSessionDependent[SYSTEM_CHECK_CONSTRAINTS] = 442             sysTableSessionDependent[SYSTEM_SEQUENCES] = 443             sysTableSessionDependent[SYSTEM_USAGE_PRIVILEGES] = 444             sysTableSessionDependent[SYSTEM_TABLE_CONSTRAINTS] = 445             sysTableSessionDependent[SYSTEM_CHECK_COLUMN_USAGE] = 446             sysTableSessionDependent[SYSTEM_CHECK_ROUTINE_USAGE] = 447             sysTableSessionDependent[SYSTEM_CHECK_TABLE_USAGE] = 448             sysTableSessionDependent[SYSTEM_VIEW_COLUMN_USAGE] = 449             sysTableSessionDependent[SYSTEM_VIEW_TABLE_USAGE] = 450             sysTableSessionDependent[SYSTEM_VIEW_ROUTINE_USAGE] = 451             sysTableSessionDependent[SYSTEM_AUTHORIZATIONS] = true; 452 453         Table t; 454 455 /* 456         Session oldSession = session; 457 458         session = database.sessionManager.getSysSession( 459             database.schemaManager.INFORMATION_SCHEMA); 460 */ 461         for (int i = 0; i < sysTables.length; i++) { 462             t = sysTables[i] = generateTable(i); 463 464             if (t != null) { 465                 t.setDataReadOnly(true); 466             } 467         } 468 469         GranteeManager gm = database.getGranteeManager(); 470 471         for (int i = 0; i < sysTableHsqlNames.length; i++) { 472             if (sysTables[i] != null) { 473                 gm.grant(GranteeManager.PUBLIC_ROLE_NAME, 474                          sysTableHsqlNames[i], UserManager.SELECT); 475             } 476         } 477 /* 478         session = oldSession; 479 */ 480     } 481 482     /** 483      * Retrieves whether any form of SQL access is allowed against the 484      * the specified table w.r.t the database access rights 485      * assigned to current Session object's User. <p> 486      * 487      * @return true if the table is accessible, else false 488      * @param table the table for which to check accessibility 489      * @throws HsqlException if a database access error occurs 490      */ 491     protected final boolean isAccessibleTable(Table table) 492     throws HsqlException { 493         return session.isAccessible(table.getName()); 494     } 495 496     /** 497      * Creates a new primoidal system table with the specified name. <p> 498      * 499      * @return a new system table 500      * @param name of the table 501      * @throws HsqlException if a database access error occurs 502      */ 503     protected final Table createBlankTable(HsqlName name) 504     throws HsqlException { 505         return new Table(database, name, Table.SYSTEM_TABLE); 506     } 507 508     /** 509      * Retrieves the system <code>Table</code> object corresponding to 510      * the given <code>name</code> and <code>session</code> arguments. <p> 511      * 512      * @param session the Session object requesting the table 513      * @param name a String identifying the desired table 514      * @throws HsqlException if there is a problem producing the table or a 515      * database access error occurs 516      * @return a system table corresponding to the <code>name</code> and 517      * <code>session</code> arguments 518      */ 519     final Table getSystemTable(Session session, 520                                String name) throws HsqlException { 521 522         Table t; 523         int tableIndex; 524 525         // must come first...many methods depend on this being set properly 526 this.session = session; 527 528         if (!isSystemTable(name)) { 529             return null; 530         } 531 532         tableIndex = getSysTableID(name); 533         t = sysTables[tableIndex]; 534 535         // fredt - any system table that is not supported will be null here 536 if (t == null) { 537             return t; 538         } 539 540         // At the time of opening the database, no content is needed 541 // at present. However, table structure is required at this 542 // point to allow processing logged View defn's against system 543 // tables. Returning tables without content speeds the database 544 // open phase under such cases. 545 if (!withContent) { 546             return t; 547         } 548 549         if (isDirty) { 550             cacheClear(); 551         } 552 553         int oldSessionId = sysTableSessions[tableIndex]; 554         boolean tableValid = oldSessionId != -1; 555 556         // user has changed and table is user-dependent 557 if (session.getId() != oldSessionId 558                 && sysTableSessionDependent[tableIndex]) { 559             tableValid = false; 560         } 561 562         if (nonCachedTablesSet.contains(name)) { 563             tableValid = false; 564         } 565 566         // any valid cached table will be returned here 567 if (tableValid) { 568             return t; 569         } 570 571         // fredt - clear the contents of table and set new User 572 t.clearAllRows(session); 573 574         sysTableSessions[tableIndex] = session.getId(); 575 576         // match and if found, generate. 577 t = generateTable(tableIndex); 578 579         // t will be null at this point, if the implementation 580 // does not support the particular table 581 // send back what we found or generated 582 return t; 583     } 584 585     /** 586      * Retrieves a <code>Table</code> object describing the optimal 587      * set of visible columns that uniquely identifies a row 588      * for each accessible table defined within this database. <p> 589      * 590      * Each row describes a single column of the best row indentifier column 591      * set for a particular table. Each row has the following 592      * columns: <p> 593      * 594      * <pre class="SqlCodeExample"> 595      * SCOPE SMALLINT scope of applicability 596      * COLUMN_NAME VARCHAR simple name of the column 597      * DATA_TYPE SMALLINT SQL data type from Types 598      * TYPE_NAME VARCHAR canonical type name 599      * COLUMN_SIZE INTEGER precision 600      * BUFFER_LENGTH INTEGER transfer size in bytes, if definitely known 601      * DECIMAL_DIGITS SMALLINT scale - fixed # of decimal digits 602      * PSEUDO_COLUMN SMALLINT is this a pseudo column like an Oracle ROWID? 603      * TABLE_CAT VARCHAR table catalog 604      * TABLE_SCHEM VARCHAR simple name of table schema 605      * TABLE_NAME VARCHAR simple table name 606      * NULLABLE SMALLINT is column nullable? 607      * IN_KEY BOOLEAN column belongs to a primary or alternate key? 608      * </pre> <p> 609      * 610      * <b>Notes:</b><p> 611      * 612      * <code>jdbcDatabaseMetaData.getBestRowIdentifier</code> uses its 613      * nullable parameter to filter the rows of this table in the following 614      * manner: <p> 615      * 616      * If the nullable parameter is <code>false</code>, then rows are reported 617      * only if, in addition to satisfying the other specified filter values, 618      * the IN_KEY column value is TRUE. If the nullable parameter is 619      * <code>true</code>, then the IN_KEY column value is ignored. <p> 620      * 621      * There is not yet infrastructure in place to make some of the ranking 622      * descisions described below, and it is anticipated that mechanisms 623      * upon which cost descisions could be based will change significantly over 624      * the next few releases. Hence, in the interest of simplicity and of not 625      * making overly complex dependency on features that will almost certainly 626      * change significantly in the near future, the current implementation, 627      * while perfectly adequate for all but the most demanding or exacting 628      * purposes, is actually sub-optimal in the strictest sense. <p> 629      * 630      * A description of the current implementation follows: <p> 631      * 632      * <b>DEFINTIONS:</b> <p> 633      * 634      * <b>Alternate key</b> <p> 635      * 636      * <UL> 637      * <LI> An attribute of a table that, by virtue of its having a set of 638      * columns that are both the full set of columns participating in a 639      * unique constraint or index and are all not null, yeilds the same 640      * selectability characteristic that would obtained by declaring a 641      * primary key on those same columns. 642      * </UL> <p> 643      * 644      * <b>Column set performance ranking</b> <p> 645      * 646      * <UL> 647      * <LI> The ranking of the expected average performance w.r.t a subset of 648      * a table's columns used to select and/or compare rows, as taken in 649      * relation to all other distinct candidate subsets under 650      * consideration. This can be estimated by comparing each cadidate 651      * subset in terms of total column count, relative peformance of 652      * comparisons amongst the domains of the columns and differences 653      * in other costs involved in the execution plans generated using 654      * each subset under consideration for row selection/comparison. 655      * </UL> <p> 656      * 657      * 658      * <b>Rules:</b> <p> 659      * 660      * Given the above definitions, the rules currently in effect for reporting 661      * best row identifier are as follows, in order of precedence: <p> 662      * 663      * <OL> 664      * <LI> if the table under consideration has a primary key contraint, then 665      * the columns of the primary key are reported, with no consideration 666      * given to the column set performance ranking over the set of 667      * candidate keys. Each row has its IN_KEY column set to TRUE. 668      * 669      * <LI> if 1.) does not hold, then if there exits one or more alternate 670      * keys, then the columns of the alternate key with the lowest column 671      * count are reported, with no consideration given to the column set 672      * performance ranking over the set of candidate keys. If there 673      * exists a tie for lowest column count, then the columns of the 674      * first such key encountered are reported. 675      * Each row has its IN_KEY column set to TRUE. 676      * 677      * <LI> if both 1.) and 2.) do not hold, then, if possible, a unique 678      * contraint/index is selected from the set of unique 679      * contraints/indices containing at least one column having 680      * a not null constraint, with no consideration given to the 681      * column set performance ranking over the set of all such 682      * candidate column sets. If there exists a tie for lowest non-zero 683      * count of columns having a not null constraint, then the columns 684      * of the first such encountered candidate set are reported. Each 685      * row has its IN_KEY column set to FALSE. <p> 686      * 687      * <LI> Finally, if the set of candidate column sets in 3.) is the empty, 688      * then no column set is reported for the table under consideration. 689      * </OL> <p> 690      * 691      * The scope reported for a best row identifier column set is determined 692      * thus: <p> 693      * 694      * <OL> 695      * <LI> if the database containing the table under consideration is in 696      * read-only mode or the table under consideration is GLOBAL TEMPORARY 697      * (a TEMP or TEMP TEXT table, in HSQLDB parlance), then the scope 698      * is reported as 699      * <code>java.sql.DatabaseMetaData.bestRowSession</code>. 700      * 701      * <LI> if 1.) does not hold, then the scope is reported as 702      * <code>java.sql.DatabaseMetaData.bestRowTemporary</code>. 703      * </OL> <p> 704      * 705      * @return a <code>Table</code> object describing the optimal 706      * set of visible columns that uniquely identifies a row 707      * for each accessible table defined within this database 708      * @throws HsqlException if an error occurs while producing the table 709      */ 710     final Table SYSTEM_BESTROWIDENTIFIER() throws HsqlException { 711 712         Table t = sysTables[SYSTEM_BESTROWIDENTIFIER]; 713 714         if (t == null) { 715             t = createBlankTable(sysTableHsqlNames[SYSTEM_BESTROWIDENTIFIER]); 716 717             addColumn(t, "SCOPE", Types.SMALLINT, false); // not null 718 addColumn(t, "COLUMN_NAME", Types.VARCHAR, false); // not null 719 addColumn(t, "DATA_TYPE", Types.SMALLINT, false); // not null 720 addColumn(t, "TYPE_NAME", Types.VARCHAR, 32, false); // not null 721 addColumn(t, "COLUMN_SIZE", Types.INTEGER); 722             addColumn(t, "BUFFER_LENGTH", Types.INTEGER); 723             addColumn(t, "DECIMAL_DIGITS", Types.SMALLINT); 724             addColumn(t, "PSEUDO_COLUMN", Types.SMALLINT, false); // not null 725 addColumn(t, "TABLE_CAT", Types.VARCHAR); 726             addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 727             addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // not null 728 addColumn(t, "NULLABLE", Types.SMALLINT, false); // not null 729 addColumn(t, "IN_KEY", Types.BOOLEAN, false); // not null 730 731             // order: SCOPE 732 // for unique: TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME 733 // false PK, as TABLE_CAT and/or TABLE_SCHEM may be null 734 t.createPrimaryKey(null, new int[] { 735                 0, 8, 9, 10, 1 736             }, false); 737 738             return t; 739         } 740 741         // calculated column values 742 Integer scope; // { temp, transaction, session } 743 Integer pseudo; 744 745         //------------------------------------------- 746 // required for restriction of results via 747 // DatabaseMetaData filter parameters, but 748 // not actually required to be included in 749 // DatabaseMetaData.getBestRowIdentifier() 750 // result set 751 //------------------------------------------- 752 String tableCatalog; // table calalog 753 String tableSchema; // table schema 754 String tableName; // table name 755 Boolean inKey; // column participates in PK or AK? 756 757         //------------------------------------------- 758 // TODO: Maybe include: 759 // - backing index (constraint) name? 760 // - column sequence in index (constraint)? 761 //------------------------------------------- 762 // Intermediate holders 763 Iterator tables; 764         Table table; 765         DITableInfo ti; 766         int[] cols; 767         Object [] row; 768         HsqlProperties p; 769 770         // Column number mappings 771 final int iscope = 0; 772         final int icolumn_name = 1; 773         final int idata_type = 2; 774         final int itype_name = 3; 775         final int icolumn_size = 4; 776         final int ibuffer_length = 5; 777         final int idecimal_digits = 6; 778         final int ipseudo_column = 7; 779         final int itable_cat = 8; 780         final int itable_schem = 9; 781         final int itable_name = 10; 782         final int inullable = 11; 783         final int iinKey = 12; 784 785         // Initialization 786 ti = new DITableInfo(); 787         p = database.getProperties(); 788         tables = p.isPropertyTrue("hsqldb.system_table_bri") ? allTables() 789                                                              : database 790                                                              .schemaManager 791                                                                  .allTablesIterator(); 792 793         // Do it. 794 while (tables.hasNext()) { 795             table = (Table) tables.next(); 796 797             if (table.isView() ||!isAccessibleTable(table)) { 798                 continue; 799             } 800 801             cols = table.getBestRowIdentifiers(); 802 803             if (cols == null) { 804                 continue; 805             } 806 807             ti.setTable(table); 808 809             inKey = ValuePool.getBoolean(table.isBestRowIdentifiersStrict()); 810             tableCatalog = ns.getCatalogName(table); 811             tableSchema = table.getSchemaName(); 812             tableName = ti.getName(); 813             scope = ti.getBRIScope(); 814             pseudo = ti.getBRIPseudo(); 815 816             for (int i = 0; i < cols.length; i++) { 817                 row = t.getEmptyRowData(); 818                 row[iscope] = scope; 819                 row[icolumn_name] = ti.getColName(i); 820                 row[idata_type] = ti.getColDataType(i); 821                 row[itype_name] = ti.getColDataTypeName(i); 822                 row[icolumn_size] = ti.getColSize(i); 823                 row[ibuffer_length] = ti.getColBufLen(i); 824                 row[idecimal_digits] = ti.getColScale(i); 825                 row[ipseudo_column] = pseudo; 826                 row[itable_cat] = tableCatalog; 827                 row[itable_schem] = tableSchema; 828                 row[itable_name] = tableName; 829                 row[inullable] = ti.getColNullability(i); 830                 row[iinKey] = inKey; 831 832                 t.insertSys(row); 833             } 834         } 835 836         t.setDataReadOnly(true); 837 838         return t; 839     } 840 841     /** 842      * Retrieves a <code>Table</code> object naming the accessible catalogs 843      * defined within this database. <p> 844      * 845      * Each row is a catalog name description with the following column: <p> 846      * 847      * <pre class="SqlCodeExample"> 848      * TABLE_CAT VARCHAR catalog name 849      * </pre> <p> 850      * 851      * @return a <code>Table</code> object naming the accessible 852      * catalogs defined within this database 853      * @throws HsqlException if an error occurs while producing the table 854      */ 855     final Table SYSTEM_CATALOGS() throws HsqlException { 856 857         Table t = sysTables[SYSTEM_CATALOGS]; 858 859         if (t == null) { 860             t = createBlankTable(sysTableHsqlNames[SYSTEM_CATALOGS]); 861 862             addColumn(t, "TABLE_CAT", Types.VARCHAR, false); // not null 863 864             // order: TABLE_CAT 865 // true PK 866 t.createPrimaryKey(null, new int[]{ 0 }, true); 867 868             return t; 869         } 870 871         Object [] row; 872         Iterator catalogs; 873 874         catalogs = ns.iterateCatalogNames(); 875 876         while (catalogs.hasNext()) { 877             row = t.getEmptyRowData(); 878             row[0] = (String ) catalogs.next(); 879 880             t.insertSys(row); 881         } 882 883         t.setDataReadOnly(true); 884 885         return t; 886     } 887 888     /** 889      * Retrieves a <code>Table</code> object describing the visible 890      * access rights for all visible columns of all accessible 891      * tables defined within this database.<p> 892      * 893      * Each row is a column privilege description with the following 894      * columns: <p> 895      * 896      * <pre class="SqlCodeExample"> 897      * TABLE_CAT VARCHAR table catalog 898      * TABLE_SCHEM VARCHAR table schema 899      * TABLE_NAME VARCHAR table name 900      * COLUMN_NAME VARCHAR column name 901      * GRANTOR VARCHAR grantor of access 902      * GRANTEE VARCHAR grantee of access 903      * PRIVILEGE VARCHAR name of access 904      * IS_GRANTABLE VARCHAR grantable?: "YES" - grant to others, else "NO" 905      * </pre> 906      * 907      * <b>Note:</b> As of 1.7.2, HSQLDB does not support column level 908      * privileges. However, it does support table-level privileges, so they 909      * are reflected here. That is, the content of this table is equivalent 910      * to a projection of SYSTEM_TABLEPRIVILEGES and SYSTEM_COLUMNS joined by 911      * full table identifier. <p> 912      * 913      * @return a <code>Table</code> object describing the visible 914      * access rights for all visible columns of 915      * all accessible tables defined within this 916      * database 917      * @throws HsqlException if an error occurs while producing the table 918      */ 919     final Table SYSTEM_COLUMNPRIVILEGES() throws HsqlException { 920 921         Table t = sysTables[SYSTEM_COLUMNPRIVILEGES]; 922 923         if (t == null) { 924             t = createBlankTable(sysTableHsqlNames[SYSTEM_COLUMNPRIVILEGES]); 925 926             addColumn(t, "TABLE_CAT", Types.VARCHAR); 927             addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 928             addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // not null 929 addColumn(t, "COLUMN_NAME", Types.VARCHAR, false); // not null 930 addColumn(t, "GRANTOR", Types.VARCHAR, false); // not null 931 addColumn(t, "GRANTEE", Types.VARCHAR, false); // not null 932 addColumn(t, "PRIVILEGE", Types.VARCHAR, 10, false); // not null 933 addColumn(t, "IS_GRANTABLE", Types.VARCHAR, 3, false); // not null 934 935             // order: COLUMN_NAME, PRIVILEGE 936 // for unique: GRANTEE, GRANTOR, TABLE_NAME, TABLE_SCHEM, TABLE_CAT 937 // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null 938 t.createPrimaryKey(null, new int[] { 939                 3, 6, 5, 4, 2, 1, 0 940             }, false); 941 942             return t; 943         } 944 945         Result rs; 946 947         rs = session.sqlExecuteDirectNoPreChecks( 948             "select a.TABLE_CAT, a.TABLE_SCHEM, a.TABLE_NAME, b.COLUMN_NAME, " 949             + "a.GRANTOR, a.GRANTEE, a.PRIVILEGE, a.IS_GRANTABLE " 950             + "from INFORMATION_SCHEMA.SYSTEM_TABLEPRIVILEGES a, " 951             + "INFORMATION_SCHEMA.SYSTEM_COLUMNS b " 952             + "where a.TABLE_NAME = b.TABLE_NAME and " 953             + "a.TABLE_SCHEM = b.TABLE_SCHEM;"); 954 955         t.insertSys(rs); 956         t.setDataReadOnly(true); 957 958         return t; 959     } 960 961     /** 962      * Retrieves a <code>Table</code> object describing the 963      * visible columns of all accessible tables defined 964      * within this database.<p> 965      * 966      * Each row is a column description with the following columns: <p> 967      * 968      * <pre class="SqlCodeExample"> 969      * TABLE_CAT VARCHAR table catalog 970      * TABLE_SCHEM VARCHAR table schema 971      * TABLE_NAME VARCHAR table name 972      * COLUMN_NAME VARCHAR column name 973      * DATA_TYPE SMALLINT SQL type from DITypes 974      * TYPE_NAME VARCHAR canonical type name 975      * COLUMN_SIZE INTEGER column size (length/precision) 976      * BUFFER_LENGTH INTEGER transfer size in bytes, if definitely known 977      * DECIMAL_DIGITS INTEGER # of fractional digits (scale) 978      * NUM_PREC_RADIX INTEGER Radix 979      * NULLABLE INTEGER is NULL allowed? (from DatabaseMetaData) 980      * REMARKS VARCHAR comment describing column 981      * COLUMN_DEF VARCHAR default value (possibly expression) 982      * SQL_DATA_TYPE VARCHAR type code as expected in the SQL CLI SQLDA 983      * SQL_DATETIME_SUB INTEGER the SQL CLI subtype for DATETIME types 984      * CHAR_OCTET_LENGTH INTEGER for char types, max # of bytes in column 985      * ORDINAL_POSITION INTEGER 1-based index of column in table 986      * IS_NULLABLE VARCHAR is column nullable? ("YES"|"NO"|""} 987      * SCOPE_CATLOG VARCHAR catalog of REF attribute scope table 988      * SCOPE_SCHEMA VARCHAR schema of REF attribute scope table 989      * SCOPE_TABLE VARCHAR name of REF attribute scope table 990      * SOURCE_DATA_TYPE VARCHAR source type of REF attribute 991      * TYPE_SUB INTEGER HSQLDB data subtype code 992      * </pre> <p> 993      * 994      * @return a <code>Table</code> object describing the 995      * visible columns of all accessible 996      * tables defined within this database.<p> 997      * @throws HsqlException if an error occurs while producing the table 998      */ 999     final Table SYSTEM_COLUMNS() throws HsqlException { 1000 1001        Table t = sysTables[SYSTEM_COLUMNS]; 1002 1003        if (t == null) { 1004            t = createBlankTable(sysTableHsqlNames[SYSTEM_COLUMNS]); 1005 1006            addColumn(t, "TABLE_CAT", Types.VARCHAR); 1007            addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 1008            addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // not null 1009 addColumn(t, "COLUMN_NAME", Types.VARCHAR, false); // not null 1010 addColumn(t, "DATA_TYPE", Types.SMALLINT, false); // not null 1011 addColumn(t, "TYPE_NAME", Types.VARCHAR, 32, false); // not null 1012 addColumn(t, "COLUMN_SIZE", Types.INTEGER); 1013            addColumn(t, "BUFFER_LENGTH", Types.INTEGER); 1014            addColumn(t, "DECIMAL_DIGITS", Types.INTEGER); 1015            addColumn(t, "NUM_PREC_RADIX", Types.INTEGER); 1016            addColumn(t, "NULLABLE", Types.INTEGER, false); // not null 1017 addColumn(t, "REMARKS", Types.VARCHAR); 1018            addColumn(t, "COLUMN_DEF", Types.VARCHAR); 1019            addColumn(t, "SQL_DATA_TYPE", Types.INTEGER); 1020            addColumn(t, "SQL_DATETIME_SUB", Types.INTEGER); 1021            addColumn(t, "CHAR_OCTET_LENGTH", Types.INTEGER); 1022            addColumn(t, "ORDINAL_POSITION", Types.INTEGER, false); // not null 1023 addColumn(t, "IS_NULLABLE", Types.VARCHAR, 3, false); // not null 1024 addColumn(t, "SCOPE_CATLOG", Types.VARCHAR); 1025            addColumn(t, "SCOPE_SCHEMA", Types.VARCHAR); 1026            addColumn(t, "SCOPE_TABLE", Types.VARCHAR); 1027            addColumn(t, "SOURCE_DATA_TYPE", Types.VARCHAR); 1028            addColumn(t, "TYPE_SUB", Types.INTEGER, false); // not null 1029 1030            // order: TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION 1031 // added for unique: TABLE_CAT 1032 // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null 1033 t.createPrimaryKey(null, new int[] { 1034                1, 2, 16, 0 1035            }, false); 1036 1037            return t; 1038        } 1039 1040        // calculated column values 1041 String tableCatalog; 1042        String tableSchema; 1043        String tableName; 1044 1045        // intermediate holders 1046 int columnCount; 1047        Iterator tables; 1048        Table table; 1049        Object [] row; 1050        DITableInfo ti; 1051 1052        // column number mappings 1053 final int itable_cat = 0; 1054        final int itable_schem = 1; 1055        final int itable_name = 2; 1056        final int icolumn_name = 3; 1057        final int idata_type = 4; 1058        final int itype_name = 5; 1059        final int icolumn_size = 6; 1060        final int ibuffer_length = 7; 1061        final int idecimal_digits = 8; 1062        final int inum_prec_radix = 9; 1063        final int inullable = 10; 1064        final int iremark = 11; 1065        final int icolumn_def = 12; 1066        final int isql_data_type = 13; 1067        final int isql_datetime_sub = 14; 1068        final int ichar_octet_length = 15; 1069        final int iordinal_position = 16; 1070        final int iis_nullable = 17; 1071        final int itype_sub = 22; 1072 1073        // Initialization 1074 tables = allTables(); 1075        ti = new DITableInfo(); 1076 1077        // Do it. 1078 while (tables.hasNext()) { 1079            table = (Table) tables.next(); 1080 1081            if (!isAccessibleTable(table)) { 1082                continue; 1083            } 1084 1085            ti.setTable(table); 1086 1087            tableCatalog = ns.getCatalogName(table); 1088            tableSchema = table.getSchemaName(); 1089            tableName = ti.getName(); 1090            columnCount = table.getColumnCount(); 1091 1092            for (int i = 0; i < columnCount; i++) { 1093                row = t.getEmptyRowData(); 1094                row[itable_cat] = tableCatalog; 1095                row[itable_schem] = tableSchema; 1096                row[itable_name] = tableName; 1097                row[icolumn_name] = ti.getColName(i); 1098                row[idata_type] = ti.getColDataType(i); 1099                row[itype_name] = ti.getColDataTypeName(i); 1100                row[icolumn_size] = ti.getColSize(i); 1101                row[ibuffer_length] = ti.getColBufLen(i); 1102                row[idecimal_digits] = ti.getColScale(i); 1103                row[inum_prec_radix] = ti.getColPrecRadix(i); 1104                row[inullable] = ti.getColNullability(i); 1105                row[iremark] = ti.getColRemarks(i); 1106                row[icolumn_def] = ti.getColDefault(i); 1107                row[isql_data_type] = ti.getColSqlDataType(i); 1108                row[isql_datetime_sub] = ti.getColSqlDateTimeSub(i); 1109                row[ichar_octet_length] = ti.getColCharOctLen(i); 1110                row[iordinal_position] = ValuePool.getInt(i + 1); 1111                row[iis_nullable] = ti.getColIsNullable(i); 1112                row[itype_sub] = ti.getColDataTypeSub(i); 1113 1114                t.insertSys(row); 1115            } 1116        } 1117 1118        t.setDataReadOnly(true); 1119 1120        return t; 1121    } 1122 1123    /** 1124     * Retrieves a <code>Table</code> object describing, for each 1125     * accessible referencing and referenced table, how the referencing 1126     * tables import, for the purposes of referential integrity, 1127     * the columns of the referenced tables.<p> 1128     * 1129     * Each row is a foreign key column description with the following 1130     * columns: <p> 1131     * 1132     * <pre class="SqlCodeExample"> 1133     * PKTABLE_CAT VARCHAR referenced table catalog 1134     * PKTABLE_SCHEM VARCHAR referenced table schema 1135     * PKTABLE_NAME VARCHAR referenced table name 1136     * PKCOLUMN_NAME VARCHAR referenced column name 1137     * FKTABLE_CAT VARCHAR referencing table catalog 1138     * FKTABLE_SCHEM VARCHAR referencing table schema 1139     * FKTABLE_NAME VARCHAR referencing table name 1140     * FKCOLUMN_NAME VARCHAR referencing column 1141     * KEY_SEQ SMALLINT sequence number within foreign key 1142     * UPDATE_RULE SMALLINT 1143     * { Cascade | Set Null | Set Default | Restrict (No Action)}? 1144     * DELETE_RULE SMALLINT 1145     * { Cascade | Set Null | Set Default | Restrict (No Action)}? 1146     * FK_NAME VARCHAR foreign key constraint name 1147     * PK_NAME VARCHAR primary key or unique constraint name 1148     * DEFERRABILITY SMALLINT 1149     * { initially deferred | initially immediate | not deferrable } 1150     * </pre> <p> 1151     * 1152     * @return a <code>Table</code> object describing how accessible tables 1153     * import other accessible tables' primary key and/or unique 1154     * constraint columns 1155     * @throws HsqlException if an error occurs while producing the table 1156     */ 1157    final Table SYSTEM_CROSSREFERENCE() throws HsqlException { 1158 1159        Table t = sysTables[SYSTEM_CROSSREFERENCE]; 1160 1161        if (t == null) { 1162            t = createBlankTable(sysTableHsqlNames[SYSTEM_CROSSREFERENCE]); 1163 1164            addColumn(t, "PKTABLE_CAT", Types.VARCHAR); 1165            addColumn(t, "PKTABLE_SCHEM", Types.VARCHAR); 1166            addColumn(t, "PKTABLE_NAME", Types.VARCHAR, false); // not null 1167 addColumn(t, "PKCOLUMN_NAME", Types.VARCHAR, false); // not null 1168 addColumn(t, "FKTABLE_CAT", Types.VARCHAR); 1169            addColumn(t, "FKTABLE_SCHEM", Types.VARCHAR); 1170            addColumn(t, "FKTABLE_NAME", Types.VARCHAR, false); // not null 1171 addColumn(t, "FKCOLUMN_NAME", Types.VARCHAR, false); // not null 1172 addColumn(t, "KEY_SEQ", Types.SMALLINT, false); // not null 1173 addColumn(t, "UPDATE_RULE", Types.SMALLINT, false); // not null 1174 addColumn(t, "DELETE_RULE", Types.SMALLINT, false); // not null 1175 addColumn(t, "FK_NAME", Types.VARCHAR); 1176            addColumn(t, "PK_NAME", Types.VARCHAR); 1177            addColumn(t, "DEFERRABILITY", Types.SMALLINT, false); // not null 1178 1179            // order: FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ 1180 // added for unique: FK_NAME 1181 // false PK, as FKTABLE_CAT, FKTABLE_SCHEM and/or FK_NAME 1182 // may be null 1183 t.createPrimaryKey(null, new int[] { 1184                4, 5, 6, 8, 11 1185            }, false); 1186 1187            return t; 1188        } 1189 1190        // calculated column values 1191 String pkTableCatalog; 1192        String pkTableSchema; 1193        String pkTableName; 1194        String pkColumnName; 1195        String fkTableCatalog; 1196        String fkTableSchema; 1197        String fkTableName; 1198        String fkColumnName; 1199        Integer keySequence; 1200        Integer updateRule; 1201        Integer deleteRule; 1202        String fkName; 1203        String pkName; 1204        Integer deferrability; 1205 1206        // Intermediate holders 1207 Iterator tables; 1208        Table table; 1209        Table fkTable; 1210        Table pkTable; 1211        int columnCount; 1212        int[] mainCols; 1213        int[] refCols; 1214        Constraint[] constraints; 1215        Constraint constraint; 1216        int constraintCount; 1217        HsqlArrayList fkConstraintsList; 1218        Object [] row; 1219        DITableInfo pkInfo; 1220        DITableInfo fkInfo; 1221 1222        // column number mappings 1223 final int ipk_table_cat = 0; 1224        final int ipk_table_schem = 1; 1225        final int ipk_table_name = 2; 1226        final int ipk_column_name = 3; 1227        final int ifk_table_cat = 4; 1228        final int ifk_table_schem = 5; 1229        final int ifk_table_name = 6; 1230        final int ifk_column_name = 7; 1231        final int ikey_seq = 8; 1232        final int iupdate_rule = 9; 1233        final int idelete_rule = 10; 1234        final int ifk_name = 11; 1235        final int ipk_name = 12; 1236        final int ideferrability = 13; 1237 1238        tables = database.schemaManager.allTablesIterator(); 1239        pkInfo = new DITableInfo(); 1240        fkInfo = new DITableInfo(); 1241 1242        // the only deferrability rule currently supported by hsqldb is: 1243 deferrability = ValuePool.getInt(Constraint.NOT_DEFERRABLE); 1244 1245        // We must consider all the constraints in all the user tables, since 1246 // this is where reference relationships are recorded. However, we 1247 // are only concerned with Constraint.FOREIGN_KEY constraints here 1248 // because their corresponing Constraint.MAIN entries are essentially 1249 // duplicate data recorded in the referenced rather than the 1250 // referencing table. Also, we skip constraints where either 1251 // the referenced, referencing or both tables are not accessible 1252 // relative to the session of the calling context 1253 fkConstraintsList = new HsqlArrayList(); 1254 1255        while (tables.hasNext()) { 1256            table = (Table) tables.next(); 1257 1258            if (!isAccessibleTable(table)) { 1259                continue; 1260            } 1261 1262            constraints = table.getConstraints(); 1263            constraintCount = constraints.length; 1264 1265            for (int i = 0; i < constraintCount; i++) { 1266                constraint = (Constraint) constraints[i]; 1267 1268                if (constraint.getType() == Constraint.FOREIGN_KEY 1269                        && isAccessibleTable(constraint.getRef())) { 1270                    fkConstraintsList.add(constraint); 1271                } 1272            } 1273        } 1274 1275        // Now that we have all of the desired constraints, we need to 1276 // process them, generating one row in our ouput table for each 1277 // imported/exported column pair of each constraint. 1278 // Do it. 1279 for (int i = 0; i < fkConstraintsList.size(); i++) { 1280            constraint = (Constraint) fkConstraintsList.get(i); 1281            pkTable = constraint.getMain(); 1282 1283            pkInfo.setTable(pkTable); 1284 1285            pkTableName = pkInfo.getName(); 1286            fkTable = constraint.getRef(); 1287 1288            fkInfo.setTable(fkTable); 1289 1290            fkTableName = fkInfo.getName(); 1291            pkTableCatalog = ns.getCatalogName(pkTable); 1292            pkTableSchema = pkTable.getSchemaName(); 1293            fkTableCatalog = ns.getCatalogName(fkTable); 1294            fkTableSchema = fkTable.getSchemaName(); 1295            mainCols = constraint.getMainColumns(); 1296            refCols = constraint.getRefColumns(); 1297            columnCount = refCols.length; 1298            fkName = constraint.getFkName(); 1299            pkName = constraint.getPkName(); 1300 1301            //pkName = constraint.getMainIndex().getName().name; 1302 deleteRule = ValuePool.getInt(constraint.getDeleteAction()); 1303            updateRule = ValuePool.getInt(constraint.getUpdateAction()); 1304 1305            for (int j = 0; j < columnCount; j++) { 1306                keySequence = ValuePool.getInt(j + 1); 1307                pkColumnName = pkInfo.getColName(mainCols[j]); 1308                fkColumnName = fkInfo.getColName(refCols[j]); 1309                row = t.getEmptyRowData(); 1310                row[ipk_table_cat] = pkTableCatalog; 1311                row[ipk_table_schem] = pkTableSchema; 1312                row[ipk_table_name] = pkTableName; 1313                row[ipk_column_name] = pkColumnName; 1314                row[ifk_table_cat] = fkTableCatalog; 1315                row[ifk_table_schem] = fkTableSchema; 1316                row[ifk_table_name] = fkTableName; 1317                row[ifk_column_name] = fkColumnName; 1318                row[ikey_seq] = keySequence; 1319                row[iupdate_rule] = updateRule; 1320                row[idelete_rule] = deleteRule; 1321                row[ifk_name] = fkName; 1322                row[ipk_name] = pkName; 1323                row[ideferrability] = deferrability; 1324 1325                t.insertSys(row); 1326            } 1327        } 1328 1329        t.setDataReadOnly(true); 1330 1331        return t; 1332    } 1333 1334    /** 1335     * Retrieves a <code>Table</code> object describing the visible 1336     * <code>Index</code> objects for each accessible table defined 1337     * within this database.<p> 1338     * 1339     * Each row is an index column description with the following 1340     * columns: <p> 1341     * 1342     * <pre class="SqlCodeExample"> 1343     * TABLE_CAT VARCHAR table's catalog 1344     * TABLE_SCHEM VARCHAR simple name of table's schema 1345     * TABLE_NAME VARCHAR simple name of the table using the index 1346     * NON_UNIQUE BOOLEAN can index values be non-unique? 1347     * INDEX_QUALIFIER VARCHAR catalog in which the index is defined 1348     * INDEX_NAME VARCHAR simple name of the index 1349     * TYPE SMALLINT index type: { Clustered | Hashed | Other } 1350     * ORDINAL_POSITION SMALLINT column sequence number within index 1351     * COLUMN_NAME VARCHAR simple column name 1352     * ASC_OR_DESC VARCHAR col. sort sequence: {"A" (Asc) | "D" (Desc)} 1353     * CARDINALITY INTEGER # of unique values in index (not implemented) 1354     * PAGES INTEGER index page use (not implemented) 1355     * FILTER_CONDITION VARCHAR filter condition, if any (not implemented) 1356     * // HSQLDB-extension 1357     * ROW_CARDINALITY INTEGER total # of rows in index (not implemented) 1358     * </pre> <p> 1359     * 1360     * @return a <code>Table</code> object describing the visible 1361     * <code>Index</code> objects for each accessible 1362     * table defined within this database. 1363     * @throws HsqlException if an error occurs while producing the table 1364     */ 1365    final Table SYSTEM_INDEXINFO() throws HsqlException { 1366 1367        Table t = sysTables[SYSTEM_INDEXINFO]; 1368 1369        if (t == null) { 1370            t = createBlankTable(sysTableHsqlNames[SYSTEM_INDEXINFO]); 1371 1372            // JDBC 1373 addColumn(t, "TABLE_CAT", Types.VARCHAR); 1374            addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 1375            addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // NOT NULL 1376 addColumn(t, "NON_UNIQUE", Types.BOOLEAN, false); // NOT NULL 1377 addColumn(t, "INDEX_QUALIFIER", Types.VARCHAR); 1378            addColumn(t, "INDEX_NAME", Types.VARCHAR); 1379            addColumn(t, "TYPE", Types.SMALLINT, false); // NOT NULL 1380 addColumn(t, "ORDINAL_POSITION", Types.SMALLINT, false); // NOT NULL 1381 addColumn(t, "COLUMN_NAME", Types.VARCHAR); 1382            addColumn(t, "ASC_OR_DESC", Types.VARCHAR, 1, true); 1383            addColumn(t, "CARDINALITY", Types.INTEGER); 1384            addColumn(t, "PAGES", Types.INTEGER); 1385            addColumn(t, "FILTER_CONDITION", Types.VARCHAR); 1386 1387            // HSQLDB extension 1388 addColumn(t, "ROW_CARDINALITY", Types.INTEGER); 1389 1390            // order: NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION. 1391 // added for unique: INDEX_QUALIFIER, TABLE_NAME 1392 // false PK, as INDEX_QUALIFIER may be null 1393 t.createPrimaryKey(null, new int[] { 1394                3, 6, 5, 7, 4, 2 1395            }, false); 1396 1397            return t; 1398        } 1399 1400        // calculated column values 1401 String tableCatalog; 1402        String tableSchema; 1403        String tableName; 1404        Boolean nonUnique; 1405        String indexQualifier; 1406        String indexName; 1407        Integer indexType; 1408 1409        //Integer ordinalPosition; 1410 //String columnName; 1411 //String ascOrDesc; 1412 Integer cardinality; 1413        Integer pages; 1414        String filterCondition; 1415        Integer rowCardinality; 1416 1417        // Intermediate holders 1418 Iterator tables; 1419        Table table; 1420        int indexCount; 1421        int[] cols; 1422        int col; 1423        int colCount; 1424        Object [] row; 1425        DITableInfo ti; 1426        HsqlProperties p; 1427 1428        // column number mappings 1429 final int itable_cat = 0; 1430        final int itable_schem = 1; 1431        final int itable_name = 2; 1432        final int inon_unique = 3; 1433        final int iindex_qualifier = 4; 1434        final int iindex_name = 5; 1435        final int itype = 6; 1436        final int iordinal_position = 7; 1437        final int icolumn_name = 8; 1438        final int iasc_or_desc = 9; 1439        final int icardinality = 10; 1440        final int ipages = 11; 1441        final int ifilter_condition = 12; 1442        final int irow_cardinality = 13; 1443 1444        // Initialization 1445 ti = new DITableInfo(); 1446        p = database.getProperties(); 1447        tables = p.isPropertyTrue("hsqldb.system_table_indexinfo") 1448                 ? allTables() 1449                 : database.schemaManager.allTablesIterator(); 1450 1451        // Do it. 1452 while (tables.hasNext()) { 1453            table = (Table) tables.next(); 1454 1455            if (table.isView() ||!isAccessibleTable(table)) { 1456                continue; 1457            } 1458 1459            ti.setTable(table); 1460 1461            tableCatalog = ns.getCatalogName(table); 1462            tableSchema = table.getSchemaName(); 1463            tableName = ti.getName(); 1464 1465            // not supported yet 1466 filterCondition = null; 1467 1468            // different cat for index not supported yet 1469 indexQualifier = tableCatalog; 1470            indexCount = table.getIndexCount(); 1471 1472            // process all of the visible indices for this table 1473 for (int i = 0; i < indexCount; i++) { 1474                colCount = ti.getIndexVisibleColumns(i); 1475 1476                if (colCount < 1) { 1477                    continue; 1478                } 1479 1480                indexName = ti.getIndexName(i); 1481                nonUnique = ti.isIndexNonUnique(i); 1482                cardinality = ti.getIndexCardinality(i); 1483                pages = ValuePool.getInt(0); 1484                rowCardinality = ti.getIndexRowCardinality(i); 1485                cols = ti.getIndexColumns(i); 1486                indexType = ti.getIndexType(i); 1487 1488                for (int k = 0; k < colCount; k++) { 1489                    col = cols[k]; 1490                    row = t.getEmptyRowData(); 1491                    row[itable_cat] = tableCatalog; 1492                    row[itable_schem] = tableSchema; 1493                    row[itable_name] = tableName; 1494                    row[inon_unique] = nonUnique; 1495                    row[iindex_qualifier] = indexQualifier; 1496                    row[iindex_name] = indexName; 1497                    row[itype] = indexType; 1498                    row[iordinal_position] = ValuePool.getInt(k + 1); 1499                    row[icolumn_name] = ti.getColName(col); 1500                    row[iasc_or_desc] = ti.getIndexColDirection(i, col); 1501                    row[icardinality] = cardinality; 1502                    row[ipages] = pages; 1503                    row[irow_cardinality] = rowCardinality; 1504                    row[ifilter_condition] = filterCondition; 1505 1506                    t.insertSys(row); 1507                } 1508            } 1509        } 1510 1511        t.setDataReadOnly(true); 1512 1513        return t; 1514    } 1515 1516    /** 1517     * Retrieves a <code>Table</code> object describing the visible 1518     * primary key columns of each accessible table defined within 1519     * this database. <p> 1520     * 1521     * Each row is a PRIMARY KEY column description with the following 1522     * columns: <p> 1523     * 1524     * <pre class="SqlCodeExample"> 1525     * TABLE_CAT VARCHAR table catalog 1526     * TABLE_SCHEM VARCHAR table schema 1527     * TABLE_NAME VARCHAR table name 1528     * COLUMN_NAME VARCHAR column name 1529     * KEY_SEQ SMALLINT sequence number within primary key 1530     * PK_NAME VARCHAR primary key constraint name 1531     * </pre> <p> 1532     * 1533     * @return a <code>Table</code> object describing the visible 1534     * primary key columns of each accessible table 1535     * defined within this database. 1536     * @throws HsqlException if an error occurs while producing the table 1537     */ 1538    final Table SYSTEM_PRIMARYKEYS() throws HsqlException { 1539 1540        Table t = sysTables[SYSTEM_PRIMARYKEYS]; 1541 1542        if (t == null) { 1543            t = createBlankTable(sysTableHsqlNames[SYSTEM_PRIMARYKEYS]); 1544 1545            addColumn(t, "TABLE_CAT", Types.VARCHAR); 1546            addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 1547            addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // not null 1548 addColumn(t, "COLUMN_NAME", Types.VARCHAR, false); // not null 1549 addColumn(t, "KEY_SEQ", Types.SMALLINT, false); // not null 1550 addColumn(t, "PK_NAME", Types.VARCHAR); 1551 1552            // order: COLUMN_NAME 1553 // added for unique: TABLE_NAME, TABLE_SCHEM, TABLE_CAT 1554 // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null 1555 t.createPrimaryKey(null, new int[] { 1556                3, 2, 1, 0 1557            }, false); 1558 1559            return t; 1560        } 1561 1562        // calculated column values 1563 String tableCatalog; 1564        String tableSchema; 1565        String tableName; 1566 1567        //String columnName; 1568 //Integer keySequence; 1569 String primaryKeyName; 1570 1571        // Intermediate holders 1572 Iterator tables; 1573        Table table; 1574        Object [] row; 1575        Index index; 1576        int[] cols; 1577        int colCount; 1578        DITableInfo ti; 1579        HsqlProperties p; 1580 1581        // column number mappings 1582 final int itable_cat = 0; 1583        final int itable_schem = 1; 1584        final int itable_name = 2; 1585        final int icolumn_name = 3; 1586        final int ikey_seq = 4; 1587        final int ipk_name = 5; 1588 1589        // Initialization 1590 ti = new DITableInfo(); 1591        p = database.getProperties(); 1592        tables = p.isPropertyTrue("hsqldb.system_table_primarykeys") 1593                 ? allTables() 1594                 : database.schemaManager.allTablesIterator(); 1595 1596        while (tables.hasNext()) { 1597            table = (Table) tables.next(); 1598 1599            if (table.isView() ||!isAccessibleTable(table) 1600                    ||!table.hasPrimaryKey()) { 1601                continue; 1602            } 1603 1604            index = table.getPrimaryIndex(); 1605 1606            ti.setTable(table); 1607 1608            tableCatalog = ns.getCatalogName(table); 1609            tableSchema = table.getSchemaName(); 1610            tableName = ti.getName(); 1611            primaryKeyName = index.getName().name; 1612            cols = index.getColumns(); 1613            colCount = cols.length; 1614 1615            for (int j = 0; j < colCount; j++) { 1616                row = t.getEmptyRowData(); 1617                row[itable_cat] = tableCatalog; 1618                row[itable_schem] = tableSchema; 1619                row[itable_name] = tableName; 1620                row[icolumn_name] = ti.getColName(cols[j]); 1621                row[ikey_seq] = ValuePool.getInt(j + 1); 1622                row[ipk_name] = primaryKeyName; 1623 1624                t.insertSys(row); 1625            } 1626        } 1627 1628        t.setDataReadOnly(true); 1629 1630        return t; 1631    } 1632 1633    /** 1634     * Retrieves a <code>Table</code> object describing the 1635     * return, parameter and result columns of the accessible 1636     * routines defined within this database.<p> 1637     * 1638     * Each row is a procedure column description with the following 1639     * columns: <p> 1640     * 1641     * <pre class="SqlCodeExample"> 1642     * PROCEDURE_CAT VARCHAR routine catalog 1643     * PROCEDURE_SCHEM VARCHAR routine schema 1644     * PROCEDURE_NAME VARCHAR routine name 1645     * COLUMN_NAME VARCHAR column/parameter name 1646     * COLUMN_TYPE SMALLINT kind of column/parameter 1647     * DATA_TYPE SMALLINT SQL type from DITypes 1648     * TYPE_NAME VARCHAR SQL type name 1649     * PRECISION INTEGER precision (length) of type 1650     * LENGTH INTEGER transfer size, in bytes, if definitely known 1651     * (roughly equivalent to BUFFER_SIZE for table 1652     * columns) 1653     * SCALE SMALLINT scale 1654     * RADIX SMALLINT radix 1655     * NULLABLE SMALLINT can column contain NULL? 1656     * REMARKS VARCHAR explanatory comment on column 1657     * SPECIFIC_NAME VARCHAR typically (but not restricted to) a 1658     * fully qualified Java Method name and signature 1659     * SEQ INTEGER The JDBC-specified order within 1660     * runs of PROCEDURE_SCHEM, PROCEDURE_NAME, 1661     * SPECIFIC_NAME, which is: 1662     * 1663     * return value (0), if any, first, followed 1664     * by the parameter descriptions in call order 1665     * (1..n1), followed by the result column 1666     * descriptions in column number order 1667     * (n1 + 1..n1 + n2) 1668     * </pre> <p> 1669     * 1670     * @return a <code>Table</code> object describing the 1671     * return, parameter and result columns 1672     * of the accessible routines defined 1673     * within this database. 1674     * @throws HsqlException if an error occurs while producing the table 1675     */ 1676    Table SYSTEM_PROCEDURECOLUMNS() throws HsqlException { 1677 1678        Table t = sysTables[SYSTEM_PROCEDURECOLUMNS]; 1679 1680        if (t == null) { 1681            t = createBlankTable(sysTableHsqlNames[SYSTEM_PROCEDURECOLUMNS]); 1682 1683            // ---------------------------------------------------------------- 1684 // required 1685 // ---------------------------------------------------------------- 1686 addColumn(t, "PROCEDURE_CAT", Types.VARCHAR); 1687            addColumn(t, "PROCEDURE_SCHEM", Types.VARCHAR); 1688            addColumn(t, "PROCEDURE_NAME", Types.VARCHAR, false); // not null 1689 addColumn(t, "COLUMN_NAME", Types.VARCHAR, false); // not null 1690 addColumn(t, "COLUMN_TYPE", Types.SMALLINT, false); // not null 1691 addColumn(t, "DATA_TYPE", Types.SMALLINT, false); // not null 1692 addColumn(t, "TYPE_NAME", Types.VARCHAR, 32, false); // not null 1693 addColumn(t, "PRECISION", Types.INTEGER); 1694            addColumn(t, "LENGTH", Types.INTEGER); 1695            addColumn(t, "SCALE", Types.SMALLINT); 1696            addColumn(t, "RADIX", Types.SMALLINT); 1697            addColumn(t, "NULLABLE", Types.SMALLINT, false); // not null 1698 addColumn(t, "REMARKS", Types.VARCHAR); 1699 1700            // ---------------------------------------------------------------- 1701 // extended (and required for JDBC sort contract w.r.t. overloading) 1702 // ---------------------------------------------------------------- 1703 addColumn(t, "SPECIFIC_NAME", Types.VARCHAR, false); // not null 1704 1705            // ---------------------------------------------------------------- 1706 // just required for JDBC sort contract 1707 // ---------------------------------------------------------------- 1708 addColumn(t, "SEQ", Types.INTEGER, false); // not null 1709 1710            // ---------------------------------------------------------------- 1711 // order: PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME, SEQ 1712 // added for unique: PROCEDURE_CAT 1713 // false PK, as PROCEDURE_SCHEM and/or PROCEDURE_CAT may be null 1714 t.createPrimaryKey(null, new int[] { 1715                1, 2, 13, 14, 0 1716            }, false); 1717 1718            return t; 1719        } 1720 1721        t.setDataReadOnly(true); 1722 1723        return t; 1724    } 1725 1726    /** 1727     * Retrieves a <code>Table</code> object describing the accessible 1728     * routines defined within this database. 1729     * 1730     * Each row is a procedure description with the following 1731     * columns: <p> 1732     * 1733     * <pre class="SqlCodeExample"> 1734     * PROCEDURE_CAT VARCHAR catalog in which routine is defined 1735     * PROCEDURE_SCHEM VARCHAR schema in which routine is defined 1736     * PROCEDURE_NAME VARCHAR simple routine identifier 1737     * NUM_INPUT_PARAMS INTEGER number of input parameters 1738     * NUM_OUTPUT_PARAMS INTEGER number of output parameters 1739     * NUM_RESULT_SETS INTEGER number of result sets returned 1740     * REMARKS VARCHAR explanatory comment on the routine 1741     * PROCEDURE_TYPE SMALLINT { Unknown | No Result | Returns Result } 1742     * ORIGIN VARCHAR {ALIAS | 1743     * [BUILTIN | USER DEFINED] ROUTINE | 1744     * [BUILTIN | USER DEFINED] TRIGGER | 1745     * ...} 1746     * SPECIFIC_NAME VARCHAR typically (but not restricted to) a 1747     * fully qualified Java Method name 1748     * and signature 1749     * </pre> <p> 1750     * 1751     * @return a <code>Table</code> object describing the accessible 1752     * routines defined within the this database 1753     * @throws HsqlException if an error occurs while producing the table 1754     */ 1755    Table SYSTEM_PROCEDURES() throws HsqlException { 1756 1757        Table t = sysTables[SYSTEM_PROCEDURES]; 1758 1759        if (t == null) { 1760            t = createBlankTable(sysTableHsqlNames[SYSTEM_PROCEDURES]); 1761 1762            // ---------------------------------------------------------------- 1763 // required 1764 // ---------------------------------------------------------------- 1765 addColumn(t, "PROCEDURE_CAT", Types.VARCHAR); 1766            addColumn(t, "PROCEDURE_SCHEM", Types.VARCHAR); 1767            addColumn(t, "PROCEDURE_NAME", Types.VARCHAR, false); // not null 1768 addColumn(t, "NUM_INPUT_PARAMS", Types.INTEGER); 1769            addColumn(t, "NUM_OUTPUT_PARAMS", Types.INTEGER); 1770            addColumn(t, "NUM_RESULT_SETS", Types.INTEGER); 1771            addColumn(t, "REMARKS", Types.VARCHAR); 1772 1773            // basically: function (returns result), procedure (no return value) 1774 // or unknown (say, a trigger callout routine) 1775 addColumn(t, "PROCEDURE_TYPE", Types.SMALLINT, false); // not null 1776 1777            // ---------------------------------------------------------------- 1778 // extended 1779 // ---------------------------------------------------------------- 1780 addColumn(t, "ORIGIN", Types.VARCHAR, 32, false); // not null 1781 addColumn(t, "SPECIFIC_NAME", Types.VARCHAR, false); // not null 1782 1783            // ---------------------------------------------------------------- 1784 // order: PROCEDURE_SCHEM and PROCEDURE_NAME. 1785 // added for uniqe: SPECIFIC_NAME, PROCEDURE_CAT 1786 // false PK, as PROCEDURE_SCHEM and/or PROCEDURE_CAT may be null 1787 t.createPrimaryKey(null, new int[] { 1788                1, 2, 9, 0 1789            }, false); 1790 1791            return t; 1792        } 1793 1794        t.setDataReadOnly(true); 1795 1796        return t; 1797    } 1798 1799    /** 1800     * Retrieves a <code>Table</code> object describing the accessible schemas 1801     * defined within this database. <p> 1802     * 1803     * Each row is a schema description with the following 1804     * columns: <p> 1805     * 1806     * <pre class="SqlCodeExample"> 1807     * TABLE_SCHEM VARCHAR simple schema name 1808     * TABLE_CATALOG VARCHAR catalog in which schema is defined 1809     * IS_DEFAULT BOOLEAN is the schema the default for new sessions 1810     * </pre> <p> 1811     * 1812     * @return table containing information about schemas defined 1813     * within this database 1814     * @throws HsqlException if an error occurs while producing the table 1815     */ 1816    final Table SYSTEM_SCHEMAS() throws HsqlException { 1817 1818        Table t = sysTables[SYSTEM_SCHEMAS]; 1819 1820        if (t == null) { 1821            t = createBlankTable(sysTableHsqlNames[SYSTEM_SCHEMAS]); 1822 1823            addColumn(t, "TABLE_SCHEM", Types.VARCHAR, false); // not null 1824 addColumn(t, "TABLE_CATALOG", Types.VARCHAR); 1825            addColumn(t, "IS_DEFAULT", Types.BOOLEAN); 1826 1827            // order: TABLE_SCHEM 1828 // true PK, as rows never have null TABLE_SCHEM 1829 t.createPrimaryKey(null, new int[]{ 0 }, true); 1830 1831            return t; 1832        } 1833 1834        Iterator schemas; 1835        Object [] row; 1836 1837        // Initialization 1838 schemas = database.schemaManager.fullSchemaNamesIterator(); 1839 1840        String defschema = database.schemaManager.getDefaultSchemaName(); 1841 1842        // Do it. 1843 while (schemas.hasNext()) { 1844            row = t.getEmptyRowData(); 1845 1846            String schema = (String ) schemas.next(); 1847 1848            row[0] = schema; 1849            row[1] = ns.getCatalogName(row[0]); 1850            row[2] = schema.equals(defschema) ? Boolean.TRUE 1851                                              : Boolean.FALSE; 1852 1853            t.insertSys(row); 1854        } 1855 1856        t.setDataReadOnly(true); 1857 1858        return t; 1859    } 1860 1861    /** 1862     * Retrieves a <code>Table</code> object describing the visible access 1863     * rights for each accessible table definied within this database. <p> 1864     * 1865     * Each row is a table privilege description with the following columns: <p> 1866     * 1867     * <pre class="SqlCodeExample"> 1868     * TABLE_CAT VARCHAR table catalog 1869     * TABLE_SCHEM VARCHAR table schema 1870     * TABLE_NAME VARCHAR table name 1871     * GRANTOR VARCHAR grantor of access 1872     * GRANTEE VARCHAR grantee of access 1873     * PRIVILEGE VARCHAR { "SELECT" | "INSERT" | "UPDATE" | "DELETE" } 1874     * IS_GRANTABLE VARCHAR { "YES" | "NO" | NULL (unknown) } 1875     * </pre> 1876     * 1877     * <b>Note:</b> Up to and including HSQLDB 1.7.2, the access rights granted 1878     * on a table apply to all of the columns of that table as well. <p> 1879     * 1880     * @return a <code>Table</code> object describing the visible 1881     * access rights for each accessible table 1882     * defined within this database 1883     * @throws HsqlException if an error occurs while producing the table 1884     */ 1885    final Table SYSTEM_TABLEPRIVILEGES() throws HsqlException { 1886 1887        Table t = sysTables[SYSTEM_TABLEPRIVILEGES]; 1888 1889        if (t == null) { 1890            t = createBlankTable(sysTableHsqlNames[SYSTEM_TABLEPRIVILEGES]); 1891 1892            addColumn(t, "TABLE_CAT", Types.VARCHAR); 1893            addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 1894            addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // not null 1895 addColumn(t, "GRANTOR", Types.VARCHAR, false); // not null 1896 addColumn(t, "GRANTEE", Types.VARCHAR, false); // not null 1897 addColumn(t, "PRIVILEGE", Types.VARCHAR, 10, false); // not null 1898 addColumn(t, "IS_GRANTABLE", Types.VARCHAR, 3, false); // not null 1899 1900            // order: TABLE_SCHEM, TABLE_NAME, and PRIVILEGE, 1901 // added for unique: GRANTEE, GRANTOR, TABLE_CAT 1902 // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null 1903 t.createPrimaryKey(null, new int[] { 1904                1, 2, 5, 4, 3, 0 1905            }, false); 1906 1907            return t; 1908        } 1909 1910        // calculated column values 1911 String tableCatalog; 1912        String tableSchema; 1913        String tableName; 1914        String grantorName; 1915        String granteeName; 1916        String privilege; 1917        String isGrantable; 1918 1919        // intermediate holders 1920 HsqlArrayList users; 1921        User user; 1922        String [] tablePrivileges; 1923        Iterator tables; 1924        Table table; 1925        HsqlName accessKey; 1926        Object [] row; 1927 1928        // column number mappings 1929 final int itable_cat = 0; 1930        final int itable_schem = 1; 1931        final int itable_name = 2; 1932        final int igrantor = 3; 1933        final int igrantee = 4; 1934        final int iprivilege = 5; 1935        final int iis_grantable = 6; 1936 1937        // Initialization 1938 grantorName = GranteeManager.DBA_ADMIN_ROLE_NAME; 1939        users = database.getUserManager().listVisibleUsers(session, true); 1940        tables = allTables(); 1941 1942        // Do it. 1943 while (tables.hasNext()) { 1944            table = (Table) tables.next(); 1945            accessKey = table.getName(); 1946 1947            // Only show table grants if session user is admin, has some 1948 // right, or the special PUBLIC user has some right. 1949 if (!isAccessibleTable(table)) { 1950                continue; 1951            } 1952 1953            tableName = table.getName().name; 1954            tableCatalog = ns.getCatalogName(table); 1955            tableSchema = table.getSchemaName(); 1956 1957            for (int i = 0; i < users.size(); i++) { 1958                user = (User) users.get(i); 1959                granteeName = user.getName(); 1960 1961                if (user.isAdmin()) { 1962                    tablePrivileges = 1963                        UserManager.getRightsArray(UserManager.ALL); 1964                } else { 1965                    tablePrivileges = 1966                        user.listGrantedTablePrivileges(accessKey); 1967                } 1968 1969                isGrantable = (user.isAdmin()) ? "YES" 1970                                               : "NO"; 1971 1972                for (int j = 0; j < tablePrivileges.length; j++) { 1973                    privilege = (String ) tablePrivileges[j]; 1974                    row = t.getEmptyRowData(); 1975                    row[itable_cat] = tableCatalog; 1976                    row[itable_schem] = tableSchema; 1977                    row[itable_name] = tableName; 1978                    row[igrantor] = grantorName; 1979                    row[igrantee] = granteeName; 1980                    row[iprivilege] = privilege; 1981                    row[iis_grantable] = isGrantable; 1982 1983                    t.insertSys(row); 1984                } 1985            } 1986        } 1987 1988        t.setDataReadOnly(true); 1989 1990        return t; 1991    } 1992 1993    /** 1994     * Retrieves a <code>Table</code> object describing the accessible 1995     * tables defined within this database. <p> 1996     * 1997     * Each row is a table description with the following columns: <p> 1998     * 1999     * <pre class="SqlCodeExample"> 2000     * TABLE_CAT VARCHAR table catalog 2001     * TABLE_SCHEM VARCHAR table schema 2002     * TABLE_NAME VARCHAR table name 2003     * TABLE_TYPE VARCHAR {"TABLE" | "VIEW" | 2004     * "SYSTEM TABLE" | "GLOBAL TEMPORARY"} 2005     * REMARKS VARCHAR comment on the table. 2006     * TYPE_CAT VARCHAR table type catalog (not implemented). 2007     * TYPE_SCHEM VARCHAR table type schema (not implemented). 2008     * TYPE_NAME VARCHAR table type name (not implemented). 2009     * SELF_REFERENCING_COL_NAME VARCHAR designated "identifier" column of 2010     * typed table (not implemented). 2011     * REF_GENERATION VARCHAR {"SYSTEM" | "USER" | 2012     * "DERIVED" | NULL } (not implemented) 2013     * HSQLDB_TYPE VARCHAR HSQLDB-specific type: 2014     * {"MEMORY" | "CACHED" | "TEXT" | ...} 2015     * READ_ONLY BOOLEAN TRUE if table is read-only, 2016     * else FALSE. 2017     * </pre> <p> 2018     * 2019     * @return a <code>Table</code> object describing the accessible 2020     * tables defined within this database 2021     * @throws HsqlException if an error occurs while producing the table 2022     */ 2023    final Table SYSTEM_TABLES() throws HsqlException { 2024 2025        Table t = sysTables[SYSTEM_TABLES]; 2026 2027        if (t == null) { 2028            t = createBlankTable(sysTableHsqlNames[SYSTEM_TABLES]); 2029 2030            // ------------------------------------------------------------- 2031 // required 2032 // ------------------------------------------------------------- 2033 addColumn(t, "TABLE_CAT", Types.VARCHAR); 2034            addColumn(t, "TABLE_SCHEM", Types.VARCHAR); 2035            addColumn(t, "TABLE_NAME", Types.VARCHAR, false); // not null 2036 addColumn(t, "TABLE_TYPE", Types.VARCHAR, 16, false); // not null 2037 addColumn(t, "REMARKS", Types.VARCHAR); 2038 2039            // ------------------------------------------------------------- 2040 // JDBC3 2041 // ------------------------------------------------------------- 2042 addColumn(t, "TYPE_CAT", Types.VARCHAR); 2043            addColumn(t, "TYPE_SCHEM", Types.VARCHAR); 2044            addColumn(t, "TYPE_NAME", Types.VARCHAR); 2045            addColumn(t, "SELF_REFERENCING_COL_NAME", Types.VARCHAR); 2046            addColumn(t, "REF_GENERATION", Types.VARCHAR); 2047 2048            // ------------------------------------------------------------- 2049 // extended 2050 // ------------------------------------------------------------ 2051 addColumn(t, "HSQLDB_TYPE", Types.VARCHAR, 6, true); 2052            addColumn(t, "READ_ONLY", Types.BOOLEAN, false); // not null 2053 2054            // ------------------------------------------------------------ 2055 // order TABLE_TYPE, TABLE_SCHEM and TABLE_NAME 2056 // added for unique: TABLE_CAT 2057 // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null 2058 t.createPrimaryKey(null, new int[] { 2059                3, 1, 2, 0 2060            }, false); 2061 2062            return t; 2063        } 2064 2065        // intermediate holders 2066 Iterator tables; 2067        Table table; 2068        Object [] row; 2069        HsqlName accessKey; 2070        DITableInfo ti; 2071 2072        // column number mappings 2073 // jdbc 1 2074 final int itable_cat = 0; 2075        final int itable_schem = 1; 2076        final int itable_name = 2; 2077        final int itable_type = 3; 2078        final int iremark = 4; 2079 2080        // jdbc 3 2081 final int itype_cat = 5; 2082        final int itype_schem = 6; 2083        final int itype_name = 7; 2084        final int isref_cname = 8; 2085        final int iref_gen = 9; 2086 2087        // hsqldb ext 2088 final int ihsqldb_type = 10; 2089        final int iread_only = 11; 2090 2091        // Initialization 2092 tables = allTables(); 2093        ti = new DITableInfo(); 2094 2095        // Do it. 2096 while (tables.hasNext()) { 2097            table = (Table) tables.next(); 2098 2099            if (!isAccessibleTable(table)) { 2100                continue; 2101            } 2102 2103            ti.setTable(table); 2104 2105            row = t.getEmptyRowData(); 2106            row[itable_cat] = ns.getCatalogName(table); 2107            row[itable_schem] = table.getSchemaName(); 2108            row[itable_name] = ti.getName(); 2109            row[itable_type] = ti.getStandardType(); 2110            row[iremark] = ti.getRemark(); 2111            row[ihsqldb_type] = ti.getHsqlType(); 2112            row[iread_only] = ti.isReadOnly(); 2113 2114            t.insertSys(row); 2115        } 2116 2117        t.setDataReadOnly(true); 2118 2119        return t; 2120    } 2121 2122    /** 2123     * Retrieves a <code>Table</code> object describing the table types 2124     * available in this database. <p> 2125     * 2126     * In general, the range of values that may be commonly encounted across 2127     * most DBMS implementations is: <p> 2128     * 2129     * <UL> 2130     * <LI><FONT color='#FF00FF'>"TABLE"</FONT> 2131     * <LI><FONT color='#FF00FF'>"VIEW"</FONT> 2132     * <LI><FONT color='#FF00FF'>"SYSTEM TABLE"</FONT> 2133     * <LI><FONT color='#FF00FF'>"GLOBAL TEMPORARY"</FONT> 2134     * <LI><FONT color='#FF00FF'>"LOCAL TEMPORARY"</FONT> 2135     * <LI><FONT color='#FF00FF'>"ALIAS"</FONT> 2136     * <LI><FONT color='#FF00FF'>"SYNONYM"</FONT> 2137     * </UL> <p> 2138     * 2139     * As of HSQLDB 1.7.2, the engine supports and thus this method reports 2140     * only a subset of the range above: <p> 2141     * 2142     * <UL> 2143     * <LI><FONT color='#FF00FF'>"TABLE"</FONT> 2144     * (HSQLDB MEMORY, CACHED and TEXT tables) 2145     * <LI><FONT color='#FF00FF'>"VIEW"</FONT> (Views) 2146     * <LI><FONT color='#FF00FF'>"SYSTEM TABLE"</FONT> 2147     * (The tables generated by this object) 2148     * <LI><FONT color='#FF00FF'>"GLOBAL TEMPORARY"</FONT> 2149     * (HSQLDB TEMP and TEMP TEXT tables) 2150     * </UL> <p> 2151     * 2152     * @return a <code>Table</code> object describing the table types 2153     * available in this database 2154     * @throws HsqlException if an error occurs while producing the table 2155     */ 2156    Table SYSTEM_TABLETYPES() throws HsqlException { 2157 2158        Table t = sysTables[SYSTEM_TABLETYPES]; 2159 2160        if (t == null) { 2161            t = createBlankTable(sysTableHsqlNames[SYSTEM_TABLETYPES]); 2162 2163            addColumn(t, "TABLE_TYPE", Types.VARCHAR, 16, false); // not null 2164 2165            // order: TABLE_TYPE 2166 // true PK 2167 t.createPrimaryKey(null, new int[]{ 0 }, true); 2168 2169            return t; 2170        } 2171 2172        Object [] row; 2173 2174        for (int i = 0; i < tableTypes.length; i++) { 2175            row = t.getEmptyRowData(); 2176            row[0] = tableTypes[i]; 2177 2178            t.insertSys(row); 2179        } 2180 2181        t.setDataReadOnly(true); 2182 2183        return t; 2184    } 2185 2186    /** 2187     * Retrieves a <code>Table</code> object describing the 2188     * result expected by the JDBC DatabaseMetaData interface implementation 2189     * for system-defined SQL types supported as table columns. 2190     * 2191     * <pre class="SqlCodeExample"> 2192     * TYPE_NAME VARCHAR the canonical name for DDL statements. 2193     * DATA_TYPE SMALLINT data type code from DITypes. 2194     * PRECISION INTEGER max column size. 2195     * number => max precision. 2196     * character => max characters. 2197     * datetime => max chars incl. frac. component. 2198     * LITERAL_PREFIX VARCHAR char(s) prefixing literal of this type. 2199     * LITERAL_SUFFIX VARCHAR char(s) terminating literal of this type. 2200     * CREATE_PARAMS VARCHAR Localized syntax-order list of domain 2201     * create parameter keywords. 2202     * - for human consumption only 2203     * NULLABLE SMALLINT {No Nulls | Nullable | Unknown} 2204     * CASE_SENSITIVE BOOLEAN case-sensitive in collations/comparisons? 2205     * SEARCHABLE SMALLINT {None | Char (Only WHERE .. LIKE) | 2206     * Basic (Except WHERE .. LIKE) | 2207     * Searchable (All forms)} 2208     * UNSIGNED_ATTRIBUTE BOOLEAN {TRUE (unsigned) | FALSE (signed) | 2209     * NULL (non-numeric or not applicable)} 2210     * FIXED_PREC_SCALE BOOLEAN {TRUE (fixed) | FALSE (variable) | 2211     * NULL (non-numeric or not applicable)} 2212     * AUTO_INCREMENT BOOLEAN automatic unique value generated for 2213     * inserts and updates when no value or 2214     * NULL specified? 2215     * LOCAL_TYPE_NAME VARCHAR localized name of data type; 2216     * - NULL if not supported. 2217     * - for human consuption only 2218     * MINIMUM_SCALE SMALLINT minimum scale supported. 2219     * MAXIMUM_SCALE SMALLINT maximum scale supported. 2220     * SQL_DATA_TYPE INTEGER value expected in SQL CLI SQL_DESC_TYPE 2221     * field of the SQLDA. 2222     * SQL_DATETIME_SUB INTEGER SQL CLI datetime/interval subcode. 2223     * NUM_PREC_RADIX INTEGER numeric base w.r.t # of digits reported in 2224     * PRECISION column (typically 10). 2225     * TYPE_SUB INTEGER From DITypes: 2226     * {TYPE_SUB_DEFAULT | TYPE_SUB_IDENTITY | 2227     * TYPE_SUB_IGNORECASE} 2228     * </pre> <p> 2229     * 2230     * @return a <code>Table</code> object describing the 2231     * system-defined SQL types supported as table columns 2232     * @throws HsqlException if an error occurs while producing the table 2233     */ 2234    final Table SYSTEM_TYPEINFO() throws HsqlException { 2235 2236        Table t = sysTables[SYSTEM_TYPEINFO]; 2237 2238        if (t == null) { 2239            t = createBlankTable(sysTableHsqlNames[SYSTEM_TYPEINFO]); 2240 2241            //------------------------------------------- 2242 // required by JDBC: 2243 // ------------------------------------------ 2244 addColumn(t, "TYPE_NAME", Types.VARCHAR, 32, false); 2245            addColumn(t, "DATA_TYPE", Types.SMALLINT, false); 2246            addColumn(t, "PRECISION", Types.INTEGER); 2247            addColumn(t, "LITERAL_PREFIX", Types.VARCHAR, 2, true); 2248            addColumn(t, "LITERAL_SUFFIX", Types.VARCHAR, 2, true); 2249            addColumn(t, "CREATE_PARAMS", Types.VARCHAR); 2250            addColumn(t, "NULLABLE", Types.SMALLINT); 2251            addColumn(t, "CASE_SENSITIVE", Types.BOOLEAN); 2252            addColumn(t, "SEARCHABLE", Types.SMALLINT); 2253            addColumn(t, "UNSIGNED_ATTRIBUTE", Types.BOOLEAN); 2254            addColumn(t, "FIXED_PREC_SCALE", Types.BOOLEAN); 2255            addColumn(t, "AUTO_INCREMENT", Types.BOOLEAN); 2256            addColumn(t, "LOCAL_TYPE_NAME", Types.VARCHAR); 2257            addColumn(t, "MINIMUM_SCALE", Types.SMALLINT); 2258            addColumn(t, "MAXIMUM_SCALE", Types.SMALLINT); 2259            addColumn(t, "SQL_DATA_TYPE", Types.INTEGER); 2260            addColumn(t, "SQL_DATETIME_SUB", Types.INTEGER); 2261            addColumn(t, "NUM_PREC_RADIX", Types.INTEGER); 2262 2263            //------------------------------------------- 2264 // for JDBC sort contract: 2265 //------------------------------------------- 2266 addColumn(t, "TYPE_SUB", Types.INTEGER); 2267 2268            // order: DATA_TYPE, TYPE_SUB 2269 // true PK 2270 t.createPrimaryKey(null, new int[] { 2271                1, 18 2272            }, true); 2273 2274            return t; 2275        } 2276 2277        Result rs; 2278 2279        rs = session.sqlExecuteDirectNoPreChecks( 2280            "select TYPE_NAME, DATA_TYPE, PRECISION, LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, NULLABLE, CASE_SENSITIVE, SEARCHABLE," 2281            + "UNSIGNED_ATTRIBUTE, FIXED_PREC_SCALE, AUTO_INCREMENT, LOCAL_TYPE_NAME, MINIMUM_SCALE, " 2282            + "MAXIMUM_SCALE, SQL_DATA_TYPE, SQL_DATETIME_SUB, NUM_PREC_RADIX, TYPE_SUB " 2283            + "from INFORMATION_SCHEMA.SYSTEM_ALLTYPEINFO where AS_TAB_COL = true;"); 2284 2285        t.insertSys(rs); 2286        t.setDataReadOnly(true); 2287 2288        return t; 2289    } 2290 2291    /** 2292     * Retrieves a <code>Table</code> object describing, in an extended 2293     * fashion, all of the system or formal specification SQL types known to 2294     * this database, including its level of support for them (which may 2295     * be no support at all) in various capacities. <p> 2296     * 2297     * <pre class="SqlCodeExample"> 2298     * TYPE_NAME VARCHAR the canonical name used in DDL statements. 2299     * DATA_TYPE SMALLINT data type code from Types 2300     * PRECISION INTEGER max column size. 2301     * number => max. precision. 2302     * character => max characters. 2303     * datetime => max chars incl. frac. component. 2304     * LITERAL_PREFIX VARCHAR char(s) prefixing literal of this type. 2305     * LITERAL_SUFFIX VARCHAR char(s) terminating literal of this type. 2306     * CREATE_PARAMS VARCHAR Localized syntax-order list of domain 2307     * create parameter keywords. 2308     * - for human consumption only 2309     * NULLABLE SMALLINT { No Nulls | Nullable | Unknown } 2310     * CASE_SENSITIVE BOOLEAN case-sensitive in collations/comparisons? 2311     * SEARCHABLE SMALLINT { None | Char (Only WHERE .. LIKE) | 2312     * Basic (Except WHERE .. LIKE) | 2313     * Searchable (All forms) } 2314     * UNSIGNED_ATTRIBUTE BOOLEAN { TRUE (unsigned) | FALSE (signed) | 2315     * NULL (non-numeric or not applicable) } 2316     * FIXED_PREC_SCALE BOOLEAN { TRUE (fixed) | FALSE (variable) | 2317     * NULL (non-numeric or not applicable) } 2318     * AUTO_INCREMENT BOOLEAN automatic unique value generated for 2319     * inserts and updates when no value or 2320     * NULL specified? 2321     * LOCAL_TYPE_NAME VARCHAR Localized name of data type; 2322     * - NULL => not supported (no resource avail). 2323     * - for human consumption only 2324     * MINIMUM_SCALE SMALLINT minimum scale supported. 2325     * MAXIMUM_SCALE SMALLINT maximum scale supported. 2326     * SQL_DATA_TYPE INTEGER value expected in SQL CLI SQL_DESC_TYPE 2327     * field of the SQLDA. 2328     * SQL_DATETIME_SUB INTEGER SQL CLI datetime/interval subcode 2329     * NUM_PREC_RADIX INTEGER numeric base w.r.t # of digits reported 2330     * in PRECISION column (typically 10) 2331     * INTERVAL_PRECISION INTEGER interval leading precision (not implemented) 2332     * AS_TAB_COL BOOLEAN type supported as table column? 2333     * AS_PROC_COL BOOLEAN type supported as procedure column? 2334     * MAX_PREC_ACT BIGINT like PRECISION unless value would be 2335     * truncated using INTEGER 2336     * MIN_SCALE_ACT INTEGER like MINIMUM_SCALE unless value would be 2337     * truncated using SMALLINT 2338     * MAX_SCALE_ACT INTEGER like MAXIMUM_SCALE unless value would be 2339     * truncated using SMALLINT 2340     * COL_ST_CLS_NAME VARCHAR Java Class FQN of in-memory representation 2341     * COL_ST_IS_SUP BOOLEAN is COL_ST_CLS_NAME supported under the 2342     * hosting JVM and engine build option? 2343     * STD_MAP_CLS_NAME VARCHAR Java class FQN of standard JDBC mapping 2344     * STD_MAP_IS_SUP BOOLEAN Is STD_MAP_CLS_NAME supported under the 2345     * hosting JVM? 2346     * CST_MAP_CLS_NAME VARCHAR Java class FQN of HSQLDB-provided JDBC 2347     * interface representation 2348     * CST_MAP_IS_SUP BOOLEAN is CST_MAP_CLS_NAME supported under the 2349     * hosting JVM and engine build option? 2350     * MCOL_JDBC INTEGER maximum character octet length representable 2351     * via JDBC interface 2352     * MCOL_ACT BIGINT like MCOL_JDBC unless value would be 2353     * truncated using INTEGER 2354     * DEF_OR_FIXED_SCALE INTEGER default or fixed scale for numeric types 2355     * REMARKS VARCHAR localized comment on the data type 2356     * TYPE_SUB INTEGER From Types: 2357     * {TYPE_SUB_DEFAULT | TYPE_SUB_IGNORECASE} 2358     * deprecated: TYPE_SUB_IDENTITY 2359     * </pre> <p> 2360     * 2361     * @return a <code>Table</code> object describing all of the 2362     * standard SQL types known to this database 2363     * @throws HsqlException if an error occurs while producing the table 2364     */ 2365    final Table SYSTEM_ALLTYPEINFO() throws HsqlException { 2366 2367        Table t = sysTables[SYSTEM_ALLTYPEINFO]; 2368 2369        if (t == null) { 2370            t = createBlankTable(sysTableHsqlNames[SYSTEM_ALLTYPEINFO]); 2371 2372            //------------------------------------------- 2373 // same as SYSTEM_TYPEINFO: 2374 // ------------------------------------------ 2375 addColumn(t, "TYPE_NAME", Types.VARCHAR, 32, false); 2376            addColumn(t, "DATA_TYPE", Types.SMALLINT, false); 2377            addColumn(t, "PRECISION", Types.INTEGER); 2378            addColumn(t, "LITERAL_PREFIX", Types.VARCHAR, 2, true); 2379            addColumn(t, "LITERAL_SUFFIX", Types.VARCHAR, 2, true); 2380            addColumn(t, "CREATE_PARAMS", Types.VARCHAR); 2381            addColumn(t, "NULLABLE", Types.SMALLINT); 2382            addColumn(t, "CASE_SENSITIVE", Types.BOOLEAN); 2383            addColumn(t, "SEARCHABLE", Types.SMALLINT); 2384            addColumn(t, "UNSIGNED_ATTRIBUTE", Types.BOOLEAN); 2385            addColumn(t, "FIXED_PREC_SCALE", Types.BOOLEAN); 2386            addColumn(t, "AUTO_INCREMENT", Types.BOOLEAN); 2387            addColumn(t, "LOCAL_TYPE_NAME", Types.VARCHAR); 2388            addColumn(t, "MINIMUM_SCALE", Types.SMALLINT); 2389            addColumn(t, "MAXIMUM_SCALE", Types.SMALLINT); 2390            addColumn(t, "SQL_DATA_TYPE", Types.INTEGER); 2391            addColumn(t, "SQL_DATETIME_SUB", Types.INTEGER); 2392            addColumn(t, "NUM_PREC_RADIX", Types.INTEGER); 2393 2394            //------------------------------------------- 2395 // SQL CLI / ODBC - not in JDBC spec 2396 // ------------------------------------------ 2397 addColumn(t, "INTERVAL_PRECISION", Types.INTEGER); 2398 2399            //------------------------------------------- 2400 // extended: 2401 //------------------------------------------- 2402 // level of support 2403 //------------------------------------------- 2404 addColumn(t, "AS_TAB_COL", Types.BOOLEAN); 2405 2406            // for instance, some executable methods take Connection 2407 // or return non-serializable Object such as ResultSet, neither 2408 // of which maps to a supported table column type but which 2409 // we show as JAVA_OBJECT in SYSTEM_PROCEDURECOLUMNS. 2410 // Also, triggers take Object[] row, which we show as ARRAY 2411 // presently, although STRUCT would probably be better in the 2412 // future, as the row can actually contain mixed data types. 2413 addColumn(t, "AS_PROC_COL", Types.BOOLEAN); 2414 2415            //------------------------------------------- 2416 // actual values for attributes that cannot be represented 2417 // within the limitations of the SQL CLI / JDBC interface 2418 //------------------------------------------- 2419 addColumn(t, "MAX_PREC_ACT", Types.BIGINT); 2420            addColumn(t, "MIN_SCALE_ACT", Types.INTEGER); 2421            addColumn(t, "MAX_SCALE_ACT", Types.INTEGER); 2422 2423            //------------------------------------------- 2424 // how do we store this internally as a column value? 2425 //------------------------------------------- 2426 addColumn(t, "COL_ST_CLS_NAME", Types.VARCHAR); 2427            addColumn(t, "COL_ST_IS_SUP", Types.BOOLEAN); 2428 2429            //------------------------------------------- 2430 // what is the standard Java mapping for the type? 2431 //------------------------------------------- 2432 addColumn(t, "STD_MAP_CLS_NAME", Types.VARCHAR); 2433            addColumn(t, "STD_MAP_IS_SUP", Types.BOOLEAN); 2434 2435            //------------------------------------------- 2436 // what, if any, custom mapping do we provide? 2437 // (under the current build options and hosting VM) 2438 //------------------------------------------- 2439 addColumn(t, "CST_MAP_CLS_NAME", Types.VARCHAR); 2440            addColumn(t, "CST_MAP_IS_SUP", Types.BOOLEAN); 2441 2442            //------------------------------------------- 2443 // what is the max representable and actual 2444 // character octet length, if applicable? 2445 //------------------------------------------- 2446 addColumn(t, "MCOL_JDBC", Types.INTEGER); 2447            addColumn(t, "MCOL_ACT", Types.BIGINT); 2448 2449            //------------------------------------------- 2450 // what is the default or fixed scale, if applicable? 2451 //------------------------------------------- 2452 addColumn(t, "DEF_OR_FIXED_SCALE", Types.INTEGER); 2453 2454            //------------------------------------------- 2455 // Any type-specific, localized remarks can go here 2456 //------------------------------------------- 2457 addColumn(t, "REMARKS", Types.VARCHAR); 2458 2459            //------------------------------------------- 2460 // required for JDBC sort contract: 2461 //------------------------------------------- 2462 addColumn(t, "TYPE_SUB", Types.INTEGER); 2463 2464            // order: DATA_TYPE, TYPE_SUB 2465 // true primary key 2466 t.createPrimaryKey(null, new int[] { 2467                1, 34 2468            }, true); 2469 2470            return t; 2471        } 2472 2473        Object [] row; 2474        int type; 2475        DITypeInfo ti; 2476 2477        //----------------------------------------- 2478 // Same as SYSTEM_TYPEINFO 2479 //----------------------------------------- 2480 final int itype_name = 0; 2481        final int idata_type = 1; 2482        final int iprecision = 2; 2483        final int iliteral_prefix = 3; 2484        final int iliteral_suffix = 4; 2485        final int icreate_params = 5; 2486        final int inullable = 6; 2487        final int icase_sensitive = 7; 2488        final int isearchable = 8; 2489        final int iunsigned_attribute = 9; 2490        final int ifixed_prec_scale = 10; 2491        final int iauto_increment = 11; 2492        final int ilocal_type_name = 12; 2493        final int iminimum_scale = 13; 2494        final int imaximum_scale = 14; 2495        final int isql_data_type = 15; 2496        final int isql_datetime_sub = 16; 2497        final int inum_prec_radix = 17; 2498 2499        //------------------------------------------ 2500 // Extentions 2501 //------------------------------------------ 2502 // not in JDBC, but in SQL CLI SQLDA / ODBC 2503 //------------------------------------------ 2504 final int iinterval_precision = 18; 2505 2506        //------------------------------------------ 2507 // HSQLDB/Java-specific: 2508 //------------------------------------------ 2509 final int iis_sup_as_tcol = 19; 2510        final int iis_sup_as_pcol = 20; 2511 2512        //------------------------------------------ 2513 final int imax_prec_or_len_act = 21; 2514        final int imin_scale_actual = 22; 2515        final int imax_scale_actual = 23; 2516 2517        //------------------------------------------ 2518 final int ics_cls_name = 24; 2519        final int ics_cls_is_supported = 25; 2520 2521        //------------------------------------------ 2522 final int ism_cls_name = 26; 2523        final int ism_cls_is_supported = 27; 2524 2525        //------------------------------------------ 2526 final int icm_cls_name = 28; 2527        final int icm_cls_is_supported = 29; 2528 2529        //------------------------------------------ 2530 final int imax_char_oct_len_jdbc = 30; 2531        final int imax_char_oct_len_act = 31; 2532 2533        //------------------------------------------ 2534 final int idef_or_fixed_scale = 32; 2535 2536        //------------------------------------------ 2537 final int iremarks = 33; 2538 2539        //------------------------------------------ 2540 final int itype_sub = 34; 2541 2542        ti = new DITypeInfo(); 2543 2544        for (int i = 0; i < Types.ALL_TYPES.length; i++) { 2545            ti.setTypeCode(Types.ALL_TYPES[i][0]); 2546            ti.setTypeSub(Types.ALL_TYPES[i][1]); 2547 2548            row = t.getEmptyRowData(); 2549            row[itype_name] = ti.getTypeName(); 2550            row[idata_type] = ti.getDataType(); 2551            row[iprecision] = ti.getPrecision(); 2552            row[iliteral_prefix] = ti.getLiteralPrefix(); 2553            row[iliteral_suffix] = ti.getLiteralSuffix(); 2554            row[icreate_params] = ti.getCreateParams(); 2555            row[inullable] = ti.getNullability(); 2556            row[icase_sensitive] = ti.isCaseSensitive(); 2557            row[isearchable] = ti.getSearchability(); 2558            row[iunsigned_attribute] = ti.isUnsignedAttribute(); 2559            row[ifixed_prec_scale] = ti.isFixedPrecisionScale(); 2560            row[iauto_increment] = ti.isAutoIncrement(); 2561            row[ilocal_type_name] = ti.getLocalName(); 2562            row[iminimum_scale] = ti.getMinScale(); 2563            row[imaximum_scale] = ti.getMaxScale(); 2564            row[isql_data_type] = ti.getSqlDataType(); 2565            row[isql_datetime_sub] = ti.getSqlDateTimeSub(); 2566            row[inum_prec_radix] = ti.getNumPrecRadix(); 2567 2568            //------------------------------------------ 2569 row[iinterval_precision] = ti.getIntervalPrecision(); 2570 2571            //------------------------------------------ 2572 row[iis_sup_as_tcol] = ti.isSupportedAsTCol(); 2573            row[iis_sup_as_pcol] = ti.isSupportedAsPCol(); 2574 2575            //------------------------------------------ 2576 row[imax_prec_or_len_act] = ti.getPrecisionAct(); 2577            row[imin_scale_actual] = ti.getMinScaleAct(); 2578            row[imax_scale_actual] = ti.getMaxScaleAct(); 2579 2580            //------------------------------------------ 2581 row[ics_cls_name] = ti.getColStClsName(); 2582            row[ics_cls_is_supported] = ti.isColStClsSupported(); 2583 2584            //------------------------------------------ 2585 row[ism_cls_name] = ti.getStdMapClsName(); 2586            row[ism_cls_is_supported] = ti.isStdMapClsSupported(); 2587 2588            //------------------------------------------ 2589 row[icm_cls_name] = ti.getCstMapClsName(); 2590 2591            try { 2592                ns.classForName((String ) row[icm_cls_name]); 2593 2594                row[icm_cls_is_supported] = Boolean.TRUE; 2595            } catch (Exception e) { 2596                row[icm_cls_is_supported] = Boolean.FALSE; 2597            } 2598 2599            //------------------------------------------ 2600 row[imax_char_oct_len_jdbc] = ti.getCharOctLen(); 2601            row[imax_char_oct_len_act] = ti.getCharOctLenAct(); 2602 2603            //------------------------------------------ 2604 row[idef_or_fixed_scale] = ti.getDefaultScale(); 2605 2606            //------------------------------------------ 2607 row[iremarks] = ti.getRemarks(); 2608 2609            //------------------------------------------ 2610 row[itype_sub] = ti.getDataTypeSub(); 2611 2612            t.insertSys(row); 2613        } 2614 2615        t.setDataReadOnly(true); 2616 2617        return t; 2618    } 2619 2620    /** 2621     * Retrieves a <code>Table</code> object describing the 2622     * visible <code>Users</code> defined within this database. 2623     * @return table containing information about the users defined within 2624     * this database 2625     * @throws HsqlException if an error occurs while producing the table 2626     */ 2627    Table SYSTEM_USERS() throws HsqlException { 2628 2629        Table t = sysTables[SYSTEM_USERS]; 2630 2631        if (t == null) { 2632            t = createBlankTable(sysTableHsqlNames[SYSTEM_USERS]); 2633 2634            addColumn(t, "USER", Types.VARCHAR, false); 2635            addColumn(t, "ADMIN", Types.BOOLEAN, false); 2636 2637            // order: USER 2638 // true PK 2639 t.createPrimaryKey(null, new int[]{ 0 }, true); 2640 2641            return t; 2642        } 2643 2644        // Intermediate holders 2645 HsqlArrayList users; 2646        User user; 2647        int userCount; 2648        Object [] row; 2649 2650        // Initialization 2651 users = database.getUserManager().listVisibleUsers(session, false); 2652 2653        // Do it. 2654 for (int i = 0; i < users.size(); i++) { 2655            row = t.getEmptyRowData(); 2656            user = (User) users.get(i); 2657            row[0] = user.getName(); 2658            row[1] = ValuePool.getBoolean(user.isAdmin()); 2659 2660            t.insertSys(row); 2661        } 2662 2663        t.setDataReadOnly(true); 2664 2665        return t; 2666    } 2667 2668    /** 2669     * The SYSTEM_CHECK_CONSTRAINTS table has one row for each domain 2670     * constraint, table check constraint, and assertion. <p> 2671     * 2672     * <b>Definition:</b><p> 2673     * 2674     * <pre class="SqlCodeExample"> 2675     * CREATE TABLE SYSTEM_CHECK_CONSTRAINTS ( 2676     * CONSTRAINT_CATALOG VARCHAR NULL, 2677     * CONSTRAINT_SCHEMA VARCHAR NULL, 2678     * CONSTRAINT_NAME VARCHAR NOT NULL, 2679     * CHECK_CLAUSE VARCHAR NOT NULL, 2680     * 2681     * UNIQUE ( CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME ) 2682     * ) 2683     * </pre> 2684     * 2685     * <b>Description:</b><p> 2686     * 2687     * <ol> 2688     * <li> The values of CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA and 2689     * CONSTRAINT_NAME are the catalog name, unqualified schema name, 2690     * and qualified identifier, respectively, of the constraint being 2691     * described. <p> 2692     * 2693     * <li> Case: <p> 2694     * 2695     * <table> 2696     * <tr> 2697     * <td valign="top" halign="left">a)</td> 2698     * <td> If the character representation of the 2699     * &lt;search condition&gt; contained in the 2700     * &lt;check constraint definition&gt;, 2701     * &lt;domain constraint definition&gt;, or 2702     * &lt;assertion definition&gt; that defined 2703     * the check constraint being described can be 2704     * represented without truncation, then the 2705     * value of CHECK_CLAUSE is that character 2706     * representation. </td> 2707     * </tr> 2708     * <tr> 2709     * <td align="top" halign="left">b)</td> 2710     * <td>Otherwise, the value of CHECK_CLAUSE is the 2711     * null value.</td> 2712     * </tr> 2713     * </table> 2714     * </ol> 2715     */ 2716    final Table SYSTEM_CHECK_CONSTRAINTS() throws HsqlException { 2717 2718        Table t = sysTables[SYSTEM_CHECK_CONSTRAINTS]; 2719 2720        if (t == null) { 2721            t = createBlankTable(sysTableHsqlNames[SYSTEM_CHECK_CONSTRAINTS]); 2722 2723            addColumn(t, "CONSTRAINT_CATALOG", Types.VARCHAR); 2724            addColumn(t, "CONSTRAINT_SCHEMA", Types.VARCHAR); 2725            addColumn(t, "CONSTRAINT_NAME", Types.VARCHAR, false); // not null 2726 addColumn(t, "CHECK_CLAUSE", Types.VARCHAR, false); // not null 2727 2728            // false PK, as FKTABLE_CAT, FKTABLE_SCHEM and/or FK_NAME 2729 // may be null 2730 t.createPrimaryKey(null, new int[] { 2731                0, 1, 2 2732            }, false); 2733 2734            return t; 2735        } 2736 2737        // calculated column values 2738 // Intermediate holders 2739 Iterator tables; 2740        Table table; 2741        Constraint[] tableConstraints; 2742        int constraintCount; 2743        Constraint constraint; 2744        HsqlArrayList constraintList; 2745        Object [] row; 2746 2747        // column number mappings 2748 final int icons_cat = 0; 2749        final int icons_schem = 1; 2750        final int icons_name = 2; 2751        final int icons_clause = 3; 2752 2753        tables = database.schemaManager.allTablesIterator(); 2754        constraintList = new HsqlArrayList(); 2755 2756        while (tables.hasNext()) { 2757            table = (Table) tables.next(); 2758 2759            if (!isAccessibleTable(table)) { 2760                continue; 2761            } 2762 2763            tableConstraints = table.getConstraints(); 2764            constraintCount = tableConstraints.length; 2765 2766            for (int i = 0; i < constraintCount; i++) { 2767                constraint = (Constraint) tableConstraints[i]; 2768 2769                if (constraint.getType() == Constraint.CHECK) { 2770                    constraintList.add(constraint); 2771                } 2772            } 2773        } 2774 2775        for (int i = 0; i < constraintList.size(); i++) { 2776            row = t.getEmptyRowData(); 2777            constraint = (Constraint) constraintList.get(i); 2778            table = constraint.getMain(); 2779            row[icons_cat] = ns.getCatalogName(table); 2780            row[icons_schem] = table.getSchemaName(); 2781            row[icons_name] = constraint.constName.name; 2782 2783            try { 2784                row[icons_clause] = constraint.core.check.getDDL(); 2785            } catch (Exception e) {} 2786 2787            t.insertSys(row); 2788        } 2789 2790        t.setDataReadOnly(true); 2791 2792        return t; 2793    } 2794 2795    /** 2796     * The SYSTEM_SEQUENCES table has one row for each external sequence 2797     * generator. <p> 2798     * 2799     * <b>Definition:</b> <p> 2800     * 2801     * <pre class="SqlCodeExample"> 2802     * CREATE TABLE SYSTEM_SEQUENCES ( 2803     * SEQUENCE_CATALOG VARCHAR NULL, 2804     * SEQUENCE_SCHEMA VARCHAR NULL, 2805     * SEQUENCE_NAME VARCHAR NOT NULL, 2806     * DTD_IDENTIFIER VARCHAR NOT NULL, 2807     * MAXIMUM_VALUE VARCHAR NOT NULL, 2808     * MINIMUM_VALUE VARCHAR NOT NULL, 2809     * INCREMENT VARCHAR NOT NULL, 2810     * CYCLE_OPTION VARCHAR NOT NULL, 2811     * START_WITH VARCHAR NOT NULL, 2812     * 2813     * CHECK(CYCLE_OPTION IN('YES', 'NO')), 2814     * 2815     * CHECK(CAST(START_WITH AS BIGINT) 2816     * BETWEEN CAST(MINIMUM_VALUE AS BIGINT) 2817     * AND CAST(MAXIMUM_VALUE AS BIGINT)), 2818     * 2819     * UNIQUE(SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME) 2820     * ) 2821     * </pre> 2822     * 2823     * <b>DESCRIPTION:</b><p> 2824     * 2825     * <ol> 2826     * <li> The values of SEQUENCE_CATALOG, SEQUENCE_SCHEMA, and 2827     * SEQUENCE_NAME are the catalog name, unqualified schema name, 2828     * and qualified identifier, respectively, of the sequence generator 2829     * being described. <p> 2830     * 2831     * <li> The values of SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME, and 2832     * DTD_IDENTIFIER are the values of OBJECT_CATALOG, OBJECT_SCHEMA, 2833     * OBJECT_NAME, and DTD_IDENTIFIER, respectively, of the row in 2834     * DATA_TYPE_DESCRIPTOR (not yet implemented) that describes the data 2835     * type of the sequence generator. <p> 2836     * 2837     * <li> The values of MAXIMUM_VALUE, MINIMUM_VALUE, and INCREMENT are the 2838     * character representations of maximum value, minimum value, 2839     * and increment, respectively, of the sequence generator being 2840     * described. <p> 2841     * 2842     * <li> The values of CYCLE_OPTION have the following meanings: <p> 2843     * 2844     * <table border cellpadding="3"> 2845     * <tr> 2846     * <td nowrap>YES</td> 2847     * <td nowrap>The cycle option of the sequence generator 2848     * is CYCLE.</td> 2849     * <tr> 2850     * <td nowrap>NO</td> 2851     * <td nowrap>The cycle option of the sequence generator is 2852     * NO CYCLE.</td> 2853     * </tr> 2854     * </table> <p> 2855     * 2856     * <li> The value of START_WITH is HSQLDB-specific (not in the SQL 200n 2857     * spec). <p> 2858     * 2859     * It is the character representation of the START WITH clause 2860     * value that would be required to recreate or ALTER RESET START WITH 2861     * the described SEQUENCE, such that its behaviour would be identical 2862     * to that exhibited at the instant this table is materialized in the 2863     * session context. <p> 2864     * 2865     * In short, this is the character representation of value that 2866     * would be generated by NEXT VALUE FOR at the instant this table 2867     * is materialized in session context. <p> 2868     * 2869     */ 2870    final Table SYSTEM_SEQUENCES() throws HsqlException { 2871 2872        Table t = sysTables[SYSTEM_SEQUENCES]; 2873 2874        if (t == null) { 2875            t = createBlankTable(sysTableHsqlNames[SYSTEM_SEQUENCES]); 2876 2877            addColumn(t, "SEQUENCE_CATALOG", Types.VARCHAR); 2878            addColumn(t, "SEQUENCE_SCHEMA", Types.VARCHAR); 2879            addColumn(t, "SEQUENCE_NAME", Types.VARCHAR, true); // not null 2880 addColumn(t, "DTD_IDENTIFIER", Types.VARCHAR, true); // not null 2881 addColumn(t, "MAXIMUM_VALUE", Types.VARCHAR, 20, true); // not null 2882 addColumn(t, "MINIMUM_VALUE", Types.VARCHAR, 20, true); // not null 2883 addColumn(t, "INCREMENT", Types.VARCHAR, 20, true); // not null 2884 addColumn(t, "CYCLE_OPTION", Types.VARCHAR, 3, true); // not null 2885 2886            // HSQLDB-specific 2887 addColumn(t, "START_WITH", Types.VARCHAR, 20, true); // not null 2888 2889            // order SEQUENCE_CATALOG, SEQUENCE_SCHEMA, SEQUENCE_NAME 2890 // false PK, as SCHEMA and/or CATALOG may be null 2891 t.createPrimaryKey(null, new int[] { 2892                0, 1, 2 2893            }, false); 2894 2895            return t; 2896        } 2897 2898        // 2899 final int iseq_cat = 0; 2900        final int iseq_schem = 1; 2901        final int iseq_name = 2; 2902        final int iseq_dtdid = 3; 2903        final int iseq_max = 4; 2904        final int iseq_min = 5; 2905        final int iseq_incr = 6; 2906        final int iseq_cycopt = 7; 2907        final int iseq_start = 8; 2908 2909        // 2910 final String min = ValuePool.getString("0"); 2911        final String intMax = 2912            ValuePool.getString(String.valueOf(Integer.MAX_VALUE)); 2913 2914        // 19 is longer than max value pool string length (15) 2915 final String longMax = String.valueOf(Long.MAX_VALUE); 2916        final String cycleOption = ValuePool.getString("NO"); 2917 2918        // 2919 Iterator it; 2920        Object [] row; 2921        String sequenceName; 2922        NumberSequence sequence; 2923        int dataType; 2924 2925        it = database.schemaManager.allSequencesIterator(); 2926 2927        while (it.hasNext()) { 2928            row = t.getEmptyRowData(); 2929            sequence = (NumberSequence) it.next(); 2930            dataType = sequence.getType(); 2931            sequenceName = sequence.getName().name; 2932            row[iseq_cat] = ns.getCatalogName(sequence); 2933            row[iseq_schem] = sequence.getSchemaName(); 2934            row[iseq_name] = sequenceName; 2935            row[iseq_dtdid] = Types.getTypeString(dataType); 2936            row[iseq_min] = min; 2937            row[iseq_incr] = String.valueOf(sequence.getIncrement()); 2938            row[iseq_cycopt] = cycleOption; 2939            row[iseq_start] = String.valueOf(sequence.peek()); 2940 2941            switch (dataType) { 2942 2943                case Types.INTEGER : { 2944                    row[iseq_max] = intMax; 2945 2946                    break; 2947                } 2948                case Types.BIGINT : 2949                default : { 2950                    row[iseq_max] = longMax; 2951 2952                    break; 2953                } 2954            } 2955 2956            t.insertSys(row); 2957        } 2958 2959        t.setDataReadOnly(true); 2960 2961        return t; 2962    } 2963} 2964

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