change chown to lchown

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index 020f228d658dcb0f464e92afef8e2de0ab04e910..316f3e47ba42354640d334341d0771bafd12f0aa 100644 (file)
--- 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;