3 PRAGMA application_id = 0x5a504442;
4 PRAGMA user_version = 1;
6 -- should be faster with rowid due to the blob content
7 -- these are really just blobs of data
8 -- TODO copyright and license information should probably
11 hash text primary key, -- sha256 of content
12 size integer, -- bigint? certainly need > 2GB
13 compression text, -- always xz?
18 -- information about packages
19 -- a package is identified by a package,version,release triple
20 create table packages (
21 -- primary key columns
23 version text, -- the upstream version string
24 release integer, -- the local release number
25 pkgid text, -- the three above joined with '-'
32 licenses text, -- hash of actual license? need table for more than one?
34 build_time integer default (strftime('%s', 'now')),
36 checksum text, -- checksum of package contents. null for incompleted packages
37 primary key (package,version,release),
38 check (typeof(release) = 'integer'),
40 -- TODO enforce name and release conventions
45 -- handle package status history with a logging trigger.
46 create trigger logpkgstatus after update of status on packages
47 begin insert into zpmlog (action,target,info)
48 values (printf('status change %s %s', OLD.status, NEW.status),
49 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
52 create table packagetags (
58 set_time integer default (strftime('%s', 'now')),
59 primary key (package,version,release,tag),
60 foreign key (package,version,release) references packages (package,version,release) on delete cascade
63 -- packagefile hash is columns as text, joined with null bytes, then
65 -- package checksum is package columns as text, joined with null bytes,
66 -- other than the checksum and install_time column
67 -- then that hashed. finally, that hash, plus the ascii sorted
68 -- hashes of the package files all joined with newlines, hashed.
69 -- really don't like this.
71 -- files contained in a package
72 create table packagefiles (
78 path text, -- filesystem path
79 mode text, -- perms, use text for octal rep?
80 username text, -- name of owner
81 groupname text, -- group of owner
82 uid integer, -- numeric uid, generally ignored
83 gid integer, -- numeric gid, generally ignored
84 filetype varchar default 'r',
88 -- h hard link -- not supported
89 -- c character special and b device special files add dev number column
91 -- p fifos (i.e. pipe)
92 target text, -- link target for links
93 -- device file dev numbers, should probably be a separate table
96 hash text, -- null if no actual content, i.e. anything but a regular file
97 mtime integer, -- seconds since epoch, finer resolution probably not needed
98 primary key (package,version,release,path),
99 foreign key (package,version,release) references packages (package,version,release) on delete cascade
104 create view installedfiles as
105 select PF.package, PF.version, PF.release,
106 printf('%s-%s-%s', PF.package, PF.version, PF.release) as pkgid,
107 PF.path, PF.hash, PF.filetype
110 on P.package = PF.package and P.version = PF.version and P.release = PF.release
112 P.status = 'installed'
115 create view installed_ref_count as
116 select I.path, count(*) as refcount
117 from installedfiles I
121 create table pathtags (
127 path text, -- filesystem path
129 primary key (package,version,release,path,tag)
134 create table elfinfo (
135 file text primary key, -- hash of blob
137 foreign key (file) references files on delete cascade
142 create table elfdeps (
146 primary key (file, soname, dependency),
147 foreign key (file) references files on delete cascade
152 -- TODO just elf information?
153 -- and just hash, not package?
154 create table elflibraries (
155 file text primary key,
157 foreign key (file) references files on delete cascade
162 create table elfneeded (
164 needed text, -- soname of dependency
165 primary key (file, needed),
166 foreign key (file) references files on delete cascade
171 -- package scripts: table of package, stage, file
172 create table scripts (
180 -- package dependencies: table of package, dependency, dep type (package, soname)
181 create table packagedeps (
185 required text, -- package name
186 -- following can be null for not checked
194 create table provides (
197 label text -- a capability label
200 create table requires (
203 label text -- a capability label
206 create table packagegroups (
212 -- not sure how machine readable this needs to be,
213 -- do not at all for now, figure it out later
214 -- could be worth logging all commands in a history table,
215 -- the zpm driver could do that and capture the exit status
217 -- might want the history table to note a "group" to tie together
218 -- sub-invocations, probably an environment variable set if not
219 -- already set by zpm, probably a uuid or a timestamp
220 create table zpmlog (
221 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
222 -- timestamp of action
224 target text, -- packagename, repo name, etc
225 info text -- human readable
228 create table history (
229 ts integer, -- again, probably needs timestamp sub second
235 create table repository (
236 name text primary key, -- our name for a repo
238 priority integer not null default 1,
239 refreshed integer -- last refresh time
242 -- urls for downloading packages. possibly unneeded
243 create table repository_packages (
245 pkg text, -- glob pattern? in which case others not needed
251 -- track which repository a package was cloned from, i.e. where we got it
252 create table packagesource (
256 repository text references repository