You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2020/09/08 20:12:52 UTC
Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column
Working with old DB designs really results in the weirdest questions…
So… I've been working around a design problem in a customer DB by using my own BatchTranslatorFactory. The functionality was that if a column is called "company", every update wraps the column's new value in a coalesce function to ensure that it's never set to null (for… reasons). This has worked great as a workaround for our problem.
However, SQL generation in Cayenne 4.2 is all new so my current solution ( https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a <https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a> ) doesn't really port.
Before I start considering migration to 4.2, is this possible to do there? Or should I just bite the bullet and start fixing up that bloody DB before upgrading?
Cheers,
- hugi
Re: Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Thanks John, not a bad idea.
I'd prefer it if the current method ports though (since I'd have to create triggers for something like 200 columns). But if that isn't possible we might consider this.
- hugi
> On 8 Sep 2020, at 20:33, John Huss <jo...@gmail.com> wrote:
>
> Since you are already at the DB level (outside of cayenne) making this
> change, I would just use a BEFORE UPDATE row-level trigger in Postgres. You
> can change the value there.
>
> Something like this would work:
>
> CREATE OR REPLACE FUNCTION company_default()
>
> RETURNS trigger AS $$
>
> BEGIN
>
> NEW.company = coalesce(NEW.company, <some default>);
>
> RETURN NEW;
>
> END;
>
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER mytable_update
> BEFORE UPDATE ON MyTable
> FOR EACH ROW
> WHEN (NEW.company IS NULL)
> EXECUTE FUNCTION company_default();
>
>
> On Tue, Sep 8, 2020 at 3:13 PM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Working with old DB designs really results in the weirdest questions…
>>
>> So… I've been working around a design problem in a customer DB by using my
>> own BatchTranslatorFactory. The functionality was that if a column is
>> called "company", every update wraps the column's new value in a coalesce
>> function to ensure that it's never set to null (for… reasons). This has
>> worked great as a workaround for our problem.
>>
>> However, SQL generation in Cayenne 4.2 is all new so my current solution (
>> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a <
>> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a> )
>> doesn't really port.
>> Before I start considering migration to 4.2, is this possible to do there?
>> Or should I just bite the bullet and start fixing up that bloody DB before
>> upgrading?
>>
>> Cheers,
>> - hugi
Re: Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column
Posted by John Huss <jo...@gmail.com>.
Since you are already at the DB level (outside of cayenne) making this
change, I would just use a BEFORE UPDATE row-level trigger in Postgres. You
can change the value there.
Something like this would work:
CREATE OR REPLACE FUNCTION company_default()
RETURNS trigger AS $$
BEGIN
NEW.company = coalesce(NEW.company, <some default>);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER mytable_update
BEFORE UPDATE ON MyTable
FOR EACH ROW
WHEN (NEW.company IS NULL)
EXECUTE FUNCTION company_default();
On Tue, Sep 8, 2020 at 3:13 PM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Working with old DB designs really results in the weirdest questions…
>
> So… I've been working around a design problem in a customer DB by using my
> own BatchTranslatorFactory. The functionality was that if a column is
> called "company", every update wraps the column's new value in a coalesce
> function to ensure that it's never set to null (for… reasons). This has
> worked great as a workaround for our problem.
>
> However, SQL generation in Cayenne 4.2 is all new so my current solution (
> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a <
> https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a> )
> doesn't really port.
> Before I start considering migration to 4.2, is this possible to do there?
> Or should I just bite the bullet and start fixing up that bloody DB before
> upgrading?
>
> Cheers,
> - hugi
Re: Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column
Posted by Hugi Thordarson <hu...@karlmenn.is>.
First glance at that API looks real nice, I'm going to check this out, thanks :)
- hugi
> On 11 Sep 2020, at 07:06, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> 4.2 for the first time exposes generated SQL as an AST, so tweaking it has become much easier. This is the feature that made possible to support geospatial and JSON types.
>
> Nikita, please correct me if I am wrong with the following instructions (as this API is still in flux). So.. To customize produced SQL, you will need to register a custom DbAdapter that returns a custom SQLTreeProcessor. SQLTreeProcessor is a SQL tree walking callback that can change the default tree structure. Your own processor can extend TypeAwareSQLTreeProcessor. See for instance PostgreSQLTreeProcessor for how to change the default behavior.
>
> Andrus
>
>
>> On Sep 8, 2020, at 11:12 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>>
>> Working with old DB designs really results in the weirdest questions…
>>
>> So… I've been working around a design problem in a customer DB by using my own BatchTranslatorFactory. The functionality was that if a column is called "company", every update wraps the column's new value in a coalesce function to ensure that it's never set to null (for… reasons). This has worked great as a workaround for our problem.
>>
>> However, SQL generation in Cayenne 4.2 is all new so my current solution ( https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a <https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a> ) doesn't really port.
>> Before I start considering migration to 4.2, is this possible to do there? Or should I just bite the bullet and start fixing up that bloody DB before upgrading?
>>
>> Cheers,
>> - hugi
>
Re: Cayenne 4.2—Modifying SQL generation to always use COALESCE when updating a certain column
Posted by Andrus Adamchik <an...@objectstyle.org>.
4.2 for the first time exposes generated SQL as an AST, so tweaking it has become much easier. This is the feature that made possible to support geospatial and JSON types.
Nikita, please correct me if I am wrong with the following instructions (as this API is still in flux). So.. To customize produced SQL, you will need to register a custom DbAdapter that returns a custom SQLTreeProcessor. SQLTreeProcessor is a SQL tree walking callback that can change the default tree structure. Your own processor can extend TypeAwareSQLTreeProcessor. See for instance PostgreSQLTreeProcessor for how to change the default behavior.
Andrus
> On Sep 8, 2020, at 11:12 PM, Hugi Thordarson <hu...@karlmenn.is> wrote:
>
> Working with old DB designs really results in the weirdest questions…
>
> So… I've been working around a design problem in a customer DB by using my own BatchTranslatorFactory. The functionality was that if a column is called "company", every update wraps the column's new value in a coalesce function to ensure that it's never set to null (for… reasons). This has worked great as a workaround for our problem.
>
> However, SQL generation in Cayenne 4.2 is all new so my current solution ( https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a <https://gist.github.com/hugith/a33a20fc7da7fd8f709f59ce3a30a96a> ) doesn't really port.
> Before I start considering migration to 4.2, is this possible to do there? Or should I just bite the bullet and start fixing up that bloody DB before upgrading?
>
> Cheers,
> - hugi