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
31 licenses text, -- hash of actual license? need table for more than one?
33 build_time integer default (strftime('%s', 'now')),
35 hash text, -- see integ.c for package hash details
36 primary key (package,version,release),
37 check (typeof(package) = 'text'),
38 check (typeof(version) = 'text'),
39 check (typeof(release) = 'integer'),
41 -- enforce name and version conventions
42 check(instr(package,' ') = 0),
43 check(instr(package,'/') = 0),
44 check(instr(package,':') = 0),
45 check(instr(version,' ') = 0),
46 check(instr(version,'-') = 0),
47 check(instr(version,'/') = 0),
48 check(instr(version,':') = 0),
49 check(length(package) < 64),
50 check(length(package) > 0),
51 check(length(version) < 32),
52 check(length(version) > 0)
57 create index package_status_index on packages (status);
58 create index package_package_index on packages (package);
60 create view packages_pkgid as
61 select printf('%s-%s-%s', package, version, release) as pkgid, *
64 create trigger packages_delete_trigger instead of
65 delete on packages_pkgid
67 delete from packages where package = OLD.package
68 and version = OLD.version and release = OLD.release;
71 create trigger packages_update_trigger instead of
72 update on packages_pkgid
75 set package = NEW.package,
76 version = NEW.version,
77 release = NEW.release,
78 description = NEW.description,
79 architecture = NEW.architecture,
82 licenses = NEW.licenses,
83 packager = NEW.packager,
84 build_time = NEW.build_time,
85 install_time = NEW.install_time,
87 where package = OLD.package
88 and version = OLD.version
89 and release = OLD.release
94 -- handle package status history with a logging trigger.
95 create trigger logpkgstatus after update of status on packages
96 begin insert into zpmlog (action,target,info)
97 values (printf('status change %s %s', OLD.status, NEW.status),
98 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
101 create table packagetags (
107 set_time integer default (strftime('%s', 'now')),
108 primary key (package,version,release,tag),
109 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
112 -- files contained in a package
113 create table packagefiles (
119 path text, -- filesystem path
120 mode text not null, -- perms, use text for octal rep?
121 username text not null, -- name of owner
122 groupname text not null, -- group of owner
123 uid integer, -- numeric uid, generally ignored
124 gid integer, -- numeric gid, generally ignored
125 configuration integer not null default 0, -- boolean if config file
126 confhash text, -- last hash on disk
127 filetype varchar not null default 'r',
131 -- h hard link -- not supported
132 -- c character special -- not supported
133 -- b block special -- not supported
134 -- c and b device special files add dev number column
135 -- p fifos (i.e. pipe) -- not supported
136 -- s unix domain socket -- not supported
137 target text, -- link target for links
138 device integer, -- device file dev_t
139 hash text, -- null if not a regular file
140 mtime integer, -- seconds since epoch, finer resolution not needed
141 primary key (package,version,release,path),
142 foreign key (package,version,release)
143 references packages (package,version,release)
144 on delete cascade on update cascade,
145 check (not (filetype = 'l' and target is null)),
146 check (not (filetype = 'h' and target is null)),
147 check (target is null or length(target) between 1 and 4095),
148 check (hash is null or length(hash) between 1 and 1024),
149 check (not (filetype = 'r' and hash is null)),
150 check (not (filetype = 'c' and device is null)),
151 check (not (filetype = 'b' and device is null)),
152 check (filetype in ('r','d','l','h','c','b','p')),
153 check(length(username) between 1 and 256),
154 check(length(groupname) between 1 and 256),
155 check (configuration = 0 or configuration = 1)
160 create index packagefile_package_index on packagefiles (package);
161 create index packagefile_path_index on packagefiles (path);
162 create index packagefile_hash_index on packagefiles (hash);
164 create view packagefiles_pkgid as
165 select printf('%s-%s-%s', package, version, release) as pkgid, *,
166 printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
170 create trigger packagefiles_update_trigger instead of
171 update on packagefiles_pkgid
174 set package = NEW.package,
175 version = NEW.version,
176 release = NEW.release,
179 username = NEW.username,
180 groupname = NEW.groupname,
183 configuration = NEW.configuration,
184 filetype = NEW.filetype,
189 where package = OLD.package
190 and version = OLD.version
191 and release = OLD.release
197 create trigger packagefiles_delete_trigger instead of
198 delete on packagefiles_pkgid
200 delete from packagefiles
201 where package = OLD.package
202 and version = OLD.version
203 and release = OLD.release
206 update packages set hash = null
207 where package = OLD.package
208 and version = OLD.version
209 and release = OLD.release
214 create view installed_ref_count as
215 select I.path, count(*) as refcount
216 from installedfiles I
220 create view sync_status_ref_count as
221 select path, status, count(*) as refcount
222 from packagefiles_status
223 where status in ('installed', 'installing', 'removing')
224 group by path, status
227 create view packagefiles_status as
228 select P.status, PF.*
229 from packagefiles_pkgid PF
230 left join packages_pkgid P on P.pkgid = PF.pkgid
233 create view installedfiles as
234 select * from packagefiles_status
235 where status = 'installed'
238 create table pathtags (
244 path text, -- filesystem path
246 primary key (package,version,release,path,tag),
247 foreign key (package,version,release,path)
248 references packagefiles on delete cascade on update cascade
253 create view elfdeps as
254 select PF.pkgid, PF.status, PF.path, N.needed as needs,
255 PL.path as library, PL.pkgid provider, PL.status as library_status
256 from packagefiles_status PF
257 join elfneeded N on N.file = PF.hash
258 left join elflibraries L on N.needed = L.soname
259 left join packagefiles_status PL on PL.hash = L.file
262 -- TODO just elf information?
263 -- and just hash, not package?
264 create table elflibraries (
265 file text primary key,
271 create table elfneeded (
273 needed text, -- soname of dependency
274 primary key (file, needed)
279 -- package scripts: table of package, stage, file
280 create table scripts (
286 primary key (package,version,release,stage),
287 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
290 create view scripts_pkgid as
291 select printf('%s-%s-%s', package, version, release) as pkgid, *
295 -- package dependencies: table of package, dependency, dep type (package, soname)
296 create table packagedeps (
300 requires text, -- package name (only)
303 primary key (package,version,release,package),
304 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
308 create table provides (
311 label text -- a capability label
314 create table requires (
317 label text -- a capability label
320 create table packagegroups (
326 -- not sure how machine readable this needs to be,
327 -- do not at all for now, figure it out later
328 -- could be worth logging all commands in a history table,
329 -- the zpm driver could do that and capture the exit status
331 -- might want the history table to note a "group" to tie together
332 -- sub-invocations, probably an environment variable set if not
333 -- already set by zpm, probably a uuid or a timestamp
334 create table zpmlog (
335 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
336 -- timestamp of action
338 target text, -- packagename, repo name, etc
339 info text -- human readable
343 id integer primary key, -- rowid alias
344 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
346 pkgid text, -- package
347 path text, -- file path involved
348 file text, -- hash of file
349 ack integer default 0
352 create table history (
353 ts integer, -- again, probably needs timestamp sub second
359 create table repository (
360 name text primary key, -- our name for a repo
362 priority integer not null default 1,
363 refreshed integer -- last refresh time
366 -- urls for downloading packages. possibly unneeded
367 create table repository_packages (
369 pkg text, -- glob pattern? in which case others not needed
375 -- track which repository a package was cloned from, i.e. where we got it
376 create table packagesource (
380 repository text references repository
383 create view syncconflicts as
386 select BASE.*, 'preserved' as rstatus
387 from packagefiles_status BASE
388 join elflibraries EL on EL.file = BASE.hash
390 BASE.status in ('removed', 'updated')
391 and BASE.hash in (select hash from packagefiles_status where
392 status in ('installed'))
395 select distinct BASE.*,
396 case when P.status = 'installing' and BASE.status = 'installed'
398 when BASE.status in ('removed','updated')
399 and BASE.path in (select path from preserved) then
404 from packagefiles_status BASE
405 left join packages P on P.package = BASE.package
406 and BASE.status in ('installed', 'removing')
407 and P.status = 'installing'
409 -- metadata different
411 select path, count(distinct mds) as mdcount,
412 count(distinct hash) as hashcount
414 where SS.rstatus in ('installing', 'installed')
416 having (count(distinct mds) > 1 or count(distinct hash) > 1)
418 select BASE.*, 'hash' as conflict
420 where path in (select path from md_conflict where hashcount > 1)
422 select BASE.*, 'md' as conflict
424 where path in (select path from md_conflict where mdcount > 1)
427 create view syncinfo as
430 select BASE.*, 'preserved' as rstatus
431 from packagefiles_status BASE
432 join elflibraries EL on EL.file = BASE.hash
434 BASE.status in ('removed', 'updated')
435 and BASE.hash in (select hash from packagefiles_status where
436 status in ('installed'))
439 select distinct BASE.*,
440 case when P.status = 'installing' and BASE.status = 'installed'
442 when BASE.status in ('removed','updated')
443 and BASE.path in (select path from preserved) then
448 from packagefiles_status BASE
449 left join packages P on P.package = BASE.package
450 and BASE.status in ('installed', 'removing')
451 and P.status = 'installing'
453 -- new file: in installing, not in installed or updating or removing
456 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
457 configuration,target,device, null as ohash
459 where path not in (select path from syncstatus where
460 rstatus in ('installed', 'updating', 'removing')
462 and rstatus in ('installing')
464 -- modified: retained, but with different metadata
468 SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
469 SS.mtime, SS.hash, SS.configuration, SS.target, SS.device,
470 OS.hash as ohash, SS.mds, OS.mds as omds
473 on SS.path = OS.path and SS.pkgid is not OS.pkgid
475 and OS.rstatus in ('installed','updating','removing')
476 and (SS.mds is not OS.mds or SS.hash is not OS.hash)
478 SS.rstatus in ('installing')
480 -- preserve: libraries needed by something in installed or installing
485 where status in ('installed', 'installing')
486 and library is not null
490 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
491 configuration,target,device, null as ohash
493 where path in (select library from needed)
494 and SS.rstatus in ('removing', 'removed')
496 -- remove: cur, not preserved, not in final set
499 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
500 configuration,target,device, null as ohash
503 select path from syncstatus where
504 rstatus in ('installed', 'installing')
506 and path not in (select path from preserve)
507 and rstatus in ('removing', 'updating')
509 -- expired: libraries that had been preserved, but aren't needed now
512 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
513 configuration,target,device, null as ohash
515 where hash in (select file from elflibraries where file is not null)
516 and path not in (select path from preserve)
517 and rstatus in ('removed','updated')
519 select 'update' as op, *
520 ,(select group_concat(pkgid, ' ')
522 where SS.rstatus = 'installing' and SS.path = M.path
526 select 'remove' as op, *, null, null, null from remove
528 select 'obsolete' as op, *, null, null, null from expired
530 select 'new' as op, *, null, null, null from newfiles
532 select 'preserve' as op, *, null, null, null from preserve