From 9f2ba07b040d44491bfea7032872b567b60a4000 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 23 Aug 2015 01:08:13 +0100 Subject: Add missing column mapping and correct file name --- gentoobrowse/console/news.xml | 46 ------------------------------------ gentoobrowse/console/newsimport.xml | 47 +++++++++++++++++++++++++++++++++++++ 2 files changed, 47 insertions(+), 46 deletions(-) delete mode 100644 gentoobrowse/console/news.xml create mode 100644 gentoobrowse/console/newsimport.xml diff --git a/gentoobrowse/console/news.xml b/gentoobrowse/console/news.xml deleted file mode 100644 index 109524c..0000000 --- a/gentoobrowse/console/news.xml +++ /dev/null @@ -1,46 +0,0 @@ - - - - - a.newsid IN ( - SELECT f.pathparts[3] - FROM files f - WHERE f.filetypeid = 11 - AND (f.cachedat IS NULL OR f.cachedat != f.moddate)) - - - SELECT - newsid, - (SELECT REGEXP_MATCHES(p[1], 'Title: ([^\n]+)'))[1] title, - CAST((SELECT REGEXP_MATCHES(p[1], 'Posted: ([^\n]+)'))[1] AS DATE) posted, - (SELECT REGEXP_MATCHES(P[1], 'Author: ([^\n]+) <'))[1] authorname, - (SELECT REGEXP_MATCHES(P[1], 'Author:.*?<([^>]+)>'))[1] authoremail, - (SELECT ARRAY_AGG(pkg) FROM (SELECT (REGEXP_MATCHES(P[1], 'Display-If-Installed: ([^\n]+)', 'g') )[1] pkg) p) atomspec, - (SELECT ARRAY_AGG(REGEXP_REPLACE(PARA, '\s+', ' ', 'g')) FROM (SELECT REGEXP_SPLIT_TO_TABLE(p[2], '\n\n') para) p) body, - (SELECT ARRAY_AGG(url) FROM (SELECT (REGEXP_MATCHES(p[3], '(\w+://[^\s]+)', 'g'))[1] url) u) urls - FROM ( - SELECT pathparts[3] newsid, REGEXP_MATCHES(PG_READ_FILE(filename), '^(.*?)\n\n(.*?)(\n(\n\[[\d]+\] \w+://[^ ]+)*)?$') p - FROM files f - WHERE f.filetypeid = 11 - AND (f.cachedat IS NULL OR f.cachedat != f.moddate) - ) newsfiles - - - - - - - - - - - - - - UPDATE files SET - cachedat = moddate - WHERE filetypeid = 11 - - - diff --git a/gentoobrowse/console/newsimport.xml b/gentoobrowse/console/newsimport.xml new file mode 100644 index 0000000..3e9c041 --- /dev/null +++ b/gentoobrowse/console/newsimport.xml @@ -0,0 +1,47 @@ + + + + + a.newsid IN ( + SELECT f.pathparts[3] + FROM files f + WHERE f.filetypeid = 11 + AND (f.cachedat IS NULL OR f.cachedat != f.moddate)) + + + SELECT + newsid, + (SELECT REGEXP_MATCHES(p[1], 'Title: ([^\n]+)'))[1] title, + CAST((SELECT REGEXP_MATCHES(p[1], 'Posted: ([^\n]+)'))[1] AS DATE) posted, + (SELECT REGEXP_MATCHES(P[1], 'Author: ([^\n]+) <'))[1] authorname, + (SELECT REGEXP_MATCHES(P[1], 'Author:.*?<([^>]+)>'))[1] authoremail, + (SELECT ARRAY_AGG(pkg) FROM (SELECT (REGEXP_MATCHES(P[1], 'Display-If-Installed: ([^\n]+)', 'g') )[1] pkg) p) atomspec, + (SELECT ARRAY_AGG(REGEXP_REPLACE(PARA, '\s+', ' ', 'g')) FROM (SELECT REGEXP_SPLIT_TO_TABLE(p[2], '\n\n') para) p) body, + (SELECT ARRAY_AGG(url) FROM (SELECT (REGEXP_MATCHES(p[3], '(\w+://[^\s]+)', 'g'))[1] url) u) urls + FROM ( + SELECT pathparts[3] newsid, REGEXP_MATCHES(PG_READ_FILE(filename), '^(.*?)\n\n(.*?)(\n(\n\[[\d]+\] \w+://[^ ]+)*)?$') p + FROM files f + WHERE f.filetypeid = 11 + AND (f.cachedat IS NULL OR f.cachedat != f.moddate) + ) newsfiles + + + + + <posted /> + <authorname /> + <authoremail /> + <atomspec /> + <body /> + <urls /> + </columns> + </project2:sqlmerge> + <project2:sqltask name="update" datasource="postgres"> + <sql> + UPDATE files SET + cachedat = moddate + WHERE filetypeid = 11 + </sql> + </project2:sqltask> +</packageimport> -- cgit v1.2.3