X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=316f3e47ba42354640d334341d0771bafd12f0aa;hb=e8412186c6099fa67c7bb9c7547e42d7490c7edb;hp=020f228d658dcb0f464e92afef8e2de0ab04e910;hpb=383712eefa950c5dc619f8cd5fb60be8e8041502;p=zpackage

diff --git a/db.sql b/db.sql
index 020f228..316f3e4 100644
--- a/db.sql
+++ b/db.sql
@@ -15,6 +15,17 @@ CREATE TABLE files (
 )
 ;
 
+create view filerefs as
+select F.hash,
+count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
+from files F
+left join packagefiles PF on PF.hash = F.hash
+left join scripts S on S.hash = F.hash
+left join elflibraries EL on EL.file = F.hash
+left join notes N on N.file = F.hash
+group by F.hash
+;
+
 -- information about packages
 -- a package is identified by a package,version,release triple
 create table packages (
@@ -163,7 +174,7 @@ create index packagefile_hash_index on packagefiles (hash);
 
 create view packagefiles_pkgid as
 select printf('%s-%s-%s', package, version, release) as pkgid, *,
-printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
+printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
 from packagefiles
 ;
 
@@ -194,6 +205,23 @@ begin
 end
 ;
 
+create trigger packagefiles_delete_trigger instead of
+delete on packagefiles_pkgid
+begin
+	delete from packagefiles
+	where package = OLD.package
+	and version = OLD.version
+	and release = OLD.release
+	and path = OLD.path
+	;
+	update packages set hash = null
+	where package = OLD.package
+	and version = OLD.version
+	and release = OLD.release
+	;
+end
+;
+
 create view installed_ref_count as
 select I.path, count(*) as refcount
 from installedfiles I
@@ -218,51 +246,6 @@ 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'
-and PI.hash is not PN.hash
-
-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
-	and PI.pkgid != PN.pkgid
-where PN.path is null
-and PI.package in (select package from packages where status = 'installing')
-
-union all
--- remove files in removing, but not installing
-select distinct 'remove' as op, PR.*
-from packagefiles_status PR
-left join packagefiles_status PN
-on PR.path = PN.path
-and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
-where PN.path is null
-and PR.status = 'removing'
-;
-
 create table pathtags (
 	-- package id triple
 	package	text,
@@ -493,10 +476,12 @@ newfiles as (
 modified as (
 	select distinct
 	SS.path, 
-	SS.username,
-       	SS.uid, SS.groupname, SS.gid, SS.mode,
-	SS.filetype, SS.mtime, SS.hash,SS.configuration, SS.target, SS.device,
-	null as ohash
+	SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
+	SS.mtime, SS.hash,
+	SS.configuration + case when OS.configuration = 1 then 2 else 0 end
+	as configuration,
+       	SS.target, SS.device,
+	OS.hash as ohash, SS.mds, OS.mds as omds
 	from syncstatus SS
 	join syncstatus OS
 	on SS.path = OS.path and SS.pkgid is not OS.pkgid
@@ -545,15 +530,20 @@ expired as (
 	and path not in (select path from preserve)
 	and rstatus in ('removed','updated')
 )
-select 'update' as op, * from modified
+select 'update' as op, *
+,(select group_concat(pkgid, ' ')
+       	from syncstatus SS
+       	where SS.rstatus = 'installing' and SS.path = M.path
+) as pkglist
+from modified M
 union
-select 'remove' as op, * from remove
+select 'remove' as op, *, null, null, null from remove
 union
-select 'obsolete' as op, * from expired
+select 'obsolete' as op, *, null, null, null from expired
 union
-select 'new' as op, * from newfiles
+select 'new' as op, *, null, null, null from newfiles
 union
-select 'preserve' as op, * from preserve
+select 'preserve' as op, *, null, null, null from preserve
 ;
 
 commit;