private static Task CreateDatabase (SQLiteAsyncConnection connection, CancellationToken cancellationToken)
{
return Task.Factory.StartNew(() =>
{
//Create the tables
var createTask = connection.CreateTablesAsync (tableTypes);
createTask.Wait();
//Count number of assignments
var countTask = connection.Table<Assignment>().CountAsync();
countTask.Wait();
//If no assignments exist, insert our initial data
if (countTask.Result == 0)
{
var insertTask = connection.InsertAllAsync(TestData.All);
//Wait for inserts
insertTask.Wait();
//Mark database created
initialized = true;
}
});
}
private async void Configure()
{
try
{
await _connection.CreateTablesAsync <RssFeed, WeatherLocations, SmartMirror, QuadrantSettings, GoogleEntity>();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
async void RecreateDb (SQLiteAsyncConnection connection, bool init)
{
await connection.CreateTablesAsync<Ticket, User> ();
if (init) {
await connection.InsertAllAsync (new [] {
new User{ Name = "Dmitry Reshetnik", CreationDateTime = DateTime.UtcNow },
new User{ Name = "Oleg Tyshchenko", CreationDateTime = DateTime.UtcNow },
});
}
}
/// <summary>
/// Initialise the Database connection and creates tables/default values if not existant.
/// </summary>
private async void ConnectionInit()
{
//Connection init
var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
Database = new SQLite.SQLiteAsyncConnection(dbPath);
//Create tables if not exists
await Database.CreateTablesAsync(new Account().GetType(), new DatabaseRoute().GetType(), new DatabasePOI().GetType());
await Database.ExecuteAsync("create table if not exists \"RouteBinds\"(\"RouteID\" integer,\"WaypointID\" integer);", new object[] { });
//Set default Admin Admin password
var result = await Database.ExecuteScalarAsync<String>("Select Gebruikersnaam From Account WHERE Gebruikersnaam = ? AND Password = ?", new object[] { "Admin", "Admin" });
if(result == null)
await Database.InsertAsync(new Account("Admin", "Admin"));
}
//TODO:remove method
public static async Task CreateDataBaseAsync(this Activity context, int taskCount, int bonusCount)
{
var path = Rep.Instance.DataBaseFile;
var connection = new SQLiteAsyncConnection(path);
//File.Delete(path);
if (!File.Exists(path))
{
var results = await connection.CreateTablesAsync<TaskTable, BonusTable, CategoryTable>();
if (results.Results.Count == 3)
{
var tasks = GetTasks(context.Assets.Open("Task.csv"));
var bonuses = GetBonus(context.Assets.Open("Bonus.csv"));
var category = GetCategory(context.Assets.Open("Category.csv"));
await connection.InsertAllAsync(tasks);
await connection.InsertAllAsync(bonuses);
await connection.InsertAllAsync(category);
}
}
await Rep.Instance.TaskGenerateAsync(taskCount);
await Rep.Instance.BonusGenerateAsync(bonusCount);
}
public async Task<Dictionary<Type, int>> CreateTables() {
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(this.dbPath);
var result = await conn.CreateTablesAsync<Visit, Friend, Item>();
return result.Results;
}
public static async Task<bool> UpdateDataAsync()
{
bool updated = false;
WebHelper webHelper = new WebHelper();
if (await webHelper.getAllData())
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\Data.db");
await conn.DropTableAsync<Term>();
await conn.DropTableAsync<Lesson>();
await conn.DropTableAsync<Exam>();//删除之前的数据
await conn.DropTableAsync<Course>();//不提示成绩更新情况下，直接删除成绩
await conn.CreateTablesAsync<Course, Term, Lesson, Exam>();
await conn.InsertAllAsync(webHelper.Exams);//update exams;
await conn.InsertAllAsync(webHelper.Terms);
await conn.InsertAllAsync(webHelper.Lessons);
await conn.InsertAllAsync(webHelper.Courses);
Term.ClearData();
Exam.ClearData();
WeekDay.ClearData();
updated = true;
}
return updated;
////区分有无成绩更新，方便后续扩展出成绩提示
//var previousCourses = terms.SelectMany(term => term.Courses);
//var updateCourses = from course in webHelper.Courses
// let previousCourse = from tempCourse in previousCourses
// where tempCourse.CourseName == course.CourseName &&
// tempCourse.TermNumber == course.TermNumber &&
// (tempCourse.GradePoints != course.GradePoints | tempCourse.MakeUpExamGrades != course.MakeUpExamGrades)
// select tempCourse
// where previousCourse.Any()
// select course;
//var newCourses = from course in webHelper.Courses
// let previousCourse = from tempCourse in previousCourses
// where tempCourse.CourseName == course.CourseName &&
// tempCourse.TermNumber == course.TermNumber
// select tempCourse
// where !previousCourse.Any()
// select course;
//var updatedCourse = updateCourses.ToList();
// var newcourses = newCourses.ToList();
//if (updateCourses.Any() | newCourses.Any())
//{
// try
// {
// await conn.InsertAllAsync(newCourses);
// await conn.UpdateAllAsync(updateCourses);
// }
// catch (Exception)
// {
// await conn.DropTableAsync<Course>();
// var message = new Windows.UI.Popups.MessageDialog("更新本地数据出错，建议或重试，或进入设置删除本地账号和数据或重启应用后再导入");
// await message.ShowAsync();
// }
// updated = true;
//}
}
//public Term(string termName, string termNumber, float averagePoints, float termTotalCredits, float termCommonClassCredits,
// float termHILClassCredits)
//{
// this.TermName = termName;
// this.TermNumber = termNumber;
// this.AveragePoints = averagePoints;
// this.TermTotalCredits = termTotalCredits;
// this.TermCommonClassCredits = termCommonClassCredits;
// this.TermHILClassCredits = termHILClassCredits;
//}
public static async Task<List<Term>> GetTermsAsync()
{
if (terms.Count == 0)
{
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\Data.db");
await conn.CreateTablesAsync<Course, Term>();
terms = await conn.Table<Term>().OrderBy(term => term.TermNumber).ToListAsync();
statistics[0] = statistics[1] = statistics[2] = statistics[3] = 0;
// the following foreach and for cannot be put together, for Async problem,there will occur a mixup for i;
foreach (var term in terms)
{
statistics[0] += term.TermTotalCredits;
statistics[1] += term.AveragePoints * term.TermTotalCredits;
statistics[2] += term.TermCommonClassCredits;
statistics[3] += term.TermHILClassCredits;
}
for (int i = 0; i < terms.Count; i++)
{
var term = terms[i];
term.Courses = await conn.Table<Course>().Where(course => course.TermNumber == term.TermNumber).ToListAsync();
}
}
return terms;
}
public static async Task<List<Term>> GetTermsAsync()
{
if (terms.Count != 0)
{
return terms;
}
SQLiteAsyncConnection conn = new SQLiteAsyncConnection(Windows.Storage.ApplicationData.Current.LocalFolder.Path + "\\Data.db");
await conn.CreateTablesAsync<Course, Term>();
terms = await conn.Table<Term>().OrderBy(term => term.TermNumber).ToListAsync();
statistics[0]= statistics[1] = statistics[2] = statistics[3] =0;
foreach (var term in terms)
{
statistics[0] += term.TermTotalCredits;
statistics[1] += term.AveragePoints* term.TermTotalCredits;
statistics[2] += term.TermCommonClassCredits;
statistics[3] += term.TermHILClassCredits;
}
foreach (var term in terms)
{
term.Courses = await conn.Table<Course>().Where(course => course.TermNumber == term.TermNumber).ToListAsync();
}
return terms;
}
