<?php /*

 Composr
 Copyright (c) Christopher Graham, 2004-2024

 See docs/LICENSE.md for full licensing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  Christopher Graham
 * @package    core
 */

/*
REMEMBER to keep db_export.sh updated too
*/

/**
 * Standard code module initialisation function.
 *
 * @ignore
 */
function init__database_relations()
{
    if (!defined('TABLE_PURPOSE__NORMAL')) {
        define('TABLE_PURPOSE__NORMAL', 0);
        define('TABLE_PURPOSE__NO_BACKUPS', 1); // For some reason we do not backup this
        define('TABLE_PURPOSE__FLUSHABLE', 2); // Flushable because the contents is not HUGELY important. Should not be routinely flushed. Logs, chats, etc - not member settings, etc. Think: "stuff to do before opening a new site that has just gone through testing"
        define('TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE', 4); // Flushable if we're being extra aggressive. Don't set if already has FLUSHABLE set
        define('TABLE_PURPOSE__NO_STAGING_COPY', 8); // For some special reason we don't copy this between staging to live. Don't set if already has FLUSHABLE set
        define('TABLE_PURPOSE__NON_BUNDLED', 16); // Non-bundled. Do not apply this to anything defined in this core file. Applies only to non-bundled tables injected via an override to this file
        define('TABLE_PURPOSE__AUTOGEN_STATIC', 32); // Contents is auto-generated/meta and essentially static, not for merging between sites
        define('TABLE_PURPOSE__MISC_NO_MERGE', 64); // Should not be merged between sites for other unspecified reasons
        define('TABLE_PURPOSE__SUBDATA', 128); // Data which is subsumed under other data when doing a transfer and has some importance but is totally meaningless when taken on its own
        define('TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG', 256); // We won't give the table full handling somewhere under a Resource-fs hook, we'll have a Commandr-fs extended config hook instead
        // -
        define('TABLE_PURPOSE__NOT_KNOWN', 512);
    }
}

/**
 * Find how tables might be ignored for backups etc.
 * This is mainly used for building automated tests that make sure things are consistently implemented.
 *
 * @return array List of tables and their status regarding being ignored for backups etc
 */
