1 PRAGMA application_id = 0x5a504442;
2 PRAGMA user_version = 1;
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
9 hash text primary key, -- sha256 of (uncompressed) content
10 size integer, -- bigint? certainly need > 2GB
11 compression text, -- always xz?
16 create view filerefs as
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
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
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
46 licenses text, -- hash of actual license? need table for more than one?
48 build_time integer default (strftime('%s', 'now')),
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'),
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)
72 create index package_status_index on packages (status);
73 create index package_package_index on packages (package);
75 create view packages_pkgid as
76 select printf('%s-%s-%s', package, version, release) as pkgid, *
79 create trigger packages_delete_trigger instead of
80 delete on packages_pkgid
82 delete from packages where package = OLD.package
83 and version = OLD.version and release = OLD.release;
86 create trigger packages_update_trigger instead of
87 update on packages_pkgid
90 set package = NEW.package,
91 version = NEW.version,
92 release = NEW.release,
93 description = NEW.description,
94 architecture = NEW.architecture,
97 licenses = NEW.licenses,
98 packager = NEW.packager,
99 build_time = NEW.build_time,
100 install_time = NEW.install_time,
102 where package = OLD.package
103 and version = OLD.version
104 and release = OLD.release
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),
116 create table packagetags (
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
127 -- files contained in a package
128 create table packagefiles (
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',
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)
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;
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
187 create trigger packagefiles_update_trigger instead of
188 update on packagefiles_pkgid
191 set package = NEW.package,
192 version = NEW.version,
193 release = NEW.release,
196 username = NEW.username,
197 groupname = NEW.groupname,
200 configuration = NEW.configuration,
201 filetype = NEW.filetype,
206 where package = OLD.package
207 and version = OLD.version
208 and release = OLD.release
214 create trigger packagefiles_delete_trigger instead of
215 delete on packagefiles_pkgid
217 delete from packagefiles
218 where package = OLD.package
219 and version = OLD.version
220 and release = OLD.release
223 update packages set hash = null
224 where package = OLD.package
225 and version = OLD.version
226 and release = OLD.release
231 create view installed_ref_count as
232 select I.path, count(*) as refcount
233 from installedfiles I
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
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
250 create view installedfiles as
251 select * from packagefiles_status
252 where status = 'installed'
255 create table pathtags (
261 path text, -- filesystem path
263 primary key (package,version,release,path,tag),
264 foreign key (package,version,release,path)
265 references packagefiles on delete cascade on update cascade
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
279 -- TODO just elf information?
280 -- and just hash, not package?
281 create table elflibraries (
282 file text primary key,
287 create index elf_library_name_index on elflibraries(soname);
289 create table elfneeded (
290 file text, -- hash of file
291 needed text, -- soname of dependency
292 primary key (file, needed)
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
302 create view package_libraries_needed as
304 select distinct EN.needed as soname, PF.pkgid
306 join packagefiles_pkgid PF on PF.hash = EN.file
309 select distinct EL.soname, PF.pkgid
311 join packagefiles_pkgid PF on PF.hash = EL.file
313 select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied
315 left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname
318 -- package scripts: table of package, stage, file
319 create table scripts (
325 primary key (package,version,release,stage),
326 foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade
329 create view scripts_pkgid as
330 select printf('%s-%s-%s', package, version, release) as pkgid, *
334 -- package dependencies: table of package, dependency, dep type (package,
336 -- how to specify min/max/exact
337 create table packagedeps (
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
347 create table provides (
350 label text -- a capability label
353 create table requires (
356 label text -- a capability label
359 create table packagegroups (
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
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
377 target text, -- packagename, repo name, etc
378 info text -- human readable
382 id integer primary key, -- rowid alias
383 ts text default (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
385 pkgid text, -- package
386 path text, -- file path involved
387 file text, -- hash of file
388 ack integer default 0
391 create table history (
392 ts integer, -- again, probably needs timestamp sub second
398 create table repository (
399 name text primary key, -- our name for a repo
401 priority integer not null default 1,
402 refreshed integer -- last refresh time
404 -- force the url to be repourl/info.repo
405 -- package urls repourl/pkgid.zpm
407 -- urls for downloading packages. possibly unneeded
408 create table repository_packages (
410 pkg text, -- glob pattern? in which case others not needed
416 -- track which repository a package was cloned from, i.e. where we got it
417 create table packagesource (
421 repository text references repository
425 create view needed_libraries as
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
431 select L.file, N.needed as needs, EL.file as provider
433 join elfneeded N on N.file = L.provider
434 left join elflibraries EL on N.needed = EL.soname
439 * tables for repository info, essentially materalized views
441 create table repository_libs (
446 create table repository_libsneeded (