[CHORE] Database cleanup

[christopherpickering]

• [x] I've searched the issues
• [x] I've read the docs

Feature Request

Change from db first to code first so we can run migrations to update the database when deploying new versions.

Also, clean up db table names.

Potential Updates

ReportGroupsMemberships

• [ ] table ReportGroupsMemberships > ReportGroupMemberships
• [ ] col MembershipId > membership_id
• [ ] col GroupId > group (fk)
• [ ] col ReportId > report (fk)
• [ ] col LastLoadDate > etl_date

ReportObject

• [ ] table ReportObject > Reports
• [ ] col ReportObjectID > report_id
• [ ] col ReportObjectBizKey > report_key
• [ ] col ReportObjectTypeID > type
• [ ] col Name > name
• [ ] col DisplayTitle > title
• [ ] col Description > description
• [ ] col DetailedDescription > detailed_description
• [ ] col RepositoryDescription > system_description
• [ ] col SourceServer > system_server
• [ ] col SourceDB > system_db
• [ ] col SourceTable > system_table
• [ ] col AuthorUserID > created_by
• [ ] col LastModifiedByUserID > modified_by
• [ ] col LastModifiedDate > _modified_at
• [ ] col ReportObjectURL > url
• [ ] col EpicMasterFile > system_identifier
• [ ] col EpicRecordID > system_id
• [ ] col EpicReportTemplateId > system_template_id
• [ ] col ReportServerCatalogID > system_catalog_id
• [ ] col DefaultVisibilityYN > visible
• [ ] col OrphanedReportObjectYN > orphan
• [ ] col ReportServerPath > system_path
• [ ] col LastLoadDate > etl_date
• [ ] col CertificationTag > certification_tag

ReportObjectHierarchy

• [ ] table ReportObjectHierarchy > ReportHierarchies
• [ ] col ParentReportObjectID > parent
• [ ] col ChildReportObjectID > child
• [ ] col Line > rank
• [ ] col LastLoadDate > etl_date

ReportObjectQuery

• [ ] table ReportObjectQuery > ReportQueries
• [ ] col ReportObjectQueryId > query_id
• [ ] col ReportObjectId > report_id
• [ ] col Query > query
• [ ] col LastLoadDate > etl_date

ReportObjectRunData

• [ ] table ReportObjectRunData > ReportRuns
• [ ] col ReportObjectID > report_id
• [ ] col RunID > run_id
• [ ] col RunUserID > user
• [ ] col RunStartTime > start_time
• [ ] col RunDurationSeconds > duration_seconds
• [ ] col RunStatus > status
• [ ] col LastLoadDate > etl_date

ReportObjectSubscriptions

• [ ] table ReportObjectSubscriptions > ReportSubscriptions
• [ ] col ReportObjectSubscriptionsId > subscriptions_id
• [ ] col ReportObjectId > report
• [ ] col UserId > user_id
• [ ] col SubscriptionId > unique_id
• [ ] col InactiveFlags > inactive
• [ ] col EmailList > email_list
• [ ] col Description > description
• [ ] col LastStatus > status
• [ ] col LastRunTime > last_run
• [ ] col SubscriptionTo > email
• [ ] col LastLoadDate > etl_date

ReportObjectType

• [ ] table ReportObjectType > ReportTypes
• [ ] col ReportObjectTypeID > type_id
• [ ] col Name > name
• [ ] col ShortName > short_name
• [ ] col DefaultEpicMasterFile > code
• [ ] col LastLoadDate > etl_date

User

• [ ] table User > Users
• [ ] col UserID > user_id
• [ ] col Username > username
• [ ] col EmployeeID > employee_id
• [ ] col AccountName > account_name
• [ ] col DisplayName > display_name
• [ ] col FullName > _full_name
• [ ] col FirstName > _first_name
• [ ] col LastName > last_name
• [ ] col Department > department
• [ ] col Title > title
• [ ] col Phone > phone
• [ ] col Email > email
• [ ] col Base > base
• [ ] col EpicId > system_id
• [ ] col LastLoadDate > etl_date
• [ ] col LastLogin > last_login

Analytics

• [ ] table Analytics > Analytics
• [ ] col Id > analytics_id
• [x] col Username > username
• [x] col appCodeName > app_code_name
• [x] col appName > app_name
• [x] col appVersion > app_version
• [x] col cookieEnabled > cookie_enabled
• [x] col language
• [x] col oscpu
• [x] col platform
• [ ] col userAgent > useragent
• [x] col host
• [ ] col hostname
• [ ] col href
• [ ] col protocol
• [ ] col search
• [ ] col pathname
• [ ] col hash > unique_id
• [ ] col screenHeight > screen_height
• [ ] col screenWidth > screen_width
• [ ] col origin
• [x] col title
• [ ] col loadTime > load_time
• [ ] col accessDateTime > access_date
• [ ] col referrer
• [ ] col UserId > user
• [ ] col Zoom > zoom
• [ ] col Epic > epic
• [ ] col pageId > page_id
• [ ] col sessionId > session_id
• [ ] col pageTime > page_time
• [x] col sessionTime > session_time
• [ ] col updateTime > update_time