function get_table_purpose_flags() : array
{
    return [ // If you change TABLE_PURPOSE__NO_BACKUPS on any of these, you should probably also edit the performance_bloat testTableSize health check
        'addons' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC,
        'addons_dependencies' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC | TABLE_PURPOSE__SUBDATA/*under addons*/,
        'addons_files' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC | TABLE_PURPOSE__SUBDATA/*under addons*/,
        'actionlogs' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'aggregate_type_instances' => TABLE_PURPOSE__NORMAL,
        'alternative_ids' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'attachment_refs' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content> (implied)*/,
        'attachments' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content> (special handling)*/,
        'authors' => TABLE_PURPOSE__NORMAL,
        'autosave' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'award_archive' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under award_types*/,
        'award_types' => TABLE_PURPOSE__NORMAL,
        'banned_ip' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'banner_clicks' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under banners*/,
        'banner_types' => TABLE_PURPOSE__NORMAL,
        'banners' => TABLE_PURPOSE__NORMAL,
        'banners_types' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under banners*/,
        'blocks' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC,
        'cache' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'cache_on' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__AUTOGEN_STATIC,
        'cached_comcode_pages' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'calendar_events' => TABLE_PURPOSE__NORMAL,
        'calendar_interests' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'calendar_jobs' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__MISC_NO_MERGE/*ephemeral*/ | TABLE_PURPOSE__SUBDATA/*under calendar_events*/,
        'calendar_reminders' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under calendar_events*/,
        'calendar_types' => TABLE_PURPOSE__NORMAL,
        'captchas' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'catalogue_cat_treecache' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under catalogues*/,
        'catalogue_categories' => TABLE_PURPOSE__NORMAL,
        'catalogue_childcountcache' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under catalogues*/,
        'catalogue_efv_float' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogue_entries*/,
        'catalogue_efv_integer' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogue_entries*/,
        'catalogue_efv_long' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogue_entries*/,
        'catalogue_efv_long_trans' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogue_entries*/,
        'catalogue_efv_short' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogue_entries*/,
        'catalogue_efv_short_trans' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogue_entries*/,
        'catalogue_entries' => TABLE_PURPOSE__NORMAL,
        'catalogue_entry_linkage' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'catalogue_fields' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under catalogues*/,
        'catalogues' => TABLE_PURPOSE__NORMAL,
        'chat_active' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'chat_blocking' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'chat_events' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'chat_friends' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'chat_messages' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under chat_rooms*/,
        'chat_rooms' => TABLE_PURPOSE__NORMAL,
        'chat_sound_effects' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'comcode_pages' => TABLE_PURPOSE__NORMAL,
        'config' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_STAGING_COPY/*has-special-Commandr-fs-hook*/,
        'content_privacy__members' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'content_privacy' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'content_regions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'content_reviews' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'cron_caching_requests' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'cron_progression' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'post_tokens' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'custom_comcode' => TABLE_PURPOSE__NORMAL,
        'staff_checklist_cus_tasks' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'db_meta' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC,
        'db_meta_indices' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC,
        'digestives_consumed' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__MISC_NO_MERGE/*ephemeral*/,
        'digestives_tin' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__MISC_NO_MERGE/*ephemeral*/,
        'download_categories' => TABLE_PURPOSE__NORMAL,
        'download_downloads' => TABLE_PURPOSE__NORMAL,
        'download_licences' => TABLE_PURPOSE__NORMAL,
        'download_logging' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under download_downloads*/,
        'edit_pings' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'email_bounces' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'escrow' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members and points_ledger*/,
        'escrow_logs' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under escrow*/,
        'f_custom_fields' => TABLE_PURPOSE__NORMAL,
        'f_emoticons' => TABLE_PURPOSE__NORMAL,
        'f_forum_groupings' => TABLE_PURPOSE__NORMAL,
        'f_forum_intro_ip' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under f_forums*/,
        'f_forum_intro_member' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under f_forums*/,
        'f_forums' => TABLE_PURPOSE__NORMAL,
        'f_group_approvals' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_groups*/,
        'f_group_join_log' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under f_groups*/,
        'f_group_member_timeouts' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_group_members' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_groups' => TABLE_PURPOSE__NORMAL,
        'f_invites' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'f_member_cpf_perms' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_member_custom_fields' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_member_known_login_ips' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_members' => TABLE_PURPOSE__NORMAL,
        'f_moderator_logs' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'f_multi_moderations' => TABLE_PURPOSE__NORMAL,
        'f_password_history' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_poll_answers' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_topics*/,
        'f_poll_votes' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_topics*/,
        'f_polls' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*f_topics*/,
        'f_post_templates' => TABLE_PURPOSE__NORMAL,
        'f_posts' => TABLE_PURPOSE__NORMAL,
        'f_read_logs' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_saved_warnings' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'f_special_pt_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_topics*/,
        'f_topics' => TABLE_PURPOSE__NORMAL,
        'f_usergroup_sub_mails' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_usergroup_subs*/,
        'f_usergroup_subs' => TABLE_PURPOSE__NORMAL,
        'f_warnings' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'f_warnings_punitive' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_warnings*/,
        'f_welcome_emails' => TABLE_PURPOSE__NORMAL,
        'failedlogins' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'feature_lifetime_monitor' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__MISC_NO_MERGE/*ephemeral*/,
        'filedump' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*subsumed within filedump hook when it finds files*/,
        'galleries' => TABLE_PURPOSE__NORMAL,
        'group_category_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'group_page_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'group_privileges' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'group_zone_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under zones*/,
        'hackattack' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'images' => TABLE_PURPOSE__NORMAL,
        'import_id_remap' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under import_session*/,
        'import_parts_done' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under import_session*/,
        'import_session' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'incoming_uploads' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'ecom_invoices' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'ip_country' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__AUTOGEN_STATIC,
        'leader_board' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'leader_boards' => TABLE_PURPOSE__NORMAL,
        'leader_boards_groups' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA,
        'daily_visits' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'logged_mail_messages' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'match_key_messages' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'member_category_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'member_page_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'member_privileges' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'member_tracking' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'member_zone_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under zones*/,
        'menu_items' => TABLE_PURPOSE__NORMAL,
        'messages_to_render' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'modules' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC,
        'news' => TABLE_PURPOSE__NORMAL,
        'news_categories' => TABLE_PURPOSE__NORMAL,
        'news_category_entries' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under news*/,
        'news_rss_cloud' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/ | TABLE_PURPOSE__SUBDATA/*under news*/,
        'newsletter_archive' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under newsletters*/,
        'newsletter_drip_send' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'newsletter_periodic' => TABLE_PURPOSE__NORMAL,
        'newsletter_subscribe' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under newsletters*/,
        'newsletter_subscribers' => TABLE_PURPOSE__NORMAL,
        'newsletters' => TABLE_PURPOSE__NORMAL,
        'notification_lockdown' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'notifications_enabled' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'points_ledger' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'poll' => TABLE_PURPOSE__NORMAL,
        'poll_votes' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under poll*/,
        'ecom_prods_prices' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'privilege_list' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AUTOGEN_STATIC,
        'ecom_prods_custom' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'ecom_prods_permissions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'quiz_entries' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under quizzes*/,
        'quiz_entry_answer' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under quizzes*/,
        'quiz_member_last_visit' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under quizzes*/,
        'quiz_question_answers' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under quizzes*/,
        'quiz_questions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under quizzes*/,
        'quiz_winner' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under quizzes*/,
        'quizzes' => TABLE_PURPOSE__NORMAL,
        'rating' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'redirects' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'review_supplement' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'revisions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__SUBDATA/*under <lots>*/,
        'ecom_sales' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'searches_logged' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'seo_meta' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'seo_meta_keywords' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'sessions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'shopping_cart' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'shopping_logging' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'shopping_orders' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'site_messages' => TABLE_PURPOSE__NORMAL,
        'site_messages_pages' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under site_messages*/,
        'site_messages_groups' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under site_messages*/,
        'ecom_trans_addresses' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/ | TABLE_PURPOSE__SUBDATA/*under shopping_orders*/,
        'shopping_order_details' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/ | TABLE_PURPOSE__SUBDATA/*under shopping_orders*/,
        'sitemap_cache' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'sms_log' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'staff_tips_dismissed' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'staff_links' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'stats' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'stats_events' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'stats_preprocessed' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'stats_preprocessed_flat' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'stats_kpis' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'stats_known_events' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'stats_known_tracking' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'stats_known_links' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'stats_link_tracker' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'ecom_subscriptions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'task_queue' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__MISC_NO_MERGE/*ephemeral*/,
        'temp_block_permissions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'theme_images' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_STAGING_COPY/*as can deal in files*/,
        'theme_screen_tree' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'theme_template_relations' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'ticket_extra_access' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_topics*/,
        'ticket_known_emailers' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'ticket_types' => TABLE_PURPOSE__NORMAL,
        'tickets' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under f_topics*/,
        'trackbacks' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'ecom_trans_expecting' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'ecom_transactions' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE,
        'translate' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under <lots>*/,
        'tutorial_links' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE | TABLE_PURPOSE__AUTOGEN_STATIC,
        'unbannable_ip' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'unsubscribed_emails' => TABLE_PURPOSE__NORMAL,
        'url_id_monikers' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/ | TABLE_PURPOSE__SUBDATA/*under <content>*/,
        'url_title_cache' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'urls_checked' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__NO_BACKUPS | TABLE_PURPOSE__FLUSHABLE,
        'usersonline_track' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'usersubmitban_member' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__SUBDATA/*under f_members*/,
        'values' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'values_elective' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'video_transcoding' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'videos' => TABLE_PURPOSE__NORMAL,
        'webstandards_checked_once' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'wiki_children' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__SUBDATA/*under wiki_pages*/,
        'wiki_pages' => TABLE_PURPOSE__NORMAL,
        'wiki_posts' => TABLE_PURPOSE__NORMAL,
        'wordfilter' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__AS_COMMANDER_FS_EXTENDED_CONFIG,
        'zones' => TABLE_PURPOSE__NORMAL,
        'ecom_sales_expecting' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE_AGGRESSIVE | TABLE_PURPOSE__MISC_NO_MERGE/*too-site-tied*/,
        'ce_fulltext_index' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'cpages_fulltext_index' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'f_posts_fulltext_index' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'f_pposts_fulltext_index' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
        'ft_index_commonality' => TABLE_PURPOSE__NORMAL | TABLE_PURPOSE__FLUSHABLE,
    ];
}

