<commit_before>#!/usr/bin/perl

use warnings;
use strict;
use DBI;
use POSIX;
use Archive::Zip;

sub table_cols_query { 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME = ? ORDER BY COLUMN_ID' }

sub table_cols { [ map { $_->[0] } @{ shift->selectall_arrayref(table_cols_query, undef, uc shift, uc shift) } ] }

sub csv_line { map { "$_\n" } join ',', map { $_ ||= ''; s/[\",]/\\$&/g; $_ } map { @$_ } @_ }

sub all_vocabularies { map { $_->[0] } @{ shift->selectall_arrayref('SELECT * FROM VOCABULARY') } }

sub csv_dump {
    my ($dbh, $table) = @_;
    my $cols = table_cols $dbh, $dbh->{Username}, $table->{name};
    my $sth = $dbh->prepare($table->{query});
    $sth->execute(@{$table->{params}});
    my $dump = tmpnam;
    open my $fh, '>', $dump;
    print $fh csv_line $cols;
    while (my $line = $sth->fetch) {
	print $fh csv_line $line;
    }
    close $fh;
    return $dump;
}

do { print <<END
Usage:
./dump.pl \<user/pass\@host/sid\> \<output.zip\> [vocabulary ids]
'vocabulary ids' is a comma-separated list of required vocabularies, omit the list in order to dump all available 
END
} and exit unless @ARGV;

die unless shift =~ /^(.+)\/(.+)\@(.+)\/(.+)$/;
my $dbh = DBI->connect(sprintf('dbi:Oracle:host=%s;sid=%s', $3, $4), $1, $2) or die;
$dbh->do('ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"');
my $output = shift or die;
my @vocabularies = split /,/, (shift or join ',', all_vocabularies $dbh);
my $placeholder = join ', ', split //, '?' x @vocabularies;

my $zip = new Archive::Zip;
do {
    my $dump = csv_dump $dbh, $_;
    $zip->addFile($dump, sprintf('%s.csv', $_->{name}));
} for
    {
	name => 'CONCEPT',
    	query => sprintf('SELECT * FROM CONCEPT WHERE VOCABULARY_ID IN (%s)', $placeholder),
	params => [ @vocabularies ],
    },
    {
	name => 'CONCEPT_RELATIONSHIP',
	query => sprintf('SELECT * FROM CONCEPT_RELATIONSHIP WHERE EXISTS (SELECT * FROM CONCEPT WHERE CONCEPT_ID_1 = CONCEPT_ID AND VOCABULARY_ID IN (%s)) AND EXISTS (SELECT * FROM CONCEPT WHERE CONCEPT_ID_2 = CONCEPT_ID AND VOCABULARY_ID IN (%s))', $placeholder, $placeholder),
	params => [ @vocabularies, @vocabularies ],
    },
    {
	name => 'SOURCE_TO_CONCEPT_MAP',
	query => sprintf('SELECT * FROM SOURCE_TO_CONCEPT_MAP WHERE SOURCE_VOCABULARY_ID IN (%s) AND TARGET_VOCABULARY_ID IN (%s)', $placeholder, $placeholder),
	params => [ @vocabularies, @vocabularies ],
    };
die unless $zip->writeToFileNamed($output) == Archive::Zip::AZ_OK;
unlink $_->{externalFileName} for $zip->members;
<commit_msg>added concept_ancestor, synonym, vocabulary and relationship tables to the dump<commit_after>#!/usr/bin/perl

use warnings;
use strict;
use DBI;
use POSIX;
use Archive::Zip;

sub table_cols_query { 'SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE OWNER = ? AND TABLE_NAME = ? ORDER BY COLUMN_ID' }

sub table_cols { [ map { $_->[0] } @{ shift->selectall_arrayref(table_cols_query, undef, uc shift, uc shift) } ] }

sub csv_line { map { "$_\n" } join ',', map { $_ ||= ''; s/[\",]/\\$&/g; $_ } map { @$_ } @_ }

sub all_vocabularies { map { $_->[0] } @{ shift->selectall_arrayref('SELECT * FROM VOCABULARY') } }

sub csv_dump {
    my ($dbh, $table) = @_;
    my $cols = table_cols $dbh, $dbh->{Username}, $table->{name};
    my $sth = $dbh->prepare(join(' ', sprintf('SELECT %s FROM %s t', join(',', @$cols), $table->{name}), $table->{query}));
    $sth->execute(@{$table->{params}});
    my $dump = tmpnam;
    open my $fh, '>', $dump;
    print $fh csv_line $cols;
    while (my $line = $sth->fetch) {
	print $fh csv_line $line;
    }
    close $fh;
    return $dump;
}

do { print <<END
Usage:
./dump.pl \<user/pass\@host/sid\> \<output.zip\> [vocabulary ids]
'vocabulary ids' is a comma-separated list of required vocabularies, omit the list in order to dump all available 
END
} and exit unless @ARGV;

die unless shift =~ /^(.+)\/(.+)\@(.+)\/(.+)$/;
my $dbh = DBI->connect(sprintf('dbi:Oracle:host=%s;sid=%s', $3, $4), $1, $2) or die;
$dbh->do('ALTER SESSION SET NLS_DATE_FORMAT="YYYYMMDD"');
my $output = shift or die;
my @vocabularies = split /,/, (shift or join ',', all_vocabularies $dbh);
my $placeholder = join ', ', split //, '?' x @vocabularies;

my $zip = new Archive::Zip;
$zip->addFile(csv_dump($dbh, $_), sprintf('%s.csv', $_->{name})) for
    {
	name => 'CONCEPT',
    	query => sprintf('WHERE VOCABULARY_ID IN (%s)', $placeholder),
	params => [ @vocabularies ],
    },
    {
	name => 'CONCEPT_RELATIONSHIP',
	query => sprintf('WHERE EXISTS (SELECT * FROM CONCEPT WHERE CONCEPT_ID_1 = CONCEPT_ID AND VOCABULARY_ID IN (%s)) AND EXISTS (SELECT * FROM CONCEPT WHERE CONCEPT_ID_2 = CONCEPT_ID AND VOCABULARY_ID IN (%s))', $placeholder, $placeholder),
	params => [ @vocabularies, @vocabularies ],
    },
    {
	name => 'SOURCE_TO_CONCEPT_MAP',
	query => sprintf('WHERE SOURCE_VOCABULARY_ID IN (%s) AND TARGET_VOCABULARY_ID IN (%s)', $placeholder, $placeholder),
	params => [ @vocabularies, @vocabularies ],
    },
    {
	name => 'CONCEPT_ANCESTOR',
	query => sprintf('WHERE EXISTS (SELECT * FROM CONCEPT WHERE ANCESTOR_CONCEPT_ID = CONCEPT_ID AND VOCABULARY_ID IN (%s)) AND EXISTS (SELECT * FROM CONCEPT WHERE DESCENDANT_CONCEPT_ID = CONCEPT_ID AND VOCABULARY_ID IN (%s))', $placeholder, $placeholder),
	params => [ @vocabularies, @vocabularies ],	
    },
    {
	name => 'CONCEPT_SYNONYM',
	query => sprintf('WHERE EXISTS (SELECT * FROM CONCEPT c WHERE t.CONCEPT_ID = t.CONCEPT_ID AND VOCABULARY_ID IN (%s))', $placeholder),
	params => [ @vocabularies ],	
    },
    {
	name => 'VOCABULARY',
	query => sprintf('WHERE VOCABULARY_ID IN (%s)', $placeholder),
	params => [ @vocabularies ],
    },
    {
	name => 'RELATIONSHIP',
	query => '',
	params => [],
    };
die unless $zip->writeToFileNamed($output) == Archive::Zip::AZ_OK;
unlink $_->{externalFileName} for $zip->members;

{'fraction_non_alphanumeric': 0.1854783207157605, 'fraction_numerical': 0.0027529249827942187, 'mean_word_length': 4.162522202486678, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 76, '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': '5148098', 'n_tokens_mistral': 2282, 'n_tokens_neox': 2211, 'n_words': 553}