File: SqlClient\SqlBuilder.cs
Project: ndp\fx\src\DLinq\Dlinq\System.Data.Linq.csproj (System.Data.Linq)
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
 
namespace System.Data.Linq.SqlClient {
    using System.Data.Linq.Mapping;
    using System.Data.Linq.Provider;
    using System.Diagnostics.CodeAnalysis;
 
    /// <summary>
    /// Class for building up SQL DDL commands.
    /// </summary>
    internal static class SqlBuilder {
 
        internal static string GetCreateDatabaseCommand(string catalog, string dataFilename, string logFilename) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE DATABASE {0}", SqlIdentifier.QuoteIdentifier(catalog));
            if (dataFilename != null) {
                sb.AppendFormat(" ON PRIMARY (NAME='{0}', FILENAME='{1}')", Path.GetFileName(dataFilename), dataFilename);
                sb.AppendFormat(" LOG ON (NAME='{0}', FILENAME='{1}')", Path.GetFileName(logFilename), logFilename);
            }
            return sb.ToString();
        }
 
        internal static string GetDropDatabaseCommand(string catalog) {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("DROP DATABASE {0}", SqlIdentifier.QuoteIdentifier(catalog));
            return sb.ToString();
        }
 
        internal static string GetCreateSchemaForTableCommand(MetaTable table) {
            StringBuilder sb = new StringBuilder();
            List<string> parts = new List<string>(SqlIdentifier.GetCompoundIdentifierParts(table.TableName));
 
            // table names look like this in Yukon (according to MSDN):
            //     [ database_name . [ schema_name ] . | schema_name . ] table_name 
            // ... which means that either way, the schema name is the second to last part.
 
            if ((parts.Count) < 2) {
                return null;
            }
            
            string schema = parts[parts.Count - 2];
            if (String.Compare(schema, "DBO", StringComparison.OrdinalIgnoreCase) != 0 &&
                String.Compare(schema, "[DBO]", StringComparison.OrdinalIgnoreCase) != 0) {
                sb.AppendFormat("CREATE SCHEMA {0}", SqlIdentifier.QuoteIdentifier(schema));
            }
            return sb.ToString();
        }
 
        internal static string GetCreateTableCommand(MetaTable table) {
            StringBuilder sb = new StringBuilder();
            StringBuilder decl = new StringBuilder();
            BuildFieldDeclarations(table, decl);
            sb.AppendFormat("CREATE TABLE {0}", SqlIdentifier.QuoteCompoundIdentifier(table.TableName));
            sb.Append("(");
            sb.Append(decl.ToString());
            decl = new StringBuilder();
            BuildPrimaryKey(table, decl);
            if (decl.Length > 0) {
                string name = String.Format(Globalization.CultureInfo.InvariantCulture, "PK_{0}", table.TableName);
                sb.Append(", ");
                sb.AppendLine();
                sb.AppendFormat("  CONSTRAINT {0} PRIMARY KEY ({1})", SqlIdentifier.QuoteIdentifier(name), decl.ToString());
            }
            sb.AppendLine();
            sb.Append("  )");
            return sb.ToString();
        }
 
        internal static void BuildFieldDeclarations(MetaTable table, StringBuilder sb) {
            int n = 0;
            Dictionary<object, string> memberNameToMappedName = new Dictionary<object, string>();
            foreach (MetaType type in table.RowType.InheritanceTypes) {
                n += BuildFieldDeclarations(type, memberNameToMappedName, sb);
            }
            if (n == 0) {
                throw Error.CreateDatabaseFailedBecauseOfClassWithNoMembers(table.RowType.Type);
            }
        }
 
