Skip to content

Instantly share code, notes, and snippets.

@hiendinhngoc

hiendinhngoc/org.rb

Created July 18, 2025 02:53
Show Gist options
  • Save hiendinhngoc/c94f96a590d177c08a947744202052e2 to your computer and use it in GitHub Desktop.
Save hiendinhngoc/c94f96a590d177c08a947744202052e2 to your computer and use it in GitHub Desktop.
Download ZIP
Export data
Raw
org.rb
#!/usr/bin/env ruby
require 'pg'
require 'fileutils'
require 'json'
require 'set'
RESERVED_WORDS = {
"column" => "\"column\"",
"default" => "\"default\"",
"type" => "\"type\"",
"not" => "\"not\"",
"null" => "\"null\"",
"check" => "\"check\"",
"unique" => "\"unique\"",
"primary" => "\"primary\"",
"authorization" => "\"authorization\"",
"group" => "\"group\"",
}
MODEL_DEFINITIONS = {
'organizations' => {
pk: 'id',
has_one: [
{ related_table: 'custom_domain_setups', fk_in_related_table: 'organization_id' },
{ related_table: 'sportdata_suspensions', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_settings', fk_in_related_table: 'organization_id' },
{ related_table: 'club_infos', fk_in_related_table: 'organization_id' },
{ related_table: 'containers', fk_in_related_table: 'menu_organization_id' },
{ related_table: 'org_websites', fk_in_related_table: 'organization_id' },
{ related_table: 'game_sheet_apis', fk_in_related_table: 'organization_id' },
{ related_table: 'transient_org_themes', fk_in_related_table: 'organization_id' },
{ related_table: 'short_name_prefix_values', fk_in_related_table: 'organization_id' },
{ related_table: 'org_ui_settings', fk_in_related_table: 'organization_id' },
{ related_table: 'org_family_configuration_settings', fk_in_related_table: 'organization_id' },
{ related_table: 'athlete_global_ranking_configs', fk_in_related_table: 'organization_id' },
],
has_many: [
{ related_table: 'registrations', fk_in_related_table: 'organization_id' },
{ related_table: 'discount_codes', fk_in_related_table: 'organization_id' },
{ related_table: 'micro_service_requests', fk_in_related_table: 'organization_id' },
{ related_table: 'installment_plans', fk_in_related_table: 'organization_id' },
{ related_table: 'membership_registration_fees', fk_in_related_table: 'organization_id' },
{ related_table: 'membership_registration_fee_items', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_people', fk_in_related_table: 'organization_id' },
{ related_table: 'chargebacks', fk_in_related_table: 'organization_id' },
{ related_table: 'season_delegations', fk_in_related_table: 'delegation_organization_id' },
{ related_table: 'questions', fk_in_related_table: 'organization_id' },
{ related_table: 'sub_questions', fk_in_related_table: 'organization_id' },
{ related_table: 'organizations_sports', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_social_networks', fk_in_related_table: 'organization_id' },
{ related_table: 'builder_variations', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_type_definitions', fk_in_related_table: 'organization_id' },
{ related_table: 'events', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_types', fk_in_related_table: 'organization_id' },
{ related_table: 'media_galleries', fk_in_related_table: 'organization_id' },
{ related_table: 'account_statement_exportable_questions', fk_in_related_table: 'organization_id' },
{ related_table: 'payments', fk_in_related_table: 'organization_id' },
{ related_table: 'installment_plan_subscriptions', fk_in_related_table: 'organization_id' },
{ related_table: 'rsportz_fee_invoices', fk_in_related_table: 'organization_id' },
{ related_table: 'multi_event_discount_schemes', fk_in_related_table: 'organization_id' },
{ related_table: 'membership_subscription_activations', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_teams', fk_in_related_table: 'organization_id' },
{ related_table: 'locations', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_locations', fk_in_related_table: 'organization_id' },
{ related_table: 'competitions', fk_in_related_table: 'organization_id' },
{ related_table: 'seasons', fk_in_related_table: 'organization_id' },
{ related_table: 'delegations', fk_in_related_table: 'organization_id' },
{ related_table: 'divisions', fk_in_related_table: 'organization_id' },
{ related_table: 'skill_levels', fk_in_related_table: 'organization_id' },
{ related_table: 'age_groups', fk_in_related_table: 'organization_id' },
{ related_table: 'orders', fk_in_related_table: 'organization_id' },
{ related_table: 'order_items', fk_in_related_table: 'organization_id' },
{ related_table: 'transactions', fk_in_related_table: 'organization_id' },
{ related_table: 'recurring_payments', fk_in_related_table: 'organization_id' },
{ related_table: 'suspension_rules', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_certifications', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_certification_providers', fk_in_related_table: 'organization_id' },
{ related_table: 'bulk_emails', fk_in_related_table: 'organization_id' },
{ related_table: 'ad_banners', fk_in_related_table: 'organization_id' },
{ related_table: 'fees', fk_in_related_table: 'organization_id' },
{ related_table: 'membership_options', fk_in_related_table: 'organization_id' },
{ related_table: 'membership_subscriptions', fk_in_related_table: 'organization_id' },
{ related_table: 'file_assets', fk_in_related_table: 'organization_id' },
{ related_table: 'page_inserts', fk_in_related_table: 'organization_id' },
{ related_table: 'sidebar_inserts', fk_in_related_table: 'organization_id' },
{ related_table: 'org_event_tags', fk_in_related_table: 'organization_id' },
{ related_table: 'org_groupings', fk_in_related_table: 'organization_id' },
{ related_table: 'org_group_items', fk_in_related_table: 'organization_id' },
{ related_table: 'competition_groupings', fk_in_related_table: 'organization_id' },
{ related_table: 'game_result_loaders', fk_in_related_table: 'organization_id' },
{ related_table: 'widget_inserts', fk_in_related_table: 'organization_id' },
{ related_table: 'layout_row_org_settings', fk_in_related_table: 'organization_id' },
{ related_table: 'page_layout_org_settings', fk_in_related_table: 'organization_id' },
{ related_table: 'stripe_notifications', fk_in_related_table: 'organization_id' },
{ related_table: 'membership_levels', fk_in_related_table: 'organization_id' },
{
related_table: 'metric_assignments',
fk_in_related_table: 'assignment_id',
polymorphic: { type_column: 'assignment_type', type_value: 'Organization' }
},
{ related_table: 'org_pages', fk_in_related_table: 'organization_id' },
{ related_table: 'waivers', fk_in_related_table: 'organization_id' },
{ related_table: 'org_integrations', fk_in_related_table: 'organization_id' },
{ related_table: 'org_subscriptions', fk_in_related_table: 'organization_id'},
# { related_table: 'stripe_payment_accounts', fk_in_related_table: 'owned_organization_id' },
{ related_table: 'achievement_categories', fk_in_related_table: 'organization_id' },
{ related_table: 'organization_person_for_child_orgs', fk_in_related_table: 'organization_id' },
{ related_table: 'notification_assignments', fk_in_related_table: 'organization_id' },
{ related_table: 'account_statement_exportable_spec_attrs', fk_in_related_table: 'organization_id' },
{ related_table: 'org_api_users_organizations', fk_in_related_table: 'organization_id' },
{ related_table: 'org_fifa_duplicates', fk_in_related_table: 'organization_id' },
{ related_table: 'team_tags', fk_in_related_table: 'organization_id' },
{ related_table: 'proof_of_payments', fk_in_related_table: 'organization_id' },
{ related_table: 'org_sport_settings', fk_in_related_table: 'organization_id' },
{ related_table: 'custom_reports', fk_in_related_table: 'organization_id' },
{ related_table: 'account_stripe_payments', fk_in_related_table: 'organization_id' },
],
belongs_to: [
{ related_table: 'membership_levels', fk_in_this_table: 'membership_level_id' },
{ related_table: 'questions', fk_in_this_table: 'selected_question_id' },
{ related_table: 'containers', fk_in_this_table: 'footer_container_id' },
{ related_table: 'containers', fk_in_this_table: 'header_container_id' },
{ related_table: 'containers', fk_in_this_table: 'page_insert_container_id' },
{ related_table: 'locale_validate_formats', fk_in_this_table: 'locale_number_format_id' },
{ related_table: 'locale_display_formats', fk_in_this_table: 'short_date_format_id' },
{ related_table: 'locale_display_formats', fk_in_this_table: 'long_date_format_id' },
{ related_table: 'stripe_payment_accounts', fk_in_this_table: 'stripe_payment_account_id' },
{ related_table: 'organizations', fk_in_this_table: 'ancestor1_id' },
{ related_table: 'organizations', fk_in_this_table: 'ancestor2_id' },
{ related_table: 'organizations', fk_in_this_table: 'ancestor3_id' },
{ related_table: 'organizations', fk_in_this_table: 'ancestor4_id' },
{ related_table: 'organizations', fk_in_this_table: 'ancestor5_id' },
],
},
'metric_assignments' => {
pk: 'id',
},
'people' => {
pk: 'id',
has_one: [
{ related_table: 'iawa_registrations', fk_in_related_table: 'person_id' },
{ related_table: 'accounts', fk_in_related_table: 'person_id' },
{ related_table: 'schedule_views', fk_in_related_table: 'person_id' },
{ related_table: 'super_admin_permissions', fk_in_related_table: 'person_id' },
{ related_table: 'person_suspensions', fk_in_related_table: 'person_id' },
{ related_table: 'removed_people', fk_in_related_table: 'person_id' },
{ related_table: 'person_general_information', fk_in_related_table: 'person_id' },
{ related_table: 'person_medicals', fk_in_related_table: 'person_id' },
{ related_table: 'person_additional_contacts', fk_in_related_table: 'person_id' },
{ related_table: 'person_emergency_contacts', fk_in_related_table: 'person_id' },
{ related_table: 'person_team_contacts', fk_in_related_table: 'person_id' },
{ related_table: 'person_equipment', fk_in_related_table: 'person_id' },
{ related_table: 'person_identification', fk_in_related_table: 'person_id' },
{ related_table: 'person_martial_arts', fk_in_related_table: 'person_id' },
{ related_table: 'person_screening', fk_in_related_table: 'person_id' },
{ related_table: 'person_skills_credentials', fk_in_related_table: 'person_id' },
{ related_table: 'person_fifa_registration', fk_in_related_table: 'person_id' },
],
has_many: [
{ related_table: 'registration_groups', fk_in_related_table: 'person_id' },
{ related_table: 'season_delegation_registrants', fk_in_related_table: 'person_id' },
{ related_table: 'child_transfer_requests', fk_in_related_table: 'child_person_id' },
{ related_table: 'division_people', fk_in_related_table: 'person_id' },
{ related_table: 'attendees', fk_in_related_table: 'person_id' },
{ related_table: 'player_statistics', fk_in_related_table: 'person_id' },
{ related_table: 'game_actions', fk_in_related_table: 'person_id' },
{ related_table: 'organization_alert_people', fk_in_related_table: 'person_id' },
{ related_table: 'team_roster_invitations', fk_in_related_table: 'person_id' },
{ related_table: 'profile_pages', fk_in_related_table: 'person_id' },
{ related_table: 'people_placements', fk_in_related_table: 'person_id' },
{ related_table: 'unverified_achievements', fk_in_related_table: 'person_id' },
{ related_table: 'person_sport_details', fk_in_related_table: 'person_id' },
{ related_table: 'person_profile_videos', fk_in_related_table: 'person_id' },
{ related_table: 'delegation_officials', fk_in_related_table: 'person_id' },
{ related_table: 'delegation_admins', fk_in_related_table: 'person_id' },
{ related_table: 'scoring_actions', fk_in_related_table: 'person_id' },
{ related_table: 'membership_certifications', fk_in_related_table: 'person_id' },
{ related_table: 'bulk_member_invites', fk_in_related_table: 'person_id' },
{ related_table: 'certification_expiration_update_logs', fk_in_related_table: 'person_id' },
{ related_table: 'system_alert_dismissed_people', fk_in_related_table: 'person_id' },
{ related_table: 'people_fifa_duplicates', fk_in_related_table: 'person_id' },
{ related_table: 'ncsa_recruit_profiles', fk_in_related_table: 'person_id' },
{ related_table: 'person_blocked_mobiles', fk_in_related_table: 'person_id' },
{ related_table: 'organization_people', fk_in_related_table: 'person_id' },
# Children relationships
{ related_table: 'people', fk_in_related_table: 'parent_id' },
],
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'parent_id' },
],
},
'locale_display_formats' => {
pk: 'id',
},
'locale_validate_formats' => {
pk: 'id',
},
'custom_reports' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'org_sport_settings' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'sports', fk_in_this_table: 'sport_id' },
],
has_many: [
{ related_table: 'org_sport_achievement_levels', fk_in_related_table: 'org_sport_setting_id' },
]
},
'proof_of_payments' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'people', fk_in_this_table: 'person_id' },
],
},
'team_tags' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'team_tag_standings', fk_in_related_table: 'team_tag_id' },
]
},
'org_fifa_duplicates' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'org_api_users_organizations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'org_api_users', fk_in_this_table: 'org_api_user_id' },
],
},
'organizations_sports' => {
pk: 'organization_id,sport_id', # Composite primary key
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'sports', fk_in_this_table: 'sport_id' },
],
},
'account_statement_exportable_spec_attrs' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'notification_assignments' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'notifications', fk_in_this_table: 'notification_id' },
],
},
'organization_person_for_child_orgs' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'achievement_categories' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'sports', fk_in_this_table: 'sport_id' },
],
has_many: [
{ related_table: 'organization_person_achievement_levels', fk_in_related_table: 'category_id' },
]
},
'stripe_payment_accounts' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'owned_organization_id' },
{ related_table: 'people', fk_in_this_table: 'current_org_admin_id' },
],
has_many: [
{ related_table: 'stripe_payment_owners', fk_in_related_table: 'stripe_payment_account_id' },
{ related_table: 'account_statements', fk_in_related_table: 'stripe_payment_account_id' },
{
related_table: 'organization_alerts',
fk_in_related_table: 'reference_id',
polymorphic: { type_column: 'reference_type', type_value: 'StripePaymentAccount' }
},
],
},
'org_integrations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'waivers' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
]
},
'org_pages' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'containers', fk_in_this_table: 'body_container_id' },
],
has_one: []
},
'membership_levels' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: []
},
'stripe_notifications' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'organization_alerts', fk_in_this_table: 'organization_alert_id' },
],
},
'page_layout_org_settings' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'page_layouts', fk_in_this_table: 'page_layout_id' },
],
},
'layout_row_org_settings' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'containers', fk_in_this_table: 'row_id' },
],
},
'widget_inserts' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'containers', fk_in_this_table: 'widget_container_id' },
],
},
'game_result_loaders' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'competition_groupings' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'competition_groups', fk_in_related_table: 'grouping_id' },
]
},
'org_group_items' => {
pk: 'id',
belongs_to: [
{ related_table: 'org_groups', fk_in_this_table: 'org_group_id' },
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'org_groupings' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'org_groups', fk_in_related_table: 'grouping_id' },
]
},
'org_event_tags' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'event_taggings', fk_in_related_table: 'org_event_tag_id' },
]
},
'sidebar_inserts' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'page_inserts' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'file_assets' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'people', fk_in_this_table: 'author_id' },
],
has_many: [
{ related_table: 'widget_file_assets', fk_in_related_table: 'file_asset_id' },
{
related_table: 'media_gallery_assets',
fk_in_related_table: 'asset_id',
polymorphic: { type_column: 'asset_type', type_value: 'FileAsset' }
},
]
},
'membership_subscriptions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'memberships', fk_in_this_table: 'membership_id' },
],
has_many: [
{
related_table: 'waiver_registrations',
fk_in_related_table: 'registerable_id',
polymorphic: { type_column: 'registerable_type', type_value: 'MembershipSubscription' }
},
{
related_table: 'question_answers',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'MembershipSubscription' }
},
{
related_table: 'sub_question_answers',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'MembershipSubscription' }
},
{
related_table: 'activity_logs',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'MembershipSubscription' }
},
{
related_table: 'activity_logs',
fk_in_related_table: 'sub_resource_id',
polymorphic: { type_column: 'sub_resource_type', type_value: 'MembershipSubscription' }
},
{
related_table: 'stripe_checkouts',
fk_in_related_table: 'product_id',
polymorphic: { type_column: 'product_type', type_value: 'MembershipSubscription' }
},
{ related_table: 'membership_certification_subscriptions', fk_in_related_table: 'membership_subscription_id' },
{ related_table: 'alternate_owners', fk_in_related_table: 'membership_subscription_id' },
{ related_table: 'aau_membership_details', fk_in_related_table: 'membership_subscription_id' }
]
},
'membership_options' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'membership_option_certifications', fk_in_related_table: 'membership_option_id' },
{ related_table: 'membership_option_required_membership_options', fk_in_related_table: 'membership_option_id' },
{ related_table: 'membership_option_organization_type_definitions', fk_in_related_table: 'membership_option_id' },
{ related_table: 'membership_option_restricted_organization_type_definitions', fk_in_related_table: 'membership_option_id' },
{ related_table: 'recurring_payment_plans', fk_in_related_table: 'membership_option_id' },
# { related_table: 'colombian_receipt_membership_fields', fk_in_related_table: 'membership_option_id' },
{
related_table: 'activity_logs',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'MembershipOption' }
},
{
related_table: 'form_data_assignments',
fk_in_related_table: 'registerable_id',
polymorphic: { type_column: 'registerable_type', type_value: 'MembershipOption' }
},
],
has_one: [
{ related_table: 'membership_option_expiration_schemes', fk_in_related_table: 'membership_option_id' },
{ related_table: 'fifa_registrations', fk_in_related_table: 'membership_option_id' },
{
related_table: 'text_instructions',
fk_in_related_table: 'textable_id',
polymorphic: { type_column: 'textable_type', type_value: 'MembershipOption' }
},
]
},
'fees' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'question_options', fk_in_related_table: 'fee_id' }
]
},
'ad_banners' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
]
},
'bulk_emails' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
},
'organization_certification_providers' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
]
},
'organization_certifications' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'certifications', fk_in_this_table: 'certification_id' },
],
has_many: [
{ related_table: 'membership_option_certifications', fk_in_related_table: 'organization_certification_id' },
],
has_one: [
{
related_table: 'custom_cards',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'OrganizationCertification' },
},
]
},
'suspension_rules' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'game_rules', fk_in_this_table: 'game_rule_id' }
]
},
'recurring_payments' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
},
'transactions' => {
pk: 'id',
belongs_to: [
{ related_table: 'billing_infos', fk_in_this_table: 'billing_info_id' },
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_one: [
{ related_table: 'charge_refunds', fk_in_related_table: 'refund_transaction_id' },
],
has_many: [
{ related_table: 'transaction_fees', fk_in_related_table: 'transaction_id' },
{ related_table: 'charge_refunds', fk_in_related_table: 'charge_transaction_id' },
{ related_table: 'account_statements', fk_in_related_table: 'transaction_id' },
{ related_table: 'refunded_order_items', fk_in_related_table: 'transaction_id' }
]
},
'order_items' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'question_answers', fk_in_this_table: 'question_answer_id' },
{ related_table: 'people', fk_in_this_table: 'marked_by_id' },
{ related_table: 'installment_plan_subscription_phases', fk_in_this_table: 'installment_plan_subscription_phase_id' },
],
},
'orders' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'organizations', fk_in_this_table: 'payer_org_id' },
{ related_table: 'organizations', fk_in_this_table: 'parent_id' },
],
has_many: [
],
},
'divisions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'sports', fk_in_this_table: 'sport_id' },
{ related_table: 'division_structs', fk_in_this_table: 'division_struct_id' },
{ related_table: 'fee_generators', fk_in_this_table: 'fee_generator_id' },
{ related_table: 'fee_generators', fk_in_this_table: 'team_fee_generator_id' },
],
has_many: [
{ related_table: 'division_teams', fk_in_related_table: 'division_id' },
{ related_table: 'games', fk_in_related_table: 'division_id' },
{ related_table: 'division_pool_names', fk_in_related_table: 'division_id' },
],
has_one: [
{ related_table: 'division_race_settings', fk_in_related_table: 'division_id' },
{ related_table: 'division_race_standings', fk_in_related_table: 'division_id' },
{ related_table: 'race_heat_configs', fk_in_related_table: 'division_id' },
{ related_table: 'custom_podia', fk_in_related_table: 'division_id' },
{
related_table: 'text_instructions',
fk_in_related_table: 'textable_id',
polymorphic: { type_column: 'textable_type', type_value: 'Division' }
},
]
},
'org_subscriptions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'org_subscription_plans', fk_in_this_table: 'stripe_plan_number' }
],
has_many: [
]
},
'age_groups' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
},
'skill_levels' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
},
'delegations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{
related_table: 'photos',
fk_in_related_table: 'photographed_id',
polymorphic: { type_column: 'photographed_type', type_value: 'Delegation' }
},
]
},
'seasons' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{
related_table: 'form_data_assignments',
fk_in_related_table: 'registerable_id',
polymorphic: { type_column: 'registerable_type', type_value: 'Season' }
},
{
related_table: 'waiver_registrations',
fk_in_related_table: 'registerable_id',
polymorphic: { type_column: 'registerable_type', type_value: 'Season' }
},
{ related_table: 'season_checkin_custom_slices', fk_in_related_table: 'season_id' },
{ related_table: 'ossd_surface_auths', fk_in_related_table: 'season_id' },
{ related_table: 'accreditation_data_sets', fk_in_related_table: 'season_id' },
{ related_table: 'accreditation_one_offs', fk_in_related_table: 'season_id' },
{ related_table: 'season_official_roles', fk_in_related_table: 'season_id' },
{ related_table: 'accreditation_card_backgrounds', fk_in_related_table: 'season_id' },
{ related_table: 'season_orders', fk_in_related_table: 'season_id' },
{ related_table: 'tournament_permissions', fk_in_related_table: 'season_id' },
{ related_table: 'event_video_feeds', fk_in_related_table: 'season_id' },
{ related_table: 'facility_preferences', fk_in_related_table: 'season_id' },
{
related_table: 'news_page_tags',
fk_in_related_table: 'news_page_taggable_id',
polymorphic: { type_column: 'news_page_taggable_type', type_value: 'Season' }
},
{ related_table: 'last_bulk_movements', fk_in_related_table: 'season_id' },
{ related_table: 'season_schedule_runs', fk_in_related_table: 'season_id' }
],
has_one: [
{ related_table: 'season_registrations', fk_in_related_table: 'season_id' },
{ related_table: 'season_checkin_configurations', fk_in_related_table: 'season_id' },
{ related_table: 'custom_cards', fk_in_related_table: 'resource_id', polymorphic: { type_column: 'resource_type', type_value: 'Season' } },
{ related_table: 'accreditation_settings', fk_in_related_table: 'season_id' },
{ related_table: 'race_medal_standings_configs', fk_in_related_table: 'season_id' },
{ related_table: 'season_official_fee_calculations', fk_in_related_table: 'season_id' }
],
},
'competitions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'sports', fk_in_this_table: 'sport_id' }
],
has_many: [
{
related_table: 'competition_group_items',
fk_in_related_table: 'competition_id',
},
{
related_table: 'child_team_categories',
fk_in_related_table: 'competition_id',
},
]
},
'organization_locations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
]
},
'locations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'games', fk_in_related_table: 'facility_id' },
{ related_table: 'scorekeeper_assignments', fk_in_related_table: 'location_id' }
]
},
'organization_teams' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'teams', fk_in_this_table: 'team_id' }
]
},
'membership_subscription_activations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'recurring_payment_plans', fk_in_this_table: 'recurring_payment_plan_id' },
{ related_table: 'people', fk_in_this_table: 'person_id' }
],
has_many: [
{ related_table: 'membership_subscription_activation_invoices', fk_in_related_table: 'membership_subscription_activation_id' },
{
related_table: 'activity_logs',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'MembershipSubscriptionActivation' },
},
{
related_table: 'question_answers',
fk_in_related_table: 'sub_resource_id',
polymorphic: { type_column: 'sub_resource_type', type_value: 'MembershipSubscriptionActivation' }
},
]
},
'multi_event_discount_schemes' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
]
},
'rsportz_fee_invoices' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'people', fk_in_this_table: 'marked_by_id' }
],
},
'installment_plan_subscriptions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'installment_plan_subscription_phases', fk_in_related_table: 'installment_plan_subscription_id' },
]
},
'payments' => {
pk: 'id',
belongs_to: [
],
},
'account_statement_exportable_questions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'media_galleries' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{ related_table: 'media_gallery_assets', fk_in_related_table: 'media_gallery_id' },
{ related_table: 'media_gallery_widget_usages', fk_in_related_table: 'media_gallery_id' }
],
},
'organization_types' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
},
'events' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'facilities', fk_in_this_table: 'facility_id' },
],
has_many: [
{ related_table: 'event_taggings', fk_in_related_table: 'event_id' }
]
},
'organization_type_definitions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
]
},
'builder_variations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'division_builders', fk_in_this_table: 'division_builder_id' }
],
has_many: [
{ related_table: 'builder_segments', fk_in_related_table: 'builder_variation_id' }
]
},
'organization_social_networks' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{ related_table: 'question_answers', fk_in_related_table: 'resource_id', polymorphic: { type_column: 'resource_type', type_value: 'OrganizationSocialNetwork' } }
]
},
'sub_questions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{
related_table: 'sub_question_options',
fk_in_related_table: 'sub_question_id',
class_name: 'SubQuestionOption'
},
{
related_table: 'sub_question_answers',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'SubQuestion' }
}
]
},
'questions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{ related_table: 'question_options', fk_in_related_table: 'question_id' },
{
related_table: 'question_answers',
fk_in_related_table: 'resource_id',
polymorphic: { type_column: 'resource_type', type_value: 'Question' }
}
],
has_one: [
{ related_table: 'question_answer_notifications', fk_in_related_table: 'question_id' }
]
},
'season_delegations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'delegation_organization_id', class_name: 'Organization' },
],
has_many: [
{ related_table: 'question_answers', fk_in_related_table: 'resource_id', polymorphic: { type_column: 'resource_type', type_value: 'SeasonDelegation' } },
{ related_table: 'division_teams', fk_in_related_table: 'season_delegation_id' },
{ related_table: 'season_delegation_registrants', fk_in_related_table: 'season_delegation_id' },
{ related_table: 'sub_question_answers', fk_in_related_table: 'resource_id', polymorphic: { type_column: 'resource_type', type_value: 'SeasonDelegation' } },
{ related_table: 'season_orders', fk_in_related_table: 'season_delegation_id' },
{ related_table: 'season_delegation_standings', fk_in_related_table: 'season_delegation_id' },
],
},
'chargebacks' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
],
has_many: [
{ related_table: 'chargeback_evidence_attachments', fk_in_related_table: 'chargeback_id' }
]
},
'membership_registration_fee_items' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
]
},
'membership_registration_fees' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
},
'installment_plans' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{ related_table: 'installment_plan_phases', fk_in_related_table: 'installment_plan_id', class_name: 'InstallmentPlanPhase' },
]
},
'discount_codes' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{ related_table: 'redeemed_discount_codes', fk_in_related_table: 'discount_code_id' }
]
},
'micro_service_requests' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
],
has_many: [
{ related_table: 'micro_service_athletes', fk_in_related_table: 'micro_service_request_id' }
]
},
'organization_people' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' },
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'memberships', fk_in_this_table: 'membership_id'}
]
},
'custom_domain_setups' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}] },
'sportdata_suspensions' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id'},
{ related_table: 'people', fk_in_this_table: 'updated_by_id'}
]
},
'organization_settings' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}] },
'club_infos' => {
pk: 'id',
has_many: [
{ related_table: 'alternate_owners', fk_in_related_table: 'club_info_id' },
{ related_table: 'club_info_groupings', fk_in_related_table: 'club_info_id' },
{ related_table: 'question_answers',
fk_in_related_table: 'resource_id',
polymorphic: {
type_column: 'resource_type',
type_value: 'ClubInfo'
}
},
{ related_table: 'sub_question_answers',
fk_in_related_table: 'resource_id',
polymorphic: {
type_column: 'resource_type',
type_value: 'ClubInfo'
}
}
],
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'org_subscription_plans', fk_in_this_table: 'org_subscription_plan_id' },
{ related_table: 'alternate_owners', fk_in_this_table: 'main_alternate_owner_id' },
{ related_table: 'organizations', fk_in_this_table: 'parent_organization_id' },
],
has_one: [
{ related_table: 'aau_infos', fk_in_related_table: 'club_info_id' }
]
},
'containers' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'menu_organization_id'}] }, # Note custom FK
'org_websites' => { pk: 'id', belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id'},
]},
'game_sheet_apis' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}] },
'transient_org_themes' => {
pk: 'id',
belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}],
},
'short_name_prefix_values' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}] },
'org_ui_settings' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}] },
'org_family_configuration_settings' => { pk: 'id', belongs_to: [{ related_table: 'organizations', fk_in_this_table: 'organization_id'}] },
'athlete_global_ranking_configs' => { pk: 'id', belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id'},
]},
'registrations' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id'},
{ related_table: 'people', fk_in_this_table: 'person_id'},
{ related_table: 'teams', fk_in_this_table: 'team_id'},
{ related_table: 'billing_infos', fk_in_this_table: 'billing_info_id'},
],
has_many: [
{ related_table: 'registration_cached_objects', fk_in_related_table: 'registration_id'},
{ related_table: 'registration_group_registrations', fk_in_related_table: 'registration_id', dependent: :destroy},
{ related_table: 'registration_discount_codes', fk_in_related_table: 'registration_id'},
{ related_table: 'registration_log_items', fk_in_related_table: 'registration_object_id', dependent: :destroy},
{ related_table: 'bank_wire_transfers', fk_in_related_table: 'registration_id'}
],
has_one: [
{ related_table: 'registration_cached_objects', fk_in_related_table: 'registration_id'},
{ related_table: 'bank_wire_transfers', fk_in_related_table: 'registration_id'}
],
},
'text_inserts' => {
pk: 'id',
has_many: [
{ related_table: 'form_data_assignments', fk_in_related_table: 'item_id' }
]
},
'form_data_assignments' => {
pk: 'id',
belongs_to: [
{ related_table: 'text_inserts', fk_in_this_table: 'item_id' },
{ related_table: 'custom_sections', fk_in_this_table: 'item_id' },
{ related_table: 'questions', fk_in_this_table: 'item_id' },
{ related_table: 'question_placeholders', fk_in_this_table: 'item_id' },
{ related_table: 'org_groupings', fk_in_this_table: 'item_id' }
]
},
'question_placeholders' => {
pk: 'id',
has_many: [
{ related_table: 'form_data_assignments', fk_in_related_table: 'item_id' }
]
},
'custom_sections' => {
pk: 'id',
has_many: [
{ related_table: 'form_data_assignments', fk_in_related_table: 'item_id' }
]
},
'accounts' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'account_stripe_payments' => {
pk: 'id',
belongs_to: [
{ related_table: 'organizations', fk_in_this_table: 'organization_id' },
{ related_table: 'accounts', fk_in_this_table: 'account_id' }
]
},
'division_people' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' },
{ related_table: 'divisions', fk_in_this_table: 'division_id' }
]
},
'memberships' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' },
{ related_table: 'organizations', fk_in_this_table: 'organization_id' }
]
},
'certifications' => {
pk: 'id'
},
'sports' => {
pk: 'id',
has_many: [
{ related_table: 'organizations_sports', fk_in_related_table: 'sport_id' },
],
},
'billing_infos' => {
pk: 'id'
},
'teams' => {
pk: 'id'
},
'facilities' => {
pk: 'id'
},
'division_structs' => {
pk: 'id'
},
'fee_generators' => {
pk: 'id'
},
'game_rules' => {
pk: 'id'
},
'division_builders' => {
pk: 'id'
},
'page_layouts' => {
pk: 'id'
},
'notifications' => {
pk: 'id'
},
'org_api_users' => {
pk: 'id',
has_many: [
{ related_table: 'org_api_users_organizations', fk_in_related_table: 'org_api_user_id' },
],
},
'org_subscription_plans' => {
pk: 'plan_id'
},
'charge_refunds' => {
pk: 'id',
belongs_to: [
{ related_table: 'transactions', fk_in_this_table: 'charge_transaction_id' },
{ related_table: 'transactions', fk_in_this_table: 'refund_transaction_id' },
]
},
'widget_file_assets' => {
pk: 'id',
belongs_to: [
{ related_table: 'file_assets', fk_in_this_table: 'file_asset_id' },
]
},
'organization_alerts' => {
pk: 'id'
},
# People-related tables that were missing definitions
'attendees' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'player_statistics' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'game_actions' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'organization_alert_people' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'team_roster_invitations' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'profile_pages' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'people_placements' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'unverified_achievements' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'delegation_officials' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'delegation_admins' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'scoring_actions' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'membership_certifications' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'bulk_member_invites' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'certification_expiration_update_logs' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'system_alert_dismissed_people' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'people_fifa_duplicates' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'ncsa_recruit_profiles' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'person_blocked_mobiles' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'registration_groups' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'season_delegation_registrants' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'person_id' }
]
},
'child_transfer_requests' => {
pk: 'id',
belongs_to: [
{ related_table: 'people', fk_in_this_table: 'child_person_id' }
]
}
}.freeze
class OrganizationExporter
def initialize(org_id)
@db_user = ENV['DB_USER'] || 'deploy'
@db_name = ENV['DB_NAME'] || 'rsportz_dev'
@org_id = org_id
@db_password = ENV['DB_PASSWORD'] || 'password'
@host = ENV['DB_HOST'] || 'localhost'
@export_dir = "org_#{org_id}_export"
@export_file = File.join(@export_dir, "org_#{org_id}_complete_export.sql")
@count_file = File.join(@export_dir, "org_#{org_id}_count.sql")
@validation_file = File.join(@export_dir, "org_#{org_id}_validation.log")
@conn = nil
@export_order = []
# Track record IDs to export per table to avoid duplicates
@records_to_export = Hash.new { |h, k| h[k] = Set.new }
@tables_processed = Set.new
# Track visited records to prevent infinite recursion
@visited_records = Set.new
end
def connect
@conn = PG.connect(dbname: @db_name, user: @db_user, password: @db_password, host: @host)
end
def disconnect
@conn.close if @conn
end
def export
start_time = Time.now
puts "From database: #{@db_name}, user: #{@db_user}, host: #{@host}"
puts "Exporting organization data for ID: #{@org_id}"
puts "Export started at: #{start_time.strftime('%Y-%m-%d %H:%M:%S')}"
# Show debugging options
if ENV['DEBUG_COLLECTION'] || ENV['DEBUG_SQL']
puts "\n🐛 DEBUG MODE ENABLED:"
puts " DEBUG_COLLECTION=#{ENV['DEBUG_COLLECTION']}" if ENV['DEBUG_COLLECTION']
puts " DEBUG_SQL=#{ENV['DEBUG_SQL']}" if ENV['DEBUG_SQL']
else
puts "\n💡 Tip: Set DEBUG_COLLECTION=1 or DEBUG_SQL=1 for detailed logging"
end
puts ""
FileUtils.mkdir_p(@export_dir)
File.open(@export_file, 'w') do |f|
f.puts "BEGIN;"
f.puts "SET CONSTRAINTS ALL DEFERRED;"
end
# Phase 1: Collect all record IDs to export
puts "Phase 1: Collecting record IDs to export..."
collect_records_recursively('organizations', @org_id)
# Phase 1.5: Ensure all people and their relationships are collected
puts "Phase 1.5: Collecting all person relationships..."
collect_all_people_relationships
# Phase 1.6: Collect orphaned models that weren't reached through relationships
collect_orphaned_models
# Phase 2: Validate data integrity
puts "Phase 2: Validating data integrity..."
validate_data_integrity
# Phase 3: Export deduplicated data
puts "Phase 3: Exporting deduplicated data..."
export_collected_records
export_child_containers(@org_id)
export_widgets(@saved_ids)
File.open(@export_file, 'a') do |f|
f.puts "COMMIT;"
f.puts "SET CONSTRAINTS ALL IMMEDIATE;"
end
end_time = Time.now
duration = end_time - start_time
puts "\nExport complete! Created: #{@export_file}"
puts "Validation report: #{@validation_file}"
puts "\nExport order:"
@export_order.each_with_index do |table, index|
puts "#{index + 1}. #{table}"
end
puts "\n"
puts "Export finished at: #{end_time.strftime('%Y-%m-%d %H:%M:%S')}"
puts "Total duration: #{duration} seconds"
# Final recommendations
puts "\n📋 RECOMMENDATIONS:"
puts " 1. Check validation report: #{@validation_file}"
puts " 2. Review skipped relationships in the collection summary above"
puts " 3. If tables are missing, run with DEBUG_COLLECTION=1 to trace collection"
puts " 4. If SQL errors occur, run with DEBUG_SQL=1 to see queries"
end
private
def collect_all_people_relationships
puts " Collecting all people from organization_people..."
# Get all people referenced by organization_people for this org and related orgs
org_people_query = <<~SQL
SELECT DISTINCT op.person_id
FROM organization_people op
WHERE (
op.organization_id = #{@org_id} OR
op.organization_id IN (
SELECT id FROM organizations
WHERE ancestor1_id = #{@org_id} OR ancestor2_id = #{@org_id} OR ancestor3_id = #{@org_id} OR
ancestor4_id = #{@org_id} OR ancestor5_id = #{@org_id}
)
)
AND op.person_id IS NOT NULL
SQL
result = @conn.exec(org_people_query)
initial_person_ids = result.map { |row| row['person_id'].to_i }.compact
puts " Found #{initial_person_ids.size} people from organization_people"
# Get all people from division_people for divisions in this org
division_people_query = <<~SQL
SELECT DISTINCT dp.person_id
FROM division_people dp
INNER JOIN divisions d ON dp.division_id = d.id
WHERE (
d.organization_id = #{@org_id} OR
d.organization_id IN (
SELECT id FROM organizations
WHERE ancestor1_id = #{@org_id} OR ancestor2_id = #{@org_id} OR ancestor3_id = #{@org_id} OR
ancestor4_id = #{@org_id} OR ancestor5_id = #{@org_id}
)
)
AND dp.person_id IS NOT NULL
SQL
result = @conn.exec(division_people_query)
division_person_ids = result.map { |row| row['person_id'].to_i }.compact
puts " Found #{division_person_ids.size} people from division_people"
all_person_ids = (initial_person_ids + division_person_ids).uniq
if all_person_ids.any?
# Collect all family relationships (parents and children) recursively
family_query = <<~SQL
WITH RECURSIVE person_family AS (
-- Base case: all people we've identified
SELECT id, parent_id, 1 as level
FROM people
WHERE id IN (#{all_person_ids.join(',')})
UNION ALL
-- Recursive case: get parents and children
SELECT p.id, p.parent_id, pf.level + 1
FROM people p
INNER JOIN person_family pf ON (
p.id = pf.parent_id OR -- children of current people
p.parent_id = pf.id -- parents of current people
)
WHERE pf.level < 10 -- Prevent infinite loops
)
SELECT DISTINCT id FROM person_family
SQL
result = @conn.exec(family_query)
complete_person_ids = result.map { |row| row['id'].to_i }.compact
puts " Expanded to #{complete_person_ids.size} people (including family relationships)"
# Add all people to export list
@records_to_export['people'].merge(complete_person_ids)
@export_order << 'people' unless @export_order.include?('people')
# Also collect related models for these people
collect_person_related_models(complete_person_ids)
end
end
def collect_person_related_models(person_ids)
return if person_ids.empty?
puts " Collecting person-related models..."
# Collect accounts for these people
account_query = "SELECT DISTINCT id FROM accounts WHERE person_id IN (#{person_ids.join(',')})"
result = @conn.exec(account_query)
account_ids = result.map { |row| row['id'].to_i }.compact
if account_ids.any?
@records_to_export['accounts'].merge(account_ids)
@export_order << 'accounts' unless @export_order.include?('accounts')
puts " Added #{account_ids.size} accounts"
end
# Collect other person-related tables
person_tables = %w[
person_general_information person_medicals person_additional_contacts
person_emergency_contacts person_team_contacts person_equipment
person_identification person_martial_arts person_screening
person_skills_credentials person_fifa_registration iawa_registrations
schedule_views super_admin_permissions person_suspensions
removed_people person_sport_details person_profile_videos
]
person_tables.each do |table|
begin
query = "SELECT DISTINCT id FROM #{table} WHERE person_id IN (#{person_ids.join(',')})"
result = @conn.exec(query)
ids = result.map { |row| row['id'].to_i }.compact
if ids.any?
@records_to_export[table].merge(ids)
@export_order << table unless @export_order.include?(table)
puts " Added #{ids.size} #{table} records"
end
rescue PG::UndefinedTable
# Table doesn't exist, skip it
next
rescue PG::Error => e
puts " Warning: Could not collect #{table}: #{e.message}"
next
end
end
end
def validate_data_integrity
validation_issues = []
puts " Validating organization_people -> people relationships..."
# Check for organization_people with missing people
if @records_to_export['organization_people'].any?
op_ids = @records_to_export['organization_people'].to_a.join(',')
missing_people_query = <<~SQL
SELECT op.id as op_id, op.person_id, op.organization_id
FROM organization_people op
WHERE op.id IN (#{op_ids})
AND op.person_id IS NOT NULL
AND op.person_id NOT IN (#{@records_to_export['people'].to_a.join(',') || '0'})
SQL
result = @conn.exec(missing_people_query)
if result.any?
result.each do |row|
issue = "OrganizationPerson ID #{row['op_id']} references missing person_id #{row['person_id']} (org: #{row['organization_id']})"
validation_issues << issue
puts " ⚠️ #{issue}"
# Try to add the missing person
missing_person_id = row['person_id'].to_i
@records_to_export['people'].add(missing_person_id)
collect_person_related_models([missing_person_id])
end
end
end
# Check for people with missing parents
if @records_to_export['people'].any?
people_ids = @records_to_export['people'].to_a.join(',')
missing_parents_query = <<~SQL
SELECT p.id, p.parent_id
FROM people p
WHERE p.id IN (#{people_ids})
AND p.parent_id IS NOT NULL
AND p.parent_id NOT IN (#{people_ids})
SQL
result = @conn.exec(missing_parents_query)
if result.any?
missing_parent_ids = []
result.each do |row|
issue = "Person ID #{row['id']} references missing parent_id #{row['parent_id']}"
validation_issues << issue
puts " ⚠️ #{issue}"
missing_parent_ids << row['parent_id'].to_i
end
# Add missing parents and their related models
@records_to_export['people'].merge(missing_parent_ids)
collect_person_related_models(missing_parent_ids)
puts " ✅ Added #{missing_parent_ids.size} missing parents"
end
end
# Check for division_people with missing people
if @records_to_export['division_people'].any?
dp_ids = @records_to_export['division_people'].to_a.join(',')
missing_dp_people_query = <<~SQL
SELECT dp.id as dp_id, dp.person_id, dp.division_id
FROM division_people dp
WHERE dp.id IN (#{dp_ids})
AND dp.person_id IS NOT NULL
AND dp.person_id NOT IN (#{@records_to_export['people'].to_a.join(',') || '0'})
SQL
result = @conn.exec(missing_dp_people_query)
if result.any?
missing_dp_person_ids = []
result.each do |row|
issue = "DivisionPerson ID #{row['dp_id']} references missing person_id #{row['person_id']} (division: #{row['division_id']})"
validation_issues << issue
puts " ⚠️ #{issue}"
missing_dp_person_ids << row['person_id'].to_i
end
# Add missing people and their related models
@records_to_export['people'].merge(missing_dp_person_ids)
collect_person_related_models(missing_dp_person_ids)
puts " ✅ Added #{missing_dp_person_ids.size} missing division people"
end
end
# Write validation report
File.open(@validation_file, 'w') do |f|
f.puts "Data Integrity Validation Report"
f.puts "Organization ID: #{@org_id}"
f.puts "Generated at: #{Time.now}"
f.puts "=" * 50
f.puts
if validation_issues.empty?
f.puts "✅ No data integrity issues found!"
else
f.puts "⚠️ Found #{validation_issues.size} data integrity issues:"
f.puts
validation_issues.each_with_index do |issue, index|
f.puts "#{index + 1}. #{issue}"
end
end
f.puts
f.puts "Export Summary:"
@records_to_export.each do |table, ids|
f.puts " #{table}: #{ids.size} records"
end
end
if validation_issues.any?
puts " ⚠️ Found #{validation_issues.size} data integrity issues (see #{@validation_file})"
else
puts " ✅ Data integrity validation passed!"
end
end
def collect_records_recursively(model_name, record_id)
model_def = MODEL_DEFINITIONS[model_name]
return unless model_def
# Add cycle detection to prevent infinite recursion
record_key = "#{model_name}:#{record_id}"
if @visited_records.include?(record_key)
puts " Skipping already visited #{record_key}" if ENV['DEBUG_COLLECTION']
return
end
@visited_records.add(record_key)
puts " Collecting #{model_name} record #{record_id}..." if ENV['DEBUG_COLLECTION']
if model_name == 'organizations'
@records_to_export['organizations'].add(record_id)
@export_order << 'organizations' unless @export_order.include?('organizations')
puts "Collecting organization #{record_id}"
else
# For non-organization models, add the record to the export list
@records_to_export[model_name].add(record_id)
@export_order << model_name unless @export_order.include?(model_name)
end
# Collect has_one relationships
if model_def[:has_one]
model_def[:has_one].each do |rel|
related_table = rel[:related_table]
# Handle polymorphic relationships
if rel[:polymorphic]
where_condition = "#{rel[:fk_in_related_table]} = #{record_id} AND #{rel[:polymorphic][:type_column]} = '#{rel[:polymorphic][:type_value]}'"
else
where_condition = "#{rel[:fk_in_related_table]} = #{record_id}"
end
ids = get_related_record_ids(related_table, where_condition)
if ids.any?
@records_to_export[related_table].merge(ids)
@export_order << related_table unless @export_order.include?(related_table)
puts " Found #{ids.size} #{related_table} records (has_one)" if ENV['DEBUG_COLLECTION']
# Recursively collect related model's relationships for each ID
ids.each do |related_id|
collect_records_recursively(related_table, related_id)
end
end
end
end
# Collect has_many relationships
if model_def[:has_many]
@skipped_relationships ||= []
model_def[:has_many].each do |rel|
related_table = rel[:related_table]
# Handle polymorphic relationships
if rel[:polymorphic]
where_condition = "#{rel[:fk_in_related_table]} = #{record_id} AND #{rel[:polymorphic][:type_column]} = '#{rel[:polymorphic][:type_value]}'"
else
# For organizations, use the record_id directly (which is org_id)
if model_name == 'organizations'
where_condition = "#{rel[:fk_in_related_table]} = #{record_id}"
else
# For other models, we need to find records in related_table that reference the current record
# The current record_id should be used with the FK specified in the relationship
where_condition = "#{rel[:fk_in_related_table]} = #{record_id}"
end
end
if where_condition.blank?
@skipped_relationships << "#{model_name} -> #{related_table} (blank condition)"
next
end
ids = get_related_record_ids(related_table, where_condition)
if ids.any?
@records_to_export[related_table].merge(ids)
@export_order << related_table unless @export_order.include?(related_table)
puts " Found #{ids.size} #{related_table} records (has_many)" if ENV['DEBUG_COLLECTION']
# Recursively collect related model's relationships for each ID
ids.each do |related_id|
collect_records_recursively(related_table, related_id)
end
else
# Track tables with zero records
@records_to_export[related_table] ||= Set.new
end
end
end
# Collect belongs_to relationships
if model_def[:belongs_to]
model_def[:belongs_to].each do |rel|
related_table = rel[:related_table]
next if related_table == 'organizations' && model_name != 'organizations'
fk_column = rel[:fk_in_this_table]
related_ids = get_related_ids_for_record(model_name, fk_column, record_id)
related_ids.each do |related_id|
next if related_id.blank?
@records_to_export[related_table].add(related_id)
@export_order << related_table unless @export_order.include?(related_table)
puts " Found #{related_table} record #{related_id} (belongs_to)" if ENV['DEBUG_COLLECTION']
# Recursively collect related model's relationships
collect_records_recursively(related_table, related_id)
end
end
end
end
def get_related_record_ids(table_name, where_condition)
begin
# First check if table exists
table_check = @conn.exec("SELECT to_regclass('#{table_name}') as exists")
if table_check.first['exists'].nil?
puts " Table #{table_name} doesn't exist, skipping..." if ENV['DEBUG_COLLECTION']
return []
end
pk_column = MODEL_DEFINITIONS.dig(table_name, :pk) || 'id'
# Check if the primary key column actually exists
column_check = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{table_name}' AND column_name = '#{pk_column}'")
if column_check.ntuples == 0
# If specified pk doesn't exist, try to find the actual primary key
actual_pk_query = @conn.exec("SELECT a.attname FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE i.indrelid = '#{table_name}'::regclass AND i.indisprimary")
if actual_pk_query.ntuples > 0
pk_column = actual_pk_query.first['attname']
else
puts "Warning: No primary key found for #{table_name}, using all columns"
# For tables without primary keys (like join tables), select all columns
columns = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{table_name}' ORDER BY ordinal_position").map { |r| r['column_name'] }
pk_column = columns.join(', ')
end
end
query = "SELECT #{pk_column} FROM #{table_name} WHERE #{where_condition}"
puts " Query: #{query}" if ENV['DEBUG_SQL']
result = @conn.exec(query)
if pk_column.include?(',')
# For composite keys, return a simple count or identifier
result.map.with_index { |row, idx| "#{table_name}_#{idx}" }
else
result.map { |row| row[pk_column] }.compact.select { |id| !id.to_s.empty? }
end
rescue PG::UndefinedTable => e
puts " Table #{table_name} doesn't exist: #{e.message}" if ENV['DEBUG_COLLECTION']
[]
rescue PG::Error => e
puts "Database error while fetching record IDs for #{table_name}: #{e.message}" if ENV['DEBUG_COLLECTION']
puts "Query attempted: #{query}" if defined?(query) && ENV['DEBUG_COLLECTION']
puts "Where condition: #{where_condition}" if ENV['DEBUG_COLLECTION']
[]
rescue StandardError => e
puts "Unexpected error while fetching record IDs for #{table_name}: #{e.message}" if ENV['DEBUG_COLLECTION']
puts "Query attempted: #{query}" if defined?(query) && ENV['DEBUG_COLLECTION']
[]
end
end
def export_collected_records
puts "\n=== COLLECTION SUMMARY ==="
total_tables_with_data = 0
total_tables_checked = 0
@records_to_export.each do |table_name, record_ids|
puts "#{table_name}: #{record_ids.size} records"
total_tables_with_data += 1 if record_ids.size > 0
total_tables_checked += 1
end
puts "========================"
puts "Total tables checked: #{total_tables_checked}"
puts "Total tables with data: #{total_tables_with_data}"
# Show missing models from MODEL_DEFINITIONS
missing_models = MODEL_DEFINITIONS.keys - @records_to_export.keys
if missing_models.any?
puts "\n⚠️ MISSING MODELS FROM MODEL_DEFINITIONS:"
missing_models.each_with_index do |model, index|
puts " #{index + 1}. #{model}"
end
else
puts "\n✅ All models from MODEL_DEFINITIONS were processed!"
end
# Show models with zero records
zero_record_models = @records_to_export.select { |table, ids| ids.empty? }.keys
if zero_record_models.any?
puts "\n📭 MODELS WITH ZERO RECORDS:"
zero_record_models.each_with_index do |model, index|
puts " #{index + 1}. #{model}"
end
end
if @skipped_relationships&.any?
puts "\nSKIPPED RELATIONSHIPS:"
@skipped_relationships.uniq.each_with_index do |skip, index|
puts " #{index + 1}. #{skip}"
end
end
puts "========================\n"
@records_to_export.each do |table_name, record_ids|
next if record_ids.empty?
# Check if table actually exists before trying to export
begin
table_check = @conn.exec("SELECT to_regclass('#{table_name}') as exists")
if table_check.first['exists'].nil?
puts "⚠️ Skipping #{table_name}: table doesn't exist in database (#{record_ids.size} records collected)"
next
end
rescue PG::Error => e
puts "⚠️ Skipping #{table_name}: error checking table existence - #{e.message}"
next
end
puts "Exporting #{record_ids.size} records from #{table_name}"
pk_column = MODEL_DEFINITIONS.dig(table_name, :pk) || 'id'
if table_name == 'org_subscription_plans'
where_clause = "WHERE plan_id IN ('#{record_ids.to_a.join("','")}')"
elsif pk_column.include?(',')
# Handle composite primary keys by filtering on known organization columns
if table_name == 'organizations_sports'
where_clause = "WHERE organization_id = #{@org_id}"
else
# For other composite key tables, try to find organization_id column
org_column_check = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{table_name}' AND column_name = 'organization_id'")
if org_column_check.ntuples > 0
where_clause = "WHERE organization_id = #{@org_id}"
puts "Filtering #{table_name} by organization_id for composite primary key"
else
where_clause = ""
puts "Warning: #{table_name} has composite primary key but no organization_id column, exporting all records"
end
end
else
where_clause = "WHERE #{pk_column} IN (#{record_ids.to_a.join(',')})"
end
export_table(table_name, where_clause)
end
generate_sql_statements_for_collected_records
end
def generate_sql_statements_for_collected_records
@records_to_export.each do |table_name, record_ids|
next if record_ids.empty?
# Check if table actually exists before generating SQL
begin
table_check = @conn.exec("SELECT to_regclass('#{table_name}') as exists")
if table_check.first['exists'].nil?
puts " Skipping SQL generation for #{table_name}: table doesn't exist" if ENV['DEBUG_COLLECTION']
next
end
rescue PG::Error => e
puts " Skipping SQL generation for #{table_name}: error checking table - #{e.message}" if ENV['DEBUG_COLLECTION']
next
end
pk_column = MODEL_DEFINITIONS.dig(table_name, :pk) || 'id'
if table_name == 'org_subscription_plans'
where_clause = "WHERE plan_id IN ('#{record_ids.to_a.join("','")}')"
elsif pk_column.include?(',')
# Handle composite primary keys by filtering on known organization columns
if table_name == 'organizations_sports'
where_clause = "WHERE organization_id = #{@org_id}"
else
# For other composite key tables, try to find organization_id column
org_column_check = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{table_name}' AND column_name = 'organization_id'")
if org_column_check.ntuples > 0
where_clause = "WHERE organization_id = #{@org_id}"
else
where_clause = ""
end
end
else
where_clause = "WHERE #{pk_column} IN (#{record_ids.to_a.join(',')})"
end
generate_table_sql(table_name, [where_clause])
end
end
def generate_table_sql(table_name, where_clauses)
columns = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{table_name}' ORDER BY ordinal_position").map { |r| r['column_name'] }
quoted_columns = columns.map { |c| RESERVED_WORDS[c] || c }.join(', ')
csv_file = File.join(@export_dir, "#{table_name}.csv")
File.open(@export_file, 'a') do |f|
f.puts "-- Exporting #{table_name}"
if File.exist?(csv_file) && File.size(csv_file) > 0
where_clauses.each do |where_clause|
if where_clause.present?
f.puts "DELETE FROM #{table_name} #{where_clause};"
else
f.puts "DELETE FROM #{table_name};"
end
end
f.puts "\\COPY #{table_name} (#{quoted_columns}) FROM '#{csv_file}' WITH (FORMAT csv, NULL '\\N');"
end
f.puts ""
end
end
def find_parent_model_key(model_name, model_definitions)
model_def = model_definitions[model_name]
return nil unless model_def
model_def[:belongs_to]&.find { |rel| rel[:related_table] == 'organizations' }&.dig(:fk_in_this_table)
end
def has_organization_relationship?(model_name)
# Check if model has direct organization relationship
model_def = MODEL_DEFINITIONS[model_name]
return false unless model_def
# Check belongs_to relationships
has_direct_org_relation = model_def[:belongs_to]&.any? { |rel| rel[:related_table] == 'organizations' }
# Check if it's a known model with indirect relationships
indirect_models = %w[accounts division_people people]
has_direct_org_relation || indirect_models.include?(model_name)
end
def get_ids_for_model(model_name, fk_column, related_id)
begin
return [related_id] if model_name == 'organizations' && fk_column == 'organization_id'
# Handle case where fk_column is nil or empty
if fk_column.blank?
puts "Warning: No foreign key column found for #{model_name}, skipping..."
return []
end
query = "SELECT id FROM #{model_name} WHERE #{fk_column} = #{related_id}"
result = @conn.exec(query)
result.map { |row| row['id'] }.compact
rescue PG::Error => e
puts "Database error while fetching IDs for #{model_name}: #{e.message}"
puts "Query attempted: SELECT id FROM #{model_name} WHERE #{fk_column} = #{related_id}"
[] # Return empty array as fallback
rescue StandardError => e
puts "Unexpected error while fetching IDs for #{model_name}: #{e.message}"
puts e.backtrace.join("\n")
[] # Return empty array as fallback
end
end
def get_related_ids_for_record(model_name, fk_column, record_id)
begin
# Handle case where fk_column is nil or empty
if fk_column.blank?
puts "Warning: No foreign key column specified for #{model_name}, skipping..."
return []
end
# Skip synthetic record IDs from composite primary key tables
if record_id.to_s.include?("#{model_name}_")
puts " Skipping synthetic record ID #{record_id} for composite key table #{model_name}" if ENV['DEBUG_COLLECTION']
return []
end
# Check if the model has composite primary key
model_def = MODEL_DEFINITIONS[model_name]
if model_def && model_def[:pk]&.include?(',')
puts " Skipping #{model_name} with composite primary key for belongs_to relationship" if ENV['DEBUG_COLLECTION']
return []
end
# Check if the foreign key column exists in the table
column_check = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{model_name}' AND column_name = '#{fk_column}'")
if column_check.ntuples == 0
puts " Column #{fk_column} doesn't exist in #{model_name}, skipping..." if ENV['DEBUG_COLLECTION']
return []
end
# Check if 'id' column exists for WHERE clause
id_column_check = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{model_name}' AND column_name = 'id'")
if id_column_check.ntuples == 0
puts " No 'id' column in #{model_name}, skipping belongs_to relationship" if ENV['DEBUG_COLLECTION']
return []
end
query = "SELECT DISTINCT #{fk_column} FROM #{model_name} WHERE id = #{record_id}"
result = @conn.exec(query)
result.map { |row| row[fk_column] }.compact.select { |id| !id.to_s.empty? }
rescue PG::Error => e
puts "Database error while fetching related IDs for #{model_name}.#{fk_column} with record_id #{record_id}: #{e.message}" if ENV['DEBUG_COLLECTION']
[] # Return empty array as fallback
rescue StandardError => e
puts "Unexpected error while fetching related IDs for #{model_name}.#{fk_column} with record_id #{record_id}: #{e.message}" if ENV['DEBUG_COLLECTION']
[] # Return empty array as fallback
end
end
def collect_orphaned_models
puts "Phase 1.6: Collecting orphaned models with organization_id..."
# Check all models in MODEL_DEFINITIONS that weren't collected
orphaned_count = 0
MODEL_DEFINITIONS.each do |model_name, model_def|
# Skip if already collected
next if @records_to_export[model_name]&.any?
# Skip if no primary key defined
next unless model_def[:pk]
begin
# Check if table has organization_id column
column_check = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{model_name}' AND column_name = 'organization_id'")
if column_check.ntuples > 0
# Get records for this organization
pk_column = model_def[:pk]
if pk_column.include?(',')
# Skip composite primary keys for now
puts " Skipping #{model_name} (composite primary key)"
next
end
query = "SELECT #{pk_column} FROM #{model_name} WHERE organization_id = #{@org_id}"
result = @conn.exec(query)
ids = result.map { |row| row[pk_column] }.compact
if ids.any?
@records_to_export[model_name].merge(ids)
@export_order << model_name unless @export_order.include?(model_name)
puts " Collected #{ids.size} orphaned records from #{model_name}"
orphaned_count += ids.size
# Recursively collect relationships for these orphaned records
ids.each do |record_id|
collect_records_recursively(model_name, record_id)
end
end
end
rescue PG::UndefinedTable
puts " Table #{model_name} doesn't exist, skipping..."
next
rescue PG::Error => e
puts " Database error checking #{model_name}: #{e.message}"
next
end
end
puts " Found #{orphaned_count} orphaned records across all models"
end
def export_child_containers(org_id)
# Get body containers
query_body_containers = @conn.exec("SELECT c.id FROM organizations o \
INNER JOIN org_pages p ON p.organization_id = o.id \
INNER JOIN containers c ON c.id = p.body_container_id WHERE o.id = #{org_id}"
)
body_container_ids = query_body_containers.map { |row| row['id'] }
# Get menu containers - this was missing!
query_menu_containers = @conn.exec("SELECT c.id FROM containers c \
WHERE c.menu_organization_id = #{org_id} AND c.type = 'MenuContainer'"
)
menu_container_ids = query_menu_containers.map { |row| row['id'] }
# Combine both container types
all_container_ids = body_container_ids + menu_container_ids
puts "Found #{body_container_ids.size} body containers and #{menu_container_ids.size} menu containers"
puts "Total containers to export: #{all_container_ids.size}"
@saved_ids = all_container_ids.dup # Include root containers in saved_ids
# Skip if no containers to export
return if all_container_ids.empty?
def recursively_export_child_containers(container_ids)
return if container_ids.empty?
count_query = "SELECT COUNT(*) FROM containers WHERE parent_id IN (#{container_ids.join(',')});"
puts "export containers: #{count_query}"
count_result = @conn.exec(count_query)
count = count_result.first['count']
puts "export containers: #{count}"
query = "SELECT * FROM containers WHERE parent_id IN (#{container_ids.join(',')})"
puts "export_child_containers query: #{query}"
query_child_containers = @conn.exec(query + ";")
@conn.exec("COPY (#{query}) TO STDOUT WITH (FORMAT csv, NULL '\\N')")
child_container_ids = query_child_containers.map { |row| row['id'] }
csv_file = File.join(@export_dir, "containers.csv")
# Add all child container IDs to saved_ids (not just the last level)
@saved_ids += child_container_ids if child_container_ids.any?
File.open(csv_file, 'ab') do |f|
while row = @conn.get_copy_data
f.write row
end
end
return if child_container_ids.empty?
recursively_export_child_containers(child_container_ids)
end
recursively_export_child_containers(all_container_ids)
puts "Total containers collected for widget export: #{@saved_ids.size}"
end
def export_widgets(container_ids)
columns = @conn
.exec("SELECT column_name FROM information_schema.columns WHERE table_name = 'widgets' ORDER BY ordinal_position")
.map { |r| r['column_name'] }
quoted_columns = columns.map { |c| RESERVED_WORDS[c] || c }.join(', ')
count_query = "SELECT COUNT(*) as count FROM widgets WHERE widget_container_id IN (#{container_ids.join(',')});"
count_result = @conn.exec(count_query)
count = count_result.first['count']
puts "export widgets count: #{count}"
query = "SELECT * FROM widgets WHERE widget_container_id IN (#{container_ids.join(',')})"
query_widgets = @conn.exec(query + ";")
puts "export_widgets query: #{query}"
@conn.exec("COPY (#{query}) TO STDOUT WITH (FORMAT csv, NULL '\\N')")
csv_file = File.join(@export_dir, "widgets.csv")
File.open(csv_file, 'ab') do |f|
while row = @conn.get_copy_data
f.write row
end
end
File.open(@export_file, 'a') do |f|
f.puts "DELETE FROM widgets WHERE widget_container_id IN (#{container_ids.join(',')});"
f.puts "\\COPY widgets (#{quoted_columns}) FROM '#{csv_file}' WITH (FORMAT csv, NULL '\\N');"
end
end
def export_table(table, where_clause)
columns = @conn.exec("SELECT column_name FROM information_schema.columns WHERE table_name = '#{table}' ORDER BY ordinal_position").map { |r| r['column_name'] }
count_query = "SELECT '#{table}' as table, COUNT(*) as count FROM #{table} #{where_clause};"
count_result = @conn.exec(count_query)
count = count_result.first['count']
puts "count #{table}: #{count}"
File.open(@count_file, 'a') do |f|
f.puts count_query
end
select_query = "SELECT #{columns.map { |c| RESERVED_WORDS[c] || c }.join(', ')} FROM #{table} #{where_clause}"
puts "export_table select_query: #{select_query}"
@conn.exec("COPY (#{select_query}) TO STDOUT WITH (FORMAT csv, NULL '\\N')")
return if count.to_i == 0 || count.nil?
csv_file = File.join(@export_dir, "#{table}.csv")
# Always overwrite the file since we're exporting each table only once
File.open(csv_file, 'wb') do |f|
while row = @conn.get_copy_data
f.write row
end
end
end
end
namespace :migrate do
desc 'Export data and related data for one org at a time'
task :org_export, [:org_id] => :environment do |t, args|
unless args[:org_id].present?
puts "Error: Please provide an organization ID"
puts ""
puts "Usage: rake migrate:org_export[org_id]"
puts ""
puts "Debugging options:"
puts " DEBUG_COLLECTION=1 rake migrate:org_export[org_id] # Show detailed collection tracing"
puts " DEBUG_SQL=1 rake migrate:org_export[org_id] # Show SQL queries being executed"
puts " DEBUG_COLLECTION=1 DEBUG_SQL=1 rake migrate:org_export[org_id] # Enable both"
puts ""
puts "Examples:"
puts " rake migrate:org_export[123]"
puts " DEBUG_COLLECTION=1 rake migrate:org_export[123]"
exit 1
end
begin
exporter = OrganizationExporter.new(args[:org_id])
exporter.connect
exporter.export
rescue => e
puts "Error: #{e.message}"
puts e.backtrace
exit 1
ensure
exporter.disconnect if exporter
end
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment