Eric Bouwsema - 2008-05-23

I've added the ability for MySQL to check and quote reserved words.  The code is based on the code in the Firebird provider, with the reserved words pulled from: http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html. 

I've only done very basic testing on it, but expect it should work fine (NUnit was not working correctly for me as I'm running VS 2005 rather than 2008).

File: MySQLFactory.cs

/*
* MySql specifics
* Copyright (C) 2004 Morten Mertner
*
* This library is free software; you can redistribute it and/or modify it
* under the terms of the GNU Lesser General Public License 2.1 or later, as
* published by the Free Software Foundation. See the included License.txt
* or http://www.gnu.org/copyleft/lesser.html for details.
*
* $Id: MySQLFactory.cs 1232 2008-03-14 05:36:00Z mm $
*/

using System;
using System.Data;
using Gentle.Common;
using Gentle.Framework;
using MySql.Data.MySqlClient;
using System.Collections;

namespace Gentle.Provider.MySQL
{
    /// <summary>
    /// This class is an implementation of the <see cref="GentleSqlFactory"/> class for the MySQL RDBMS.
    /// </summary>
    public class MySQLFactory : GentleSqlFactory
    {
        public MySQLFactory(IGentleProvider provider)
            : base(provider)
        {
        }

        /// <summary>
        /// Obtain the integer value of the database type corresponding to the given system type.
        /// The value returned must be castable to a valid type for the current persistence engine.
        /// </summary>
        /// <param name="type">The system type.</param>
        /// <returns>The corresponding database type.</returns>
        public override long GetDbType(Type type)
        {
            MySqlDbType result = MySqlDbType.Int32;
            if (type.Equals(typeof(int)) || type.IsEnum)
            {
                result = MySqlDbType.Int32;
            }
            else if (type.Equals(typeof(long)))
            {
                result = MySqlDbType.Int64;
            }
            else if (type.Equals(typeof(double)) || type.Equals(typeof(Single)))
            {
                result = MySqlDbType.Double;
            }
            else if (type.Equals(typeof(decimal)))
            {
                result = MySqlDbType.Decimal;
            }
            else if (type.Equals(typeof(DateTime)))
            {
                result = MySqlDbType.Datetime;
            }
            else if (type.Equals(typeof(bool)) || type.Equals(typeof(Byte)) || type.Equals(typeof(byte)))
            {
                result = MySqlDbType.Byte;
            }
            else if (type.Equals(typeof(string)))
            {
                result = MySqlDbType.String;
            }
            else if (type.Equals(typeof(byte[])) || type.Equals(typeof(Byte[])))
            {
                // hmm.. possible type size loss here as we dont know the length of the array
                result = MySqlDbType.LongBlob;
            }
            else
            {
                Check.Fail(Error.UnsupportedPropertyType, type.Name, provider.Name);
            }
            return (long)result;
        }

        /// <summary>
        /// This method converts the given string (as extracted from the database system tables)
        /// to the corresponding type enumeration value.
        /// </summary>
        /// <param name="dbType">The name of the type with the database engine used.</param>
        /// <param name="isUnsigned">A boolean value indicating whether the type is unsigned. This
        /// is not supported by most engines and/or data providers and is thus fairly useless at
        /// this point.</param>
        /// <returns>The value of the corresponding database type enumeration. The enum is converted
        /// to its numeric (long) representation because each provider uses its own enum (and they
        /// are not compatible with the generic DbType defined in System.Data).</returns>
        public override long GetDbType(string dbType, bool isUnsigned)
        {
            switch (dbType)
            {
                case "byte": // 1
                    return (long)MySqlDbType.Byte;
                case "tinyint": // 1
                    return (long)MySqlDbType.Byte;
                case "smallint": // 2
                    return (long)MySqlDbType.Int16;
                case "int": // 4
                    return (long)MySqlDbType.Int32;
                case "bigint": // 8
                    return (long)MySqlDbType.Int64;
                case "float": // 8
                    return (long)MySqlDbType.Float;
                case "datetime": // 8
                    return (long)MySqlDbType.Datetime;
                case "decimal":
                case "numeric":
                    return (long)MySqlDbType.Decimal;
                case "char":
                    return (long)MySqlDbType.String;
                case "varchar":
                    return (long)MySqlDbType.VarChar;
                case "text":
                    return (long)MySqlDbType.String;
                case "tinyblob":
                    return (long)MySqlDbType.TinyBlob;
                case "blob":
                    return (long)MySqlDbType.Blob;
                case "mediumblob":
                    return (long)MySqlDbType.MediumBlob;
                case "longblob":
                    return (long)MySqlDbType.LongBlob;
                case "enum":
                    return (long)MySqlDbType.Enum;
                default:
                    if (!GentleSettings.AnalyzerSilent)
                    {
                        Check.Fail(Error.UnsupportedColumnType, dbType, provider.Name);
                    }
                    return NO_DBTYPE; // unreachable
            }
        }

