From 57218db95d0c469d3a2de65c63a784e819cbf041 Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Wed, 19 Sep 2018 11:32:04 +0000 Subject: [PATCH] add views for installing and upgrading packages add checks and not nulls to packagefiles add test file for database schema --- db.sql | 70 ++++++++++++++++++++++++++++++++++++++++++----------- t/schema.t | 71 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 127 insertions(+), 14 deletions(-) create mode 100755 t/schema.t diff --git a/db.sql b/db.sql index 5583d02..e1565e6 100644 --- a/db.sql +++ b/db.sql @@ -90,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 @@ -110,20 +111,17 @@ 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 installedfiles as -select PF.package, PF.version, PF.release, -printf('%s-%s-%s', PF.package, PF.version, PF.release) as pkgid, -PF.path, PF.hash, PF.filetype -from packagefiles PF -left join packages P -on P.package = PF.package and P.version = PF.version and P.release = PF.release -where -P.status = 'installed' +create view packagefiles_pkgid as +select printf('%s-%s-%s', package, version, release) as pkgid, * +from packagefiles ; create view installed_ref_count as @@ -132,6 +130,50 @@ 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, diff --git a/t/schema.t b/t/schema.t new file mode 100755 index 0000000..27bbc11 --- /dev/null +++ b/t/schema.t @@ -0,0 +1,71 @@ +#!/bin/sh + +. tap.sh + +plan 5 + +PF=test.db + +rm -f $PF + +zpm-shell $PF < db.sql + +okexit direct schema load + +{ cat<<-EOS +pragma foreign_keys = off; +insert into packagefiles +(package,version,release,path,mode,username,groupname,hash) +values +('foo','1',1,'/nopath','0644','root','root','fakehash') +EOS +} | zpm-shell $PF 2>/dev/null + +okexit basic packagefile insert + +{ cat<<-EOS +pragma foreign_keys = off; +insert into packagefiles +(package,version,release,path,mode,username,groupname,hash) +values +('foo','1',1,'/nopath','0644','root','root',NULL) +EOS +} | zpm-shell $PF 2>/dev/null + +failsok regular with null hash rejected + +{ cat<<-EOS +pragma foreign_keys = off; +insert into packagefiles +(package,version,release,path,mode,username,groupname,hash) +values +('foo','1',1,'/nopath','0644','root',NULL,'fakehash') +EOS +} | zpm-shell $PF 2>/dev/null + +failsok null groupname rejected + +{ cat<<-EOS +pragma foreign_keys = off; +insert into packagefiles +(package,version,release,path,mode,username,groupname,hash) +values +('foo','1',1,'/nopath','0644',NULL,'root','fakehash') +EOS +} | zpm-shell $PF 2>/dev/null + +failsok null username rejected + +finish +rm -f $PF +exit 0 + +zpm-test test.empty 2>/dev/null +failsok zpm-test of empty file + +zpm-init $PF + zpm-init new file + +finish + +rm -f $PF test.empty -- 2.40.0