public async Task Load(string rootFolder)
{
var createTables = false;
if (!_databaseFile.Exists)
{
SQLiteConnection.CreateFile(_databaseFile.FullName);
createTables = true;
}
var dataProvider = new IDataProvider[] {Images, Artists, Albums, Tracks, Playlists};
_connection = new SQLiteConnection($"Data Source={_databaseFile.FullName};Version=3;");
await _connection.OpenAsync();
if(createTables)
foreach (var provider in dataProvider)
await provider.CreateTables(_connection);
foreach (var data in dataProvider)
await data.Load(_connection);
var userDataFileInfo = new FileInfo(Path.Combine(rootFolder, UserDataFilename));
if (userDataFileInfo.Exists)
await UserData.LoadFromFile(userDataFileInfo.FullName);
LoadSettings();
}
public async Task Clear()
{
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
await conn.ExecuteAsync("delete from [User]").ConfigureAwait(false);
}
}
public async Task<ObservableCollection<Todo>> GetTodos()
{
using (var conn = new SQLiteConnection("Data Source=TodoList.s3db"))
{
using (var cmd = new SQLiteCommand("SELECT * FROM TODOs", conn))
{
await conn.OpenAsync();
using (var reader = await cmd.ExecuteReaderAsync())
{
if (!reader.HasRows)
{
return new ObservableCollection<Todo>();
}
var todos = new ObservableCollection<Todo>();
Todo todo = null;
while (reader.Read())
{
todo = new Todo();
todo.Id = Convert.ToInt32(reader["Id"].ToString());
todo.CreateDate = Convert.ToDateTime(reader["CreateDate"].ToString());
todo.Task = reader["Task"].ToString();
todo.Done = Convert.ToBoolean(reader["Done"].ToString());
todo.DueDate = reader["DueDate"] == DBNull.Value ?
null as DateTime? : Convert.ToDateTime(reader["DueDate"].ToString());
todos.Add(todo);
}
return todos;
}
}
}
}
/// <summary>
/// Connects to db.
/// </summary>
public static async Task<IDbConnection> ConnectToDb(string dbPath, bool isReadOnly, bool enablePooling, int? cacheSize, ILogger logger)
{
if (string.IsNullOrEmpty(dbPath))
{
throw new ArgumentNullException("dbPath");
}
SQLiteConnection.SetMemoryStatus(false);
var connectionstr = new SQLiteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = cacheSize ?? 2000,
SyncMode = SynchronizationModes.Normal,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Wal,
// This is causing crashing under linux
Pooling = enablePooling && Environment.OSVersion.Platform == PlatformID.Win32NT,
ReadOnly = isReadOnly
};
var connectionString = connectionstr.ConnectionString;
if (!enablePooling)
{
logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, connectionString);
}
var connection = new SQLiteConnection(connectionString);
await connection.OpenAsync().ConfigureAwait(false);
return connection;
}
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<UserInfo> AddUser(string userLogin, string displayName)
{
var res = new UserInfo() {UserLogin = userLogin, DisplayName = displayName, Created = DateTime.UtcNow};
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
int count = await conn.ExecuteAsync("insert or ignore into [User] (Login, Display, Created) values(@userLogin, @displayName, @created)",
new {userLogin, displayName, created = res.Created }).ConfigureAwait(false);
if (count == 0)
throw new Exception($"The user '{userLogin}' already exists");
}
return res;
}
/// <summary>
/// Connects to DB.
/// </summary>
/// <param name="dbPath">The db path.</param>
/// <returns>Task{System.Boolean}.</returns>
/// <exception cref="System.ArgumentNullException">dbPath</exception>
protected Task ConnectToDb(string dbPath)
{
if (string.IsNullOrEmpty(dbPath))
{
throw new ArgumentNullException("dbPath");
}
DbFileName = dbPath;
var connectionstr = new SQLiteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = 40960,
SyncMode = SynchronizationModes.Off,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Wal
};
Connection = new SQLiteConnection(connectionstr.ConnectionString);
return Connection.OpenAsync();
}
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);
}
async Task LoadAsync()
{
Db = new SQLiteConnection($"Data Source={dbPath}");
await Db.OpenAsync();
var canContinue = await TryCreateTablesAsync();
if (!canContinue)
{
// If the expected schema can't be created, delete
// the cache file and start over.
Db.Dispose();
File.Delete(dbPath);
SQLiteConnection.CreateFile(dbPath);
Db = new SQLiteConnection($"Data Source={dbPath}");
await Db.OpenAsync();
// If table creation still fails, throw
canContinue = await TryCreateTablesAsync();
if (!canContinue)
{
throw new Exception("Unable to create cache database");
}
}
}
public static async Task<bool> InsertReadedFile(string path)
{
return await Task.Factory.StartNew(async () =>
{
using (var connection = new SQLiteConnection(ConnectionString))
{
await connection.OpenAsync();
try
{
var command = connection.CreateCommand();
command.CommandText = $"INSERT INTO readed_files(path) VALUES ('{path}');";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
return true;
}
catch (SQLiteException)
{
return false;
}
}
}).Result;
}
public async Task<UserMessage> AddMessage(string userLogin, string text)
{
var res = new UserMessage() {Text = text, UserLogin = userLogin, Created = DateTime.UtcNow};
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
int count = await conn.ExecuteAsync(
"insert or ignore into [Message] (Text, Created, UserId) select @text as Text, @created as Created, UserId from [User] where [Login] = @userLogin",
new {text, created = res.Created, userLogin}).ConfigureAwait(false);
if (count == 0)
{
int userId =
await
conn.ExecuteScalarAsync<int>(
"insert into [User] (Login, Created) values(@userLogin, @created); SELECT last_insert_rowid() FROM [User]",
new {userLogin, created = res.Created}).ConfigureAwait(false);
await conn.ExecuteAsync("insert into [Message] (Text, Created, UserId) values(@text, @created, @userId)", new { text, created = res.Created, userId}).ConfigureAwait(false);
}
}
return res;
}
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();
}
}
}
/// <summary>
/// Connects to db.
/// </summary>
/// <param name="dbPath">The db path.</param>
/// <param name="logger">The logger.</param>
/// <returns>Task{IDbConnection}.</returns>
/// <exception cref="System.ArgumentNullException">dbPath</exception>
public static async Task<IDbConnection> ConnectToDb(string dbPath, ILogger logger)
{
if (string.IsNullOrEmpty(dbPath))
{
throw new ArgumentNullException("dbPath");
}
logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, dbPath);
var connectionstr = new SQLiteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = 2000,
SyncMode = SynchronizationModes.Full,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Wal
};
var connection = new SQLiteConnection(connectionstr.ConnectionString);
await connection.OpenAsync().ConfigureAwait(false);
return connection;
}
/// <summary>
/// Connects to db.
/// </summary>
/// <param name="dbPath">The db path.</param>
/// <param name="logger">The logger.</param>
/// <returns>Task{IDbConnection}.</returns>
/// <exception cref="System.ArgumentNullException">dbPath</exception>
public static async Task<IDbConnection> ConnectToDb(string dbPath, ILogger logger)
{
if (string.IsNullOrEmpty(dbPath))
{
throw new ArgumentNullException("dbPath");
}
logger.Info("Opening {0}", dbPath);
#if __MonoCS__
var connectionstr = new SqliteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = 4096,
SyncMode = SynchronizationModes.Normal,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Off
};
var connection = new SqliteConnection(connectionstr.ConnectionString);
#else
var connectionstr = new SQLiteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = 4096,
SyncMode = SynchronizationModes.Normal,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Wal
};
var connection = new SQLiteConnection(connectionstr.ConnectionString);
#endif
await connection.OpenAsync().ConfigureAwait(false);
return connection;
}
/// <summary>
/// Runs the "ANALYZE;" SQL command on the database to update the statistics tables for better query performance
/// </summary>
/// <returns>Task that completes when the "ANALYZE;" command has finished</returns>
private async Task PerformDatabaseMaintenanceAsync()
{
ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Performing database maintenance...");
try
{
using (var connection = new SQLiteConnection(_connectionString))
{
await connection.OpenAsync();
using (var command = connection.CreateCommand())
{
command.CommandText = "ANALYZE;";
await command.ExecuteNonQueryAsync();
}
connection.Close();
}
}
catch (Exception e)
{
ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Error while performing database maintenance:", e);
}
ServiceRegistration.Get<ILogger>().Info("SQLiteDatabase: Database maintenance finished");
LogStatistics();
}
private async Task InitializeAsync()
{
if (_connection == null)
{
var builder = new SQLiteConnectionStringBuilder()
{
FailIfMissing = true,
ReadOnly = !this.IsWriteable,
DataSource = this.StorageLocation,
Version = 3,
UseUTF16Encoding = true,
};
_connection = new SQLiteConnection(builder.ToString());
await _connection.OpenAsync();
}
}
public async Task<DataPage<UserMessage>> GetUserMessages(string userLogin, int pageNumber, int pageSize)
{
ValidatePagination(pageNumber, pageSize);
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
if (await conn.ExecuteScalarAsync<int>("select count(*) from [User] where Login = @userLogin", new { userLogin }).ConfigureAwait(false) == 0)
return new DataPage<UserMessage>(pageNumber, pageSize, 0, null); //not found
int count = await conn.ExecuteScalarAsync<int>("select count(*) from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin", new {userLogin}).ConfigureAwait(false);
string q = "select u.Login as UserLogin, m.Text, m.Created from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin order by m.Created";
object prms;
if (pageSize == -1)
prms = new { userLogin };
else
{
q += " limit @size offset @skip";
prms = new { userLogin, size = pageSize, skip = (pageNumber - 1) * pageSize };
}
return
new DataPage<UserMessage>(pageNumber, pageSize, count,
(await conn.QueryAsync<UserMessage>(q, prms)).ToList());
}
}
private async void InitializeFromDatabase()
{
var sqlConStr = new SQLiteConnectionStringBuilder
{
DataSource = Settings.Default.Database,
//Password = Settings.Default.DatabasePwd,
DateTimeKind = DateTimeKind.Utc,
ForeignKeys = true
};
var sqlCon = new SQLiteConnection(sqlConStr.ToString());
await sqlCon.OpenAsync().ContinueWith(t =>
{
if (!t.IsCompleted)
{
return;
}
var sqlCmd = new SQLiteCommand("SELECT * FROM [AppSeriesTable] ORDER BY AppSeriesID ASC", sqlCon);
using (var sqlReader = sqlCmd.ExecuteReader())
{
AppSeries.BeginBulkOperation();
while (sqlReader.Read())
{
AppSeries.Add(new AppSeries
{
AppSeriesID = sqlReader.GetInt32(0),
AppSeriesName = sqlReader.GetString(1),
AppSeriesFriendlyDescription = sqlReader.GetString(2)
});
}
AppSeries.EndBulkOperation();
}
sqlCmd = new SQLiteCommand("SELECT * FROM [AppBranchesTable] ORDER BY AppBranchID ASC", sqlCon);
using (var sqlReader = sqlCmd.ExecuteReader())
{
IAppSeries series = null;
AppBranches.BeginBulkOperation();
while (sqlReader.Read())
{
var seriesId = sqlReader.GetInt32(0);
if (series == null || series.AppSeriesID != seriesId)
{
series = AppSeries.FirstOrDefault(s => s.AppSeriesID == seriesId);
}
var branch = new AppBranch
{
AppSeries = series,
AppBranchID = sqlReader.GetInt32(1),
AppBranchName = sqlReader.GetString(2),
AppBranchFriendlyDescription = sqlReader.GetString(3)
};
AppBranches.Add(branch);
series?.ChildBranches.Add(branch);
}
AppBranches.EndBulkOperation();
}
sqlCmd = new SQLiteCommand("SELECT * FROM [AppUpdateClientsTable] ORDER BY AppBrancheID ASC", sqlCon);
using (var sqlReader = sqlCmd.ExecuteReader())
{
IAppBranch branch = null;
ClientInfos.BeginBulkOperation();
while (sqlReader.Read())
{
var branchId = sqlReader.GetInt32(3);
if (branch == null || branch.AppBranchID != branchId)
{
branch = AppBranches.FirstOrDefault(b => b.AppBranchID == branchId);
}
IPAddress ipAddress;
if (sqlReader.IsDBNull(4) || !IPAddress.TryParse(sqlReader.GetString(4), out ipAddress))
{
ipAddress = IPAddress.None;
}
var client = new ClientInfo
{
MachineID = sqlReader.GetString(0),
ClientName = sqlReader.GetString(1),
Company = sqlReader.GetString(2),
AppBranch = branch,
IPAddress = ipAddress,
RsaPrivateKey = sqlReader.GetString(5),
Expiration = sqlReader.GetDateTime(6),
Serial = sqlReader.GetString(7),
SetupLocation = sqlReader.GetString(8)
};
ClientInfos.Add(client);
branch?.ChildClients.Add(client);
}
ClientInfos.BeginBulkOperation();
}
}).ContinueWith(t => sqlCon.Close());
}
/// <exception cref="ArgumentNullException">The exception that is thrown when the <paramref name="function" /> argument is null.</exception>
public static async Task<bool> RemoveReadedFile(string path)
{
return await Task.Factory.StartNew(async () =>
{
using (var connection = new SQLiteConnection(ConnectionString))
{
await connection.OpenAsync();
try
{
var command = connection.CreateCommand();
command.CommandText = $"DELETE FROM readed_files WHERE path = '{path}';";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
return true;
}
catch (SQLiteException)
{
return false;
}
}
}).Result;
}
public async Task<int> RemoveUser(string userLogin)
{
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
return await conn.ExecuteAsync("delete from [User] where [Login] = @userLogin", new {userLogin}).ConfigureAwait(false);
}
}
/// <summary>
/// Connects to db.
/// </summary>
/// <param name="dbPath">The db path.</param>
/// <returns>Task{IDbConnection}.</returns>
/// <exception cref="System.ArgumentNullException">dbPath</exception>
public static async Task<SQLiteConnection> ConnectToDb(string dbPath)
{
if (string.IsNullOrEmpty(dbPath))
{
throw new ArgumentNullException("dbPath");
}
var connectionstr = new SQLiteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = 4096,
SyncMode = SynchronizationModes.Normal,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Wal
};
var connection = new SQLiteConnection(connectionstr.ConnectionString);
await connection.OpenAsync().ConfigureAwait(false);
return connection;
}
public static async Task<bool> ClearDb()
{
return await Task.Factory.StartNew(async () =>
{
using (var connection = new SQLiteConnection(ConnectionString))
{
await connection.OpenAsync();
try
{
var command = connection.CreateCommand();
command.CommandText = "DELETE FROM readed_files WHERE id >= 0;";
command.ExecuteNonQuery();
return true;
}
catch (SQLiteException)
{
return false;
}
}
}).Result;
}
public async Task<DataPage<UserInfo>> GetUsers(int pageNumber, int pageSize)
{
ValidatePagination(pageNumber, pageSize);
using (var conn = new SQLiteConnection(_connectionString))
{
await conn.OpenAsync().ConfigureAwait(false);
int count = await conn.ExecuteScalarAsync<int>("select count(*) from [User]").ConfigureAwait(false);
string q = "select [Login] as UserLogin, Display as DisplayName, Created from [User] order by [Login]";
object prms;
if (pageSize == -1)
prms = null;
else
{
q += " limit @size offset @skip";
prms = new { size = pageSize, skip = (pageNumber - 1) * pageSize };
}
return
new DataPage<UserInfo>(pageNumber, pageSize, count,
(await conn.QueryAsync<UserInfo>(q, prms).ConfigureAwait(false)).ToList());
}
}
