i.e. I've broken my $rowcount = $dbh->do("SELECT * FROM TABLE");

That was sort of broken from the beginning, I'm afraid. As PodMaster notes, the DBI does not guarantee that a method returns a row count for a SELECT, so using $dbh->do to get a row count doesn't seem to be kosher.

I would go for option number 2, trying to mimic DBD::mysql approach, where you have "store_result" (when you get all the records at once) or "use_result" (when you get the records one-by-one). You may leave the old method as behavior, so existing code would not be broken, and the new method as optional for each statement handler (e.g. $sth= $dbh->prepare($query, {sqlite_use_result => 1})).

Getting the numbers of rows in this scenario would follow the DBI standards. If you use selectall_arrayref or fetchall_arrayref, the number of rows is given by the resulting array size. Using the fetchrow_* methods, you need to count manually, the same way you do it for other drivers.

Concerning the suggestion of integrating the SELECT call with a separate COUNT(*) statement, I would rather not do that, since it is a kind of intrusive coding that is difficult to control. For example, given this query

SELECT
    field1, field2, SUM(field3)
FROM
    table1, table2, table3
WHERE
    table1.id1 = table2.id1
    AND table2.id2=table3.id2
GROUP BY
    field1, field2
[download]

How would you modify it to get a count? You must get the count of the aggregated columns, but if this is an expensive query, the count would be almost as expensive as the original query. The same is true for WHERE clauses with heavy calculations. In this case, I would advise to leave such tricks to the users imagination.