public async Task<List<string>> Get(string appId, string itemId, string relation, string userId)
{
using (var conn = new CqlConnection(_connString))
{
await conn.OpenAsync().ConfigureAwait(false);
var query = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
"select values from negrapi.relations where app_id = '{0}' and item_id='{1}' and relation='{2}';",
appId,
GetKey(itemId, userId),
relation);
var cmd = new CqlCommand(
conn,
query,
CqlConsistency.One);
var result = await cmd.ExecuteScalarAsync().ConfigureAwait(false) as string;
if (string.IsNullOrEmpty(result))
{
return null;
}
var list = JsonConvert.DeserializeObject<List<RecommendationsDataValue>>(result);
return list
.Select(i => i.ItemId)
.ToList();
}
}
public void Init()
{
const string createKsCql =
@"CREATE KEYSPACE Test WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1} and durable_writes = 'false';";
const string createTableCql = @"create table Test.BasicFlow (id int primary key, value text);";
const string truncateTableCql = @"truncate Test.BasicFlow;";
using (var connection = new CqlConnection(ConnectionString))
{
connection.Open();
try
{
var createKs = new CqlCommand(connection, createKsCql);
createKs.ExecuteNonQuery();
}
catch (AlreadyExistsException)
{
//ignore
}
try
{
var createTable = new CqlCommand(connection, createTableCql);
createTable.ExecuteNonQuery();
}
catch (AlreadyExistsException)
{
var truncTable = new CqlCommand(connection, truncateTableCql);
truncTable.ExecuteNonQuery();
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="CqlBatchTransaction" /> class.
/// </summary>
/// <param name="connection"> The connection. </param>
/// <param name="batchType"> Type of the batch. </param>
/// <param name="consistency"> The consistency. </param>
public CqlBatchTransaction(CqlConnection connection, CqlBatchType batchType = CqlBatchType.Logged,
CqlConsistency consistency = CqlConsistency.One)
{
_batchCommand = new CqlCommand(connection) {Consistency = consistency, Transaction = this};
_commands = new List<BatchFrame.BatchedCommand>();
_batchType = batchType;
_state = TransactionState.Pending;
}
/// <summary>
/// Initializes a new instance of the <see cref="CqlCommand" /> class.
/// </summary>
/// <param name="connection"> The connection. </param>
/// <param name="cql"> The CQL. </param>
/// <param name="level"> The level. </param>
public CqlCommand(CqlConnection connection, string cql, CqlConsistency level)
{
_connection = connection;
_cql = cql;
_level = level;
_prepared = false;
Load = 1;
UseBuffering = connection.Config.UseBuffering;
}
/// <summary>
/// Creates the cart async.
/// </summary>
/// <returns> </returns>
public async Task<Cart> AddCartAsync(string groupId)
{
var c = new Cart {Id = Guid.NewGuid(), GroupId = groupId};
using (var connection = new CqlConnection("cartDB"))
{
await connection.OpenAsync();
var command = new CqlCommand(connection, "insert into Shop.Carts (id, groupId) values (?,?);");
await command.PrepareAsync();
command.Parameters.Set(c);
await command.ExecuteNonQueryAsync();
return c;
}
}
/// <summary>
/// Prepares the db async.
/// </summary>
/// <returns> </returns>
public async Task PrepareDbAsync()
{
using (var connection = new CqlConnection("cartDB"))
{
await connection.OpenAsync();
try
{
var shopExistCommand = new CqlCommand(connection,
"select * from system.schema_keyspaces where keyspace_name='shop';");
using (var reader = shopExistCommand.ExecuteReader())
{
if (reader.HasRows)
{
var dropKs = new CqlCommand(connection, @"DROP KEYSPACE Shop;");
await dropKs.ExecuteNonQueryAsync();
}
}
}
catch (AlreadyExistsException)
{
//ignore
}
try
{
//create the keyspace if it does not exist
var createKs = new CqlCommand(connection,
@"CREATE KEYSPACE Shop WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1} and durable_writes = 'false';");
await createKs.ExecuteNonQueryAsync();
//create the table
var createTable = new CqlCommand(connection,
@"create table Shop.Carts (id uuid primary key, groupId text, items map<text, int>);");
await createTable.ExecuteNonQueryAsync();
//create corresponding index
var createIndex = new CqlCommand(connection, @"create index on Shop.Carts(GroupId)");
await createIndex.ExecuteNonQueryAsync();
}
catch (AlreadyExistsException)
{
}
}
}
public void Cleanup()
{
const string dropCql = @"drop keyspace Test;";
using (var connection = new CqlConnection(ConnectionString))
{
connection.Open();
try
{
var drop = new CqlCommand(connection, dropCql);
drop.ExecuteNonQuery();
}
catch (InvalidException)
{
//ignore
}
}
}
public async Task BasicFlow()
{
//Assume
const string insertCql = @"insert into Test.BasicFlow (id,value) values (?,?);";
const string retrieveCql = @"select * from Test.BasicFlow;";
const int insertCount = 1000;
//Act
using (var connection = new CqlConnection(ConnectionString))
{
await connection.OpenAsync();
var executions = new Task<ICqlQueryResult>[insertCount];
var options = new ParallelOptions() { MaxDegreeOfParallelism = 1 };
Parallel.For(0, insertCount, options, (i) =>
{
var cmd = new CqlCommand(connection, insertCql, CqlConsistency.One);
cmd.Prepare();
var b = new BasicFlowData { Id = i, Data = "Hallo " + i };
cmd.PartitionKey.Set(b);
cmd.Parameters.Set(b);
executions[i] = cmd.ExecuteNonQueryAsync();
});
await Task.WhenAll(executions);
var presence = new bool[insertCount];
var selectCmd = new CqlCommand(connection, retrieveCql, CqlConsistency.One) { EnableTracing = true };
CqlDataReader<BasicFlowData> reader = await selectCmd.ExecuteReaderAsync<BasicFlowData>();
while (await reader.ReadAsync())
{
BasicFlowData row = reader.Current;
Assert.AreEqual(row.Data, "Hallo " + row.Id);
presence[row.Id] = true;
}
Assert.IsTrue(presence.All(p => p));
var tracer = new QueryTraceCommand(connection, reader.TracingId.Value);
TracingSession session = await tracer.GetTraceSessionAsync();
Assert.IsNotNull(session);
}
}
/// <summary>
/// Finds the carts by group id async.
/// </summary>
/// <param name="groupId"> The group id. </param>
/// <returns> </returns>
public async Task<List<Cart>> FindCartsByGroupIdAsync(string groupId)
{
using (var connection = new CqlConnection("cartDB"))
{
await connection.OpenAsync();
var command = new CqlCommand(connection, "select * from Shop.Carts where GroupId=?;");
await command.PrepareAsync();
command.UseBuffering = true;
command.Parameters["groupid"] = groupId;
using (var reader = await command.ExecuteReaderAsync<Cart>())
{
var carts = new List<Cart>();
while (await reader.ReadAsync())
{
carts.Add(reader.Current);
}
return carts;
}
}
}
/// <summary>
/// Adds the items async.
/// </summary>
/// <param name="guid"> The GUID. </param>
/// <param name="items"> The items. </param>
/// <returns> </returns>
public async Task AddItemsAsync(Guid guid, Dictionary<string, int> items)
{
using (var connection = new CqlConnection("cartDB"))
{
await connection.OpenAsync();
var command = new CqlCommand(connection, "update Shop.Carts set items = items + ? where id = ?;");
await command.PrepareAsync();
command.Parameters["id"] = guid;
command.Parameters["items"] = items;
await command.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Queries the cart async.
/// </summary>
/// <param name="guid"> The GUID. </param>
/// <returns> </returns>
public async Task<Dictionary<string, int>> GetItemsAsync(Guid guid)
{
using (var connection = new CqlConnection("cartDB"))
{
await connection.OpenAsync();
var command = new CqlCommand(connection, "select Items from Shop.Carts where id=?;");
await command.PrepareAsync();
command.UseBuffering = true;
command.Parameters["id"] = guid;
using (var reader = await command.ExecuteReaderAsync())
{
return await reader.ReadAsync() ? (Dictionary<string, int>) reader["items"] : null;
}
}
}
/// <summary>
/// Updates the cart async.
/// </summary>
/// <param name="c"> The c. </param>
/// <returns> </returns>
public async Task UpdateCartAsync(Cart c)
{
using (var connection = new CqlConnection("cartDB"))
{
await connection.OpenAsync();
var command = new CqlCommand(connection, "update Shop.Carts set groupid=?, items=? where id=?;");
await command.PrepareAsync();
command.Parameters.Set(c);
await command.ExecuteNonQueryAsync();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="CqlCommand" /> class. Uses a default consistency level One
/// </summary>
/// <param name="connection"> The connection. </param>
/// <param name="cql"> The CQL. </param>
public CqlCommand(CqlConnection connection, string cql)
: this(connection, cql, CqlConsistency.One)
{
}