add foreach_script function

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index 041c06efdf24e9524b7457a8627c7d2ff036df5e..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 (


@@ -22,7 +33,6 @@ create table packages (
        package text not null,
        version text not null, -- the upstream version string
        release integer not null, -- the local release number
        package text not null,
        version text not null, -- the upstream version string
        release integer not null, -- the local release number

---     pkgid   text, -- the three above joined with '-'

 
        -- metadata columns
        description     text,
 
        -- metadata columns
        description     text,


@@ -33,21 +43,24 @@ create table packages (
        packager        text,
        build_time      integer default (strftime('%s', 'now')),
        install_time    integer,
        packager        text,
        build_time      integer default (strftime('%s', 'now')),
        install_time    integer,

-       checksum        text, -- checksum of package contents.  null for incompleted packages
+       hash    text, -- see integ.c for package hash details

        primary key (package,version,release),
        check (typeof(package) = 'text'),
        check (typeof(version) = 'text'),
        check (typeof(release) = 'integer'),
        primary key (package,version,release),
        check (typeof(package) = 'text'),
        check (typeof(version) = 'text'),
        check (typeof(release) = 'integer'),

-       check (release > 0)
-       -- TODO enforce name and version conventions
-       -- check(instr(version,'-') = 0)
-       -- check(instr(package,'/') = 0)
-       -- check(instr(package,'/') = 0)
-       -- check(instr(version,' ') = 0)
-       -- check(instr(package,' ') = 0)
-       -- check(instr(package,' ') = 0)
-       -- check(length(package) < 64)
-       -- check(length(version) < 32)
+       check (release > 0),
+       -- enforce name and version conventions
+       check(instr(package,' ') = 0),
+       check(instr(package,'/') = 0),
+       check(instr(package,':') = 0),
+       check(instr(version,' ') = 0),
+       check(instr(version,'-') = 0),
+       check(instr(version,'/') = 0),
+       check(instr(version,':') = 0),
+       check(length(package) < 64),
+       check(length(package) > 0),
+       check(length(version) < 32),
+       check(length(version) > 0)

 )
 without rowid
 ;
 )
 without rowid
 ;


@@ -59,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


@@ -74,7 +94,7 @@ begin
        packager = NEW.packager,
        build_time = NEW.build_time,
        install_time = NEW.install_time,
        packager = NEW.packager,
        build_time = NEW.build_time,
        install_time = NEW.install_time,

-       checksum = NEW.checksum
+       hash = NEW.hash

        where package = OLD.package
        and version = OLD.version
        and release = OLD.release
        where package = OLD.package
        and version = OLD.version
        and release = OLD.release


@@ -100,14 +120,6 @@ create table packagetags (
        foreign key (package,version,release) references packages (package,version,release) on delete cascade on update 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
--- sha256 sum of that
--- package checksum is package columns as text, joined with null bytes,
--- other than the checksum and install_time column
--- then that hashed.  finally, that hash, plus the ascii sorted
--- hashes of the package files all joined with newlines, hashed.
--- really don't like this.
-

 -- files contained in a package
 create table packagefiles (
        -- package id triple
 -- files contained in a package
 create table packagefiles (
        -- package id triple


@@ -122,37 +134,47 @@ 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
        -- h hard link -- not supported

-       -- c character special and b device special files add dev number column
-       -- b block special
-       -- p fifos (i.e. pipe)
+       -- 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, should probably be a separate table
-       devmajor        integer,
-       devminor        integer,
-       hash    text, -- null if no actual content, i.e. anything but a regular file
-       mtime   integer, -- seconds since epoch, finer resolution probably not needed
+       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),
        primary key (package,version,release,path),

-       foreign key (package,version,release) references packages (package,version,release) on delete cascade on update 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 = 'l' and target is null)),

+       check (not (filetype = 'h' and target 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 = '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 (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)
 )
 without rowid
 ;
        check (configuration = 0 or configuration = 1)
 )
 without rowid
 ;

+

 create index packagefile_package_index on packagefiles (package);
 create index packagefile_path_index on packagefiles (path);
 create index packagefile_hash_index on packagefiles (hash);
 
 create view packagefiles_pkgid as
 select printf('%s-%s-%s', package, version, release) as pkgid, *,
 create index packagefile_package_index on packagefiles (package);
 create index packagefile_path_index on packagefiles (path);
 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
 ;
 
 from packagefiles
 ;
 


@@ -172,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


@@ -184,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


@@ -208,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,


@@ -357,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,


@@ -462,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')


@@ -473,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


@@ -496,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')


@@ -505,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


@@ -518,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;