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