add support for notes

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index be71bde3428047a6368f6e6ed611ab0f7d12b867..020f228d658dcb0f464e92afef8e2de0ab04e910 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -123,6 +123,7 @@ 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
@@ -132,10 +133,9 @@ create table packagefiles (
        -- 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),
@@ -147,8 +147,8 @@ 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 (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),
@@ -183,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
@@ -368,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,
@@ -473,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')
@@ -486,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
@@ -507,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')
@@ -516,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
@@ -529,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)