-
Notifications
You must be signed in to change notification settings - Fork 1.2k
/
newsfeed.sql
133 lines (127 loc) · 5.89 KB
/
newsfeed.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
/*
* Copyright 2004-2022 H2 Group. Multiple-Licensed under the MPL 2.0,
* and the EPL 1.0 (https://h2database.com/html/license.html).
* Initial Developer: H2 Group
*/
CREATE TABLE VERSION(ID INT PRIMARY KEY, VERSION VARCHAR, CREATED VARCHAR);
INSERT INTO VERSION VALUES
(153, '2.0.206', '2022-01-04'),
(152, '2.0.204', '2021-12-21'),
(151, '2.0.202', '2021-11-25'),
(150, '1.4.200', '2019-10-14'),
(149, '1.4.199', '2019-03-13'),
(148, '1.4.198', '2019-02-22'),
(147, '1.4.197', '2018-03-18'),
(146, '1.4.196', '2017-06-10'),
(145, '1.4.195', '2017-04-23'),
(144, '1.4.194', '2017-03-10'),
(143, '1.4.193', '2016-10-31'),
(142, '1.4.192', '2016-05-26'),
(141, '1.4.191', '2016-01-21'),
(140, '1.4.190', '2015-10-11'),
(139, '1.4.189', '2015-09-13'),
(138, '1.4.188', '2015-08-01'),
(137, '1.4.187', '2015-04-10'),
(136, '1.4.186', '2015-03-02'),
(135, '1.4.185', '2015-01-16');
CREATE TABLE CHANNEL(TITLE VARCHAR, LINK VARCHAR, DESC VARCHAR,
LANGUAGE VARCHAR, PUB TIMESTAMP, LAST TIMESTAMP, AUTHOR VARCHAR);
INSERT INTO CHANNEL VALUES('H2 Database Engine' ,
'https://h2database.com/', 'H2 Database Engine', 'en-us', LOCALTIMESTAMP, LOCALTIMESTAMP, 'Thomas Mueller');
CREATE VIEW ITEM AS
SELECT ID, 'New version available: ' || VERSION || ' (' || CREATED || ')' TITLE,
CAST((CREATED || ' 12:00:00') AS TIMESTAMP) ISSUED,
$$A new version of H2 is available for
<a href="https://h2database.com">download</a>.
(You may have to click 'Refresh').
<br />
For details, see the
<a href="https://h2database.com/html/changelog.html">change log</a>.
$$ AS DESC FROM VERSION;
SELECT 'newsfeed-rss.xml' FILE,
XMLSTARTDOC() ||
XMLNODE('rss', XMLATTR('version', '2.0'),
XMLNODE('channel', NULL,
XMLNODE('title', NULL, C.TITLE) ||
XMLNODE('link', NULL, C.LINK) ||
XMLNODE('description', NULL, C.DESC) ||
XMLNODE('language', NULL, C.LANGUAGE) ||
XMLNODE('pubDate', NULL, FORMATDATETIME(C.PUB, 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')) ||
XMLNODE('lastBuildDate', NULL, FORMATDATETIME(C.LAST, 'EEE, d MMM yyyy HH:mm:ss z', 'en', 'GMT')) ||
GROUP_CONCAT(
XMLNODE('item', NULL,
XMLNODE('title', NULL, I.TITLE) ||
XMLNODE('link', NULL, C.LINK) ||
XMLNODE('description', NULL, XMLCDATA(I.TITLE))
)
ORDER BY I.ID DESC SEPARATOR '')
)
) CONTENT
FROM CHANNEL C, ITEM I
UNION
SELECT 'newsfeed-atom.xml' FILE,
XMLSTARTDOC() ||
XMLNODE('feed', XMLATTR('xmlns', 'http://www.w3.org/2005/Atom') || XMLATTR('xml:lang', C.LANGUAGE),
XMLNODE('title', XMLATTR('type', 'text'), C.TITLE) ||
XMLNODE('id', NULL, XMLTEXT(C.LINK)) ||
XMLNODE('author', NULL, XMLNODE('name', NULL, C.AUTHOR)) ||
XMLNODE('link', XMLATTR('rel', 'self') || XMLATTR('href', 'https://h2database.com/html/newsfeed-atom.xml'), NULL) ||
XMLNODE('updated', NULL, FORMATDATETIME(C.LAST, 'yyyy-MM-dd''T''HH:mm:ss''Z''', 'en', 'GMT')) ||
GROUP_CONCAT(
XMLNODE('entry', NULL,
XMLNODE('title', XMLATTR('type', 'text'), I.TITLE) ||
XMLNODE('link', XMLATTR('rel', 'alternate') || XMLATTR('type', 'text/html') || XMLATTR('href', C.LINK), NULL) ||
XMLNODE('id', NULL, XMLTEXT(C.LINK || '/' || I.ID)) ||
XMLNODE('updated', NULL, FORMATDATETIME(I.ISSUED, 'yyyy-MM-dd''T''HH:mm:ss''Z''', 'en', 'GMT')) ||
XMLNODE('content', XMLATTR('type', 'html'), XMLCDATA(I.DESC))
)
ORDER BY I.ID DESC SEPARATOR '')
) CONTENT
FROM CHANNEL C, ITEM I
UNION
SELECT 'newsletter.txt' FILE, I.DESC CONTENT FROM ITEM I WHERE I.ID = (SELECT MAX(ID) FROM ITEM)
UNION
SELECT 'doap-h2.rdf' FILE,
XMLSTARTDOC() ||
$$<rdf:RDF xmlns="http://usefulinc.com/ns/doap#" xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xml:lang="en">
<Project rdf:about="https://h2database.com">
<name>H2 Database Engine</name>
<homepage rdf:resource="https://h2database.com"/>
<programming-language>Java</programming-language>
<category rdf:resource="http://projects.apache.org/category/database"/>
<category rdf:resource="http://projects.apache.org/category/library"/>
<category rdf:resource="http://projects.apache.org/category/network-server"/>
<license rdf:resource="http://usefulinc.com/doap/licenses/mpl"/>
<bug-database rdf:resource="https://github.com/h2database/h2database/issues"/>
<download-page rdf:resource="https://h2database.com/html/download.html"/>
<shortdesc xml:lang="en">H2 Database Engine</shortdesc>
<description xml:lang="en">
H2 is a relational database management system written in Java.
It can be embedded in Java applications or run in the client-server mode.
The disk footprint is about 1 MB. The main programming APIs are SQL and JDBC,
however the database also supports using the PostgreSQL ODBC driver by acting like a PostgreSQL server.
It is possible to create both in-memory tables, as well as disk-based tables.
Tables can be persistent or temporary. Index types are hash table and tree for in-memory tables,
and b-tree for disk-based tables.
All data manipulation operations are transactional. (from Wikipedia)
</description>
<repository>
<SVNRepository>
<browse rdf:resource="https://github.com/h2database/h2database"/>
<location rdf:resource="https://github.com/h2database/h2database"/>
</SVNRepository>
</repository>
<mailing-list rdf:resource="https://groups.google.com/g/h2-database"/>
$$ ||
GROUP_CONCAT(
XMLNODE('release', NULL,
XMLNODE('Version', NULL,
XMLNODE('name', NULL, 'H2 ' || V.VERSION) ||
XMLNODE('created', NULL, V.CREATED) ||
XMLNODE('revision', NULL, V.VERSION)
)
)
ORDER BY V.ID DESC SEPARATOR '') ||
' </Project>
</rdf:RDF>' CONTENT
FROM VERSION V