public async void AddPremiumPlayerAsync(string ip, Guid guid)
{
await Connection.OpenAsync();
// Check if the player already has an entry
SQLiteCommand command = new SQLiteCommand("SELECT COUNT(*) FROM premium WHERE GUID = $guid");
command.Parameters.AddWithValue("$guid", guid.ToString("N"));
long count = (long)await ExecuteScalarAsync(command);
if (count == 1)
{
command = new SQLiteCommand("UPDATE premium SET ip = $ip, timestamp = datetime('now') WHERE GUID = $guid");
command.Parameters.AddWithValue("$guid", guid.ToString("N"));
command.Parameters.AddWithValue("$ip", ip);
command.Connection = Connection;
await command.ExecuteNonQueryAsync();
}
else
{
command = new SQLiteCommand("INSERT INTO premium(IP, GUID) VALUES ($ip, $guid)");
command.Parameters.AddWithValue("$guid", guid.ToString("N"));
command.Parameters.AddWithValue("$ip", ip);
command.Connection = Connection;
await command.ExecuteNonQueryAsync();
}
Connection.Close();
}
public async Task CreateTables(SQLiteConnection connection)
{
using (
var command =
new SQLiteCommand(
"CREATE TABLE `Playlists` (Name VARCHAR(255), Guid VARCHAR(36) NOT NULL, PRIMARY KEY (Guid))",
connection))
await command.ExecuteNonQueryAsync();
using (
var command =
new SQLiteCommand(
"CREATE TABLE `PlaylistTracks` (PlaylistId VARCHAR(36), TrackId VARCHAR(36))",
connection))
await command.ExecuteNonQueryAsync();
using (
var command =
new SQLiteCommand(
"INSERT INTO `Playlists` (Name, Guid) VALUES (@name, @guid)", connection))
{
command.Parameters.AddWithValue("@name", "Default Playlist");
command.Parameters.AddGuid("@guid", Guid.NewGuid());
await command.ExecuteNonQueryAsync();
}
}
public Task CreateTables(SQLiteConnection connection)
{
using (
var command =
new SQLiteCommand("CREATE TABLE `Images` (Type INT, Guid VARCHAR(36) NOT NULL, Data VARCHAR(400), PRIMARY KEY (Guid))",
connection))
return command.ExecuteNonQueryAsync();
}
public async Task ExecuteQueryAsync(string queryString)
{
_con.Open();
_sqlCmd = _con.CreateCommand();
_sqlCmd.CommandText = queryString;
await _sqlCmd.ExecuteNonQueryAsync();
//sql_con.Dispose();
_con.Close();
}
public Task CreateTables(SQLiteConnection connection)
{
using (
var command =
new SQLiteCommand(
"CREATE TABLE `Artists` (Name VARCHAR(255), MusicBrainzId VARCHAR(36), Url VARCHAR(255), Guid VARCHAR(36) NOT NULL, SmallImage VARCHAR(36), MediumImage VARCHAR(36), LargeImage VARCHAR(36), PRIMARY KEY (Guid))",
connection))
return command.ExecuteNonQueryAsync();
}
public Task CreateTables(SQLiteConnection connection)
{
using (
var command =
new SQLiteCommand(
"CREATE TABLE `Albums` (Name VARCHAR(255), Guid VARCHAR(36) NOT NULL, Artists VARCHAR(8192),PRIMARY KEY (Guid))",
connection))
return command.ExecuteNonQueryAsync();
}
public Task CreateTables(SQLiteConnection connection)
{
using (
var command =
new SQLiteCommand(
"CREATE TABLE `Tracks` (Title VARCHAR(255) NOT NULL, ArtistGuid VARCHAR(36), AlbumGuid VARCHAR(36), Guid VARCHAR(36) NOT NULL, LastTimePlayed DATETIME, MusicBrainzId VARCHAR(36), Duration VARCHAR(25), Cover VARCHAR(36), XmlData VARCHAR(1024), PRIMARY KEY (Guid))",
connection))
return command.ExecuteNonQueryAsync();
}
public async void SetComplete(Todo todo)
{
using (var conn = new SQLiteConnection("Data Source=TodoList.s3db"))
{
using (var cmd = new SQLiteCommand("UPDATE TODOs SET Done = 1 WHERE Id = " + todo.Id, conn))
{
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
}
}
public async Task<int> DeleteDataAsync(InventurItem item)
{
//using (var db = new InventurContext())
//{
// db.InventurItems.Attach(item);
// db.InventurItems.Remove(item);
// return await db.SaveChangesAsync();
//}
var command = new SQLiteCommand($"delete from {TABNAME} where ID={item.ID}", _dbTool.ConnectDb());
return await command.ExecuteNonQueryAsync();
}
public Task AddAlbum(Album album)
{
Collection.Add(album.Guid, album);
using (
var command =
new SQLiteCommand(
"INSERT INTO `Albums` (Name, Guid, Artists) VALUES (@name, @guid, @artists)",
_connection))
{
command.Parameters.AddWithValue("@name", album.Name);
command.Parameters.AddGuid("@guid", album.Guid);
command.Parameters.AddWithValue("@artists",
string.Join(",", album.Artists.Select(x => x.Guid.ToString("D"))));
return command.ExecuteNonQueryAsync();
}
}
public async void ConvertToSqlite(string pathToExcelFile)
{
SetPathToParentDirectoryOfDatabaseFile();
if (File.Exists(PathToDatabaseArchiveFile) && !File.Exists(_pathToDatabaseFile))
ZipFile.ExtractToDirectory(PathToDatabaseArchiveFile, _pathToDatabase);
using (
var dbSqLiteConnection =
new SQLiteConnection((WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString)))
{
//load data from xlsx(excel) file
var ds = await SetDataSet(pathToExcelFile);
await dbSqLiteConnection.OpenAsync();
//Set data from rows
for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
{
var rowsStringBuilder = new StringBuilder();
//load data from row to string
for (var j = 0; j < ds.Tables[0].Rows[i].ItemArray.Length; j++)
{
var row = string.IsNullOrEmpty(ds.Tables[0].Rows[i][j].ToString())
? "NULL"
: ds.Tables[0].Rows[i][j].ToString();
if (j < ds.Tables[0].Rows[i].ItemArray.Length - 1)
rowsStringBuilder.Append(row + ",");
else
rowsStringBuilder.Append(row);
}
//Insert data into table
var sqlQuery = "Insert into " + TableName + "(" + ColumnNames + ") Values(" + rowsStringBuilder + ");";
using (var cmd = new SQLiteCommand(sqlQuery, dbSqLiteConnection))
await cmd.ExecuteNonQueryAsync();
}
dbSqLiteConnection.Shutdown();
dbSqLiteConnection.Close();
}
if (File.Exists(PathToDatabaseArchiveFile))
File.Delete(PathToDatabaseArchiveFile);
ZipFile.CreateFromDirectory(_pathToDatabase, PathToDatabaseArchiveFile);
}
public async void AddTodo(Todo todo)
{
using (var conn = new SQLiteConnection("Data Source=TodoList.s3db"))
{
var sb = new StringBuilder();
sb.Append("INSERT INTO TODOs (Task, DueDate, CreateDate, Done) VALUES ('");
sb.Append(todo.Task);
sb.Append("', '");
sb.Append(todo.DueDate.Value.ToString("yyyy-MM-dd HH:mm"));
sb.Append("', '");
sb.Append(todo.CreateDate.ToString("yyyy-MM-dd HH:mm"));
sb.Append("', ");
sb.Append(todo.Done ? 1 : 0);
sb.Append(");");
Debug.WriteLine("Executing: " + sb.ToString());
using (var cmd = new SQLiteCommand(sb.ToString(), conn))
{
await conn.OpenAsync();
await cmd.ExecuteNonQueryAsync();
}
}
}
public Task AddTrack(PlayableBase track)
{
track.Guid = Guid.NewGuid();
Collection.Add(track.Guid, track);
Tracks.Add(track);
_imageProvider.AddImage(track.Cover);
using (
var command =
new SQLiteCommand(
"INSERT INTO `Tracks` (Title, ArtistGuid, AlbumGuid, Guid, LastTimePlayed, MusicBrainzId, Duration, Cover, XmlData) VALUES (@title, @artistGuid, @albumGuid, @guid, @lastTimePlayed, @musicBrainzId, @duration, @cover, @xmlData)",
_connection))
{
command.Parameters.AddWithValue("@title", track.Title);
command.Parameters.AddGuid("@artistGuid", track.Artist.Guid);
command.Parameters.AddGuid("@albumGuid", track.Album?.Guid);
command.Parameters.AddGuid("@guid", track.Guid);
command.Parameters.AddWithValue("@lastTimePlayed", track.LastTimePlayed.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddWithValue("@musicBrainzId", track.MusicBrainzId);
command.Parameters.AddWithValue("@duration", XmlConvert.ToString(track.Duration));
command.Parameters.AddGuid("@cover", track.Cover?.Guid);
using (var stringWriter = new StringWriter())
{
_serializer.Serialize(stringWriter, track);
command.Parameters.AddWithValue("@xmlData", stringWriter.ToString());
}
return command.ExecuteNonQueryAsync();
}
}
public async Task<bool> SetExportedAsync()
{
//using (var db = new InventurContext())
//{
// await db.InventurItems.Where(x => x.Exported == false).ForEachAsync<InventurItem>(y => y.Exported = true);
// var changes = await db.SaveChangesAsync();
// if (changes > 0) return true;
// return false;
//}
var command = new SQLiteCommand($"update {TABNAME} set Exported=1 where Exported=0", _dbTool.ConnectDb());
var changes = await command.ExecuteNonQueryAsync();
if (changes > 0) return true;
return false;
}
public Task UpdateLastTimePlayed(PlayableBase track, DateTime newLastTimePlayed)
{
track.LastTimePlayed = newLastTimePlayed;
using (var command = new SQLiteCommand("UPDATE `Tracks` SET LastTimePlayed=@newLastTimePlayed WHERE Guid=@guid", _connection))
{
command.Parameters.AddWithValue("@newLastTimePlayed", newLastTimePlayed.ToString("yyyy-MM-dd HH:mm:ss"));
command.Parameters.AddGuid("@guid", track.Guid);
return command.ExecuteNonQueryAsync();
}
}
public Task UpdateAlbumArtists(Album album)
{
using (var command = new SQLiteCommand("UPDATE `Albums` SET Artists=@artists WHERE Guid=@guid", _connection))
{
command.Parameters.AddWithValue("@artists",
string.Join(",", album.Artists.Select(x => x.Guid.ToString("D"))));
command.Parameters.AddGuid("@guid", album.Guid);
return command.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Edit an existing entry
/// </summary>
/// <param name="post"></param>
/// <returns>a post object</returns>
public async Task<Post> Update(Post post)
{
if (post == null)
{
return null;
}
bool result = false;
try
{
using (var conn = new SQLiteConnection(Connectionstring))
{
conn.Open();
string query = $"UPDATE {TableName} SET Title = '{post.Title}', Content = '{post.Content}' WHERE Id = {post.ID}";
using (SQLiteCommand cmd = new SQLiteCommand(query, conn))
{
await cmd.ExecuteNonQueryAsync();
result = true;
}
conn.Close();
}
}
//useless catch, I need to handle it properly, either log or display a meaningfull message to the UI
catch (SQLiteException ex)
{
Console.WriteLine(ex.ToString());
}
return result ? post : null;
}
public Task RemoveTrack(PlayableBase track)
{
Collection.Remove(track.Guid);
Tracks.Remove(track);
using (var command = new SQLiteCommand("DELETE FROM `Tracks` WHERE Guid=@guid", _connection))
{
command.Parameters.AddGuid("@guid", track.Guid);
return command.ExecuteNonQueryAsync();
}
}
public override void saveToDB()
{
if (imageCacheNew.Count == 0)
return;
Dictionary<String, Image> tempData;
try
{
tempData = new Dictionary<string, Image>(imageCacheNew);
imageCacheNew.Clear();
this.connect();
SQLiteCommand command = new SQLiteCommand(connection);
// only store new images
foreach (var imageData in tempData)
{
command.CommandText = String.Format("REPLACE INTO imageData (id, data) VALUES ('{0}', @0);", imageData.Key);
SQLiteParameter parameter = new SQLiteParameter("@0", System.Data.DbType.Binary);
parameter.Value = imageToByteArray(imageData.Value);
command.Parameters.Add(parameter);
command.ExecuteNonQueryAsync();
}
command.Dispose();
this.close();
tempData.Clear();
}
catch (Exception e)
{
this.writeLog(LogType.Warning, "Fehler beim speichern der Bilder auf der Datenbank", e);
}
}
public Task AddPlaylist(UserPlaylist playlist)
{
Playlists.Add(playlist);
PlaylistAdded?.Invoke(this, playlist);
using (
var command =
new SQLiteCommand(
"INSERT INTO `Playlists` (Name, Guid) VALUES (@name, @guid)", _connection))
{
command.Parameters.AddWithValue("@name", playlist.Name);
command.Parameters.AddGuid("@guid", playlist.Id);
return command.ExecuteNonQueryAsync();
}
}
public async Task RemovePlaylist(UserPlaylist playlist)
{
Playlists.Remove(playlist);
PlaylistRemoved?.Invoke(this, playlist);
using (
var command =
new SQLiteCommand(
"DELETE FROM `Playlists` WHERE Guid=@guid", _connection))
{
command.Parameters.AddGuid("@guid", playlist.Id);
await command.ExecuteNonQueryAsync();
}
using (
var command =
new SQLiteCommand(
"DELETE FROM `PlaylistTracks` WHERE PlaylistId=@guid", _connection))
{
command.Parameters.AddGuid("@guid", playlist.Id);
await command.ExecuteNonQueryAsync();
}
}
protected void ExecuteDatabaseQuery(String sql, Boolean async = false)
{
if (IsUnix())
{
try
{
using (SqliteConnection db = new SqliteConnection(ConnectionString))
{
db.Open();
using (SqliteCommand cmd = new SqliteCommand(sql, db))
{
if (async)
{
cmd.ExecuteNonQueryAsync();
}
else
{
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception)
{
// ignored
}
}
else
{
/*try
{*/
using (SQLiteConnection db = new SQLiteConnection(ConnectionString))
{
db.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sql, db))
{
if (async)
{
cmd.ExecuteNonQueryAsync();
}
else
{
cmd.ExecuteNonQuery();
}
}
}
/* }
catch (Exception)
{
// ignored
}*/
}
}
async static void BuildDatabase(string fileName, IEnumerable<Entry> entries)
{
try
{
ResourceExtractor.ExtractResourceToFile("awagame.sqlite.dll", "SQLite.Interop.dll");
}
catch
{
Console.WriteLine("[ERROR] Can not write SQLite library.");
}
try
{
File.Delete(fileName);
}
catch (IOException)
{
}
finally
{
SQLiteConnection.CreateFile(fileName);
}
SQLiteConnection database = new SQLiteConnection("Data Source=:memory:;Version=3;"); //use a memory database for speed purposes
SQLiteConnection disk = new SQLiteConnection("Data Source=" + fileName + ";Version=3;");
database.Open();
using (var sqlCommand = new SQLiteCommand(@"CREATE TABLE IF NOT EXISTS roms(
gamename TEXT,
romname TEXT,
size TEXT,
crc TEXT,
md5 TEXT,
sha1 TEXT PRIMARY KEY,
romID TEXT,
datName TEXT,
datSource TEXT,
datDate TEXT
)", database))
{
sqlCommand.ExecuteNonQuery();
}
foreach (Entry gameEntry in entries)
{
using (var sqlCommand = new SQLiteCommand(@"INSERT OR IGNORE INTO roms VALUES(
@gamename,
@romname,
@size,
@crc,
@md5,
@sha1,
@romID,
@datName,
@datSource,
@datDate)", database))
{
sqlCommand.Parameters.AddWithValue("@gamename", gameEntry.GameName);
sqlCommand.Parameters.AddWithValue("@romname", gameEntry.RomFileName);
sqlCommand.Parameters.AddWithValue("@size", gameEntry.RomSize);
sqlCommand.Parameters.AddWithValue("@crc", gameEntry.HashCRC32);
sqlCommand.Parameters.AddWithValue("@md5", gameEntry.HashMD5);
sqlCommand.Parameters.AddWithValue("@sha1", gameEntry.HashSHA1);
sqlCommand.Parameters.AddWithValue("@romID", gameEntry.OpenVGDB_RomID);
sqlCommand.Parameters.AddWithValue("@datName", gameEntry.DatName);
sqlCommand.Parameters.AddWithValue("@datSource", gameEntry.DatSource);
sqlCommand.Parameters.AddWithValue("@datDate", gameEntry.DatDate);
await sqlCommand.ExecuteNonQueryAsync();
if (Program.Verbose)
{
await Console.Out.WriteLineAsync(String.Format("[INFO] Added ROM record {0} with game record {1} (SHA {2})",
gameEntry.RomFileName, gameEntry.GameName, gameEntry.HashSHA1));
}
}
}
disk.Open();
Console.WriteLine("[INFO] Saving Database to " + fileName);
try
{
database.BackupDatabase(disk, "main", "main", -1, null, 0);
}
catch
{
Console.WriteLine("[ERROR] Could not save SQLite file (File in use?).");
}
Console.WriteLine("Saved to " + fileName);
disk.Close();
database.Close();
}
public Task AddArtist(Artist artist)
{
ArtistDictionary.Add(artist.Guid, artist);
_imageProvider.AddImage(artist.SmallImage);
_imageProvider.AddImage(artist.MediumImage);
_imageProvider.AddImage(artist.LargeImage);
using (
var command =
new SQLiteCommand(
"INSERT INTO `Artists` (Name, MusicBrainzId, Url, Guid, SmallImage, MediumImage, LargeImage) VALUES (@name, @musicBrainzId, @url, @guid, @smallImage, @mediumImage, @largeImage)",
_connection))
{
command.Parameters.AddWithValue("@name", artist.Name);
command.Parameters.AddWithValue("@musicBrainzId", artist.MusicBrainzId);
command.Parameters.AddWithValue("@url", artist.Url);
command.Parameters.AddGuid("@guid", artist.Guid);
command.Parameters.AddGuid("@smallImage", artist.SmallImage?.Guid);
command.Parameters.AddGuid("@mediumImage", artist.MediumImage?.Guid);
command.Parameters.AddGuid("@largeImage", artist.LargeImage?.Guid);
return command.ExecuteNonQueryAsync();
}
}
private Task AddImageRow(ImageProvider image, int id, string data)
{
Collection.Add(image.Guid, image);
using (
var command =
new SQLiteCommand(
"INSERT INTO `Images` (Type, Guid, Data) VALUES (@type, @guid, @data)",
_connection))
{
command.Parameters.AddWithValue("@type", id);
command.Parameters.AddGuid("@guid", image.Guid);
command.Parameters.AddWithValue("@data", data);
return command.ExecuteNonQueryAsync();
}
}
private async void Playlist_TrackRemoved(object sender, PlayableBase e)
{
using (
var command =
new SQLiteCommand(
"DELETE FROM `PlaylistTracks` WHERE PlaylistId=@playlistId AND TrackId=@trackId", _connection)
)
{
command.Parameters.AddGuid("@playlistId", ((UserPlaylist) sender).Id);
command.Parameters.AddGuid("@trackId", e.Guid);
await command.ExecuteNonQueryAsync();
}
}
private async void Playlist_TrackAdded(object sender, PlayableBase e)
{
using (
var command =
new SQLiteCommand(
"INSERT INTO `PlaylistTracks` (PlaylistId, TrackId) VALUES (@playlistId, @trackId)", _connection)
)
{
command.Parameters.AddGuid("@playlistId", ((UserPlaylist) sender).Id);
command.Parameters.AddGuid("@trackId", e.Guid);
await command.ExecuteNonQueryAsync();
}
}
public async Task<int> SaveDataAsync(InventurItem item, bool isNew = false)
{
//using (var db = new InventurContext())
//{
// item.ChangedAt = DateTime.Now;
// if (isNew)
// {
// item.CreatedAt = DateTime.Now;
// db.InventurItems.Add(item);
// }
// else
// {
// db.InventurItems.Attach(item);
// var entry = db.Entry(item);
// entry.Property(x => x.EANCode).IsModified = true;
// entry.Property(x => x.ChangedAt).IsModified = true;
// entry.Property(x => x.Amount).IsModified = true;
// }
// return await db.SaveChangesAsync();
//}
SQLiteCommand command;
item.ChangedAt = DateTime.Now;
if (isNew)
{
item.CreatedAt = DateTime.Now;
command = new SQLiteCommand($"insert into {TABNAME} (CreatedAt, ChangedAt, EANCode, Amout, Exported) values ({item.CreatedAt}, {item.ChangedAt}, {item.EANCode}, {item.Amount}, 0)", _dbTool.ConnectDb());
return await command.ExecuteNonQueryAsync();
}
else
{
command = new SQLiteCommand($"update {TABNAME} set EANCode={item.EANCode}, ChangedAt={item.ChangedAt}, Amount={item.Amount} where ID={item.ID}", _dbTool.ConnectDb());
return await command.ExecuteNonQueryAsync();
}
}
public static async Task Init()
{
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "qts.db");
string connectionString = new SQLiteConnectionStringBuilder { DataSource = path }.ToString();
bool isExist = File.Exists(path);
if (isExist)
{
Connection = new SQLiteConnection(connectionString);
Connection.Open();
}
else
{
SQLiteConnection.CreateFile(path);
Connection = new SQLiteConnection(connectionString);
Connection.Open();
var cmd = new SQLiteCommand(SQL_SCRIPT, Connection);
await cmd.ExecuteNonQueryAsync();
}
}