/**
 * Find if a table must be ignored for backups or whatever flag(s).
 *
 * @param  string $table The table name
 * @param  integer $flag A particular flag(s)
 * @return boolean Whether the flag(s) exists (at least one)
 */
function table_has_purpose_flag(string $table, int $flag) : bool
{
    static $flags = null;
    if ($flags === null) {
        $flags = get_table_purpose_flags();
    }

    if (!isset($flags[$table])) {
        return ($flag & TABLE_PURPOSE__NOT_KNOWN) != 0;
    }

    $real_flag = $flags[$table];
    return ($real_flag & $flag) != 0;
}

/**
 * Get a map of table descriptions.
 *
 * @return array Map of table descriptions
 */
function get_table_descriptions() : array
{
    return [
        'actionlogs' => 'stores logs of actions performed on the website',
        'addons' => 'registry of installed addons, including the version that was installed (used for auto-upgrading)',
        'addons_dependencies' => 'files for installed addons (not all installed addons have addon_registry hooks, and not all installed addons have this table populated)',
        'addons_files' => 'files for installed addons (not all installed addons have addon_registry hooks, and not all installed addons have this table populated)',
        'alternative_ids' => 'different sets of IDs for a database ID, allowing more robust cross-site or label based content referencing',
        'attachments' => 'attachments referenced by Comcode (images, downloads, videos, etc)',
        'attachment_refs' => 'stores references of what content uses what attachments (it allows attachment permissions to work, as it tells the software what \'owner\' content to verify permissions against)',
        'authors' => 'stores a list of author profiles mapped to their members',
        'autosave' => 'stores unsaved form data in case of browser crashes, called by AJAX',
        'award_archive' => 'stores a list of top content awards that were granted',
        'banned_ip' => 'list of banned IP addresses (the software will use .htaccess also if it can, to improve performance)',
        'banner_clicks' => 'stores clicks made on banners and their associated member and IP address',
        'banners' => 'stores banners in the system, some of which have been purchased by members',
        'blocks' => 'registry of all installed blocks, including the version that was installed (used for auto-upgrading)',
        'cache' => 'data caching, especially block caching',
        'cache_on' => 'a registry of what cacheable things are cached by what parameters',
        'calendar_events' => 'stores calendar events, some of which may be mapped to a specific member\'s personal calendar',
        'calendar_reminders' => 'stores subscriptions by members to calendar events for reminders of those events',
        'calendar_interests' => 'stores members who indicated they are interested in particular calendar events',
        'calendar_jobs' => 'stores system command job meta for calendar events meant to trigger a system command',
        'captchas' => 'stores CAPTCHA image expectations, so the software can check what they entered against what they were asked to enter',
        'catalogue_entries' => 'stores entries made into catalogues',
        'chat_active' => 'maintains a record of which members are active in which chats',
        'chat_events' => 'stores specific chat events that occur such as members joining or leaving',
        'chat_blocking' => 'a record of which members blocked which other members from chatting with them',
        'chat_friends' => 'a record of which members are friends with which other members',
        'chat_messages' => 'stores messages sent in chat and metadata about the message',
        'chat_rooms' => 'stores the available chat rooms and their settings',
        'chat_sound_effects' => 'stores chat sound effect settings for members',
        'comcode_pages' => 'stores Comcode page metadata',
        'config' => 'all the configuration settings that have been saved',
        'content_privacy__members' => 'sets content privacy',
        'content_regions' => 'sets the regions content may be viewed from',
        'cron_caching_requests' => 'requests made by the system to load a block cache in the background',
        'cron_progression' => 'used to track and configure system scheduler hooks',
        'daily_visits' => 'tracks daily visits from site members',
        'digestives_consumed' => 'tracks when member digests were sent out and when they should be sent out next',
        'digestives_tin' => 'stores member notifications',
        'download_downloads' => 'stores available files for downloading and their metadata',
        'download_logging' => 'stores records of those who download files from the downloads system',
        'ecom_invoices' => 'stores a record of invoices generated through the eCommerce system',
        'ecom_sales' => 'stores a record of purchases made through the eCommerce system',
        'ecom_subscriptions' => 'maintains a list of subscriptions (recurring sales) placed in the eCommerce system',
        'ecom_transactions' => 'a master ledger of every financial transaction made through the eCommerce system',
        'ecom_trans_addresses' => 'stores a record of physical addresses for applicable transactions (e.g. shipping or billing)',
        'ecom_trans_expecting' => 'a list of pending transactions we are expecting to receive responses from payment gateways',
        'edit_pings' => 'used to stop people editing the same thing at the same time (AJAX)',
        'email_bounces' => 'a log of emails that the website software tried to send but bounced back',
        'escrow' => 'a log of point escrows between members',
        'escrow_logs' => 'a sub-log of actions taken on each escrow',
        'f_forums' => 'stores a list of forums in Conversr for which members can create topics',
        'f_forum_intro_ip' => 'stores a record of IP addresses who completed a forum\'s introduction',
        'f_forum_intro_member' => 'stores a record of members who completed a forum\'s introduction',
        'f_group_approvals' => 'a record of group join requests and whether they were approved',
        'f_group_join_log' => 'a log of members joining groups',
        'f_group_members' => 'maintains a list of current group memberships',
        'f_group_member_timeouts' => 'maintains a list of members who have temporary memberships to groups and when they expire',
        'f_groups' => 'stores the configured usergroups for the site',
        'f_invites' => 'stores a record of invitations sent out by members for others to join the site',
        'f_members' => 'stores a record of registered members, basic information, and cached forum stats',
        'f_member_cpf_perms' => 'maintains member permissions for each custom profile field',
        'f_member_custom_fields' => 'stores values for member custom profile fields',
        'f_member_known_login_ips' => 'maintains a record of known IP addresses used by members when logging in',
        'f_moderator_logs' => 'stores a log of moderator actions taken pertaining to Conversr',
        'f_poll_votes' => 'stores a record of votes placed on topic polls',
        'f_password_history' => 'maintains a record of member passwords used (hashed and salted)',
        'f_posts' => 'stores every post made in Conversr and metadata',
        'f_read_logs' => 'maintains a record of which forums / topics members have read so Conversr can inform them of unread ones',
        'f_special_pt_access' => 'a list of members who have been invited or granted access to specific private topics',
        'f_topics' => 'stores a list of topics within Conversr forums for which members can create posts',
        'f_warnings' => 'a ledger of warnings issued against members',
        'f_warnings_punitive' => 'a ledger of individual punitive actions taken for a warning',
        'failedlogins' => 'a log of all failed logins',
        'filedump' => 'stores metadata for folders within the filedump',
        'galleries' => 'stores every gallery available on the site where multimedia can be submitted',
        'group_category_access' => 'defines what groups may access what categories',
        'group_page_access' => 'defines what groups may access what pages',
        'group_privileges' => 'defines what groups have what privileges',
        'group_zone_access' => 'defines what groups may access what zones',
        'hackattack' => 'stores a record of hack attempts on the site',
        'images' => 'maintains a list of images within galleries and their metadata',
        'incoming_uploads' => 'temporary storage of uploaded files, before main form submission',
        'leader_board' => 'stores result sets for leader_boards',
        'leader_boards' => 'stores leader-boards for displaying top members by points in a block',
        'leader_boards_groups' => 'relational database to store which usergroups leader_boards should use for filtering',
        'logged_mail_messages' => 'logged emails (so you can check incorrect emails aren\'t going out) / email queuing',
        'member_category_access' => 'defines what members may access what categories (rarely used, no admin UI)',
        'member_page_access' => 'defines what members may access what pages (rarely used, no admin UI)',
        'member_privileges' => 'defines what members have what privileges (rarely used, no admin UI)',
        'member_tracking' => 'tracks the locations of online users',
        'member_zone_access' => 'defines what members may access what zones (rarely used, no admin UI)',
        'menu_items' => 'stores all the items shown on menus (except auto-generated ones)',
        'messages_to_render' => 'stores messages that have been queued up for display on a members browser (e.g. if they have just been redirected after completing something, so a status message will be queued for display after they\'ve been redirected)',
        'modules' => 'registry of all installed modules, including the version that was installed (used for auto-upgrading)',
        'news' => 'stores a list of news articles and blogs submitted to the site',
        'news_categories' => 'stores a list of categories for which news and blogs can be submitted',
        'newsletter_archive' => 'maintains an archive of newsletters created',
        'newsletter_drip_send' => 'keeps track of the progress of sending out a newsletter',
        'newsletter_periodic' => 'maintains a list of auto-generated newsletters which are populated with the latest content',
        'newsletter_subscribe' => 'maintains a list of newsletters to which an e-mail address subscribed',
        'newsletter_subscribers' => 'maintains a list of e-mails and names who subscribed to one or more newsletters',
        'notifications_enabled' => 'what notifications members receive',
        'points_ledger' => 'a log of every point transaction that occurred on the site',
        'poll' => 'stores a list of website polls',
        'poll_votes' => 'stores votes made to website polls',
        'post_tokens' => 'a record of CSRF tokens usually for form submission',
        'privilege_list' => 'a list of all the privileges available (aka privileges)',
        'quizzes' => 'stores quizzes and surveys which can be taken / completed by members',
        'quiz_entries' => 'stores entries made to quizzes / surveys',
        'quiz_member_last_visit' => 'records when a member last opened a quiz / survey',
        'rating' => 'stores ratings for all kinds of content (rating_for_type determines what kind of content, rating_for_id determines what ID of content within that type)',
        'review_supplement' => 'stores reviews for all kinds of content',
        'revisions' => 'used to store old versions of content (any content type that chooses to support revisions)',
        'searches_logged' => 'logs searches performed on the site',
        'seo_meta' => 'stores meta descriptions for all kinds of content',
        'seo_meta_keywords' => 'stores meta keywords for all kinds of content',
        'sessions' => 'stores user sessions, for guests and members (session ID\'s are treated with high security)',
        'shopping_cart' => 'maintains shopping carts of members and the items in them',
        'shopping_logging' => 'stores a log of shopping cart activity',
        'shopping_orders' => 'stores a record of orders placed by members',
        'site_messages' => 'maintains global / pinned messages to be shown to certain members on certain pages at certain times',
        'sitemap_cache' => 'a cache of all addressable sitemap nodes for building out the full XML Sitemaps across multiple files iteratively, which is extremely intensive on large sites',
        'sms_log' => 'logs what SMS messages were sent out on behalf of what users and when',
        'staff_tips_dismissed' => 'stores what webmaster tips (Admin Zone dashboard) have been read so far',
        'task_queue' => 'stores a list of background tasks pending to be run',
        'ticket_extra_access' => 'maintains a list of additional members who have access to a ticket',
        'ticket_known_emailers' => 'stores a record of e-mail addresses who sent an e-mail to the tickets system mapped to the member',
        'trackbacks' => 'stores trackbacks for all kinds of content',
        'translate' => 'very important table, stores most of the text; this table exists to internationalise content and also to store compiled Comcode',
        'tutorial_links' => 'used by the software documentation, don\'t worry about this table',
        'unbannable_ip' => 'maintains a list of IP addresses which should never be banned',
        'unsubscribed_emails' => 'maintains a list of e-mail addresses which opted out of receiving any mail from the site so that they are never mailed',
        'urls_checked' => 'stores whether URLs exist, may be used by any system within the software',
        'url_id_monikers' => 'stores search-engine-friendly URL codes for all kinds of content (we call these "monikers")',
        'url_title_cache' => 'stores the HTML titles for URLs, used in particular by the Comcode parser when it auto-detects links, and the media rendering system',
        'usersubmitban_member' => 'list of members who have been banned from submitting content',
        'values' => 'arbitrary store of data values (mapping of keys to values)',
        'values_elective' => 'arbitrary store of lengthy/elective data values (mapping of keys to values)',
        'videos' => 'maintains a list of videos / multimedia within galleries and their metadata',
        'webstandards_checked_once' => 'this is used by the inbuilt XHTML checker to know what markup it has already checked, so it doesn\'t waste a lot of time re-checking the same stuff; it uses a hash-signature-check so it doesn\'t need to store all data in the table',
        'wiki_pages' => 'maintains a list of pages and their content in the site wiki',
        'wiki_posts' => 'stores every post made on a wiki page',
        'zones' => 'details of all zones on the website',
        'ecom_sales_expecting' => 'stores details of an in-progress purchase',
        'stats' => 'stores basic hit logging',
        'stats_events' => 'stores statistics of events that have happened such as contact forms being filled in',
        'stats_kpis' => 'configured stats KPIs and targets',
        'stats_preprocessed' => 'stores long-term data for quick processing of statistics',
        'stats_preprocessed_flat' => 'stores long-term data for quick processing of statistics, for data where no dates are involved',
        'stats_known_events' => 'keeps a record of all the event (types) that have happened',
        'stats_known_tracking' => 'keeps a record of all the tracking codes that have been seen',
        'stats_known_links' => 'keeps a record of all the tracked links that have been seen',
        'stats_link_tracker' => 'outgoing click tracking (not really used much)',
        'ce_fulltext_index' => 'search indexing for catalogue entries',
        'cpages_fulltext_index' => 'search indexing for Comcode pages',
        'f_posts_fulltext_index' => 'search indexing for forum posts',
        'f_pposts_fulltext_index' => 'search indexing for forum posts in private topics',
        'ft_index_commonality' => 'search indexing keyword stats',
    ];
}

