Пример #1
0
        /// <summary>
        /// Example of how to create migration scripts from an old legacy databse
        /// </summary>
        /// <param name="newServerDbName"></param>
        /// <param name="doCreate"></param>
        /// <returns></returns>
        public static List <Sql> GenerateDataMigrationScripts(string newServerDbName, bool doCreate)
        {
            // generate models off old database with underscorized naming (connstr = guru)
            // newServerDbName = thatcher.rinnai
            var result = new List <Sql>();
            var models = ActiveRecordDatabaseGenerator.GetModelInstances();

            foreach (ActiveRecord model in models)
            {
                var tableName = model.GetTableName().SqlizeName().value;
                if (!tableName.Contains("rn_"))
                {
                    continue;
                }
                if (tableName.Contains("homebanner"))
                {
                    continue;
                }
                if (tableName.Contains("filetracking"))
                {
                    continue;
                }
                if (tableName.Contains("rn_tracking"))
                {
                    continue;
                }
                if (tableName.Contains("rn_emailtemplate"))
                {
                    continue;
                }
                if (tableName.Contains("rn_categorybanner"))
                {
                    continue;
                }
                if (tableName.Contains("rn_pagecontent"))
                {
                    continue;
                }
                if (tableName.Contains("rn_promo"))
                {
                    continue;
                }
                if (tableName.Contains("rn_pagesection"))
                {
                    continue;
                }
                if (tableName.Contains("banner"))
                {
                    continue;
                }

                string sourceTable, destTable;
                sourceTable = tableName;
                destTable   = model.GetType().Name;
                destTable   = destTable.Replace("tradesmart", "_tradesmart_");
                destTable   = destTable.Replace("centre", "_centre_");
                destTable   = destTable.Replace("subcategory", "_subcategory_");
                destTable   = destTable.Replace("training", "_training_");
                destTable   = destTable.Replace("file", "_file_");
                destTable   = destTable.Replace("association", "_association_");
                destTable   = destTable.Replace("group", "_group_");
                destTable   = destTable.Replace("manuals", "_manuals_");
                destTable   = destTable.Replace("archive", "_archive_");
                destTable   = destTable.Replace("rn_tn", "rn_trade_");

                destTable = destTable.Remove("rn_").PascalCase();

                bool hasID = false;

                var fields     = new DelimitedString(",");
                var destfields = new DelimitedString(",");
                var creates    = "" + destTable + "ID [int] IDENTITY(1,1) NOT NULL, ";
                foreach (var field in model.GetFields())
                {
                    fields += field.Name.SqlizeName().value;
                    var newFieldName = field.Name;
                    foreach (var repl in "meta,centre,areas,covered,subcategory,training,retailer,tradesmart,consumer,accessory,group,price,email,size,section,path,name,manuals,product,banner,studies,performance,compare,text,study,archive,video,type,manuals,content,outlet".Split(','))
                    {
                        newFieldName = newFieldName.Replace(repl, "_" + repl + "_");
                    }
                    newFieldName = newFieldName.Replace("dateandtime", "date_and_time");
                    newFieldName = Fmt.PascalCase(newFieldName);
                    newFieldName = newFieldName.Replace("Id", "ID");
                    if (newFieldName == "ID" && destTable == "Country")
                    {
                        newFieldName = "Code";
                    }
                    else if (newFieldName == "ID")
                    {
                        newFieldName = destTable + "ID";
                        hasID        = true;
                    }
                    if (newFieldName == "Priority")
                    {
                        newFieldName = "SortPosition";
                    }
                    if (newFieldName == "Image")
                    {
                        newFieldName = "Picture";
                    }
                    if (newFieldName == "Created")
                    {
                        newFieldName = "DateAdded";
                    }
                    destfields += newFieldName;

                    if (field.Name != "id" || !hasID)
                    {
                        creates += newFieldName + " " + field.GetSqlDataTypeDeclaration().Replace("tinyint", "bit").Replace("smallint", "int").Replace("char", "varchar").Replace("varchar", "nvarchar").Replace("varnvarchar", "nvarchar").Replace("text", "nvarchar(max)").Replace("NOT NULL", "") + ", ";
                    }
                }

                if (doCreate)
                {
                    var sql2 = new Sql("create table " + destTable + " (<b>" + creates + "</b> CONSTRAINT [pk_" + destTable + "] PRIMARY KEY CLUSTERED (" + destTable + "ID ASC));");
                    result.Add(sql2);
                }
                var sql = new Sql("delete from " + destTable + ";");                   // maybe use trunc table

                if (hasID)
                {
                    sql.Add("set identity_insert " + destTable + " on;");
                }
                sql.Add("insert into " + destTable + " (" + destfields + ") select " + fields + " from RinnaiOld.dbo." + sourceTable + ";");
                if (hasID)
                {
                    sql.Add("set identity_insert " + destTable + " off;");
                }
                result.Add(sql);
            }

            foreach (var migration in result)
            {
                Web.WriteLine(migration.ToString());
            }

            return(result);
        }