-- 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
+-- 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 view packages_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from packages;
+
+-- 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,
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 varchar default 'r',
+ configuration integer not null default 0, -- boolean if config file
+ filetype varchar not null default 'r',
-- r regular file
-- d directory
-- s symlink
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,
+ 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)))
)
without rowid
;
+create view packagefiles_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from packagefiles
+;
+
+create view installed_ref_count as
+select I.path, count(*) as refcount
+from installedfiles I
+group by I.path
+;
+
+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'
+
+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
+where PN.path is null
+and PN.status = 'installing'
+;
+
create table pathtags (
-- package id triple
package text,
;
create table elfinfo (
- file text, -- hash of blob
+ 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
+;
-- 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
);
+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,
-- sub-invocations, probably an environment variable set if not
-- already set by zpm, probably a uuid or a timestamp
create table zpmlog (
- ts integer, -- timestamp of action, may need sub-second
+ 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