/**
 * Get a map of foreign key relations, for a specific table.
 *
 * @param  string $table A particular table
 * @return array Map of foreign key relations
 */
function get_relation_map_for_table(string $table) : array
{
    $relation_map = get_relation_map();
    $new_relation_map = [];
    foreach ($relation_map as $from => $to) {
        if ($to !== null) {
            list($from_table, $from_field) = explode('.', $from, 2);
            if ($table == $from_table) {
                list($to_table, $to_field) = explode('.', $to, 2);
                $new_relation_map[$from_field] = [$to_table, $to_field];
            }
        }
    }
    return $new_relation_map;
}

/**
 * Get a map of foreign key relations.
 *
 * @return array Map of foreign key relations
 */
function get_relation_map() : array
{
    return [
        'actionlogs.warning_id' => 'f_warnings.id',
        'attachment_refs.a_id' => 'attachments.id',
        'attachment_refs.r_referer_id' => null,
        'award_archive.a_type_id' => 'award_types.id',
        'banners.b_type' => 'banner_types.id',
        'banners_types.name' => 'banners.name',
        'banners_types.b_type' => 'banner_types.id',
        'cached_comcode_pages.the_zone' => 'zones.zone_name',
        'calendar_events.e_type' => 'calendar_types.id',
        'calendar_interests.t_type' => 'calendar_types.id',
        'calendar_jobs.j_event_id' => 'calendar_events.id',
        'calendar_jobs.j_reminder_id' => 'calendar_reminders.id',
        'calendar_reminders.e_id' => 'calendar_events.id',
        'catalogue_categories.cc_move_target_id' => 'catalogue_categories.id',
        'catalogue_categories.cc_parent_id' => 'catalogue_categories.id',
        'catalogue_categories.c_name' => 'catalogues.c_name',
        'catalogue_cat_treecache.cc_ancestor_id' => 'catalogue_categories.id',
        'catalogue_cat_treecache.cc_id' => 'catalogue_categories.id',
        'catalogue_childcountcache.cc_id' => 'catalogue_categories.id',
        'catalogue_efv_float.ce_id' => 'catalogue_entries.id',
        'catalogue_efv_float.cf_id' => 'catalogue_fields.id',
        'catalogue_efv_integer.ce_id' => 'catalogue_entries.id',
        'catalogue_efv_integer.cf_id' => 'catalogue_fields.id',
        'catalogue_efv_long.ce_id' => 'catalogue_entries.id',
        'catalogue_efv_long.cf_id' => 'catalogue_fields.id',
        'catalogue_efv_long_trans.ce_id' => 'catalogue_entries.id',
        'catalogue_efv_long_trans.cf_id' => 'catalogue_fields.id',
        'catalogue_efv_short.ce_id' => 'catalogue_entries.id',
        'catalogue_efv_short.cf_id' => 'catalogue_fields.id',
        'catalogue_efv_short_trans.ce_id' => 'catalogue_entries.id',
        'catalogue_efv_short_trans.cf_id' => 'catalogue_fields.id',
        'catalogue_entries.cc_id' => 'catalogue_categories.id',
        'catalogue_entries.c_name' => 'catalogues.c_name',
        'catalogue_entry_linkage.catalogue_entry_id' => 'catalogue_entries.id',
        'catalogue_fields.c_name' => 'catalogues.c_name',
        'chat_active.room_id' => 'chat_rooms.id',
        'chat_events.e_room_id' => 'chat_rooms.id',
        'chat_messages.room_id' => 'chat_rooms.id',
        'comcode_pages.the_zone' => 'zones.zone_name',
        'download_categories.parent_id' => 'download_categories.id',
        'download_downloads.category_id' => 'download_categories.id',
        'download_downloads.download_licence_id' => 'download_licences.id',
        'download_downloads.out_mode_id' => 'download_downloads.id',
        'download_logging.id' => 'download_downloads.id',
        'escrow.original_points_ledger_id' => 'points_ledger.id',
        'escrow_logs.escrow_id' => 'escrow.id',
        'f_forums.f_cache_last_forum_id' => 'f_forums.id',
        'f_forums.f_cache_last_topic_id' => 'f_topics.id',
        'f_forums.f_forum_grouping_id' => 'f_forum_groupings.id',
        'f_forums.f_parent_forum_id' => 'f_forums.id',
        'f_forum_intro_ip.i_forum_id' => 'f_forums.id',
        'f_forum_intro_member.i_forum_id' => 'f_forums.id',
        'f_group_approvals.ga_old_group_id' => 'f_groups.id',
        'f_group_approvals.ga_new_group_id' => 'f_groups.id',
        'f_group_join_log.usergroup_id' => 'f_groups.id',
        'f_member_cpf_perms.field_id' => 'f_custom_fields.id',
        'f_moderator_logs.l_warning_id' => 'f_warnings.id',
        'f_multi_moderations.mm_move_to_forum_id' => 'f_forums.id',
        'f_poll_answers.pa_poll_id' => 'f_polls.id',
        'f_poll_votes.pv_answer_id' => 'f_poll_answers.id',
        'f_poll_votes.pv_poll_id' => 'f_polls.id',
        'f_posts.p_cache_forum_id' => 'f_forums.id',
        'f_posts.p_parent_id' => 'f_posts.id',
        'f_posts.p_topic_id' => 'f_topics.id',
        'f_read_logs.l_topic_id' => 'f_topics.id',
        'f_special_pt_access.s_topic_id' => 'f_topics.id',
        'f_topics.t_cache_first_post_id' => 'f_posts.id',
        'f_topics.t_cache_last_post_id' => 'f_posts.id',
        'f_topics.t_forum_id' => 'f_forums.id',
        'f_topics.t_poll_id' => 'f_polls.id',
        'f_usergroup_sub_mails.m_usergroup_sub_id' => 'f_usergroup_subs.id',
        'f_warnings.w_topic_id' => 'f_topics.id',
        'f_warnings_punitive.p_warning_id' => 'f_warnings.id',
        'f_welcome_emails.w_newsletter_id' => 'newsletters.id',
        'f_welcome_emails.w_usergroup' => 'f_groups.id',
        'galleries.g_owner' => 'f_members.id',
        'galleries.parent_id' => 'galleries.name',
        'group_category_access.category_name' => null,
        'group_page_access.zone_name' => 'zones.zone_name',
        'group_privileges.category_name' => null,
        'group_privileges.privilege' => 'privilege_list.the_name',
        'group_privileges.the_page' => 'modules.module_the_name',
        'group_zone_access.zone_name' => 'zones.zone_name',
        'images.cat' => 'galleries.name',
        'import_id_remap.id_new' => null,
        'import_id_remap.id_old' => null,
        'import_id_remap.id_session' => 'import_session.imp_session',
        'import_parts_done.imp_session' => 'import_session.imp_session',
        'leader_board.lb_leader_board_id' => 'leader_boards.id',
        'leader_boards_groups.lb_leader_board_id' => 'leader_boards.id',
        'leader_boards_groups.lb_group' => 'f_groups.id',
        'member_category_access.category_name' => null,
        'member_page_access.page_name' => 'modules.module_the_name',
        'member_page_access.zone_name' => 'zones.zone_name',
        'member_privileges.category_name' => null,
        'member_privileges.privilege' => 'privilege_list.the_name',
        'member_privileges.the_page' => 'modules.module_the_name',
        'member_zone_access.zone_name' => 'zones.zone_name',
        'menu_items.i_parent_id' => 'menu_items.id',
        'messages_to_render.r_session_id' => 'sessions.the_session',
        'news.news_category' => 'news_categories.id',
        'newsletter_subscribe.newsletter_id' => 'newsletters.id',
        'news_category_entries.news_entry' => 'news.id',
        'news_category_entries.news_entry_category' => 'news_categories.id',
        'notifications_enabled.l_code_category' => null,
        'points_ledger.linked_ledger_id' => 'points_ledger.id',
        'poll_votes.v_poll_id' => 'poll.id',
        'ecom_prods_permissions.p_category' => null,
        'ecom_prods_permissions.p_page' => 'modules.module_the_name',
        'ecom_prods_permissions.p_privilege' => 'privilege_list.the_name',
        'ecom_prods_permissions.p_zone' => 'zones.zone_name',
        'quizzes.q_newsletter_id' => 'newsletters.id',
        'quiz_entries.q_quiz_id' => 'quizzes.id',
        'quiz_entry_answer.q_entry_id' => 'quiz_entries.id',
        'quiz_entry_answer.q_question_id' => 'quiz_questions.id',
        'quiz_member_last_visit.v_quiz_id' => 'quizzes.id',
        'quiz_questions.q_quiz_id' => 'quizzes.id',
        'quiz_question_answers.q_question_id' => 'quiz_questions.id',
        'quiz_winner.q_entry_id' => 'quiz_entries.id',
        'quiz_winner.q_quiz_id' => 'quizzes.id',
        'rating.rating_for_id' => 'modules.module_the_name',
        'redirects.r_from_zone' => 'zones.zone_name',
        'redirects.r_to_zone' => 'zones.zone_name',
        'review_supplement.r_post_id' => 'f_posts.id',
        'review_supplement.r_rating_for_id' => 'modules.module_the_name',
        'review_supplement.r_topic_id' => 'f_topics.id',
        'revisions.r_actionlog_id' => 'actionlogs.id',
        'revisions.r_moderatorlog_id' => 'f_moderator_logs.id',
        'seo_meta.meta_for_id' => null,
        'sessions.the_zone' => 'zones.zone_name',
        'shopping_cart.ordering_member' => 'f_members.id',
        'site_messages_groups.message_id' => 'site_messages.id',
        'site_messages_groups.group_id' => 'f_groups.id',
        'site_messages_pages.message_id' => 'site_messages.id',
        'temp_block_permissions.p_session_id' => 'sessions.the_session',
        'tickets.forum_id' => 'f_forums.id',
        'tickets.ticket_type' => 'ticket_types.id',
        'tickets.topic_id' => 'f_topics.id',
        'trackbacks.trackback_for_id' => null,
        'url_id_monikers.m_resource_id' => null,
        'url_id_monikers.m_resource_page' => 'modules.module_the_name',
        'videos.cat' => 'galleries.name',
        'video_transcoding.t_local_id' => 'videos.id',
        'wiki_children.child_id' => 'wiki_pages.id',
        'wiki_children.parent_id' => 'wiki_pages.id',
        'wiki_posts.page_id' => 'wiki_pages.id',
        'newsletter_drip_send.d_message_id' => 'newsletter_archive.id',
        'shopping_cart.type_code' => 'catalogue_entries.id',
        'ecom_trans_expecting.e_session_id' => 'sessions.the_session',
        'ecom_trans_addresses.a_trans_expecting_id' => 'ecom_trans_expecting.id',
        'ecom_trans_addresses.a_txn_id' => 'ecom_transactions.id',
        'shopping_order_details.p_order_id' => 'shopping_orders.id',
        'shopping_order_details.p_type_code' => 'catalogue_entries.id',
        'ecom_transactions.t_parent_txn_id' => 'ecom_transactions.id',
        'ecom_transactions.t_session_id' => 'sessions.the_session',
        'ce_fulltext_index.i_category_id' => 'catalogue_categories.id',
        'ce_fulltext_index.i_catalogue_entry_id' => 'catalogue_entries.id',
        'f_posts_fulltext_index.i_forum_id' => 'f_forums.id',
        'f_posts_fulltext_index.i_post_id' => 'f_posts.id',
        'f_pposts_fulltext_index.i_post_id' => 'f_posts.id',
    ];
}

