add views for installing and upgrading packages

add checks and not nulls to packagefiles

add test file for database schema

diff --git a/db.sql b/db.sql
index 5583d02702afd49fd662dd5471fd062b9d9deb4f..e1565e69aee73f8b6541b84522b8b058122e31cc 100644 (file)
--- 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 (executable)
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