| protected Dispose ( bool disposing ) : void | ||
| disposing | bool | True if the connection is being explicitly closed. |
| return | void | |
public virtual bool CreateDatabase( string sFile, bool bKeepOpen = false )
{
myDatabase = new SqliteConnection();
try {
if( System.IO.File.Exists(sFile) ) {
if( bKeepOpen == true ) {
myDatabase.ConnectionString = "Data Source=" + sFile + ";";
myDatabase.Open();
}
return false;
}
myDatabase.ConnectionString = "Data Source=" + sFile + ";";
myDatabase.Open();
if( bKeepOpen == false ) {
myDatabase.Close();
myDatabase.Dispose();
}
return true;
} catch {
return false;
}
}
public SqliteVsMonoDSSpeedTests()
{
// create path and filename to the database file.
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
_db = Path.Combine (documents, "mydb.db3");
if (File.Exists (_db))
File.Delete (_db);
SqliteConnection.CreateFile (_db);
var conn = new SqliteConnection("URI=" + _db);
using (var c = conn.CreateCommand()) {
c.CommandText = "CREATE TABLE DataIndex (SearchKey INTEGER NOT NULL,Name TEXT NOT NULL,Email Text NOT NULL)";
conn.Open ();
c.ExecuteNonQuery ();
conn.Close();
}
conn.Dispose();
// create path and filename to the database file.
var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments);
var libraryPath = Path.Combine (documentsPath, "..", "Library");
_dataDirectory = Path.Combine (libraryPath, "MonoDS");
_entity = "PersonEntity";
_serializer = new Serializer();
}
public void Open(string filePath)
{
if (_command != null)
_command.Dispose();
if (_conn != null)
_conn.Close();
try
{
ConnectionStringBuilder connstr = new ConnectionStringBuilder();
_conn = new Connection();
connstr.DataSource = (filePath.EndsWith("/") || filePath.EndsWith("\\")) ? filePath + "sys.db" : filePath + "/sys.db";
_conn.ConnectionString = connstr.ToString();
_conn.Open();
_command = new Command(_conn);
_command.CommandText = "SELECT Content FROM [Text] WHERE [Name]=:name AND [Language]=:language";
_command.Parameters.Add(new Parameter(":name", DbType.Binary));
_command.Parameters.Add(new Parameter(":language", DbType.Binary));
}
catch
{
if (_command != null)
_command.Dispose();
if (_conn != null)
_conn.Dispose();
_command = null;
_conn = null;
throw new DatabaseException("Cannot Open System Database");
}
}
public string proDataExc(string[] strTT)
{
try
{
if (strTT.Length < 1) { return "No SQL"; }
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
var conn = new SqliteConnection("Data Source=" + db);
var sqlitecmd = conn.CreateCommand();
conn.Open();
sqlitecmd.CommandType = CommandType.Text;
for (int j = 0; j < strTT.Length; j++)
{
if (strTT[j] == "") { continue; }
sqlitecmd.CommandText = strTT[j];
sqlitecmd.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();
return "";
}
catch (Exception Ex)
{
return Ex.ToString();
}
}
private void Cleanup(SqliteConnection cnn)
{
if (_disposeConnection)
cnn.Dispose();
_transaction = null;
_scope = null;
}
public virtual void Close()
{
lock (_Mutex)
{
if (_Connection != null)
{
_Connection.Dispose();
_Connection = null;
}
}
}
public EvolutionSummaryTracker (string directory, string account_name, string folder_name)
{
// Make the on-disk files for folders have sane names
folder_name = folder_name.Replace ('/', '-');
folder_name = folder_name.Replace (':', '_');
folder_name = folder_name.Replace (',', ' '); // Causes problems with the ConnectionString otherwise
string filename = Path.Combine (directory, String.Format ("SummaryTracker-{0}-{1}.db", account_name, folder_name));
bool create_new_db = ! File.Exists (filename);
bool purge_old_db = false;
connection = GetConnection (filename);
try {
connection.Open ();
} catch (ApplicationException) {
purge_old_db = true;
}
if (! create_new_db && ! purge_old_db) {
// Run a dummy SELECT statement to catch more errors
// indicating sqlite version mismatches.
using (SqliteCommand command = new SqliteCommand ()) {
command.Connection = connection;
command.CommandText = "SELECT flags FROM mapping WHERE uid = 'fo/ky'";
SqliteDataReader reader;
try {
reader = SqliteUtils.ExecuteReaderOrWait (command);
reader.Close ();
} catch (ApplicationException) {
purge_old_db = true;
}
}
}
if (purge_old_db) {
connection.Dispose ();
// Purge the old database and create a new one
File.Delete (filename);
connection = GetConnection (filename);
connection.Open ();
create_new_db = true;
}
if (create_new_db)
CreateDatabase ();
// Start a transaction for any updates
SqliteUtils.DoNonQuery (connection, "BEGIN");
}
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);
}
public string proDataCreate()
{
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
bool BFile = File.Exists(db);
if (!BFile) { SqliteConnection.CreateFile(db); }
var conn = new SqliteConnection("Data Source=" + db);
string[] commands = proDataCreateFmt();
try
{
foreach (var cmd in commands)
{
var sqlitecmd = conn.CreateCommand();
sqlitecmd.CommandText = cmd;
sqlitecmd.CommandType = CommandType.Text;
conn.Open();
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
if (!BFile) // 初始化
{
var sqlitecmd = conn.CreateCommand();
conn.Open();
sqlitecmd.CommandType = CommandType.Text;
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERID (USERID) VALUES (0);";
sqlitecmd.ExecuteNonQuery();
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTELNO (USERTELNO) VALUES ('');";
sqlitecmd.ExecuteNonQuery();
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTYPE (USERTYPE) VALUES (0);";
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
conn.Dispose();
return "";
}
catch (System.Exception sysExc)
{
return "创建数据出错: " + sysExc.Message;
}
}
public void Dispose()
{
try
{
if (_SqlTransaction != null)
{
_SqlTransaction.Commit();
_SqlTransaction.Dispose();
}
if (_SqlCommand != null)
{
_SqlCommand.Dispose();
}
if (_SqlConnection != null)
{
_SqlConnection.Dispose();
}
}
catch { }
}
public static void Execute(string command, Object obj, Dictionary<string, DbType> lookup)
{
SqliteConnection conn = new SqliteConnection (data_source);
SqliteCommand cmd;
conn.Open ();
cmd = new SqliteCommand (command, conn);
try {
if (obj != null)
Database.AddParameters (cmd, obj, lookup);
cmd.ExecuteNonQuery ();
} catch (KeyNotFoundException) {
Log.Warning ("Missing a parameter somewhere; not executing SQL statement");
} catch (Exception e) {
Log.Exception ("Exception occurred while executing query", e);
} finally {
cmd.Dispose ();
conn.Dispose ();
}
}
public static void InitXBansDB()
{
if (!EBConfig.UseMysql)
{
string sql = Path.Combine(EBDB);
if (!File.Exists(EBDB))
{
SqliteConnection.CreateFile(EBDB);
}
DBSqlite = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
CheckTables(DBSqlite);
DBSqlite.Dispose();
}
else
{
DBMysql = new MySqlConnection(string.Format("Data Source={0};User Id={1};Password={2}", EBConfig.MysqlHost, EBConfig.MysqlLogin, EBConfig.MysqlPassword));
string CMD = string.Format("CREATE DATABASE IF NOT EXISTS {0}", EBConfig.MysqlDatabase);
RunExec(CMD);
DBMysql = new MySqlConnection(string.Format("Database={0};Data Source={1};User Id={2};Password={3}", EBConfig.MysqlDatabase, EBConfig.MysqlHost, EBConfig.MysqlLogin, EBConfig.MysqlPassword));
CheckTables(DBMysql);
ImportToMysql();
}
}
internal static void ImportToMysql()
{
string sql = Path.Combine(EBDB);
if (File.Exists(EBDB))
{
string[] baninfo = new string[5];
DBSqlite = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql));
var DBQuery = DBSqlite.QueryReader("SELECT * FROM BannedIP");
while (DBQuery.Read())
{
baninfo[0] = DBQuery.Get<string>("IP");
baninfo[1] = DBQuery.Get<int>("BanDate").ToString();
baninfo[2] = DBQuery.Get<int>("UnbanDate").ToString();
baninfo[3] = DBQuery.Get<string>("BannedBy");
baninfo[4] = DBQuery.Get<string>("Reason");
RunExec("INSERT INTO BannedIP (IP, BanDate, UnbanDate, BannedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')");
}
DBSqlite.Dispose();
DBQuery.Dispose();
DBQuery = DBSqlite.QueryReader("SELECT * FROM BannedPlayer");
while (DBQuery.Read())
{
baninfo[0] = DBQuery.Get<string>("Player");
baninfo[1] = DBQuery.Get<int>("BanDate").ToString();
baninfo[2] = DBQuery.Get<int>("UnbanDate").ToString();
baninfo[3] = DBQuery.Get<string>("BannedBy");
baninfo[4] = DBQuery.Get<string>("Reason");
RunExec("INSERT INTO BannedPlayer (Player, BanDate, UnbanDate, BannedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')");
}
DBSqlite.Dispose();
DBQuery.Dispose();
DBQuery = DBSqlite.QueryReader("SELECT * FROM MutedPlayer");
while (DBQuery.Read())
{
baninfo[0] = DBQuery.Get<string>("Player");
baninfo[1] = DBQuery.Get<int>("MuteDate").ToString();
baninfo[2] = DBQuery.Get<int>("UnmuteDate").ToString();
baninfo[3] = DBQuery.Get<string>("BannedBy");
baninfo[4] = DBQuery.Get<string>("MutedBy");
RunExec("INSERT INTO MutedPlayer (Player, MuteDate, UnmuteDate, MutedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')");
}
DBSqlite.Dispose();
DBSqlite.Close();
DBQuery.Dispose();
File.Delete(EBDB);
}
}
void PullDataButton_Click(object sender, EventArgs e)
{
var conn = new SqliteConnection("Data Source=" + dbPath ()); //object om db te lezen
var strSql = "select Name from Customer where STATEID=@STATEID";
var cmd = new SqliteCommand(strSql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqliteParameter("@STATEID", 1));
try
{
conn.Open();
var sdr = cmd.ExecuteReader ();//lezer
while (sdr.Read())
{
// TODO RunOnUiThread
/* Found out how - not sure why though?
*/
string name = Convert.ToString(sdr["Name"]);
RunOnUiThread(() => tv.Text = name);
}
}
catch (System.Exception sysExc)
{
tv.Text = sysExc.Message;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
}
}
/// <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 static bool ConvertFrom101(string FilePath)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + FilePath;
SQLiteCommand command;
SQLiteDataReader reader = null;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
command = new SQLiteCommand(connection);
command.CommandText = "PRAGMA table_info(US_Scores);";
reader = command.ExecuteReader();
bool dateExists = false;
//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;
}
}
}
reader.Close();
//This is a USDX 1.01 DB
if (!dateExists)
command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores";
else // This is a CMD 1.01 DB
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();
// convert from CP1252 to UTF8
List<SData> scores = new List<SData>();
List<SData> 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
{
Sqlite3.Vdbe 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)
{
SData data = new SData();
data.id = Sqlite3.sqlite3_column_int(Stmt, 0);
byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1);
if (bytes != null)
data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
else
data.str1 = "Someone";
bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 2);
if (bytes != null)
data.str2 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
else
data.str2 = "Someone";
songs.Add(data);
}
Sqlite3.sqlite3_finalize(Stmt);
}
Stmt = new Sqlite3.Vdbe();
if (!dateExists)
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)
{
SData data = new SData();
data.id = Sqlite3.sqlite3_column_int(Stmt, 0);
byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1);
if (bytes != null)
data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes));
else
data.str1 = "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", System.Data.DbType.String, 0).Value = data.str2;
command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1;
command.Parameters.Add("@id", System.Data.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", System.Data.DbType.Int64, 0).Value = data.ticks;
}
command.Parameters.Add("@player", System.Data.DbType.String, 0).Value = data.str1;
command.Parameters.Add("@id", System.Data.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();
reader.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
return true;
}
public static int AddScore(SPlayer player)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _HighscoreFilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return -1;
}
command = new SQLiteCommand(connection);
int DataBaseSongID = GetDataBaseSongID(player, command);
int result = AddScore(player, command, DataBaseSongID);
connection.Close();
connection.Dispose();
return result;
}
//If you want to add an image to db, call this method!
private static bool AddImageToCreditsDB(String ImagePath)
{
bool result = false;
STexture tex;
if (File.Exists(ImagePath))
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
command = new SQLiteCommand(connection);
SQLiteDataReader reader = null;
if (reader != null)
reader.Close();
Bitmap origin;
try
{
origin = new Bitmap(ImagePath);
}
catch (Exception)
{
CLog.LogError("Error loading Texture: " + ImagePath);
tex = new STexture(-1);
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
connection.Close();
connection.Dispose();
return false;
}
int w = origin.Width;
int h = origin.Height;
Bitmap bmp = new Bitmap(w, h);
Graphics g = Graphics.FromImage(bmp);
g.DrawImage(origin, new Rectangle(0, 0, w, h));
g.Dispose();
tex = CDraw.AddTexture(bmp);
byte[] data = new byte[w * h * 4];
BitmapData bmp_data = bmp.LockBits(new Rectangle(0, 0, bmp.Width, bmp.Height), ImageLockMode.ReadOnly, System.Drawing.Imaging.PixelFormat.Format32bppArgb);
Marshal.Copy(bmp_data.Scan0, data, 0, w * h * 4);
bmp.UnlockBits(bmp_data);
bmp.Dispose();
command.CommandText = "INSERT INTO Images (Path, width, height) " +
"VALUES (@path, " + w.ToString() + ", " + h.ToString() + ")";
command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = Path.GetFileName(ImagePath);
command.ExecuteNonQuery();
command.CommandText = "SELECT id FROM Images WHERE [Path] = @path";
command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = Path.GetFileName(ImagePath);
reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader != null)
{
reader.Read();
int id = reader.GetInt32(0);
reader.Close();
command.CommandText = "INSERT INTO ImageData (ImageID, Data) " +
"VALUES ('" + id.ToString() + "', @data)";
command.Parameters.Add("@data", System.Data.DbType.Binary, 20).Value = data;
command.ExecuteReader();
result = true;
}
}
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 static bool ConvertFrom110(string FilePath)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + FilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
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();
List<SData> scores = new List<SData>();
List<SData> songs = new List<SData>();
SQLiteDataReader reader = null;
command.CommandText = "SELECT id, PlayerName, Date FROM Scores";
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SData data = new SData();
data.id = reader.GetInt32(0);
data.str1 = reader.GetString(1);
data.ticks = UnixTimeToTicks((int)reader.GetInt64(2));
scores.Add(data);
}
reader.Close();
}
command.CommandText = "SELECT id, Artist, Title FROM Songs";
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SData data = new SData();
data.id = reader.GetInt32(0);
data.str1 = reader.GetString(1);
data.str2 = reader.GetString(2);
songs.Add(data);
}
reader.Close();
}
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", System.Data.DbType.String, 0).Value = data.str2;
command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1;
command.Parameters.Add("@id", System.Data.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", System.Data.DbType.String, 0).Value = data.str1;
command.Parameters.Add("@date", System.Data.DbType.Int64, 0).Value = data.ticks;
command.Parameters.Add("@id", System.Data.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();
command.Dispose();
connection.Close();
connection.Dispose();
return true;
}
private static void CreateHighscoreDBV1(string FilePath)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + FilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return;
}
command = new SQLiteCommand(connection);
command.CommandText = "CREATE TABLE IF NOT EXISTS Version ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Value INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Version (id, Value) VALUES(NULL, 1 )";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Songs ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Artist TEXT NOT NULL, Title TEXT NOT NULL, NumPlayed INTEGER);";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Scores ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"SongID INTEGER NOT NULL, PlayerName TEXT NOT NULL, Score INTEGER NOT NULL, LineNr INTEGER NOT NULL, Date BIGINT NOT NULL, " +
"Medley INTEGER NOT NULL, Duet INTEGER NOT NULL, Difficulty INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
public static int AddScore(string PlayerName, int Score, int LineNr, long Date, int Medley, int Duet, int ShortSong, int Diff,
string Artist, string Title, int NumPlayed, string FilePath)
{
SPlayer player = new SPlayer();
player.Name = PlayerName;
player.Points = Score;
player.LineNr = LineNr;
player.DateTicks = Date;
player.Medley = (Medley == 1);
player.Duet = (Duet == 1);
player.ShortSong = (ShortSong == 1);
player.Difficulty = (EGameDifficulty)Diff;
SQLiteConnection connection = new SQLiteConnection();
SQLiteCommand command;
connection.ConnectionString = "Data Source=" + FilePath;
try
{
connection.Open();
}
catch (Exception)
{
return -1;
}
command = new SQLiteCommand(connection);
int DataBaseSongID = GetDataBaseSongID(Artist, Title, NumPlayed, command);
int result = AddScore(player, command, DataBaseSongID);
command.Dispose();
connection.Close();
connection.Dispose();
return result;
}
public static object ExecuteScalar(string command, Object obj, Dictionary<string, DbType> lookup)
{
SqliteConnection conn = new SqliteConnection (data_source);
SqliteCommand cmd;
object result = null;
conn.Open ();
cmd = new SqliteCommand (command, conn);
try {
if (obj != null)
Database.AddParameters (cmd, obj, lookup);
result = cmd.ExecuteScalar ();
} catch (KeyNotFoundException) {
Log.Warning ("Missing a parameter somewhere; not executing SQL statement");
} catch (Exception e) {
Log.Exception (e);
} finally {
cmd.Dispose ();
conn.Dispose ();
}
return result;
}
private static bool GetDataBaseSongInfos(int SongID, out string Artist, out string Title, out int NumPlayed, string FilePath)
{
Artist = String.Empty;
Title = String.Empty;
NumPlayed = 0;
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + FilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
command = new SQLiteCommand(connection);
command.CommandText = "SELECT Artist, Title, NumPlayed FROM Songs WHERE [id] = @id";
command.Parameters.Add("@id", System.Data.DbType.String, 0).Value = SongID;
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
return false;
}
if (reader != null && reader.HasRows)
{
reader.Read();
Artist = reader.GetString(0);
Title = reader.GetString(1);
NumPlayed = reader.GetInt32(2);
reader.Close();
reader.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
return true;
}
else
{
if (reader != null)
reader.Close();
}
command.Dispose();
connection.Close();
connection.Dispose();
return false;
}
void CreateDataBaseButton_Click(object sender, EventArgs e)
{
//Database aanmaken
bool exists = File.Exists(dbPath ());
if (!exists)
{
SqliteConnection.CreateFile(dbPath ());// db maken indien't niet bestaat
}
var conn = new SqliteConnection("Data Source=" + dbPath ()); //object dat communiceert met database
//TODO where do these commands come from?
/* other language with specific syntax?
*/
var commands = new[] {
"DROP TABLE IF EXISTS TWITTERDATA",
"DROP TRIGGER IF EXISTS TWITTERDATA_INSERT",
"CREATE TABLE IF NOT EXISTS STATE (STATEID INT PRIMARY KEY, STATENAME VARCHAR(50))",
"CREATE TABLE IF NOT EXISTS CUSTOMER(CUSTOMERID BIGINT PRIMARY KEY, " +
"NAME VARCHAR(100), CONTACTNAME VARCHAR(100), DATEJOINED DATETIME, " +
"PHONE VARCHAR(25), ADDRESS VARCHAR(100), CITY VARCHAR(50), " +
"STATEID INT, ZIPCODE VARCHAR(25), DATEENTERED DATETIME, " +
"DATEUPDATED DATETIME, FOREIGN KEY(STATEID) REFERENCES STATE(STATEID))",
"CREATE TRIGGER IF NOT EXISTS CUSTOMER_INSERT INSERT ON CUSTOMER " +
"BEGIN UPDATE CUSTOMER SET DATEENTERED=DATE('now') " +
"WHERE CUSTOMERID=NEW.CUSTOMERID; END;",
"CREATE INDEX IF NOT EXISTS IDX_CUSTOMERNAME ON CUSTOMER (NAME)",
"CREATE INDEX IF NOT EXISTS IDX_STATEID ON CUSTOMER (STATEID)",
"CREATE INDEX IF NOT EXISTS IDX_DATEENTERED ON CUSTOMER (DATEENTERED)",
"INSERT INTO STATE (STATENAME) VALUES ('TENNESSEE');",
"INSERT INTO STATE (STATENAME) VALUES ('GEORGIA');"};
try
{
// TODO indentation (like this? or...??)
foreach (var cmd in commands)
using (var sqlitecmd = conn.CreateCommand())
{
sqlitecmd.CommandText = cmd;
sqlitecmd.CommandType = CommandType.Text;
conn.Open();
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
SqliteCommand sqlc = new SqliteCommand();
sqlc.Connection = conn;
conn.Open();
string strSql = "INSERT INTO CUSTOMER (NAME, " +
"CONTACTNAME, STATEID) VALUES " +
"(@NAME, @CONTACTNAME, @STATEID)";
sqlc.CommandText = strSql;
sqlc.CommandType = CommandType.Text;
sqlc.Parameters.Add(new SqliteParameter("@NAME", "The Coca-Cola Company"));
sqlc.Parameters.Add(new SqliteParameter("@CONTACTNAME", "John Johns"));
sqlc.Parameters.Add(new SqliteParameter("@STATEID", 1));//parameters voor commando toevoegen
sqlc.ExecuteNonQuery();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.Dispose();
tv.Text = "Commands completed.";
}
catch (System.Exception sysExc)
{
tv.Text = "Exception: " + sysExc.Message;
}
}
private static bool InitCoverDB()
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _CoverFilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
command = new SQLiteCommand(connection);
command.CommandText = "SELECT Value FROM Version";
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
;
}
if (reader == null)
{
// create new database/tables
CreateCoverDB();
}
else if (reader.FieldCount == 0)
{
// create new database/tables
CreateCoverDB();
}
else
{
reader.Read();
if (reader.GetInt32(0) < CSettings.iDatabaseHighscoreVersion)
{
// update database
}
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
connection.Close();
connection.Dispose();
return true;
}
private static bool InitCreditsRessourcesDB()
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
command = new SQLiteCommand(connection);
command.CommandText = "SELECT Value FROM Version";
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
;
}
if (reader == null)
{
// Log error
CLog.LogError("Can't find Credits-DB!");
}
else if (reader.FieldCount == 0)
{
// Log error
CLog.LogError("Can't find Credits-DB! Field-Count = 0");
}
else
{
reader.Read();
if (reader.GetInt32(0) < CSettings.iDatabaseHighscoreVersion)
{
// update database
}
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
connection.Close();
connection.Dispose();
return true;
}
public TextCache (string storage_dir, bool read_only)
{
text_cache_dir = Path.Combine (storage_dir, "TextCache");
if (! Directory.Exists (text_cache_dir)) {
Directory.CreateDirectory (text_cache_dir);
// Create our cache subdirectories.
for (int i = 0; i < 256; ++i) {
string subdir = i.ToString ("x");
if (i < 16)
subdir = "0" + subdir;
subdir = Path.Combine (text_cache_dir, subdir);
Directory.CreateDirectory (subdir);
}
}
// Create our Sqlite database
string db_filename = Path.Combine (text_cache_dir, "TextCache.db");
bool create_new_db = false;
if (! File.Exists (db_filename))
create_new_db = true;
// Funky logic here to deal with sqlite versions.
//
// When sqlite 3 tries to open an sqlite 2 database,
// it will throw an SqliteException with SqliteError
// NOTADB when trying to execute a command.
//
// When sqlite 2 tries to open an sqlite 3 database,
// it will throw an ApplicationException when it
// tries to open the database.
try {
connection = Open (db_filename);
} catch (ApplicationException) {
Logger.Log.Warn ("Likely sqlite database version mismatch trying to open {0}. Purging.", db_filename);
create_new_db = true;
}
if (!create_new_db) {
// Run a dummy query to see if we get a NOTADB error. Sigh.
SqliteCommand command;
SqliteDataReader reader = null;
command = new SqliteCommand ();
command.Connection = connection;
command.CommandText =
"SELECT filename FROM textcache_data WHERE uri='blah'";
try {
reader = SqliteUtils.ExecuteReaderOrWait (command);
} catch (ApplicationException ex) {
Logger.Log.Warn ("Likely sqlite database version mismatch trying to read from {0}. Purging.", db_filename);
create_new_db = true;
} catch (SqliteException ex) {
// When the table name changed from 0.2.18 -> 0.3.0.
Logger.Log.Warn ("Sqlite error: {0}. Purging textcache.", ex.Message);
create_new_db = true;
}
if (reader != null)
reader.Dispose ();
command.Dispose ();
}
if (create_new_db) {
if (connection != null)
connection.Dispose ();
if (read_only)
throw new UnauthorizedAccessException (String.Format ("Unable to create read only text cache {0}", db_filename));
File.Delete (db_filename);
try {
connection = Open (db_filename);
} catch (Exception e) {
Log.Debug (e, "Exception opening text cache {0}", db_filename);
}
// Database schema: uri, filename, data
SqliteUtils.DoNonQuery (connection,
"CREATE TABLE textcache_data ( " +
" uri TEXT UNIQUE NOT NULL, " +
" filename TEXT NOT NULL, " +
" data BLOB " +
")");
}
#if ENABLE_RDF_ADAPTER
try {
SqliteUtils.DoNonQuery (connection,
"CREATE TABLE IF NOT EXISTS links_data ( " +
" uri TEXT UNIQUE NOT NULL, " +
" links TEXT " +
")");
} catch (SqliteException) { }
#endif
this.InitCommands ();
}
/// <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 static bool CreateOrConvert(string FilePath)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + FilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
SQLiteDataReader reader = null;
command = new SQLiteCommand(connection);
command.CommandText = "PRAGMA user_version";
reader = command.ExecuteReader();
reader.Read();
int version = reader.GetInt32(0);
reader.Close();
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.Close();
reader.Dispose();
command.CommandText = "SELECT Value FROM Version";
reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
;
}
if (reader == null)
{
// create new database/tables
if (version == 1) //Check for USDX 1.1 DB
{
CreateHighscoreDBV1(FilePath);
ConvertFrom110(FilePath);
UpdateDatabase(1, connection);
}
else if (version == 0 && scoresTableExists) //Check for USDX 1.01 or CMD Mod DB
{
CreateHighscoreDBV1(FilePath);
ConvertFrom101(FilePath);
UpdateDatabase(1, connection);
}
else
CreateHighscoreDB(FilePath);
}
else if (reader.FieldCount == 0)
{
// create new database/tables
if (version == 1) //Check for USDX 1.1 DB
{
CreateHighscoreDBV1(FilePath);
ConvertFrom110(FilePath);
UpdateDatabase(1, connection);
}
else if (version == 0 && scoresTableExists) //Check for USDX 1.01 or CMD Mod DB
{
CreateHighscoreDBV1(FilePath);
ConvertFrom101(FilePath);
UpdateDatabase(1, connection);
}
else
CreateHighscoreDB(FilePath);
}
else
{
reader.Read();
int CurrentVersion = reader.GetInt32(0);
if (CurrentVersion < CSettings.iDatabaseHighscoreVersion)
{
// update database
UpdateDatabase(CurrentVersion, connection);
}
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
connection.Close();
connection.Dispose();
return true;
}
private static void CreateCreditsRessourcesDB()
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return;
}
command = new SQLiteCommand(connection);
command.CommandText = "CREATE TABLE IF NOT EXISTS Version ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Value INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO Version (id, Value) VALUES(NULL, " + CSettings.iDatabaseCreditsRessourcesVersion.ToString() + ")";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS Images ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"Path TEXT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL);";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IF NOT EXISTS ImageData ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
"ImageID INTEGER NOT NULL, Data BLOB NOT NULL);";
command.ExecuteNonQuery();
command.Dispose();
connection.Close();
connection.Dispose();
}
public static bool GetCreditsRessource(string FileName, ref STexture tex)
{
bool result = false;
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return false;
}
command = new SQLiteCommand(connection);
command.CommandText = "SELECT id, width, height FROM Images WHERE [Path] = @path";
command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = FileName;
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
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.ToString();
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader.HasRows)
{
result = true;
reader.Read();
byte[] data = GetBytes(reader);
tex = CDraw.AddTexture(w, h, ref data);
}
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
command.Dispose();
connection.Close();
connection.Dispose();
return result;
}
public DataTable proDataSql(string strSql)
{
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
var conn = new SqliteConnection("Data Source=" + db);
var cmd = new SqliteCommand(strSql, conn);
cmd.CommandType = CommandType.Text;
try
{
conn.Open();
SqliteDataReader sdr = cmd.ExecuteReader();
System.Data.DataTable dtBack = new System.Data.DataTable();
for (int i = 0; i < sdr.FieldCount; i++)
{
dtBack.Columns.Add(sdr.GetName(i));
}
while (sdr.Read())
{
DataRow dr = dtBack.NewRow();
for (int j = 0; j < dtBack.Columns.Count; j++)
{
dr[j] = sdr[j].ToString();
}
dtBack.Rows.Add(dr);
}
return dtBack;
}
catch // (System.Exception sysExc)
{
return null;
}
finally
{
if (conn.State != ConnectionState.Closed) { conn.Close(); }
conn.Dispose();
}
}
public static void LoadScore(ref List<SScores> Score, SPlayer player)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + _HighscoreFilePath;
SQLiteCommand command;
Score = new List<SScores>();
try
{
connection.Open();
}
catch (Exception)
{
return;
}
command = new SQLiteCommand(connection);
int Medley = 0;
if (player.Medley)
Medley = 1;
int Duet = 0;
if (player.Duet)
Duet = 1;
int ShortSong = 0;
if (player.ShortSong)
ShortSong = 1;
int DataBaseSongID = GetDataBaseSongID(player, command);
if (DataBaseSongID >= 0)
{
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";
command.Parameters.Add("@SongID", System.Data.DbType.Int32, 0).Value = DataBaseSongID;
command.Parameters.Add("@Medley", System.Data.DbType.Int32, 0).Value = Medley;
command.Parameters.Add("@Duet", System.Data.DbType.Int32, 0).Value = Duet;
command.Parameters.Add("@ShortSong", System.Data.DbType.Int32, 0).Value = ShortSong;
SQLiteDataReader reader = null;
try
{
reader = command.ExecuteReader();
}
catch (Exception)
{
throw;
}
if (reader != null && reader.HasRows)
{
while (reader.Read())
{
SScores score = new SScores();
score.Name = reader.GetString(0);
score.Score = reader.GetInt32(1);
score.Date = new DateTime(reader.GetInt64(2)).ToString("dd/MM/yyyy");
score.Difficulty = (EGameDifficulty)reader.GetInt32(3);
score.LineNr = reader.GetInt32(4);
score.ID = reader.GetInt32(5);
Score.Add(score);
}
reader.Close();
reader.Dispose();
}
}
command.Dispose();
connection.Close();
connection.Dispose();
}
public void Open()
{
string connString = ConnStrForFile (m_FileName);
var dirInfo = new FileInfo (m_FileName).Directory;
if (!dirInfo.Exists)
dirInfo.Create ();
m_rwLock.AcquireWriterLock (s_lockTimeout);
try {
// Backup if needed
var backup = m_FileName + ".bak";
var needsUpgrade = NeedsUpgrade;
if (needsUpgrade && File.Exists (m_FileName))
File.Move (m_FileName, backup);
else if (!File.Exists (m_FileName))
File.WriteAllText (VersionFile, String.Format ("{0}", s_version));
// Build
m_conn = new SqliteConnection (connString);
m_conn.Open ();
EnsureTables ();
if (needsUpgrade) {
File.WriteAllText (VersionFile, String.Format ("{0}", s_version));
// Open backup database
var conn = new SqliteConnection (ConnStrForFile (backup));
conn.Open ();
// Copy the database in a thread
ThreadPool.QueueUserWorkItem (delegate {
try {
this.CopyDatabase (conn);
} catch (Exception ex) {
Console.WriteLine (ex.ToString ());
}
conn.Close ();
conn.Dispose ();
if (File.Exists (backup))
File.Delete (backup);
});
}
}
finally {
m_rwLock.ReleaseWriterLock ();
}
}
private static int GetDataBaseSongID(string Artist, string Title, string FilePath, int DefNumPlayed)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + FilePath;
SQLiteCommand command;
try
{
connection.Open();
}
catch (Exception)
{
return -1;
}
command = new SQLiteCommand(connection);
int id = GetDataBaseSongID(Artist, Title, DefNumPlayed, command);
command.Dispose();
connection.Close();
connection.Dispose();
return id;
}