/**
 * Get a list of the defined tables.
 *
 * @param  object $db Database connector to look in
 * @return array The tables
 */
function find_all_tables(object $db) : array
{
    $fields = $db->query_select('db_meta', ['m_table', 'm_name', 'm_type'], [], 'ORDER BY m_table');
    $tables = [];
    foreach ($fields as $field) {
        if (!isset($tables[$field['m_table']])) {
            $tables[$field['m_table']] = [];
        }
        $tables[$field['m_table']][$field['m_name']] = $field['m_type'];
    }
    $tables['db_meta'] = ['m_table' => '*ID_TEXT', 'm_name' => '*ID_TEXT', 'm_type' => 'ID_TEXT'];
    $tables['db_meta_indices'] = ['i_table' => '*ID_TEXT', 'i_name' => '*ID_TEXT', 'i_fields' => '*ID_TEXT'];

    ksort($tables);

    return $tables;
}

/**
 * Get an SQL dump of a database.
 *
 * @param  resource $out_file File to stream into
 * @param  boolean $include_drops Whether to include 'DROP' statements
 * @param  boolean $output_statuses Whether to output status as we go
 * @param  array $skip Array of table names to skip
 * @param  ?array $only Array of only table names to do (null: all)
 * @param  ?object $db Database connector to use (null: site database)
 * @param  ?string $intended_db_type Database driver to use (null: site database driver)
 */