DP_Agreement

• [x] table DP_Agreement > drop table

DP_AgreementUsers

• [x] table DP_AgreementUsers > drop table

DP_Attachments

• [x] table DP_Attachments > drop table

DP_Contact

• [x] table DP_Contact > drop table

DP_Contact_Links

• [x] table DP_Contact_Links > drop table

DP_DataInitiative

• [x] table DP_DataInitiative > Initiatives
• [x] col DataInitiativeID> initiative_id
• [x] col Name> name
• [x] col Description> description
• [x] col OperationOwnerID> ops_owner
• [ ] col ExecutiveOwnerID> exec_owner
• [ ] col FinancialImpact> financial_impact
• [ ] col StrategicImportance> strategic_importance
• [ ] col LastUpdateDate> _modified_at
• [ ] col LastUpdateUser> modified_by

DP_DataProject

• [x] table DP_DataProject > Collections
• [x] col DataProjectID> collection_id
• [x] col DataInitiativeID> initiative
• [x] col Name> name
• [x] col Purpose> search_summary
• [x] col Description> description
• [ ] col LastUpdateDate> _modified_at
• [ ] col LastUpdateUser> modified_by
• [x] col Hidden> hidden

DP_MilestoneChecklist

• [x] table DP_MilestoneChecklist > drop table

DP_MilestoneFrequency

• [x] table DP_MilestoneFrequency > drop table

DP_MilestoneTasks

• [x] table DP_MilestoneTasks > drop table

DP_MilestoneTasksCompleted

• [x] table DP_MilestoneTasksCompleted > drop table

DP_MilestoneTemplates

• [x] table DP_MilestoneTemplates > drop table

DP_ReportAnnotation

• [x] table DP_ReportAnnotation > CollectionReport
• [x] col ReportAnnotationID> link_id
• [x] col ReportId> report
• [x] col DataProjectId> collection
• [x] col Rank> drop

DP_TermAnnotation

• [x] table DP_TermAnnotation > CollectionTerm
• [x] col TermAnnotationID> link_id
• [x] col TermId> term
• [x] col DataProjectId > collection
• [x] col Rank > rank

Dp_DataProjectConversation

• [x] table Dp_DataProjectConversation > drop table

Dp_DataProjectConversationMessage

• [x] table Dp_DataProjectConversationMessage > drop table

EstimatedRunFrequency

• [x] table EstimatedRunFrequency > RunFrequency
• [x] col EstimatedRunFrequencyID > frequency_id
• [x] col EstimatedRunFrequencyName > name

FinancialImpact

• [x] table FinancialImpact > FinancialImpact
• [x] col FinancialImpactId > impact_id
• [x] col Name > name

Fragility

• [x] table Fragility > Fragility
• [x] col FragilityID > fragility_id
• [x] col FragilityName > name

FragilityTag

• [x] table FragilityTag > FragilityTag
• [x] col FragilityTagID > tag_id
• [x] col FragilityTagName > name

MaintenanceLog

• [x] table MaintenanceLog > MaintenanceLogs
• [x] col MaintenanceLogID > log_id
• [x] col MaintainerID > maintainer
• [x] col MaintenanceDate > maintained_at
• [x] col Comment > comments
• [x] col MaintenanceLogStatusID > status

MaintenanceLogStatus

• [x] table MaintenanceLogStatus > MaintenanceLogStatus
• [x] col MaintenanceLogStatusID > status_id
• [x] col MaintenanceLogStatusName > name

MaintenanceSchedule

• [x] table MaintenanceSchedule > MaintenanceSchedule
• [x] col MaintenanceScheduleID > schedule_id
• [x] col MaintenanceScheduleName > name

OrganizationalValue

• [x] table OrganizationalValue > OrganizationalValue
• [x] col OrganizationalValueID > value_id
• [x] col OrganizationalValueName > name

ReportManageEngineTickets

• [x] table ReportManageEngineTickets > ReportTickets
• [x] col ManageEngineTicketsId > ticket_id
• [x] col TicketNumber > number
• [x] col Description > description
• [x] col ReportObjectId > report_id
• [x] col TicketUrl > ticketurl

ReportObjectConversation_doc

• [x] table ReportObjectConversation_doc > drop table

ReportObjectConversationMessage_doc

