private UpgradeSQL loadUpgradeSQL()
{
UpgradeSQL result = null;
string fileName = "Upgrade.json";
var streamInfo = readAppFile(fileName);
if (streamInfo == null)
{
streamInfo = readAppResource(fileName);
}
if (streamInfo != null)
{
StreamReader sr = new StreamReader(streamInfo.Stream);
try
{
result = UpgradeSQL.loadJson(sr.ReadToEnd());
}
catch (JsonException e)
{
Trace.TraceError(e.Message);
Trace.TraceError(e.StackTrace);
Debug.WriteLine(e);
}
}
return(result);
}
public static void DoUpgradeDb()
{
var dbVerItem = SettingItemUtils.GetDbVersion();
int v = Convert.ToInt32(dbVerItem.Value);
Trace.TraceInformation("current database version {0}", v);
UpgradeSQL upgradeSQL = GlobalSetting.Instance.GetUpgradeSQL();
int newerVersion = upgradeSQL.SQLS.Count;
Trace.TraceInformation("newer database version {0}", newerVersion);
if (newerVersion > v)
{
Trace.TraceInformation("upgrade database version from {0} to {1}", v, newerVersion);
using (var dbConn = getConnection())
{
dbConn.Open();
using (var transaction = dbConn.BeginTransaction())
{
for (int i = v; i < newerVersion; i++)
{
List <string> sqls = upgradeSQL.SQLS[i];
foreach (string sql in sqls)
{
Trace.TraceInformation("run sql {0}", sql);
var cmd = new SqliteCommand(sql, dbConn, transaction);
cmd.ExecuteNonQuery();
}
}
Trace.TraceInformation("write current database version to db");
var verSQL = string.Format("INSERT OR REPLACE INTO SETTING(KEY, VALUE) VALUES ('db.version', '{0}');", newerVersion);
var verCmd = new SqliteCommand(verSQL, dbConn, transaction);
verCmd.ExecuteNonQuery();
transaction.Commit();
}
}
}
else
{
Trace.TraceInformation("database version is already the latest");
}
}