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