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
_______________________________________________
Devel mailing list
Devel@lists.openser.org
http://lists.openser.org/cgi-bin/mailman/listinfo/devel
