X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=schema%2Fmain.sql;fp=schema%2Fmain.sql;h=6ea34f27caeb8fc4fa12dd4add1a18a99409e421;hb=b3c05e3e2a4b5cf159a2ab94b1c6696ceeb8c1a8;hp=0000000000000000000000000000000000000000;hpb=b53647c07cf58ed2a8b76727f22cf5328c6e02fb;p=zpackage diff --git a/schema/main.sql b/schema/main.sql new file mode 100644 index 0000000..6ea34f2 --- /dev/null +++ b/schema/main.sql @@ -0,0 +1,589 @@ +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') +;