internal void Sync(long rowid)
{
IsValid = false;
_command.Parameters[0].Value = rowid;
_reader = _command.ExecuteReader();
_reader.Read();
}
public void CloseSqlConnection()
{
if (dbCommand != null) {
dbCommand.Dispose ();
}
dbCommand = null;
if (reader != null) {
reader.Dispose ();
}
reader = null;
if (dbConnection != null) {
dbConnection.Close ();
}
dbConnection = null;
Debug.Log ("Disconnected from db.");
}
static int get_Item(IntPtr L)
{
try
{
int count = LuaDLL.lua_gettop(L);
if (count == 2 && TypeChecker.CheckTypes(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader), typeof(int)))
{
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.ToObject(L, 1);
int arg0 = (int)LuaDLL.lua_tonumber(L, 2);
object o = obj[arg0];
ToLua.Push(L, o);
return(1);
}
else if (count == 2 && TypeChecker.CheckTypes(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader), typeof(string)))
{
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.ToObject(L, 1);
string arg0 = ToLua.ToString(L, 2);
object o = obj[arg0];
ToLua.Push(L, o);
return(1);
}
else
{
return(LuaDLL.luaL_throw(L, "invalid arguments to method: Mono.Data.Sqlite.SqliteDataReader.get_Item"));
}
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static public SqliteDataReader ExecuteQuery(string sqlQuery)
{
dbCommand = OpenDB("monsters.db").CreateCommand();
dbCommand.CommandText = sqlQuery;
reader = dbCommand.ExecuteReader();
return reader;
}
public void InsertMonsters()
{
string[] nombres = SaveMonster.GetMonsterList();
Monstruo temp;
if (PlayerPrefs.GetString ("botonPresionado") == "new") {
DeleteMonsters();
}
for(int i = 0;i < nombres.Length;++i){
temp = SaveMonster.LoadMonster(nombres[i]);
_query="SELECT * FROM tablaMonstruos WHERE owner='PEPE' and name='"+temp.nombre+"'";
_command = _conexion.CreateCommand ();
_command.CommandText = _query;
_reader = _command.ExecuteReader ();
int cont=0;
if(_reader != null){
while(_reader.Read()){
cont++;
}
if(cont!=0){
_query= "UPDATE tablaMonstruos set specie='"+temp.especie+"',exp='"+temp.exp.ToString()+"',modStats='"+temp.modStats.ToString()+"',estado='"+temp.estado.ToString()+"' WHERE owner='PEPE' and name='"+temp.nombre+"'";
}else{
_query = "INSERT INTO tablaMonstruos VALUES('"+temp.nombre+"','"+temp.especie+"','"+temp.exp.ToString()+"','"+temp.modStats.ToString()+"','"+temp.estado.ToString()+"','PEPE')";
}
}
_command = _conexion.CreateCommand();
_command.CommandText = _query;
_command.ExecuteReader();
}
}
/// <summary>Convert from DataReader to Task object</summary>
TodoItem FromReader (SqliteDataReader r) {
var t = new TodoItem();
t.ID = Convert.ToInt32 (r ["_id"]);
t.Name = r ["Name"].ToString ();
t.Notes = r ["Notes"].ToString ();
t.Done = Convert.ToInt32 (r ["Done"]) == 1 ? true : false;
return t;
}
MatchingTable CreateMatchingTable(SqliteDataReader reader)
{
return new MatchingTable(
reader["id"].ToString(),
reader["host"].ToString(),
reader["guest"].ToString()
);
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
Motion = System.Convert.ToString(sdr ["Motion"]);
MotionTransition= System.Convert.ToSingle(sdr ["MotionTransition"]);
MotionSpeed = System.Convert.ToSingle(sdr ["MotionSpeed"]);
}
public SqliteDataReader ExecuteQuery(string q)
{
databaseCommand = databaseConnection.CreateCommand();
databaseCommand.CommandText = q;
reader = databaseCommand.ExecuteReader();
return reader;
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
ShapeID = System.Convert.ToInt32(sdr ["ShapeID"]);
Weapon = System.Convert.ToString(sdr ["Weapon"]);
Describe = System.Convert.ToString(sdr ["Describe"]);
}
public void CloseDB()
{
_reader.Close ();
_reader = null;
_command = null;
_conexion.Close ();
_conexion = null;
}
/// <summary>
/// DBFill
/// </summary>
/// <param name="reader"></param>
/// <param name="slide"></param>
public static void DBFill(SqliteDataReader reader, Slide slide)
{
slide.SlideID = Convert.ToInt32(reader["slide_id"]);
slide.SlideText = HttpUtility.UrlDecode(reader["slide_text"].ToString(), System.Text.Encoding.Default);
slide.SlideIndex = Convert.ToInt32(reader["slide_index"]);
slide.PhotoID = Convert.ToInt32(reader["photo_id"]);
slide.Photo = Photo.Get(slide.PhotoID);
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
ShapeID = System.Convert.ToInt32(sdr ["Shape"]);
Level = System.Convert.ToInt32(sdr ["Level"]);
Blueprint = System.Convert.ToString(sdr ["AI"]);
}
public BlockData(SqliteDataReader reader)
{
ID = reader.GetInt32(0);
File = reader.GetString(1);
Width = reader.GetInt32(2);
Height = reader.GetInt32(3);
AvailableWidth = reader.GetInt32(4);
AvailableHeight = reader.GetInt32(5);
}
internal HyenaSqliteArrayDataReader (SqliteDataReader reader)
{
rows = new List<object[]> ();
column_names = new Dictionary<string, int> ();
closed = false;
current_row = -1;
ReadAllRows (reader);
reader.Dispose ();
}
/// <summary>Convert from DataReader to Task object</summary>
private TodoItem FromReader (SqliteDataReader r) {
var t = new TodoItem {
ID = Convert.ToInt32 (r["_id"]),
Name = r["Name"].ToString (),
Notes = r["Notes"].ToString (),
Done = (Convert.ToInt32 (r["Done"]) == 1)
};
return t;
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
ShapeID = System.Convert.ToInt32(sdr ["ShapeID"]);
State = System.Convert.ToInt32(sdr ["State"]);
Blueprint = System.Convert.ToString(sdr ["AI"]);
Describe = System.Convert.ToString(sdr ["Describe"]);
}
/// <summary>
/// Inicializa uma nova instancia da classe <see cref="Spartacus.Database.Sqlite"/>.
/// </summary>
/// <param name='p_file'>
/// Caminho para o arquivo DB.
/// </param>
public Sqlite(string p_file)
: base(p_file)
{
this.v_connectionstring = "Data Source=" + p_file + ";Version=3;Synchronous=Full;Journal Mode=Off;";
this.v_con = null;
this.v_cmd = null;
this.v_reader = null;
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["id"]);
OffsetPosition = MathfEx.ToVector3(System.Convert.ToString(sdr ["lockPosition"]));
OffsetPositionLerp = System.Convert.ToSingle(sdr ["lockPositionLerp"]);
OffsetEulerAngles = MathfEx.ToVector3(System.Convert.ToString(sdr ["lockRotation"]));
OffsetEulerAnglesLerp = System.Convert.ToSingle(sdr ["lockRotationLerp"]);
FieldOfView = System.Convert.ToSingle(sdr ["fieldOfView"]);
IsRelative = System.Convert.ToInt32 (sdr ["Relative"]) != 0 ? true : false;
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr["ID"]);
Name = System.Convert.ToString(sdr["Name"]);
Url = System.Convert.ToString(sdr["Url"]);
Type = System.Convert.ToInt32(sdr["Type"]);
Part = System.Convert.ToInt32(sdr["PartType"]);
Icon = System.Convert.ToString(sdr["Icon"]);
ExtendUrl = System.Convert.ToString(sdr["ExtendUrl"]);
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
SelfBuffID = System.Convert.ToInt32(sdr ["SelfBuffID"]);
TargetBuffID = System.Convert.ToInt32(sdr ["TargetBuffID"]);
SelfActionID = System.Convert.ToInt32(sdr ["SelfActionID"]);
TargetActionID = System.Convert.ToInt32(sdr ["TargetActionID"]);
AttackDistance = System.Convert.ToSingle(sdr ["AttackDistance"]);
}
private static TaskItem ReadTask(SqliteDataReader reader)
{
TaskItem task = new TaskItem
{
Id = Convert.ToInt32(reader["Id"]),
Name = Convert.ToString(reader["Name"]),
IsComplete = Convert.ToBoolean(reader["IsComplete"]),
};
return task;
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
AnimationID = System.Convert.ToInt32(sdr ["AnimationID"]);
DecodeEffect(
System.Convert.ToString(sdr ["Effect"])
);
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
Version = System.Convert.ToString(sdr ["Version"]);
UserName = System.Convert.ToString(sdr ["UserName"]);
Password = System.Convert.ToString(sdr ["Password"]);
MainScript = System.Convert.ToString(sdr ["MainScript"]);
ServerVersion = System.Convert.ToInt32(sdr ["ServerVersion"]);
ServerID = System.Convert.ToInt32(sdr ["ServerID"]);
IPAddress = System.Convert.ToString(sdr ["IPAddress"]);
Port = System.Convert.ToInt32(sdr ["Port"]);
}
public void CloseDatabase()
{
if (reader != null)
{
reader.Close();
reader = null;
}
databaseCommand = null;
databaseConnection.Close();
databaseConnection = null;
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr["ID"]);
Name = System.Convert.ToString(sdr["Name"]);
Url = System.Convert.ToString(sdr["Url"]);
Stream = System.Convert.ToInt32(sdr["Stream"]) != 0;
Script = System.Convert.ToString(sdr["Script"]);
Describe = System.Convert.ToString(sdr["Describe"]);
Type = (SceneType)System.Convert.ToInt32(sdr["Type"]);
Born = MathfEx.ToVector3(System.Convert.ToString(sdr["Born"]));
CameraID = System.Convert.ToInt32(sdr["CameraID"]);
}
/// <summary>
/// Decode the specified sdr.
/// </summary>
/// <param name="sdr">Sdr.</param>
public override void Decode (SqliteDataReader sdr)
{
ID = System.Convert.ToInt32(sdr ["ID"]);
Name = System.Convert.ToString(sdr ["Name"]);
Icon = System.Convert.ToString(sdr ["Icon"]);
Level = System.Convert.ToInt32(sdr ["Level"]);
State = System.Convert.ToInt32(sdr ["State"]);
Area = System.Convert.ToInt32(sdr ["Area"]);
Observer = System.Convert.ToString(sdr ["Observer"]);
EnableMenu = System.Convert.ToInt32(sdr ["EnableMenu"]) != 0;
EnableJoystick = System.Convert.ToInt32(sdr ["EnableJoystick"]) != 0;
}
static int Close(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
obj.Close();
return(0);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int GetEnumerator(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
System.Collections.IEnumerator o = obj.GetEnumerator();
ToLua.Push(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int GetSchemaTable(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
System.Data.DataTable o = obj.GetSchemaTable();
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
// Close database after use
public void CloseDB()
{
if (reader != null) {
reader.Close();
}
reader = null;
if (dbcmd != null) {
dbcmd.Cancel();
}
dbcmd = null;
if (dbcon != null) {
dbcon.Close();
}
dbcon = null;
}
/// <summary>Convert from DataReader to Task object</summary>
StepEntry FromReader (SqliteDataReader r) {
var t = new StepEntry ();
t.ID = Convert.ToInt32 (r ["_id"]);
t.Steps = Convert.ToInt64(r ["Steps"]);
var date = r ["Date"].ToString ();
var culture = CultureInfo.CreateSpecificCulture("en-US");
var styles = DateTimeStyles.None;
DateTime dateOut;
if (!DateTime.TryParse (date, culture, styles, out dateOut)) {
//back compat, but will never come in here really.
DateTime.TryParse (date, out dateOut);
}
t.Date = dateOut;
return t;
}
public SqliteDataReader ExecuteQuery(string sqlQuery)
{
try
{
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = sqlQuery;
reader = dbCommand.ExecuteReader();
return reader;
}
catch (Exception e)
{
SqlErr = e.Message;
return reader;
}
}
static int Read(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
bool o = obj.Read();
LuaDLL.lua_pushboolean(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
public static void ModifyPlayerWinCount(uint ID, int value)
{
for (int i = 0; i < players.Count; i++)
{
if (players [i].ID == ID)
{
//Confirm log in credentials through database
string databasePath = "";
Platform platform = RunningPlatform();
if (platform == Platform.Windows)
{
databasePath = "Data Source=" + System.Environment.CurrentDirectory + "\\database.db; FailIfMissing=True";
}
else if (platform == Platform.Mac)
{
databasePath = "Data Source=" + System.Environment.CurrentDirectory + "/data/database.db; FailIfMissing=True";
}
SQLiteConnection db_connection = new SQLiteConnection(databasePath);
db_connection.Open();
string query = "select * from Users where ID = @id";
SQLiteCommand command = new SQLiteCommand(query, db_connection);
command.Parameters.AddWithValue("@id", players[i].ID);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
uint currentWinCount = Convert.ToUInt32(reader ["WINCOUNT"]);
players [i] = SetPlayerWinCount(players [i], currentWinCount + 1);
string sql = "update Users set WINCOUNT = @wincount where ID = @id";
command = new SQLiteCommand(sql, db_connection);
command.Parameters.AddWithValue("@wincount", currentWinCount + 1);
command.Parameters.AddWithValue("@id", players[i].ID);
command.ExecuteNonQuery();
}
}
db_connection.Close();
break;
}
}
}
static int ExecuteQuery(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
string arg0 = ToLua.CheckString(L, 2);
Mono.Data.Sqlite.SqliteDataReader o = obj.ExecuteQuery(arg0);
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int GetValue(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
int arg0 = (int)LuaDLL.luaL_checknumber(L, 2);
object o = obj.GetValue(arg0);
ToLua.Push(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int GetValues(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
object[] arg0 = ToLua.CheckObjectArray(L, 2);
int o = obj.GetValues(arg0);
LuaDLL.lua_pushinteger(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int IsDBNull(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 2);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
int arg0 = (int)LuaDLL.luaL_checknumber(L, 2);
bool o = obj.IsDBNull(arg0);
LuaDLL.lua_pushboolean(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int InsertInto(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 3);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
string arg0 = ToLua.CheckString(L, 2);
string[] arg1 = ToLua.CheckStringArray(L, 3);
Mono.Data.Sqlite.SqliteDataReader o = obj.InsertInto(arg0, arg1);
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
/// <summary>
/// 转换实体
/// </summary>
/// <param name="read">OleDbDataReader</param>
/// <returns>TermInfo</returns>
private static List<StatisticsInfo> DataReaderToList(SqliteDataReader read)
{
List<StatisticsInfo> list = new List<StatisticsInfo>();
while (read.Read())
{
StatisticsInfo _site = new StatisticsInfo();
_site.PostCount = Convert.ToInt32(read["PostCount"]);
_site.CommentCount = Convert.ToInt32(read["CommentCount"]);
_site.VisitCount = Convert.ToInt32(read["VisitCount"]);
_site.TagCount = Convert.ToInt32(read["TagCount"]);
list.Add(_site);
}
read.Close();
return list;
}
static int get_RecordsAffected(IntPtr L)
{
object o = null;
try
{
o = ToLua.ToObject(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)o;
int ret = obj.RecordsAffected;
LuaDLL.lua_pushinteger(L, ret);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e, o == null ? "attempt to index RecordsAffected on a nil value" : e.Message));
}
}
static int get_IsClosed(IntPtr L)
{
object o = null;
try
{
o = ToLua.ToObject(L, 1);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)o;
bool ret = obj.IsClosed;
LuaDLL.lua_pushboolean(L, ret);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e, o == null ? "attempt to index IsClosed on a nil value" : e.Message));
}
}
static int GetChars(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 6);
Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader));
int arg0 = (int)LuaDLL.luaL_checknumber(L, 2);
long arg1 = LuaDLL.tolua_checkint64(L, 3);
char[] arg2 = ToLua.CheckCharBuffer(L, 4);
int arg3 = (int)LuaDLL.luaL_checknumber(L, 5);
int arg4 = (int)LuaDLL.luaL_checknumber(L, 6);
long o = obj.GetChars(arg0, arg1, arg2, arg3, arg4);
LuaDLL.tolua_pushint64(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
static int SelectWhere(IntPtr L)
{
try
{
ToLua.CheckArgsCount(L, 6);
SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper));
string arg0 = ToLua.CheckString(L, 2);
string[] arg1 = ToLua.CheckStringArray(L, 3);
string[] arg2 = ToLua.CheckStringArray(L, 4);
string[] arg3 = ToLua.CheckStringArray(L, 5);
string[] arg4 = ToLua.CheckStringArray(L, 6);
Mono.Data.Sqlite.SqliteDataReader o = obj.SelectWhere(arg0, arg1, arg2, arg3, arg4);
ToLua.PushObject(L, o);
return(1);
}
catch (Exception e)
{
return(LuaDLL.toluaL_exception(L, e));
}
}
/// <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 bool login(String name, String password)
{
using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection)) {
command.CommandText = "SELECT * FROM `account` WHERE `name`=@name AND `password`=@password";
command.Parameters.AddWithValue("@name", name);
command.Parameters.AddWithValue("@password", password);
using (SQLiteDataReader sdrITM = command.ExecuteReader()) {
if (!sdrITM.HasRows)
{
return(false);
}
sdrITM.Read();
accountID = Convert.ToInt32(sdrITM["id"]);
String world = Convert.ToString(sdrITM["world"]);
player.setSpawnInfos(world, new Types.Vec3f(Convert.ToSingle(sdrITM["posx"]), Convert.ToSingle(sdrITM["posy"]), Convert.ToSingle(sdrITM["posz"])), null);
}
//Attributes:
command.CommandText = "SELECT * FROM `account_attributes` WHERE `accountID`=@accountID ORDER BY `type` DESC";
command.Parameters.AddWithValue("@accountID", accountID);
using (SQLiteDataReader sdrITM = command.ExecuteReader()) {
if (sdrITM.HasRows)
{
while (sdrITM.Read())
{
player.setAttribute((NPCAttribute)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["value"]));
}
}
}
//Talents:
command.CommandText = "SELECT * FROM `account_talents` WHERE `accountID`=@accountID";
command.Parameters.AddWithValue("@accountID", accountID);
using (SQLiteDataReader sdrITM = command.ExecuteReader()) {
if (sdrITM.HasRows)
{
while (sdrITM.Read())
{
player.setTalentValues((NPCTalent)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["value"]));
player.setTalentSkills((NPCTalent)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["skill"]));
}
}
}
//Hitchances
command.CommandText = "SELECT * FROM `account_hitchances` WHERE `accountID`=@accountID";
command.Parameters.AddWithValue("@accountID", accountID);
using (SQLiteDataReader sdrITM = command.ExecuteReader()) {
if (sdrITM.HasRows)
{
while (sdrITM.Read())
{
player.setHitchances((NPCTalent)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["value"]));
}
}
}
//Items
command.CommandText = "SELECT * FROM `account_items` WHERE `accountID`=@accountID";
command.Parameters.AddWithValue("@accountID", accountID);
using (SQLiteDataReader sdrITM = command.ExecuteReader()) {
if (sdrITM.HasRows)
{
while (sdrITM.Read())
{
player.addItem(ItemInstance.getItemInstance(Convert.ToString(sdrITM["instanceID"])), Convert.ToInt32(sdrITM["amount"]));
}
}
}
return(true);
}
}
public static void LogInHandler(StateObject state, Socket handler)
{
Console.WriteLine("Received 'Log In' Request from {0}:{1}", ((IPEndPoint)handler.RemoteEndPoint).Address, ((IPEndPoint)handler.RemoteEndPoint).Port);
ClientLogInPacket dataReceived = GetLogInPacketFromBytes(state.buffer);
//Confirm log in credentials through database
string databasePath = "";
Platform platform = RunningPlatform();
if (platform == Platform.Windows)
{
databasePath = "Data Source=" + System.Environment.CurrentDirectory + "\\database.db; FailIfMissing=True";
}
else if (platform == Platform.Mac)
{
databasePath = "Data Source=" + System.Environment.CurrentDirectory + "/data/database.db; FailIfMissing=True";
}
SQLiteConnection db_connection = new SQLiteConnection(databasePath);
db_connection.Open();
string query = "select * from Users where USERNAME = @username and PASSWORD = @password";
SQLiteCommand command = new SQLiteCommand(query, db_connection);
command.Parameters.AddWithValue("@username", dataReceived.username);
command.Parameters.AddWithValue("@password", dataReceived.password);
SQLiteDataReader reader = command.ExecuteReader();
ServerConfirmationPacket packet;
packet.ID = 0;
packet.msg = 0;
if (reader.HasRows)
{
while (reader.Read())
{
//Check if the username & password are correct
if ((string)reader["USERNAME"] == dataReceived.username && (string)reader["PASSWORD"] == dataReceived.password)
{
uint id = Convert.ToUInt32(reader["ID"]);
//Check if that user has already logged in
if (CheckPlayerLogged(id))
{
packet.msg = (uint)ServerMessage.Server_Denied_AlreadyLogged;
break;
}
if (packet.msg != (uint)ServerMessage.Server_Denied_AlreadyLogged)
{
PlayerInfo newPlayer;
newPlayer.ID = id;
newPlayer.handler = handler;
newPlayer.authority = false;
newPlayer.username = dataReceived.username;
newPlayer.ready = false;
newPlayer.bomb = 0;
newPlayer.dead = false;
newPlayer.winCount = Convert.ToUInt32(reader["WINCOUNT"]);
newPlayer.ep = null;
packet.ID = id;
packet.msg = (uint)ServerMessage.Server_Accepted;
if (players.Count < 1)
{
packet.msg = (uint)ServerMessage.Server_AcceptedAuthority;
newPlayer.authority = true;
}
//Broadcast the message to the rest of the players
ServerMessagePacket broadcastPacket;
broadcastPacket.msg = (uint)ServerMessage.Server_PlayerConnected;
Broadcast(ConnectionType.TCP, GetBytesFromPacket(broadcastPacket));
players.Add(newPlayer);
}
break;
}
}
}
db_connection.Close();
SendTCP(handler, GetBytesFromPacket(packet));
}
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);
}
/// <summary>
/// This function does all the nasty work at determining what keys need to be returned for
/// a given statement.
/// </summary>
/// <param name="cnn"></param>
/// <param name="reader"></param>
/// <param name="stmt"></param>
internal SqliteKeyReader(SqliteConnection cnn, SqliteDataReader reader, SqliteStatement stmt)
{
Dictionary <string, int> catalogs = new Dictionary <string, int>();
Dictionary <string, List <string> > tables = new Dictionary <string, List <string> >();
List <string> list;
List <KeyInfo> keys = new List <KeyInfo>();
// Record the statement so we can use it later for sync'ing
_stmt = stmt;
// Fetch all the attached databases on this connection
using (DataTable tbl = cnn.GetSchema("Catalogs"))
{
foreach (DataRow row in tbl.Rows)
{
catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"]));
}
}
// Fetch all the unique tables and catalogs used by the current statement
using (DataTable schema = reader.GetSchemaTable(false, false))
{
foreach (DataRow row in schema.Rows)
{
// Check if column is backed to a table
if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
{
continue;
}
// Record the unique table so we can look up its keys
string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string table = (string)row[SchemaTableColumn.BaseTableName];
if (tables.ContainsKey(catalog) == false)
{
list = new List <string>();
tables.Add(catalog, list);
}
else
{
list = tables[catalog];
}
if (list.Contains(table) == false)
{
list.Add(table);
}
}
// For each catalog and each table, query the indexes for the table.
// Find a primary key index if there is one. If not, find a unique index instead
foreach (KeyValuePair <string, List <string> > pair in tables)
{
for (int i = 0; i < pair.Value.Count; i++)
{
string table = pair.Value[i];
DataRow preferredRow = null;
using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
{
// Loop twice. The first time looking for a primary key index,
// the second time looking for a unique index
for (int n = 0; n < 2 && preferredRow == null; n++)
{
foreach (DataRow row in tbl.Rows)
{
if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
{
preferredRow = row;
break;
}
else if (n == 1 && (bool)row["UNIQUE"] == true)
{
preferredRow = row;
break;
}
}
}
if (preferredRow == null) // Unable to find any suitable index for this table so remove it
{
pair.Value.RemoveAt(i);
i--;
}
else // We found a usable index, so fetch the necessary table details
{
using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
{
// Find the root page of the table in the current statement and get the cursor that's iterating it
int database = catalogs[pair.Key];
int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"]);
int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);
// Now enumerate the members of the index we're going to use
using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
{
KeyQuery query = null;
List <string> cols = new List <string>();
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
bool addKey = true;
// If the column in the index already appears in the query, skip it
foreach (DataRow row in schema.Rows)
{
if (row.IsNull(SchemaTableColumn.BaseColumnName))
{
continue;
}
if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] &&
(string)row[SchemaTableColumn.BaseTableName] == table &&
(string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key)
{
indexColumns.Rows.RemoveAt(x);
x--;
addKey = false;
break;
}
}
if (addKey == true)
{
cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]);
}
}
// If the index is not a rowid alias, record all the columns
// needed to make up the unique index and construct a SQL query for it
if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table)
{
// Whatever remains of the columns we need that make up the index that are not
// already in the query need to be queried separately, so construct a subquery
if (cols.Count > 0)
{
string[] querycols = new string[cols.Count];
cols.CopyTo(querycols);
query = new KeyQuery(cnn, pair.Key, table, querycols);
}
}
// Create a KeyInfo struct for each column of the index
for (int x = 0; x < indexColumns.Rows.Count; x++)
{
string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"];
KeyInfo key = new KeyInfo();
key.rootPage = rootPage;
key.cursor = cursor;
key.database = database;
key.databaseName = pair.Key;
key.tableName = table;
key.columnName = columnName;
key.query = query;
key.column = x;
keys.Add(key);
}
}
}
}
}
}
}
}
// Now we have all the additional columns we have to return in order to support
// CommandBehavior.KeyInfo
_keyInfo = new KeyInfo[keys.Count];
keys.CopyTo(_keyInfo);
}
/// <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.LogError("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.LogError("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.LogError("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 US_Scores;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE US_Songs;";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE us_statistics_info;";
command.ExecuteNonQuery();
//This versioning is not used in Vocaluxe so reset it to 0
command.CommandText = "PRAGMA user_version = 0";
command.ExecuteNonQuery();
}
}
return(true);
}
private bool _ImportData(string sourceDBPath)
{
#region open db
using (var connSource = new SQLiteConnection())
{
connSource.ConnectionString = "Data Source=" + sourceDBPath;
try
{
connSource.Open();
}
catch (Exception e)
{
CLog.LogError("Error on import high score data. Can't open source database \"" + sourceDBPath + "\" (" + e.Message + ")");
return(false);
}
#endregion open db
using (var cmdSource = new SQLiteCommand(connSource))
{
#region import table scores
cmdSource.CommandText = "SELECT SongID, PlayerName, Score, LineNr, Date, Medley, Duet, ShortSong, Difficulty FROM Scores";
SQLiteDataReader source = cmdSource.ExecuteReader();
if (source == null)
{
return(false);
}
if (source.FieldCount == 0)
{
source.Close();
return(true);
}
while (source.Read())
{
int songid = source.GetInt32(0);
string player = source.GetString(1);
int score = source.GetInt32(2);
int linenr = source.GetInt32(3);
long date = source.GetInt64(4);
int medley = source.GetInt32(5);
int duet = source.GetInt32(6);
int shortsong = source.GetInt32(7);
int diff = source.GetInt32(8);
string artist, title;
DateTime dateadded;
int numplayed;
if (_GetDataBaseSongInfos(songid, out artist, out title, out numplayed, out dateadded, sourceDBPath))
{
AddScore(player, score, linenr, date, medley, duet, shortsong, diff, artist, title, numplayed, _FilePath);
}
}
#endregion import table scores
source.Close();
}
}
return(true);
}
public bool GetCover(string coverPath, ref CTextureRef tex, int maxSize)
{
if (_Connection == null)
{
return(false);
}
if (!File.Exists(coverPath))
{
CLog.Error("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);
}
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 CommonSqliteDataReader(SQLiteDataReader sqliteDataReader)
#endif
{
this.sqliteDataReader = sqliteDataReader;
}
/// <summary>
/// Called by the SqliteDataReader when the data reader is closed.
/// </summary>
internal void ClearDataReader()
{
_activeReader = null;
}