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