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 )
 144 without rowid
 145 ;
 146
 147 create view packagefiles_pkgid as
 148 select printf('%s-%s-%s', package, version, release) as pkgid, *
 149 from packagefiles
 150 ;
 151
 152 create view installed_ref_count as
 153 select I.path, count(*) as refcount
 154 from installedfiles I
 155 group by I.path
 156 ;
 157
 158 create view sync_status_ref_count as
 159 select path, status, count(*) as refcount
 160 from packagefiles_status
 161 where status in ('installed', 'installing', 'removing')
 162 group by path, status
 163 ;
 164
 165 create view packagefiles_status as
 166 select P.status, PF.*
 167 from packagefiles_pkgid PF
 168 left join packages_pkgid P on P.pkgid = PF.pkgid
 169 ;
 170
 171 create view installedfiles as
 172 select * from packagefiles_status
 173 where status = 'installed'
 174 ;
 175
 176 create view install_status as
 177
 178 select 'new' as op, PN.*
 179 from packagefiles_status PN
 180 left join installed_ref_count RC on RC.path = PN.path
 181 where RC.refcount is null
 182 and PN.status = 'installing'
 183
 184 union all
 185
 186 select 'update' as op, PN.*
 187 from packagefiles_status PN
 188 inner join installedfiles PI on PI.path = PN.path and PI.package = PN.package
 189 left join installed_ref_count RC on RC.path = PN.path
 190 where RC.refcount = 1
 191 and PN.status = 'installing'
 192 and PI.hash is not PN.hash
 193
 194 union all
 195
 196 select 'conflict' as op, PI.*
 197 from packagefiles_status PN
 198 inner join installedfiles PI on PI.path = PN.path and PI.package != PN.package
 199 where PN.status = 'installing'
 200
 201 union all
 202 select 'remove' as op, PI.*
 203 from installedfiles PI
 204 left join packagefiles_status PN
 205     on PI.path = PN.path and PI.package = PN.package
 206         and PI.pkgid != PN.pkgid
 207 where PN.path is null
 208 and PI.package in (select package from packages where status = 'installing')
 209
 210 union all
 211 -- remove files in removing, but not installing
 212 select distinct 'remove' as op, PR.*
 213 from packagefiles_status PR
 214 left join packagefiles_status PN
 215 on PR.path = PN.path
 216 and PR.pkgid != PN.pkgid and PN.status in ('installing', 'installed')
 217 where PN.path is null
 218 and PR.status = 'removing'
 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 table elfinfo (
 237         file    text primary key, -- hash of blob
 238         elftype text,
 239         foreign key (file) references files on delete cascade
 240 )
 241 without rowid
 242 ;
 243
 244 create table elfdeps (
 245         file    text,
 246         soname  text,
 247         dependency text,
 248         primary key (file, soname, dependency),
 249         foreign key (file) references files on delete cascade
 250 )
 251 without rowid
 252 ;
 253
 254 -- TODO just elf information?
 255 -- and just hash, not package?
 256 create table elflibraries (
 257         file    text primary key,
 258         soname  text,
 259         foreign key (file) references files on delete cascade
 260 )
 261 without rowid
 262 ;
 263
 264 create table elfneeded (
 265         file    text,
 266         needed  text, -- soname of dependency
 267         primary key (file, needed),
 268         foreign key (file) references files on delete cascade
 269 )
 270 without rowid
 271 ;
 272
 273 -- package scripts: table of package, stage, file
 274 create table scripts (
 275         package text,
 276         version text,
 277         release integer,
 278         stage   text,
 279         hash    text,
 280         primary key (package,version,release,stage),
 281         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 282 );
 283
 284 create view scripts_pkgid as
 285 select printf('%s-%s-%s', package, version, release) as pkgid, *
 286 from scripts
 287 ;
 288
 289 -- package dependencies: table of package, dependency, dep type (package, soname)
 290 create table packagedeps (
 291         package text,
 292         version text,
 293         release integer,
 294         requires text, -- package name (only)
 295         minimum text,
 296         maximum text,
 297         primary key (package,version,release,package),
 298         foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
 299 );
 300
 301 -- capability labels
 302 create table provides (
 303         package text,
 304         subpackage      text,
 305         label   text -- a capability label
 306 );
 307
 308 create table requires (
 309         package text,
 310         subpackage      text,
 311         label   text -- a capability label
 312 );
 313
 314 create table packagegroups (
 315         package text,
 316         "group" text
 317 );
 318
 319 -- zpm actions
 320 -- not sure how machine readable this needs to be,
 321 -- do not at all for now, figure it out later
 322 -- could be worth logging all commands in a history table,
 323 -- the zpm driver could do that and capture the exit status
 324 -- as well
 325 -- might want the history table to note a "group" to tie together
 326 -- sub-invocations, probably an environment variable set if not
 327 -- already set by zpm, probably a uuid or a timestamp
 328 create table zpmlog (
 329         ts      text default (strftime('%Y-%m-%d %H:%M:%f', 'now')),
 330         -- timestamp of action
 331         action  text,
 332         target  text, -- packagename, repo name, etc
 333         info    text -- human readable
 334 );
 335
 336 create table history (
 337         ts      integer, -- again, probably needs timestamp sub second
 338         cmd     text,
 339         args    text,
 340         status  integer
 341 );
 342
 343 create table repository (
 344         name    text primary key, -- our name for a repo
 345         url     text not null,
 346         priority        integer not null default 1,
 347         refreshed       integer -- last refresh time
 348 );
 349
 350 -- urls for downloading packages.  possibly unneeded
 351 create table repository_packages (
 352         repo    text,
 353         pkg     text, -- glob pattern?  in which case others not needed
 354         version text,
 355         release text,
 356         url     text
 357 );
 358
 359 -- track which repository a package was cloned from, i.e. where we got it
 360 create table packagesource (
 361         name    text,
 362         version text,
 363         release integer,
 364         repository      text references repository
 365 );
 366
 367 commit;