packager text,
build_time integer default (strftime('%s', 'now')),
install_time integer,
- checksum text, -- checksum of package contents. null for incompleted packages
+ hash text, -- see integ.c for package hash
primary key (package,version,release),
check (typeof(package) = 'text'),
check (typeof(version) = 'text'),
;
create index package_status_index on packages (status);
+create index package_package_index on packages (package);
create view packages_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
packager = NEW.packager,
build_time = NEW.build_time,
install_time = NEW.install_time,
- checksum = NEW.checksum
+ hash = NEW.hash
where package = OLD.package
and version = OLD.version
and release = OLD.release
foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);
--- packagefile hash is columns as text, joined with null bytes, then
--- sha256 sum of that
--- package checksum is package columns as text, joined with null bytes,
--- other than the checksum and install_time column
--- then that hashed. finally, that hash, plus the ascii sorted
--- hashes of the package files all joined with newlines, hashed.
--- really don't like this.
-
-- files contained in a package
create table packagefiles (
-- package id triple
)
without rowid
;
+create index packagefile_package_index on packagefiles (package);
+create index packagefile_path_index on packagefiles (path);
+create index packagefile_hash_index on packagefiles (hash);
create view packagefiles_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *,
without rowid
;
-create table elfinfo (
- file text primary key, -- hash of blob
- elftype text
-)
-without rowid
-;
-
-create table elfdeps (
- file text,
- soname text,
- dependency text,
- primary key (file, soname, dependency)
-)
-without rowid
+create view elfdeps as
+select PF.pkgid, PF.status, PF.path, N.needed as needs,
+PL.path as library, PL.pkgid provider, PL.status as library_status
+from packagefiles_status PF
+join elfneeded N on N.file = PF.hash
+left join elflibraries L on N.needed = L.soname
+left join packagefiles_status PL on PL.hash = L.file
;
-- TODO just elf information?
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 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,
+ target,devminor,devmajor
+ 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.target, SS.devminor, SS.devmajor
+ 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
+),
+preserve as (
+ select distinct
+ path,username,uid,groupname,gid,mode,filetype,mtime,hash,
+ target,devminor,devmajor
+ from syncstatus SS
+ 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,
+ target,devminor,devmajor
+ 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,
+ target,devminor,devmajor
+ 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, * from modified
+union
+select 'remove' as op, * from remove
+union
+select 'obsolete' as op, * from expired
+union
+select 'new' as op, * from newfiles
+union
+select 'preserve' as op, * from preserve
+;
+
commit;