add library dependencies of libraries

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index d5b1d103113f304d55b16e73908dba1118349700..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 (
 -- information about packages
 -- a package is identified by a package,version,release triple
 create table packages (


@@ -61,6 +72,13 @@ create view packages_pkgid as
 select printf('%s-%s-%s', package, version, release) as pkgid, *
 from packages;
 
 select printf('%s-%s-%s', package, version, release) as pkgid, *
 from packages;
 

+create trigger packages_delete_trigger instead of
+delete on packages_pkgid
+begin
+       delete from packages where package = OLD.package
+       and version = OLD.version and release = OLD.release;
+end;
+

 create trigger packages_update_trigger instead of
 update on packages_pkgid
 begin
 create trigger packages_update_trigger instead of
 update on packages_pkgid
 begin


@@ -116,19 +134,19 @@ create table packagefiles (
        uid     integer, -- numeric uid, generally ignored
        gid     integer, -- numeric gid, generally ignored
        configuration integer not null default 0, -- boolean if config file
        uid     integer, -- numeric uid, generally ignored
        gid     integer, -- numeric gid, generally ignored
        configuration integer not null default 0, -- boolean if config file

+       confhash text, -- last hash on disk

        filetype varchar not null default 'r',
        -- r regular file
        -- d directory
        filetype varchar not null default 'r',
        -- r regular file
        -- d directory

-       -- s symlink
+       -- l symlink

        -- h hard link -- not supported
        -- c character special -- not supported
        -- b block special -- not supported
        -- c and b device special files add dev number column
        -- p fifos (i.e. pipe) -- not supported
        -- h hard link -- not supported
        -- c character special -- not supported
        -- b block special -- not supported
        -- c and b device special files add dev number column
        -- p fifos (i.e. pipe) -- not supported

+       -- s unix domain socket -- not supported

        target  text, -- link target for links
        target  text, -- link target for links

-       -- device file dev numbers
-       devmajor        integer,
-       devminor        integer,
+       device  integer, -- device file dev_t

        hash    text, -- null if not a regular file
        mtime   integer, -- seconds since epoch, finer resolution not needed
        primary key (package,version,release,path),
        hash    text, -- null if not a regular file
        mtime   integer, -- seconds since epoch, finer resolution not needed
        primary key (package,version,release,path),


@@ -140,9 +158,9 @@ create table packagefiles (
        check (target is null or length(target) between 1 and 4095),
        check (hash is null or length(hash) between 1 and 1024),
        check (not (filetype = 'r' and hash is null)),
        check (target is null or length(target) between 1 and 4095),
        check (hash is null or length(hash) between 1 and 1024),
        check (not (filetype = 'r' and hash is null)),

-       check (not (filetype = 'c' and (devmajor is null or devminor is null))),
-       check (not (filetype = 'b' and (devmajor is null or devminor is null))),
-       check (filetype in ('r','d','s','h','c','b','p')),
+       check (not (filetype = 'c' and device is null)),
+       check (not (filetype = 'b' and device is null)),
+       check (filetype in ('r','d','l','h','c','b','p')),

        check(length(username) between 1 and 256),
        check(length(groupname) between 1 and 256),
        check (configuration = 0 or configuration = 1)
        check(length(username) between 1 and 256),
        check(length(groupname) between 1 and 256),
        check (configuration = 0 or configuration = 1)


@@ -156,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, *,
 
 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
 ;
 
 from packagefiles
 ;
 


@@ -176,8 +194,7 @@ begin
        configuration = NEW.configuration,
        filetype = NEW.filetype,
        target = NEW.target,
        configuration = NEW.configuration,
        filetype = NEW.filetype,
        target = NEW.target,

-       devmajor = NEW.devmajor,
-       devminor = NEW.devminor,
+       device = NEW.device,

        hash = NEW.hash,
        mtime = NEW.mtime
        where package = OLD.package
        hash = NEW.hash,
        mtime = NEW.mtime
        where package = OLD.package


@@ -188,6 +205,23 @@ begin
 end
 ;
 
 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
 create view installed_ref_count as
 select I.path, count(*) as refcount
 from installedfiles I


@@ -212,51 +246,6 @@ select * from packagefiles_status
 where status = 'installed'
 ;
 
 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,
 create table pathtags (
        -- package id triple
        package text,


@@ -361,6 +350,16 @@ create table zpmlog (
        info    text -- human readable
 );
 
        info    text -- human readable
 );
 

+create table notes (
+       id      integer primary key, -- rowid alias
+       ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
+       note    text not null,
+       pkgid   text, -- package
+       path    text, -- file path involved
+       file    text, -- hash of file
+       ack     integer default 0
+);
+

 create table history (
        ts      integer, -- again, probably needs timestamp sub second
        cmd     text,
 create table history (
        ts      integer, -- again, probably needs timestamp sub second
        cmd     text,


@@ -466,7 +465,7 @@ syncstatus as (
 newfiles as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 newfiles as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,

-       target,devminor,devmajor
+       configuration,target,device, null as ohash

        from syncstatus SS
        where path not in (select path from syncstatus where
                rstatus in ('installed', 'updating', 'removing')
        from syncstatus SS
        where path not in (select path from syncstatus where
                rstatus in ('installed', 'updating', 'removing')


@@ -477,9 +476,12 @@ newfiles as (
 modified as (
        select distinct
        SS.path, 
 modified as (
        select distinct
        SS.path, 

-       SS.username,
-               SS.uid, SS.groupname, SS.gid, SS.mode,
-       SS.filetype, SS.mtime, SS.hash, SS.target, SS.devminor, SS.devmajor
+       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
        from syncstatus SS
        join syncstatus OS
        on SS.path = OS.path and SS.pkgid is not OS.pkgid


@@ -500,7 +502,7 @@ needed as (
 preserve as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 preserve as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,

-       target,devminor,devmajor
+       configuration,target,device, null as ohash

        from syncstatus SS
        where path in (select library from needed)
        and SS.rstatus in ('removing', 'removed')
        from syncstatus SS
        where path in (select library from needed)
        and SS.rstatus in ('removing', 'removed')


@@ -509,7 +511,7 @@ preserve as (
 remove as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 remove as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,

-       target,devminor,devmajor
+       configuration,target,device, null as ohash

        from syncstatus SS
        where path not in (
                select path from syncstatus where
        from syncstatus SS
        where path not in (
                select path from syncstatus where


@@ -522,21 +524,26 @@ remove as (
 expired as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 expired as (
        select distinct
                path,username,uid,groupname,gid,mode,filetype,mtime,hash,

-       target,devminor,devmajor
+       configuration,target,device, null as ohash

        from syncstatus BASE
        where hash in (select file from elflibraries where file is not null)
        and path not in (select path from preserve)
        and rstatus in ('removed','updated')
 )
        from syncstatus BASE
        where hash in (select file from elflibraries where file is not null)
        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
 union

-select 'remove' as op, * from remove
+select 'remove' as op, *, null, null, null from remove

 union
 union

-select 'obsolete' as op, * from expired
+select 'obsolete' as op, *, null, null, null from expired

 union
 union

-select 'new' as op, * from newfiles
+select 'new' as op, *, null, null, null from newfiles

 union
 union

-select 'preserve' as op, * from preserve
+select 'preserve' as op, *, null, null, null from preserve

 ;
 
 commit;
 ;
 
 commit;