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 create trigger packages_update_trigger instead of
60 update on packages_pkgid
63 set package = NEW.package,
64 version = NEW.version,
65 release = NEW.release,
66 description = NEW.description,
67 architecture = NEW.architecture,
70 licenses = NEW.licenses,
71 packager = NEW.packager,
72 build_time = NEW.build_time,
73 install_time = NEW.install_time,
74 checksum = NEW.checksum
75 where package = OLD.package
76 and version = OLD.version
77 and release = OLD.release
82 -- handle package status history with a logging trigger.
83 create trigger logpkgstatus after update of status on packages
84 begin insert into zpmlog (action,target,info)
85 values (printf('status change %s %s', OLD.status, NEW.status),
86 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
89 create table packagetags (
95 set_time integer default (strftime('%s', 'now')),
96 primary key (package,version,release,tag),
97 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
100 -- packagefile hash is columns as text, joined with null bytes, then
101 -- sha256 sum of that
102 -- package checksum is package columns as text, joined with null bytes,
103 -- other than the checksum and install_time column
104 -- then that hashed. finally, that hash, plus the ascii sorted
105 -- hashes of the package files all joined with newlines, hashed.
106 -- really don't like this.
108 -- files contained in a package
109 create table packagefiles (
115 path text, -- filesystem path
116 mode text not null, -- perms, use text for octal rep?
117 username text not null, -- name of owner
118 groupname text not null, -- group of owner
119 uid integer, -- numeric uid, generally ignored
120 gid integer, -- numeric gid, generally ignored
121 configuration integer not null default 0, -- boolean if config file
122 filetype varchar not null default 'r',
126 -- h hard link -- not supported
127 -- c character special and b device special files add dev number column
129 -- p fifos (i.e. pipe)
130 target text, -- link target for links
131 -- device file dev numbers, should probably be a separate table
134 hash text, -- null if no actual content, i.e. anything but a regular file
135 mtime integer, -- seconds since epoch, finer resolution probably not needed
136 primary key (package,version,release,path),
137 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade,
138 check (not (filetype = 'l' and target is null)),
139 check (not (filetype = 'r' and hash is null)),
140 check (not (filetype = 'c' and (devmajor is null or devminor is null)))
145 create view packagefiles_pkgid as
146 select printf('%s-%s-%s', package, version, release) as pkgid, *
150 create view installed_ref_count as
151 select I.path, count(*) as refcount
152 from installedfiles I
156 create view packagefiles_status as
157 select P.status, PF.*
158 from packagefiles_pkgid PF
159 left join packages_pkgid P on P.pkgid = PF.pkgid
162 create view installedfiles as
163 select * from packagefiles_status
164 where status = 'installed'
167 create view install_status as
169 select 'new' as op, PN.*
170 from packagefiles_status PN
171 left join installed_ref_count RC on RC.path = PN.path
172 where RC.refcount is null
173 and PN.status = 'installing'
177 select 'update' as op, PN.*
178 from packagefiles_status PN
179 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
180 left join installed_ref_count RC on RC.path = PN.path
181 where RC.refcount = 1
182 and PN.status = 'installing'
183 and PI.hash is not PN.hash
187 select 'conflict' as op, PI.*
188 from packagefiles_status PN
189 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
190 where PN.status = 'installing'
193 select 'remove' as op, PI.*
194 from installedfiles PI
195 left join packagefiles_status PN
196 on PI.path = PN.path and PI.package = PN.package
197 and PI.pkgid != PN.pkgid
198 where PN.path is null
199 and PI.package in (select package from packages where status = 'installing')
202 create table pathtags (
208 path text, -- filesystem path
210 primary key (package,version,release,path,tag),
211 foreign key (package,version,release,path)
212 references packagefiles on delete cascade on update cascade
217 create table elfinfo (
218 file text primary key, -- hash of blob
220 foreign key (file) references files on delete cascade
225 create table elfdeps (
229 primary key (file, soname, dependency),
230 foreign key (file) references files on delete cascade
235 -- TODO just elf information?
236 -- and just hash, not package?
237 create table elflibraries (
238 file text primary key,
240 foreign key (file) references files on delete cascade
245 create table elfneeded (
247 needed text, -- soname of dependency
248 primary key (file, needed),
249 foreign key (file) references files on delete cascade
254 -- package scripts: table of package, stage, file
255 create table scripts (
261 primary key (package,version,release,stage),
262 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
265 create view scripts_pkgid as
266 select printf('%s-%s-%s', package, version, release) as pkgid, *
270 -- package dependencies: table of package, dependency, dep type (package, soname)
271 create table packagedeps (
275 requires text, -- package name (only)
278 primary key (package,version,release,package),
279 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
283 create table provides (
286 label text -- a capability label
289 create table requires (
292 label text -- a capability label
295 create table packagegroups (
301 -- not sure how machine readable this needs to be,
302 -- do not at all for now, figure it out later
303 -- could be worth logging all commands in a history table,
304 -- the zpm driver could do that and capture the exit status
306 -- might want the history table to note a "group" to tie together
307 -- sub-invocations, probably an environment variable set if not
308 -- already set by zpm, probably a uuid or a timestamp
309 create table zpmlog (
310 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
311 -- timestamp of action
313 target text, -- packagename, repo name, etc
314 info text -- human readable
317 create table history (
318 ts integer, -- again, probably needs timestamp sub second
324 create table repository (
325 name text primary key, -- our name for a repo
327 priority integer not null default 1,
328 refreshed integer -- last refresh time
331 -- urls for downloading packages. possibly unneeded
332 create table repository_packages (
334 pkg text, -- glob pattern? in which case others not needed
340 -- track which repository a package was cloned from, i.e. where we got it
341 create table packagesource (
345 repository text references repository