From f4450ccf4f76db7f6e18c483ac48f30e0907eb2a Mon Sep 17 00:00:00 2001
From: AlexRa
Date: Sun, 16 May 2010 16:22:38 +0300
Subject: Migration.cs supports single-file migration history format
Scans for migration resources in either old-style "scattered" (one file per version)
or new-style "integrated" format (single file "Resources/{StoreName}.migrations[.nnn]") 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.
Status: TESTED and works fine in all modes!
---
OpenSim/Data/Migration.cs | 337 ++++++++++++++++++++++++++++++++--------------
1 file changed, 233 insertions(+), 104 deletions(-)
diff --git a/OpenSim/Data/Migration.cs b/OpenSim/Data/Migration.cs
index 06defe4..7980c35 100644
--- a/OpenSim/Data/Migration.cs
+++ b/OpenSim/Data/Migration.cs
@@ -70,61 +70,111 @@ namespace OpenSim.Data
public class Migration
{
- private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
+ protected 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;
+ cmd.ExecuteNonQuery();
+ }
}
+ }
+
+ protected void ExecuteScript(DbConnection conn, string sql)
+ {
+ ExecuteScript(conn, new string[]{sql});
+ }
+
+ protected void ExecuteScript(string sql)
+ {
+ ExecuteScript(_conn, sql);
+ }
- InsertVersion("migrations", 1);
+ 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 +182,41 @@ 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}", kvp.Value.ToString());
+ 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. Migration aborted.", e.Message);
+ ExecuteScript("ROLLBACK;");
+ return;
+ }
- 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 +240,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 +251,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 +260,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;
--
cgit v1.1