/// <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 }); }
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> /// 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 progress state of the given task by id, and sets InProgress to false /// </summary> /// <param name="taskId"></param> /// <param name="completedState"></param> /// <returns></returns> public bool UpdateTaskCompletedState(Guid taskId, bool completedState) { var taskProps = NulahStandardLib.GetPropertiesForType <Task>(); var update = _sqliteProvider.Update <Task>(_currentTaskDatabase.DatabaseName, $"UPDATE [{nameof(Task)}] SET [{nameof(Task.IsComplete)}] = @{nameof(Task.IsComplete)}" + $", [{nameof(Task.UpdatedUTC)}] = @{nameof(Task.UpdatedUTC)}" + $", [{nameof(Task.InProgress)}] = @{nameof(Task.InProgress)}" + $" WHERE [{nameof(Task.Id)}] = @{nameof(Task.Id)}", new { Id = taskId, IsComplete = completedState, InProgress = false, UpdatedUTC = NulahStandardLib.DateTimeNow() }); LoadTasksForDatabase(_currentTaskDatabase); return(update); }
/// <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); } }