You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by Patrick Carl <pa...@web.de> on 2005/11/17 23:02:32 UTC
Torque with hsqldb and referenced identity columns
Hello there,
when using Torque with hsqldb adn native id generation Torque uses the
column constraint "IDENTITY" of hsqldb. This creates by default primary
key ids starting with 0. This can lead to false behaviour in the
following conditions:
It is possible that an object A with id 0 is created. If this object is
refered by an related object B, the method B.getA does not work, since
B.getA checks if B.aId != 0.
To prevent this I created the attached patches. Using them will set the
starting id of an identity column to 1.
Please note that I wasn't abled to get the test project to work, so I
couldn't test this patch as much as I would. Perhaps someone else can do
this.
Patrick
Re: [PATCH] Torque with hsqldb and referenced identity columns
Posted by Scott Eade <se...@backstagetech.com.au>.
Patrick Carl wrote:
> I tried but I am missing the privileges to create issues for Torque. I
> requested the QA role, I think it was the wrong one.
> Perhaps you can help me and assign the appropriate role to me. My
> scarab user name is "carlptr".
Fixed.
Scott
--
Scott Eade
Backstage Technologies Pty. Ltd.
http://www.backstagetech.com.au
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: [PATCH] Torque with hsqldb and referenced identity columns
Posted by Patrick Carl <pa...@web.de>.
Hi Thomas,
Thomas Fischer schrieb:
>
>
>
> Would you mind to open an issue in scarab on that ?
>
> http://issues.apache.org/scarab/issues/
>
I tried but I am missing the privileges to create issues for Torque. I
requested the QA role, I think it was the wrong one.
Perhaps you can help me and assign the appropriate role to me. My scarab
user name is "carlptr".
Thanks a lot,
Patrick
> Patches tend to get lost at the list...
>
> Thanks in advance,
>
> Thomas
>
> patrick.carl@web.de schrieb am 23.11.2005 07:20:12:
>
>
>>Hi,
>>
>>my submitted patch broke the generated sql for hypersonic. After I was
>>able to run the test project I found it out.
>>
>>But I was able to get it to work again, here's the patch description:
>>
>>Using hsqldb with id-method native columns creates columns defined with
>>the constraint "IDENTITY". This generated an index starting with 0. As
>>Torque regards objects with an primary key 0 as not initialized, this
>>can lead to problems when using relationships between tables. To prevent
>>this I've patched the generated string from "IDENTITY" to "GENERATED BY
>>DEFAULT AS IDENTITY (STARTING WITH 0)". This broke the created SQL for
>>hsqldb tables since Torque preprends the String "NOT NULL" in front of
>>the identity string. hsqldb does not allow this. To change this I've
>>patched the interface org.apache.torque.engine.platform.Platform and
>>added a method "public getNullString(Column col)" since the
>>PlatformHypersonicImpl needed to know if the column is a not null and if
>>the column uses the native id-method. The default implementation of
>>Platform delegates the call of this method to the former existing one.
>>Only the hsqldb implementation uses more than the property notNull of
>>the column. The new method is now used getSqlString of class Column.
>>
>>Please find the patch files attached.
>>
>>Cheers, Patrick
>>
>>Patrick Carl schrieb:
>>
>>>Hello there,
>>>
>>>when using Torque with hsqldb adn native id generation Torque uses the
>>>column constraint "IDENTITY" of hsqldb. This creates by default primary
>
>
>>>key ids starting with 0. This can lead to false behaviour in the
>>>following conditions:
>>>It is possible that an object A with id 0 is created. If this object is
>
>
>>>refered by an related object B, the method B.getA does not work, since
>>>B.getA checks if B.aId != 0.
>>>
>>>To prevent this I created the attached patches. Using them will set the
>
>
>>>starting id of an identity column to 1.
>>>
>>>Please note that I wasn't abled to get the test project to work, so I
>>>couldn't test this patch as much as I would. Perhaps someone else can
>
> do
>
>>>this.
>>>
>>>Patrick
>>>
>>>
>>>
>
> ------------------------------------------------------------------------
>
>>>---------------------------------------------------------------------
>>>To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
>>>For additional commands, e-mail: torque-dev-help@db.apache.org
>>
>>Index:
>
> src/java/org/apache/torque/engine/platform/PlatformHypersonicImpl.java
>
>>===================================================================
>>---
>>src/java/org/apache/torque/engine/platform/PlatformHypersonicImpl.
>>java (Revision 348110)
>>+++
>>src/java/org/apache/torque/engine/platform/PlatformHypersonicImpl.
>>java (Arbeitskopie)
>>@@ -18,7 +18,8 @@
>>
>> import org.apache.torque.engine.database.model.Domain;
>> import org.apache.torque.engine.database.model.SchemaType;
>>-
>>+import org.apache.torque.engine.database.model.Column;
>>+import org.apache.torque.engine.database.model.IDMethod;
>> /**
>> * HSQLDB (formerly known as Hypersonic) Platform implementation.
>> *
>>@@ -35,7 +36,7 @@
>> super();
>> initialize();
>> }
>>-
>>+
>> /**
>> * Initializes db specific domain mapping.
>> */
>>@@ -47,6 +48,19 @@
>> setSchemaDomainMapping(new Domain(SchemaType.VARBINARY,
>
> "BINARY"));
>
>> setSchemaDomainMapping(new Domain(SchemaType.BLOB, "BINARY"));
>> setSchemaDomainMapping(new Domain(SchemaType.CLOB,
>
> "LONGVARCHAR"));
>
>>- }
>>-
>>+ }
>>+
>>+
>>+ public String getAutoIncrement()
>>+ {
>>+ return "GENERATED BY DEFAULT AS IDENTITY (START WITH 1)";
>>+ }
>>+
>>+ public String getNullString(Column col)
>>+ {
>>+ if(col.isPrimaryKey() && IDMethod.NATIVE.equals(col.
>>getTable().getIdMethod()))
>>+ return "";
>>+
>>+ return super.getNullString(col);
>>+ }
>> }
>>Index:
>>
>
> src/test/org/apache/torque/engine/database/model/HypersonicDomainTest.java
>
>>===================================================================
>>---
>>src/test/org/apache/torque/engine/database/model/HypersonicDomainTest.
>>java (Revision 348110)
>>+++
>>src/test/org/apache/torque/engine/database/model/HypersonicDomainTest.
>>java (Arbeitskopie)
>>@@ -113,8 +113,8 @@
>> {
>> Table table = db.getTable("native");
>> Column col = table.getColumn("native_id");
>>- assertEquals("IDENTITY", col.getAutoIncrementString());
>>- assertEquals("native_id INTEGER NOT NULL IDENTITY", col.
>>getSqlString());
>>+ assertEquals("GENERATED BY DEFAULT AS IDENTITY (START WITH
>>1)", col.getAutoIncrementString());
>>+ assertEquals("native_id INTEGER GENERATED BY DEFAULT AS
>>IDENTITY (START WITH 1)", col.getSqlString());
>> col = table.getColumn("name");
>> assertEquals("", col.getAutoIncrementString());
>> }
>>Index: src/java/org/apache/torque/engine/platform/Platform.java
>>===================================================================
>>--- src/java/org/apache/torque/engine/platform/Platform.java
>>(Revision 348110)
>>+++ src/java/org/apache/torque/engine/platform/Platform.java
>
> (Arbeitskopie)
>
>>@@ -15,7 +15,7 @@
>> * See the License for the specific language governing permissions and
>> * limitations under the License.
>> */
>>-
>>+import org.apache.torque.engine.database.model.Column;
>> import org.apache.torque.engine.database.model.Domain;
>> import org.apache.torque.engine.database.model.SchemaType;
>>
>>@@ -61,6 +61,13 @@
>> String getNullString(boolean notNull);
>>
>> /**
>>+ * @return The RDBMS-specific SQL fragement for <code>NULL</code>
>>+ * or <code>NOT NULL</code> depending on the given column
>>+ */
>>+ String getNullString(Column column);
>>+
>>+
>>+ /**
>> * @return The RDBMS-specific SQL fragment for autoincrement.
>> */
>> String getAutoIncrement();
>>Index:
>
> src/java/org/apache/torque/engine/platform/PlatformDefaultImpl.java
>
>>===================================================================
>>--- src/java/org/apache/torque/engine/platform/PlatformDefaultImpl.
>>java (Revision 348110)
>>+++ src/java/org/apache/torque/engine/platform/PlatformDefaultImpl.
>>java (Arbeitskopie)
>>@@ -20,6 +20,7 @@
>> import java.util.Iterator;
>> import java.util.Map;
>>
>>+import org.apache.torque.engine.database.model.Column;
>> import org.apache.torque.engine.database.model.Domain;
>> import org.apache.torque.engine.database.model.SchemaType;
>>
>>@@ -97,8 +98,19 @@
>> // the old Sybase templates.
>> return (notNull ? "NOT NULL" : "");
>> }
>>-
>>+
>> /**
>>+ * @return Only produces a SQL fragment if null values are
>>+ * disallowed.
>>+ * @see Platform#getNullString(boolean)
>>+ */
>>+
>>+ public String getNullString(Column col)
>>+ {
>>+ return getNullString(col.isNotNull());
>>+ }
>>+
>>+ /**
>> * @see Platform#getAutoIncrement()
>> */
>> public String getAutoIncrement()
>>Index: src/java/org/apache/torque/engine/database/model/Column.java
>>===================================================================
>>--- src/java/org/apache/torque/engine/database/model/Column.java
>>(Revision 348110)
>>+++ src/java/org/apache/torque/engine/database/model/Column.java
>>(Arbeitskopie)
>>@@ -439,7 +439,7 @@
>> public String getNotNullString()
>> {
>> return getTable().getDatabase().getPlatform()
>>- .getNullString(this.isNotNull());
>>+ .getNullString(this);
>> }
>>
>> /**
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
>>For additional commands, e-mail: torque-dev-help@db.apache.org
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: [PATCH] Torque with hsqldb and referenced identity columns
Posted by Thomas Fischer <fi...@seitenbau.net>.
Would you mind to open an issue in scarab on that ?
http://issues.apache.org/scarab/issues/
Patches tend to get lost at the list...
Thanks in advance,
Thomas
patrick.carl@web.de schrieb am 23.11.2005 07:20:12:
> Hi,
>
> my submitted patch broke the generated sql for hypersonic. After I was
> able to run the test project I found it out.
>
> But I was able to get it to work again, here's the patch description:
>
> Using hsqldb with id-method native columns creates columns defined with
> the constraint "IDENTITY". This generated an index starting with 0. As
> Torque regards objects with an primary key 0 as not initialized, this
> can lead to problems when using relationships between tables. To prevent
> this I've patched the generated string from "IDENTITY" to "GENERATED BY
> DEFAULT AS IDENTITY (STARTING WITH 0)". This broke the created SQL for
> hsqldb tables since Torque preprends the String "NOT NULL" in front of
> the identity string. hsqldb does not allow this. To change this I've
> patched the interface org.apache.torque.engine.platform.Platform and
> added a method "public getNullString(Column col)" since the
> PlatformHypersonicImpl needed to know if the column is a not null and if
> the column uses the native id-method. The default implementation of
> Platform delegates the call of this method to the former existing one.
> Only the hsqldb implementation uses more than the property notNull of
> the column. The new method is now used getSqlString of class Column.
>
> Please find the patch files attached.
>
> Cheers, Patrick
>
> Patrick Carl schrieb:
> > Hello there,
> >
> > when using Torque with hsqldb adn native id generation Torque uses the
> > column constraint "IDENTITY" of hsqldb. This creates by default primary
> > key ids starting with 0. This can lead to false behaviour in the
> > following conditions:
> > It is possible that an object A with id 0 is created. If this object is
> > refered by an related object B, the method B.getA does not work, since
> > B.getA checks if B.aId != 0.
> >
> > To prevent this I created the attached patches. Using them will set the
> > starting id of an identity column to 1.
> >
> > Please note that I wasn't abled to get the test project to work, so I
> > couldn't test this patch as much as I would. Perhaps someone else can
do
> > this.
> >
> > Patrick
> >
> >
> >
------------------------------------------------------------------------
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> > For additional commands, e-mail: torque-dev-help@db.apache.org
> Index:
src/java/org/apache/torque/engine/platform/PlatformHypersonicImpl.java
> ===================================================================
> ---
> src/java/org/apache/torque/engine/platform/PlatformHypersonicImpl.
> java (Revision 348110)
> +++
> src/java/org/apache/torque/engine/platform/PlatformHypersonicImpl.
> java (Arbeitskopie)
> @@ -18,7 +18,8 @@
>
> import org.apache.torque.engine.database.model.Domain;
> import org.apache.torque.engine.database.model.SchemaType;
> -
> +import org.apache.torque.engine.database.model.Column;
> +import org.apache.torque.engine.database.model.IDMethod;
> /**
> * HSQLDB (formerly known as Hypersonic) Platform implementation.
> *
> @@ -35,7 +36,7 @@
> super();
> initialize();
> }
> -
> +
> /**
> * Initializes db specific domain mapping.
> */
> @@ -47,6 +48,19 @@
> setSchemaDomainMapping(new Domain(SchemaType.VARBINARY,
"BINARY"));
> setSchemaDomainMapping(new Domain(SchemaType.BLOB, "BINARY"));
> setSchemaDomainMapping(new Domain(SchemaType.CLOB,
"LONGVARCHAR"));
> - }
> -
> + }
> +
> +
> + public String getAutoIncrement()
> + {
> + return "GENERATED BY DEFAULT AS IDENTITY (START WITH 1)";
> + }
> +
> + public String getNullString(Column col)
> + {
> + if(col.isPrimaryKey() && IDMethod.NATIVE.equals(col.
> getTable().getIdMethod()))
> + return "";
> +
> + return super.getNullString(col);
> + }
> }
> Index:
>
src/test/org/apache/torque/engine/database/model/HypersonicDomainTest.java
> ===================================================================
> ---
> src/test/org/apache/torque/engine/database/model/HypersonicDomainTest.
> java (Revision 348110)
> +++
> src/test/org/apache/torque/engine/database/model/HypersonicDomainTest.
> java (Arbeitskopie)
> @@ -113,8 +113,8 @@
> {
> Table table = db.getTable("native");
> Column col = table.getColumn("native_id");
> - assertEquals("IDENTITY", col.getAutoIncrementString());
> - assertEquals("native_id INTEGER NOT NULL IDENTITY", col.
> getSqlString());
> + assertEquals("GENERATED BY DEFAULT AS IDENTITY (START WITH
> 1)", col.getAutoIncrementString());
> + assertEquals("native_id INTEGER GENERATED BY DEFAULT AS
> IDENTITY (START WITH 1)", col.getSqlString());
> col = table.getColumn("name");
> assertEquals("", col.getAutoIncrementString());
> }
> Index: src/java/org/apache/torque/engine/platform/Platform.java
> ===================================================================
> --- src/java/org/apache/torque/engine/platform/Platform.java
> (Revision 348110)
> +++ src/java/org/apache/torque/engine/platform/Platform.java
(Arbeitskopie)
> @@ -15,7 +15,7 @@
> * See the License for the specific language governing permissions and
> * limitations under the License.
> */
> -
> +import org.apache.torque.engine.database.model.Column;
> import org.apache.torque.engine.database.model.Domain;
> import org.apache.torque.engine.database.model.SchemaType;
>
> @@ -61,6 +61,13 @@
> String getNullString(boolean notNull);
>
> /**
> + * @return The RDBMS-specific SQL fragement for <code>NULL</code>
> + * or <code>NOT NULL</code> depending on the given column
> + */
> + String getNullString(Column column);
> +
> +
> + /**
> * @return The RDBMS-specific SQL fragment for autoincrement.
> */
> String getAutoIncrement();
> Index:
src/java/org/apache/torque/engine/platform/PlatformDefaultImpl.java
> ===================================================================
> --- src/java/org/apache/torque/engine/platform/PlatformDefaultImpl.
> java (Revision 348110)
> +++ src/java/org/apache/torque/engine/platform/PlatformDefaultImpl.
> java (Arbeitskopie)
> @@ -20,6 +20,7 @@
> import java.util.Iterator;
> import java.util.Map;
>
> +import org.apache.torque.engine.database.model.Column;
> import org.apache.torque.engine.database.model.Domain;
> import org.apache.torque.engine.database.model.SchemaType;
>
> @@ -97,8 +98,19 @@
> // the old Sybase templates.
> return (notNull ? "NOT NULL" : "");
> }
> -
> +
> /**
> + * @return Only produces a SQL fragment if null values are
> + * disallowed.
> + * @see Platform#getNullString(boolean)
> + */
> +
> + public String getNullString(Column col)
> + {
> + return getNullString(col.isNotNull());
> + }
> +
> + /**
> * @see Platform#getAutoIncrement()
> */
> public String getAutoIncrement()
> Index: src/java/org/apache/torque/engine/database/model/Column.java
> ===================================================================
> --- src/java/org/apache/torque/engine/database/model/Column.java
> (Revision 348110)
> +++ src/java/org/apache/torque/engine/database/model/Column.java
> (Arbeitskopie)
> @@ -439,7 +439,7 @@
> public String getNotNullString()
> {
> return getTable().getDatabase().getPlatform()
> - .getNullString(this.isNotNull());
> + .getNullString(this);
> }
>
> /**
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org
Re: [PATCH] Torque with hsqldb and referenced identity columns
Posted by Patrick Carl <pa...@web.de>.
Hi,
my submitted patch broke the generated sql for hypersonic. After I was
able to run the test project I found it out.
But I was able to get it to work again, here's the patch description:
Using hsqldb with id-method native columns creates columns defined with
the constraint "IDENTITY". This generated an index starting with 0. As
Torque regards objects with an primary key 0 as not initialized, this
can lead to problems when using relationships between tables. To prevent
this I've patched the generated string from "IDENTITY" to "GENERATED BY
DEFAULT AS IDENTITY (STARTING WITH 0)". This broke the created SQL for
hsqldb tables since Torque preprends the String "NOT NULL" in front of
the identity string. hsqldb does not allow this. To change this I've
patched the interface org.apache.torque.engine.platform.Platform and
added a method "public getNullString(Column col)" since the
PlatformHypersonicImpl needed to know if the column is a not null and if
the column uses the native id-method. The default implementation of
Platform delegates the call of this method to the former existing one.
Only the hsqldb implementation uses more than the property notNull of
the column. The new method is now used getSqlString of class Column.
Please find the patch files attached.
Cheers, Patrick
Patrick Carl schrieb:
> Hello there,
>
> when using Torque with hsqldb adn native id generation Torque uses the
> column constraint "IDENTITY" of hsqldb. This creates by default primary
> key ids starting with 0. This can lead to false behaviour in the
> following conditions:
> It is possible that an object A with id 0 is created. If this object is
> refered by an related object B, the method B.getA does not work, since
> B.getA checks if B.aId != 0.
>
> To prevent this I created the attached patches. Using them will set the
> starting id of an identity column to 1.
>
> Please note that I wasn't abled to get the test project to work, so I
> couldn't test this patch as much as I would. Perhaps someone else can do
> this.
>
> Patrick
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-dev-help@db.apache.org