protected async Task <TableIndex[]> GetIndexes(string tableName, TableFieldDef[] fieldDefs)
{
List <TableIndex> tableIndexes = new List <TableIndex>();
string commandText = $"PRAGMA index_list({tableName});";
using (var connection = new SqliteConnection(this.ConnectionString)) {
await connection.OpenAsync();
var command = new SqliteCommand(commandText, connection);
using (var reader = command.ExecuteReader()) {
while (await reader.ReadAsync())
{
string indexName = null;
TableIndexType indexType = TableIndexType.Other;
for (int i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
var value = ConvertValue(reader[i])?.ToString();
if (name == "name")
{
indexName = value;
}
else if (name == "unique" && value == "1")
{
indexType = TableIndexType.Unique;
}
else if (name == "origin" && value == "pk")
{
indexType = TableIndexType.Primary;
}
}
if (!string.IsNullOrEmpty(indexName))
{
var fieldNames = await GetIndexFieldNames(indexName);
var tableIndex = new TableIndex(indexType, fieldNames);
tableIndexes.Add(tableIndex);
}
}
}
}
// Not sure why auto increment fields don't have an index created in PRAGMA results
foreach (var autoIncrementFieldDef in fieldDefs.Where(x => x.isAutoIncrement))
{
tableIndexes.Add(new TableIndex(TableIndexType.Primary, new string[] { autoIncrementFieldDef.name }));
}
return(tableIndexes.ToArray());
}
protected TableIndex[] GetUniqueIndexes(string tableName)
{
List <TableIndex> uniqueIndexes = new List <TableIndex>();
string commandText = $"SHOW INDEX FROM {tableName}";
TableIndexType lastTableIndexType = TableIndexType.Other;
string lastIndexName = null;
List <string> lastFieldNames = new List <string>();
using (MySqlDataReader reader = ExecuteReader(this.ConnectionString, commandText)) {
while (reader.Read())
{
bool unique = int.Parse(reader[1].ToString()) == 0;
string indexName = reader[2].ToString();
string columnName = reader[4].ToString();
if (indexName != lastIndexName)
{
if (lastFieldNames.Count > 0)
{
uniqueIndexes.Add(new TableIndex(lastTableIndexType, lastFieldNames.ToArray()));
}
if (indexName == "PRIMARY")
{
lastTableIndexType = TableIndexType.Primary;
}
else if (unique)
{
lastTableIndexType = TableIndexType.Unique;
}
else
{
lastTableIndexType = TableIndexType.Other;
}
lastIndexName = indexName;
lastFieldNames.Clear();
}
lastFieldNames.Add(columnName);
}
if (lastFieldNames.Count > 0)
{
TableIndex uniqueIndex = new TableIndex(lastTableIndexType, lastFieldNames.ToArray());
uniqueIndexes.Add(uniqueIndex);
}
}
return(uniqueIndexes.ToArray());
}
public TableIndex(TableIndexType indexType, string[] fieldNames)
{
this.IndexType = indexType;
this.FieldNames = fieldNames;
}
/// <summary>
/// Checks is valid index value.
/// </summary>
/// <param name="type">Table index type.</param>
/// <param name="fieldNames">Field names.</param>
/// <returns>TRUE if valid.</returns>
private bool _IsValidIndexValue(TableIndexType type, StringCollection fieldNames)
{
bool isValid = false;
switch (type)
{
case TableIndexType.Primary:
case TableIndexType.Simple:
isValid = (1 == fieldNames.Count);
break;
case TableIndexType.Multiple:
isValid = (1 < fieldNames.Count);
break;
default:
Debug.Assert(false); // NOTE: not supported
isValid = false;
break;
}
return isValid;
}
public sealed override void ExplicitVisit(TableIndexType node)
{
base.ExplicitVisit(node);
}
public sealed override void Visit(TableIndexType node)
{
base.Visit(node);
}
