[OpenSER-Devel] oracle schema, VARCHAR fields NOT NULL

Dan Pascu dan at ag-projects.com
Wed Apr 23 17:49:34 CEST 2008


On Wednesday 23 April 2008, Henning Westerholt wrote:
> Hi all,
>
> another issue (hopefully the last) that Iouri and i noticed during our
> work on db_oracle: The actual db schemes defines most of the VARCHAR
> colums as "DEFAULT '' NOT NULL".
>
> As a default value is defined, this NOT NULL is not really useful, it
> only specify that the colum can't be set explicit to NULL. There is no
> infrastructure in the core available for this at the moment, and the
> control scripts uses this only for the RPID value.
>
> This causes a problem for the db_oracle module. For Oracle are
> VARCHAR[,2] NULL values and the empty string ('') equivalent (don't ask
> me why). Thus most of the insert statement, e.g. in the control scripts
> fails for string values, because oracle think that they want to insert
> NULL into the column, and this is not allowed.
>
> Are this NOT NULL defines actually needed if an default value is
> specified? 

I think they are, because I can connect to the database by other means and 
manually insert/modify an entry and set that to NULL. Then the software 
will get confused because it doesn't expect a NULL value in that column.

Also at least in mysql (not sure about the others) if a column is NULL, it 
must be checked using specific operators (IS NULL or IS NOT NULL). 
Otherwise a test like WHERE column != 'value' will only return entries 
that have a value that is different from 'value' as long as it's a string 
(including the empty string), but will not return entries that have the 
column set to NULL even though NULL is different from 'value'. To include 
those as well the test would have to be written like:

WHERE column != 'value' OR column IS NULL

So if the software is not prepared it can miss entries or get confused 
about the values.

> Its is possible to remove this completly for the string 
> values, or perhaps even for all values with an given default?

I don't thinks so, considering the reasons above.

> Sure, it would be possible to remove this clause only for the oracle
> driver, but as they are generated from a common XSL file (sql.xsl) i'd
> be necessary to duplicate this functionality. And if this stuff is not
> actually useful, it would also allow to cleanup the generated schemes
> somewhat.


-- 
Dan



More information about the Devel mailing list