SqlDialect.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
using SharpData.Exceptions;
using SharpData.Schema;
using SharpData.Util;

namespace SharpData.Databases.SqlServer {
    public class SqlDialect : Dialect {
        public static string ExtendedPropertyNameForComments = "MS_Description";
        public override string ParameterPrefix => "@";

        public override DbType GetDbType(string sqlType, int dataPrecision) {
            throw new NotImplementedException();
        }

        public override string[] GetCreateTableSqls(Table table) {
            var sqls = new List<string>();
            var primaryKeyColumns = new List<string>();

            //create table
            var sb = new StringBuilder();
            sb.Append("create table ").Append(table.Name).AppendLine(" ( ");

            var size = table.Columns.Count;
            for (var i = 0; i < size; i++) {
                sb.Append(GetColumnToSqlWhenCreating(table.Columns[i]));
                if (i != size - 1) {
                    sb.AppendLine(",");
                }
                if (table.Columns[i].IsPrimaryKey) {
                    primaryKeyColumns.Add(table.Columns[i].ColumnName);
                }
            }
            sb.AppendLine(")");

            sqls.Add(sb.ToString());

            //primary key
            if (primaryKeyColumns.Count > 0) {
                sqls.Add(GetPrimaryKeySql(table.Name, String.Format("pk_{0}", table.Name),
                    primaryKeyColumns.ToArray()));
            }
            return sqls.ToArray();
        }

        public override string GetColumnToSqlWhenCreating(Column col) {
            var colType = GetDbTypeString(col.Type, col.Size);
            var colNullable = col.IsNullable ? WordNull : WordNotNull;
            var colAutoIncrement = col.IsAutoIncrement ? "identity(1,1)" : "";

            var colDefault = (col.DefaultValue != null)
                ? String.Format("default ({0})", GetColumnValueToSql(col.DefaultValue))
                : "";

            //name type default nullable autoIncrement
            return $"{col.ColumnName} {colType} {colNullable} {colDefault} {colAutoIncrement}";
        }

        public override string[] GetDropTableSqls(string tableName) {
            var sql = String.Format("drop table {0}", tableName);
            return new[] {sql};
        }

        public override string[] GetDropColumnSql(string table, string columnName) {
            var findDefaultConstraint = String.Format(
                @"select d.name
					from sys.tables t
						join
						sys.default_constraints d
							on d.parent_object_id = t.object_id
						join
						sys.columns c
							on c.object_id = t.object_id
							and c.column_id = d.parent_column_id
					where t.name = '{0}'
					and c.name = '{1}'", table, columnName);
            var dropDefaultConstrant = String.Format("ALTER TABLE {0} DROP CONSTRAINT [{{0}}]", table);
            var dropColumn = String.Format("ALTER TABLE {0} DROP COLUMN {1}", table, columnName);
            return new[] {findDefaultConstraint, dropDefaultConstrant, dropColumn};
        }

        public override string GetForeignKeySql(string fkName, string table, string column, string referencingTable,
            string referencingColumn, OnDelete onDelete) {
            string onDeleteSql;
            switch (onDelete) {
                case OnDelete.Cascade:
                    onDeleteSql = "on delete cascade";
                    break;
                case OnDelete.SetNull:
                    onDeleteSql = "on delete set null";
                    break;
                case OnDelete.NoAction:
                    onDeleteSql = "on delete no action";
                    break;
                default:
                    onDeleteSql = "";
                    break;
            }

            return String.Format("alter table {0} add constraint {1} foreign key ({2}) references {3}({4}) {5}",
                table,
                fkName,
                column,
                referencingTable,
                referencingColumn,
                onDeleteSql);
        }

        public override string GetDropIndexSql(string indexName, string table) {
            return String.Format("drop index {0} on {1}", indexName, table);
        }

        public override string GetUniqueKeySql(string ukName, string table, params string[] columnNames) {
            return String.Format("alter table {0} add constraint {1} unique ({2})",
                table,
                ukName,
                String.Join(",", columnNames));
        }

        public override string GetDropUniqueKeySql(string uniqueKeyName, string tableName) {
            return String.Format("alter table {0} drop constraint {1}", tableName, uniqueKeyName);
        }

        public override string GetInsertReturningColumnSql(string table, string[] columns, object[] values,
            string returningColumnName, string returningParameterName) {
            var sql = GetInsertSql(table, columns, values);
            sql = sql.Replace(") values (", ") output Inserted." + returningColumnName + " into @tempTable values (");

            return String.Format("declare @tempTable TABLE (id int); {0}; select @{1} = id from @tempTable",
                sql, returningParameterName);
        }

        public override string WrapSelectSqlWithPagination(string sql, int skipRows, int numberOfRows) {
            var regex = new Regex("SELECT", RegexOptions.IgnoreCase);
            sql = regex.Replace(sql, "SELECT TOP 2147483647 ", 1);
            var innerSql =
                @"select * into #TempTable from (
							select * ,ROW_NUMBER() over(order by aaa) AS rownum from (
								select 'aaa' as aaa, * from  (
									{0}
								)as t1
							)as t2
						) as t3
					where rownum between {1} and {2}
					alter table #TempTable drop column aaa
					alter table #TempTable drop column rownum
					select * from #TempTable
					drop table #TempTable
				";
            return String.Format(innerSql, sql, skipRows + 1, skipRows + numberOfRows);
        }

