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(instr(version,' ') = 0)
47 -- check(instr(package,' ') = 0)
48 -- check(instr(package,' ') = 0)
49 -- check(length(package) < 64)
50 -- check(length(version) < 32)
55 create view packages_pkgid as
56 select printf('%s-%s-%s', package, version, release) as pkgid, *
59 -- handle package status history with a logging trigger.
60 create trigger logpkgstatus after update of status on packages
61 begin insert into zpmlog (action,target,info)
62 values (printf('status change %s %s', OLD.status, NEW.status),
63 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
66 create table packagetags (
72 set_time integer default (strftime('%s', 'now')),
73 primary key (package,version,release,tag),
74 foreign key (package,version,release) references packages (package,version,release) on delete cascade
77 -- packagefile hash is columns as text, joined with null bytes, then
79 -- package checksum is package columns as text, joined with null bytes,
80 -- other than the checksum and install_time column
81 -- then that hashed. finally, that hash, plus the ascii sorted
82 -- hashes of the package files all joined with newlines, hashed.
83 -- really don't like this.
85 -- files contained in a package
86 create table packagefiles (
92 path text, -- filesystem path
93 mode text, -- perms, use text for octal rep?
94 username text, -- name of owner
95 groupname text, -- group of owner
96 uid integer, -- numeric uid, generally ignored
97 gid integer, -- numeric gid, generally ignored
98 filetype varchar default 'r',
102 -- h hard link -- not supported
103 -- c character special and b device special files add dev number column
105 -- p fifos (i.e. pipe)
106 target text, -- link target for links
107 -- device file dev numbers, should probably be a separate table
110 hash text, -- null if no actual content, i.e. anything but a regular file
111 mtime integer, -- seconds since epoch, finer resolution probably not needed
112 primary key (package,version,release,path),
113 foreign key (package,version,release) references packages (package,version,release) on delete cascade
118 create view installedfiles as
119 select PF.package, PF.version, PF.release,
120 printf('%s-%s-%s', PF.package, PF.version, PF.release) as pkgid,
121 PF.path, PF.hash, PF.filetype
124 on P.package = PF.package and P.version = PF.version and P.release = PF.release
126 P.status = 'installed'
129 create view installed_ref_count as
130 select I.path, count(*) as refcount
131 from installedfiles I
135 create table pathtags (
141 path text, -- filesystem path
143 primary key (package,version,release,path,tag)
148 create table elfinfo (
149 file text primary key, -- hash of blob
151 foreign key (file) references files on delete cascade
156 create table elfdeps (
160 primary key (file, soname, dependency),
161 foreign key (file) references files on delete cascade
166 -- TODO just elf information?
167 -- and just hash, not package?
168 create table elflibraries (
169 file text primary key,
171 foreign key (file) references files on delete cascade
176 create table elfneeded (
178 needed text, -- soname of dependency
179 primary key (file, needed),
180 foreign key (file) references files on delete cascade
185 -- package scripts: table of package, stage, file
186 create table scripts (
192 primary key (package,version,release,stage),
193 foreign key (package,version,release) references packages (package,version,release) on delete cascade
196 create view scripts_pkgid as
197 select printf('%s-%s-%s', package, version, release) as pkgid, *
201 -- package dependencies: table of package, dependency, dep type (package, soname)
202 create table packagedeps (
206 required text, -- package name
207 -- following can be null for not checked
215 create table provides (
218 label text -- a capability label
221 create table requires (
224 label text -- a capability label
227 create table packagegroups (
233 -- not sure how machine readable this needs to be,
234 -- do not at all for now, figure it out later
235 -- could be worth logging all commands in a history table,
236 -- the zpm driver could do that and capture the exit status
238 -- might want the history table to note a "group" to tie together
239 -- sub-invocations, probably an environment variable set if not
240 -- already set by zpm, probably a uuid or a timestamp
241 create table zpmlog (
242 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
243 -- timestamp of action
245 target text, -- packagename, repo name, etc
246 info text -- human readable
249 create table history (
250 ts integer, -- again, probably needs timestamp sub second
256 create table repository (
257 name text primary key, -- our name for a repo
259 priority integer not null default 1,
260 refreshed integer -- last refresh time
263 -- urls for downloading packages. possibly unneeded
264 create table repository_packages (
266 pkg text, -- glob pattern? in which case others not needed
272 -- track which repository a package was cloned from, i.e. where we got it
273 create table packagesource (
277 repository text references repository