db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jürgen Hoffmann ...@byteaction.de>
Subject Criteria Mystery
Date Sat, 15 Feb 2003 10:09:38 GMT
Hi All,

My Apologies for this very long E-Mail, but this seems to be a complex
problem, and I wanted to hopefully present you each little piece of
information that the experts need to solve this problem... I have
Cross-Posted this message, since it could be a turbine or torque
problem...

I am getting very very frustrated here. Since I have manged to count the
rows for a given criteria in a table by doing the following in my Action
which fills the context for the view...

cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
 (Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY + ") between " +
(von_date / 1000) + " and " +
 (bis_date / 1000) + " )"), Criteria.CUSTOM));

long pages = WebserverDailyPeer.getPages(cri, limit);
Log.debug("Number of Pages in Table with Criteria: " + pages);

It works and prints
[Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- Number of Pages in Table with
Criteria: 58
In turbine.log

getPages is from my extended BasePeer Class in which I put the following
functions:
public class ByteActionBasePeer extends BasePeer
{
	public static long getNumberOfRowsInTable(String table) throws
TorqueException, DataSetException
	{
		String query = "SELECT COUNT(*) FROM " + table;
		return
((Record)executeQuery(query).get(0)).getValue(1).asLong();
	}

	public static long getNumberOfRowsInTable(Criteria c) throws
TorqueException, DataSetException
	{
		c.addSelectColumn("COUNT(*)");
		String query = createQueryString(c);
		c.getSelectColumns().clear();
		return
((Record)executeQuery(query).get(0)).getValue(1).asLong();
	}

	public static long getPages(Criteria c, int limit) throws
TorqueException, DataSetException
	{
		long numRows = getNumberOfRowsInTable(c);
		long pages = (numRows / limit);
		pages = ( numRows % limit == 0 ) ? pages : pages + 1;
		return pages;
	}

	public static long getPages(String table, int limit) throws
TorqueException, DataSetException
	{
		long numRows = getNumberOfRowsInTable(table);
		long pages = (numRows / limit);
		pages = ( numRows % limit == 0 ) ? pages : pages + 1;
		return pages;
	}
}

Now I have my number of Pages, I have my Limit and I have my Index. And
now I am adding these to the criteria,

cri.setLimit(limit);
cri.setOffset(index);

Doing a WebserverDailyPeer.doSelect(cri); did not use the Limit and
Index, so I thought maybe I should add the columns, since I have done a
addSelectColumns("COUNT(*)") and a clear() in my extended BasePeer
Class, and since Criteria sometimes behaves weird. So I did.

cri.addSelectColumn(WebserverDailyPeer.ID);
cri.addSelectColumn(WebserverDailyPeer.DAY);
cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
cri.addSelectColumn(WebserverDailyPeer.KID);
cri.addSelectColumn(WebserverDailyPeer.CREATION);
cri.addSelectColumn(WebserverDailyPeer.SERVERID);

And then verifying the output via:

Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
And this gets correctly translated to:
[Sat Feb 15 10:42:15 CET 2003] -- DEBUG -- FROM logger:AuswerungAction:
SELECT webserver_daily.ID, webserver_daily.DAY, webserver_daily.AMOUNT,
webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserver_daily WHERE
(UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600 and 1041375600 )
LIMIT 1, 20

BUT when I then do a 
List list = WebserverDailyPeer.doSelect(cri);

The Limit and Index mysteriously disappears... As you can see here:
22802 [Thread-7] DEBUG util.BasePeer  - SELECT webserver_daily.ID,
webserver_daily.DAY, webserver_daily.AMOUNT
, webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserv
er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
and 1041375600 ) LIMIT 1, 20
22802 [Thread-7] DEBUG util.BasePeer  - SELECT webserver_daily.ID,
webserver_daily.DAY, webserver_daily.AMOUNT
, webserver_daily.DOMAIN, webserver_daily.KID, webserver_daily.CREATION,
webserver_daily.SERVERID FROM webserv
er_daily WHERE (UNIX_TIMESTAMP(webserver_daily.DAY) between 1009839600
and 1041375600 )

It seems as if The Peer is doing 2 Selects...

The Complete Action Method follows below, so you can verify that I am
not calling do Select twice:

    public void doGetwebservers(RunData data, Context context)
        throws Exception
    {
        ParameterParser pp = data.getParameters();
        int wsid = pp.getInt("wsid");
        int kdid = pp.getInt("kdid");
        int von_tag = pp.getInt("von_tag");
        int bis_tag = pp.getInt("bis_tag");
        int von_monat = pp.getInt("von_monat");
        int bis_monat = pp.getInt("bis_monat");
        int von_jahr = pp.getInt("von_jahr");
        int bis_jahr = pp.getInt("bis_jahr");
        int limit = pp.getInt("limit",20);
        int index = pp.getInt("index",0);
				int curpage = pp.getInt("page",1);
        String domain = pp.getString("domain");
        Calendar von = new GregorianCalendar(von_jahr, von_monat - 1,
von_tag);
        long von_date = (long)((Date)von.getTime()).getTime();
        Calendar bis = new GregorianCalendar(bis_jahr, bis_monat - 1,
bis_tag);
        long bis_date = (long)((Date)bis.getTime()).getTime();

        Criteria cri = new Criteria();

        if(wsid > 0)
        {
            cri.add(WebserverDailyPeer.SERVERID, wsid);
        }

        if(kdid > 0)
        {
            cri.add(WebserverDailyPeer.KID, kdid);
        }

        if((domain != null) && (domain.length() > 0))
        {
            cri.add(cri.getNewCriterion(WebserverDailyPeer.DOMAIN,
                    (Object)(WebserverDailyPeer.DOMAIN + " LIKE '%" +
domain + "%' "), Criteria.CUSTOM));
            cri.addJoin(WebserverDailyPeer.DOMAIN,
DomainConfigPeer.NAME);
        }

        cri.add(cri.getNewCriterion(WebserverDailyPeer.DAY,
                (Object)("(UNIX_TIMESTAMP(" + WebserverDailyPeer.DAY +
") between " + (von_date / 1000) + " and " +
                (bis_date / 1000) + " )"), Criteria.CUSTOM));

				long pages =
WebserverDailyPeer.getPages(cri, limit);
				Log.debug("Number of Pages in Table with
Criteria: " + pages);


				context.put("index", new
Integer(index));
				context.put("pages", new Long(pages));
				context.put("limit", new
Integer(limit));
				context.put("curpage", new
Integer(curpage));

	
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));

				cri.setLimit(limit);
				cri.setOffset(1);
	
cri.addSelectColumn(WebserverDailyPeer.ID);
	
cri.addSelectColumn(WebserverDailyPeer.DAY);
	
cri.addSelectColumn(WebserverDailyPeer.AMOUNT);
	
cri.addSelectColumn(WebserverDailyPeer.DOMAIN);
	
cri.addSelectColumn(WebserverDailyPeer.KID);
	
cri.addSelectColumn(WebserverDailyPeer.CREATION);
	
cri.addSelectColumn(WebserverDailyPeer.SERVERID);

	
Log.debug("AuswerungAction",WebserverDailyPeer.createQueryString(cri));
				
        List list = WebserverDailyPeer.doSelect(cri);

        context.put("webservers", list);
    }

Kind regards
 
Jürgen Hoffmann
ByteACTION GmbH
 
cert. Perl Programmer
cert. Linux System Administrator
cert. Java Programmer

Besuchen Sie uns doch auf der CeBIT 2003
in Halle 4/Stand 70



Mime
View raw message