You are viewing a plain text version of this content. The canonical link for it is here.
Posted to oak-commits@jackrabbit.apache.org by am...@apache.org on 2015/01/19 06:17:49 UTC

svn commit: r1652909 - in /jackrabbit/oak/trunk: oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/ oak-core/src/test/java/org/apache/jackrabbit/oak/plugins/document/ oak-parent/

Author: amitj
Date: Mon Jan 19 05:17:48 2015
New Revision: 1652909

URL: http://svn.apache.org/r1652909
Log:
OAK-2395: RDB: MS SQL Server support
MS SqlServer support - changes needed
- Changed column types and sizes
- Support for variation in limit, greatest, concat syntax

Modified:
    jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBBlobStore.java
    jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBDocumentStore.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/RDBBlobStore.java
URL: http://svn.apache.org/viewvc/jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBBlobStore.java?rev=1652909&r1=1652908&r2=1652909&view=diff
==============================================================================
--- jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBBlobStore.java (original)
+++ jackrabbit/oak/trunk/oak-core/src/main/java/org/apache/jackrabbit/oak/plugins/document/rdb/RDBBlobStore.java Mon Jan 19 05:17:48 2015
@@ -155,6 +155,20 @@ public class RDBBlobStore extends Cachin
                 return "create table " + tableName +
                     " (ID varbinary(767) not null primary key, DATA mediumblob)";
             }
+        },
+
+        MSSQL("Microsoft SQL Server") {
+            @Override
+            public String getMetaTableCreationStatement(String tableName) {
+                return "create table " + tableName +
+                    " (ID nvarchar(767) not null primary key, LVL int, LASTMOD bigint)";
+            }
+
+            @Override
+            public String getDataTableCreationStatement(String tableName) {
+                return "create table " + tableName +
+                    " (ID nvarchar(767) not null primary key, DATA varbinary(max))";
+            }
         };
 
         /**

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=1652909&r1=1652908&r2=1652909&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 Jan 19 05:17:48 2015
@@ -371,7 +371,39 @@ public class RDBDocumentStore implements
 
             @Override
             public String instrumentLimitQuery(String query, int limit) {
-                return query + (" LIMIT " + limit);
+                return String.format(query, "") + (" LIMIT " + limit);
+            }
+        },
+
+        MSSQL("Microsoft SQL Server") {
+            public int getDataOctetLimit() {
+                return 4000;
+            }
+
+            @Override
+            public String getTableCreationStatement(String tableName) {
+                return ("create table " + tableName
+                    + " (ID nvarchar(512) COLLATE Latin1_General_BIN2 not null primary key, "
+                    + "MODIFIED bigint, HASBINARY smallint, MODCOUNT bigint, CMODCOUNT bigint, "
+                    + "DSIZE bigint, DATA nvarchar(4000), BDATA varbinary(max))"); }
+
+            @Override
+            public String getGreatestQueryString() {
+                return "(select MAX(mod) from (VALUES (MODIFIED), (?)) AS ALLMOD(mod))";
+            }
+
+            @Override
+            public String getConcatQueryString(int dataLength) {
+                /* To avoid truncation when concatenating force an error when limit is above the octet limit */
+                return  "CASE WHEN LEN(DATA) <= " + (getDataOctetLimit() - dataLength)
+                    + " THEN (DATA + CAST(? AS nvarchar(" + getDataOctetLimit() + ")))"
+                    + " ELSE DATA + CAST(DATA AS nvarchar(max)) END ";
+
+            }
+
+            @Override
+            public String instrumentLimitQuery(String query, int limit) {
+                return String.format(query, " TOP " + limit);
             }
         };
 
@@ -427,7 +459,7 @@ public class RDBDocumentStore implements
          * @return the instrumented query string
          */
         public String instrumentLimitQuery(String query, int limit) {
-            return query + (" FETCH FIRST " + limit + " ROWS ONLY");
+            return String.format(query, "") + (" FETCH FIRST " + limit + " ROWS ONLY");
         }
 
         /**
@@ -1149,7 +1181,10 @@ 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, DATA, BDATA from " + tableName + " where ID > ? and ID < ?";
+        /* Substitutions required
+            %1$s - TOP parameter to limit rows if supported
+        */
+        String t = "select %s ID, MODIFIED, MODCOUNT, CMODCOUNT, HASBINARY, DATA, BDATA from " + tableName + " where ID > ? and ID < ?";
         if (indexedProperty != null) {
             if (MODIFIED.equals(indexedProperty)) {
                 t += " and MODIFIED >= ?";
@@ -1163,6 +1198,8 @@ public class RDBDocumentStore implements
         t += " order by ID";
         if (limit != Integer.MAX_VALUE) {
             t = db.instrumentLimitQuery(t, limit);
+        } else {
+            t = String.format(t, "");
         }
 
         PreparedStatement stmt = connection.prepareStatement(t);

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=1652909&r1=1652908&r2=1652909&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 Jan 19 05:17:48 2015
@@ -730,7 +730,8 @@ public class BasicDocumentStoreTest exte
             byte bdata[] = new byte[65536];
             String sdata = appendString;
             boolean needsConcat = super.dsname.contains(DocumentStoreFixture.RDB_MYSQL.getName());
-            int dataInChars = ((super.dsname.contains(DocumentStoreFixture.RDB_ORACLE.getName())) ? 4000 : 16384);
+            int dataInChars = ((super.dsname.contains(DocumentStoreFixture.RDB_ORACLE.getName()) ||
+                super.dsname.contains(DocumentStoreFixture.RDB_MSSQL.getName())) ? 4000 : 16384);
             int dataInBytes = dataInChars / 3;
 
             while (System.currentTimeMillis() < end) {
@@ -790,10 +791,14 @@ public class BasicDocumentStoreTest exte
                     } else if (mode == 3) {
                         PreparedStatement stmt = connection.prepareStatement("update "
                             + table
-                            + " set " +
+                            + " set "
+                            + (super.dsname.contains(DocumentStoreFixture.RDB_MSSQL.getName()) ?
+                                "DATA = CASE WHEN LEN(DATA) <= " + (dataInChars - appendString.length())
+                                    + " THEN (DATA + CAST(? AS nvarchar(" + dataInChars + ")))"
+                                    + " ELSE DATA + CAST(DATA AS nvarchar(max)) END " :
                             (needsConcat ?
                                 "DATA = CONCAT(DATA, ?)" :
-                                "DATA = DATA || CAST(? as varchar(" + dataInChars + "))")
+                                "DATA = DATA || CAST(? as varchar(" + dataInChars + "))"))
                             + " where ID = ?");
                         try {
                             stmt.setString(1, appendString);

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=1652909&r1=1652908&r2=1652909&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 Jan 19 05:17:48 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://10.40.202.13:1453;databaseName=oak", "oak", "geheimRoot1234");
     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=1652909&r1=1652908&r2=1652909&view=diff
==============================================================================
--- jackrabbit/oak/trunk/oak-parent/pom.xml (original)
+++ jackrabbit/oak/trunk/oak-parent/pom.xml Mon Jan 19 05:17:48 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\