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 -- information about packages
  19 -- a package is identified by a package,version,release triple
  20 create table packages (
  21         -- primary key columns
  22         package text not null,
  23         version text not null, -- the upstream version string
  24         release integer not null, -- the local release number
  25
  26         -- metadata columns
  27         description     text,
  28         architecture    text,
  29         url     text,
  30         status  text,
  31         licenses        text, -- hash of actual license?  need table for more than one?
  32         packager        text,
  33         build_time      integer default (strftime('%s', 'now')),
  34         install_time    integer,
  35         hash    text, -- see integ.c for package hash details
  36         primary key (package,version,release),
  37         check (typeof(package) = 'text'),
  38         check (typeof(version) = 'text'),
  39         check (typeof(release) = 'integer'),
  40         check (release > 0),
  41         -- enforce name and version conventions
  42         check(instr(package,' ') = 0),
  43         check(instr(package,'/') = 0),
  44         check(instr(package,':') = 0),
  45         check(instr(version,' ') = 0),
  46         check(instr(version,'-') = 0),
  47         check(instr(version,'/') = 0),
  48         check(instr(version,':') = 0),
  49         check(length(package) < 64),
  50         check(length(package) > 0),
  51         check(length(version) < 32),
  52         check(length(version) > 0)
  53 )
  54 without rowid
  55 ;
  56
  57 create index package_status_index on packages (status);
  58 create index package_package_index on packages (package);
  59
  60 create view packages_pkgid as
  61 select printf('%s-%s-%s', package, version, release) as pkgid, *
  62 from packages;
  63
  64 create trigger packages_update_trigger instead of
  65 update on packages_pkgid
  66 begin
  67         update packages
  68         set package = NEW.package,
  69         version = NEW.version,
  70         release = NEW.release,
  71         description = NEW.description,
  72         architecture = NEW.architecture,
  73         url = NEW.url,
  74         status = NEW.status,
  75         licenses = NEW.licenses,
  76         packager = NEW.packager,
  77         build_time = NEW.build_time,
  78         install_time = NEW.install_time,
  79         hash = NEW.hash
  80         where package = OLD.package
  81         and version = OLD.version
  82         and release = OLD.release
  83         ;
  84 end
  85 ;
  86
  87 -- handle package status history with a logging trigger.
  88 create trigger logpkgstatus after update of status on packages
  89 begin insert into zpmlog (action,target,info)
  90         values (printf('status change %s %s', OLD.status, NEW.status),
  91                 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
  92                 NULL); END;
  93
  94 create table packagetags (
  95         -- package id triple
  96         package text,
  97         version text,
  98         release integer,
  99         tag     text,
 100         set_time integer default (strftime('%s', 'now')),
 101         primary key (package,version,release,tag),
 102         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 103 );
 104
 105 -- files contained in a package
 106 create table packagefiles (
 107         -- package id triple
 108         package text,
 109         version text,
 110         release integer,
 111
 112         path    text, -- filesystem path
 113         mode    text not null, -- perms, use text for octal rep?
 114         username        text not null, -- name of owner
 115         groupname       text not null, -- group of owner
 116         uid     integer, -- numeric uid, generally ignored
 117         gid     integer, -- numeric gid, generally ignored
 118         configuration integer not null default 0, -- boolean if config file
 119         filetype varchar not null default 'r',
 120         -- r regular file
 121         -- d directory
 122         -- s symlink
 123         -- h hard link -- not supported
 124         -- c character special -- not supported
 125         -- b block special -- not supported
 126         -- c and b device special files add dev number column
 127         -- p fifos (i.e. pipe) -- not supported
 128         target  text, -- link target for links
 129         -- device file dev numbers
 130         devmajor        integer,
 131         devminor        integer,
 132         hash    text, -- null if not a regular file
 133         mtime   integer, -- seconds since epoch, finer resolution not needed
 134         primary key (package,version,release,path),
 135         foreign key (package,version,release)
 136                 references packages (package,version,release)
 137                 on delete cascade on update cascade,
 138         check (not (filetype = 'l' and target is null)),
 139         check (not (filetype = 'h' and target is null)),
 140         check (target is null or length(target) between 1 and 4095),
 141         check (hash is null or length(hash) between 1 and 1024),
 142         check (not (filetype = 'r' and hash is null)),
 143         check (not (filetype = 'c' and (devmajor is null or devminor is null))),
 144         check (not (filetype = 'b' and (devmajor is null or devminor is null))),
 145         check (filetype in ('r','d','s','h','c','b','p')),
 146         check(length(username) between 1 and 256),
 147         check(length(groupname) between 1 and 256),
 148         check (configuration = 0 or configuration = 1)
 149 )
 150 without rowid
 151 ;
 152
 153 create index packagefile_package_index on packagefiles (package);
 154 create index packagefile_path_index on packagefiles (path);
 155 create index packagefile_hash_index on packagefiles (hash);
 156
 157 create view packagefiles_pkgid as
 158 select printf('%s-%s-%s', package, version, release) as pkgid, *,
 159 printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
 160 from packagefiles
 161 ;
 162
 163 create trigger packagefiles_update_trigger instead of
 164 update on packagefiles_pkgid
 165 begin
 166         update packagefiles
 167         set package = NEW.package,
 168         version = NEW.version,
 169         release = NEW.release,
 170         path = NEW.path,
 171         mode = NEW.mode,
 172         username = NEW.username,
 173         groupname = NEW.groupname,
 174         uid = NEW.uid,
 175         gid = NEW.gid,
 176         configuration = NEW.configuration,
 177         filetype = NEW.filetype,
 178         target = NEW.target,
 179         devmajor = NEW.devmajor,
 180         devminor = NEW.devminor,
 181         hash = NEW.hash,
 182         mtime = NEW.mtime
 183         where package = OLD.package
 184         and version = OLD.version
 185         and release = OLD.release
 186         and path = OLD.path
 187         ;
 188 end
 189 ;
 190
 191 create view installed_ref_count as
 192 select I.path, count(*) as refcount
 193 from installedfiles I
 194 group by I.path
 195 ;
 196
 197 create view sync_status_ref_count as
 198 select path, status, count(*) as refcount
 199 from packagefiles_status
 200 where status in ('installed', 'installing', 'removing')
 201 group by path, status
 202 ;
 203
 204 create view packagefiles_status as
 205 select P.status, PF.*
 206 from packagefiles_pkgid PF
 207 left join packages_pkgid P on P.pkgid = PF.pkgid
 208 ;
 209
 210 create view installedfiles as
 211 select * from packagefiles_status
 212 where status = 'installed'
 213 ;
 214
 215 create view install_status as
 216
 217 select 'new' as op, PN.*
 218 from packagefiles_status PN
 219 left join installed_ref_count RC on RC.path = PN.path
 220 where RC.refcount is null
 221 and PN.status = 'installing'
 222
 223 union all
 224
 225 select 'update' as op, PN.*
 226 from packagefiles_status PN
 227 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
 228 left join installed_ref_count RC on RC.path = PN.path
 229 where RC.refcount = 1
 230 and PN.status = 'installing'
 231 and PI.hash is not PN.hash
 232
 233 union all
 234
 235 select 'conflict' as op, PI.*
 236 from packagefiles_status PN
 237 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
 238 where PN.status = 'installing'
 239
 240 union all
 241 select 'remove' as op, PI.*
 242 from installedfiles PI
 243 left join packagefiles_status PN
 244     on PI.path = PN.path and PI.package = PN.package
 245         and PI.pkgid != PN.pkgid
 246 where PN.path is null
 247 and PI.package in (select package from packages where status = 'installing')
 248
 249 union all
 250 -- remove files in removing, but not installing
 251 select distinct 'remove' as op, PR.*
 252 from packagefiles_status PR
 253 left join packagefiles_status PN
 254 on PR.path = PN.path
 255 and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
 256 where PN.path is null
 257 and PR.status = 'removing'
 258 ;
 259
 260 create table pathtags (
 261         -- package id triple
 262         package text,
 263         version text,
 264         release integer,
 265
 266         path    text, -- filesystem path
 267         tag     text,
 268         primary key (package,version,release,path,tag),
 269         foreign key (package,version,release,path)
 270         references packagefiles on delete cascade on update cascade
 271 )
 272 without rowid
 273 ;
 274
 275 create view elfdeps as
 276 select PF.pkgid, PF.status, PF.path, N.needed as needs,
 277 PL.path as library, PL.pkgid provider, PL.status as library_status
 278 from packagefiles_status PF
 279 join elfneeded N on N.file = PF.hash
 280 left join elflibraries L on N.needed = L.soname
 281 left join packagefiles_status PL on PL.hash = L.file
 282 ;
 283
 284 -- TODO just elf information?
 285 -- and just hash, not package?
 286 create table elflibraries (
 287         file    text primary key,
 288         soname  text
 289 )
 290 without rowid
 291 ;
 292
 293 create table elfneeded (
 294         file    text,
 295         needed  text, -- soname of dependency
 296         primary key (file, needed)
 297 )
 298 without rowid
 299 ;
 300
 301 -- package scripts: table of package, stage, file
 302 create table scripts (
 303         package text,
 304         version text,
 305         release integer,
 306         stage   text,
 307         hash    text,
 308         primary key (package,version,release,stage),
 309         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 310 );
 311
 312 create view scripts_pkgid as
 313 select printf('%s-%s-%s', package, version, release) as pkgid, *
 314 from scripts
 315 ;
 316
 317 -- package dependencies: table of package, dependency, dep type (package, soname)
 318 create table packagedeps (
 319         package text,
 320         version text,
 321         release integer,
 322         requires text, -- package name (only)
 323         minimum text,
 324         maximum text,
 325         primary key (package,version,release,package),
 326         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 327 );
 328
 329 -- capability labels
 330 create table provides (
 331         package text,
 332         subpackage      text,
 333         label   text -- a capability label
 334 );
 335
 336 create table requires (
 337         package text,
 338         subpackage      text,
 339         label   text -- a capability label
 340 );
 341
 342 create table packagegroups (
 343         package text,
 344         "group" text
 345 );
 346
 347 -- zpm actions
 348 -- not sure how machine readable this needs to be,
 349 -- do not at all for now, figure it out later
 350 -- could be worth logging all commands in a history table,
 351 -- the zpm driver could do that and capture the exit status
 352 -- as well
 353 -- might want the history table to note a "group" to tie together
 354 -- sub-invocations, probably an environment variable set if not
 355 -- already set by zpm, probably a uuid or a timestamp
 356 create table zpmlog (
 357         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 358         -- timestamp of action
 359         action  text,
 360         target  text, -- packagename, repo name, etc
 361         info    text -- human readable
 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
 378 -- urls for downloading packages.  possibly unneeded
 379 create table repository_packages (
 380         repo    text,
 381         pkg     text, -- glob pattern?  in which case others not needed
 382         version text,
 383         release text,
 384         url     text
 385 );
 386
 387 -- track which repository a package was cloned from, i.e. where we got it
 388 create table packagesource (
 389         name    text,
 390         version text,
 391         release integer,
 392         repository      text references repository
 393 );
 394
 395 create view syncconflicts as
 396 with
 397 preserved as (
 398         select BASE.*, 'preserved' as rstatus
 399         from packagefiles_status BASE
 400         join elflibraries EL on EL.file = BASE.hash
 401         where
 402         BASE.status in ('removed', 'updated')
 403         and BASE.hash in (select hash from packagefiles_status where
 404                 status in ('installed'))
 405 ),
 406 syncstatus as (
 407         select distinct BASE.*,
 408         case when P.status = 'installing' and BASE.status = 'installed'
 409                 then 'updating'
 410         when BASE.status in ('removed','updated')
 411                 and BASE.path in (select path from preserved) then
 412                 'preserved'
 413         else
 414                 BASE.status
 415         end as rstatus
 416         from packagefiles_status BASE
 417         left join packages P on P.package = BASE.package
 418                 and BASE.status in ('installed', 'removing')
 419                 and P.status = 'installing'
 420 ),
 421 -- metadata different
 422 md_conflict as (
 423         select path, count(distinct mds) as mdcount,
 424         count(distinct hash) as hashcount
 425         from syncstatus SS
 426         where SS.rstatus in ('installing', 'installed')
 427         group by path
 428         having (count(distinct mds) > 1 or count(distinct hash) > 1)
 429 )
 430 select BASE.*, 'hash' as conflict
 431 from syncstatus BASE
 432 where path in (select path from md_conflict where hashcount > 1)
 433 union
 434 select BASE.*, 'md' as conflict
 435 from syncstatus BASE
 436 where path in (select path from md_conflict where mdcount > 1)
 437 ;
 438
 439 create view syncinfo as
 440 with
 441 preserved as (
 442         select BASE.*, 'preserved' as rstatus
 443         from packagefiles_status BASE
 444         join elflibraries EL on EL.file = BASE.hash
 445         where
 446         BASE.status in ('removed', 'updated')
 447         and BASE.hash in (select hash from packagefiles_status where
 448                 status in ('installed'))
 449 ),
 450 syncstatus as (
 451         select distinct BASE.*,
 452         case when P.status = 'installing' and BASE.status = 'installed'
 453                 then 'updating'
 454         when BASE.status in ('removed','updated')
 455                 and BASE.path in (select path from preserved) then
 456                 'preserved'
 457         else
 458                 BASE.status
 459         end as rstatus
 460         from packagefiles_status BASE
 461         left join packages P on P.package = BASE.package
 462                 and BASE.status in ('installed', 'removing')
 463                 and P.status = 'installing'
 464 ),
 465 -- new file: in installing, not in installed or updating or removing
 466 newfiles as (
 467         select distinct
 468         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 469         target,devminor,devmajor
 470         from syncstatus SS
 471         where path not in (select path from syncstatus where
 472                 rstatus in ('installed', 'updating', 'removing')
 473         )
 474         and rstatus in ('installing')
 475 ),
 476 -- modified: retained, but with different metadata
 477 modified as (
 478         select distinct
 479         SS.path,
 480         SS.username,
 481         SS.uid, SS.groupname, SS.gid, SS.mode,
 482         SS.filetype, SS.mtime, SS.hash, SS.target, SS.devminor, SS.devmajor
 483         from syncstatus SS
 484         join syncstatus OS
 485         on SS.path = OS.path and SS.pkgid is not OS.pkgid
 486         -- preserved?
 487         and OS.rstatus in ('installed','updating','removing')
 488         and (SS.mds is not OS.mds or SS.hash is not OS.hash)
 489         where
 490         SS.rstatus in ('installing')
 491 ),
 492 -- preserve: libraries needed by something in installed or installing
 493 needed as (
 494         select distinct
 495         ED.library
 496         from elfdeps ED
 497         where status in ('installed', 'installing')
 498         and library is not null
 499 ),
 500 preserve as (
 501         select distinct
 502         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 503         target,devminor,devmajor
 504         from syncstatus SS
 505         where path in (select library from needed)
 506         and SS.rstatus in ('removing', 'removed')
 507 ),
 508 -- remove: cur, not preserved, not in final set
 509 remove as (
 510         select distinct
 511         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 512         target,devminor,devmajor
 513         from syncstatus SS
 514         where path not in (
 515                 select path from syncstatus where
 516                 rstatus in ('installed', 'installing')
 517         )
 518         and path not in (select path from preserve)
 519         and rstatus in ('removing', 'updating')
 520 ),
 521 -- expired: libraries that had been preserved, but aren't needed now
 522 expired as (
 523         select distinct
 524         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 525         target,devminor,devmajor
 526         from syncstatus BASE
 527         where hash in (select file from elflibraries where file is not null)
 528         and path not in (select path from preserve)
 529         and rstatus in ('removed','updated')
 530 )
 531 select 'update' as op, * from modified
 532 union
 533 select 'remove' as op, * from remove
 534 union
 535 select 'obsolete' as op, * from expired
 536 union
 537 select 'new' as op, * from newfiles
 538 union
 539 select 'preserve' as op, * from preserve
 540 ;
 541
 542 commit;