| 80 | self.execute("DELETE FROM data WHERE ?", {"site_id": site_id}) |
| 81 | |
| 82 | def archive(self): |
| 83 | week_back = 1 |
| 84 | while 1: |
| 85 | s = time.time() |
| 86 | date_added_from = time.time() - 60 * 60 * 24 * 7 * (week_back + 1) |
| 87 | date_added_to = date_added_from + 60 * 60 * 24 * 7 |
| 88 | res = self.execute(""" |
| 89 | SELECT |
| 90 | MAX(date_added) AS date_added, |
| 91 | SUM(value) AS value, |
| 92 | GROUP_CONCAT(data_id) AS data_ids, |
| 93 | type_id, |
| 94 | site_id, |
| 95 | COUNT(*) AS num |
| 96 | FROM data |
| 97 | WHERE |
| 98 | site_id IS NULL AND |
| 99 | date_added > :date_added_from AND |
| 100 | date_added < :date_added_to |
| 101 | GROUP BY strftime('%Y-%m-%d %H', date_added, 'unixepoch', 'localtime'), type_id |
| 102 | """, {"date_added_from": date_added_from, "date_added_to": date_added_to}) |
| 103 | |
| 104 | num_archived = 0 |
| 105 | cur = self.getCursor() |
| 106 | for row in res: |
| 107 | if row["num"] == 1: |
| 108 | continue |
| 109 | cur.execute("INSERT INTO data ?", { |
| 110 | "type_id": row["type_id"], |
| 111 | "site_id": row["site_id"], |
| 112 | "value": row["value"], |
| 113 | "date_added": row["date_added"] |
| 114 | }) |
| 115 | cur.execute("DELETE FROM data WHERE data_id IN (%s)" % row["data_ids"]) |
| 116 | num_archived += row["num"] |
| 117 | self.log.debug("Archived %s data from %s weeks ago in %.3fs" % (num_archived, week_back, time.time() - s)) |
| 118 | week_back += 1 |
| 119 | time.sleep(0.1) |
| 120 | if num_archived == 0: |
| 121 | break |
| 122 | # Only keep 6 month of global stats |
| 123 | self.execute( |
| 124 | "DELETE FROM data WHERE site_id IS NULL AND date_added < :date_added_limit", |
| 125 | {"date_added_limit": time.time() - 60 * 60 * 24 * 30 * 6 } |
| 126 | ) |
| 127 | # Only keep 1 month of site stats |
| 128 | self.execute( |
| 129 | "DELETE FROM data WHERE site_id IS NOT NULL AND date_added < :date_added_limit", |
| 130 | {"date_added_limit": time.time() - 60 * 60 * 24 * 30 } |
| 131 | ) |
| 132 | if week_back > 1: |
| 133 | self.execute("VACUUM") |