public async Task AddWiki(Wiki newWiki, WikiUser wikiUser) { if (string.IsNullOrWhiteSpace(newWiki.Name)) { return; } await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"INSERT INTO wikis(id, name, body, tags, user_id) VALUES (@id, @name, @body, @tags, @user_id);"; cmd.Parameters.AddWithValue("id", NpgsqlDbType.Uuid, newWiki.Id); cmd.Parameters.AddWithValue("name", NpgsqlDbType.Text, newWiki.Name); cmd.Parameters.AddWithValue("body", NpgsqlDbType.Text, newWiki.Body); cmd.Parameters.AddWithValue("tags", NpgsqlDbType.Array | NpgsqlDbType.Text, newWiki.Tags); cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); var rowsChanged = await cmd.ExecuteNonQueryAsync(); if (rowsChanged == 0) { throw new NpgsqlException("Didn't add wiki, something went wrong."); } if (rowsChanged > 1) { throw new NpgsqlException("Added more than one wiki, something has gone seriously wrong. "); } }
public async Task <Wiki> GetWikiByName(string wikiName, WikiUser wikiUser) { await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"SELECT * FROM wikis WHERE name = @name AND user_id = @user_id;"; cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); cmd.Parameters.AddWithValue("name", NpgsqlDbType.Text, wikiName); await using var reader = await cmd.ExecuteReaderAsync(); if (!reader.Read()) { // no wiki found return(null); } var wikiId = (Guid)reader["id"]; var wikiBody = (string)reader["body"]; var tagsFromReader = reader["tags"]; var wikiTags = tagsFromReader is DBNull ? Enumerable.Empty <string>() : (string[])tagsFromReader; return(new Wiki(wikiId, wikiName, wikiBody, wikiTags)); }
public async Task <IEnumerable <Wiki> > GetWikisWithTag(string tag, WikiUser wikiUser) { await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"SELECT * FROM wikis WHERE @tag = ANY(tags) AND user_id = @user_id;"; cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); cmd.Parameters.AddWithValue("tag", NpgsqlDbType.Text, tag); await using var reader = await cmd.ExecuteReaderAsync(); var wikis = new List <Wiki>(); while (reader.Read()) { var wikiId = (Guid)reader["id"]; var wikiName = (string)reader["name"]; var wikiBody = (string)reader["body"]; var tagsFromReader = reader["tags"]; var wikiTags = tagsFromReader is DBNull?Enumerable.Empty <string>() : (string[])tagsFromReader; wikis.Add(new Wiki(wikiId, wikiName, wikiBody, wikiTags)); } return(wikis); }
public async Task <IEnumerable <string> > GetAllWikiTags(WikiUser wikiUser) { await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"select array_agg(c) tags FROM ( SELECT unnest(tags) FROM wikis WHERE user_id=@user_id ) AS dt(c);"; cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); await using var reader = await cmd.ExecuteReaderAsync(); if (!reader.Read()) { return(null); } var wikiTagsFromDb = reader["tags"]; var wikiTags = wikiTagsFromDb is DBNull?Enumerable.Empty <string>() : (string[])wikiTagsFromDb; // TODO: handle casing return(wikiTags.Distinct()); }
public async Task UpdateWiki(Wiki updatedWiki, WikiUser wikiUser) { await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"UPDATE wikis SET body = @body, tags = @tags, last_modified = now() WHERE name = @name AND user_id=@user_id;"; cmd.Parameters.AddWithValue("name", NpgsqlDbType.Text, updatedWiki.Name); cmd.Parameters.AddWithValue("body", NpgsqlDbType.Text, updatedWiki.Body); cmd.Parameters.AddWithValue("tags", NpgsqlDbType.Array | NpgsqlDbType.Text, updatedWiki.Tags); cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); var rowsChanged = await cmd.ExecuteNonQueryAsync(); if (rowsChanged == 0) { throw new NpgsqlException("Didn't update wiki, something went wrong."); } if (rowsChanged > 1) { throw new NpgsqlException("Updated more than one wiki, something has gone seriously wrong. "); } }
public async Task DeleteWikiByName(string name, WikiUser wikiUser) { await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"DELETE FROM wikis WHERE name=@name AND user_id=@user_id;"; cmd.Parameters.AddWithValue("name", NpgsqlDbType.Text, name); cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); var rowsChanged = await cmd.ExecuteNonQueryAsync(); if (rowsChanged == 0) { throw new NpgsqlException("Didn't delete wiki, something went wrong."); } if (rowsChanged > 1) { throw new NpgsqlException("Deleted more than one wiki, something has gone seriously wrong. "); } }
public async Task Given_A_DomainUrl__Should_Retrieve_User_Details(string domainUrl, string id) { // Arrange var sut = new WikiUser(domainUrl); // Act var result = await sut.Details(new UserRequestParameters { Ids = new HashSet <string> { id } }); // Assert result.Should().NotBeNull(); }
public async Task <IEnumerable <string> > GetWikiNames(WikiUser wikiUser) { await using var conn = new NpgsqlConnection(m_ConnectionString); await conn.OpenAsync(); await using var cmd = conn.CreateCommand(); cmd.CommandText = @"SELECT name FROM wikis WHERE user_id=@user_id;"; cmd.Parameters.AddWithValue("user_id", NpgsqlDbType.Uuid, wikiUser.Id); await using var reader = await cmd.ExecuteReaderAsync(); var wikiNames = new List <string>(); while (reader.Read()) { wikiNames.Add((string)reader["name"]); } return(wikiNames); }