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 create index pkgfile_configuration on packagefiles (configuration) where configuration = 1;
 175
 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
 179 from packagefiles
 180 ;
 181
 182 create trigger packagefiles_update_trigger instead of
 183 update on packagefiles_pkgid
 184 begin
 185         update packagefiles
 186         set package = NEW.package,
 187         version = NEW.version,
 188         release = NEW.release,
 189         path = NEW.path,
 190         mode = NEW.mode,
 191         username = NEW.username,
 192         groupname = NEW.groupname,
 193         uid = NEW.uid,
 194         gid = NEW.gid,
 195         configuration = NEW.configuration,
 196         filetype = NEW.filetype,
 197         target = NEW.target,
 198         device = NEW.device,
 199         hash = NEW.hash,
 200         mtime = NEW.mtime
 201         where package = OLD.package
 202         and version = OLD.version
 203         and release = OLD.release
 204         and path = OLD.path
 205         ;
 206 end
 207 ;
 208
 209 create trigger packagefiles_delete_trigger instead of
 210 delete on packagefiles_pkgid
 211 begin
 212         delete from packagefiles
 213         where package = OLD.package
 214         and version = OLD.version
 215         and release = OLD.release
 216         and path = OLD.path
 217         ;
 218         update packages set hash = null
 219         where package = OLD.package
 220         and version = OLD.version
 221         and release = OLD.release
 222         ;
 223 end
 224 ;
 225
 226 create view installed_ref_count as
 227 select I.path, count(*) as refcount
 228 from installedfiles I
 229 group by I.path
 230 ;
 231
 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
 237 ;
 238
 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
 243 ;
 244
 245 create view installedfiles as
 246 select * from packagefiles_status
 247 where status = 'installed'
 248 ;
 249
 250 create table pathtags (
 251         -- package id triple
 252         package text,
 253         version text,
 254         release integer,
 255
 256         path    text, -- filesystem path
 257         tag     text,
 258         primary key (package,version,release,path,tag),
 259         foreign key (package,version,release,path)
 260         references packagefiles on delete cascade on update cascade
 261 )
 262 without rowid
 263 ;
 264
 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
 272 ;
 273
 274 -- TODO just elf information?
 275 -- and just hash, not package?
 276 create table elflibraries (
 277         file    text primary key,
 278         soname  text
 279 )
 280 without rowid
 281 ;
 282 create index elf_library_name_index on elflibraries(soname);
 283
 284 create table elfneeded (
 285         file    text, -- hash of file
 286         needed  text, -- soname of dependency
 287         primary key (file, needed)
 288 )
 289 without rowid
 290 ;
 291
 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
 295 ;
 296
 297 create view package_libraries_needed as
 298 with pkglibs as (
 299                 select distinct EN.needed as soname, PF.pkgid
 300                 from elfneeded EN
 301                 join packagefiles_pkgid PF on PF.hash = EN.file
 302                 ),
 303      pkgprovides as (
 304                      select distinct EL.soname, PF.pkgid
 305                      from elflibraries EL
 306                      join packagefiles_pkgid PF on PF.hash = EL.file
 307                     )
 308      select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
 309      from pkglibs PL
 310      left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
 311 ;
 312
 313 -- package scripts: table of package, stage, file
 314 create table scripts (
 315         package text,
 316         version text,
 317         release integer,
 318         stage   text,
 319         hash    text,
 320         primary key (package,version,release,stage),
 321         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 322 );
 323
 324 create view scripts_pkgid as
 325 select printf('%s-%s-%s', package, version, release) as pkgid, *
 326 from scripts
 327 ;
 328
 329 -- package dependencies: table of package, dependency, dep type (package, soname)
 330 create table packagedeps (
 331         package text,
 332         version text,
 333         release integer,
 334         requires text, -- package name (only)
 335         minimum text,
 336         maximum text,
 337         primary key (package,version,release,package),
 338         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 339 );
 340
 341 -- capability labels
 342 create table provides (
 343         package text,
 344         subpackage      text,
 345         label   text -- a capability label
 346 );
 347
 348 create table requires (
 349         package text,
 350         subpackage      text,
 351         label   text -- a capability label
 352 );
 353
 354 create table packagegroups (
 355         package text,
 356         "group" text
 357 );
 358
 359 -- zpm actions
 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
 364 -- as well
 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
 371         action  text,
 372         target  text, -- packagename, repo name, etc
 373         info    text -- human readable
 374 );
 375
 376 create table notes (
 377         id      integer primary key, -- rowid alias
 378         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 379         note    text not null,
 380         pkgid   text, -- package
 381         path    text, -- file path involved
 382         file    text, -- hash of file
 383         ack     integer default 0
 384 );
 385
 386 create table history (
 387         ts      integer, -- again, probably needs timestamp sub second
 388         cmd     text,
 389         args    text,
 390         status  integer
 391 );
 392
 393 create table repository (
 394         name    text primary key, -- our name for a repo
 395         url     text not null,
 396         priority        integer not null default 1,
 397         refreshed       integer -- last refresh time
 398 );
 399 -- force the url to be repourl/info.repo
 400 -- package urls repourl/pkgid.zpm
 401
 402 -- urls for downloading packages.  possibly unneeded
 403 create table repository_packages (
 404         repo    text,
 405         pkg     text, -- glob pattern?  in which case others not needed
 406         version text,
 407         release text,
 408         url     text
 409 );
 410
 411 -- track which repository a package was cloned from, i.e. where we got it
 412 create table packagesource (
 413         name    text,
 414         version text,
 415         release integer,
 416         repository      text references repository
 417 );
 418
 419 create view syncconflicts as
 420 with
 421 -- metadata different
 422 md_conflict as (
 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
 427         join packages PA
 428         on PA.package = PFA.package and PA.version = PFA.version and PA.release = PFA.release
 429         where PA.status in ('installing', 'installed')
 430         group by path
 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)
 433 )
 434 select PFH.*,
 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,
 437 'hash' as conflict
 438 from packagefiles PFH
 439 where path in (select path from md_conflict where hashcount > 1)
 440 union
 441 select PFM.*,
 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,
 444 'md' as conflict
 445 from packagefiles PFM
 446 where path in (select path from md_conflict where mdcount > 1)
 447 ;
 448
 449 create view needed_libraries as
 450 with recursive
 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
 454         union
 455         select L.file, N.needed as needs, EL.file as provider
 456         from libs L
 457         join elfneeded N on N.file = L.provider
 458         left join elflibraries EL on N.needed = EL.soname
 459 )
 460 select * from libs;
 461
 462 /*
 463  * tables for repository info, essentially materalized views
 464  */
 465 create table repository_libs (
 466         pkgid text,
 467         soname text
 468 );
 469
 470 create table repository_libsneeded (
 471         pkgid text,
 472         soname text,
 473         selfsat integer
 474 );
 475
 476 create view syncinfo as
 477 with
 478 -- paths to libraries we need to keep around
 479 preserve as (
 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
 485 join packages PN
 486 on PFN.package = PN.package and PFN.version = PN.version and PFN.release = PN.release
 487 where
 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)
 490 ),
 491 waspreserved as (
 492         select PF.path
 493         from packagefiles PF
 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
 497         join packages P
 498         on PL.path = P.package and PL.version = P.version and PL.release = P.release
 499         where
 500         P.status = 'removing' or P.status = 'installed' or P.status = 'updating'
 501 )
 502 -- every path in 'installing' is either new or update, or no-op
 503 select
 504 case
 505 when PFC.path is null and PFC.path not in (select path from waspreserved) then 'new'
 506 when
 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
 510         then 'noop'
 511 else 'update'
 512 end as op,
 513         printf('%s-%s-%s', PFI.package, PFI.version, PFI.release) as pkgid,
 514         PFI.path,
 515         PFI.username, PFI.uid, PFI.groupname, PFI.gid, PFI.mode, PFI.filetype,
 516         PFI.mtime, PFI.hash,
 517         PFI.configuration + case when PFC.configuration = 1 then 2 else 0 end
 518         as configuration,
 519         PFI.target, PFI.device,
 520         PFC.hash as ohash,
 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
 523 from
 524 packagefiles PFI
 525 join packages PI
 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
 534 where
 535 PI.status = 'installing'
 536
 537 -- every path in updating is either remove or no-op
 538 union
 539 select 'remove',
 540         printf('%s-%s-%s', PFU.package, PFU.version, PFU.release) as pkgid,
 541         PFU.path,
 542         PFU.username, PFU.uid, PFU.groupname, PFU.gid, PFU.mode, PFU.filetype,
 543         PFU.mtime, PFU.hash,
 544         PFU.configuration + case when PFC.configuration = 1 then 2 else 0 end
 545         as configuration,
 546         PFU.target, PFU.device,
 547         null as ohash,
 548         printf('%s:%s:%s:%s', PFU.filetype, PFU.mode, PFU.username, PFU.groupname) as mds,
 549         null as omds
 550 from packagefiles PFU
 551 join packages PU
 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
 561
 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
 566
 567 where
 568 PU.status in ('updating')
 569 and PFI.path is null
 570 and PFC.path is null
 571 and PFU.path not in (select path from preserve)
 572
 573 -- every path in removing is either remove or no-op
 574 union
 575 select 'remove',
 576         printf('%s-%s-%s', PFR.package, PFR.version, PFR.release) as pkgid,
 577         PFR.path,
 578         PFR.username, PFR.uid, PFR.groupname, PFR.gid, PFR.mode, PFR.filetype,
 579         PFR.mtime, PFR.hash,
 580         PFR.configuration + case when PFC.configuration = 1 then 2 else 0 end
 581         as configuration,
 582         PFR.target, PFR.device,
 583         null as ohash,
 584         printf('%s:%s:%s:%s', PFR.filetype, PFR.mode, PFR.username, PFR.groupname) as mds,
 585         null as omds
 586 from packagefiles PFR
 587 join packages PU
 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
 592
 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
 597
 598 where
 599 PU.status in ('removing')
 600 and PFI.path is null
 601 and PFC.path is null
 602 and PFR.path not in (select path from preserve)
 603
 604 -- paths in 'installed' or 'updated' are no-ops
 605
 606 union
 607 select 'preserve',
 608         printf('%s-%s-%s', PFP.package, PFP.version, PFP.release) as pkgid,
 609         PFP.path,
 610         PFP.username, PFP.uid, PFP.groupname, PFP.gid, PFP.mode, PFP.filetype,
 611         PFP.mtime, PFP.hash,
 612         PFP.configuration as configuration,
 613         PFP.target, PFP.device,
 614         null as ohash,
 615         printf('%s:%s:%s:%s', PFP.filetype, PFP.mode, PFP.username, PFP.groupname) as mds,
 616         null as omds
 617 from preserve PFP
 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')
 621 ;
 622
 623 commit;