+create view packages_pkgid as
+select printf('%s-%s-%s', package, version, release) as pkgid, *
+from packages;
+
+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,
+ checksum = NEW.checksum
+ 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
+);
+