public IList <ForeignKeyMold> ConvertForeignKeys(TableMold originTable, string originProviderName)
{
return(originTable
.ForeignKeys
.Select(x => x.CloneForeignKey(false))
.ToList());
}
public virtual string BuildSelectAllScript(TableMold table)
{
var sb = new StringBuilder();
var decoratedTableName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Table,
table.Name,
this.CurrentOpeningIdentifierDelimiter);
sb.AppendLine($"SELECT");
for (var i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
var decoratedColumnName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Column,
column.Name,
this.CurrentOpeningIdentifierDelimiter);
sb.Append($" {decoratedColumnName}");
if (i < table.Columns.Count - 1)
{
sb.AppendLine(",");
}
}
sb.AppendLine();
sb.AppendLine($"FROM {decoratedTableName}");
var sql = sb.ToString();
return(sql);
}
protected virtual void DeserializeTableData(TableMold tableMold, JArray tableData)
{
var rows = tableData
.Select((x, xIndex) => tableMold
.Columns
.Select(y => y.Name)
.ToDictionary(
z => z,
z =>
{
var jToken = x[z];
if (jToken == null)
{
throw new DbException($"Property '{z}' not found in JSON. Table '{tableMold.Name}', entry index '{xIndex}'.");
}
if (jToken is JValue jValue)
{
return(jValue.Value);
}
else
{
throw new DbException($"Property '{z}' is not a JValue. Table '{tableMold.Name}', entry index '{xIndex}'.");
}
}))
.ToList();
this.Cruder.InsertRows(tableMold.Name, rows);
}
public virtual string BuildDeleteByIdScript(TableMold table, string idParameterName)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (idParameterName == null)
{
throw new ArgumentNullException(nameof(idParameterName));
}
var sb = new StringBuilder();
var decoratedTableName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Table,
table.Name,
this.CurrentOpeningIdentifierDelimiter);
var decoratedIdColumnName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Column,
table.GetPrimaryKeyColumn().Name,
this.CurrentOpeningIdentifierDelimiter);
sb.Append($"DELETE FROM {decoratedTableName} WHERE {decoratedIdColumnName} = @{idParameterName}");
var sql = sb.ToString();
return(sql);
}
protected override string BuildInsertScriptWithDefaultValues(TableMold table)
{
var decoratedTableName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Table,
table.Name,
this.CurrentOpeningIdentifierDelimiter);
var result = $"INSERT INTO {decoratedTableName} DEFAULT VALUES";
return(result);
}
public virtual TableMold GetTable()
{
var primaryKey = this.GetPrimaryKey();
var columns = this.GetColumns();
var foreignKeys = this.GetForeignKeys();
var indexes = this.GetIndexes();
var table = new TableMold
{
Name = this.TableName,
PrimaryKey = primaryKey,
Columns = columns.ToList(),
ForeignKeys = foreignKeys.ToList(),
Indexes = indexes.ToList(),
};
return(table);
}
private IReadOnlyDictionary <string, ColumnMold> BuildColumnsByColumnName(
TableMold table,
IEnumerable <string> columnNames)
{
return(columnNames
.Select(x =>
{
var column = table.Columns.SingleOrDefault(y =>
string.Equals(y.Name, x, StringComparison.InvariantCultureIgnoreCase));
if (column == null)
{
throw new DbException($"Column not found: '{x}'.");
}
return column;
})
.ToDictionary(x => x.Name.ToLowerInvariant(), x => x));
}
public CommandHelper(
CruderBase cruder,
TableMold table,
IEnumerable <string> columnNames)
{
if (columnNames == null)
{
throw new ArgumentNullException(nameof(columnNames));
}
_table = table ?? throw new ArgumentNullException(nameof(table));
_cruder = cruder ?? throw new ArgumentNullException(nameof(cruder));
_command = _cruder.Connection.CreateCommand();
_columnsByColumnName = this.BuildColumnsByColumnName(table, columnNames);
_parameterNamesByColumnNames = this.BuildParameterNamesByColumnNames();
_parameterInfosByColumnNames = this.BuildParameterInfosByColumnNames();
_parametersByParameterNames = this.BuildParametersByParameterNames();
}
public IList <IndexMold> ConvertIndexes(TableMold originTable, string originProviderName)
{
var list = new List <IndexMold>();
var pkColumns = originTable.PrimaryKey
.Columns
.Select(x => x.Name.ToLowerInvariant())
.ToList();
pkColumns.Sort();
foreach (var index in originTable.Indexes)
{
var indexColumns = index
.Columns
.Select(x => x.Name.ToLowerInvariant())
.ToList();
indexColumns.Sort();
var needSkip = true;
if (pkColumns.Count == indexColumns.Count)
{
if (indexColumns.Where((t, i) => pkColumns[i] != t).Any())
{
needSkip = false;
}
}
if (needSkip)
{
continue;
}
list.Add(index.CloneIndex(false));
}
return(list);
}
public TableMold ConvertTable(TableMold originTable, string originProviderName)
{
var convertedTable = new TableMold
{
Name = originTable.Name,
Columns = originTable
.Columns
.Select(x => new ColumnMold
{
Name = x.Name,
Type = this.ConvertType(x.Type, originProviderName),
IsNullable = x.IsNullable,
Identity = this.ConvertIdentity(x.Identity, originProviderName),
Default = this.ConvertDefault(x.Default, originProviderName),
})
.ToList(),
};
convertedTable.PrimaryKey = this.ConvertPrimaryKey(originTable, originProviderName);
convertedTable.ForeignKeys = this.ConvertForeignKeys(originTable, originProviderName);
convertedTable.Indexes = this.ConvertIndexes(originTable, originProviderName);
return(convertedTable);
}
public static ColumnMold GetPrimaryKeyColumn(this TableMold table)
{
return(table.Columns.Single(x => x.Name == table.PrimaryKey.Columns.Single().Name));
}
private void ChargeRoot(INode root)
{
var allSqlNodes = root.FetchTree();
var exactTextNodes = allSqlNodes
.Where(x => x is ExactTextNode)
.Cast <ExactTextNode>()
.ToList();
var reservedWords = exactTextNodes
.Select(x => x.ExactText)
.Distinct()
.Select(x => x.ToUpperInvariant())
.ToHashSet();
var identifiersAsWords = allSqlNodes
.Where(x =>
x is TextNode textNode &&
x.Name.EndsWith("-name", StringComparison.InvariantCultureIgnoreCase))
.Cast <TextNode>()
.ToList();
#region assign job to nodes
// table
var createTable = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "do-create-table", StringComparison.InvariantCultureIgnoreCase));
createTable.Action = (node, token, accumulator) =>
{
var tableMold = new TableMold();
accumulator.AddResult(tableMold);
};
var tableName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "table-name", StringComparison.InvariantCultureIgnoreCase));
tableName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
tableMold.Name = ((TextToken)token).Text;
};
var columnName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "column-name", StringComparison.InvariantCultureIgnoreCase));
columnName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = new ColumnMold
{
Name = ((TextToken)token).Text,
};
tableMold.Columns.Add(columnMold);
};
var typeName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "type-name", StringComparison.InvariantCultureIgnoreCase));
typeName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Type.Name = ((TextToken)token).Text;
};
var precision = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "precision", StringComparison.InvariantCultureIgnoreCase));
precision.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Type.Precision = ((IntegerToken)token).Value.ToInt32();
};
var scale = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "scale", StringComparison.InvariantCultureIgnoreCase));
scale.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Type.Scale = ((IntegerToken)token).Value.ToInt32();
};
var nullToken = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "null", StringComparison.InvariantCultureIgnoreCase));
nullToken.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.IsNullable = true;
};
var notNullToken = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "not-null", StringComparison.InvariantCultureIgnoreCase));
notNullToken.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.IsNullable = false;
};
var inlinePrimaryKey = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "inline-primary-key", StringComparison.InvariantCultureIgnoreCase));
inlinePrimaryKey.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.MarkAsExplicitPrimaryKey();
};
var autoincrement = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "autoincrement", StringComparison.InvariantCultureIgnoreCase));
autoincrement.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Identity = new ColumnIdentityMold();
};
var defaultNull = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "default-null", StringComparison.InvariantCultureIgnoreCase));
defaultNull.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Default = "NULL";
};
var defaultInteger = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "default-integer", StringComparison.InvariantCultureIgnoreCase));
defaultInteger.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Default = ((IntegerToken)token).Value;
};
var defaultString = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "default-string", StringComparison.InvariantCultureIgnoreCase));
defaultString.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var columnMold = tableMold.Columns.Last();
columnMold.Default = $"'{((TextToken)token).Text}'";
};
var constraintName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "constraint-name", StringComparison.InvariantCultureIgnoreCase));
constraintName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
tableMold.SetLastConstraintName(((TextToken)token).Text);
};
var pk = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "do-primary-key", StringComparison.InvariantCultureIgnoreCase));
pk.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
tableMold.PrimaryKey = new PrimaryKeyMold
{
Name = tableMold.GetLastConstraintName(),
};
};
var pkColumnName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "pk-column-name", StringComparison.InvariantCultureIgnoreCase));
pkColumnName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var primaryKey = tableMold.PrimaryKey;
var indexColumn = new IndexColumnMold
{
Name = ((TextToken)token).Text,
};
primaryKey.Columns.Add(indexColumn);
};
var pkColumnAscOrDesc = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "pk-asc-or-desc", StringComparison.InvariantCultureIgnoreCase));
pkColumnAscOrDesc.Action = (node, token, accumulator) =>
{
var tableInfo = accumulator.GetLastResult <TableMold>();
var primaryKey = tableInfo.PrimaryKey;
var indexColumn = primaryKey.Columns.Last();
var ascOrDesc = ((TextToken)token).Text.ToLowerInvariant();
indexColumn.SortDirection = SQLiteParsingHelper.SqlToSortDirection(ascOrDesc);
};
var fk = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "do-foreign-key", StringComparison.InvariantCultureIgnoreCase));
fk.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var foreignKey = new ForeignKeyMold
{
Name = tableMold.GetLastConstraintName(),
};
tableMold.ForeignKeys.Add(foreignKey);
};
var fkTableName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "fk-referenced-table-name", StringComparison.InvariantCultureIgnoreCase));
fkTableName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var foreignKey = tableMold.ForeignKeys.Last();
var foreignKeyTableName = ((TextToken)token).Text;
foreignKey.ReferencedTableName = foreignKeyTableName;
};
var fkColumnName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "fk-column-name", StringComparison.InvariantCultureIgnoreCase));
fkColumnName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var foreignKey = tableMold.ForeignKeys.Last();
var foreignKeyColumnName = ((TextToken)token).Text;
foreignKey.ColumnNames.Add(foreignKeyColumnName);
};
var fkReferencedColumnName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "fk-referenced-column-name", StringComparison.InvariantCultureIgnoreCase));
fkReferencedColumnName.Action = (node, token, accumulator) =>
{
var tableMold = accumulator.GetLastResult <TableMold>();
var foreignKey = tableMold.ForeignKeys.Last();
var foreignKeyReferencedColumnName = ((TextToken)token).Text;
foreignKey.ReferencedColumnNames.Add(foreignKeyReferencedColumnName);
};
// index
var createUniqueIndex = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "do-create-unique-index", StringComparison.InvariantCultureIgnoreCase));
createUniqueIndex.Action = (node, token, accumulator) =>
{
var index = new IndexMold
{
IsUnique = true,
};
index.SetIsCreationFinalized(false);
accumulator.AddResult(index);
};
var createIndex = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "do-create-index", StringComparison.InvariantCultureIgnoreCase));
createIndex.Action = (node, token, accumulator) =>
{
bool brandNewIndex;
if (accumulator.Count == 0)
{
brandNewIndex = true;
}
else
{
var result = accumulator.Last();
if (result is IndexMold indexMold)
{
brandNewIndex = indexMold.GetIsCreationFinalized();
}
else
{
brandNewIndex = true;
}
}
if (brandNewIndex)
{
var newIndex = new IndexMold();
newIndex.SetIsCreationFinalized(true);
accumulator.AddResult(newIndex);
}
else
{
var existingIndexMold = accumulator.GetLastResult <IndexMold>();
existingIndexMold.SetIsCreationFinalized(true);
}
};
var indexName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "index-name", StringComparison.InvariantCultureIgnoreCase));
indexName.Action = (node, token, accumulator) =>
{
var index = accumulator.GetLastResult <IndexMold>();
index.Name = ((TextToken)token).Text;
};
var indexTableName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "index-table-name", StringComparison.InvariantCultureIgnoreCase));
indexTableName.Action = (node, token, accumulator) =>
{
var index = accumulator.GetLastResult <IndexMold>();
index.TableName = ((TextToken)token).Text;
};
var indexColumnName = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "index-column-name", StringComparison.InvariantCultureIgnoreCase));
indexColumnName.Action = (node, token, accumulator) =>
{
var index = accumulator.GetLastResult <IndexMold>();
var columnMold = new IndexColumnMold
{
Name = ((TextToken)token).Text,
};
index.Columns.Add(columnMold);
};
var indexColumnAscOrDesc = (ActionNode)allSqlNodes.Single(x =>
string.Equals(x.Name, "index-column-asc-or-desc", StringComparison.InvariantCultureIgnoreCase));
indexColumnAscOrDesc.Action = (node, token, accumulator) =>
{
var index = accumulator.GetLastResult <IndexMold>();
var columnInfo = index.Columns.Last();
var ascOrDesc = ((TextToken)token).Text.ToLowerInvariant();
columnInfo.SortDirection = SQLiteParsingHelper.SqlToSortDirection(ascOrDesc);
};
#endregion
foreach (var identifiersAsWord in identifiersAsWords)
{
identifiersAsWord.AdditionalChecker = (token, accumulator) =>
{
var wordToken = ((TextToken)token).Text.ToUpperInvariant();
return(!reservedWords.Contains(wordToken));
};
}
}
public PrimaryKeyMold ConvertPrimaryKey(TableMold originTable, string originProviderName)
{
return(originTable.PrimaryKey.ClonePrimaryKey(false));
}
protected virtual string BuildInsertScriptWithDefaultValues(TableMold table)
{
throw new NotSupportedException($"Default implementation of '{nameof(BuildInsertScriptWithDefaultValues)}' not supported.");
}
public virtual string BuildCreateTableScript(TableMold table, bool includeConstraints)
{
var sb = new StringBuilder();
var decoratedTableName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Table,
table.Name,
this.CurrentOpeningIdentifierDelimiter);
sb.AppendLine($@"CREATE TABLE {decoratedTableName}(");
for (var i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
sb.Append(" ");
this.WriteColumnDefinitionScriptFragment(sb, column);
if (i < table.Columns.Count - 1)
{
sb.AppendLine(",");
}
}
if (includeConstraints)
{
var constraints = new List <IConstraint>();
if (table.PrimaryKey != null)
{
constraints.Add(table.PrimaryKey);
}
constraints.AddRange(table.ForeignKeys);
if (constraints.Count > 0)
{
sb.AppendLine(",");
for (var i = 0; i < constraints.Count; i++)
{
var constraint = constraints[i];
sb.Append(" ");
if (constraint is PrimaryKeyMold primaryKey)
{
this.WritePrimaryKeyConstraintScriptFragment(sb, primaryKey);
}
else if (constraint is ForeignKeyMold foreignKey)
{
this.WriteForeignKeyConstraintScriptFragment(sb, foreignKey);
}
if (i < constraints.Count - 1)
{
sb.AppendLine(",");
}
}
foreach (var constraint in constraints)
{
}
}
}
sb.Append(")");
return(sb.ToString());
}
public virtual string BuildInsertScript(
TableMold table,
IReadOnlyDictionary <string, string> columnToParameterMappings)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (columnToParameterMappings == null)
{
throw new ArgumentNullException(nameof(columnToParameterMappings));
}
if (columnToParameterMappings.Count == 0)
{
return(this.BuildInsertScriptWithDefaultValues(table));
throw new ArgumentException($"'{nameof(columnToParameterMappings)}' must not be empty.");
}
var tuples = columnToParameterMappings
.Select(x => Tuple.Create(
this.Dialect.DecorateIdentifier(
DbIdentifierType.Column,
x.Key,
this.CurrentOpeningIdentifierDelimiter),
x.Value))
.ToList();
var sb = new StringBuilder();
var decoratedTableName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Table,
table.Name,
this.CurrentOpeningIdentifierDelimiter);
sb.AppendLine($"INSERT INTO {decoratedTableName} (");
for (var i = 0; i < tuples.Count; i++)
{
var tuple = tuples[i];
var columnName = tuple.Item1;
sb.Append(" ");
sb.Append(columnName);
if (i < tuples.Count - 1)
{
sb.AppendLine(",");
}
}
sb.AppendLine(")");
sb.AppendLine("VALUES (");
for (var i = 0; i < tuples.Count; i++)
{
var tuple = tuples[i];
var parameterName = tuple.Item2;
sb.Append(" @");
sb.Append(parameterName);
if (i < tuples.Count - 1)
{
sb.AppendLine(",");
}
}
sb.Append(")");
var sql = sb.ToString();
return(sql);
}
public virtual string BuildUpdateScript(
TableMold table,
IReadOnlyDictionary <string, string> columnToParameterMappings)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (columnToParameterMappings == null)
{
throw new ArgumentNullException(nameof(columnToParameterMappings));
}
if (columnToParameterMappings.Count == 0)
{
throw new ArgumentException($"'{nameof(columnToParameterMappings)}' must not be empty.");
}
var sb = new StringBuilder();
var decoratedTableName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Table,
table.Name,
this.CurrentOpeningIdentifierDelimiter);
sb.AppendLine($"UPDATE {decoratedTableName} SET");
var idColumnName = table.GetPrimaryKeyColumn().Name.ToLowerInvariant();
var decoratedIdColumnName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Column,
idColumnName,
this.CurrentOpeningIdentifierDelimiter);
var idParameterName = columnToParameterMappings[idColumnName];
var columnNamesToUpdate = columnToParameterMappings.Keys.Except(new[] { idColumnName }).ToList();
for (var i = 0; i < columnNamesToUpdate.Count; i++)
{
var columnName = columnNamesToUpdate[i];
var decoratedColumnName = this.Dialect.DecorateIdentifier(
DbIdentifierType.Column,
columnName,
this.CurrentOpeningIdentifierDelimiter);
var parameterName = columnToParameterMappings[columnName];
sb.Append($" {decoratedColumnName} = @{parameterName}");
if (i < columnNamesToUpdate.Count - 1)
{
sb.AppendLine(",");
}
}
sb.AppendLine();
sb.Append($"WHERE {decoratedIdColumnName} = @{idParameterName}");
var sql = sb.ToString();
return(sql);
}
internal static string GetLastConstraintName(this TableMold tableMold)
{
return(tableMold.Properties["last-constraint-name"]);
}
internal static void SetLastConstraintName(this TableMold tableMold, string value)
{
tableMold.Properties["last-constraint-name"] = value;
}
