Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

fActiveRecord update question

posted by ihumanable 9 years ago

I just noticed some odd behavior from fActiveRecord. I created a test table to see what was up, here is a simple user table I put into MySQL

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(100) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I created a test user, then I ran the following code

$user = new User(1);
$user->setEmail('test@test.net');
$user->store();

It produces the following update statement

UPDATE "users" SET 
"email" = 'test@test.com', 
"password_hash" = 'fCryptography::password_hash#PSIL2YtbUS#4c7f51458e232997d16f6775fa9dfda75a8cab63', "first_name" = NULL, 
"last_name" = NULL, 
"active" = FALSE, 
"created_at" = '2010-06-29 23:29:29', 
"updated_at" = '2010-06-29 23:29:29' 
WHERE "users"."user_id" = 1

I was surprised to see this. I had pulled a record out of the database and changed a single field, even though the fActiveRecord implementation tracks the old_values and the current values it still decides to update every column. This is causing an issue because none of my records ever set their timestamp past the created_at timestamp.

I was expecting to see a query that looked more like this

UPDATE "users" SET
"email" = 'test@test.net'
WHERE "users"."user_id" = 1

I guess my question is the following. Is there a defect in fActiveRecord or is my thinking about this all wrong.

Thank you for your time.

Well, the MySQL timestamp column seems like the real culprit here. There is fORMDate::configureDateUpdatedColumn() which provides the same functionality, but works across all databases, and has the benefit of the update timestamp being available to your PHP without having to reselect data from the table. I believe you could use the MySQL timestamp data type with fORM::configureDateUpdatedColumn() to provide functionality that works from Flourish in an optimal way, but also for raw queries run against the database.

The issues I could see with changing the update statement to only include columns that have changed would be what would happen when no columns have changed? Would the store action do nothing? That could have the potential of not triggering database triggers, and probably some other things. Let say we just re-set the primary key values to their current value. This could be confusing since only some values were being re-set and not all.

posted by wbond 9 years ago

Thanks for the nudge in the right direction fORMDate::configureDateCreatedColumn() and fORMDate::configureDateUpdatedColumn() were exactly what I was looking for, I just didn't realize they existed.

Once again the solution to some issue I'm having with Flourish has already been solved and is neatly packed up in Flourish, I just need to take more time to read the docs.

Thanks again Will.

posted by ihumanable 9 years ago