public void Table <T>() { var tableDefinition = DefinitionFactory.GetTableDefinition(_sqlSyntax, typeof(T)); AddSql(_sqlSyntax.Format(tableDefinition)); AddSql(_sqlSyntax.FormatPrimaryKey(tableDefinition)); foreach (var sql in _sqlSyntax.Format(tableDefinition.ForeignKeys)) { AddSql(sql); } foreach (var sql in _sqlSyntax.Format(tableDefinition.Indexes)) { AddSql(sql); } }
/// <inheritdoc /> public void Do() { ISqlSyntaxProvider syntax = _context.SqlContext.SqlSyntax; if (TypeOfDto is null) { return; } TableDefinition tableDefinition = DefinitionFactory.GetTableDefinition(TypeOfDto, syntax); // note: of course we are creating the keys and indexes as per the DTO, so // changing the DTO may break old migrations - or, better, these migrations // should capture a copy of the DTO class that will not change ExecuteSql(syntax.FormatPrimaryKey(tableDefinition)); foreach (var sql in syntax.Format(tableDefinition.Indexes)) { ExecuteSql(sql); } foreach (var sql in syntax.Format(tableDefinition.ForeignKeys)) { ExecuteSql(sql); } // note: we do *not* create the DF_ default constraints /* * foreach (var column in tableDefinition.Columns) * { * var sql = syntax.FormatDefaultConstraint(column); * if (!sql.IsNullOrWhiteSpace()) * ExecuteSql(sql); * } */ }
public void CreateTable(bool overwrite, Type modelType) { var tableDefinition = DefinitionFactory.GetTableDefinition(modelType); var tableName = tableDefinition.Name; string createSql = _syntaxProvider.Format(tableDefinition); string createPrimaryKeySql = _syntaxProvider.FormatPrimaryKey(tableDefinition); var foreignSql = _syntaxProvider.Format(tableDefinition.ForeignKeys); var indexSql = _syntaxProvider.Format(tableDefinition.Indexes); var tableExist = _db.TableExist(tableName); if (overwrite && tableExist) { _db.DropTable(tableName); tableExist = false; } if (tableExist == false) { using (var transaction = _db.GetTransaction()) { //Execute the Create Table sql int created = _db.Execute(new Sql(createSql)); _logger.Info <Database>(string.Format("Create Table sql {0}:\n {1}", created, createSql)); //If any statements exists for the primary key execute them here if (!string.IsNullOrEmpty(createPrimaryKeySql)) { int createdPk = _db.Execute(new Sql(createPrimaryKeySql)); _logger.Info <Database>(string.Format("Primary Key sql {0}:\n {1}", createdPk, createPrimaryKeySql)); } //Turn on identity insert if db provider is not mysql if (_syntaxProvider.SupportsIdentityInsert() && tableDefinition.Columns.Any(x => x.IsIdentity)) { _db.Execute(new Sql(string.Format("SET IDENTITY_INSERT {0} ON ", _syntaxProvider.GetQuotedTableName(tableName)))); } //Call the NewTable-event to trigger the insert of base/default data //OnNewTable(tableName, _db, e, _logger); _baseDataCreation.InitializeBaseData(tableName); //Turn off identity insert if db provider is not mysql if (_syntaxProvider.SupportsIdentityInsert() && tableDefinition.Columns.Any(x => x.IsIdentity)) { _db.Execute(new Sql(string.Format("SET IDENTITY_INSERT {0} OFF;", _syntaxProvider.GetQuotedTableName(tableName)))); } //Special case for MySql if (_syntaxProvider is MySqlSyntaxProvider && tableName.Equals("umbracoUser")) { _db.Update <UserDto>("SET id = @IdAfter WHERE id = @IdBefore AND userLogin = @Login", new { IdAfter = 0, IdBefore = 1, Login = "******" }); } //Loop through index statements and execute sql foreach (var sql in indexSql) { int createdIndex = _db.Execute(new Sql(sql)); _logger.Info <Database>(string.Format("Create Index sql {0}:\n {1}", createdIndex, sql)); } //Loop through foreignkey statements and execute sql foreach (var sql in foreignSql) { int createdFk = _db.Execute(new Sql(sql)); _logger.Info <Database>(string.Format("Create Foreign Key sql {0}:\n {1}", createdFk, sql)); } transaction.Complete(); } } _logger.Info <Database>(string.Format("New table '{0}' was created", tableName)); }