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         checksum        text, -- checksum of package contents.  null for incompleted packages
  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
  57 create view packages_pkgid as
  58 select printf('%s-%s-%s', package, version, release) as pkgid, *
  59 from packages;
  60
  61 create trigger packages_update_trigger instead of
  62 update on packages_pkgid
  63 begin
  64         update packages
  65         set package = NEW.package,
  66         version = NEW.version,
  67         release = NEW.release,
  68         description = NEW.description,
  69         architecture = NEW.architecture,
  70         url = NEW.url,
  71         status = NEW.status,
  72         licenses = NEW.licenses,
  73         packager = NEW.packager,
  74         build_time = NEW.build_time,
  75         install_time = NEW.install_time,
  76         checksum = NEW.checksum
  77         where package = OLD.package
  78         and version = OLD.version
  79         and release = OLD.release
  80         ;
  81 end
  82 ;
  83
  84 -- handle package status history with a logging trigger.
  85 create trigger logpkgstatus after update of status on packages
  86 begin insert into zpmlog (action,target,info)
  87         values (printf('status change %s %s', OLD.status, NEW.status),
  88                 printf('%s-%s-%s', NEW.package, NEW.version, NEW.release),
  89                 NULL); END;
  90
  91 create table packagetags (
  92         -- package id triple
  93         package text,
  94         version text,
  95         release integer,
  96         tag     text,
  97         set_time integer default (strftime('%s', 'now')),
  98         primary key (package,version,release,tag),
  99         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 100 );
 101
 102 -- packagefile hash is columns as text, joined with null bytes, then
 103 -- sha256 sum of that
 104 -- package checksum is package columns as text, joined with null bytes,
 105 -- other than the checksum and install_time column
 106 -- then that hashed.  finally, that hash, plus the ascii sorted
 107 -- hashes of the package files all joined with newlines, hashed.
 108 -- really don't like this.
 109
 110 -- files contained in a package
 111 create table packagefiles (
 112         -- package id triple
 113         package text,
 114         version text,
 115         release integer,
 116
 117         path    text, -- filesystem path
 118         mode    text not null, -- perms, use text for octal rep?
 119         username        text not null, -- name of owner
 120         groupname       text not null, -- group of owner
 121         uid     integer, -- numeric uid, generally ignored
 122         gid     integer, -- numeric gid, generally ignored
 123         configuration integer not null default 0, -- boolean if config file
 124         filetype varchar not null default 'r',
 125         -- r regular file
 126         -- d directory
 127         -- s symlink
 128         -- h hard link -- not supported
 129         -- c character special and b device special files add dev number column
 130         -- b block special
 131         -- p fifos (i.e. pipe)
 132         target  text, -- link target for links
 133         -- device file dev numbers, should probably be a separate table
 134         devmajor        integer,
 135         devminor        integer,
 136         hash    text, -- null if no actual content, i.e. anything but a regular file
 137         mtime   integer, -- seconds since epoch, finer resolution probably not needed
 138         primary key (package,version,release,path),
 139         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade,
 140         check (not (filetype = 'l' and target is null)),
 141         check (not (filetype = 'r' and hash is null)),
 142         check (not (filetype = 'c' and (devmajor is null or devminor is null))),
 143         check (not (filetype = 'b' and (devmajor is null or devminor is null))),
 144         check (configuration = 0 or configuration = 1)
 145 )
 146 without rowid
 147 ;
 148
 149 create view packagefiles_pkgid as
 150 select printf('%s-%s-%s', package, version, release) as pkgid, *,
 151 printf('%s:%o:%s:%s', filetype, mode, username, groupname) as mds
 152 from packagefiles
 153 ;
 154
 155 create trigger packagefiles_update_trigger instead of
 156 update on packagefiles_pkgid
 157 begin
 158         update packages
 159         set package = NEW.package,
 160         version = NEW.version,
 161         release = NEW.release,
 162         path = NEW.path,
 163         mode = NEW.mode,
 164         username = NEW.username,
 165         groupname = NEW.groupname,
 166         uid = NEW.uid,
 167         gid = NEW.gid,
 168         configuration = NEW.configuration,
 169         filetype = NEW.filetype,
 170         target = NEW.target,
 171         devmajor = NEW.devmajor,
 172         devminor = NEW.devminor,
 173         hash = NEW.hash,
 174         mtime = NEW.mtime
 175         where package = OLD.package
 176         and version = OLD.version
 177         and release = OLD.release
 178         and path = OLD.path
 179         ;
 180 end
 181 ;
 182
 183 create view installed_ref_count as
 184 select I.path, count(*) as refcount
 185 from installedfiles I
 186 group by I.path
 187 ;
 188
 189 create view sync_status_ref_count as
 190 select path, status, count(*) as refcount
 191 from packagefiles_status
 192 where status in ('installed', 'installing', 'removing')
 193 group by path, status
 194 ;
 195
 196 create view packagefiles_status as
 197 select P.status, PF.*
 198 from packagefiles_pkgid PF
 199 left join packages_pkgid P on P.pkgid = PF.pkgid
 200 ;
 201
 202 create view installedfiles as
 203 select * from packagefiles_status
 204 where status = 'installed'
 205 ;
 206
 207 create view install_status as
 208
 209 select 'new' as op, PN.*
 210 from packagefiles_status PN
 211 left join installed_ref_count RC on RC.path = PN.path
 212 where RC.refcount is null
 213 and PN.status = 'installing'
 214
 215 union all
 216
 217 select 'update' as op, PN.*
 218 from packagefiles_status PN
 219 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
 220 left join installed_ref_count RC on RC.path = PN.path
 221 where RC.refcount = 1
 222 and PN.status = 'installing'
 223 and PI.hash is not PN.hash
 224
 225 union all
 226
 227 select 'conflict' as op, PI.*
 228 from packagefiles_status PN
 229 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
 230 where PN.status = 'installing'
 231
 232 union all
 233 select 'remove' as op, PI.*
 234 from installedfiles PI
 235 left join packagefiles_status PN
 236     on PI.path = PN.path and PI.package = PN.package
 237         and PI.pkgid != PN.pkgid
 238 where PN.path is null
 239 and PI.package in (select package from packages where status = 'installing')
 240
 241 union all
 242 -- remove files in removing, but not installing
 243 select distinct 'remove' as op, PR.*
 244 from packagefiles_status PR
 245 left join packagefiles_status PN
 246 on PR.path = PN.path
 247 and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
 248 where PN.path is null
 249 and PR.status = 'removing'
 250 ;
 251
 252 create table pathtags (
 253         -- package id triple
 254         package text,
 255         version text,
 256         release integer,
 257
 258         path    text, -- filesystem path
 259         tag     text,
 260         primary key (package,version,release,path,tag),
 261         foreign key (package,version,release,path)
 262         references packagefiles on delete cascade on update cascade
 263 )
 264 without rowid
 265 ;
 266
 267 create table elfinfo (
 268         file    text primary key, -- hash of blob
 269         elftype text,
 270         foreign key (file) references files on delete cascade
 271 )
 272 without rowid
 273 ;
 274
 275 create table elfdeps (
 276         file    text,
 277         soname  text,
 278         dependency text,
 279         primary key (file, soname, dependency),
 280         foreign key (file) references files on delete cascade
 281 )
 282 without rowid
 283 ;
 284
 285 -- TODO just elf information?
 286 -- and just hash, not package?
 287 create table elflibraries (
 288         file    text primary key,
 289         soname  text,
 290         foreign key (file) references files on delete cascade
 291 )
 292 without rowid
 293 ;
 294
 295 create table elfneeded (
 296         file    text,
 297         needed  text, -- soname of dependency
 298         primary key (file, needed),
 299         foreign key (file) references files on delete cascade
 300 )
 301 without rowid
 302 ;
 303
 304 -- package scripts: table of package, stage, file
 305 create table scripts (
 306         package text,
 307         version text,
 308         release integer,
 309         stage   text,
 310         hash    text,
 311         primary key (package,version,release,stage),
 312         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 313 );
 314
 315 create view scripts_pkgid as
 316 select printf('%s-%s-%s', package, version, release) as pkgid, *
 317 from scripts
 318 ;
 319
 320 -- package dependencies: table of package, dependency, dep type (package, soname)
 321 create table packagedeps (
 322         package text,
 323         version text,
 324         release integer,
 325         requires text, -- package name (only)
 326         minimum text,
 327         maximum text,
 328         primary key (package,version,release,package),
 329         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 330 );
 331
 332 -- capability labels
 333 create table provides (
 334         package text,
 335         subpackage      text,
 336         label   text -- a capability label
 337 );
 338
 339 create table requires (
 340         package text,
 341         subpackage      text,
 342         label   text -- a capability label
 343 );
 344
 345 create table packagegroups (
 346         package text,
 347         "group" text
 348 );
 349
 350 -- zpm actions
 351 -- not sure how machine readable this needs to be,
 352 -- do not at all for now, figure it out later
 353 -- could be worth logging all commands in a history table,
 354 -- the zpm driver could do that and capture the exit status
 355 -- as well
 356 -- might want the history table to note a "group" to tie together
 357 -- sub-invocations, probably an environment variable set if not
 358 -- already set by zpm, probably a uuid or a timestamp
 359 create table zpmlog (
 360         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 361         -- timestamp of action
 362         action  text,
 363         target  text, -- packagename, repo name, etc
 364         info    text -- human readable
 365 );
 366
 367 create table history (
 368         ts      integer, -- again, probably needs timestamp sub second
 369         cmd     text,
 370         args    text,
 371         status  integer
 372 );
 373
 374 create table repository (
 375         name    text primary key, -- our name for a repo
 376         url     text not null,
 377         priority        integer not null default 1,
 378         refreshed       integer -- last refresh time
 379 );
 380
 381 -- urls for downloading packages.  possibly unneeded
 382 create table repository_packages (
 383         repo    text,
 384         pkg     text, -- glob pattern?  in which case others not needed
 385         version text,
 386         release text,
 387         url     text
 388 );
 389
 390 -- track which repository a package was cloned from, i.e. where we got it
 391 create table packagesource (
 392         name    text,
 393         version text,
 394         release integer,
 395         repository      text references repository
 396 );
 397
 398 commit;