fix bug in aging view, add man pages

[zpackage] / schema / age.sql

diff --git a/schema/age.sql b/schema/age.sql
new file mode 100644 (file)
index 0000000..bdc5da8
--- /dev/null
+++ b/schema/age.sql
@@ -0,0 +1,24 @@
+create view package_age as
+with aging as (
+select
+P.package, P.version,P.release,P.status,
+row_number() over (
+       partition by P.package
+       order by P.version collate vercmp desc, P.release desc
+) as age
+from
+packages P
+left join preserve_packages KEEP
+on KEEP.package = P.package and KEEP.version = P.version and KEEP.release = P.release
+where P.status in ('updated','removed')
+and KEEP.package is null
+)
+select * from aging
+union
+select P.package, P.version, P.release, P.status, 0 as age
+from packages P where P.status = 'installed'
+union
+select P.package, P.version, P.release, 'preserved' as status, 0 as age
+from preserve_packages P
+order by package, version collate vercmp desc, release desc
+;