)
;
+create view filerefs as
+select F.hash,
+count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
+from files F
+left join packagefiles PF on PF.hash = F.hash
+left join scripts S on S.hash = F.hash
+left join elflibraries EL on EL.file = F.hash
+left join notes N on N.file = F.hash
+group by F.hash
+;
+
-- information about packages
-- a package is identified by a package,version,release triple
create table packages (
-- primary key columns
- package text,
- version text, -- the upstream version string
- release integer, -- the local release number
- pkgid text, -- the three above joined with '-'
+ package text not null,
+ version text not null, -- the upstream version string
+ release integer not null, -- the local release number
-- metadata columns
description text,
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 details
primary key (package,version,release),
+ check (typeof(package) = 'text'),
+ check (typeof(version) = 'text'),
check (typeof(release) = 'integer'),
- check (release > 0)
- -- TODO enforce name and release conventions
+ check (release > 0),
+ -- enforce name and version conventions
+ check(instr(package,' ') = 0),
+ check(instr(package,'/') = 0),
+ check(instr(package,':') = 0),
+ check(instr(version,' ') = 0),
+ check(instr(version,'-') = 0),
+ check(instr(version,'/') = 0),
+ check(instr(version,':') = 0),
+ check(length(package) < 64),
+ check(length(package) > 0),
+ check(length(version) < 32),
+ check(length(version) > 0)
)
without rowid
;
+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, *
+from packages;
+
+create trigger packages_delete_trigger instead of
+delete on packages_pkgid
+begin
+ delete from packages where package = OLD.package
+ and version = OLD.version and release = OLD.release;
+end;
+
+create trigger packages_update_trigger instead of
+update on packages_pkgid
+begin
+ update packages
+ set package = NEW.package,
+ version = NEW.version,
+ release = NEW.release,
+ description = NEW.description,
+ architecture = NEW.architecture,
+ url = NEW.url,
+ status = NEW.status,
+ licenses = NEW.licenses,
+ packager = NEW.packager,
+ build_time = NEW.build_time,
+ install_time = NEW.install_time,
+ hash = NEW.hash
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ ;
+end
+;
+
-- handle package status history with a logging trigger.
create trigger logpkgstatus after update of status on packages
begin insert into zpmlog (action,target,info)
tag text,
set_time integer default (strftime('%s', 'now')),
primary key (package,version,release,tag),
- foreign key (package,version,release) references packages (package,version,release) on delete cascade
+ 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
release integer,
path text, -- filesystem path
- mode text, -- perms, use text for octal rep?
- username text, -- name of owner
- groupname text, -- group of owner
+ mode text not null default '0644', -- perms, use text for octal rep?
+ username text not null default 'root', -- name of owner
+ groupname text not null default 'root', -- group of owner
uid integer, -- numeric uid, generally ignored
gid integer, -- numeric gid, generally ignored
- filetype varchar default 'r',
+ configuration integer not null default 0, -- boolean if config file
+ confhash text, -- last hash on disk
+ filetype varchar not null default 'r',
-- r regular file
-- d directory
- -- s symlink
+ -- l symlink
-- h hard link -- not supported
- -- c character special and b device special files add dev number column
- -- b block special
- -- p fifos (i.e. pipe)
+ -- c character special -- not supported
+ -- b block special -- not supported
+ -- c and b device special files add dev number column
+ -- p fifos (i.e. pipe) -- not supported
+ -- s unix domain socket -- not supported
target text, -- link target for links
- -- device file dev numbers, should probably be a separate table
- devmajor integer,
- devminor integer,
- hash text, -- null if no actual content, i.e. anything but a regular file
- mtime integer, -- seconds since epoch, finer resolution probably not needed
+ device integer, -- device file dev_t
+ hash text, -- null if not a regular file
+ mtime integer, -- seconds since epoch, finer resolution not needed
primary key (package,version,release,path),
- foreign key (package,version,release) references packages (package,version,release) on delete cascade
+ foreign key (package,version,release)
+ references packages (package,version,release)
+ on delete cascade on update cascade,
+ check (not (filetype = 'l' and target is null)),
+ check (not (filetype = 'h' and target is null)),
+ check (target is null or length(target) between 1 and 4095),
+ check (hash is null or length(hash) between 1 and 1024),
+ check (not (filetype = 'r' and hash is null)),
+ check (not (filetype = 'c' and device is null)),
+ check (not (filetype = 'b' and device is null)),
+ check (filetype in ('r','d','l','h','c','b','p')),
+ check(length(username) between 1 and 256),
+ check(length(groupname) between 1 and 256),
+ check (configuration = 0 or configuration = 1)
)
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, *,
+printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
+from packagefiles
+;
+
+create trigger packagefiles_update_trigger instead of
+update on packagefiles_pkgid
+begin
+ update packagefiles
+ set package = NEW.package,
+ version = NEW.version,
+ release = NEW.release,
+ path = NEW.path,
+ mode = NEW.mode,
+ username = NEW.username,
+ groupname = NEW.groupname,
+ uid = NEW.uid,
+ gid = NEW.gid,
+ configuration = NEW.configuration,
+ filetype = NEW.filetype,
+ target = NEW.target,
+ device = NEW.device,
+ hash = NEW.hash,
+ mtime = NEW.mtime
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ and path = OLD.path
+ ;
+end
+;
+
+create trigger packagefiles_delete_trigger instead of
+delete on packagefiles_pkgid
+begin
+ delete from packagefiles
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ and path = OLD.path
+ ;
+ update packages set hash = null
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ ;
+end
+;
+
+create view installed_ref_count as
+select I.path, count(*) as refcount
+from installedfiles I
+group by I.path
+;
+
+create view sync_status_ref_count as
+select path, status, count(*) as refcount
+from packagefiles_status
+where status in ('installed', 'installing', 'removing')
+group by path, status
+;
+
+create view packagefiles_status as
+select P.status, PF.*
+from packagefiles_pkgid PF
+left join packages_pkgid P on P.pkgid = PF.pkgid
+;
+
+create view installedfiles as
+select * from packagefiles_status
+where status = 'installed'
+;
+
create table pathtags (
-- package id triple
package text,
path text, -- filesystem path
tag text,
- primary key (package,version,release,path,tag)
+ primary key (package,version,release,path,tag),
+ foreign key (package,version,release,path)
+ references packagefiles on delete cascade on update cascade
)
without rowid
;
-create table elfinfo (
- file text primary key, -- hash of blob
- elftype text,
- foreign key (file) references files on delete cascade
-)
-without rowid
-;
-
-create table elfdeps (
- file text,
- soname text,
- dependency text,
- primary key (file, soname, dependency),
- foreign key (file) references files on delete cascade
-)
-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?
-- and just hash, not package?
create table elflibraries (
file text primary key,
- soname text,
- foreign key (file) references files on delete cascade
+ soname text
)
without rowid
;
+create index elf_library_name_index on elflibraries(soname);
create table elfneeded (
- file text,
+ file text, -- hash of file
needed text, -- soname of dependency
- primary key (file, needed),
- foreign key (file) references files on delete cascade
+ primary key (file, needed)
)
without rowid
;
+create view package_libraries as
+select distinct PF.pkgid, EL.soname
+from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
+;
+
+create view package_libraries_needed as
+with pkglibs as (
+ select distinct EN.needed as soname, PF.pkgid
+ from elfneeded EN
+ join packagefiles_pkgid PF on PF.hash = EN.file
+ ),
+ pkgprovides as (
+ select distinct EL.soname, PF.pkgid
+ from elflibraries EL
+ join packagefiles_pkgid PF on PF.hash = EL.file
+ )
+ select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
+ from pkglibs PL
+ left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
+;
+
-- package scripts: table of package, stage, file
create table scripts (
package text,
version text,
release integer,
stage text,
- hash text
+ hash text,
+ primary key (package,version,release,stage),
+ foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);
+create view scripts_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from scripts
+;
+
-- package dependencies: table of package, dependency, dep type (package, soname)
create table packagedeps (
package text,
version text,
release integer,
- required text, -- package name
- -- following can be null for not checked
- minversion text,
- minrelease integer,
- maxversion text,
- maxrelease integer
+ requires text, -- package name (only)
+ minimum text,
+ maximum text,
+ primary key (package,version,release,package),
+ foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);
-- capability labels
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,
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 (
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
+;
+
+/*
+ * tables for repository info, essentially materalized views
+ */
+create table repository_libs (
+ pkgid text,
+ soname text
+);
+
+create table repository_libsneeded (
+ pkgid text,
+ soname text,
+ selfsat integer
+);
+
commit;
projects / zpackage / blobdiff