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 default '0644', -- perms, use text for octal rep?
132 username text not null default 'root', -- name of owner
133 groupname text not null default 'root', -- 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,
281 create index elf_library_name_index on elflibraries(soname);
283 create table elfneeded (
284 file text, -- hash of file
285 needed text, -- soname of dependency
286 primary key (file, needed)
291 create view package_libraries as
292 select distinct PF.pkgid, EL.soname
293 from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
296 create view package_libraries_needed as
298 select distinct EN.needed as soname, PF.pkgid
300 join packagefiles_pkgid PF on PF.hash = EN.file
303 select distinct EL.soname, PF.pkgid
305 join packagefiles_pkgid PF on PF.hash = EL.file
307 select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
309 left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
312 -- package scripts: table of package, stage, file
313 create table scripts (
319 primary key (package,version,release,stage),
320 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
323 create view scripts_pkgid as
324 select printf('%s-%s-%s', package, version, release) as pkgid, *
328 -- package dependencies: table of package, dependency, dep type (package, soname)
329 create table packagedeps (
333 requires text, -- package name (only)
336 primary key (package,version,release,package),
337 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
341 create table provides (
344 label text -- a capability label
347 create table requires (
350 label text -- a capability label
353 create table packagegroups (
359 -- not sure how machine readable this needs to be,
360 -- do not at all for now, figure it out later
361 -- could be worth logging all commands in a history table,
362 -- the zpm driver could do that and capture the exit status
364 -- might want the history table to note a "group" to tie together
365 -- sub-invocations, probably an environment variable set if not
366 -- already set by zpm, probably a uuid or a timestamp
367 create table zpmlog (
368 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
369 -- timestamp of action
371 target text, -- packagename, repo name, etc
372 info text -- human readable
376 id integer primary key, -- rowid alias
377 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
379 pkgid text, -- package
380 path text, -- file path involved
381 file text, -- hash of file
382 ack integer default 0
385 create table history (
386 ts integer, -- again, probably needs timestamp sub second
392 create table repository (
393 name text primary key, -- our name for a repo
395 priority integer not null default 1,
396 refreshed integer -- last refresh time
398 -- force the url to be repourl/info.repo
399 -- package urls repourl/pkgid.zpm
401 -- urls for downloading packages. possibly unneeded
402 create table repository_packages (
404 pkg text, -- glob pattern? in which case others not needed
410 -- track which repository a package was cloned from, i.e. where we got it
411 create table packagesource (
415 repository text references repository
418 create view syncconflicts as
420 -- metadata different
422 select path, count(distinct
423 printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
424 count(distinct PFA.hash) as hashcount
425 from packagefiles PFA
427 on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
428 where PA.status in ('installing', 'installed')
430 having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
431 ) > 1 or count(distinct PFA.hash) > 1)
434 printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
435 printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
437 from packagefiles PFH
438 where path in (select path from md_conflict where hashcount > 1)
441 printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
442 printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
444 from packagefiles PFM
445 where path in (select path from md_conflict where mdcount > 1)
448 create view needed_libraries as
450 libs(file,needs,provider) as (
451 select N.file, N.needed as needs, L.file as provider
452 from elfneeded N left join elflibraries L on N.needed = L.soname
454 select L.file, N.needed as needs, EL.file as provider
456 join elfneeded N on N.file = L.provider
457 left join elflibraries EL on N.needed = EL.soname
462 * tables for repository info, essentially materalized views
464 create table repository_libs (
469 create table repository_libsneeded (
475 create view syncinfo as
477 -- paths to libraries we need to keep around
479 select distinct PFL.*
480 from packagefiles PFL
481 join elflibraries EL on EL.file = PFL.hash
482 join elfneeded EN on EN.needed = EL.soname
483 join packagefiles PFN on EN.file = PFN.hash
485 on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
487 PN.status = 'installing' or PN.status = 'installed'
488 and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release)
493 join elflibraries EL on EL.file = PF.hash
494 join elfneeded EN on EN.needed = EL.soname
495 join packagefiles PL on EN.file = PL.hash
497 on PL.path = P.package and PL.version = P.version and PL.release = P.release
499 P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
501 -- every path in 'installing' is either new or update, or no-op
504 when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
506 printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is
507 printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname)
508 and PFI.hash is PFC.hash
512 printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
514 PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
516 PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
518 PFI.target, PFI.device,
520 printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds,
521 printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds
525 on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release
526 left join packages PC
527 on PC.package = PI.package and PC.status in ('installed','removing','updating')
528 left join packagefiles PFC
529 on PFC.package = PC.package
530 and PFC.version = PC.version
531 and PFC.release = PC.release
532 and PFC.path = PFI.path
534 PI.status = 'installing'
536 -- every path in updating is either remove or no-op
539 printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
541 PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
543 PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
545 PFU.target, PFU.device,
547 printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
549 from packagefiles PFU
551 on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release
552 -- inner join because the installing package must exist or this shouldn't
553 -- be an 'updating' package
554 join packages P on PU.package = P.package and P.status = 'installing'
555 left join packagefiles PFI on
556 PFI.package = P.package
557 and PFI.version = P.version
558 and PFI.release = P.release
559 and PFI.path = PFU.path
561 -- handle paths owned by other installed packages
562 left join packages PI on PI.status = 'installed'
563 left join packagefiles PFC
564 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path
567 PU.status in ('updating')
570 and PFU.path not in (select path from preserve)
572 -- every path in removing is either remove or no-op
575 printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
577 PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
579 PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
581 PFR.target, PFR.device,
583 printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
585 from packagefiles PFR
587 on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release
588 left join packages P on PU.package = P.package and P.status = 'installing'
589 left join packagefiles PFI on PFI.path = PFR.path
590 and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release
592 -- handle paths owned by other installed packages
593 left join packages PI on PI.status = 'installed'
594 left join packagefiles PFC
595 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path
598 PU.status in ('removing')
601 and PFR.path not in (select path from preserve)
603 -- paths in 'installed' or 'updated' are no-ops
607 printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
609 PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
611 PFP.configuration as configuration,
612 PFP.target, PFP.device,
614 printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
617 -- TODO don't include installed or installing
618 -- left join packages P on P.package = PFP.package ...
619 -- where P.status not in ('installed','installing')