- Introduction
- Let Me Get This Straight, Apache Derby Is IBM Cloudscape?
- Development of the Apache Derby Database—Who Can Contribute and How?
- How Can IBM Sell a Product for Profit and Contribute the Same Product to the Open Source Community?
- How an Open Source Database Like Apache Derby Can Help
- Why the Need for a Local Data Store?
- Why Use a Relational Database?
- How the Apache Derby Platform Can Help Your Business
- A High-Level View of the Apache Derby Database
- The Apache Derby Components
- Developing Apache Derby Applications
This chapter is from the book
This chapter is from the book
This chapter is from the book
A High-Level View of the Apache Derby Database
Apache Derby is a relational database management system that is written in Java. Java and the open source movement go hand-in-hand. Java is an attractive language in which to write such software because its "write once, run anywhere" mantra allows for seamless portability. People that like Java will love Apache Derby because the engine is very portable: again, from Mac to mainframe, and all parts in between. For example, Apache Derby is heavily leveraged on mainframes for applications that require a lightweight meta-data storage engine.
The moment many application developers or project managers hear the word "database," they start looking for a database administrator (DBA), and questions along the line of "how do I implement an incremental backup" and so forth arise. The Apache Derby database is not for DBAs (and they’re likely not to like it very much because they’ll be looking for the wrong things). In fact, Apache Derby is unique in that it does not require professional DBAs, or DBA skills for that matter, to run. It is truly a "lights-out" database management system. Think of Apache Derby as that "black box" in the corner. But don’t let the "behind the scenes" characteristics of the Apache Derby platform fool you: there are customers today with Cloudscape databases that hold tens of gigabytes of data.
Apache Derby delivers an efficient "next to no compromise" database engine in a "fingerprint-sized" engine of only 2 MB. Along with its tiny size, it has generally minimized resource -requirements too. For example, in some environments, it can run with as little as 4 MB of RAM on a J2SE/J2EE 1.3 or later platform. In addition, the number and size of tables are pretty much limited only by the disk space available to them. (The maximum number of indexes per table is 32,767, and the maximum number of columns in a table is 1,012.) This makes Apache Derby not only easy to deploy but also easy to embed within applications as well. In fact, it literally disappears into its hosting application.
Apache Derby packs a lot of punch in its tiny 2 MB allocation. It provides a fully relational database engine, including complete support for SQL92E and JDBC (2.0 and 3.0), and partial support for SQL99. It has many advanced features that experienced database developers may be accustomed to with larger databases, such as:
Identity columns (sometimes referred to as auto-incremented columns) for key generation
Fast query compilation through a cost-based optimizer that supports hash joins, sort avoidance, and row- or table-level locking based on a percentage of data selected
Automated statistics collection, automatic table reorganization and space reclamation, programmatic backups (no DBA required), and more
Binary large objects (BLOBs) for complex non-traditional data handling
Referential constraints (primary and foreign key), general constraints (unique and check), and default values for business rules enforcement
Transactional processing (it’s ACID-compliant; see the "Why Use a Relational Database?" section for more details) with isolation levels such as repeatable-read and uncommitted-read
Objects to encapsulate business logic and promote code reuse and best practices, such as stored procedures, user-defined functions (UDFs), and triggers
Views
Bulk load utility
Scrollable cursors for more efficient result set processing
Concurrent access by any number of program threads or processes
Multi-user access
JDBC (both an embedded driver and an external driver that’s the same one used with DB2 UDB for z/OS and DB2 UDB for Linux, Unix, and Windows—called the Java Common Client), ODBC, CLI, .NET, PHP, and Perl connectivity (add-on interfaces are available from the IBM Cloudscape Web site). In fact, IBM contributed the Apache Derby Network Client during the writing of this book—learn more at: http://incubator.apache.org/derby/faq.html#netclient.
Rich security features, such as signed Java Archive (.JAR) files, encryption of stored data (through the Java Cryptography Extension, or JCE, and other providers), ability to run with a Java 2 security manager enabled, optional LDAP authentication, and more
Unlimited table size (though many operating system’s 2 GB file limits could theoretically come into play because Cloudscape stores each table as a separate file)
The IBM Integration Plug-in for Apache Derby; a free Eclipse-based plug-in to implement Apache Derby functionality as user interface component (http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0501cline/)
National language support for program integrated information (for example, error messages, program output, and so on), and documentation for the following languages: Spanish (es), German (de_DE), French (fr), Italian (it), Brazilian Portuguese (pt_BR), Korean (ko_KR), Japanese (ja_JP), Traditional Chinese (zh_TW), and Simplified -Chinese (zh_CN)
Officially, Apache Derby supports any standard JVM (J2SE 1.3 or higher). Whereas most applications detail operating system support prerequisites, the JVM is really the run-time platform for this database.
All Apache Derby components and future enhancements will adhere to the Apache Derby Charter. This charter is a statement of direction or purpose. It dictates a course of action that everyone involved in this community will adhere to in order to create the best possible underlying data store for their applications. Specifically, it decrees that any contributions to the Apache Derby code base must be completely written in Java, easy to use, have a small footprint, and be secure.
Details on SQL Support in Apache Derby and IBM Cloudscape
Apache Derby and IBM Cloudscape implement the SQL92E language standard and many features that are found in the SQL99 specification (with extensions for Java). Although not complete, the following list details most of the features that you’re likely to leverage when building your own application:
Basic database types, such as: CHAR, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NUMERIC, REAL, SMALLINT
Datetime data types (from SQL92T): DATE, TIME, TIMESTAMP (with JDBC date/time escape syntax)
Other types: BIGINT, VARCHAR, CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, LONG VARCHAR, LONG VARCHAR FOR BIT DATA, BLOB, CLOB
Basic operations: +,*,-,/,unary +,unary -
Basic comparisons: <,>,<=,>=,<>,=
Datetime literals
Built-in functions: ABS or ABSVAL, CAST, LENGTH, CONCATENATION (||), -NULLIF and CASE expressions, CURRENT_DATE, CURRENT_ISOLATION, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER, DATE, DAY, HOUR, IDENTITY_VAL_LOCAL, LOCATE, LCASE or LOWER, LTRIM, MINUTE, MOD, MONTH, RTRIM, SECOND, SESSION_USER, SQRT, -SUBSTR, TIME, TIMESTAMP, UCASE or UPPER, USER, YEAR
Basic predicates: BETWEEN, LIKE, NULL
Quantified predicates: IN, ALL, ANY or SOME, EXISTS
CREATE and DROP SCHEMA
CREATE and DROP TABLE
Check constraints
ALTER TABLE: ADD COLUMN and ADD or DROP CONSTRAINT
CREATE and DROP VIEW
Constraints: NOT NULL, UNIQUE, PRIMARY KEY, CHECK, FOREIGN KEY
Cascade delete
Column defaults
Delimited identifiers
Updatable cursors (through JDBC)
Dynamic SQL (through JDBC)
INSERT, UPDATE, and DELETE statements
Positioned updates and deletes
WHERE qualifications
GROUP BY
HAVING
ORDER BY
UNION and UNION ALL
Subqueries as expressions (from SQL92F)
Joins in the WHERE clause
Joins (SQL92T): INNER, RIGHT OUTER, LEFT OUTER, named column join, conditional join
Aggregate functions (with DISTINCT): AVG, COUNT, MAX, MIN, SUM
SELECT *, SELECT table.* (SQL92T), SELECT DISTINCT, select expressions
Named select columns
SQLSTATE
UNION in views (SQL92T)
CAST (SQL92T)
INSERT expressions (SQL92T): insert into T2 (COL) select col from T1
VALUES expressions: select * from (values (1, 2)) as foo(x, y), and so on
Triggers