10.2 New Functionality

1
10.2 New Functionality

• Dan DebrunnerSTSM, IBM
• djd_at_apache.org
• debrunne_at_us.ibm.com

2
Agenda

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

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)

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

5
10.2 New Features

• JDBC improvements
• SQL improvements
• Client/Server improvements
• Miscellaneous

6
JDBC Improvements

• Quick List
• JDBC 4.0

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

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.

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

10
EOD - Starting Point

• Define a data-class that contains information
  that is related to a database
• public class Customer public int idpublic
  String namepublic String streetpublic String
  city
• public String zipCode
• Limited support for JavaBean property style

11
EOD - Query Class

• Annotated interface that maps SQL statements to
  sets of the data-class
• public interface MyQuery extends BaseQuery
  _at_Select(sql SELECT FROM CUST WHERE ID
  ?1)DataSetltCustomergt getCustomer(int id)
• _at_Select(sql SELECT FROM CUST)DataSetltCust
  omergt getCustomers()

12
EOD - Executing Queries

• MyQuery query conn. createQueryObject(MyQuery.cl
  ass)// Get all cutomersDataSetltCustomergt
  custs query.getCustomers()for (Customer c
  custs) System.out.println(Name
  c.name)// Get one customerint id
  2456DataSetltCustomergt cust query.getCustomer(i
  d)for (Customer c cust) System.out.println(
  Name c.name)

13
EOD Column name mapping

• Default case insensitive match from SQL column
  name in ResultSet to Java field
• ID -gt id, ZIPCODE -gt zipCode
• Or ResultColumn annotation in data
  class_at_ResultColumn(nameC_NAME)public String
  name
• Or use alias in select list
• SELECT C_NAME AS NAME,
• One way only though, column to Java field

14
EOD Type mapping NULL

• Standard SQL/JDBC type to Java mapping
• Table B-1 JDBC 4 specification
• CHAR -gt String
• INTEGER -gt int or Integer
• NULL handling
• NULL -gt 0 for SQL INTEGER -gt Java int
• NULL -gt 0 for SQL INTEGER -gt Java Integer
• NULL -gt null for SQL VARCHAR -gt String

15
EOD _at_Select Options

• allColumnsMappedtruefalse
• 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.
• readOnlytruefalse
• Resulting DataSet is read-only
• scrollabletruefalse
• Is the DataSet scrollable (connected mode only)
• _at_Select(sql SELECT FROM CUST WHERE ID
  ?1, readOnlytrue, allColumnsMappedtrue)

16
EOD _at_Select with updates

• Update
• DataSetltCustomergt cust query.getCustomer(id)fo
  r (Customer c cust) c.zipCode 94105
  cust.modify()conn.commit()
• Delete
• DataSetltCustomergt cust query.getCustomer(id)fo
  r (Customer c cust) cust.delete()conn.commi
  t()

17
EOD _at_Select with inserts

• Insert
• DataSetltCustomergt cust query.getCustomer(id)Cu
  stomer nc new Customer()nc.name ABC
  Intlnc.zipCode 34211cust.insert(nc)
  conn.commit()

18
EOD - Disconnected DataSets

• Annotated query
• _at_Select(sqlSELECT , connectedfalse,
  tableNameCUST)
• Empty DataSet method in query classDataSetltCustom
  ergt create(String tableName)
• Modifications held locally until DataSet.sync()
  called

19
EOD Update statements

• Update annotation on Query interface
• public interface MyQuery extends BaseQuery
  _at_Update(sql UPDATE CUST SET BALANCE
  BALANCE ?2 WHERE ID ?1)void makePayment(int
  id, BigDecimal amount)
• _at_Update(sql DELETE FROM CUST)int
  deleteAllCustomers()

20
EOD - Executing Update Statements

• MyQuery query conn. createQueryObject(MyQuery.cl
  ass)// Register a customer payment int id
  1355BigDecimal amount new BigDecimal(34.55)
  query.makePayment(id, amount)// Delete all
  customersint id 2456int howMany
  query.deleteAllCustomers()System.out.println(
  howMany customers deleted)

