newsfeed.sql

来自「非常棒的java数据库」· SQL 代码 · 共 388 行 · 第 1/2 页

</li><li>The column INFORMATION_SCHEMA.TRIGGERS.SQL is now correct.
</li><li>This database could not be used in applets. 
</li><li>The index file is now re-created automatically when required.
</li></ul>
For future plans, see the ''Roadmap'' page at
http://www.h2database.com/html/roadmap.html
');

INSERT INTO ITEM VALUES(33,
'New version available: 1.0.63 (2007-12-02)', '2007-12-02 12:00:00',
'A new version of H2 is available for <a href="http://www.h2database.com">download</a>.
(You may have to click ''Refresh'').
<br />
<b>Changes and new functionality:</b>
<ul><li>Performance optimization for IN(...) and IN(SELECT...), 
    currently disabled by default. To enable, use java -Dh2.optimizeInJoin=true
</li><li>The H2 Console has been translated to Ukrainian by Igor Dobrovolskyi. Thanks a lot! 
</li><li>The SecurePassword example has been improved.
</li><li>Improved FTP server: now the PORT command is supported.
</li><li>New function TABLE_DISTINCT. 
</li></ul>
<b>Bugfixes:</b>
<ul><li>Certain setting in the Server didn''t work.
</li><li>In timezones where the summer time saving limit is at midnight, 
  some dates did not work in some virtual machines, 
    for example 2007-10-14 in Chile, using the Sun JVM 1.6.0_03-b05.
</li><li>The native fulltext search was not working properly after re-connecting. 
</li><li>Temporary views (FROM(...)) with UNION didn''t work if nested. 
</li><li>Using LIMIT with values close to Integer.MAX_VALUE didn''t work. 
</li></ul>
For future plans, see the ''Roadmap'' page at
http://www.h2database.com/html/roadmap.html
');

INSERT INTO ITEM VALUES(32,
'New version available: 1.0.62 (2007-11-25)', '2007-11-25 12:00:00',
'A new version of H2 is available for <a href="http://www.h2database.com">download</a>.
(You may have to click ''Refresh'').
<br />
<b>Changes and new functionality:</b>
<ul><li>Large updates and deletes are now supported.
</li><li>Google Android is supported.
</li><li>Large CSV operations are now faster.
</li><li>A patch for Apache DDL Utils is available.
</li><li>Eduardo Velasques has translated H2 to Brazilian Portuguese.
</li><li>Now using custom toString() for JDBC objects.
</li><li>The setting h2.emergencySpaceInitial is now 256 KB.
</li></ul>
<b>Bugfixes:</b>
<ul><li>Creating a table from GROUP_CONCAT didn''t always work.
</li><li>CSV: Using an empty field delimiter didn''t work.
</li><li>Nested temporary views with parameters didn''t always work.
</li><li>Cluster mode: could not connect if only one server was running.
</li><li>ARRAY values are now sorted as in PostgreSQL.
</li><li>The console did not display multiple spaces correctly.
</li><li>Duplicate column names were not detected when renaming columns.
</li><li>The H2 Console now also supports -ifExists.
</li><li>Changing a user with a schema made the schema inaccessible.
</li><li>Referential integrity checks didn''t lock the referenced table.
</li><li>Now changing MVCC too late throws an Exception.
</li></ul>
For future plans, see the ''Roadmap'' page at
http://www.h2database.com/html/roadmap.html
');

INSERT INTO ITEM VALUES(31,
'New version available: 1.0.61 (2007-11-10)', '2007-11-10 12:00:00',
'A new version of H2 is available for <a href="http://www.h2database.com">download</a>.
(You may have to click ''Refresh'').
<br />
<b>Changes and new functionality:</b>
<ul><li>Read-only databases in zip (or jar) files are now supported: jdbc:h2:zip:c:/temp/db.zip!/test
</li><li>File access is now done using an extensible API. Additional file systems are easy to implement.
</li><li>Descending indexes are supported.
</li><li>The Lucene fulltext search is included in the h2.jar.
</li><li>MODE is now a database level setting (not global).
</li><li>Vlad Alexahin has translated H2 Console to Russian. Thanks a lot!
</li><li>INSTR, LOCATE: backward searching is now supported by using a negative start position.
</li><li>CREATE SEQUENCE: New option CACHE (number of pre-allocated numbers).
</li><li>Converting decimal to integer now rounds like MySQL and PostgreSQL.
</li><li>Math operations using only parameters are now interpreted as decimal.
</li><li>MVCC: The system property h2.mvcc has been removed.
</li></ul>
<b>Bugfixes:</b>
<ul><li>ResultSetMetaData.getColumnDisplaySize is now calculated correctly.
</li><li>A few MVCC bugs have been fixed.
</li><li>The code coverage is now at 83%.
</li></ul>
For future plans, see the ''Roadmap'' page at
http://www.h2database.com/html/roadmap.html
');

INSERT INTO ITEM VALUES(30,
'New version available: 1.0.60 (2007-10-20)', '2007-10-20 12:00:00',
'A new version of H2 is available for <a href="http://www.h2database.com">download</a>.
(You may have to click ''Refresh'').
<br />
<b>Changes and new functionality:</b>
<ul><li>User defined aggregate functions are now supported.
</li><li>New Italian translation from PierPaolo Ucchino. Thanks a lot!
</li><li>CSV: New methods to set the escape character and field delimiter in the Csv tool and the CSVWRITE and CSVREAD methods.
</li><li>CSVREAD, RUNSCRIPT and so on now support URLs as well, using
    URL.openStream(). Example: select * from csvread(''jar:file:///c:/temp/test.jar!/test.csv'');
</li></ul>
<b>Bugfixes:</b>
<ul><li>Prepared statements could not be used after data definition statements (creating tables and so on). Fixed.
</li><li>PreparedStatement.setMaxRows could not be changed to a higher value after the statement was executed.
</li><li>Linked tables: now tables in non-default schemas are supported as well 
</li><li>JdbcXAConnection: starting a transaction before getting the connection didn''t switch off autocommit.
</li><li>Server.shutdownTcpServer was blocked when first called with force=false and then force=true.
    Now documentation is improved, and it is no longer blocked.
</li><li>Stack traces did not include the SQL statement in all cases where they could have. 
    Also, stack traces with SQL statement are now shorter.
</li><li>The H2 Console could not connect twice to the same H2 embedded database at the same time. Fixed.
</li></ul>
For future plans, see the ''Roadmap'' page at
http://www.h2database.com/html/roadmap.html
');

INSERT INTO ITEM VALUES(29,
'New version available: 1.0.59 (2007-10-03)', '2007-10-03 12:00:00',
'A new version of H2 is available for <a href="http://www.h2database.com">download</a>.
(You may have to click ''Refresh'').
<br />
<b>Changes and new functionality:</b>
<ul><li>Fulltext search is now documented (see Tutorial).
</li><li>H2 Console: Progress information when logging into a H2 embedded database.
</li><li>SCRIPT: the SQL statements in the result set now include the terminating semicolon.
</li></ul>
<b>Bugfixes:</b>
<ul><li>If the process was killed while the database was running, 
    sometimes the database could not be opened.
</li><li>Comparing columns with constants that are out of range works again.
</li><li>When the data type was unknown in a subquery, sometimes the wrong exception was thrown.
</li><li>Multi-threaded kernel (MULTI_THREADED=1): A synchronization problem has been fixed.
</li><li>A PreparedStatement that was cancelled could not be reused. 
</li><li>When the database was closed while logging was disabled (LOG 0), 
    re-opening the database was slow.
</li><li>The Console did not always refresh the table list when required.
</li><li>When creating a table using CREATE TABLE .. AS SELECT, 
    the precision for some data types was wrong in some cases.
</li><li>When using the (undocumented) in-memory file system 
    (jdbc:h2:memFS:x or jdbc:h2:memLZF:x), and using multiple connections, 
    a ConcurrentModificationException could occur. 
</li><li>REGEXP compatibility: now Matcher.find is used.
</li><li>When using a subquery with group by as a table, some columns could not be used.
</li><li>Views with subqueries as tables and queries with nested subqueries as tables did not always work.
</li></ul>
For future plans, see the new ''Roadmap'' page on the web site.
');

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('version', '0.3') || XMLATTR('xmlns', 'http://purl.org/atom/ns#') || XMLATTR('xml:lang', C.LANGUAGE),
        XMLNODE('title', XMLATTR('type', 'text/plain') || XMLATTR('mode', 'escaped'), C.TITLE) ||
        XMLNODE('author', NULL, XMLNODE('name', NULL, C.AUTHOR)) ||
        XMLNODE('link', XMLATTR('rel', 'alternate') || XMLATTR('type', 'text/html') || XMLATTR('href', C.LINK), NULL) ||
        XMLNODE('modified', NULL, FORMATDATETIME(C.LAST, 'yyyy-MM-dd''T''HH:mm:ss.SSS', 'en', 'GMT')) ||
        GROUP_CONCAT(
            XMLNODE('entry', NULL,
                XMLNODE('title', XMLATTR('type', 'text/plain') || XMLATTR('mode', 'escaped'), 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('issued', NULL, FORMATDATETIME(I.ISSUED, 'yyyy-MM-dd''T''HH:mm:ss.SSS', 'en', 'GMT')) ||
                XMLNODE('modified', NULL, FORMATDATETIME(I.ISSUED, 'yyyy-MM-dd''T''HH:mm:ss.SSS', 'en', 'GMT')) ||
                XMLNODE('content', XMLATTR('type', 'text/html') || XMLATTR('mode', 'escaped'), 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)