]> pd.if.org Git - zpackage/blob - db.sql
a4a065161f3c972dde677be7fd5d4f73c4e2af2f
[zpackage] / db.sql
1 begin;
2
3 PRAGMA application_id = 0x5a504442;
4 PRAGMA user_version = 1;
5
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
9 -- go here
10 CREATE TABLE files (
11         hash text primary key, -- sha256 of content
12         size integer, -- bigint?  certainly need > 2GB
13         compression text, -- always xz?
14         content blob
15 )
16 ;
17
18 create view filerefs as
19 select F.hash,
20 count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount
21 from files F
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
26 group by F.hash
27 ;
28
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
36
37         -- metadata columns
38         description     text,
39         architecture    text,
40         url     text,
41         status  text,
42         licenses        text, -- hash of actual license?  need table for more than one?
43         packager        text,
44         build_time      integer default (strftime('%s', 'now')),
45         install_time    integer,
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'),
51         check (release > 0),
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)
64 )
65 without rowid
66 ;
67
68 create index package_status_index on packages (status);
69 create index package_package_index on packages (package);
70
71 create view packages_pkgid as
72 select printf('%s-%s-%s', package, version, release) as pkgid, *
73 from packages;
74
75 create trigger packages_delete_trigger instead of
76 delete on packages_pkgid
77 begin
78         delete from packages where package = OLD.package
79         and version = OLD.version and release = OLD.release;
80 end;
81
82 create trigger packages_update_trigger instead of
83 update on packages_pkgid
84 begin
85         update packages
86         set package = NEW.package,
87         version = NEW.version,
88         release = NEW.release,
89         description = NEW.description,
90         architecture = NEW.architecture,
91         url = NEW.url,
92         status = NEW.status,
93         licenses = NEW.licenses,
94         packager = NEW.packager,
95         build_time = NEW.build_time,
96         install_time = NEW.install_time,
97         hash = NEW.hash
98         where package = OLD.package
99         and version = OLD.version
100         and release = OLD.release
101         ;
102 end
103 ;
104
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),
110                 NULL); END;
111
112 create table packagetags (
113         -- package id triple
114         package text,
115         version text,
116         release integer,
117         tag     text,
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
121 );
122
123 -- files contained in a package
124 create table packagefiles (
125         -- package id triple
126         package text,
127         version text,
128         release integer,
129
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',
139         -- r regular file
140         -- d directory
141         -- l symlink
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)
167 )
168 without rowid
169 ;
170
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
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
178 from packagefiles
179 ;
180
181 create trigger packagefiles_update_trigger instead of
182 update on packagefiles_pkgid
183 begin
184         update packagefiles
185         set package = NEW.package,
186         version = NEW.version,
187         release = NEW.release,
188         path = NEW.path,
189         mode = NEW.mode,
190         username = NEW.username,
191         groupname = NEW.groupname,
192         uid = NEW.uid,
193         gid = NEW.gid,
194         configuration = NEW.configuration,
195         filetype = NEW.filetype,
196         target = NEW.target,
197         device = NEW.device,
198         hash = NEW.hash,
199         mtime = NEW.mtime
200         where package = OLD.package
201         and version = OLD.version
202         and release = OLD.release
203         and path = OLD.path
204         ;
205 end
206 ;
207
208 create trigger packagefiles_delete_trigger instead of
209 delete on packagefiles_pkgid
210 begin
211         delete from packagefiles
212         where package = OLD.package
213         and version = OLD.version
214         and release = OLD.release
215         and path = OLD.path
216         ;
217         update packages set hash = null
218         where package = OLD.package
219         and version = OLD.version
220         and release = OLD.release
221         ;
222 end
223 ;
224
225 create view installed_ref_count as
226 select I.path, count(*) as refcount
227 from installedfiles I
228 group by I.path
229 ;
230
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
236 ;
237
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
242 ;
243
244 create view installedfiles as
245 select * from packagefiles_status
246 where status = 'installed'
247 ;
248
249 create table pathtags (
250         -- package id triple
251         package text,
252         version text,
253         release integer,
254
255         path    text, -- filesystem path
256         tag     text,
257         primary key (package,version,release,path,tag),
258         foreign key (package,version,release,path)
259         references packagefiles on delete cascade on update cascade
260 )
261 without rowid
262 ;
263
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
271 ;
272
273 -- TODO just elf information?
274 -- and just hash, not package?
275 create table elflibraries (
276         file    text primary key,
277         soname  text
278 )
279 without rowid
280 ;
281 create index elf_library_name_index on elflibraries(soname);
282
283 create table elfneeded (
284         file    text, -- hash of file
285         needed  text, -- soname of dependency
286         primary key (file, needed)
287 )
288 without rowid
289 ;
290
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
294 ;
295
296 create view package_libraries_needed as
297 with pkglibs as (
298                 select distinct EN.needed as soname, PF.pkgid
299                 from elfneeded EN
300                 join packagefiles_pkgid PF on PF.hash = EN.file
301                 ),
302      pkgprovides as (
303                      select distinct EL.soname, PF.pkgid
304                      from elflibraries EL
305                      join packagefiles_pkgid PF on PF.hash = EL.file
306                     )
307      select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
308      from pkglibs PL
309      left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
310 ;
311
312 -- package scripts: table of package, stage, file
313 create table scripts (
314         package text,
315         version text,
316         release integer,
317         stage   text,
318         hash    text,
319         primary key (package,version,release,stage),
320         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
321 );
322
323 create view scripts_pkgid as
324 select printf('%s-%s-%s', package, version, release) as pkgid, *
325 from scripts
326 ;
327
328 -- package dependencies: table of package, dependency, dep type (package, soname)
329 create table packagedeps (
330         package text,
331         version text,
332         release integer,
333         requires text, -- package name (only)
334         minimum text,
335         maximum text,
336         primary key (package,version,release,package),
337         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
338 );
339
340 -- capability labels
341 create table provides (
342         package text,
343         subpackage      text,
344         label   text -- a capability label
345 );
346
347 create table requires (
348         package text,
349         subpackage      text,
350         label   text -- a capability label
351 );
352
353 create table packagegroups (
354         package text,
355         "group" text
356 );
357
358 -- zpm actions
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
363 -- as well
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
370         action  text,
371         target  text, -- packagename, repo name, etc
372         info    text -- human readable
373 );
374
375 create table notes (
376         id      integer primary key, -- rowid alias
377         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
378         note    text not null,
379         pkgid   text, -- package
380         path    text, -- file path involved
381         file    text, -- hash of file
382         ack     integer default 0
383 );
384
385 create table history (
386         ts      integer, -- again, probably needs timestamp sub second
387         cmd     text,
388         args    text,
389         status  integer
390 );
391
392 create table repository (
393         name    text primary key, -- our name for a repo
394         url     text not null,
395         priority        integer not null default 1,
396         refreshed       integer -- last refresh time
397 );
398 -- force the url to be repourl/info.repo
399 -- package urls repourl/pkgid.zpm
400
401 -- urls for downloading packages.  possibly unneeded
402 create table repository_packages (
403         repo    text,
404         pkg     text, -- glob pattern?  in which case others not needed
405         version text,
406         release text,
407         url     text
408 );
409
410 -- track which repository a package was cloned from, i.e. where we got it
411 create table packagesource (
412         name    text,
413         version text,
414         release integer,
415         repository      text references repository
416 );
417
418 create view syncconflicts as
419 with 
420 -- metadata different
421 md_conflict as (
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
426         join packages PA
427         on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
428         where PA.status in ('installing', 'installed')
429         group by path
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)
432 )
433 select PFH.*,
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,
436 'hash' as conflict
437 from packagefiles PFH
438 where path in (select path from md_conflict where hashcount > 1)
439 union
440 select PFM.*,
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,
443 'md' as conflict
444 from packagefiles PFM
445 where path in (select path from md_conflict where mdcount > 1)
446 ;
447
448 create view needed_libraries as
449 with recursive
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
453         union
454         select L.file, N.needed as needs, EL.file as provider
455         from libs L
456         join elfneeded N on N.file = L.provider
457         left join elflibraries EL on N.needed = EL.soname
458 )
459 select * from libs;
460
461 /*
462  * tables for repository info, essentially materalized views
463  */
464 create table repository_libs (
465         pkgid text,
466         soname text
467 );
468
469 create table repository_libsneeded (
470         pkgid text,
471         soname text,
472         selfsat integer
473 );
474
475 create view syncinfo as
476 with
477 -- paths to libraries we need to keep around
478 preserve as (
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
484 join packages PN
485 on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
486 where
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)
489 ),
490 waspreserved as (
491         select PF.path
492         from packagefiles PF
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
496         join packages P
497         on PL.path = P.package and PL.version = P.version and PL.release = P.release
498         where
499         P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
500 )
501 -- every path in 'installing' is either new or update, or no-op
502 select
503 case
504 when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
505 when 
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
509         then 'noop'
510 else 'update'
511 end as op,
512         printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
513         PFI.path, 
514         PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
515         PFI.mtime, PFI.hash,
516         PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
517         as configuration,
518         PFI.target, PFI.device,
519         PFC.hash as ohash,
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
522 from
523 packagefiles PFI
524 join packages PI
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
533 where
534 PI.status = 'installing'
535
536 -- every path in updating is either remove or no-op
537 union
538 select 'remove',
539         printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
540         PFU.path, 
541         PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
542         PFU.mtime, PFU.hash,
543         PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
544         as configuration,
545         PFU.target, PFU.device,
546         null as ohash,
547         printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
548         null as omds
549 from packagefiles PFU
550 join packages PU
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
560
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
565
566 where
567 PU.status in ('updating')
568 and PFI.path is null
569 and PFC.path is null
570 and PFU.path not in (select path from preserve)
571
572 -- every path in removing is either remove or no-op
573 union
574 select 'remove',
575         printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
576         PFR.path, 
577         PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
578         PFR.mtime, PFR.hash,
579         PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
580         as configuration,
581         PFR.target, PFR.device,
582         null as ohash,
583         printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
584         null as omds
585 from packagefiles PFR
586 join packages PU
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
591
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
596
597 where
598 PU.status in ('removing')
599 and PFI.path is null
600 and PFC.path is null
601 and PFR.path not in (select path from preserve)
602
603 -- paths in 'installed' or 'updated' are no-ops
604
605 union
606 select 'preserve',
607         printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
608         PFP.path, 
609         PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
610         PFP.mtime, PFP.hash,
611         PFP.configuration as configuration,
612         PFP.target, PFP.device,
613         null as ohash,
614         printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
615         null as omds
616 from preserve PFP
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')
620 ;
621
622 commit;