Transcript Apache Derby 10.2 whats new

10.2 New Functionality
Dan Debrunner
STSM, IBM
[email protected]
[email protected]
7/7/2015
ApacheCon US2006
1
Agenda
• Quick Overview
• 10.1 Release Summary
• 10.2 New Features
– JDBC
– SQL
– Client/Server
– Miscellaneous
• Upgrading to 10.2
7/7/2015
ApacheCon US2006
2
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)
7/7/2015
ApacheCon US2006
3
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
7/7/2015
ApacheCon US2006
4
10.2 New Features
•
•
•
•
JDBC improvements
SQL improvements
Client/Server improvements
Miscellaneous
7/7/2015
ApacheCon US2006
5
JDBC Improvements
• Quick List
• JDBC 4.0
7/7/2015
ApacheCon US2006
6
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
7/7/2015
ApacheCon US2006
7
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.
7/7/2015
ApacheCon US2006
8
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
7/7/2015
ApacheCon US2006
9
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
7/7/2015
ApacheCon US2006
10
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();
}
7/7/2015
ApacheCon US2006
11
EOD - Executing Queries
• MyQuery query = conn.
createQueryObject(MyQuery.class);
// Get all cutomers
DataSet<Customer> custs = query.getCustomers();
for (Customer c : custs)
System.out.println(“Name “ + c.name);
// Get one customer
int id = 2456;
DataSet<Customer> cust = query.getCustomer(id);
for (Customer c : cust)
System.out.println(“Name “ + c.name);
7/7/2015
ApacheCon US2006
12
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
7/7/2015
ApacheCon US2006
13
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
7/7/2015
ApacheCon US2006
14
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)
7/7/2015
ApacheCon US2006
15
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();
7/7/2015
ApacheCon US2006
16
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();
7/7/2015
ApacheCon US2006
17
EOD - Disconnected DataSets
•
Annotated query
@Select(sql=“SELECT …”, connected=false,
tableName=“CUST”)
•
Empty DataSet method in query class
DataSet<Customer> create(String tableName);
•
Modifications held locally until
DataSet.sync() called
7/7/2015
ApacheCon US2006
18
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();
}
7/7/2015
ApacheCon US2006
19
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 customers
int id = 2456;
int howMany = query.deleteAllCustomers();
System.out.println(
howMany + “ customers deleted”);
7/7/2015
ApacheCon US2006
20
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 caseinsensitive mapping of column name to
field name
7/7/2015
ApacheCon US2006
21
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.
7/7/2015
ApacheCon US2006
22
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
7/7/2015
ApacheCon US2006
23
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 …)
7/7/2015
ApacheCon US2006
24
SQL Improvements
• Quick List
• SQL/XML
• Optimizer Directives
7/7/2015
ApacheCon US2006
25
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
7/7/2015
ApacheCon US2006
26
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
7/7/2015
ApacheCon US2006
27
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
7/7/2015
ApacheCon US2006
28
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 WHERE
XMLEXISTS( '//reporter[text() = "Daniel John Debrunner"]' PASSING BY REF ISSUE)
7/7/2015
ApacheCon US2006
29
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 JIRAISSUES
WHERE XMLEXISTS( '//reporter[text() = "Daniel John Debrunner"]' PASSING BY
REF ISSUE)
7/7/2015
ApacheCon US2006
30
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>
7/7/2015
ApacheCon US2006
31
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
7/7/2015
ApacheCon US2006
32
Optimizer Directives
• Ability to override optimizer’s decisions
• Enabled as comments to allow SQL
statement to also executed against other
databases
7/7/2015
ApacheCon US2006
33
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 = FIXED
CUSTOMERS C, ORDERS O
WHERE O.CID = C.ID AND C.ID = ?
7/7/2015
ApacheCon US2006
34
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_LAST
WHERE C.LASTNAME = ? AND C.ID = O.CID
7/7/2015
ApacheCon US2006
35
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
7/7/2015
ApacheCon US2006
36
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.
7/7/2015
ApacheCon US2006
37
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
7/7/2015
ApacheCon US2006
38
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
7/7/2015
ApacheCon US2006
39
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.
7/7/2015
ApacheCon US2006
40
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
7/7/2015
ApacheCon US2006
41
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
7/7/2015
ApacheCon US2006
42
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
7/7/2015
ApacheCon US2006
43
Online Backup
• 10.1 supported online back but operation
would block writers (though not readers)
• 10.2 improves to be non-blocking
7/7/2015
ApacheCon US2006
44
Miscellaneous Improvements
• Encryption improvements
• IJ commands
• derbyrun.jar
7/7/2015
ApacheCon US2006
45
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
7/7/2015
ApacheCon US2006
46
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
7/7/2015
ApacheCon US2006
47
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
7/7/2015
ApacheCon US2006
48
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
7/7/2015
ApacheCon US2006
49
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)
7/7/2015
ApacheCon US2006
50
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
7/7/2015
ApacheCon US2006
51
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
7/7/2015
ApacheCon US2006
52
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
7/7/2015
ApacheCon US2006
53
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
–
7/7/2015
http://www.onjava.com/pub/a/onjava/2006/08/02/jjdbc-4-enhancements-in-java-se-6.html
ApacheCon US2006
54