Integer to GUID conversion notes for Inspector Blair project.<\/p>\n\n\n\n
The grouping feature offline caching requires GUID values in place of integer ID values. I had preferred to avoid this conversion for the following reasons:<\/p>\n\n\n\n
\n<option value = "BC072ADE-ACBF-42B0-8FCA-5587E0FE95BC">Inspector \u2013 Biosafety<\/option>\n<option value="1">Inspector \u2013 Biosafety<\/option>\n<\/pre><\/div>\n\n\nUltimately, I elected to migrate to GUID due to Inspector Blair’s unique workflow. Records may be created independently by multiple users, sometimes offline, and later synchronized. Although GUIDs add storage, payload, and indexing overhead compared with integers, the project\u2019s relatively low data volume makes that tradeoff acceptable and avoids a more complex dual-identifier synchronization model.<\/p>\n\n\n\n
Switching to GUID values shouldn’t cause a real-world noticeable performance issue. I had still hoped to keep integer IDs for maximum speed, but GUIDs are required in order to support grouping and integrate that grouping with lists of individuals.<\/p>\n\n\n\n
The account records were converted using the process below. The same general process may also be applied to inspections, system logs, autoclaves, and buildings.<\/p>\n\n\n\n
Note to any readers – this conversion process is on a development environment with dummy data and so I could get away with a lot of shortcuts. Real-world migration necessitates at minimum backups, transactions, redundant validations, retention of previous keys, and a robust restoration plan.<\/p>\n\n\n\n
Table Conversion<\/h2>\n\n\n\n
For the primary table:<\/p>\n\n\n\n
ID_OLD<\/code>.<\/li>\n\n\n\n- Create a new
uniqueidentifier<\/code> field named ID<\/code>.<\/li>\n\n\n\n- Set
RowGuid<\/code> to True<\/code>.<\/li>\n\n\n\n- Set the default binding to
NEWSEQUENTIALID<\/code>()<\/code>.<\/li>\n<\/ul>\n\n\n\nFor tables that are not part of a relationship:<\/p>\n\n\n\n
\n- Set the new
ID<\/code> field as the primary key.<\/li>\n<\/ul>\n\n\n\nRelationship Conversion<\/h2>\n\n\n\n
For tables that participate in parent-child relationships:<\/p>\n\n\n\n
\n- Rename the integer foreign key field in the child table from
FK_ID<\/code> to FK_ID_OLD<\/code>.<\/li>\n\n\n\n- Create a new
uniqueidentifier<\/code> field in the child table named FK_ID<\/code>.<\/li>\n<\/ul>\n\n\n\nPopulate the new child foreign key from the parent GUID:<\/p>\n\n\n
\nUPDATE child\nSET child.FK_ID = parent.ID\nFROM child\nINNER JOIN parent\n ON child.FK_ID_OLD = parent.ID_OLD;\n<\/pre><\/div>\n\n\nRun a simple verify.<\/p>\n\n\n
\nSELECT COUNT(*) AS MissingParentReferences\nFROM child\nWHERE FK_ID IS NULL\n AND FK_ID_OLD IS NOT NULL;\n<\/pre><\/div>\n\n\nThen update the relationship structure:<\/p>\n\n\n\n
\n- Delete the old
ID_OLD<\/code> to FK_ID_OLD<\/code> relationship between the parent and child tables.<\/li>\n\n\n\n- Set the new
ID<\/code> field in the parent table as the primary key.<\/li>\n\n\n\n- Set the new
FK_ID<\/code> field in the child table as the foreign key.<\/li>\n\n\n\n- Create the new one-to-many relationship:\n
\n- Primary key table:
ID<\/code><\/li>\n\n\n\n- Foreign key table:
FK_ID<\/code><\/li>\n\n\n\n- Insert and update update rule:
Cascade<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\nStored Procedure and XML Updates<\/h2>\n\n\n\n
Update all stored procedures and XML handling that reference the affected IDs:<\/p>\n\n\n\n
\n- Retype all
@id<\/code> variables in stored procedures from int<\/code> to uniqueidentifier<\/code>.<\/li>\n\n\n\n- Retype all
int<\/code> values in XML variables to uniqueidentifier<\/code>.<\/li>\n\n\n\n- For selected-item list tables, such as account roles, convert the relevant list fields to GUID as well.<\/li>\n\n\n\n
- No detailed notes are available for selected-item list tables, but the same parent-child update pattern should apply.<\/li>\n<\/ul>\n\n\n\n
Detail Display Page Updates<\/h2>\n\n\n\n
For detail display pages, as opposed to data list pages, make the following stored procedure updates.<\/p>\n\n\n\n
Use the dbo.account<\/code> stored procedure as the example.<\/p>\n\n\n\n\n- Replace the primary table variable, such as
@tempMain<\/code>, with a temp table, such as #primary_cache<\/code>.<\/li>\n\n\n\n- This does not change functionality, but should improve performance.<\/li>\n\n\n\n
- Navigation queries can no longer use output variables.<\/li>\n\n\n\n
- Output all relevant navigation data as the first record set instead.<\/li>\n\n\n\n
- When no ID is provided, run a
SELECT TOP 1<\/code> query to pre-populate the detail view.<\/li>\n<\/ul>\n\n\n\nPHP Display Page Updates<\/h2>\n\n\n\n
Use account.php<\/code> as the example.<\/p>\n\n\n\n\n- Remove all navigation variables from the stored procedure call.<\/li>\n\n\n\n
- Remove the associated PHP navigation variables.<\/li>\n\n\n\n
- Read the first record set and place the returned values directly into the navigation object.<\/li>\n\n\n\n
- Immediately after reading the navigation record set, add:<\/li>\n<\/ul>\n\n\n
\n$query->get_next_result();\n<\/pre><\/div>\n\n\n
\n- Use the next result set for the main data record. <\/li>\n\n\n\n
- If the detail view contains sub-data, remove array verification from the sub-table update code. <\/li>\n\n\n\n
- Add array verification to the
xml()<\/code> method in the relevant data class file. <\/li>\n\n\n\n- Add the script URL reference for the
dc_guid<\/code> function. <\/li>\n\n\n\n- In all insert functions, create a single string variable populated by
dc_guid<\/code>. Replace all $temp_int<\/code> references with the new GUID variable. Remove global $temp_int<\/code>.<\/li>\n<\/ul>\n\n\n\nTesting Checklist<\/h2>\n\n\n\n
Test the updated detail page with the following actions:<\/p>\n\n\n\n
\n- Create a new record.<\/li>\n\n\n\n
- Update the record.<\/li>\n\n\n\n
- Create a sub-record.<\/li>\n\n\n\n
- Update the sub-record.<\/li>\n\n\n\n
- Delete the sub-record.<\/li>\n\n\n\n
- Delete the main record.<\/li>\n\n\n\n
- Test navigation.<\/li>\n<\/ul>\n\n\n\n
List Page Verification<\/h2>\n\n\n\n
Verify that list pages still point to the correct detail page entries after the conversion.<\/p>\n\n\n\n
Delete Flags<\/h2>\n\n\n\n
Post migration and verify, flush all records with delete flags marked. <\/p>\n\n\n\n
Final Steps<\/h2>\n\n\n\n
This conversion is the first stage of a two-stage GUID migration. During the initial conversion, GUIDs are generated by the database using NEWSEQUENTIALID()<\/code> so the schema, relationships, stored procedures, XML handling, and PHP pages can be migrated safely from integer IDs to GUIDs. After the conversion is stable, the second stage will shift new record creation toward client-side GUID generation. This will allow offline-created records to receive permanent identifiers before synchronization. Database-generated GUIDs will remain available as standby functionality for server-created records, fallback workflows, and compatibility during the transition.<\/p>\n","protected":false},"excerpt":{"rendered":"Guid conversion notes for Inspector Blair project.<\/p>\n","protected":false},"author":1,"featured_media":6216,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":true,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[71],"tags":[286],"class_list":["post-5961","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology-temerity","tag-uk-ehs"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.caskeys.com\/dc\/wp-content\/uploads\/2016\/06\/logo-php.png","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5lNM5-1y9","jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/5961","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/comments?post=5961"}],"version-history":[{"count":19,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/5961\/revisions"}],"predecessor-version":[{"id":7984,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/posts\/5961\/revisions\/7984"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/media\/6216"}],"wp:attachment":[{"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/media?parent=5961"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/categories?post=5961"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.caskeys.com\/dc\/wp-json\/wp\/v2\/tags?post=5961"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}