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 view installed_ref_count as
198 select I.path, count(*) as refcount
199 from installedfiles I
203 create view sync_status_ref_count as
204 select path, status, count(*) as refcount
205 from packagefiles_status
206 where status in ('installed', 'installing', 'removing')
207 group by path, status
210 create view packagefiles_status as
211 select P.status, PF.*
212 from packagefiles_pkgid PF
213 left join packages_pkgid P on P.pkgid = PF.pkgid
216 create view installedfiles as
217 select * from packagefiles_status
218 where status = 'installed'
221 create table pathtags (
227 path text, -- filesystem path
229 primary key (package,version,release,path,tag),
230 foreign key (package,version,release,path)
231 references packagefiles on delete cascade on update cascade
236 create view elfdeps as
237 select PF.pkgid, PF.status, PF.path, N.needed as needs,
238 PL.path as library, PL.pkgid provider, PL.status as library_status
239 from packagefiles_status PF
240 join elfneeded N on N.file = PF.hash
241 left join elflibraries L on N.needed = L.soname
242 left join packagefiles_status PL on PL.hash = L.file
245 -- TODO just elf information?
246 -- and just hash, not package?
247 create table elflibraries (
248 file text primary key,
254 create table elfneeded (
256 needed text, -- soname of dependency
257 primary key (file, needed)
262 -- package scripts: table of package, stage, file
263 create table scripts (
269 primary key (package,version,release,stage),
270 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
273 create view scripts_pkgid as
274 select printf('%s-%s-%s', package, version, release) as pkgid, *
278 -- package dependencies: table of package, dependency, dep type (package, soname)
279 create table packagedeps (
283 requires text, -- package name (only)
286 primary key (package,version,release,package),
287 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
291 create table provides (
294 label text -- a capability label
297 create table requires (
300 label text -- a capability label
303 create table packagegroups (
309 -- not sure how machine readable this needs to be,
310 -- do not at all for now, figure it out later
311 -- could be worth logging all commands in a history table,
312 -- the zpm driver could do that and capture the exit status
314 -- might want the history table to note a "group" to tie together
315 -- sub-invocations, probably an environment variable set if not
316 -- already set by zpm, probably a uuid or a timestamp
317 create table zpmlog (
318 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
319 -- timestamp of action
321 target text, -- packagename, repo name, etc
322 info text -- human readable
326 id integer primary key, -- rowid alias
327 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
329 pkgid text, -- package
330 path text, -- file path involved
331 file text, -- hash of file
332 ack integer default 0
335 create table history (
336 ts integer, -- again, probably needs timestamp sub second
342 create table repository (
343 name text primary key, -- our name for a repo
345 priority integer not null default 1,
346 refreshed integer -- last refresh time
349 -- urls for downloading packages. possibly unneeded
350 create table repository_packages (
352 pkg text, -- glob pattern? in which case others not needed
358 -- track which repository a package was cloned from, i.e. where we got it
359 create table packagesource (
363 repository text references repository
366 create view syncconflicts as
369 select BASE.*, 'preserved' as rstatus
370 from packagefiles_status BASE
371 join elflibraries EL on EL.file = BASE.hash
373 BASE.status in ('removed', 'updated')
374 and BASE.hash in (select hash from packagefiles_status where
375 status in ('installed'))
378 select distinct BASE.*,
379 case when P.status = 'installing' and BASE.status = 'installed'
381 when BASE.status in ('removed','updated')
382 and BASE.path in (select path from preserved) then
387 from packagefiles_status BASE
388 left join packages P on P.package = BASE.package
389 and BASE.status in ('installed', 'removing')
390 and P.status = 'installing'
392 -- metadata different
394 select path, count(distinct mds) as mdcount,
395 count(distinct hash) as hashcount
397 where SS.rstatus in ('installing', 'installed')
399 having (count(distinct mds) > 1 or count(distinct hash) > 1)
401 select BASE.*, 'hash' as conflict
403 where path in (select path from md_conflict where hashcount > 1)
405 select BASE.*, 'md' as conflict
407 where path in (select path from md_conflict where mdcount > 1)
410 create view syncinfo as
413 select BASE.*, 'preserved' as rstatus
414 from packagefiles_status BASE
415 join elflibraries EL on EL.file = BASE.hash
417 BASE.status in ('removed', 'updated')
418 and BASE.hash in (select hash from packagefiles_status where
419 status in ('installed'))
422 select distinct BASE.*,
423 case when P.status = 'installing' and BASE.status = 'installed'
425 when BASE.status in ('removed','updated')
426 and BASE.path in (select path from preserved) then
431 from packagefiles_status BASE
432 left join packages P on P.package = BASE.package
433 and BASE.status in ('installed', 'removing')
434 and P.status = 'installing'
436 -- new file: in installing, not in installed or updating or removing
439 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
440 configuration,target,device, null as ohash
442 where path not in (select path from syncstatus where
443 rstatus in ('installed', 'updating', 'removing')
445 and rstatus in ('installing')
447 -- modified: retained, but with different metadata
451 SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
452 SS.mtime, SS.hash, SS.configuration, SS.target, SS.device,
453 OS.hash as ohash, SS.mds, OS.mds as omds
456 on SS.path = OS.path and SS.pkgid is not OS.pkgid
458 and OS.rstatus in ('installed','updating','removing')
459 and (SS.mds is not OS.mds or SS.hash is not OS.hash)
461 SS.rstatus in ('installing')
463 -- preserve: libraries needed by something in installed or installing
468 where status in ('installed', 'installing')
469 and library is not null
473 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
474 configuration,target,device, null as ohash
476 where path in (select library from needed)
477 and SS.rstatus in ('removing', 'removed')
479 -- remove: cur, not preserved, not in final set
482 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
483 configuration,target,device, null as ohash
486 select path from syncstatus where
487 rstatus in ('installed', 'installing')
489 and path not in (select path from preserve)
490 and rstatus in ('removing', 'updating')
492 -- expired: libraries that had been preserved, but aren't needed now
495 path,username,uid,groupname,gid,mode,filetype,mtime,hash,
496 configuration,target,device, null as ohash
498 where hash in (select file from elflibraries where file is not null)
499 and path not in (select path from preserve)
500 and rstatus in ('removed','updated')
502 select 'update' as op, *
503 ,(select group_concat(pkgid, ' ')
505 where SS.rstatus = 'installing' and SS.path = M.path
509 select 'remove' as op, *, null, null, null from remove
511 select 'obsolete' as op, *, null, null, null from expired
513 select 'new' as op, *, null, null, null from newfiles
515 select 'preserve' as op, *, null, null, null from preserve