[zpackage] / db.sql

begin;

PRAGMA application_id = 0x5a504442;
PRAGMA user_version = 1;

-- should be faster with rowid due to the blob content
-- these are really just blobs of data
-- TODO copyright and license information should probably
-- go here
CREATE TABLE files (
        hash text primary key, -- sha256 of content
        size integer, -- bigint?  certainly need > 2GB
        compression text, -- always xz?
        content blob
)
;

-- information about packages
-- a package is identified by a package,version,release triple
create table packages (
        -- primary key columns
        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,
        architecture    text,
        url     text,
        status  text,
        licenses        text, -- hash of actual license?  need table for more than one?
        packager        text,
        build_time      integer default (strftime('%s', 'now')),
        install_time    integer,
        checksum        text, -- checksum of package contents.  null for incompleted packages
        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)
)
without rowid
;

create index package_status_index on packages (status);
create index package_package_index on packages (package);

create view packages_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
from packages;

create trigger packages_update_trigger instead of
update on packages_pkgid
begin
        update packages
        set package = NEW.package,
        version = NEW.version,
        release = NEW.release,
        description = NEW.description,
        architecture = NEW.architecture,
        url = NEW.url,
        status = NEW.status,
        licenses = NEW.licenses,
        packager = NEW.packager,
        build_time = NEW.build_time,
        install_time = NEW.install_time,
        checksum = NEW.checksum
        where package = OLD.package
        and version = OLD.version
        and release = OLD.release
        ;
end
;

-- handle package status history with a logging trigger.
create trigger logpkgstatus after update of status on packages
begin insert into zpmlog (action,target,info)
        values (printf('status change %s %s', OLD.status, NEW.status),
                printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
                NULL); END;

create table packagetags (
        -- package id triple
        package text,
        version text,
        release integer,
        tag     text,
        set_time integer default (strftime('%s', 'now')),
        primary key (package,version,release,tag),
        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
        package text,
        version text,
        release integer,

        path    text, -- filesystem path
        mode    text not null, -- perms, use text for octal rep?
        username        text not null, -- name of owner
        groupname       text not null, -- group of owner
        uid     integer, -- numeric uid, generally ignored
        gid     integer, -- numeric gid, generally ignored
        configuration integer not null default 0, -- boolean if config file
        filetype varchar not null default 'r',
        -- r regular file
        -- d directory
        -- s symlink
        -- 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)
        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
        primary key (package,version,release,path),
        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 = '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 (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, *,
printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
from packagefiles
;

create trigger packagefiles_update_trigger instead of
update on packagefiles_pkgid
begin
        update packagefiles
        set package = NEW.package,
        version = NEW.version,
        release = NEW.release,
        path = NEW.path,
        mode = NEW.mode,
        username = NEW.username,
        groupname = NEW.groupname,
        uid = NEW.uid,
        gid = NEW.gid,
        configuration = NEW.configuration,
        filetype = NEW.filetype,
        target = NEW.target,
        devmajor = NEW.devmajor,
        devminor = NEW.devminor,
        hash = NEW.hash,
        mtime = NEW.mtime
        where package = OLD.package
        and version = OLD.version
        and release = OLD.release
        and path = OLD.path
        ;
end
;

create view installed_ref_count as
select I.path, count(*) as refcount
from installedfiles I
group by I.path
;

create view sync_status_ref_count as
select path, status, count(*) as refcount
from packagefiles_status
where status in ('installed', 'installing', 'removing')
group by path, status
;

create view packagefiles_status as
select P.status, PF.*
from packagefiles_pkgid PF
left join packages_pkgid P on P.pkgid = PF.pkgid
;

create view installedfiles as
select * from packagefiles_status
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,
        version text,
        release integer,

        path    text, -- filesystem path
        tag     text,
        primary key (package,version,release,path,tag),
        foreign key (package,version,release,path)
        references packagefiles on delete cascade on update cascade
)
without rowid
;

create view elfdeps as
select PF.pkgid, PF.status, PF.path, N.needed as needs,
PL.path as library, PL.pkgid provider, PL.status as library_status
from packagefiles_status PF
join elfneeded N on N.file = PF.hash
left join elflibraries L on N.needed = L.soname
left join packagefiles_status PL on PL.hash = L.file
;

-- TODO just elf information?
-- and just hash, not package?
create table elflibraries (
        file    text primary key,
        soname  text
)
without rowid
;

create table elfneeded (
        file    text,
        needed  text, -- soname of dependency
        primary key (file, needed)
)
without rowid
;

