pd.if.org Git - zpackage/blob - 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 preserved as (
 421         select BASE.*, 'preserved' as rstatus
 422         from packagefiles_status BASE
 423         join elflibraries EL on EL.file = BASE.hash
 424         where
 425         BASE.status in ('removed', 'updated')
 426         and BASE.hash in (select hash from packagefiles_status where
 427                 status in ('installed'))
 428 ),
 429 syncstatus as (
 430         select distinct BASE.*,
 431         case when P.status = 'installing' and BASE.status = 'installed'
 432                 then 'updating'
 433         when BASE.status in ('removed','updated')
 434                 and BASE.path in (select path from preserved) then
 435                 'preserved'
 436         else
 437                 BASE.status
 438         end as rstatus
 439         from packagefiles_status BASE
 440         left join packages P on P.package = BASE.package
 441                 and BASE.status in ('installed', 'removing')
 442                 and P.status = 'installing'
 443 ),
 444 -- metadata different
 445 md_conflict as (
 446         select path, count(distinct mds) as mdcount,
 447         count(distinct hash) as hashcount
 448         from syncstatus SS
 449         where SS.rstatus in ('installing', 'installed')
 450         group by path
 451         having (count(distinct mds) > 1 or count(distinct hash) > 1)
 452 )
 453 select BASE.*, 'hash' as conflict
 454 from syncstatus BASE
 455 where path in (select path from md_conflict where hashcount > 1)
 456 union
 457 select BASE.*, 'md' as conflict
 458 from syncstatus BASE
 459 where path in (select path from md_conflict where mdcount > 1)
 460 ;
 461
 462 create view needed_libraries as
 463 with recursive
 464 libs(file,needs,provider) as (
 465         select N.file, N.needed as needs, L.file as provider
 466         from elfneeded N left join elflibraries L on N.needed = L.soname
 467         union
 468         select L.file, N.needed as needs, EL.file as provider
 469         from libs L
 470         join elfneeded N on N.file = L.provider
 471         left join elflibraries EL on N.needed = EL.soname
 472 )
 473 select * from libs;
 474
 475 /*
 476  * tables for repository info, essentially materalized views
 477  */
 478 create table repository_libs (
 479         pkgid text,
 480         soname text
 481 );
 482
 483 create table repository_libsneeded (
 484         pkgid text,
 485         soname text,
 486         selfsat integer
 487 );
 488
 489 create view syncinfo as
 490 with
 491 -- paths to libraries we need to keep around
 492 preserve as (
 493 select distinct PFL.*
 494 from packagefiles PFL
 495 join elflibraries EL on EL.file = PFL.hash
 496 join elfneeded EN on EN.needed = EL.soname
 497 join packagefiles PFN on EN.file = PFN.hash
 498 join packages PN
 499 on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
 500 where
 501 PN.status = 'installing' or PN.status = 'installed'
 502 and not (PFN.package = PFL.package and PFN.version = PFN.version and PFN.release = PFL.release)
 503 ),
 504 waspreserved as (
 505         select PF.path
 506         from packagefiles PF
 507         join elflibraries EL on EL.file = PF.hash
 508         join elfneeded EN on EN.needed = EL.soname
 509         join packagefiles PL on EN.file = PL.hash
 510         join packages P
 511         on PL.path = P.package and PL.version = P.version and PL.release = P.release
 512         where
 513         P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
 514 )
 515 -- every path in 'installing' is either new or update, or no-op
 516 select
 517 case
 518 when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
 519 when
 520         printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) is
 521         printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname)
 522         and PFI.hash is PFC.hash
 523         then 'noop'
 524 else 'update'
 525 end as op,
 526         printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
 527         PFI.path,
 528         PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
 529         PFI.mtime, PFI.hash,
 530         PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
 531         as configuration,
 532         PFI.target, PFI.device,
 533         PFC.hash as ohash,
 534         printf('%s:%s:%s:%s', PFI.filetype, PFI.mode, PFI.username, PFI.groupname) as mds,
 535         printf('%s:%s:%s:%s', PFC.filetype, PFC.mode, PFC.username, PFC.groupname) as omds
 536 from
 537 packagefiles PFI
 538 join packages PI
 539 on PFI.package = PI.package and PFI.version = PI.version and PFI.release = PI.release
 540 left join packages PC
 541 on PC.package = PI.package and PC.status in ('installed','removing','updating')
 542 left join packagefiles PFC
 543         on PFC.package = PC.package
 544         and PFC.version = PC.version
 545         and PFC.release = PC.release
 546         and PFC.path = PFI.path
 547 where
 548 PI.status = 'installing'
 549
 550 -- every path in updating is either remove or no-op
 551 union
 552 select 'remove',
 553         printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
 554         PFU.path,
 555         PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
 556         PFU.mtime, PFU.hash,
 557         PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
 558         as configuration,
 559         PFU.target, PFU.device,
 560         null as ohash,
 561         printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
 562         null as omds
 563 from packagefiles PFU
 564 join packages PU
 565 on PFU.package = PU.package and PFU.version = PU.version and PFU.release = PU.release
 566 -- inner join because the installing package must exist or this shouldn't
 567 -- be an 'updating' package
 568 join packages P on PU.package = P.package and P.status = 'installing'
 569 left join packagefiles PFI on
 570         PFI.package = P.package
 571         and PFI.version = P.version
 572         and PFI.release = P.release
 573         and PFI.path = PFU.path
 574
 575 -- handle paths owned by other installed packages
 576 left join packages PI on PI.status = 'installed'
 577 left join packagefiles PFC
 578 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFU.path
 579
 580 where
 581 PU.status in ('updating')
 582 and PFI.path is null
 583 and PFC.path is null
 584 and PFU.path not in (select path from preserve)
 585
 586 -- every path in removing is either remove or no-op
 587 union
 588 select 'remove',
 589         printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
 590         PFR.path,
 591         PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
 592         PFR.mtime, PFR.hash,
 593         PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
 594         as configuration,
 595         PFR.target, PFR.device,
 596         null as ohash,
 597         printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
 598         null as omds
 599 from packagefiles PFR
 600 join packages PU
 601 on PFR.package = PU.package and PFR.version = PU.version and PFR.release = PU.release
 602 left join packages P on PU.package = P.package and P.status = 'installing'
 603 left join packagefiles PFI on PFI.path = PFR.path
 604 and PFI.package = P.package and PFI.version = P.version and PFI.release = P.release
 605
 606 -- handle paths owned by other installed packages
 607 left join packages PI on PI.status = 'installed'
 608 left join packagefiles PFC
 609 on PFC.package = PI.package and PFC.version = PI.version and PFC.release = PI.release and PFC.path = PFR.path
 610
 611 where
 612 PU.status in ('removing')
 613 and PFI.path is null
 614 and PFC.path is null
 615 and PFR.path not in (select path from preserve)
 616
 617 -- paths in 'installed' or 'updated' are no-ops
 618
 619 union
 620 select 'preserve',
 621         printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
 622         PFP.path,
 623         PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
 624         PFP.mtime, PFP.hash,
 625         PFP.configuration as configuration,
 626         PFP.target, PFP.device,
 627         null as ohash,
 628         printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
 629         null as omds
 630 from preserve PFP
 631 ;
 632
 633 commit;