From 1543353d5f552717ef55bea0a70f5ef80072ce3d Mon Sep 17 00:00:00 2001
From: Nathan Wagner <nw@hydaspes.if.org>
Date: Fri, 21 Sep 2018 10:01:49 +0000
Subject: [PATCH] add check for different hash in install_status

cleanup schema, add foreign key cascades

fix query to find files for removal at upgrade
---
 db.sql | 56 ++++++++++++++++++++++++++++++++++++++++++--------------
 1 file changed, 42 insertions(+), 14 deletions(-)

diff --git a/db.sql b/db.sql
index 816cbe5..bf6fd22 100644
--- a/db.sql
+++ b/db.sql
@@ -56,6 +56,29 @@ create view packages_pkgid as
 select printf('%s-%s-%s', package, version, release) as pkgid, *
 from packages;
 
+create trigger packages_update_trigger instead of
+update on packages_pkgid
+begin
+	update packages
+	set package = NEW.package,
+	version = NEW.version,
+	release = NEW.release,
+	description = NEW.description,
+	architecture = NEW.architecture,
+	url = NEW.url,
+	status = NEW.status,
+	licenses = NEW.licenses,
+	packager = NEW.packager,
+	build_time = NEW.build_time,
+	install_time = NEW.install_time,
+	checksum = NEW.checksum
+	where package = OLD.package
+	and version = OLD.version
+	and release = OLD.release
+	;
+end
+;
+
 -- handle package status history with a logging trigger.
 create trigger logpkgstatus after update of status on packages
 begin insert into zpmlog (action,target,info)
@@ -71,7 +94,7 @@ create table packagetags (
 	tag	text,
 	set_time integer default (strftime('%s', 'now')),
 	primary key (package,version,release,tag),
-	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 on update cascade
 );
 
 -- packagefile hash is columns as text, joined with null bytes, then
@@ -111,7 +134,7 @@ 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 on update 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)))
@@ -142,6 +165,7 @@ 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
@@ -156,6 +180,7 @@ 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'
+and PI.hash is not PN.hash
 
 union all
 
@@ -165,13 +190,13 @@ 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
+	and PI.pkgid != PN.pkgid
 where PN.path is null
-and PN.status = 'installing'
+and PI.package in (select package from packages where status = 'installing')
 ;
 
 create table pathtags (
@@ -182,7 +207,9 @@ create table pathtags (
 
 	path	text, -- filesystem path
 	tag	text,
-	primary key (package,version,release,path,tag)
+	primary key (package,version,release,path,tag),
+	foreign key (package,version,release,path)
+	references packagefiles on delete cascade on update cascade
 )
 without rowid
 ;
@@ -209,7 +236,8 @@ without rowid
 -- and just hash, not package?
 create table elflibraries (
 	file	text primary key,
-	soname	text
+	soname	text,
+	foreign key (file) references files on delete cascade
 )
 without rowid
 ;
@@ -217,7 +245,8 @@ without rowid
 create table elfneeded (
 	file	text,
 	needed	text, -- soname of dependency
-	primary key (file, needed)
+	primary key (file, needed),
+	foreign key (file) references files on delete cascade
 )
 without rowid
 ;
@@ -230,7 +259,7 @@ create table scripts (
 	stage	text,
 	hash	text,
 	primary key (package,version,release,stage),
-	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 on update cascade
 );
 
 create view scripts_pkgid as
@@ -243,12 +272,11 @@ create table packagedeps (
 	package	text,
 	version	text,
 	release	integer,
-	required	text, -- package name
-	-- following can be null for not checked
-	minversion	text,
-	minrelease	integer,
-	maxversion	text,
-	maxrelease	integer
+	requires text, -- package name (only)
+	minimum text,
+	maximum text,
+	primary key (package,version,release,package),
+	foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 );
 
 -- capability labels
-- 
2.40.0