private int _GetDataBaseSongID(string artist, string title, int defNumPlayed, SQLiteCommand command)
{
command.CommandText = "SELECT id FROM Songs WHERE [Title] = @title AND [Artist] = @artist";
command.Parameters.Add("@title", DbType.String, 0).Value = title;
command.Parameters.Add("@artist", DbType.String, 0).Value = artist;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
return(id);
}
if (reader != null)
{
reader.Close();
}
command.CommandText = "INSERT INTO Songs (Title, Artist, NumPlayed, DateAdded) " +
"VALUES (@title, @artist, @numplayed, @dateadded)";
command.Parameters.Add("@title", DbType.String, 0).Value = title;
command.Parameters.Add("@artist", DbType.String, 0).Value = artist;
command.Parameters.Add("@numplayed", DbType.Int32, 0).Value = defNumPlayed;
command.Parameters.Add("@dateadded", DbType.Int64, 0).Value = DateTime.Now.Ticks;
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Songs WHERE [Title] = @title AND [Artist] = @artist";
command.Parameters.Add("@title", DbType.String, 0).Value = title;
command.Parameters.Add("@artist", DbType.String, 0).Value = artist;
reader = command.ExecuteReader();
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
return(id);
}
return(-1);
}
internal HyenaSqliteArrayDataReader (SqliteDataReader reader)
{
rows = new List<object[]> ();
column_names = new Dictionary<string, int> ();
closed = false;
current_row = -1;
ReadAllRows (reader);
reader.Dispose ();
}
public virtual bool Init()
{
lock (_Mutex)
{
if (_Connection != null)
{
return(false);
}
_Connection = new SQLiteConnection("Data Source=" + _FilePath);
try
{
_Connection.Open();
}
catch (Exception)
{
_Connection.Dispose();
_Connection = null;
return(false);
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "SELECT Value FROM Version";
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception) {}
if (reader == null || !reader.Read() || reader.FieldCount == 0)
{
_Version = -1;
}
else
{
_Version = reader.GetInt32(0);
}
if (reader != null)
{
reader.Dispose();
}
}
}
return(true);
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
}
if (sqliteDataReader != null)
{
sqliteDataReader.Dispose();
}
disposedValue = true;
}
}
public bool GetCreditsRessource(string fileName, ref CTextureRef tex)
{
if (_Connection == null)
{
return(false);
}
bool result = false;
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "SELECT id, width, height FROM Images WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String, 0).Value = fileName;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
int w = reader.GetInt32(1);
int h = reader.GetInt32(2);
reader.Close();
command.CommandText = "SELECT Data FROM ImageData WHERE ImageID = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
reader = command.ExecuteReader();
if (reader.HasRows)
{
result = true;
reader.Read();
byte[] data = _GetBytes(reader);
tex = CDraw.AddTexture(w, h, data);
}
}
if (reader != null)
{
reader.Dispose();
}
}
return(result);
}
/// <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);
}
/// <summary>
/// Creates a new Vocaluxe Database if no file exists. Converts an existing old Ultrastar Deluxe highscore database into vocaluxe format.
/// </summary>
/// <param name="filePath">Database file path</param>
/// <returns></returns>
private bool _CreateOrConvert(string filePath)
{
bool result = true;
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 user_version";
SQLiteDataReader reader = command.ExecuteReader();
reader.Read();
int version = reader.GetInt32(0);
reader.Dispose();
//Check if old scores table exists
command.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='US_Scores';";
reader = command.ExecuteReader();
reader.Read();
bool scoresTableExists = reader.HasRows;
reader.Dispose();
command.CommandText = "SELECT Value FROM Version";
reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception) {}
if (reader == null || reader.FieldCount == 0)
{
// create new database/tables
if (version == 1)
{
//Check for USDX 1.1 DB
_CreateHighscoreDBV1(filePath);
result &= _ConvertFrom110(filePath);
result &= _UpdateDatabase(1, connection);
result &= _UpdateDatabase(2, connection);
}
else if (version == 0 && scoresTableExists)
{
//Check for USDX 1.01 or CMD Mod DB
_CreateHighscoreDBV1(filePath);
result &= _ConvertFrom101(filePath);
result &= _UpdateDatabase(1, connection);
result &= _UpdateDatabase(2, connection);
}
else
{
_CreateHighscoreDB(filePath);
}
}
else
{
reader.Read();
int currentVersion = reader.GetInt32(0);
if (currentVersion < CSettings.DatabaseHighscoreVersion)
{
// update database
result &= _UpdateDatabase(currentVersion, connection);
}
}
if (reader != null)
{
reader.Dispose();
}
}
}
return(result);
}
public List <SDBScoreEntry> LoadScore(int songID, EGameMode gameMode, EHighscoreStyle style)
{
var scores = new List <SDBScoreEntry>();
using (var connection = new SQLiteConnection())
{
connection.ConnectionString = "Data Source=" + _FilePath;
try
{
connection.Open();
}
catch (Exception)
{
return(scores);
}
using (var command = new SQLiteCommand(connection))
{
int medley = 0;
int duet = 0;
int shortSong = 0;
switch (gameMode)
{
case EGameMode.TR_GAMEMODE_MEDLEY:
medley = 1;
break;
case EGameMode.TR_GAMEMODE_DUET:
duet = 1;
break;
case EGameMode.TR_GAMEMODE_SHORTSONG:
shortSong = 1;
break;
}
int dataBaseSongID = _GetDataBaseSongID(songID, command);
if (dataBaseSongID < 0)
{
return(scores);
}
switch (style)
{
case EHighscoreStyle.TR_CONFIG_HIGHSCORE_LIST_BEST:
command.CommandText = "SELECT os.PlayerName, os.Score, os.Date, os.Difficulty, os.LineNr, os.id " +
"FROM Scores os " +
"INNER JOIN ( " +
"SELECT sc.PlayerName, sc.Score, sc.Difficulty, sc.LineNr, MIN(sc.Date) AS Date " +
"FROM Scores sc " +
"INNER JOIN ( " +
"SELECT Playername, MAX(Score) AS Score, Difficulty, LineNr " +
"FROM Scores " +
"WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " +
"GROUP BY PlayerName, Difficulty, LineNr " +
") AS mc " +
"ON sc.PlayerName = mc.PlayerName AND sc.Difficulty = mc.Difficulty AND sc.LineNr = mc.LineNr AND sc.Score = mc.Score " +
"WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " +
"GROUP BY sc.PlayerName, sc.Difficulty, sc.LineNr, sc.Score " +
") AS iq " +
"ON os.PlayerName = iq.PlayerName AND os.Difficulty = iq.Difficulty AND os.LineNr = iq.LineNr AND os.Score = iq.Score AND os.Date = iq.Date " +
"WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " +
"GROUP BY os.PlayerName, os.Difficulty, os.LineNr, os.Score " +
"ORDER BY os.Score DESC, os.Date ASC";
break;
case EHighscoreStyle.TR_CONFIG_HIGHSCORE_LIST_ALL:
command.CommandText = "SELECT PlayerName, Score, Date, Difficulty, LineNr, id " +
"FROM Scores " +
"WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " +
"ORDER BY [Score] DESC, [Date] ASC";
break;
}
command.Parameters.Add("@SongID", DbType.Int32, 0).Value = dataBaseSongID;
command.Parameters.Add("@Medley", DbType.Int32, 0).Value = medley;
command.Parameters.Add("@Duet", DbType.Int32, 0).Value = duet;
command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = shortSong;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
var score = new SDBScoreEntry
{
Name = reader.GetString(0),
Score = reader.GetInt32(1),
Date = new DateTime(reader.GetInt64(2)).ToString("dd/MM/yyyy"),
Difficulty = (EGameDifficulty)reader.GetInt32(3),
VoiceNr = reader.GetInt32(4),
ID = reader.GetInt32(5)
};
scores.Add(score);
}
reader.Dispose();
}
}
}
return(scores);
}
private int _AddScore(string playerName, int score, int lineNr, long date, int medley, int duet, int shortSong, int difficulty,
int dataBaseSongID, SQLiteCommand command)
{
int lastInsertID = -1;
if (dataBaseSongID >= 0)
{
command.CommandText = "SELECT id FROM Scores WHERE SongID = @SongID AND PlayerName = @PlayerName AND Score = @Score AND " +
"LineNr = @LineNr AND Date = @Date AND Medley = @Medley AND Duet = @Duet AND ShortSong = @ShortSong AND Difficulty = @Difficulty";
command.Parameters.Add("@SongID", DbType.Int32, 0).Value = dataBaseSongID;
command.Parameters.Add("@PlayerName", DbType.String, 0).Value = playerName;
command.Parameters.Add("@Score", DbType.Int32, 0).Value = score;
command.Parameters.Add("@LineNr", DbType.Int32, 0).Value = lineNr;
command.Parameters.Add("@Date", DbType.Int64, 0).Value = date;
command.Parameters.Add("@Medley", DbType.Int32, 0).Value = medley;
command.Parameters.Add("@Duet", DbType.Int32, 0).Value = duet;
command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = shortSong;
command.Parameters.Add("@Difficulty", DbType.Int32, 0).Value = difficulty;
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception) {}
if (reader != null && reader.HasRows)
{
if (reader.Read())
{
return(reader.GetInt32(0));
}
}
if (reader != null)
{
reader.Dispose();
}
command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, ShortSong, Difficulty) " +
"VALUES (@SongID, @PlayerName, @Score, @LineNr, @Date, @Medley, @Duet, @ShortSong, @Difficulty)";
command.Parameters.Add("@SongID", DbType.Int32, 0).Value = dataBaseSongID;
command.Parameters.Add("@PlayerName", DbType.String, 0).Value = playerName;
command.Parameters.Add("@Score", DbType.Int32, 0).Value = score;
command.Parameters.Add("@LineNr", DbType.Int32, 0).Value = lineNr;
command.Parameters.Add("@Date", DbType.Int64, 0).Value = date;
command.Parameters.Add("@Medley", DbType.Int32, 0).Value = medley;
command.Parameters.Add("@Duet", DbType.Int32, 0).Value = duet;
command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = shortSong;
command.Parameters.Add("@Difficulty", DbType.Int32, 0).Value = difficulty;
command.ExecuteNonQuery();
//Read last insert line
command.CommandText = "SELECT id FROM Scores ORDER BY id DESC LIMIT 0, 1";
reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
lastInsertID = reader.GetInt32(0);
}
reader.Dispose();
}
}
return(lastInsertID);
}
public bool GetCover(string coverPath, ref CTextureRef tex, int maxSize)
{
if (_Connection == null)
{
return(false);
}
if (!File.Exists(coverPath))
{
CLog.LogError("Can't find File: " + coverPath);
return(false);
}
lock (_Mutex)
{
//Double check here because we may have just closed our connection
if (_Connection == null)
{
return(false);
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "SELECT id, width, height FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String).Value = coverPath;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null && reader.HasRows)
{
reader.Read();
int id = reader.GetInt32(0);
int w = reader.GetInt32(1);
int h = reader.GetInt32(2);
reader.Close();
command.CommandText = "SELECT Data FROM CoverData WHERE CoverID = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
byte[] data2 = _GetBytes(reader);
reader.Dispose();
tex = CDraw.EnqueueTexture(w, h, data2);
return(true);
}
command.CommandText = "DELETE FROM Cover WHERE id = @id";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.ExecuteNonQuery();
}
if (reader != null)
{
reader.Close();
}
}
}
// At this point we do not have a mathing entry in the CoverDB (either no Data found and deleted or nothing at all)
// We break out of the lock to do the bitmap loading and resizing here to allow multithreaded loading
Bitmap origin = CHelper.LoadBitmap(coverPath);
if (origin == null)
{
return(false);
}
Size size = origin.GetSize();
if (size.Width > maxSize || size.Height > maxSize)
{
size = CHelper.FitInBounds(new SRectF(0, 0, maxSize, maxSize, 0), (float)size.Width / size.Height, EAspect.LetterBox).SizeI;
Bitmap tmp = origin.Resize(size);
origin.Dispose();
origin = tmp;
}
byte[] data;
try
{
data = new byte[size.Width * size.Height * 4];
BitmapData bmpData = origin.LockBits(origin.GetRect(), ImageLockMode.ReadOnly, PixelFormat.Format32bppArgb);
Marshal.Copy(bmpData.Scan0, data, 0, data.Length);
origin.UnlockBits(bmpData);
}
finally
{
origin.Dispose();
}
tex = CDraw.EnqueueTexture(size.Width, size.Height, data);
lock (_Mutex)
{
//Double check here because we may have just closed our connection
if (_Connection == null)
{
return(false);
}
if (_TransactionCover == null)
{
_TransactionCover = _Connection.BeginTransaction();
}
using (var command = new SQLiteCommand(_Connection))
{
command.CommandText = "INSERT INTO Cover (Path, width, height) VALUES (@path, @w, @h)";
command.Parameters.Add("@w", DbType.Int32).Value = size.Width;
command.Parameters.Add("@h", DbType.Int32).Value = size.Height;
command.Parameters.Add("@path", DbType.String).Value = coverPath;
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Cover WHERE [Path] = @path";
command.Parameters.Add("@path", DbType.String).Value = coverPath;
SQLiteDataReader reader = command.ExecuteReader();
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Dispose();
command.CommandText = "INSERT INTO CoverData (CoverID, Data) VALUES (@id, @data)";
command.Parameters.Add("@id", DbType.Int32).Value = id;
command.Parameters.Add("@data", DbType.Binary).Value = data;
command.ExecuteNonQuery();
return(true);
}
}
}
return(false);
}
static public ItemData GetItemData(int id)
{
string query = "SELECT * FROM ITEM where id = " + id;
reader = ExecuteQuery(query);
ItemData item = new ItemData();
if (reader.Read())
{
item.id = reader.GetInt32(0);
item.name = reader.GetString(1);
item.addHp = reader.GetFloat(2);
item.addAttack = reader.GetFloat(3);
item.addDefence = reader.GetFloat(4);
item.addGold = reader.GetInt32(5);
}
reader.Dispose();
return item;
}
static public MonsterData GetMonsterData(int id)
{
string query = "SELECT * FROM MONSTER where id = " + id;
reader = ExecuteQuery(query);
MonsterData monster = new MonsterData();
if (reader.Read())
{
monster.id = reader.GetInt32(0);
monster.name = reader.GetString(1);
monster.hp = reader.GetFloat(2);
monster.attack = reader.GetFloat(3);
monster.defence = reader.GetFloat(4);
monster.gold = reader.GetInt32(5);
}
reader.Dispose();
return monster;
}
public static bool LoadFromDB(ref List <List <List <object> > > results,
string completeQuery, string dataSource)
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = dataSource;
try { con.Open(); }
catch (Exception ex)
{
MakeLogErrorStatic(typeof(DBReader), ex);
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
return(false);
}
// security check and close connection if necessary
if (!DBSecurity.IsSecureSQLCommand(completeQuery))
{
MakeLogWarningStatic(typeof(DBReader),
"LoadFromDB: Prevented forwarding of insecure sql-command: "
+ completeQuery);
return(false);
}
using (SQLiteCommand cmd = new SQLiteCommand(completeQuery, con))
{
SQLiteDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (rdr == null)
{
return(false);
}
if (!rdr.HasRows)
{
return(true);
}
// temporary array to put all data of a row into
object[] rowArr = null;
do
{
// add new result-list
results.Add(new List <List <object> >());
while (rdr.Read())
{
// create and fill array of the temporary data row
rowArr = new object[rdr.FieldCount];
rdr.GetValues(rowArr);
results[results.Count - 1].Add(new List <object>(rowArr));
}
}while (rdr.NextResult());
}
catch (Exception ex)
{
throw new Exception("LoadFromDB: Could not execute SQLiteDataReader: " + ex);
}
finally
{
if (rdr != null)
{
rdr.Close();
rdr.Dispose();
}
}
}
// close connection if still opened
if (con.State.ToString() == "Open")
{
con.Close();
con.Dispose();
}
// everything went through without errors thrown
return(true);
}
public DataSet ConvertDataReaderToDataSet(SqliteDataReader reader, string[] sColNames)
{
DataSet dataSet = new DataSet();
try
{
// DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
for (int i = 0; i < sColNames.Length; i++)
{
string columnName = sColNames[i];
DataColumn column = new DataColumn(columnName);
dataTable.Columns.Add(column);
}
//for (int i = 0; i <= schemaTable.Rows.Count - 1; i++)
//{
// DataRow dataRow = schemaTable.Rows[i];
// string columnName = dataRow["ColumnName"].ToString();DataColumn column = new DataColumn(columnName, dataRow["DataType"].GetType());
// dataTable.Columns.Add(column);
//}
dataSet.Tables.Add(dataTable);
int j = 0;
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < sColNames.Length; i++)
{
dataRow[i] = reader.GetValue(i);
}
j++;
dataTable.Rows.Add(dataRow);
}
reader.Dispose();
}
catch (Exception ex)
{
string sMsg = ex.Message.ToString();
}
return dataSet;
}
