public static Topic GetTopicById(int topicId) { //ToDo: Check if given category exists string query = "SELECT topics.category_id, categories.parent_category_id, categories.name AS category_name, title, user_id, closed, sticky, create_time " + "FROM topics " + "JOIN categories " + "ON topics.category_id = categories.category_id " + $"WHERE topic_id = {topicId}"; var reader = DbManager.Select(query); reader.Read(); if (reader.HasRows) { var categoryId = reader.GetInt32(0); var parentCategoryId = reader.GetInt32(1); var categoryName = reader.GetString(2); var topicTitle = reader.GetString(3); var topicUserId = reader.GetInt32(4); var isTopicClosed = reader.GetBoolean(5); var isTopicSticky = reader.GetBoolean(6); var topicCreateTime = reader.GetDateTime(7); var category = new Category(categoryId, parentCategoryId, categoryName); reader.Close(); return(new Topic(topicId, category, topicTitle, topicUserId, isTopicClosed, isTopicSticky, topicCreateTime)); } reader.Close(); return(null); }
public static bool UserHasPermission(int userId, int permissionId) { string query = "SELECT permission_id " + "FROM " + "( " + "SELECT group_permission_relation.permission_id " + "FROM group_user_relation " + "JOIN group_permission_relation " + "ON group_user_relation.permission_group_id = group_permission_relation.permission_group_id " + $"WHERE(user_id = {userId} " + $"AND permission_id = {permissionId})) AS grp_permissions " + "UNION " + "SELECT permission_id " + "FROM " + "( " + "SELECT permission_id " + "FROM user_permission_relation " + $"WHERE(user_id = {userId} " + $"AND permission_id = {permissionId})) AS acc_permissions"; var reader = DbManager.Select(query); if (reader.HasRows) { reader.Close(); return(true); } reader.Close(); return(false); }
public static List <PermissionGroup> GetAllPermissionGroups() { const string query = "SELECT * FROM permission_groups"; var reader = DbManager.Select(query); var groups = new List <PermissionGroup>(); while (reader.Read()) { var id = reader.GetInt32(0); var name = reader.GetString(1); groups.Add(new PermissionGroup(id, name)); } reader.Close(); return(groups); }
public static List <Category> GetFirstHierarchyCategories() { const string query = "SELECT category_id, name FROM categories WHERE parent_category_id IS NULL"; var reader = DbManager.Select(query); var categories = new List <Category>(); while (reader.Read()) { var id = reader.GetInt32(0); var name = reader.GetString(1); var cat = new Category(id, name); categories.Add(cat); } reader.Close(); return(categories); }
public static List <Post> GetPosts(int topicId) { string query = $"SELECT post_id, content, user_id, create_time FROM posts WHERE topic_id = {topicId}"; var reader = DbManager.Select(query); var posts = new List <Post>(); while (reader.Read()) { var postId = reader.GetInt32(0); var content = reader.GetString(1); var userId = reader.GetInt32(2); var createTime = reader.GetDateTime(3); posts.Add(new Post(postId, topicId, content, userId, createTime)); } reader.Close(); return(posts); }
public static User GetUser(int userId) { string query = "SELECT username, password " + "FROM users " + $"WHERE userID = {userId}"; var reader = DbManager.Select(query); reader.Read(); if (!reader.HasRows) { reader.Close(); return(null); } var username = reader.GetString(0); var passwordHash = reader.GetString(1); reader.Close(); return(new User(userId, username, passwordHash)); }
public static List <Word> GetWord(IList <Word> pickedWords, int limit = 3) { string query = "SELECT id, word " + "FROM words " + $"{BuildWhere(pickedWords)} " + "ORDER BY RAND() " + $"LIMIT {limit}"; var reader = DbManager.Select(query); var words = new List <Word>(); while (reader.Read()) { var id = reader.GetInt32(0); var word = reader.GetString(1); words.Add(new Word(id, word)); } reader.Close(); return(words); }