add support for notes

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index d5b1d103113f304d55b16e73908dba1118349700..020f228d658dcb0f464e92afef8e2de0ab04e910 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -61,6 +61,13 @@ create view packages_pkgid as
 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
@@ -116,19 +123,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
+       confhash text, -- last hash on disk
        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
+       -- s unix domain socket -- not supported
        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),
@@ -140,9 +147,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 (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)
@@ -176,8 +183,7 @@ begin
        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
@@ -361,6 +367,16 @@ create table zpmlog (
        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,
@@ -466,7 +482,7 @@ syncstatus as (
 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')
@@ -479,7 +495,8 @@ modified as (
        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.filetype, SS.mtime, SS.hash,SS.configuration, SS.target, SS.device,
+       null as ohash
        from syncstatus SS
        join syncstatus OS
        on SS.path = OS.path and SS.pkgid is not OS.pkgid
@@ -500,7 +517,7 @@ needed as (
 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')
@@ -509,7 +526,7 @@ preserve as (
 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
@@ -522,7 +539,7 @@ remove as (
 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)