Source code: org/hsqldb/DatabaseInformation.java
1 /* Copyrights and Licenses
2 *
3 * This product includes Hypersonic SQL.
4 * Originally developed by Thomas Mueller and the Hypersonic SQL Group.
5 *
6 * Copyright (c) 1995-2000 by the Hypersonic SQL Group. All rights reserved.
7 * Redistribution and use in source and binary forms, with or without modification, are permitted
8 * provided that the following conditions are met:
9 * - Redistributions of source code must retain the above copyright notice, this list of conditions
10 * and the following disclaimer.
11 * - Redistributions in binary form must reproduce the above copyright notice, this list of
12 * conditions and the following disclaimer in the documentation and/or other materials
13 * provided with the distribution.
14 * - All advertising materials mentioning features or use of this software must display the
15 * following acknowledgment: "This product includes Hypersonic SQL."
16 * - Products derived from this software may not be called "Hypersonic SQL" nor may
17 * "Hypersonic SQL" appear in their names without prior written permission of the
18 * Hypersonic SQL Group.
19 * - Redistributions of any form whatsoever must retain the following acknowledgment: "This
20 * product includes Hypersonic SQL."
21 * This software is provided "as is" and any expressed or implied warranties, including, but
22 * not limited to, the implied warranties of merchantability and fitness for a particular purpose are
23 * disclaimed. In no event shall the Hypersonic SQL Group or its contributors be liable for any
24 * direct, indirect, incidental, special, exemplary, or consequential damages (including, but
25 * not limited to, procurement of substitute goods or services; loss of use, data, or profits;
26 * or business interruption). However caused any on any theory of liability, whether in contract,
27 * strict liability, or tort (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 * This software consists of voluntary contributions made by many individuals on behalf of the
30 * Hypersonic SQL Group.
31 *
32 *
33 * For work added by the HSQL Development Group:
34 *
35 * Copyright (c) 2001-2002, The HSQL Development Group
36 * All rights reserved.
37 *
38 * Redistribution and use in source and binary forms, with or without
39 * modification, are permitted provided that the following conditions are met:
40 *
41 * Redistributions of source code must retain the above copyright notice, this
42 * list of conditions and the following disclaimer, including earlier
43 * license statements (above) and comply with all above license conditions.
44 *
45 * Redistributions in binary form must reproduce the above copyright notice,
46 * this list of conditions and the following disclaimer in the documentation
47 * and/or other materials provided with the distribution, including earlier
48 * license statements (above) and comply with all above license conditions.
49 *
50 * Neither the name of the HSQL Development Group nor the names of its
51 * contributors may be used to endorse or promote products derived from this
52 * software without specific prior written permission.
53 *
54 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
55 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
56 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
57 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
58 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
59 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
60 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
61 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
62 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
63 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
64 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
65 */
66
67
68 package org.hsqldb;
69
70 import java.sql.SQLException;
71 import java.sql.Types;
72 import java.sql.DatabaseMetaData;
73 import java.util.Hashtable;
74 import java.util.Vector;
75
76 // fredt@users 20020130 - patch 491987 by jimbag@users
77 // applied to different parts
78 // fredt@users 20020215 - patch 1.7.0 by fredt - quoted identifiers
79 // applied to different parts to support the sql standard for
80 // naming of columns and tables (use of quoted identifiers as names)
81 // written for universal support of quoted names including the quote character
82 // and speed improvements to avoid repetitive string comparisons
83 // thanks to suggestions by boucherb@users
84 // instigated by patch 489864 by jytou@users
85 // fredt@users 20020218 - patch 1.7.0 by fredt - DEFAULT keyword
86 // support for default values for table columns
87 // fredt@users 20020225 - patch 489777 by fredt
88 // restructuring for error trapping
89 // fredt@users 20020225 - patch 1.7.0 - named constraints
90 // fredt@users 20020225 - patch 1.7.0 - multi-column primary keys
91 // fredt@users 20020523 - patch 1.7.0 - JDBC reporting of forgin keys
92 // fredt@users 20020526 - patch 1.7.0 - JDBC reporting of best row identifier
93
94 /**
95 * Provides information about the database.
96 *
97 *
98 * @version 1.7.0
99 */
100 class DatabaseInformation {
101
102 private Database dDatabase;
103 private UserManager aAccess;
104 private Vector tTable;
105 private static final Integer INTEGER_0 = new Integer(0);
106
107 /**
108 * Constructor declaration
109 *
110 *
111 * @param db
112 * @param tables
113 * @param access
114 */
115 DatabaseInformation(Database db, Vector tables, UserManager access) {
116
117 dDatabase = db;
118 tTable = tables;
119 aAccess = access;
120 }
121
122 // some drivers use the following titles:
123 // static final String META_SCHEM="OWNER";
124 // static final String META_CAT="QUALIFIER";
125 // static final String META_COLUMN_SIZE="PRECISION";
126 // static final String META_BUFFER_LENGTH="LENGTH";
127 // static final String META_DECIMAL_DIGITS="SCALE";
128 // static final String META_NUM_PREC_RADIX="RADIX";
129 // static final String META_FIXED_PREC_SCALE="MONEY";
130 // static final String META_ORDINAL_POSITION="SEQ_IN_INDEX";
131 // static final String META_ASC_OR_DESC="COLLATION";
132 static final String META_SCHEM = "SCHEM";
133 static final String META_CAT = "CAT";
134 static final String META_COLUMN_SIZE = "COLUMN_SIZE";
135 static final String META_BUFFER_LENGTH = "BUFFER_LENGTH";
136 static final String META_DECIMAL_DIGITS = "DECIMAL_DIGITS";
137 static final String META_NUM_PREC_RADIX = "NUM_PREC_RADIX";
138 static final String META_FIXED_PREC_SCALE = "FIXED_PREC_SCALE";
139 static final String META_ORDINAL_POSITION = "ORDINAL_POSITION";
140 static final String META_ASC_OR_DESC = "ASC_OR_DESC";
141 private static Hashtable sysTableNames;
142 private static final int SYSTEM_PROCEDURES = 1;
143 private static final int SYSTEM_PROCEDURECOLUMNS = 2;
144 private static final int SYSTEM_TABLES = 3;
145 private static final int SYSTEM_SCHEMAS = 4;
146 private static final int SYSTEM_CATALOGS = 5;
147 private static final int SYSTEM_TABLETYPES = 6;
148 private static final int SYSTEM_COLUMNS = 7;
149 private static final int SYSTEM_COLUMNPRIVILEGES = 8;
150 private static final int SYSTEM_TABLEPRIVILEGES = 9;
151 private static final int SYSTEM_BESTROWIDENTIFIER = 10;
152 private static final int SYSTEM_VERSIONCOLUMNS = 11;
153 private static final int SYSTEM_PRIMARYKEYS = 12;
154 private static final int SYSTEM_IMPORTEDKEYS = 13;
155 private static final int SYSTEM_EXPORTEDKEYS = 14;
156 private static final int SYSTEM_CROSSREFERENCE = 15;
157 private static final int SYSTEM_TYPEINFO = 16;
158 private static final int SYSTEM_INDEXINFO = 17;
159 private static final int SYSTEM_UDTS = 18;
160 private static final int SYSTEM_CONNECTIONINFO = 19;
161 private static final int SYSTEM_USERS = 20;
162
163 // supported table types
164 private static final String[] tableTypes = new String[] {
165 "TABLE", "VIEW", "GLOBAL TEMPORARY"
166 };
167
168 static {
169 sysTableNames = new Hashtable(37);
170
171 String sysNames[] = {
172 "SYSTEM_PROCEDURES", "SYSTEM_PROCEDURECOLUMNS", "SYSTEM_TABLES",
173 "SYSTEM_SCHEMAS", "SYSTEM_CATALOGS", "SYSTEM_TABLETYPES",
174 "SYSTEM_COLUMNS", "SYSTEM_COLUMNPRIVILEGES",
175 "SYSTEM_TABLEPRIVILEGES", "SYSTEM_BESTROWIDENTIFIER",
176 "SYSTEM_VERSIONCOLUMNS", "SYSTEM_PRIMARYKEYS",
177 "SYSTEM_IMPORTEDKEYS", "SYSTEM_EXPORTEDKEYS",
178 "SYSTEM_CROSSREFERENCE", "SYSTEM_TYPEINFO", "SYSTEM_INDEXINFO",
179 "SYSTEM_UDTS", "SYSTEM_CONNECTIONINFO", "SYSTEM_USERS"
180 };
181
182 for (int i = 0; i < sysNames.length; i++) {
183 sysTableNames.put(sysNames[i], new Integer(i + 1));
184 }
185 }
186
187 static boolean isSystemTable(String name) {
188 return sysTableNames.get(name) == null ? false
189 : true;
190 }
191
192 /**
193 * Method declaration
194 *
195 *
196 * @param name
197 * @param session
198 *
199 * @return
200 *
201 * @throws SQLException
202 */
203 Table getSystemTable(String tablename,
204 Session session) throws SQLException {
205
206 HsqlName name = new HsqlName(tablename, false);
207 Integer tableId = (Integer) sysTableNames.get(tablename);
208
209 if (tableId == null) {
210 return null;
211 }
212
213 int tableIdValue = tableId.intValue();
214 Table t = createTable(name);
215
216 switch (tableIdValue) {
217
218 case SYSTEM_PROCEDURES : {
219 t.addColumn("PROCEDURE_" + META_CAT, Types.VARCHAR);
220 t.addColumn("PROCEDURE_" + META_SCHEM, Types.VARCHAR);
221 t.addColumn("PROCEDURE_NAME", Types.VARCHAR);
222 t.addColumn("NUM_INPUT_PARAMS", Types.INTEGER);
223 t.addColumn("NUM_OUTPUT_PARAMS", Types.INTEGER);
224 t.addColumn("NUM_RESULT_SETS", Types.INTEGER);
225 t.addColumn("REMARKS", Types.VARCHAR);
226 t.addColumn("PROCEDURE_TYPE", Types.SMALLINT);
227 t.createPrimaryKey();
228
229 return t;
230 }
231 case SYSTEM_PROCEDURECOLUMNS : {
232 t.addColumn("PROCEDURE_" + META_CAT, Types.VARCHAR);
233 t.addColumn("PROCEDURE_" + META_SCHEM, Types.VARCHAR);
234 t.addColumn("PROCEDURE_NAME", Types.VARCHAR);
235 t.addColumn("COLUMN_NAME", Types.VARCHAR);
236 t.addColumn("COLUMN_TYPE", Types.SMALLINT);
237 t.addColumn("DATA_TYPE", Types.SMALLINT);
238 t.addColumn("TYPE_NAME", Types.VARCHAR);
239 t.addColumn("PRECISION", Types.INTEGER);
240 t.addColumn("LENGTH", Types.INTEGER);
241 t.addColumn("SCALE", Types.SMALLINT);
242 t.addColumn("RADIX", Types.SMALLINT);
243 t.addColumn("NULLABLE", Types.SMALLINT);
244 t.addColumn("REMARKS", Types.VARCHAR);
245 t.createPrimaryKey();
246
247 return t;
248 }
249 case SYSTEM_TABLES : {
250 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
251 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
252 t.addColumn("TABLE_NAME", Types.VARCHAR);
253 t.addColumn("TABLE_TYPE", Types.VARCHAR);
254 t.addColumn("REMARKS", Types.VARCHAR);
255
256 // boucherb@users 20020415 added for JDBC 3 clients
257 t.addColumn("TYPE_" + META_CAT, Types.VARCHAR);
258 t.addColumn("TYPE_" + META_SCHEM, Types.VARCHAR);
259 t.addColumn("TYPE_NAME", Types.VARCHAR);
260 t.addColumn("SELF_REFERENCING_COL_NAME", Types.VARCHAR);
261 t.addColumn("REF_GENERATION", Types.VARCHAR);
262 t.createPrimaryKey();
263
264 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
265 Table table = (Table) tTable.elementAt(i);
266 Object o[] = t.getNewRow();
267
268 o[0] = o[1] = "";
269 o[2] = table.getName().name;
270
271 switch (table.tableType) {
272
273 case Table.VIEW :
274 o[3] = "VIEW";
275 break;
276
277 case Table.TEMP_TABLE :
278 case Table.TEMP_TEXT_TABLE :
279 if (dDatabase.findUserTable(
280 table.getName().name, session) == null) {
281 continue;
282 }
283
284 o[3] = "GLOBAL TEMPORARY";
285 break;
286
287 default :
288 o[3] = "TABLE";
289 }
290
291 // sqlbob@users Set remarks to readonly status.
292 if (table.isDataReadOnly()) {
293 o[4] = "ReadOnlyData=true";
294 }
295
296 // sqlbob@users Add data source to remarks
297 String dataSource = table.getDataSource();
298
299 if (dataSource != null) {
300 if (o[4] == null) {
301 o[4] = "";
302 } else {
303 o[4] = o[4] + "; ";
304 }
305
306 o[4] = o[4] + "DataSource=\"" + dataSource + "\"";
307
308 if (table.isDescDataSource()) {
309 o[4] = o[4] + " DESC";
310 }
311 }
312
313 t.insert(o, null);
314 }
315
316 return t;
317 }
318 case SYSTEM_SCHEMAS : {
319 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
320
321 // boucherb@users 20020415 added for JDBC 3 clients
322 t.addColumn("TABLE_CATALOG", Types.VARCHAR);
323 t.createPrimaryKey();
324
325 return t;
326 }
327 case SYSTEM_CATALOGS : {
328 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
329 t.createPrimaryKey();
330
331 return t;
332 }
333 case SYSTEM_TABLETYPES : {
334 t.addColumn("TABLE_TYPE", Types.VARCHAR);
335 t.createPrimaryKey();
336
337 for (int i = 0; i < tableTypes.length; i++) {
338 Object o[] = t.getNewRow();
339
340 o[0] = tableTypes[i];
341
342 t.insert(o, null);
343 }
344
345 return t;
346 }
347 case SYSTEM_COLUMNS : {
348 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
349 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
350 t.addColumn("TABLE_NAME", Types.VARCHAR);
351 t.addColumn("COLUMN_NAME", Types.VARCHAR);
352 t.addColumn("DATA_TYPE", Types.SMALLINT);
353 t.addColumn("TYPE_NAME", Types.VARCHAR);
354 t.addColumn(META_COLUMN_SIZE, Types.INTEGER);
355 t.addColumn(META_BUFFER_LENGTH, Types.INTEGER);
356 t.addColumn(META_DECIMAL_DIGITS, Types.INTEGER);
357 t.addColumn(META_NUM_PREC_RADIX, Types.INTEGER);
358 t.addColumn("NULLABLE", Types.INTEGER);
359 t.addColumn("REMARKS", Types.VARCHAR);
360 t.addColumn("COLUMN_DEF", Types.VARCHAR);
361
362 // fredt@users 20020407 - patch 1.7.0 by sqlbob@users - fixed incorrect type
363 t.addColumn("SQL_DATA_TYPE", Types.INTEGER);
364 t.addColumn("SQL_DATETIME_SUB", Types.INTEGER);
365 t.addColumn("CHAR_OCTET_LENGTH", Types.INTEGER);
366
367 // fredt@users 20020407 - patch 1.7.0 - fixed incorrect type
368 t.addColumn("ORDINAL_POSITION", Types.INTEGER);
369 t.addColumn("IS_NULLABLE", Types.VARCHAR);
370
371 // boucherb@users 20020415 added for JDBC 3 clients
372 // fredt - spelling of SCOPE_CATLOG is according to JDBC specs
373 t.addColumn("SCOPE_CATLOG", Types.VARCHAR);
374 t.addColumn("SCOPE_SCHEMA", Types.VARCHAR);
375 t.addColumn("SCOPE_TABLE", Types.VARCHAR);
376 t.addColumn("SOURCE_DATA_TYPE", Types.VARCHAR);
377 t.addColumn("SCOPE_CATLOG ", Types.SMALLINT);
378 t.createPrimaryKey();
379
380 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
381 Table table = (Table) tTable.elementAt(i);
382 int columnCount = table.getColumnCount();
383
384 if (table.tableType == Table.TEMP_TABLE
385 || table.tableType == Table.TEMP_TEXT_TABLE) {
386 if (dDatabase.findUserTable(
387 table.getName().name, session) == null) {
388 continue;
389 }
390 }
391
392 for (int j = 0; j < columnCount; j++) {
393 Column column = table.getColumn(j);
394 Object o[] = t.getNewRow();
395
396 o[0] = o[1] = "";
397 o[2] = table.getName().name;
398 o[3] = column.columnName.name;
399 o[4] = new Integer(column.getType());
400 o[5] = Column.getTypeString(column.getType());
401
402 // fredt@users 20020130 - patch 491987 by jimbag@users
403 o[6] = new Integer(column.getSize());
404 o[8] = new Integer(column.getScale());
405 o[9] = new Integer(10);
406
407 int nullable;
408
409 if (column.isNullable()) {
410 nullable = DatabaseMetaData.columnNullable;
411 o[17] = new String("YES");
412 } else {
413 nullable = DatabaseMetaData.columnNoNulls;
414 o[17] = new String("NO");
415 }
416
417 o[10] = new Integer(nullable);
418
419 if (table.getIdentityColumn() == j) {
420 o[11] = "IDENTITY";
421 }
422
423 o[12] = column.getDefaultString();
424
425 // ordinal position
426 o[16] = new Integer(j + 1);
427
428 t.insert(o, null);
429 }
430 }
431
432 return t;
433 }
434 case SYSTEM_COLUMNPRIVILEGES : {
435 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
436 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
437 t.addColumn("TABLE_NAME", Types.VARCHAR);
438 t.addColumn("COLUMN_NAME", Types.VARCHAR);
439 t.addColumn("GRANTOR", Types.VARCHAR);
440 t.addColumn("GRANTEE", Types.VARCHAR);
441 t.addColumn("PRIVILEGE", Types.VARCHAR);
442 t.addColumn("IS_GRANTABLE", Types.VARCHAR);
443 t.createPrimaryKey();
444
445 /*
446 * // todo: get correct info
447 * for(int i=0;i<tTable.size();i++) {
448 * table=(Table)tTable.elementAt(i);
449 * int columns=table.getColumnCount();
450 * for(int j=0;j<columns;j++) {
451 * Object o[]=t.getNewRow();
452 * o[2]=table.getName();
453 * o[3]=table.getColumnName(j);
454 * o[4]="sa";
455 * o[6]="FULL";
456 * o[7]="NO";
457 * t.insert(o,null);
458 * }
459 * }
460 */
461 return t;
462 }
463 case SYSTEM_TABLEPRIVILEGES : {
464 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
465 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
466 t.addColumn("TABLE_NAME", Types.VARCHAR);
467 t.addColumn("GRANTOR", Types.VARCHAR);
468 t.addColumn("GRANTEE", Types.VARCHAR);
469 t.addColumn("PRIVILEGE", Types.VARCHAR);
470 t.addColumn("IS_GRANTABLE", Types.VARCHAR);
471 t.createPrimaryKey();
472
473 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
474 Table table = (Table) tTable.elementAt(i);
475
476 if (table.tableType == Table.TEMP_TABLE
477 || table.tableType == Table.TEMP_TEXT_TABLE) {
478 if (dDatabase.findUserTable(
479 table.getName().name, session) == null) {
480 continue;
481 }
482 }
483
484 Object o[] = t.getNewRow();
485
486 o[0] = o[1] = "";
487 o[2] = table.getName().name;
488 o[3] = "sa";
489 o[5] = "FULL";
490
491 t.insert(o, null);
492 }
493
494 return t;
495 }
496 case SYSTEM_VERSIONCOLUMNS :
497
498 // return an empty table for SYSTEM_VERSIONCOLUMNS
499 case SYSTEM_BESTROWIDENTIFIER :
500 t.addColumn("SCOPE", Types.SMALLINT);
501 t.addColumn("COLUMN_NAME", Types.VARCHAR);
502 t.addColumn("DATA_TYPE", Types.SMALLINT);
503 t.addColumn("TYPE_NAME", Types.VARCHAR);
504 t.addColumn(META_COLUMN_SIZE, Types.INTEGER);
505 t.addColumn(META_BUFFER_LENGTH, Types.INTEGER);
506 t.addColumn(META_DECIMAL_DIGITS, Types.SMALLINT);
507 t.addColumn("PSEUDO_COLUMN", Types.SMALLINT);
508 t.addColumn("TABLE_NAME", Types.VARCHAR);
509 t.createPrimaryKey();
510
511 if (tableIdValue == SYSTEM_VERSIONCOLUMNS) {
512 return t;
513 }
514 {
515 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
516 Table table = (Table) tTable.elementAt(i);
517
518 if (table.tableType == Table.VIEW) {
519 continue;
520 }
521
522 if (table.tableType == Table.TEMP_TABLE
523 || table.tableType == Table.TEMP_TEXT_TABLE) {
524 if (dDatabase.findUserTable(
525 table.getName().name, session) == null) {
526 continue;
527 }
528 }
529
530 Index index = null;
531 int[] cols = null;
532
533 // fredt - don't report primary key on hidden column
534 for (int j = 0; j < table.getIndexCount(); j++) {
535 index = table.getIndex(j);
536
537 if (index.isUnique()) {
538 cols = index.getColumns();
539
540 if (cols[0] == table.getColumnCount()) {
541 cols = null;
542 } else {
543 break;
544 }
545 }
546 }
547
548 if (cols == null) {
549 continue;
550 }
551
552 int len = cols.length;
553
554 for (int j = 0; j < len; j++) {
555 Column column = table.getColumn(cols[j]);
556 Object o[] = t.getNewRow();
557
558 o[0] = new Short(
559 (short) DatabaseMetaData.bestRowTemporary);
560 o[1] = column.columnName.name;
561 o[2] = new Short((short) column.getType());
562 o[3] = Column.getTypeString(column.getType());
563 o[4] = new Integer(column.getSize());
564 o[6] = new Integer(column.getScale());
565 o[7] = new Short(
566 (short) DatabaseMetaData.bestRowNotPseudo);
567 o[8] = table.getName().name;
568
569 t.insert(o, null);
570 }
571 }
572 }
573
574 return t;
575
576 case SYSTEM_PRIMARYKEYS : {
577 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
578 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
579 t.addColumn("TABLE_NAME", Types.VARCHAR);
580 t.addColumn("COLUMN_NAME", Types.VARCHAR);
581 t.addColumn("KEY_SEQ", Types.SMALLINT);
582 t.addColumn("PK_NAME", Types.VARCHAR);
583 t.createPrimaryKey();
584
585 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
586 Table table = (Table) tTable.elementAt(i);
587
588 if (table.tableType == Table.VIEW) {
589 continue;
590 }
591
592 if (table.tableType == Table.TEMP_TABLE
593 || table.tableType == Table.TEMP_TEXT_TABLE) {
594 if (dDatabase.findUserTable(
595 table.getName().name, session) == null) {
596 continue;
597 }
598 }
599
600 Index index = table.getIndex(0);
601 int cols[] = index.getColumns();
602
603 // fredt - don't report primary key on hidden column
604 if (cols[0] == table.getColumnCount()) {
605 continue;
606 }
607
608 int len = cols.length;
609
610 for (int j = 0; j < len; j++) {
611 Object o[] = t.getNewRow();
612
613 o[0] = o[1] = "";
614 o[2] = table.getName().name;
615 o[3] = table.getColumn(cols[j]).columnName.name;
616 o[4] = new Integer((j + 1));
617 o[5] = index.getName().name;
618
619 t.insert(o, null);
620 }
621 }
622
623 return t;
624 }
625 case SYSTEM_IMPORTEDKEYS :
626 case SYSTEM_EXPORTEDKEYS :
627 case SYSTEM_CROSSREFERENCE :
628 return getCrossReference(name, session);
629
630 case SYSTEM_TYPEINFO : {
631 t.addColumn("TYPE_NAME", Types.VARCHAR);
632 t.addColumn("DATA_TYPE", Types.SMALLINT);
633 t.addColumn("PRECISION", Types.INTEGER);
634 t.addColumn("LITERAL_PREFIX", Types.VARCHAR);
635 t.addColumn("LITERAL_SUFFIX", Types.VARCHAR);
636 t.addColumn("CREATE_PARAMS", Types.VARCHAR);
637 t.addColumn("NULLABLE", Types.SMALLINT);
638 t.addColumn("CASE_SENSITIVE", Types.BIT);
639 t.addColumn("SEARCHABLE", Types.SMALLINT);
640 t.addColumn("UNSIGNED_ATTRIBUTE", Types.BIT);
641 t.addColumn(META_FIXED_PREC_SCALE, Types.BIT);
642 t.addColumn("AUTO_INCREMENT", Types.BIT);
643 t.addColumn("LOCAL_TYPE_NAME", Types.VARCHAR);
644 t.addColumn("MINIMUM_SCALE", Types.SMALLINT);
645 t.addColumn("MAXIMUM_SCALE", Types.SMALLINT);
646 t.addColumn("SQL_DATE_TYPE", Types.INTEGER);
647 t.addColumn("SQL_DATETIME_SUB", Types.INTEGER);
648 t.addColumn("NUM_PREC_RADIX", Types.INTEGER);
649 t.createPrimaryKey();
650
651 for (int h = 0; h < Column.typesArray.length; h++) {
652 for (int i = 0; i < Column.typesArray[h].length; i++) {
653 Object o[] = t.getNewRow();
654 int type = Column.typesArray[h][i];
655
656 o[0] = Column.getTypeString(type);
657 o[1] = new Integer(type);
658 o[2] = INTEGER_0; // precision
659 o[6] = new Integer(DatabaseMetaData.typeNullable);
660 o[7] = new Boolean(true); // case sensitive
661 o[8] = new Integer(DatabaseMetaData.typeSearchable);
662 o[9] = new Boolean(false); // unsigned
663 o[10] = new Boolean(type == Types.NUMERIC
664 || type == Types.DECIMAL);
665 o[11] = new Boolean(type == Types.INTEGER);
666 o[12] = o[0];
667 o[13] = INTEGER_0;
668 o[14] = INTEGER_0; // maximum scale
669 o[17] = new Integer(10);
670
671 t.insert(o, null);
672 }
673 }
674
675 return t;
676 }
677 case SYSTEM_INDEXINFO : {
678 t.addColumn("TABLE_" + META_CAT, Types.VARCHAR);
679 t.addColumn("TABLE_" + META_SCHEM, Types.VARCHAR);
680 t.addColumn("TABLE_NAME", Types.VARCHAR);
681 t.addColumn("NON_UNIQUE", Types.BIT);
682 t.addColumn("INDEX_QUALIFIER", Types.VARCHAR);
683 t.addColumn("INDEX_NAME", Types.VARCHAR);
684 t.addColumn("TYPE", Types.SMALLINT);
685 t.addColumn(META_ORDINAL_POSITION, Types.SMALLINT);
686 t.addColumn("COLUMN_NAME", Types.VARCHAR);
687 t.addColumn(META_ASC_OR_DESC, Types.VARCHAR);
688 t.addColumn("CARDINALITY", Types.INTEGER);
689 t.addColumn("PAGES", Types.INTEGER);
690 t.addColumn("FILTER_CONDITION", Types.VARCHAR);
691 t.createPrimaryKey();
692
693 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
694 Table table = (Table) tTable.elementAt(i);
695
696 for (int j = 0; j < table.getIndexCount(); j++) {
697 Index index = table.getIndex(j);
698 int cols[] = index.getColumns();
699 int len = index.getVisibleColumns();
700
701 if (len == 0) {
702 continue;
703 }
704
705 for (int k = 0; k < len; k++) {
706 Object o[] = t.getNewRow();
707
708 o[0] = o[1] = "";
709 o[2] = table.getName().name;
710 o[3] = new Boolean(!index.isUnique());
711 o[5] = index.getName().name;
712 o[6] = new Integer(
713 DatabaseMetaData.tableIndexOther);
714 o[7] = new Integer(k + 1);
715 o[8] = table.getColumn(cols[k]).columnName.name;
716 o[9] = "A";
717
718 t.insert(o, null);
719 }
720 }
721 }
722
723 return t;
724 }
725 case SYSTEM_UDTS : {
726 t.addColumn("TYPE_" + META_CAT, Types.VARCHAR);
727 t.addColumn("TYPE_" + META_SCHEM, Types.VARCHAR);
728 t.addColumn("TYPE_NAME", Types.VARCHAR);
729 t.addColumn("CLASS_NAME", Types.BIT);
730 t.addColumn("DATA_TYPE", Types.VARCHAR);
731 t.addColumn("REMARKS", Types.VARCHAR);
732
733 // boucherb@users 20020415 added for JDBC 3 clients
734 t.addColumn("BASE_TYPE ", Types.SMALLINT);
735 t.createPrimaryKey();
736
737 return t;
738 }
739 case SYSTEM_CONNECTIONINFO : {
740 t.addColumn("KEY", Types.VARCHAR);
741 t.addColumn("VALUE", Types.VARCHAR);
742 t.createPrimaryKey();
743
744 Object o[] = t.getNewRow();
745
746 o[0] = "USER";
747 o[1] = session.getUsername();
748
749 t.insert(o, null);
750
751 o = t.getNewRow();
752 o[0] = "READONLY";
753 o[1] = session.isReadOnly() ? "TRUE"
754 : "FALSE";
755
756 t.insert(o, null);
757
758 o = t.getNewRow();
759 o[0] = "MAXROWS";
760 o[1] = String.valueOf(session.getMaxRows());
761
762 t.insert(o, null);
763
764 o = t.getNewRow();
765 o[0] = "DATABASE";
766 o[1] = session.getDatabase().getName();
767
768 t.insert(o, null);
769
770 o = t.getNewRow();
771 o[0] = "IDENTITY";
772 o[1] = String.valueOf(session.getLastIdentity());
773
774 t.insert(o, null);
775
776 return t;
777 }
778 case SYSTEM_USERS : {
779 t.addColumn("USER", Types.VARCHAR);
780 t.addColumn("ADMIN", Types.BIT);
781 t.createPrimaryKey();
782
783 Vector v = aAccess.getUsers();
784
785 for (int i = 0, vSize = v.size(); i < vSize; i++) {
786 User u = (User) v.elementAt(i);
787
788 // todo: this is not a nice implementation
789 if (u == null) {
790 continue;
791 }
792
793 String user = u.getName();
794
795 if (!user.equals("PUBLIC")) {
796 Object o[] = t.getNewRow();
797
798 o[0] = user;
799 o[1] = new Boolean(u.isAdmin());
800
801 t.insert(o, null);
802 }
803 }
804
805 return t;
806 }
807 default :
808 return null;
809 }
810 }
811
812 static final Short importedKeyNoActionShort =
813 new Short((short) DatabaseMetaData.importedKeyNoAction);
814 static final Short importedKeyCascadeShort =
815 new Short((short) DatabaseMetaData.importedKeyCascade);
816 static final Short importedKeyNotDeferrableShort =
817 new Short((short) DatabaseMetaData.importedKeyNotDeferrable);
818
819 Table getCrossReference(HsqlName name,
820 Session session) throws SQLException {
821
822 Table t = createTable(name);
823
824 t.addColumn("PKTABLE_" + META_CAT, Types.VARCHAR);
825 t.addColumn("PKTABLE_" + META_SCHEM, Types.VARCHAR);
826 t.addColumn("PKTABLE_NAME", Types.VARCHAR);
827 t.addColumn("PKCOLUMN_NAME", Types.VARCHAR);
828 t.addColumn("FKTABLE_" + META_CAT, Types.VARCHAR);
829 t.addColumn("FKTABLE_" + META_SCHEM, Types.VARCHAR);
830 t.addColumn("FKTABLE_NAME", Types.VARCHAR);
831 t.addColumn("FKCOLUMN_NAME", Types.VARCHAR);
832 t.addColumn("KEY_SEQ", Types.SMALLINT);
833 t.addColumn("UPDATE_RULE", Types.SMALLINT);
834 t.addColumn("DELETE_RULE", Types.SMALLINT);
835 t.addColumn("FK_NAME", Types.VARCHAR);
836 t.addColumn("PK_NAME", Types.VARCHAR);
837 t.addColumn("DEFERRABILITY", Types.SMALLINT);
838 t.createPrimaryKey();
839
840 for (int i = 0, tSize = tTable.size(); i < tSize; i++) {
841 Table table = (Table) tTable.elementAt(i);
842 Vector constVect = table.getConstraints();
843 Constraint constraint;
844
845 for (int j = 0; j < constVect.size(); j++) {
846 constraint = (Constraint) constVect.elementAt(j);
847
848 if (constraint.getType() != Constraint.FOREIGN_KEY) {
849 continue;
850 }
851
852 String mainTableName = constraint.getMain().tableName.name;
853 String refTableName = constraint.getRef().tableName.name;
854
855 if (dDatabase.findUserTable(mainTableName) == null
856 || dDatabase.findUserTable(refTableName) == null) {
857 continue;
858 }
859
860 int pkcols[] = constraint.getMainColumns();
861 int fkcols[] = constraint.getRefColumns();
862 int len = pkcols.length;
863
864 for (int k = 0; k < len; k++) {
865 Object o[] = t.getNewRow();
866
867 o[0] = o[1] = "";
868 o[2] = mainTableName;
869 o[3] = constraint.getMain().getColumn(
870 pkcols[k]).columnName.name;
871 o[4] = o[5] = "";
872 o[6] = refTableName;
873 o[7] = constraint.getRef().getColumn(
874 fkcols[k]).columnName.name;
875 o[8] = new Short((short) (k + 1));
876 o[9] = importedKeyNoActionShort;
877 o[10] = constraint.isCascade() ? importedKeyCascadeShort
878 : importedKeyNoActionShort;
879 o[11] = constraint.getFkName();
880 o[12] = constraint.getPkName();
881 o[13] = importedKeyNotDeferrableShort;
882
883 t.insert(o, null);
884 }
885 }
886 }
887
888 return t;
889 }
890
891 /**
892 * Method declaration
893 *
894 *
895 * @param name
896 *
897 * @return
898 */
899 private Table createTable(HsqlName name) throws SQLException {
900 return new Table(dDatabase, name, Table.SYSTEM_TABLE, null);
901 }
902 }