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; }