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
22 package text not null,
23 version text not null, -- the upstream version string
24 release integer not null, -- 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(package) = 'text'),
39 check (typeof(version) = 'text'),
40 check (typeof(release) = 'integer'),
42 -- TODO enforce name and version conventions
43 -- check(instr(version,'-') = 0)
44 -- check(instr(package,'/') = 0)
45 -- check(instr(package,'/') = 0)
46 -- check(length(package) < 64)
47 -- check(length(version) < 32)
52 create view packages_pkgid as
53 select printf('%s-%s-%s', package, version, release) as pkgid, *
56 -- handle package status history with a logging trigger.
57 create trigger logpkgstatus after update of status on packages
58 begin insert into zpmlog (action,target,info)
59 values (printf('status change %s %s', OLD.status, NEW.status),
60 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
63 create table packagetags (
69 set_time integer default (strftime('%s', 'now')),
70 primary key (package,version,release,tag),
71 foreign key (package,version,release) references packages (package,version,release) on delete cascade
74 -- packagefile hash is columns as text, joined with null bytes, then
76 -- package checksum is package columns as text, joined with null bytes,
77 -- other than the checksum and install_time column
78 -- then that hashed. finally, that hash, plus the ascii sorted
79 -- hashes of the package files all joined with newlines, hashed.
80 -- really don't like this.
82 -- files contained in a package
83 create table packagefiles (
89 path text, -- filesystem path
90 mode text, -- perms, use text for octal rep?
91 username text, -- name of owner
92 groupname text, -- group of owner
93 uid integer, -- numeric uid, generally ignored
94 gid integer, -- numeric gid, generally ignored
95 filetype varchar default 'r',
99 -- h hard link -- not supported
100 -- c character special and b device special files add dev number column
102 -- p fifos (i.e. pipe)
103 target text, -- link target for links
104 -- device file dev numbers, should probably be a separate table
107 hash text, -- null if no actual content, i.e. anything but a regular file
108 mtime integer, -- seconds since epoch, finer resolution probably not needed
109 primary key (package,version,release,path),
110 foreign key (package,version,release) references packages (package,version,release) on delete cascade
115 create view installedfiles as
116 select PF.package, PF.version, PF.release,
117 printf('%s-%s-%s', PF.package, PF.version, PF.release) as pkgid,
118 PF.path, PF.hash, PF.filetype
121 on P.package = PF.package and P.version = PF.version and P.release = PF.release
123 P.status = 'installed'
126 create view installed_ref_count as
127 select I.path, count(*) as refcount
128 from installedfiles I
132 create table pathtags (
138 path text, -- filesystem path
140 primary key (package,version,release,path,tag)
145 create table elfinfo (
146 file text primary key, -- hash of blob
148 foreign key (file) references files on delete cascade
153 create table elfdeps (
157 primary key (file, soname, dependency),
158 foreign key (file) references files on delete cascade
163 -- TODO just elf information?
164 -- and just hash, not package?
165 create table elflibraries (
166 file text primary key,
168 foreign key (file) references files on delete cascade
173 create table elfneeded (
175 needed text, -- soname of dependency
176 primary key (file, needed),
177 foreign key (file) references files on delete cascade
182 -- package scripts: table of package, stage, file
183 create table scripts (
191 -- package dependencies: table of package, dependency, dep type (package, soname)
192 create table packagedeps (
196 required text, -- package name
197 -- following can be null for not checked
205 create table provides (
208 label text -- a capability label
211 create table requires (
214 label text -- a capability label
217 create table packagegroups (
223 -- not sure how machine readable this needs to be,
224 -- do not at all for now, figure it out later
225 -- could be worth logging all commands in a history table,
226 -- the zpm driver could do that and capture the exit status
228 -- might want the history table to note a "group" to tie together
229 -- sub-invocations, probably an environment variable set if not
230 -- already set by zpm, probably a uuid or a timestamp
231 create table zpmlog (
232 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
233 -- timestamp of action
235 target text, -- packagename, repo name, etc
236 info text -- human readable
239 create table history (
240 ts integer, -- again, probably needs timestamp sub second
246 create table repository (
247 name text primary key, -- our name for a repo
249 priority integer not null default 1,
250 refreshed integer -- last refresh time
253 -- urls for downloading packages. possibly unneeded
254 create table repository_packages (
256 pkg text, -- glob pattern? in which case others not needed
262 -- track which repository a package was cloned from, i.e. where we got it
263 create table packagesource (
267 repository text references repository