10 2 new functionality n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
10.2 New Functionality PowerPoint Presentation
Download Presentation
10.2 New Functionality

Loading in 2 Seconds...

play fullscreen
1 / 54

10.2 New Functionality - PowerPoint PPT Presentation


  • 137 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about '10.2 New Functionality' - leda


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
10 2 new functionality

10.2 New Functionality

Dan DebrunnerSTSM, IBM

djd@apache.org

debrunne@us.ibm.com

ApacheCon US2006

agenda
Agenda
  • Quick Overview
  • 10.1 Release Summary
  • 10.2 New Features
    • JDBC
    • SQL
    • Client/Server
    • Miscellaneous
  • Upgrading to 10.2

ApacheCon US2006

derby overview
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

derby 10 1 releases
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

10 2 new features
10.2 New Features
  • JDBC improvements
  • SQL improvements
  • Client/Server improvements
  • Miscellaneous

ApacheCon US2006

jdbc improvements
JDBC Improvements
  • Quick List
  • JDBC 4.0

ApacheCon US2006

jdbc quick list
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

jdbc 4 0
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

ease of development
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

eod starting point
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

eod query class
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

eod executing queries
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

eod column name mapping
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

eod type mapping null
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

eod @select options
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

eod @select with updates
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

eod @select with inserts
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

eod disconnected datasets
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

eod update statements
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

eod executing update statements
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

eod status
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

auto loading of drivers
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

lengthless overrides
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

blob clob improvements
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

sql improvements
SQL Improvements
  • Quick List
  • SQL/XML
  • Optimizer Directives

ApacheCon US2006

sql quick list
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

sql xml support
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

xml type
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

xmlexists xpath
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

xmlquery xpath
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

xml sequence from jira issue
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

xml setup
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

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

ApacheCon US2006

fixed join order
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

index selection
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

client server improvements
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

grant revoke
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

existing authorization
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

sql authorization mode
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

authentication
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

permissions supported
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

users
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

grantor
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

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

ApacheCon US2006

miscellaneous improvements
Miscellaneous Improvements
  • Encryption improvements
  • IJ commands
  • derbyrun.jar

ApacheCon US2006

re encryption
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

key change for encrypted db
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

encrypt existing database
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

show describe tables in ij
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

derbyrun jar
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

upgrading to 10 2
Upgrading to 10.2
  • Continues Derby’s simple upgrade model
  • Two modes
    • Soft Upgrade
    • Hard Upgrade
  • Quick operation, changes made to database catalogs, does not require scanning any data.
  • Remember – always good to back-up your database before any upgrade

ApacheCon US2006

soft upgrade
Soft Upgrade
  • Run existing application using 10.2 jar files
  • Can switch back to previous release
  • Some new functionality exposed
    • Most runtime functionality, JDBC api changes, new builtin functions (SIN etc.)
  • Other new functionality blocked
    • Anything that would add on-disk information that would not be understood by previous releases

ApacheCon US2006

hard upgrade
Hard Upgrade
  • Boot database with JDBC connection that includes the upgrade=true attribute
  • Allows full use of 10.2
  • Cannot revert database to previous release

ApacheCon US2006

references
References
  • Download
    • http://db.apache.org/derby/derby_downloads.html
  • Documentation
    • http://db.apache.org/derby/manuals/index.html
  • OnJava.com JDBC 4.0 article
    • JDBC 4.0 Enhancements in Java SE 6
    • http://www.onjava.com/pub/a/onjava/2006/08/02/jjdbc-4-enhancements-in-java-se-6.html

ApacheCon US2006