File D:\maven-qcr-plugin-testsunitaires\qcr4java\h2-1.3.170-sources\target\site\qc\lecode\table\TableLink.java.html

1 /*
2 * Copyright 2004-2011 H2 Group. Multiple-Licensed under the H2 License,
3 * Version 1.0, and under the Eclipse Public License, Version 1.0
4 * (http://h2database.com/html/license.html).
5 * Initial Developer: H2 Group
6 */
7 package org.h2.table;
8
9 import java.sql.DatabaseMetaData;
10 import java.sql.PreparedStatement;
11 import java.sql.ResultSet;
12 import java.sql.ResultSetMetaData;
13 import java.sql.SQLException;
14 import java.sql.Statement;
15 import java.sql.Types;
16 import java.util.ArrayList;
17 import java.util.HashMap;
18 import org.h2.command.Prepared;
19 import org.h2.constant.ErrorCode;
20 import org.h2.engine.Session;
21 import org.h2.engine.UndoLogRecord;
22 import org.h2.index.Index;
23 import org.h2.index.IndexType;
24 import org.h2.index.LinkedIndex;
25 import org.h2.jdbc.JdbcSQLException;
26 import org.h2.message.DbException;
27 import org.h2.result.Row;
28 import org.h2.result.RowList;
29 import org.h2.schema.Schema;
30 import org.h2.util.JdbcUtils;
31 import org.h2.util.MathUtils;
32 import org.h2.util.New;
33 import org.h2.util.StatementBuilder;
34 import org.h2.util.StringUtils;
35 import org.h2.value.DataType;
36 import org.h2.value.Value;
37 import org.h2.value.ValueDate;
38 import org.h2.value.ValueTime;
39 import org.h2.value.ValueTimestamp;
40
41 /**
42 * A linked table contains connection information for a table accessible by JDBC.
43 * The table may be stored in a different database.
44 */
45 public class TableLink extends Table {
46
47 private static final int MAX_RETRY = 2;
48
49 private static final long ROW_COUNT_APPROXIMATION = 100000;
50
51 private final String originalSchema;
52 private String driver, url, user, password, originalTable, qualifiedTableName;
53 private TableLinkConnection conn;
54 private HashMap<String, PreparedStatement> preparedMap = New.hashMap();
55 private final ArrayList<Index> indexes = New.arrayList();
56 private final boolean emitUpdates;
57 private LinkedIndex linkedIndex;
58 private DbException connectException;
59 private boolean storesLowerCase;
60 private boolean storesMixedCase;
61 private boolean storesMixedCaseQuoted;
62 private boolean supportsMixedCaseIdentifiers;
63 private boolean globalTemporary;
64 private boolean readOnly;
65
66 public TableLink(Schema schema, int id, String name, String driver, String url, String user, String password,
67 String originalSchema, String originalTable, boolean emitUpdates, boolean force) {
68 super(schema, id, name, false, true);
69 this.driver = driver;
70 this.url = url;
71 this.user = user;
72 this.password = password;
73 this.originalSchema = originalSchema;
74 this.originalTable = originalTable;
75 this.emitUpdates = emitUpdates;
76 try {
77 connect();
78 } catch (DbException e) {
79 if (!force) {
80 throw e;
81 }
82 Column[] cols = { };
83 setColumns(cols);
84 linkedIndex = new LinkedIndex(this, id, IndexColumn.wrap(cols), IndexType.createNonUnique(false));
85 indexes.add(linkedIndex);
86 }
87 }
88
89 private void connect() {
90 connectException = null;
91 for (int retry = 0;; retry++) {
92 try {
93 conn = database.getLinkConnection(driver, url, user, password);
94 synchronized (conn) {
95 try {
96 readMetaData();
97 return;
98 } catch (Exception e) {
99 // could be SQLException or RuntimeException
100 conn.close(true);
101 conn = null;
102 throw DbException.convert(e);
103 }
104 }
105 } catch (DbException e) {
106 if (retry >= MAX_RETRY) {
107 connectException = e;
108 throw e;
109 }
110 }
111 }
112 }
113
114 private void readMetaData() throws SQLException {
115 DatabaseMetaData meta = conn.getConnection().getMetaData();
116 storesLowerCase = meta.storesLowerCaseIdentifiers();
117 storesMixedCase = meta.storesMixedCaseIdentifiers();
118 storesMixedCaseQuoted = meta.storesMixedCaseQuotedIdentifiers();
119 supportsMixedCaseIdentifiers = meta.supportsMixedCaseIdentifiers();
120 ResultSet rs = meta.getTables(null, originalSchema, originalTable, null);
121 if (rs.next() && rs.next()) {
122 throw DbException.get(ErrorCode.SCHEMA_NAME_MUST_MATCH, originalTable);
123 }
124 rs.close();
125 rs = meta.getColumns(null, originalSchema, originalTable, null);
126 int i = 0;
127 ArrayList<Column> columnList = New.arrayList();
128 HashMap<String, Column> columnMap = New.hashMap();
129 String catalog = null, schema = null;
130 while (rs.next()) {
131 String thisCatalog = rs.getString("TABLE_CAT");
132 if (catalog == null) {
133 catalog = thisCatalog;
134 }
135 String thisSchema = rs.getString("TABLE_SCHEM");
136 if (schema == null) {
137 schema = thisSchema;
138 }
139 if (!StringUtils.equals(catalog, thisCatalog) || !StringUtils.equals(schema, thisSchema)) {
140 // if the table exists in multiple schemas or tables,
141 // use the alternative solution
142 columnMap.clear();
143 columnList.clear();
144 break;
145 }
146 String n = rs.getString("COLUMN_NAME");
147 n = convertColumnName(n);
148 int sqlType = rs.getInt("DATA_TYPE");
149 long precision = rs.getInt("COLUMN_SIZE");
150 precision = convertPrecision(sqlType, precision);
151 int scale = rs.getInt("DECIMAL_DIGITS");
152 scale = convertScale(sqlType, scale);
153 int displaySize = MathUtils.convertLongToInt(precision);
154 int type = DataType.convertSQLTypeToValueType(sqlType);
155 Column col = new Column(n, type, precision, scale, displaySize);
156 col.setTable(this, i++);
157 columnList.add(col);
158 columnMap.put(n, col);
159 }
160 rs.close();
161 if (originalTable.indexOf('.') < 0 && !StringUtils.isNullOrEmpty(schema)) {
162 qualifiedTableName = schema + "." + originalTable;
163 } else {
164 qualifiedTableName = originalTable;
165 }
166 // check if the table is accessible
167 Statement stat = null;
168 try {
169 stat = conn.getConnection().createStatement();
170 rs = stat.executeQuery("SELECT * FROM " + qualifiedTableName + " T WHERE 1=0");
171 if (columnList.size() == 0) {
172 // alternative solution
173 ResultSetMetaData rsMeta = rs.getMetaData();
174 for (i = 0; i < rsMeta.getColumnCount();) {
175 String n = rsMeta.getColumnName(i + 1);
176 n = convertColumnName(n);
177 int sqlType = rsMeta.getColumnType(i + 1);
178 long precision = rsMeta.getPrecision(i + 1);
179 precision = convertPrecision(sqlType, precision);
180 int scale = rsMeta.getScale(i + 1);
181 scale = convertScale(sqlType, scale);
182 int displaySize = rsMeta.getColumnDisplaySize(i + 1);
183 int type = DataType.convertSQLTypeToValueType(sqlType);
184 Column col = new Column(n, type, precision, scale, displaySize);
185 col.setTable(this, i++);
186 columnList.add(col);
187 columnMap.put(n, col);
188 }
189 }
190 rs.close();
191 } catch (Exception e) {
192 throw DbException.get(ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, e,
193 originalTable + "(" + e.toString() + ")");
194 } finally {
195 JdbcUtils.closeSilently(stat);
196 }
197 Column[] cols = new Column[columnList.size()];
198 columnList.toArray(cols);
199 setColumns(cols);
200 int id = getId();
201 linkedIndex = new LinkedIndex(this, id, IndexColumn.wrap(cols), IndexType.createNonUnique(false));
202 indexes.add(linkedIndex);
203 try {
204 rs = meta.getPrimaryKeys(null, originalSchema, originalTable);
205 } catch (Exception e) {
206 // Some ODBC bridge drivers don't support it:
207 // some combinations of "DataDirect SequeLink(R) for JDBC"
208 // http://www.datadirect.com/index.ssp
209 rs = null;
210 }
211 String pkName = "";
212 ArrayList<Column> list;
213 if (rs != null && rs.next()) {
214 // the problem is, the rows are not sorted by KEY_SEQ
215 list = New.arrayList();
216 do {
217 int idx = rs.getInt("KEY_SEQ");
218 if (pkName == null) {
219 pkName = rs.getString("PK_NAME");
220 }
221 while (list.size() < idx) {
222 list.add(null);
223 }
224 String col = rs.getString("COLUMN_NAME");
225 col = convertColumnName(col);
226 Column column = columnMap.get(col);
227 if (idx == 0) {
228 // workaround for a bug in the SQLite JDBC driver
229 list.add(column);
230 } else {
231 list.set(idx - 1, column);
232 }
233 } while (rs.next());
234 addIndex(list, IndexType.createPrimaryKey(false, false));
235 rs.close();
236 }
237 try {
238 rs = meta.getIndexInfo(null, originalSchema, originalTable, false, true);
239 } catch (Exception e) {
240 // Oracle throws an exception if the table is not found or is a
241 // SYNONYM
242 rs = null;
243 }
244 String indexName = null;
245 list = New.arrayList();
246 IndexType indexType = null;
247 if (rs != null) {
248 while (rs.next()) {
249 if (rs.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) {
250 // ignore index statistics
251 continue;
252 }
253 String newIndex = rs.getString("INDEX_NAME");
254 if (pkName.equals(newIndex)) {
255 continue;
256 }
257 if (indexName != null && !indexName.equals(newIndex)) {
258 addIndex(list, indexType);
259 indexName = null;
260 }
261 if (indexName == null) {
262 indexName = newIndex;
263 list.clear();
264 }
265 boolean unique = !rs.getBoolean("NON_UNIQUE");
266 indexType = unique ? IndexType.createUnique(false, false) : IndexType.createNonUnique(false);
267 String col = rs.getString("COLUMN_NAME");
268 col = convertColumnName(col);
269 Column column = columnMap.get(col);
270 list.add(column);
271 }
272 rs.close();
273 }
274 if (indexName != null) {
275 addIndex(list, indexType);
276 }
277 }
278
279 private static long convertPrecision(int sqlType, long precision) {
280 // workaround for an Oracle problem:
281 // for DATE columns, the reported precision is 7
282 // for DECIMAL columns, the reported precision is 0
283 switch (sqlType) {
284 case Types.DECIMAL:
285 case Types.NUMERIC:
286 if (precision == 0) {
287 precision = 65535;
288 }
289 break;
290 case Types.DATE:
291 precision = Math.max(ValueDate.PRECISION, precision);
292 break;
293 case Types.TIMESTAMP:
294 precision = Math.max(ValueTimestamp.PRECISION, precision);
295 break;
296 case Types.TIME:
297 precision = Math.max(ValueTime.PRECISION, precision);
298 break;
299 }
300 return precision;
301 }
302
303 private static int convertScale(int sqlType, int scale) {
304 // workaround for an Oracle problem:
305 // for DECIMAL columns, the reported precision is -127
306 switch (sqlType) {
307 case Types.DECIMAL:
308 case Types.NUMERIC:
309 if (scale < 0) {
310 scale = 32767;
311 }
312 break;
313 }
314 return scale;
315 }
316
317 private String convertColumnName(String columnName) {
318 if ((storesMixedCase || storesLowerCase) && columnName.equals(StringUtils.toLowerEnglish(columnName))) {
319 columnName = StringUtils.toUpperEnglish(columnName);
320 } else if (storesMixedCase && !supportsMixedCaseIdentifiers) {
321 // TeraData
322 columnName = StringUtils.toUpperEnglish(columnName);
323 } else if (storesMixedCase && storesMixedCaseQuoted) {
324 // MS SQL Server (identifiers are case insensitive even if quoted)
325 columnName = StringUtils.toUpperEnglish(columnName);
326 }
327 return columnName;
328 }
329
330 private void addIndex(ArrayList<Column> list, IndexType indexType) {
331 Column[] cols = new Column[list.size()];
332 list.toArray(cols);
333 Index index = new LinkedIndex(this, 0, IndexColumn.wrap(cols), indexType);
334 indexes.add(index);
335 }
336
337 public String getDropSQL() {
338 return "DROP TABLE IF EXISTS " + getSQL();
339 }
340
341 public String getCreateSQL() {
342 StringBuilder buff = new StringBuilder("CREATE FORCE ");
343 if (isTemporary()) {
344 if (globalTemporary) {
345 buff.append("GLOBAL ");
346 } else {
347 buff.append("LOCAL ");
348 }
349 buff.append("TEMPORARY ");
350 }
351 buff.append("LINKED TABLE ").append(getSQL());
352 if (comment != null) {
353 buff.append(" COMMENT ").append(StringUtils.quoteStringSQL(comment));
354 }
355 buff.append('(').
356 append(StringUtils.quoteStringSQL(driver)).
357 append(", ").
358 append(StringUtils.quoteStringSQL(url)).
359 append(", ").
360 append(StringUtils.quoteStringSQL(user)).
361 append(", ").
362 append(StringUtils.quoteStringSQL(password)).
363 append(", ").
364 append(StringUtils.quoteStringSQL(originalTable)).
365 append(')');
366 if (emitUpdates) {
367 buff.append(" EMIT UPDATES");
368 }
369 if (readOnly) {
370 buff.append(" READONLY");
371 }
372 buff.append(" /*" + JdbcSQLException.HIDE_SQL + "*/");
373 return buff.toString();
374 }
375
376 public Index addIndex(Session session, String indexName, int indexId, IndexColumn[] cols, IndexType indexType,
377 boolean create, String indexComment) {
378 throw DbException.getUnsupportedException("LINK");
379 }
380
381 public void lock(Session session, boolean exclusive, boolean force) {
382 // nothing to do
383 }
384
385 public boolean isLockedExclusively() {
386 return false;
387 }
388
389 public Index getScanIndex(Session session) {
390 return linkedIndex;
391 }
392
393 private void checkReadOnly() {
394 if (readOnly) {
395 throw DbException.get(ErrorCode.DATABASE_IS_READ_ONLY);
396 }
397 }
398
399 public void removeRow(Session session, Row row) {
400 checkReadOnly();
401 getScanIndex(session).remove(session, row);
402 }
403
404 public void addRow(Session session, Row row) {
405 checkReadOnly();
406 getScanIndex(session).add(session, row);
407 }
408
409 public void close(Session session) {
410 if (conn != null) {
411 try {
412 conn.close(false);
413 } finally {
414 conn = null;
415 }
416 }
417 }
418
419 public synchronized long getRowCount(Session session) {
420 String sql = "SELECT COUNT(*) FROM " + qualifiedTableName;
421 try {
422 PreparedStatement prep = execute(sql, null, false);
423 ResultSet rs = prep.getResultSet();
424 rs.next();
425 long count = rs.getLong(1);
426 rs.close();
427 reusePreparedStatement(prep, sql);
428 return count;
429 } catch (Exception e) {
430 throw wrapException(sql, e);
431 }
432 }
433
434 /**
435 * Wrap a SQL exception that occurred while accessing a linked table.
436 *
437 * @param sql the SQL statement
438 * @param ex the exception from the remote database
439 * @return the wrapped exception
440 */
441 public static DbException wrapException(String sql, Exception ex) {
442 SQLException e = DbException.toSQLException(ex);
443 return DbException.get(ErrorCode.ERROR_ACCESSING_LINKED_TABLE_2, e, sql, e.toString());
444 }
445
446 public String getQualifiedTable() {
447 return qualifiedTableName;
448 }
449
450 /**
451 * Execute a SQL statement using the given parameters. Prepared
452 * statements are kept in a hash map to avoid re-creating them.
453 *
454 * @param sql the SQL statement
455 * @param params the parameters or null
456 * @param reusePrepared if the prepared statement can be re-used immediately
457 * @return the prepared statement, or null if it is re-used
458 */
459 public PreparedStatement execute(String sql, ArrayList<Value> params, boolean reusePrepared) {
460 if (conn == null) {
461 throw connectException;
462 }
463 for (int retry = 0;; retry++) {
464 try {
465 synchronized (conn) {
466 PreparedStatement prep = preparedMap.remove(sql);
467 if (prep == null) {
468 prep = conn.getConnection().prepareStatement(sql);
469 }
470 if (trace.isDebugEnabled()) {
471 StatementBuilder buff = new StatementBuilder();
472 buff.append(getName()).append(":\n").append(sql);
473 if (params != null && params.size() > 0) {
474 buff.append(" {");
475 int i = 1;
476 for (Value v : params) {
477 buff.appendExceptFirst(", ");
478 buff.append(i++).append(": ").append(v.getSQL());
479 }
480 buff.append('}');
481 }
482 buff.append(';');
483 trace.debug(buff.toString());
484 }
485 if (params != null) {
486 for (int i = 0, size = params.size(); i < size; i++) {
487 Value v = params.get(i);
488 v.set(prep, i + 1);
489 }
490 }
491 prep.execute();
492 if (reusePrepared) {
493 reusePreparedStatement(prep, sql);
494 return null;
495 }
496 return prep;
497 }
498 } catch (SQLException e) {
499 if (retry >= MAX_RETRY) {
500 throw DbException.convert(e);
501 }
502 conn.close(true);
503 connect();
504 }
505 }
506 }
507
508 public void unlock(Session s) {
509 // nothing to do
510 }
511
512 public void checkRename() {
513 // ok
514 }
515
516 public void checkSupportAlter() {
517 throw DbException.getUnsupportedException("LINK");
518 }
519
520 public void truncate(Session session) {
521 throw DbException.getUnsupportedException("LINK");
522 }
523
524 public boolean canGetRowCount() {
525 return true;
526 }
527
528 public boolean canDrop() {
529 return true;
530 }
531
532 public String getTableType() {
533 return Table.TABLE_LINK;
534 }
535
536 public void removeChildrenAndResources(Session session) {
537 super.removeChildrenAndResources(session);
538 close(session);
539 database.removeMeta(session, getId());
540 driver = null;
541 url = user = password = originalTable = null;
542 preparedMap = null;
543 invalidate();
544 }
545
546 public boolean isOracle() {
547 return url.startsWith("jdbc:oracle:");
548 }
549
550 public ArrayList<Index> getIndexes() {
551 return indexes;
552 }
553
554 public long getMaxDataModificationId() {
555 // data may have been modified externally
556 return Long.MAX_VALUE;
557 }
558
559 public Index getUniqueIndex() {
560 for (Index idx : indexes) {
561 if (idx.getIndexType().isUnique()) {
562 return idx;
563 }
564 }
565 return null;
566 }
567
568 public void updateRows(Prepared prepared, Session session, RowList rows) {
569 boolean deleteInsert;
570 checkReadOnly();
571 if (emitUpdates) {
572 for (rows.reset(); rows.hasNext();) {
573 prepared.checkCanceled();
574 Row oldRow = rows.next();
575 Row newRow = rows.next();
576 linkedIndex.update(oldRow, newRow);
577 session.log(this, UndoLogRecord.DELETE, oldRow);
578 session.log(this, UndoLogRecord.INSERT, newRow);
579 }
580 deleteInsert = false;
581 } else {
582 deleteInsert = true;
583 }
584 if (deleteInsert) {
585 super.updateRows(prepared, session, rows);
586 }
587 }
588
589 public void setGlobalTemporary(boolean globalTemporary) {
590 this.globalTemporary = globalTemporary;
591 }
592
593 public void setReadOnly(boolean readOnly) {
594 this.readOnly = readOnly;
595 }
596
597 public long getRowCountApproximation() {
598 return ROW_COUNT_APPROXIMATION;
599 }
600
601 /**
602 * Add this prepared statement to the list of cached statements.
603 *
604 * @param prep the prepared statement
605 * @param sql the SQL statement
606 */
607 public void reusePreparedStatement(PreparedStatement prep, String sql) {
608 synchronized (conn) {
609 preparedMap.put(sql, prep);
610 }
611 }
612
613 public boolean isDeterministic() {
614 return false;
615 }
616
617 /**
618 * Linked tables don't know if they are readonly. This overwrites
619 * the default handling.
620 */
621 public void checkWritingAllowed() {
622 // only the target database can verify this
623 }
624
625 /**
626 * Convert the values if required. Default values are not set (kept as
627 * null).
628 *
629 * @param session the session
630 * @param row the row
631 */
632 public void validateConvertUpdateSequence(Session session, Row row) {
633 for (int i = 0; i < columns.length; i++) {
634 Value value = row.getValue(i);
635 if (value != null) {
636 // null means use the default value
637 Column column = columns[i];
638 Value v2 = column.validateConvertUpdateSequence(session, value);
639 if (v2 != value) {
640 row.setValue(i, v2);
641 }
642 }
643 }
644 }
645
646 /**
647 * Get or generate a default value for the given column. Default values are
648 * not set (kept as null).
649 *
650 * @param session the session
651 * @param column the column
652 * @return the value
653 */
654 public Value getDefaultValue(Session session, Column column) {
655 return null;
656 }
657
658 }
659