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