+
+-- zpm actions
+-- not sure how machine readable this needs to be,
+-- do not at all for now, figure it out later
+-- could be worth logging all commands in a history table,
+-- the zpm driver could do that and capture the exit status
+-- as well
+-- might want the history table to note a "group" to tie together
+-- sub-invocations, probably an environment variable set if not
+-- already set by zpm, probably a uuid or a timestamp
+create table zpmlog (
+ ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
+ -- timestamp of action
+ action text,
+ target text, -- packagename, repo name, etc
+ info text -- human readable
+);
+
+create table notes (
+ id integer primary key, -- rowid alias
+ ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
+ note text not null,
+ pkgid text, -- package
+ path text, -- file path involved
+ file text, -- hash of file
+ ack integer default 0
+);
+
+create table history (
+ ts integer, -- again, probably needs timestamp sub second
+ cmd text,
+ args text,
+ status integer
+);
+
+create table repository (
+ name text primary key, -- our name for a repo
+ url text not null,
+ priority integer not null default 1,
+ refreshed integer -- last refresh time
+);
+-- force the url to be repourl/info.repo
+-- package urls repourl/pkgid.zpm
+
+-- urls for downloading packages. possibly unneeded
+create table repository_packages (
+ repo text,
+ pkg text, -- glob pattern? in which case others not needed
+ version text,
+ release text,
+ url text
+);
+
+-- track which repository a package was cloned from, i.e. where we got it
+create table packagesource (
+ name text,
+ version text,
+ release integer,
+ repository text references repository
+);
+
+create view syncconflicts as
+with
+preserved as (
+ select BASE.*, 'preserved' as rstatus
+ from packagefiles_status BASE
+ join elflibraries EL on EL.file = BASE.hash
+ where
+ BASE.status in ('removed', 'updated')
+ and BASE.hash in (select hash from packagefiles_status where
+ status in ('installed'))
+),
+syncstatus as (
+ select distinct BASE.*,
+ case when P.status = 'installing' and BASE.status = 'installed'
+ then 'updating'
+ when BASE.status in ('removed','updated')
+ and BASE.path in (select path from preserved) then
+ 'preserved'
+ else
+ BASE.status
+ end as rstatus
+ from packagefiles_status BASE
+ left join packages P on P.package = BASE.package
+ and BASE.status in ('installed', 'removing')
+ and P.status = 'installing'
+),
+-- metadata different
+md_conflict as (
+ select path, count(distinct mds) as mdcount,
+ count(distinct hash) as hashcount
+ from syncstatus SS
+ where SS.rstatus in ('installing', 'installed')
+ group by path
+ having (count(distinct mds) > 1 or count(distinct hash) > 1)
+)
+select BASE.*, 'hash' as conflict
+from syncstatus BASE
+where path in (select path from md_conflict where hashcount > 1)
+union
+select BASE.*, 'md' as conflict
+from syncstatus BASE
+where path in (select path from md_conflict where mdcount > 1)
+;
+
+create view needed_libraries as
+with recursive
+libs(file,needs,provider) as (
+ select N.file, N.needed as needs, L.file as provider
+ from elfneeded N left join elflibraries L on N.needed = L.soname
+ union
+ select L.file, N.needed as needs, EL.file as provider
+ from libs L
+ join elfneeded N on N.file = L.provider
+ left join elflibraries EL on N.needed = EL.soname
+)
+select * from libs;
+
+create view syncinfo as
+with
+preserved as (
+ select BASE.*, 'preserved' as rstatus
+ from packagefiles_status BASE
+ join elflibraries EL on EL.file = BASE.hash
+ where
+ BASE.status in ('removed', 'updated')
+ and BASE.hash in (select hash from packagefiles_status where
+ status in ('installed'))
+),
+syncstatus as (
+ select distinct BASE.*,
+ case when P.status = 'installing' and BASE.status = 'installed'
+ then 'updating'
+ when BASE.status in ('removed','updated')
+ and BASE.path in (select path from preserved) then
+ 'preserved'
+ else
+ BASE.status
+ end as rstatus
+ from packagefiles_status BASE
+ left join packages P on P.package = BASE.package
+ and BASE.status in ('installed', 'removing')
+ and P.status = 'installing'
+),
+-- new file: in installing, not in installed or updating or removing
+newfiles as (
+ select distinct
+ path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+ configuration,target,device, null as ohash
+ from syncstatus SS
+ where path not in (select path from syncstatus where
+ rstatus in ('installed', 'updating', 'removing')
+ )
+ and rstatus in ('installing')
+),
+-- modified: retained, but with different metadata
+modified as (
+ select distinct
+ SS.path,
+ SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
+ SS.mtime, SS.hash,
+ SS.configuration + case when OS.configuration = 1 then 2 else 0 end
+ as configuration,
+ SS.target, SS.device,
+ OS.hash as ohash, SS.mds, OS.mds as omds
+ from syncstatus SS
+ join syncstatus OS
+ on SS.path = OS.path and SS.pkgid is not OS.pkgid
+ -- preserved?
+ and OS.rstatus in ('installed','updating','removing')
+ and (SS.mds is not OS.mds or SS.hash is not OS.hash)
+ where
+ SS.rstatus in ('installing')
+),
+-- preserve: libraries needed by something in installed or installing
+/*
+needed as (
+ select distinct
+ ED.library
+ from elfdeps ED
+ where status in ('installed', 'installing')
+ and library is not null
+),
+*/
+needed as (
+ select NL.file as needslib, NL.provider as libraryhash
+ from needed_libraries NL
+ inner join syncstatus SS on SS.hash = NL.file
+ and SS.status in ('installed', 'installing')
+),
+preserve as (
+ select distinct
+ path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+ configuration,target,device, null as ohash
+ from syncstatus SS
+ join needed N on SS.hash = N.libraryhash
+ where SS.rstatus in ('removing', 'removed')
+-- where SS.hash in (select libraryhash from needed)
+-- where path in (select library from needed)
+-- and SS.rstatus in ('removing', 'removed')
+),
+-- remove: cur, not preserved, not in final set
+remove as (
+ select distinct
+ path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+ configuration,target,device, null as ohash
+ from syncstatus SS
+ where path not in (
+ select path from syncstatus where
+ rstatus in ('installed', 'installing')
+ )
+ and path not in (select path from preserve)
+ and rstatus in ('removing', 'updating')
+),
+-- expired: libraries that had been preserved, but aren't needed now
+expired as (
+ select distinct
+ path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+ configuration,target,device, null as ohash
+ from syncstatus BASE
+ where hash in (select file from elflibraries where file is not null)
+ and path not in (select path from preserve)
+ and rstatus in ('removed','updated')
+)
+select 'update' as op, *
+,(select group_concat(pkgid, ' ')
+ from syncstatus SS
+ where SS.rstatus = 'installing' and SS.path = M.path
+) as pkglist
+from modified M
+union
+select 'remove' as op, *, null, null, null from remove
+union
+select 'obsolete' as op, *, null, null, null from expired
+union
+select 'new' as op, *, null, null, null from newfiles
+union
+select 'preserve' as op, *, null, null, null from preserve
+;
+