1 | /* |
2 | * Copyright 2004-2014 H2 Group. Multiple-Licensed under the MPL 2.0, |
3 | * and the EPL 1.0 (http://h2database.com/html/license.html). |
4 | * Initial Developer: H2 Group |
5 | */ |
6 | package org.h2.index; |
7 | |
8 | import java.sql.PreparedStatement; |
9 | import java.sql.ResultSet; |
10 | import java.util.ArrayList; |
11 | import org.h2.engine.Constants; |
12 | import org.h2.engine.Session; |
13 | import org.h2.message.DbException; |
14 | import org.h2.result.Row; |
15 | import org.h2.result.SearchRow; |
16 | import org.h2.result.SortOrder; |
17 | import org.h2.table.Column; |
18 | import org.h2.table.IndexColumn; |
19 | import org.h2.table.TableFilter; |
20 | import org.h2.table.TableLink; |
21 | import org.h2.util.New; |
22 | import org.h2.util.StatementBuilder; |
23 | import org.h2.value.Value; |
24 | import org.h2.value.ValueNull; |
25 | |
26 | /** |
27 | * A linked index is a index for a linked (remote) table. |
28 | * It is backed by an index on the remote table which is accessed over JDBC. |
29 | */ |
30 | public class LinkedIndex extends BaseIndex { |
31 | |
32 | private final TableLink link; |
33 | private final String targetTableName; |
34 | private long rowCount; |
35 | |
36 | public LinkedIndex(TableLink table, int id, IndexColumn[] columns, |
37 | IndexType indexType) { |
38 | initBaseIndex(table, id, null, columns, indexType); |
39 | link = table; |
40 | targetTableName = link.getQualifiedTable(); |
41 | } |
42 | |
43 | @Override |
44 | public String getCreateSQL() { |
45 | return null; |
46 | } |
47 | |
48 | @Override |
49 | public void close(Session session) { |
50 | // nothing to do |
51 | } |
52 | |
53 | private static boolean isNull(Value v) { |
54 | return v == null || v == ValueNull.INSTANCE; |
55 | } |
56 | |
57 | @Override |
58 | public void add(Session session, Row row) { |
59 | ArrayList<Value> params = New.arrayList(); |
60 | StatementBuilder buff = new StatementBuilder("INSERT INTO "); |
61 | buff.append(targetTableName).append(" VALUES("); |
62 | for (int i = 0; i < row.getColumnCount(); i++) { |
63 | Value v = row.getValue(i); |
64 | buff.appendExceptFirst(", "); |
65 | if (v == null) { |
66 | buff.append("DEFAULT"); |
67 | } else if (isNull(v)) { |
68 | buff.append("NULL"); |
69 | } else { |
70 | buff.append('?'); |
71 | params.add(v); |
72 | } |
73 | } |
74 | buff.append(')'); |
75 | String sql = buff.toString(); |
76 | try { |
77 | link.execute(sql, params, true); |
78 | rowCount++; |
79 | } catch (Exception e) { |
80 | throw TableLink.wrapException(sql, e); |
81 | } |
82 | } |
83 | |
84 | @Override |
85 | public Cursor find(Session session, SearchRow first, SearchRow last) { |
86 | ArrayList<Value> params = New.arrayList(); |
87 | StatementBuilder buff = new StatementBuilder("SELECT * FROM "); |
88 | buff.append(targetTableName).append(" T"); |
89 | for (int i = 0; first != null && i < first.getColumnCount(); i++) { |
90 | Value v = first.getValue(i); |
91 | if (v != null) { |
92 | buff.appendOnlyFirst(" WHERE "); |
93 | buff.appendExceptFirst(" AND "); |
94 | Column col = table.getColumn(i); |
95 | buff.append(col.getSQL()); |
96 | if (v == ValueNull.INSTANCE) { |
97 | buff.append(" IS NULL"); |
98 | } else { |
99 | buff.append(">="); |
100 | addParameter(buff, col); |
101 | params.add(v); |
102 | } |
103 | } |
104 | } |
105 | for (int i = 0; last != null && i < last.getColumnCount(); i++) { |
106 | Value v = last.getValue(i); |
107 | if (v != null) { |
108 | buff.appendOnlyFirst(" WHERE "); |
109 | buff.appendExceptFirst(" AND "); |
110 | Column col = table.getColumn(i); |
111 | buff.append(col.getSQL()); |
112 | if (v == ValueNull.INSTANCE) { |
113 | buff.append(" IS NULL"); |
114 | } else { |
115 | buff.append("<="); |
116 | addParameter(buff, col); |
117 | params.add(v); |
118 | } |
119 | } |
120 | } |
121 | String sql = buff.toString(); |
122 | try { |
123 | PreparedStatement prep = link.execute(sql, params, false); |
124 | ResultSet rs = prep.getResultSet(); |
125 | return new LinkedCursor(link, rs, session, sql, prep); |
126 | } catch (Exception e) { |
127 | throw TableLink.wrapException(sql, e); |
128 | } |
129 | } |
130 | |
131 | private void addParameter(StatementBuilder buff, Column col) { |
132 | if (col.getType() == Value.STRING_FIXED && link.isOracle()) { |
133 | // workaround for Oracle |
134 | // create table test(id int primary key, name char(15)); |
135 | // insert into test values(1, 'Hello') |
136 | // select * from test where name = ? -- where ? = "Hello" > no rows |
137 | buff.append("CAST(? AS CHAR(").append(col.getPrecision()).append("))"); |
138 | } else { |
139 | buff.append('?'); |
140 | } |
141 | } |
142 | |
143 | @Override |
144 | public double getCost(Session session, int[] masks, TableFilter filter, |
145 | SortOrder sortOrder) { |
146 | return 100 + getCostRangeIndex(masks, rowCount + |
147 | Constants.COST_ROW_OFFSET, filter, sortOrder); |
148 | } |
149 | |
150 | @Override |
151 | public void remove(Session session) { |
152 | // nothing to do |
153 | } |
154 | |
155 | @Override |
156 | public void truncate(Session session) { |
157 | // nothing to do |
158 | } |
159 | |
160 | @Override |
161 | public void checkRename() { |
162 | throw DbException.getUnsupportedException("LINKED"); |
163 | } |
164 | |
165 | @Override |
166 | public boolean needRebuild() { |
167 | return false; |
168 | } |
169 | |
170 | @Override |
171 | public boolean canGetFirstOrLast() { |
172 | return false; |
173 | } |
174 | |
175 | @Override |
176 | public Cursor findFirstOrLast(Session session, boolean first) { |
177 | // TODO optimization: could get the first or last value (in any case; |
178 | // maybe not optimized) |
179 | throw DbException.getUnsupportedException("LINKED"); |
180 | } |
181 | |
182 | @Override |
183 | public void remove(Session session, Row row) { |
184 | ArrayList<Value> params = New.arrayList(); |
185 | StatementBuilder buff = new StatementBuilder("DELETE FROM "); |
186 | buff.append(targetTableName).append(" WHERE "); |
187 | for (int i = 0; i < row.getColumnCount(); i++) { |
188 | buff.appendExceptFirst("AND "); |
189 | Column col = table.getColumn(i); |
190 | buff.append(col.getSQL()); |
191 | Value v = row.getValue(i); |
192 | if (isNull(v)) { |
193 | buff.append(" IS NULL "); |
194 | } else { |
195 | buff.append('='); |
196 | addParameter(buff, col); |
197 | params.add(v); |
198 | buff.append(' '); |
199 | } |
200 | } |
201 | String sql = buff.toString(); |
202 | try { |
203 | PreparedStatement prep = link.execute(sql, params, false); |
204 | int count = prep.executeUpdate(); |
205 | link.reusePreparedStatement(prep, sql); |
206 | rowCount -= count; |
207 | } catch (Exception e) { |
208 | throw TableLink.wrapException(sql, e); |
209 | } |
210 | } |
211 | |
212 | /** |
213 | * Update a row using a UPDATE statement. This method is to be called if the |
214 | * emit updates option is enabled. |
215 | * |
216 | * @param oldRow the old data |
217 | * @param newRow the new data |
218 | */ |
219 | public void update(Row oldRow, Row newRow) { |
220 | ArrayList<Value> params = New.arrayList(); |
221 | StatementBuilder buff = new StatementBuilder("UPDATE "); |
222 | buff.append(targetTableName).append(" SET "); |
223 | for (int i = 0; i < newRow.getColumnCount(); i++) { |
224 | buff.appendExceptFirst(", "); |
225 | buff.append(table.getColumn(i).getSQL()).append('='); |
226 | Value v = newRow.getValue(i); |
227 | if (v == null) { |
228 | buff.append("DEFAULT"); |
229 | } else { |
230 | buff.append('?'); |
231 | params.add(v); |
232 | } |
233 | } |
234 | buff.append(" WHERE "); |
235 | buff.resetCount(); |
236 | for (int i = 0; i < oldRow.getColumnCount(); i++) { |
237 | Column col = table.getColumn(i); |
238 | buff.appendExceptFirst(" AND "); |
239 | buff.append(col.getSQL()); |
240 | Value v = oldRow.getValue(i); |
241 | if (isNull(v)) { |
242 | buff.append(" IS NULL"); |
243 | } else { |
244 | buff.append('='); |
245 | params.add(v); |
246 | addParameter(buff, col); |
247 | } |
248 | } |
249 | String sql = buff.toString(); |
250 | try { |
251 | link.execute(sql, params, true); |
252 | } catch (Exception e) { |
253 | throw TableLink.wrapException(sql, e); |
254 | } |
255 | } |
256 | |
257 | @Override |
258 | public long getRowCount(Session session) { |
259 | return rowCount; |
260 | } |
261 | |
262 | @Override |
263 | public long getRowCountApproximation() { |
264 | return rowCount; |
265 | } |
266 | |
267 | @Override |
268 | public long getDiskSpaceUsed() { |
269 | return 0; |
270 | } |
271 | } |