wjw:
It sounds like you've already got your SQLite issues sorted out. But looking at your code, I thought I'd make a couple suggestions regarding your programs structure as well as the interaction of your process_data subroutine and the database.
Before diving in, though, I'll apologize in advance as the node got long and I rambled on a bit. I've come upon a time deadline, though, so I can't finish editing/tuning my response. So the disorganized mess (i.e. Appendix) isn't where I wanted it to be. Also *none* of the code has been tested yet, so ping me if there are any problems/questions/etc. I'll be sure to check back this evening when I get back.
First of all, the readability of your process_data subroutine isn't very good: there's a lot of redundant code in there that makes it hard to see what's actually happening in there. Since your insert statement is always the same, rather than having multiple copies of it and the associated prepare calls, just move them before your if statements:
sub process_data {
if ($debug) {
say "Processing polled data...."
}
my $json_line = shift(@_);
my $dt = DateTime->now();
$dt->set_time_zone( 'America/Chicago' );
my $sth;
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile" ,"","");
$q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?, ?,
+?, ?, ?, ?, ?, ?, ?, ?)";
$sth = $dbh->prepare($q);
if ( (exists($json->{'bbl'})) and (not exists($json->{'ra'}))) {
$sth->execute(undef, $dt->datetime(), $dt->ymd(), $dt->hms(),
$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $js
+on->{'tC'},
$json->{'bp'}, $json->{'rh'}, $json->{'li'}, $js
+on->{'ov'},
$json->{'lux'}, $json->{'bbl'}, $json->{'irl'},
+'null', 'null'
);
} elsif ( (exists($json->{'ra'})) and (exists($json->{'bbl'}))) {
+
$sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(),
$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $js
+on->{'tC'},
$json->{'bp'}, $json->{'rh'}, $json->{'li'}, $js
+on->{'ov'},
$json->{'lux'}, $json->{'bbl'}, $json->{'irl'},
+ $json->{'ra'}, $json->{'rr'}
);
} elsif ( (exists($json->{'ra'})) and (not exists($json->{'bbl'}))
+) {
$sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(),
$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $js
+on->{'tC'},
$json->{'bp'}, $json->{'rh'}, $json->{'li'}, $js
+on->{'ov'},
'null', 'null', 'null', $json->{'ra'}, $json->{'
+rr'}
);
} elsif ( (not exists($json->{'ra'})) and (not exists($json->{'bbl
+'}))) {
$sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(),
$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $js
+on->{'tC'},
$json->{'bp'}, $json->{'rh'}, $json->{'li'}, $js
+on->{'ov'},
'null', 'null', 'null', 'null', 'null'
);
}
$sth->finish;
$dbh->disconnect;
}
OK, that's a bit better. Now it's a little easier to see what's going on. Looking at your logic, you've got two sets of variables that you're trying to handle the NULL case for. If the 'ra' date element is missing, then you want the 'ra' and 'rr' elements to be 'null'. Similarly, if the 'bbl' element is missing, you want to set 'bbl', 'irl' and 'lux' to 'null'. If you just edit the $json structure, you can fix the data in your if statements, and then execute the statement later, further simplifying your code to:
sub process_data {
if ($debug) {
say "Processing polled data...."
}
my $json_line = shift(@_);
my $dt = DateTime->now();
$dt->set_time_zone( 'America/Chicago' );
my $sth;
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile" ,"","");
$q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?, ?,
+?, ?, ?, ?, ?, ?, ?, ?)";
$sth = $dbh->prepare($q);
if ( not exists($json->{'ra'}) ) {
$json->{ra} = $json->{rr} = 'null';
}
if ( not exists($json->{'bbl'}) ) {
$json->{bbl} = $json->{irl} = $json->{lux} = 'null';
}
$sth->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(),
$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $json->
+{'tC'},
$json->{'bp'}, $json->{'rh'}, $json->{'li'}, $json->
+{'ov'},
$json->{'lux'}, $json->{'bbl'}, $json->{'irl'}, $js
+on->{'ra'}, $json->{'rr'}
);
$sth->finish;
$dbh->disconnect;
}
That's certainly easier to understand. Now let's take a look at performance/efficiency: The way you have things right now, for each record you're connecting to the database, preparing your statement, editing your data, executing the insert statement, and then closing the database connection. In typical database applications, you'd want to reuse your database connection and insert statement to reduce the amount of work your system has to perform. For your current application, it may not be a concern, but in the general case of database wrangling, you can often gain a good bit of performance by reusing connections and statement handles. So I'd restructure your code a little:
# Up above your main loop
my $DBH;
my $STH = setup_database();
while (1) {
. . . SNIP . . .
}
$DBH->disconnect;
sub setup_database {
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile" ,"","");
my $q = "insert into weather_data values (?, ?, ?, ?, ?, ?, ?, ?,
+?, ?, ?, ?, ?, ?, ?, ?, ?)";
return $dbh->prepare($q);
}
sub process_data {
if ($debug) {
say "Processing polled data...."
}
my $json_line = shift(@_);
my $dt = DateTime->now();
$dt->set_time_zone( 'America/Chicago' );
if ( not exists($json->{'ra'}) ) {
$json->{ra} = $json->{rr} = 'null';
}
if ( not exists($json->{'bbl'}) ) {
$json->{bbl} = $json->{irl} = $json->{lux} = 'null';
}
$STH->execute(undef,$dt->datetime(), $dt->ymd(), $dt->hms(),
$json->{'wd'}, $json->{'wv'}, $json->{'tF'}, $json->
+{'tC'},
$json->{'bp'}, $json->{'rh'}, $json->{'li'}, $json->
+{'ov'},
$json->{'lux'}, $json->{'bbl'}, $json->{'irl'}, $js
+on->{'ra'}, $json->{'rr'}
);
}
(Note: you rarely need to use the $STH->finish() function. In fact, I can't recall a case where I ever needed it, and I've done a *LOT* of database wrangling with Perl/DBI, so I removed it. You also don't really have to call $DBH->disconnect, either, if it's at the end of your program. But there are many cases where you may wish to disconnect from the database, so I left that in, though I do it after the end of the loop.)
One big concern I would have with your code is the insert statement. If your project grows, eventually you'll have multiple programs interacting with the database. As such, you may find yourself modifying the table structures and fixing up various parts of the program. However, your insert statement uses the default column set of your weather_data table. If you ever alter the order of columns in your table, you may get subtle bugs in your system.
In fact, you've already encountered this issue to a minor extent: you're always passing in undef as the first parameter in your execute statement because the first column in your weather_data table is a value you're not getting in your data. It's considered to be better practice to explicitly list what you're going to pull out of or insert into your database. That way you can change column orders, add columns and delete columns with fewer chances for errors. So the first thing I'd do is change your insert statement to something like the following, and delete the first parameter from your execute statement:
my $q = "insert into weather_data("
. " reading_DTM, reading_YMD, reading_HMS, wd,"
. " wv, tF, tC, bp,"
. " rh, li, ov, lux,"
. " bbl, irl, ra, rr"
. ")"
. "values ("
. " ?, ?, ?, ?,"
. " ?, ?, ?, ?,"
. " ?, ?, ?, ?,"
. " ?, ?, ?, ?"
. ")";
You'll note that I've split the insert statement into multiple lines and added a lot of whitespace. (I do it this way to make things line up, as it helps me catch errors more easily. In fact, I'd normally add \n before the closing quote on each line, so if I happen to print $q during debugging, it'll look the way I expect it to.) I also just guessed the column names, and I'm certainly wrong, so you'll have to edit the names appropriately.
With those changes, I'd be pretty happy with the code.
Here's where I ran out of time, so the rest of my response is a few other bits and random thoughts that occurred to me while editing, that I couldn't integrate cleanly into my reply, but didn't want to delete, so I'm just leaving them as appendices...
Appendix 1: Possible issue with editing the $json data in place
One objection you might have to editing the $json structure is that you might reuse it later in your code, and the edits could be harmful to that other code. In that case, you might want to make a copy of the data before editing it:
sub process_data {
my $rData = shift; # Fetch the reference to the $json data
my $json = { %$data }; # Make a copy of it
. . . the rest of the subroutine . . .
}
The my $json line might be a bit much for a beginner in Perl, but it's telling perl to make a new hash reference and then copy the keys and values from the original into it:
my $json = # create a new scalar
{ # and we'll make it a new hash reference
+ containing:
% # the list of key/value elements in the
+hash
$data # in the $data hash reference
};
Copying it this way lets you use the rest of the subroutine essentially unmodified. But since using hash references adds that extra '->' for each member access, and you're making a copy of it anyway, why not copy it into a local hash instead and save a little typing? You could do it like this:
sub process_data {
if ($debug) {
say "Processing polled data...."
}
my $data = shift(@_);
my %JSON = %$data; ### Copy the data into a hash ###
my $dt = DateTime->now();
$dt->set_time_zone( 'America/Chicago' );
if ( not exists($JSON{ra}) ) {
$JSON{ra} = $JSON{rr} = 'null';
}
if ( not exists($JSON{bbl}) ) {
$JSON{bbl} = $JSON{irl} = $JSON{lux} = 'null';
}
$STH->execute($dt->datetime(), $dt->ymd(), $dt->hms(), $JSON{'w
+d'},
$JSON{'wv'}, $JSON{'tF'}, $JSON{'tC'}, $JSON{'b
+p'},
$JSON{'rh'}, $JSON{'li'}, $JSON{'ov'}, $JSON{'l
+ux'},
$JSON{'bbl'}, $JSON{'irl'}, $JSON{'ra'}, $JSON{'r
+r'}
);
}
You'll notice that we got rid of a good few uses of '->' by using the local hash.
Hash Slicing
If you really want to avoid typing, there's an intermediate technique called "hash slicing" that lets you access multiple entries in a hash at the same time. So rather than listing each entry in the hash like this:
$STH->execute($dt->datetime(), $dt->ymd(), $dt->hms(), $JSON{'w
+d'},
$JSON{'wv'}, $JSON{'tF'}, $JSON{'tC'}, $JSON{'b
+p'},
$JSON{'rh'}, $JSON{'li'}, $JSON{'ov'}, $JSON{'l
+ux'},
$JSON{'bbl'}, $JSON{'irl'}, $JSON{'ra'}, $JSON{'r
+r'}
);
you could tell perl to give you a list of values from the hash like this:
$STH->execute($dt->datetime(), $dt->ymd(), $dt->hms(),
@JSON{ 'wd', 'wv', 'tF', 'tC', 'bp', 'rh',
'li', 'ov', 'lux', 'bbl', 'irl', 'ra', 'rr'
}
);
...roboticus
When your only tool is a hammer, all problems look like your thumb. |