X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=bf6fd22a07c01b886d9d6e26a9dc1557d5132c03;hb=119f7176a2cedfe3c87417a35d91ec3b35b582b1;hp=e1565e69aee73f8b6541b84522b8b058122e31cc;hpb=57218db95d0c469d3a2de65c63a784e819cbf041;p=zpackage

diff --git a/db.sql b/db.sql
index e1565e6..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
 ;
@@ -232,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
@@ -245,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