pd.if.org Git - zpackage/blob - schema/main.sql

   1 PRAGMA application_id = 0x5a504442;
   2 PRAGMA user_version = 1;
   3
   4 -- should be faster with rowid due to the blob content
   5 -- these are really just blobs of data
   6 -- TODO copyright and license information should probably
   7 -- go here
   8 CREATE TABLE files (
   9         hash text primary key, -- sha256 of (uncompressed) content
  10         size integer, -- bigint?  certainly need > 2GB
  11         compression text, -- always xz?
  12         content blob
  13 )
  14 ;
  15
  16 create view filerefs as
  17 select F.hash,
  18 count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) + count(EN.file) as refcount,
  19 count(PF.hash) as pfrefs,
  20 count(S.hash) as scriptrefs,
  21 count(EL.file) as librefs,
  22 count(EN.file) as needrefs,
  23 count(N.file) as noterefs
  24 from files F
  25 left join packagefiles PF on PF.hash = F.hash
  26 left join scripts S on S.hash = F.hash
  27 left join elflibraries EL on EL.file = F.hash
  28 left join elfneeded EN on EN.file = F.hash
  29 left join notes N on N.file = F.hash
  30 group by F.hash
  31 ;
  32
  33 -- information about packages
  34 -- a package is identified by a package,version,release triple
  35 create table packages (
  36         -- primary key columns
  37         package text not null,
  38         version text not null, -- the upstream version string
  39         release integer not null, -- the local release number
  40
  41         -- metadata columns
  42         description     text,
  43         architecture    text,
  44         url     text,
  45         status  text,
  46         licenses        text, -- hash of actual license?  need table for more than one?
  47         packager        text,
  48         build_time      integer default (strftime('%s', 'now')),
  49         install_time    integer,
  50         hash    text, -- see integ.c for package hash details
  51         primary key (package,version,release),
  52         check (typeof(package) = 'text'),
  53         check (typeof(version) = 'text'),
  54         check (typeof(release) = 'integer'),
  55         check (release > 0),
  56         -- enforce name and version conventions
  57         check(instr(package,' ') = 0),
  58         check(instr(package,'/') = 0),
  59         check(instr(package,':') = 0),
  60         check(instr(version,' ') = 0),
  61         check(instr(version,'-') = 0),
  62         check(instr(version,'/') = 0),
  63         check(instr(version,':') = 0),
  64         check(length(package) < 64),
  65         check(length(package) > 0),
  66         check(length(version) < 32),
  67         check(length(version) > 0)
  68 )
  69 without rowid
  70 ;
  71
  72 create index package_status_index on packages (status);
  73 create index package_package_index on packages (package);
  74
  75 create view packages_pkgid as
  76 select printf('%s-%s-%s', package, version, release) as pkgid, *
  77 from packages;
  78
  79 create trigger packages_delete_trigger instead of
  80 delete on packages_pkgid
  81 begin
  82         delete from packages where package = OLD.package
  83         and version = OLD.version and release = OLD.release;
  84 end;
  85
  86 create trigger packages_update_trigger instead of
  87 update on packages_pkgid
  88 begin
  89         update packages
  90         set package = NEW.package,
  91         version = NEW.version,
  92         release = NEW.release,
  93         description = NEW.description,
  94         architecture = NEW.architecture,
  95         url = NEW.url,
  96         status = NEW.status,
  97         licenses = NEW.licenses,
  98         packager = NEW.packager,
  99         build_time = NEW.build_time,
 100         install_time = NEW.install_time,
 101         hash = NEW.hash
 102         where package = OLD.package
 103         and version = OLD.version
 104         and release = OLD.release
 105         ;
 106 end
 107 ;
 108
 109 -- handle package status history with a logging trigger.
 110 create trigger logpkgstatus after update of status on packages
 111 begin insert into zpmlog (action,target,info)
 112         values (printf('status change %s %s', OLD.status, NEW.status),
 113                 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
 114                 NULL); END;
 115
 116 create table packagetags (
 117         -- package id triple
 118         package text,
 119         version text,
 120         release integer,
 121         tag     text,
 122         set_time integer default (strftime('%s', 'now')),
 123         primary key (package,version,release,tag),
 124         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 125 );
 126
 127 -- files contained in a package
 128 create table packagefiles (
 129         -- package id triple
 130         package text,
 131         version text,
 132         release integer,
 133
 134         path    text, -- filesystem path
 135         mode    text not null default '0644', -- perms, use text for octal rep?
 136         username        text not null default 'root', -- name of owner
 137         groupname       text not null default 'root', -- group of owner
 138         uid     integer, -- numeric uid, generally ignored
 139         gid     integer, -- numeric gid, generally ignored
 140         configuration integer not null default 0, -- boolean if config file
 141         confhash text, -- last hash on disk
 142         filetype varchar not null default 'r',
 143         -- r regular file
 144         -- d directory
 145         -- l symlink
 146         -- h hard link -- not supported
 147         -- c character special -- not supported
 148         -- b block special -- not supported
 149         -- c and b device special files add dev number column
 150         -- p fifos (i.e. pipe) -- not supported
 151         -- s unix domain socket -- not supported
 152         target  text, -- link target for links
 153         device  integer, -- device file dev_t
 154         hash    text, -- null if not a regular file
 155         mtime   integer, -- seconds since epoch, finer resolution not needed
 156         primary key (package,version,release,path),
 157         foreign key (package,version,release)
 158                 references packages (package,version,release)
 159                 on delete cascade on update cascade,
 160         check (not (filetype = 'l' and target is null)),
 161         check (not (filetype = 'h' and target is null)),
 162         check (target is null or length(target) between 1 and 4095),
 163         check (hash is null or length(hash) between 1 and 1024),
 164         check (path not in ('.', '..')),
 165         check (not (filetype = 'r' and hash is null)),
 166         check (not (filetype = 'c' and device is null)),
 167         check (not (filetype = 'b' and device is null)),
 168         check (filetype in ('r','d','l','h','c','b','p')),
 169         check(length(username) between 1 and 256),
 170         check(length(groupname) between 1 and 256),
 171         check (configuration = 0 or configuration = 1)
 172 )
 173 without rowid
 174 ;
 175
 176 create index packagefile_package_index on packagefiles (package);
 177 create index packagefile_path_index on packagefiles (path);
 178 create index packagefile_hash_index on packagefiles (hash);
 179 create index pkgfile_configuration on packagefiles (configuration) where configuration = 1;
 180
 181 create view packagefiles_pkgid as
 182 select printf('%s-%s-%s', package, version, release) as pkgid, *,
 183 printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds
 184 from packagefiles
 185 ;
 186
 187 create trigger packagefiles_update_trigger instead of
 188 update on packagefiles_pkgid
 189 begin
 190         update packagefiles
 191         set package = NEW.package,
 192         version = NEW.version,
 193         release = NEW.release,
 194         path = NEW.path,
 195         mode = NEW.mode,
 196         username = NEW.username,
 197         groupname = NEW.groupname,
 198         uid = NEW.uid,
 199         gid = NEW.gid,
 200         configuration = NEW.configuration,
 201         filetype = NEW.filetype,
 202         target = NEW.target,
 203         device = NEW.device,
 204         hash = NEW.hash,
 205         mtime = NEW.mtime
 206         where package = OLD.package
 207         and version = OLD.version
 208         and release = OLD.release
 209         and path = OLD.path
 210         ;
 211 end
 212 ;
 213
 214 create trigger packagefiles_delete_trigger instead of
 215 delete on packagefiles_pkgid
 216 begin
 217         delete from packagefiles
 218         where package = OLD.package
 219         and version = OLD.version
 220         and release = OLD.release
 221         and path = OLD.path
 222         ;
 223         update packages set hash = null
 224         where package = OLD.package
 225         and version = OLD.version
 226         and release = OLD.release
 227         ;
 228 end
 229 ;
 230
 231 create view installed_ref_count as
 232 select I.path, count(*) as refcount
 233 from installedfiles I
 234 group by I.path
 235 ;
 236
 237 create view sync_status_ref_count as
 238 select path, status, count(*) as refcount
 239 from packagefiles_status
 240 where status in ('installed', 'installing', 'removing')
 241 group by path, status
 242 ;
 243
 244 create view packagefiles_status as
 245 select P.status, PF.*
 246 from packagefiles_pkgid PF
 247 left join packages_pkgid P on P.pkgid = PF.pkgid
 248 ;
 249
 250 create view installedfiles as
 251 select * from packagefiles_status
 252 where status = 'installed'
 253 ;
 254
 255 create table pathtags (
 256         -- package id triple
 257         package text,
 258         version text,
 259         release integer,
 260
 261         path    text, -- filesystem path
 262         tag     text,
 263         primary key (package,version,release,path,tag),
 264         foreign key (package,version,release,path)
 265         references packagefiles on delete cascade on update cascade
 266 )
 267 without rowid
 268 ;
 269
 270 create view elfdeps as
 271 select PF.pkgid, PF.status, PF.path, N.needed as needs,
 272 PL.path as library, PL.pkgid provider, PL.status as library_status
 273 from packagefiles_status PF
 274 join elfneeded N on N.file = PF.hash
 275 left join elflibraries L on N.needed = L.soname
 276 left join packagefiles_status PL on PL.hash = L.file
 277 ;
 278
 279 -- TODO just elf information?
 280 -- and just hash, not package?
 281 create table elflibraries (
 282         file    text primary key,
 283         soname  text
 284 )
 285 without rowid
 286 ;
 287 create index elf_library_name_index on elflibraries(soname);
 288
 289 create table elfneeded (
 290         file    text, -- hash of file
 291         needed  text, -- soname of dependency
 292         primary key (file, needed)
 293 )
 294 without rowid
 295 ;
 296
 297 create view package_libraries as
 298 select distinct PF.pkgid, EL.soname
 299 from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file
 300 ;
 301
 302 create view package_libraries_needed as
 303 with pkglibs as (
 304                 select distinct EN.needed as soname, PF.pkgid
 305                 from elfneeded EN
 306                 join packagefiles_pkgid PF on PF.hash = EN.file
 307                 ),
 308      pkgprovides as (
 309                      select distinct EL.soname, PF.pkgid
 310                      from elflibraries EL
 311                      join packagefiles_pkgid PF on PF.hash = EL.file
 312                     )
 313      select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
 314      from pkglibs PL
 315      left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
 316 ;
 317
 318 -- package scripts: table of package, stage, file
 319 create table scripts (
 320         package text,
 321         version text,
 322         release integer,
 323         stage   text,
 324         hash    text,
 325         primary key (package,version,release,stage),
 326         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 327 );
 328
 329 create view scripts_pkgid as
 330 select printf('%s-%s-%s', package, version, release) as pkgid, *
 331 from scripts
 332 ;
 333
 334 -- package dependencies: table of package, dependency, dep type (package,
 335         -- soname)
 336 -- how to specify min/max/exact
 337 create table packagedeps (
 338         package text,
 339         version text,
 340         release integer,
 341         requires text, -- package, can be partial, minimum
 342         primary key (package,version,release,requires),
 343         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 344 );
 345
 346 -- capability labels
 347 create table provides (
 348         package text,
 349         subpackage      text,
 350         label   text -- a capability label
 351 );
 352
 353 create table requires (
 354         package text,
 355         subpackage      text,
 356         label   text -- a capability label
 357 );
 358
 359 create table packagegroups (
 360         package text,
 361         "group" text
 362 );
 363
 364 -- zpm actions
 365 -- not sure how machine readable this needs to be,
 366 -- do not at all for now, figure it out later
 367 -- could be worth logging all commands in a history table,
 368 -- the zpm driver could do that and capture the exit status
 369 -- as well
 370 -- might want the history table to note a "group" to tie together
 371 -- sub-invocations, probably an environment variable set if not
 372 -- already set by zpm, probably a uuid or a timestamp
 373 create table zpmlog (
 374         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 375         -- timestamp of action
 376         action  text,
 377         target  text, -- packagename, repo name, etc
 378         info    text -- human readable
 379 );
 380
 381 create table notes (
 382         id      integer primary key, -- rowid alias
 383         ts      text default (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
 384         note    text not null,
 385         pkgid   text, -- package
 386         path    text, -- file path involved
 387         file    text, -- hash of file
 388         ack     integer default 0
 389 );
 390
 391 create table history (
 392         ts      integer, -- again, probably needs timestamp sub second
 393         cmd     text,
 394         args    text,
 395         status  integer
 396 );
 397
 398 create table repository (
 399         name    text primary key, -- our name for a repo
 400         url     text not null,
 401         priority        integer not null default 1,
 402         refreshed       integer -- last refresh time
 403 );
 404 -- force the url to be repourl/info.repo
 405 -- package urls repourl/pkgid.zpm
 406
 407 -- urls for downloading packages.  possibly unneeded
 408 create table repository_packages (
 409         repo    text,
 410         pkg     text, -- glob pattern?  in which case others not needed
 411         version text,
 412         release text,
 413         url     text
 414 );
 415
 416 -- track which repository a package was cloned from, i.e. where we got it
 417 create table packagesource (
 418         name    text,
 419         version text,
 420         release integer,
 421         repository      text references repository
 422 );
 423
 424
 425 create view needed_libraries as
 426 with recursive
 427 libs(file,needs,provider) as (
 428         select N.file, N.needed as needs, L.file as provider
 429         from elfneeded N left join elflibraries L on N.needed = L.soname
 430         union
 431         select L.file, N.needed as needs, EL.file as provider
 432         from libs L
 433         join elfneeded N on N.file = L.provider
 434         left join elflibraries EL on N.needed = EL.soname
 435 )
 436 select * from libs;
 437
 438 /*
 439  * tables for repository info, essentially materalized views
 440  */
 441 create table repository_libs (
 442         pkgid text,
 443         soname text
 444 );
 445
 446 create table repository_libsneeded (
 447         pkgid text,
 448         soname text,
 449         selfsat integer
 450 );