<commit_before>#!/usr/bin/perl -w

# create media_tag_tag_counts table by querying the database tags / feeds / stories

use strict;
use warnings;

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

use MediaWords::DB;
use DBIx::Simple::MediaWords;
use TableCreationUtils;
use Readonly;
use File::Temp qw/ tempfile tempdir /;
use Archive::Zip qw( :ERROR_CODES :CONSTANTS );
use File::Copy;
use List::Util qw(first max maxstr min minstr reduce shuffle sum);

use Carp;
use Dir::Self;
my $_stories_id_window_size = 1000;

# base dir
my $_base_dir = __DIR__ . '/..';

sub get_max_stories_id
{
    my ( $dbh ) = @_;

    my $max_stories_id_row = $dbh->query( "select max(stories_id) as max_id from stories" );

    my $max_stories_id = $max_stories_id_row->hash()->{ max_id };

    return $max_stories_id;
}

sub scroll_stories_id_window
{
    my ( $_stories_id_start, $_stories_id_stop, $max_stories_id ) = @_;

    $_stories_id_start = $_stories_id_stop + 1;
    $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size - 1;

    $_stories_id_stop = min( $_stories_id_stop, $max_stories_id );

    return ( $_stories_id_start, $_stories_id_stop );
}

sub isNonnegativeInteger
{
    my ( $val ) = @_;

    return int( $val ) eq $val;
}

sub dump_story_words
{

    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    my $_stories_id_start;

    if ( !defined( $first_dumped_id ) )
    {
        $first_dumped_id = 0;
    }

    if ( !defined( $last_dumped_id ) )
    {
        my $max_stories_id = get_max_stories_id( $dbh );
        $last_dumped_id = $max_stories_id;
    }

    my $file_name = "$dir/story_words.csv";
    open my $output_file, ">", $file_name
      or die "Can't open $file_name $@";

    Readonly my $select_query =>
"select stories_id, media_id, publish_day, stem, term, sum(stem_count)  as count from story_sentence_words where stories_id >= ? and stories_id < ? group by stories_id, media_id, publish_day, stem, term";

    $dbh->query_csv_dump( $output_file, " $select_query  limit 0 ", [ 0, 0 ], 1 );

    my $_stories_id_start = $first_dumped_id;
    my $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size;

    while ( $_stories_id_start <= $last_dumped_id )
    {
        $dbh->query_csv_dump( $output_file, " $select_query ", [ $_stories_id_start, $_stories_id_stop ], 0 );

        last if ( $_stories_id_stop ) == $last_dumped_id;

        ( $_stories_id_start, $_stories_id_stop ) =
          scroll_stories_id_window( $_stories_id_start, $_stories_id_stop, $last_dumped_id );
        print STDERR "story_id windows: $_stories_id_start -- $_stories_id_stop   (max_dumped_id: " . $last_dumped_id .
          ")  -- " .
          localtime() . "\n";

    }

    $dbh->disconnect;
}

sub dump_stories
{
    my ( $dbh, $dir ) = @_;

    my $file_name = "$dir/stories.csv";
    open my $output_file, ">", "$dir/stories.csv"
      or die "Can't open $file_name: $@";

    $dbh->query_csv_dump( $output_file,
        " select stories_id, media_id, url, guid, title, publish_date, collect_date, full_text_rss from stories ",
        [], 1 );
}

sub dump_media
{
    my ( $dbh, $dir ) = @_;

    my $file_name = "$dir/media.csv";
    open my $output_file, ">", "$file_name"
      or die "Can't open $file_name: $@";

    $dbh->query_csv_dump( $output_file, " select * from media ", [], 1 );
}

sub _current_date
{
    my $ret = localtime();

    $ret =~ s/ /_/g;

    return $ret;
}