        private static int BuildFieldDeclarations(MetaType type, Dictionary<object, string> memberNameToMappedName, StringBuilder sb) {
            int n = 0;
            foreach (MetaDataMember mm in type.DataMembers) {
                // Only generate declarations for the current type.
                if (mm.IsDeclaredBy(type)) {
                    if (!mm.IsAssociation) {
                        if (mm.IsPersistent) {
                            object dn = InheritanceRules.DistinguishedMemberName(mm.Member);
                            string mappedName;
                            if (memberNameToMappedName.TryGetValue(dn, out mappedName)) {
                                if (mappedName == mm.MappedName) {
                                    continue;
                                }
                            }
                            else {
                                memberNameToMappedName.Add(dn, mm.MappedName);
                            }
                            if (sb.Length > 0) {
                                sb.Append(", ");
                            }
                            sb.AppendLine();
                            sb.Append(string.Format(Globalization.CultureInfo.InvariantCulture, "  {0} ", SqlIdentifier.QuoteCompoundIdentifier(mm.MappedName)));
                            if (!string.IsNullOrEmpty(mm.Expression)) {
                                // Generate "AS <expression>" for computed columns
                                sb.Append("AS " + mm.Expression);
                            }
                            else {
                                sb.Append(GetDbType(mm));
                            }
                            n++;
                        }
                    }
                }
            }
            return n;
        }
 
        private static void BuildPrimaryKey(MetaTable table, StringBuilder sb) {
            foreach (MetaDataMember mm in table.RowType.IdentityMembers) {
                if (sb.Length > 0) {
                    sb.Append(", ");
                }
                sb.Append(SqlIdentifier.QuoteCompoundIdentifier(mm.MappedName));
            }
        }
 
        private static string BuildKey(IEnumerable<MetaDataMember> members) {
            StringBuilder sb = new StringBuilder();
            foreach (MetaDataMember mm in members) {
                if (sb.Length > 0) {
                    sb.Append(", ");
                }
                sb.Append(SqlIdentifier.QuoteCompoundIdentifier(mm.MappedName));
            }
            return sb.ToString();
        }
 
        internal static IEnumerable<String> GetCreateForeignKeyCommands(MetaTable table) {
            foreach (MetaType type in table.RowType.InheritanceTypes) {
                foreach (string command in GetCreateForeignKeyCommands(type)) {
                    yield return command;
                }
            }
        }
 
        private static IEnumerable<String> GetCreateForeignKeyCommands(MetaType type) {
            string tableName = type.Table.TableName;
            foreach (MetaDataMember mm in type.DataMembers) {
                if (mm.IsDeclaredBy(type) && mm.IsAssociation) {
                    MetaAssociation assoc = mm.Association;
                    if (assoc.IsForeignKey) {
                        StringBuilder sb = new StringBuilder();
                        string thisKey = BuildKey(assoc.ThisKey);
                        string otherKey = BuildKey(assoc.OtherKey);
                        string otherTable = assoc.OtherType.Table.TableName;
                        string name;
                        name = mm.MappedName;
                        if (name == mm.Name) {
                            name = String.Format(Globalization.CultureInfo.InvariantCulture, "FK_{0}_{1}", tableName, mm.Name);
                        }
                        string cmd = "ALTER TABLE {0}" + Environment.NewLine + "  ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}({4})";
                        //In DLinq we put the constraint on the child object (which triggers the behavior when deleted),
                        //but in SQL it is part of the parent constraint (the parent row gets changed / deleted to satisfy the constraint)
                        MetaDataMember otherMember = mm.Association.OtherMember;
                        if (otherMember != null) {
                            string delConstr = otherMember.Association.DeleteRule;
                            if (delConstr != null) {
                                cmd += Environment.NewLine + "  ON DELETE " + delConstr;
                            }
                        }
                        sb.AppendFormat(cmd,
                            SqlIdentifier.QuoteCompoundIdentifier(tableName),
                            SqlIdentifier.QuoteIdentifier(name),
                            SqlIdentifier.QuoteCompoundIdentifier(thisKey),
                            SqlIdentifier.QuoteCompoundIdentifier(otherTable),
                            SqlIdentifier.QuoteCompoundIdentifier(otherKey));
                        yield return sb.ToString();
                    }
                }
            }
        }
 
