Title: Open Source Databases
1Open Source Databases
2Content
- Purpose
- Survey of available open source databases and
tools - Strong emphasis on HSqlDb and MySQL
- Feature comparison
- Content
- SQL, JDBC basics (optional)
- Open Source databases
- Introduction (Java non-Java)
- Access and administration tools
- Demo
3SQL
- DDL (Data Definition Language)
- create, alter, drop
- DML (Data Manipulation Language)
- CRUD (select, insert, update, delete)
- DCL (Data Control Language)
- connect, grant, revoke
4JDBC drivers
- Type 1 JDBC to ODBC bridge
- Usually slow, meant as transient solution
- Early drivers designed to leverage existing ODBC
drivers - Type 2 Java wrappers around native DB interfaces
- No longer very common
- Using JNI
- Type 3 Pure Java drivers
- Use middleware component to translate JDBC-calls
to a DB-generic communication protocol - Type 4 Native Java drivers
- Driver designed specifically to interact with the
DB - The most common
5JDBC connections
- DriverManager
- Introduced in JDBC 1.0 API
- Connection retrieved with hard-coded URL
- DataSource
- Introduced in the JDBC 2.0 Optional Package API
- Preferred method
- Underlying DataSource transparent to the
application - ConnectionPoolDataSource
- XADataSource
6JDBC connections DriverManager
- public class DbConnector
-
- private static String driver
"oracle.jdbc.driver.OracleDriver" - private static String url "jdbcoraclethin_at_A
MISNT151521LUDO" - private static Connection connection null
- private static void createConnection
-
- try
-
- if (connection null
connection.isClosed()) -
- Class.forName(driver).newInstance()
- connection
- DriverManager.getConnection(url,adf6,
adf6) -
- catch (Exception e) // omitted here
-
7JDBC connections DataSource
- A DataSource
- a factory to create database connections
- centralizes database connection-related data
- implementation of javax.sql.DataSource
- three implementations available
- Standard implementation
- Connection pooling implementation
- Distributed transaction implementation
8JDBC JNDI pooling
- DataSources JNDI
- DataSources are designed for use with JNDI
- Configured within JNDI provider bound to JNDI
nameContext context new InitialContext()Data
Source ds (DataSource)
context.lookup(jdbc/TestDataSource)Connection
con ds.getConnection(sa, )Program only
knows about username and password! - Usage in Servlets
- Get connections in doGet() en doPost()
- Get DataSource in init() ? provides connections
from pool
9JDBC pooling
- A pooled DataSource stores connections in a pool
- Pool cache, BUT pool is prepopulated with a
of cons - If all connections are used1. May fail2. May
add more connections to pool - Purpose increase in performance
10JDBC 1.0 ? 2.0
- Scrollable result sets
- Batch updates (multiple insert/update/delete in 1
request) - Advanced data types (BLOB, CLOB)
- Rowsets, for JavaBeans (rowset set of rows
bean) - JNDI for obtaining connection
- Connection pooling
- Distributed transaction support
- Two-phase commit used by the JTS API.
- Additional minor additions/improvements
11JDBC 2.0 ? 3.0
- Savepoint support
- Reuse of prepared statements by connection pools
- Connection pool configuration
- Retrieval of parameter metadata
- Retrieval of auto-generated keys
- Ability to have multiple open ResultSet objects
- Passing parameters to CallableStatement objects
by name - Holdable cursor support
- Boolean data type
- Making internal updates to data in BLOB en CLOB
objects
12JDBC 2.0 ? 3.0 (cont.)
- Retrieving and updating the object referenced by
a Ref object - Updating of columns containing BLOB, CLOB, ARRAY
and REF types - DATALINK/URL data type
- Transform groups and type mapping
- Relationship between the JDBC SPI (Service
Provider Interface) and the Connector
architecture - DatabaseMetadata APIs
13Enterprise demands on databases
- A JDBC Compliant driver must support SQL-92 at
the entry level ? entry level SQL-92 is a must - Transaction support
- may also be handled in business tier
- JDBC driver that is JDBC gt 2.0 compliant
- Includes support for DataSources
- necessary for pooling ? critical for scaling
performance - Preferred JDBC gt 3.0 compliant
- Triggers, views and stored procedures
- BLOB, CLOB
- Support, user-base
14Development models
15Development models
Closed Source (Oracle, DB2) MySQL, Berkeley DB PostgreSQL, HSQL, Firebird
Development team Company Coordinated by company Volunteer developers all over the world
Distribution Usually reseller agreement required Free for open source projects Fee for closed source projects Freely distributable
License Commercial Dual licensed Open Source
Availability Purchase or limited eval. Free download Free download
16Java databases
17Java vs. non-Java databases
Java
Java
non-Java
- Platform independent
- Small ? so may be embedded in application e.g. as
part of a .war - Small ? but can handle large databases
- Some versions allow memory-based tables
- Easy to set-up, configure, maintain.
- HSqlDb, Derby (Apache), Axion, McKoi and
Xindice.
- Better scalability
- Better support for very large applications
- Firebird, GNU SQL, MySQL, PostGreSQL, Max DB (
SAP DB), Ingres, Berkely DB
18Java databases Axion
- Product of Tigris.org (known from GEF)
- Embedded Java DB with file- or memory-based
tables - Currently no server mode
- Nov. 2003 The Apache DB Project has accepted
Axion's proposal to become a part of the Apache
Software Foundation. - Features http//axion.tigris.org/features.html
19Java databases HSqlDb
- Continuation of Thomas Muellers Hypersonic DB
- Yet another example of successful OS project
- Basically Axion ?
- Same functionality many useful advanced
features - JDBC gt 2.0, full transaction support
- Additional DDL commands alter table create
view - Additional DML commands outer join, group by ,
grant/revoke - Standard DB with JBoss AS
- License BSD-based
- Default in-memory table regenerated from SQL
script - transaction log of SQL statements (executed at
startup)
20Java databases HSqlDb (cont.)
- Features
- In-memory and disk-based tables
- Transaction support
- JDBC 2.0 (partly 3.0)
- Five modes
- embedded (in-memory mode)
- standalone
- servlet
- server
- HTTP server
21Java databases HSqlDb (cont.)
- If URL is jdbchsqldbc/path/databaseName
- ? creation of database databaseName in c/path
- If URL is jdbchsqldb.
- ? database not saved to disk!
- Behaviour of in-memory tables rebuilt with logs
can be changed on a per-table basis - CREATE MEMORY CACHED TEMP TEXT TABLE
name - Memory ? persistent in-memory table
- Cache ? disk based table with cache
- Text ? table saved as CSV-based file
- Temp ? non-persistent in-memory table
22Java databases HSqlDb (cont.)
- Drawback no keyword for modification of this
kind of storage type limits script portability - HSqlDb comes with nice manager application, which
may be used with other JDBC-supported databases - Tool available from the JBoss management page!
- Personal remark ZWH
- Sometimes it seems confusing to me (with respect
to the storage types) if the JBoss EJB container
has added data as expected there seems to be a
mismatch what the tool shows you and what is
actually in the (in-memory) database.
23Java databases HSqlDb (cont.)
24Java databases Derby
- Formerly IBMs Cloudscape
- At incubation at ASF http//incubator.apache.org/
derby/ - Features
- Easy to use
- Small footprint
- Standards based
- SQL syntax, transaction management, concurrency,
triggers, and online backups. - Secure
- Secure data management appropriate to environment
the engine is executing in - Both as embedded database engine and network
server
25Java databases McKoi
- License GPL and maintained by individual
developer - Home _at_ http//mckoi.com/database/
- Features
- Transaction support
- both embedded and client/server mode
- Full JDBC 3.0 support
- Comparison
- More traditional than HSqlDb and more features
than Axion - Stores data files on disk and caches in memory
like HSqlDb - License (including JDBC driver) GPL
- Results in incompatibility with Apache
license/products!!!
26Java databases Xindice
- Native XML DB, stores compressed XML documents
- Donated to ASF by former xmlDB team
- XPath is query language and APIs support DOM
SAX - No DTD or Schema support for documents in DB yet!
- Why not relational?
- O/R impedance mismatch (would need Castor XML or
similar)! - Why not object DB?
- Object DB is application bound
27Evaluation Java databases
- McKoi and HSqlDb richer feature set compared to
Axion - Derby is in incubation at ASF
- McKois GPL license is limiting
- HSqlDb is the Java database of choice
- JBoss database
- Largest Java-database community
- Xindice is a native XML database
- comes with pros and cons of native XML DB
- Well suited for document-style oriented data
- No need to map from relational DB to XML
- Less flexible queries
28non-Java databases
29Non-Java databases GNU SQL
- Seems to be dead (latest news from 28-09-1998)
30Non-Java databases Firebird
- Code base of Borlands Interbase, OSS since 2000
- Forked off ? Interbase and Firebird
- Well documented
Category Relational database with JDBC driver
URL http//firebird.sourceforge.net/
Supported platforms Win 9x/2000/XP, Linux, Unix and MacOS X
License Interbase Public License ( Mozilla Public L.)
Features SQL 92
31Non-Java databases MySQL
- Most common open source database (from NASA to
Yahoo) - Back-up by Swedish company MySQL AB
- Version 3.2x still widely used, 4.1.x now
available and stable - Stored procedures and triggers expected for 5.0
- JDBC driver
- Used to be MM.MySQL ? Connector/J, available form
MySQL site
Category Relational database with JDBC driver
URL http//www.mysql.com/
Supported platforms Win 98/2000/XP, Linux, Unix and MacOS X
License GPL server, LPGL client components
Features Fastreliable, recently transaction support
32Non-Java databases MySQL (cont.)
- MySQL API choose your engine!
- ISAM MyISAM, no transaction nor foreign key
support - ISAM
- fast for reads gtgt updates, but not fault-tolerant
nor transaction support - MyISAM
- ISAM (table locking), but requires more
maintenance - HEAP
- In-memory, fast but volatile
- InnoDB Berkely BDB, transaction foreign key
support, but slower! - Implement your own ?
- Suggestion for rainy Sunday afternoon implement
native XML engine!
33Non-Java databases MySQL (cont.)
- Example
- CREATE TABLE tblMyISAM (
- id INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (id),
- value_a TINYINT
- ) TYPEMyISAM
- CREATE TABLE tblISAM (
- id INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (id),
- value_a TINYINT
- ) TYPEISAM
- CREATE TABLE tblHeap (
- id INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (id),
- value_a TINYINT
- ) TYPEHeap
- You can also use the ALTER TABLE command
- ALTER TABLE tblMyISAM CHANGE TYPEInnoDB
34Non-Java databases PostGreSQL
- Evolved from database research at UC Berkeley
- Actively being developed/supported
- NO server version for windows platform available
Category Relational database with JDBC driver
URL http//postgresql.org/
Supported platforms Linux, Unix
License BSD
Features One of the most complete SQL implementations, very rich datatype support
35Non-Java databases MaxDB
- Code base from SAP (ERP software)
- Back-up by Swedish company MySQL AB (!!!)
- Can be configured to support Oracle (v.7) SQL and
DB2 (v.2) - Fear skeptical of going open source motivation
- Remains to be seen how this (huge) project is
picked up by OS community
Category Relational database with JDBC driver
URL http//www.mysql.com/products/maxdb/
Supported platforms Windows, Linux, others
License GPL server, LPGL client components
Features Industrial strength database
36Non-Java databases Ingres
- Recently went Open Source,
- License (CA-TOSL) to be OSI approved (CPL
derived) - http//opensource.ca.com/projects/ingres
- Strengthened relations with JBoss organization
- Ingres will be the default DB delivered with
JBoss - CA will coordinate development efforts
- Like MySQL AB for MySQL and MaxDB databases
- Industrial strength database
- Questions
- Remains to be seen if an OS community will
emerge - What will the prices be for support
37Non-Java databases Berkely DB
- Embedded database for servers, networking
hardware, handhelds, - Supporting company Sleepycat
- Three products
- Berkely DB, embedded, accessible via API (Perl,
Python, ) - Berkeley DB XML, native XML database
- Berkeley DB JE, for pure Java solutions
- License free for open source projects
38Evaluation non-Java databases
- Requirement analysis (examples of valid
considerations) - Server has to run on windows ? No PostgreSQL
- Requirement for triggers and stored procedures ?
No MySQL - Porting app. from DB2 or Oracle ? MaxDB
- is able to understand different SQL dialects
- Support user base ? MySQL
- Licensing issues
- MySQL AB/Sleepycat only free for OS projects
- GPL might be restrictive
- Availability of supporting/admin tools
- There may be many requirements-satisfying
candidates
39Evaluation non-Java databases (cont.)
- Quick through the corner ? (snel door de
bocht) - PostgreSQL
- implementing most of the ANSI standard
- transactions, triggers, views, subselects and
user-defined types. - Stored procedures (many languages), sophisticated
locking. - Client libraries for all of your favorite
programming languages. - MySQL
- high-traffic applications where speed is more
important than data integrity. - lacks support for transactions, views, stored
procedures, and subselects. - Oracle
- Not free
- Most popular and powerful relational database on
the market - Applications, functions, and stored procedures
(PL/SQL language)
40Evaluation non-Java databases (cont.)
- Generally less features than commercial
alternatives, but covers 80 of applications
requirements - Finally we havent exhaustively examined all
open source databases here!
41TOOLS
42Tools MySQL Control Centre
- GUI for MySQL database
- GPL, for Windows and Linux platforms
- Download http//www.mysql.com/products/mysqlcc/
43Tools MySQL admin tools
- MySQL Administrator
- by MySQL AB (free)
- http//www.mysql.com/products/administrator/
- MySQL manager
- by EMS (commercial)
- http//ems-hitech.com/mymanager/
- NOT SHOWN!
44Tools MySQL Query Browser
45Tools Squirrel
- Relational database access tool
- Purely Java and JDBC-based (license LPGL)
- http//squirrel-sql.sourceforge.net/
- Various plug-ins available
- Adds Oracle specific functionality
- Adds MySQL specific functionality
-
- Support to validate SQL against ISO SQL standard
- Look-and-feel
- Write scripts to file
46Tools Squirrel (cont.)
47Tools HSQL db-manager from jmx-console
- From JBoss management console
- HSQL db-manager
- Demo!
48Tools Eclipse plug-ins
49Tools Eclipse plug-ins
- IBMs WSAD
- JfaceDb from http//www.pratocity.com/index.jsp
(commercial!!)
50Integration with application servers
- Integration with Tomcat
- JDBC Drivers in CATALINA_HOME/common/lib
- Configure JNDI
- Use Tomcat Admin tool/page
- Adapt the server.xml
- Configure
- Class name of JDBC driver (e.g. jdbc/DefaultDS)
- Connection URL (e.g. com.mysql.jdbc.driver or
org.hsqldb.jdbcDriver) - Username passwd (tomcat welkom123)
- Integration with JBoss
- Demo!
51Conclusions
- Open source databases scratch your own itch
- Suffices for most cases though!
- If PostgreSQL or MySQL is not enough
- Look at former commercial alternatives, such as
Firebird - No support argument does not hold anymore
- Look at which license suits you
- More candidates may satisfy your needs!