1 files changed, 238 insertions, 103 deletions

diff --git a/OpenSim/Data/Migration.cs b/OpenSim/Data/Migration.cs
index 06defe4..4f113a2 100644
--- a/OpenSim/Data/Migration.cs
+++ b/OpenSim/Data/Migration.cs
@@ -72,59 +72,116 @@ namespace OpenSim.Data
     {
         private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
 
-        private string _type;
-        private DbConnection _conn;
-        // private string _subtype;
-        private Assembly _assem;
-        private Regex _match;
+        protected string _type;
+        protected DbConnection _conn;
+        protected Assembly _assem;
 
-        private static readonly string _migrations_create = "create table migrations(name varchar(100), version int)";
-        // private static readonly string _migrations_init = "insert into migrations values('migrations', 1)";
-        // private static readonly string _migrations_find = "select version from migrations where name='migrations'";
+        private Regex _match_old;
+        private Regex _match_new;
 
+        /// <summary>Have the parameterless constructor just so we can specify it as a generic parameter with the new() constraint.
+        /// Currently this is only used in the tests. A Migration instance created this way must be then
+        /// initialized with Initialize(). Regular creation should be through the parameterized constructors. 
+        /// </summary>
+        public Migration()
+        { 
+        }
 
-        public Migration(DbConnection conn, Assembly assem, string type)
+        public Migration(DbConnection conn, Assembly assem, string subtype, string type)
         {
-            _type = type;
-            _conn = conn;
-            _assem = assem;
-            _match = new Regex(@"\.(\d\d\d)_" + _type + @"\.sql");
-            Initialize();
+            Initialize(conn, assem, type, subtype);
         }
 
-        public Migration(DbConnection conn, Assembly assem, string subtype, string type)
+        public Migration(DbConnection conn, Assembly assem, string type) 
+        {
+            Initialize(conn, assem, type, "");
+        }
+
+        /// <summary>Must be called after creating with the parameterless constructor.
+        /// NOTE that the Migration class now doesn't access database in any way during initialization.
+        /// Specifically, it won't check if the [migrations] table exists. Such checks are done later:
+        /// automatically on Update(), or you can explicitly call InitMigrationsTable().
+        /// </summary>
+        /// <param name="conn"></param>
+        /// <param name="assem"></param>
+        /// <param name="subtype"></param>
+        /// <param name="type"></param>
+        public void Initialize (DbConnection conn, Assembly assem, string type, string subtype)
         {
             _type = type;
             _conn = conn;
             _assem = assem;
-            _match = new Regex(subtype + @"\.(\d\d\d)_" + _type + @"\.sql");
-            Initialize();
+            _match_old = new Regex(subtype + @"\.(\d\d\d)_" + _type + @"\.sql");
+            string s = String.IsNullOrEmpty(subtype) ? _type : _type + @"\." + subtype;
+            _match_new = new Regex(@"\." + s + @"\.migrations(?:\.(?<ver>\d+)$|.*)");
         }
 
-        private void Initialize()
+        public void InitMigrationsTable()
         {
-            // clever, eh, we figure out which migrations version we are
-            int migration_version = FindVersion(_conn, "migrations");
-
-            if (migration_version > 0)
-                return;
+            // NOTE: normally when the [migrations] table is created, the version record for 'migrations' is
+            // added immediately. However, if for some reason the table is there but empty, we want to handle that as well.
+            int ver = FindVersion(_conn, "migrations");
+            if (ver <= 0)   // -1 = no table, 0 = no version record
+            {
+                if( ver < 0 )
+                    ExecuteScript("create table migrations(name varchar(100), version int)");
+                InsertVersion("migrations", 1);
+            }
+        }
 
-            // If not, create the migration tables
-            using (DbCommand cmd = _conn.CreateCommand())
+        /// <summary>Executes a script, possibly in a database-specific way.
+        /// It can be redefined for a specific DBMS, if necessary. Specifically,
+        /// to avoid problems with proc definitions in MySQL, we must use
+        /// MySqlScript class instead of just DbCommand. We don't want to bring
+        /// MySQL references here, so instead define a MySQLMigration class
+        /// in OpenSim.Data.MySQL
+        /// </summary>
+        /// <param name="conn"></param>
+        /// <param name="script">Array of strings, one-per-batch (often just one)</param>
+        protected virtual void ExecuteScript(DbConnection conn, string[] script)
+        {
+            using (DbCommand cmd = conn.CreateCommand())
             {
-                cmd.CommandText = _migrations_create;
-                cmd.ExecuteNonQuery();
+                cmd.CommandTimeout = 0;
+                foreach (string sql in script)
+                {
+                    cmd.CommandText = sql;
+                    try
+                    {
+                        cmd.ExecuteNonQuery();
+                    }
+                    catch(Exception e)
+                    {
+                        throw new Exception(e.Message + " in SQL: " + sql);
+                    }
+                }
             }
+        }
 
-            InsertVersion("migrations", 1);
+        protected void ExecuteScript(DbConnection conn, string sql)
+        {
+            ExecuteScript(conn, new string[]{sql});
+        }
+
+        protected void ExecuteScript(string sql)
+        {
+            ExecuteScript(_conn, sql);
+        }
+
+        protected void ExecuteScript(string[] script)
+        {
+            ExecuteScript(_conn, script);
         }
 
+
+
         public void Update()
         {
-            int version = 0;
-            version = FindVersion(_conn, _type);
+            InitMigrationsTable();
 
-            SortedList<int, string> migrations = GetMigrationsAfter(version);
+            int version = FindVersion(_conn, _type);
+
+            SortedList<int, string[]> migrations = GetMigrationsAfter(version);
             if (migrations.Count < 1)
                 return;
 
@@ -132,57 +189,40 @@ namespace OpenSim.Data
             m_log.InfoFormat("[MIGRATIONS] Upgrading {0} to latest revision {1}.", _type, migrations.Keys[migrations.Count - 1]);
             m_log.Info("[MIGRATIONS] NOTE: this may take a while, don't interupt this process!");
 
-            using (DbCommand cmd = _conn.CreateCommand())
+            foreach (KeyValuePair<int, string[]> kvp in migrations)
             {
-                foreach (KeyValuePair<int, string> kvp in migrations)
+                int newversion = kvp.Key;
+                // we need to up the command timeout to infinite as we might be doing long migrations.
+
+                /* [AlexRa 01-May-10]: We can't always just run any SQL in a single batch (= ExecuteNonQuery()). Things like 
+                 * stored proc definitions might have to be sent to the server each in a separate batch.
+                 * This is certainly so for MS SQL; not sure how the MySQL connector sorts out the mess
+                 * with 'delimiter @@'/'delimiter ;' around procs.  So each "script" this code executes now is not
+                 * a single string, but an array of strings, executed separately.
+                */
+                try
                 {
-                    int newversion = kvp.Key;
-                    cmd.CommandText = kvp.Value;
-                    // we need to up the command timeout to infinite as we might be doing long migrations.
-                    cmd.CommandTimeout = 0;
-                    try
-                    {
-                        cmd.ExecuteNonQuery();
-                    }
-                    catch (Exception e)
-                    {
-                        m_log.DebugFormat("[MIGRATIONS] Cmd was {0}", cmd.CommandText);
-                        m_log.DebugFormat("[MIGRATIONS]: An error has occurred in the migration {0}.\n This may mean you could see errors trying to run OpenSim. If you see database related errors, you will need to fix the issue manually. Continuing.", e.Message);
-                        cmd.CommandText = "ROLLBACK;";
-                        cmd.ExecuteNonQuery();
-                    }
+                    ExecuteScript(kvp.Value);
+                }
+                catch (Exception e)
+                {
+                    m_log.DebugFormat("[MIGRATIONS] Cmd was {0}", e.Message.Replace("\n", " "));
+                    m_log.Debug("[MIGRATIONS]: An error has occurred in the migration. This may mean you could see errors trying to run OpenSim. If you see database related errors, you will need to fix the issue manually. Continuing.");
+                    ExecuteScript("ROLLBACK;");
+                }
 
-                    if (version == 0)
-                    {
-                        InsertVersion(_type, newversion);
-                    }
-                    else
-                    {
-                        UpdateVersion(_type, newversion);
-                    }
-                    version = newversion;
+                if (version == 0)
+                {
+                    InsertVersion(_type, newversion);
                 }
+                else
+                {
+                    UpdateVersion(_type, newversion);
+                }
+                version = newversion;
             }
         }
 
-        // private int MaxVersion()
-        // {
-        //     int max = 0;
-        //     string[] names = _assem.GetManifestResourceNames();
-
-        //     foreach (string s in names)
-        //     {
-        //         Match m = _match.Match(s);
-        //         if (m.Success)
-        //         {
-        //             int MigrationVersion = int.Parse(m.Groups[1].ToString());
-        //             if (MigrationVersion > max)
-        //                 max = MigrationVersion;
-        //         }
-        //     }
-        //     return max;
-        // }
-
         public int Version
         {
             get { return FindVersion(_conn, _type); }
@@ -206,7 +246,7 @@ namespace OpenSim.Data
                 try
                 {
                     cmd.CommandText = "select version from migrations where name='" + type + "' order by version desc";
-                    using (IDataReader reader = cmd.ExecuteReader())
+                    using (DbDataReader reader = cmd.ExecuteReader())
                     {
                         if (reader.Read())
                         {
@@ -217,7 +257,8 @@ namespace OpenSim.Data
                 }
                 catch
                 {
-                    // Something went wrong, so we're version 0
+                    // Something went wrong (probably no table), so we're at version -1
+                    version = -1;
                 }
             }
             return version;
@@ -225,57 +266,151 @@ namespace OpenSim.Data
 
         private void InsertVersion(string type, int version)
         {
-            using (DbCommand cmd = _conn.CreateCommand())
-            {
-                cmd.CommandText = "insert into migrations(name, version) values('" + type + "', " + version + ")";
-                m_log.InfoFormat("[MIGRATIONS]: Creating {0} at version {1}", type, version);
-                cmd.ExecuteNonQuery();
-            }
+            m_log.InfoFormat("[MIGRATIONS]: Creating {0} at version {1}", type, version);
+            ExecuteScript("insert into migrations(name, version) values('" + type + "', " + version + ")");
         }
 
         private void UpdateVersion(string type, int version)
         {
-            using (DbCommand cmd = _conn.CreateCommand())
-            {
-                cmd.CommandText = "update migrations set version=" + version + " where name='" + type + "'";
-                m_log.InfoFormat("[MIGRATIONS]: Updating {0} to version {1}", type, version);
-                cmd.ExecuteNonQuery();
-            }
+            m_log.InfoFormat("[MIGRATIONS]: Updating {0} to version {1}", type, version);
+            ExecuteScript("update migrations set version=" + version + " where name='" + type + "'");
         }
 
-        // private SortedList<int, string> GetAllMigrations()
-        // {
-        //     return GetMigrationsAfter(0);
-        // }
+        private delegate void FlushProc();
 
-        private SortedList<int, string> GetMigrationsAfter(int after)
+        /// <summary>Scans for migration resources in either old-style "scattered" (one file per version)
+        /// or new-style "integrated" format (single file with ":VERSION nnn" sections). 
+        /// In the new-style migrations it also recognizes ':GO' separators for parts of the SQL script
+        /// that must be sent to the server separately.  The old-style migrations are loaded each in one piece
+        /// and don't support the ':GO' feature.
+        /// </summary>
+        /// <param name="after">The version we are currently at. Scan for any higher versions</param>
+        /// <returns>A list of string arrays, representing the scripts.</returns>
+        private SortedList<int, string[]> GetMigrationsAfter(int after)
         {
+            SortedList<int, string[]> migrations = new SortedList<int, string[]>();
+
             string[] names = _assem.GetManifestResourceNames();
-            SortedList<int, string> migrations = new SortedList<int, string>();
-            // because life is funny if we don't
-            Array.Sort(names);
+            if( names.Length == 0 )     // should never happen
+                return migrations;
+
+            Array.Sort(names);  // we want all the migrations ordered
+
+            int nLastVerFound = 0;
+            Match m = null;
+            string sFile = Array.FindLast(names, nm => { m = _match_new.Match(nm); return m.Success; });  // ; nm.StartsWith(sPrefix, StringComparison.InvariantCultureIgnoreCase
+
+            if( (m != null) && !String.IsNullOrEmpty(sFile) )
+            {
+                /* The filename should be '<StoreName>.migrations[.NNN]' where NNN
+                 * is the last version number defined in the file. If the '.NNN' part is recognized, the code can skip
+                 * the file without looking inside if we have a higher version already. Without the suffix we read 
+                 * the file anyway and use the version numbers inside.  Any unrecognized suffix (such as '.sql')
+                 * is valid but ignored.
+                 * 
+                 *  NOTE that we expect only one 'merged' migration file. If there are several, we take the last one. 
+                 *  If you are numbering them, leave only the latest one in the project or at least make sure they numbered 
+                 *  to come up in the correct order (e.g. 'SomeStore.migrations.001' rather than 'SomeStore.migrations.1')
+                 */
+
+                if (m.Groups.Count > 1 && int.TryParse(m.Groups[1].Value, out nLastVerFound))
+                {
+                    if( nLastVerFound <= after )
+                        goto scan_old_style;
+                }
+
+                System.Text.StringBuilder sb = new System.Text.StringBuilder(4096);
+                int nVersion = -1;
+
+                List<string> script = new List<string>();
+
+                FlushProc flush = delegate()
+                {
+                    if (sb.Length > 0)     // last SQL stmt to script list
+                    {
+                        script.Add(sb.ToString());
+                        sb.Length = 0;
+                    }
 
+                    if ( (nVersion > 0) && (nVersion > after) && (script.Count > 0) && !migrations.ContainsKey(nVersion))   // script to the versioned script list
+                    {
+                        migrations[nVersion] = script.ToArray();
+                    }
+                    script.Clear();
+                };
+
+                using (Stream resource = _assem.GetManifestResourceStream(sFile))
+                using (StreamReader resourceReader = new StreamReader(resource))
+                {
+                    int nLineNo = 0;
+                    while (!resourceReader.EndOfStream)
+                    {
+                        string sLine = resourceReader.ReadLine();
+                        nLineNo++;
+
+                        if( String.IsNullOrEmpty(sLine) || sLine.StartsWith("#") )  // ignore a comment or empty line
+                            continue;
+
+                        if (sLine.Trim().Equals(":GO", StringComparison.InvariantCultureIgnoreCase))
+                        {
+                            if (sb.Length == 0) continue;
+                            if (nVersion > after) 
+                                script.Add(sb.ToString());
+                            sb.Length = 0;
+                            continue;
+                        }
+
+                        if (sLine.StartsWith(":VERSION ", StringComparison.InvariantCultureIgnoreCase))  // ":VERSION nnn"
+                        {
+                            flush();
+
+                            int n = sLine.IndexOf('#');     // Comment is allowed in version sections, ignored
+                            if (n >= 0)
+                                sLine = sLine.Substring(0, n);
+
+                            if (!int.TryParse(sLine.Substring(9).Trim(), out nVersion))
+                            {
+                                m_log.ErrorFormat("[MIGRATIONS]: invalid version marker at {0}: line {1}. Migration failed!", sFile, nLineNo);
+                                break;
+                            }
+                        }
+                        else
+                        {
+                            sb.AppendLine(sLine);
+                        }
+                    }
+                    flush();
+
+                    // If there are scattered migration files as well, only look for those with higher version numbers.
+                    if (after < nVersion)
+                        after = nVersion;
+                }
+            }
+
+scan_old_style:
+            // scan "old style" migration pieces anyway, ignore any versions already filled from the single file
             foreach (string s in names)
             {
-                Match m = _match.Match(s);
+                m = _match_old.Match(s);
                 if (m.Success)
                 {
                     int version = int.Parse(m.Groups[1].ToString());
-                    if (version > after)
+                    if ( (version > after) && !migrations.ContainsKey(version) )
                     {
                         using (Stream resource = _assem.GetManifestResourceStream(s))
                         {
                             using (StreamReader resourceReader = new StreamReader(resource))
                             {
-                                string resourceString = resourceReader.ReadToEnd();
-                                migrations.Add(version, resourceString);
+                                string sql = resourceReader.ReadToEnd();
+                                migrations.Add(version, new string[]{sql});
                             }
                         }
                     }
                 }
             }
-
-            if (migrations.Count < 1) {
+            
+            if (migrations.Count < 1) 
+            {
                 m_log.InfoFormat("[MIGRATIONS]: {0} up to date, no migrations to apply", _type);
             }
             return migrations;