001: /*
002: * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
003: * (http://h2database.com/html/license.html).
004: * Initial Developer: H2 Group
005: */
006: package org.h2.table;
007:
008: import java.sql.Connection;
009: import java.sql.DatabaseMetaData;
010: import java.sql.PreparedStatement;
011: import java.sql.ResultSet;
012: import java.sql.ResultSetMetaData;
013: import java.sql.SQLException;
014: import java.sql.Statement;
015: import java.util.HashMap;
016:
017: import org.h2.command.Prepared;
018: import org.h2.constant.ErrorCode;
019: import org.h2.engine.Session;
020: import org.h2.index.Index;
021: import org.h2.index.IndexType;
022: import org.h2.index.LinkedIndex;
023: import org.h2.log.UndoLogRecord;
024: import org.h2.message.Message;
025: import org.h2.result.Row;
026: import org.h2.result.RowList;
027: import org.h2.schema.Schema;
028: import org.h2.util.JdbcUtils;
029: import org.h2.util.MathUtils;
030: import org.h2.util.ObjectArray;
031: import org.h2.util.StringUtils;
032: import org.h2.value.DataType;
033:
034: /**
035: * A linked table contains connection information for a table accessible by JDBC.
036: * The table may be stored in a different database.
037: */
038: public class TableLink extends Table {
039:
040: private String driver, url, user, password, originalTable,
041: qualifiedTableName;
042: private Connection conn;
043: private HashMap prepared = new HashMap();
044: private final ObjectArray indexes = new ObjectArray();
045: private final boolean emitUpdates;
046: private LinkedIndex linkedIndex;
047: private SQLException connectException;
048:
049: public TableLink(Schema schema, int id, String name, String driver,
050: String url, String user, String password,
051: String originalTable, boolean emitUpdates, boolean force)
052: throws SQLException {
053: super (schema, id, name, false);
054: this .driver = driver;
055: this .url = url;
056: this .user = user;
057: this .password = password;
058: this .originalTable = originalTable;
059: this .emitUpdates = emitUpdates;
060: try {
061: connect();
062: } catch (SQLException e) {
063: connectException = e;
064: if (!force) {
065: throw e;
066: }
067: Column[] cols = new Column[0];
068: setColumns(cols);
069: linkedIndex = new LinkedIndex(this , id, IndexColumn
070: .wrap(cols), IndexType.createNonUnique(false));
071: indexes.add(linkedIndex);
072: }
073: }
074:
075: private void connect() throws SQLException {
076: conn = JdbcUtils.getConnection(driver, url, user, password);
077: DatabaseMetaData meta = conn.getMetaData();
078: boolean storesLowerCase = meta.storesLowerCaseIdentifiers();
079: ResultSet rs = meta.getColumns(null, null, originalTable, null);
080: int i = 0;
081: ObjectArray columnList = new ObjectArray();
082: HashMap columnMap = new HashMap();
083: String catalog = null, schema = null;
084: while (rs.next()) {
085: String this Catalog = rs.getString("TABLE_CAT");
086: if (catalog == null) {
087: catalog = this Catalog;
088: }
089: String this Schema = rs.getString("TABLE_SCHEM");
090: if (schema == null) {
091: schema = this Schema;
092: }
093: if (!StringUtils.equals(catalog, this Catalog)
094: || !StringUtils.equals(schema, this Schema)) {
095: // if the table exists in multiple schemas or tables,
096: // use the alternative solution
097: columnMap.clear();
098: columnList.clear();
099: break;
100: }
101: String n = rs.getString("COLUMN_NAME");
102: if (storesLowerCase
103: && n.equals(StringUtils.toLowerEnglish(n))) {
104: n = StringUtils.toUpperEnglish(n);
105: }
106: int sqlType = rs.getInt("DATA_TYPE");
107: long precision = rs.getInt("COLUMN_SIZE");
108: int scale = rs.getInt("DECIMAL_DIGITS");
109: int displaySize = MathUtils.convertLongToInt(precision);
110: int type = DataType.convertSQLTypeToValueType(sqlType);
111: Column col = new Column(n, type, precision, scale,
112: displaySize);
113: col.setTable(this , i++);
114: columnList.add(col);
115: columnMap.put(n, col);
116: }
117: if (originalTable.indexOf('.') < 0
118: && !StringUtils.isNullOrEmpty(schema)) {
119: qualifiedTableName = schema + "." + originalTable;
120: } else {
121: qualifiedTableName = originalTable;
122: }
123: // check if the table is accessible
124: Statement stat = null;
125: try {
126: stat = conn.createStatement();
127: rs = stat.executeQuery("SELECT * FROM "
128: + qualifiedTableName + " T WHERE 1=0");
129: if (columnList.size() == 0) {
130: // alternative solution
131: ResultSetMetaData rsMeta = rs.getMetaData();
132: for (i = 0; i < rsMeta.getColumnCount();) {
133: String n = rsMeta.getColumnName(i + 1);
134: if (storesLowerCase
135: && n.equals(StringUtils.toLowerEnglish(n))) {
136: n = StringUtils.toUpperEnglish(n);
137: }
138: int sqlType = rsMeta.getColumnType(i + 1);
139: long precision = rsMeta.getPrecision(i + 1);
140: int scale = rsMeta.getScale(i + 1);
141: int displaySize = rsMeta
142: .getColumnDisplaySize(i + 1);
143: int type = DataType
144: .convertSQLTypeToValueType(sqlType);
145: Column col = new Column(n, type, precision, scale,
146: displaySize);
147: col.setTable(this , i++);
148: columnList.add(col);
149: columnMap.put(n, col);
150: }
151: }
152: } catch (SQLException e) {
153: throw Message.getSQLException(
154: ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1,
155: new String[] { originalTable + "(" + e.toString()
156: + ")" }, e);
157: } finally {
158: JdbcUtils.closeSilently(stat);
159: }
160: Column[] cols = new Column[columnList.size()];
161: columnList.toArray(cols);
162: setColumns(cols);
163: int id = getId();
164: linkedIndex = new LinkedIndex(this , id, IndexColumn.wrap(cols),
165: IndexType.createNonUnique(false));
166: indexes.add(linkedIndex);
167: rs = meta.getPrimaryKeys(null, null, originalTable);
168: String pkName = "";
169: ObjectArray list;
170: if (rs.next()) {
171: // the problem is, the rows are not sorted by KEY_SEQ
172: list = new ObjectArray();
173: do {
174: int idx = rs.getInt("KEY_SEQ");
175: if (pkName == null) {
176: pkName = rs.getString("PK_NAME");
177: }
178: while (list.size() < idx) {
179: list.add(null);
180: }
181: String col = rs.getString("COLUMN_NAME");
182: Column column = (Column) columnMap.get(col);
183: list.set(idx - 1, column);
184: } while (rs.next());
185: addIndex(list, IndexType.createPrimaryKey(false, false));
186: }
187: try {
188: rs = meta.getIndexInfo(null, null, originalTable, false,
189: false);
190: } catch (SQLException e) {
191: // Oracle throws an exception if the table is not found or is a
192: // SYNONYM
193: rs = null;
194: }
195: String indexName = null;
196: list = new ObjectArray();
197: IndexType indexType = null;
198: while (rs != null && rs.next()) {
199: String newIndex = rs.getString("INDEX_NAME");
200: if (pkName.equals(newIndex)) {
201: continue;
202: }
203: if (indexName != null && !indexName.equals(newIndex)) {
204: addIndex(list, indexType);
205: indexName = null;
206: }
207: if (indexName == null) {
208: indexName = newIndex;
209: list.clear();
210: }
211: boolean unique = !rs.getBoolean("NON_UNIQUE");
212: indexType = unique ? IndexType.createUnique(false, false)
213: : IndexType.createNonUnique(false);
214: String col = rs.getString("COLUMN_NAME");
215: Column column = (Column) columnMap.get(col);
216: list.add(column);
217: }
218: if (indexName != null) {
219: addIndex(list, indexType);
220: }
221: }
222:
223: private void addIndex(ObjectArray list, IndexType indexType) {
224: Column[] cols = new Column[list.size()];
225: list.toArray(cols);
226: Index index = new LinkedIndex(this , 0, IndexColumn.wrap(cols),
227: indexType);
228: indexes.add(index);
229: }
230:
231: public String getDropSQL() {
232: return "DROP TABLE IF EXISTS " + getSQL();
233: }
234:
235: public String getCreateSQL() {
236: StringBuffer buff = new StringBuffer();
237: buff.append("CREATE FORCE LINKED TABLE ");
238: buff.append(getSQL());
239: if (comment != null) {
240: buff.append(" COMMENT ");
241: buff.append(StringUtils.quoteStringSQL(comment));
242: }
243: buff.append("(");
244: buff.append(StringUtils.quoteStringSQL(driver));
245: buff.append(", ");
246: buff.append(StringUtils.quoteStringSQL(url));
247: buff.append(", ");
248: buff.append(StringUtils.quoteStringSQL(user));
249: buff.append(", ");
250: buff.append(StringUtils.quoteStringSQL(password));
251: buff.append(", ");
252: buff.append(StringUtils.quoteStringSQL(originalTable));
253: buff.append(")");
254: if (emitUpdates) {
255: buff.append(" EMIT UPDATES");
256: }
257: return buff.toString();
258: }
259:
260: public Index addIndex(Session session, String indexName,
261: int indexId, IndexColumn[] cols, IndexType indexType,
262: int headPos, String comment) throws SQLException {
263: throw Message.getUnsupportedException();
264: }
265:
266: public void lock(Session session, boolean exclusive, boolean force)
267: throws SQLException {
268: // nothing to do
269: }
270:
271: public boolean isLockedExclusively() {
272: return false;
273: }
274:
275: public Index getScanIndex(Session session) {
276: return linkedIndex;
277: }
278:
279: public void removeRow(Session session, Row row) throws SQLException {
280: getScanIndex(session).remove(session, row);
281: }
282:
283: public void addRow(Session session, Row row) throws SQLException {
284: getScanIndex(session).add(session, row);
285: }
286:
287: public void close(Session session) throws SQLException {
288: if (conn != null) {
289: try {
290: conn.close();
291: } finally {
292: conn = null;
293: }
294: }
295: }
296:
297: public long getRowCount(Session session) throws SQLException {
298: PreparedStatement prep = getPreparedStatement("SELECT COUNT(*) FROM "
299: + qualifiedTableName);
300: ResultSet rs = prep.executeQuery();
301: rs.next();
302: long count = rs.getLong(1);
303: rs.close();
304: return count;
305: }
306:
307: public String getQualifiedTable() {
308: return qualifiedTableName;
309: }
310:
311: public PreparedStatement getPreparedStatement(String sql)
312: throws SQLException {
313: if (conn == null) {
314: throw connectException;
315: }
316: PreparedStatement prep = (PreparedStatement) prepared.get(sql);
317: if (prep == null) {
318: prep = conn.prepareStatement(sql);
319: prepared.put(sql, prep);
320: }
321: return prep;
322: }
323:
324: public void unlock(Session s) {
325: // nothing to do
326: }
327:
328: public void checkRename() throws SQLException {
329: }
330:
331: public void checkSupportAlter() throws SQLException {
332: throw Message.getUnsupportedException();
333: }
334:
335: public void truncate(Session session) throws SQLException {
336: throw Message.getUnsupportedException();
337: }
338:
339: public boolean canGetRowCount() {
340: return true;
341: }
342:
343: public boolean canDrop() {
344: return true;
345: }
346:
347: public String getTableType() {
348: return Table.TABLE_LINK;
349: }
350:
351: public void removeChildrenAndResources(Session session)
352: throws SQLException {
353: super .removeChildrenAndResources(session);
354: close(session);
355: database.removeMeta(session, getId());
356: driver = null;
357: url = user = password = originalTable = null;
358: conn = null;
359: prepared = null;
360: invalidate();
361: }
362:
363: public ObjectArray getIndexes() {
364: return indexes;
365: }
366:
367: public long getMaxDataModificationId() {
368: // data may have been modified externally
369: return Long.MAX_VALUE;
370: }
371:
372: public Index getUniqueIndex() {
373: for (int i = 0; i < indexes.size(); i++) {
374: Index idx = (Index) indexes.get(i);
375: if (idx.getIndexType().isUnique()) {
376: return idx;
377: }
378: }
379: return null;
380: }
381:
382: public void updateRows(Prepared prepared, Session session,
383: RowList rows) throws SQLException {
384: boolean deleteInsert;
385: if (emitUpdates) {
386: for (rows.reset(); rows.hasNext();) {
387: prepared.checkCancelled();
388: Row oldRow = rows.next();
389: Row newRow = rows.next();
390: linkedIndex.update(session, oldRow, newRow);
391: session.log(this , UndoLogRecord.DELETE, oldRow);
392: session.log(this , UndoLogRecord.INSERT, newRow);
393: }
394: deleteInsert = false;
395: } else {
396: deleteInsert = true;
397: }
398: if (deleteInsert) {
399: super.updateRows(prepared, session, rows);
400: }
401: }
402:
403: }
|