Customize Applications with XML Fragments: Part 2

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Bulasoft.Common.SchemaGenerators
{
    internal class FirebirdSchemaGenerator: SchemaGeneratorBase
    {

        protected override string GenerateTableCommand(TableDescriptor name, FieldDescriptorList fields)
        {
            StringBuilder commandText;
            if (fields.Count == 0)
            {
                commandText = new StringBuilder("create table ");
                commandText.AppendFormat("\"{0}\" (", name.Name);
                commandText.Append(Environment.NewLine);
                foreach (FieldDescriptor field in name.Fields)
                    if (field.HasNulls)
                        commandText.AppendFormat(" \"{0}\" {1}," + Environment.NewLine, field.Name, FieldToSQL(field));
                    else
                    {
                        if (field.IsPK)
                            commandText.AppendFormat(" \"{0}\" {1} NOT NULL CONSTRAINT {2} PRIMARY KEY," + Environment.NewLine, field.Name, FieldToSQL(field), SchemaGenerator.PKPrefix + name.Name);
                        else
                            commandText.AppendFormat(" \"{0}\" {1} NOT NULL," + Environment.NewLine, field.Name, FieldToSQL(field));
                    }

                commandText.Remove(commandText.Length - 1 - Environment.NewLine.Length, 1 + Environment.NewLine.Length);
                commandText.Append(")");
            }
            else
            {
                bool someChange = false;
                commandText = new StringBuilder("alter table ");
                commandText.AppendFormat("\"{0}\" add", name.Name);
                commandText.Append(Environment.NewLine);
                foreach (FieldDescriptor field in name.Fields)
                    if (!fields.Contains(field.Name))
                    {
                        someChange = true;
                        if (field.HasNulls)
                            commandText.AppendFormat("\"{0}\" {1}," + Environment.NewLine, field.Name, FieldToSQL(field));
                        else
                            commandText.AppendFormat("\"{0}\" {1} NOT NULL," + Environment.NewLine, field.Name, FieldToSQL(field));
                    }

                if (!someChange)
                    return null;
                commandText.Remove(commandText.Length - 1 - Environment.NewLine.Length, 1 + Environment.NewLine.Length);
                commandText.Append("");
            }
            return commandText.ToString();
        }
        protected override FieldDescriptorList GetFieldDescriptorList(string tableName)
        {
            FieldDescriptorList result = new FieldDescriptorList();
            DataColumn tableNameCol = allColumns.Columns["table_name"];
            DataColumn dbTypeCol = allColumns.Columns["column_data_type"];
            DataColumn columnNameCol = allColumns.Columns["column_name"];

            foreach (DataRow dr in allColumns.Rows)
            {
                if (dr[tableNameCol].ToString() == tableName)
                {
                    string dataType = dr[dbTypeCol].ToString();
                    FieldDescriptor descriptor = result.Add(dr[columnNameCol].ToString(), dataType);
                    descriptor.HasNulls = dr["is_nullable"].ToString().Equals("1");
                    if (dataType == "varchar")
                        descriptor.Length = (int)dr["COLUMN_SIZE"];
                    if (dataType == "decimal")
                    {
                        descriptor.Precision = (int)dr["numeric_precision"];
                        descriptor.Scale = (int)dr["numeric_scale"];
                    }
                }
            }

            return result;
        }


        protected override string FieldToSQL(FieldDescriptor field)
        {
            switch (Type.GetTypeCode(field.FieldSystemType))
            {
                case TypeCode.DateTime:
                    return "timestamp";
                case TypeCode.Int32:
                    return "integer";
                case TypeCode.Int64:
                    return "bigint";
                case TypeCode.Double:
                    return "DOUBLE PRECISION";
                case TypeCode.String:
                    if (field.Length.HasValue)
                        return string.Format("varchar({0})", field.Length);
                    else
                        return "BLOB SUB_TYPE 1";
                case TypeCode.Boolean:
                    return "SMALLINT";
                case TypeCode.Decimal:
                    return "DECIMAL(15,4)";
                default:
                    {
                        if (field.FieldSystemType == typeof(Guid))
                            return "CHAR(36)";

                        if (field.FieldSystemType == typeof(TimeSpan))
                            return "char(11)";

                        if (field.FieldSystemType == typeof(Enum))
                            return "integer";

                        throw new ArgumentException(string.Format("Unsupported data type {0}", field.FieldSystemType.GetType().Name));
                    }
            }
        }

        protected override string GenerateIndexCommand(TableDescriptor table, FieldDescriptor field)
        {
            return string.Format("CREATE INDEX {0} ON \"{1}\" (\"{2}\")", SchemaGenerator.IndexPrefix + table.Name + '_' + field.Name, table.Name, field.Name);
        }

        protected override string GenerateFKCommand(TableDescriptor table, FieldDescriptor field)
        {
            return string.Format("ALTER TABLE \"{0}\" ADD CONSTRAINT \"{1}\" FOREIGN KEY (\"{2}\") REFERENCES \"{3}\" (\"{4}\")",
                table.Name, table.GetFKName(field), field.Name, field.FKTableName, field.FKField.Name);
        }
    }
}