[OAK-1266] DocumentStore implementation for relational databases

• Type: Epic
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Component/s: documentmk, rdbmk
• Epic Name: RDB Support
• Assignee: Unassigned
• Reporter: Julian Reschke
• Created: 05/Dec/13 12:55
• Updated: 18/Nov/20 15:03
• Resolved: 18/Nov/20 15:03

Description

There should be an alternative DocumentStore implementation that persists to SQL databases rather than MongoDB.

Sub-Tasks

• "stupid" SQL persistence of BlobStore: Closed
• "stupid" SQL persistence of DocumentStore: Closed
• ResultSet.getSize returns -1 failing QueryResultTest: Closed
• memory cache for RDB persistence: Closed
• IBM DB2 support: Closed
• extend DocumentNodeStoreService to support RDBPersistence: Closed
• find portable variant of SQL "limit" clause: Closed
• enable RDBBlobStore testing for non-H2 databases: Closed
• RDBBlobStore failures for large blobs: Closed
• catch ID mismatches in create operations: Closed
• RdbDocumentStore's create should batch more inserts: Resolved
• DS.query: add tests for query collations, also catch fire when collation is incorrect: Closed
• improve error handling for closed RDBBlobStore: Closed
• RDBBlobStore cache nonfunctional: Closed
• unit tests for concurrent DocumentStore access: Closed
• support RDB in oak-run benchmarks: Resolved
• RDB: MariaDB (MySQL) support: Resolved
• RDB: Oracle support: Resolved
• avoid use of system dependency for IBM DB2 JDBC JARs: Closed
• memory cache for RDB persistence: Closed
• RDB: decide on table layout: Closed
• DocumentNodeStoreService: make table prefixes in RDB persistence configurable: Open
• retry strategy for failed JDBC requests: Open
• remove RDB support from 1.0 branch: Closed
• oak-run should support repository upgrades with all available options: Closed
• improve connection handling: Closed
• cleanup JSON related code: Closed
• RDB: MS SQL Server support: Closed
• reduce primary key size in RDBBlobStore: Closed
• support RDB persistence in oak-run modes console and server: Closed
• port RDB support back to Oak 1.0: Closed
• RDB: convenience tool for dumping table creation statements: Closed
• update Postgres JDBC version to 9.4: Closed
• improve RDB diagnostics: Closed
• RDBBlobStoreTest should be able to run against multiple DB types: Closed
• add (experimental) support for Apache Derby: Closed
• RDBDocumentStore: use of "GREATEST" in SQL apparently doesn't have test coverage in unit tests: Closed
• RDBConnectionHandler: handle failure on setReadOnly() gracefully: Closed
• RDBDocumentStore: fail init when database config seems to be inadequate: Open
• RDB/DB2: change minimal supported version from 10.5 to 10.1, also log decimal version numbers as well: Closed
• RDBBlob/DocumentStore throws NPE when used after being closed: Closed
• RDBDocumentStore: mitigate effects of large query result sets: Closed
• RDBDocumentStore: "set" operation on _modified appears to be implemented as "max": Closed
• RDBDocumentStore: no custom SQL needed for GREATEST: Closed
• RDBDocumentStore: conditional fetch logic is reversed: Closed
• RDBConnectionHandler: log failures on setReadOnly() only once: Closed
• JDBC config for document store tests should be overridable: Closed
• BasicDocumentStoreTest: separate actual unit tests from performance tests: Closed
• RDBDocumentStore: more diagnostics for long-running queries: Closed
• RDB: switch to tomcat datasource implementation: Closed
• RDBDocumentStore: try PreparedStatement batching: Closed
• [sonar]Some statements not being closed in RDBDocumentStore: Closed
• RDBDocumentStore on MySQL: default transaction isolation level: Open
• RDB*Store: check transaction isolation level: Closed
• RDBDocumentStore: add support for optional additional index: Closed
• RDBDocumentStore: remove hardwired "id-is-binary" flag: Closed
• RDBDocumentStore: add more ID related tests: Open
• RDBDocumentStore: query() might fetch unneeded content: Open
• RDBDocumentStore: improve diagnostics for failed batch inserts: Closed
• RDBDocumentStore: improve diagnostics for failed inserts/updates caused by long data: Closed
• RDBDocumentStore: add BDATA DDL information to startup diagnostics: Closed
• reduce RDBDocumentStore class size, avoid code duplication with RDBBlobStore: Closed
• DocumentStorePerformanceTest executed together with unit tests: Closed
• RDBDocumentStore: add missing RDBHelper support for JOURNAL table: Closed
• OakDocumentRDBRepositoryStub should have working default for JDBC URL: Closed
• oak-run check for dropped tables broken: Open
• RDBBlobStore: speed up testBigBlob(), also improve memory usage: Closed
• RDBDocumentStore startup: log more DDL information (incl. index information): Closed
• RDBDocumentStore: improve MySQL diagnostics: Closed
• RDB*Store diagnostics: strip line feeds from product information: Closed
• RDBDocumentStorePerformanceTest leaks PreparedStatements: Closed
• RDBDocumentStore: improve DB2 diagnostics: Closed
• RDBDocumentStore: force DB2 to use a clustered index: Closed
• RDBDocumentStore: improve index diagnostics: Closed
• RDBDocumentStoreDB: leaking resultset: Closed
• RDBDocumentStore: when generating SQL for queries, leave out unneeded constraints: Closed
• RDBDocumentStore: update PostgresQL and MySQL JDBC drivers: Closed
• create RDB export tool: Closed
• DocumentStore tests: use named parametrization: Closed
• RDBDocumentStore: update JDBC drivers for PostgresQL, MySQL, and Derby: Closed
• RDBBlob/DocumentStore: reduce class complexity: Closed
• RDBDocumentStore: improve retry logic in updateDocument: Closed
• Increase lock stripes in RDBDocumentStore: Closed
• RDBDocumentStore: improve logging for server time difference detection: Closed
• RDBDocumentStore: server time detection for DB2 fails due to timezone/dst differences: Closed
• RDB support: extend RDB export tool for CSV export: Closed
• Stale documents in RDBDocumentStore cache: Closed
• RDBDocumentStore: cache update logic introduced for OAK-3566 should only be used for NODES collection: Closed
• Stale document in RDBDocumentStore cache: Closed
• RDBDocumentStore: improve logging for invalid data in persistence: Closed
• RDBDocumentStore on SQLServer: off-by-one bug may cause truncated JSON to be written: Closed
• RDBDocumentStore on SQLServer: append logic creates many different PreparedStatements: Closed
• RDBBlob/DocumentStore.finalize() do not call super.finalize(): Closed
• RDBDocumentStore: refactor update logic: Closed
• RDBDocumentStore shutdown: improve logging: Closed
• RDBDocumentStore diagnostics for Oracle might not contain index information: Closed
• RDBDataSourceFactory: restore ability to close the pool: Closed
• RDBDocumentSerializer: improve format of REMOVE_MAP_ENTRY operations: Open
• RDB/NodeStoreFixture fails to track DataSource instances: Closed
• Oracle DB doesn't support lists longer than 1000: Closed
• RDBBlobStoreTest should use named parameters: Closed
• AbstractRDBConnectionTest fails to close the DataSource: Closed
• MS SQL doesn't support more than 2100 parameters in one request: Closed
• RDB*Store: update PostgreSQL and MySQL JDBC driver dependencies: Closed
• RDBDocumentStore: batched append logic may loose property changes: Closed
• RDBDocumentStore: refactor JSON support: Closed
• RDBBlobStore: seen insert failures due to duplicate keys: Closed
• RDBDocumentStore: backport memory cache refactoring: Resolved
• RDBDocumentStore: StripedNodeDocumentLocks - special case root?: Closed
• RDBDocumentStore: export tool - improve handling of export files allowing to override column order: Closed
• Fix database-level row deadlock during bulk updates in RDB: Closed
• RDBDocumentStore: internalCreateOrUpdate() needs to check return code of insertDocuments(): Closed
• RDBDocumentStore: add system property for disabling bulk updates: Resolved
• RDBDocumentStore: always upsert in the bulk updates: Closed
• CacheConsistencyRDBTest creates overlong table names: Closed
• RDBDocumentStore: include table name when logging slow/excessive queries: Closed
• RDBDocumentStore: remove PostgreSQL workaround after upgrading to JDBC driver 9.4.1208: Closed
• RDBExport: misleading warning about column names: Closed
• RDBDocumentStore: create() for collections other than NODES is broken: Closed
• RDBDocumentStore: refactor version check to address driver version as well: Closed
• RDBDocumentStore: upgrade PostgreSQL JDBC driver dependency to 9.4.1208: Closed
• RDBJDBCTools: fix JDBC driver version check: Closed
• RDBBlobStore: improve error handling and logging: Closed
• RDBConnectionHandler: add logging when getting the connection takes long: Closed
• DocumentMK: refactor RDB-specific code: Closed
• RDBBlobStore: logging for garbage collection has parameters reversed: Closed
• RDBBlobStore: garbage collection fails to delete DATA records when there was subsequent blobstore activity: Closed
• DocumentNodeStoreIT.modifiedResetWithDiff fails for various RDBs: Closed
• RDBDocumentStore: conditional update to _modified property sometimes applied unconditionally: Closed
• RDBDocumentStore: add system property for disabling appending updates: Closed
• extend RDBExport to also optionally provide raw data: Open
• RDBDocumentStore: dead code in internalUpdate: Closed
• RDBDocumentStore: synchronize access to BloomFilter: Closed
• RDB*Store: bump up recommended DB2 version to 10.5: Closed
• RDBDocumentStore: transform long-running tests into proper Integration Tests: Open
• RDBDocumentStore: upgrade MySQL JDBC driver dependency to 5.1.39: Closed
• RDBDatasourceFactory should use pool config similar to sling datasource defaults: Closed
• RDB*Store: update tomcat-jdbc test dependency: Closed
• RDBDocumentStore: consistent handling of invalidate methods: Closed
• RDBDocumentStore: low-level read method should also support condition on MODIFIED value: Closed
• RDBDocumentStore: can't persist _modified value of null: Closed
• RDBBlobStoreTest should use RDBDatasourceWrapper: Closed
• RDBPreparedStatementWrapper: do not hide implementation bugs: Closed
• RDB*Store: failures with Tomcat JDBC pool's StatementCache interceptor: Closed
• BasicDocumentStore max id test might return misleading results: Closed
• RDB*Store: update Tomcat JDBC pool dependency: Closed
• RDBDocumentStore: in 1.0, cache invalidation is slightly different from 1.2: Closed
• Check usage of DocumentStoreException in RDBDocumentStore: Closed
• RDBDocumentStore: update PostgresQL JDBC driver: Closed
• RDB*Store: update mysql JDBC driver reference to 5.1.40: Closed
• RDB*Store: update postgresql JDBC driver reference to 9.4.1211: Closed
• RDBDocumentStore: potential NPE in document read: Closed
• improve DocumentNodeStoreService robustness for RDB configs: Closed
• RDB*Store: update postgresql JDBC driver reference to 9.4.1212: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 7.0.73: Closed
• RDBDocumentStore: improve logic that decides whether to try string appends: Open
• RDBDocumentStore: improve long query logging: Closed
• RDBDocumentStore on Oracle: ArrayIndexOutOfBoundsException in Oracle JDBC driver: Closed
• RDB*Store: update Oracle JDBC driver reference to 12.1.0.2.0: Closed
• RDB*Store: update Derby to release 10.13: Closed
• RDBDocumentStore: remove support for DBs without support for CASE statements in SELECT: Closed
• RDBDocumentStore: properly handle null values for system properties: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 7.0.75: Closed
• RDBDocumentStore: improve query support for VersionGC: Closed
• RDB*Store: implement test wrappers for DataSource etc: Resolved
• RDBDocumentStore: implement support for VersionGCSupport extensions added for OAK-4780: Closed
• RDB*Store: update PostgreSQL JDBC: Closed
• Create RDB-specific BlobReferenceIterator: Closed
• RDB*store fixtures: shorten table name prefixes for Oracle: Closed
• RDBDocumentStoreDB: missing @Override statements: Closed
• support cockroachdb: Open
• RDB*Store: update postgresql JDBC driver reference to 42.1.1: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 7.0.78: Closed
• RDB*Store: update postgresql JDBC driver reference to 42.1.4: Closed
• RDB*Store: update mysql JDBC driver reference to 5.1.44 (2017-08-30): Closed
• RDB*Store: update Tomcat JDBC pool dependency to 7.0.81: Closed
• RDB*Store: update DB2 JDBC dependency to 4.19.66: Closed
• Tomcat JDBC pool's StatementCache interceptor may cache borked PreparedStatements with DB2: Closed
• RDBDocumentStore: inconsistent handling of cache invalidation on remove(): Closed
• RDB*Store: update Derby to release 10.14: Closed
• RDB*Store: update Oracle JDBC dependency to 12.2.0.1: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 7.0.82: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 8.5.23 (for branches compatible with Java 7): Closed
• RDB*Store: require ojdbc 12.2.0.1 because of known issues in earlier versions: Closed
• RDBDocumentStoreJDBC: remove code which is dead due to removal of DocumentStore.update(): Resolved
• RDBDocumentSerializer: factor out columnProperties: Closed
• RDBDocumentStoreJDBC: remove unused parameter: Closed
• RDBDocumentStore: refactor table upgrade code: Closed
• add JDBC driver information to metadata map: Closed
• RDBDocumentStore.getStats() for MySQL: Closed
• RDBDocumentStore.getStats() for SQLServer: Closed
• RDB*Store: update mysql driver reference to 5.1.45: Closed
• RDBBlobStore may wrap SQLExceptions into RuntimeExceptions: Closed
• RDBDataSourceWrapper: properly name setTemporaryUpdateException: Closed
• RDBDocumentStoreDB: use try-with-resources in new code introduced for getStats(): Closed
• RDBDocumentStore.getStats() for Derby: Closed
• RDBDocumentStore: use try-with-resources for nodes cache locks: Closed
• RDBDocumentStore: use try-with-resources for ChangesTracker: Closed
• RDB: potential optimization - avoid creating strings and byte arrays for serialization: Open
• RDB*Store: update postgresql JDBC driver reference to 42.2.0: Closed
• RDBDocumentStore: potential NPE in error handling code: Closed
• RDB*Store: update tomcat jdbc dependency to 8.5.28: Closed
• RDB*Store: update postgresql JDBC driver reference to 42.2.1: Closed
• RDB*Store: add DEBUG level logging for filters in RDBVersionGCSupport: Closed
• RDB*Store for SQLServer: name the PK index for better readability: Closed
• RDBDocumentStore: make indices on SD* sparse where possible: Closed
• RDBDocumentStore: add index on _MODIFIED to improve VersionGC performance: Closed
• RDBDocumentStore: refactor index report: Closed
• RDBDocumentStore: missing rollback after delete failures: Closed
• RDB*Store: update postgresql JDBC driver reference to 42.2.2: Closed
• RDB*Store: update mysql driver reference to 5.1.46: Closed
• RDB*Store: update DB2 JDBC dependency to 4.19.72: Closed
• RDB*Store: update SQLServer JDBC dependency to 6.4.0.jre*: Closed
• RDB*Store: update mysql driver reference to 8.0.11: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 8.5.32: Closed
• RDB*Store: update postgresql JDBC driver reference to 42.2.4: Closed
• RDB*Store: update mysql driver reference to 8.0.12: Closed
• RDB*Store: update SQLServer JDBC dependency to 7.0.0.jre*: Closed
• RDB*Store: log message after failed DB upgrade too verbose: Closed
• RDB*Store: update postgresql JDBC driver reference to 42.2.5: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 8.5.33: Closed
• RDB*Store: provide support for testing with mysql inside docker: Open
• need to document oak-run for RDB: Closed
• update clustering documentation wrt rdbmk: Closed
• DocumentStore: test (and optionally optimize) bulk update fallback logic: Closed
• RDBDocumentStore: add perf logging for JDBC read operations: Closed
• RDB*Store: update mysql driver reference to 8.0.13: Closed
• RDB: add to Oak documentation: Closed
• RDB*Store: add profile for DB2 11.1 JDBC driver: Closed
• RDB*Store: update DB2 JDBC reference to 4.19.77: Closed
• RDBDocumentStore: add RDB-specific MissingLastRevSeeker: Closed
• oak-run: support "recovery" command for RDBDocumentStore: Closed
• RDBDocumentStore: minor improvements to GZIP compression of BLOB contents: Closed
• RDB: expose DDL generation functionality in oak-run: Closed
• RDB*Store: update Tomcat JDBC pool dependency to 8.5.38: Closed
• RDB*Store: update mysql-connector-java dependency to 8.0.15: Closed
• RDB*Store: move DB-specific config hints from Javadoc into oak-doc: Closed
• RDB*Store: add Oracle specific documentation: Resolved
• RDB*Store: add SQLServer specific documentation: Closed
• RDB*Store: update mssql-jdbc driver reference to 7.2.1.jre8: Closed
• RDBBlobStore: add perf logging for JDBC read operations: Closed
• RDB*Store: add Oracle specific documentation: Closed
• RDBDocumentStore: improve trace logging of batch operations: Closed
• RDBDocumentStore: docs obtained from cache may need revalidation before returned to caller: Resolved
• RDBDocumentStore: createOrUpdate with less than 3 ops suboptimal: Closed
• RDBDocumentStore: add DEBUG logging when fetching index metadata fails: Closed
• RDBDocumentStore: allow to turn off RDB-specific MissingLastRevSeeker: Closed
• update Tomcat JDBC dependency to 8.5.41: Closed
• RDBDocumentStore: refactor index dumping code: Closed
• RDBDocumentStoreJDBC: fix theoretically possible NPE in perflogging code: Closed
• Document use of oak-run for RDB: Closed
• RDBDocumentStore*: fix several potential but improbable NPEs: Closed
• RDBDocumentStore*: "reset clusterId tool" in oak-run: Closed
• RDBDocumentNodeStoreBuilder: refactor setRDBConnection for consistency: Closed
• Stop using deprecated DocumentMK.Builder in RDB tests: Closed
• rdb/oak-run: update usage and documentation for garbage command: Closed
• documentation steps missing to run sweep on RDBDocumentStore: Closed
• RDBConnectionHandler: add minimal open connection stats in TRACE log level: Closed
• RDB documentation: minor fixes to documentation of "revisions" command: Closed
• document RDB specific log messages: Closed
• RDB*Store: update postgresql jdbc driver reference to 42.2.6: Closed
• RDB*Store: update mysql jdbc driver reference to 8.0.16: Closed
• RDB*Store: update mssql jdbc driver reference to 7.2.2: Closed
• RDB*Store: update mysql jdbc driver reference to 8.0.17: Closed
• update tomcat-jdbc dependency to 8.5.43: Closed
• RDB*Store: update mssql-jdbc driver reference to 7.4.1.jre8: Closed
• RDB*Store: update postgresql jdbc driver reference to 42.2.8: Closed
• RDBDocumentStore: avoid potential dangerous use of addAll(entrySet) in logging code: Closed
• update Tomcat JDBC dependency to 8.5.47: Closed
• RDB*Store: update mysql jdbc driver reference to 8.0.18: Closed
• RDB*Store: improve OSGi related documentation: Closed
• RDB*Store: update Oracle JDBC dependency to 19.3.0.0: Open
• RDBBlobStore: dump table structure on startup: Closed
• RDBDocumentStore on SQL Server (additional diags) log DB creation time: Closed
• RDBDocumentStore: change diagnostics from String to Map: Closed
• RDBBlobStore on SQL Server: bad performance when default collation is of type SQL*: Open
• RDBBlobStore: when creating META table for SQL Server, assign readable name for PK: Closed
• RDB*Store: extract "additionalDiagnostics" code into separate class for re-use from RDBBlobStore: Closed
• RDBDocumentStore: use setNString() for columns known to be of type N*CHAR: Closed
• RDBBlobStore: log additional DB diagnostics (align with RDBDocumentStore): Closed
• RDBBlobStore: warn when legacy (SQLServer) default collation is active: Closed
• RDBDocumentStore: for SQL Server stats, include type_desc and fill_factor: Closed
• add RDBBlobStore performance test: Closed
• RDBDocumentStore: allow RDBVersionGC support fallback to simpler algorithm: Closed
• RDB*Store: update mysql jdbc driver reference to 8.0.19: Closed
• RDB*Store: update postgresql jdbc driver reference to 42.2.10: Closed
• RDB*Store: update mssql-jdbc driver reference to 8.2.1.jre8: Closed
• RDBBlobStore: on SQL Server, specify binary collations upon table creation: Open
• RDBBlobStore: warn when ID columns are using legacy "SQL Server collation": Open
• RDBBlobStore performance improvement for SQL Server (MSSQL): Open
• RDB*Store: update postgresql jdbc driver reference to 42.2.12: Closed
• RDB*Store: update mssql-jdbc driver reference to 8.2.2.jre8: Closed
• RDB*Store: update postgresql jdbc driver reference to 42.2.18: Closed