!/usr/local/bin/perl

use DBI;
use Data::Dump qw(dump);

use strict;

use warnings;
use lib qw(..);
use JSON qw( );

create a new database in sqlite named trades.

my $dsn = “DBI:SQLite:L0trades.sqlite”;
my %attr = (PrintError=>0, RaiseError=>1);

connect to the database

my $dbh = DBI->connect($dsn, \%attr) || warn “Didn’t create DB connection”;

check if the database opened successfully or not;

print “Opened database successfully\n”;

$dbh->do(‘PRAGMA foreign_keys = ON’);
$dbh->do(‘PRAGMA foreign_keys’);

my $filename=shift || die (“No second argument”);

$count = 0;

open ($info, $filename) || die “Could not open $file: $!”;

loop through, one line at a time.

while( $json_text = <$info>) {
# store the decoded json data in a variable ($data)
my $json = JSON->new;
# data is the decoded JSON
$data = $json->decode($json_text);
my @prod = %$data;
my $trades_id = $data{‘Id’};
my $query=””;
my $counter = 0;

# data is the decoded JSON
$createStr = “CREATE TABLE TABLE_0 (“;
$template= “CREATE TABLE V_”;
my $query=””;
my $counter = 0;
my @List;
my $insert = “insert into TABLE_0 ( “;
my $insert2 = ” values ( “;
for $f (keys(%$data)) {

$value=$data->{$f};
$createStr .= "$f TEXT,";

if(ref($value) eq 'ARRAY') {
    $counter += 1;
    $createSub = $template . "$f (";

    my $subinsert = "insert into $f ( ";
    my $subinsert2 = " values ( ";

    for $subkey (keys(%$data)) {
        $subvalue=$data->{$subkey};
        $createSub .= "$subkey TEXT,";

        if ($value eq 'Product') {
            @productSubList = (@productSubList, $subkey);
            }

        $subinsert .= "\'" . $subkey . "\',";
        $subinsert2 .=  "\'" .  $subvalue . "\',";
        }
    $subinsert =~ s/,$//g;
    $subinsert2 =~ s/,$//g;

    $subinsert .= ") ";
    $subinsert2 .= ");";
    $query = $subinsert . $subinsert2;

    $createSub =~ s/,$//g;
    $createSub .= ");";
    if ($value ne 'Product') {
        @List = (@List, $createSub);
        }
    #print "\n$createSub";
    @insertList = (@insertList, $query); 
    #print "\n$query";

    }

}
$createStr =~ s/,$//g;
$createStr .= “);”;
@List = (@List, $createStr);

for (@List) {
print “\n”, $_ , “\n”;
$dbh->do($_);
}

for (@insertList) {
print $_ , “\n”;
$dbh->do($_);
}
}
close $info;