private static void recomputeYesterday(FileAssetType file_type) { var label_id = (file_type == FileAssetType.image) ? Settings.Default.LabelPhotoYesterday : Settings.Default.LabelVideoYesterday; var yes_start = DateTime.Now.AddDays(-1.0).TrimToDay(); var yes_end = DateTime.Now.TrimToDay(); using (var db = new MyDbContext()) { var actual = (from f in db.Object.Files where f.event_time >= yes_start && f.event_time < yes_end && f.type == (int)file_type select f.file_id).Union( from f in db.Object.PendingFiles where f.event_time >= yes_start && f.event_time < yes_end && f.type == (int)file_type select f.file_id ).ToList(); var current = (from f in db.Object.LabelFiles where f.label_id == label_id select f).ToList(); var areCurrentAndActualIdentical = actual.Count == current.Count && actual.TrueForAll(x => current.Where(y => y.file_id == x).Any()); if (!areCurrentAndActualIdentical) { foreach (var cur in current) { db.Object.LabelFiles.Remove(cur); } foreach (var act in actual) { db.Object.LabelFiles.Add(new LabeledFile { file_id = act, label_id = label_id }); } var lbRecord = (from lb in db.Object.Labels where lb.label_id == label_id select lb).First(); lbRecord.seq = SeqNum.GetNextSeq(); db.Object.SaveChanges(); } } }
private static void updateLabelSeq(SQLiteConnection db, IEnumerable <Guid> labels) { using (var cmd = db.CreateCommand()) { cmd.CommandText = "update [labels] set seq = @seq where label_id = @label_id"; cmd.Prepare(); foreach (var label in labels) { cmd.Parameters.Clear(); cmd.Parameters.Add(new SQLiteParameter("@seq", SeqNum.GetNextSeq())); cmd.Parameters.Add(new SQLiteParameter("@label_id", label)); cmd.ExecuteNonQuery(); } } }
private static void copyToFilesTable(List <FileData> data, SQLiteConnection db) { using (var cmd = db.CreateCommand()) { cmd.CommandText = "insert into Files (file_id, file_name, file_path, file_size, saved_path, parent_folder, device_id, type, event_time, seq, deleted, thumb_ready, width, height, orientation) " + "select file_id, file_name, file_path, file_size, @saved, @parent, device_id, type, event_time, @seq, deleted, thumb_ready, width, height, orientation from [PendingFiles] " + "where file_id = @fid"; cmd.Prepare(); foreach (var file in data) { cmd.Parameters.Clear(); cmd.Parameters.Add(new SQLiteParameter("@saved", file.saved_path)); cmd.Parameters.Add(new SQLiteParameter("@parent", file.parent_folder)); cmd.Parameters.Add(new SQLiteParameter("@fid", file.file_id)); cmd.Parameters.Add(new SQLiteParameter("@seq", SeqNum.GetNextSeq())); cmd.ExecuteNonQuery(); } } }