public void QueryBinding()
{
string tableName = CreateSimpleTableAndInsert(0);
var sst = new SimpleStatement(string.Format("INSERT INTO {0}(id, label) VALUES(?, ?)", tableName));
Session.Execute(sst.Bind(new object[] { Guid.NewGuid(), "label" }));
}
public void NoMappingDefinedTest()
{
const string cqlType = "CREATE TYPE temp_udt (text_sample text, date_sample timestamp)";
const string cqlTable = "CREATE TABLE temp_table (id int PRIMARY KEY, sample_udt frozen<temp_udt>, sample_udt_list list<frozen<temp_udt>>)";
const string cqlInsert = "INSERT INTO temp_table (id, sample_udt, sample_udt_list) VALUES (1, {text_sample: 'one', date_sample: 1}, [{text_sample: 'first'}])";
var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
var localSession = localCluster.Connect("tester");
localSession.Execute(cqlType);
localSession.Execute(cqlTable);
localSession.Execute(cqlInsert);
var row = localSession.Execute("SELECT * from temp_table").First();
Assert.IsNotNull(row.GetValue <object>("sample_udt"));
Assert.IsInstanceOf <byte[]>(row.GetValue <object>("sample_udt"));
Assert.IsNotNull(row.GetValue <object>("sample_udt_list"));
Assert.IsInstanceOf <List <byte[]> >(row.GetValue <object>("sample_udt_list"));
row = localSession.Execute("SELECT id, sample_udt.text_sample from temp_table").First();
Assert.AreEqual("one", row.GetValue <string>("sample_udt.text_sample"));
//Trying to bind to an unmapped type should throw
var statement = new SimpleStatement("INSERT INTO temp_table (id, sample_udt) VALUES (?, ?)");
Assert.Throws <InvalidTypeException>(() => localSession.Execute(statement.Bind(2, new DummyClass())));
localCluster.Dispose();
}
private void ParameterizedStatementTest(Type type, bool testAsync = false)
{
var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
var cassandraDataTypeName = QueryTools.convertTypeNameToCassandraEquivalent(type);
var expectedValues = new List <object[]>(1);
var val = Randomm.RandomVal(type);
var bindValues = new object[] { Guid.NewGuid(), val };
expectedValues.Add(bindValues);
CreateTable(tableName, cassandraDataTypeName);
SimpleStatement statement = new SimpleStatement(String.Format("INSERT INTO {0} (id, val) VALUES (?, ?)", tableName));
statement.Bind(bindValues);
if (testAsync)
{
Session.ExecuteAsync(statement).Wait(500);
}
else
{
Session.Execute(statement);
}
// Verify results
RowSet rs = Session.Execute("SELECT * FROM " + tableName);
VerifyData(rs, expectedValues);
}
private void ParameterizedStatementTimeStampTest()
{
RowSet rs = null;
var expectedValues = new List <object[]>(1);
var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
var valuesToTest = new List <object[]> {
new object[] { Guid.NewGuid(), new DateTimeOffset(2011, 2, 3, 16, 5, 0, new TimeSpan(0000)) },
{ new object[] { Guid.NewGuid(), (long)0 } }
};
foreach (var bindValues in valuesToTest)
{
expectedValues.Add(bindValues);
CreateTable(tableName, "timestamp");
SimpleStatement statement = new SimpleStatement(String.Format("INSERT INTO {0} (id, val) VALUES (?, ?)", tableName));
statement.Bind(bindValues);
Session.Execute(statement);
// Verify results
rs = Session.Execute("SELECT * FROM " + tableName);
VerifyData(rs, expectedValues);
DropTable(tableName);
expectedValues.Clear();
}
}
public void MappingEncodingSingleTest()
{
foreach (var protocolVersion in UdtProtocolVersionSupported)
{
//Use all possible protocol versions
Cluster.MaxProtocolVersion = protocolVersion;
//Use a local cluster
var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
var localSession = localCluster.Connect("tester");
localSession.UserDefinedTypes.Define(
UdtMap.For <Phone>("phone")
.Map(v => v.Alias, "alias")
.Map(v => v.CountryCode, "country_code")
.Map(v => v.Number, "number")
);
var insert = new SimpleStatement("INSERT INTO users (id, main_phone) values (?, ?)");
//All of the fields null
var id = 201;
var phone = new Phone();
localSession.Execute(insert.Bind(id, phone));
var rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
Assert.AreEqual(phone, rs.First().GetValue <Phone>("main_phone"));
//Some fields null and others with value
id = 202;
phone = new Phone()
{
Alias = "Home phone"
};
localSession.Execute(insert.Bind(id, phone));
rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
Assert.AreEqual(phone, rs.First().GetValue <Phone>("main_phone"));
//All fields filled in
id = 203;
phone = new Phone()
{
Alias = "Mobile phone", CountryCode = 54, Number = "1234567"
};
localSession.Execute(insert.Bind(id, phone));
rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
Assert.AreEqual(phone, rs.First().GetValue <Phone>("main_phone"));
}
}
public void SimpleStatementNamedValuesNotSpecifiedTest()
{
var insertQuery = String.Format("INSERT INTO {0} (float_sample, text_sample, bigint_sample, id) VALUES (:MY_float, :my_TexT, :my_BIGint, :id)", AllTypesTableName);
var statement = new SimpleStatement(insertQuery);
Assert.Throws <InvalidQueryException>(() => Session.Execute(
statement.Bind(
new { id = Guid.NewGuid(), my_bigint = 1L })));
}
public void SimpleStatementNamedValuesNotSpecifiedTest()
{
var insertQuery = String.Format("INSERT INTO {0} (float_sample, text_sample, bigint_sample, id) VALUES (:MY_float, :my_TexT, :my_BIGint, :id)", AllTypesTableName);
var statement = new SimpleStatement(insertQuery);
Assert.Throws<InvalidQueryException>(() => Session.Execute(
statement.Bind(
new {id = Guid.NewGuid(), my_bigint = 1L })));
}
/// <summary>
/// Gets the list of temperature stored for a provided weather station and day.
/// </summary>
public RowSet GetTemperatureRecords(string weatherStationId, DateTime day)
{
var selectCql = "SELECT * FROM temperature_by_day WHERE weatherstation_id = ? AND date = ?";
//Create a statement
var selectStatement = new SimpleStatement(selectCql);
//Add the parameters
selectStatement.Bind(weatherStationId, DateTime.Now.ToString("yyyyMMdd"));
//Execute the select statement
return(Session.Execute(selectStatement));
}
public void SimpleStatementSetTimestamp()
{
var timestamp = new DateTimeOffset(1999, 12, 31, 1, 2, 3, TimeSpan.Zero);
var id = Guid.NewGuid();
var insertStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, text_sample) VALUES (?, ?)", AllTypesTableName));
Session.Execute(insertStatement.Bind(id, "sample text").SetTimestamp(timestamp));
var row = Session.Execute(new SimpleStatement(String.Format("SELECT id, text_sample, writetime(text_sample) FROM {0} WHERE id = ?", AllTypesTableName)).Bind(id)).First();
Assert.NotNull(row.GetValue <string>("text_sample"));
Assert.AreEqual(TypeCodec.ToUnixTime(timestamp).Ticks / 10, row.GetValue <object>("writetime(text_sample)"));
}
public void EncodeDecodeTupleAsNestedTest()
{
var achievements = new List<Tuple<string, int>>
{
new Tuple<string, int>("What", 1),
new Tuple<string, int>(null, 100),
new Tuple<string, int>(@"¯\_(ツ)_/¯", 150)
};
var insert = new SimpleStatement("INSERT INTO users_tuples (id, achievements) values (?, ?)");
Session.Execute(insert.Bind(31, achievements));
var row = Session.Execute("SELECT * FROM users_tuples WHERE id = 31").First();
Assert.AreEqual(achievements, row.GetValue<List<Tuple<string, int>>>("achievements"));
}
public void SimpleStatementNamedValuesCaseInsensitivityTest()
{
var insertQuery = String.Format("INSERT INTO {0} (id, \"text_sample\", int_sample) VALUES (:my_ID, :my_TEXT, :MY_INT)", AllTypesTableName);
var statement = new SimpleStatement(insertQuery);
var id = Guid.NewGuid();
Session.Execute(
statement.Bind(
new { my_INt = 1, my_TEXT = "WAT1", my_id = id}));
var row = Session.Execute(String.Format("SELECT * FROM {0} WHERE id = {1:D}", AllTypesTableName, id)).First();
Assert.AreEqual(1, row.GetValue<int>("int_sample"));
Assert.AreEqual("WAT1", row.GetValue<string>("text_sample"));
}
public void CollectionParamsTests()
{
var id = Guid.NewGuid();
var map = new SortedDictionary<string, string> { { "fruit", "apple" }, { "band", "Beatles" } };
var list = new List<string> { "one", "two" };
var set = new List<string> { "set_1one", "set_2two" };
var insertStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, map_sample, list_sample, set_sample) VALUES (?, ?, ?, ?)", AllTypesTableName));
Session.Execute(insertStatement.Bind(id, map, list, set));
var row = Session.Execute(new SimpleStatement(String.Format("SELECT * FROM {0} WHERE id = ?", AllTypesTableName)).Bind(id)).First();
CollectionAssert.AreEquivalent(map, row.GetValue<IDictionary<string, string>>("map_sample"));
CollectionAssert.AreEquivalent(list, row.GetValue<List<string>>("list_sample"));
CollectionAssert.AreEquivalent(set, row.GetValue<List<string>>("set_sample"));
}
public void EncodeDecodeTupleAsNestedTest()
{
var achievements = new List <Tuple <string, int> >
{
new Tuple <string, int>("What", 1),
new Tuple <string, int>(null, 100),
new Tuple <string, int>(@"¯\_(ツ)_/¯", 150)
};
var insert = new SimpleStatement("INSERT INTO users_tuples (id, achievements) values (?, ?)");
Session.Execute(insert.Bind(31, achievements));
var row = Session.Execute("SELECT * FROM users_tuples WHERE id = 31").First();
Assert.AreEqual(achievements, row.GetValue <List <Tuple <string, int> > >("achievements"));
}
public void SimpleStatementNamedValuesCaseInsensitivityTest()
{
var insertQuery = String.Format("INSERT INTO {0} (id, \"text_sample\", int_sample) VALUES (:my_ID, :my_TEXT, :MY_INT)", AllTypesTableName);
var statement = new SimpleStatement(insertQuery);
var id = Guid.NewGuid();
Session.Execute(
statement.Bind(
new { my_INt = 1, my_TEXT = "WAT1", my_id = id }));
var row = Session.Execute(String.Format("SELECT * FROM {0} WHERE id = {1:D}", AllTypesTableName, id)).First();
Assert.AreEqual(1, row.GetValue <int>("int_sample"));
Assert.AreEqual("WAT1", row.GetValue <string>("text_sample"));
}
public void Batch_SimpleStatement_Bound()
{
var batch = new BatchStatement();
var simpleStatement = new SimpleStatement($"INSERT INTO {_tableName} (id, label, number) VALUES (?, ?, ?)");
#pragma warning disable 618
batch.Add(simpleStatement.Bind(100, "label 100", 10000));
#pragma warning restore 618
Session.Execute(batch);
//Verify Results
var rs = Session.Execute($"SELECT * FROM {_tableName} WHERE id IN ({100})");
var row = rs.First();
CollectionAssert.AreEqual(row, new object[] { 100, "label 100", 10000 });
}
public void SimpleStatementNamedValuesTest()
{
var insertQuery = String.Format("INSERT INTO {0} (text_sample, int_sample, bigint_sample, id) VALUES (:my_text, :my_int, :my_bigint, :my_id)", AllTypesTableName);
var statement = new SimpleStatement(insertQuery);
var id = Guid.NewGuid();
Session.Execute(
statement.Bind(
new { my_int = 100, my_bigint = -500L, my_id = id, my_text = "named params ftw again!" }));
var row = Session.Execute(String.Format("SELECT int_sample, bigint_sample, text_sample FROM {0} WHERE id = {1:D}", AllTypesTableName, id)).First();
Assert.AreEqual(100, row.GetValue<int>("int_sample"));
Assert.AreEqual(-500L, row.GetValue<long>("bigint_sample"));
Assert.AreEqual("named params ftw again!", row.GetValue<string>("text_sample"));
}
public void Batch_SimpleStatement_Bound()
{
var batch = new BatchStatement();
var simpleStatement = new SimpleStatement($"INSERT INTO {_tableName} (id, label, number) VALUES (?, ?, ?)");
#pragma warning disable 618
batch.Add(simpleStatement.Bind(100, "label 100", 10000));
#pragma warning restore 618
Session.Execute(batch);
VerifyBatchStatement(
1,
new[] { $"INSERT INTO {_tableName} (id, label, number) VALUES (?, ?, ?)" },
new object[] { 100, "label 100", 10000 });
}
public void SimpleStatementNamedValuesTest()
{
var insertQuery = String.Format("INSERT INTO {0} (text_sample, int_sample, bigint_sample, id) VALUES (:my_text, :my_int, :my_bigint, :my_id)", AllTypesTableName);
var statement = new SimpleStatement(insertQuery);
var id = Guid.NewGuid();
Session.Execute(
statement.Bind(
new { my_int = 100, my_bigint = -500L, my_id = id, my_text = "named params ftw again!" }));
var row = Session.Execute(String.Format("SELECT int_sample, bigint_sample, text_sample FROM {0} WHERE id = {1:D}", AllTypesTableName, id)).First();
Assert.AreEqual(100, row.GetValue <int>("int_sample"));
Assert.AreEqual(-500L, row.GetValue <long>("bigint_sample"));
Assert.AreEqual("named params ftw again!", row.GetValue <string>("text_sample"));
}
/// <summary>
/// Add a temperature information for a given weather station
/// </summary>
public void AddTemperature(string weatherStationId, decimal value)
{
var insertCql = @"
INSERT INTO temperature_by_day
(weatherstation_id, date, event_time, temperature)
VALUES
(?, ?, ?, ?)";
//Create an insert statement
var insertStatement = new SimpleStatement(insertCql);
//Bind the parameters to the statement
insertStatement.Bind(weatherStationId, DateTime.Now.ToString("yyyyMMdd"), DateTime.Now, value);
//You can set other options of the statement execution, for example the consistency level.
insertStatement.SetConsistencyLevel(ConsistencyLevel.Quorum);
//Execute the insert
Session.Execute(insertStatement);
}
public void MappingEncodingNestedTest()
{
foreach (var protocolVersion in UdtProtocolVersionSupported)
{
//Use all possible protocol versions
Cluster.MaxProtocolVersion = protocolVersion;
//Use a local cluster
var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
var localSession = localCluster.Connect("tester");
localSession.UserDefinedTypes.Define(
UdtMap.For <Phone>(),
UdtMap.For <Contact>()
.Map(c => c.FirstName, "first_name")
.Map(c => c.LastName, "last_name")
.Map(c => c.Birth, "birth_date")
);
var insert = new SimpleStatement("INSERT INTO users_contacts (id, contacts) values (?, ?)");
//All of the fields null
var id = 301;
var contacts = new List <Contact>
{
new Contact
{
FirstName = "Vincent",
LastName = "Vega",
Phones = new List <Phone>
{
new Phone {
Alias = "Wat", Number = "0000000000121220000"
},
new Phone {
Alias = "Office", Number = "123"
}
}
}
};
localSession.Execute(insert.Bind(id, contacts));
var rs = localSession.Execute(new SimpleStatement("SELECT * FROM users_contacts WHERE id = ?").Bind(id));
Assert.AreEqual(contacts, rs.First().GetValue <List <Contact> >("contacts"));
}
}
public void BatchSimpleStatementSingleBinded()
{
var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
CreateTable(tableName);
var batch = new BatchStatement();
var simpleStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, label, number) VALUES (?, ?, ?)", tableName));
batch.Add(simpleStatement.Bind(100, "label 100", 10000));
Session.Execute(batch);
//Verify Results
var rs = Session.Execute("SELECT * FROM " + tableName);
var row = rs.First();
Assert.True(row != null, "There should be a row stored.");
Assert.True(row.SequenceEqual(new object[] { 100, "label 100", 10000 }), "Stored values dont match");
}
/// <summary>
/// Add a temperature information for a given weather station asynchronously
/// </summary>
public Task AddTemperatureAsync(string weatherStationId, decimal value)
{
//It is basically the same code as the AddTemperature
//Except it returns a Task that already started but didn't finished
//The row would not be in Cassandra yet when this method finishes executing.
var insertCql = @"
INSERT INTO temperature_by_day
(weatherstation_id, date, event_time, temperature)
VALUES
(?, ?, ?, ?)";
//Create an insert statement
var insertStatement = new SimpleStatement(insertCql);
//Bind the parameters to the statement
insertStatement.Bind(weatherStationId, DateTime.Now.ToString("yyyyMMdd"), DateTime.Now, value);
//You can set other options of the statement execution, for example the consistency level.
insertStatement.SetConsistencyLevel(ConsistencyLevel.Quorum);
//Execute the insert
return(Session.ExecuteAsync(insertStatement));
}
public void CollectionParamsTests()
{
var id = Guid.NewGuid();
var map = new SortedDictionary <string, string> {
{ "fruit", "apple" }, { "band", "Beatles" }
};
var list = new List <string> {
"one", "two"
};
var set = new List <string> {
"set_1one", "set_2two"
};
var insertStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, map_sample, list_sample, set_sample) VALUES (?, ?, ?, ?)", AllTypesTableName));
Session.Execute(insertStatement.Bind(id, map, list, set));
var row = Session.Execute(new SimpleStatement(String.Format("SELECT * FROM {0} WHERE id = ?", AllTypesTableName)).Bind(id)).First();
CollectionAssert.AreEquivalent(map, row.GetValue <IDictionary <string, string> >("map_sample"));
CollectionAssert.AreEquivalent(list, row.GetValue <List <string> >("list_sample"));
CollectionAssert.AreEquivalent(set, row.GetValue <List <string> >("set_sample"));
}
private void ParameterizedStatementTest(Type type, bool testAsync = false)
{
var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
var cassandraDataTypeName = QueryTools.convertTypeNameToCassandraEquivalent(type);
var expectedValues = new List<object[]>(1);
var val = Randomm.RandomVal(type);
var bindValues = new object[] { Guid.NewGuid(), val };
expectedValues.Add(bindValues);
CreateTable(tableName, cassandraDataTypeName);
SimpleStatement statement = new SimpleStatement(String.Format("INSERT INTO {0} (id, val) VALUES (?, ?)", tableName));
statement.Bind(bindValues);
if (testAsync)
{
Session.ExecuteAsync(statement).Wait(500);
}
else
{
Session.Execute(statement);
}
// Verify results
RowSet rs = Session.Execute("SELECT * FROM " + tableName);
VerifyData(rs, expectedValues);
}
static void Main(string[] args)
{
#if false
var cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build();
var session = cluster.Connect();
session.Execute("DROP KEYSPACE IF EXISTS simplex");
session.Execute("CREATE KEYSPACE IF NOT EXISTS simplex WITH replication = { 'class': 'SimpleStrategy', 'replication_factor' : 1};");
session.ChangeKeyspace("simplex");
session.Execute(
"CREATE TABLE IF NOT EXISTS simplex.songs (" +
"id uuid PRIMARY KEY," +
"title text," +
"album text," +
"artist text," +
"tags set<text>," +
"data blob" +
");");
session.Execute(
"CREATE TABLE IF NOT EXISTS simplex.playlists (" +
"id uuid," +
"title text," +
"album text, " +
"artist text," +
"song_id uuid," +
"PRIMARY KEY (id, title, album, artist)" +
");");
session.Execute(
"INSERT INTO simplex.songs (id, title, album, artist, tags) " +
"VALUES (" +
"756716f7-2e54-4715-9f00-91dcbea6cf50," +
"'La Petite Tonkinoise'," +
"'Bye Bye Blackbird'," +
"'Joséphine Baker'," +
"{'jazz', '2013'})" +
";");
session.Execute(
"INSERT INTO simplex.playlists (id, song_id, title, album, artist) " +
"VALUES (" +
"2cc9ccb7-6221-4ccb-8387-f22b6a1b354d," +
"756716f7-2e54-4715-9f00-91dcbea6cf50," +
"'La Petite Tonkinoise'," +
"'Bye Bye Blackbird'," +
"'Joséphine Baker'" +
");");
RowSet results = session.Execute("SELECT * FROM simplex.playlists WHERE id = 2cc9ccb7-6221-4ccb-8387-f22b6a1b354d;");
session.Execute("DROP TABLE IF EXISTS simplex.playlists");
session.Execute("DROP TABLE IF EXISTS simplex.songs");
session.DeleteKeyspaceIfExists("simplex");
#else
var cluster = Cluster.Builder().AddContactPoint("127.0.0.1").Build();
var session = cluster.Connect();
session.Execute("DROP KEYSPACE IF EXISTS driver_samples_kp");
session.Execute("CREATE KEYSPACE IF NOT EXISTS driver_samples_kp WITH replication = { 'class': 'SimpleStrategy', 'replication_factor' : 1};");
// session.ChangeKeyspace("driver_samples_kp");
var createCql = @"
CREATE TABLE driver_samples_kp.temperature_by_day (
weatherstation_id text,
date text,
event_time timestamp,
temperature decimal,
PRIMARY KEY ((weatherstation_id,date),event_time)
)";
session.Execute(createCql);
var createTopicCql = @"
CREATE TABLE driver_samples_kp.topics (
topic_id uuid PRIMARY KEY,
topic_title text,
topic_date timestamp
)";
session.Execute(createTopicCql);
var createMessageCql = @"
CREATE TABLE driver_samples_kp.messages (
topic_id uuid,
message_date timestamp,
message_body text,
PRIMARY KEY (topic_id, message_date)
)";
session.Execute(createMessageCql);
session = cluster.Connect("driver_samples_kp");
{
//Trying to simulate the insertion of several rows
//with temperature measures
for (var i = 0; i < 1; i++)
{
var insertCql = @"
INSERT INTO temperature_by_day
(weatherstation_id, date, event_time, temperature)
VALUES
(?, ?, ?, ?)";
//Create an insert statement
var insertStatement = new SimpleStatement(insertCql);
//Bind the parameters to the statement
insertStatement.Bind("station1", DateTime.Now.ToString("yyyyMMdd"), DateTime.Now, i / 16M);
//You can set other options of the statement execution, for example the consistency level.
insertStatement.SetConsistencyLevel(ConsistencyLevel.Quorum);
//Execute the insert
session.Execute(insertStatement);
}
var selectCql = "SELECT * FROM temperature_by_day WHERE weatherstation_id = ? AND date = ?";
var selectStatement = new SimpleStatement(selectCql);
selectStatement.Bind("station1", DateTime.Now.ToString("yyyyMMdd"));
session.Execute(selectStatement);
}
// Execute Async ////////////////////////////////////////////////////
//It is basically the same code as the AddTemperature
//Except it returns a Task that already started but didn't finished
//The row would not be in Cassandra yet when this method finishes executing.
{
var insertTaskList = new List <Task>();
for (var i = 0; i < 1000; i++)
{
//It returns a task that is going to be completed when the Cassandra ring acknowledge the insert
var insertCql = @"
INSERT INTO temperature_by_day
(weatherstation_id, date, event_time, temperature)
VALUES
(?, ?, ?, ?)";
//Create an insert statement
var insertStatement = new SimpleStatement(insertCql);
//Bind the parameters to the statement
insertStatement.Bind("station2", DateTime.Now.ToString("yyyyMMdd"), DateTime.Now, i / 16M);
//You can set other options of the statement execution, for example the consistency level.
insertStatement.SetConsistencyLevel(ConsistencyLevel.Quorum);
//Execute the insert
insertTaskList.Add(session.ExecuteAsync(insertStatement));
}
while (true)
{
if (insertTaskList.Count == 0)
{
break;
}
foreach (var task in insertTaskList.Reverse <Task>())
{
if (task.IsCompleted)
{
insertTaskList.Remove(task);
}
}
System.Threading.Thread.Sleep(3);
}
// Task.WaitAny(insertTaskList.ToArray());
//Now lets retrieve the temperatures for a given date
var selectCql = "SELECT * FROM temperature_by_day WHERE weatherstation_id = ? AND date = ?";
//Create a statement
var selectStatement = new SimpleStatement(selectCql);
//Add the parameters
selectStatement.Bind("station2", DateTime.Now.ToString("yyyyMMdd"));
//Execute the select statement
var rs = session.Execute(selectStatement);
//lets print a few of them
Console.WriteLine("Printing the first temperature records (only 20, if available)");
var counter = 0;
foreach (var row in rs)
{
Console.Write(row.GetValue <string>("weatherstation_id"));
Console.Write("\t");
Console.Write(row.GetValue <DateTime>("event_time").ToString("HH:mm:ss.fff"));
Console.Write("\t");
Console.WriteLine(row.GetValue <decimal>("temperature"));
//It is just an example, 20 is enough
if (counter++ == 20)
{
Console.WriteLine();
break;
}
}
}
// Forum Execution ////////////////
{
var _insertTopicStatement = session.Prepare("INSERT INTO topics (topic_id, topic_title, topic_date) VALUES (?, ?, ?)");
var _insertMessageStatement = session.Prepare("INSERT INTO messages (topic_id, message_date, message_body) VALUES (?, ?, ?)");
var topicId = Guid.NewGuid();
var batch = new BatchStatement();
//bind the parameters on each statement and add them to the batch
batch.Add(_insertTopicStatement.Bind(topicId, "Sample forum thread", DateTime.Now));
batch.Add(_insertMessageStatement.Bind(topicId, DateTime.Now, "This is the first message and body of the topic"));
//You can set other options of the batch execution, for example the consistency level.
batch.SetConsistencyLevel(ConsistencyLevel.Quorum);
//Execute the insert of the 2 rows
session.Execute(batch);
//Insert some messages
for (var i = 1; i < 250; i++)
{
var boundStatement = _insertMessageStatement.Bind(topicId, DateTime.Now, "Message " + (i + 1));
//We can specify execution options for the statement
boundStatement.SetConsistencyLevel(ConsistencyLevel.Quorum);
//Execute the bound statement
session.Execute(boundStatement);
}
//Now lets retrieve the messages by topic with a page size of 20.
//We will add 1 row using a prepared statement.
var selectCql = "SELECT * FROM messages WHERE topic_id = ?";
//Prepare the insert message statement and bind the parameters
var statement = session.Prepare(selectCql).Bind(topicId);
//We can specify execution options, like page size and consistency
statement.SetPageSize(100).SetConsistencyLevel(ConsistencyLevel.One);
//Execute the prepared statement
var rs = session.Execute(statement);
//At this point only 100 rows are loaded into the RowSet.
Console.WriteLine("Printing all the rows paginating with a page size of 100");
foreach (var row in rs)
{
//While we iterate though the RowSet
//We will paginate through all the rows
Console.WriteLine(row.GetValue <string>("message_body"));
}
}
#endif
}
public void Batch_SimpleStatement_Bound()
{
var batch = new BatchStatement();
var simpleStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, label, number) VALUES (?, ?, ?)", _tableName));
#pragma warning disable 618
batch.Add(simpleStatement.Bind(100, "label 100", 10000));
#pragma warning restore 618
Session.Execute(batch);
//Verify Results
var rs = Session.Execute(String.Format("SELECT * FROM {0} WHERE id IN ({1})", _tableName, 100));
var row = rs.First();
CollectionAssert.AreEqual(row, new object[] {100, "label 100", 10000});
}
public void BatchSimpleStatementSingleBinded()
{
var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
CreateTable(tableName);
var batch = new BatchStatement();
var simpleStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, label, number) VALUES (?, ?, ?)", tableName));
batch.Add(simpleStatement.Bind(100, "label 100", 10000));
Session.Execute(batch);
//Verify Results
var rs = Session.Execute("SELECT * FROM " + tableName);
var row = rs.First();
Assert.True(row != null, "There should be a row stored.");
Assert.True(row.SequenceEqual(new object[] { 100, "label 100", 10000}), "Stored values dont match");
}
public void MappingEncodingNestedTest()
{
foreach (var protocolVersion in UdtProtocolVersionSupported)
{
//Use all possible protocol versions
Cluster.MaxProtocolVersion = protocolVersion;
//Use a local cluster
var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
var localSession = localCluster.Connect("tester");
localSession.UserDefinedTypes.Define(
UdtMap.For<Phone>(),
UdtMap.For<Contact>()
.Map(c => c.FirstName, "first_name")
.Map(c => c.LastName, "last_name")
.Map(c => c.Birth, "birth_date")
);
var insert = new SimpleStatement("INSERT INTO users_contacts (id, contacts) values (?, ?)");
//All of the fields null
var id = 301;
var contacts = new List<Contact>
{
new Contact
{
FirstName = "Vincent",
LastName = "Vega",
Phones = new List<Phone>
{
new Phone {Alias = "Wat", Number = "0000000000121220000"},
new Phone {Alias = "Office", Number = "123"}
}
}
};
localSession.Execute(insert.Bind(id, contacts));
var rs = localSession.Execute(new SimpleStatement("SELECT * FROM users_contacts WHERE id = ?").Bind(id));
Assert.AreEqual(contacts, rs.First().GetValue<List<Contact>>("contacts"));
}
}
public void NoMappingDefinedTest()
{
const string cqlType = "CREATE TYPE temp_udt (text_sample text, date_sample timestamp)";
const string cqlTable = "CREATE TABLE temp_table (id int PRIMARY KEY, sample_udt temp_udt, sample_udt_list list<temp_udt>)";
const string cqlInsert = "INSERT INTO temp_table (id, sample_udt, sample_udt_list) VALUES (1, {text_sample: 'one', date_sample: 1}, [{text_sample: 'first'}])";
var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
var localSession = localCluster.Connect("tester");
localSession.Execute(cqlType);
localSession.Execute(cqlTable);
localSession.Execute(cqlInsert);
var row = localSession.Execute("SELECT * from temp_table").First();
Assert.IsNotNull(row.GetValue<object>("sample_udt"));
Assert.IsInstanceOf<byte[]>(row.GetValue<object>("sample_udt"));
Assert.IsNotNull(row.GetValue<object>("sample_udt_list"));
Assert.IsInstanceOf<List<byte[]>>(row.GetValue<object>("sample_udt_list"));
row = localSession.Execute("SELECT id, sample_udt.text_sample from temp_table").First();
Assert.AreEqual("one", row.GetValue<string>("sample_udt.text_sample"));
//Trying to bind to an unmapped type should throw
var statement = new SimpleStatement("INSERT INTO temp_table (id, sample_udt) VALUES (?, ?)");
Assert.Throws<InvalidTypeException>(() => localSession.Execute(statement.Bind(2, new DummyClass())));
localCluster.Dispose();
}
public void MappingEncodingSingleTest()
{
foreach (var protocolVersion in UdtProtocolVersionSupported)
{
//Use all possible protocol versions
Cluster.MaxProtocolVersion = protocolVersion;
//Use a local cluster
var localCluster = Cluster.Builder().AddContactPoint(IpPrefix + "1").Build();
var localSession = localCluster.Connect("tester");
localSession.UserDefinedTypes.Define(
UdtMap.For<Phone>("phone")
.Map(v => v.Alias, "alias")
.Map(v => v.CountryCode, "country_code")
.Map(v => v.Number, "number")
);
var insert = new SimpleStatement("INSERT INTO users (id, main_phone) values (?, ?)");
//All of the fields null
var id = 201;
var phone = new Phone();
localSession.Execute(insert.Bind(id, phone));
var rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
Assert.AreEqual(phone, rs.First().GetValue<Phone>("main_phone"));
//Some fields null and others with value
id = 202;
phone = new Phone() {Alias = "Home phone"};
localSession.Execute(insert.Bind(id, phone));
rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
Assert.AreEqual(phone, rs.First().GetValue<Phone>("main_phone"));
//All fields filled in
id = 203;
phone = new Phone() { Alias = "Mobile phone", CountryCode = 54, Number = "1234567"};
localSession.Execute(insert.Bind(id, phone));
rs = localSession.Execute(new SimpleStatement("SELECT * FROM users WHERE id = ?").Bind(id));
Assert.AreEqual(phone, rs.First().GetValue<Phone>("main_phone"));
}
}
private void ParameterizedStatementTimeStampTest()
{
RowSet rs = null;
var expectedValues = new List<object[]>(1);
var tableName = "table" + Guid.NewGuid().ToString("N").ToLower();
var valuesToTest = new List<object[]> { new object[] { Guid.NewGuid(), new DateTimeOffset(2011, 2, 3, 16, 5, 0, new TimeSpan(0000)) },
{new object[] {Guid.NewGuid(), (long)0}}};
foreach (var bindValues in valuesToTest)
{
expectedValues.Add(bindValues);
CreateTable(tableName, "timestamp");
SimpleStatement statement = new SimpleStatement(String.Format("INSERT INTO {0} (id, val) VALUES (?, ?)", tableName));
statement.Bind(bindValues);
Session.Execute(statement);
// Verify results
rs = Session.Execute("SELECT * FROM " + tableName);
VerifyData(rs, expectedValues);
DropTable(tableName);
expectedValues.Clear();
}
}
public void QueryBinding()
{
string tableName = CreateSimpleTableAndInsert(0);
var sst = new SimpleStatement(string.Format("INSERT INTO {0}(id, label) VALUES(?, ?)", tableName));
Session.Execute(sst.Bind(new object[] { Guid.NewGuid(), "label" }));
}
public void SimpleStatementSetTimestamp()
{
var timestamp = new DateTimeOffset(1999, 12, 31, 1, 2, 3, TimeSpan.Zero);
var id = Guid.NewGuid();
var insertStatement = new SimpleStatement(String.Format("INSERT INTO {0} (id, text_sample) VALUES (?, ?)", AllTypesTableName));
Session.Execute(insertStatement.Bind(id, "sample text").SetTimestamp(timestamp));
var row = Session.Execute(new SimpleStatement(String.Format("SELECT id, text_sample, writetime(text_sample) FROM {0} WHERE id = ?", AllTypesTableName)).Bind(id)).First();
Assert.NotNull(row.GetValue<string>("text_sample"));
Assert.AreEqual(TypeCodec.ToUnixTime(timestamp).Ticks / 10, row.GetValue<object>("writetime(text_sample)"));
}