Support query fetch limits.

7 files changed, 329 insertions, 2 deletions

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/H2ExceptionTransformer.java b/src/main/java/com/amazon/carbonado/repo/jdbc/H2ExceptionTransformer.java
new file mode 100644
index 0000000..e367cb0
--- /dev/null
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/H2ExceptionTransformer.java
@@ -0,0 +1,35 @@
+/*

+ * Copyright 2008 Amazon Technologies, Inc. or its affiliates.

+ * Amazon, Amazon.com and Carbonado are trademarks or registered trademarks

+ * of Amazon Technologies, Inc. or its affiliates.  All rights reserved.

+ *

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ *

+ *     http://www.apache.org/licenses/LICENSE-2.0

+ *

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ */

+

+package com.amazon.carbonado.repo.jdbc;

+

+import java.sql.SQLException;

+

+/**

+ * 

+ *

+ * @author Brian S O'Neill

+ * @since 1.2

+ */

+class H2ExceptionTransformer extends JDBCExceptionTransformer {

+    public static int DUPLICATE_KEY = 23001;

+

+    public boolean isUniqueConstraintError(SQLException e) {

+        return DUPLICATE_KEY == e.getErrorCode();

+    }

+}

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java
new file mode 100644
index 0000000..6850fbc
--- /dev/null
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java
@@ -0,0 +1,60 @@
+/*

+ * Copyright 2008 Amazon Technologies, Inc. or its affiliates.

+ * Amazon, Amazon.com and Carbonado are trademarks or registered trademarks

+ * of Amazon Technologies, Inc. or its affiliates.  All rights reserved.

+ *

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ *

+ *     http://www.apache.org/licenses/LICENSE-2.0

+ *

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ */

+

+package com.amazon.carbonado.repo.jdbc;

+

+/**

+ * 

+ *

+ * @author Brian S O'Neill

+ * @since 1.2

+ */

+class H2SupportStrategy extends JDBCSupportStrategy {

+    private static final String TRUNCATE_STATEMENT = "TRUNCATE TABLE %s";

+

+    protected H2SupportStrategy(JDBCRepository repo) {

+        super(repo);

+

+        setTruncateTableStatement(TRUNCATE_STATEMENT);

+    }

+

+    @Override

+    JDBCExceptionTransformer createExceptionTransformer() {

+        return new H2ExceptionTransformer();

+    }

+

+    @Override

+    SliceOption getSliceOption() {

+        return SliceOption.LIMIT_AND_OFFSET;

+    }

+

+    @Override

+    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

+        if (limit) {

+            if (offset) {

+                return select.concat(" LIMIT ? OFFSET ?");

+            } else {

+                return select.concat(" LIMIT ?");

+            }

+        } else if (offset) {

+            return select.concat(" LIMIT 2147483647 OFFSET ?");

+        } else {

+            return select;

+        }

+    }

+}

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java
index 54ff24a..8810c09 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java
@@ -44,6 +44,8 @@ import com.amazon.carbonado.SupportException;
 import com.amazon.carbonado.Transaction;

 import com.amazon.carbonado.Trigger;

 import com.amazon.carbonado.capability.IndexInfo;

+import com.amazon.carbonado.cursor.EmptyCursor;

+import com.amazon.carbonado.cursor.LimitCursor;

 import com.amazon.carbonado.filter.AndFilter;

 import com.amazon.carbonado.filter.Filter;

 import com.amazon.carbonado.filter.FilterValues;

@@ -647,6 +649,98 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
         }

 

         @Override