        /// <summary>
        /// This method assumes that the tableName and identityColumn parameters are passed
        /// in the correct case.
        /// Please refer to the documentation of <see cref="GentleSqlFactory"/> for details on the
        /// purpose of this method.
        /// </summary>
        /// <param name="sql">The sql string to which we should append</param>
        /// <param name="om">An <see cref="Gentle.Framework.ObjectMap"/> instance of the object for which to retrieve the identity select</param>
        /// <returns>The modified sql string which also retrieves the identity value</returns>
        public override string GetIdentitySelect(string sql, ObjectMap om)
        {
            return String.Format("{0}{1} {2}", sql, GetStatementTerminator(),
                                  "select LAST_INSERT_ID()");
        }

        /// <summary>
        /// Please refer to the documentation of <see cref="GentleSqlFactory"/> for details.
        /// </summary>
        public override string GetParameterPrefix()
        {
            return "?";
        }

        /// <summary>
        /// Please refer to the documentation of <see cref="GentleSqlFactory"/> for details.
        /// </summary>
        public override char GetQuoteCharacter()
        {
            return '"'; // was: '`';
        }

        /// <summary>
        /// Please refer to the documentation of <see cref="GentleSqlFactory"/> for details.
        /// </summary>
        public override void AddParameter(IDbCommand cmd, string name, long dbType)
        {
            try
            {
                MySqlCommand myc = (MySqlCommand)cmd;
                MySqlParameter param = new MySqlParameter(name, (MySqlDbType)dbType);
                param.Direction = ParameterDirection.Input;
                myc.Parameters.Add(param);
            }
            catch (Exception e)
            {
                Check.Fail(Error.Unspecified, e.Message);
                throw new GentleException(Error.Unspecified, "Unreachable code.");
            }
        }

        #region "  Reserved Words  "
        // Added: May 23, 2008
        // Author: Eric Bouwsema - eric@solarbotics.com
        // Comments: Based on code written into Firebird Provider.

        ///    <summary>
        ///    Obtain a quoted    version    of the reserved    word to    allow    the    reserved word    to be   
        ///    used in    queries    anyway.    If a reserved    word cannot    be quoted    this method    should
        ///    raise    an error informing the user    that they    need to    pick a different name.
        ///    </summary>
        ///    <returns>The given reserved    word or    field    quoted to    avoid    errors.</returns>
        public override bool IsReservedWord(string word)
        {
            Check.VerifyNotNull(word, Error.NullParameter, "word");

            // Trim whitespaces
            word = word.Trim();

            // Tokens should not contain spaces or special characters
            // TODO: Add other characters not allowed (,;.:) (do they get
            // filtered before?)
            if (word.IndexOfAny(new char[] { ' ', '-' }) >= 0)
            {
                return true;
            }

            // Lookup word in the list of reserved words
            if (reservedWords.IsReservedWord(word))
            {
                return true;
            }

            return false;
        }

        ///    <summary>
        ///    Obtain a quoted    version    of the reserved    word to    allow    the    reserved word    to be   
        ///    used in    queries    anyway.    If a reserved    word cannot    be quoted    this method    should
        ///    raise    an error informing the user    that they    need to    pick a different name.
        ///    </summary>
        ///    <returns>The given reserved    word or    field    quoted to    avoid    errors.</returns>
        public override string QuoteReservedWord(string word)
        {
            return String.Format("`{0}`", word);
        }

        private static ReservedWords reservedWords = new ReservedWords();
        private class ReservedWords
        {
            ///    <summary>
            ///    Create a new <see    cref="ReservedWords"/> instance    and    fill
            ///    internal <see    cref="Hashtable"/> with    reserved words for fast
            ///    retrieval.
            ///    </summary>
            public ReservedWords()
            {
                // Create    hashtable
                reservedWordsHashtable = new Hashtable(
                    // Initialize    the    list to    contain    the    number of    elements in    the    list
                    reservedWordsList.Length,
                    // Do    case-insensitive search/hashing
                    StringComparer.InvariantCultureIgnoreCase);
                // Fill    values
                foreach (string reservedWord in reservedWordsList)
                {
                    reservedWordsHashtable.Add(reservedWord, true);
                }
            }

