From 63ff050e4d48f8b997905e9e0a11d944ea6038a6 Mon Sep 17 00:00:00 2001 From: "Brian S. O'Neill" Date: Mon, 14 Apr 2008 03:18:32 +0000 Subject: Support query fetch limits. --- .../repo/jdbc/H2ExceptionTransformer.java | 35 +++++++ .../carbonado/repo/jdbc/H2SupportStrategy.java | 60 ++++++++++++ .../amazon/carbonado/repo/jdbc/JDBCStorage.java | 103 ++++++++++++++++++++- .../carbonado/repo/jdbc/JDBCSupportStrategy.java | 35 +++++++ .../carbonado/repo/jdbc/MysqlSupportStrategy.java | 21 +++++ .../carbonado/repo/jdbc/OracleSupportStrategy.java | 22 +++++ .../repo/jdbc/PostgresqlSupportStrategy.java | 55 +++++++++++ 7 files changed, 329 insertions(+), 2 deletions(-) create mode 100644 src/main/java/com/amazon/carbonado/repo/jdbc/H2ExceptionTransformer.java create mode 100644 src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java create mode 100644 src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java (limited to 'src/main/java/com/amazon/carbonado/repo/jdbc') 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; @@ -646,6 +648,98 @@ class JDBCStorage extends StandardQueryFactory } } + @Override + public Cursor fetch(FilterValues 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 c = new JDBCCursor(JDBCStorage.this, con, ps); + return new LimitCursor(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(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 values) throws FetchException { Connection con = mRepository.getConnection(); @@ -758,13 +852,16 @@ class JDBCStorage extends StandardQueryFactory return b.toString(); } - private void setParameters(PreparedStatement ps, FilterValues filterValues) + /** + * @return next value ordinal + */ + private int setParameters(PreparedStatement ps, FilterValues filterValues) throws Exception { PropertyFilter[] propertyFilters = mPropertyFilters; if (propertyFilters == null) { - return; + return 1; } boolean[] propertyFilterNullable = mPropertyFilterNullable; @@ -799,6 +896,8 @@ class JDBCStorage extends StandardQueryFactory 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; + } + } +} -- cgit v1.2.3