+++ /dev/null
-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
-)
-;
-
-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 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 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 syncconflicts as
-with
--- metadata different
-md_conflict as (
- select path, count(distinct
- printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
- count(distinct PFA.hash) as hashcount
- from packagefiles PFA
- join packages PA
- on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
- where PA.status in ('installing', 'installed')
- group by path
- having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
- ) > 1 or count(distinct PFA.hash) > 1)
-)
-select PFH.*,
- printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
- printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
-'hash' as conflict
-from packagefiles PFH
-where path in (select path from md_conflict where hashcount > 1)
-union
-select PFM.*,
- printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
- printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
-'md' as conflict
-from packagefiles PFM
-where path in (select path from md_conflict where mdcount > 1)
-;
-
-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')
-;
-
-commit;
projects / zpackage / blobdiff