        [SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity", Justification="These issues are related to our use of if-then and case statements for node types, which adds to the complexity count however when reviewed they are easy to navigate and understand.")]
        private static string GetDbType(MetaDataMember mm) {
            string dbType = mm.DbType;
 
            if (dbType != null) return dbType;
            StringBuilder sb = new StringBuilder();
 
            Type type = mm.Type;
            bool isNullable = mm.CanBeNull;
            if (type.IsValueType && IsNullable(type)) {
                type = type.GetGenericArguments()[0];
            }
 
            if (mm.IsVersion) {
                sb.Append("Timestamp");
            }
            else {
                if (mm.IsPrimaryKey && mm.IsDbGenerated) {
                    switch (Type.GetTypeCode(type)) {
                        case TypeCode.Byte:
                            sb.Append("TinyInt");
                            break;
                        case TypeCode.SByte:
                        case TypeCode.Int16:
                            sb.Append("SmallInt");
                            break;
                        case TypeCode.Int32:
                        case TypeCode.UInt16:
                            sb.Append("Int");
                            break;
                        case TypeCode.Int64:
                        case TypeCode.UInt32:
                            sb.Append("BigInt");
                            break;
                        case TypeCode.UInt64:
                        case TypeCode.Decimal:
                            sb.Append("Decimal(20)");
                            break;
                        case TypeCode.Object:
                            if (type == typeof(Guid)) {
                                sb.Append("UniqueIdentifier");
                            } else {
                                throw Error.CouldNotDetermineDbGeneratedSqlType(type);
                            }
                            break;
                    }
                }
                else {
                    switch (Type.GetTypeCode(type)) {
                        case TypeCode.Boolean:
                           sb.Append("Bit");
                            break;
                        case TypeCode.Byte:
                            sb.Append("TinyInt");
                            break;
                        case TypeCode.SByte:
                        case TypeCode.Int16:
                            sb.Append("SmallInt");
                            break;
                        case TypeCode.Int32:
                        case TypeCode.UInt16:
                            sb.Append("Int");
                            break;
                        case TypeCode.Int64:
                        case TypeCode.UInt32:
                            sb.Append("BigInt");
                            break;
                        case TypeCode.UInt64:
                            sb.Append("Decimal(20)");
                            break;
                        case TypeCode.Decimal:
                            sb.Append("Decimal(29, 4)");
                            break;
                        case TypeCode.Double:
                            sb.Append("Float");
                            break;
                        case TypeCode.Single:
                            sb.Append("Real");
                            break;
                        case TypeCode.Char:
                            sb.Append("NChar(1)");
                            break;
                        case TypeCode.String:
                            sb.Append("NVarChar(4000)");
                            break;
                        case TypeCode.DateTime:
                            sb.Append("DateTime");
                            break;
                            case TypeCode.Object:
                                if (type == typeof(Guid)) {
                                    sb.Append("UniqueIdentifier");
                                } else if (type == typeof(byte[])) {
                                    sb.Append("VarBinary(8000)");
                                } else if (type == typeof(char[])) {
                                    sb.Append("NVarChar(4000)");
                                } else if (type == typeof(DateTimeOffset)) {
                                    sb.Append("DateTimeOffset");
                                } else if (type == typeof(TimeSpan)) {
                                    sb.Append("Time");
                                } else {
                                    throw Error.CouldNotDetermineSqlType(type);
                                }
                                break;
                     }
                }
            }
 
            if (!isNullable) {
                sb.Append(" NOT NULL");
            }
 
            if (mm.IsPrimaryKey && mm.IsDbGenerated) {
                if (type == typeof(Guid)) {
                    sb.Append(" DEFAULT NEWID()");
                }
                else {
                    sb.Append(" IDENTITY");
                }
            }
 
            return sb.ToString();
        }
 
        internal static bool IsNullable(Type type) {
            return type.IsGenericType && typeof(Nullable<>).IsAssignableFrom(type.GetGenericTypeDefinition());
        }
    }
}