You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Thomas Fischer <tf...@apache.org> on 2006/04/10 21:50:41 UTC
Re: Postgresql, integer types, and size (Torque 3.2 only)
I cannot reproduce this error. Boolean selects are tested in the Torque
runtime test, and work fine. I just ran the test against the 8.0-311
version of the postgresql driver, and it works fine.
The code used is (see org.apache.torque.DataTest in the test project)
....
// clean booleancheck table (because insert uses fixed keys)
Criteria criteria = new Criteria();
criteria.add(BooleanCheckPeer.TEST_KEY, (Object) null,
Criteria.NOT_EQUAL);
BooleanCheckPeer.doDelete(criteria);
BooleanCheck bc = new BooleanCheck();
bc.setTestKey("t1");
bc.setBintValue(true);
bc.setBcharValue(true);
bc.save();
bc = new BooleanCheck();
bc.setTestKey("f1");
bc.setBintValue(false);
bc.setBcharValue(false);
bc.save();
....
Criteria criteria = new Criteria();
criteria.add(BooleanCheckPeer.BCHAR_VALUE, new Boolean(true));
criteria.add(BooleanCheckPeer.BINT_VALUE, new Boolean(true));
List booleanCheckList = BooleanCheckPeer.doSelect(criteria);
assertTrue("Should have read 1 dataset with both values true "
+ "but read " + booleanCheckList.size(),
booleanCheckList.size() == 1);
BooleanCheck booleanCheck = (BooleanCheck)
booleanCheckList.get(0);
// use trim() for testkey because some databases will return the
// testkey filled up with blanks, as it is defined as char(10)
assertTrue("Primary key of data set should be t1 but is "
+ booleanCheck.getTestKey().trim(),
"t1".equals(booleanCheck.getTestKey().trim()));
criteria.clear();
criteria.add(BooleanCheckPeer.BCHAR_VALUE, new Boolean(false));
criteria.add(BooleanCheckPeer.BINT_VALUE, new Boolean(false));
booleanCheckList = BooleanCheckPeer.doSelect(criteria);
assertTrue("Should have read 1 dataset with both values false "
+ "but read " + booleanCheckList.size(),
booleanCheckList.size() == 1);
booleanCheck = (BooleanCheck) booleanCheckList.get(0);
assertTrue("Primary key of data set should be f1 but is "
+ booleanCheck.getTestKey().trim(),
"f1".equals(booleanCheck.getTestKey().trim()));
The schema file snippet for the booleancheck table is
<table name="boolean_check" idMethod="none">
<column name="test_key" required="true" primaryKey="true" type="CHAR"
size="10" />
<column name="bint_value" required="true" type="BOOLEANINT" size="1"/>
<column name="bchar_value" required="true" type="BOOLEANCHAR" />
</table>
If you are still of the opinion that this is a bug, can you please produce
a test case and create a issue in Torque's bug tracker ?
Thanks,
Thomas
On Mon, 27 Mar 2006, Peter Ledbrook wrote:
> Further to this, any query that involves a BOOLEANINT field fails with
> the error:
>
> org.postgresql.util.PSQLException: ERROR: operator does not exist:
> smallint = boolean
>
> Apparently the query passes a boolean value for the field even though
> it's implemented as an integer. From what I read elsewhere, this
> strict behaviour in the PostgreSQL driver is unlikely to change, so
> does anyone know of a workaround?
>
> Thanks,
>
> Peter
>
> On 25/03/06, Peter Ledbrook <pe...@cacoethes.co.uk> wrote:
>> Hi,
>>
>> I have tried to build Scarab against a postgresql database, but I'm
>> running into a problem with the SQL generated by Torque. The Scarab
>> database schema specifies a size for some of its integer columns, and
>> these columns then have a type of INTEGER(n), where 'n' is the given
>> size. For example:
>>
>> <column name="DELETED" required="false" type="BOOLEANINT"
>> default="0" size="1" javaType="primitive"/>
>>
>> becomes the SQL:
>>
>> DELETED INT2(1)
>>
>> Unfortunately, postgresql does not support a size here, i.e. "INT2"
>> works, but "INT2(1)" does not. Note that this problem occurs with
>> INTEGER as well as BOOLEANINT.
>>
>> Is this a know problem? Is there a workaround other than removing the
>> 'size' attributes from integer columns?
>>
>> Thanks,
>>
>> Peter
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Postgresql, integer types, and size (Torque 3.2 only)
Posted by Thomas Fischer <tf...@apache.org>.
Peter,
thanks for digging into this. I have upgraded your role as developer in
scarab, you you are able to create attachments in the future. I have also
attached your test case to the issue.
Thomas
On Sun, 16 Apr 2006, Peter Ledbrook wrote:
> I have created an issue for this:
>
> http://issues.apache.org/scarab/issues/id/TRQS354
>
> I have also created a patch for the unit tests that reproduces both
> problems, but I managed to request the wrong role on Scarab and can't
> attach the patch to the issue. You can find it at the end of the
> e-mail.
>
> Peter
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Postgresql, integer types, and size (Torque 3.2 only)
Posted by Peter Ledbrook <pe...@cacoethes.co.uk>.
I have created an issue for this:
http://issues.apache.org/scarab/issues/id/TRQS354
I have also created a patch for the unit tests that reproduces both
problems, but I managed to request the wrong role on Scarab and can't
attach the patch to the issue. You can find it at the end of the
e-mail.
Peter
Index: test-project/src/java/org/apache/torque/DataTest.java
===================================================================
--- test-project/src/java/org/apache/torque/DataTest.java
(revision 394272)
+++ test-project/src/java/org/apache/torque/DataTest.java (working copy)
@@ -50,6 +50,8 @@
import org.apache.torque.test.CPeer;
import org.apache.torque.test.ClobTest;
import org.apache.torque.test.ClobTestPeer;
+import org.apache.torque.test.Course;
+import org.apache.torque.test.CoursePeer;
import org.apache.torque.test.DateTest;
import org.apache.torque.test.DateTestPeer;
import org.apache.torque.test.IntegerPk;
@@ -432,7 +434,7 @@
{
Criteria criteria = new Criteria();
criteria.add(BooleanCheckPeer.BCHAR_VALUE, new Boolean(true));
- criteria.add(BooleanCheckPeer.BINT_VALUE, new Boolean(true));
+ criteria.add(BooleanCheckPeer.BINT_VALUE, true);
List booleanCheckList = BooleanCheckPeer.doSelect(criteria);
assertTrue("Should have read 1 dataset with both values true "
+ "but read " + booleanCheckList.size(),
@@ -455,6 +457,25 @@
assertTrue("Primary key of data set should be f1 but is "
+ booleanCheck.getTestKey().trim(),
"f1".equals(booleanCheck.getTestKey().trim()));
+
+ // This checks that BOOLEANINT criteria values are 'corrected'
+ // if they are in a sub-criterion. If this isn't working,
+ // PostgreSQL will throw an exception.
+ criteria.clear();
+ criteria.add(BooleanCheckPeer.BCHAR_VALUE, new Boolean(false));
+
+ Criteria.Criterion subcrit =
criteria.getNewCriterion(BooleanCheckPeer.TEST_KEY, "f1",
Criteria.EQUAL);
+ subcrit.and(criteria.getNewCriterion(BooleanCheckPeer.BINT_VALUE,
new Boolean(false), Criteria.EQUAL));
+ criteria.add(subcrit);
+ BooleanCheckPeer.doSelect(criteria);
+
+ // This checks that BOOLEANINT values are corrected if any
+ // BOOLEANINT columns are in a join. If this isn't working,
+ // PostgreSQL at least will throw an exception.
+ criteria.clear();
+ criteria.addJoin(CoursePeer.COL_A, BooleanCheckPeer.TEST_KEY);
+ criteria.add(BooleanCheckPeer.BINT_VALUE, true);
+ CoursePeer.doSelect(criteria);
}
/**
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Postgresql, integer types, and size (Torque 3.2 only)
Posted by Peter Ledbrook <pe...@cacoethes.co.uk>.
> Sorry, it's quite an involved one. However, the thing to note is that
> SCARAB_ISSUE_TEMPLATE_INFO.APPROVED is *not* a key in the top-level
> Criteria object, and yet it's value needs translating from "TRUE" to
> "1". The 'correctBooleans()' method only checks whether the Criteria
> contains a key of boolean type, so if a boolean criterion is hidden
> away in a sub-criterion, then its value is not corrected.
I have also realised that 'correctBooleans()' will fail if the
BOOLEANINT column is in a JOINed table, rather than in the table that
the SELECT is being performed on.
Peter
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Postgresql, integer types, and size (Torque 3.2 only)
Posted by Peter Ledbrook <pe...@cacoethes.co.uk>.
I couldn't reproduce the problem in the unit test, but here's the
toString() output of a Criteria object that's cauing problems:
Criteria:: SCARAB_ISSUE.DELETED<=>SCARAB_ISSUE.DELETED=0:
SCARAB_ISSUE_TEMPLATE_INFO.SCOPE_ID<=>((SCARAB_ISSUE_TEMPLATE_INFO.SCOPE_ID=2
AND SCARAB_ISSUE_TEMPLATE_INFO.APPROVED=TRUE) OR
(SCARAB_TRANSACTION.CREATED_BY=1 AND
SCARAB_ISSUE_TEMPLATE_INFO.SCOPE_ID=1)):
SCARAB_ISSUE.TYPE_ID<=>SCARAB_ISSUE.TYPE_ID=101:
SCARAB_ISSUE.MOVED<=>SCARAB_ISSUE.MOVED=0:
SCARAB_ISSUE.MODULE_ID<=>SCARAB_ISSUE.MODULE_ID=100:
Current Query SQL (may not be complete or applicable): SELECT DISTINCT
FROM SCARAB_TRANSACTION, SCARAB_ACTIVITY, SCARAB_ISSUE,
SCARAB_ISSUE_TEMPLATE_INFO WHERE
SCARAB_TRANSACTION.TRANSACTION_ID=SCARAB_ACTIVITY.TRANSACTION_ID AND
SCARAB_ISSUE.ISSUE_ID=SCARAB_ACTIVITY.ISSUE_ID AND
SCARAB_ISSUE_TEMPLATE_INFO.ISSUE_ID=SCARAB_ISSUE.ISSUE_ID AND
SCARAB_ISSUE.DELETED=0 AND ((SCARAB_ISSUE_TEMPLATE_INFO.SCOPE_ID=2 AND
SCARAB_ISSUE_TEMPLATE_INFO.APPROVED=TRUE) OR
(SCARAB_TRANSACTION.CREATED_BY=1 AND
SCARAB_ISSUE_TEMPLATE_INFO.SCOPE_ID=1)) AND SCARAB_ISSUE.TYPE_ID=101
AND SCARAB_ISSUE.MOVED=0 AND SCARAB_ISSUE.MODULE_ID=100
Sorry, it's quite an involved one. However, the thing to note is that
SCARAB_ISSUE_TEMPLATE_INFO.APPROVED is *not* a key in the top-level
Criteria object, and yet it's value needs translating from "TRUE" to
"1". The 'correctBooleans()' method only checks whether the Criteria
contains a key of boolean type, so if a boolean criterion is hidden
away in a sub-criterion, then its value is not corrected.
I might be able to come up with a simple test case, but I'll leave it
for a day or two.
Peter
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Postgresql, integer types, and size (Torque 3.2 only)
Posted by Peter Ledbrook <pe...@cacoethes.co.uk>.
On 11/04/06, Peter Ledbrook <pe...@cacoethes.co.uk> wrote:
> > If you are still of the opinion that this is a bug, can you please produce
> > a test case and create a issue in Torque's bug tracker ?
> >
> > Thanks,
> >
> > Thomas
>
Well, I downloaded and ran the Torque tests, and they worked fine. I
have also looked at the code generated by Torque for Scarab, and it
uses the "correctBooleans()" method to convert booleans to integers. I
thought that having 'javaType="primitive"' might be the problem, but
it has no effect on the unit test.
And yet, I'm still getting this exception in Scarab:
Caused by: org.apache.torque.TorqueException:
org.postgresql.util.PSQLException: ERROR: operator does not exist:
smallint = boolean
at org.apache.torque.util.BasePeer.throwTorqueException(BasePeer.java:103)
at org.apache.torque.util.BasePeer.executeQuery(BasePeer.java:899)
at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:740)
at org.apache.torque.util.BasePeer.doSelect(BasePeer.java:702)
at org.tigris.scarab.om.BaseIssueTypePeer.doSelectVillageRecords(BaseIssueTypePeer.java:428)
at org.tigris.scarab.om.BaseIssueTypePeer.doSelectVillageRecords(BaseIssueTypePeer.java:400)
at org.tigris.scarab.om.BaseIssueTypePeer.doSelect(BaseIssueTypePeer.java:369)
at org.tigris.scarab.om.AbstractScarabModule.getIssueTypes(AbstractScarabModule.java:852)
at org.tigris.scarab.om.AbstractScarabModule.getIssueTypes(AbstractScarabModule.java:826)
Any ideas how to trace the queries that are going to PostgreSQL? I
just can't see how a boolean is being sent rather than an integer...
Cheers,
Peter
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org