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