CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion (Moderator: Donald Lobo) »
  • Why do we allow null on boolean fields
Pages: [1]

Author Topic: Why do we allow null on boolean fields  (Read 355 times)

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Why do we allow null on boolean fields
August 25, 2014, 05:35:19 pm
I'm debugging some custom code where many contacts are being excluded from a query because the criteria is 'do_not_email' = 0 rather than 'do_not_email' = 0 OR do_not_email IS NULL

perhaps the api would have handled this problem (or maybe not) & the use of custom sql is part of the problem.

But, I've debugged quite a few like this is core too & it seems to me that we make our lives difficult for very little performance gain by allowing boolean fields to be NULL. I won't go as far as to say all boolean fields should default to 0 but DB level defaults of 0 + not allowing NULL seems to be less gaff-prone for these fields at least
do_not_email
do_not_phone
is_deceased (that may not be a binary field actually as a few hours of debugging can leave me unclear as to whether at least parts of my brain are)
do_not_mail
do_not_sms
do_not_trade

On email
is_primary
is_billing

I guess we save space with the NULL but it seems like a small amount of space gain for a lot of extra error-risk & slightly less efficient queries
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute

Erik Hommel

  • Forum Godess / God
  • I live on this forum
  • *****
  • Posts: 1773
  • Karma: 59
    • EE-atWork
  • CiviCRM version: all sorts
  • CMS version: Drupal
  • MySQL version: Ubuntu's latest LTS version
  • PHP version: Ubuntu's latest LTS version
Re: Why do we allow null on boolean fields
August 25, 2014, 11:24:37 pm
+1
Consultant/project manager at EEatWork and CiviCooP (http://www.civicoop.org/)

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Why do we allow null on boolean fields
August 26, 2014, 07:09:13 am

+1

I think this was done due to legacy reasons. The only issue doing so now, would be the upgrade ALTER table commands (to set the default value in the DB to 0 for all booleans) will take a long long time on large db's. But biting the bullet sooner rather than later is definitely better, IMO

lobo

A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Eileen

  • Forum Godess / God
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4195
  • Karma: 218
    • Fuzion
Re: Why do we allow null on boolean fields
August 26, 2014, 02:59:45 pm
OK - I've logged https://issues.civicrm.org/jira/browse/CRM-15189 so it stays on the radar.

From my experience the largest tables are usually the mailing and activity tables. The changes to mailing tables in 4.2 derailed a few upgrades but the same DBs survived the activity table changes ok -

done some tests on a DB with 238,000 contacts
 Historically this DB didn't cope well with the 4.2 upgrade script (which altered the mailing tables) but did cope with the 4.4 upgrade script which altered activity tables. I *think* it even coped with the post-fact addition of an index to the activity table

SELECT count(*) FROM civicrm_contact;
UPDATE civicrm_contact SET do_not_email = 0 WHERE do_not_email IS NULL;
UPDATE civicrm_contact SET do_not_phone = 0 WHERE do_not_phone IS NULL;
UPDATE civicrm_contact SET is_deceased = 0 WHERE is_deceased IS NULL;
UPDATE civicrm_contact SET do_not_mail = 0 WHERE do_not_mail IS NULL;
UPDATE civicrm_contact SET do_not_sms = 0 WHERE do_not_sms IS NULL;
UPDATE civicrm_contact SET do_not_trade = 0 WHERE do_not_trade IS NULL;
ALTER TABLE `civicrm_contact`
CHANGE COLUMN `is_deceased` `is_deceased` TINYINT(4) NOT NULL DEFAULT '0',
CHANGE COLUMN `is_deleted` `is_deleted` TINYINT(4) NOT NULL DEFAULT '0',
CHANGE COLUMN `do_not_email` `do_not_email` TINYINT(4) NOT NULL DEFAULT '0',
CHANGE COLUMN `do_not_phone` `do_not_phone` TINYINT(4) NOT NULL DEFAULT '0',
CHANGE COLUMN `do_not_sms` `do_not_smsl` TINYINT(4) NOT NULL DEFAULT '0',
CHANGE COLUMN `do_not_trade` `do_not_tradel` TINYINT(4) NOT NULL DEFAULT '0',
CHANGE COLUMN `do_not_mail` `do_not_mail` TINYINT(4) NOT NULL DEFAULT '0'
;

The alter took 30 seconds for one DB & 1.47 mins for another DB with similar size for a different customer

On a site with half as many contacts the alter took half the time.

By comparison the alters in the 4.2 upgrade script took 2 hours  & the 4.4 took a few minutes.

Most of the fields now have defaults of 0 - although some still allow NULL. The NULL values appear to be mainly historical

I assume if we change the schema to not allow NULL it won't try to force it for the log tables?
« Last Edit: August 26, 2014, 03:04:04 pm by Eileen »
Make today the day you step up to support CiviCRM and all the amazing organisations that are using it to improve our world - http://civicrm.org/contribute
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Developer Discussion (Moderator: Donald Lobo) »
  • Why do we allow null on boolean fields

This forum was archived on 2017-11-26.