FAQ
Edit report at https://pear.php.net/bugs/bug.php?id=14209&edit=1

ID: 14209
Updated by: daniel.oconnor@gmail.com
Reported By: holger dot schletz at web dot de
Summary: Default portability options not suitable for
upgrading
-Status: Feedback
+Status: Open
Type: Bug
Package: MDB2_Schema
Package Version: 0.8.2
PHP Version: Irrelevant
Roadmap Versions:
New Comment:

-Status: Feedback
+Status: Open



Previous Comments:
------------------------------------------------------------------------

[2009-02-22 15:57:41] hschletz

This is what I do for creating/upgrading database schemas:

$options = array (
'quote_identifier' => true,
'force_defaults' => false,
'portability' => MDB2_PORTABILITY_ALL ^
MDB2_PORTABILITY_EMPTY_TO_NULL
);
$schema =& MDB2_Schema::factory($dsn, $options);
...

This has been extensively tested and does exactly what I expect. Note
that 'force_defaults' has to be explicitly set to false to exactly
reflect the given input schema.

It's been a long time since I hade a closer look at the code, but yes, I
think updateDatabase() should be the right place for this. Just ensure
that the old value is restored in every case, even if an error occurs
within one of the functions called direclty or indirectly from within
updateDatabase().

This would still leave the potential portability issue open. Maybe the
validation code could be modified to issue a friendly warning whenever
an empty string is given as a default value?

------------------------------------------------------------------------

[2009-02-22 15:21:05] ifeghali

-Status: Open
+Status: Feedback

Hello Holger,

With portability = entirely off we don't face this problem ?

I am inclined to sticky with "Temporarily disable the
MDB2_PORTABILITY_EMPTY_TO_NULL setting within the relevant functions and
restore the old value on function exit.". Maybe this would be necessary
only in updateDatabase() ? Or do you have in mind any other place ?

------------------------------------------------------------------------

[2008-11-23 06:56:21] hschletz

Option #1 is meant as a design decision to the database schema, not as a
suggested change to the MDB2_Schema code. If someone really needs empty
default values (there may be no choice when migrating an existing
schema), they could still be used, with all the caveats. Two things
should be considered:

1. If you put a NOT NULL constraint on a column and then make up an
empty default value just to get arround the constraint, you should ask
yourself whether the constraint makes sense at all. An empty string is
effectively a pseudo-NULL in most cases (logically, not technically), so
we could as well stick to NULL. That's what the NULL contruct is meant
for.
I don't say there is no use for this at all, but every case I have seen
that far is actually poor design and doesn't make any sense at a closer
look.

2. Your schema will not be portable. For text columns, the mentioned
Oracle issue may give unexpected results. For timestamp columns, each
DBMS has a differrent idea of valid input. PostgreSQL neither allows
empty strings nor '0000-00-00', as allowed by MySQL. Again, NULL is your
friend.

------------------------------------------------------------------------

[2008-10-24 21:09:26] ifeghali

Just to note, option #1 would make impossible a fix for Bug #14650.

------------------------------------------------------------------------

[2008-08-10 07:26:32] hschletz

There are a lot of possible combinations which are difficult to explore
completely. To make things worse, each DBMS may behave differently. The
following was tested with PostgreSQL 8.1 and MDB2_Schema's
'force_defaults' option set to 'false'.

For a basic test case, create a table with the following XML field
definition:

<field>
<name>default_empty_string</name>
<type>text</type>
<default></default>
<notnull>true</notnull>
</field>

We'd expect the following result:

Column | Type |
Modifiers
----------------------+-------------------------+----------------------------------------
default_empty_string | character varying(4096) | not null default
''::character varying

If we apply the same definition again on the existing table, the column
is expected not to be altered.

With MDB2_PORTABILITY_EMPTY_TO_NULL disabled, the result is exactly like
this. Note that the <notnull> property is required for
<default></default>. Otherwise the default value would be NULL instead
of '' (possibly another bug?). It is not required for nonempty default
values.


With MDB2_PORTABILITY_EMPTY_TO_NULL enabled (the default setting), the
table is created like this:

Column | Type |
Modifiers
----------------------+-------------------------+-----------------------------------------
default_empty_string | character varying(4096) | not null default '
'::character varying

If we apply the same definition again on the existing table we get:

Column | Type |
Modifiers
----------------------+-------------------------+-----------------------------------------
default_empty_string | character varying(4096) | not null

This is a stable state which does not change anymore on further
upgrades. Unfortunately, it's the opposite of what we wanted.


MDB2_PORTABILITY_EMPTY_TO_NULL must be used with care. If the
application is not explicitly designed for its behavior (like
MDB2_Schema, apparently), it may not behave as expected. Furthermore,
the logic applies only to the application, not to the database itself.
While this option operates on retrieved data, it has no effect on SQL
queries and the way they are processed within the database. But notnull
constraints and default values are part of the database's internal logic
- if the DBMS distincts empty strings and NULL values, we cannot force
it to treat them as equivalent. Since clear rules are vital for data
integrity, the ambiguity resulting from the
MDB2_PORTABILITY_EMPTY_TO_NULL setting makes it unsuitable for managing
the database schema.


So what could we do about it?

Option 1: For best portability, avoid empty strings as default values.
This should be clearly pointed out in the documentation. This is
possible if we design a database and application from scratch. When
porting an existing database or application to MDB2, things might get
more complicated.

Option 2: Disallow empty strings completely as default values. If we
really need them (with all the caveats), provide an option to re-enable
them.

Option 3: Disallow the presence of MDB2_PORTABILITY_EMPTY_TO_NULL when
creating the MDB2_Schema object.

Option 4: Review the MDB2_Schema code for handling of default values and
work around side-effects of the MDB2_PORTABILITY_EMPTY_TO_NULL setting.

Option 5: Temporarily disable the MDB2_PORTABILITY_EMPTY_TO_NULL setting
within the relevant functions and restore the old value on function
exit.
This should work with all DBMS that distinct empty strings and NULL
values. How this would behave with Oracle (the guilty one for all this
trouble, according to MDB2 documentation) still has to be investigated.


Option 1 is the preferred way to deal with the problem. Option 2 could
enforce these measures, but this could break existing applications and
schemas (quick workaround possible though, by setting an option.) This
also gives us the opportunity to think about what we're doing. Again, a
big warning in the documentation could be helpful.
Option 3 would save us from bad surprises if we choose to enable empty
strings. Again, minor changes to existing code could be necessary if a
Database object is passed to the constructor (instead of having one
created automatically, in which case the constructor should take care of
disabling the setting).

Options 4 and 5 are quirks that won't necessarily force changes to
existing applications, although we might experience different, possibly
erratic behavior if the code relies on the current implementation.


Of course it's possible to get everything right without any changes to
the MDB2_Schema code, but it took me hours to find out. The suggested
changes could help pushing the developer on the right track.

------------------------------------------------------------------------

The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://pear.php.net/bugs/bug.php?id=14209

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppear-bugs @
categoriesphp
postedJan 27, '12 at 1:25p
activeJan 27, '12 at 1:25p
posts1
users1
websitepear.php.net

1 user in discussion

Daniel Oconnor: 1 post

People

Translate

site design / logo © 2022 Grokbase