sub main
{

    my $config = MediaWords::Util::Config::get_config;

    #my $data_dir = $config->{ mediawords }->{ data_dir };

    my $data_dir = $_base_dir . "/root/include/data_dumps";

    mkdir( $data_dir );

    my $temp_dir = tempdir( DIR => $data_dir, CLEANUP => 1 );

    my $current_date = _current_date();

    my $dump_name = 'media_word_story_dump_' . "$current_date";
    my $dir       = $temp_dir . "/$dump_name";

    mkdir( $dir ) or die "$@";

    my $dbh = MediaWords::DB::connect_to_db;
    dump_media( $dbh, $dir );
    dump_stories( $dbh, $dir );
    dump_story_words( $dbh, $dir );

    my $zip = Archive::Zip->new();

    my $dir_member = $zip->addTree( "$temp_dir" );

    # Save the Zip file
    unless ( $zip->writeToFileNamed( "/$data_dir/tmp_$dump_name" . ".zip" ) == AZ_OK )
    {
        die 'write error';
    }

    move( "/$data_dir/tmp_$dump_name" . ".zip", "/$data_dir/$dump_name" . ".zip" ) || die "Error renaming file $@";
}

main();<commit_msg>Add support for incremental dumps and fix various bugs. Add support update file names to reflect stories_id's included.<commit_after>#!/usr/bin/perl -w

# create media_tag_tag_counts table by querying the database tags / feeds / stories

use strict;
use warnings;

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

use MediaWords::DB;
use DBIx::Simple::MediaWords;
use TableCreationUtils;
use Readonly;
use File::Temp qw/ tempfile tempdir /;
use Archive::Zip qw( :ERROR_CODES :CONSTANTS );
use File::Copy;
use List::Util qw(first max maxstr min minstr reduce shuffle sum);
use MediaWords::Controller::Dashboard;

use Date::Parse;
use Data::Dumper;
use Carp;
use Dir::Self;
my $_stories_id_window_size = 1000;

# base dir
my $_base_dir = __DIR__ . '/..';

sub get_max_stories_id
{
    my ( $dbh ) = @_;

    my $max_stories_id_row = $dbh->query( "select max(stories_id) as max_id from story_sentence_words" );

    my $max_stories_id = $max_stories_id_row->hash()->{ max_id };

    return $max_stories_id;
}

sub scroll_stories_id_window
{
    my ( $_stories_id_start, $_stories_id_stop, $max_stories_id ) = @_;

    $_stories_id_start = $_stories_id_stop + 1;
    $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size - 1;

    $_stories_id_stop = min( $_stories_id_stop, $max_stories_id );

    return ( $_stories_id_start, $_stories_id_stop );
}

sub isNonnegativeInteger
{
    my ( $val ) = @_;

    return int( $val ) eq $val;
}

sub dump_story_words
{

    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    if ( !defined( $first_dumped_id ) )
    {
        $first_dumped_id = 0;
    }

    if ( !defined( $last_dumped_id ) )
    {
        my $max_stories_id = get_max_stories_id( $dbh );
        $last_dumped_id = $max_stories_id;
    }

    my $file_name = "$dir/story_words_" . $first_dumped_id . "_$last_dumped_id" . ".csv";
    open my $output_file, ">", $file_name
      or die "Can't open $file_name $@";

    Readonly my $select_query => "select stories_id, media_id, publish_day, stem, term, sum(stem_count)  as count from  " .
"   story_sentence_words where stories_id >= ? and stories_id <= ? group by stories_id, media_id, publish_day, stem, term"
      . "   order by stories_id, term                  ";

    $dbh->query_csv_dump( $output_file, " $select_query  limit 0 ", [ 0, 0 ], 1 );

    my $_stories_id_start = $first_dumped_id;
    my $_stories_id_stop  = $_stories_id_start + $_stories_id_window_size;

    while ( $_stories_id_start <= $last_dumped_id )
    {
        $dbh->query_csv_dump( $output_file, " $select_query ", [ $_stories_id_start, $_stories_id_stop ], 0 );

        last if ( $_stories_id_stop ) >= $last_dumped_id;

        ( $_stories_id_start, $_stories_id_stop ) =
          scroll_stories_id_window( $_stories_id_start, $_stories_id_stop, $last_dumped_id );
        print STDERR "story_id windows: $_stories_id_start -- $_stories_id_stop   (max_dumped_id: " . $last_dumped_id .
          ")  -- " .
          localtime() . "\n";

    }

    $dbh->disconnect;

    return [ $first_dumped_id, $last_dumped_id ];
}

