<commit_before><commit_msg>script to extract to JSON data in original database<commit_after>#!/usr/bin/env perl

use strict;
use warnings;

use DBI;
use File::Slurp;
use JSON;

=pod

This script looks at selected tables in the original mzalendo database and pulls
out the data into json files that can then be processed with the Django code to
import into db.

=cut

# setup the json object
my $json = JSON->new->utf8(1)->pretty(1);

# setup where to create the various json files.
my $json_dir = '/tmp/mzalendo';
mkpath $json_dir;

# connect to the local mysql 'mzalendo' database
my $dbh = DBI->connect( "DBI:mysql:database=mzalendo", 'root', '' )
  || die "Could not connect to db...";

# dump some tables directly to JSON
dump_table_to_json($_)
  for (
    'answers',       'aspirantcomments',   'aspirants',
    'aspirantsites', 'bills',              'billstatus',
    'comments',      'commentstatus',      'committeemembers',
    'committees',    'constituencies',     'constituencymembers',
    'districts',     'hansards',           'ipBlocker',
    'members',       'membersites',        'membersterms',
    'membertypes',   'ministries',         'ministrymembers',
    'motions',       'motiontypes',        'offices',
    'parties',       'partyoffices',       'polling',
    'provinces',     'questioncategories', 'questions',
    'questiontypes', 'ranks',              'sitetypes',
    'sk2_blacklist', 'sponsors',           'status',
    'terms',
  );

=head2 dump_table_to_json

    dump_table_to_json( $table_name );

Outputs the content of the table to json in a file called '$table_name.json' in
'$json_dir'.

=cut

sub dump_table_to_json {
    my $table_name = shift;

    my $query = $dbh->prepare("select * from $table_name");
    $query->execute;

    my @rows = ();
    while ( my $row = $query->fetchrow_hashref ) {
        push @rows, $row;
    }

    write_file( "$json_dir/$table_name.json", $json->encode( \@rows ) );

    return 1;
}
{'fraction_non_alphanumeric': 0.13397860417727966, 'fraction_numerical': 0.003056546102903719, 'mean_word_length': 3.091666666666667, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 11, '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': '3436578', 'n_tokens_mistral': 660, 'n_tokens_neox': 646, 'n_words': 183}