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