<commit_before>package MediaWords::CM::Dump;

# code to analyze a controversy and dump the controversy to snapshot tables and a gexf file

use strict;

use Data::Dumper;
use Date::Format;
use Encode;
use File::Temp;
use FileHandle;
use Getopt::Long;
use XML::Simple;

use MediaWords::CM::Model;
use MediaWords::DBI::Media;
use MediaWords::Util::CSV;
use MediaWords::Util::Colors;
use MediaWords::Util::Config;
use MediaWords::Util::SQL;
use MediaWords::Util::Tags;

# max and mind node sizes for gexf dump
use constant MAX_NODE_SIZE => 50;
use constant MIN_NODE_SIZE => 5;

# max map width for gexf dump
use constant MAX_MAP_WIDTH => 800;

# consistent colors for media types
my $_media_type_color_map;

# attributes to include in gexf dump
my $_media_static_gexf_attribute_types = {
    url          => 'string',
    inlink_count => 'integer',
    story_count  => 'integer',
    view_medium  => 'string'
};

# all tables that the dump process snapshots for each controversy_dump
my $_snapshot_tables = [
    qw/controversy_stories controversy_links_cross_media controversy_media_codes
      stories media stories_tags_map media_tags_map tags tag_sets/
];

# tablespace clause for temporary tables
my $_temporary_tablespace;

# get the list of all snapshot tables
sub get_snapshot_tables
{
    return [ @{ $_snapshot_tables } ];
}

# if the temporary_table_tablespace config is present, set $_temporary_tablespace
# to a tablespace clause for the tablespace, otherwise set it to ''
sub set_temporary_table_tablespace
{
    my $config = MediaWords::Util::Config::get_config;

    my $tablespace = $config->{ mediawords }->{ temporary_table_tablespace };

    $_temporary_tablespace = $tablespace ? "tablespace $tablespace" : '';
}

# create all of the temporary dump* tables other than medium_links and story_links
sub write_live_dump_tables
{
    my ( $db, $controversy, $cdts ) = @_;

    my $controversies_id;
    if ( $controversy )
    {
        $controversies_id = $controversy->{ controversies_id };
    }
    else
    {
        my $cd = $db->find_by_id( 'controversy_dumps', $cdts->{ controversy_dumps_id } );
        $controversies_id = $cd->{ controversies_id };
    }

    write_temporary_dump_tables( $db, $controversies_id );
    write_period_stories( $db, $cdts );
    write_story_link_counts_dump( $db, $cdts, 1 );
    write_story_links_dump( $db, $cdts, 1 );
    write_medium_link_counts_dump( $db, $cdts, 1 );
    write_medium_links_dump( $db, $cdts, 1 );
}

# create temporary view of all the dump_* tables that call into the cd.* tables.
# this is useful for writing queries on the cd.* tables without lots of ugly
# joins and clauses to cd and cdts.  It also provides the same set of dump_*
# tables as provided by write_story_link_counts_dump_tables, so that the same
# set of queries can run against either.
sub create_temporary_dump_views
{
    my ( $db, $cdts ) = @_;

    my $snapshot_tables = get_snapshot_tables();

    for my $t ( @{ $snapshot_tables } )
    {
        $db->query( <<END );
create temporary view dump_$t as select * from cd.$t 
    where controversy_dumps_id = $cdts->{ controversy_dumps_id }
END
    }

    for my $t ( qw(story_link_counts story_links medium_link_counts medium_links) )
    {
        $db->query( <<END )
create temporary view dump_$t as select * from cd.$t 
    where controversy_dump_time_slices_id = $cdts->{ controversy_dump_time_slices_id }
END
    }
}

# setup dump_* tables by either creating views for the relevant cd.*
# tables for a dump snapshot or by copying live data for live requests.
sub setup_temporary_dump_tables
{
    my ( $db, $cdts, $controversy, $live ) = @_;

    # postgres prints lots of 'NOTICE's when deleting temp tables
    $db->dbh->{ PrintWarn } = 0;

    if ( $live )
    {
        MediaWords::CM::Dump::write_live_dump_tables( $db, $controversy, $cdts );
    }
    else
    {
        MediaWords::CM::Dump::create_temporary_dump_views( $db, $cdts );
    }
}

# run $db->query( "discard temp" ) to clean up temp tables and views
sub discard_temp_tables
{
    my ( $db ) = @_;

    $db->query( "discard temp" );
}

