10.2 New Functionality

1. 10.2 New Functionality Dan DebrunnerSTSM, IBM djd@apache.org debrunne@us.ibm.com ApacheCon US2006

2. Agenda • Quick Overview • 10.1 Release Summary • 10.2 New Features • JDBC • SQL • Client/Server • Miscellaneous • Upgrading to 10.2 ApacheCon US2006

3. Derby Overview • Pure Java database engine • Standards based, SQL, JDBC • Embedded mode • Client/Server mode with client driver • Multi-user, multi-connection, thread safe • Full transaction support (XA, ACID compliant, all isolation levels, row locking) ApacheCon US2006

4. Derby 10.1 releases • 10.1.1.0 (Aug 2005) • First release out of incubator • Added network client • 10.1.2.1 (Nov 2005) • ~46 bug fixes, 3 minor improvements including run on MacOS with no special settings • 10.1.3.1 (June 2006) • ~100 bug fixes, 9 minor improvements ApacheCon US2006

5. 10.2 New Features • JDBC improvements • SQL improvements • Client/Server improvements • Miscellaneous ApacheCon US2006

6. JDBC Improvements • Quick List • JDBC 4.0 ApacheCon US2006

7. JDBC Quick List • Query time-out, client and embedded (Statement.setQueryTimeout) • New JDBC Escape functions {fn func} • ACOS, ASIN, ATAN, COS, SIN, TAN, PI, DEGREES, RADIANS, EXP, LOG, LOG10, CEILING, FLOOR • Updateable ResultSets • insertRow() support • Support for scrollable ResultSets • Remove requirement for FOR UPDATE clause ApacheCon US2006

8. JDBC 4.0 • JSR-221 part of Java SE 6 • Derby implements some of JDBC 4.0 • Ease of Development Support • Auto-loading of drivers • Lengthless streaming methods • Blob/Clob improvements • Statement events • If Derby 10.2 was released before Mustang GA then it is based upon the “Proposed Final Draft” or other early version and maybe be subject to later changes. ApacheCon US2006

9. Ease of development • Goal to make it easier to code & execute single ResultSet or update SQL statements • Not intended to be an ORM technology • See JDBC 4.0 section 19.1 ApacheCon US2006

10. EOD - Starting Point • Define a “data-class” that contains information that is related to a database public class Customer {public int id;public String name;public String street;public String city; public String zipCode; } • Limited support for JavaBean property style ApacheCon US2006

11. EOD - Query Class • Annotated interface that maps SQL statements to sets of the data-class public interface MyQuery extends BaseQuery {@Select(sql= “SELECT * FROM CUST WHERE ID = ?1”)DataSet<Customer> getCustomer(int id); @Select(sql= “SELECT * FROM CUST”)DataSet<Customer> getCustomers();} ApacheCon US2006

12. EOD - Executing Queries • MyQuery query = conn. createQueryObject(MyQuery.class);// Get all cutomersDataSet<Customer> custs = query.getCustomers();for (Customer c : custs) System.out.println(“Name “ + c.name);// Get one customerint id = 2456;DataSet<Customer> cust = query.getCustomer(id);for (Customer c : cust) System.out.println(“Name “ + c.name); ApacheCon US2006

13. EOD – Column name mapping • Default – case insensitive match from SQL column name in ResultSet to Java field • ID -> id, ZIPCODE -> zipCode • Or ResultColumn annotation in data class@ResultColumn(name=C_NAME)public String name; • Or use alias in select list • SELECT C_NAME AS NAME, … • One way only though, column to Java field ApacheCon US2006

14. EOD – Type mapping & NULL • Standard SQL/JDBC type to Java mapping • Table B-1 JDBC 4 specification • CHAR -> String • INTEGER -> int or Integer • NULL handling • NULL -> 0 for SQL INTEGER -> Java int • NULL -> 0 for SQL INTEGER -> Java Integer • NULL -> null for SQL VARCHAR -> String ApacheCon US2006

