From 1bfb5f3817fda4edc6044e56e8a7ef548cf4c0db Mon Sep 17 00:00:00 2001 From: Nathan Wagner Date: Sat, 15 Sep 2018 02:05:55 +0000 Subject: [PATCH] sanitize packages table Added check constraints, and removed pkgid column. Added a view packages_pkgid with the pkgid column constructed from the package, version, and release columns. --- db.sql | 21 ++++++++++++++++----- 1 file changed, 16 insertions(+), 5 deletions(-) diff --git a/db.sql b/db.sql index 5f227c8..0a18326 100644 --- a/db.sql +++ b/db.sql @@ -19,10 +19,10 @@ CREATE TABLE files ( -- a package is identified by a package,version,release triple create table packages ( -- primary key columns - package text, - version text, -- the upstream version string - release integer, -- the local release number - pkgid text, -- the three above joined with '-' + 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, @@ -35,13 +35,24 @@ create table packages ( install_time integer, checksum text, -- checksum of package contents. null for incompleted packages primary key (package,version,release), + check (typeof(package) = 'text'), + check (typeof(version) = 'text'), check (typeof(release) = 'integer'), check (release > 0) - -- TODO enforce name and release conventions + -- TODO enforce name and version conventions + -- check(instr(version,'-') = 0) + -- check(instr(package,'/') = 0) + -- check(instr(package,'/') = 0) + -- check(length(package) < 64) + -- check(length(version) < 32) ) without rowid ; +create view packages_pkgid as +select printf('%s-%s-%s', package, version, release) as pkgid, * +from packages; + -- handle package status history with a logging trigger. create trigger logpkgstatus after update of status on packages begin insert into zpmlog (action,target,info) -- 2.40.0