1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
#region Disclaimer / License
// Copyright (C) 2015, The Duplicati Team
// http://www.duplicati.com, info@duplicati.com
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
//
#endregion
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace Duplicati.Library.SQLiteHelper
{
/// <summary>
/// This class will read embedded files from the given folder.
/// Updates should have the form "1.Sample upgrade.sql".
/// When the database schema changes, simply put a new file into the folder.
/// Each upgrade file should ONLY upgrade from the previous version.
/// If done correctly, a user may be upgrade from the very first version
/// to the very latest.
///
/// The Schema.sql file should ALWAYS have the latests schema, as that will
/// ensure that new installs do not run upgrades after installation.
/// Also remember to update the last line in Schema.sql to insert the
/// current version number in the version table.
///
/// Currently no upgrades may contain semicolons, except as the SQL statement
/// delimiter.
/// </summary>
public static class DatabaseUpgrader
{
//This is the "folder" where the embedded resources can be found
private const string FOLDER_NAME = "Database_schema";
//This is the name of the schema sql
private const string SCHEMA_NAME = "Schema.sql";
public static void UpgradeDatabase(IDbConnection connection, string sourcefile, Type eltype)
{
var asm = eltype.Assembly;
string schema;
using (var rd = new System.IO.StreamReader(asm.GetManifestResourceStream(eltype, FOLDER_NAME + "." + SCHEMA_NAME)))
schema = rd.ReadToEnd();
//Get updates, and sort them according to version
//This enables upgrading through several versions
//ea, from 1 to 8, by stepping 2->3->4->5->6->7->8
SortedDictionary<int, string> upgrades = new SortedDictionary<int, string>();
string prefix = eltype.Namespace + "." + FOLDER_NAME + ".";
foreach (string s in asm.GetManifestResourceNames())
{
//The resource name will be "Duplicati.GUI.Database_schema.1.Sample upgrade.sql"
//The number indicates the version that will be upgraded to
if (s.StartsWith(prefix) && !s.Equals(prefix + SCHEMA_NAME))
{
try
{
string version = s.Substring(prefix.Length, s.IndexOf(".", prefix.Length + 1) - prefix.Length);
int fileversion = int.Parse(version);
string prev;
if (!upgrades.TryGetValue(fileversion, out prev))
prev = "";
upgrades[fileversion] = prev + new System.IO.StreamReader(asm.GetManifestResourceStream(s)).ReadToEnd();
}
catch
{
}
}
}
UpgradeDatabase(connection, sourcefile, schema, new List<string>(upgrades.Values));
}
/// <summary>
/// Ensures that the database is up to date
/// </summary>
/// <param name="connection">The database connection to use</param>
/// <param name="sourcefile">The file the database is placed in</param>
public static void UpgradeDatabase(IDbConnection connection, string sourcefile, string schema, IList<string> versions)
{
if (connection.State != ConnectionState.Open)
{
if (string.IsNullOrEmpty(connection.ConnectionString))
connection.ConnectionString = "Data Source=" + sourcefile;
connection.Open();
}
int dbversion = 0;
IDbCommand cmd = connection.CreateCommand();
try
{
//See if the version table is present,
cmd.CommandText = "SELECT COUNT(*) FROM SQLITE_MASTER WHERE Name LIKE 'Version'";
int count = Convert.ToInt32(cmd.ExecuteScalar());
if (count == 0)
dbversion = -1; //Empty
else if (count == 1)
{
cmd.CommandText = "SELECT max(Version) FROM Version";
dbversion = Convert.ToInt32(cmd.ExecuteScalar());
}
else
throw new Exception(Strings.DatabaseUpgrader.TableLayoutError);
}
catch(Exception ex)
{
//Hopefully a more explanatory error message
throw new Exception(string.Format(Strings.DatabaseUpgrader.DatabaseFormatError, ex.Message), ex);
}
//On a new database, we just load the most current schema, and upgrade from there
//This avoids potentitally lenghty upgrades
if (dbversion == -1)
{
cmd.CommandText = schema;
cmd.ExecuteNonQuery();
UpgradeDatabase(connection, sourcefile, schema, versions);
return;
}
if (dbversion > versions.Count)
throw new Exception(string.Format(Strings.DatabaseUpgrader.InvalidVersionError, dbversion, versions.Count, System.IO.Path.GetDirectoryName(sourcefile)));
if (versions.Count > dbversion)
{
string backupfile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(sourcefile), Strings.DatabaseUpgrader.BackupFilenamePrefix + " " + DateTime.Now.ToString("yyyyMMddhhmmss") + ".sqlite");
try
{
if (!System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(backupfile)))
System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(backupfile));
//Keep a backup
System.IO.File.Copy(sourcefile, backupfile, false);
for (int i = dbversion; i < versions.Count; i++)
{
//TODO: Find a better way to split SQL statements, as there may be embedded semicolons
//in the SQL, like "UPDATE x WHERE y = ';';"
//We split them to get a better error message
foreach (string c in versions[i].Split(';'))
if (c.Trim().Length > 0)
{
cmd.CommandText = c;
cmd.ExecuteNonQuery();
}
}
//Update databaseversion, so we don't run the scripts again
cmd.CommandText = "Update version SET Version = " + versions.Count.ToString();
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
connection.Close();
//Restore the database
System.IO.File.Copy(backupfile, sourcefile, true);
throw new Exception(string.Format(Strings.DatabaseUpgrader.UpgradeFailure, cmd.CommandText, ex.Message), ex);
}
}
}
}
}
|
