X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=db.sql;h=6eb01906be160c00f26c263f4c033b46d81ad63a;hb=9ed8a8ffdb3c31be8d324a9a67a84aad59922c9f;hp=fab5b24752c99aae59417bfe07c379c7a3fa857f;hpb=0643628a3b9c359a78862d09828e83b562feea08;p=zpackage

diff --git a/db.sql b/db.sql
index fab5b24..6eb0190 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,9 +18,13 @@ CREATE TABLE files (
 -- 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,
@@ -32,6 +38,25 @@ create table packages (
 without rowid
 ;
 
+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
@@ -45,15 +70,18 @@ create table packagefiles (
 	groupname	text, -- 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
+	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),
@@ -62,11 +90,36 @@ create table packagefiles (
 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, -- 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?
@@ -127,4 +180,51 @@ 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	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;