Open Source Databases - PowerPoint PPT Presentation

About This Presentation
Title:

Open Source Databases

Description:

Deze presentatie is gemaakt om een uitleg te geven over de implementatie van Real Application Clusters bij de Holland Belleging Groep – PowerPoint PPT presentation

Number of Views:118
Avg rating:3.0/5.0
Slides: 52
Provided by: BJ193
Category:

less

Transcript and Presenter's Notes

Title: Open Source Databases


1
Open Source Databases
  • Zeger W. Hendrikse

2
Content
  • 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

3
SQL
  • DDL (Data Definition Language)
  • create, alter, drop
  • DML (Data Manipulation Language)
  • CRUD (select, insert, update, delete)
  • DCL (Data Control Language)
  • connect, grant, revoke

4
JDBC 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

5
JDBC 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

6
JDBC 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

7
JDBC 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

8
JDBC 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

9
JDBC 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

10
JDBC 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

11
JDBC 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

12
JDBC 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

13
Enterprise 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

14
Development models
15
Development 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
16
Java databases
17
Java 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

18
Java 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

19
Java 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)

20
Java 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

21
Java 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

22
Java 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.

23
Java databases HSqlDb (cont.)
  • Demo Hypersonic SQL!

24
Java 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

25
Java 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!!!

26
Java 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

27
Evaluation 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

28
non-Java databases
29
Non-Java databases GNU SQL
  • Seems to be dead (latest news from 28-09-1998)

30
Non-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
31
Non-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
32
Non-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!

33
Non-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

34
Non-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
35
Non-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
36
Non-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

37
Non-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

38
Evaluation 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

39
Evaluation 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)

40
Evaluation 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!

41
TOOLS
42
Tools MySQL Control Centre
  • GUI for MySQL database
  • GPL, for Windows and Linux platforms
  • Download http//www.mysql.com/products/mysqlcc/

43
Tools 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!

44
Tools MySQL Query Browser
45
Tools 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

46
Tools Squirrel (cont.)
  • Demo!

47
Tools HSQL db-manager from jmx-console
  • From JBoss management console
  • HSQL db-manager
  • Demo!

48
Tools Eclipse plug-ins
49
Tools Eclipse plug-ins
  • IBMs WSAD
  • JfaceDb from http//www.pratocity.com/index.jsp
    (commercial!!)

50
Integration 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!

51
Conclusions
  • 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!
Write a Comment
User Comments (0)
About PowerShow.com