        protected override string GetDbTypeString(DbType type, int precision) {
            switch (type) {
                case DbType.AnsiStringFixedLength:
	                if (precision <= 0) return "CHAR(255)";
	                if (precision.Between(1, 8000)) return $"CHAR({precision})";
	                return "CHAR(8000)";
                case DbType.AnsiString:
		            if (precision <= 0) return "VARCHAR(255)";
		            if (precision.Between(1, 8000)) return $"VARCHAR({precision})";
		            return "VARCHAR(MAX)";
                case DbType.Binary: return "BINARY";
                case DbType.Boolean: return "BIT";
                case DbType.Byte: return "TINYINT UNSIGNED";
                case DbType.Currency: return "MONEY";
	            case DbType.Date: return "DATETIME";
	            case DbType.DateTime: return "DATETIME";
	            case DbType.DateTime2: return "DATETIME2";
	            case DbType.DateTimeOffset: return "DATETIMEOFFSET";
                case DbType.Decimal:
                    if (precision <= 0) return "NUMERIC(29,4)";
                    return String.Format("NUMERIC(29,{0})", precision);
                case DbType.Double: return "FLOAT";
                case DbType.Guid: return "UNIQUEIDENTIFIER";
                case DbType.Int16: return "SMALLINT";
                case DbType.Int32: return "INTEGER";
                case DbType.Int64: return "BIGINT";
                case DbType.Single: return "REAL";
                case DbType.StringFixedLength:
		            if (precision <= 0) return "NCHAR(255)";
		            if (precision.Between(1, 4000)) return $"NCHAR({precision})";
		            return "NCHAR(4000)";
                case DbType.String:
		            if (precision <= 0) return "NVARCHAR(255)";
		            if (precision.Between(1, 4000)) return $"NVARCHAR({precision})";
		            return "NVARCHAR(MAX)";
                case DbType.Time: return "TIME";
	            case DbType.SByte: return "SMALLINT";
	            default:
		            throw new DataTypeNotAvailableException($"The type {type} is no available for sqlserver");
            }
        }

        public override string GetColumnValueToSql(object value) {
            if (value is bool) {
                return ((bool) value) ? "1" : "0";
            }

            if ((value is Int16) || (value is Int32) || (value is Int64) || (value is double) || (value is float) ||
                (value is decimal)) {
                return Convert.ToString(value, CultureInfo.InvariantCulture);
            }

            if (value is DateTime dt) {
                return String.Format("'{0}'", dt.ToString("s"));
            }

            return String.Format("'{0}'", value);
        }

        public override string GetTableExistsSql(string tableName) {
            return String.Format("SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{0}'", tableName);
        }

        public override string GetAddCommentToColumnSql(string tableName, string columnName, string comment) {
            return String.Format(@"
                declare @CurrentUser sysname; 
                select @CurrentUser = user_name(); 
                execute sp_addextendedproperty '{0}', '{1}', 'user', @CurrentUser, 'table', '{2}', 'column', '{3}'",
                ExtendedPropertyNameForComments, comment, tableName, columnName);
        }

        public override string GetAddCommentToTableSql(string tableName, string comment) {
            return String.Format(@"
                declare @CurrentUser sysname; 
                select @CurrentUser = user_name(); 
                execute sp_addextendedproperty '{0}', '{1}', 'user', @CurrentUser, 'table', '{2}'",
                ExtendedPropertyNameForComments, comment, tableName);
        }

        public override string GetRemoveCommentFromColumnSql(string tableName, string columnName) {
            return String.Format(@"
                declare @CurrentUser sysname; 
                select @CurrentUser = user_name(); 
                execute sp_dropextendedproperty '{0}', 'user', @CurrentUser, 'table', '{1}', 'column', '{2}' ",
                ExtendedPropertyNameForComments, tableName, columnName);
        }

        public override string GetRemoveCommentFromTableSql(string tableName) {
            return String.Format(@"
                declare @CurrentUser sysname; 
                select @CurrentUser = user_name(); 
                execute sp_dropextendedproperty '{0}', 'user', @CurrentUser, 'table', '{1}'",
                ExtendedPropertyNameForComments, tableName);
        }

        public override string GetRenameTableSql(string tableName, string newTableName) {
            return String.Format("execute sp_rename '{0}', '{1}'", tableName, newTableName);
        }

        public override string GetRenameColumnSql(string tableName, string columnName, string newColumnName) {
            return String.Format("execute sp_rename '{0}.{1}', '{2}', 'column'", tableName, columnName, newColumnName);
        }

        public override string GetModifyColumnSql(string tableName, string columnName, Column columnDefinition) {
            return String.Format("alter table {0} alter column {1}", tableName,
                GetColumnToSqlWhenCreating(columnDefinition));
        }
    }
}