1 / 54

10.2 New Functionality

10.2 New Functionality. Dan Debrunner STSM, IBM djd@apache.org debrunne@us.ibm.com. Agenda. Quick Overview 10.1 Release Summary 10.2 New Features JDBC SQL Client/Server Miscellaneous Upgrading to 10.2. Derby Overview. Pure Java database engine Standards based, SQL, JDBC

leda
Télécharger la présentation

10.2 New Functionality

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related