Coverage Report

 /*
  * $Source$
  * $Revision$
  *
  * Copyright (C) 2002 Peter Kehl
  * 
  * Part of Melati (http://melati.org), a framework for the rapid
  * development of clean, maintainable web applications.
  *
  * Melati is free software; Permission is granted to copy, distribute
  * and/or modify this software under the terms either:
  *
  * a) the GNU General Public License as published by the Free Software
  *    Foundation; either version 2 of the License, or (at your option)
  *    any later version,
  *
  *    or
  *
  * b) any version of the Melati Software License, as published
  *    at http://melati.org
  *
  * You should have received a copy of the GNU General Public License and
  * the Melati Software License along with this program;
  * if not, write to the Free Software Foundation, Inc.,
  * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the
  * GNU General Public License and visit http://melati.org to obtain the
  * Melati Software License.
  *
  * Feel free to contact the Developers of Melati (http://melati.org),
  * if you would like to work out a different arrangement than the options
  * outlined here.  It is our intention to allow Melati to be used by as
  * wide an audience as possible.
  *
  * This program is distributed in the hope that it will be useful,
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  * GNU General Public License for more details.
  *
  *
  */
 
 
 package org.melati.poem.dbms;
 
 import java.util.Enumeration;
 import java.sql.SQLException;
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 
 import org.melati.poem.SizedAtomPoemType;
 import org.melati.poem.Table;
 import org.melati.poem.Column;
 import org.melati.poem.PoemType;
 import org.melati.poem.SQLPoemType;
 import org.melati.poem.IntegerPoemType;
 import org.melati.poem.BinaryPoemType;
 import org.melati.poem.BooleanPoemType;
 import org.melati.poem.StringPoemType;
 import org.melati.poem.ParsingPoemException;
 import org.melati.poem.SQLPoemException;
 import org.melati.poem.TimestampPoemType;
 import org.melati.poem.util.StringUtils;
 
  /**
   * A Driver for MySQL.
   * See http://www.mysql.com.
   *
   * <b>Notes</b>
   * <ol>
   * <li>
   *  Use JDBC URL of type jdbc:mysql://[host][:port]/dbname[?param=value[...]]
   *  ie. the simpliest one has 3 slashes: jdbc:mysql:///melatitest
   * </li>
   * <li>
   *  Don't use asterix * for password, leave it empty (end of line), as:
   * <pre>
   *  org.Melati.LogicalDatabase.melatitest.pass=
   * </pre>
   * 
   * or use explicit username and password and 
   * <pre>
   *   GRANT ALL PRIVILEGES ON dbname
   *  TO username@localhost IDENTIFIED BY 'password';
   * </pre> 
   * </li>
   * <li>
   * If you want to use double quotes to delimit table and column names then 
   * start MySQL in ANSI mode and modify getQuotedName(String name).
   * </li>
   * <li>   
   * Start MySQL with transactioned tables as default. InnoDB is stable,
   *  BDB nearly stable.
   *  <code>getConnection</code> now returns a <code>Connection</code> 
   *  with <code>autocommit</code> turned off through JDBC.
   * 
   *  BDB tables of MySQL-Max 3.23.49 don't support full transactions
   *  - they lock whole table instead, until commit/rollback is called.
   *  According to MySQL 4.0.2-alpha doc, interface between MySQL and
   *  BDB tables is still improved.
   *
   *  As I tested MySQL-Max 3.23.49, InnoDB has correct transactions,
   *  however database size must be specified and reserved in advance
   *  in one file, that is share by all InnoDB tables.
   *  Set in /etc/my.cnf by line like:
   *     innodb_data_file_path=ibdata1:30M
   * <pre>
   *  run
   *  safe_mysqld --user=mysql --ansi --default-table-type=InnoDB
   * </pre>
   * After it created and initialised dB file /var/lib/mysql/ibdata1
   * of 30MB, it creates 2 own log files  /var/lib/mysql/ib_logfile0
   * and ib_logfile1, both of size 5MB.
   * <br>
   * The table type is currently hardcoded in <tt>createTableOptions</tt>.
   *
   * </li>
   * <li>
   *  <tt>boolean</tt> type works (both applications melatitest and contacts).
   *  Because MySQL returns metainfo about BOOL as TINYINT.
   * </li>
   * </ol>
   */
 public class MySQL extends AnsiStandard {
 
   /** Size of indexes. */
   public static final int indexSize = 30;
   /** Size of MySQL text fields. */
   public static final int mysqlTextSize = 65535;
 
   /** Constructor - sets driver. */
   public MySQL() {
     setDriverClassName("org.gjt.mm.mysql.Driver");
   }
 
   /**
    * {@inheritDoc}
    * @see org.melati.poem.dbms.AnsiStandard#createTableOptionsSql()
    */
   public String createTableOptionsSql() {
     //MySQL 5.5 doesn't support "type" table option, instead it uses ENGINE option.
     //return " TYPE='InnoDB' "; 
 
     return " ENGINE='InnoDB' ";
   }
 
 /**
   * Retrieve an SQL type keyword used by the DBMS 
   * for the given Melati type name.
   *
   * @param sqlTypeName the Melati internal type name
   * @return this dbms specific type keyword
   */
   public String getSqlDefinition(String sqlTypeName) {
     if(sqlTypeName.equals("BOOLEAN")) return "bool"; 
     return super.getSqlDefinition(sqlTypeName);
   }
 
    @Override
   public String getStringSqlDefinition(int size) throws SQLException {
     if (size < 0) { 
       return "text";
     }
     return super.getStringSqlDefinition(size); //VARCHAR(size) is OK
   }
 
   /**
    * Ignores size.
    */
   @Override
   public String getBinarySqlDefinition(int size) {
     return "BLOB"; 
   }
 
    @Override
   public String getQuotedName(String name) {
     return unreservedName(name);
   }
 
  /**
   * Translates a MySQL String into a Poem <code>StringPoemType</code>.
   */ 
   public static class MySQLStringPoemType extends StringPoemType {
 
     /**
      * Constructor.
      * @param nullable whether nullable
      * @param size size
      */
     public MySQLStringPoemType(boolean nullable, int size) {
       super(nullable, size);
     }
 
     //MySQL returns metadata info size 65535 for TEXT type
     protected boolean _canRepresent(SQLPoemType<?> other) {
       return
              sqlTypeCode() == other.sqlTypeCode() 
              && other instanceof StringPoemType 
              && (getSize()<0 || getSize()==mysqlTextSize || getSize()>=((StringPoemType)other).getSize());
     }
 
     /**
      * @see org.melati.poem.SizedAtomPoemType#withSize(int)
      */
     public SizedAtomPoemType<String> withSize(int newSize) {
       if (newSize==mysqlTextSize)
         return super.withSize(-1);
       return super.withSize(newSize);
     }
   }
 
  /**
   * Translates a MySQL Boolean into a Poem <code>BooleanType</code>.
   */ 
   public static class MySQLBooleanPoemType extends BooleanPoemType {
     /**
      * Constructor.
      * @param nullable whether nullable
      */
     public MySQLBooleanPoemType(boolean nullable) {
       super(nullable);
     }
 
     protected Boolean _getRaw(ResultSet rs, int col) throws SQLException {
       synchronized (rs) {
         int i = rs.getInt(col);
           return rs.wasNull() ? null :
             (i==1 ? Boolean.TRUE : Boolean.FALSE);
       }
     }
 /*
     protected Object _getRaw(ResultSet rs, int col) throws SQLException {
       synchronized (rs) {
         String v = rs.getString(col);
         return rs.wasNull() ? null :
          (v.equals("t") ? Boolean.TRUE : Boolean.FALSE);
       }
     }
 */
 
     protected void _setRaw(PreparedStatement ps, int col, Object bool)
         throws SQLException {
       ps.setInt(col, ((Boolean)bool).booleanValue() ? 1 : 0);
     }
     /*
     protected void _setRaw(PreparedStatement ps, int col, Object bool)
         throws SQLException {
       if (bool instanceof Boolean && bool == Boolean.TRUE) 
         ps.setString(col, "t");
       else
         ps.setString(col, "f");
     }
     */ 
         
     /**
      * We could use original method from BooleanPoemType,
      * it too recognizes 0/1.
      * {@inheritDoc}
      * @see org.melati.poem.BooleanPoemType#_rawOfString(java.lang.String)
      */
     protected Boolean _rawOfString(String rawString)
         throws ParsingPoemException {
       rawString = rawString.trim();
       switch (rawString.charAt(0)) {
         case '1': return Boolean.TRUE;
         case '0': return Boolean.FALSE;
         default: throw new ParsingPoemException(this, rawString);
       }
     }
   }
 
  /**
   * Translates a MySQL Blob into a Poem <code>IntegerPoemType</code>.
   */ 
   public static class BlobPoemType extends BinaryPoemType {
  
    /**
     * Constructor.
     * @param nullable whether nullable
     * @param size size
     */
     public BlobPoemType(boolean nullable, int size) {
       super(nullable, size);
     }
 
     protected boolean _canRepresent(SQLPoemType<?> other) {
       return other instanceof BinaryPoemType;
     }
 
     @Override
     public <O>PoemType<O> canRepresent(PoemType<O> other) {
       return other instanceof BinaryPoemType &&
           !(!getNullable() && ((BinaryPoemType)other).getNullable()) ?
                        other : null;
     }
   }
 
    @Override
   public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> other) {
     if (storage instanceof IntegerPoemType &&
         other instanceof BooleanPoemType
         && !(!storage.getNullable() && other.getNullable())  // Nullable may represent not nullable
     ) {
       return other;
     } else {
       return storage.canRepresent(other);
     }
   }
 
    @Override
   public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet md)
       throws SQLException {
     boolean nullable = md.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
     String typeName = md.getString("TYPE_NAME").toLowerCase();
     if(typeName.equals("blob"))
       return new BlobPoemType(nullable, md.getInt("COLUMN_SIZE"));
     else if(typeName.equals("text"))
       return new MySQLStringPoemType(nullable, md.getInt("COLUMN_SIZE"));
     else if(typeName.equals("smallint"))
       return new IntegerPoemType(nullable);
     // See http://www.postgresql.org/docs/current/interactive/datatype-datetime.html
     else if(typeName.equals("datetime"))
       return new TimestampPoemType(nullable);
     else if(typeName.equals("set"))
       return new StringPoemType(nullable, md.getInt("COLUMN_SIZE"));
     
     else if(typeName.equals("char"))
       return new StringPoemType(nullable, md.getInt("COLUMN_SIZE"));
     // MySQL:BOOL --> MySQL:TINYINT --> Melati:boolean backward mapping
     else if(typeName.equals("tinyint"))
       return new MySQLBooleanPoemType(nullable);
     else
       return super.defaultPoemTypeOfColumnMetaData(md);
   }
 
 
   @Override
   public SQLPoemException exceptionForUpdate(
         Table<?> table, String sql, boolean insert, SQLException e) {
 
     String m = e.getMessage();
 
     // MySQL's duplicate key (or any unique field) message is:
     // "ERROR 1062: Duplicate entry '106' for key 1"
     //  Duplicate index value      <--|           |
     //                                            |
     //  Which 'index' (unique field) it is, in <--|
     //  order as table was defined, starting from 1.
 
     if (m != null &&
         m.indexOf("1062") >= 0) {
 
   // It's not simple as in Postgres. This duplicated 'index' is one
   // of possibly more unique columns. That involves searching for its
   // column. For error "Duplicate entry '106' for key 4"
   // we search 4th unique field = we loop over columns, skip first 3 that
   // are unique and return 4th unique.
 
       try { //Try parsing error message.
 
         int preIndex, postIndex; //Places of apostrophes around index value
         int preColumn; //Place of "key ", which is in front of column number
     
         preIndex= m.indexOf('\'');
         postIndex= m.lastIndexOf('\'');
         preColumn= m.indexOf("key ");
   
         String indexValue= m.substring(preIndex+1, postIndex);
         String indexColumn= m.substring(preColumn+4);
 
         System.err.println("Duplicated value " + indexValue +
               " of " + indexColumn + "th unique field."); 
   
         int indexNum= Integer.parseInt(indexColumn);
         Column<?> column = table.troidColumn(); //Just to satisfy compiler.
          //At the end, it will (should) be our column anyway.
       
         for(Enumeration<Column<?>> columns = table.columns(); columns.hasMoreElements();) {
           column = columns.nextElement();
           if(column.getUnique() && (--indexNum == 0))
             break; //We found it!
         }
         //Now, it's found & indexNum==0.
         if(indexNum==0)
           return new DuplicateKeySQLPoemException(column, sql, insert, e);
       } catch(NumberFormatException f) {
           throw new RuntimeException(
               "Number format exception parsing dbms error.");  
       }
       return new DuplicateKeySQLPoemException(table, sql, insert, e);
     }
     return super.exceptionForUpdate(table, sql, insert, e);
   }
 
   @Override
   public String unreservedName(String name) {
     if(name.equalsIgnoreCase("group")) name = "poem_" + name;
     if(name.equalsIgnoreCase("precision")) name = "poem_" + name;
     if(name.equalsIgnoreCase("unique")) name = "poem_" + name;
     return name;
   }
 
   @Override
   public String melatiName(String name) {
     if (name == null) return name;
     if(name.equalsIgnoreCase("poem_group")) name = "group";
     if(name.equalsIgnoreCase("poem_precision")) name = "precision";
     if(name.equalsIgnoreCase("poem_unique")) name = "unique";
     return name;
   }
 
  /**
   * MySQL requires TEXT and BLOB field indices to have an 
   * explicit length, 30 should be fine.
   *
   * @return a snippet of sql to insert into an SQL statement.
   */
   @Override
   public String getIndexLength(Column<?> column) {
     PoemType<?> t = column.getType();
     if (t instanceof StringPoemType && 
         ((StringPoemType)t).getSize() < 0) return "(" + indexSize + ")";
     if (t instanceof BinaryPoemType) return "(" + indexSize + ")";
     return "";
   }
 
    @Override
   public String givesCapabilitySQL(Integer userTroid, String capabilityExpr) {
     return
         "SELECT groupMembership.* " + 
         "FROM groupMembership LEFT JOIN groupCapability " +
         "ON groupMembership." + getQuotedName("group") +
         " =  groupCapability." + getQuotedName("group") + " " +
         "WHERE " + getQuotedName("user") + " = " + userTroid + " " +
         "AND groupCapability." + getQuotedName("group") + " IS NOT NULL " +
         "AND capability = " + capabilityExpr;
   }
 
    @Override
   public String caseInsensitiveRegExpSQL(String term1, String term2) {
     if (StringUtils.isQuoted(term2)) {
       term2 = term2.substring(1, term2.length() - 1);
     } 
     term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');
     
     return term1 + " LIKE " + term2;
   }
 
    @Override
   public String alterColumnNotNullableSQL(String tableName, Column<?> column) {
     return "ALTER TABLE " + getQuotedName(tableName) +
     " CHANGE " + getQuotedName(column.getName()) + " " + getQuotedName(column.getName()) +
     " " + 
     column.getSQLType().sqlDefinition(this);
   }
   
  /**
   * MySQL does not do the normal thing. 
   */
   @Override
   public String alterColumnAddCommentSQL(Column<?> column, String comment) {
     return null;
   }
 
   /**
   * MySQL does not do the normal thing. 
    */
   @Override
   public String alterTableAddCommentSQL(Table<?> table, String comment) {
     return null;
   }
   
 }

