001/*
002 * Copyright (c) 2008-2010 The Regents of the University of California.
003 * All rights reserved.
004 *
005 * '$Author: crawl $'
006 * '$Date: 2015-08-21 22:15:53 +0000 (Fri, 21 Aug 2015) $' 
007 * '$Revision: 33602 $'
008 * 
009 * Permission is hereby granted, without written agreement and without
010 * license or royalty fees, to use, copy, modify, and distribute this
011 * software and its documentation for any purpose, provided that the above
012 * copyright notice and the following two paragraphs appear in all copies
013 * of this software.
014 *
015 * IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY
016 * FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
017 * ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
018 * THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF
019 * SUCH DAMAGE.
020 *
021 * THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
022 * INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
023 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE
024 * PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF
025 * CALIFORNIA HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
026 * ENHANCEMENTS, OR MODIFICATIONS.
027 *
028 */
029
030package org.kepler.util.sql;
031
032import java.sql.DatabaseMetaData;
033import java.sql.ResultSet;
034import java.sql.SQLException;
035import java.sql.Statement;
036import java.util.Map;
037import java.util.regex.Matcher;
038import java.util.regex.Pattern;
039
040/**
041 *
042 * An implementation of DatabaseType for MySQL.
043 *
044 * @author Daniel Crawl
045 * @version $Id: MySQL.java 33602 2015-08-21 22:15:53Z crawl $
046 *
047 */
048    
049public class MySQL extends DatabaseType
050{
051    /** Only this package (DatabaseFactory) can instantiate. */
052    protected MySQL()
053    {
054        super();
055    }
056    
057    /** Connect to a database. */
058    @Override
059    public void connect(Map<String,String> parameters) throws SQLException
060    {   
061        _connectInitialize(parameters.get(DatabaseFactory.Parameter.TABLEPREFIX.getName()));
062
063        String dbName = parameters.get(DatabaseFactory.Parameter.NAME.getName());
064        
065        // see if the jdbc url parameter is used
066        String url = parameters.get(DatabaseFactory.Parameter.JDBC_URL.getName());
067        if(url != null && url.length() > 0)
068        {
069            // get the database name from the url
070            Matcher matcher = _databaseNamePattern.matcher(url);
071            if(!matcher.find())
072            {
073                throw new SQLException("Could not determine database name in " +
074                    "JDBC URL " + url);
075            }
076            
077            // save the database name
078            dbName = matcher.group(1);
079            
080            // make the connection without the database name since the catalog
081            // may not yet exist
082            String urlNoDB = matcher.replaceFirst("/\\?");
083            _connection = _getConnection(urlNoDB);
084        }
085        else
086        {
087            // no jdbc url, so build one from the other parameters.
088            String hostName = parameters.get(DatabaseFactory.Parameter.HOST.getName());
089            String port = parameters.get(DatabaseFactory.Parameter.PORT.getName());
090            String userName = parameters.get(DatabaseFactory.Parameter.USER.getName());
091            String passwd = parameters.get(DatabaseFactory.Parameter.PASSWD.getName());
092
093            url = _getJDBCUrl(hostName, port, "");
094            _connection = _getConnection(url, port, dbName, userName, passwd);            
095        }
096        
097        // create the catalog if it does not exist.
098        _createCatalog(dbName);
099        
100        String createIndexes = parameters.get(DatabaseFactory.Parameter.CREATE_INDEXES.getName());
101        if(createIndexes == null || createIndexes.trim().isEmpty() || createIndexes.equals("true"))
102        {
103            _createIndexes = true;
104        }
105
106    }
107    
108    /** Get the name of the type. */
109    @Override
110    public String getName()
111    {
112        return "MySQL";
113    }
114
115    /** Rename a column. */
116    @Override
117    public void renameColumn(String oldName, Column newColumn, String tableName) throws SQLException
118    {        
119        String sqlStr = "ALTER TABLE " + getTableName(tableName) +
120        " CHANGE COLUMN " + getColumnName(oldName) + " " + getColumnDefinition(newColumn);
121        _executeSQL(sqlStr);
122    }
123
124    /** Change a column to allow null values. */
125    @Override
126    public void setColumnNull(Column column, String tableName) throws SQLException
127    {
128        String sqlStr = "ALTER TABLE " + getTableName(tableName) +
129        " CHANGE COLUMN " + getColumnName(column.getName()) + " " + getColumnDefinition(column);
130        _executeSQL(sqlStr);
131    }
132    
133    /** Set not null constraint to a column. */
134    @Override
135    public void setColumnNotNull(Column column, String tableName) throws SQLException
136    {
137        String sqlStr = "ALTER TABLE " + getTableName(tableName) +
138        " CHANGE COLUMN " + getColumnName(column.getName()) + " " + getColumnDefinition(column);
139        _executeSQL(sqlStr);
140    }
141
142    ///////////////////////////////////////////////////////////////////
143    // protected methods
144    
145    /** Get any suffix used when creating tables. */
146    @Override
147    protected String _getCreateTableSuffix()
148    {
149        return "ENGINE=InnoDB";
150    }
151
152    /** Get the driver class name. */
153    @Override
154    protected String _getDriverName()
155    {
156        return "com.mysql.jdbc.Driver";
157    }
158    
159    /** Get a JDBC URL. */
160    @Override
161    protected String _getJDBCUrl(String hostName, String port,
162            String databaseName) throws SQLException
163    {
164        // NOTE: we set zeroDateTimeBehavior so that if a date
165        // time is set to all zeros, we want to return null
166        // instead of throwing an exception.
167        // (see http://bugzilla.ecoinformatics.org/show_bug.cgi?id=4308)
168        
169        String hostAndPort = _combineHostAndPort(hostName, port);
170        return "jdbc:mysql://" + hostAndPort + "/" + databaseName +
171            "?zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&cachePrepStmts=true";
172    }
173
174    /** Get the SQL string of a column type. */
175    @Override
176    protected String _getTypeString(Column column)
177    {
178        String retval = null;
179
180        switch(column.getType())
181        {
182            case Boolean:
183                retval = "tinyint(1)";
184                break;
185            case Binary:
186                retval = "binary";
187                break;
188           case Blob:
189                retval = "longblob";
190                break;
191            case TextBlob:
192                retval = "longtext";
193                break;
194            case Integer:
195                retval = "int";
196                break;
197            case Timestamp:
198                retval = "timestamp";
199                break;
200            case VarBinary:
201                retval = "varbinary";
202                break;
203            case Varchar:
204                retval = "varchar";
205                break;
206        }
207
208        if(retval != null && column.isAutoIncrement())
209        {
210            retval += " auto_increment";
211        }
212
213        return retval;
214    }
215
216    /** Returns true if database supports auto-generated keys in its prepared
217     *  statements.
218     */
219    @Override
220    protected boolean _hasGeneratedKeys()
221    {
222        return true;
223    }
224    
225    ///////////////////////////////////////////////////////////////////
226    // private methods
227
228    /** Create the database catalog if it does not exist. */
229    private void _createCatalog(String dbName) throws SQLException
230    {
231        boolean found = false;
232        DatabaseMetaData metadata = _connection.getMetaData();
233        ResultSet results = null;
234        
235        try
236        {
237            results = metadata.getCatalogs();
238            // see if database exists
239            while(results.next())
240            {
241                if(dbName.equals(results.getString("TABLE_CAT")))
242                {
243                    found = true;
244                    break;
245                }
246            }
247        }
248        finally
249        {
250            if(results != null)
251            {
252                results.close();
253            }
254        }
255
256        // create if not found
257        if(!found)
258        {
259            Statement statement = null;
260            try
261            {
262                statement = _connection.createStatement();
263                statement.execute("CREATE DATABASE " + dbName); 
264            }
265            finally
266            {
267                if(statement != null)
268                {
269                    statement.close();
270                }
271            }
272        }
273
274        _connection.setCatalog(dbName); 
275    }
276    
277    /** A regex pattern to find the database name in a mysql jdbc url. */
278    private static final Pattern _databaseNamePattern = Pattern.compile("/(\\w+)\\?");
279
280}