SQLite delete the last 25% of records in a database.
- by Steven smethurst
I am using a SQLite database to store values from a data logger. The data logger will eventually fills up all the available hard drive space on the computer. I'm looking for a way to remove the last 25% of the logs from the database once it reaches a certain limit.
Using the following code:
$ret = Query( 'SELECT id as last FROM data ORDER BY id desc LIMIT 1 ;' );
$last_id = $ret[0]['last'] ;
$ret = Query( 'SELECT count( * ) as total FROM data' );
$start_id = $last_id - $ret[0]['total'] * 0.75 ;
Query( 'DELETE FROM data WHERE id < '. round( $start_id, 0 ) );
A journal file gets created next to the database that fills up the remaining space on the drive until the script fails.
How/Can I stop this journal file from being created?
Anyway to combined all three SQL queries in to one statement?