+        public Cursor<S> fetch(FilterValues<S> values, long from, Long to) throws FetchException {

+            if (to != null && (to - from) <= 0) {

+                return EmptyCursor.the();

+            }

+

+            JDBCSupportStrategy.SliceOption option = mSupportStrategy.getSliceOption();

+

+            String select;

+

+            switch (option) {

+            case NOT_SUPPORTED: default:

+                return super.fetch(values, from, to);

+            case LIMIT_ONLY:

+                if (from > 0 || to == null) {

+                    return super.fetch(values, from, to);

+                }

+                select = prepareSelect(values, false);

+                select = mSupportStrategy.buildSelectWithSlice(select, true, false);

+                break;

+            case OFFSET_ONLY:

+                if (from <= 0) {

+                    return super.fetch(values, from, to);

+                }

+                select = prepareSelect(values, false);

+                select = mSupportStrategy.buildSelectWithSlice(select, false, true);

+                break;

+            case LIMIT_AND_OFFSET:

+            case OFFSET_AND_LIMIT:

+                select = prepareSelect(values, false);

+                select = mSupportStrategy.buildSelectWithSlice(select, to != null, from > 0);

+                break;

+            }

+

+            if (mRepository.localTransactionScope().isForUpdate()) {

+                select = select.concat(" FOR UPDATE");

+            }

+

+            Connection con = mRepository.getConnection();

+            try {

+                PreparedStatement ps = con.prepareStatement(select);

+                Integer fetchSize = mRepository.getFetchSize();

+                if (fetchSize != null) {

+                    ps.setFetchSize(fetchSize);

+                }

+

+                try {

+                    int psOrdinal = setParameters(ps, values);

+

+                    if (from > 0) {

+                        if (to != null) {

+                            switch (option) {

+                            case OFFSET_ONLY:

+                                ps.setLong(psOrdinal, from);

+                                Cursor<S> c = new JDBCCursor<S>(JDBCStorage.this, con, ps);

+                                return new LimitCursor<S>(c, to - from);

+                            case LIMIT_AND_OFFSET:

+                                ps.setLong(psOrdinal, to - from);

+                                ps.setLong(psOrdinal + 1, from);

+                                break;

+                            case OFFSET_AND_LIMIT:

+                                ps.setLong(psOrdinal, from);

+                                ps.setLong(psOrdinal + 1, to - from);

+                            }

+                        } else {

+                            ps.setLong(psOrdinal, from);

+                        }

+                    } else if (to != null) {

+                        ps.setLong(psOrdinal, to);

+                    }

+

+                    return new JDBCCursor<S>(JDBCStorage.this, con, ps);

+                } catch (Exception e) {

+                    // in case of exception, close statement

+                    try {

+                        ps.close();

+                    } catch (SQLException e2) {

+                        // ignore and allow triggering exception to propagate

+                    }

+                    throw e;

+                }

+            } catch (Exception e) {

+                // in case of exception, yield connection

+                try {

+                    mRepository.yieldConnection(con);

+                } catch (FetchException e2) {

+                   // ignore and allow triggering exception to propagate

+                }

+                throw mRepository.toFetchException(e);

+            }

+        }

+

+        @Override

         public long count(FilterValues<S> values) throws FetchException {

             Connection con = mRepository.getConnection();

             try {

@@ -758,13 +852,16 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
             return b.toString();

         }

 

-        private void setParameters(PreparedStatement ps, FilterValues<S> filterValues)

+        /**

+         * @return next value ordinal

+         */

+        private int setParameters(PreparedStatement ps, FilterValues<S> filterValues)

             throws Exception

         {

             PropertyFilter<S>[] propertyFilters = mPropertyFilters;

 

             if (propertyFilters == null) {

-                return;

+                return 1;

             }

 

             boolean[] propertyFilterNullable = mPropertyFilterNullable;

@@ -799,6 +896,8 @@ class JDBCStorage<S extends Storable> extends StandardQueryFactory<S>
 

                 ordinal++;

             }

+

+            return psOrdinal;

         }

     }

 

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java
index 3643c65..f3b0db5 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java
@@ -287,4 +287,39 @@ class JDBCSupportStrategy {
     void setTruncateTableStatement(String truncateTableStatement) {

         mTruncateTableStatement = truncateTableStatement;

     }

+

+    /**

+     * @since 1.2

+     */

+    enum SliceOption {

+        // Slice is always emulated

+        NOT_SUPPORTED,

+        // Slice is emulated if from is not zero

+        LIMIT_ONLY,

+        // Slice is emulated if to is not null

+        OFFSET_ONLY,

+        // Slice is fully supported with limit parameter first

+        LIMIT_AND_OFFSET,

+        // Slice is fully supported with offset parameter first

+        OFFSET_AND_LIMIT,

+    }

+

+    /**

+     * @since 1.2

+     */

+    SliceOption getSliceOption() {

+        return SliceOption.NOT_SUPPORTED;

+    }

+

+    /**

+     * @param select base select statement

+     * @param limit when true, select must support limit parameter

+     * @param offset when true, select must support offset parameter

+     * @return revised select statement

+     * @throws UnsupportedOperationException

+     * @since 1.2

+     */

+    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

+        throw new UnsupportedOperationException();

+    }

 }

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java
index 27f813f..bf15ec6 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java
@@ -32,7 +32,28 @@ class MysqlSupportStrategy extends JDBCSupportStrategy {
         setTruncateTableStatement(TRUNCATE_STATEMENT);

     }

 