15. EOD – @Select Options • allColumnsMapped={true|false} • If true all columns in the select list must map to a field in the data class. If more fields in data class than columns then SQLWarning is generated. • readOnly={true|false} • Resulting DataSet is read-only • scrollable={true|false} • Is the DataSet scrollable (connected mode only) @Select(sql= “SELECT * FROM CUST WHERE ID = ?1”, readOnly=true, allColumnsMapped=true) ApacheCon US2006

16. EOD – @Select with updates • Update • DataSet<Customer> cust = query.getCustomer(id);for (Customer c : cust) { c.zipCode = ‘94105’; cust.modify();}conn.commit(); • Delete • DataSet<Customer> cust = query.getCustomer(id);for (Customer c : cust) cust.delete();conn.commit(); ApacheCon US2006

17. EOD – @Select with inserts • Insert • DataSet<Customer> cust = query.getCustomer(id);Customer nc = new Customer();nc.name = “ABC Intl”;…nc.zipCode = “34211”;cust.insert(nc);}conn.commit(); ApacheCon US2006

18. EOD - Disconnected DataSets • Annotated query @Select(sql=“SELECT …”, connected=false, tableName=“CUST”) • Empty DataSet method in query classDataSet<Customer> create(String tableName); • Modifications held locally until DataSet.sync() called ApacheCon US2006

19. EOD – Update statements • Update annotation on Query interface public interface MyQuery extends BaseQuery {@Update(sql= “UPDATE CUST SET BALANCE = BALANCE + ?2 WHERE ID = ?1”)void makePayment(int id, BigDecimal amount); @Update(sql= “DELETE FROM CUST”)int deleteAllCustomers();} ApacheCon US2006

20. EOD - Executing Update Statements • MyQuery query = conn. createQueryObject(MyQuery.class);// Register a customer payment int id = 1355;BigDecimal amount = new BigDecimal(“34.55”);query.makePayment(id, amount);// Delete all customersint id = 2456;int howMany = query.deleteAllCustomers();System.out.println( howMany + “ customers deleted”); ApacheCon US2006

21. EOD Status • Derby uses the JRE’s query generator • Mustang only Java SE 6 JRE available • Tests with build 98 show a number of bugs • Best approach to use direct case-insensitive mapping of column name to field name ApacheCon US2006

22. Auto-loading of drivers • java.sql.DriverManager supports Java SE Service Provider mechanism • JDBC drivers includes a file in jar to auto-load • META-INF/services/java.sql.Driver • No need to load driver class anymore with Java SE 6 Connection conn = DriverManager.getConnection(“jdbc:derby:db”); • Loading drivers (existing code) still works. ApacheCon US2006

23. Lengthless overrides • setCharacterStream, setAsciiStream,setBinaryStream • updateCharacterStream, updateAsciiStream, updateBinaryStream • Previously had to provide exact length of stream on streaming calls • New overrides that read until end-of-file and send complete value ApacheCon US2006

24. Blob/Clob improvements • Methods to create empty java.sql.Blob/Clob objects • Connection.createBlob() • Connection.createClob() • Methods to free resources early • Blob.free(), Clob.free() • Methods to set BLOB/CLOB as stream • PreparedStatement.setBlob(… InputStream …) • PreparedStatement.setClob(… Reader …) ApacheCon US2006

25. SQL Improvements • Quick List • SQL/XML • Optimizer Directives ApacheCon US2006

26. SQL Quick List • Unary + and – operator • WHERE C1=+? AND C2=-? • Numeric functions: • ACOS, ASIN, ATAN, COS, SIN, TAN, PI, DEGREES, RADIANS, EXP, LN, LOG, LOG10, CEIL, CEILING, FLOOR • CALL procedure in trigger • ALTER TABLE • column NULL to NOT NULL • Identity column restart value • Change DEFAULT ApacheCon US2006

