PRAGMA application_id = 0x5a504442; PRAGMA user_version = 1; -- should be faster with rowid due to the blob content -- these are really just blobs of data -- TODO copyright and license information should probably -- go here CREATE TABLE files ( hash text primary key, -- sha256 of content size integer, -- bigint? certainly need > 2GB compression text, -- always xz? content blob ) ; create view filerefs as select F.hash, count(PF.hash) + count(S.hash) + count(EL.file) + count(N.file) as refcount from files F left join packagefiles PF on PF.hash = F.hash left join scripts S on S.hash = F.hash left join elflibraries EL on EL.file = F.hash left join notes N on N.file = F.hash group by F.hash ; -- information about packages -- a package is identified by a package,version,release triple create table packages ( -- primary key columns package text not null, version text not null, -- the upstream version string release integer not null, -- the local release number -- metadata columns description text, architecture text, url text, status text, licenses text, -- hash of actual license? need table for more than one? packager text, build_time integer default (strftime('%s', 'now')), install_time integer, hash text, -- see integ.c for package hash details primary key (package,version,release), check (typeof(package) = 'text'), check (typeof(version) = 'text'), check (typeof(release) = 'integer'), check (release > 0), -- enforce name and version conventions check(instr(package,' ') = 0), check(instr(package,'/') = 0), check(instr(package,':') = 0), check(instr(version,' ') = 0), check(instr(version,'-') = 0), check(instr(version,'/') = 0), check(instr(version,':') = 0), check(length(package) < 64), check(length(package) > 0), check(length(version) < 32), check(length(version) > 0) ) without rowid ; create index package_status_index on packages (status); create index package_package_index on packages (package); create view packages_pkgid as select printf('%s-%s-%s', package, version, release) as pkgid, * from packages; create trigger packages_delete_trigger instead of delete on packages_pkgid begin delete from packages where package = OLD.package and version = OLD.version and release = OLD.release; end; create trigger packages_update_trigger instead of update on packages_pkgid begin update packages set package = NEW.package, version = NEW.version, release = NEW.release, description = NEW.description, architecture = NEW.architecture, url = NEW.url, status = NEW.status, licenses = NEW.licenses, packager = NEW.packager, build_time = NEW.build_time, install_time = NEW.install_time, hash = NEW.hash where package = OLD.package and version = OLD.version and release = OLD.release ; end ; -- handle package status history with a logging trigger. create trigger logpkgstatus after update of status on packages begin insert into zpmlog (action,target,info) values (printf('status change %s %s', OLD.status, NEW.status), printf('%s-%s-%s', NEW.package, NEW.version, NEW.release), NULL); END; create table packagetags ( -- package id triple package text, version text, release integer, tag text, set_time integer default (strftime('%s', 'now')), primary key (package,version,release,tag), foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade ); -- files contained in a package create table packagefiles ( -- package id triple package text, version text, release integer, path text, -- filesystem path mode text not null default '0644', -- perms, use text for octal rep? username text not null default 'root', -- name of owner groupname text not null default 'root', -- group of owner uid integer, -- numeric uid, generally ignored gid integer, -- numeric gid, generally ignored configuration integer not null default 0, -- boolean if config file confhash text, -- last hash on disk filetype varchar not null default 'r', -- r regular file -- d directory -- l symlink -- h hard link -- not supported -- c character special -- not supported -- b block special -- not supported -- c and b device special files add dev number column -- p fifos (i.e. pipe) -- not supported -- s unix domain socket -- not supported target text, -- link target for links device integer, -- device file dev_t hash text, -- null if not a regular file mtime integer, -- seconds since epoch, finer resolution not needed primary key (package,version,release,path), foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade, check (not (filetype = 'l' and target is null)), check (not (filetype = 'h' and target is null)), check (target is null or length(target) between 1 and 4095), check (hash is null or length(hash) between 1 and 1024), check (path not in ('.', '..')), check (not (filetype = 'r' and hash is null)), check (not (filetype = 'c' and device is null)), check (not (filetype = 'b' and device is null)), check (filetype in ('r','d','l','h','c','b','p')), check(length(username) between 1 and 256), check(length(groupname) between 1 and 256), check (configuration = 0 or configuration = 1) ) without rowid ; create index packagefile_package_index on packagefiles (package); create index packagefile_path_index on packagefiles (path); create index packagefile_hash_index on packagefiles (hash); create index pkgfile_configuration on packagefiles (configuration) where configuration = 1; create view packagefiles_pkgid as select printf('%s-%s-%s', package, version, release) as pkgid, *, printf('%s:%s:%s:%s', filetype, mode, username, groupname) as mds from packagefiles ; create trigger packagefiles_update_trigger instead of update on packagefiles_pkgid begin update packagefiles set package = NEW.package, version = NEW.version, release = NEW.release, path = NEW.path, mode = NEW.mode, username = NEW.username, groupname = NEW.groupname, uid = NEW.uid, gid = NEW.gid, configuration = NEW.configuration, filetype = NEW.filetype, target = NEW.target, device = NEW.device, hash = NEW.hash, mtime = NEW.mtime where package = OLD.package and version = OLD.version and release = OLD.release and path = OLD.path ; end ; create trigger packagefiles_delete_trigger instead of delete on packagefiles_pkgid begin delete from packagefiles where package = OLD.package and version = OLD.version and release = OLD.release and path = OLD.path ; update packages set hash = null where package = OLD.package and version = OLD.version and release = OLD.release ; end ; create view installed_ref_count as select I.path, count(*) as refcount from installedfiles I group by I.path ; create view sync_status_ref_count as select path, status, count(*) as refcount from packagefiles_status where status in ('installed', 'installing', 'removing') group by path, status ; create view packagefiles_status as select P.status, PF.* from packagefiles_pkgid PF left join packages_pkgid P on P.pkgid = PF.pkgid ; create view installedfiles as select * from packagefiles_status where status = 'installed' ; create table pathtags ( -- package id triple package text, version text, release integer, path text, -- filesystem path tag text, primary key (package,version,release,path,tag), foreign key (package,version,release,path) references packagefiles on delete cascade on update cascade ) without rowid ; create view elfdeps as select PF.pkgid, PF.status, PF.path, N.needed as needs, PL.path as library, PL.pkgid provider, PL.status as library_status from packagefiles_status PF join elfneeded N on N.file = PF.hash left join elflibraries L on N.needed = L.soname left join packagefiles_status PL on PL.hash = L.file ; -- TODO just elf information? -- and just hash, not package? create table elflibraries ( file text primary key, soname text ) without rowid ; create index elf_library_name_index on elflibraries(soname); create table elfneeded ( file text, -- hash of file needed text, -- soname of dependency primary key (file, needed) ) without rowid ; create view package_libraries as select distinct PF.pkgid, EL.soname from packagefiles_pkgid PF join elflibraries EL on PF.hash = EL.file ; create view package_libraries_needed as with pkglibs as ( select distinct EN.needed as soname, PF.pkgid from elfneeded EN join packagefiles_pkgid PF on PF.hash = EN.file ), pkgprovides as ( select distinct EL.soname, PF.pkgid from elflibraries EL join packagefiles_pkgid PF on PF.hash = EL.file ) select distinct PL.pkgid,PL.soname, PP.soname is not null as selfsatisfied from pkglibs PL left join pkgprovides PP on PL.pkgid = PP.pkgid and PL.soname = PP.soname ; -- package scripts: table of package, stage, file create table scripts ( package text, version text, release integer, stage text, hash text, primary key (package,version,release,stage), foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade ); create view scripts_pkgid as select printf('%s-%s-%s', package, version, release) as pkgid, * from scripts ; -- package dependencies: table of package, dependency, dep type (package, soname) create table packagedeps ( package text, version text, release integer, requires text, -- package, can be partial primary key (package,version,release,package), foreign key (package,version,release) references packages (package,version,release) on delete cascade on update cascade ); -- capability labels create table provides ( package text, subpackage text, label text -- a capability label ); create table requires ( package text, subpackage text, label text -- a capability label ); create table packagegroups ( package text, "group" text ); -- zpm actions -- not sure how machine readable this needs to be, -- do not at all for now, figure it out later -- could be worth logging all commands in a history table, -- the zpm driver could do that and capture the exit status -- as well -- might want the history table to note a "group" to tie together -- sub-invocations, probably an environment variable set if not -- already set by zpm, probably a uuid or a timestamp create table zpmlog ( ts text default (strftime('%Y-%m-%d %H:%M:%f', 'now')), -- timestamp of action action text, target text, -- packagename, repo name, etc info text -- human readable ); create table notes ( id integer primary key, -- rowid alias ts text default (strftime('%Y-%m-%dT%H:%M:%f', 'now')), note text not null, pkgid text, -- package path text, -- file path involved file text, -- hash of file ack integer default 0 ); create table history ( ts integer, -- again, probably needs timestamp sub second cmd text, args text, status integer ); create table repository ( name text primary key, -- our name for a repo url text not null, priority integer not null default 1, refreshed integer -- last refresh time ); -- force the url to be repourl/info.repo -- package urls repourl/pkgid.zpm -- urls for downloading packages. possibly unneeded create table repository_packages ( repo text, pkg text, -- glob pattern? in which case others not needed version text, release text, url text ); -- track which repository a package was cloned from, i.e. where we got it create table packagesource ( name text, version text, release integer, repository text references repository ); create view needed_libraries as with recursive libs(file,needs,provider) as ( select N.file, N.needed as needs, L.file as provider from elfneeded N left join elflibraries L on N.needed = L.soname union select L.file, N.needed as needs, EL.file as provider from libs L join elfneeded N on N.file = L.provider left join elflibraries EL on N.needed = EL.soname ) select * from libs; /* * tables for repository info, essentially materalized views */ create table repository_libs ( pkgid text, soname text ); create table repository_libsneeded ( pkgid text, soname text, selfsat integer );