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 -- package scripts: table of package, stage, file
 292 create table scripts (
 293         package text,
 294         version text,
 295         release integer,
 296         stage   text,
 297         hash    text,
 298         primary key (package,version,release,stage),
 299         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 300 );
 301
 302 create view scripts_pkgid as
 303 select printf('%s-%s-%s', package, version, release) as pkgid, *
 304 from scripts
 305 ;
 306
 307 -- package dependencies: table of package, dependency, dep type (package, soname)
 308 create table packagedeps (
 309         package text,
 310         version text,
 311         release integer,
 312         requires text, -- package name (only)
 313         minimum text,
 314         maximum text,
 315         primary key (package,version,release,package),
 316         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 317 );
 318
 319 -- capability labels
 320 create table provides (
 321         package text,
 322         subpackage      text,
 323         label   text -- a capability label
 324 );
 325
 326 create table requires (
 327         package text,
 328         subpackage      text,
 329         label   text -- a capability label
 330 );
 331
 332 create table packagegroups (
 333         package text,
 334         "group" text
 335 );
 336
 337 -- zpm actions
 338 -- not sure how machine readable this needs to be,
 339 -- do not at all for now, figure it out later
 340 -- could be worth logging all commands in a history table,
 341 -- the zpm driver could do that and capture the exit status
 342 -- as well
 343 -- might want the history table to note a "group" to tie together
 344 -- sub-invocations, probably an environment variable set if not
 345 -- already set by zpm, probably a uuid or a timestamp
 346 create table zpmlog (
 347         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 348         -- timestamp of action
 349         action  text,
 350         target  text, -- packagename, repo name, etc
 351         info    text -- human readable
 352 );
 353
 354 create table notes (
 355         id      integer primary key, -- rowid alias
 356         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 357         note    text not null,
 358         pkgid   text, -- package
 359         path    text, -- file path involved
 360         file    text, -- hash of file
 361         ack     integer default 0
 362 );
 363
 364 create table history (
 365         ts      integer, -- again, probably needs timestamp sub second
 366         cmd     text,
 367         args    text,
 368         status  integer
 369 );
 370
 371 create table repository (
 372         name    text primary key, -- our name for a repo
 373         url     text not null,
 374         priority        integer not null default 1,
 375         refreshed       integer -- last refresh time
 376 );
 377 -- force the url to be repourl/info.repo
 378 -- package urls repourl/pkgid.zpm
 379
 380 -- urls for downloading packages.  possibly unneeded
 381 create table repository_packages (
 382         repo    text,
 383         pkg     text, -- glob pattern?  in which case others not needed
 384         version text,
 385         release text,
 386         url     text
 387 );
 388
 389 -- track which repository a package was cloned from, i.e. where we got it
 390 create table packagesource (
 391         name    text,
 392         version text,
 393         release integer,
 394         repository      text references repository
 395 );
 396
 397 create view syncconflicts as
 398 with
 399 preserved as (
 400         select BASE.*, 'preserved' as rstatus
 401         from packagefiles_status BASE
 402         join elflibraries EL on EL.file = BASE.hash
 403         where
 404         BASE.status in ('removed', 'updated')
 405         and BASE.hash in (select hash from packagefiles_status where
 406                 status in ('installed'))
 407 ),
 408 syncstatus as (
 409         select distinct BASE.*,
 410         case when P.status = 'installing' and BASE.status = 'installed'
 411                 then 'updating'
 412         when BASE.status in ('removed','updated')
 413                 and BASE.path in (select path from preserved) then
 414                 'preserved'
 415         else
 416                 BASE.status
 417         end as rstatus
 418         from packagefiles_status BASE
 419         left join packages P on P.package = BASE.package
 420                 and BASE.status in ('installed', 'removing')
 421                 and P.status = 'installing'
 422 ),
 423 -- metadata different
 424 md_conflict as (
 425         select path, count(distinct mds) as mdcount,
 426         count(distinct hash) as hashcount
 427         from syncstatus SS
 428         where SS.rstatus in ('installing', 'installed')
 429         group by path
 430         having (count(distinct mds) > 1 or count(distinct hash) > 1)
 431 )
 432 select BASE.*, 'hash' as conflict
 433 from syncstatus BASE
 434 where path in (select path from md_conflict where hashcount > 1)
 435 union
 436 select BASE.*, 'md' as conflict
 437 from syncstatus BASE
 438 where path in (select path from md_conflict where mdcount > 1)
 439 ;
 440
 441 create view needed_libraries as
 442 with recursive
 443 libs(file,needs,provider) as (
 444         select N.file, N.needed as needs, L.file as provider
 445         from elfneeded N left join elflibraries L on N.needed = L.soname
 446         union
 447         select L.file, N.needed as needs, EL.file as provider
 448         from libs L
 449         join elfneeded N on N.file = L.provider
 450         left join elflibraries EL on N.needed = EL.soname
 451 )
 452 select * from libs;
 453
 454 create view syncinfo as
 455 with
 456 preserved as (
 457         select BASE.*, 'preserved' as rstatus
 458         from packagefiles_status BASE
 459         join elflibraries EL on EL.file = BASE.hash
 460         where
 461         BASE.status in ('removed', 'updated')
 462         and BASE.hash in (select hash from packagefiles_status where
 463                 status in ('installed'))
 464 ),
 465 syncstatus as (
 466         select distinct BASE.*,
 467         case when P.status = 'installing' and BASE.status = 'installed'
 468                 then 'updating'
 469         when BASE.status in ('removed','updated')
 470                 and BASE.path in (select path from preserved) then
 471                 'preserved'
 472         else
 473                 BASE.status
 474         end as rstatus
 475         from packagefiles_status BASE
 476         left join packages P on P.package = BASE.package
 477                 and BASE.status in ('installed', 'removing')
 478                 and P.status = 'installing'
 479 ),
 480 -- new file: in installing, not in installed or updating or removing
 481 newfiles as (
 482         select distinct
 483         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 484         configuration,target,device, null as ohash
 485         from syncstatus SS
 486         where path not in (select path from syncstatus where
 487                 rstatus in ('installed', 'updating', 'removing')
 488         )
 489         and rstatus in ('installing')
 490 ),
 491 -- modified: retained, but with different metadata
 492 modified as (
 493         select distinct
 494         SS.path,
 495         SS.username, SS.uid, SS.groupname, SS.gid, SS.mode, SS.filetype,
 496         SS.mtime, SS.hash,
 497         SS.configuration + case when OS.configuration = 1 then 2 else 0 end
 498         as configuration,
 499         SS.target, SS.device,
 500         OS.hash as ohash, SS.mds, OS.mds as omds
 501         from syncstatus SS
 502         join syncstatus OS
 503         on SS.path = OS.path and SS.pkgid is not OS.pkgid
 504         -- preserved?
 505         and OS.rstatus in ('installed','updating','removing')
 506         and (SS.mds is not OS.mds or SS.hash is not OS.hash)
 507         where
 508         SS.rstatus in ('installing')
 509 ),
 510 -- preserve: libraries needed by something in installed or installing
 511 /*
 512 needed as (
 513         select distinct
 514         ED.library
 515         from elfdeps ED
 516         where status in ('installed', 'installing')
 517         and library is not null
 518 ),
 519 */
 520 needed as (
 521         select NL.file as needslib, NL.provider as libraryhash
 522         from needed_libraries NL
 523         inner join syncstatus SS on SS.hash = NL.file
 524         and SS.status in ('installed', 'installing')
 525 ),
 526 preserve as (
 527         select distinct
 528         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 529         configuration,target,device, null as ohash
 530         from syncstatus SS
 531         join needed N on SS.hash = N.libraryhash
 532         where SS.rstatus in ('removing', 'removed')
 533 --      where SS.hash in (select libraryhash from needed)
 534 --      where path in (select library from needed)
 535 --      and SS.rstatus in ('removing', 'removed')
 536 ),
 537 -- remove: cur, not preserved, not in final set
 538 remove as (
 539         select distinct
 540         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 541         configuration,target,device, null as ohash
 542         from syncstatus SS
 543         where path not in (
 544                 select path from syncstatus where
 545                 rstatus in ('installed', 'installing')
 546         )
 547         and path not in (select path from preserve)
 548         and rstatus in ('removing', 'updating')
 549 ),
 550 -- expired: libraries that had been preserved, but aren't needed now
 551 expired as (
 552         select distinct
 553         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 554         configuration,target,device, null as ohash
 555         from syncstatus BASE
 556         where hash in (select file from elflibraries where file is not null)
 557         and path not in (select path from preserve)
 558         and rstatus in ('removed','updated')
 559 )
 560 select 'update' as op, *
 561 ,(select group_concat(pkgid, ' ')
 562         from syncstatus SS
 563         where SS.rstatus = 'installing' and SS.path = M.path
 564 ) as pkglist
 565 from modified M
 566 union
 567 select 'remove' as op, *, null, null, null from remove
 568 union
 569 select 'obsolete' as op, *, null, null, null from expired
 570 union
 571 select 'new' as op, *, null, null, null from newfiles
 572 union
 573 select 'preserve' as op, *, null, null, null from preserve
 574 ;
 575
 576 commit;