/// <summary>
/// Creates a task database in the given location, if a project database already exists, false is returned
/// <para>Regardless of database creation, the datasource will be registered and available</para>
/// </summary>
/// <param name="projectName"></param>
/// <param name="projectLocation"></param>
public bool CreateProjectDatabase(string projectName, string projectLocation)
{
var databaseLocation = Path.Combine(projectLocation, $"{projectName}.{NULAH_DB_EXTENSION}");
var databaseCreated = _sqliteProvider.CreateOrRegisterDataSource(projectName, databaseLocation);
// Track the new task list database in the application settings database if this is the first time we're creating it
if (databaseCreated == true)
{
_sqliteProvider.CreateTable <Task>(projectName);
// Create metadata
_sqliteProvider.CreateTable <NulahDBMeta>(projectName);
var nulahDBMetaPropertyList = NulahStandardLib.GetPropertiesForType <NulahDBMeta>();
var dbMetadata = new NulahDBMeta
{
IsProjectDatabase = true,
ProjectName = projectName,
ProjectOriginalLocation = projectLocation,
TaskListName = projectName // TODO: Maybe add override to this later for project task lists?
};
_sqliteProvider.Insert <NulahDBMeta>(projectName,
$"INSERT INTO [{nameof(NulahDBMeta)}] ({string.Join(", ", nulahDBMetaPropertyList.Select(x => $"[{ x.Name}]"))}) VALUES ({string.Join(",", nulahDBMetaPropertyList.Select(x => $"@{x.Name}"))})",
dbMetadata
);
CreateDatabaseEntry(databaseLocation);
}
return(databaseCreated);
}
/// <summary>
/// Takes the results of an SQLite query, and serialises into a List <typeparamref name="T"/>
/// <para>This method always returns a list object, so null checks are not required</para>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
private List <T> ReaderToType <T>(SQLiteDataReader reader)
{
var typeProps = NulahStandardLib.GetPropertiesForType <T>();
var l = new List <T>();
var dt = new DataTable();
dt.Load(reader);
foreach (DataRow dr in dt.Rows)
{
var o = Activator.CreateInstance <T>();
foreach (var prop in typeProps)
{
if (dr.Table.Columns.Contains(prop.Name))
{
var rowValue = dr[prop.Name];
if (rowValue != DBNull.Value)
{
SetValueOnObject(o, prop.Name, rowValue, prop.ValueType);
}
}
}
l.Add(o);
}
return(l);
}
/// <summary>
/// Creates a record of a created nulah database location given in the global app settings database
/// </summary>
/// <param name="databaseLocation"></param>
public void CreateDatabaseEntry(string databaseLocation)
{
var appSettingsPropertyList = NulahStandardLib.GetPropertiesForType <Database>();
_sqliteProvider.Insert <Database>(APP_SETTINGS_DB_DATASOURCE_NAME,
$"INSERT INTO [{nameof(Database)}] ({string.Join(", ", appSettingsPropertyList.Select(x => $"[{ x.Name}]"))}) VALUES ({string.Join(",", appSettingsPropertyList.Select(x => $"@{x.Name}"))})",
new Database
{
Id = Guid.NewGuid(),
CreatedUTC = NulahStandardLib.DateTimeNow(),
LastKnownLocation = databaseLocation
});
}
/// <summary>
/// Queries the database from the given data store key, returning all results found.
/// <para>List will be empty if no results are found</para>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataStore"></param>
/// <param name="query"></param>
/// <param name="queryParameters"></param>
/// <returns></returns>
public List <T> Query <T>(string dataStore, string query, object queryParameters)
{
using (var conn = GetConnection(dataStore))
{
conn.Open();
using (var cmd = new SQLiteCommand(query, conn))
{
var objectParams = NulahStandardLib.GetPropertiesAndValuesForObject(queryParameters);
foreach (var param in objectParams)
{
AddValueToQueryParameters(param.Key, param.Value, cmd.Parameters);
}
var q = cmd.ExecuteReader();
return(ReaderToType <T>(q));
}
}
}
public bool CreateTask(string title, string content)
{
var taskProps = NulahStandardLib.GetPropertiesForType <Task>();
var insert = _sqliteProvider.Insert <Task>(_currentTaskDatabase.DatabaseName,
$"INSERT INTO [{nameof(Task)}] ({string.Join(", ", taskProps.Select(x => $"[{ x.Name}]"))}) VALUES ({string.Join(",", taskProps.Select(x => $"@{x.Name}"))})",
new Task
{
Content = content,
Title = title,
CreatedUTC = NulahStandardLib.DateTimeNow(),
Id = Guid.NewGuid()
});
LoadTasksForDatabase(_currentTaskDatabase);
return(insert);
}
/// <summary>
/// Updates the progress state of the given task by id, and sets IsComplete to false
/// </summary>
/// <param name="taskId"></param>
/// <param name="progressState"></param>
/// <returns></returns>
public bool UpdateTaskProgressState(Guid taskId, bool progressState)
{
var update = _sqliteProvider.Update <Task>(_currentTaskDatabase.DatabaseName,
$"UPDATE [{nameof(Task)}] SET [{nameof(Task.InProgress)}] = @{nameof(Task.InProgress)}" +
$", [{nameof(Task.UpdatedUTC)}] = @{nameof(Task.UpdatedUTC)}" +
$", [{nameof(Task.IsComplete)}] = @{nameof(Task.IsComplete)}" +
$" WHERE [{nameof(Task.Id)}] = @{nameof(Task.Id)}",
new
{
Id = taskId,
InProgress = progressState,
IsComplete = false,
UpdatedUTC = NulahStandardLib.DateTimeNow()
});
LoadTasksForDatabase(_currentTaskDatabase);
return(update);
}
/// <summary>
/// Creates a table of given type <typeparamref name="T"/>, for the given datasource name
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataSource"></param>
/// <returns></returns>
public bool CreateTable <T>(string dataSource)
{
var typeDetails = NulahStandardLib.GetPropertiesForType <T>();
var columnDefinitions = typeDetails.Select(x => $"[{x.Name}] {TypeToSQLType(x.ValueType)} {(x.IsNullableType ? string.Empty : "NOT NULL")}");
var createQuery = $"CREATE table IF NOT EXISTS [{typeof(T).Name}] ({string.Join(",", columnDefinitions)});";
using (var conn = GetConnection(dataSource))
{
conn.Open();
using (var cmd = new SQLiteCommand(createQuery, conn))
{
var res = cmd.ExecuteNonQuery();
}
}
return(false);
}
/// <summary>
/// Updates the value in the given database by its data store key
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dataStore"></param>
/// <param name="query"></param>
/// <param name="queryParameters"></param>
/// <returns></returns>
public bool Update <T>(string dataStore, string query, object queryParameters)
{
using (var conn = GetConnection(dataStore))
{
conn.Open();
using (var cmd = new SQLiteCommand(query, conn))
{
var objectParams = NulahStandardLib.GetPropertiesAndValuesForObject(queryParameters);
foreach (var param in objectParams)
{
AddValueToQueryParameters(param.Key, param.Value, cmd.Parameters);
}
var update = cmd.ExecuteNonQuery();
if (update > 0)
{
return(true);
}
}
}
return(false);
}
/// <summary>
/// Create a database in the users %appdata%
/// </summary>
/// <param name="datasourceName"></param>
/// <param name="databaseName"></param>
public void CreateAppDataDatabase(string datasourceName, string databaseName, NulahDBMeta dbMetadata)
{
var databaseLocation = Path.Combine(_applicationDataLocation, $"{databaseName}.{NULAH_DB_EXTENSION}");
var databaseCreated = _sqliteProvider.CreateOrRegisterDataSource(datasourceName, databaseLocation);
// Track the new task list database in the application settings database if this is the first time we're creating it
if (databaseCreated == true)
{
_sqliteProvider.CreateTable <Task>(datasourceName);
// Create metadata
_sqliteProvider.CreateTable <NulahDBMeta>(datasourceName);
var nulahDBMetaPropertyList = NulahStandardLib.GetPropertiesForType <NulahDBMeta>();
_sqliteProvider.Insert <NulahDBMeta>(datasourceName,
$"INSERT INTO [{nameof(NulahDBMeta)}] ({string.Join(", ", nulahDBMetaPropertyList.Select(x => $"[{ x.Name}]"))}) VALUES ({string.Join(",", nulahDBMetaPropertyList.Select(x => $"@{x.Name}"))})",
dbMetadata
);
CreateDatabaseEntry(databaseLocation);
}
}
