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 not null, -- perms, use text for octal rep?
94 username text not null, -- name of owner
95 groupname text not null, -- group of owner
96 uid integer, -- numeric uid, generally ignored
97 gid integer, -- numeric gid, generally ignored
98 configuration integer not null default 0, -- boolean if config file
99 filetype varchar not null default 'r',
103 -- h hard link -- not supported
104 -- c character special and b device special files add dev number column
106 -- p fifos (i.e. pipe)
107 target text, -- link target for links
108 -- device file dev numbers, should probably be a separate table
111 hash text, -- null if no actual content, i.e. anything but a regular file
112 mtime integer, -- seconds since epoch, finer resolution probably not needed
113 primary key (package,version,release,path),
114 foreign key (package,version,release) references packages (package,version,release) on delete cascade,
115 check (not (filetype = 'l' and target is null)),
116 check (not (filetype = 'r' and hash is null)),
117 check (not (filetype = 'c' and (devmajor is null or devminor is null)))
122 create view packagefiles_pkgid as
123 select printf('%s-%s-%s', package, version, release) as pkgid, *
127 create view installed_ref_count as
128 select I.path, count(*) as refcount
129 from installedfiles I
133 create view packagefiles_status as
134 select P.status, PF.*
135 from packagefiles_pkgid PF
136 left join packages_pkgid P on P.pkgid = PF.pkgid
139 create view installedfiles as
140 select * from packagefiles_status
141 where status = 'installed'
144 create view install_status as
145 select 'new' as op, PN.*
146 from packagefiles_status PN
147 left join installed_ref_count RC on RC.path = PN.path
148 where RC.refcount is null
149 and PN.status = 'installing'
153 select 'update' as op, PN.*
154 from packagefiles_status PN
155 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
156 left join installed_ref_count RC on RC.path = PN.path
157 where RC.refcount = 1
158 and PN.status = 'installing'
162 select 'conflict' as op, PI.*
163 from packagefiles_status PN
164 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
165 where PN.status = 'installing'
169 select 'remove' as op, PI.*
170 from installedfiles PI
171 left join packagefiles_status PN
172 on PI.path = PN.path and PI.package = PN.package
173 where PN.path is null
174 and PN.status = 'installing'
177 create table pathtags (
183 path text, -- filesystem path
185 primary key (package,version,release,path,tag)
190 create table elfinfo (
191 file text primary key, -- hash of blob
193 foreign key (file) references files on delete cascade
198 create table elfdeps (
202 primary key (file, soname, dependency),
203 foreign key (file) references files on delete cascade
208 -- TODO just elf information?
209 -- and just hash, not package?
210 create table elflibraries (
211 file text primary key,
213 foreign key (file) references files on delete cascade
218 create table elfneeded (
220 needed text, -- soname of dependency
221 primary key (file, needed),
222 foreign key (file) references files on delete cascade
227 -- package scripts: table of package, stage, file
228 create table scripts (
234 primary key (package,version,release,stage),
235 foreign key (package,version,release) references packages (package,version,release) on delete cascade
238 create view scripts_pkgid as
239 select printf('%s-%s-%s', package, version, release) as pkgid, *
243 -- package dependencies: table of package, dependency, dep type (package, soname)
244 create table packagedeps (
248 required text, -- package name
249 -- following can be null for not checked
257 create table provides (
260 label text -- a capability label
263 create table requires (
266 label text -- a capability label
269 create table packagegroups (
275 -- not sure how machine readable this needs to be,
276 -- do not at all for now, figure it out later
277 -- could be worth logging all commands in a history table,
278 -- the zpm driver could do that and capture the exit status
280 -- might want the history table to note a "group" to tie together
281 -- sub-invocations, probably an environment variable set if not
282 -- already set by zpm, probably a uuid or a timestamp
283 create table zpmlog (
284 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
285 -- timestamp of action
287 target text, -- packagename, repo name, etc
288 info text -- human readable
291 create table history (
292 ts integer, -- again, probably needs timestamp sub second
298 create table repository (
299 name text primary key, -- our name for a repo
301 priority integer not null default 1,
302 refreshed integer -- last refresh time
305 -- urls for downloading packages. possibly unneeded
306 create table repository_packages (
308 pkg text, -- glob pattern? in which case others not needed
314 -- track which repository a package was cloned from, i.e. where we got it
315 create table packagesource (
319 repository text references repository