X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=e1565e69aee73f8b6541b84522b8b058122e31cc;hb=57218db95d0c469d3a2de65c63a784e819cbf041;hp=6eb01906be160c00f26c263f4c033b46d81ad63a;hpb=ec7869647ac453722efaf40ad0c61f186166040d;p=zpackage diff --git a/db.sql b/db.sql index 6eb0190..e1565e6 100644 --- a/db.sql +++ b/db.sql @@ -19,25 +19,50 @@ CREATE TABLE files ( -- 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, @@ -65,12 +90,13 @@ create table packagefiles ( 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 @@ -85,11 +111,69 @@ create table packagefiles ( 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, @@ -146,9 +230,16 @@ create table scripts ( 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, @@ -190,7 +281,8 @@ create table packagegroups ( -- 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