[ic] Matrix options and set_row possible bug?
Jon Jensen
jon at endpoint.com
Thu Jun 22 12:03:11 EDT 2006
On Wed, 21 Jun 2006, Peter wrote:
> Ok, I've been having problems with trying to use Matrix options on my IC 5.2
> core/4.9 foundation based catalogs (with a fair bit of customization).
> Anyways, I'll list the symptoms of the problem first, then show you what else
> I've discovered in my attempts at solving the problem...
>
> I can change a given item to Matrix Options but can't generate the variants
> with the "Create all possible combinations" button in the UI. Also items that
> are set to matrix options cannot be added to a shopping cart (this last
> symptom may just be because there are no variants for the item and so IC
> can't find a varyant to add to the cart).
>
> Checking the error log I get the following when I click on the "Create all
> possible combinations" button:
>
> 203.173.163.5 jqJXiIfA:203.173.163.5 - [21/June/2006:18:38:03 -0700] mr-s
> /cgi-bin/mr-s/admin/item_option_old.html Safe: DBD::Pg::st execute failed:
> ERROR: null value in
> column "sku" violates not-null constraint
>
> ...followed by a long block of code which is the [perl] block contained in
> the [if cgi explode] block in the file
> include/Options/old/item_option_matrix.
>
> If I then click on the "Create all possible combinations" button again I get
> no errors at all and the item gets set back to simple options.
>
> So I turned debugging on, added in/uncommented a number of Debug() and
> ::logDebug() lines and eventually narrowed the problem down to the
> Vend::Table::DBI::set_row sub. I uncommented all the ::logDebug() lines in
> the set_row sub and got the following output in my debug log (when I click on
> "Create all possible combinations"):
>
> Vend::Table::DBI:debug: set_row fields='HT148-NoChoice-NoChoice,,,,,,,,,'
> Vend::Table::DBI:debug: set_row query=INSERT INTO options VALUES (?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> Vend::Table::DBI:debug: set_row fields='HT148-NoChoice-NoChoice,,,,,,,,,'
> Safe: DBD::Pg::st execute failed: ERROR: null value in column "sku" violates
> not-null constraint
>
> ...same long block of code as before here.
>
> From what I can tell, there is just one field in the @fields array which
> marks all the rest as undefined and DBI interprets the undefined value as
> NULL, so DBI is setting all those ? placeholders (barring the first) to NULL
> in an insert statement. This clashes with the NOT NULL field constraints in
> the options table.
>
> I realise that I can probably work around this by removing the NOT NULL
> constraints from the fields, but I'd really rather fix this problem properly.
> Unfortunately it looks like this may be a problem (bug?) in the set_row sub
> and it will require a significant rewrite to fix it. I simply do not know
> the code well enough to be able to rewrite it without worrying about breaking
> some other compatibility elsewhere.
Peter,
It is a known deficiency in some Interchange database routines that when a
new row is created, it's first created with only the primary key and the
remaining fields NULL, and then updated to populate the fields. Obviously
quite bad if you're using a well-designed database that doesn't allow NULL
all over the place.
I can't remember if that has been fixed in newer versions of Interchange
yet or not, but it's worth taking a look at the latest set_row and any
dependent routines in CVS.
I will also note that it's pretty pointless to try to keep a tight data
model in the options table when you're using the old 4.8-style options,
because the table is not even remotely normalized: There are essentially
three different kinds of rows crammed into the same table, and it just
isn't a sane data model.
So you'd might as well just consider dropping the NOT NULL constraints on
options, as the table is fairly messy anyway. If you want to do things a
better way, look at the newer options introduced in (I think) Interchange
5.2, using a separate variants table.
Jon
--
Jon Jensen
End Point Corporation
http://www.endpoint.com/
More information about the interchange-users
mailing list