You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ofbiz.apache.org by Adrian Crum <ad...@sandglass-software.com> on 2012/12/15 12:30:03 UTC
Re: svn commit: r1422221 - in /ofbiz/trunk/framework/entity: dtd/
src/org/ofbiz/entity/config/ src/org/ofbiz/entity/datasource/ src/org/ofbiz/entity/test/
src/org/ofbiz/entity/util/
This commit makes the framework dependent on the Content component.
-Adrian
On 12/15/2012 11:20 AM, jleroux@apache.org wrote:
> Author: jleroux
> Date: Sat Dec 15 11:20:13 2012
> New Revision: 1422221
>
> URL: http://svn.apache.org/viewvc?rev=1422221&view=rev
> Log:
> A patch from Shi Jinghai for "Support MySQL and Postgres's LIMIT and OFFSET options" https://issues.apache.org/jira/browse/OFBIZ-4346
>
> Change the existing code to support the different syntax variations, and add an attribute to the datasource element in the entityengine.xml file so that the proper variation can be chosen for each database.
> Inspired by Moqui code. Test successful
>
> Modified:
> ofbiz/trunk/framework/entity/dtd/entity-config.xsd
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java
> ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java
>
> Modified: ofbiz/trunk/framework/entity/dtd/entity-config.xsd
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/dtd/entity-config.xsd?rev=1422221&r1=1422220&r2=1422221&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/dtd/entity-config.xsd (original)
> +++ ofbiz/trunk/framework/entity/dtd/entity-config.xsd Sat Dec 15 11:20:13 2012
> @@ -399,6 +399,15 @@ under the License.
> </xs:restriction>
> </xs:simpleType>
> </xs:attribute>
> + <xs:attribute name="offset-style" default="none">
> + <xs:simpleType>
> + <xs:restriction base="xs:token">
> + <xs:enumeration value="none"/>
> + <xs:enumeration value="fetch"/>
> + <xs:enumeration value="limit"/>
> + </xs:restriction>
> + </xs:simpleType>
> + </xs:attribute>
> <xs:attribute type="xs:string" name="table-type"/>
> <xs:attribute type="xs:string" name="character-set"/>
> <xs:attribute type="xs:string" name="collate"/>
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java?rev=1422221&r1=1422220&r2=1422221&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/config/DatasourceInfo.java Sat Dec 15 11:20:13 2012
> @@ -72,6 +72,7 @@ public class DatasourceInfo extends Name
> public boolean dropFkUseForeignKeyKeyword = false;
> public boolean useBinaryTypeForBlob = false;
> public boolean useOrderByNulls = false;
> + public String offsetStyle = null;
> public String tableType = null;
> public String characterSet = null;
> public String collate = null;
> @@ -158,7 +159,8 @@ public class DatasourceInfo extends Name
> this.dropFkUseForeignKeyKeyword = "true".equals(datasourceElement.getAttribute("drop-fk-use-foreign-key-keyword"));
> this.useBinaryTypeForBlob = "true".equals(datasourceElement.getAttribute("use-binary-type-for-blob"));
> this.useOrderByNulls = "true".equals(datasourceElement.getAttribute("use-order-by-nulls"));
> -
> +
> + this.offsetStyle = datasourceElement.getAttribute("offset-style");
> this.tableType = datasourceElement.getAttribute("table-type");
> this.characterSet = datasourceElement.getAttribute("character-set");
> this.collate = datasourceElement.getAttribute("collate");
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java?rev=1422221&r1=1422220&r2=1422221&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/datasource/GenericDAO.java Sat Dec 15 11:20:13 2012
> @@ -763,6 +763,9 @@ public class GenericDAO {
> }
> sqlBuffer.append(SqlJdbcUtil.makeOrderByClause(modelEntity, orderByExpanded, datasourceInfo));
>
> + // OFFSET clause
> + makeOffsetString(sqlBuffer, findOptions);
> +
> // make the final SQL String
> String sql = sqlBuffer.toString();
>
> @@ -884,6 +887,29 @@ public class GenericDAO {
> return havingString;
> }
>
> + protected StringBuilder makeOffsetString(StringBuilder offsetString, EntityFindOptions findOptions) {
> + if (UtilValidate.isNotEmpty(datasourceInfo.offsetStyle)) {
> + if (datasourceInfo.offsetStyle.equals("limit")) {
> + // use the LIMIT/OFFSET style
> + if (findOptions.getLimit() > -1) {
> + offsetString.append(" LIMIT " + findOptions.getLimit());
> + if (findOptions.getOffset() > -1) {
> + offsetString.append(" OFFSET " + findOptions.getOffset());
> + }
> + }
> + } else if (datasourceInfo.offsetStyle.equals("fetch")) {
> + // use SQL2008 OFFSET/FETCH style by default
> + if (findOptions.getOffset() > -1) {
> + offsetString.append(" OFFSET ").append(findOptions.getOffset()).append(" ROWS");
> + if (findOptions.getLimit() > -1) {
> + offsetString.append(" FETCH FIRST ").append(findOptions.getLimit()).append(" ROWS ONLY");
> + }
> + }
> + }
> + }
> + return offsetString;
> + }
> +
> public List<GenericValue> selectByMultiRelation(GenericValue value, ModelRelation modelRelationOne, ModelEntity modelEntityOne,
> ModelRelation modelRelationTwo, ModelEntity modelEntityTwo, List<String> orderBy) throws GenericEntityException {
> SQLProcessor sqlP = new SQLProcessor(helperInfo);
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java?rev=1422221&r1=1422220&r2=1422221&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/test/EntityTestSuite.java Sat Dec 15 11:20:13 2012
> @@ -31,6 +31,7 @@ import java.util.Map;
> import org.ofbiz.base.util.Debug;
> import org.ofbiz.base.util.UtilDateTime;
> import org.ofbiz.base.util.UtilMisc;
> +import org.ofbiz.base.util.UtilValidate;
> import org.ofbiz.base.util.UtilXml;
> import org.ofbiz.entity.Delegator;
> import org.ofbiz.entity.DelegatorFactory;
> @@ -44,6 +45,8 @@ import org.ofbiz.entity.condition.Entity
> import org.ofbiz.entity.condition.EntityOperator;
> import org.ofbiz.entity.config.DatasourceInfo;
> import org.ofbiz.entity.config.EntityConfigUtil;
> +import org.ofbiz.entity.datasource.GenericHelperDAO;
> +import org.ofbiz.entity.model.ModelEntity;
> import org.ofbiz.entity.testtools.EntityTestCase;
> import org.ofbiz.entity.transaction.GenericTransactionException;
> import org.ofbiz.entity.transaction.TransactionUtil;
> @@ -604,4 +607,94 @@ public class EntityTestSuite extends Ent
> strBufTemp.append(iNum);
> return strBufTemp.toString();
> }
> +
> + /*
> + * This test will verify that the LIMIT and OFFSET options can work properly.
> + */
> + public void testLimitOffsetOptions() throws Exception {
> + String entityName = "Content";
> + DatasourceInfo datasourceInfo = EntityConfigUtil.getDatasourceInfo(delegator.getEntityHelper(entityName).getHelperName());
> + if (UtilValidate.isEmpty(datasourceInfo.offsetStyle) || datasourceInfo.offsetStyle.equals("none")) {
> + Debug.logInfo("The offset-stype configured in datasource is " + datasourceInfo.offsetStyle + ", this test is skipped.", module);
> + return;
> + } else {
> + Debug.logInfo("The offset-stype configured in datasource is " + datasourceInfo.offsetStyle + ".", module);
> + }
> + try {
> + EntityFindOptions findOptions = new EntityFindOptions();
> + long count = delegator.findCountByCondition("Content", null, null, null);
> + Debug.logInfo("Content entity has " + count + " rows", module);
> + int rowsPerPage = 10;
> + // use rows/page as limit option
> + findOptions.setLimit(rowsPerPage);
> + int pages = (int) count/rowsPerPage;
> + if (count > pages * rowsPerPage) {
> + pages += 1;
> + }
> + Debug.logInfo("These rows will be displayed in " + pages + " pages, each page has " + rowsPerPage + " rows.", module);
> + ModelEntity modelEntity = delegator.getModelEntity(entityName);
> +
> + long start = UtilDateTime.nowTimestamp().getTime();
> + for (int page = 1; page <= pages; page++) {
> + Debug.logInfo("Page " + page + ":", module);
> + // set offset option
> + findOptions.setOffset((page - 1) * rowsPerPage);
> + EntityListIterator iterator = null;
> + try {
> + iterator = delegator.getEntityHelper(entityName).findListIteratorByCondition(modelEntity, null, null, null, UtilMisc.toList("lastUpdatedStamp DESC"), findOptions);
> + while (iterator != null) {
> + GenericValue gv = iterator.next();
> + if (gv == null) {
> + break;
> + }
> + Debug.logInfo(gv.getString("contentId") + ": " + gv.getString("contentName") + " (updated: " + gv.getTimestamp("lastUpdatedStamp") + ")", module);
> + }
> + } catch (GenericEntityException e) {
> + Debug.logError(e, module);
> + } finally {
> + if (iterator != null) {
> + iterator.close();
> + }
> + }
> + }
> + long end = UtilDateTime.nowTimestamp().getTime();
> + long time1 = end - start;
> + Debug.logInfo("Time consumed WITH limit and offset option (ms): " + time1, module);
> +
> + start = UtilDateTime.nowTimestamp().getTime();
> + for (int page = 1; page <= pages; page++) {
> + Debug.logInfo("Page " + page + ":", module);
> + EntityListIterator iterator = null;
> + try {
> + iterator = ((GenericHelperDAO) delegator.getEntityHelper(entityName)).findListIteratorByCondition(modelEntity, null, null, null, UtilMisc.toList("lastUpdatedStamp DESC"), null);
> + if (iterator == null) {
> + continue;
> + }
> + iterator.setDelegator(delegator);
> + List<GenericValue> gvs = iterator.getCompleteList();
> + int fromIndex = (page - 1) * rowsPerPage;
> + int toIndex = fromIndex + rowsPerPage;
> + if (toIndex > count) {
> + toIndex = (int) count;
> + }
> + gvs = gvs.subList(fromIndex, toIndex);
> + for (GenericValue gv : gvs) {
> + Debug.logInfo(gv.getString("contentId") + ": " + gv.getString("contentName") + " (updated: " + gv.getTimestamp("lastUpdatedStamp") + ")", module);
> + }
> + } catch (GenericEntityException e) {
> + Debug.logError(e, module);
> + } finally {
> + if (iterator != null) {
> + iterator.close();
> + }
> + }
> + }
> + end = UtilDateTime.nowTimestamp().getTime();
> + long time2 = end - start;
> + Debug.logInfo("Time consumed WITHOUT limit and offset option (ms): " + time2, module);
> + Debug.logInfo("Time saved (ms): " + (time2 - time1), module);
> + } catch (GenericEntityException e) {
> + Debug.logError(e, module);
> + }
> + }
> }
>
> Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java
> URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java?rev=1422221&r1=1422220&r2=1422221&view=diff
> ==============================================================================
> --- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java (original)
> +++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/util/EntityFindOptions.java Sat Dec 15 11:20:13 2012
> @@ -48,6 +48,12 @@ public class EntityFindOptions implement
> protected int maxRows = -1;
> protected boolean distinct = false;
>
> + /** LIMIT option */
> + protected int limit = -1;
> +
> + /** OFFSET option */
> + protected int offset = -1;
> +
> /** Default constructor. Defaults are as follows:
> * specifyTypeAndConcur = true
> * resultSetType = TYPE_FORWARD_ONLY
> @@ -145,4 +151,25 @@ public class EntityFindOptions implement
> public void setDistinct(boolean distinct) {
> this.distinct = distinct;
> }
> +
> +
> + /** Get the LIMIT number. */
> + public int getLimit() {
> + return limit;
> + }
> +
> + /** Specifies the LIMIT number. */
> + public void setLimit(int limit) {
> + this.limit = limit;
> + }
> +
> + /** Get the OFFSET number. */
> + public int getOffset() {
> + return offset;
> + }
> +
> + /** Specifies the OFFSET number. */
> + public void setOffset(int offset) {
> + this.offset = offset;
> + }
> }
>
>