No subject

in expresso in order to have the true isolation of DB layer. Would there be
any information or an complete config file is available?

Thanks in advance,
Kenneth

----- Original Message -----
From: "Michael Rimov" <rimovm at centercomp.com>
To: <opensource at javacorporate.com>
Sent: Wednesday, March 27, 2002 3:55 AM
Subject: [Opensource] RE: DB Limitations [Long]


> Limitation Syntax, A Brief History Of
> Definition
> Essentially this solves the problem of optimizing SQL SELECT statements
> where we are interesting in subset of rows of the result set.
>
>
> Problem
> Suppose we have a select statement S that returns a result set R.
> SELECT * FROM WORLD_PEOPLE
> Where WORLD_PEOPE could be something like this incomplete schema
> CREATE TABLE (
> NAME VARCHAR(32),
> ADDRESS VARCHAR(128)
> DOB DATETIME,
> ISO_COUNTRY_CODE VARCHAR(2)
> ...
> }
>
> The point is that the result set R is very large, even if we
> applied a filter such as WHERE ISO_COUNTRY_CODE='UK'.
> With larger result sets many web sites often paginate the information.
> Before the limitation syntax was introduced. The DBOBject implementation
> would have no choice but read all the rows from the database.
> Suppose we had a result set R that had in truth 1000 rows,
> but we are only interested in present rows 500 to 600.
> This is wasteful for both database and expresso.
> 1) Expresso's DBObject has interate through all the rows until the 500th
> row before the saving rows from R.
> 2) Database has taken resources to create all 1000 rows and there
> is no way of telling it that we are not interested in 900 other rows.
>
> Therefore some database vendors have introduced a non standard
> rowset limitation optimization.
> For example MySQL using the LIMIT syntax
> SELECT * FROM WORLD_PEOPLE LIMIT <offset> [, <length> ]
>
> I think ORACLE has the idea of the ROW index .
> SELECT * FROM WORLD_PEOPLE LIMIT WHERE ROW >= <offset>
> AND ROW < <<offset> + <length>>
>
> As you can the above is very useful for producing pagination
> web sites.
> The limitation syntax allows you introduce database vendor
> optimization to the DBOBject. You need to define
> XML attributes of the jdbc tag in expresso XML config
> file.
>
> `Limitation Syntax'
> Defines syntax string of how the database vendor constraining
> the number of rows return in a result set. In this string
> You should define two of the following
> %offset% the start row set offset index
> %maxrecord% the number of the rows
> %endrecord% the last offset index ( = %offset% + %maxrecord% - 1)
> For MYSQL you define the string as "LIMIT %offset% , %maxrecords%"
>
> `Limitation Position'
> Declare where the SQL statement that the limitation syntax should be
inserted.
> LIMITATION_DISABLED
> Do not use limitation syntax (this is the default)
> LIMITATION_AFTER_TABLE
> insert jthe syntax just after
> SELECT .. FROM <TABLE_VIEW_NAME>
> LIMITATION_AFTER_WHERE
> insert jthe syntax just after
> SELECT .. FROM <TABLE_VIEW_NAME> WHERE <where clause>
> LIMITATION_AFTER_ORDER_BY
> insert jthe syntax just after
> SELECT .. FROM <TABLE_VIEW_NAME> WHERE <where clause>
> ORDER BY <order clause>
> For MYSQL the value should be "LIMITATION_AFTER_ORDER_BY"
>
> Here is part of my expresso-config.xml
> <expresso-config>
> <logDirectory>%web-app%WEB-INF/log</logDirectory>
> <strongCrypto>n</strongCrypto>
> <servletAPI>2_3</servletAPI>
> <context name="default">
> <description>MYSQL 3.23.37 Database</description>
> <jdbc
> driver="org.gjt.mm.mysql.Driver"
> url="jdbc:mysql://localhost:3306/expresso"
> connectFormat="1"
> login="expresso"
> password="b0rneoXY"
> cache="y"
> checkZeroUpdate="false"
> limitationPosition="LIMITATION_AFTER_ORDER_BY"
> limitationSyntax="LIMIT %offset% , %maxrecords%" />
> <type-mapping>
> <java-type>LONGVARCHAR</java-type>
> <expresso-type>text</expresso-type>
> <db-type>text</db-type>
> </type-mapping>
> <images>%context%/%expresso-dir%/images</images>
> <startJobHandler>y</startJobHandler>
> <showStackTrace>y</showStackTrace>
> <mailDebug>n</mailDebug>
> </context>
> </expresso-config>
>
> And finally the limitation syntax is only triggered in DBOBject.java
> if limitationPosition != LIMITATION_DISABLED and
> (offsetRecord > 0 ) || ( maxRecords > 0 )
> Also the way it works in completely transparent. If limitationPosition
> is set to LIMITATION_DISABLED, then the DBObject will read
> all the result set skipping up to ``offsetRecord'', and then
> store the data, and then discards the result set. In other work
> in non-optimized mode.
>
>
>
> _______________________________________________
> Opensource mailing list
> Opensource at javacorporate.com
> http://www.javacorporate.com/mailman/listinfo/opensource
> Archives: http://www.javacorporate.com/pipermail/opensource/
>