//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;
}