+    @Override

     JDBCExceptionTransformer createExceptionTransformer() {

         return new MysqlExceptionTransformer();

     }

+

+    @Override

+    SliceOption getSliceOption() {

+        return SliceOption.OFFSET_AND_LIMIT;

+    }

+

+    @Override

+    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

+        if (limit) {

+            if (offset) {

+                return select.concat(" LIMIT ?,?");

+            } else {

+                return select.concat(" LIMIT ?");

+            }

+        } else if (offset) {

+            return select.concat(" LIMIT ?,18446744073709551615");

+        } else {

+            return select;

+        }

+    }

 }

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java
index 27ca7c5..582b299 100644
--- a/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java
@@ -220,6 +220,28 @@ class OracleSupportStrategy extends JDBCSupportStrategy {
         }

     }

 

+    @Override

+    SliceOption getSliceOption() {

+        return SliceOption.OFFSET_AND_LIMIT;

+    }

+

+    @Override

+    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

+        if (limit) {

+            if (offset) {

+                return "SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(" +

+                    select + ") ROW_) WHERE ROWNUM_ > ? AND ROWNUM_ <= ?";

+            } else {

+                return "SELECT * FROM (" + select + ") WHERE ROWNUM <= ?";

+            }

+        } else if (offset) {

+            return "SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(" +

+                select + ") ROW_) WHERE ROWNUM_ > ?";

+        } else {

+            return select;

+        }

+    }

+

     /* FIXME

     @Override

     boolean printPlan(Appendable app, int indentLevel, String statement)

diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java
new file mode 100644
index 0000000..43d35c2
--- /dev/null
+++ b/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java
@@ -0,0 +1,55 @@
+/*

+ * Copyright 2008 Amazon Technologies, Inc. or its affiliates.

+ * Amazon, Amazon.com and Carbonado are trademarks or registered trademarks

+ * of Amazon Technologies, Inc. or its affiliates.  All rights reserved.

+ *

+ * Licensed under the Apache License, Version 2.0 (the "License");

+ * you may not use this file except in compliance with the License.

+ * You may obtain a copy of the License at

+ *

+ *     http://www.apache.org/licenses/LICENSE-2.0

+ *

+ * Unless required by applicable law or agreed to in writing, software

+ * distributed under the License is distributed on an "AS IS" BASIS,

+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

+ * See the License for the specific language governing permissions and

+ * limitations under the License.

+ */

+

+package com.amazon.carbonado.repo.jdbc;

+

+/**

+ * 

+ *

+ * @author Brian S O'Neill

+ * @since 1.2

+ */

+class PostgresqlSupportStrategy extends JDBCSupportStrategy {

+    private static final String TRUNCATE_STATEMENT = "TRUNCATE TABLE %s";

+

+    protected PostgresqlSupportStrategy(JDBCRepository repo) {

+        super(repo);

+

+        setTruncateTableStatement(TRUNCATE_STATEMENT);

+    }

+

+    @Override

+    SliceOption getSliceOption() {

+        return SliceOption.LIMIT_AND_OFFSET;

+    }

+

+    @Override

+    String buildSelectWithSlice(String select, boolean limit, boolean offset) {

+        if (limit) {

+            if (offset) {

+                return select.concat(" LIMIT ? OFFSET ?");

+            } else {

+                return select.concat(" LIMIT ?");

+            }

+        } else if (offset) {

+            return select.concat(" LIMIT ALL OFFSET ?");

+        } else {

+            return select;

+        }

+    }

+}