            ///    <summary>
            ///    Determine    if the specified token is    a    reserved word    in the current
            ///    implementation.    Works    case insensitive.
            ///    </summary>
            ///    <param name="Token">The    token    that should    be checked.</param>
            ///    <returns><see    langword="true"/>    if the specified token is    a    reserved
            ///    word,    <see langword="false"/>    if not.</returns>
            ///    <example>
            ///    The    following    call returns <see    langword="false"/>:
            ///    <code>IsReservedWord("FIRSTNAME");</code>
            ///    The    following    call returns <see    langword="true"/>:
            ///    <code>IsReservedWord("VARCHAR");</code>
            ///</example>
            public bool IsReservedWord(string Token)
            {
                return reservedWordsHashtable.ContainsKey(Token);
            }

            ///    <summary>
            ///    <see cref="Hashtable"/>    containing all reserved    words    for    fast lookup.
            ///    </summary>
            private Hashtable reservedWordsHashtable;

            //Source: http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html
            private string[] reservedWordsList = new string[]
             {
                "ADD","ALL","ALTER","ANALYZE","AND",
                "AS","ASC","ASENSITIVE","BEFORE","BETWEEN",
                "BIGINT","BINARY","BLOB","BOTH","BY",
                "CALL","CASCADE","CASE","CHANGE","CHAR",
                "CHARACTER","CHECK","COLLATE","COLUMN","COLUMNS",
                "CONDITION","CONNECTION","CONSTRAINT","CONTINUE","CONVERT",
                "CREATE","CROSS","CURRENT_DATE","CURRENT_TIME",
                "CURRENT_TIMESTAMP","CURRENT_USER","CURSOR","DATABASE",
                "DATABASES","DAY_HOUR","DAY_MICROSECOND","DAY_MINUTE","DAY_SECOND",
                "DEC","DECIMAL","DECLARE","DEFAULT","DELAYED",
                "DELETE","DESC","DESCRIBE","DETERMINISTIC","DISTINCT",
                "DISTINCTROW","DIV","DOUBLE","DROP","DUAL",
                "EACH","ELSE","ELSEIF","ENCLOSED","ESCAPED","EXISTS",
                "EXIT","EXPLAIN","FALSE","FETCH","FIELDS","FLOAT",
                "FLOAT4","FLOAT8","FOR","FORCE","FOREIGN",
                "FROM","FULLTEXT","GOTO","GRANT","GROUP","HAVING",
                "HIGH_PRIORITY","HOUR_MICROSECOND","HOUR_MINUTE","HOUR_SECOND",
                "IF","IGNORE","IN","INDEX","INFILE","INNER",
                "INOUT","INSENSITIVE","INSERT","INT",
                "INT1","INT2","INT3","INT4","INT8",
                "INTEGER","INTERVAL","INTO","IS",
                "ITERATE","JOIN","KEY","KEYS","KILL",
                "LABEL","LEADING","LEAVE","LEFT","LIKE",
                "LIMIT","LINES","LOAD","LOCALTIME","LOCALTIMESTAMP",
                "LOCK","LONG","LONGBLOB","LONGTEXT","LOOP",
                "LOW_PRIORITY","MATCH","MEDIUMBLOB","MEDIUMINT",
                "MEDIUMTEXT","MIDDLEINT","MINUTE_MICROSECOND","MINUTE_SECOND",
                "MOD","MODIFIES","NATURAL","NOT","NO_WRITE_TO_BINLOG",
                "NULL","NUMERIC","ON","OPTIMIZE","OPTION",
                "OPTIONALLY","OR","ORDER","OUT","OUTER",
                "OUTFILE","PRECISION","PRIMARY","PRIVILEGES","PROCEDURE",
                "PURGE","READ","READS","REAL","REFERENCES",
                "REGEXP","RELEASE","RENAME","REPEAT","REPLACE",
                "REQUIRE","RESTRICT","RETURN","REVOKE","RIGHT",
                "RLIKE","SCHEMA","SCHEMAS","SECOND_MICROSECOND",
                "SELECT","SENSITIVE","SEPARATOR","SET","SHOW",
                "SMALLINT","SONAME","SPATIAL","SPECIFIC","SQL",
                "SQLEXCEPTION","SQLSTATE","SQLWARNING","SQL_BIG_RESULT","SQL_CALC_FOUND_ROWS",
                "SQL_SMALL_RESULT","SSL","STARTING","STRAIGHT_JOIN","TABLE","TABLES",
                "TERMINATED","THEN","TINYBLOB","TINYINT","TINYTEXT","TO",
                "TRAILING","TRIGGER","TRUE","UNDO","UNION","UNIQUE",
                "UNLOCK","UNSIGNED","UPDATE","UPGRADE","USAGE","USE",
                "USING","UTC_DATE","UTC_TIME","UTC_TIMESTAMP","VALUES",
                "VARBINARY","VARCHAR","VARCHARACTER","VARYING","WHEN",
                "WHERE","WHILE","WITH","WRITE","XOR","YEAR_MONTH",
                "ZEROFILL"
           };
        }
    }
        #endregion
}