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.table; |
7 | |
8 | import java.util.ArrayList; |
9 | import java.util.Arrays; |
10 | import java.util.HashSet; |
11 | import org.h2.api.ErrorCode; |
12 | import org.h2.command.Prepared; |
13 | import org.h2.command.dml.Query; |
14 | import org.h2.engine.Constants; |
15 | import org.h2.engine.DbObject; |
16 | import org.h2.engine.Session; |
17 | import org.h2.engine.User; |
18 | import org.h2.expression.Alias; |
19 | import org.h2.expression.Expression; |
20 | import org.h2.expression.ExpressionColumn; |
21 | import org.h2.expression.ExpressionVisitor; |
22 | import org.h2.expression.Parameter; |
23 | import org.h2.index.Index; |
24 | import org.h2.index.IndexType; |
25 | import org.h2.index.ViewIndex; |
26 | import org.h2.message.DbException; |
27 | import org.h2.result.LocalResult; |
28 | import org.h2.result.Row; |
29 | import org.h2.result.SortOrder; |
30 | import org.h2.schema.Schema; |
31 | import org.h2.util.New; |
32 | import org.h2.util.SmallLRUCache; |
33 | import org.h2.util.StatementBuilder; |
34 | import org.h2.util.StringUtils; |
35 | import org.h2.util.SynchronizedVerifier; |
36 | import org.h2.value.Value; |
37 | |
38 | /** |
39 | * A view is a virtual table that is defined by a query. |
40 | * @author Thomas Mueller |
41 | * @author Nicolas Fortin, Atelier SIG, IRSTV FR CNRS 24888 |
42 | */ |
43 | public class TableView extends Table { |
44 | |
45 | private static final long ROW_COUNT_APPROXIMATION = 100; |
46 | |
47 | private String querySQL; |
48 | private ArrayList<Table> tables; |
49 | private String[] columnNames; |
50 | private Query viewQuery; |
51 | private ViewIndex index; |
52 | private boolean recursive; |
53 | private DbException createException; |
54 | private final SmallLRUCache<CacheKey, ViewIndex> indexCache = |
55 | SmallLRUCache.newInstance(Constants.VIEW_INDEX_CACHE_SIZE); |
56 | private long lastModificationCheck; |
57 | private long maxDataModificationId; |
58 | private User owner; |
59 | private Query topQuery; |
60 | private LocalResult recursiveResult; |
61 | private boolean tableExpression; |
62 | |
63 | public TableView(Schema schema, int id, String name, String querySQL, |
64 | ArrayList<Parameter> params, String[] columnNames, Session session, |
65 | boolean recursive) { |
66 | super(schema, id, name, false, true); |
67 | init(querySQL, params, columnNames, session, recursive); |
68 | } |
69 | |
70 | /** |
71 | * Try to replace the SQL statement of the view and re-compile this and all |
72 | * dependent views. |
73 | * |
74 | * @param querySQL the SQL statement |
75 | * @param columnNames the column names |
76 | * @param session the session |
77 | * @param recursive whether this is a recursive view |
78 | * @param force if errors should be ignored |
79 | */ |
80 | public void replace(String querySQL, String[] columnNames, Session session, |
81 | boolean recursive, boolean force) { |
82 | String oldQuerySQL = this.querySQL; |
83 | String[] oldColumnNames = this.columnNames; |
84 | boolean oldRecursive = this.recursive; |
85 | init(querySQL, null, columnNames, session, recursive); |
86 | DbException e = recompile(session, force); |
87 | if (e != null) { |
88 | init(oldQuerySQL, null, oldColumnNames, session, oldRecursive); |
89 | recompile(session, true); |
90 | throw e; |
91 | } |
92 | } |
93 | |
94 | private synchronized void init(String querySQL, ArrayList<Parameter> params, |
95 | String[] columnNames, Session session, boolean recursive) { |
96 | this.querySQL = querySQL; |
97 | this.columnNames = columnNames; |
98 | this.recursive = recursive; |
99 | index = new ViewIndex(this, querySQL, params, recursive); |
100 | SynchronizedVerifier.check(indexCache); |
101 | indexCache.clear(); |
102 | initColumnsAndTables(session); |
103 | } |
104 | |
105 | private static Query compileViewQuery(Session session, String sql) { |
106 | Prepared p = session.prepare(sql); |
107 | if (!(p instanceof Query)) { |
108 | throw DbException.getSyntaxError(sql, 0); |
109 | } |
110 | return (Query) p; |
111 | } |
112 | |
113 | /** |
114 | * Re-compile the view query and all views that depend on this object. |
115 | * |
116 | * @param session the session |
117 | * @param force if exceptions should be ignored |
118 | * @return the exception if re-compiling this or any dependent view failed |
119 | * (only when force is disabled) |
120 | */ |
121 | public synchronized DbException recompile(Session session, boolean force) { |
122 | try { |
123 | compileViewQuery(session, querySQL); |
124 | } catch (DbException e) { |
125 | if (!force) { |
126 | return e; |
127 | } |
128 | } |
129 | ArrayList<TableView> views = getViews(); |
130 | if (views != null) { |
131 | views = New.arrayList(views); |
132 | } |
133 | SynchronizedVerifier.check(indexCache); |
134 | indexCache.clear(); |
135 | initColumnsAndTables(session); |
136 | if (views != null) { |
137 | for (TableView v : views) { |
138 | DbException e = v.recompile(session, force); |
139 | if (e != null && !force) { |
140 | return e; |
141 | } |
142 | } |
143 | } |
144 | return force ? null : createException; |
145 | } |
146 | |
147 | private void initColumnsAndTables(Session session) { |
148 | Column[] cols; |
149 | removeViewFromTables(); |
150 | try { |
151 | Query query = compileViewQuery(session, querySQL); |
152 | this.querySQL = query.getPlanSQL(); |
153 | tables = New.arrayList(query.getTables()); |
154 | ArrayList<Expression> expressions = query.getExpressions(); |
155 | ArrayList<Column> list = New.arrayList(); |
156 | for (int i = 0, count = query.getColumnCount(); i < count; i++) { |
157 | Expression expr = expressions.get(i); |
158 | String name = null; |
159 | if (columnNames != null && columnNames.length > i) { |
160 | name = columnNames[i]; |
161 | } |
162 | if (name == null) { |
163 | name = expr.getAlias(); |
164 | } |
165 | int type = expr.getType(); |
166 | long precision = expr.getPrecision(); |
167 | int scale = expr.getScale(); |
168 | int displaySize = expr.getDisplaySize(); |
169 | Column col = new Column(name, type, precision, scale, displaySize); |
170 | col.setTable(this, i); |
171 | // Fetch check constraint from view column source |
172 | ExpressionColumn fromColumn = null; |
173 | if (expr instanceof ExpressionColumn) { |
174 | fromColumn = (ExpressionColumn) expr; |
175 | } else if (expr instanceof Alias) { |
176 | Expression aliasExpr = expr.getNonAliasExpression(); |
177 | if (aliasExpr instanceof ExpressionColumn) { |
178 | fromColumn = (ExpressionColumn) aliasExpr; |
179 | } |
180 | } |
181 | if (fromColumn != null) { |
182 | Expression checkExpression = fromColumn.getColumn() |
183 | .getCheckConstraint(session, name); |
184 | if (checkExpression != null) { |
185 | col.addCheckConstraint(session, checkExpression); |
186 | } |
187 | } |
188 | list.add(col); |
189 | } |
190 | cols = new Column[list.size()]; |
191 | list.toArray(cols); |
192 | createException = null; |
193 | viewQuery = query; |
194 | } catch (DbException e) { |
195 | e.addSQL(getCreateSQL()); |
196 | createException = e; |
197 | // if it can't be compiled, then it's a 'zero column table' |
198 | // this avoids problems when creating the view when opening the |
199 | // database |
200 | tables = New.arrayList(); |
201 | cols = new Column[0]; |
202 | if (recursive && columnNames != null) { |
203 | cols = new Column[columnNames.length]; |
204 | for (int i = 0; i < columnNames.length; i++) { |
205 | cols[i] = new Column(columnNames[i], Value.STRING); |
206 | } |
207 | index.setRecursive(true); |
208 | createException = null; |
209 | } |
210 | } |
211 | setColumns(cols); |
212 | if (getId() != 0) { |
213 | addViewToTables(); |
214 | } |
215 | } |
216 | |
217 | /** |
218 | * Check if this view is currently invalid. |
219 | * |
220 | * @return true if it is |
221 | */ |
222 | public boolean isInvalid() { |
223 | return createException != null; |
224 | } |
225 | |
226 | @Override |
227 | public PlanItem getBestPlanItem(Session session, int[] masks, |
228 | TableFilter filter, SortOrder sortOrder) { |
229 | PlanItem item = new PlanItem(); |
230 | item.cost = index.getCost(session, masks, filter, sortOrder); |
231 | final CacheKey cacheKey = new CacheKey(masks, session); |
232 | |
233 | synchronized (this) { |
234 | SynchronizedVerifier.check(indexCache); |
235 | ViewIndex i2 = indexCache.get(cacheKey); |
236 | if (i2 != null) { |
237 | item.setIndex(i2); |
238 | return item; |
239 | } |
240 | } |
241 | // We cannot hold the lock during the ViewIndex creation or we risk ABBA |
242 | // deadlocks if the view creation calls back into H2 via something like |
243 | // a FunctionTable. |
244 | ViewIndex i2 = new ViewIndex(this, index, session, masks); |
245 | synchronized (this) { |
246 | // have to check again in case another session has beat us to it |
247 | ViewIndex i3 = indexCache.get(cacheKey); |
248 | if (i3 != null) { |
249 | item.setIndex(i3); |
250 | return item; |
251 | } |
252 | indexCache.put(cacheKey, i2); |
253 | item.setIndex(i2); |
254 | } |
255 | return item; |
256 | } |
257 | |
258 | @Override |
259 | public boolean isQueryComparable() { |
260 | if (!super.isQueryComparable()) { |
261 | return false; |
262 | } |
263 | for (Table t : tables) { |
264 | if (!t.isQueryComparable()) { |
265 | return false; |
266 | } |
267 | } |
268 | if (topQuery != null && |
269 | !topQuery.isEverything(ExpressionVisitor.QUERY_COMPARABLE_VISITOR)) { |
270 | return false; |
271 | } |
272 | return true; |
273 | } |
274 | |
275 | @Override |
276 | public String getDropSQL() { |
277 | return "DROP VIEW IF EXISTS " + getSQL() + " CASCADE"; |
278 | } |
279 | |
280 | @Override |
281 | public String getCreateSQLForCopy(Table table, String quotedName) { |
282 | return getCreateSQL(false, true, quotedName); |
283 | } |
284 | |
285 | |
286 | @Override |
287 | public String getCreateSQL() { |
288 | return getCreateSQL(false, true); |
289 | } |
290 | |
291 | /** |
292 | * Generate "CREATE" SQL statement for the view. |
293 | * |
294 | * @param orReplace if true, then include the OR REPLACE clause |
295 | * @param force if true, then include the FORCE clause |
296 | * @return the SQL statement |
297 | */ |
298 | public String getCreateSQL(boolean orReplace, boolean force) { |
299 | return getCreateSQL(orReplace, force, getSQL()); |
300 | } |
301 | |
302 | private String getCreateSQL(boolean orReplace, boolean force, |
303 | String quotedName) { |
304 | StatementBuilder buff = new StatementBuilder("CREATE "); |
305 | if (orReplace) { |
306 | buff.append("OR REPLACE "); |
307 | } |
308 | if (force) { |
309 | buff.append("FORCE "); |
310 | } |
311 | buff.append("VIEW "); |
312 | buff.append(quotedName); |
313 | if (comment != null) { |
314 | buff.append(" COMMENT ").append(StringUtils.quoteStringSQL(comment)); |
315 | } |
316 | if (columns != null && columns.length > 0) { |
317 | buff.append('('); |
318 | for (Column c : columns) { |
319 | buff.appendExceptFirst(", "); |
320 | buff.append(c.getSQL()); |
321 | } |
322 | buff.append(')'); |
323 | } else if (columnNames != null) { |
324 | buff.append('('); |
325 | for (String n : columnNames) { |
326 | buff.appendExceptFirst(", "); |
327 | buff.append(n); |
328 | } |
329 | buff.append(')'); |
330 | } |
331 | return buff.append(" AS\n").append(querySQL).toString(); |
332 | } |
333 | |
334 | @Override |
335 | public void checkRename() { |
336 | // ok |
337 | } |
338 | |
339 | @Override |
340 | public boolean lock(Session session, boolean exclusive, boolean forceLockEvenInMvcc) { |
341 | // exclusive lock means: the view will be dropped |
342 | return false; |
343 | } |
344 | |
345 | @Override |
346 | public void close(Session session) { |
347 | // nothing to do |
348 | } |
349 | |
350 | @Override |
351 | public void unlock(Session s) { |
352 | // nothing to do |
353 | } |
354 | |
355 | @Override |
356 | public boolean isLockedExclusively() { |
357 | return false; |
358 | } |
359 | |
360 | @Override |
361 | public Index addIndex(Session session, String indexName, int indexId, |
362 | IndexColumn[] cols, IndexType indexType, boolean create, |
363 | String indexComment) { |
364 | throw DbException.getUnsupportedException("VIEW"); |
365 | } |
366 | |
367 | @Override |
368 | public void removeRow(Session session, Row row) { |
369 | throw DbException.getUnsupportedException("VIEW"); |
370 | } |
371 | |
372 | @Override |
373 | public void addRow(Session session, Row row) { |
374 | throw DbException.getUnsupportedException("VIEW"); |
375 | } |
376 | |
377 | @Override |
378 | public void checkSupportAlter() { |
379 | throw DbException.getUnsupportedException("VIEW"); |
380 | } |
381 | |
382 | @Override |
383 | public void truncate(Session session) { |
384 | throw DbException.getUnsupportedException("VIEW"); |
385 | } |
386 | |
387 | @Override |
388 | public long getRowCount(Session session) { |
389 | throw DbException.throwInternalError(); |
390 | } |
391 | |
392 | @Override |
393 | public boolean canGetRowCount() { |
394 | // TODO view: could get the row count, but not that easy |
395 | return false; |
396 | } |
397 | |
398 | @Override |
399 | public boolean canDrop() { |
400 | return true; |
401 | } |
402 | |
403 | @Override |
404 | public String getTableType() { |
405 | return Table.VIEW; |
406 | } |
407 | |
408 | @Override |
409 | public void removeChildrenAndResources(Session session) { |
410 | removeViewFromTables(); |
411 | super.removeChildrenAndResources(session); |
412 | database.removeMeta(session, getId()); |
413 | querySQL = null; |
414 | index = null; |
415 | invalidate(); |
416 | } |
417 | |
418 | @Override |
419 | public String getSQL() { |
420 | if (isTemporary()) { |
421 | return "(\n" + StringUtils.indent(querySQL) + ")"; |
422 | } |
423 | return super.getSQL(); |
424 | } |
425 | |
426 | public String getQuery() { |
427 | return querySQL; |
428 | } |
429 | |
430 | @Override |
431 | public Index getScanIndex(Session session) { |
432 | if (createException != null) { |
433 | String msg = createException.getMessage(); |
434 | throw DbException.get(ErrorCode.VIEW_IS_INVALID_2, |
435 | createException, getSQL(), msg); |
436 | } |
437 | PlanItem item = getBestPlanItem(session, null, null, null); |
438 | return item.getIndex(); |
439 | } |
440 | |
441 | @Override |
442 | public boolean canReference() { |
443 | return false; |
444 | } |
445 | |
446 | @Override |
447 | public ArrayList<Index> getIndexes() { |
448 | return null; |
449 | } |
450 | |
451 | @Override |
452 | public long getMaxDataModificationId() { |
453 | if (createException != null) { |
454 | return Long.MAX_VALUE; |
455 | } |
456 | if (viewQuery == null) { |
457 | return Long.MAX_VALUE; |
458 | } |
459 | // if nothing was modified in the database since the last check, and the |
460 | // last is known, then we don't need to check again |
461 | // this speeds up nested views |
462 | long dbMod = database.getModificationDataId(); |
463 | if (dbMod > lastModificationCheck && maxDataModificationId <= dbMod) { |
464 | maxDataModificationId = viewQuery.getMaxDataModificationId(); |
465 | lastModificationCheck = dbMod; |
466 | } |
467 | return maxDataModificationId; |
468 | } |
469 | |
470 | @Override |
471 | public Index getUniqueIndex() { |
472 | return null; |
473 | } |
474 | |
475 | private void removeViewFromTables() { |
476 | if (tables != null) { |
477 | for (Table t : tables) { |
478 | t.removeView(this); |
479 | } |
480 | tables.clear(); |
481 | } |
482 | } |
483 | |
484 | private void addViewToTables() { |
485 | for (Table t : tables) { |
486 | t.addView(this); |
487 | } |
488 | } |
489 | |
490 | private void setOwner(User owner) { |
491 | this.owner = owner; |
492 | } |
493 | |
494 | public User getOwner() { |
495 | return owner; |
496 | } |
497 | |
498 | /** |
499 | * Create a temporary view out of the given query. |
500 | * |
501 | * @param session the session |
502 | * @param owner the owner of the query |
503 | * @param name the view name |
504 | * @param query the query |
505 | * @param topQuery the top level query |
506 | * @return the view table |
507 | */ |
508 | public static TableView createTempView(Session session, User owner, |
509 | String name, Query query, Query topQuery) { |
510 | Schema mainSchema = session.getDatabase().getSchema(Constants.SCHEMA_MAIN); |
511 | String querySQL = query.getPlanSQL(); |
512 | TableView v = new TableView(mainSchema, 0, name, |
513 | querySQL, query.getParameters(), null, session, |
514 | false); |
515 | if (v.createException != null) { |
516 | throw v.createException; |
517 | } |
518 | v.setTopQuery(topQuery); |
519 | v.setOwner(owner); |
520 | v.setTemporary(true); |
521 | return v; |
522 | } |
523 | |
524 | private void setTopQuery(Query topQuery) { |
525 | this.topQuery = topQuery; |
526 | } |
527 | |
528 | @Override |
529 | public long getRowCountApproximation() { |
530 | return ROW_COUNT_APPROXIMATION; |
531 | } |
532 | |
533 | @Override |
534 | public long getDiskSpaceUsed() { |
535 | return 0; |
536 | } |
537 | |
538 | public int getParameterOffset() { |
539 | return topQuery == null ? 0 : topQuery.getParameters().size(); |
540 | } |
541 | |
542 | @Override |
543 | public boolean isDeterministic() { |
544 | if (recursive || viewQuery == null) { |
545 | return false; |
546 | } |
547 | return viewQuery.isEverything(ExpressionVisitor.DETERMINISTIC_VISITOR); |
548 | } |
549 | |
550 | public void setRecursiveResult(LocalResult value) { |
551 | if (recursiveResult != null) { |
552 | recursiveResult.close(); |
553 | } |
554 | this.recursiveResult = value; |
555 | } |
556 | |
557 | public LocalResult getRecursiveResult() { |
558 | return recursiveResult; |
559 | } |
560 | |
561 | public void setTableExpression(boolean tableExpression) { |
562 | this.tableExpression = tableExpression; |
563 | } |
564 | |
565 | public boolean isTableExpression() { |
566 | return tableExpression; |
567 | } |
568 | |
569 | @Override |
570 | public void addDependencies(HashSet<DbObject> dependencies) { |
571 | super.addDependencies(dependencies); |
572 | if (tables != null) { |
573 | for (Table t : tables) { |
574 | if (!Table.VIEW.equals(t.getTableType())) { |
575 | t.addDependencies(dependencies); |
576 | } |
577 | } |
578 | } |
579 | } |
580 | |
581 | /** |
582 | * The key of the index cache for views. |
583 | */ |
584 | private static final class CacheKey { |
585 | |
586 | private final int[] masks; |
587 | private final Session session; |
588 | |
589 | public CacheKey(int[] masks, Session session) { |
590 | this.masks = masks; |
591 | this.session = session; |
592 | } |
593 | |
594 | @Override |
595 | public int hashCode() { |
596 | final int prime = 31; |
597 | int result = 1; |
598 | result = prime * result + Arrays.hashCode(masks); |
599 | result = prime * result + session.hashCode(); |
600 | return result; |
601 | } |
602 | |
603 | @Override |
604 | public boolean equals(Object obj) { |
605 | if (this == obj) { |
606 | return true; |
607 | } |
608 | if (obj == null) { |
609 | return false; |
610 | } |
611 | if (getClass() != obj.getClass()) { |
612 | return false; |
613 | } |
614 | CacheKey other = (CacheKey) obj; |
615 | if (session != other.session) { |
616 | return false; |
617 | } |
618 | if (!Arrays.equals(masks, other.masks)) { |
619 | return false; |
620 | } |
621 | return true; |
622 | } |
623 | } |
624 | |
625 | } |