4

I'm using CiviCRM with Drupal. When upgrading a site to a new CiviCRM major version, it's very useful to know all the database schema changes introduced in the new version, so that any custom code referring to database fields can be amended accordingly.

A case in point is civicrm_contribution_recur.next_sched_contribution -> next_sched_contribution_date in 4.3 -> 4.4 .

"You shouldn't be accessing database fields directly, you should be using the CiviCRM API", I hear someone say. Often that is the preferred approach but even so, API v3 is very closely tied to the database structure and the fields returned by an API call reflect the database field names, so the code would still need amending.

A really useful resource for CiviCRM would be an equivalent of Drupal's Converting n.x modules to (n+1).x documentation.

  • Invoke hook_schema() for the modules you're interested in before the upgrade, save the result, then do the same thing afterwards. Diff the two, voila - that's what changed – Clive Nov 6 '14 at 11:22
  • Thanks Clive, however I believe CiviCRM does not use hook_schema. Being CRM-agnostic, it creates and updates its db schema itself. That's a useful tip for other modules, however. – davejenx Nov 6 '14 at 11:38
  • Ah, my bad I didn't realise that – Clive Nov 6 '14 at 11:41
4

One way to do it would be to pore through the CRM/Upgrade/ directory in the new version, looking for schema changes. However these are distributed across multiple files, depending on which alpha or beta version the change was introduced in. E.g. the next_sched_contribution_date change was introduced in this file:

civicrm/CRM/Upgrade/Incremental/sql/4.4.alpha1.mysql.tpl

4

An easier way of doing it from a pure structural perspective is to use a mysql schema diff tool, something like:

http://adamspiers.org/computing/mysqldiff/

However that might not give you all the changes, but i suspect will catch most changes. A lot better to do it in an automated way, IMO :)

4

Another option would be to diff the xml schema between versions. The xml schema files are not shipped in the tar.gz releases, only in git, so it's not exactly the most user-friendly method, but it could be a good starting point.

You could also try:

git log -w -p tag1..tag2 xml/schema

For example:

git log -w -p 4.4.5..4.5.2 xml/schema

Not perfect, but semi-comprehensible.

  • The command syntax as given doesn't work, however this does: "git log -w -p 4.3.0..4.4.0 xml/schema" Thanks for pointing me in that direction. This approach results in pretty verbose output, e.g. including many commits that simply add comments, so it can be difficult to see the wood for the trees. The best approach I think would be to document and explain schema changes per major version but given that this doesn't seem to be done currently, your answer is very helpful, thank you. – davejenx Nov 10 '14 at 13:50
  • Comparing 4.4.0 with 4.5.0 using this method produces 19061 lines of output - far too much to wade through and mostly not real schema changes, e.g. adding titles. So I opted for a mysql schema diff tool, mysqldbcompare, which produced 1400 lines. I have edited this down to a fairly compact summary and will happily document that somewhere if it's of interest. – davejenx Jul 6 '15 at 17:25
2

I have now found a viable approach to identifying the changes, using mysqldbcompare. I've added a couple of pages to the CiviCRM Wiki, under Developer Resources -> Reference Sheets -> Database Reference:

Database Schema Changes: CiviCRM 4.4 to 4.5

Database Schema Changes: CiviCRM 4.5 to 4.6

Note that there is now a CiviCRM Stack Exchange site, currently in beta. Should this question move there?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.