From: Date: May 23 2008 6:04pm
Subject: Connector/NET commit: r1302 - in branches/5.2: . MySql.Data/Provider/Source
List-Archive: http://lists.mysql.com/commits/47005
X-Bug: 36694
Message-Id: <200805231604.m4NG4nQa032196@bk-internal.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Modified:
branches/5.2/CHANGES
branches/5.2/MySql.Data/Provider/Source/ISSchemaProvider.cs
branches/5.2/MySql.Data/Provider/Source/SchemaProvider.cs
Log:
- Changed how the procedure schema collection is retrieved. If 'use procedure bodies=true'
then we select on the mysql.proc table directly as this is up to 50x faster than our current
IS implementation. If 'use procedure bodies=false', then the IS collection is queried.
(bug #36694)
Modified: branches/5.2/CHANGES
===================================================================
--- branches/5.2/CHANGES 2008-05-22 18:41:55 UTC (rev 1301)
+++ branches/5.2/CHANGES 2008-05-23 16:04:48 UTC (rev 1302)
@@ -3,7 +3,11 @@
of hashes for lookups
- Fixed problem where some tables that support the web providers used the latin1
character set instead of the database default. (bug #36444)
-
+- Changed how the procedure schema collection is retrieved. If 'use procedure bodies=true'
+ then we select on the mysql.proc table directly as this is up to 50x faster than our current
+ IS implementation. If 'use procedure bodies=false', then the IS collection is queried.
+ (bug #36694)
+
Version 5.2.2 -
- Fixed profile provider that would throw an exception if you were updating
a profile that already existed.
Modified: branches/5.2/MySql.Data/Provider/Source/ISSchemaProvider.cs
===================================================================
--- branches/5.2/MySql.Data/Provider/Source/ISSchemaProvider.cs 2008-05-22 18:41:55 UTC (rev 1301)
+++ branches/5.2/MySql.Data/Provider/Source/ISSchemaProvider.cs 2008-05-23 16:04:48 UTC (rev 1302)
@@ -183,8 +183,13 @@
///
///
///
- private DataTable GetProcedures(string[] restrictions)
+ public override DataTable GetProcedures(string[] restrictions)
{
+ // if the user has said that we have access to mysql.proc then
+ // we use that as it is a lot faster
+ if (connection.Settings.UseProcedureBodies)
+ return base.GetProcedures(restrictions);
+
string[] keys = new string[4];
keys[0] = "ROUTINE_CATALOG";
keys[1] = "ROUTINE_SCHEMA";
Modified: branches/5.2/MySql.Data/Provider/Source/SchemaProvider.cs
===================================================================
--- branches/5.2/MySql.Data/Provider/Source/SchemaProvider.cs 2008-05-22 18:41:55 UTC (rev 1301)
+++ branches/5.2/MySql.Data/Provider/Source/SchemaProvider.cs 2008-05-23 16:04:48 UTC (rev 1302)
@@ -522,6 +522,76 @@
return dt;
}
+ public virtual DataTable GetProcedures(string[] restrictions)
+ {
+ DataTable dt = new DataTable("Procedures");
+ dt.Columns.Add(new DataColumn("SPECIFIC_NAME", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_CATALOG", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_SCHEMA", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_NAME", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_TYPE", typeof(string)));
+ dt.Columns.Add(new DataColumn("DTD_IDENTIFIER", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_BODY", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_DEFINITION", typeof(string)));
+ dt.Columns.Add(new DataColumn("EXTERNAL_NAME", typeof(string)));
+ dt.Columns.Add(new DataColumn("EXTERNAL_LANGUAGE", typeof(string)));
+ dt.Columns.Add(new DataColumn("PARAMETER_STYLE", typeof(string)));
+ dt.Columns.Add(new DataColumn("IS_DETERMINISTIC", typeof(string)));
+ dt.Columns.Add(new DataColumn("SQL_DATA_ACCESS", typeof(string)));
+ dt.Columns.Add(new DataColumn("SQL_PATH", typeof(string)));
+ dt.Columns.Add(new DataColumn("SECURITY_TYPE", typeof(string)));
+ dt.Columns.Add(new DataColumn("CREATED", typeof(DateTime)));
+ dt.Columns.Add(new DataColumn("LAST_ALTERED", typeof(DateTime)));
+ dt.Columns.Add(new DataColumn("SQL_MODE", typeof(string)));
+ dt.Columns.Add(new DataColumn("ROUTINE_COMMENT", typeof(string)));
+ dt.Columns.Add(new DataColumn("DEFINER", typeof(string)));
+
+ StringBuilder sql = new StringBuilder("SELECT * FROM mysql.proc WHERE 1=1");
+ if (restrictions != null)
+ {
+ if (restrictions.Length >= 2 && restrictions[1] != null)
+ sql.AppendFormat(" AND db LIKE '{0}'", restrictions[1]);
+ if (restrictions.Length >= 3 && restrictions[2] != null)
+ sql.AppendFormat(" AND name LIKE '{0}'", restrictions[2]);
+ if (restrictions.Length >= 4 && restrictions[3] != null)
+ sql.AppendFormat(" AND type LIKE '{0}'", restrictions[3]);
+ }
+
+ MySqlDataAdapter da = new MySqlDataAdapter(sql.ToString(), connection);
+ DataTable procs = new DataTable();
+ da.Fill(procs);
+
+ foreach (DataRow procRow in procs.Rows)
+ {
+ DataRow row = dt.NewRow();
+ row["SPECIFIC_NAME"] = procRow["specific_name"];
+ row["ROUTINE_CATALOG"] = DBNull.Value;
+ row["ROUTINE_SCHEMA"] = procRow["db"];
+ row["ROUTINE_NAME"] = procRow["name"];
+ row["ROUTINE_TYPE"] = procRow["type"];
+ row["DTD_IDENTIFIER"] =
+ procRow["type"].ToString().ToLower(CultureInfo.InvariantCulture) == "function" ?
+ procRow["returns"] : DBNull.Value;
+ row["ROUTINE_BODY"] = "SQL";
+ row["ROUTINE_DEFINITION"] = procRow["body"];
+ row["EXTERNAL_NAME"] = DBNull.Value;
+ row["EXTERNAL_LANGUAGE"] = DBNull.Value;
+ row["PARAMETER_STYLE"] = "SQL";
+ row["IS_DETERMINISTIC"] = procRow["is_deterministic"];
+ row["SQL_DATA_ACCESS"] = procRow["sql_data_access"];
+ row["SQL_PATH"] = DBNull.Value;
+ row["SECURITY_TYPE"] = procRow["security_type"];
+ row["CREATED"] = procRow["created"];
+ row["LAST_ALTERED"] = procRow["modified"];
+ row["SQL_MODE"] = procRow["sql_mode"];
+ row["ROUTINE_COMMENT"] = procRow["comment"];
+ row["DEFINER"] = procRow["definer"];
+ dt.Rows.Add(row);
+ }
+
+ return dt;
+ }
+
protected virtual DataTable GetCollections()
{
object[][] collections = new object[][]