X-Git-Url: https://pd.if.org/git/?a=blobdiff_plain;f=schema%2Fage.sql;fp=schema%2Fage.sql;h=bdc5da8394bde45cb31ce90edb259e34306cd06b;hb=e391f268e77b1609f4df34b1bb272cad3b5de7d4;hp=0000000000000000000000000000000000000000;hpb=441208eb6fa19d6eb529da0a176fa647bb005cda;p=zpackage

diff --git a/schema/age.sql b/schema/age.sql
new file mode 100644
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
+;