package text not null,
version text not null, -- the upstream version string
release integer not null, -- the local release number
--- pkgid text, -- the three above joined with '-'
-- 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 version conventions
- -- check(instr(version,'-') = 0)
- -- check(instr(package,'/') = 0)
- -- check(instr(package,'/') = 0)
- -- check(instr(version,' ') = 0)
- -- check(instr(package,' ') = 0)
- -- check(instr(package,' ') = 0)
- -- check(length(package) < 64)
- -- check(length(version) < 32)
+ 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
;
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
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
uid integer, -- numeric uid, generally ignored
gid integer, -- numeric gid, generally ignored
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 on update 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 (devmajor is null or devminor is null))),
- check (not (filetype = 'b' and (devmajor is null or devminor 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:%o:%s:%s', filetype, mode, username, groupname) as mds
+printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
from packagefiles
;
configuration = NEW.configuration,
filetype = NEW.filetype,
target = NEW.target,
- devmajor = NEW.devmajor,
- devminor = NEW.devminor,
+ device = NEW.device,
hash = NEW.hash,
mtime = NEW.mtime
where package = OLD.package
where status = 'installed'
;
-create view install_status as
-
-select 'new' as op, PN.*
-from packagefiles_status PN
-left join installed_ref_count RC on RC.path = PN.path
-where RC.refcount is null
-and PN.status = 'installing'
-
-union all
-
-select 'update' as op, PN.*
-from packagefiles_status PN
-inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
-left join installed_ref_count RC on RC.path = PN.path
-where RC.refcount = 1
-and PN.status = 'installing'
-and PI.hash is not PN.hash
-
-union all
-
-select 'conflict' as op, PI.*
-from packagefiles_status PN
-inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
-where PN.status = 'installing'
-
-union all
-select 'remove' as op, PI.*
-from installedfiles PI
-left join packagefiles_status PN
- on PI.path = PN.path and PI.package = PN.package
- and PI.pkgid != PN.pkgid
-where PN.path is null
-and PI.package in (select package from packages where status = 'installing')
-
-union all
--- remove files in removing, but not installing
-select distinct 'remove' as op, PR.*
-from packagefiles_status PR
-left join packagefiles_status PN
-on PR.path = PN.path
-and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
-where PN.path is null
-and PR.status = 'removing'
-;
-
create table pathtags (
-- package id triple
package text,
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,
newfiles as (
select distinct
path,username,uid,groupname,gid,mode,filetype,mtime,hash,
- target,devminor,devmajor
+ configuration,target,device, null as ohash
from syncstatus SS
where path not in (select path from syncstatus where
rstatus in ('installed', 'updating', 'removing')
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
+ SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
+ SS.mtime, SS.hash, SS.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
preserve as (
select distinct
path,username,uid,groupname,gid,mode,filetype,mtime,hash,
- target,devminor,devmajor
+ configuration,target,device, null as ohash
from syncstatus SS
where path in (select library from needed)
and SS.rstatus in ('removing', 'removed')
remove as (
select distinct
path,username,uid,groupname,gid,mode,filetype,mtime,hash,
- target,devminor,devmajor
+ configuration,target,device, null as ohash
from syncstatus SS
where path not in (
select path from syncstatus where
expired as (
select distinct
path,username,uid,groupname,gid,mode,filetype,mtime,hash,
- target,devminor,devmajor
+ 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, * from modified
+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, * from remove
+select 'remove' as op, *, null, null, null from remove
union
-select 'obsolete' as op, * from expired
+select 'obsolete' as op, *, null, null, null from expired
union
-select 'new' as op, * from newfiles
+select 'new' as op, *, null, null, null from newfiles
union
-select 'preserve' as op, * from preserve
+select 'preserve' as op, *, null, null, null from preserve
;
commit;
projects / zpackage / blobdiff