/// <summary>
/// Creates a CREATE INDEX DDL for the specified table and index schema.
/// </summary>
/// <param name="tableName">The name of the indexed table.</param>
/// <param name="indexSchema">The schema of the index object</param>
/// <returns>A CREATE INDEX DDL (SQLite format).</returns>
private static string BuildCreateIndex(string tableName, IndexSchema indexSchema)
{
var sb = new StringBuilder();
sb.Append("CREATE ");
if (indexSchema.IsUnique)
{
sb.Append("UNIQUE ");
}
sb.Append("INDEX [" + tableName + "_" + indexSchema.IndexName + "]\n");
sb.Append("ON [" + tableName + "]\n");
sb.Append("(");
for (int i = 0; i < indexSchema.Columns.Count; i++)
{
sb.Append("[" + indexSchema.Columns[i].ColumnName + "]");
if (!indexSchema.Columns[i].IsAscending)
{
sb.Append(" DESC");
}
if (i < indexSchema.Columns.Count - 1)
{
sb.Append(", ");
}
}
sb.Append(")");
return sb.ToString();
}
/// <summary>
/// Builds an index schema object from the specified components (Read from SQL Server).
/// </summary>
/// <param name="indexName">The name of the index</param>
/// <param name="desc">The description of the index</param>
/// <param name="keys">Key columns that are part of the index.</param>
/// <returns>An index schema object that represents our knowledge of the index</returns>
private IndexSchema BuildIndexSchema(string indexName, string desc, string keys)
{
IndexSchema res = new IndexSchema();
res.IndexName = indexName;
// Determine if this is a unique index or not.
string[] descParts = desc.Split(',');
foreach (string p in descParts)
{
if (p.Trim().Contains("unique"))
{
res.IsUnique = true;
break;
}
}
// Get all key names and check if they are ASCENDING or DESCENDING
res.Columns = new List<IndexColumn>();
string[] keysParts = keys.Split(',');
foreach (string p in keysParts)
{
Match m = _keyRx.Match(p.Trim());
if (!m.Success)
{
throw new ApplicationException("Illegal key name [" + p + "] in index [" + indexName + "]");
}
string key = m.Groups[1].Value;
IndexColumn ic = new IndexColumn();
ic.ColumnName = key;
ic.IsAscending = !m.Groups[2].Success;
res.Columns.Add(ic);
}
return res;
}