Migrate Determiners to the 'Determiner' table

Documentation Configuration & Installation
, ,
1

Important: These steps can and should only be performed by IT users. Remember to make a back up of your database before performing any actions in the command line. There is no simple way to reverse this action.

The Specify Schema 2.10 update introduces the new Determiner table to the database schema! This highly requested feature allows you to add multiple determiners to a single determination record.

For databases that already have determiner data in the determiner field in the Determination table, you must perform a manual migration to move this data into the new table.

Note: You will need to modify your data export mappings, reports and labels, forms, and any place where the determiner field is being currently referenced. References will not be updated, meaning that you will need to make these changes manually.

The following commands will move all determiners from the determination table into the determiner table for all records in the database:

  1. This begins the transaction in the database:
start transaction;
  1. This statement creates records in the Determiner table for every determiner that already exists in the Determination table and links it to the determination.
insert into determiner (timestampcreated, version, isprimary, ordernumber, createdbyagentid, determinationid, agentid) select now(), 0, 1, 0, createdbyagentid, determinationid, determinerid from determination where determinerid is not null;
  1. This statement sets the determinerid to be null now that the records exist in the Determiner table.
update determination set determinerid = null where determinerid is not null;
  1. This statement commits the changes to your database.
commit;

Here is an example output from the command line when running these in a database:

MariaDB [schema_update210]> start transaction;
Query OK, 0 rows affected (0.000 sec)

MariaDB [schema_update210]> insert into determiner (timestampcreated, version, isprimary, ordernumber, createdbyagentid, determinationid, agentid) select now(), 0, 1, 0, createdbyagentid, determinationid, determinerid from determination where determinerid is not null;
Query OK, 33406 rows affected (0.253 sec)
Records: 33406  Duplicates: 0  Warnings: 0

MariaDB [schema_update210]> update determination set determinerid = null where determinerid is not null;
Query OK, 33406 rows affected (0.269 sec)
Rows matched: 33406  Changed: 33406  Warnings: 0

MariaDB [schema_update210]> commit;
Query OK, 0 rows affected (0.595 sec)

If you have a Specify Cloud database, you can contact support@specifysoftware.org for assistance with this migration.