/// <summary> /// Generate init string from contrains related to this column. /// </summary> /// <param name="column">Column attribute.</param> /// <param name="isForeignKey">FK attribute.</param> /// <param name="selfTableName">Name of holding table.</param> /// <returns>Generated SQL command.</returns> public static string ConstrainDeclarationCommand( ColumnAttribute column, IsForeignKeyAttribute isForeignKey, string selfTableName) { string command = ""; command += "CONSTRAINT `" + isForeignKey.FKName(selfTableName) + "`\n"; command += "\tFOREIGN KEY(`" + column.title + "`)\n"; command += "\tREFERENCES `" + isForeignKey.schema + "`.`" + isForeignKey.table + "` (`" + isForeignKey.column + "`)\n"; command += "\tON DELETE " + IsForeignKeyAttribute.ActionToCommand(isForeignKey.onDeleteCommand) + "\n"; command += "\tON UPDATE " + IsForeignKeyAttribute.ActionToCommand(isForeignKey.onUpdateCommand); return(command); }
/// <summary> /// Return index init string suitable from forgeign key suitable for this column. /// </summary> /// <param name="column">Column attribute.</param> /// <param name="isForeignKey">FL attribute</param> /// <param name="selfTableName">Name of holding table.</param> /// <returns>Generated SQL command.</returns> public static string FKIndexDeclarationCommand( ColumnAttribute column, IsForeignKeyAttribute isForeignKey, string selfTableName) { // Generate comman string command = "INDEX `" + isForeignKey.FKName(selfTableName) + "_idx` (`" + column.title + "` ASC) VISIBLE"; return(command); }
/// <summary> /// Return command that would allow to create table by descriptor. /// </summary> /// <param name="sourceType"></param> /// <returns></returns> public static string GenerateCreateTableCommand(Type sourceType) { // Loking for table descriptor. if (!TableAttribute.TryToGetTableAttribute(sourceType, out TableAttribute tableDescriptor, out string error)) { SqlOperatorHandler.InvokeSQLErrorOccured(sourceType, error); return(null); } // Variable that would contain SQL comand. string command = ""; command += "CREATE TABLE IF NOT EXISTS `" + tableDescriptor.schema + "`.`" + tableDescriptor.table + "` (\n"; IEnumerable <MemberInfo> columns = MembersHandler.FindMembersWithAttribute <ColumnAttribute>(sourceType); #region Declere columns string colCommand = ""; foreach (MemberInfo member in columns) { if (!string.IsNullOrEmpty(colCommand)) { colCommand += ",\n"; } colCommand += SqlOperatorHandler.Active.ColumnDeclarationCommand(member); } command += colCommand; #endregion #region Primary keys // Build PKs substring string. string subPkCommand = ""; foreach (MemberInfo cMeta in columns) { if (MembersHandler.TryToGetAttribute <IsPrimaryKeyAttribute>(cMeta, out IsPrimaryKeyAttribute isPrimaryKey)) { if (!string.IsNullOrEmpty(subPkCommand)) { subPkCommand += ", "; } MembersHandler.TryToGetAttribute <ColumnAttribute>(cMeta, out ColumnAttribute column); subPkCommand += "`" + column.title + "`"; } } // Add to command command if pks exist. command += subPkCommand.Length > 0 ? ",\nPRIMARY KEY(" + subPkCommand + ")" : ""; #endregion #region Unique indexes foreach (MemberInfo cMeta in columns) { if (MembersHandler.TryToGetAttribute <IsUniqueAttribute>(cMeta, out IsUniqueAttribute isUnique)) { command += ",\n"; command += isUnique.UniqueIndexDeclarationCommand(cMeta); } } #endregion #region FK indexes IsForeignKeyAttribute.DropIndexator(); foreach (MemberInfo cMeta in columns) { string decleration = IsForeignKeyAttribute.FKIndexDeclarationCommand(cMeta, tableDescriptor.table); if (!string.IsNullOrEmpty(decleration)) { command += ",\n" + decleration; } } #endregion #region Constraints IsForeignKeyAttribute.DropIndexator(); foreach (MemberInfo cMeta in columns) { string decleration = IsForeignKeyAttribute.ConstrainDeclarationCommand(cMeta, tableDescriptor.table); if (!string.IsNullOrEmpty(decleration)) { command += ",\n" + decleration; } } #endregion command += ")\n"; command += "ENGINE = " + (string.IsNullOrEmpty(tableDescriptor.engine) ? "InnoDB" : tableDescriptor.engine) + ";"; return(command); }