//Save modified file record public void saveModifiedFileRecords(Files fi) { using (var transaction = dbConnection.BeginTransaction()) { string[] tags = fi.Tags.Split(new string[] { ", ", "," }, StringSplitOptions.RemoveEmptyEntries); // For remove other empty lines i.e. " ". for (int i = 0; i < tags.Length; i++) { tags[i] = tags[i].Trim(); } tags = tags.Where(val => !string.IsNullOrEmpty(val)).ToArray(); List<string> existLinkedTagsName = new List<string>(); List<int> existLinkedTagsId = new List<int>(); // Find exist and linked tags. SQLiteCommand sqlcf = new SQLiteCommand(@"SELECT t.* FROM tags t LEFT JOIN file_tag ft ON t.'id' = ft.'tags_id' WHERE t.name IN ({names}) AND ft.'files_id' = $files_id;", dbConnection); sqlcf.Parameters.AddWithValue("$files_id", fi.Id); sqlcf.AddArrayParameters("names", tags); SQLiteDataReader sqlcfr = sqlcf.ExecuteReader(); while (sqlcfr.Read()) { existLinkedTagsId.Add(Convert.ToInt32(sqlcfr["id"])); existLinkedTagsName.Add(Convert.ToString(sqlcfr["name"])); } string[] linkedTagsDiff = new string[0]; // Gets deleted and new tags. linkedTagsDiff = tags.Except(existLinkedTagsName.ToArray()).ToArray(); if (existLinkedTagsId.Count > 0) { // Search and delete those records which was removed from the Files' tags. SQLiteCommand sqlcdFileTag = new SQLiteCommand(@"DELETE FROM file_tag WHERE files_id = $files_id AND tags_id NOT IN ({tags_id});", dbConnection); sqlcdFileTag.Parameters.AddWithValue("$files_id", fi.Id); sqlcdFileTag.AddArrayParameters("tags_id", existLinkedTagsId.ToArray()); sqlcdFileTag.ExecuteNonQuery(); } else { // Delete all tags, if there's no match the previous ones. SQLiteCommand sqlcdFileTag = new SQLiteCommand(@"DELETE FROM file_tag WHERE files_id = $files_id;", dbConnection); sqlcdFileTag.Parameters.AddWithValue("$files_id", fi.Id); sqlcdFileTag.ExecuteNonQuery(); } List<string> existTagsName = new List<string>(); List<int> existTagsId = new List<int>(); // Get's not linked exist tags. SQLiteCommand sqlcft = new SQLiteCommand(@"SELECT * FROM tags WHERE name IN ({name})", dbConnection); sqlcft.AddArrayParameters("name", linkedTagsDiff); SQLiteDataReader sqlcftr = sqlcft.ExecuteReader(); while (sqlcftr.Read()) { existTagsId.Add(Convert.ToInt32(sqlcftr["id"])); existTagsName.Add(Convert.ToString(sqlcftr["name"])); } string[] tagsDiff = new string[0]; // Gets new tags. tagsDiff = linkedTagsDiff.Except(existTagsName.ToArray()).ToArray(); if (tagsDiff.Length > 0) { foreach (string item in tagsDiff) { // Insert new tag name. SQLiteCommand sqlci = new SQLiteCommand(@"INSERT INTO tags (name) VALUES ($name);", dbConnection); sqlci.Parameters.AddWithValue("$name", item); int a = sqlci.ExecuteNonQuery(); // Get last insert id. SQLiteCommand Command = new SQLiteCommand(@"select last_insert_rowid()", dbConnection); long lastId = (long)Command.ExecuteScalar(); // Add new insert id to collection. existTagsId.Add(Convert.ToInt32(lastId)); } } if (existTagsId.Count > 0) { foreach (int item in existTagsId) { // Add newly added tags to file. SQLiteCommand sqlci = new SQLiteCommand(@"INSERT INTO file_tag (files_id, tags_id) VALUES ($files_id, $tags_id);", dbConnection); sqlci.Parameters.AddWithValue("$files_id", fi.Id); sqlci.Parameters.AddWithValue("$tags_id", item); int a = sqlci.ExecuteNonQuery(); } } // Search and delete those records which doesn't have pair in file_tag table form tags table. SQLiteCommand sqlcd = new SQLiteCommand(@"DELETE FROM tags WHERE id NOT IN ( SELECT tags_id FROM file_tag );", dbConnection); sqlcd.ExecuteNonQuery(); if (fi.Year == "") { SQLiteCommand sqlc = new SQLiteCommand(@"UPDATE files SET title = $title, author = $author, year = null, doi = $doi, favorite = $favorite WHERE id = $id", dbConnection); sqlc.Parameters.AddWithValue("$id", fi.Id); sqlc.Parameters.AddWithValue("$title", fi.Title); sqlc.Parameters.AddWithValue("$author", fi.Author); sqlc.Parameters.AddWithValue("$doi", fi.Doi); sqlc.Parameters.AddWithValue("$favorite", fi.Favorite); sqlc.ExecuteNonQuery(); } else { SQLiteCommand sqlc = new SQLiteCommand(@"UPDATE files SET title = $title, author = $author, year = $year, doi = $doi, favorite = $favorite WHERE id = $id", dbConnection); sqlc.Parameters.AddWithValue("$id", fi.Id); sqlc.Parameters.AddWithValue("$title", fi.Title); sqlc.Parameters.AddWithValue("$author", fi.Author); sqlc.Parameters.AddWithValue("$year", fi.Year); sqlc.Parameters.AddWithValue("$doi", fi.Doi); sqlc.Parameters.AddWithValue("$favorite", fi.Favorite); sqlc.ExecuteNonQuery(); } transaction.Commit(); } }
// This finds all files, which have 'Biztosítás' and 'pdf' tags on it. //Select * //From files As f //Where Exists ( // Select 1 // From file_tag As ft // LEFT JOIN tags t ON t.id = ft.tags_id // Where t.name In('Biztosítás','pdf') --insert tags here, it can't be empty! // And ft.files_id = f.Id // Group By ft.files_id // Having Count(*) = 2 --tags no. // ) --insert addicional condition here // public List<Files> getASFiles(ASCriteria criteria) { List<Files> files = new List<Files>(); string where = ""; string command = @"SELECT f.*, group_concat(t.name, ', ') AS tags_name FROM files f LEFT JOIN file_tag ft ON ft.'files_id' = f.'id' LEFT JOIN tags t ON ft.'tags_id' = t.'id'"; if (criteria.Title != "") { where += "and f.title LIKE $title "; } if (criteria.Author != "") { where += "and f.author LIKE $author "; } if (criteria.Doi != "") { where += "and f.doi LIKE $doi "; } if (criteria.Tags != "") { where += @"and Exists ( Select 1 From file_tag As ft LEFT JOIN tags t ON t.id = ft.tags_id Where t.name In ({name_tags}) And ft.files_id = f.Id Group By ft.files_id Having Count(*) = $name_tags_count )"; } if (criteria.YearFrom != 0) { where += "and f.year >= $yearFrom "; } if (criteria.YearTo != 0) { where += "and f.year <= $yearTo "; } if (criteria.AddedFrom > DateTime.MinValue) { where += "and f.added >= $addedFrom "; } if (criteria.AddedTo > DateTime.MinValue) { where += "and f.added <= $addedTo "; } if (criteria.Notes != "") { where += "and f.note LIKE $note "; } if (criteria.Favorite == 1) { where += "and f.favorite = 1 "; } else if (criteria.Favorite == 0) { where += "and f.favorite = 0 "; } if (where != "") { where = " WHERE " + where.Remove(0, 4); command += where; } command += @" GROUP BY f.id"; SQLiteCommand sqlc = new SQLiteCommand(command, dbConnection); if (criteria.Title != "") { sqlc.Parameters.AddWithValue("$title", criteria.Title); } if (criteria.Author != "") { sqlc.Parameters.AddWithValue("$author", criteria.Author); } if (criteria.Doi != "") { sqlc.Parameters.AddWithValue("$doi", criteria.Doi); } if (criteria.Tags != "") { string[] tags = criteria.Tags.Split(new string[] { ", ", "," }, StringSplitOptions.RemoveEmptyEntries); sqlc.AddArrayParameters("name_tags", tags); sqlc.Parameters.AddWithValue("$name_tags_count", tags.Length); } if (criteria.YearFrom != 0) { sqlc.Parameters.AddWithValue("$yearFrom", criteria.YearFrom); } if (criteria.YearTo != 0) { sqlc.Parameters.AddWithValue("$yearTo", criteria.YearTo); } if (criteria.AddedFrom > DateTime.MinValue) { sqlc.Parameters.AddWithValue("$addedFrom", criteria.AddedFrom); } if (criteria.AddedTo > DateTime.MinValue) { sqlc.Parameters.AddWithValue("$addedTo", criteria.AddedTo); } if (criteria.Notes != "") { sqlc.Parameters.AddWithValue("$note", criteria.Notes); } SQLiteDataReader sqldr = sqlc.ExecuteReader(); while (sqldr.Read()) { // Do not use sqldr["id"].toString() because it won't work and kills the program! files.Add(new Files( Convert.ToInt32(sqldr["id"]), Convert.ToString(sqldr["title"]), Convert.ToString(sqldr["author"]), Convert.ToString(sqldr["year"]), Convert.ToString(sqldr["doi"]), Convert.ToString(sqldr["vdirs_id"]), Convert.ToBoolean(sqldr["favorite"]), Convert.ToString(sqldr["type"]), Convert.ToString(sqldr["tags_name"]), Convert.ToString(sqldr["note"]), Convert.ToString(sqldr["location"]), Convert.ToString(sqldr["added"]), Convert.ToString(sqldr["rread"]) )); } return files; }