--sweep_ramdisk.sql

--copies ramdisk to ssd
--run before ramdisk gets full; you size ramdisk in fstab
--ramdisk is lost between system boots
--maintain last 3 days of meter readings in ramdisk
--ramdisk is used as meter data to avoid write-wear on ssd
--# create ramdisk
--tmpfs /var/www/html/AIO/ramdisk tmpfs nodev,nosuid,size=6M 0 0 

--or, run with cron
--00 */6 * * *       (sleep 15; cd /var/www/html/AIO/ && /usr/bin/sqlite3 -batch ':memory:' '.read sweep_ramdisk.sql')


.mode column
.echo on

.open ':memory:'
PRAGMA busy_timeout=20000;

attach database '/var/www/html/AIO/aiox.db' as ssd;
attach database '/var/www/html/AIO/ramdisk/aiox.db' as ram;
.tables

--archive tables to ssd
begin transaction ;
insert or ignore into ssd.irx select * from  ram.irx where strftime('%M', dt) % 10 = 0;
insert or ignore into ssd.ir  select * from  ram.ir  where strftime('%M', dt) % 10 = 0;
insert or ignore into ssd.hrx select * from  ram.hrx;
insert or ignore into ssd.hr  select * from  ram.hr;
end transaction ;

--trim/prune old data from ram
select 'Removing data before ', date(max(dt), '-3 days') from ram.irx;
begin transaction ;

delete from ram.irx where dt <  (select date(max(dt), '-3 days') from ram.irx);
select changes();

delete from ram.ir where dt <  (select date(max(dt), '-3 days') from ram.ir);
select changes();

end transaction ;


