Implement MySqlConnection.GetSchema

[Thorium]

I would like to use some of .NET System.Data.Common database-interface features, like:

• DbConnection.GetSchema(...)
• MySqlDbType

For a start, these would be nice:

GetSchema "DataTypes":

• Returns a datatable with columns: DataType, TypeName, ProviderDbType
• Exposes the information how this provider maps .NET types to MySql database

GetSchema "Procedures":

• Returns a datatable with columns: "ROUTINE_TYPE", "ROUTINE_SCHEMA", "SPECIFIC_NAME"
• Type is e.g. a "FUNCTION" or "PROCEDURE"

[bgrainger]

The DbConnection.GetSchema method is documented here.

It's available in .NET 4.5+ and .NET Standard 2.0.

In Connector/NET, GetSchema returns a DataTable with 19 rows; each row defines a separate collectionName that can be passed to GetSchema(string). Schema restrictions are documented here.

This will need signficant integration testing to verify backwards compatibility with the schemas returned by Connector/NET.

[Thorium]

I think we can start with simple and throw NotSupportedException on runtime for collection names that are not yet implemented. It's still better than the current not-exist-anything-version.

[bgrainger]

Moved MySqlDbType (which may block this feature) to a separate issue: #362.

[Thorium]

After MySqlDbType fix, I think adding a method like this to class MySqlConnection would be a good start:

/// <summary>
/// System.Data.Common, initial implementation of API DBConnection.GetSchema(String)
/// Returns schema information for the data source of this DbConnection using the specified string for the schema name.
/// </summary>
/// <param name="collectionName">Specifies the name of the schema to return.</param>
/// <returns>A DataTable that contains schema information.</returns>
public override System.Data.DataTable GetSchema(string collectionName)
{
	var dt = new DataTable(collectionName);
	switch (collectionName)
	{
		case "DataTypes":
			dt.Columns.AddRange(new[] { // The names come from DbMetaDataColumnNames
				new DataColumn("DataType", typeof(string)),
				new DataColumn("TypeName", typeof(string)),
				new DataColumn("ProviderDbType", typeof(int))
			});

			foreach (MySqlDbType mapItem in Enum.GetValues(typeof(MySqlDbType)))
			{
				// Mapping needs a bit work...:
				var map = Types.TypeMapper.Mapper.GetDbTypeMapping(mapItem);

				dt.Rows.Add(new object[] {
					map.ClrType.Name,
					Enum.GetName(typeof(MySqlDbType), map.MySqlDbType),
					(int)map.DbType
				});
			}
			// Rows result should be like:
			//dt.Rows.Add(new object[] { "System.Int16", "smallint", 10 });
			//dt.Rows.Add(new object[] { "System.Int32", "int", 11 });
			//dt.Rows.Add(new object[] { "System.String","varchar",16 });
			//dt.Rows.Add(new object[] { "System.String","nvarchar",16 });
			//...

			return dt;

		case "Procedures":
			dt.Columns.AddRange(new[] {
					new DataColumn("ROUTINE_TYPE"),
					new DataColumn("ROUTINE_SCHEMA"),
					new DataColumn("SPECIFIC_NAME")
				});
			var procsQuery = "SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES;";
			if (m_connectionState != ConnectionState.Open)
			{
				this.Open();
			}
			using (var com = new MySqlCommand(procsQuery, this))
			using (var reader = com.ExecuteReader())
			{
				while (reader.Read())
				{
					dt.Rows.Add(new object[] { reader.GetString(0), reader.GetString(1), reader.GetString(2) });
				}
			}
			return dt;

		default:
			throw new NotImplementedException("Not yet supported: GetSchema(\"" + collectionName + "\"). Please send a PR.");
	}
}

[Thorium]

One more column, "CreateFormat", is important for DataTypes: That is where current MySql.Data exposes signed/unsigned information which is relevant for type-mapping. "TypeName" is just "int" but "CreateFormat" can be "int unsigned".

Edit: "CreateFormat" or "IsUnsigned". I don't mind which one, either of them is ok.

[Thorium]

As complete GetSchema("DataTypes") seems to be pretty static and not related to database, it can be easily printed out. IMHO most of the columns are not relevant.

