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 packagefiles
 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 view elfdeps as
 268 select PF.pkgid, PF.status, PF.path, N.needed as needs,
 269 PL.path as library, PL.pkgid provider, PL.status as library_status
 270 from packagefiles_status PF
 271 join elfneeded N on N.file = PF.hash
 272 left join elflibraries L on N.needed = L.soname
 273 left join packagefiles_status PL on PL.hash = L.file
 274 ;
 275
 276 -- TODO just elf information?
 277 -- and just hash, not package?
 278 create table elflibraries (
 279         file    text primary key,
 280         soname  text
 281 )
 282 without rowid
 283 ;
 284
 285 create table elfneeded (
 286         file    text,
 287         needed  text, -- soname of dependency
 288         primary key (file, needed)
 289 )
 290 without rowid
 291 ;
 292
 293 -- package scripts: table of package, stage, file
 294 create table scripts (
 295         package text,
 296         version text,
 297         release integer,
 298         stage   text,
 299         hash    text,
 300         primary key (package,version,release,stage),
 301         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 302 );
 303
 304 create view scripts_pkgid as
 305 select printf('%s-%s-%s', package, version, release) as pkgid, *
 306 from scripts
 307 ;
 308
 309 -- package dependencies: table of package, dependency, dep type (package, soname)
 310 create table packagedeps (
 311         package text,
 312         version text,
 313         release integer,
 314         requires text, -- package name (only)
 315         minimum text,
 316         maximum text,
 317         primary key (package,version,release,package),
 318         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 319 );
 320
 321 -- capability labels
 322 create table provides (
 323         package text,
 324         subpackage      text,
 325         label   text -- a capability label
 326 );
 327
 328 create table requires (
 329         package text,
 330         subpackage      text,
 331         label   text -- a capability label
 332 );
 333
 334 create table packagegroups (
 335         package text,
 336         "group" text
 337 );
 338
 339 -- zpm actions
 340 -- not sure how machine readable this needs to be,
 341 -- do not at all for now, figure it out later
 342 -- could be worth logging all commands in a history table,
 343 -- the zpm driver could do that and capture the exit status
 344 -- as well
 345 -- might want the history table to note a "group" to tie together
 346 -- sub-invocations, probably an environment variable set if not
 347 -- already set by zpm, probably a uuid or a timestamp
 348 create table zpmlog (
 349         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 350         -- timestamp of action
 351         action  text,
 352         target  text, -- packagename, repo name, etc
 353         info    text -- human readable
 354 );
 355
 356 create table history (
 357         ts      integer, -- again, probably needs timestamp sub second
 358         cmd     text,
 359         args    text,
 360         status  integer
 361 );
 362
 363 create table repository (
 364         name    text primary key, -- our name for a repo
 365         url     text not null,
 366         priority        integer not null default 1,
 367         refreshed       integer -- last refresh time
 368 );
 369
 370 -- urls for downloading packages.  possibly unneeded
 371 create table repository_packages (
 372         repo    text,
 373         pkg     text, -- glob pattern?  in which case others not needed
 374         version text,
 375         release text,
 376         url     text
 377 );
 378
 379 -- track which repository a package was cloned from, i.e. where we got it
 380 create table packagesource (
 381         name    text,
 382         version text,
 383         release integer,
 384         repository      text references repository
 385 );
 386
 387 commit;