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 index package_status_index on packages (status);
56 create index package_package_index on packages (package);
58 create view packages_pkgid as
59 select printf('%s-%s-%s', package, version, release) as pkgid, *
62 create trigger packages_update_trigger instead of
63 update on packages_pkgid
66 set package = NEW.package,
67 version = NEW.version,
68 release = NEW.release,
69 description = NEW.description,
70 architecture = NEW.architecture,
73 licenses = NEW.licenses,
74 packager = NEW.packager,
75 build_time = NEW.build_time,
76 install_time = NEW.install_time,
77 checksum = NEW.checksum
78 where package = OLD.package
79 and version = OLD.version
80 and release = OLD.release
85 -- handle package status history with a logging trigger.
86 create trigger logpkgstatus after update of status on packages
87 begin insert into zpmlog (action,target,info)
88 values (printf('status change %s %s', OLD.status, NEW.status),
89 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
92 create table packagetags (
98 set_time integer default (strftime('%s', 'now')),
99 primary key (package,version,release,tag),
100 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
103 -- packagefile hash is columns as text, joined with null bytes, then
104 -- sha256 sum of that
105 -- package checksum is package columns as text, joined with null bytes,
106 -- other than the checksum and install_time column
107 -- then that hashed. finally, that hash, plus the ascii sorted
108 -- hashes of the package files all joined with newlines, hashed.
109 -- really don't like this.
111 -- files contained in a package
112 create table packagefiles (
118 path text, -- filesystem path
119 mode text not null, -- perms, use text for octal rep?
120 username text not null, -- name of owner
121 groupname text not null, -- group of owner
122 uid integer, -- numeric uid, generally ignored
123 gid integer, -- numeric gid, generally ignored
124 configuration integer not null default 0, -- boolean if config file
125 filetype varchar not null default 'r',
129 -- h hard link -- not supported
130 -- c character special and b device special files add dev number column
132 -- p fifos (i.e. pipe)
133 target text, -- link target for links
134 -- device file dev numbers, should probably be a separate table
137 hash text, -- null if no actual content, i.e. anything but a regular file
138 mtime integer, -- seconds since epoch, finer resolution probably not needed
139 primary key (package,version,release,path),
140 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade,
141 check (not (filetype = 'l' and target is null)),
142 check (not (filetype = 'r' and hash is null)),
143 check (not (filetype = 'c' and (devmajor is null or devminor is null))),
144 check (not (filetype = 'b' and (devmajor is null or devminor is null))),
145 check (configuration = 0 or configuration = 1)
149 create index packagefile_package_index on packagefiles (package);
150 create index packagefile_path_index on packagefiles (path);
151 create index packagefile_hash_index on packagefiles (hash);
153 create view packagefiles_pkgid as
154 select printf('%s-%s-%s', package, version, release) as pkgid, *,
155 printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
159 create trigger packagefiles_update_trigger instead of
160 update on packagefiles_pkgid
163 set package = NEW.package,
164 version = NEW.version,
165 release = NEW.release,
168 username = NEW.username,
169 groupname = NEW.groupname,
172 configuration = NEW.configuration,
173 filetype = NEW.filetype,
175 devmajor = NEW.devmajor,
176 devminor = NEW.devminor,
179 where package = OLD.package
180 and version = OLD.version
181 and release = OLD.release
187 create view installed_ref_count as
188 select I.path, count(*) as refcount
189 from installedfiles I
193 create view sync_status_ref_count as
194 select path, status, count(*) as refcount
195 from packagefiles_status
196 where status in ('installed', 'installing', 'removing')
197 group by path, status
200 create view packagefiles_status as
201 select P.status, PF.*
202 from packagefiles_pkgid PF
203 left join packages_pkgid P on P.pkgid = PF.pkgid
206 create view installedfiles as
207 select * from packagefiles_status
208 where status = 'installed'
211 create view install_status as
213 select 'new' as op, PN.*
214 from packagefiles_status PN
215 left join installed_ref_count RC on RC.path = PN.path
216 where RC.refcount is null
217 and PN.status = 'installing'
221 select 'update' as op, PN.*
222 from packagefiles_status PN
223 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
224 left join installed_ref_count RC on RC.path = PN.path
225 where RC.refcount = 1
226 and PN.status = 'installing'
227 and PI.hash is not PN.hash
231 select 'conflict' as op, PI.*
232 from packagefiles_status PN
233 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
234 where PN.status = 'installing'
237 select 'remove' as op, PI.*
238 from installedfiles PI
239 left join packagefiles_status PN
240 on PI.path = PN.path and PI.package = PN.package
241 and PI.pkgid != PN.pkgid
242 where PN.path is null
243 and PI.package in (select package from packages where status = 'installing')
246 -- remove files in removing, but not installing
247 select distinct 'remove' as op, PR.*
248 from packagefiles_status PR
249 left join packagefiles_status PN
251 and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
252 where PN.path is null
253 and PR.status = 'removing'
256 create table pathtags (
262 path text, -- filesystem path
264 primary key (package,version,release,path,tag),
265 foreign key (package,version,release,path)
266 references packagefiles on delete cascade on update cascade
271 create view elfdeps as
272 select PF.pkgid, PF.status, PF.path, N.needed as needs,
273 PL.path as library, PL.pkgid provider, PL.status as library_status
274 from packagefiles_status PF
275 join elfneeded N on N.file = PF.hash
276 left join elflibraries L on N.needed = L.soname
277 left join packagefiles_status PL on PL.hash = L.file
280 -- TODO just elf information?
281 -- and just hash, not package?
282 create table elflibraries (
283 file text primary key,
289 create table elfneeded (
291 needed text, -- soname of dependency
292 primary key (file, needed)
297 -- package scripts: table of package, stage, file
298 create table scripts (
304 primary key (package,version,release,stage),
305 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
308 create view scripts_pkgid as
309 select printf('%s-%s-%s', package, version, release) as pkgid, *
313 -- package dependencies: table of package, dependency, dep type (package, soname)
314 create table packagedeps (
318 requires text, -- package name (only)
321 primary key (package,version,release,package),
322 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
326 create table provides (
329 label text -- a capability label
332 create table requires (
335 label text -- a capability label
338 create table packagegroups (
344 -- not sure how machine readable this needs to be,
345 -- do not at all for now, figure it out later
346 -- could be worth logging all commands in a history table,
347 -- the zpm driver could do that and capture the exit status
349 -- might want the history table to note a "group" to tie together
350 -- sub-invocations, probably an environment variable set if not
351 -- already set by zpm, probably a uuid or a timestamp
352 create table zpmlog (
353 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
354 -- timestamp of action
356 target text, -- packagename, repo name, etc
357 info text -- human readable
360 create table history (
361 ts integer, -- again, probably needs timestamp sub second
367 create table repository (
368 name text primary key, -- our name for a repo
370 priority integer not null default 1,
371 refreshed integer -- last refresh time
374 -- urls for downloading packages. possibly unneeded
375 create table repository_packages (
377 pkg text, -- glob pattern? in which case others not needed
383 -- track which repository a package was cloned from, i.e. where we got it
384 create table packagesource (
388 repository text references repository
391 create view syncconflicts as
394 select BASE.*, 'preserved' as rstatus
395 from packagefiles_status BASE
396 join elflibraries EL on EL.file = BASE.hash
398 BASE.status in ('removed', 'updated')
399 and BASE.hash in (select hash from packagefiles_status where
400 status in ('installed'))
403 select distinct BASE.*,
404 case when P.status = 'installing' and BASE.status = 'installed'
406 when BASE.status in ('removed','updated')
407 and BASE.path in (select path from preserved) then
412 from packagefiles_status BASE
413 left join packages P on P.package = BASE.package
414 and BASE.status in ('installed', 'removing')
415 and P.status = 'installing'
417 -- metadata different
419 select path, count(distinct mds) as mdcount,
420 count(distinct hash) as hashcount
422 where SS.rstatus in ('installing', 'installed')
424 having (count(distinct mds) > 1 or count(distinct hash) > 1)
426 select BASE.*, 'hash' as conflict
428 where path in (select path from md_conflict where hashcount > 1)
430 select BASE.*, 'md' as conflict
432 where path in (select path from md_conflict where mdcount > 1)
435 create view syncinfo as
438 select BASE.*, 'preserved' as rstatus
439 from packagefiles_status BASE
440 join elflibraries EL on EL.file = BASE.hash
442 BASE.status in ('removed', 'updated')
443 and BASE.hash in (select hash from packagefiles_status where
444 status in ('installed'))
447 select distinct BASE.*,
448 case when P.status = 'installing' and BASE.status = 'installed'
450 when BASE.status in ('removed','updated')
451 and BASE.path in (select path from preserved) then
456 from packagefiles_status BASE
457 left join packages P on P.package = BASE.package
458 and BASE.status in ('installed', 'removing')
459 and P.status = 'installing'
461 -- new file: in installing, not in installed or updating or removing
464 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
465 target,devminor,devmajor
467 where path not in (select path from syncstatus where
468 rstatus in ('installed', 'updating', 'removing')
470 and rstatus in ('installing')
472 -- modified: retained, but with different metadata
477 SS.uid, SS.groupname, SS.gid, SS.mode,
478 SS.filetype, SS.mtime, SS.hash, SS.target, SS.devminor, SS.devmajor
481 on SS.path = OS.path and SS.pkgid is not OS.pkgid
483 and OS.rstatus in ('installed','updating','removing')
484 and (SS.mds is not OS.mds or SS.hash is not OS.hash)
486 SS.rstatus in ('installing')
488 -- preserve: libraries needed by something in installed or installing
493 where status in ('installed', 'installing')
494 and library is not null
498 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
499 target,devminor,devmajor
501 where path in (select library from needed)
502 and SS.rstatus in ('removing', 'removed')
504 -- remove: cur, not preserved, not in final set
507 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
508 target,devminor,devmajor
511 select path from syncstatus where
512 rstatus in ('installed', 'installing')
514 and path not in (select path from preserve)
515 and rstatus in ('removing', 'updating')
517 -- expired: libraries that had been preserved, but aren't needed now
520 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
521 target,devminor,devmajor
523 where hash in (select file from elflibraries where file is not null)
524 and path not in (select path from preserve)
525 and rstatus in ('removed','updated')
527 select 'update' as op, * from modified
529 select 'remove' as op, * from remove
531 select 'obsolete' as op, * from expired
533 select 'new' as op, * from newfiles
535 select 'preserve' as op, * from preserve