From c8cf77a41735c303259775742e1980848a71f928 Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Mon, 15 Oct 2018 07:29:19 +0000 Subject: [PATCH] add check constraints to db schema --- db.sql | 50 +++++++++++++++++++++++++++++++------------------- 1 file changed, 31 insertions(+), 19 deletions(-) diff --git a/db.sql b/db.sql index d74803d..d5b1d10 100644 --- a/db.sql +++ b/db.sql @@ -22,7 +22,6 @@ create table packages ( package text not null, version text not null, -- the upstream version string release integer not null, -- the local release number --- pkgid text, -- the three above joined with '-' -- metadata columns description text, @@ -33,21 +32,24 @@ create table packages ( packager text, build_time integer default (strftime('%s', 'now')), install_time integer, - hash text, -- see integ.c for package hash + 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) - -- TODO enforce name and version conventions - -- check(instr(version,'-') = 0) - -- check(instr(package,'/') = 0) - -- check(instr(package,'/') = 0) - -- check(instr(version,' ') = 0) - -- check(instr(package,' ') = 0) - -- check(instr(package,' ') = 0) - -- check(length(package) < 64) - -- check(length(version) < 32) + 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 ; @@ -119,25 +121,35 @@ create table packagefiles ( -- d directory -- s symlink -- h hard link -- not supported - -- c character special and b device special files add dev number column - -- b block special - -- p fifos (i.e. pipe) + -- 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 target text, -- link target for links - -- device file dev numbers, should probably be a separate table + -- device file dev numbers devmajor integer, devminor integer, - hash text, -- null if no actual content, i.e. anything but a regular file - mtime integer, -- seconds since epoch, finer resolution probably not needed + 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, + 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 (not (filetype = 'r' and hash is null)), check (not (filetype = 'c' and (devmajor is null or devminor is null))), check (not (filetype = 'b' and (devmajor is null or devminor is null))), + check (filetype in ('r','d','s','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); -- 2.40.0