Hi all;
Sorry if I'm asking a silly question - I'm very inexperienced with
databases. I'm playing around with using DBI / SQLite as a back-end for
parsing and storing log files - I have multiple columns like "log date",
"logged by", etc. I parse these in a Perl script, and then store them in
the SQLite database. I would like to increment a "count" column if I
parse the same log line twice: on the first time I see that log line, I
parse it and store it in its own row, and on subsequent occasions I
increment a "count" column to say that I have seen that log line "count"
amount of times. Some quick Googling has shown some stuff to do with
auto increments, but that seemed to be much more to do with primary keys
and wasn't guaranteed to be in order / contiguous. I've also seen MySQL
syntax like "ON DUPLICATE KEY UPDATE ..." which looks good, but I'd like
to avoid an extra dependency on a database that has to be set up and
maintained (hence SQLite).
My preference for a solution would be something that's simple and
maintainable primarily, followed by fast (for reads first, writes
second) - but I don't have the database experience to know what the best
solution is. Is there an easy way? Should I be storing duplicate rows
and counting them at display time?
Using hashes, this works something like this (but I have more data than
I can safely store in memory with a hash, and sorting and merging
subhashes on top of this makes things tricky):
my %lineCount;
while (<>) {
chomp();
$lineCount{$_}++;
}
Using DBI, the best I've been able to get has been this:
my $dbh = DBI->connect("DBI:SQLite:dbname=mydb.sqldb","","");
$dbh->do("CREATE TABLE $table (line CHAR(100) PRIMARY KEY,
count INT)");
# read data
while(<>) {
chomp();
my $seenCount = 0;
my $rowRef =
$dbh->selectall_arrayref("SELECT * FROM $table WHERE line = ?",
undef, $_);
unless (@$rowRef) {
# don't insert row if it's already inserted
$dbh->do("INSERT INTO $table VALUES(?, ?)",
undef, $_, $seenCount);
}
$dbh->do("UPDATE $table SET count = count + 1 WHERE line = ?",
undef, $_);
}
Thanks in advance for any help.
