Analyze Existing Data For Migration to New Data Model

[kiiskila-bcgov]

We have data related to form building in the database but it is structured around the data model. In the implementation of the new data model I have not be careful to preserve the existing data since it does not match the data model.

We would like to do 3 things:

1. Analyze and document the data that exists in the current database relating to form building. Identify what is worth keeping and what does not make sense with the new model and can be disregarded.
2. For the data that is worth keeping, find out how it will need to be converted to fit the new data model. Not necessarily migration files or code but just high level data transformation.
3. Export the data that is worth keeping in a way that we can use it to be imported later in the new data model.

I will provide a database dump so you can work locally with the data locally.

[kiiskila-bcgov]

Here is the data model we are working off of:

erDiagram
    %% Main entities with vertical layout
    Forms ||--o{ FormVersions : "has many"
    %% FormVersions relationships
    FormVersions ||--o{ FormElements : "has many"
    FormVersions ||--o{ FormStylesheets : "applied to"
    FormVersions ||--o{ FormScripts : "has many"
    FormStylesheets ||--o{ FormVersions : "applied to"
    FormScripts ||--o{ FormVersions : "applied to"
    FormVersions ||--o{ FormDeployments : "has many"
    %% FormElement (using labels to indicate inheritance)
    FormElements ||--|o FormFieldDataBindings : "has one"
    FormElements ||--|o FormElements : "has one parent"
    %% Models for FormElements Types
    TextInputFormElements ||--o{ FormElements : "has one"
    CheckboxInputFormElements ||--o{ FormElements : "has one"
    SelectInputFormElements ||--o{ FormElements : "has one"
    RadioInputFormElements ||--o{ FormElements : "has one"
    TextareaInputFormElements ||--o{ FormElements : "has one"
    NumberInputFormElements ||--o{ FormElements : "has one"
    DateSelectInputFormElements ||--o{ FormElements : "has one"
    ButtonInputFormElements ||--o{ FormElements : "has one"
    ContainerFormElements ||--o{ FormElements : "has one"
    HTMLFormElements ||--o{ FormElements : "has one"
    %% Sub-FormElements
    SelectInputFormElements ||--o{ SelectOptionFormElements : "has many"
    %% Form Field Validators
    TextInputFormElements ||--o{ FormFieldValidators : "has many"
    CheckboxInputFormElements ||--o{ FormFieldValidators : "has many"
    NumberInputFormElements ||--o{ FormFieldValidators : "has many"
    TextareaInputFormElements ||--o{ FormFieldValidators : "has many"
    SelectInputFormElements ||--o{ FormFieldValidators : "has many"
    RadioInputFormElements ||--o{ FormFieldValidators : "has many"
    DateSelectInputFormElements ||--o{ FormFieldValidators : "has many"
    
    %% Entity definitions
    Forms {
        int id PK
        int forms_id
        int parent_form_elements_id FK
        string form_title
        int ministry_id FK
        string description
        string notes
        int data_bindings_id FK
        int form_frequencies_id FK
        int form_reaches_id FK
        string print_reason
        string retention_needs
        boolean icm_non_interactive
        string workbench_footer_fragment_path
        string dcv_material_number
        string orbeon_functions
        boolean icm_generated
    }
    FormVersions {
        int id PK
        int form_id FK
        string uuid 
        int version_number
        string status "draft|approved|final"
        int form_developer_id FK
    }
    FormDeployments {
        int id PK
        int form_version_id FK
        string environment "test|dev|prod"
        datetime deployed_at
    }
    FormFieldDataBindings {
        int id PK
	string name
	string description
        string data_sources_id FK
        string data_binding_path
        string data_binding_type
    }
    FormStylesheets {
        int id PK
        int form_version_id FK
        string filepath 
        string name
        string type "web|pdf"
        string description
    }
    FormScripts { 
        int id PK
        int form_version_id FK
        string filepath 
        string name
        string type "web|pdf"
        string description
    }
    FormFieldValidators { 
        int id PK  
        string name
        string regex
        string description
    }
    FormElements {
        int id PK
        uuid token
        string name
        int form_versions_id FK
        int parent_id FK "Nullable FK to FormElements.id (for hierarchical structure)"
        int elementable_id FK "ID of element type"
        string elementable_type "Class name of element type (e.g., 'ContainerFormElements')"
        string help_text
        string description
        boolean is_repeatable
        string repeater_item_label
        boolean is_resetable
        boolean visible_web
        boolean visible_pdf
        boolean is_template
    }
    TextInputFormElements {
        int id PK
        string placeholder_text
        string label
        boolean visible_label
        string mask
        int maxlength
        int minlength
    }
    CheckboxInputFormElements {
        int id PK
        string label
        boolean visible_label
    }
    SelectInputFormElements {
        int id PK
        string label
        boolean visible_label
    }
    SelectOptionFormElements {
        int id PK
        int form_element_select_inputs_id FK
        string label
        int order
        string description
    }
    RadioInputFormElements {
        int id PK
        string label
        boolean visible_label
        string[] options
        string default_value
    }

    TextareaInputFormElements {
        int id PK
        string name
        string placeholder_text
        string label
        boolean visible_label
        int rows
        int cols
        int maxlength
        int minlength
    }

    NumberInputFormElements {
        int id PK
        string placeholder_text
        string label
        boolean visible_label
        int min
        int max
        int step
        int default_value
    }
    DateSelectInputFormElements {
        int id PK
        string placeholder_text
        string label
        boolean visible_label
        string repeater_item_label
        date min_date
        date max_date
        date default_date
        string date_format
        boolean include_time
    }
    ButtonInputFormElements {
        int id PK
        string label
        string button_type "submit|reset|button"
    }
    ContainerFormElements {
        int id PK
        string container_type "page|fieldset|section"
        boolean collapsible
        boolean collapsed_by_default
    }
    HTMLFormElements {
        int id PK
        string name
        string html_content "raw HTML to embed"
        string repeater_item_label
    }
    
    %% Relationships

    %% Self-referencing relationship for parent/child
    FormElements o|--o{ FormElements : "can be parent/child of"

    %% FormElements morphs to one of the specific types (Laravel Eloquent)
    %% We represent it by showing the FK/type columns and multiple optional relationships.
    FormElements ||--o| ContainerFormElements : "morphs to (if Container)"
    FormElements ||--o| TextareaInputFormElements : "morphs to (if Textarea)"
    FormElements ||--o| NumberInputFormElements : "morphs to (if Number)"
    FormElements ||--o| DateSelectInputFormElements : "morphs to (if DateSelect)"
    FormElements ||--o| ButtonInputFormElements : "morphs to (if Button)"
    FormElements ||--o| TextInputFormElements : "morphs to (if Text)"
    FormElements ||--o| HTMLFormElements : "morphs to (if HTML)"

Loading

[kiiskila-bcgov]

Just doing some refinement based on your feedback @JohYoshidaBCGov . I tweaked a few columns here, let me know what you think.

erDiagram
    %% Main entities with vertical layout
    Forms ||--o{ FormVersions : "has many"
    %% FormVersions relationships
    FormVersions ||--o{ FormElements : "has many"
    FormVersions ||--o{ FormStylesheets : "applied to"
    FormVersions ||--o{ FormScripts : "has many"
    FormStylesheets ||--o{ FormVersions : "applied to"
    FormScripts ||--o{ FormVersions : "applied to"
    FormVersions ||--o{ FormDeployments : "has many"
    %% FormElement (using labels to indicate inheritance)
    FormElements ||--|o FormFieldDataBindings : "has one"
    FormElements ||--|o FormElements : "has one parent"
    %% Models for FormElements Types
    TextInputFormElements ||--o{ FormElements : "has one"
    CheckboxInputFormElements ||--o{ FormElements : "has one"
    SelectInputFormElements ||--o{ FormElements : "has one"
    RadioInputFormElements ||--o{ FormElements : "has one"
    TextareaInputFormElements ||--o{ FormElements : "has one"
    NumberInputFormElements ||--o{ FormElements : "has one"
    DateSelectInputFormElements ||--o{ FormElements : "has one"
    ButtonInputFormElements ||--o{ FormElements : "has one"
    ContainerFormElements ||--o{ FormElements : "has one"
    HTMLFormElements ||--o{ FormElements : "has one"
    TextInfoFormElements ||--o{ FormElements : "has one"
    %% Sub-FormElements
    SelectInputFormElements ||--o{ SelectOptionFormElements : "has many"
    RadioInputFormElements ||--o{ SelectOptionFormElements : "has many"
    %% Form Field Validators
    TextInputFormElements ||--o{ FormFieldValidators : "has many"
    CheckboxInputFormElements ||--o{ FormFieldValidators : "has many"
    NumberInputFormElements ||--o{ FormFieldValidators : "has many"
    TextareaInputFormElements ||--o{ FormFieldValidators : "has many"
    SelectInputFormElements ||--o{ FormFieldValidators : "has many"
    RadioInputFormElements ||--o{ FormFieldValidators : "has many"
    DateSelectInputFormElements ||--o{ FormFieldValidators : "has many"
    
    %% Entity definitions
    Forms {
        int id PK
        int forms_id
        int parent_form_elements_id FK
        string form_title
        int ministry_id FK
        string description
        string notes
        int data_bindings_id FK
        int form_frequencies_id FK
        int form_reaches_id FK
        string print_reason
        string retention_needs
        boolean icm_non_interactive
        string workbench_footer_fragment_path
        string dcv_material_number
        string orbeon_functions
        boolean icm_generated
    }
    FormVersions {
        int id PK
        int form_id FK
        string uuid 
        int version_number
        string status "draft|approved|final"
        int form_developer_id FK
    }
    FormDeployments {
        int id PK
        int form_version_id FK
        string environment "test|dev|prod"
        datetime deployed_at
    }
    FormFieldDataBindings {
        int id PK
	string name
	string description
        string data_sources_id FK
        string data_binding_path
        string data_binding_type
    }
    FormStylesheets {
        int id PK
        int form_version_id FK
        string filepath 
        string name
        string type "web|pdf"
        string description
    }
    FormScripts { 
        int id PK
        int form_version_id FK
        string filepath 
        string name
        string type "web|pdf"
        string description
    }
    FormFieldValidators { 
        int id PK  
        string name
        string regex
        string description
    }
    FormElements {
        int id PK
        uuid token
        string name
        int form_versions_id FK
        int parent_id FK "Nullable FK to FormElements.id (for hierarchical structure)"
        int elementable_id FK "ID of element type"
        string elementable_type "Class name of element type (e.g., 'ContainerFormElements')"
        string help_text
        string description
        int order
        string calculated_value
        boolean is_visible 
        boolean is_read_only
        boolean save_on_submit
        boolean visible_web
        boolean visible_pdf
    }
    TextInputFormElements {
        int id PK
        string placeholder_text
        string label
        boolean visible_label
        string mask
        int maxlength
        int minlength
    }
    CheckboxInputFormElements {
        int id PK
        string label
        boolean visible_label
    }
    SelectInputFormElements {
        int id PK
        string label
        boolean visible_label
    }
    SelectOptionFormElements {
        int id PK
        int form_element_select_inputs_id FK
        string label
        int order
        string description
    }
    RadioInputFormElements {
        int id PK
        string label
        boolean visible_label
        string default_value
    }

    TextareaInputFormElements {
        int id PK
        string name
        string placeholder_text
        string label
        boolean visible_label
        int rows
        int cols
        int maxlength
        int minlength
    }

    NumberInputFormElements {
        int id PK
        string placeholder_text
        string label
        boolean visible_label
        int min
        int max
        int step
        int default_value
    }
    DateSelectInputFormElements {
        int id PK
        string placeholder_text
        string label
        boolean visible_label
        string repeater_item_label
        date min_date
        date max_date
        date default_date
        string date_format
        boolean include_time
    }
    ButtonInputFormElements {
        int id PK
        string label
        string button_type "submit|reset|button"
    }
    ContainerFormElements {
        int id PK
        string container_type "page|fieldset|section"
        boolean collapsible
        boolean collapsed_by_default
        boolean is_repeatable
        string legend
    }
    HTMLFormElements {
        int id PK
        string name
        string html_content "raw HTML to embed"
        string repeater_item_label
    }
    TextInfoFormElements {
        int id PK
        string content
    }
    
    %% Relationships

    %% Self-referencing relationship for parent/child
    FormElements o|--o{ FormElements : "can be parent/child of"

    %% FormElements morphs to one of the specific types (Laravel Eloquent)
    %% We represent it by showing the FK/type columns and multiple optional relationships.
    FormElements ||--o| ContainerFormElements : "morphs to (if Container)"
    FormElements ||--o| TextareaInputFormElements : "morphs to (if Textarea)"
    FormElements ||--o| NumberInputFormElements : "morphs to (if Number)"
    FormElements ||--o| DateSelectInputFormElements : "morphs to (if DateSelect)"
    FormElements ||--o| ButtonInputFormElements : "morphs to (if Button)"
    FormElements ||--o| TextInputFormElements : "morphs to (if Text)"
    FormElements ||--o| HTMLFormElements : "morphs to (if HTML)"
    FormElements ||--o| TextInfoFormElements : "morphs to (if TextInfo)"

Loading

[spencercrose]

Copying changes noted by @kiiskila-bcgov :

• is_repeatable moved to the ContainerFormElements
• legend property added to ContainerFormElements since it is how the fieldset uses the title
• TextInfoFormElements added, maybe rename to DisplayTextFormElements? Just a way for static text
• order added to elements
• is_template removed from FormElements, we are thinking of a different way to reuse or categorize the field. I still vote for using tags which Joh is in favour of as well. But I haven't looked at an implementation for that vs alternatives
• Instead of a conditionals table, I added the list of conditional properties to the FormElements or specific element. So things like calculated_value, is_visible, is_read_only, save_on_submit
• And RadioFormElements uses that same list of SelectOptionFormElements. Just letting us reuse pieces here and also save some of the select options we previously created when transferring existing data

[JohYoshidaBCGov]

Can we rename SelectOptionFormElements to SelectableValueFormElements? And SelectInputFormElements to DropdownInputFormElements?

[JohYoshidaBCGov]

Just noticed that is_visible, is_read_only, save_on_submit need to be strings - like calculated_value they receive JavaScript

Here's my analysis:

Tables

Table	Keep?	Rationale	Conversion Notes
containers	No	Only used on test forms
data_types	Temporarily	Each data type is becoming its own type of FormElement	Preserve until after form_instance_field conversion
field_group_form_field	No	No more FieldGroup templates
field_group_instances	Yes	Convert to ContainerFormElement	See table
field_group_style_pdf	No
field_group_style_web	No
field_groups	No	No more templates
form_field_date_formats	No	Empty, otherwise could go to DateSelectInputFormElement.date_format
form_field_style_pdf	No	Too difficult to associate to correct FormElement.StyleSheet
form_field_style_web	No	Too difficult to associate to correct FormElement.StyleSheet
form_field_validations	No	No real data
form_field_values	No	No more FormField templates
form_fields	Temporarily	No more FormField templates, but required for form_instance_fields conversion	Preserve until after form_instance_field conversion
form_instance_field_conditionals	Yes		FormElement can have columns for readOnly, visibility, saveOnSubmit, calculatedValue
form_instance_field_date_formats	No	Empty
form_instance_field_validations	No	Only the phone type entries belong to non-test forms, and they don't contain anything of value
form_instance_field_values	Temporarily	Convert to TextDisplayFormElements	See table
form_instance_fields	Yes	Convert to specific FormElement type based on form_field_id.data_type_id	See table
form_versions	Yes
forms	Yes
select_option_instances	Yes		See table
select_options	Temporarily	No more SelectOption template, but required for select_option_instances conversion	Preserve until after select_option_instance conversion
style_instances	No	Too difficult to consolidate many style_instances to a stylesheet while preserving parent element info, especially with change to UUIDs
styles	Temporarily	Worth holding onto to manually add to CF0700/CF0322 or a 'commonly used styles' stylesheet	Preserve until CF0700 and CF0322 styles are manually added

Field Mapping Details

Convert from FormInstanceFields to FormElements

Before conversion, select by FormInstanceField.form_field_id.data_type_id.name to determine which type of FormElement to create

From	To	Notes
id	id
order	order
form_version_id	form_version_id
form_field_id	Drop	Use to select type of FormElement to create
custom_label	label	Fallback: if null, use form_field.label
custom_data_binding	FormFieldDataBinding.data_binding_type	Fallback: form_field.data_binding
field_group_instance_id	parent_id
custom_data_binding_path	FormFieldDataBinding.data_binding_path	Fallback: form_field.data_binding_path
instance_id	Drop	Use token instead
custom_mask	Drop	Empty
custom_help_text	help_text
customize_label	Drop
custom_instance_id	Drop
container_id	parent_id	Ignore if field_group_instance_id exists

Convert from FieldGroupInstances to ContainerFormElements

From	To	Notes
form_version_id	form_version_id
field_group_id	Drop
custom_group_label	label	Fallback if null: field_group.group_label
repeater	is_repeatable
id	id
order	order
instance_id	Drop	Use token instead
custom_data_binding	FormFieldDataBinding.data_binding_type	Fallback if null: field_group.data_binding
custom_data_binding_path	FormFieldDataBinding.data_binding_path	Fallback if null: field_group.data_binding_path
visibility	Drop	Only used twice; converting to FormScript would be overcomplicated
custom_repeater_item_label	repeater_item_label
customize_group_label	Drop
custom_instance_id	Drop
container_id	parent_id
clear_button	is_resetable

Convert from SelectOptionInstances to SelectOptionFormElements

From	To	Notes
id	id
select_option_id	Drop
form_field_id	Drop
form_instance_field_id	form_element_select_input_id
order	order
SelectOption.label	label

Convert from FormInstanceFieldValues to TextInfoFormElements

From	To	Notes
id	id
form_instance_field_id	Drop
custom_value	content

[JohYoshidaBCGov]

Apparently for data bindings to work, FormVersions will need a data_binding_source and a data_binding_source_order