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 (path not in ('.', '..')),
161 check (not (filetype = 'r' and hash is null)),
162 check (not (filetype = 'c' and device is null)),
163 check (not (filetype = 'b' and device is null)),
164 check (filetype in ('r','d','l','h','c','b','p')),
165 check(length(username) between 1 and 256),
166 check(length(groupname) between 1 and 256),
167 check (configuration = 0 or configuration = 1)
172 create index packagefile_package_index on packagefiles (package);
173 create index packagefile_path_index on packagefiles (path);
174 create index packagefile_hash_index on packagefiles (hash);
175 create index pkgfile_configuration on packagefiles (configuration) where configuration = 1;
177 create view packagefiles_pkgid as
178 select printf('%s-%s-%s', package, version, release) as pkgid, *,
179 printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
183 create trigger packagefiles_update_trigger instead of
184 update on packagefiles_pkgid
187 set package = NEW.package,
188 version = NEW.version,
189 release = NEW.release,
192 username = NEW.username,
193 groupname = NEW.groupname,
196 configuration = NEW.configuration,
197 filetype = NEW.filetype,
202 where package = OLD.package
203 and version = OLD.version
204 and release = OLD.release
210 create trigger packagefiles_delete_trigger instead of
211 delete on packagefiles_pkgid
213 delete from packagefiles
214 where package = OLD.package
215 and version = OLD.version
216 and release = OLD.release
219 update packages set hash = null
220 where package = OLD.package
221 and version = OLD.version
222 and release = OLD.release
227 create view installed_ref_count as
228 select I.path, count(*) as refcount
229 from installedfiles I
233 create view sync_status_ref_count as
234 select path, status, count(*) as refcount
235 from packagefiles_status
236 where status in ('installed', 'installing', 'removing')
237 group by path, status
240 create view packagefiles_status as
241 select P.status, PF.*
242 from packagefiles_pkgid PF
243 left join packages_pkgid P on P.pkgid = PF.pkgid
246 create view installedfiles as
247 select * from packagefiles_status
248 where status = 'installed'
251 create table pathtags (
257 path text, -- filesystem path
259 primary key (package,version,release,path,tag),
260 foreign key (package,version,release,path)
261 references packagefiles on delete cascade on update cascade
266 create view elfdeps as
267 select PF.pkgid, PF.status, PF.path, N.needed as needs,
268 PL.path as library, PL.pkgid provider, PL.status as library_status
269 from packagefiles_status PF
270 join elfneeded N on N.file = PF.hash
271 left join elflibraries L on N.needed = L.soname
272 left join packagefiles_status PL on PL.hash = L.file
275 -- TODO just elf information?
276 -- and just hash, not package?
277 create table elflibraries (
278 file text primary key,
283 create index elf_library_name_index on elflibraries(soname);
285 create table elfneeded (
286 file text, -- hash of file
287 needed text, -- soname of dependency
288 primary key (file, needed)
293 create view package_libraries as
294 select distinct PF.pkgid, EL.soname
295 from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
298 create view package_libraries_needed as
300 select distinct EN.needed as soname, PF.pkgid
302 join packagefiles_pkgid PF on PF.hash = EN.file
305 select distinct EL.soname, PF.pkgid
307 join packagefiles_pkgid PF on PF.hash = EL.file
309 select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
311 left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
314 -- package scripts: table of package, stage, file
315 create table scripts (
321 primary key (package,version,release,stage),
322 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
325 create view scripts_pkgid as
326 select printf('%s-%s-%s', package, version, release) as pkgid, *
330 -- package dependencies: table of package, dependency, dep type (package, soname)
331 create table packagedeps (
335 requires text, -- package name (only)
338 primary key (package,version,release,package),
339 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
343 create table provides (
346 label text -- a capability label
349 create table requires (
352 label text -- a capability label
355 create table packagegroups (
361 -- not sure how machine readable this needs to be,
362 -- do not at all for now, figure it out later
363 -- could be worth logging all commands in a history table,
364 -- the zpm driver could do that and capture the exit status
366 -- might want the history table to note a "group" to tie together
367 -- sub-invocations, probably an environment variable set if not
368 -- already set by zpm, probably a uuid or a timestamp
369 create table zpmlog (
370 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
371 -- timestamp of action
373 target text, -- packagename, repo name, etc
374 info text -- human readable
378 id integer primary key, -- rowid alias
379 ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
381 pkgid text, -- package
382 path text, -- file path involved
383 file text, -- hash of file
384 ack integer default 0
387 create table history (
388 ts integer, -- again, probably needs timestamp sub second
394 create table repository (
395 name text primary key, -- our name for a repo
397 priority integer not null default 1,
398 refreshed integer -- last refresh time
400 -- force the url to be repourl/info.repo
401 -- package urls repourl/pkgid.zpm
403 -- urls for downloading packages. possibly unneeded
404 create table repository_packages (
406 pkg text, -- glob pattern? in which case others not needed
412 -- track which repository a package was cloned from, i.e. where we got it
413 create table packagesource (
417 repository text references repository
420 create view syncconflicts as
422 -- metadata different
424 select path, count(distinct
425 printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)) as mdcount,
426 count(distinct PFA.hash) as hashcount
427 from packagefiles PFA
429 on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
430 where PA.status in ('installing', 'installed')
432 having (count(distinct printf('%s:%s:%s:%s', PFA.filetype, PFA.mode, PFA.username, PFA.groupname)
433 ) > 1 or count(distinct PFA.hash) > 1)
436 printf('%s-%s-%s', PFH.package, PFH.version, PFH.release) as pkgid,
437 printf('%s:%s:%s:%s', PFH.filetype, PFH.mode, PFH.username, PFH.groupname) as mds,
439 from packagefiles PFH
440 where path in (select path from md_conflict where hashcount > 1)
443 printf('%s-%s-%s', PFM.package, PFM.version, PFM.release) as pkgid,
444 printf('%s:%s:%s:%s', PFM.filetype, PFM.mode, PFM.username, PFM.groupname) as mds,
446 from packagefiles PFM
447 where path in (select path from md_conflict where mdcount > 1)
450 create view needed_libraries as
452 libs(file,needs,provider) as (
453 select N.file, N.needed as needs, L.file as provider
454 from elfneeded N left join elflibraries L on N.needed = L.soname
456 select L.file, N.needed as needs, EL.file as provider
458 join elfneeded N on N.file = L.provider
459 left join elflibraries EL on N.needed = EL.soname
464 * tables for repository info, essentially materalized views
466 create table repository_libs (
471 create table repository_libsneeded (
477 create view syncinfo as
479 -- paths to libraries we need to keep around
481 select distinct PFL.*
482 from packagefiles PFL
483 join elflibraries EL on EL.file = PFL.hash
484 join elfneeded EN on EN.needed = EL.soname
485 join packagefiles PFN on EN.file = PFN.hash
487 on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
489 PN.status = 'installing' or PN.status = 'installed'
490 and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release)
495 join elflibraries EL on EL.file = PF.hash
496 join elfneeded EN on EN.needed = EL.soname
497 join packagefiles PL on EN.file = PL.hash
499 on PL.path = P.package and PL.version = P.version and PL.release = P.release
501 P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
503 -- every path in 'installing' is either new or update, or no-op
506 when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
508 printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is
509 printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname)
510 and PFI.hash is PFC.hash
514 printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
516 PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
518 PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
520 PFI.target, PFI.device,
522 printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds,
523 printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds
527 on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release
528 left join packages PC
529 on PC.package = PI.package and PC.status in ('installed','removing','updating')
530 left join packagefiles PFC
531 on PFC.package = PC.package
532 and PFC.version = PC.version
533 and PFC.release = PC.release
534 and PFC.path = PFI.path
536 PI.status = 'installing'
538 -- every path in updating is either remove or no-op
541 printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
543 PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
545 PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
547 PFU.target, PFU.device,
549 printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
551 from packagefiles PFU
553 on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release
554 -- inner join because the installing package must exist or this shouldn't
555 -- be an 'updating' package
556 join packages P on PU.package = P.package and P.status = 'installing'
557 left join packagefiles PFI on
558 PFI.package = P.package
559 and PFI.version = P.version
560 and PFI.release = P.release
561 and PFI.path = PFU.path
563 -- handle paths owned by other installed packages
564 left join packages PI on PI.status = 'installed'
565 left join packagefiles PFC
566 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path
569 PU.status in ('updating')
572 and PFU.path not in (select path from preserve)
574 -- every path in removing is either remove or no-op
577 printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
579 PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
581 PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
583 PFR.target, PFR.device,
585 printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
587 from packagefiles PFR
589 on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release
590 left join packages P on PU.package = P.package and P.status = 'installing'
591 left join packagefiles PFI on PFI.path = PFR.path
592 and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release
594 -- handle paths owned by other installed packages
595 left join packages PI on PI.status = 'installed'
596 left join packagefiles PFC
597 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path
600 PU.status in ('removing')
603 and PFR.path not in (select path from preserve)
605 -- paths in 'installed' or 'updated' are no-ops
609 printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
611 PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
613 PFP.configuration as configuration,
614 PFP.target, PFP.device,
616 printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
619 -- TODO don't include installed or installing
620 -- left join packages P on P.package = PFP.package ...
621 -- where P.status not in ('installed','installing')