-- should be faster with rowid due to the blob content
-- these are really just blobs of data
+-- TODO copyright and license information should probably
+-- go here
CREATE TABLE files (
- hash text primary key,
- size integer,
- compression text,
+ hash text primary key, -- sha256 of content
+ size integer, -- bigint? certainly need > 2GB
+ compression text, -- always xz?
content blob
)
;
-- information about packages
-- a package is identified by a package,version,release triple
create table packages (
- package text,
- version text, -- the upstream version string
- release integer, -- the local release number
+ -- primary key columns
+ 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,
architecture text,
url text,
+ status text,
licenses text, -- hash of actual license? need table for more than one?
packager text,
build_time integer default (strftime('%s', 'now')),
install_time integer,
checksum text, -- checksum of package contents. null for incompleted packages
- primary key (package,version,release)
+ 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)
)
without rowid
;
+create index package_status_index on packages (status);
+
+create view packages_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from packages;
+
+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,
+ checksum = NEW.checksum
+ 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)
+ values (printf('status change %s %s', OLD.status, NEW.status),
+ printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
+ NULL); END;
+
+create table packagetags (
+ -- package id triple
+ package text,
+ version text,
+ release integer,
+ 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 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, -- perms, use text for octal rep?
+ username text not null, -- name of owner
+ groupname text not null, -- group of owner
uid integer, -- numeric uid, generally ignored
gid integer, -- numeric gid, generally ignored
- --filetype integer default 0, -- 0 regular file, 1 directory, 2 symlink
- -- regular file if null target and not null hash
- -- except that we could not know the hash, or care
- -- directory if null hash and null target
- -- symlink if null hash and not null target
- -- hard link if not null hash and not null target
- -- device special files add dev number column
- -- fifos add mode? Can encode filetype in mode.
- target text, -- link target for symlinks
+ configuration integer not null default 0, -- boolean if config file
+ filetype varchar not null default 'r',
+ -- r regular file
+ -- d directory
+ -- s 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)
+ 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
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 = '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 (configuration = 0 or configuration = 1)
)
without rowid
;
-create table elfinfo (
- file text, -- hash of blob
- elftype text,
- foreign key (file) references files on delete cascade
-);
+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
+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,
+ devmajor = NEW.devmajor,
+ devminor = NEW.devminor,
+ hash = NEW.hash,
+ mtime = NEW.mtime
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ and path = OLD.path
+ ;
+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 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,
+ version text,
+ release integer,
+
+ path text, -- filesystem path
+ tag text,
+ primary key (package,version,release,path,tag),
+ foreign key (package,version,release,path)
+ references packagefiles on delete cascade on update 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 table elfneeded (
file text,
needed text, -- soname of dependency
- primary key (file, needed),
- foreign key (file) references files on delete cascade
+ primary key (file, needed)
)
without rowid
;
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
"group" text
);
+-- 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 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
+);
+
+-- 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
+);
+
commit;