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 create view filerefs as
20 count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
22 left join packagefiles PF on PF.hash = F.hash
23 left join scripts S on S.hash = F.hash
24 left join elflibraries EL on EL.file = F.hash
25 left join notes N on N.file = F.hash
29 -- information about packages
30 -- a package is identified by a package,version,release triple
31 create table packages (
32 -- primary key columns
33 package text not null,
34 version text not null, -- the upstream version string
35 release integer not null, -- the local release number
42 licenses text, -- hash of actual license? need table for more than one?
44 build_time integer default (strftime('%s', 'now')),
46 hash text, -- see integ.c for package hash details
47 primary key (package,version,release),
48 check (typeof(package) = 'text'),
49 check (typeof(version) = 'text'),
50 check (typeof(release) = 'integer'),
52 -- enforce name and version conventions
53 check(instr(package,' ') = 0),
54 check(instr(package,'/') = 0),
55 check(instr(package,':') = 0),
56 check(instr(version,' ') = 0),
57 check(instr(version,'-') = 0),
58 check(instr(version,'/') = 0),
59 check(instr(version,':') = 0),
60 check(length(package) < 64),
61 check(length(package) > 0),
62 check(length(version) < 32),
63 check(length(version) > 0)
68 create index package_status_index on packages (status);
69 create index package_package_index on packages (package);
71 create view packages_pkgid as
72 select printf('%s-%s-%s', package, version, release) as pkgid, *
75 create trigger packages_delete_trigger instead of
76 delete on packages_pkgid
78 delete from packages where package = OLD.package
79 and version = OLD.version and release = OLD.release;
82 create trigger packages_update_trigger instead of
83 update on packages_pkgid
86 set package = NEW.package,
87 version = NEW.version,
88 release = NEW.release,
89 description = NEW.description,
90 architecture = NEW.architecture,
93 licenses = NEW.licenses,
94 packager = NEW.packager,
95 build_time = NEW.build_time,
96 install_time = NEW.install_time,
98 where package = OLD.package
99 and version = OLD.version
100 and release = OLD.release
105 -- handle package status history with a logging trigger.
106 create trigger logpkgstatus after update of status on packages
107 begin insert into zpmlog (action,target,info)
108 values (printf('status change %s %s', OLD.status, NEW.status),
109 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
112 create table packagetags (
118 set_time integer default (strftime('%s', 'now')),
119 primary key (package,version,release,tag),
120 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
123 -- files contained in a package
124 create table packagefiles (
130 path text, -- filesystem path
131 mode text not null, -- perms, use text for octal rep?
132 username text not null, -- name of owner
133 groupname text not null, -- group of owner
134 uid integer, -- numeric uid, generally ignored
135 gid integer, -- numeric gid, generally ignored
136 configuration integer not null default 0, -- boolean if config file
137 confhash text, -- last hash on disk
138 filetype varchar not null default 'r',
142 -- h hard link -- not supported
143 -- c character special -- not supported
144 -- b block special -- not supported
145 -- c and b device special files add dev number column
146 -- p fifos (i.e. pipe) -- not supported
147 -- s unix domain socket -- not supported
148 target text, -- link target for links
149 device integer, -- device file dev_t
150 hash text, -- null if not a regular file
151 mtime integer, -- seconds since epoch, finer resolution not needed
152 primary key (package,version,release,path),
153 foreign key (package,version,release)
154 references packages (package,version,release)
155 on delete cascade on update cascade,
156 check (not (filetype = 'l' and target is null)),
157 check (not (filetype = 'h' and target is null)),
158 check (target is null or length(target) between 1 and 4095),
159 check (hash is null or length(hash) between 1 and 1024),
160 check (not (filetype = 'r' and hash is null)),
161 check (not (filetype = 'c' and device is null)),
162 check (not (filetype = 'b' and device is null)),
163 check (filetype in ('r','d','l','h','c','b','p')),
164 check(length(username) between 1 and 256),
165 check(length(groupname) between 1 and 256),
166 check (configuration = 0 or configuration = 1)
171 create index packagefile_package_index on packagefiles (package);
172 create index packagefile_path_index on packagefiles (path);
173 create index packagefile_hash_index on packagefiles (hash);
175 create view packagefiles_pkgid as
176 select printf('%s-%s-%s', package, version, release) as pkgid, *,
177 printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
181 create trigger packagefiles_update_trigger instead of
182 update on packagefiles_pkgid
185 set package = NEW.package,
186 version = NEW.version,
187 release = NEW.release,
190 username = NEW.username,
191 groupname = NEW.groupname,
194 configuration = NEW.configuration,
195 filetype = NEW.filetype,
200 where package = OLD.package
201 and version = OLD.version
202 and release = OLD.release
208 create trigger packagefiles_delete_trigger instead of
209 delete on packagefiles_pkgid
211 delete from packagefiles
212 where package = OLD.package
213 and version = OLD.version
214 and release = OLD.release
217 update packages set hash = null
218 where package = OLD.package
219 and version = OLD.version
220 and release = OLD.release
225 create view installed_ref_count as
226 select I.path, count(*) as refcount
227 from installedfiles I
231 create view sync_status_ref_count as
232 select path, status, count(*) as refcount
233 from packagefiles_status
234 where status in ('installed', 'installing', 'removing')
235 group by path, status
238 create view packagefiles_status as
239 select P.status, PF.*
240 from packagefiles_pkgid PF
241 left join packages_pkgid P on P.pkgid = PF.pkgid
244 create view installedfiles as
245 select * from packagefiles_status
246 where status = 'installed'
249 create table pathtags (
255 path text, -- filesystem path
257 primary key (package,version,release,path,tag),
258 foreign key (package,version,release,path)
259 references packagefiles on delete cascade on update cascade
264 create view elfdeps as
265 select PF.pkgid, PF.status, PF.path, N.needed as needs,
266 PL.path as library, PL.pkgid provider, PL.status as library_status
267 from packagefiles_status PF
268 join elfneeded N on N.file = PF.hash
269 left join elflibraries L on N.needed = L.soname
270 left join packagefiles_status PL on PL.hash = L.file
273 -- TODO just elf information?
274 -- and just hash, not package?
275 create table elflibraries (
276 file text primary key,
282 create table elfneeded (
284 needed text, -- soname of dependency
285 primary key (file, needed)
290 -- package scripts: table of package, stage, file
291 create table scripts (
297 primary key (package,version,release,stage),
298 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
301 create view scripts_pkgid as
302 select printf('%s-%s-%s', package, version, release) as pkgid, *
306 -- package dependencies: table of package, dependency, dep type (package, soname)
307 create table packagedeps (
311 requires text, -- package name (only)
314 primary key (package,version,release,package),
315 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
319 create table provides (
322 label text -- a capability label
325 create table requires (
328 label text -- a capability label
331 create table packagegroups (
337 -- not sure how machine readable this needs to be,
338 -- do not at all for now, figure it out later
339 -- could be worth logging all commands in a history table,
340 -- the zpm driver could do that and capture the exit status
342 -- might want the history table to note a "group" to tie together
343 -- sub-invocations, probably an environment variable set if not
344 -- already set by zpm, probably a uuid or a timestamp
345 create table zpmlog (
346 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
347 -- timestamp of action
349 target text, -- packagename, repo name, etc
350 info text -- human readable
354 id integer primary key, -- rowid alias
355 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
357 pkgid text, -- package
358 path text, -- file path involved
359 file text, -- hash of file
360 ack integer default 0
363 create table history (
364 ts integer, -- again, probably needs timestamp sub second
370 create table repository (
371 name text primary key, -- our name for a repo
373 priority integer not null default 1,
374 refreshed integer -- last refresh time
377 -- urls for downloading packages. possibly unneeded
378 create table repository_packages (
380 pkg text, -- glob pattern? in which case others not needed
386 -- track which repository a package was cloned from, i.e. where we got it
387 create table packagesource (
391 repository text references repository
394 create view syncconflicts as
397 select BASE.*, 'preserved' as rstatus
398 from packagefiles_status BASE
399 join elflibraries EL on EL.file = BASE.hash
401 BASE.status in ('removed', 'updated')
402 and BASE.hash in (select hash from packagefiles_status where
403 status in ('installed'))
406 select distinct BASE.*,
407 case when P.status = 'installing' and BASE.status = 'installed'
409 when BASE.status in ('removed','updated')
410 and BASE.path in (select path from preserved) then
415 from packagefiles_status BASE
416 left join packages P on P.package = BASE.package
417 and BASE.status in ('installed', 'removing')
418 and P.status = 'installing'
420 -- metadata different
422 select path, count(distinct mds) as mdcount,
423 count(distinct hash) as hashcount
425 where SS.rstatus in ('installing', 'installed')
427 having (count(distinct mds) > 1 or count(distinct hash) > 1)
429 select BASE.*, 'hash' as conflict
431 where path in (select path from md_conflict where hashcount > 1)
433 select BASE.*, 'md' as conflict
435 where path in (select path from md_conflict where mdcount > 1)
438 create view syncinfo as
441 select BASE.*, 'preserved' as rstatus
442 from packagefiles_status BASE
443 join elflibraries EL on EL.file = BASE.hash
445 BASE.status in ('removed', 'updated')
446 and BASE.hash in (select hash from packagefiles_status where
447 status in ('installed'))
450 select distinct BASE.*,
451 case when P.status = 'installing' and BASE.status = 'installed'
453 when BASE.status in ('removed','updated')
454 and BASE.path in (select path from preserved) then
459 from packagefiles_status BASE
460 left join packages P on P.package = BASE.package
461 and BASE.status in ('installed', 'removing')
462 and P.status = 'installing'
464 -- new file: in installing, not in installed or updating or removing
467 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
468 configuration,target,device, null as ohash
470 where path not in (select path from syncstatus where
471 rstatus in ('installed', 'updating', 'removing')
473 and rstatus in ('installing')
475 -- modified: retained, but with different metadata
479 SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
480 SS.mtime, SS.hash, SS.configuration, SS.target, SS.device,
481 OS.hash as ohash, SS.mds, OS.mds as omds
484 on SS.path = OS.path and SS.pkgid is not OS.pkgid
486 and OS.rstatus in ('installed','updating','removing')
487 and (SS.mds is not OS.mds or SS.hash is not OS.hash)
489 SS.rstatus in ('installing')
491 -- preserve: libraries needed by something in installed or installing
496 where status in ('installed', 'installing')
497 and library is not null
501 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
502 configuration,target,device, null as ohash
504 where path in (select library from needed)
505 and SS.rstatus in ('removing', 'removed')
507 -- remove: cur, not preserved, not in final set
510 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
511 configuration,target,device, null as ohash
514 select path from syncstatus where
515 rstatus in ('installed', 'installing')
517 and path not in (select path from preserve)
518 and rstatus in ('removing', 'updating')
520 -- expired: libraries that had been preserved, but aren't needed now
523 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
524 configuration,target,device, null as ohash
526 where hash in (select file from elflibraries where file is not null)
527 and path not in (select path from preserve)
528 and rstatus in ('removed','updated')
530 select 'update' as op, *
531 ,(select group_concat(pkgid, ' ')
533 where SS.rstatus = 'installing' and SS.path = M.path
537 select 'remove' as op, *, null, null, null from remove
539 select 'obsolete' as op, *, null, null, null from expired
541 select 'new' as op, *, null, null, null from newfiles
543 select 'preserve' as op, *, null, null, null from preserve