Drupal core 7.67 / Ubercart 7.x-3.13 / Stability Theme
One of the things I want to avoid (because I hate it when it happens to me!) is when a website upgrades, they lose all of my data and I have to re-enter it all over again. It's a small thing, I know - but why should I have to re-enter my information when it was them who decided to upgrade? It seems unfair somehow to offload the data entry to the customer, when it is the vendor making the decision to make a change.
Migrating Drupal 6 Ubercart Addresses to Drupal 7 Ubercart
Now that all of our active customers have been migrated from the Drupal 6 System to the Drupal 7 System, we want to continue to make their inter-system transition as easy as possible.
Let's see what we are up against.
First, we have to figure out where customer addresses are stored in the Drupal 6 System.
Well, it looks like we may be in luck. There are two tables in our Drupal 6 System with very evocative names:
uc_addresses
uc_addresses_defaults
uc_addresses
uc_addresses_defaults
Let's see what these tables are all about, shall we?
In the Drupal 6 System, the uc_addresses table has the following schema:
In the Drupal 6 System, the uc_addresses table has the following schema:
mysql> show columns from uc_addresses
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| aid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| uid | int(10) unsigned | NO | | 0 | |
| first_name | varchar(255) | NO | | | |
| last_name | varchar(255) | NO | | | |
| phone | varchar(255) | NO | | | |
| company | varchar(255) | NO | | | |
| street1 | varchar(255) | NO | | | |
| street2 | varchar(255) | NO | | | |
| city | varchar(255) | NO | | | |
| zone | mediumint(9) | NO | | 0 | |
| postal_code | varchar(255) | NO | | | |
| country | mediumint(8) unsigned | NO | | 0 | |
| address_name | varchar(20) | YES | | NULL | |
| created | int(11) | NO | | 0 | |
| modified | int(11) | NO | | 0 | |
+--------------+-----------------------+------+-----+---------+----------------+
So it seems to me that:
1) User addresses are stored in the Drupal 6 System uc_addresses table
2) The address function is part of the Ubercart 2 subsystem
3) Everything is tied to the user uid value that appears in the users table
OK, now for a look at the schema of the uc_addresses_defaults table:
mysql> show columns from uc_addresses_defaults;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| aid | int(10) unsigned | NO | PRI | NULL | |
| uid | int(10) unsigned | NO | PRI | NULL | |
+-------+------------------+------+-----+---------+-------+
OK, this is a classic database lookup table that simply "glues" together two other tables (in this case, users, and uc_addresses) together by their primary keys, uid and aid.
Great! This is beginning to look really straightforward!
But before celebrating, let's have a look at the Drupal 7 System.
Hmmm...there's only one table!
uc_addresses
What's the table schema look like?
MariaDB [hph]> show columns from uc_addresses;
+------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------------+------+-----+---------+----------------+
| aid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| uid | int(10) unsigned | NO | | 0 | |
| first_name | varchar(255) | NO | | | |
| last_name | varchar(255) | NO | | | |
| phone | varchar(255) | NO | | | |
| company | varchar(255) | NO | | | |
| street1 | varchar(255) | NO | | | |
| street2 | varchar(255) | NO | | | |
| city | varchar(255) | NO | | | |
| zone | mediumint(9) | NO | | 0 | |
| postal_code | varchar(255) | NO | | | |
| country | mediumint(8) unsigned | NO | | 0 | |
| address_name | varchar(20) | YES | | NULL | |
| default_shipping | tinyint(4) | NO | | NULL | |
| default_billing | tinyint(4) | NO | | NULL | |
| created | int(11) | NO | | 0 | |
| modified | int(11) | NO | | 0 | |
+------------------+-----------------------+------+-----+---------+----------------+
No the same.
Worse yet, there's no uc_addreses_defaults table in the Drupal 7 System!
Maybe there's a module installed in the Drupal 6 System that isn't present in the Drupal 7 System that has something to do with "Multiple", "Preferred" or "Default" addresses....Nope.
So what the Ubercart people probably did was to collapse the functionality of those two tables into one, probably because it turned out that people often have multiple billing and shipping addresses, and sometimes they want to use the default billing address and/or default shipping address, and sometimes they don't.
So what the Drupal 7 people chose to do was to use a tinyint(4) flag instead to "tag" the record type with a 0 or a 1 to indicate if it is the default_billing or default_shipping (or both). This imposes a little bit of overhead on each address record, but it also saves a disk access because everthing is captured in one place.
Database designers can go either way on these design decisions, in Drupal 6 they decided to go 3NF, in Drupal 7 they decided to go 2NF. Both have their pros and cons. This only becomes a drag when you move from one design strategy to another, like they did - because it makes moving data complicated.
Here's a side-by-side comparison of the schemas of the two tables:
Yes, the only difference between the two tables is the default_shipping and default_billing flags.
OK, a plan of action is starting to emerge. Just moving the data over wholesale like we did when we moved the users is a non-starter, because we have since purged a few hundred users from our Drupal 6 System database, and their addresses might be bloating the uc_addresses table(s).
Let's get some numbers to check on that concern:
mysql> select count(*) from uc_addresses;
+----------+
| count(*) |
+----------+
| 1338 |
+----------+
Hmmm...only 885 unique users.
So, let's do a quick look for "orphan" address records, which would happen when the uid in the uc_addresses table does not exist as a uid in the users table;
mysql> select distinct(uid) from users order by uid;
<output removed for brevity>
882 rows in set (0.00 sec)
mysql> select distinct(uid) from users order by uid into outfile '~/users-uid.txt';
mysql> select distinct(uid) from uc_addresses order by uid into outfile '~/addresses-uid.txt';
Then we downloaded the resulting files (user-uid.txt and address-uid.txt) to our local system and then imported them into MS-EXCEL. Once they were in MS-EXCEL, we did a little programming to come up with the following result:
Every other row matched, so we really only have to pay attention to four (4) users of interest.
The first two are internal Drupal accounts:
User 0 (Anonymous) shouldn't have an entry in the uc_addresses table, and it doesn't. Good.
User 1 (Administrator) could have an entry in the uc_addresses table, and it does. Good
The final two rows are external, customer accounts, and they should have an entry in the uc_addresses table. The fact that they didn't is well worth looking into, because that may mean that thay registered, but never bought anything from us:
User 2340
User 2357
As it turns out, the following users have always ordered by telephone and never online, so their address was never entered into the Drupal 6 System database. But it they were in our accounting system, so we just entered their address data in for them.
Once that was done, every non-internal account had an address associated with it.
Now that we have balanced tables, things are getting easier and easier.
OK, now every user has at least one address entered into the system, but some users also have multiple addresses entered into the sytem, so we are going to need to figure out which address is their preferred (or default) Shipping and Billing address.
This is where the uc_default_addresses table now comes into play.
Looking into the table, it turned out that every uid that should have had a default address did, even the Administrator user.
So things are looking fairly simple now. Here's the plan:
1) On System A, connect to the Drupal 6 System database
2) Read the uc_addresses table, row by row
3) Check each row to see if it appears in the uc_addresses_defaults table
4) If it does appear in the uc_addresses_defaults table, flag it as the default Billing and Shipping address
5) If it does not appear in the uc_addresses_defaults table, enter it as an unflagged, alternative address
6) Once finished, move the data file physically from System A to System B
7) On System B, connect to the Drupal 7 System database
8) Read the data file directly into the Drupal 7 System database
Here's a sample from Export-Drupal-6-Ubercart-Addresses.php, the file I wrote to do that:
In the end, Export-Drupal-6-Ubercart-Addresses.php functioned quite well.
1) User addresses are stored in the Drupal 6 System uc_addresses table
2) The address function is part of the Ubercart 2 subsystem
3) Everything is tied to the user uid value that appears in the users table
OK, now for a look at the schema of the uc_addresses_defaults table:
mysql> show columns from uc_addresses_defaults;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| aid | int(10) unsigned | NO | PRI | NULL | |
| uid | int(10) unsigned | NO | PRI | NULL | |
+-------+------------------+------+-----+---------+-------+
Great! This is beginning to look really straightforward!
But before celebrating, let's have a look at the Drupal 7 System.
Hmmm...there's only one table!
uc_addresses
What's the table schema look like?
MariaDB [hph]> show columns from uc_addresses;
+------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------------+------+-----+---------+----------------+
| aid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| uid | int(10) unsigned | NO | | 0 | |
| first_name | varchar(255) | NO | | | |
| last_name | varchar(255) | NO | | | |
| phone | varchar(255) | NO | | | |
| company | varchar(255) | NO | | | |
| street1 | varchar(255) | NO | | | |
| street2 | varchar(255) | NO | | | |
| city | varchar(255) | NO | | | |
| zone | mediumint(9) | NO | | 0 | |
| postal_code | varchar(255) | NO | | | |
| country | mediumint(8) unsigned | NO | | 0 | |
| address_name | varchar(20) | YES | | NULL | |
| default_shipping | tinyint(4) | NO | | NULL | |
| default_billing | tinyint(4) | NO | | NULL | |
| created | int(11) | NO | | 0 | |
| modified | int(11) | NO | | 0 | |
+------------------+-----------------------+------+-----+---------+----------------+
Worse yet, there's no uc_addreses_defaults table in the Drupal 7 System!
Maybe there's a module installed in the Drupal 6 System that isn't present in the Drupal 7 System that has something to do with "Multiple", "Preferred" or "Default" addresses....Nope.
So what the Ubercart people probably did was to collapse the functionality of those two tables into one, probably because it turned out that people often have multiple billing and shipping addresses, and sometimes they want to use the default billing address and/or default shipping address, and sometimes they don't.
So what the Drupal 7 people chose to do was to use a tinyint(4) flag instead to "tag" the record type with a 0 or a 1 to indicate if it is the default_billing or default_shipping (or both). This imposes a little bit of overhead on each address record, but it also saves a disk access because everthing is captured in one place.
Database designers can go either way on these design decisions, in Drupal 6 they decided to go 3NF, in Drupal 7 they decided to go 2NF. Both have their pros and cons. This only becomes a drag when you move from one design strategy to another, like they did - because it makes moving data complicated.
Here's a side-by-side comparison of the schemas of the two tables:
Yes, the only difference between the two tables is the default_shipping and default_billing flags.
Plan of Action:
OK, a plan of action is starting to emerge. Just moving the data over wholesale like we did when we moved the users is a non-starter, because we have since purged a few hundred users from our Drupal 6 System database, and their addresses might be bloating the uc_addresses table(s).
Let's get some numbers to check on that concern:
mysql> select count(*) from uc_addresses;
+----------+
| count(*) |
+----------+
| 1338 |
+----------+
OK, so it looks like we have 1338 addresses...but how many unique users do we have?
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
| 885 |
+----------+
So, let's do a quick look for "orphan" address records, which would happen when the uid in the uc_addresses table does not exist as a uid in the users table;
mysql> select distinct(uid) from users order by uid;
<output removed for brevity>
885 rows in set (0.00 sec)
mysql> select distinct(uid) from uc_addresses order by uid;
<output removed for brevity>882 rows in set (0.00 sec)
Alright, we have 885 unique entries in the users table, including user 0 (Anonymous) and user 1 (Administrator). Looks like we have a user that has NO address. Let's go find them!
mysql> select distinct(uid) from users order by uid into outfile '~/users-uid.txt';
mysql> select distinct(uid) from uc_addresses order by uid into outfile '~/addresses-uid.txt';
Then we downloaded the resulting files (user-uid.txt and address-uid.txt) to our local system and then imported them into MS-EXCEL. Once they were in MS-EXCEL, we did a little programming to come up with the following result:
The first two are internal Drupal accounts:
User 0 (Anonymous) shouldn't have an entry in the uc_addresses table, and it doesn't. Good.
User 1 (Administrator) could have an entry in the uc_addresses table, and it does. Good
The final two rows are external, customer accounts, and they should have an entry in the uc_addresses table. The fact that they didn't is well worth looking into, because that may mean that thay registered, but never bought anything from us:
User 2340
User 2357
As it turns out, the following users have always ordered by telephone and never online, so their address was never entered into the Drupal 6 System database. But it they were in our accounting system, so we just entered their address data in for them.
Once that was done, every non-internal account had an address associated with it.
Now that we have balanced tables, things are getting easier and easier.
OK, now every user has at least one address entered into the system, but some users also have multiple addresses entered into the sytem, so we are going to need to figure out which address is their preferred (or default) Shipping and Billing address.
This is where the uc_default_addresses table now comes into play.
Looking into the table, it turned out that every uid that should have had a default address did, even the Administrator user.
So things are looking fairly simple now. Here's the plan:
1) On System A, connect to the Drupal 6 System database
2) Read the uc_addresses table, row by row
3) Check each row to see if it appears in the uc_addresses_defaults table
4) If it does appear in the uc_addresses_defaults table, flag it as the default Billing and Shipping address
5) If it does not appear in the uc_addresses_defaults table, enter it as an unflagged, alternative address
6) Once finished, move the data file physically from System A to System B
7) On System B, connect to the Drupal 7 System database
8) Read the data file directly into the Drupal 7 System database
Here's a sample from Export-Drupal-6-Ubercart-Addresses.php, the file I wrote to do that:
In the end, Export-Drupal-6-Ubercart-Addresses.php functioned quite well.
- I was able to preserve the maximum amount of address data from the Drupal 6 Ubercart uc_addresses and uc_addresse_defaults tables.
- Any missing data was generated and included so the format of the output file conformed to the requirements of the Drupal 7 System database.
- The data was output to a file in a format that was acceptable to the the Drupal 7 System database engine.
- Neither system was disrupted as the new data was introduced.
