mysql-net · bgrainger · Dec 15, 2023 · Dec 15, 2023
diff --git a/docs/content/overview/schema-collections.md b/docs/content/overview/schema-collections.md
@@ -1,6 +1,6 @@
 ---
 date: 2021-04-24
-lastmod: 2023-12-14
+lastmod: 2023-12-15
 menu:
   main:
     parent: getting started
@@ -44,6 +44,6 @@ weight: 80
 * `Triggers`
 * `UserPrivileges`
 * `Views`
-* `Foreign Keys`
-* `Indexes`
-* `IndexColumns`
+* `Foreign Keys`—[information about foreign keys in the server's SQL syntax](../schema/foreign keys/)
+* `Indexes`—[information about indexes in the server's SQL syntax](../schema/indexes/)
+* `IndexColumns`—[information about indexes in the server's SQL syntax](../schema/indexcolumns/)
diff --git a/docs/content/overview/schema/columns.md b/docs/content/overview/schema/columns.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-07-11
+lastmod: 2023-12-15
 title: Columns Schema
 ---
 

diff --git a/docs/content/overview/schema/datatypes.md b/docs/content/overview/schema/datatypes.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-07-11
+lastmod: 2023-12-15
 title: DataTypes Schema
 ---
 

diff --git a/docs/content/overview/schema/foreign keys.md b/docs/content/overview/schema/foreign keys.md
@@ -0,0 +1,34 @@
+---
+date: 2022-07-10
+lastmod: 2023-12-15
+title: Foreign Keys Schema
+---
+
+# Foreign Keys Schema
+
+The `Foreign Keys` schema provides information about foreign keys in the server's SQL syntax.
+
+Column Name | Data Type | Description
+--- | --- | ---
+CONSTRAINT_CATALOG | string | 
+CONSTRAINT_SCHEMA | string | 
+CONSTRAINT_NAME | string | 
+TABLE_CATALOG | string | 
+TABLE_SCHEMA | string | 
+TABLE_NAME | string | 
+MATCH_OPTION | string | 
+UPDATE_RULE | string | 
+DELETE_RULE | string | 
+REFERENCED_TABLE_CATALOG | string | 
+REFERENCED_TABLE_SCHEMA | string | 
+REFERENCED_TABLE_NAME | string | 
+
+The following restrictions are supported:
+
+Restriction Name | Restriction Default | Restriction Number
+--- | --- | --:
+Catalog | TABLE_CATALOG | 1
+Schema | TABLE_SCHEMA | 2
+Table | TABLE_NAME | 3
+Column | COLUMN_NAME | 4
+
diff --git a/docs/content/overview/schema/indexcolumns.md b/docs/content/overview/schema/indexcolumns.md
@@ -0,0 +1,30 @@
+---
+date: 2022-07-10
+lastmod: 2023-12-15
+title: IndexColumns Schema
+---
+
+# IndexColumns Schema
+
+The `IndexColumns` schema provides information about indexes in the server's SQL syntax.
+
+Column Name | Data Type | Description
+--- | --- | ---
+INDEX_CATALOG | string | 
+INDEX_SCHEMA | string | 
+INDEX_NAME | string | 
+TABLE_NAME | string | 
+COLUMN_NAME | string | 
+ORDINAL_POSITION | int | 
+SORT_ORDER | string | 
+
+The following restrictions are supported:
+
+Restriction Name | Restriction Default | Restriction Number
+--- | --- | --:
+Catalog | TABLE_CATALOG | 1
+Schema | TABLE_SCHEMA | 2
+Table | TABLE_NAME | 3
+Constraint | CONSTRAINT_NAME | 4
+Column | COLUMN_NAME | 5
+
diff --git a/docs/content/overview/schema/indexes.md b/docs/content/overview/schema/indexes.md
@@ -0,0 +1,31 @@
+---
+date: 2022-07-10
+lastmod: 2023-12-15
+title: Indexes Schema
+---
+
+# Indexes Schema
+
+The `Indexes` schema provides information about indexes in the server's SQL syntax.
+
+Column Name | Data Type | Description
+--- | --- | ---
+SEQ_IN_INDEX | long | 
+INDEX_CATALOG | string | 
+INDEX_SCHEMA | string | 
+INDEX_NAME | string | 
+TABLE_NAME | string | 
+UNIQUE | bool | 
+PRIMARY | bool | 
+TYPE | string | 
+COMMENT | string | 
+
+The following restrictions are supported:
+
+Restriction Name | Restriction Default | Restriction Number
+--- | --- | --:
+Catalog | TABLE_CATALOG | 1
+Schema | TABLE_SCHEMA | 2
+Table | TABLE_NAME | 3
+Column | COLUMN_NAME | 4
+
diff --git a/docs/content/overview/schema/metadatacollections.md b/docs/content/overview/schema/metadatacollections.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-07-11
+lastmod: 2023-12-15
 title: MetaDataCollections Schema
 ---
 

diff --git a/docs/content/overview/schema/procedures.md b/docs/content/overview/schema/procedures.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-07-11
+lastmod: 2023-12-15
 title: Procedures Schema
 ---
 

diff --git a/docs/content/overview/schema/reservedwords.md b/docs/content/overview/schema/reservedwords.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-07-11
+lastmod: 2023-12-15
 title: ReservedWords Schema
 ---
 

diff --git a/docs/content/overview/schema/restrictions.md b/docs/content/overview/schema/restrictions.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-07-11
+lastmod: 2023-12-15
 title: Restrictions Schema
 ---
 

diff --git a/docs/content/overview/schema/tables.md b/docs/content/overview/schema/tables.md
@@ -1,6 +1,6 @@
 ---
 date: 2022-07-10
-lastmod: 2022-08-12
+lastmod: 2023-12-15
 title: Tables Schema
 ---
 

diff --git a/src/MySqlConnector/Core/SchemaProvider.cs b/src/MySqlConnector/Core/SchemaProvider.cs
@@ -1,4 +1,5 @@
 using System.Globalization;
+using System.Text;
 using MySqlConnector.Protocol.Serialization;
 
 namespace MySqlConnector.Core;
@@ -429,4 +430,131 @@ private async Task FillDataTableAsync(IOBehavior ioBehavior, DataTable dataTable
 
 		close?.Invoke();
 	}
+
+	private void DoFillForeignKeys(DataTable dataTable, string?[]? restrictionValues)
+	{
+		void ConfigurateCommand(MySqlCommand command)
+		{
+			string sql = @"SELECT rc.constraint_catalog, rc.constraint_schema,
+                rc.constraint_name, kcu.table_catalog, kcu.table_schema, rc.table_name,
+                rc.match_option, rc.update_rule, rc.delete_rule, 
+                NULL as referenced_table_catalog,
+                kcu.referenced_table_schema, rc.referenced_table_name 
+                FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
+                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON 
+                kcu.constraint_catalog <=> rc.constraint_catalog AND
+                kcu.constraint_schema <=> rc.constraint_schema AND 
+                kcu.constraint_name <=> rc.constraint_name 
+                WHERE 1=1 AND kcu.ORDINAL_POSITION=1";
+			if (restrictionValues != null)
+			{
+				var where = new StringBuilder();
+				if (restrictionValues.Length >= 2 && !string.IsNullOrEmpty(restrictionValues[1]))
+				{
+					where.Append(" AND rc.constraint_schema LIKE @schema");
+					command.Parameters.AddWithValue("@schema", restrictionValues[1]);
+				}
+				if (restrictionValues.Length >= 3 && !string.IsNullOrEmpty(restrictionValues[2]))
+				{
+					where.Append(" AND rc.table_name LIKE @table");
+					command.Parameters.AddWithValue("@table", restrictionValues[2]);
+				}
+				if (restrictionValues.Length >= 4 && !string.IsNullOrEmpty(restrictionValues[3]))
+				{
+					where.Append(" AND rc.constraint_name LIKE @constraint");
+					command.Parameters.AddWithValue("@constraint", restrictionValues[2]);
+				}
+				sql += where.ToString();
+			}
+			command.CommandText = sql;
+		}
+		ReadIntoTableFromSQL(ConfigurateCommand, dataTable, connection);
+	}
+
+	private void DoFillIndexes(DataTable dataTable, string?[]? restrictionValues)
+	{
+		void ConfigurateCommand(MySqlCommand command)
+		{
+			string sql = @"SELECT SEQ_IN_INDEX, null AS INDEX_CATALAG, INDEX_SCHEMA,
+                INDEX_NAME, TABLE_NAME,
+                !NON_UNIQUE as `UNIQUE`, 
+                INDEX_NAME=""PRIMARY"" as `PRIMARY`,
+                INDEX_TYPE as TYPE, COMMENT 
+                FROM INFORMATION_SCHEMA.STATISTICS
+                WHERE 1=1";
+			if (restrictionValues != null)
+			{
+				var where = new StringBuilder();
+				if (restrictionValues.Length >= 2 && !string.IsNullOrEmpty(restrictionValues[1]))
+				{
+					where.Append(" AND INDEX_SCHEMA LIKE @schema");
+					command.Parameters.AddWithValue("@schema", restrictionValues[1]);
+				}
+				if (restrictionValues.Length >= 3 && !string.IsNullOrEmpty(restrictionValues[2]))
+				{
+					where.Append(" AND TABLE_NAME LIKE @table");
+					command.Parameters.AddWithValue("@table", restrictionValues[2]);
+				}
+
+				sql += where.ToString();
+			}
+			command.CommandText = sql;
+		}
+		ReadIntoTableFromSQL(ConfigurateCommand, dataTable, connection);
+	}
+	private void DoFillIndexColumns(DataTable dataTable, string?[]? restrictionValues)
+	{
+		string sql = @"SELECT null AS INDEX_CATALAG, INDEX_SCHEMA,
+                INDEX_NAME, TABLE_NAME,
+                COLUMN_NAME,
+                SEQ_IN_INDEX as `ORDINAL_POSITION`,
+				COLLATION as SORT_ORDER
+
+                FROM INFORMATION_SCHEMA.STATISTICS
+                WHERE 1=1";
+		void ConfigurateCommand(MySqlCommand command)
+		{
+			if (restrictionValues != null)
+			{
+				var where = new StringBuilder();
+				if (restrictionValues.Length >= 2 && !string.IsNullOrEmpty(restrictionValues[1]))
+				{
+					where.Append(" AND INDEX_SCHEMA LIKE @schema");
+					command.Parameters.AddWithValue("@schema", restrictionValues[1]);
+				}
+				if (restrictionValues.Length >= 3 && !string.IsNullOrEmpty(restrictionValues[2]))
+				{
+					where.Append(" AND TABLE_NAME LIKE @table");
+					command.Parameters.AddWithValue("@table", restrictionValues[2]);
+				}
+
+				sql += where.ToString();
+				command.CommandText = sql;
+			}
+		}
+		ReadIntoTableFromSQL(ConfigurateCommand, dataTable, connection);
+	}
+
+	private static void ReadIntoTableFromSQL(Action<MySqlCommand> commandConfigurator, DataTable dataTableToFill, MySqlConnection connection)
+	{
+		Action? close = null;
+		if (connection.State != ConnectionState.Open)
+		{
+			connection.Open();
+			close = connection.Close;
+		}
+
+		using (var command = connection.CreateCommand())
+		{
+			commandConfigurator?.Invoke(command);
+			using var reader = command.ExecuteReader();
+			while (reader.Read())
+			{
+				var rowValues = new object[dataTableToFill.Columns.Count];
+				reader.GetValues(rowValues);
+				dataTableToFill.Rows.Add(rowValues);
+			}
+		}
+		close?.Invoke();
+	}
 }