1		/*
2		* $Source$
3		* $Revision$
4		*
5		* Copyright (C) 2002 Peter Kehl
6		*
7		* Part of Melati (http://melati.org), a framework for the rapid
8		* development of clean, maintainable web applications.
9		*
10		* Melati is free software; Permission is granted to copy, distribute
11		* and/or modify this software under the terms either:
12		*
13		* a) the GNU General Public License as published by the Free Software
14		* Foundation; either version 2 of the License, or (at your option)
15		* any later version,
16		*
17		* or
18		*
19		* b) any version of the Melati Software License, as published
20		* at http://melati.org
21		*
22		* You should have received a copy of the GNU General Public License and
23		* the Melati Software License along with this program;
24		* if not, write to the Free Software Foundation, Inc.,
25		* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the
26		* GNU General Public License and visit http://melati.org to obtain the
27		* Melati Software License.
28		*
29		* Feel free to contact the Developers of Melati (http://melati.org),
30		* if you would like to work out a different arrangement than the options
31		* outlined here. It is our intention to allow Melati to be used by as
32		* wide an audience as possible.
33		*
34		* This program is distributed in the hope that it will be useful,
35		* but WITHOUT ANY WARRANTY; without even the implied warranty of
36		* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
37		* GNU General Public License for more details.
38		*
39		*
40		*/
41
42
43		package org.melati.poem.dbms;
44
45		import java.util.Enumeration;
46		import java.sql.SQLException;
47		import java.sql.DatabaseMetaData;
48		import java.sql.PreparedStatement;
49		import java.sql.ResultSet;
50
51		import org.melati.poem.SizedAtomPoemType;
52		import org.melati.poem.Table;
53		import org.melati.poem.Column;
54		import org.melati.poem.PoemType;
55		import org.melati.poem.SQLPoemType;
56		import org.melati.poem.IntegerPoemType;
57		import org.melati.poem.BinaryPoemType;
58		import org.melati.poem.BooleanPoemType;
59		import org.melati.poem.StringPoemType;
60		import org.melati.poem.ParsingPoemException;
61		import org.melati.poem.SQLPoemException;
62		import org.melati.poem.TimestampPoemType;
63		import org.melati.poem.util.StringUtils;
64
65		/**
66		* A Driver for MySQL.
67		* See http://www.mysql.com.
68		*
69		* <b>Notes</b>
70		* <ol>
71		* <li>
72		* Use JDBC URL of type jdbc:mysql://[host][:port]/dbname[?param=value[...]]
73		* ie. the simpliest one has 3 slashes: jdbc:mysql:///melatitest
74		* </li>
75		* <li>
76		* Don't use asterix * for password, leave it empty (end of line), as:
77		* <pre>
78		* org.Melati.LogicalDatabase.melatitest.pass=
79		* </pre>
80		*
81		* or use explicit username and password and
82		* <pre>
83		* GRANT ALL PRIVILEGES ON dbname
84		* TO username@localhost IDENTIFIED BY 'password';
85		* </pre>
86		* </li>
87		* <li>
88		* If you want to use double quotes to delimit table and column names then
89		* start MySQL in ANSI mode and modify getQuotedName(String name).
90		* </li>
91		* <li>
92		* Start MySQL with transactioned tables as default. InnoDB is stable,
93		* BDB nearly stable.
94		* <code>getConnection</code> now returns a <code>Connection</code>
95		* with <code>autocommit</code> turned off through JDBC.
96		*
97		* BDB tables of MySQL-Max 3.23.49 don't support full transactions
98		* - they lock whole table instead, until commit/rollback is called.
99		* According to MySQL 4.0.2-alpha doc, interface between MySQL and
100		* BDB tables is still improved.
101		*
102		* As I tested MySQL-Max 3.23.49, InnoDB has correct transactions,
103		* however database size must be specified and reserved in advance
104		* in one file, that is share by all InnoDB tables.
105		* Set in /etc/my.cnf by line like:
106		* innodb_data_file_path=ibdata1:30M
107		* <pre>
108		* run
109		* safe_mysqld --user=mysql --ansi --default-table-type=InnoDB
110		* </pre>
111		* After it created and initialised dB file /var/lib/mysql/ibdata1
112		* of 30MB, it creates 2 own log files /var/lib/mysql/ib_logfile0
113		* and ib_logfile1, both of size 5MB.
114		* <br>
115		* The table type is currently hardcoded in <tt>createTableOptions</tt>.
116		*
117		* </li>
118		* <li>
119		* <tt>boolean</tt> type works (both applications melatitest and contacts).
120		* Because MySQL returns metainfo about BOOL as TINYINT.
121		* </li>
122		* </ol>
123		*/
124		public class MySQL extends AnsiStandard {
125
126		/** Size of indexes. */
127		public static final int indexSize = 30;
128		/** Size of MySQL text fields. */
129		public static final int mysqlTextSize = 65535;
130
131		/** Constructor - sets driver. */
132	1	public MySQL() {
133	1	setDriverClassName("org.gjt.mm.mysql.Driver");
134	1	}
135
136		/**
137		* {@inheritDoc}
138		* @see org.melati.poem.dbms.AnsiStandard#createTableOptionsSql()
139		*/
140		public String createTableOptionsSql() {
141		//MySQL 5.5 doesn't support "type" table option, instead it uses ENGINE option.
142		//return " TYPE='InnoDB' ";
143
144	1	return " ENGINE='InnoDB' ";
145		}
146
147		/**
148		* Retrieve an SQL type keyword used by the DBMS
149		* for the given Melati type name.
150		*
151		* @param sqlTypeName the Melati internal type name
152		* @return this dbms specific type keyword
153		*/
154		public String getSqlDefinition(String sqlTypeName) {
155	4	if(sqlTypeName.equals("BOOLEAN")) return "bool";
156	3	return super.getSqlDefinition(sqlTypeName);
157		}
158
159		@Override
160		public String getStringSqlDefinition(int size) throws SQLException {
161	2	if (size < 0) {
162	1	return "text";
163		}
164	1	return super.getStringSqlDefinition(size); //VARCHAR(size) is OK
165		}
166
167		/**
168		* Ignores size.
169		*/
170		@Override
171		public String getBinarySqlDefinition(int size) {
172	1	return "BLOB";
173		}
174
175		@Override
176		public String getQuotedName(String name) {
177	19	return unreservedName(name);
178		}
179
180		/**
181		* Translates a MySQL String into a Poem <code>StringPoemType</code>.
182		*/
183		public static class MySQLStringPoemType extends StringPoemType {
184
185		/**
186		* Constructor.
187		* @param nullable whether nullable
188		* @param size size
189		*/
190		public MySQLStringPoemType(boolean nullable, int size) {
191	0	super(nullable, size);
192	0	}
193
194		//MySQL returns metadata info size 65535 for TEXT type
195		protected boolean _canRepresent(SQLPoemType<?> other) {
196	0	return
197	0	sqlTypeCode() == other.sqlTypeCode()
198		&& other instanceof StringPoemType
199	0	&& (getSize()<0 \|\| getSize()==mysqlTextSize \|\| getSize()>=((StringPoemType)other).getSize());
200		}
201
202		/**
203		* @see org.melati.poem.SizedAtomPoemType#withSize(int)
204		*/
205		public SizedAtomPoemType<String> withSize(int newSize) {
206	0	if (newSize==mysqlTextSize)
207	0	return super.withSize(-1);
208	0	return super.withSize(newSize);
209		}
210		}
211
212		/**
213		* Translates a MySQL Boolean into a Poem <code>BooleanType</code>.
214		*/
215	0	public static class MySQLBooleanPoemType extends BooleanPoemType {
216		/**
217		* Constructor.
218		* @param nullable whether nullable
219		*/
220		public MySQLBooleanPoemType(boolean nullable) {
221	0	super(nullable);
222	0	}
223
224		protected Boolean _getRaw(ResultSet rs, int col) throws SQLException {
225	0	synchronized (rs) {
226	0	int i = rs.getInt(col);
227	0	return rs.wasNull() ? null :
228		(i==1 ? Boolean.TRUE : Boolean.FALSE);
229	0	}
230		}
231		/*
232		protected Object _getRaw(ResultSet rs, int col) throws SQLException {
233		synchronized (rs) {
234		String v = rs.getString(col);
235		return rs.wasNull() ? null :
236		(v.equals("t") ? Boolean.TRUE : Boolean.FALSE);
237		}
238		}
239		*/
240
241		protected void _setRaw(PreparedStatement ps, int col, Object bool)
242		throws SQLException {
243	0	ps.setInt(col, ((Boolean)bool).booleanValue() ? 1 : 0);
244	0	}
245		/*
246		protected void _setRaw(PreparedStatement ps, int col, Object bool)
247		throws SQLException {
248		if (bool instanceof Boolean && bool == Boolean.TRUE)
249		ps.setString(col, "t");
250		else
251		ps.setString(col, "f");
252		}
253		*/
254
255		/**
256		* We could use original method from BooleanPoemType,
257		* it too recognizes 0/1.
258		* {@inheritDoc}
259		* @see org.melati.poem.BooleanPoemType#_rawOfString(java.lang.String)
260		*/
261		protected Boolean _rawOfString(String rawString)
262		throws ParsingPoemException {
263	0	rawString = rawString.trim();
264	0	switch (rawString.charAt(0)) {
265	0	case '1': return Boolean.TRUE;
266	0	case '0': return Boolean.FALSE;
267	0	default: throw new ParsingPoemException(this, rawString);
268		}
269		}
270		}
271
272		/**
273		* Translates a MySQL Blob into a Poem <code>IntegerPoemType</code>.
274		*/
275		public static class BlobPoemType extends BinaryPoemType {
276
277		/**
278		* Constructor.
279		* @param nullable whether nullable
280		* @param size size
281		*/
282		public BlobPoemType(boolean nullable, int size) {
283	0	super(nullable, size);
284	0	}
285
286		protected boolean _canRepresent(SQLPoemType<?> other) {
287	0	return other instanceof BinaryPoemType;
288		}
289
290		@Override
291		public <O>PoemType<O> canRepresent(PoemType<O> other) {
292	0	return other instanceof BinaryPoemType &&
293	0	!(!getNullable() && ((BinaryPoemType)other).getNullable()) ?
294		other : null;
295		}
296		}
297
298		@Override
299		public <S,O>PoemType<O> canRepresent(PoemType<S> storage, PoemType<O> other) {
300	17	if (storage instanceof IntegerPoemType &&
301		other instanceof BooleanPoemType
302	2	&& !(!storage.getNullable() && other.getNullable()) // Nullable may represent not nullable
303		) {
304	2	return other;
305		} else {
306	15	return storage.canRepresent(other);
307		}
308		}
309
310		@Override
311		public SQLPoemType<?> defaultPoemTypeOfColumnMetaData(ResultSet md)
312		throws SQLException {
313	0	boolean nullable = md.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
314	0	String typeName = md.getString("TYPE_NAME").toLowerCase();
315	0	if(typeName.equals("blob"))
316	0	return new BlobPoemType(nullable, md.getInt("COLUMN_SIZE"));
317	0	else if(typeName.equals("text"))
318	0	return new MySQLStringPoemType(nullable, md.getInt("COLUMN_SIZE"));
319	0	else if(typeName.equals("smallint"))
320	0	return new IntegerPoemType(nullable);
321		// See http://www.postgresql.org/docs/current/interactive/datatype-datetime.html
322	0	else if(typeName.equals("datetime"))
323	0	return new TimestampPoemType(nullable);
324	0	else if(typeName.equals("set"))
325	0	return new StringPoemType(nullable, md.getInt("COLUMN_SIZE"));
326
327	0	else if(typeName.equals("char"))
328	0	return new StringPoemType(nullable, md.getInt("COLUMN_SIZE"));
329		// MySQL:BOOL --> MySQL:TINYINT --> Melati:boolean backward mapping
330	0	else if(typeName.equals("tinyint"))
331	0	return new MySQLBooleanPoemType(nullable);
332		else
333	0	return super.defaultPoemTypeOfColumnMetaData(md);
334		}
335
336
337		@Override
338		public SQLPoemException exceptionForUpdate(
339		Table<?> table, String sql, boolean insert, SQLException e) {
340
341	0	String m = e.getMessage();
342
343		// MySQL's duplicate key (or any unique field) message is:
344		// "ERROR 1062: Duplicate entry '106' for key 1"
345		// Duplicate index value <--\| \|
346		// \|
347		// Which 'index' (unique field) it is, in <--\|
348		// order as table was defined, starting from 1.
349
350	0	if (m != null &&
351	0	m.indexOf("1062") >= 0) {
352
353		// It's not simple as in Postgres. This duplicated 'index' is one
354		// of possibly more unique columns. That involves searching for its
355		// column. For error "Duplicate entry '106' for key 4"
356		// we search 4th unique field = we loop over columns, skip first 3 that
357		// are unique and return 4th unique.
358
359		try { //Try parsing error message.
360
361		int preIndex, postIndex; //Places of apostrophes around index value
362		int preColumn; //Place of "key ", which is in front of column number
363
364	0	preIndex= m.indexOf('\'');
365	0	postIndex= m.lastIndexOf('\'');
366	0	preColumn= m.indexOf("key ");
367
368	0	String indexValue= m.substring(preIndex+1, postIndex);
369	0	String indexColumn= m.substring(preColumn+4);
370
371	0	System.err.println("Duplicated value " + indexValue +
372		" of " + indexColumn + "th unique field.");
373
374	0	int indexNum= Integer.parseInt(indexColumn);
375	0	Column<?> column = table.troidColumn(); //Just to satisfy compiler.
376		//At the end, it will (should) be our column anyway.
377
378	0	for(Enumeration<Column<?>> columns = table.columns(); columns.hasMoreElements();) {
379	0	column = columns.nextElement();
380	0	if(column.getUnique() && (--indexNum == 0))
381	0	break; //We found it!
382		}
383		//Now, it's found & indexNum==0.
384	0	if(indexNum==0)
385	0	return new DuplicateKeySQLPoemException(column, sql, insert, e);
386	0	} catch(NumberFormatException f) {
387	0	throw new RuntimeException(
388		"Number format exception parsing dbms error.");
389	0	}
390	0	return new DuplicateKeySQLPoemException(table, sql, insert, e);
391		}
392	0	return super.exceptionForUpdate(table, sql, insert, e);
393		}
394
395		@Override
396		public String unreservedName(String name) {
397	20	if(name.equalsIgnoreCase("group")) name = "poem_" + name;
398	20	if(name.equalsIgnoreCase("precision")) name = "poem_" + name;
399	20	if(name.equalsIgnoreCase("unique")) name = "poem_" + name;
400	20	return name;
401		}
402
403		@Override
404		public String melatiName(String name) {
405	3	if (name == null) return name;
406	2	if(name.equalsIgnoreCase("poem_group")) name = "group";
407	2	if(name.equalsIgnoreCase("poem_precision")) name = "precision";
408	2	if(name.equalsIgnoreCase("poem_unique")) name = "unique";
409	2	return name;
410		}
411
412		/**
413		* MySQL requires TEXT and BLOB field indices to have an
414		* explicit length, 30 should be fine.
415		*
416		* @return a snippet of sql to insert into an SQL statement.
417		*/
418		@Override
419		public String getIndexLength(Column<?> column) {
420	2	PoemType<?> t = column.getType();
421	2	if (t instanceof StringPoemType &&
422	1	((StringPoemType)t).getSize() < 0) return "(" + indexSize + ")";
423	1	if (t instanceof BinaryPoemType) return "(" + indexSize + ")";
424	1	return "";
425		}
426
427		@Override
428		public String givesCapabilitySQL(Integer userTroid, String capabilityExpr) {
429	1	return
430		"SELECT groupMembership.* " +
431		"FROM groupMembership LEFT JOIN groupCapability " +
432	1	"ON groupMembership." + getQuotedName("group") +
433	1	" = groupCapability." + getQuotedName("group") + " " +
434	1	"WHERE " + getQuotedName("user") + " = " + userTroid + " " +
435	1	"AND groupCapability." + getQuotedName("group") + " IS NOT NULL " +
436		"AND capability = " + capabilityExpr;
437		}
438
439		@Override
440		public String caseInsensitiveRegExpSQL(String term1, String term2) {
441	3	if (StringUtils.isQuoted(term2)) {
442	1	term2 = term2.substring(1, term2.length() - 1);
443		}
444	3	term2 = StringUtils.quoted(StringUtils.quoted(term2, '%'), '\'');
445
446	3	return term1 + " LIKE " + term2;
447		}
448
449		@Override
450		public String alterColumnNotNullableSQL(String tableName, Column<?> column) {
451	0	return "ALTER TABLE " + getQuotedName(tableName) +
452	0	" CHANGE " + getQuotedName(column.getName()) + " " + getQuotedName(column.getName()) +
453		" " +
454	0	column.getSQLType().sqlDefinition(this);
455		}
456
457		/**
458		* MySQL does not do the normal thing.
459		*/
460		@Override
461		public String alterColumnAddCommentSQL(Column<?> column, String comment) {
462	0	return null;
463		}
464
465		/**
466		* MySQL does not do the normal thing.
467		*/
468		@Override
469		public String alterTableAddCommentSQL(Table<?> table, String comment) {
470	0	return null;
471		}
472
473		}