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