statistics_history_daily handling : drop/create within the transaction
-- Contains history of the statistics_history table
-- Data are historicized daily
--
+BEGIN TRANSACTION;
+
+DROP INDEX IF EXISTS fledge.statistics_history_daily_ix1;
+DROP TABLE IF EXISTS fledge.statistics_history_daily;
+
CREATE TABLE fledge.statistics_history_daily (
year DATE DEFAULT (STRFTIME('%Y', 'NOW')),
day DATE DEFAULT (STRFTIME('%Y-%m-%d', 'NOW')),
@@ -8,11 +13,10 @@ CREATE TABLE fledge.statistics_history_daily (
value bigint NOT NULL DEFAULT 0
);
-CREATE INDEX statistics_history_daily_ix1
+CREATE INDEX fledge.statistics_history_daily_ix1
ON statistics_history_daily (year);
--- statistics_history_daily ------------------------------------------------------------------:
-BEGIN TRANSACTION;
INSERT INTO fledge.statistics_history_daily
(year, day, key, value)
@@ -26,13 +30,16 @@ WHERE history_ts < datetime('now', '-7 days')
GROUP BY date(history_ts), key;
DELETE FROM fledge.statistics_history WHERE history_ts < datetime('now', '-7 days');
-COMMIT;
+
--- -----------------------------------------------------------------------------------------:
DELETE FROM fledge.tasks WHERE start_time < datetime('now', '-30 days');
DELETE FROM fledge.log WHERE ts < datetime('now', '-30 days');
--- Insert purge system schedule and process entry
+DELETE FROM fledge.scheduled_processes WHERE name = 'purge_system';
+DELETE FROM fledge.schedules WHERE id = 'd37265f0-c83a-11eb-b8bc-0242ac130003';
+
INSERT INTO fledge.scheduled_processes (name, script) VALUES ('purge_system', '["tasks/purge_system"]');
INSERT INTO fledge.schedules (id, schedule_name, process_name, schedule_type, schedule_time, schedule_interval, exclusive, enabled)
VALUES ('d37265f0-c83a-11eb-b8bc-0242ac130003', -- id
@@ -44,3 +51,5 @@ VALUES ('d37265f0-c83a-11eb-b8bc-0242ac130003', -- id
't', -- exclusive
't' -- enabled
);
+
+COMMIT;
\ No newline at end of file
