001/* -*- c-basic-offset: 4; indent-tabs-mode: nil -*- */
002/*
003 *                    BioJava development code
004 *
005 * This code may be freely distributed and modified under the
006 * terms of the GNU Lesser General Public Licence.  This should
007 * be distributed with the code.  If you do not have a copy,
008 * see:
009 *
010 *      http://www.gnu.org/copyleft/lesser.html
011 *
012 * Copyright for this code is held jointly by the individual
013 * authors.  These should be listed in @author doc comments.
014 *
015 * For more information on the BioJava project and its aims,
016 * or to join the biojava-l mailing list, visit the home page
017 * at:
018 *
019 *      http://www.biojava.org/
020 *
021 */
022
023package org.biojava.bio.seq.db.biosql;
024
025import java.sql.Connection;
026import java.sql.PreparedStatement;
027import java.sql.SQLException;
028
029import javax.sql.DataSource;
030
031import org.biojava.bio.BioRuntimeException;
032
033/**
034 * Isolates all code that is specific to a particular RDBMS. To add
035 * support for a new RDBMS, write a new <code>DBHelper</code> subclass
036 * and ensure that it can be found by editing the
037 * <code>getDBHelperForURL</code> method in this class.
038 *
039 * @author Thomas Down
040 * @author Matthew Pocock
041 * @author Len Trigg
042 * @author Eric Haugen
043 * @author Richard Holland
044 * @deprecated Use hibernate and org.biojavax.bio.db.*
045 */
046public abstract class DBHelper {
047
048    /**
049     * Returns a DBHelper implementation suitable for a particular
050     * database.
051     *
052     * @param conn a connection to the database.
053     * @return a <code>DBHelper</code>.
054     */
055    public static DBHelper getDBHelper(Connection conn) {
056        try {
057            String dbType = conn.getMetaData().getURL();
058            if (dbType.startsWith("jdbc:")) {
059                dbType = dbType.substring(5);
060            }
061            if (!Character.isLetter(dbType.charAt(0))) {
062                throw new IllegalArgumentException("URL must start with a letter: " + dbType);
063            }
064            
065            int colon = dbType.indexOf(':');
066            if (colon > 0) {
067                String protocol = dbType.substring(0, colon);
068                if (protocol.indexOf("mysql") >= 0) {
069                    // Accept any string containing `mysql', to cope with Caucho driver
070                    return new MySQLDBHelper(conn);
071                } else if (protocol.equals("postgresql")) {
072                    return new PostgreSQLDBHelper();
073                } else if (protocol.equals("oracle")) {
074                    return new OracleDBHelper(conn);
075                } else if (protocol.equals("hsqldb")) {
076                    return new HypersonicDBHelper();
077                }
078            }
079        } catch (SQLException se) {
080            se.printStackTrace();
081        }
082        return new UnknownDBHelper();
083    }
084
085    public static final class DeleteStyle {
086        private final String name;
087
088        private DeleteStyle(String name) {
089            this.name = name;
090        }
091
092        public String toString() {
093            return "DBHelper.DeleteStyle: " + name;
094        }
095    }
096
097    public static final DeleteStyle DELETE_POSTGRESQL = new DeleteStyle("Postgresql");
098    public static final DeleteStyle DELETE_MYSQL4 = new DeleteStyle("Mysql 4.0.* or later");
099    public static final DeleteStyle DELETE_GENERIC = new DeleteStyle("Portable SQL");
100
101
102    public static final class JoinStyle {
103        private final String name;
104
105        private JoinStyle(String name) {
106            this.name = name;
107        }
108
109        public String toString() {
110            return "DBHelper.JoinStyle: " + name;
111        }
112    }
113
114    public static final JoinStyle JOIN_ORACLE8 = new JoinStyle("Oracle 8i or earlier");
115    public static final JoinStyle JOIN_GENERIC = new JoinStyle("Portable SQL");
116
117
118    public static final class BioSequenceStyle {
119        private final String name;
120
121        private BioSequenceStyle(String name) {
122            this.name = name;
123        }
124
125        public String toString() {
126            return "DBHelper.BioSequenceStyle: " + name;
127        }
128    }
129
130    public static final BioSequenceStyle BIOSEQUENCE_GENERIC = new BioSequenceStyle("Standard schema (except for Oracle schemas using CLOBs)");
131    public static final BioSequenceStyle BIOSEQUENCE_ORACLECLOB = new BioSequenceStyle("Oracle schema using CLOBS (but not Len Trigg's schema)");
132
133    /**
134     * Returns the id value created during the last insert
135     * command. This is for tables that have an auto increment column.
136     * 
137     * @return the last id assigned, or -1 if the id could not be
138     * found.
139     */
140    public abstract int getInsertID(Connection conn, String table, 
141                                    String columnName) throws SQLException;
142
143
144    /**
145     * Returns the an object indicating the style of deletion that
146     * this database should employ. Unless overridden, this is
147     * DELETE_GENERIC.
148     * 
149     * @return the preferred deletion style.
150     */
151    public DeleteStyle getDeleteStyle() {
152        return DELETE_GENERIC;
153    }
154
155
156    /**
157     * Returns the an object indicating the style of table joining that
158     * this database should employ.
159     * 
160     * @return the preferred joining style.
161     */
162    public JoinStyle getJoinStyle() {
163        return JOIN_GENERIC;
164    }
165
166
167    /**
168     * Returns the an object indicating the style of biosequence storage
169     * that this database should employ. Generally, leave it at the default
170     * unless you are using the Oracle schema, in which case you need
171     * to override it to return BIOSEQUENCE_ORACLECLOB. This is because, in the
172     * Oracle schema we need to use CLOBs (except when using Len Trigg's 
173     * version which uses LONGs instead.)
174     * 
175     * @return the preferred joining style.
176     */
177    public BioSequenceStyle getBioSequenceStyle() {
178        return BIOSEQUENCE_GENERIC;
179    }
180
181    /**
182     * Detects whether a particular table is present in the database.
183     *
184     * @param ds a <code>DataSource</code> that can provide a connection to a database 
185     * @param tablename the name of the table.
186     * @return true if the table exists in the database.
187     * @throws NullPointerException if pool is null.
188     * @throws IllegalArgumentException if tablename is null or empty.
189     */
190    public boolean containsTable(DataSource ds, String tablename) {
191        if (ds == null) {
192            throw new NullPointerException("Require a datasource.");
193        }
194        if ((tablename == null) || (tablename.length() == 0)) {
195            throw new IllegalArgumentException("Invalid table name given");
196        } 
197        Connection conn = null;
198        try {
199            boolean present;
200            PreparedStatement ps = null;
201            try {
202                conn = ds.getConnection();
203                ps = conn.prepareStatement("select * from " + tablename + " limit 1");
204                ps.executeQuery();
205                present = true;
206            } catch (SQLException ex) {
207                present = false;
208            }
209            if (ps != null) {
210                ps.close();
211            }
212            if (conn != null) {
213                conn.close();
214            }
215            return present;
216        } catch (SQLException ex) {
217            if (conn!=null) try {conn.close();} catch (SQLException ex3) {}
218            throw new BioRuntimeException(ex);
219        }
220    }
221}