remove foreign key on files from elf tables

[zpackage] / db.sql

diff --git a/db.sql b/db.sql
index 2e34ad38b6f0fbfc67ba5fc82ba510fe242f3f27..350bdd090b08d2102d4fd05ab9412249b8ed12b8 100644 (file)
--- a/db.sql
+++ b/db.sql
@@ -139,7 +139,9 @@ create table packagefiles (
        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 = 'r' and hash is null)),
-       check (not (filetype = 'c' and (devmajor is null or devminor 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 (configuration = 0 or configuration = 1)
 )
 without rowid
 ;
@@ -150,6 +152,34 @@ printf('%s:%o:%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,
+       devmajor = NEW.devmajor,
+       devminor = NEW.devminor,
+       hash = NEW.hash,
+       mtime = NEW.mtime
+       where package = OLD.package
+       and version = OLD.version
+       and release = OLD.release
+       and path = OLD.path
+       ;
+end
+;
+
 create view installed_ref_count as
 select I.path, count(*) as refcount
 from installedfiles I
@@ -236,8 +266,7 @@ without rowid
 
 create table elfinfo (
        file    text primary key, -- hash of blob
-       elftype text,
-       foreign key (file) references files on delete cascade
+       elftype text
 )
 without rowid
 ;
@@ -246,8 +275,7 @@ create table elfdeps (
        file    text,
        soname  text,
        dependency text,
-       primary key (file, soname, dependency),
-       foreign key (file) references files on delete cascade
+       primary key (file, soname, dependency)
 )
 without rowid
 ;
@@ -256,8 +284,7 @@ without rowid
 -- and just hash, not package?
 create table elflibraries (
        file    text primary key,
-       soname  text,
-       foreign key (file) references files on delete cascade
+       soname  text
 )
 without rowid
 ;
@@ -265,8 +292,7 @@ without rowid
 create table elfneeded (
        file    text,
        needed  text, -- soname of dependency
-       primary key (file, needed),
-       foreign key (file) references files on delete cascade
+       primary key (file, needed)
 )
 without rowid
 ;