Overview
We need to find a solution to get our data from DNPC into Google App Engine. This will require us to switch over to a storage mechanism using salted hash values to perform data lookups. Export the database deltas and have them run in google app engine.
The current create script can be found here.
Requirements
- We will need table objects in the DNPC environment.
- We will need to split the data export into segments. IE: before cut over (read only data), and cutover data.
- Pre cut over data will be things like the audit table as it contains records that won't be changing very often.
post cut over data will be things that are volatile and will need to be re written once the cut over is complete.
- axis versioned storage should be replaced with a shifting IV in the GAE code base.
- Create tables that we will insert data into.
- Create an API to export data into GAE. The exported items will be insert statements to be run on tables already created.
- Inserts should perform an upsert if the same row is detected but the data is different.
Timeline
- Pre cutover SQL scripts are generated and run in GAE.
- The cutover blackout window starts
- Post cutover items are generated and run in GAE
- An order of importance should be set for moderation of names/ugc to happen last as it's easy to regulate.
- application is tested and black out window is ended.
Questions
- Will we be able to use JPA message converters to perform the encryption and decryption process.
- Likely not because of the need to update the hash data. This will likely need to be a custom solution otherwise we will need to know about what the entity type is in the message converters.
Notes
Encrypted Column lengths.
| unencrypted | encrypted length |
|---|---|
| 38 | 95 |
| 100 | 183 |
| 64 | 139 |
| 5000 | 6711 |
| 15 | 55 |
| 1000 | 1375 |
| 254 | 375 |
| 255 | 375 |
| 32 | 95 |
| 128 | 223 |
| 64 | 139 |
Hashing Length: 67 → increasing to 70 in case of long version value.
Configurations
Permissions
- migration.data.export
- migration.data.import
Configuration Properties
- hash.database.v1.version = v1
- hash.database.v1.startSalt = CHANGEME
- hash.database.v1.endSalt = CHANGEME
- hash.database.v1.method = SHA-256
- hash.database.v1.encoder = HEX
Instructions
- run the create table script.
- run each table export call which consists of an auto incremental update followed by the data insertion script.
Table PII and Sensitive Information Audit
| Database | Table | PII | Deprecated | Fields | Size (mb) As of 12th July 2017 | Migration Instructions | Notes | Auto Increment (Sept 19, 2017) | Auto Increment (Sept 22) |
|---|---|---|---|---|---|---|---|---|---|
| support_centre | account_note | yes | no | swid, note | 445.38 | precutover, postcutover | 3338227 | 3338226 | |
| support_centre | no | yes | None | ||||||
| support_centre | yes | yes | client_id, client_token | 0.03 | precutover | b2b http basic auth | |||
| support_centre | no | yes | 0.03 | precutover | |||||
| support_centre | integration_dynamicmodule_endpoint | no | no | precutover | 0 | ||||
| support_centre | integration_dynamicmodule_space | no | no | precutover | 0 | ||||
| support_centre | yes | yes | note | 0.02 | |||||
| support_centre | yes | yes | swid | 0.02 | |||||
| support_centre | yes | yes | raw_msg, filtered_msg | 5498.33 | |||||
| support_centre | no | yes | 0.05 | ||||||
| support_centre | yes | yes | swid | 279.91 | |||||
| support_centre | yes | yes | swid | 114.91 | |||||
| support_centre | no | yes | 271.91 | ||||||
| support_centre | name_approval_stats | no | no | 405.09 | precutover, postcutover | 20636450 | |||
| support_centre | name_approvals | yes | no | name, account_id, secondary_id | 172.73 | precutover, postcutover | 337340829 | ||
| support_centre | name_checkout | no | no | 0.05 | precutover, postcutover | null | |||
| support_centre | premoderated_names | no | no | 43.13 | precutover, postcutover | 1473321 | |||
| support_centre | no | yes | 0.05 | ||||||
| support_centre | no | yes | 0.03 | ||||||
| support_centre | semaphores | no | no | 0.02 | precutover | null | |||
| support_centre | no | yes | 0.03 | ||||||
| support_centre | systems | no | no | 0.03 | precutover | 264 | |||
| support_centre | no | yes | 649.00 | ||||||
| support_centre | ugc_campaign | no | no | 0.03 | precutover | 325 | |||
| support_centre | ugc_campaign_details_map | no | no | 0.03 | precutover | 0 | |||
| support_centre | ugc_campaign_flags | no | no | 0.08 | precutover | 1509 | |||
| support_centre | ugc_campaign_settings | no | no | 0.06 | precutover | 1547 | |||
| support_centre | ugc_campaign_systems | no | no | 0.03 | precutover | 715 | |||
| support_centre | ugc_moderation | yes | no | account_id, source,text,submitted_ip | 132.81 | precutover | 1156407 | ||
| support_centre | ugc_moderation_checkout | no | no | 0.05 | precutover | null | |||
| support_centre | ugc_moderation_flags | no | no | 0.08 | precutover | 42323 | 42322 | ||
| support_centre | yes | yes | account_id | ||||||
| support_centre | ugc_moderation_metadata | yes | no | value | 77.09 | precutover | 3307017 | 3313348 | |
| support_centre | ugc_moderation_settings | no | no | 0.11 | precutover | 175749 | 175616 | ||
| support_centre | ugc_moderation_stats | no | no | 51.11 | precutover | 1887567 | 1890544 | ||
| support_centre | ugc_moderation_submission_queue | no | no | 0.05 | precutover | 174955 | |||
| support_centre | whats_new | no | no | 1.53 | precutover | 539 | |||
| axis | permission_group_effective_permissions | no | no | precutover | |||||
| axis | permission_group_permissions | no | no | precutover | 1839 | ||||
| axis | permission_groups | no | no | precutover | 38 | ||||
| axis | permission_permissions | no | no | precutover | 313 | ||||
| gxtools | no | no | Axis 5x specific | ||||||
| gxtools | yes | yes | client_uuid, email | 0.03 | precutover | Cellophane specific | 12 | ||
| gxtools | no | yes | name | 0.05 | |||||
| gxtools | no | yes | 0.03 | ||||||
| gxtools | no | no | |||||||
| gxtools | product_languages | no | no | 0.02 | precutover | null | |||
| gxtools | product_settings | yes | no | value | 0.02 | precutover | No PII. Is this just because it is sensitive data? | null | |
| gxtools | product_site_settings | yes | no | product_site_setting_value | 0.03 | precutover | No PII. Is this just because it is sensitive data? | null | |
| gxtools | product_sites | no | no | 0.05 | precutover | null | |||
| gxtools | products | no | no | 0.05 | precutover | 71 | |||
| gxtools | no | yes | |||||||
| gxtools | no? | yes | 0.11 | ||||||
| gxtools | no | yes | 0.03 | ||||||
| gxtools | no | yes | view | ||||||
| gxtools | no | yes | 1.42 | ||||||
| gxtools | user_keystone_abilities | no | no | 4.5 | precutover | null | |||
| gxtools | no | yes | |||||||
| gxtools | user_preferences | no | no | 1.42 | precutover | null | |||
| gxtools | users | yes | no | username, display_name, firstname, lastname | 0.17 | precutover | email column to be removed | 6901 | |
| gxaudit | access | yes | no | external_key, ip | 38.68 | precutover, postcutover | 1171043 | 1256678 | |
| gxaudit | action | yes | no | external_key, old_value, new_value, comment | 6577.0 | precutover, postcutover | 150551593 | 150674396 | |
| gxaudit | no | no | reason | 0.0 | precutover, postcutover | unused, zero records | |||
| gxaudit | action_source | no | no | 0.0 | precutover | 28 | 28 | ||
| gxaudit | action_type | no | no | 2.69 | precutover, postcutover | 10874 | 10888 | ||
| gxaudit | no | yes | view |