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         confhash text, -- last hash on disk
 127         filetype varchar not null default 'r',
 128         -- r regular file
 129         -- d directory
 130         -- l symlink
 131         -- h hard link -- not supported
 132         -- c character special -- not supported
 133         -- b block special -- not supported
 134         -- c and b device special files add dev number column
 135         -- p fifos (i.e. pipe) -- not supported
 136         -- s unix domain socket -- not supported
 137         target  text, -- link target for links
 138         device  integer, -- device file dev_t
 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 device is null)),
 151         check (not (filetype = 'b' and device 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         device = NEW.device,
 187         hash = NEW.hash,
 188         mtime = NEW.mtime
 189         where package = OLD.package
 190         and version = OLD.version
 191         and release = OLD.release
 192         and path = OLD.path
 193         ;
 194 end
 195 ;
 196
 197 create view installed_ref_count as
 198 select I.path, count(*) as refcount
 199 from installedfiles I
 200 group by I.path
 201 ;
 202
 203 create view sync_status_ref_count as
 204 select path, status, count(*) as refcount
 205 from packagefiles_status
 206 where status in ('installed', 'installing', 'removing')
 207 group by path, status
 208 ;
 209
 210 create view packagefiles_status as
 211 select P.status, PF.*
 212 from packagefiles_pkgid PF
 213 left join packages_pkgid P on P.pkgid = PF.pkgid
 214 ;
 215
 216 create view installedfiles as
 217 select * from packagefiles_status
 218 where status = 'installed'
 219 ;
 220
 221 create view install_status as
 222
 223 select 'new' as op, PN.*
 224 from packagefiles_status PN
 225 left join installed_ref_count RC on RC.path = PN.path
 226 where RC.refcount is null
 227 and PN.status = 'installing'
 228
 229 union all
 230
 231 select 'update' as op, PN.*
 232 from packagefiles_status PN
 233 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
 234 left join installed_ref_count RC on RC.path = PN.path
 235 where RC.refcount = 1
 236 and PN.status = 'installing'
 237 and PI.hash is not PN.hash
 238
 239 union all
 240
 241 select 'conflict' as op, PI.*
 242 from packagefiles_status PN
 243 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
 244 where PN.status = 'installing'
 245
 246 union all
 247 select 'remove' as op, PI.*
 248 from installedfiles PI
 249 left join packagefiles_status PN
 250     on PI.path = PN.path and PI.package = PN.package
 251         and PI.pkgid != PN.pkgid
 252 where PN.path is null
 253 and PI.package in (select package from packages where status = 'installing')
 254
 255 union all
 256 -- remove files in removing, but not installing
 257 select distinct 'remove' as op, PR.*
 258 from packagefiles_status PR
 259 left join packagefiles_status PN
 260 on PR.path = PN.path
 261 and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
 262 where PN.path is null
 263 and PR.status = 'removing'
 264 ;
 265
 266 create table pathtags (
 267         -- package id triple
 268         package text,
 269         version text,
 270         release integer,
 271
 272         path    text, -- filesystem path
 273         tag     text,
 274         primary key (package,version,release,path,tag),
 275         foreign key (package,version,release,path)
 276         references packagefiles on delete cascade on update cascade
 277 )
 278 without rowid
 279 ;
 280
 281 create view elfdeps as
 282 select PF.pkgid, PF.status, PF.path, N.needed as needs,
 283 PL.path as library, PL.pkgid provider, PL.status as library_status
 284 from packagefiles_status PF
 285 join elfneeded N on N.file = PF.hash
 286 left join elflibraries L on N.needed = L.soname
 287 left join packagefiles_status PL on PL.hash = L.file
 288 ;
 289
 290 -- TODO just elf information?
 291 -- and just hash, not package?
 292 create table elflibraries (
 293         file    text primary key,
 294         soname  text
 295 )
 296 without rowid
 297 ;
 298
 299 create table elfneeded (
 300         file    text,
 301         needed  text, -- soname of dependency
 302         primary key (file, needed)
 303 )
 304 without rowid
 305 ;
 306
 307 -- package scripts: table of package, stage, file
 308 create table scripts (
 309         package text,
 310         version text,
 311         release integer,
 312         stage   text,
 313         hash    text,
 314         primary key (package,version,release,stage),
 315         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 316 );
 317
 318 create view scripts_pkgid as
 319 select printf('%s-%s-%s', package, version, release) as pkgid, *
 320 from scripts
 321 ;
 322
 323 -- package dependencies: table of package, dependency, dep type (package, soname)
 324 create table packagedeps (
 325         package text,
 326         version text,
 327         release integer,
 328         requires text, -- package name (only)
 329         minimum text,
 330         maximum text,
 331         primary key (package,version,release,package),
 332         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 333 );
 334
 335 -- capability labels
 336 create table provides (
 337         package text,
 338         subpackage      text,
 339         label   text -- a capability label
 340 );
 341
 342 create table requires (
 343         package text,
 344         subpackage      text,
 345         label   text -- a capability label
 346 );
 347
 348 create table packagegroups (
 349         package text,
 350         "group" text
 351 );
 352
 353 -- zpm actions
 354 -- not sure how machine readable this needs to be,
 355 -- do not at all for now, figure it out later
 356 -- could be worth logging all commands in a history table,
 357 -- the zpm driver could do that and capture the exit status
 358 -- as well
 359 -- might want the history table to note a "group" to tie together
 360 -- sub-invocations, probably an environment variable set if not
 361 -- already set by zpm, probably a uuid or a timestamp
 362 create table zpmlog (
 363         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 364         -- timestamp of action
 365         action  text,
 366         target  text, -- packagename, repo name, etc
 367         info    text -- human readable
 368 );
 369
 370 create table notes (
 371         id      integer primary key, -- rowid alias
 372         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 373         note    text not null,
 374         pkgid   text, -- package
 375         path    text, -- file path involved
 376         file    text, -- hash of file
 377         ack     integer default 0
 378 );
 379
 380 create table history (
 381         ts      integer, -- again, probably needs timestamp sub second
 382         cmd     text,
 383         args    text,
 384         status  integer
 385 );
 386
 387 create table repository (
 388         name    text primary key, -- our name for a repo
 389         url     text not null,
 390         priority        integer not null default 1,
 391         refreshed       integer -- last refresh time
 392 );
 393
 394 -- urls for downloading packages.  possibly unneeded
 395 create table repository_packages (
 396         repo    text,
 397         pkg     text, -- glob pattern?  in which case others not needed
 398         version text,
 399         release text,
 400         url     text
 401 );
 402
 403 -- track which repository a package was cloned from, i.e. where we got it
 404 create table packagesource (
 405         name    text,
 406         version text,
 407         release integer,
 408         repository      text references repository
 409 );
 410
 411 create view syncconflicts as
 412 with
 413 preserved as (
 414         select BASE.*, 'preserved' as rstatus
 415         from packagefiles_status BASE
 416         join elflibraries EL on EL.file = BASE.hash
 417         where
 418         BASE.status in ('removed', 'updated')
 419         and BASE.hash in (select hash from packagefiles_status where
 420                 status in ('installed'))
 421 ),
 422 syncstatus as (
 423         select distinct BASE.*,
 424         case when P.status = 'installing' and BASE.status = 'installed'
 425                 then 'updating'
 426         when BASE.status in ('removed','updated')
 427                 and BASE.path in (select path from preserved) then
 428                 'preserved'
 429         else
 430                 BASE.status
 431         end as rstatus
 432         from packagefiles_status BASE
 433         left join packages P on P.package = BASE.package
 434                 and BASE.status in ('installed', 'removing')
 435                 and P.status = 'installing'
 436 ),
 437 -- metadata different
 438 md_conflict as (
 439         select path, count(distinct mds) as mdcount,
 440         count(distinct hash) as hashcount
 441         from syncstatus SS
 442         where SS.rstatus in ('installing', 'installed')
 443         group by path
 444         having (count(distinct mds) > 1 or count(distinct hash) > 1)
 445 )
 446 select BASE.*, 'hash' as conflict
 447 from syncstatus BASE
 448 where path in (select path from md_conflict where hashcount > 1)
 449 union
 450 select BASE.*, 'md' as conflict
 451 from syncstatus BASE
 452 where path in (select path from md_conflict where mdcount > 1)
 453 ;
 454
 455 create view syncinfo as
 456 with
 457 preserved as (
 458         select BASE.*, 'preserved' as rstatus
 459         from packagefiles_status BASE
 460         join elflibraries EL on EL.file = BASE.hash
 461         where
 462         BASE.status in ('removed', 'updated')
 463         and BASE.hash in (select hash from packagefiles_status where
 464                 status in ('installed'))
 465 ),
 466 syncstatus as (
 467         select distinct BASE.*,
 468         case when P.status = 'installing' and BASE.status = 'installed'
 469                 then 'updating'
 470         when BASE.status in ('removed','updated')
 471                 and BASE.path in (select path from preserved) then
 472                 'preserved'
 473         else
 474                 BASE.status
 475         end as rstatus
 476         from packagefiles_status BASE
 477         left join packages P on P.package = BASE.package
 478                 and BASE.status in ('installed', 'removing')
 479                 and P.status = 'installing'
 480 ),
 481 -- new file: in installing, not in installed or updating or removing
 482 newfiles as (
 483         select distinct
 484         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 485         configuration,target,device, null as ohash
 486         from syncstatus SS
 487         where path not in (select path from syncstatus where
 488                 rstatus in ('installed', 'updating', 'removing')
 489         )
 490         and rstatus in ('installing')
 491 ),
 492 -- modified: retained, but with different metadata
 493 modified as (
 494         select distinct
 495         SS.path,
 496         SS.username,
 497         SS.uid, SS.groupname, SS.gid, SS.mode,
 498         SS.filetype, SS.mtime, SS.hash,SS.configuration, SS.target, SS.device,
 499         null as ohash
 500         from syncstatus SS
 501         join syncstatus OS
 502         on SS.path = OS.path and SS.pkgid is not OS.pkgid
 503         -- preserved?
 504         and OS.rstatus in ('installed','updating','removing')
 505         and (SS.mds is not OS.mds or SS.hash is not OS.hash)
 506         where
 507         SS.rstatus in ('installing')
 508 ),
 509 -- preserve: libraries needed by something in installed or installing
 510 needed as (
 511         select distinct
 512         ED.library
 513         from elfdeps ED
 514         where status in ('installed', 'installing')
 515         and library is not null
 516 ),
 517 preserve as (
 518         select distinct
 519         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 520         configuration,target,device, null as ohash
 521         from syncstatus SS
 522         where path in (select library from needed)
 523         and SS.rstatus in ('removing', 'removed')
 524 ),
 525 -- remove: cur, not preserved, not in final set
 526 remove 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         where path not in (
 532                 select path from syncstatus where
 533                 rstatus in ('installed', 'installing')
 534         )
 535         and path not in (select path from preserve)
 536         and rstatus in ('removing', 'updating')
 537 ),
 538 -- expired: libraries that had been preserved, but aren't needed now
 539 expired as (
 540         select distinct
 541         path,username,uid,groupname,gid,mode,filetype,mtime,hash,
 542         configuration,target,device, null as ohash
 543         from syncstatus BASE
 544         where hash in (select file from elflibraries where file is not null)
 545         and path not in (select path from preserve)
 546         and rstatus in ('removed','updated')
 547 )
 548 select 'update' as op, * from modified
 549 union
 550 select 'remove' as op, * from remove
 551 union
 552 select 'obsolete' as op, * from expired
 553 union
 554 select 'new' as op, * from newfiles
 555 union
 556 select 'preserve' as op, * from preserve
 557 ;
 558
 559 commit;