sub dump_stories
{
    my ( $dbh, $dir, $first_dumped_id, $last_dumped_id ) = @_;

    my $file_name = "$dir/stories_" . $first_dumped_id . "_$last_dumped_id" . ".csv";
    open my $output_file, ">", "$dir/stories.csv"
      or die "Can't open $file_name: $@";

    $dbh->query_csv_dump(
        $output_file,
        " select stories_id, media_id, url, guid, title, publish_date, collect_date, full_text_rss from stories " .
          "   where stories_id >= ? and stories_id <= ? order by stories_id",
        [ $first_dumped_id, $last_dumped_id ],
        1
    );
}

sub dump_media
{
    my ( $dbh, $dir ) = @_;

    my $file_name = "$dir/media.csv";
    open my $output_file, ">", "$file_name"
      or die "Can't open $file_name: $@";

    $dbh->query_csv_dump( $output_file, " select * from media order by media_id", [], 1 );
}

sub _current_date
{
    my $ret = localtime();

    $ret =~ s/ /_/g;

    return $ret;
}

sub _get_time_from_file_name
{
    my ( $file_name ) = @_;

    $file_name =~ /media_.*dump_(.*)_\d+_(\d+)\.zip/;
    my $date = $1;
    $date =~ s/_/ /g;
    return str2time( $date );
}

sub _get_last_story_id_from_file_name
{
    my ( $file_name ) = @_;

    $file_name =~ /media_.*dump_(.*)_\d+_(\d+)\.zip/;
    my $stories_id = $2;

    return $stories_id;
}

my $full = 1;

sub main
{

    my $config = MediaWords::Util::Config::get_config;

    #my $data_dir = $config->{ mediawords }->{ data_dir };

    my $data_dir = $_base_dir . "/root/include/data_dumps";

    mkdir( $data_dir );

    my $temp_dir = tempdir( DIR => $data_dir, CLEANUP => 1 );

    my $current_date = _current_date();

    my $dump_name = 'media_word_story_dump_' . "$current_date";
    my $dir       = $temp_dir . "/$dump_name";

    mkdir( $dir ) or die "$@";

    my $dbh = MediaWords::DB::connect_to_db;

    my $stories_id_start;

    if ( $full )
    {
        $stories_id_start = 0;
    }
    else
    {

        my $existing_dump_files = MediaWords::Controller::Dashboard::get_data_dump_file_list();
        say STDERR Dumper( $existing_dump_files );
        say STDERR Dumper(
            [
                map { $_ . ' -- ' . _get_time_from_file_name( $_ ) . ' ' . _get_last_story_id_from_file_name( $_ ); }
                  @$existing_dump_files
            ]
        );
        my $previous_max = max( map { _get_last_story_id_from_file_name( $_ ); } @$existing_dump_files );

        $stories_id_start = $previous_max + 1;
    }

    my $last_dumped_id = get_max_stories_id( $dbh );

    dump_media( $dbh, $dir );
    dump_stories( $dbh, $dir, $stories_id_start, $last_dumped_id );

    my $existing_dump_files = MediaWords::Controller::Dashboard::get_data_dump_file_list();
    say STDERR Dumper( $existing_dump_files );
    say STDERR Dumper(
        [
            map { $_ . ' -- ' . _get_time_from_file_name( $_ ) . ' ' . _get_last_story_id_from_file_name( $_ ); }
              @$existing_dump_files
        ]
    );

    #exit;

    my $dumped_stories = dump_story_words( $dbh, $dir, $stories_id_start, $last_dumped_id );

    my $zip = Archive::Zip->new();

    my $dir_member = $zip->addTree( "$temp_dir" );

    # Save the Zip file
    my $dump_zip_file_name = $dump_name . '_' . $dumped_stories->[ 0 ] . '_' . $dumped_stories->[ 1 ];

    my $tmp_zip_file_path = "/$data_dir/tmp_$dump_zip_file_name" . ".zip";
    unless ( $zip->writeToFileNamed( $tmp_zip_file_path ) == AZ_OK )
    {
        die 'write error';
    }

    move( $tmp_zip_file_path, "/$data_dir/$dump_zip_file_name" . ".zip" ) || die "Error renaming file $@";
}

main();
{'fraction_non_alphanumeric': 0.14504569073642717, 'fraction_numerical': 0.003404407812220032, 'mean_word_length': 3.080043859649123, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 47, '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': '4996875', 'n_tokens_mistral': 4413, 'n_tokens_neox': 4228, 'n_words': 866}