These notes describe the interface differences between Derby release 10.4 and the preceding minor release, 10.3. (IFx refers to the interfaces as defined by the interface table): 1. Security 1.1 Authentication required for Network Server shutdown (IF15, IF16) Shutting down the Network Server now supports user authentication, and in fact requires credentials when authentication is enabled. Previously, a network server running with user authentication didn't check for user credentials for server shutdown. Any client could shut down the server by calling NetworkServerControl with a shutdown command-line argument or by invoking the shutdown() method (provided the shutdown was initiated on the host running the server). Now, class NetworkServerControl supports user and password information as command-line and constructor arguments. When running a network server with user authentication, a server shutdown now requires user credentials; if the user authentication check fails, the client sees an authentication error and the running server remains intact. Note that Derby does not yet restrict the shutdown privilege to specific users: the server can be shut down by any user on the server machine who presents valid credentials. See Derby 10.4 Release Notes [1] for further details. 2. Administration 2.1 JMX Support (IF30 NEW) This project adds provisional support for Java Management Extensions (JMX) to Java DB. These features will enable the user to manage a Java DB system dynamically through a management application/GUI, without writing any code or configuration files. The described management and monitoring features are: See the functional specification [2] for the describtion of the initial JMX-related management and monitoring features for Derby. 2.2 Admin scripts in the frameworks directory has been removed. (IF2) In the 10.2 release, new and improved scripts were added in a new bin directory, intended to replace the scripts in the frameworks directory. The new scripts follow Apache conventions, and all scripts are located in a single directory, making them easier to find. Removing the old and deprecated scripts and the frameworks directory itself will eliminate a potential source of confusion and annoyance among users. See Derby 10.4 Release Notes for more details. 3. High Availability (IF5, IF6, IF31 NEW, IF32 NEW) To provide support for high availability, a replication feature with the following characteristics will be implemented: One master, one slave - A replicated database will reside in two locations and be managed by two different Java DB instances. One of these Java DB instances will have the master role for this database, and the other will have the slave role. Typically, the master and slave will run on different nodes, but this is not a requirement. Together, the master and its associated slave represent a replication pair. Roll-Forward Shipped Log - Replication will be based on shipping the Java DB transaction log from the master to the slave, and then roll forward the operations described in the log to the slave database. Asymmetrical - Only the master will process transactions. Not even read operations will be processed by the slave. Asynchronous - transactions are committed on the master without waiting for the slave. The shipping of log to the slave is performed regularly, and is completely decoupled from the transaction execution at the master. This may lead to a few lost transactions if the master crashes. Shared nothing - Apart from the network line, no hardware is assumed to be shared. Replication Granularity - The granularity for replication will be exactly one database. However, one Java DB instance may have different roles for different databases. Hence, one Java DB instance may, e.g., have the master role for one database D1 replicated to one node, the slave role for a database D2 replicated from another node, and the normal, non-replicated, role for a database D3 at same time. See Functional Specification for Replication [3] for further details. 4. SQL 4.1 Derby-Style Table Functions (IF102, IF105, IF23, IF29 NEW) Derby-style Table Functions lets Java DB treat user-written ResultSets like tables. Table Functions bolt a tabular API onto arbitrary data feeds. Through Table Functions, external data feeds can drive inserts and queries. This feature adds new SQL syntax supported by small catalog changes plus additional javadoc published in Derby's public API. Details for these changes can be found in the functional spec for Derby-Style Table Functions [4] in the sections titled "New SQL Syntax", "System Tables", and "Public API". 4.2 Add support for ROW_NUMBER() window function (IF102) The planned changes will improve Java DB performance for queries where the user wants to see the N next rows starting from row M. MySQL implements this functionality in an effective manner using 'LIMIT' and 'OFFSET' as SQL keywords. However, MySQLs LIMIT and OFFSET are not part of the SQL standard as of SQL-2003. Derby 10.4 adds the same functionality in a similarly effective fashion, while strictly adhering to the SQL standard specifying the ranking function ROW_NUMBER() to do this. See the functional specification for ROW_NUMBER() window function [5] for further details. 4.3 Unique Constraint over Nullable Column (IF102) SQL 2003 specifies Unique Constraint over nullable column as an optional feature (T591). According to the SQL spec when a unique constraint is created over nullable columns, it should consider nulls as unequal, i.e. if one or more part of the unique key is null, it shouldn't cause a constraint violation if the non null parts are equal. The null part of the key ensures that the key is unique irrespective of the value of non null part of the key. Currently, Java DB support Unique Constraint only over not null columns (mandatory feature E141-2). It is mandatory to declare the columns, participating in a unique constraint as not null, failing to do so results in an SQLException. In this release, support for creation of unique constraint over nullable columns is provided. See the functional specification for Uniques Constraint over nullable column [6] for further details. 4.4 SQL bracketed comments (/*..*/). (IF102) SQL Feature T351, SQL bracketed comments (/*..*/), has been implemented. Previously, only double dash comments was supported. Such comments ended at the first new line encountered in the SQL text. Now, one can also enclose text in /* */ characters to create either one-line or multi-line comments. Nested comments are permitted. For more details, see user documentation [7]. 4.5 New reserved keywords: NONE and CURRENT_ROLE. (IF102) The two following SQL reserved keywords are introduced: NONE and CURRENT_ROLE. This change complies with the SQL standard and prepares Derby for supporting SQL roles in a future release. For more details, see Derby 10.4 Release Notes [1]. 4.6 Default value for a column can now also be specified as CURRENT_USER or SESSION_USER. (IF102) Example: create table foo (c VARCHAR(200) DEFAULT CURRENT_USER); create table foo (c VARCHAR(200) DEFAULT SESSION_USER); 5. JDBC 5.1 Client-side Statement Cache (IF101) A JDBC statement object cache has been added in the client driver. The statement cache primarily avoids the performance penalty of goingover the network from the client to the server when preparing astatement that has already been prepared on the same connection. For more details, see writeup [8]. 5.2 Caching of session information in JDBC client driver (IF103) Caching of the isolation level and the current schema name in the client driver has been implemented. The caching lets the client driver execute getTransactionIsolation() and a new method called getCurrentSchemaName() (which is not part of the java.sql.Connection interface), without a round-trip to the server. One would ordinarily not expect the performance of getTransactionIsolation() or getCurrentSchemaName() to be critical. However, the use of connection pools (commonly found in application server environments), invalidates this assumption. To achieve this so-called product-specific code points in the DRDA protocol is used to implement piggy-backing of sesssion state on messages from server to client. See writeup [9] for more details. 6. Tools 6.1 IJ Continuation Marker. (IF7) Many users have been confused by the lack of response from IJ, the interactive SQL scripting tool, when they forget to end a statement with a semicolon, and believe that Derby is taking its sweet time answering their query, while ij is simply waiting for more input. IJ now adds a prompt, "> ", at the beginning of each line until semicolon is entered. References: [1] Release Notes for Derby 10.4.1.1 http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=RELEASE-NOTES.html [2] Functional Specification for JMX Management and Monitoring of Derby http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=jmxFuncspec.html [3] Functional Specification for Derby Replication http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=replication_funcspec_v10.html [4] Functional Spec for Derby-Style Table Functions http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=functionTables.html [5] Functional Spec for Implementation of ROW_NUMBER() in Derby http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=rownumber-fsd-16.html [6] Proposal For Implementation of T591 in Apache Derby http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=FunctionalSpec_DERBY-3330-V2.html [7] Derby Tools and Utilities Guide, Syntax for comments in ij commands http://db.apache.org/derby/docs/dev/tools/rtoolsijcomref16653.html [8] Derby JDBC client driver statement cache http://clustra.norway.sun.com/twiki/bin/viewfile/Derby/DerbyTenFourARCCaseTask?rev=1;filename=JDBCClientStatementCacheOverview.txt [9] Derby3192Writeup http://wiki.apache.org/db-derby/Derby3192Writeup