
-- Entries coming from known sources.
CREATE TABLE storage (
       md5    TEXT NOT NULL,
       path   TEXT NOT NULL,
       size   INTEGER NOT NULL,
       host   TEXT NOT NULL,
       mtime  INTEGER NOT NULL,
       --  origin TEXT,   -- for backups there is an origin == working directory
       PRIMARY KEY(path, md5)
);


-- Table created from hashed store. 
CREATE TABLE backup (
       md5    TEXT NOT NULL,
       size   INTEGER NOT NULL,
       --  origin TEXT,   -- for backups there is an origin == working directory
       PRIMARY KEY(md5)
);


CREATE VIEW dupes AS
select *,count(*) as nb from storage group by md5 order by -count(*);

CREATE VIEW recent AS
select md5,path,max(mtime) as mtime, count(*) as nb from storage group by path;
-- select * from recent order by -nb limit 10;

CREATE VIEW storage_sorted AS
select * from storage order by host, path, md5, size;

CREATE VIEW untracked AS
select md5,size from backup except select md5,size from storage;
