X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=6a8f51ed05ec56e63b639cceeb5efb7c48f6284e;hb=7cc581729bb4b242c803299f72eed987b9fee216;hp=7ad22b9593f66bf990be3ce9db5b0e2d62f73cd4;hpb=ecdd57da7df775c039aee834e1f74172004f352b;p=zpackage diff --git a/db.sql b/db.sql index 7ad22b9..6a8f51e 100644 --- a/db.sql +++ b/db.sql @@ -4,18 +4,27 @@ 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, - size integer, - compression text, + hash text primary key, -- sha256 of content + size integer, -- bigint? certainly need > 2GB + compression text, -- always xz? content blob ) ; +-- information about packages +-- a package is identified by a package,version,release triple create table packages ( + -- primary key columns package text, version text, -- the upstream version string release integer, -- the local release number + pkgid text, -- the three above joined with '-' + + -- metadata columns description text, architecture text, url text, @@ -23,57 +32,126 @@ create table packages ( 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) ) without rowid ; +create table packagestatus ( + pkgid text primary key, + status text, -- installed installing removed upgraded + -- asof timestamp + asof integer default (strftime('%s', 'now')) +); + +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 +); + +-- 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 package text, version text, release integer, - path text, + + path text, -- filesystem path mode text, -- perms, use text for octal rep? username text, -- name of owner groupname text, -- group of owner - --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 - hash text, -- what should go here, null for dir? - mtime integer, -- seconds since epoch, but allow finer? + uid integer, -- numeric uid, generally ignored + gid integer, -- numeric gid, generally ignored + filetype varchar 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 ) without rowid ; +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) +) +without rowid +; + +create table elfinfo ( + 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? -create table libraries ( - package text, - subpackage text, - path text, - soname text +create table elflibraries ( + file text primary key, + soname text, + foreign key (file) references files on delete cascade ) +without rowid ; -create table librarydeps ( - package text, - subpackage text, - path text, - soname text -- soname of dependency -); +create table elfneeded ( + file text, + needed text, -- soname of dependency + primary key (file, needed), + foreign key (file) references files on delete cascade +) +without rowid +; -- package scripts: table of package, stage, file create table scripts ( package text, - subpackage text, + version text, + release integer, stage text, hash text ); @@ -81,11 +159,14 @@ create table scripts ( -- package dependencies: table of package, dependency, dep type (package, soname) create table packagedeps ( package text, - subpackage text, - requires text, -- package name - subreq text, -- if requires only a sub package, probably most common for libs + version text, + release integer, + required text, -- package name + -- following can be null for not checked minversion text, - maxversion text + minrelease integer, + maxversion text, + maxrelease integer ); -- capability labels @@ -105,4 +186,52 @@ 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 integer, -- timestamp of action, may need sub-second + 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;