function get_sql_dump($out_file, bool $include_drops = false, bool $output_statuses = false, array $skip = [], ?array $only = null, ?object $db = null, ?string $intended_db_type = null)
{
    disable_php_memory_limit();
    $old_limit = cms_disable_time_limit();
    push_db_scope_check(false);
    push_query_limiting(false);

    require_code('database_helper');

    if ($db === null) {
        $db = $GLOBALS['SITE_DB'];
    }

    if ($intended_db_type === null) {
        $intended_db_type = get_db_type();
    }
    require_code('database/' . filter_naughty_harsh($intended_db_type));
    $db_static = object_factory('Database_Static_' . filter_naughty_harsh($intended_db_type), false, [get_table_prefix()]);

    if (!$db->driver->has_drop_table_if_exists($db->connection_write)) {
        $include_drops = false; // "DROP IF EXISTS" only supported on some DBs.
    }

    // Tables
    $tables = find_all_tables($db);
    foreach ($tables as $table_name => $fields) {
        if (in_array($table_name, $skip)) {
            continue;
        }
        if (($only !== null) && (!in_array($table_name, $only))) {
            continue;
        }

        if ($output_statuses) {
            print('Working out SQL for table: ' . $table_name . "\n");
            cms_flush_safe();
        }

        if ($include_drops) {
            $queries = $db_static->drop_table_if_exists__sql($db->get_table_prefix() . $table_name, $db->connection_write);
            foreach ($queries as $i => $sql) {
                if ($i != 0) {
                    fwrite($out_file, "\n");
                }

                fwrite($out_file, $sql . ";\n");
            }
        }

        $fields_copy = $fields;
        foreach ($fields_copy as $name => $type) {
            if (!multi_lang_content()) {
                if (strpos($type, '_TRANS') !== false) {
                    if (strpos($type, '__COMCODE') !== false) {
                        $fields[$name . '__text_parsed'] = 'LONG_TEXT';
                        $fields[$name . '__source_user'] = 'MEMBER';
                    }

                    $fields[$name] = 'LONG_TEXT'; // In the DB layer, it must now save as such
                }
            }
        }
        $save_bytes = _helper_needs_to_save_bytes($table_name, $fields);
        $queries = $db_static->create_table__sql($db->get_table_prefix() . $table_name, $fields, $db->connection_write, $table_name, $save_bytes);
        foreach ($queries as $i => $sql) {
            if ($i != 0) {
                fwrite($out_file, "\n");
            }

            fwrite($out_file, $sql . ";\n");
        }

        // Indexes
        $indexes = $db->query_select('db_meta_indices', ['*'], ['i_table' => $table_name]);
        foreach ($indexes as $i => $index) {
            if ($i != 0) {
                fwrite($out_file, "\n");
            }

            $index_name = $index['i_name'];

            if ($index_name[0] == '#') {
                $_index_name = substr($index_name, 1);
                $is_full_text = true;
            } else {
                $_index_name = $index_name;
                $is_full_text = false;
            }

            $fields = [];
            foreach (explode(',', $index['i_fields']) as $field_name) {
                $db_type = $GLOBALS['SITE_DB']->query_select_value_if_there('db_meta', 'm_type', ['m_table' => $table_name, 'm_name' => $field_name]);
                $fields[$field_name] = $db_type;
            }

            $_fields = _helper_generate_index_fields($table_name, $fields, $is_full_text);

            if ($_fields !== null) {
                $unique_key_fields = implode(',', _helper_get_table_key_fields($table_name));

                $queries = $db_static->create_index__sql($db->get_table_prefix() . $table_name, $index_name, $_fields, $db->connection_write, $table_name, $unique_key_fields, $db->get_table_prefix());
                foreach ($queries as $sql) {
                    fwrite($out_file, $sql . ";\n");
                }
            }
        }

        // Data
        $start = 0;
        do {
            $data = $db->query_select($table_name, ['*'], [], '', 100, $start, false, []);
            $values_buildup = '';
            foreach ($data as $row_counter => $map) {
                $keys = '';
                $all_values = [];

                foreach ($map as $key => $value) {
                    if ($keys != '') {
                        $keys .= ', ';
                    }
                    $keys .= $key;

                    $_value = (!is_array($value)) ? [$value] : $value;

                    $v = null;
                    foreach ($_value as $i => $v) {
                        if (!array_key_exists($i, $all_values)) {
                            $all_values[$i] = '';
                        }
                        $values = $all_values[$i];

                        if ($values != '') {
                            $values .= ', ';
                        }

                        if ($value === null) {
                            $values .= 'NULL';
                        } else {
                            if (is_float($v)) {
                                $values .= float_to_raw_string($v);
                            } elseif (is_integer($v)) {
                                $values .= strval($v);
                            } else {
                                $values .= '\'' . db_escape_string($v) . '\'';
                            }
                        }

                        $all_values[$i] = $values; // essentially appends, as $values was loaded from former $all_values[$i] value
                    }
                }

                if ((!$db->driver->has_batch_inserts()) || (!isset($data[$row_counter + 1])) || (($row_counter != 0) && ($row_counter % 40) == 0)) {
                    $sql = 'INSERT INTO ' . $db->get_table_prefix() . $table_name . ' (' . $keys . ') VALUES ' . $values_buildup . '(' . $all_values[0] . ')';
                    fwrite($out_file, $sql . ";\n");

                    $values_buildup = '';
                } else {
                    $values_buildup .= '(' . $all_values[0] . '),' . "\n";
                }
            }

            if (!empty($data)) {
                $start += 100;
            }
        } while (!empty($data));

        // Divider, if we put out some data
        if ($start > 0) {
            fwrite($out_file, "\n");
        }
    }

    pop_db_scope_check();
    pop_query_limiting();

    cms_set_time_limit($old_limit);
}
