MySQLTests.cs

using System;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using Xunit;

namespace Dapper.Tests
{
    /// <summary>
    /// If Docker Desktop is installed, run the following command to start a container suitable for the tests.
    /// <code>
    /// docker run -d -p 3306:3306 --name Dapper.Tests.MySQL -e MYSQL_DATABASE=tests -e MYSQL_USER=test -e MYSQL_PASSWORD=pass -e MYSQL_ROOT_PASSWORD=pass mysql
    /// </code>
    /// </summary>
    public class MySqlProvider : DatabaseProvider
    {
        public override DbProviderFactory Factory => MySqlConnector.MySqlConnectorFactory.Instance;
        public override string GetConnectionString() =>
            GetConnectionString("MySqlConnectionString", "Server=localhost;Database=tests;Uid=test;Pwd=pass;");

        public DbConnection GetMySqlConnection(bool open = true,
            bool convertZeroDatetime = false, bool allowZeroDatetime = false)
        {
            string cs = GetConnectionString();
            var csb = Factory.CreateConnectionStringBuilder()!;
            csb.ConnectionString = cs;
            ((dynamic)csb).AllowZeroDateTime = allowZeroDatetime;
            ((dynamic)csb).ConvertZeroDateTime = convertZeroDatetime;
            var conn = Factory.CreateConnection()!;
            conn.ConnectionString = csb.ConnectionString;
            if (open) conn.Open();
            return conn;
        }
    }
    public class MySQLTests : TestBase<MySqlProvider>
    {      
        [FactMySql]
        public void DapperEnumValue_Mysql()
        {
            using var conn = Provider.GetMySqlConnection();
            Common.DapperEnumValue(conn);
        }

