1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 package com.isenshi.util.extlib;
17
18 import java.sql.Connection;
19 import java.sql.PreparedStatement;
20 import java.sql.ResultSet;
21 import java.sql.SQLException;
22
23 import org.apache.slide.common.Service;
24 import org.apache.slide.common.ServiceAccessException;
25 import org.apache.slide.common.Uri;
26 import org.apache.slide.content.NodeRevisionDescriptor;
27 import org.apache.slide.content.NodeRevisionNumber;
28 import org.apache.slide.lock.LockTokenNotFoundException;
29 import org.apache.slide.lock.NodeLock;
30 import org.apache.slide.security.NodePermission;
31 import org.apache.slide.store.impl.rdbms.SequenceAdapter;
32 import org.apache.slide.store.impl.rdbms.StandardRDBMSAdapter;
33 import org.apache.slide.structure.ObjectNode;
34 import org.apache.slide.structure.ObjectNotFoundException;
35 import org.apache.slide.util.logger.Logger;
36
37 /***
38 * <p>
39 * RDBMSAdapter for HSQLDB
40 * This expects HSQLDB version 1.8.0 and thus some methods and query might not work
41 * on ealier release of HSQLDB.
42 * </p>
43 *
44 * <p>
45 * Points to override StandardRDBMSAdapter.
46 * <ol>
47 * <li>DELETE SQL
48 * Within StandardRDBMSAdapter, DELETE SQL's are issued in the manner like,
49 * <code>DELETE TAB1 FROM TAB1 WHERE TAB1.COL1 = ...</code>
50 * In this case, HSQLDB reports the error like,
51 * <samp>Unexpected token TAB1, requires FROM in statement [delete tab1]</samp>
52 * so, you need to change the SQL as
53 * <code>DELETE FROM TAB1 WHERE TAB1.COL1 = ... </code>
54 * </p>
55 *
56 * <p>
57 * Also, HSQLDB cannot execute the DELETE SQL which has join pattern in where
58 * clause like, (although this looks to work in PostgreSQL, see PostgreSQLRDBMSAdapter)
59 * <code>DELETE TAB1 FROM TAB1.COL1 = TAB2.COL1 AND TAB2.COL2 = TAB3.COL2 AND...</code>
60 * The error message reported in above is very, very, very confusing, becase it says like
61 * <samp>Column not found: COLNAME in statement</samp>
62 * although the column "COLNAME" exists properly.
63 * I observed this intermittently. Apparently, if the data matching to the condition exists,
64 * it tends to report this error. Here, to avoid this,
65 * <code>DELETE TAB1 FROM TAB1.COL1 IN (SELECT TAB2.COL1 FROM TAB2,TAB3 WHERE TAB2.COLS2 = TAB3.COL2 ...</code>
66 * </li>
67 * <li>Function
68 * Some function calls seems to be different from standard one.
69 * org.hsqldb.Library includes collect manner of functions available in HSQLDB
70 * Also, FAQ (http://hsqldb.org/web/hsqlFAQ.html#STORED) might help you.
71 * </li>
72 * </ol>
73 * </p>
74 *
75 * <p>
76 * Implements SequenceAdapter
77 * If #isSequenceSupported returns false, <class#method> throws Exception, so
78 * it might be better to implements SequenceAdapter.
79 * </p>
80 *
81 * @author someda
82 */
83 public class HSQLDBRDBMSAdapter extends StandardRDBMSAdapter implements SequenceAdapter{
84
85
86 private static final String DELETE_LINKS_SQL =
87 "DELETE FROM LINKS WHERE LINKS.URI_ID IN " +
88 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING = ?)";
89
90 private static final String DELETE_VERSION_HISTORY_SQL =
91 "DELETE FROM VERSION_HISTORY WHERE VERSION_HISTORY.URI_ID IN " +
92 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING =?)";
93
94 private static final String DELETE_VERSION_SQL =
95 "DELETE FROM VERSION WHERE VERSION.URI_ID IN " +
96 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING = ?)";
97
98 private static final String DELETE_OBJECT_SQL =
99 "DELETE FROM OBJECT WHERE OBJECT.URI_ID IN " +
100 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING = ?)";
101
102 private static final String DELETE_URI_SQL =
103 "DELETE FROM URI WHERE URI_STRING = ?";
104
105 private static final String DELETE_VERSION_CONTENT_SQL =
106 "DELETE FROM VERSION_CONTENT WHERE VERSION_CONTENT.VERSION_ID IN " +
107 "(SELECT VERSION_HISTORY.VERSION_ID FROM VERSION_HISTORY,URI WHERE VERSION_HISTORY.REVISION_NO = ? AND VERSION_HISTORY.URI_ID=URI.URI_ID AND URI.URI_STRING=?)";
108
109 private static final String DELETE_VERSION_LABELS_SQL =
110 "DELETE FROM VERSION_LABELS WHERE VERSION_LABELS.VERSION_ID IN " +
111 "(SELECT VERSION_HISTORY.VERSION_ID FROM VERSION_HISTORY,URI WHERE VERSION_HISTORY.REVISION_NO = ? AND VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.URI_STRING = ?)";
112
113 private static final String DELETE_PROPERTIES_SQL =
114 "DELETE FROM PROPERTIES WHERE PROPERTIES.VERSION_ID IN " +
115 "(SELECT VERSION_HISTORY.VERSION_ID FROM VERSION_HISTORY,URI WHERE VERSION_HISTORY.REVISION_NO = ? AND VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.URI_STRING = ?)";
116
117 private static final String DELETE_VERSION_PREDS_SQL =
118 "DELETE FROM VERSION_PREDS WHERE VERSION_PREDS.VERSION_ID IN "+
119 "(SELECT VERSION_HISTORY.VERSION_ID FROM VERSION_HISTORY,URI WHERE VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.URI_STRING = ?)";
120
121 private static final String DELETE_LOCKS_SQL =
122 "DELETE FROM LOCKS WHERE LOCK_ID IN " +
123 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING=?)";
124
125 private static final String DELETE_PERMISSIONS_BYNODEPERMISSION_SQL = "DELETE FROM PERMISSIONS WHERE " +
126 "PERMISSIONS.OBJECT_ID IN (SELECT ou.URI_ID FROM URI ou WHERE ou.URI_STRING = ?) AND " +
127 "PERMISSIONS.SUBJECT_ID IN (SELECT su.URI_ID FROM URI su WHERE su.URI_STRING = ?) AND " +
128 "PERMISSIONS.ACTION_ID IN (SELECT au.URI_ID FROM URI au WHERE au.URI_STRING = ?) AND " +
129 "PERMISSIONS.VERSION_NO = ?";
130
131 private static final String DELETE_PERMISSIONS_BYNODEPERMISSION_SQL_WITHOUTREVISION = "DELETE FROM PERMISSIONS WHERE " +
132 "PERMISSIONS.OBJECT_ID IN (SELECT ou.URI_ID FROM URI ou WHERE ou.URI_STRING = ?) AND " +
133 "PERMISSIONS.SUBJECT_ID IN (SELECT su.URI_ID FROM URI su WHERE su.URI_STRING = ?) AND " +
134 "PERMISSIONS.ACTION_ID IN (SELECT au.URI_ID FROM URI au WHERE au.URI_STRING = ?)";
135
136 private static final String DELETE_PERMISSIONS_BYURL_SQL =
137 "DELETE FROM PERMISSIONS WHERE PERMISSIONS.OBJECT_ID IN " +
138 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING = ?)";
139
140 private static final String DELETE_BINDING_SQL =
141 "DELETE FROM BINDING WHERE BINDING.URI_ID IN " +
142 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING = ?)";
143
144 private static final String DELETE_PARENT_BINDING_SQL =
145 "DELETE FROM PARENT_BINDING WHERE PARENT_BINDING.URI_ID IN " +
146 "(SELECT URI.URI_ID FROM URI WHERE URI.URI_STRING = ?)";
147
148
149 private static final String SELECT_SEQUENCE_SQL =
150 "SELECT * FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES WHERE SEQUENCE_NAME = ?";
151
152 private static final String REVISION_DELIMITER =".";
153
154 private static final int[] BRANCH_WEIGHTS = {10,9,8,7,6,5,4,3,2,1};
155
156 public HSQLDBRDBMSAdapter(Service service, Logger logger){
157 super(service,logger);
158 }
159
160
161
162
163 public void removeObject(Connection connection, Uri uri, ObjectNode object) throws ServiceAccessException, ObjectNotFoundException {
164 try {
165 clearBinding(connection, uri);
166 }catch (SQLException e) {
167 throw createException(e,uri.toString());
168 }
169
170
171
172 executeSQLWithUri(connection,DELETE_LINKS_SQL,uri);
173
174
175
176 executeSQLWithUri(connection,DELETE_VERSION_HISTORY_SQL,uri);
177
178
179 executeSQLWithUri(connection,DELETE_VERSION_SQL,uri);
180
181
182 executeSQLWithUri(connection,DELETE_OBJECT_SQL,uri);
183
184
185 executeSQLWithUri(connection,DELETE_URI_SQL,uri);
186 }
187
188 public void removeRevisionContent(Connection connection,Uri uri,NodeRevisionDescriptor revisionDescriptor) throws ServiceAccessException {
189
190 PreparedStatement statement = null;
191 try {
192
193 statement = connection.prepareStatement(DELETE_VERSION_CONTENT_SQL);
194 statement.setString(1,revisionDescriptor.getRevisionNumber().toString());
195 statement.setString(2, uri.toString());
196 statement.executeUpdate();
197 } catch (Exception e) {
198 getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
199 throw new ServiceAccessException(service, e);
200 } finally{
201 close(statement);
202 }
203 }
204
205 public void removeRevisionDescriptor(Connection connection,Uri uri,NodeRevisionNumber revisionNumber)
206 throws ServiceAccessException {
207 PreparedStatement statement = null;
208 try {
209
210 statement = connection.prepareStatement(DELETE_VERSION_LABELS_SQL);
211 statement.setString(1, revisionNumber.toString());
212 statement.setString(2, uri.toString());
213 statement.executeUpdate();
214 close(statement);
215
216
217 statement = connection.prepareStatement(DELETE_PROPERTIES_SQL);
218 statement.setString(1, revisionNumber.toString());
219 statement.setString(2, uri.toString());
220 statement.executeUpdate();
221 close(statement);
222 } catch (SQLException e) {
223 throw createException(e,uri.toString());
224 }finally{
225 if(statement != null) close(statement);
226 }
227 }
228
229 public void removeRevisionDescriptors(Connection connection, Uri uri) throws ServiceAccessException {
230
231 executeSQLWithUri(connection,DELETE_VERSION_PREDS_SQL,uri);
232 }
233
234 public void removeLock(Connection connection, Uri uri, NodeLock lock)
235 throws ServiceAccessException, LockTokenNotFoundException {
236
237 executeSQLWithUri(connection,DELETE_LOCKS_SQL,uri);
238 executeSQLWithUri(connection,DELETE_URI_SQL,uri);
239 }
240
241 public void revokePermission(Connection connection,Uri uri,NodePermission permission) throws ServiceAccessException {
242 if (permission == null) return;
243 PreparedStatement statement = null;
244
245 try {
246 NodeRevisionNumber revisionNumber = permission.getRevisionNumber();
247
248 statement = (revisionNumber != null)? connection.prepareStatement(DELETE_PERMISSIONS_BYNODEPERMISSION_SQL) :
249 connection.prepareStatement(DELETE_PERMISSIONS_BYNODEPERMISSION_SQL_WITHOUTREVISION);
250
251 statement =connection.prepareStatement(DELETE_PERMISSIONS_BYNODEPERMISSION_SQL);
252 statement.setString(1, permission.getObjectUri());
253 statement.setString(2, permission.getSubjectUri());
254 statement.setString(3, permission.getActionUri());
255 if(revisionNumber != null) statement.setString(4, revisionNumber.toString());
256
257 statement.executeUpdate();
258 } catch (SQLException e) {
259 throw createException(e,uri.toString());
260 } finally {
261 close(statement);
262 }
263 }
264
265 public void revokePermissions(Connection connection, Uri uri)
266 throws ServiceAccessException {
267 executeSQLWithUri(connection,DELETE_PERMISSIONS_BYURL_SQL,uri);
268 }
269
270 protected void clearBinding(Connection connection, Uri uri)
271 throws ServiceAccessException, ObjectNotFoundException, SQLException {
272 executeSQLWithUri(connection,DELETE_BINDING_SQL,uri);
273 executeSQLWithUri(connection,DELETE_PARENT_BINDING_SQL,uri);
274 }
275
276 /***
277 * <p>
278 * Determines sorting order from VERSION_HISTORY table by its
279 * REVISION_NO column.
280 * </p>
281 *
282 * <p>
283 * As the type of REVISION_NO is VARCHAR, the default ordering rule
284 * doesn't provide desirable results.
285 * For example, we have 11 nodes, revisioned from 1.0 to 1.10.
286 * Under the default rule, the node 1.10 comes earlier than node 1.2.
287 * (here the number before dot is called "major", and one after dot
288 * called "minor")
289 * </p>
290 *
291 * <p>
292 * StandardRDBMSAdapter#convertRevisionNumberToComparable uses convert
293 * function, though HSQLDB doesn't have it (see org.hsqldb.Library),
294 * thus here needed to override that method.
295 * </p>
296 *
297 * <p>
298 * First, you can get this by the following mannger,
299 * <blockquote>
300 * major = "java.lang.Integer.parseInt"(TRIM(TRAILING '.' FROM SUBSTRING(vh.REVISION_NO,1,LOCATE('.',vh.REVISION_NO))))<br/>
301 * minor = "java.lang.Integer.parseInt"(SUBSTRING(vh.REVISION_NO,LOCATE('.',vh.REVISION_NO)+1))
302 * </blockquote>
303 * This looks complicated, especially the first one uses 4 methods!!
304 * <ol>
305 * <li>parseInt<br/>
306 * needed for numerical sort, TRIM and SUBSTRING returs String object.
307 * if not specified, its result will be same as the case without using functions.</li>
308 * <li>TRIM (its argument specification itself seems complicated ...)<br/>
309 * needed to remove the dot of the string of SUBSTRING returns.
310 * SUBSTRING argument type is (String, int, Integer) thus, you couldn't specify
311 * SUBSTRING(vh.REVISION_NO,1,LOCATE('.',vh.REVISION_NO)-1).</li>
312 * <li>SUBSTRING<br/>
313 * needed to separate revision number.</li>
314 * <li>LOCATE<br/>
315 * needed for to know the index of dot.</li>
316 * </ol>
317 * </p>
318 *
319 * <p>
320 * Thus, custom 2 simple methods are used, majorVersion and minorVersion
321 * defined in this class.
322 * </p>
323 *
324 * @see StandardRDBMSAdapter#retrieveRevisionDescriptors(java.sql.Connection, org.apache.slide.common.Uri)
325 */
326 protected String convertRevisionNumberToComparable(String revisionNumber) {
327 String classname = getClass().getName();
328 String major = "\"" + classname + ".majorVersion\"(" + revisionNumber + ")";
329 String minor = "\"" + classname + ".minorVersion\"(" + revisionNumber + ")";
330
331
332 return major + "," + minor;
333 }
334
335
336
337
338
339
340
341
342
343
344 protected static String normalizeSequenceName(String sequenceName) {
345 return sequenceName.replace('-', '_').toUpperCase() + "_SEQ";
346 }
347
348 public boolean isSequenceSupported(Connection conn) {
349 return true;
350 }
351
352 public boolean createSequence(Connection conn, String sequenceName) throws ServiceAccessException {
353
354 String query = "CREATE SEQUENCE \"" + normalizeSequenceName(sequenceName) + "\" AS BIGINT";
355 PreparedStatement statement = null;
356 try {
357 statement = conn.prepareStatement(query);
358 statement.executeUpdate();
359 return true;
360 } catch (SQLException e) {
361 throw createException(e,sequenceName);
362 } finally {
363 close(statement);
364 }
365 }
366
367 public long nextSequenceValue(Connection conn, String sequenceName) throws ServiceAccessException {
368 PreparedStatement statement = null;
369 ResultSet res = null;
370
371 try {
372 statement = conn.prepareStatement("SELECT NEXT VALUE FOR " + normalizeSequenceName(sequenceName) + " FROM INFORMATION_SCHEMA.SYSTEM_SEQUENCES WHERE SEQUENCE_NAME = ?");
373 statement.setString(1,normalizeSequenceName(sequenceName));
374 res = statement.executeQuery();
375 if (!res.next()) {
376 throw new ServiceAccessException(service, "Could not increment sequence " + sequenceName);
377 }
378 long value = res.getLong(1);
379 return value;
380 } catch (SQLException e) {
381 throw createException(e,sequenceName);
382 } finally {
383 close(statement, res);
384 }
385 }
386
387 public boolean sequenceExists(Connection conn, String sequenceName) throws ServiceAccessException {
388
389 PreparedStatement statement = null;
390 ResultSet res = null;
391
392 try {
393 statement = conn.prepareStatement(SELECT_SEQUENCE_SQL);
394 statement.setString(1,normalizeSequenceName(sequenceName));
395 res = statement.executeQuery();
396 return (res.next()) ? true : false;
397 } catch (SQLException e) {
398 return false;
399 } finally {
400 close(statement, res);
401 }
402 }
403
404
405
406
407
408
409
410
411
412 /***
413 * For HSQLDB sorting by int order.
414 * Extracts major version number from whole revision number, like
415 * returns 1 for "1.3".
416 *
417 * @param revisionNumber must contains "." to separate here, and also
418 * expects %d.%d style.
419 * @throws IllegalAugumentException if revisionNumber doesn't contains ".".
420 * @throws NumberFormatException if extracted string before "." doesn't decimal string.
421 * @return major version of revisionNumber
422 */
423 public static int majorVersion(String revisionNumber){
424 int idx = revisionNumber.indexOf(REVISION_DELIMITER);
425 if(idx == -1)
426 throw new IllegalArgumentException("revision number must contains \"" + REVISION_DELIMITER + "\".");
427 return Integer.parseInt(revisionNumber.substring(0,idx));
428 }
429
430 /***
431 * For HSQLDB sorting by int order.
432 * Extracts major version number from whole revision number, like
433 * returns 3 for "1.3".
434 * Also, revision number is expected as %d.%d style, not %d.%d.%d, that is,
435 * for "1.3.3", this throws NumberFormatException because "3.3" can't be
436 * formated as integer.
437 *
438 * @param revisionNumber must contains "." to separate here, and also
439 * expects %d.%d style.
440 * @throws IllegalAugumentException if revisionNumber doesn't contains ".".
441 * @throws NumberFormatException if extracted string before "." doesn't decimal string.
442 * @return major version of revisionNumber
443 */
444 public static int minorVersion(String revisionNumber){
445 int idx = revisionNumber.indexOf(REVISION_DELIMITER);
446 if(idx == -1){
447 throw new IllegalArgumentException("revision number must contains \"" + REVISION_DELIMITER + "\".");
448 }
449
450 return Integer.parseInt(revisionNumber.substring(idx+1));
451 }
452
453
454 /***
455 * Spliting revisionNumber by revision delimiter (".") and
456 * returns weighted integer which is addition of each elements
457 * multiplied with the corresponding weights.
458 *
459 * In branching, revisionNumber become to have sub branch version,
460 * like, 1.3 to 1.3.0 or 1.3.0.1, and so on.
461 * Spliting 2 parts of revisionNumber by "." works well for 1.3, doesn't for 1.3.0.1.
462 *
463 * @see <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=37254">ASF Bugzilla Bug 37254 RDBMS store and Branching.</a>
464 * @param revisionNumber up to 10-depth supported (like 1.1.1.1.1.1.1.1.1.1)
465 * @return weighted version integer for given revisionNumber
466 * @throws IllegalStateException if revisionNumber exceeds the support limit depth of branch.
467 */
468 public static int weightedVersion(String revisionNumber){
469
470 String[] numbers = revisionNumber.split("//.");
471 if(numbers.length > BRANCH_WEIGHTS.length){
472 throw new IllegalStateException("revision number " + revisionNumber + " exceeds the support limit depth of branch " + BRANCH_WEIGHTS.length);
473 }
474
475 int revsum = 0;
476 for(int i = 0; i<numbers.length;i++){
477
478 revsum = revsum + Integer.parseInt(numbers[i]) * BRANCH_WEIGHTS[i];
479 }
480 return revsum;
481 }
482
483
484 private void executeSQLWithUri(Connection conn, String sql, Uri uri) throws ServiceAccessException{
485 PreparedStatement stmt = null;
486 try{
487 stmt = conn.prepareStatement(sql);
488 stmt.setString(1, uri.toString());
489 stmt.executeUpdate();
490 }catch(SQLException e){
491 createException(e,uri.toString());
492 }finally{
493 close(stmt);
494 }
495 }
496 }