#!/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