search

coldbox-modules / quick

A ColdBox ORM Engine
https://quick.ortusbooks.com
MIT License
23 stars 19 forks source link

Fixed issue with NULL values being inserted into identity columns for MSSQL #250

Closed ryanalbrecht closed 2 months ago

ryanalbrecht commented 2 months ago

In relation to auto-incrementing fields MySQL and MSSQL have different behaviors when inserting a null value into the database. eg.

# id = auto-increment
INSERT INTO song (id,title) 
VALUES (NULL, 'some song name')

MySQL will accept the statement and generate a new key. MSSQL will throw an exception.

When inserting subclassed entities quick will retrieve an instance of the parent class and populate (fill) it using getMemento(). This causes an issue as getMemento() will included empty attributes which will explicity set an id property with an empty string. This will subsequently flow into the save() call causing an exception. Using retrieveAttributesData() solved this issue.

elpete commented 2 months ago

Looks like you have some failures on Adobe engines to check.

ryanalbrecht commented 2 months ago

@elpete There are two different issue at play here with this pull request. The first is an easy one. Adobe released a patch yesterday which changes the default encryption algorithm for the hash() function. This was a simple fix

The other is more nuanced. Mysql, by default does not store the millisecond part of a timestamp field. To enable this you have to specify a precision on the column definition such as timestamp(1), timestamp(2), timestamp(3) etc. The reason these tests are failing is because qb will output date binding with a format that include the millisecond part (dateTimeFormat( value, "yyyy-mm-dd HH:nn:ss.lll" ). This will cause Mysql to round to the nearest second when saving it. This sometimes causes a mismatch when comparing the provided vs stored value.

Not a 100% sure how to proceed on this. What are you thoughts?

elpete commented 2 months ago

I think we do this in two parts:

  1. Update the migration that creates this column to create it at the correct precision level. The raw function should let you do that.
  2. Add a ticket in qb to consider changing the default MySQL timestamp and datetime columns to have millisecond precision.
ryanalbrecht commented 2 months ago

Copy that. Will get to work on this

ryanalbrecht commented 2 months ago

I changed the migration for the 'songs' table to be created using the raw function. I also added changes for qb.

See pr https://github.com/coldbox-modules/qb/pull/282

elpete commented 2 months ago

Thank you!