add views for installing and upgrading packages

[zpackage] / db.sql

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,