21
EOD Status

• Derby uses the JREs 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

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(jdb
  cderbydb)
• Loading drivers (existing code) still works.

23
Lengthless overrides

• setCharacterStream, setAsciiStream,setBinaryStrea
  m
• 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

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 )

25
SQL Improvements

• Quick List
• SQL/XML
• Optimizer Directives

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

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

28
XML type

• Represents one of
• Well-formed XML DOCUMENT
• XML Sequence (transient only)
• 54
• ltcostgt23.2lt/costgt ltcostgt39.1lt/costgt
• 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

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(
  '//reportertext() "Daniel John Debrunner"'
  PASSING BY REF ISSUE)

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_at_authordjd"' PASSING
  BY REF ISSUE EMPTY ON EMPTY) AS CLOB)FROM
  JIRAISSUESWHERE XMLEXISTS( '//reportertext()
  "Daniel John Debrunner"' PASSING BY REF ISSUE)

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
• ltcomment author"djd" created"Mon, 13 Mar 2006
  032137 -0800 (PST)level""gtStatement's can't
  change their holdability, but I snip
  lt/commentgtltcomment author"djd" created"Mon, 13
  Mar 2006 150632 -0800 (PST)" level""gtThanks
  for following up snip lt/commentgt

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

33
Optimizer Directives

• Ability to override optimizers decisions
• Enabled as comments to allow SQL statement to
  also executed against other databases

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 ?

35
Index Selection

• Select the specific index to scan for a table in
  the from list
• indexname Use named index.
• constraintname Use backing index for named
  contstraint
• indexnull Use a table scan
• SELECT FROM ORDERS O, CUSTOMERS C--
  DERBY-PROPERTIES index CUSTOMER_LASTWHERE
  C.LASTNAME ? AND C.ID O.CID

36
Client/Server Improvements

• Derbys 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

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.

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

39
SQL Authorization Mode

• derby.database.sqlAuthorizationtrue
• 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

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.

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

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

43
GRANTOR

• Permissions can be GRANTed or REVOKEd by the
  SQL objects 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

44
Online Backup

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

45
Miscellaneous Improvements

• Encryption improvements
• IJ commands
• derbyrun.jar

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

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
• jdbcderbysalesdbbootPasswordabc1234xyznewBoo
  tPasswordnew1234xyz
• jdbcderbysalesdbencryptionKey6162636465666768
  newEncryptionKey6862636465666768

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
• jdbcderbysalesdbdataEncryptiontruebootPasswo
  rdabc1234xyz
• jdbcderbysalesdbdataEncryptiontrueencryption
  AlgorithmDESede/CBC/NoPaddingencryptionKey9a22
  7d92bac34721a1bee392d

49
Show Describe Tables in IJ

• IJ, Derbys command line tool
• Commands added to show all tables and describe a
  single table
• Not SQL commands.
• ijgt show tables
• TABLE_SCHEM TABLE_NAME
  REMARKS
• --------------------------------------------------
  ----------------------
• WDD WEB_DOCS
  
• 1 row selected
• ijgt describe wdd.web_docs
• COLUMN_NAME TYPE_NAMEDECNUMCOLUMCO
  LUMN_DEFCHAR_OCTEIS_NULL
• --------------------------------------------------
  ----------------------------
• WD_ID VARCHAR NULLNULL128
  NULL 256 NO
• WD_URL VARCHAR NULLNULL1000
  NULL 2000 YES
• WD_CONTENT XML NULLNULL21474NU
  LL NULL YES
• WD_ACCESSTIME TIMESTAMP6 10 26
  NULL NULL YES

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)

51
Upgrading to 10.2

• Continues Derbys 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

52
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

53
Hard Upgrade

• Boot database with JDBC connection that includes
  the upgradetrue attribute
• Allows full use of 10.2
• Cannot revert database to previous release

54
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/jjdb
  c-4-enhancements-in-java-se-6.html