#!/usr/bin/env perl

# populate sqlite db with content of __DATA__

use FindBin qw($Bin); # warn $FindBin::Bin;
use Modern::Perl;
use Data::Printer;
use Data::Dumper;
use Path::Tiny;

use lib '/home/raj/perl-lib';
use Local::DB;

my $db = path($Bin, '..', 'ngs.sqlite')->realpath; # warn 'DB:'. $db;
my $dsn = "dbi:SQLite:dbname=$db";

my $dbix = Local::DB->dbix({ dsn => $dsn }); # p $dbix;

my @rows = split "\n", do { local $/; <DATA> }; # p @rows; exit;
my @cols = qw( gene_name gene feature );

my %h;
ROW: for my $row(@rows) { # warn Dumper $row; # $row = tab-delimited string
    @h{@cols} = split "\t", $row; # p %h; # next ROW; # pulls 1st 3 cols
    do { print "skipping $h{gene_name} - already exists\n" and next ROW } if 
        $dbix->select('transcripts', 1, { gene_name => $h{gene_name} })->list;
    $dbix->insert('transcripts', \%h) or die $dbix->error;
}

__DATA__
TNFRSF14	ENSG00000157873	ENST00000355716	NM_003820
ID3	ENSG00000117318	ENST00000374561	NM_002167
PRDM1	ENSG00000057657	ENST00000369096	NM_001198
TNFAIP3	ENSG00000118503	ENST00000237289	NM_001270508
CARD11	ENSG00000198286	ENST00000396946	NM_032415
MYC	ENSG00000136997	ENST00000377970	NM_002467
KMT2D	ENSG00000167548	ENST00000301067	NM_003482
CREBBP	ENSG00000005339	ENST00000262367	NM_004380
MEF2B	ENSG00000213999	ENST00000602424	NM_005919
TP53	ENSG00000141510	ENST00000269305	NM_000546
BCL6	ENSG00000113916	ENST00000406870	NM_001706
BRAF	ENSG00000157764	ENST00000288602	NM_004333
CCND3	ENSG00000112576	ENST00000372991	NM_001760
CD79A	ENSG00000105369	ENST00000221972	NM_001783
CD79B	ENSG00000007312	ENST00000006750	NM_000626
EZH2	ENSG00000106462	ENST00000320356	NM_001203247
MYD88	ENSG00000172936	ENST00000417037	NM_001172567
NOTCH1	ENSG00000148400	ENST00000277541	NM_017617
SF3B1	ENSG00000115524	ENST00000335508	NM_012433
TCF3	ENSG00000071564	ENST00000262965	NM003200