        [FactMySql]
        public void Issue552_SignedUnsignedBooleans()
        {
            using (var conn = Provider.GetMySqlConnection(true, false, false))
            {
                conn.Execute(@"
CREATE TEMPORARY TABLE IF NOT EXISTS `bar` (
  `id` INT NOT NULL,
  `bool_val` BOOL NULL,
  PRIMARY KEY (`id`));
  
  truncate table bar;
  insert bar (id, bool_val) values (1, null);
  insert bar (id, bool_val) values (2, 0);
  insert bar (id, bool_val) values (3, 1);
  insert bar (id, bool_val) values (4, null);
  insert bar (id, bool_val) values (5, 1);
  insert bar (id, bool_val) values (6, 0);
  insert bar (id, bool_val) values (7, null);
  insert bar (id, bool_val) values (8, 1);");

                var rows = conn.Query<MySqlHasBool>("select * from bar;").ToDictionary(x => x.Id);

                Assert.Null(rows[1].Bool_Val);
                Assert.False(rows[2].Bool_Val);
                Assert.True(rows[3].Bool_Val);
                Assert.Null(rows[4].Bool_Val);
                Assert.True(rows[5].Bool_Val);
                Assert.False(rows[6].Bool_Val);
                Assert.Null(rows[7].Bool_Val);
                Assert.True(rows[8].Bool_Val);
            }
        }

        private class MySqlHasBool
        {
            public int Id { get; set; }
            public bool? Bool_Val { get; set; }
        }

        [FactMySql]
        public void Issue295_NullableDateTime_MySql_Default()
        {
            using var conn = Provider.GetMySqlConnection(true, false, false);
            Common.TestDateTime(conn);
        }

        [FactMySql]
        public void Issue295_NullableDateTime_MySql_ConvertZeroDatetime()
        {
            using var conn = Provider.GetMySqlConnection(true, true, false);
            Common.TestDateTime(conn);
        }

        [FactMySql(Skip = "See https://github.com/DapperLib/Dapper/issues/295, AllowZeroDateTime=True is not supported")]
        public void Issue295_NullableDateTime_MySql_AllowZeroDatetime()
        {
            using (var conn = Provider.GetMySqlConnection(true, false, true))
            {
                Common.TestDateTime(conn);
            }
        }

        [FactMySql(Skip = "See https://github.com/DapperLib/Dapper/issues/295, AllowZeroDateTime=True is not supported")]
        public void Issue295_NullableDateTime_MySql_ConvertAllowZeroDatetime()
        {
            using var conn = Provider.GetMySqlConnection(true, true, true);
            Common.TestDateTime(conn);
        }

        [FactMySql]
        public void Issue426_SO34439033_DateTimeGainsTicks()
        {
            using var conn = Provider.GetMySqlConnection(true, true, true);

            try { conn.Execute("drop table Issue426_Test"); } catch { /* don't care */ }
            try { conn.Execute("create table Issue426_Test (Id int not null, Time time not null)"); } catch { /* don't care */ }
            const long ticks = 553440000000;
            const int Id = 426;

            var localObj = new Issue426_Test
            {
                Id = Id,
                Time = TimeSpan.FromTicks(ticks) // from code example
            };
            conn.Execute("replace into Issue426_Test values (@Id,@Time)", localObj);

            var dbObj = conn.Query<Issue426_Test>("select * from Issue426_Test where Id = @id", new { id = Id }).Single();
            Assert.Equal(Id, dbObj.Id);
            Assert.Equal(ticks, dbObj.Time?.Ticks);
        }

        [FactMySql]
        public void SO36303462_Tinyint_Bools()
        {
            using var conn = Provider.GetMySqlConnection(true, true, true);

            try { conn.Execute("drop table SO36303462_Test"); } catch { /* don't care */ }
            conn.Execute("create table SO36303462_Test (Id int not null, IsBold tinyint not null);");
            conn.Execute("insert SO36303462_Test (Id, IsBold) values (1,1);");
            conn.Execute("insert SO36303462_Test (Id, IsBold) values (2,0);");
            conn.Execute("insert SO36303462_Test (Id, IsBold) values (3,1);");

            var rows = conn.Query<SO36303462>("select * from SO36303462_Test").ToDictionary(x => x.Id);
            Assert.Equal(3, rows.Count);
            Assert.True(rows[1].IsBold);
            Assert.False(rows[2].IsBold);
            Assert.True(rows[3].IsBold);
        }

        [FactMySql]
        public void Issue1277_ReaderSync()
        {
            using var conn = Provider.GetMySqlConnection();

            try { conn.Execute("drop table Issue1277_Test"); } catch { /* don't care */ }
            conn.Execute("create table Issue1277_Test (Id int not null, IsBold tinyint not null);");
            conn.Execute("insert Issue1277_Test (Id, IsBold) values (1,1);");
            conn.Execute("insert Issue1277_Test (Id, IsBold) values (2,0);");
            conn.Execute("insert Issue1277_Test (Id, IsBold) values (3,1);");

            using (var reader = conn.ExecuteReader(
                "select * from Issue1277_Test where Id < @id",
                new { id = 42 }))
            {
                var table = new DataTable();
                table.Load(reader);
                Assert.Equal(2, table.Columns.Count);
                Assert.Equal(3, table.Rows.Count);
            }
        }

        [FactMySql]
        public async Task Issue1277_ReaderAsync()
        {
            using var conn = Provider.GetMySqlConnection();

            try { await conn.ExecuteAsync("drop table Issue1277_Test"); } catch { /* don't care */ }
            await conn.ExecuteAsync("create table Issue1277_Test (Id int not null, IsBold tinyint not null);");
            await conn.ExecuteAsync("insert Issue1277_Test (Id, IsBold) values (1,1);");
            await conn.ExecuteAsync("insert Issue1277_Test (Id, IsBold) values (2,0);");
            await conn.ExecuteAsync("insert Issue1277_Test (Id, IsBold) values (3,1);");

            using (var reader = await conn.ExecuteReaderAsync(
                "select * from Issue1277_Test where Id < @id",
                new { id = 42 }))
            {
                var table = new DataTable();
                table.Load(reader);
                Assert.Equal(2, table.Columns.Count);
                Assert.Equal(3, table.Rows.Count);
            }
        }

        private class SO36303462
        {
            public int Id { get; set; }
            public bool IsBold { get; set; }
        }

        public class Issue426_Test
        {
            public long Id { get; set; }
            public TimeSpan? Time { get; set; }
        }

        [AttributeUsage(AttributeTargets.Method, AllowMultiple = false)]
        public class FactMySqlAttribute : FactAttribute
        {
            public override string? Skip
            {
                get { return unavailable ?? base.Skip; }
                set { base.Skip = value; }
            }

            private static readonly string? unavailable;

            static FactMySqlAttribute()
            {
                try
                {
                    using (DatabaseProvider<MySqlProvider>.Instance.GetMySqlConnection(true)) { /* just trying to see if it works */ }
                }
                catch (Exception ex)
                {
                    unavailable = $"MySql is unavailable: {ex.Message}";
                }
            }
        }
    }
}