public void DeleteAllData()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
this.executeNonqueryCommand("DELETE FROM [Athlete]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Result]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Score]", conn, trans);
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
new DatabaseSetup().CreateEmptyAthleteRecord();
}
public void UpdateAllScores(List <Score> scores)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Score]";
command.ExecuteNonQuery();
command.Dispose();
foreach (var score in scores)
{
this.addScore(conn, trans, score);
}
trans.Commit();
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// You have to hold the CoverMutex when calling this!
/// </summary>
private void _CommitCovers()
{
if (_TransactionCover == null)
{
return;
}
_TransactionCover.Commit();
_TransactionCover.Dispose();
_TransactionCover = null;
}
public void Commit(Enlistment enlistment)
{
SqliteConnection cnn = _transaction.Connection;
cnn._enlistment = null;
try
{
_transaction.IsValid(true);
_transaction.Connection._transactionLevel = 1;
_transaction.Commit();
enlistment.Done();
}
finally
{
Cleanup(cnn);
}
}
/// <summary>
/// Executing SQL statements
/// </summary>
/// <param name="DB">Database</param>
/// <param name="SQLs">SQL statement</param>
/// <returns>Returns the number of rows affected</returns>
public static int Command(string DB, params string[] SQLs)
{
int result = 0;
if (File.Exists(DB) && SQLs != null)
{
using (SQLiteConnection con = new SQLiteConnection(@"Data Source=" + DB))
{
con.Open();
using (SQLiteTransaction trans = con.BeginTransaction())
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
foreach (string SQLstr in SQLs)
{
cmd.CommandText = SQLstr;
result += cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
trans.Rollback(); //There was an error, roll back
result = -1;
}
finally
{
try{
trans.Commit();
}catch { }
}
}
con.Close();
}
}
return(result);
}
//Update from V0 (empty) to V1
private bool _UpdateV1()
{
if (_Connection == null || _Version >= 1)
{
return(false);
}
using (SQLiteTransaction transaction = _Connection.BeginTransaction())
{
try
{
foreach (string file in _FilesV1)
{
string filePath = Path.Combine(CSettings.ProgramFolder, file);
if (!_AddImageToCreditsDB(filePath, transaction))
{
transaction.Rollback();
return(false);
}
}
using (SQLiteCommand command = new SQLiteCommand(_Connection))
{
command.Transaction = transaction;
command.CommandText = "Update Version SET Value=@Value)";
command.Parameters.Add("@Value", DbType.Int32).Value = 1;
command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
return(false);
}
}
return(true);
}
/// <summary>
/// Converts a USDX 1.01 or CMD 1.01 database to Vocaluxe format
/// </summary>
/// <param name="filePath">Database file path</param>
/// <returns>True if succeeded</returns>
private bool _ConvertFrom101(string filePath)
{
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return(false);
}
using (var command = new SQLiteCommand(connection))
{
command.CommandText = "PRAGMA table_info(US_Scores);";
bool dateExists = false;
using (SQLiteDataReader reader = command.ExecuteReader())
{
//Check for column Date
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i) == "name")
{
if (reader.GetString(i) == "Date")
{
dateExists = true;
}
break;
}
}
}
}
//This is a USDX 1.01 DB
command.CommandText = !dateExists
? "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores"
: "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
command.ExecuteNonQuery();
// convert from CP1252 to UTF8
var scores = new List <SData>();
var songs = new List <SData>();
Sqlite3.sqlite3 oldDB;
int res = Sqlite3.sqlite3_open(filePath, out oldDB);
if (res != Sqlite3.SQLITE_OK)
{
CLog.Error("Error opening Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")");
}
else
{
var stmt = new Sqlite3.Vdbe();
res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, Artist, Title FROM Songs", -1, ref stmt, 0);
if (res != Sqlite3.SQLITE_OK)
{
CLog.Error("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")");
}
else
{
//Sqlite3.sqlite3_step(Stmt);
Encoding utf8 = Encoding.UTF8;
Encoding cp1252 = Encoding.GetEncoding(1252);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
var data = new SData {
Id = Sqlite3.sqlite3_column_int(stmt, 0)
};
byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1);
data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone";
bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 2);
data.Str2 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone";
songs.Add(data);
}
Sqlite3.sqlite3_finalize(stmt);
}
stmt = new Sqlite3.Vdbe();
// ReSharper disable ConvertIfStatementToConditionalTernaryExpression
if (!dateExists)
{
// ReSharper restore ConvertIfStatementToConditionalTernaryExpression
res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName FROM Scores", -1, ref stmt, 0);
}
else
{
res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName, Date FROM Scores", -1, ref stmt, 0);
}
if (res != Sqlite3.SQLITE_OK)
{
CLog.Error("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")");
}
else
{
//Sqlite3.sqlite3_step(Stmt);
Encoding utf8 = Encoding.UTF8;
Encoding cp1252 = Encoding.GetEncoding(1252);
while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW)
{
var data = new SData {
Id = Sqlite3.sqlite3_column_int(stmt, 0)
};
byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1);
data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone";
if (dateExists)
{
data.Ticks = _UnixTimeToTicks(Sqlite3.sqlite3_column_int(stmt, 2));
}
scores.Add(data);
}
Sqlite3.sqlite3_finalize(stmt);
}
}
Sqlite3.sqlite3_close(oldDB);
SQLiteTransaction transaction = connection.BeginTransaction();
// update Title and Artist strings
foreach (SData data in songs)
{
command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2;
command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
// update player names
foreach (SData data in scores)
{
if (!dateExists)
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player WHERE [id] = @id";
}
else
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks;
}
command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
transaction.Commit();
//Delete old tables after conversion
command.CommandText = "DROP TABLE US_Scores;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE US_Songs;";
command.ExecuteNonQuery();
}
}
return(true);
}
/// <summary>
/// Converts a USDX 1.1 database into the Vocaluxe format
/// </summary>
/// <param name="filePath">Database file path</param>
/// <returns>True if succeeded</returns>
private bool _ConvertFrom110(string filePath)
{
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + filePath;
try
{
connection.Open();
}
catch (Exception)
{
return(false);
}
using (var command = new SQLiteCommand(connection))
{
//The USDX database has no column for LineNr, Medley and Duet so just fill 0 in there
command.CommandText =
"INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs";
command.ExecuteNonQuery();
var scores = new List <SData>();
var songs = new List <SData>();
command.CommandText = "SELECT id, PlayerName, Date FROM Scores";
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var data = new SData {
Id = reader.GetInt32(0), Str1 = reader.GetString(1)
};
Int64 ticks = 0;
try
{
ticks = reader.GetInt64(2);
}
catch {}
data.Ticks = _UnixTimeToTicks((int)ticks);
scores.Add(data);
}
reader.Close();
}
command.CommandText = "SELECT id, Artist, Title FROM Songs";
reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var data = new SData {
Id = reader.GetInt32(0), Str1 = reader.GetString(1), Str2 = reader.GetString(2)
};
songs.Add(data);
}
}
if (reader != null)
{
reader.Dispose();
}
SQLiteTransaction transaction = connection.BeginTransaction();
// update Title and Artist strings
foreach (SData data in songs)
{
command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id";
command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2;
command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
// update player names
foreach (SData data in scores)
{
command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id";
command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1;
command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks;
command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id;
command.ExecuteNonQuery();
}
transaction.Commit();
//Delete old tables after conversion
command.CommandText = "DROP TABLE IF EXISTS us_scores;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_songs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_statistics_info;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_users_info;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_webs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE IF EXISTS us_webs_stats;";
command.ExecuteNonQuery();
//This versioning is not used in Vocaluxe so reset it to 0
command.CommandText = "PRAGMA user_version = 0";
command.ExecuteNonQuery();
}
}
return(true);
}
public void ProcessData(IEnumerable<IEntity> data, ProcessMode mode, SqliteTransaction tran = null)
{
bool inTran = tran != null;
var toRemoveFromCache = new List<Guid>();
try
{
foreach (IEnumerable<IEntity> lst in GetBlock(data.GetEnumerator()))
{
if (!inTran)
tran = ActiveConnection.BeginTransaction();
ProcessAllInternal(lst, mode, tran, mode == ProcessMode.LocalChanges);
if (!inTran)
tran.Commit();
if (mode == ProcessMode.ServerChanges)
{
foreach (ISqliteEntity e in lst)
toRemoveFromCache.Add(e.EntityId);
}
GC.Collect();
}
Cache.Clear(toRemoveFromCache);
}
catch
{
if (tran != null && !inTran)
tran.Rollback();
throw;
}
}
public void SaveSecurity(int myAthleteID, bool userWantsToBeGuest, DateTime timeAthleteCreated)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
int myOldAthleteID = this.getMyAthleteID(conn);
bool myOldAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myOldAthleteID, conn, null).ToString()) == 1;
bool myNewAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myAthleteID, conn, null).ToString()) == 1;
trans = conn.BeginTransaction();
// update Singular row
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Singular SET MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.CommandText = "UPDATE Singular SET AccessToken=@AccessToken, MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.Parameters.Add(new SqliteParameter() { ParameterName = "@AccessToken", Value = Crypto.Encrypt(accessToken, "$EFK#$RF!#$#SDFwefasdWE@") });
command.ExecuteNonQuery();
command.Dispose();
if (myAthleteID != myOldAthleteID)
{
// create Athlete row
if (myNewAthleteRecordAlreadyExists == false)
{
createAhlete(myAthleteID, timeAthleteCreated, conn, trans);
}
// move results and scores
command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Result SET AthleteID=" + myAthleteID + " WHERE AthleteID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteAID=" + myAthleteID + " WHERE AthleteAID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteBID=" + myAthleteID + " WHERE AthleteBID=" + myOldAthleteID;
command.ExecuteNonQuery();
if (myOldAthleteRecordAlreadyExists == true)
{
this.executeNonqueryCommand("DELETE FROM [Athlete] WHERE AthleteID=" + myOldAthleteID, conn, trans);
}
}
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
