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);
174 create index pkgfile_configuration on packagefiles (configuration) where configuration = 1;
176 create view packagefiles_pkgid as
177 select printf('%s-%s-%s', package, version, release) as pkgid, *,
178 printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
182 create trigger packagefiles_update_trigger instead of
183 update on packagefiles_pkgid
186 set package = NEW.package,
187 version = NEW.version,
188 release = NEW.release,
191 username = NEW.username,
192 groupname = NEW.groupname,
195 configuration = NEW.configuration,
196 filetype = NEW.filetype,
201 where package = OLD.package
202 and version = OLD.version
203 and release = OLD.release
209 create trigger packagefiles_delete_trigger instead of
210 delete on packagefiles_pkgid
212 delete from packagefiles
213 where package = OLD.package
214 and version = OLD.version
215 and release = OLD.release
218 update packages set hash = null
219 where package = OLD.package
220 and version = OLD.version
221 and release = OLD.release
226 create view installed_ref_count as
227 select I.path, count(*) as refcount
228 from installedfiles I
232 create view sync_status_ref_count as
233 select path, status, count(*) as refcount
234 from packagefiles_status
235 where status in ('installed', 'installing', 'removing')
236 group by path, status
239 create view packagefiles_status as
240 select P.status, PF.*
241 from packagefiles_pkgid PF
242 left join packages_pkgid P on P.pkgid = PF.pkgid
245 create view installedfiles as
246 select * from packagefiles_status
247 where status = 'installed'
250 create table pathtags (
256 path text, -- filesystem path
258 primary key (package,version,release,path,tag),
259 foreign key (package,version,release,path)
260 references packagefiles on delete cascade on update cascade
265 create view elfdeps as
266 select PF.pkgid, PF.status, PF.path, N.needed as needs,
267 PL.path as library, PL.pkgid provider, PL.status as library_status
268 from packagefiles_status PF
269 join elfneeded N on N.file = PF.hash
270 left join elflibraries L on N.needed = L.soname
271 left join packagefiles_status PL on PL.hash = L.file
274 -- TODO just elf information?
275 -- and just hash, not package?
276 create table elflibraries (
277 file text primary key,
282 create index elf_library_name_index on elflibraries(soname);
284 create table elfneeded (
285 file text, -- hash of file
286 needed text, -- soname of dependency
287 primary key (file, needed)
292 create view package_libraries as
293 select distinct PF.pkgid, EL.soname
294 from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
297 create view package_libraries_needed as
299 select distinct EN.needed as soname, PF.pkgid
301 join packagefiles_pkgid PF on PF.hash = EN.file
304 select distinct EL.soname, PF.pkgid
306 join packagefiles_pkgid PF on PF.hash = EL.file
308 select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
310 left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
313 -- package scripts: table of package, stage, file
314 create table scripts (
320 primary key (package,version,release,stage),
321 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
324 create view scripts_pkgid as
325 select printf('%s-%s-%s', package, version, release) as pkgid, *
329 -- package dependencies: table of package, dependency, dep type (package, soname)
330 create table packagedeps (
334 requires text, -- package name (only)
337 primary key (package,version,release,package),
338 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
342 create table provides (
345 label text -- a capability label
348 create table requires (
351 label text -- a capability label
354 create table packagegroups (
360 -- not sure how machine readable this needs to be,
361 -- do not at all for now, figure it out later
362 -- could be worth logging all commands in a history table,
363 -- the zpm driver could do that and capture the exit status
365 -- might want the history table to note a "group" to tie together
366 -- sub-invocations, probably an environment variable set if not
367 -- already set by zpm, probably a uuid or a timestamp
368 create table zpmlog (
369 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
370 -- timestamp of action
372 target text, -- packagename, repo name, etc
373 info text -- human readable
377 id integer primary key, -- rowid alias
378 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
380 pkgid text, -- package
381 path text, -- file path involved
382 file text, -- hash of file
383 ack integer default 0
386 create table history (
387 ts integer, -- again, probably needs timestamp sub second
393 create table repository (
394 name text primary key, -- our name for a repo
396 priority integer not null default 1,
397 refreshed integer -- last refresh time
399 -- force the url to be repourl/info.repo
400 -- package urls repourl/pkgid.zpm
402 -- urls for downloading packages. possibly unneeded
403 create table repository_packages (
405 pkg text, -- glob pattern? in which case others not needed
411 -- track which repository a package was cloned from, i.e. where we got it
412 create table packagesource (
416 repository text references repository
419 create view syncconflicts as
421 -- metadata different
423 select path, count(distinct
424 printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
425 count(distinct PFA.hash) as hashcount
426 from packagefiles PFA
428 on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
429 where PA.status in ('installing', 'installed')
431 having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
432 ) > 1 or count(distinct PFA.hash) > 1)
435 printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
436 printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
438 from packagefiles PFH
439 where path in (select path from md_conflict where hashcount > 1)
442 printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
443 printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
445 from packagefiles PFM
446 where path in (select path from md_conflict where mdcount > 1)
449 create view needed_libraries as
451 libs(file,needs,provider) as (
452 select N.file, N.needed as needs, L.file as provider
453 from elfneeded N left join elflibraries L on N.needed = L.soname
455 select L.file, N.needed as needs, EL.file as provider
457 join elfneeded N on N.file = L.provider
458 left join elflibraries EL on N.needed = EL.soname
463 * tables for repository info, essentially materalized views
465 create table repository_libs (
470 create table repository_libsneeded (
476 create view syncinfo as
478 -- paths to libraries we need to keep around
480 select distinct PFL.*
481 from packagefiles PFL
482 join elflibraries EL on EL.file = PFL.hash
483 join elfneeded EN on EN.needed = EL.soname
484 join packagefiles PFN on EN.file = PFN.hash
486 on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
488 PN.status = 'installing' or PN.status = 'installed'
489 and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release)
494 join elflibraries EL on EL.file = PF.hash
495 join elfneeded EN on EN.needed = EL.soname
496 join packagefiles PL on EN.file = PL.hash
498 on PL.path = P.package and PL.version = P.version and PL.release = P.release
500 P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
502 -- every path in 'installing' is either new or update, or no-op
505 when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
507 printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is
508 printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname)
509 and PFI.hash is PFC.hash
513 printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
515 PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
517 PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
519 PFI.target, PFI.device,
521 printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds,
522 printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds
526 on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release
527 left join packages PC
528 on PC.package = PI.package and PC.status in ('installed','removing','updating')
529 left join packagefiles PFC
530 on PFC.package = PC.package
531 and PFC.version = PC.version
532 and PFC.release = PC.release
533 and PFC.path = PFI.path
535 PI.status = 'installing'
537 -- every path in updating is either remove or no-op
540 printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
542 PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
544 PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
546 PFU.target, PFU.device,
548 printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
550 from packagefiles PFU
552 on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release
553 -- inner join because the installing package must exist or this shouldn't
554 -- be an 'updating' package
555 join packages P on PU.package = P.package and P.status = 'installing'
556 left join packagefiles PFI on
557 PFI.package = P.package
558 and PFI.version = P.version
559 and PFI.release = P.release
560 and PFI.path = PFU.path
562 -- handle paths owned by other installed packages
563 left join packages PI on PI.status = 'installed'
564 left join packagefiles PFC
565 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path
568 PU.status in ('updating')
571 and PFU.path not in (select path from preserve)
573 -- every path in removing is either remove or no-op
576 printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
578 PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
580 PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
582 PFR.target, PFR.device,
584 printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
586 from packagefiles PFR
588 on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release
589 left join packages P on PU.package = P.package and P.status = 'installing'
590 left join packagefiles PFI on PFI.path = PFR.path
591 and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release
593 -- handle paths owned by other installed packages
594 left join packages PI on PI.status = 'installed'
595 left join packagefiles PFC
596 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path
599 PU.status in ('removing')
602 and PFR.path not in (select path from preserve)
604 -- paths in 'installed' or 'updated' are no-ops
608 printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
610 PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
612 PFP.configuration as configuration,
613 PFP.target, PFP.device,
615 printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
618 -- TODO don't include installed or installing
619 -- left join packages P on P.package = PFP.package ...
620 -- where P.status not in ('installed','installing')