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
        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


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


@@ -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 (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),
        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,
        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


@@ -368,6 +367,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,


@@ -473,7 +482,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')


@@ -486,7 +495,8 @@ modified as (
        SS.path, 
        SS.username,
                SS.uid, SS.groupname, SS.gid, SS.mode,
        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
        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,
 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')


@@ -516,7 +526,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


@@ -529,7 +539,7 @@ 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)
        from syncstatus BASE
        where hash in (select file from elflibraries where file is not null)
        and path not in (select path from preserve)