TypeName	ProviderDbType	ColumnSize	CreateFormat	CreateParameters	DataType	IsAutoincrementable	IsBestMatch	IsCaseSensitive	IsFixedLength	IsFixedPrecisionScale	IsLong	IsNullable	IsSearchable	IsSearchableWithLike	IsUnsigned	MaximumScale	MinimumScale	IsConcurrencyType	IsLiteralSupported	LiteralPrefix	LiteralSuffix	NativeDataType
BIT	16	64	BIT		System.UInt64	False	True	False	False	True	False	True	True	False	False	0	0		False
BLOB	252	65535			System.Byte[]	False	True	False	False	False	True	True	False	False					False	0x
TINYBLOB	249	255			System.Byte[]	False	True	False	False	False	False	True	False	False					False	0x
MEDIUMBLOB	250	16777215			System.Byte[]	False	True	False	False	False	True	True	False	False					False	0x
LONGBLOB	251	4294967295			System.Byte[]	False	True	False	False	False	True	True	False	False					False	0x
BINARY	600	255	binary({0})	length	System.Byte[]	False	True	False	True	False	False	True	False	False					False	0x
VARBINARY	601	65535	varbinary({0})	length	System.Byte[]	False	True	False	True	False	True	True	False	False					False	0x
DATE	10	0	DATE		System.DateTime	False	True	False	True	True	False	True	True	False	False	0	0		False
DATETIME	12	0	DATETIME		System.DateTime	False	True	False	True	True	False	True	True	False	False	0	0		False
TIMESTAMP	7	0	TIMESTAMP		System.DateTime	False	True	False	True	True	False	True	True	False	False	0	0		False
TIME	11	0	TIME		System.TimeSpan	False	True	False	True	True	False	True	True	False	False	0	0		False
CHAR	254	0	CHAR({0})	size	System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
NCHAR	254	0	NCHAR({0})	size	System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
VARCHAR	253	0	VARCHAR({0})	size	System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
NVARCHAR	253	0	NVARCHAR({0})	size	System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
SET	248	0	SET		System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
ENUM	247	0	ENUM		System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
TINYTEXT	749	0	TINYTEXT		System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
TEXT	752	0	TEXT		System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
MEDIUMTEXT	750	0	MEDIUMTEXT		System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
LONGTEXT	751	0	LONGTEXT		System.String	False	True	False	False	True	False	True	True	True	False	0	0		False
DOUBLE	5	0	DOUBLE		System.Double	False	True	False	True	True	False	True	True	False	False	0	0		False
FLOAT	4	0	FLOAT		System.Single	False	True	False	True	True	False	True	True	False	False	0	0		False
TINYINT	1	0	TINYINT		System.SByte	True	True	False	True	True	False	True	True	False	False	0	0		False
SMALLINT	2	0	SMALLINT		System.Int16	True	True	False	True	True	False	True	True	False	False	0	0		False
INT	3	0	INT		System.Int32	True	True	False	True	True	False	True	True	False	False	0	0		False
YEAR	13	0	YEAR		System.Int32	False	True	False	True	True	False	True	True	False	False	0	0		False
MEDIUMINT	9	0	MEDIUMINT		System.Int32	True	True	False	True	True	False	True	True	False	False	0	0		False
BIGINT	8	0	BIGINT		System.Int64	True	True	False	True	True	False	True	True	False	False	0	0		False
DECIMAL	246	0	DECIMAL({0},{1})	precision,scale	System.Decimal	False	True	False	True	True	False	True	True	False	False	0	0		False
TINY INT	501	0	TINYINT UNSIGNED		System.Byte	True	True	False	True	True	False	True	True	False	True	0	0		False
SMALLINT	502	0	SMALLINT UNSIGNED		System.UInt16	True	True	False	True	True	False	True	True	False	True	0	0		False
MEDIUMINT	509	0	MEDIUMINT UNSIGNED		System.UInt32	True	True	False	True	True	False	True	True	False	True	0	0		False
INT	503	0	INT UNSIGNED		System.UInt32	True	True	False	True	True	False	True	True	False	True	0	0		False
BIGINT	508	0	BIGINT UNSIGNED		System.UInt64	True	True	False	True	True	False	True	True	False	True	0	0		False

[bgrainger]

Common Schema Collections are documented here.

[Thorium]

Yes, probably would be a good idea to implement some level MetaDataCollections so that the users would know supported collections of MySqlConnector.

[Thorium]

That being said, I'm currently already very happy with the current implementation.

[bgrainger]

The current output of GetSchema("DataTypes") is documented here: https://github.com/mysql-net/MySqlConnector/wiki/DataTypes-Schema-Collection

[bgrainger]

Implemented in 0.29.0.