I don’t know if there was already such a topic, and if there is such functionality, I deleted duplicates from the database, its size decreased from 8.6 MB to 2.5 MB
code:
SELECT COUNT(*) FROM history h;
-- Removing duplicates
-- Creating a temporary table with unique records
WITH cte AS (
SELECT
id
FROM history
WHERE deleted_at IS NULL
GROUP BY TRIM(command)
HAVING MIN(id) = id
)
-- SELECT COUNT(*) FROM cte;
-- Deleting records that are not in the temporary table
DELETE FROM history
WHERE id NOT IN (
SELECT id FROM cte
);
-----------------------------
VACUUM;
SELECT COUNT(*) FROM history h;
I’m afraid if you run things like this, you’re going to break sync. Deletions have to occur through our CLI in order to ensure they are correctly marked as deleted
hi, happy new year, updated to atuin 18.4.0, again I delete duplicates using a proven method locally, but how to do it through the cli maybe I don’t know something)
root@proxmox2:~# ls -lh /root/.local/share/atuin/history.db
-rw------- 1 root root 9.1M Jan 6 13:35 /root/.local/share/atuin/history.db
root@proxmox2:~# sqlite3 /root/.local/share/atuin/history.db
SQLite version 3.40.1 2022-12-28 14:03:47
Enter “.help” for usage hints.
sqlite> SELECT COUNT() FROM history h;
25079
sqlite> WITH cte AS (
…> SELECT
…> id
…> FROM history
…> WHERE deleted_at IS NULL
…> GROUP BY TRIM(command)
…> HAVING MIN(id) = id
…> )
…> – SELECT COUNT() FROM cte;
…> – Deleting records that are not in the temporary table
…> DELETE FROM history
…> WHERE id NOT IN (
…> SELECT id FROM cte
…> );
sqlite> VACUUM;
sqlite> SELECT COUNT(*) FROM history h;
6414
sqlite>
root@proxmox2:~# ls -lh /root/.local/share/atuin/history.db
-rw------- 1 root root 2.9M Jan 6 13:36 /root/.local/share/atuin/history.db
root@proxmox2:~#