<commit_before>#!/usr/bin/env perl
#
# Export "media", "feeds", ... table data needed to run a backup crawler
#
# Usage:
#
# 1) On production machine (database that is being exported), run:
#
#     # Export table data to "mediacloud-dump.sql"
#     ./script/run_with_carton.sh ./script/import_export/export_tables_to_backup_crawler.pl > mediacloud-dump.sql
#
# 2) On target machine (e.g. a backup crawler), run:
#
#     # Create database
#     createdb mediacloud
#
#     # Import empty schema
#     psql -f script/mediawords.sql mediacloud
#
#     # Import tables from "mediacloud-dump.sql"
#     psql -v ON_ERROR_STOP=1 -f mediacloud-dump.sql mediacloud
#

use strict;
use warnings;

BEGIN
{
    use FindBin;
    use lib "$FindBin::Bin/../../lib";
}

use Modern::Perl "2015";
use MediaWords::CommonLibs;

use MediaWords::DB;
use Text::CSV_XS;

sub _validate_table_foreign_keys($$)
{
    # If table's constraints aren't right, SQL would be pretty much invalid
    my ( $db, $table ) = @_;

    my $foreign_keys = $db->query(
        <<SQL,
        SELECT
            tc.constraint_name,
            tc.table_schema,
            tc.table_name,
            kcu.column_name,
            ccu.table_schema AS foreign_table_schema,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name

        FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
                ON ccu.constraint_name = tc.constraint_name
        WHERE constraint_type = 'FOREIGN KEY'
          AND tc.table_schema = 'public'
          AND tc.table_name = ?
SQL
        $table
    )->hashes;

    foreach my $foreign_key ( @{ $foreign_keys } )
    {
        my $constraint_name      = $foreign_key->{ constraint_name };
        my $table_schema         = $foreign_key->{ table_schema };
        my $table_name           = $foreign_key->{ table_name };
        my $column_name          = $foreign_key->{ column_name };
        my $foreign_table_schema = $foreign_key->{ foreign_table_schema };
        my $foreign_table_name   = $foreign_key->{ foreign_table_name };
        my $foreign_column_name  = $foreign_key->{ foreign_column_name };

        INFO "Validating foreign key '$constraint_name' for table '$table'...";

        my $sql = "
            SELECT DISTINCT a.$column_name
            FROM $table_schema.$table_name AS a
                LEFT JOIN $foreign_table_schema.$foreign_table_name AS b
                    ON a.$column_name = b.$foreign_column_name
            WHERE a.$column_name IS NOT NULL
              AND b.$foreign_column_name IS NULL
            ORDER BY a.$column_name
        ";

        my $unreferenced_rows = $db->query( $sql )->flat;
        if ( scalar @{ $unreferenced_rows } )
        {
            die "Table '$table' has unreferenced rows for constraint '$constraint_name': " .
              join( ', ', @{ $unreferenced_rows } ) . "; SQL: $sql";
        }

        INFO "Done validating foreign key '$constraint_name' for table '$table'.";
    }<commit_msg>Don't stop validating the rest of the foreign keys on single failure<commit_after>#!/usr/bin/env perl
#
# Export "media", "feeds", ... table data needed to run a backup crawler
#
# Usage:
#
# 1) On production machine (database that is being exported), run:
#
#     # Export table data to "mediacloud-dump.sql"
#     ./script/run_with_carton.sh ./script/import_export/export_tables_to_backup_crawler.pl > mediacloud-dump.sql
#
# 2) On target machine (e.g. a backup crawler), run:
#
#     # Create database
#     createdb mediacloud
#
#     # Import empty schema
#     psql -f script/mediawords.sql mediacloud
#
#     # Import tables from "mediacloud-dump.sql"
#     psql -v ON_ERROR_STOP=1 -f mediacloud-dump.sql mediacloud
#

use strict;
use warnings;

BEGIN
{
    use FindBin;
    use lib "$FindBin::Bin/../../lib";
}

use Modern::Perl "2015";
use MediaWords::CommonLibs;

use MediaWords::DB;
use Text::CSV_XS;

sub _validate_table_foreign_keys($$)
{
    # If table's constraints aren't right, SQL would be pretty much invalid
    my ( $db, $table ) = @_;

    my $foreign_keys = $db->query(
        <<SQL,
        SELECT
            tc.constraint_name,
            tc.table_schema,
            tc.table_name,
            kcu.column_name,
            ccu.table_schema AS foreign_table_schema,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name

        FROM information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
                ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
                ON ccu.constraint_name = tc.constraint_name
        WHERE constraint_type = 'FOREIGN KEY'
          AND tc.table_schema = 'public'
          AND tc.table_name = ?
SQL
        $table
    )->hashes;

    my @foreign_key_errors;

    foreach my $foreign_key ( @{ $foreign_keys } )
    {
        my $constraint_name      = $foreign_key->{ constraint_name };
        my $table_schema         = $foreign_key->{ table_schema };
        my $table_name           = $foreign_key->{ table_name };
        my $column_name          = $foreign_key->{ column_name };
        my $foreign_table_schema = $foreign_key->{ foreign_table_schema };
        my $foreign_table_name   = $foreign_key->{ foreign_table_name };
        my $foreign_column_name  = $foreign_key->{ foreign_column_name };

        INFO "Validating foreign key '$constraint_name' for table '$table'...";

        my $sql = "
            SELECT DISTINCT a.$column_name
            FROM $table_schema.$table_name AS a
                LEFT JOIN $foreign_table_schema.$foreign_table_name AS b
                    ON a.$column_name = b.$foreign_column_name
            WHERE a.$column_name IS NOT NULL
              AND b.$foreign_column_name IS NULL
            ORDER BY a.$column_name
        ";

        my $unreferenced_rows = $db->query( $sql )->flat;
        if ( scalar @{ $unreferenced_rows } )
        {
            my $error = "Table '$table' has unreferenced rows for constraint '$constraint_name': " .
              join( ', ', @{ $unreferenced_rows } ) . "; SQL: $sql";
            push( @foreign_key_errors, $error );
            WARN $error;
        }
        else
        {
            INFO "Foreign key '$constraint_name' for table '$table' looks fine.";
        }
    }

    if ( scalar @foreign_key_errors > 0 )
    {
        die "One or more foreign key checks failed for table '$table': " . join( "\n", @foreign_key_errors );
    }
{'fraction_non_alphanumeric': 0.10468564650059312, 'fraction_numerical': 0.002224199288256228, 'mean_word_length': 2.283836416747809, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1885670', 'n_tokens_mistral': 2149, 'n_tokens_neox': 2037, 'n_words': 526}