X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=8ed6537e56b1c635f4587f0d224a7aab59f9d722;hb=042f84f74cd182f06d666781b67b015835bcf407;hp=fab5b24752c99aae59417bfe07c379c7a3fa857f;hpb=0643628a3b9c359a78862d09828e83b562feea08;p=zpackage diff --git a/db.sql b/db.sql index fab5b24..8ed6537 100644 --- a/db.sql +++ b/db.sql @@ -5,10 +5,12 @@ 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, - size integer, - compression text, + hash text primary key, -- sha256 of content + size integer, -- bigint? certainly need > 2GB + compression text, -- always xz? content blob ) ; @@ -16,22 +18,95 @@ CREATE TABLE files ( -- information about packages -- a package is identified by a package,version,release triple create table packages ( - package text, - version text, -- the upstream version string - release integer, -- the local release number + -- 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) + 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 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 @@ -40,33 +115,172 @@ create table packagefiles ( release integer, path text, -- filesystem path - mode text, -- perms, use text for octal rep? - username text, -- name of owner - groupname text, -- group of owner + 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 - --filetype integer default 0, -- 0 regular file, 1 directory, 2 symlink - -- regular file if null target and not null hash - -- except that we could not know the hash, or care - -- directory if null hash and null target - -- symlink if null hash and not null target - -- hard link if not null hash and not null target - -- device special files add dev number column - -- fifos add mode? Can encode filetype in mode. - target text, -- link target for symlinks + 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 + 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 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 table elfinfo ( - file text, -- hash of blob + file text primary key, -- hash of blob elftype text, foreign key (file) references files on delete cascade -); +) +without rowid +; + +create table elfdeps ( + file text, + soname text, + dependency text, + primary key (file, soname, dependency), + foreign key (file) references files on delete cascade +) +without rowid +; -- TODO just elf information? -- and just hash, not package? @@ -93,20 +307,26 @@ create table scripts ( version text, release integer, stage text, - hash 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, - required text, -- package name - -- following can be null for not checked - minversion text, - minrelease integer, - maxversion text, - maxrelease 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 @@ -127,4 +347,52 @@ create table packagegroups ( "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 +); + commit;