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 --      pkgid   text, -- the three above joined with '-'
  26
  27         -- metadata columns
  28         description     text,
  29         architecture    text,
  30         url     text,
  31         status  text,
  32         licenses        text, -- hash of actual license?  need table for more than one?
  33         packager        text,
  34         build_time      integer default (strftime('%s', 'now')),
  35         install_time    integer,
  36         hash    text, -- see integ.c for package hash
  37         primary key (package,version,release),
  38         check (typeof(package) = 'text'),
  39         check (typeof(version) = 'text'),
  40         check (typeof(release) = 'integer'),
  41         check (release > 0)
  42         -- TODO enforce name and version conventions
  43         -- check(instr(version,'-') = 0)
  44         -- check(instr(package,'/') = 0)
  45         -- check(instr(package,'/') = 0)
  46         -- check(instr(version,' ') = 0)
  47         -- check(instr(package,' ') = 0)
  48         -- check(instr(package,' ') = 0)
  49         -- check(length(package) < 64)
  50         -- check(length(version) < 32)
  51 )
  52 without rowid
  53 ;
  54
  55 create index package_status_index on packages (status);
  56 create index package_package_index on packages (package);
  57
  58 create view packages_pkgid as
  59 select printf('%s-%s-%s', package, version, release) as pkgid, *
  60 from packages;
  61
  62 create trigger packages_update_trigger instead of
  63 update on packages_pkgid
  64 begin
  65         update packages
  66         set package = NEW.package,
  67         version = NEW.version,
  68         release = NEW.release,
  69         description = NEW.description,
  70         architecture = NEW.architecture,
  71         url = NEW.url,
  72         status = NEW.status,
  73         licenses = NEW.licenses,
  74         packager = NEW.packager,
  75         build_time = NEW.build_time,
  76         install_time = NEW.install_time,
  77         hash = NEW.hash
  78         where package = OLD.package
  79         and version = OLD.version
  80         and release = OLD.release
  81         ;
  82 end
  83 ;
  84
  85 -- handle package status history with a logging trigger.
  86 create trigger logpkgstatus after update of status on packages
  87 begin insert into zpmlog (action,target,info)
  88         values (printf('status change %s %s', OLD.status, NEW.status),
  89                 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
  90                 NULL); END;
  91
  92 create table packagetags (
  93         -- package id triple
  94         package text,
  95         version text,
  96         release integer,
  97         tag     text,
  98         set_time integer default (strftime('%s', 'now')),
  99         primary key (package,version,release,tag),
 100         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 101 );
 102
 103 -- files contained in a package
 104 create table packagefiles (
 105         -- package id triple
 106         package text,
 107         version text,
 108         release integer,
 109
 110         path    text, -- filesystem path
 111         mode    text not null, -- perms, use text for octal rep?
 112         username        text not null, -- name of owner
 113         groupname       text not null, -- group of owner
 114         uid     integer, -- numeric uid, generally ignored
 115         gid     integer, -- numeric gid, generally ignored
 116         configuration integer not null default 0, -- boolean if config file
 117         filetype varchar not null default 'r',
 118         -- r regular file
 119         -- d directory
 120         -- s symlink
 121         -- h hard link -- not supported
 122         -- c character special and b device special files add dev number column
 123         -- b block special
 124         -- p fifos (i.e. pipe)
 125         target  text, -- link target for links
 126         -- device file dev numbers, should probably be a separate table
 127         devmajor        integer,
 128         devminor        integer,
 129         hash    text, -- null if no actual content, i.e. anything but a regular file
 130         mtime   integer, -- seconds since epoch, finer resolution probably not needed
 131         primary key (package,version,release,path),
 132         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade,
 133         check (not (filetype = 'l' and target is null)),
 134         check (not (filetype = 'r' and hash is null)),
 135         check (not (filetype = 'c' and (devmajor is null or devminor is null))),
 136         check (not (filetype = 'b' and (devmajor is null or devminor is null))),
 137         check (configuration = 0 or configuration = 1)
 138 )
 139 without rowid
 140 ;
 141 create index packagefile_package_index on packagefiles (package);
 142 create index packagefile_path_index on packagefiles (path);
 143 create index packagefile_hash_index on packagefiles (hash);
 144
 145 create view packagefiles_pkgid as
 146 select printf('%s-%s-%s', package, version, release) as pkgid, *,
 147 printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
 148 from packagefiles
 149 ;
 150
 151 create trigger packagefiles_update_trigger instead of
 152 update on packagefiles_pkgid
 153 begin
 154         update packagefiles
 155         set package = NEW.package,
 156         version = NEW.version,
 157         release = NEW.release,
 158         path = NEW.path,
 159         mode = NEW.mode,
 160         username = NEW.username,
 161         groupname = NEW.groupname,
 162         uid = NEW.uid,
 163         gid = NEW.gid,
 164         configuration = NEW.configuration,
 165         filetype = NEW.filetype,
 166         target = NEW.target,
 167         devmajor = NEW.devmajor,
 168         devminor = NEW.devminor,
 169         hash = NEW.hash,
 170         mtime = NEW.mtime
 171         where package = OLD.package
 172         and version = OLD.version
 173         and release = OLD.release
 174         and path = OLD.path
 175         ;
 176 end
 177 ;
 178
 179 create view installed_ref_count as
 180 select I.path, count(*) as refcount
 181 from installedfiles I
 182 group by I.path
 183 ;
 184
 185 create view sync_status_ref_count as
 186 select path, status, count(*) as refcount
 187 from packagefiles_status
 188 where status in ('installed', 'installing', 'removing')
 189 group by path, status
 190 ;
 191
 192 create view packagefiles_status as
 193 select P.status, PF.*
 194 from packagefiles_pkgid PF
 195 left join packages_pkgid P on P.pkgid = PF.pkgid
 196 ;
 197
 198 create view installedfiles as
 199 select * from packagefiles_status
 200 where status = 'installed'
 201 ;
 202
 203 create view install_status as
 204
 205 select 'new' as op, PN.*
 206 from packagefiles_status PN
 207 left join installed_ref_count RC on RC.path = PN.path
 208 where RC.refcount is null
 209 and PN.status = 'installing'
 210
 211 union all
 212
 213 select 'update' as op, PN.*
 214 from packagefiles_status PN
 215 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
 216 left join installed_ref_count RC on RC.path = PN.path
 217 where RC.refcount = 1
 218 and PN.status = 'installing'
 219 and PI.hash is not PN.hash
 220
 221 union all
 222
 223 select 'conflict' as op, PI.*
 224 from packagefiles_status PN
 225 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
 226 where PN.status = 'installing'
 227
 228 union all
 229 select 'remove' as op, PI.*
 230 from installedfiles PI
 231 left join packagefiles_status PN
 232     on PI.path = PN.path and PI.package = PN.package
 233         and PI.pkgid != PN.pkgid
 234 where PN.path is null
 235 and PI.package in (select package from packages where status = 'installing')
 236
 237 union all
 238 -- remove files in removing, but not installing
 239 select distinct 'remove' as op, PR.*
 240 from packagefiles_status PR
 241 left join packagefiles_status PN
 242 on PR.path = PN.path
 243 and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
 244 where PN.path is null
 245 and PR.status = 'removing'
 246 ;
 247
 248 create table pathtags (
 249         -- package id triple
 250         package text,
 251         version text,
 252         release integer,
 253
 254         path    text, -- filesystem path
 255         tag     text,
 256         primary key (package,version,release,path,tag),
 257         foreign key (package,version,release,path)
 258         references packagefiles on delete cascade on update cascade
 259 )
 260 without rowid
 261 ;
 262
 263 create view elfdeps as
 264 select PF.pkgid, PF.status, PF.path, N.needed as needs,
 265 PL.path as library, PL.pkgid provider, PL.status as library_status
 266 from packagefiles_status PF
 267 join elfneeded N on N.file = PF.hash
 268 left join elflibraries L on N.needed = L.soname
 269 left join packagefiles_status PL on PL.hash = L.file
 270 ;
 271
 272 -- TODO just elf information?
 273 -- and just hash, not package?
 274 create table elflibraries (
 275         file    text primary key,
 276         soname  text
 277 )
 278 without rowid
 279 ;
 280
 281 create table elfneeded (
 282         file    text,
 283         needed  text, -- soname of dependency
 284         primary key (file, needed)
 285 )
 286 without rowid
 287 ;
 288
 289 -- package scripts: table of package, stage, file
 290 create table scripts (
 291         package text,
 292         version text,
 293         release integer,
 294         stage   text,
 295         hash    text,
 296         primary key (package,version,release,stage),
 297         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 298 );
 299
 300 create view scripts_pkgid as
 301 select printf('%s-%s-%s', package, version, release) as pkgid, *
 302 from scripts
 303 ;
 304
 305 -- package dependencies: table of package, dependency, dep type (package, soname)
 306 create table packagedeps (
 307         package text,
 308         version text,
 309         release integer,
 310         requires text, -- package name (only)
 311         minimum text,
 312         maximum text,
 313         primary key (package,version,release,package),
 314         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 315 );
 316
 317 -- capability labels
 318 create table provides (
 319         package text,
 320         subpackage      text,
 321         label   text -- a capability label
 322 );
 323
 324 create table requires (
 325         package text,
 326         subpackage      text,
 327         label   text -- a capability label
 328 );
 329
 330 create table packagegroups (
 331         package text,
 332         "group" text
 333 );
 334
 335 -- zpm actions
 336 -- not sure how machine readable this needs to be,
 337 -- do not at all for now, figure it out later
 338 -- could be worth logging all commands in a history table,
 339 -- the zpm driver could do that and capture the exit status
 340 -- as well
 341 -- might want the history table to note a "group" to tie together
 342 -- sub-invocations, probably an environment variable set if not
 343 -- already set by zpm, probably a uuid or a timestamp
 344 create table zpmlog (
 345         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 346         -- timestamp of action
 347         action  text,
 348         target  text, -- packagename, repo name, etc
 349         info    text -- human readable
 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         target,devminor,devmajor
 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,
 469         SS.uid, SS.groupname, SS.gid, SS.mode,
 470         SS.filetype, SS.mtime, SS.hash, SS.target, SS.devminor, SS.devmajor
 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         target,devminor,devmajor
 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         target,devminor,devmajor
 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         target,devminor,devmajor
 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, * from modified
 520 union
 521 select 'remove' as op, * from remove
 522 union
 523 select 'obsolete' as op, * from expired
 524 union
 525 select 'new' as op, * from newfiles
 526 union
 527 select 'preserve' as op, * from preserve
 528 ;
 529
 530 commit;