Integer to GUID conversion notes for Inspector Blair project.

Grouping feature requires GUID in place of Integer values for IDs. I would prefer not to perform this conversion for the following reasons:

• It represents a lot of work for absolutely no visual progress to internal customers.
• GUID IDs are X*16 per record compared to integer values. This obvious exponential increase represents a larger load on the database server, but more importantly, means far more HTML code being sent for each page. The extra load is utterly negligible to our network and servers, but could impact mobile devices. <option value = “BC072ADE-ACBF-42B0-8FCA-5587E0FE95BC”>Inspector – Biosafety</option> <option value=”1”>Inspector – Biosafety</option>

While I don’t believe switching to GUID will impact performance to a noticeable degree, I had still hoped to use integers for absolute maximum speed. However, in order to add grouping and integrate it with lists of individuals, GUIDS are an absolute must. Following steps were taken to convert account records to GUID. These same steps may be applied to inspections, system log, autoclaves, and buildings.

1. Rename integer ID field to “ID_old”.
2. Create new Unique Identifier (Guid) field named “ID”.
   1. Set Rowguid = True
   2. Ensure default data binding is (newid())
3. If table is not part of a relationship, set “id” to primary key.
4. If table is part of any relationship perform the following:
   1. Rename Integer “FK_ID” to “FK_ID_OLD” in child table.
   2. Create new Unique Identifier (Guid) field named “FK_ID” in child table.
      1. Set Rowguid = True
   3. Run following query:

      UPDATE _sub SET _sub.fk_id = _main.id FROM parent _main, child _sub WHERE _sub.fk_id.old = _main.id_old

   4. Delete one (ID_OLD) to many (FK_ID_OLD) relationship of between parent and child table.
   5. Set “ID” field in parent and children tables to primary key.
   6. Create new one (ID) to many (FK_ID) relationship between parent and child table.
      1. Primary key table: ID, Foreign key table: FK_ID
      2. INSERT AND UPDATE, Delete Rule: Cascade
      3. INSERT AND UPDATE, Update Rule: Cascade
5. Retype all @id variables in stored procedures from “int” to “uniqueidentifier”.
6. Retype all INT values in xml variables to “uniqueidentifier”.
7. If table is a list for selected items, (ex. Account roles), the relevant field must be converted to GUID as well. No notes available, use query similar to parent and child.
8. For detail display (opposed to data list pages), perform the following additional steps.
   1. Modify the detail output stored procedure as follows. See “dbo.account” stored procedure for example.
      1. Replace primary table variable (<@tempMain>) with temp table (<#primary_cache>). This does not change functionality but will increase performance.
      2. Navigation queries can no longer use output variables. Instead, all relevant output data for navigation should now be output as the first record set.
      3. If the ID is not provided, run a SELECT TOP 1 query to pre populate.
   2. Modify display page as follows. See “account.php” for example.
      1. Remove all navigation variables from stored procedure call and the associated navigation variables.
      2. Insert recordset read, and place resulting values directly into navigation object. Navigation is the first record set.
         1. Make sure to add “$query->get_next_result();” for main data recordset read immediately below.
      3. If detail view contains sub data, remove array verification in sub table update code.
      4. Add array verification in the xml() method of the relevant data’s class file.
      5. Add <script> url reference to “dc_guid” function.
      6. In all insert functions, add a single string variable populated from “dc_guid” function. Replace all $temp_int references with this new variable.\
      7. Remove global $temp_int.
   3. Test detail page.
      1. Create new record.
      2. Update record.
         1. Create sub record.
         2. Update sub record.
         3. Delete sub record.
      3. Delete record.
      4. Navigation
9. Verify list pages point to correct entry in detail page.