OFBiz › OFBiz - Dev

[jira] [Created] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

_‹ Previous Topic Next Topic _›

Classic

List

9 messages Options

Options

Nicolas Malin (Jira)

[jira] [Created] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

Support MySQL and Postgres's LIMIT and OFFSET options
-----------------------------------------------------

Key: OFBIZ-4346
URL: https://issues.apache.org/jira/browse/OFBIZ-4346
Project: OFBiz
Issue Type: Improvement
Components: framework
Affects Versions: SVN trunk
Reporter: Shi Jinghai
Priority: Minor

Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
These classes can be configured in entityengine.xml:
helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
and
helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Updated] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Shi Jinghai updated OFBIZ-4346:
-------------------------------

Attachment: mysql_postgres_limit_offset_trunk.patch

Pls apply this patch to trunk.

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Commented] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13068828#comment-13068828 ]

David E. Jones commented on OFBIZ-4346:
---------------------------------------

Adding a class per database is not the preferred way to handle SQL variations (you'll notice that OOTB there are no such things for the entity engine).

The better approach is to 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. That is how all current syntax variations are configured and coded.

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Commented] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13068930#comment-13068930 ]

Shi Jinghai commented on OFBIZ-4346:
------------------------------------

Ok, I'll try to add an "allow-limit-offset" attribute to datasource element and change the Generic* code accordingly. Correct me if I misunderstood.

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Commented] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13068996#comment-13068996 ]

David E. Jones commented on OFBIZ-4346:
---------------------------------------

The concern of the attribute is not so much if limit/offset are allowed, if they are not supported the JDBC driver will eventually throw an exception.

The attribute would be used to configure the SQL syntax variation for the offset/limit concept. The attribute might look something like:

{code}
<xs:attribute name="offset-style" default="fetch">
<xs:simpleType><xs:restriction base="xs:token">
<xs:enumeration value="fetch"/>
<xs:enumeration value="limit"/>
</xs:restriction></xs:simpleType>
</xs:attribute>
{code}

The code for the SQL syntax options might look like:

{code}
if (databaseNode."@offset-style" == "limit") {
// use the LIMIT/OFFSET style
this.sqlTopLevel.append(" LIMIT ").append(limit ?: "ALL")
this.sqlTopLevel.append(" OFFSET ").append(offset ?: 0)
} else {
// use SQL2008 OFFSET/FETCH style by default
if (offset != null) this.sqlTopLevel.append(" OFFSET ").append(offset).append(" ROWS")
if (limit != null) this.sqlTopLevel.append(" FETCH FIRST ").append(limit).append(" ROWS ONLY")
}
{code}

This is how I would have implemented it in OFBiz, demonstrated by the fact that this is how I implemented it Moqui.

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Updated] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Shi Jinghai updated OFBIZ-4346:
-------------------------------

Attachment: limit_offset_trunk

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: limit_offset_trunk, mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Commented] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13069106#comment-13069106 ]

Shi Jinghai commented on OFBIZ-4346:
------------------------------------

Thank you, David!

I copied your code in last comment and build a new patch.

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: limit_offset_trunk, mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Updated] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Shi Jinghai updated OFBIZ-4346:
-------------------------------

Attachment: limit_offset_trunk.patch

Sorry, last file was submitted in eclipse. Now uploading it again in web browser.

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: limit_offset_trunk, limit_offset_trunk.patch, mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Nicolas Malin (Jira)

[jira] [Commented] (OFBIZ-4346) Support MySQL and Postgres's LIMIT and OFFSET options

In reply to this post by Nicolas Malin (Jira)

[ https://issues.apache.org/jira/browse/OFBIZ-4346?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13144947#comment-13144947 ]

Jacques Le Roux commented on OFBIZ-4346:
----------------------------------------

Reading http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-LIMIT, I wonder if we should not at least warn users (in makeOffsetString?) that they should be using ORDER BY. Else sounds good to me (1st review)

> Support MySQL and Postgres's LIMIT and OFFSET options
> -----------------------------------------------------
>
> Key: OFBIZ-4346
> URL: https://issues.apache.org/jira/browse/OFBIZ-4346
> Project: OFBiz
> Issue Type: Improvement
> Components: framework
> Affects Versions: SVN trunk
> Reporter: Shi Jinghai
> Priority: Minor
> Attachments: limit_offset_trunk, limit_offset_trunk.patch, mysql_postgres_limit_offset_trunk.patch
>
>
> Two helper classes are added for MySQL and Postgres to support LIMIT and OFFSET options.
> These classes can be configured in entityengine.xml:
> helper-class="org.ofbiz.entity.datasource.postgres.PostgresHelperDAO" for Postgres
> and
> helper-class="org.ofbiz.entity.datasource.mysql.MysqlHelperDAO" for MySQL.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira