Modified:
trunk/CHANGES
trunk/TestSuite/GetSchemaTests.cs
trunk/mysqlclient/Resources.resx
trunk/mysqlclient/SchemaProvider.cs
trunk/mysqlclient/common/ContextString.cs
Log:
ContextString
-------------
Added some IndexOf methods that take a startindex parameter. This allows code to move through a buffer looking for symbols in a contextual manner.
Resources.resx
--------------
Added a string for the exception that can be thrown if we fail to parse the fk definition.
SchemaProvider
--------------
Added foreign key support
GetSchemaTests
--------------
Added foreign key test cases
Modified: trunk/CHANGES
===================================================================
--- trunk/CHANGES 2006-09-26 23:05:49 UTC (rev 366)
+++ trunk/CHANGES 2006-09-27 20:58:20 UTC (rev 367)
@@ -17,6 +17,7 @@
Fixed problem with executing a Fill after a FillSchema
Implemented CommandTimeout for non-batch queries
Fixed socket create code related to IPv6 (thanks Mark!)
+ Added foreign key support to GetSchema
Version 5.0.0.0 (Alpha)
Modified: trunk/TestSuite/GetSchemaTests.cs
===================================================================
--- trunk/TestSuite/GetSchemaTests.cs 2006-09-26 23:05:49 UTC (rev 366)
+++ trunk/TestSuite/GetSchemaTests.cs 2006-09-27 20:58:20 UTC (rev 367)
@@ -367,5 +367,95 @@
Assert.AreEqual("FLOAT", parameters.Rows[3][7].ToString().ToUpper());
}
- }
+ [Test]
+ public void SingleForeignKey()
+ {
+ execSQL("DROP TABLE IF EXISTS child");
+ execSQL("DROP TABLE IF EXISTS parent");
+ execSQL("CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB");
+ execSQL("CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), " +
+ "CONSTRAINT c1 FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE) TYPE=INNODB");
+ string[] restrictions = new string[4];
+ restrictions[0] = null;
+ restrictions[1] = "test";
+ restrictions[2] = "child";
+ DataTable dt = conn.GetSchema("Foreign Keys", restrictions);
+ Assert.AreEqual(1, dt.Rows.Count);
+ DataRow row = dt.Rows[0];
+ Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]);
+ Assert.AreEqual("test", row["CONSTRAINT_SCHEMA"]);
+ Assert.AreEqual("c1", row["CONSTRAINT_NAME"]);
+ Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]);
+ Assert.AreEqual("test", row["TABLE_SCHEMA"]);
+ Assert.AreEqual("child", row["TABLE_NAME"]);
+ Assert.AreEqual("parent_id", row["COLUMN_NAME"]);
+ Assert.AreEqual(0, row["ORDINAL_POSITION"]);
+ Assert.AreEqual("test", row["REFERENCED_TABLE_SCHEMA"]);
+ Assert.AreEqual("parent", row["REFERENCED_TABLE_NAME"]);
+ Assert.AreEqual("id", row["REFERENCED_COLUMN_NAME"]);
+ }
+
+ [Test]
+ public void MultiSingleForeignKey()
+ {
+ execSQL("DROP TABLE IF EXISTS product_order");
+ execSQL("DROP TABLE IF EXISTS product");
+ execSQL("DROP TABLE IF EXISTS customer");
+ execSQL("CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, " +
+ "price DECIMAL, PRIMARY KEY(category, id)) TYPE=INNODB");
+ execSQL("CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB");
+ execSQL("CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, " +
+ "product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, " +
+ "PRIMARY KEY(no), INDEX (product_category, product_id), " +
+ "FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) " +
+ "ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), " +
+ "FOREIGN KEY (customer_id) REFERENCES customer(id)) TYPE=INNODB");
+
+ string[] restrictions = new string[4];
+ restrictions[0] = null;
+ restrictions[1] = "test";
+ restrictions[2] = "product_order";
+ DataTable dt = conn.GetSchema("Foreign Keys", restrictions);
+ Assert.AreEqual(3, dt.Rows.Count);
+ DataRow row = dt.Rows[0];
+ Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]);
+ Assert.AreEqual("test", row["CONSTRAINT_SCHEMA"]);
+ Assert.AreEqual("product_order_ibfk_1", row["CONSTRAINT_NAME"]);
+ Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]);
+ Assert.AreEqual("test", row["TABLE_SCHEMA"]);
+ Assert.AreEqual("product_order", row["TABLE_NAME"]);
+ Assert.AreEqual("product_category", row["COLUMN_NAME"]);
+ Assert.AreEqual(0, row["ORDINAL_POSITION"]);
+ Assert.AreEqual("test", row["REFERENCED_TABLE_SCHEMA"]);
+ Assert.AreEqual("product", row["REFERENCED_TABLE_NAME"]);
+ Assert.AreEqual("category", row["REFERENCED_COLUMN_NAME"]);
+
+ row = dt.Rows[1];
+ Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]);
+ Assert.AreEqual("test", row["CONSTRAINT_SCHEMA"]);
+ Assert.AreEqual("product_order_ibfk_1", row["CONSTRAINT_NAME"]);
+ Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]);
+ Assert.AreEqual("test", row["TABLE_SCHEMA"]);
+ Assert.AreEqual("product_order", row["TABLE_NAME"]);
+ Assert.AreEqual("product_id", row["COLUMN_NAME"]);
+ Assert.AreEqual(1, row["ORDINAL_POSITION"]);
+ Assert.AreEqual("test", row["REFERENCED_TABLE_SCHEMA"]);
+ Assert.AreEqual("product", row["REFERENCED_TABLE_NAME"]);
+ Assert.AreEqual("id", row["REFERENCED_COLUMN_NAME"]);
+
+ row = dt.Rows[2];
+ Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]);
+ Assert.AreEqual("test", row["CONSTRAINT_SCHEMA"]);
+ Assert.AreEqual("product_order_ibfk_2", row["CONSTRAINT_NAME"]);
+ Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]);
+ Assert.AreEqual("test", row["TABLE_SCHEMA"]);
+ Assert.AreEqual("product_order", row["TABLE_NAME"]);
+ Assert.AreEqual("customer_id", row["COLUMN_NAME"]);
+ Assert.AreEqual(0, row["ORDINAL_POSITION"]);
+ Assert.AreEqual("test", row["REFERENCED_TABLE_SCHEMA"]);
+ Assert.AreEqual("customer", row["REFERENCED_TABLE_NAME"]);
+ Assert.AreEqual("id", row["REFERENCED_COLUMN_NAME"]);
+ }
+
+ }
}
Modified: trunk/mysqlclient/Resources.resx
===================================================================
--- trunk/mysqlclient/Resources.resx 2006-09-26 23:05:49 UTC (rev 366)
+++ trunk/mysqlclient/Resources.resx 2006-09-27 20:58:20 UTC (rev 367)
@@ -273,4 +273,7 @@
<data name="CommandTextNotInitialized" xml:space="preserve">
<value>The CommandText property has not been properly initialized.</value>
</data>
+ <data name="UnableToParseFK" xml:space="preserve">
+ <value>There was an error parsing the foreign key definition.</value>
+ </data>
</root>
\ No newline at end of file
Modified: trunk/mysqlclient/SchemaProvider.cs
===================================================================
--- trunk/mysqlclient/SchemaProvider.cs 2006-09-26 23:05:49 UTC (rev 366)
+++ trunk/mysqlclient/SchemaProvider.cs 2006-09-27 20:58:20 UTC (rev 367)
@@ -352,9 +352,145 @@
dt.Columns.Add("REFERENCED_TABLE_NAME", typeof(string));
dt.Columns.Add("REFERENCED_COLUMN_NAME", typeof(string));
+ // first we use our restrictions to get a list of tables that should be
+ // consulted. We save the keyname restriction since GetTables doesn't
+ // understand that.
+ string keyName = restrictions[3];
+ restrictions[3] = null;
+ DataTable tables = GetTables(restrictions);
+
+ // now for each table retrieved, we call our helper function to
+ // parse it's foreign keys
+ foreach (DataRow table in tables.Rows)
+ {
+ GetForeignKeysOnTable(dt, table, keyName);
+ }
+
return dt;
}
+ private string GetSqlMode()
+ {
+ MySqlCommand cmd = new MySqlCommand("SELECT @@SQL_MODE", connection);
+ return cmd.ExecuteScalar().ToString();
+ }
+
+ /// <summary>
+ /// GetForeignKeysOnTable retrieves the foreign keys on the given table.
+ /// Since MySQL supports foreign keys on versions prior to 5.0, we can't use
+ /// information schema. MySQL also does not include any type of SHOW command
+ /// for foreign keys so we have to resort to use SHOW CREATE TABLE and parsing
+ /// the output.
+ /// </summary>
+ /// <param name="fkTable">The table to store the key info in.</param>
+ /// <param name="tableToParse">The table to get the foeign key info for.</param>
+ /// <param name="filterName">Only get foreign keys that match this name.</param>
+ private void GetForeignKeysOnTable(DataTable fkTable, DataRow tableToParse,
+ string filterName)
+ {
+ string sqlMode = GetSqlMode();
+ bool ansiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1;
+ bool noBackslash = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1;
+ string quotePattern = ansiQuotes ? "``\"\"" : "``";
+
+ if (filterName != null)
+ filterName = filterName.ToLower(CultureInfo.InvariantCulture);
+
+ string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`",
+ tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]);
+ MySqlCommand cmd = new MySqlCommand(sql, connection);
+ using (MySqlDataReader reader = cmd.ExecuteReader())
+ {
+ reader.Read();
+ string body = reader.GetString(1);
+
+ string lowerBody = body.ToLower(CultureInfo.InvariantCulture);
+ ContextString cs = new ContextString(quotePattern, !noBackslash);
+ int index = cs.IndexOf(lowerBody, "constraint");
+
+ while (index != -1)
+ {
+ index += 11;
+ int fkIndex = cs.IndexOf(lowerBody, "foreign key", index);
+ if (fkIndex != -1)
+ {
+ string cName = body.Substring(index+1, fkIndex - 3 - index);
+ index = fkIndex;
+ if (filterName == null ||
+ cName.ToLower(CultureInfo.InvariantCulture) == filterName)
+ {
+ int endOfFkArray = cs.IndexOf(lowerBody, ")", index);
+ if (endOfFkArray == -1)
+ throw new MySqlException(Resources.UnableToParseFK);
+ index += 13;
+ string fkArray = body.Substring(index, endOfFkArray - index);
+
+ int references = cs.IndexOf(lowerBody, "references", endOfFkArray);
+ if (references == -1)
+ throw new MySqlException(Resources.UnableToParseFK);
+ references += 11;
+ int startOfRefArray = cs.IndexOf(lowerBody, "(", references);
+ if (startOfRefArray == -1)
+ throw new MySqlException(Resources.UnableToParseFK);
+ string refTable = body.Substring(references, startOfRefArray - references-1);
+
+ int endOfRefArray = cs.IndexOf(lowerBody, ")", startOfRefArray);
+ if (endOfRefArray == -1)
+ throw new MySqlException(Resources.UnableToParseFK);
+ string refArray = body.Substring(startOfRefArray+1,
+ endOfRefArray - startOfRefArray - 1);
+
+ ParseFkColumns(fkTable, tableToParse, cName, refTable, cs, fkArray, refArray);
+ index = endOfRefArray;
+ }
+ }
+ index = cs.IndexOf(lowerBody, "constraint", index);
+ }
+ }
+ }
+
+ private void ParseFkColumns(DataTable fkTable, DataRow table, string cName,
+ string refTable, ContextString cs, string fkArray, string refArray)
+ {
+ string[] fkColumns = fkArray.Split(new char[] { ',' });
+ string[] refColumns = refArray.Split(new char[] { ',' });
+
+ string refSchema = table["TABLE_SCHEMA"].ToString();
+ int index = cs.IndexOf(refTable, ".");
+ if (index != -1)
+ {
+ refSchema = refTable.Substring(1, index - 3);
+ refTable = refTable.Substring(index + 2, refTable.Length - index - 3);
+ }
+ else
+ refTable = CleanSymbol(refTable);
+
+ int pos = 0;
+ foreach (string fkColumn in fkColumns)
+ {
+ DataRow row = fkTable.NewRow();
+ row["CONSTRAINT_CATALOG"] = table["TABLE_CATALOG"];
+ row["CONSTRAINT_SCHEMA"] = table["TABLE_SCHEMA"];
+ row["CONSTRAINT_NAME"] = cName;
+ row["TABLE_CATALOG"] = table["TABLE_CATALOG"];
+ row["TABLE_SCHEMA"] = table["TABLE_SCHEMA"];
+ row["TABLE_NAME"] = table["TABLE_NAME"];
+ row["COLUMN_NAME"] = CleanSymbol(fkColumn.Trim());
+ row["ORDINAL_POSITION"] = pos;
+ row["REFERENCED_TABLE_SCHEMA"] = refSchema;
+ row["REFERENCED_TABLE_NAME"] = refTable;
+ row["REFERENCED_COLUMN_NAME"] = CleanSymbol(refColumns[pos++].Trim());
+ fkTable.Rows.Add(row);
+ }
+ }
+
+ private string CleanSymbol(string sym)
+ {
+ sym = sym.Remove(0, 1);
+ sym = sym.Remove(sym.Length - 1, 1);
+ return sym;
+ }
+
public virtual DataTable GetUsers(string[] restrictions)
{
StringBuilder sb = new StringBuilder("SELECT Host, User FROM mysql.user");
@@ -517,11 +653,10 @@
new object[] {"IndexColumns", "Table", "", 2},
new object[] {"IndexColumns", "ConstraintName", "", 3},
new object[] {"IndexColumns", "Column", "", 4},
-
-// {"ForeignKeys", "Catalog", "", "CONSTRAINT_CATALOG"},
- // {"ForeignKeys", "Owner", "", "CONSTRAINT_SCHEMA"},
- // {"ForeignKeys", "Table", "", "TABLE_NAME"},
- // {"ForeignKeys", "Name", "", "CONSTAINT_NAME"},
+ new object[] {"Foreign Keys", "Catalog", "", 0},
+ new object[] {"Foreign Keys", "Owner", "", 1},
+ new object[] {"Foreign Keys", "Table", "", 2},
+ new object[] {"Foreign Keys", "Name", "", 3}
};
DataTable dt = new DataTable("Restrictions");
@@ -624,8 +759,8 @@
return GetIndexes(restrictions);
case "indexcolumns":
return GetIndexColumns(restrictions);
-// case "foreign keys":
- // return GetForeignKeys(restrictions);
+ case "foreign keys":
+ return GetForeignKeys(restrictions);
}
return null;
}
Modified: trunk/mysqlclient/common/ContextString.cs
===================================================================
--- trunk/mysqlclient/common/ContextString.cs 2006-09-26 23:05:49 UTC (rev 366)
+++ trunk/mysqlclient/common/ContextString.cs 2006-09-27 20:58:20 UTC (rev 367)
@@ -42,6 +42,47 @@
set { contextMarkers = value; }
}
+ public int IndexOf(string src, string target)
+ {
+ return IndexOf(src, target, 0);
+ }
+
+ public int IndexOf(string src, string target, int startIndex)
+ {
+ int index = src.IndexOf(target, startIndex);
+ while (index != -1)
+ {
+ if (!IndexInQuotes(src, index, startIndex)) break;
+ index = src.IndexOf(target, index + 1);
+ }
+ return index;
+ }
+
+ private bool IndexInQuotes(string src, int index, int startIndex)
+ {
+ char contextMarker = Char.MinValue;
+ bool escaped = false;
+
+ for (int i = startIndex; i < index; i++)
+ {
+ char c = src[i];
+
+ int contextIndex = contextMarkers.IndexOf(c);
+
+ // if we have found the closing marker for our open marker, then close the context
+ if (contextIndex > -1 && contextMarker == contextMarkers[contextIndex] && !escaped)
+ contextMarker = Char.MinValue;
+
+ // if we have found a context marker and we are not in a context yet, then start one
+ else if (contextMarker == Char.MinValue && contextIndex > -1 && !escaped)
+ contextMarker = c;
+
+ else if (c == '\\' && escapeBackslash)
+ escaped = !escaped;
+ }
+ return contextMarker != Char.MinValue || escaped;
+ }
+
public int IndexOf(string src, char target)
{
char contextMarker = Char.MinValue;
| Thread |
|---|
| • Connector/NET commit: r367 - in trunk: . TestSuite mysqlclient mysqlclient/common | rburnett | 27 Sep |
