| List: | Commits | « Previous MessageNext Message » | |
| From: | rburnett | Date: | July 27 2007 2:58pm |
| Subject: | Connector/NET commit: r816 - in trunk: . Driver/Source TestSuite/Source | ||
| View as plain text | |||
Modified: trunk/CHANGES trunk/Driver/Source/SchemaProvider.cs trunk/TestSuite/Source/BaseTest.cs trunk/TestSuite/Source/GetSchemaTests.cs Log: Added Foreign Key Columns metadata collection Reworked how foreign key metadata is collected to make it more robust and faster. The new code uses our SqlTokenizer class to simplify the code and enable scanning in one pass. Modified: trunk/CHANGES =================================================================== --- trunk/CHANGES 2007-07-25 20:50:30 UTC (rev 815) +++ trunk/CHANGES 2007-07-27 12:58:39 UTC (rev 816) @@ -18,6 +18,8 @@ reuse the connection to update a table while a reader was open on it. - Fixed problem with membership schema where the password key column was not large enough - Added feature where bit columns that have the value 0 or 1 are returned as bool + - Added Foreign Key Columns metadata collection + - Reworked how foreign key metadata is collected to make it more robust and faster Version 5.1.2 - 6/12/2007 - Fixed integration with the Website Administration Tool. Before this fix, the test link Modified: trunk/Driver/Source/SchemaProvider.cs =================================================================== --- trunk/Driver/Source/SchemaProvider.cs 2007-07-25 20:50:30 UTC (rev 815) +++ trunk/Driver/Source/SchemaProvider.cs 2007-07-27 12:58:39 UTC (rev 816) @@ -27,6 +27,8 @@ using System.Text; using MySql.Data.Common; using MySql.Data.Types; +using System.Collections.Specialized; +using System.Collections; namespace MySql.Data.MySqlClient { @@ -316,21 +318,25 @@ return dt; } - public virtual DataTable GetForeignKeys(string[] restrictions) + public virtual DataTable GetForeignKeys(string[] restrictions, bool includeColumns) { DataTable dt = new DataTable("Foreign Keys"); dt.Columns.Add("CONSTRAINT_CATALOG", typeof (string)); dt.Columns.Add("CONSTRAINT_SCHEMA", typeof (string)); dt.Columns.Add("CONSTRAINT_NAME", typeof (string)); - dt.Columns.Add("TABLE_CATALOG", typeof (string)); + dt.Columns.Add("TABLE_CATALOG", typeof(string)); dt.Columns.Add("TABLE_SCHEMA", typeof (string)); dt.Columns.Add("TABLE_NAME", typeof (string)); - dt.Columns.Add("COLUMN_NAME", typeof (string)); - dt.Columns.Add("ORDINAL_POSITION", typeof (int)); + if (includeColumns) + { + dt.Columns.Add("COLUMN_NAME", typeof(string)); + dt.Columns.Add("ORDINAL_POSITION", typeof(int)); + } dt.Columns.Add("REFERENCED_TABLE_CATALOG", typeof (string)); dt.Columns.Add("REFERENCED_TABLE_SCHEMA", typeof (string)); dt.Columns.Add("REFERENCED_TABLE_NAME", typeof (string)); - dt.Columns.Add("REFERENCED_COLUMN_NAME", typeof (string)); + if (includeColumns) + 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 @@ -347,7 +353,7 @@ // 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); + GetForeignKeysOnTable(dt, table, keyName, includeColumns); return dt; } @@ -358,6 +364,8 @@ return cmd.ExecuteScalar().ToString(); } + #region Foreign Key routines + /// <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 @@ -369,7 +377,7 @@ /// <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 filterName, bool includeColumns) { string sqlMode = GetSqlMode(); bool ansiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; @@ -381,100 +389,109 @@ string sql = string.Format("SHOW CREATE TABLE `{0}`.`{1}`", tableToParse["TABLE_SCHEMA"], tableToParse["TABLE_NAME"]); + string lowerBody = null, body = null; MySqlCommand cmd = new MySqlCommand(sql, connection); using (MySqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); - string body = reader.GetString(1); + body = reader.GetString(1); + lowerBody = body.ToLower(CultureInfo.InvariantCulture); + } - string lowerBody = body.ToLower(CultureInfo.InvariantCulture); - ContextString cs = new ContextString(quotePattern, !noBackslash); - int index = cs.IndexOf(lowerBody, "constraint"); + SqlTokenizer tokenizer = new SqlTokenizer(lowerBody); + tokenizer.AnsiQuotes = sqlMode.IndexOf("ANSI_QUOTES") != -1; + tokenizer.BackslashEscapes = sqlMode.IndexOf("NO_BACKSLASH_ESCAPES") != -1; + + while (true) + { + string token = tokenizer.NextToken(); + // look for a starting contraint + while (token != null && (token != "constraint" || tokenizer.Quoted)) + token = tokenizer.NextToken(); + if (token == null) break; - 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); - } + ParseConstraint(fkTable, tableToParse, tokenizer, includeColumns); } } - private static void ParseFkColumns(DataTable fkTable, DataRow table, string cName, - string refTable, ContextString cs, string fkArray, string refArray) + private void ParseConstraint(DataTable fkTable, DataRow table, + SqlTokenizer tokenizer, bool includeColumns) { - string[] fkColumns = fkArray.Split(new char[] {','}); - string[] refColumns = refArray.Split(new char[] {','}); + string name = tokenizer.NextToken(); + DataRow row = fkTable.NewRow(); - string refSchema = table["TABLE_SCHEMA"].ToString(); - int index = cs.IndexOf(refTable, "."); - if (index != -1) + // make sure this constraint is a FK + string token = tokenizer.NextToken(); + if (token != "foreign" || tokenizer.Quoted) + return; + tokenizer.NextToken(); // read off the 'KEY' symbol + tokenizer.NextToken(); // read off the '(' symbol + + row["CONSTRAINT_CATALOG"] = table["TABLE_CATALOG"]; + row["CONSTRAINT_SCHEMA"] = table["TABLE_SCHEMA"]; + row["TABLE_CATALOG"] = table["TABLE_CATALOG"]; + row["TABLE_SCHEMA"] = table["TABLE_SCHEMA"]; + row["TABLE_NAME"] = table["TABLE_NAME"]; + row["REFERENCED_TABLE_CATALOG"] = null; + row["CONSTRAINT_NAME"] = name; + + ArrayList srcColumns = includeColumns ? ParseColumns(tokenizer) : null; + + // now look for the references section + while (token != "references" || tokenizer.Quoted) + token = tokenizer.NextToken(); + string target1 = tokenizer.NextToken(); + string target2 = tokenizer.NextToken(); + if (target2.StartsWith(".")) { - refSchema = refTable.Substring(1, index - 3); - refTable = refTable.Substring(index + 2, refTable.Length - index - 3); + row["REFERENCED_TABLE_SCHEMA"] = target1; + row["REFERENCED_TABLE_NAME"] = target2.Substring(1); + tokenizer.NextToken(); // read off the '(' } else - refTable = CleanSymbol(refTable); + { + row["REFERENCED_TABLE_SCHEMA"] = table["TABLE_SCHEMA"]; + row["REFERENCED_TABLE_NAME"] = target1; + } - int pos = 0; - foreach (string fkColumn in fkColumns) + // if we are supposed to include columns, read the target columns + ArrayList targetColumns = includeColumns ? ParseColumns(tokenizer) : null; + + if (includeColumns) + ProcessColumns(fkTable, row, srcColumns, targetColumns); + else + fkTable.Rows.Add(row); + } + + private ArrayList ParseColumns(SqlTokenizer tokenizer) + { + ArrayList sc = new ArrayList(); + string token = tokenizer.NextToken(); + while (token != ")") { - 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_CATALOG"] = null; - row["REFERENCED_TABLE_SCHEMA"] = refSchema; - row["REFERENCED_TABLE_NAME"] = refTable; - row["REFERENCED_COLUMN_NAME"] = CleanSymbol(refColumns[pos++].Trim()); - fkTable.Rows.Add(row); + if (token != ",") + sc.Add(token); + token = tokenizer.NextToken(); } + return sc; } - private static string CleanSymbol(string sym) + private void ProcessColumns(DataTable fkTable, DataRow row, + ArrayList srcColumns, ArrayList targetColumns) { - sym = sym.Remove(0, 1); - sym = sym.Remove(sym.Length - 1, 1); - return sym; + for (int i = 0; i < srcColumns.Count; i++) + { + DataRow newRow = fkTable.NewRow(); + newRow.ItemArray = row.ItemArray; + newRow["COLUMN_NAME"] = (string)srcColumns[i]; + newRow["ORDINAL_POSITION"] = i; + newRow["REFERENCED_COLUMN_NAME"] = (string)targetColumns[i]; + fkTable.Rows.Add(newRow); + } } + #endregion + public virtual DataTable GetUsers(string[] restrictions) { StringBuilder sb = new StringBuilder("SELECT Host, User FROM mysql.user"); @@ -648,7 +665,11 @@ new object[] {"Foreign Keys", "Database", "", 0}, new object[] {"Foreign Keys", "Schema", "", 1}, new object[] {"Foreign Keys", "Table", "", 2}, - new object[] {"Foreign Keys", "Name", "", 3} + new object[] {"Foreign Keys", "Constraint Name", "", 3}, + new object[] {"Foreign Key Columns", "Catalog", "", 0}, + new object[] {"Foreign Key Columns", "Schema", "", 1}, + new object[] {"Foreign Key Columns", "Table", "", 2}, + new object[] {"Foreign Key Columns", "Constraint Name", "", 3}, }; DataTable dt = new DataTable("Restrictions"); @@ -801,7 +822,9 @@ case "indexcolumns": return GetIndexColumns(restrictions); case "foreign keys": - return GetForeignKeys(restrictions); + return GetForeignKeys(restrictions, false); + case "foreign key columns": + return GetForeignKeys(restrictions, true); } return null; } Modified: trunk/TestSuite/Source/BaseTest.cs =================================================================== --- trunk/TestSuite/Source/BaseTest.cs 2007-07-25 20:50:30 UTC (rev 815) +++ trunk/TestSuite/Source/BaseTest.cs 2007-07-27 12:58:39 UTC (rev 816) @@ -206,8 +206,10 @@ [TearDown] protected virtual void Teardown() { + execSQL("DROP TABLE IF EXISTS test"); if (Version >= new Version(5, 0)) { + execSQL("DROP VIEW IF EXISTS view1"); execSQL("DROP PROCEDURE IF EXISTS spTest"); execSQL("DROP FUNCTION IF EXISTS fnTest"); } Modified: trunk/TestSuite/Source/GetSchemaTests.cs =================================================================== --- trunk/TestSuite/Source/GetSchemaTests.cs 2007-07-25 20:50:30 UTC (rev 815) +++ trunk/TestSuite/Source/GetSchemaTests.cs 2007-07-27 12:58:39 UTC (rev 816) @@ -40,6 +40,23 @@ Close(); } + protected override void Setup() + { + base.Setup(); + execSQL("DROP TABLE IF EXISTS child"); + execSQL("DROP TABLE IF EXISTS parent1"); + execSQL("DROP TABLE IF EXISTS parent2"); + } + + [TearDown] + protected override void Teardown() + { + base.Teardown(); + execSQL("DROP TABLE IF EXISTS child"); + execSQL("DROP TABLE IF EXISTS parent1"); + execSQL("DROP TABLE IF EXISTS parent2"); + } + [Test] public void Collections() { @@ -174,22 +191,20 @@ [Test] public void Tables() { - execSQL("DROP TABLE IF EXISTS test1"); - execSQL("CREATE TABLE test1 (id int)"); + execSQL("CREATE TABLE test (id int)"); string[] restrictions = new string[4]; restrictions[1] = databases[0]; - restrictions[2] = "test1"; + restrictions[2] = "test"; DataTable dt = conn.GetSchema("Tables", restrictions); Assert.IsTrue(dt.Rows.Count == 1); Assert.AreEqual("Tables", dt.TableName); - Assert.AreEqual("test1", dt.Rows[0][2]); + Assert.AreEqual("test", dt.Rows[0][2]); } [Test] public void Columns() { - execSQL("DROP TABLE IF EXISTS test"); execSQL("CREATE TABLE test (col1 int, col2 decimal(20,5), " + "col3 varchar(50) character set utf8, col4 tinyint unsigned)"); @@ -238,7 +253,6 @@ { if (version < new Version(5, 0)) return; - execSQL("DROP PROCEDURE IF EXISTS spTest"); execSQL("CREATE PROCEDURE spTest (id int) BEGIN SELECT 1; END"); string[] restrictions = new string[4]; @@ -255,7 +269,6 @@ { if (version < new Version(5, 0)) return; - execSQL("DROP FUNCTION IF EXISTS spFunc"); execSQL("CREATE FUNCTION spFunc (id int) RETURNS INT BEGIN RETURN 1; END"); string[] restrictions = new string[4]; @@ -272,7 +285,6 @@ { if (version < new Version(5, 0)) return; - execSQL("DROP TABLE IF EXISTS test"); execSQL("CREATE TABLE test (id int, PRIMARY KEY(id))"); string[] restrictions = new string[4]; restrictions[2] = "test"; @@ -309,7 +321,6 @@ [Test] public void IndexColumns() { - execSQL("DROP TABLE IF EXISTS test"); execSQL("CREATE TABLE test (id int, PRIMARY KEY(id))"); string[] restrictions = new string[5]; restrictions[2] = "test"; @@ -337,16 +348,15 @@ { if (version < new Version(5, 0)) return; - execSQL("DROP VIEW IF EXISTS vw"); - execSQL("CREATE VIEW vw AS SELECT Now() as theTime"); + execSQL("CREATE VIEW view1 AS SELECT Now() as theTime"); string[] restrictions = new string[4]; restrictions[1] = databases[0]; - restrictions[2] = "vw"; + restrictions[2] = "view1"; DataTable dt = conn.GetSchema("Views", restrictions); Assert.IsTrue(dt.Rows.Count == 1); Assert.AreEqual("Views", dt.TableName); - Assert.AreEqual("vw", dt.Rows[0]["TABLE_NAME"]); + Assert.AreEqual("view1", dt.Rows[0]["TABLE_NAME"]); } [Test] @@ -354,17 +364,16 @@ { if (version < new Version(5, 0)) return; - execSQL("DROP VIEW IF EXISTS vw"); - execSQL("CREATE VIEW vw AS SELECT Now() as theTime"); + execSQL("CREATE VIEW view1 AS SELECT Now() as theTime"); string[] restrictions = new string[4]; restrictions[1] = databases[0]; - restrictions[2] = "vw"; + restrictions[2] = "view1"; DataTable dt = conn.GetSchema("ViewColumns", restrictions); Assert.IsTrue(dt.Rows.Count == 1); Assert.AreEqual("ViewColumns", dt.TableName); Assert.AreEqual(databases[0].ToLower(), dt.Rows[0]["VIEW_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("vw", dt.Rows[0]["VIEW_NAME"]); + Assert.AreEqual("view1", dt.Rows[0]["VIEW_NAME"]); Assert.AreEqual("theTime", dt.Rows[0]["COLUMN_NAME"]); } @@ -373,7 +382,6 @@ { if (version < new Version(5, 0)) return; - execSQL("DROP PROCEDURE IF EXISTS spTest"); execSQL("CREATE PROCEDURE spTest(id int, IN id2 INT(11), " + "INOUT io1 VARCHAR(20), OUT out1 FLOAT) BEGIN END"); string[] restrictions = new string[4]; @@ -434,11 +442,9 @@ [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"); + execSQL("CREATE TABLE parent1 (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB"); + execSQL(@"CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), + CONSTRAINT fk1 FOREIGN KEY (parent_id) REFERENCES parent1(id) ON DELETE CASCADE) TYPE=INNODB"); string[] restrictions = new string[4]; restrictions[0] = null; restrictions[1] = databases[0]; @@ -448,14 +454,25 @@ DataRow row = dt.Rows[0]; Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("c1", row["CONSTRAINT_NAME"]); - Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]); + Assert.AreEqual("fk1", row["CONSTRAINT_NAME"]); Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent1", row["REFERENCED_TABLE_NAME"]); + + // now check that columns are coming back + dt = conn.GetSchema("Foreign Key Columns", restrictions); + Assert.AreEqual(1, dt.Rows.Count); + row = dt.Rows[0]; + Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); + Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("fk1", row["CONSTRAINT_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent1", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("parent_id", row["COLUMN_NAME"]); Assert.AreEqual(0, row["ORDINAL_POSITION"]); - Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("parent", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("id", row["REFERENCED_COLUMN_NAME"]); } @@ -465,20 +482,17 @@ [Test] public void ForeignKeys() { - 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, " + + execSQL("CREATE TABLE parent1 (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, " + + execSQL("CREATE TABLE parent2 (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB"); + execSQL("CREATE TABLE child (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) " + + "FOREIGN KEY (product_category, product_id) REFERENCES parent1(category, id) " + "ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), " + - "FOREIGN KEY (customer_id) REFERENCES customer(id)) TYPE=INNODB"); + "FOREIGN KEY (customer_id) REFERENCES parent2(id)) TYPE=INNODB"); - try + try { DataTable dt = conn.GetSchema("Foreign Keys"); } @@ -491,62 +505,78 @@ [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, " + + execSQL("CREATE TABLE parent1 (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, " + + execSQL("CREATE TABLE parent2 (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB"); + execSQL("CREATE TABLE child (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) " + + "CONSTRAINT fk1 FOREIGN KEY (product_category, product_id) REFERENCES parent1(category, id) " + "ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), " + - "FOREIGN KEY (customer_id) REFERENCES customer(id)) TYPE=INNODB"); + "CONSTRAINT fk2 FOREIGN KEY (customer_id) REFERENCES parent2(id)) TYPE=INNODB"); string[] restrictions = new string[4]; restrictions[0] = null; restrictions[1] = databases[0]; - restrictions[2] = "product_order"; + restrictions[2] = "child"; DataTable dt = conn.GetSchema("Foreign Keys", restrictions); - Assert.AreEqual(3, dt.Rows.Count); + Assert.AreEqual(2, dt.Rows.Count); DataRow row = dt.Rows[0]; Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product_order_ibfk_1", row["CONSTRAINT_NAME"]); - Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]); + Assert.AreEqual("fk1", row["CONSTRAINT_NAME"]); Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product_order", row["TABLE_NAME"]); + Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent1", row["REFERENCED_TABLE_NAME"]); + + row = dt.Rows[1]; + Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); + Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("fk2", row["CONSTRAINT_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent2", row["REFERENCED_TABLE_NAME"]); + + dt = conn.GetSchema("Foreign Key Columns", restrictions); + Assert.AreEqual(3, dt.Rows.Count); + row = dt.Rows[0]; + Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); + Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("fk1", row["CONSTRAINT_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent1", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("product_category", row["COLUMN_NAME"]); Assert.AreEqual(0, row["ORDINAL_POSITION"]); - Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("category", row["REFERENCED_COLUMN_NAME"]); + // check #2 row = dt.Rows[1]; Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product_order_ibfk_1", row["CONSTRAINT_NAME"]); - Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]); + Assert.AreEqual("fk1", row["CONSTRAINT_NAME"]); Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product_order", row["TABLE_NAME"]); + Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent1", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("product_id", row["COLUMN_NAME"]); Assert.AreEqual(1, row["ORDINAL_POSITION"]); - Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("id", row["REFERENCED_COLUMN_NAME"]); + // check #3 row = dt.Rows[2]; Assert.AreEqual(DBNull.Value, row["CONSTRAINT_CATALOG"]); Assert.AreEqual(databases[0].ToLower(), row["CONSTRAINT_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product_order_ibfk_2", row["CONSTRAINT_NAME"]); - Assert.AreEqual(DBNull.Value, row["TABLE_CATALOG"]); + Assert.AreEqual("fk2", row["CONSTRAINT_NAME"]); Assert.AreEqual(databases[0].ToLower(), row["TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("product_order", row["TABLE_NAME"]); + Assert.AreEqual("child", row["TABLE_NAME"]); + Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); + Assert.AreEqual("parent2", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("customer_id", row["COLUMN_NAME"]); Assert.AreEqual(0, row["ORDINAL_POSITION"]); - Assert.AreEqual(databases[0].ToLower(), row["REFERENCED_TABLE_SCHEMA"].ToString().ToLower()); - Assert.AreEqual("customer", row["REFERENCED_TABLE_NAME"]); Assert.AreEqual("id", row["REFERENCED_COLUMN_NAME"]); } @@ -592,17 +622,16 @@ [Test] public void UsingQuotedRestrictions() { - execSQL("DROP TABLE IF EXISTS test1"); - execSQL("CREATE TABLE test1 (id int)"); + execSQL("CREATE TABLE test (id int)"); string[] restrictions = new string[4]; restrictions[1] = databases[0]; - restrictions[2] = "`test1`"; + restrictions[2] = "`test`"; DataTable dt = conn.GetSchema("Tables", restrictions); Assert.IsTrue(dt.Rows.Count == 1); Assert.AreEqual("Tables", dt.TableName); - Assert.AreEqual("test1", dt.Rows[0][2]); - Assert.AreEqual("`test1`", restrictions[2]); + Assert.AreEqual("test", dt.Rows[0][2]); + Assert.AreEqual("`test`", restrictions[2]); } [Test]
| Thread | ||
|---|---|---|
| • Connector/NET commit: r816 - in trunk: . Driver/Source TestSuite/Source | rburnett | 27 Jul |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.726 sec. using MySQL 5.1.14-beta-log