# remove stories from dump_period_stories that don't match the $csts->{ tags_id }, if present
sub restrict_period_stories_to_tag
{
    my ( $db, $cdts ) = @_;

    return unless ( $cdts->{ tags_id } );

    # it may be a little slower to add all the rows and then delete them, but
    # it makes the code much cleaner
    $db->query( <<END, $cdts->{ tags_id } );
delete from dump_period_stories s where not exists
        ( select 1 from stories_tags_map stm where stm.stories_id = s.stories_id and stm.tags_id = ? )
END

}

# get the where clause that will restrict the dump_period_stories creation
# to only stories within the cdts time frame
sub get_period_stories_date_where_clause
{
    my ( $cdts ) = @_;

    my $date_clause = <<END;
( ( s.publish_date between \$1::timestamp and \$2::timestamp - interval '1 second' 
      and s.stories_id not in ( select stories_id from dump_undateable_stories ) ) or
  ( ss.publish_date between \$1::timestamp and \$2::timestamp - interval '1 second'
      and ss.stories_id not in ( select stories_id from dump_undateable_stories ) )
)
END

    return $date_clause;
}

# write dump_period_stories table that holds list of all stories that should be included in the
# current period.  For an overall dump, every story should be in the current period.
# For other dumps, a story should be in the current dump if either its date is within
# the period dates or if a story that links to it has a date within the period dates.
# For this purpose, stories tagged with the 'date_invalid:undateable' tag
# are considered to have an invalid tag, so their dates cannot be used to pass
# either of the above tests.
#
# The resulting dump_period_stories should be used by all other dump queries to determine
# story membership within a give period.
sub write_period_stories
{
    my ( $db, $cdts ) = @_;

    $db->query( "drop table if exists dump_period_stories" );

    if ( !$cdts || ( !$cdts->{ tags_id } && ( $cdts->{ period } eq 'overall' ) ) )
    {
        $db->query( "create table dump_period_stories as select stories_id from dump_stories" );
    }
    else
    {<commit_msg>fix bug using permanent instead of temp table for dump_period_stories<commit_after>package MediaWords::CM::Dump;

# code to analyze a controversy and dump the controversy to snapshot tables and a gexf file

use strict;

use Data::Dumper;
use Date::Format;
use Encode;
use File::Temp;
use FileHandle;
use Getopt::Long;
use XML::Simple;

use MediaWords::CM::Model;
use MediaWords::DBI::Media;
use MediaWords::Util::CSV;
use MediaWords::Util::Colors;
use MediaWords::Util::Config;
use MediaWords::Util::SQL;
use MediaWords::Util::Tags;

# max and mind node sizes for gexf dump
use constant MAX_NODE_SIZE => 50;
use constant MIN_NODE_SIZE => 5;

# max map width for gexf dump
use constant MAX_MAP_WIDTH => 800;

# consistent colors for media types
my $_media_type_color_map;

# attributes to include in gexf dump
my $_media_static_gexf_attribute_types = {
    url          => 'string',
    inlink_count => 'integer',
    story_count  => 'integer',
    view_medium  => 'string'
};

# all tables that the dump process snapshots for each controversy_dump
my $_snapshot_tables = [
    qw/controversy_stories controversy_links_cross_media controversy_media_codes
      stories media stories_tags_map media_tags_map tags tag_sets/
];

# tablespace clause for temporary tables
my $_temporary_tablespace;

# get the list of all snapshot tables
sub get_snapshot_tables
{
    return [ @{ $_snapshot_tables } ];
}

# if the temporary_table_tablespace config is present, set $_temporary_tablespace
# to a tablespace clause for the tablespace, otherwise set it to ''
sub set_temporary_table_tablespace
{
    my $config = MediaWords::Util::Config::get_config;

    my $tablespace = $config->{ mediawords }->{ temporary_table_tablespace };

    $_temporary_tablespace = $tablespace ? "tablespace $tablespace" : '';
}

# create all of the temporary dump* tables other than medium_links and story_links
sub write_live_dump_tables
{
    my ( $db, $controversy, $cdts ) = @_;

    my $controversies_id;
    if ( $controversy )
    {
        $controversies_id = $controversy->{ controversies_id };
    }
    else
    {
        my $cd = $db->find_by_id( 'controversy_dumps', $cdts->{ controversy_dumps_id } );
        $controversies_id = $cd->{ controversies_id };
    }

    write_temporary_dump_tables( $db, $controversies_id );
    write_period_stories( $db, $cdts );
    write_story_link_counts_dump( $db, $cdts, 1 );
    write_story_links_dump( $db, $cdts, 1 );
    write_medium_link_counts_dump( $db, $cdts, 1 );
    write_medium_links_dump( $db, $cdts, 1 );
}

# create temporary view of all the dump_* tables that call into the cd.* tables.
# this is useful for writing queries on the cd.* tables without lots of ugly
# joins and clauses to cd and cdts.  It also provides the same set of dump_*
# tables as provided by write_story_link_counts_dump_tables, so that the same
# set of queries can run against either.
sub create_temporary_dump_views
{
    my ( $db, $cdts ) = @_;

    my $snapshot_tables = get_snapshot_tables();

    for my $t ( @{ $snapshot_tables } )
    {
        $db->query( <<END );
create temporary view dump_$t as select * from cd.$t 
    where controversy_dumps_id = $cdts->{ controversy_dumps_id }
END
    }

    for my $t ( qw(story_link_counts story_links medium_link_counts medium_links) )
    {
        $db->query( <<END )
create temporary view dump_$t as select * from cd.$t 
    where controversy_dump_time_slices_id = $cdts->{ controversy_dump_time_slices_id }
END
    }
}

# setup dump_* tables by either creating views for the relevant cd.*
# tables for a dump snapshot or by copying live data for live requests.
sub setup_temporary_dump_tables
{
    my ( $db, $cdts, $controversy, $live ) = @_;

    # postgres prints lots of 'NOTICE's when deleting temp tables
    $db->dbh->{ PrintWarn } = 0;

    if ( $live )
    {
        MediaWords::CM::Dump::write_live_dump_tables( $db, $controversy, $cdts );
    }
    else
    {
        MediaWords::CM::Dump::create_temporary_dump_views( $db, $cdts );
    }
}

# run $db->query( "discard temp" ) to clean up temp tables and views
sub discard_temp_tables
{
    my ( $db ) = @_;

    $db->query( "discard temp" );
}

# remove stories from dump_period_stories that don't match the $csts->{ tags_id }, if present
sub restrict_period_stories_to_tag
{
    my ( $db, $cdts ) = @_;

    return unless ( $cdts->{ tags_id } );

    # it may be a little slower to add all the rows and then delete them, but
    # it makes the code much cleaner
    $db->query( <<END, $cdts->{ tags_id } );
delete from dump_period_stories s where not exists
        ( select 1 from stories_tags_map stm where stm.stories_id = s.stories_id and stm.tags_id = ? )
END

}

# get the where clause that will restrict the dump_period_stories creation
# to only stories within the cdts time frame
sub get_period_stories_date_where_clause
{
    my ( $cdts ) = @_;

    my $date_clause = <<END;
( ( s.publish_date between \$1::timestamp and \$2::timestamp - interval '1 second' 
      and s.stories_id not in ( select stories_id from dump_undateable_stories ) ) or
  ( ss.publish_date between \$1::timestamp and \$2::timestamp - interval '1 second'
      and ss.stories_id not in ( select stories_id from dump_undateable_stories ) )
)
END

    return $date_clause;
}

# write dump_period_stories table that holds list of all stories that should be included in the
# current period.  For an overall dump, every story should be in the current period.
# For other dumps, a story should be in the current dump if either its date is within
# the period dates or if a story that links to it has a date within the period dates.
# For this purpose, stories tagged with the 'date_invalid:undateable' tag
# are considered to have an invalid tag, so their dates cannot be used to pass
# either of the above tests.
#
# The resulting dump_period_stories should be used by all other dump queries to determine
# story membership within a give period.
sub write_period_stories
{
    my ( $db, $cdts ) = @_;

    $db->query( "drop table if exists dump_period_stories" );

    if ( !$cdts || ( !$cdts->{ tags_id } && ( $cdts->{ period } eq 'overall' ) ) )
    {
        $db->query( "create temporary table dump_period_stories as select stories_id from dump_stories" );
    }
    else
    {
{'fraction_non_alphanumeric': 0.09699584531799296, 'fraction_numerical': 0.0028763183125599234, 'mean_word_length': 3.804990403071017, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 61, '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': '4860184', 'n_tokens_mistral': 4118, 'n_tokens_neox': 4003, 'n_words': 1356}