<commit_before>#!/usr/bin/env perl

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

use strict;

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

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

use DBIx::Simple::MediaWords;
use TableCreationUtils;

sub main
{

    my $dbh = MediaWords::DB::connect_to_db()
      || die DBIx::Simple::MediaWords->error;

    $dbh->query( "DROP TABLE if exists media_rss_full_text_detection_data_new" ) or die $dbh->error;

    my $table_creation_query = <<"SQL";
     create table media_rss_full_text_detection_data_new as  
        select * from 
          (select media_id, max(similarity) as max_similarity, 
            avg(similarity) as avg_similarity, min(similarity) as min_similarity,
            avg(length(extracted_text)) as avg_extracted_length, avg(length(html_strip(title || description))) 
            as avg_rss_length, avg(length(html_strip(description))) as avg_rss_discription, count(*) 
             from (select *, similarity(extracted_text, html_strip(title || description) ) from
                 stories, 
                   (select stories_id, array_to_string(array_agg(download_text), ' ') as extracted_text
                      from 
                         (select * from downloads natural join stories natural join download_texts 
                             where publish_date > now() - interval ' 1 week'  order by downloads_id)
                             as downloads group by stories_id) as story_extracted_texts
                         where stories.stories_id = story_extracted_texts.stories_id and
                            publish_date > now() - interval '1 week' ) as media_extraction_text_similarity 
         group by media_id order by media_id ) as foo  ;
SQL
<commit_msg>update to work on modern database<commit_after>#!/usr/bin/env perl

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

use strict;

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

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

use DBIx::Simple::MediaWords;
use TableCreationUtils;

sub main
{

    my $dbh = MediaWords::DB::connect_to_db()
      || die DBIx::Simple::MediaWords->error;

    $dbh->query( "DROP TABLE if exists media_rss_full_text_detection_data_new" ) or die $dbh->error;

    my $table_creation_query = <<"SQL";
 create table media_rss_full_text_detection_data_new as
     with active_media as (
         select media_id from media_health where num_stories_90 > 10 and is_healthy and has_active_feed
     ),

     story_texts as (
         select
                d.stories_id,
                min( dt.download_text ) as story_text
            from downloads d
                join download_texts dt on ( d.downloads_id = dt.downloads_id )
            where
                d.feeds_id in (
                    select feeds_id from feeds where media_id in ( select media_id from active_media )
                ) and
                date_trunc( 'day'::text, ss.publish_date ) between now() - '1 day'::interval and now()
            group by ss.stories_id
     ),

     story_similarities as (
         select
                s.stories_id,
                s.media_id,
                s.publish_date,
                similarity( st.story_text, s.description ) text_similarity,
                length( story_text ) text_length,
                length( description ) description_length
            from stories s
                join active_media am on ( s.media_id = am.media_id )
                join story_texts st on ( s.stories_id = st.stories_id )
            where
                date_trunc( 'day'::text, s.publish_date ) between now() - '1 day'::interval and now()
    )

    select
            media_id,
            max( text_similarity ) as max_similarity,
            min( text_similarity ) as min_similarity,
            avg( text_similarity ) as avg_similarity,
            avg( text_length ) as avg_text_length,
            avg( description_length ) as avg_description_length,
            count(*) as num_stories,
            min( publish_date ) as publish_date
        from
            story_similarities ss
        group by ss.media_id
        order by ss.media_id
SQL

{'fraction_non_alphanumeric': 0.08256880733944955, 'fraction_numerical': 0.003669724770642202, 'mean_word_length': 2.242379182156134, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 12, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4822666', 'n_tokens_mistral': 1362, 'n_tokens_neox': 1262, 'n_words': 339}