clockwerk-opensim – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 vero 1 /*
2 * Copyright (c) Contributors, http://opensimulator.org/
3 * See CONTRIBUTORS.TXT for a full list of copyright holders.
4 *
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions are met:
7 * * Redistributions of source code must retain the above copyright
8 * notice, this list of conditions and the following disclaimer.
9 * * Redistributions in binary form must reproduce the above copyright
10 * notice, this list of conditions and the following disclaimer in the
11 * documentation and/or other materials provided with the distribution.
12 * * Neither the name of the OpenSimulator Project nor the
13 * names of its contributors may be used to endorse or promote products
14 * derived from this software without specific prior written permission.
15 *
16 * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ``AS IS'' AND ANY
17 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
18 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
19 * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY
20 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
21 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
22 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
23 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
24 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
25 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
26 */
27  
28 using System;
29 using System.Collections.Generic;
30 using System.Data;
31 using System.Data.Common;
32 using System.IO;
33 using System.Reflection;
34 using System.Text.RegularExpressions;
35 using log4net;
36  
37 namespace OpenSim.Data
38 {
39 /// <summary>
40 ///
41 /// The Migration theory is based on the ruby on rails concept.
42 /// Each database driver is going to be allowed to have files in
43 /// Resources that specify the database migrations. They will be
44 /// of the form:
45 ///
46 /// 001_Users.sql
47 /// 002_Users.sql
48 /// 003_Users.sql
49 /// 001_Prims.sql
50 /// 002_Prims.sql
51 /// ...etc...
52 ///
53 /// When a database driver starts up, it specifies a resource that
54 /// needs to be brought up to the current revision. For instance:
55 ///
56 /// Migration um = new Migration(DbConnection, Assembly, "Users");
57 /// um.Update();
58 ///
59 /// This works out which version Users is at, and applies all the
60 /// revisions past it to it. If there is no users table, all
61 /// revisions are applied in order. Consider each future
62 /// migration to be an incremental roll forward of the tables in
63 /// question.
64 ///
65 /// Assembly must be specifically passed in because otherwise you
66 /// get the assembly that Migration.cs is part of, and what you
67 /// really want is the assembly of your database class.
68 ///
69 /// </summary>
70 public class Migration
71 {
72 private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
73  
74 protected string _type;
75 protected DbConnection _conn;
76 protected Assembly _assem;
77  
78 private Regex _match_old;
79 private Regex _match_new;
80  
81 /// <summary>Have the parameterless constructor just so we can specify it as a generic parameter with the new() constraint.
82 /// Currently this is only used in the tests. A Migration instance created this way must be then
83 /// initialized with Initialize(). Regular creation should be through the parameterized constructors.
84 /// </summary>
85 public Migration()
86 {
87 }
88  
89 public Migration(DbConnection conn, Assembly assem, string subtype, string type)
90 {
91 Initialize(conn, assem, type, subtype);
92 }
93  
94 public Migration(DbConnection conn, Assembly assem, string type)
95 {
96 Initialize(conn, assem, type, "");
97 }
98  
99 /// <summary>Must be called after creating with the parameterless constructor.
100 /// NOTE that the Migration class now doesn't access database in any way during initialization.
101 /// Specifically, it won't check if the [migrations] table exists. Such checks are done later:
102 /// automatically on Update(), or you can explicitly call InitMigrationsTable().
103 /// </summary>
104 /// <param name="conn"></param>
105 /// <param name="assem"></param>
106 /// <param name="subtype"></param>
107 /// <param name="type"></param>
108 public void Initialize (DbConnection conn, Assembly assem, string type, string subtype)
109 {
110 _type = type;
111 _conn = conn;
112 _assem = assem;
113 _match_old = new Regex(subtype + @"\.(\d\d\d)_" + _type + @"\.sql");
114 string s = String.IsNullOrEmpty(subtype) ? _type : _type + @"\." + subtype;
115 _match_new = new Regex(@"\." + s + @"\.migrations(?:\.(?<ver>\d+)$|.*)");
116 }
117  
118 public void InitMigrationsTable()
119 {
120 // NOTE: normally when the [migrations] table is created, the version record for 'migrations' is
121 // added immediately. However, if for some reason the table is there but empty, we want to handle that as well.
122 int ver = FindVersion(_conn, "migrations");
123 if (ver <= 0) // -1 = no table, 0 = no version record
124 {
125 if (ver < 0)
126 ExecuteScript("create table migrations(name varchar(100), version int)");
127 InsertVersion("migrations", 1);
128 }
129 }
130  
131 /// <summary>Executes a script, possibly in a database-specific way.
132 /// It can be redefined for a specific DBMS, if necessary. Specifically,
133 /// to avoid problems with proc definitions in MySQL, we must use
134 /// MySqlScript class instead of just DbCommand. We don't want to bring
135 /// MySQL references here, so instead define a MySQLMigration class
136 /// in OpenSim.Data.MySQL
137 /// </summary>
138 /// <param name="conn"></param>
139 /// <param name="script">Array of strings, one-per-batch (often just one)</param>
140 protected virtual void ExecuteScript(DbConnection conn, string[] script)
141 {
142 using (DbCommand cmd = conn.CreateCommand())
143 {
144 cmd.CommandTimeout = 0;
145 foreach (string sql in script)
146 {
147 cmd.CommandText = sql;
148 try
149 {
150 cmd.ExecuteNonQuery();
151 }
152 catch(Exception e)
153 {
154 throw new Exception(e.Message + " in SQL: " + sql);
155 }
156 }
157 }
158 }
159  
160 protected void ExecuteScript(DbConnection conn, string sql)
161 {
162 ExecuteScript(conn, new string[]{sql});
163 }
164  
165 protected void ExecuteScript(string sql)
166 {
167 ExecuteScript(_conn, sql);
168 }
169  
170 protected void ExecuteScript(string[] script)
171 {
172 ExecuteScript(_conn, script);
173 }
174  
175 public void Update()
176 {
177 InitMigrationsTable();
178  
179 int version = FindVersion(_conn, _type);
180  
181 SortedList<int, string[]> migrations = GetMigrationsAfter(version);
182 if (migrations.Count < 1)
183 return;
184  
185 // to prevent people from killing long migrations.
186 m_log.InfoFormat("[MIGRATIONS]: Upgrading {0} to latest revision {1}.", _type, migrations.Keys[migrations.Count - 1]);
187 m_log.Info("[MIGRATIONS]: NOTE - this may take a while, don't interrupt this process!");
188  
189 foreach (KeyValuePair<int, string[]> kvp in migrations)
190 {
191 int newversion = kvp.Key;
192 // we need to up the command timeout to infinite as we might be doing long migrations.
193  
194 /* [AlexRa 01-May-10]: We can't always just run any SQL in a single batch (= ExecuteNonQuery()). Things like
195 * stored proc definitions might have to be sent to the server each in a separate batch.
196 * This is certainly so for MS SQL; not sure how the MySQL connector sorts out the mess
197 * with 'delimiter @@'/'delimiter ;' around procs. So each "script" this code executes now is not
198 * a single string, but an array of strings, executed separately.
199 */
200 try
201 {
202 ExecuteScript(kvp.Value);
203 }
204 catch (Exception e)
205 {
206 m_log.DebugFormat("[MIGRATIONS]: Cmd was {0}", e.Message.Replace("\n", " "));
207 m_log.Debug("[MIGRATIONS]: An error has occurred in the migration. If you're running OpenSim for the first time then you can probably safely ignore this, since certain migration commands attempt to fetch data out of old tables. However, if you're using an existing database and you see database related errors while running OpenSim then you will need to fix these problems manually. Continuing.");
208 ExecuteScript("ROLLBACK;");
209 }
210  
211 if (version == 0)
212 {
213 InsertVersion(_type, newversion);
214 }
215 else
216 {
217 UpdateVersion(_type, newversion);
218 }
219 version = newversion;
220 }
221 }
222  
223 public int Version
224 {
225 get { return FindVersion(_conn, _type); }
226 set {
227 if (Version < 1)
228 {
229 InsertVersion(_type, value);
230 }
231 else
232 {
233 UpdateVersion(_type, value);
234 }
235 }
236 }
237  
238 protected virtual int FindVersion(DbConnection conn, string type)
239 {
240 int version = 0;
241 using (DbCommand cmd = conn.CreateCommand())
242 {
243 try
244 {
245 cmd.CommandText = "select version from migrations where name='" + type + "' order by version desc";
246 using (DbDataReader reader = cmd.ExecuteReader())
247 {
248 if (reader.Read())
249 {
250 version = Convert.ToInt32(reader["version"]);
251 }
252 reader.Close();
253 }
254 }
255 catch
256 {
257 // Something went wrong (probably no table), so we're at version -1
258 version = -1;
259 }
260 }
261 return version;
262 }
263  
264 private void InsertVersion(string type, int version)
265 {
266 m_log.InfoFormat("[MIGRATIONS]: Creating {0} at version {1}", type, version);
267 ExecuteScript("insert into migrations(name, version) values('" + type + "', " + version + ")");
268 }
269  
270 private void UpdateVersion(string type, int version)
271 {
272 m_log.InfoFormat("[MIGRATIONS]: Updating {0} to version {1}", type, version);
273 ExecuteScript("update migrations set version=" + version + " where name='" + type + "'");
274 }
275  
276 private delegate void FlushProc();
277  
278 /// <summary>Scans for migration resources in either old-style "scattered" (one file per version)
279 /// or new-style "integrated" format (single file with ":VERSION nnn" sections).
280 /// In the new-style migrations it also recognizes ':GO' separators for parts of the SQL script
281 /// that must be sent to the server separately. The old-style migrations are loaded each in one piece
282 /// and don't support the ':GO' feature.
283 /// </summary>
284 /// <param name="after">The version we are currently at. Scan for any higher versions</param>
285 /// <returns>A list of string arrays, representing the scripts.</returns>
286 private SortedList<int, string[]> GetMigrationsAfter(int after)
287 {
288 SortedList<int, string[]> migrations = new SortedList<int, string[]>();
289  
290 string[] names = _assem.GetManifestResourceNames();
291 if (names.Length == 0) // should never happen
292 return migrations;
293  
294 Array.Sort(names); // we want all the migrations ordered
295  
296 int nLastVerFound = 0;
297 Match m = null;
298 string sFile = Array.FindLast(names, nm => { m = _match_new.Match(nm); return m.Success; }); // ; nm.StartsWith(sPrefix, StringComparison.InvariantCultureIgnoreCase
299  
300 if ((m != null) && !String.IsNullOrEmpty(sFile))
301 {
302 /* The filename should be '<StoreName>.migrations[.NNN]' where NNN
303 * is the last version number defined in the file. If the '.NNN' part is recognized, the code can skip
304 * the file without looking inside if we have a higher version already. Without the suffix we read
305 * the file anyway and use the version numbers inside. Any unrecognized suffix (such as '.sql')
306 * is valid but ignored.
307 *
308 * NOTE that we expect only one 'merged' migration file. If there are several, we take the last one.
309 * If you are numbering them, leave only the latest one in the project or at least make sure they numbered
310 * to come up in the correct order (e.g. 'SomeStore.migrations.001' rather than 'SomeStore.migrations.1')
311 */
312  
313 if (m.Groups.Count > 1 && int.TryParse(m.Groups[1].Value, out nLastVerFound))
314 {
315 if (nLastVerFound <= after)
316 goto scan_old_style;
317 }
318  
319 System.Text.StringBuilder sb = new System.Text.StringBuilder(4096);
320 int nVersion = -1;
321  
322 List<string> script = new List<string>();
323  
324 FlushProc flush = delegate()
325 {
326 if (sb.Length > 0) // last SQL stmt to script list
327 {
328 script.Add(sb.ToString());
329 sb.Length = 0;
330 }
331  
332 if ((nVersion > 0) && (nVersion > after) && (script.Count > 0) && !migrations.ContainsKey(nVersion)) // script to the versioned script list
333 {
334 migrations[nVersion] = script.ToArray();
335 }
336 script.Clear();
337 };
338  
339 using (Stream resource = _assem.GetManifestResourceStream(sFile))
340 using (StreamReader resourceReader = new StreamReader(resource))
341 {
342 int nLineNo = 0;
343 while (!resourceReader.EndOfStream)
344 {
345 string sLine = resourceReader.ReadLine();
346 nLineNo++;
347  
348 if (String.IsNullOrEmpty(sLine) || sLine.StartsWith("#")) // ignore a comment or empty line
349 continue;
350  
351 if (sLine.Trim().Equals(":GO", StringComparison.InvariantCultureIgnoreCase))
352 {
353 if (sb.Length == 0) continue;
354 if (nVersion > after)
355 script.Add(sb.ToString());
356 sb.Length = 0;
357 continue;
358 }
359  
360 if (sLine.StartsWith(":VERSION ", StringComparison.InvariantCultureIgnoreCase)) // ":VERSION nnn"
361 {
362 flush();
363  
364 int n = sLine.IndexOf('#'); // Comment is allowed in version sections, ignored
365 if (n >= 0)
366 sLine = sLine.Substring(0, n);
367  
368 if (!int.TryParse(sLine.Substring(9).Trim(), out nVersion))
369 {
370 m_log.ErrorFormat("[MIGRATIONS]: invalid version marker at {0}: line {1}. Migration failed!", sFile, nLineNo);
371 break;
372 }
373 }
374 else
375 {
376 sb.AppendLine(sLine);
377 }
378 }
379 flush();
380  
381 // If there are scattered migration files as well, only look for those with higher version numbers.
382 if (after < nVersion)
383 after = nVersion;
384 }
385 }
386  
387 scan_old_style:
388 // scan "old style" migration pieces anyway, ignore any versions already filled from the single file
389 foreach (string s in names)
390 {
391 m = _match_old.Match(s);
392 if (m.Success)
393 {
394 int version = int.Parse(m.Groups[1].ToString());
395 if ((version > after) && !migrations.ContainsKey(version))
396 {
397 using (Stream resource = _assem.GetManifestResourceStream(s))
398 {
399 using (StreamReader resourceReader = new StreamReader(resource))
400 {
401 string sql = resourceReader.ReadToEnd();
402 migrations.Add(version, new string[]{sql});
403 }
404 }
405 }
406 }
407 }
408  
409 if (migrations.Count < 1)
410 m_log.DebugFormat("[MIGRATIONS]: {0} data tables already up to date at revision {1}", _type, after);
411  
412 return migrations;
413 }
414 }
415 }