Author: reschke
Date: Mon Feb 9 16:37:51 2015
New Revision: 1658458
URL: http://svn.apache.org/r1658458
Log:
OAK-2395 - MS SQL Support
MS SqlServer support - changes needed
- Changed column types and sizes
- Support for variation in limit, greatest, concat syntax
(ack Amit Jain)
Modified:
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBDocumentStore.java
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBJDBCTools.java
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/AbstractDocumentStoreTest.java
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/BasicDocumentStoreTest.java
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/DocumentStoreFixture.java
jackrabbit/oak/trunk/oak-parent/pom.xml
Modified:
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBDocumentStore.java
URL:
http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBDocumentStore.java?rev=1658458&r1=1658457&r2=1658458&view=diff
==============================================================================
---
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBDocumentStore.java
(original)
+++
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBDocumentStore.java
Mon Feb 9 16:37:51 2015
@@ -324,6 +324,7 @@ public class RDBDocumentStore implements
// implementation
+ enum FETCHFIRSTSYNTAX { FETCHFIRST, LIMIT, TOP};
/**
* Defines variation in the capabilities of different RDBs.
@@ -372,16 +373,44 @@ public class RDBDocumentStore implements
}
@Override
- public boolean needsConcat() {
- return true;
+ public FETCHFIRSTSYNTAX getFetchFirstSyntax() {
+ return FETCHFIRSTSYNTAX.LIMIT;
}
@Override
- public boolean needsLimit() {
- return true;
+ public String getConcatQueryString(int dataOctetLimit, int
dataLength) {
+ return "CONCAT(DATA, ?)";
+ }
+ },
+
+ MSSQL("Microsoft SQL Server") {
+ @Override
+ public String getTableCreationStatement(String tableName) {
+ // see https://issues.apache.org/jira/browse/OAK-2395
+ return ("create table " + tableName + " (ID nvarchar(512) not
null primary key, MODIFIED bigint, HASBINARY smallint, DELETEDONCE smallint,
MODCOUNT bigint, CMODCOUNT bigint, DSIZE bigint, DATA nvarchar(4000), BDATA
varbinary(max))");
}
- };
+ @Override
+ public FETCHFIRSTSYNTAX getFetchFirstSyntax() {
+ return FETCHFIRSTSYNTAX.TOP;
+ }
+
+ @Override
+ public String getConcatQueryString(int dataOctetLimit, int
dataLength) {
+ /*
+ * To avoid truncation when concatenating force an error when
+ * limit is above the octet limit
+ */
+ return "CASE WHEN LEN(DATA) <= " + (dataOctetLimit -
dataLength) + " THEN (DATA + CAST(? AS nvarchar("
+ + dataOctetLimit + "))) ELSE (DATA + CAST(DATA AS
nvarchar(max))) END";
+
+ }
+
+ @Override
+ public String getGreatestQueryString(String column) {
+ return "(select MAX(mod) from (VALUES (" + column + "), (?))
AS ALLMOD(mod))";
+ }
+ };
/**
* If the primary column is encoded in bytes.
@@ -400,17 +429,36 @@ public class RDBDocumentStore implements
}
/**
- * whether DB requires "CONCAT" over "||"
+ * Query syntax for "FETCH FIRST"
*/
- public boolean needsConcat() {
- return false;
+ public FETCHFIRSTSYNTAX getFetchFirstSyntax() {
+ return FETCHFIRSTSYNTAX.FETCHFIRST;
}
/**
- * whether DB requires "LIMIT" instead of "FETCH FIRST"
+ * Returns the CONCAT function or its equivalent function or sub-query.
+ * Note that the function MUST NOT cause a truncated value to be
+ * written!
+ *
+ * @param dataOctetLimit
+ * expected capacity of data column
+ * @param dataLength
+ * length of string to be inserted
+ *
+ * @return the concat query string
*/
- public boolean needsLimit() {
- return false;
+ public String getConcatQueryString(int dataOctetLimit, int dataLength)
{
+ return "DATA || CAST(? AS varchar(" + dataOctetLimit + "))";
+ }
+
+ /**
+ * Returns the GREATEST function or its equivalent function or
sub-query
+ * supported.
+ *
+ * @return the greatest query string
+ */
+ public String getGreatestQueryString(String column) {
+ return "GREATEST(" + column + ", ?)";
}
/**
@@ -1187,7 +1235,11 @@ public class RDBDocumentStore implements
private List<RDBRow> dbQuery(Connection connection, String tableName,
String minId, String maxId, String indexedProperty,
long startValue, int limit) throws SQLException {
- String t = "select ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY,
DELETEDONCE, DATA, BDATA from " + tableName
+ String t = "select ";
+ if (limit != Integer.MAX_VALUE && this.db.getFetchFirstSyntax() ==
FETCHFIRSTSYNTAX.TOP) {
+ t += "TOP " + limit + " ";
+ }
+ t += "ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DELETEDONCE, DATA,
BDATA from " + tableName
+ " where ID > ? and ID < ?";
if (indexedProperty != null) {
if (MODIFIED.equals(indexedProperty)) {
@@ -1207,9 +1259,20 @@ public class RDBDocumentStore implements
}
}
t += " order by ID";
+
if (limit != Integer.MAX_VALUE) {
- t += this.db.needsLimit() ? (" LIMIT " + limit) : (" FETCH FIRST "
+ limit + " ROWS ONLY");
+ switch (this.db.getFetchFirstSyntax()) {
+ case LIMIT:
+ t += " LIMIT " + limit;
+ break;
+ case FETCHFIRST:
+ t += " FETCH FIRST " + limit + " ROWS ONLY";
+ break;
+ default:
+ break;
+ }
}
+
PreparedStatement stmt = connection.prepareStatement(t);
List<RDBRow> result = new ArrayList<RDBRow>();
try {
@@ -1291,11 +1354,9 @@ public class RDBDocumentStore implements
private boolean dbAppendingUpdate(Connection connection, String tableName,
String id, Long modified, Boolean hasBinary,
Boolean deletedOnce, Long modcount, Long cmodcount, Long
oldmodcount, String appendData) throws SQLException {
StringBuilder t = new StringBuilder();
- t.append("update "
- + tableName
- + " set MODIFIED = GREATEST(MODIFIED, ?), HASBINARY = ?,
DELETEDONCE = ?, MODCOUNT = ?, CMODCOUNT = ?, DSIZE = DSIZE + ?, ");
- t.append(this.db.needsConcat() ? "DATA = CONCAT(DATA, ?) " : "DATA =
DATA || CAST(? AS varchar(" + this.dataLimitInOctets
- + ")) ");
+ t.append("update " + tableName + " set MODIFIED = " +
this.db.getGreatestQueryString("MODIFIED")
+ + ", HASBINARY = ?, DELETEDONCE = ?, MODCOUNT = ?, CMODCOUNT =
?, DSIZE = DSIZE + ?, ");
+ t.append("DATA = " +
this.db.getConcatQueryString(this.dataLimitInOctets, appendData.length()) + "
");
t.append("where ID = ?");
if (oldmodcount != null) {
t.append(" and MODCOUNT = ?");
@@ -1328,9 +1389,9 @@ public class RDBDocumentStore implements
private boolean dbBatchedAppendingUpdate(Connection connection, String
tableName, List<String> ids, Long modified,
String appendData) throws SQLException {
StringBuilder t = new StringBuilder();
- t.append("update " + tableName + " set MODIFIED = GREATEST(MODIFIED,
?), MODCOUNT = MODCOUNT + 1, DSIZE = DSIZE + ?, ");
- t.append(this.db.needsConcat() ? "DATA = CONCAT(DATA, ?) " : "DATA =
DATA || CAST(? AS varchar(" + this.dataLimitInOctets
- + ")) ");
+ t.append("update " + tableName + " set MODIFIED = " +
this.db.getGreatestQueryString("MODIFIED")
+ + ", MODCOUNT = MODCOUNT + 1, DSIZE = DSIZE + ?, ");
+ t.append("DATA = " +
this.db.getConcatQueryString(this.dataLimitInOctets, appendData.length()) + "
");
t.append("where ID in (");
for (int i = 0; i < ids.size(); i++) {
if (i != 0) {
Modified:
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBJDBCTools.java
URL:
http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBJDBCTools.java?rev=1658458&r1=1658457&r2=1658458&view=diff
==============================================================================
---
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBJDBCTools.java
(original)
+++
jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBJDBCTools.java
Mon Feb 9 16:37:51 2015
@@ -50,6 +50,8 @@ public class RDBJDBCTools {
return "com.mysql.jdbc.Driver";
} else if ("oracle".equals(type)) {
return "oracle.jdbc.OracleDriver";
+ } else if ("sqlserver".equals(type)) {
+ return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
} else {
return "";
}
Modified:
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/AbstractDocumentStoreTest.java
URL:
http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/AbstractDocumentStoreTest.java?rev=1658458&r1=1658457&r2=1658458&view=diff
==============================================================================
---
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/AbstractDocumentStoreTest.java
(original)
+++
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/AbstractDocumentStoreTest.java
Mon Feb 9 16:37:51 2015
@@ -80,7 +80,7 @@ public abstract class AbstractDocumentSt
Collection<Object[]> result = new ArrayList<Object[]>();
DocumentStoreFixture candidates[] = new DocumentStoreFixture[] {
DocumentStoreFixture.MEMORY, DocumentStoreFixture.MONGO,
DocumentStoreFixture.RDB_H2, DocumentStoreFixture.RDB_PG,
DocumentStoreFixture.RDB_DB2,
- DocumentStoreFixture.RDB_MYSQL,
DocumentStoreFixture.RDB_ORACLE };
+ DocumentStoreFixture.RDB_MYSQL,
DocumentStoreFixture.RDB_ORACLE, DocumentStoreFixture.RDB_MSSQL };
for (DocumentStoreFixture dsf : candidates) {
if (dsf.isAvailable()) {
Modified:
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/BasicDocumentStoreTest.java
URL:
http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/BasicDocumentStoreTest.java?rev=1658458&r1=1658457&r2=1658458&view=diff
==============================================================================
---
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/BasicDocumentStoreTest.java
(original)
+++
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/BasicDocumentStoreTest.java
Mon Feb 9 16:37:51 2015
@@ -728,7 +728,7 @@ public class BasicDocumentStoreTest exte
long cnt = 0;
byte bdata[] = new byte[65536];
String sdata = appendString;
- boolean needsConcat = super.dsname.contains("MySQL");
+ boolean needsConcat = super.dsname.contains("MySQL") ||
super.dsname.contains("Microsoft SQL Server");
int dataInChars = (super.dsname.contains("Oracle") ? 4000 : 16384);
int dataInBytes = dataInChars / 3;
Modified:
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/DocumentStoreFixture.java
URL:
http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/DocumentStoreFixture.java?rev=1658458&r1=1658457&r2=1658458&view=diff
==============================================================================
---
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/DocumentStoreFixture.java
(original)
+++
jackrabbit/oak/trunk/oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/DocumentStoreFixture.java
Mon Feb 9 16:37:51 2015
@@ -41,6 +41,7 @@ public abstract class DocumentStoreFixtu
public static final DocumentStoreFixture RDB_DB2 = new
RDBFixture("RDB-DB2", "jdbc:db2://localhost:50000/OAK", "oak", "geheim");
public static final DocumentStoreFixture RDB_MYSQL = new
RDBFixture("RDB-MySQL", "jdbc:mysql://localhost:3306/oak", "root", "geheim");
public static final DocumentStoreFixture RDB_ORACLE = new
RDBFixture("RDB-Oracle", "jdbc:oracle:thin:@localhost:1521:orcl", "system",
"geheim");
+ public static final DocumentStoreFixture RDB_MSSQL = new
RDBFixture("RDB-MSSql", "jdbc:sqlserver://localhost:1433;databaseName=OAK",
"sa", "geheim");
public static final DocumentStoreFixture MONGO = new
MongoFixture("mongodb://localhost:27017/oak");
public static final String TABLEPREFIX = "dstest_";
Modified: jackrabbit/oak/trunk/oak-parent/pom.xml
URL:
http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-parent/pom.xml?rev=1658458&r1=1658457&r2=1658458&view=diff
==============================================================================
--- jackrabbit/oak/trunk/oak-parent/pom.xml (original)
+++ jackrabbit/oak/trunk/oak-parent/pom.xml Mon Feb 9 16:37:51 2015
@@ -558,6 +558,21 @@
</dependencies>
</profile>
<profile>
+ <!-- requires local copy of MS SqlServer JDBC driver deployed to Maven
repo-->
+ <!-- for instance:
+ mvn install:install-file -Dfile=sqljdbc41.jar -Dpackaging=jar\
+ -DgroupId=com.microsoft.sqlserver -DartifactId=sqljdbc4 -Dversion=4.1
+ -->
+ <id>rdb-mssql</id>
+ <dependencies>
+ <dependency>
+ <groupId>com.microsoft.sqlserver</groupId>
+ <artifactId>sqljdbc4</artifactId>
+ <version>4.1</version>
+ </dependency>
+ </dependencies>
+ </profile>
+ <profile>
<!-- requires local copy of Oracle JDBC driver deployed to Maven repo-->
<!-- for instance:
mvn install:install-file -Dfile=ojdbc6.jar -Dpackaging=jar\