• [x] table ReportObjectConversationMessage_doc > drop table

ReportObjectDocFragilityTags

• [ ] table ReportObjectDocFragilityTags > ReportFragilityTags
• [ ] col LinkId > link_id
• [ ] col ReportObjectID > report
• [ ] col FragilityTagID > fragility_tag

ReportObjectDocMaintenanceLogs

• [ ] table ReportObjectDocMaintenanceLogs > ReportMaintenanceLogs
• [ ] col LinkId > link_id
• [ ] col ReportObjectID > report
• [ ] col MaintenanceLogID > log

ReportObjectDocTerms

• [ ] table ReportObjectDocTerms > ReportTerms
• [ ] col LinkId > link_id
• [ ] col ReportObjectID > report_doc
• [ ] col TermId > term

ReportObjectImages_doc

• [ ] table ReportObjectImages_doc > ReportImages
• [ ] col ImageID > image_id
• [ ] col ReportObjectID > report_id
• [ ] col ImageOrdinal > image_rank
• [ ] col ImageData > image_data
• [ ] col ImageSource > image_source

ReportObject_doc

• [ ] table ReportObject_doc > ReportDocs
• [ ] col OperationalOwnerUserID > ops_owner
• [ ] col Requester > requester
• [ ] col GitLabProjectURL > collection_url
• [ ] col DeveloperDescription > description
• [ ] col KeyAssumptions > assumptions
• [ ] col OrganizationalValueID > org_value
• [ ] col EstimatedRunFrequencyID > frequency
• [ ] col FragilityID > fragility
• [ ] col ExecutiveVisibilityYN > executive_report
• [ ] col MaintenanceScheduleID > maintenance_schedule
• [ ] col LastUpdateDateTime > _modified_at
• [ ] col CreatedDateTime > _created_at
• [ ] col CreatedBy > created_by
• [ ] col UpdatedBy > modified_by
• [ ] col EnabledForHyperspace > enabled_for_hyperspace
• [ ] col DoNotPurge > do_not_purge
• [ ] col Hidden > hidden
• [ ] col ReportObjectID > report

RolePermissionLinks

• [ ] table RolePermissionLinks > RolePermissionLinks
• [ ] col RolePermissionLinksId > permissionlinks_id
• [ ] col RoleId > role_id (pk)
• [ ] col RolePermissions > permission_id (pk)

RolePermissions

• [ ] table RolePermissions > RolePermissions
• [ ] col RolePermissionsId > permissions_id
• [ ] col Name > name
• [ ] col Description > description

StrategicImportance

• [ ] table StrategicImportance > StrategicImportance
• [ ] col StrategicImportanceId > importance_id
• [ ] col Name > name

Term

• [ ] table Term > Terms
• [ ] col TermId > term_id
• [ ] col Name > name
• [ ] col Summary > summary
• [ ] col TechnicalDefinition > technical_definition
• [ ] col ApprovedYN > approved
• [ ] col ApprovalDateTime > _approved_at
• [ ] col ApprovedByUserId > approved_by (fk)
• [ ] col ExternalStandardUrl > external_standard_url
• [ ] col ValidFromDateTime > _valid_from
• [ ] col ValidToDateTime > _valid_to
• [ ] col UpdatedByUserId > modified_by (fk)
• [ ] col LastUpdatedDateTime > _modified_at

TermConversation

• [x] table TermConversation > drop table

TermConversationMessage

• [x] table TermConversationMessage > drop table

UserFavoriteFolders

• [ ] table UserFavoriteFolders > FavoriteFolders
• [ ] col UserFavoriteFolderId > folder_id
• [ ] col FolderName > name
• [ ] col favorite_folders > user (fk)
• [ ] col FolderRank > rank

UserFavorites

• [ ] table UserFavorites > Favorites
• [ ] col UserFavoritesId > favorite_id
• [ ] col ItemType > item_type
• [ ] col ItemRank > rank
• [ ] col ItemId > item_id
• [ ] col UserId > user (fk)
• [ ] col ItemName > name
• [ ] col FolderId > folder = (fk)

UserPreferences

• [ ] table UserPreferences > UserPreferences
• [ ] col UserPreferenceId > preference_id
• [ ] col ItemType > key
• [ ] col ItemValue > value
• [ ] col ItemId > item_id
• [ ] col UserId > user_id (fk)

UserRoleLinks

• [x] table UserRoleLinks > UserRolelinks
• [x] col UserRoleLinksId > rolelinks_id
• [x] col UserId > user (fk)
• [x] col UserRolesId > role (fk)

UserRoles

• [x] table UserRoles > UserRoles
• [x] col UserRolesId > role_id
• [x] col Name > name
• [x] col Description > description