Connector/NET commit: r1302 - in branches/5.2: . MySql.Data/Provider/Source

[rburnett]

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 @@
         /// </summary>
         /// <param name="restrictions"></param>
         /// <returns></returns>
-        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[][]