Eclipse Community Forums: EMF » [CDO] Database mapping: explicitly specify nullable columns

Home » Modeling » EMF » [CDO] Database mapping: explicitly specify nullable columns(The default null/not-null is not consistent among every database engine)

Show: Today's Messages :: Show Polls :: Message Navigator

[CDO] Database mapping: explicitly specify nullable columns [message #1855834]

Mon, 07 November 2022 09:20

Robert Schulk

Messages: 144
Registered: July 2015

Senior Member

I found this issue for nullable EDate attributes in a mysql database.
In mysql, the first(!) column of type "timestamp" will get the default value "current time", if the column is not explicitly declared as nullable. This can lead to very strange effects in CDO, because first the value seems to be set correctly (to null), but once the value is read again from the database, the value changes to some timestamp.

Potentially, the fix is quite simple. In DBAdapter.java, line 965, instead of using an empty string for nullable fields, we should return "NULL" instead.

  protected String createFieldDefinition(IDBField field)
  {
    return getTypeName(field) + (field.isNotNull() ? " NOT NULL" : ""); //$NON-NLS-1$ //$NON-NLS-2$
  }

Report message to a moderator

Re: [CDO] Database mapping: explicitly specify nullable columns [message #1855849 is a reply to message #1855834]

Mon, 07 November 2022 17:03

Eike Stepper

Messages: 6682
Registered: July 2009

Senior Member

I'd prefer to keep the impact of applied changes as small as possible. So, why not change just MYSQLAdapter and limit the new behavior to just the datatypes that need it?!

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Report message to a moderator

Re: [CDO] Database mapping: explicitly specify nullable columns [message #1855866 is a reply to message #1855849]

Tue, 08 November 2022 09:41

Robert Schulk

Messages: 144
Registered: July 2015

Senior Member

I am no SQL expert. I only searched the web and found e.g. this post: https://www.red-gate.com/hub/product-learning/sql-prompt/why-you-should-always-specify-whether-a-column-accepts-nulls

But I also get your point that this global change does have some risks. For our use case, the restriction to the mysql engine is sufficient, of course.

Report message to a moderator

Re: [CDO] Database mapping: explicitly specify nullable columns [message #1855875 is a reply to message #1855866]

Tue, 08 November 2022 17:03

Eike Stepper

Messages: 6682
Registered: July 2009

Senior Member

I've read the that article and I agree with it.

More importantly, the behavior compatibility issue is not as severe as I thought.
Your proposed change only impacts the DDL generation, not the internal schema model itself.
So, with your proposed change we'd not create any schema deltas for existing DBs.
Good ;-)

I've submitted and resolved https://bugs.eclipse.org/581014 .
Please CC yourself in case we must discuss regressions caused by the change....

Cheers
/Eike

----
http://www.esc-net.de
http://thegordian.blogspot.com
http://twitter.com/eikestepper

Report message to a moderator

Re: [CDO] Database mapping: explicitly specify nullable columns [message #1857643 is a reply to message #1855875]

Fri, 17 February 2023 18:42

Alexander Stenzer

Messages: 9
Registered: April 2022

Junior Member

I think the derby database does not allow that. https://db.apache.org/derby/docs/10.16/ref/rrefsqlj16095.html#rrefsqlj16095
So you have to set -Dorg.eclipse.net4j.spi.db.DBAdapter.NO_NULLABLE_DEFAULT=true

Cheers
/Alexander

Report message to a moderator

Previous Topic:	[CDO] Idiomatic way to install dynamic MM (i.e. Ecore file)
Next Topic:	[CDO] ConcurrentHashMap NPE.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 17:35:27 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter