Exemplo n.º 1
0
        //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();
            }
        }
Exemplo n.º 2
0
        // 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;
        }