I am very close to releasing an alpha of the pure perl, join-enabled
SQL::Statement. Among other questions, is one related to the table
opening/flocking/closing behavior of the current SQL::Statement.
Basically it now operates such that tables are opened (and flocked if
DBD::File is used and flock is available) when a statement is prepared
and then closed when the statement is done executing. AFAIK, this means
that the kind of code shown below will introduce a race condition
between the time the user reads the data and when they update it:
prepare statement selectA
execute statement selectA
while ( fetch from selectA ) {
push id onto arrayA if some condition
}
# table is closed and lock released here
prepare statement updateB
# table is reopened here
for (@arrayA) {
execute updateB where id = id
}
Now obviously, this isn't good coding style, but it seems like the kind
of thing many inexperienced users will try and I doubt even good
documentation would prevent them from doing so or even make them aware
of the dangers introduced.
Another problem is that SQL::Statement truncates and rewrites the entire
file every time an update or delete statement is done executing so that
a single update statement followed by a single delete statement will
open-truncate-rewrite-close the entire file twice.
For both speed and data-security reasons, I would like to offer an
alternative mode of operation for SQL::Statement that behaves more like
the AnyData tied-hash interface in which a) files remain open and
flocked until they are specifically closed by the user or the variable
holding them goes out of scope and b) deletions are performed by adding
a record's tell() into a hash and only physically performed when the
file is closed. This means that the unocking and the
truncating-rewriting only happens once for each file regardless of how
many operations are performed.
A side benefit of this approach will be that SQL::Statement will be able
to send updated rows as single rows, not as an entire array of all rows
which will be of great benefit to things like the AnyData::Format::SNMP
that Wes Hardaker is finalizing.
So, is this all a good idea and, if so, a) should I use something like
$dbh->func($tname,'ad_close_table') method to give users control or just
let the tables close with $dbh->disconnect and/or $dbh going out of
scope, and b) should I support both methods of opening/closing styles to
support other subclasses of SQL::Statement that want to stick with the
old style?
I would value any thoughts that anyone has.
--
Jeff
