--- /dev/null
+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
+)
+;
+
+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 (
+ -- primary key columns
+ package text not null,
+ version text not null, -- the upstream version string
+ release integer not null, -- the local release number
+
+ -- 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,
+ 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'),
+ 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
+;
+
+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_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
+ 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,
+ hash = NEW.hash
+ 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
+);
+
+-- 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 default '0644', -- perms, use text for octal rep?
+ username text not null default 'root', -- name of owner
+ groupname text not null default 'root', -- 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
+ confhash text, -- last hash on disk
+ filetype varchar not null default 'r',
+ -- r regular file
+ -- d directory
+ -- 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 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),
+ 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 = '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 (path not in ('.', '..')),
+ check (not (filetype = 'r' and hash 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
+;
+
+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 index pkgfile_configuration on packagefiles (configuration) where configuration = 1;
+
+create view packagefiles_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *,
+printf('%s:%s:%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,
+ device = NEW.device,
+ hash = NEW.hash,
+ mtime = NEW.mtime
+ where package = OLD.package
+ and version = OLD.version
+ and release = OLD.release
+ and path = OLD.path
+ ;
+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
+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 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 index elf_library_name_index on elflibraries(soname);
+
+create table elfneeded (
+ file text, -- hash of file
+ needed text, -- soname of dependency
+ primary key (file, needed)
+)
+without rowid
+;
+
+create view package_libraries as
+select distinct PF.pkgid, EL.soname
+from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
+;
+
+create view package_libraries_needed as
+with pkglibs as (
+ select distinct EN.needed as soname, PF.pkgid
+ from elfneeded EN
+ join packagefiles_pkgid PF on PF.hash = EN.file
+ ),
+ pkgprovides as (
+ select distinct EL.soname, PF.pkgid
+ from elflibraries EL
+ join packagefiles_pkgid PF on PF.hash = EL.file
+ )
+ select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
+ from pkglibs PL
+ left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
+;
+
+-- 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, can be partial
+ 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 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,
+ 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
+);
+-- force the url to be repourl/info.repo
+-- package urls repourl/pkgid.zpm
+
+-- 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 needed_libraries as
+with recursive
+libs(file,needs,provider) as (
+ select N.file, N.needed as needs, L.file as provider
+ from elfneeded N left join elflibraries L on N.needed = L.soname
+ union
+ select L.file, N.needed as needs, EL.file as provider
+ from libs L
+ join elfneeded N on N.file = L.provider
+ left join elflibraries EL on N.needed = EL.soname
+)
+select * from libs;
+
+/*
+ * tables for repository info, essentially materalized views
+ */
+create table repository_libs (
+ pkgid text,
+ soname text
+);
+
+create table repository_libsneeded (
+ pkgid text,
+ soname text,
+ selfsat integer
+);
+
+create view syncinfo as
+with
+-- paths to libraries we need to keep around
+preserve as (
+select distinct PFL.*
+from packagefiles PFL
+join elflibraries EL on EL.file = PFL.hash
+join elfneeded EN on EN.needed = EL.soname
+join packagefiles PFN on EN.file = PFN.hash
+join packages PN
+on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
+where
+PN.status = 'installing' or PN.status = 'installed'
+and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release)
+),
+waspreserved as (
+ select PF.path
+ from packagefiles PF
+ join elflibraries EL on EL.file = PF.hash
+ join elfneeded EN on EN.needed = EL.soname
+ join packagefiles PL on EN.file = PL.hash
+ join packages P
+ on PL.path = P.package and PL.version = P.version and PL.release = P.release
+ where
+ P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
+)
+-- every path in 'installing' is either new or update, or no-op
+select
+case
+when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
+when
+ printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is
+ printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname)
+ and PFI.hash is PFC.hash
+ then 'noop'
+else 'update'
+end as op,
+ printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
+ PFI.path,
+ PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
+ PFI.mtime, PFI.hash,
+ PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
+ as configuration,
+ PFI.target, PFI.device,
+ PFC.hash as ohash,
+ printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds,
+ printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds
+from
+packagefiles PFI
+join packages PI
+on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release
+left join packages PC
+on PC.package = PI.package and PC.status in ('installed','removing','updating')
+left join packagefiles PFC
+ on PFC.package = PC.package
+ and PFC.version = PC.version
+ and PFC.release = PC.release
+ and PFC.path = PFI.path
+where
+PI.status = 'installing'
+
+-- every path in updating is either remove or no-op
+union
+select 'remove',
+ printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
+ PFU.path,
+ PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
+ PFU.mtime, PFU.hash,
+ PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
+ as configuration,
+ PFU.target, PFU.device,
+ null as ohash,
+ printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
+ null as omds
+from packagefiles PFU
+join packages PU
+on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release
+-- inner join because the installing package must exist or this shouldn't
+-- be an 'updating' package
+join packages P on PU.package = P.package and P.status = 'installing'
+left join packagefiles PFI on
+ PFI.package = P.package
+ and PFI.version = P.version
+ and PFI.release = P.release
+ and PFI.path = PFU.path
+
+-- handle paths owned by other installed packages
+left join packages PI on PI.status = 'installed'
+left join packagefiles PFC
+on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path
+
+where
+PU.status in ('updating')
+and PFI.path is null
+and PFC.path is null
+and PFU.path not in (select path from preserve)
+
+-- every path in removing is either remove or no-op
+union
+select 'remove',
+ printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
+ PFR.path,
+ PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
+ PFR.mtime, PFR.hash,
+ PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
+ as configuration,
+ PFR.target, PFR.device,
+ null as ohash,
+ printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
+ null as omds
+from packagefiles PFR
+join packages PU
+on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release
+left join packages P on PU.package = P.package and P.status = 'installing'
+left join packagefiles PFI on PFI.path = PFR.path
+and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release
+
+-- handle paths owned by other installed packages
+left join packages PI on PI.status = 'installed'
+left join packagefiles PFC
+on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path
+
+where
+PU.status in ('removing')
+and PFI.path is null
+and PFC.path is null
+and PFR.path not in (select path from preserve)
+
+-- paths in 'installed' or 'updated' are no-ops
+
+union
+select 'preserve',
+ printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
+ PFP.path,
+ PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
+ PFP.mtime, PFP.hash,
+ PFP.configuration as configuration,
+ PFP.target, PFP.device,
+ null as ohash,
+ printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
+ null as omds
+from preserve PFP
+ -- TODO don't include installed or installing
+ -- left join packages P on P.package = PFP.package ...
+ -- where P.status not in ('installed','installing')
+;
projects / zpackage / blobdiff