27. SQL/XML Support • SQL language support only, no JDBC api. • XML type • Transient & persistent • XMLPARSE, XMLSERIALIZE • Use these operators to get values in and out of Derby as character types • XMLEXISTS, XQUERY • Run XPath expressions against XML values ApacheCon US2006

28. XML type • Represents one of: • Well-formed XML DOCUMENT • XML Sequence (transient only) • 54 • <cost>23.2</cost> <cost>39.1</cost> • one two three CREATE TABLE JIRAISSUES(ID VARACHAR(40), ISSUE XML) INSERT INTO JIRAISSUES VALUES(?,XMLPARSE (DOCUMENT CAST (? AS CLOB) PRESERVE WHITESPACE)) SELECT ID, XMLSERIALIZE(ISSUE AS CLOB) FROM JIRAISSUES ApacheCon US2006

29. XMLEXISTS - XPath • XMLEXISTS() boolean operator • Returns TRUE if the Xpath expression evaluates to a non-empty sequence • XPath expression has to be a string literal -- Show all issues that I reported. SELECT ID FROM JIRAISSUES WHEREXMLEXISTS( '//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF ISSUE) ApacheCon US2006

30. XMLQUERY - XPath • XMLQUERY operator • Takes an XPath expression and a XML value • Returns the XML Sequence resulting from the XPath • Not full XQuery support -- Show comments for all issues reported by me SELECT ID, XMLSERIALIZE(XMLQUERY( '//item/comments/comment[@author=“djd"]' PASSING BY REF ISSUE EMPTY ON EMPTY) AS CLOB)FROM JIRAISSUESWHERE XMLEXISTS( '//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF ISSUE) ApacheCon US2006

31. XML Sequence from Jira issue • Previous query returns an XML value per row, as a sequence of Jira comment tags. • Note this is not a well formed XML document • But is a valid XML value <comment author="djd" created="Mon, 13 Mar 2006 03:21:37 -0800 (PST)“level="">Statement's can't change their holdability, but I [snip]</comment><comment author="djd" created="Mon, 13 Mar 2006 15:06:32 -0800 (PST)" level="">Thanks for following up [snip]</comment> ApacheCon US2006

32. XML Setup • Xerces and Xalan parser required • Derby tries to use these from Java virtual machine • If not provided, application must include them on the class path ApacheCon US2006

33. Optimizer Directives • Ability to override optimizer’s decisions • Enabled as comments to allow SQL statement to also executed against other databases ApacheCon US2006

34. Fixed Join Order • Sets the join order for the query • Order based upon the order in the FROM list • First table is outer table • SELECT … FROM-- DERBY-PROPERTIES joinOrder = FIXEDCUSTOMERS C, ORDERS OWHERE O.CID = C.ID AND C.ID = ? ApacheCon US2006

35. Index Selection • Select the specific index to scan for a table in the from list • index=name – Use named index. • constraint=name – Use backing index for named contstraint • index=null – Use a table scan • SELECT … FROM ORDERS O, CUSTOMERS C-- DERBY-PROPERTIES index = CUSTOMER_LASTWHERE C.LASTNAME = ? AND C.ID = O.CID ApacheCon US2006

36. Client/Server Improvements • Derby’s origins as an embedded database • Derby 10.1 added an open-source JDBC client driver • Additional functionality expected by users used to client/server databases. • GRANT/REVOKE • Online Backup ApacheCon US2006

37. GRANT/REVOKE • Sub-set of the full GRANT/REVOKE functionality defined by the SQL Standard • Goal to provide an initial set of working functionality that follows the standard and would not hamper backwards compatibility in the future. ApacheCon US2006

38. Existing Authorization • 10.1 supported three modes for a connection: • No access, read-only, full-access • G/R seen as complementing this, not replacing it. Provides fine grained access on-top of the coarse grained access. • Re-named to connection access mode in documentation ApacheCon US2006

39. SQL Authorization Mode • derby.database.sqlAuthorization=true • GRANT/REVOKE statements allowed • Mode cannot be reset for a database • Basic differences • SQL Authorization Mode • User only create SQL objects in own schema • Default access limited to owner • Legacy Authorization mode (default) • Any user create any object in any schema • Default access for everyone ApacheCon US2006

40. Authentication • Derby supports various authentication models, but default is NONE. • G/R relies on authentication • Warning produced at connect time if in SQL Authorization mode, but no authentication: • WARNING 01J14: SQL authorization is being used without first enabling authentication. ApacheCon US2006

41. Permissions Supported • TABLE • INSERT • DELETE • SELECT [(column-list)], • UPDATE [(column-list)] • TRIGGER – create trigger on table • REFERENCES – create foreign key against table • VIEW • SELECT • PROCEDURE/FUNCTION • EXECUTE ApacheCon US2006

42. Users • Database owner has special rights • User that created the database • Or the user that upgraded from 10.0/10.1 • Permissions can be granted to any user name • User does not have to be known by Derby (since authentication may be outside Derby) • PUBLIC means all users • Recommend not to define users with names PUBLIC or _SYSTEM ApacheCon US2006

43. GRANTOR • Permissions can be GRANT’ed or REVOKE’d by the SQL object’s owner or by the database owner (all powerful) • No “WITH GRANT OPTION” GRANT INSERT ON SALES.CUSTOMERS TO PUBLIC GRANT DELETE, UPDATE ON SALES.CUSTOMERS TO SALESADMIN ApacheCon US2006

44. Online Backup • 10.1 supported online back but operation would block writers (though not readers) • 10.2 improves to be non-blocking ApacheCon US2006

45. Miscellaneous Improvements • Encryption improvements • IJ commands • derbyrun.jar ApacheCon US2006

46. Re-encryption • 10.1 allows encryption only at create database time • 10.2 adds the ability to: • Encrypt an existing database • Change the encryption key on an existing encrypted database • Does require double the on-disk space during the operation • Will take time, effective copy of the entire database ApacheCon US2006

47. Key change for encrypted db • Encryption key changes occurs using JDBC URL attributes • Driven by connection request that boots the database • Key storage mode cannot be changed • Algorithm cannot be changed jdbc:derby:salesdb;bootPassword=abc1234xyz;newBootPassword=new1234xyz jdbc:derby:salesdb;encryptionKey=6162636465666768;newEncryptionKey=6862636465666768 ApacheCon US2006

48. Encrypt existing database • Encryption key changes occurs using JDBC URL attributes • Driven by connection request that boots the database • Key storage mode can be selected • Algorithm can be selected jdbc:derby:salesdb;dataEncryption=true;bootPassword=abc1234xyz jdbc:derby:salesdb;dataEncryption=true;encryptionAlgorithm=DESede/CBC/NoPadding;encryptionKey=9a227d92bac34721a1bee392d ApacheCon US2006

49. Show & Describe Tables in IJ • IJ, Derby’s command line tool • Commands added to show all tables and describe a single table • Not SQL commands. ij> show tables; TABLE_SCHEM |TABLE_NAME |REMARKS ------------------------------------------------------------------------ WDD |WEB_DOCS | 1 row selected ij> describe wdd.web_docs; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ WD_ID |VARCHAR |NULL|NULL|128 |NULL |256 |NO WD_URL |VARCHAR |NULL|NULL|1000 |NULL |2000 |YES WD_CONTENT |XML |NULL|NULL|21474&|NULL |NULL |YES WD_ACCESSTIME |TIMESTAMP|6 |10 |26 |NULL |NULL |YES 4 rows selected ApacheCon US2006

50. derbyrun.jar • Executable jar with class path manifest entries to provide easy starting point • java –jar lib/derbyrun.jar command • Supported commands • ij • sysinfo • dblook • server command(NetworkServerControl commands) ApacheCon US2006