-- package scripts: table of package, stage, file
create table scripts (
        package text,
        version text,
        release integer,
        stage   text,
        hash    text,
        primary key (package,version,release,stage),
        foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

create view scripts_pkgid as
select printf('%s-%s-%s', package, version, release) as pkgid, *
from scripts
;

-- package dependencies: table of package, dependency, dep type (package, soname)
create table packagedeps (
        package text,
        version text,
        release integer,
        requires text, -- package name (only)
        minimum text,
        maximum text,
        primary key (package,version,release,package),
        foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
);

-- capability labels
create table provides (
        package text,
        subpackage      text,
        label   text -- a capability label
);

create table requires (
        package text,
        subpackage      text,
        label   text -- a capability label
);

create table packagegroups (
        package text,
        "group" text
);

-- zpm actions
-- not sure how machine readable this needs to be,
-- do not at all for now, figure it out later
-- could be worth logging all commands in a history table,
-- the zpm driver could do that and capture the exit status
-- as well
-- might want the history table to note a "group" to tie together
-- sub-invocations, probably an environment variable set if not
-- already set by zpm, probably a uuid or a timestamp
create table zpmlog (
        ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
        -- timestamp of action
        action  text,
        target  text, -- packagename, repo name, etc
        info    text -- human readable
);

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

create table repository (
        name    text primary key, -- our name for a repo
        url     text not null,
        priority        integer not null default 1,
        refreshed       integer -- last refresh time
);

-- urls for downloading packages.  possibly unneeded
create table repository_packages (
        repo    text,
        pkg     text, -- glob pattern?  in which case others not needed
        version text,
        release text,
        url     text
);

-- track which repository a package was cloned from, i.e. where we got it
create table packagesource (
        name    text,
        version text,
        release integer,
        repository      text references repository
);

create view syncconflicts as
with
preserved as (
        select BASE.*, 'preserved' as rstatus
        from packagefiles_status BASE
        join elflibraries EL on EL.file = BASE.hash
        where
        BASE.status in ('removed', 'updated')
        and BASE.hash in (select hash from packagefiles_status where
                status in ('installed'))
),
syncstatus as (
        select distinct BASE.*,
        case when P.status = 'installing' and BASE.status = 'installed'
                then 'updating'
        when BASE.status in ('removed','updated')
                and BASE.path in (select path from preserved) then
                'preserved'
        else
                BASE.status
        end as rstatus
        from packagefiles_status BASE
        left join packages P on P.package = BASE.package
                and BASE.status in ('installed', 'removing')
                and P.status = 'installing'
),
-- metadata different
md_conflict as (
        select path, count(distinct mds) as mdcount,
        count(distinct hash) as hashcount
        from syncstatus SS
        where SS.rstatus in ('installing', 'installed')
        group by path
        having (count(distinct mds) > 1 or count(distinct hash) > 1)
)
select BASE.*, 'hash' as conflict
from syncstatus BASE
where path in (select path from md_conflict where hashcount > 1)
union
select BASE.*, 'md' as conflict
from syncstatus BASE
where path in (select path from md_conflict where mdcount > 1)
;

create view syncinfo as
with
preserved as (
        select BASE.*, 'preserved' as rstatus
        from packagefiles_status BASE
        join elflibraries EL on EL.file = BASE.hash
        where
        BASE.status in ('removed', 'updated')
        and BASE.hash in (select hash from packagefiles_status where
                status in ('installed'))
),
syncstatus as (
        select distinct BASE.*,
        case when P.status = 'installing' and BASE.status = 'installed'
                then 'updating'
        when BASE.status in ('removed','updated')
                and BASE.path in (select path from preserved) then
                'preserved'
        else
                BASE.status
        end as rstatus
        from packagefiles_status BASE
        left join packages P on P.package = BASE.package
                and BASE.status in ('installed', 'removing')
                and P.status = 'installing'
),
-- new file: in installing, not in installed or updating or removing
newfiles as (
        select distinct
        path,username,uid,groupname,gid,mode,filetype,mtime,hash,
        target,devminor,devmajor
        from syncstatus SS
        where path not in (select path from syncstatus where
                rstatus in ('installed', 'updating', 'removing')
        )
        and rstatus in ('installing')
),
-- modified: retained, but with different metadata
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
        from syncstatus SS
        join syncstatus OS
        on SS.path = OS.path and SS.pkgid is not OS.pkgid
        -- preserved?
        and OS.rstatus in ('installed','updating','removing')
        and (SS.mds is not OS.mds or SS.hash is not OS.hash)
        where
        SS.rstatus in ('installing')
),
-- preserve: libraries needed by something in installed or installing
needed as (
        select distinct
        ED.library
        from elfdeps ED
        where status in ('installed', 'installing')
        and library is not null
),
preserve as (
        select distinct
        path,username,uid,groupname,gid,mode,filetype,mtime,hash,
        target,devminor,devmajor
        from syncstatus SS
        where path in (select library from needed)
        and SS.rstatus in ('removing', 'removed')
),
-- remove: cur, not preserved, not in final set
remove as (
        select distinct
        path,username,uid,groupname,gid,mode,filetype,mtime,hash,
        target,devminor,devmajor
        from syncstatus SS
        where path not in (
                select path from syncstatus where
                rstatus in ('installed', 'installing')
        )
        and path not in (select path from preserve)
        and rstatus in ('removing', 'updating')
),
-- expired: libraries that had been preserved, but aren't needed now
expired as (
        select distinct
        path,username,uid,groupname,gid,mode,filetype,mtime,hash,
        target,devminor,devmajor
        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
union
select 'remove' as op, * from remove
union
select 'obsolete' as op, * from expired
union
select 'new' as op, * from newfiles
union
select 'preserve' as op, * from preserve
;

commit;