C# CassandraContext.ExecuteNonQuery方法代码示例

        private static void CreateColumnFamilyWithTimestampOperator()
        {
            using (var db = new CassandraContext(keyspace: KeyspaceName, server: Server))
            {

                db.ExecuteNonQuery("CREATE TABLE TestCF2 (KEY text PRIMARY KEY) WITH comparator=timestamp AND default_validation=text;");

                DateTimeOffset UnixStart = new DateTimeOffset(1970, 1, 1, 0, 0, 0, TimeSpan.Zero);
                DateTimeOffset TimeNow = DateTimeOffset.UtcNow;

                var columnName2 = Convert.ToInt64(Math.Floor((TimeNow - UnixStart).TotalMilliseconds));
                var columnName1 = columnName2 + 5000;

                db.ExecuteNonQuery(string.Format("INSERT INTO TestCF2 (KEY, {0}, {1}) VALUES ('Key1', 'Value1', 'Value2')", columnName1, columnName2));

                List<ICqlRow> rows = db.ExecuteQuery("SELECT * FROM TestCF2 WHERE KEY = 'Key1'").ToList();

                DateTime value1 = rows[0].Columns[1].ColumnName.GetValue<DateTime>();
                DateTime value2 = rows[0].Columns[2].ColumnName.GetValue<DateTime>();

                ConsoleHeader("Returned data from db for timestamp comparator");
                ConsoleHeader(value1.ToString());
                ConsoleHeader(value2.ToString());

            }
        }

        public static void CreateTablesIfNotExists(CassandraContext db)
        {
            try
            {
                db.ExecuteNonQuery(string.Format(@"
            CREATE TABLE {0} (
             id     uuid,
             logic text,
             command text,
             hidden_till timestamp,
             PRIMARY KEY (id));
            ", TableName("main")));
            }
            catch (CassandraException) { }
            try
            {

                db.ExecuteNonQuery(string.Format(@"
            CREATE TABLE {0} (
             id     uuid,
             oid    uuid,
             progress double,
             last_access timestamp,
             PRIMARY KEY (id,oid));
            ", TableName("trans")));
            }
            catch (CassandraException) { }
            try
            {
                db.ExecuteNonQuery(string.Format(@"
            CREATE TABLE {0} (
             id     uuid,
             oid    uuid,
             what   int,
             when   timestamp,
             info   text,
             PRIMARY KEY (id,oid,what,when));
            ", TableName("out")));
            }
            catch (CassandraException) { }
        }

        private static void BigDecimalTest()
        {
            using (var db = new CassandraContext(keyspace: KeyspaceName, server: Server))
            {

                // arrange
                db.ExecuteNonQuery("CREATE TABLE OfferReservation2 (KEY text PRIMARY KEY) WITH comparator = text AND default_validation = decimal");
                db.ExecuteNonQuery("INSERT INTO OfferReservation2 (KEY, 'MyColumn') VALUES ('Key0', 1000000000000000000)");
                db.ExecuteNonQuery("INSERT INTO OfferReservation2 (KEY, 'MyColumn') VALUES ('Key1', 0.25)");
                db.ExecuteNonQuery("INSERT INTO OfferReservation2 (KEY, 'MyColumn') VALUES ('Key2', 2000000000000.1234)");
                db.ExecuteNonQuery("INSERT INTO OfferReservation2 (KEY, 'MyColumn') VALUES ('Key3', -0.25)");
                db.ExecuteNonQuery("INSERT INTO OfferReservation2 (KEY, 'MyColumn') VALUES ('Key4', -0.25122333)");

                var actual = db.ExecuteQuery("SELECT * FROM OfferReservation2");

                var results = actual.ToList();

                var firstValue = (decimal)results.First(x => x.Key == "Key0")["MyColumn"];
                var secondValue = (decimal)results.First(x => x.Key == "Key1")["MyColumn"];
                var thirdValue = (decimal)results.First(x => x.Key == "Key2")["MyColumn"];
                var fourthValue = (decimal)results.First(x => x.Key == "Key3")["MyColumn"];
                var fifthValue = (decimal)results.First(x => x.Key == "Key4")["MyColumn"];

                ConsoleHeader("Returned data from Big Decimal Test");
                ConsoleHeader(firstValue.ToString());
                ConsoleHeader(secondValue.ToString());
                ConsoleHeader(thirdValue.ToString());
                ConsoleHeader(fourthValue.ToString());
                ConsoleHeader(fifthValue.ToString());
            }
        }

		private static void SetupKeyspace()
		{
			using (var db = new CassandraContext(keyspace: KeyspaceName, server: Server))
			{
				if (db.KeyspaceExists(KeyspaceName))
					db.DropKeyspace(KeyspaceName);

				var keyspace = new CassandraKeyspace(new CassandraKeyspaceSchema
				{
					Name = KeyspaceName,
				}, db);

				keyspace.TryCreateSelf();

				// create column family using CQL
				db.ExecuteNonQuery(@"
				CREATE COLUMNFAMILY Posts (
				KEY ascii PRIMARY KEY,
				Title text,
				Body text,
				Author text,
				PostedOn timestamp
				);");

				// create column family using API
				keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema
				{
					FamilyName = "Tags",
					KeyValueType = CassandraType.AsciiType,
					ColumnNameType = CassandraType.Int32Type,
					DefaultColumnValueType = CassandraType.UTF8Type
				});

				// create super column family using API
				keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema
				{
					FamilyName = "Comments",
					FamilyType = ColumnType.Super,
					KeyValueType = CassandraType.AsciiType,
					SuperColumnNameType = CassandraType.DateType,
					ColumnNameType = CassandraType.UTF8Type,
					DefaultColumnValueType = CassandraType.UTF8Type
				});
			}
		}

		public void ResetDatabase()
		{
			using (var session = new CassandraSession(ConnectionBuilder))
			using (var db = new CassandraContext(session))
			{
				db.ThrowErrors = true;
				db.TryDropKeyspace(Keyspace);

				var keyspace = new CassandraKeyspace(new CassandraKeyspaceSchema {
					Name = Keyspace
				}, db);
				db.Keyspace = keyspace;

				keyspace.TryCreateSelf();
				keyspace.TryCreateColumnFamily<AsciiType>("Standard");
				keyspace.TryCreateColumnFamily<AsciiType, AsciiType>("Super");
				keyspace.TryCreateColumnFamily<AsciiType>("StandardAsciiType");
				keyspace.TryCreateColumnFamily<BytesType>("StandardBytesType");
				keyspace.TryCreateColumnFamily<IntegerType>("StandardIntegerType");
				keyspace.TryCreateColumnFamily<LexicalUUIDType>("StandardLexicalUUIDType");
				keyspace.TryCreateColumnFamily<LongType>("StandardLongType");
				keyspace.TryCreateColumnFamily<TimeUUIDType>("StandardTimeUUIDType");
				keyspace.TryCreateColumnFamily<UTF8Type>("StandardUTF8Type");
				keyspace.TryCreateColumnFamily<UUIDType>("StandardUUIDType");
				keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema() {
					FamilyName = "Counters",
					ColumnNameType = CassandraType.AsciiType,
					DefaultColumnValueType = CassandraType.CounterColumnType
				});
				keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema {
					FamilyName = "StandardDecimalType",
					ColumnNameType = CassandraType.DecimalType
				});
				keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema {
					FamilyName = "StandardCompositeType",
					ColumnNameType = CassandraType.CompositeType(new[] { CassandraType.AsciiType, CassandraType.DoubleType })
				});
				keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema {
					FamilyName = "StandardDynamicCompositeType",
					ColumnNameType = CassandraType.DynamicCompositeType(new Dictionary<char, CassandraType> { { 'a', CassandraType.AsciiType }, { 'd', CassandraType.DoubleType } })
				});

				db.ExecuteNonQuery(@"
CREATE COLUMNFAMILY Users (
	Id int PRIMARY KEY,
	Name ascii,
	Email ascii,
	Age int
);", CqlVersion.Cql);
				db.ExecuteNonQuery(@"CREATE INDEX User_Age ON Users (Age);", CqlVersion.Cql);
				db.Keyspace.ClearCachedKeyspaceSchema();

				var family = db.GetColumnFamily<AsciiType>("Standard");
				var superFamily = db.GetColumnFamily<AsciiType, AsciiType>("Super");
				var userFamily = db.GetColumnFamily("Users");
				var counterFamily = db.GetColumnFamily("Counters");

				ResetFamily(family);
				ResetSuperFamily(superFamily);
				ResetUsersFamily(userFamily);
				ResetCounterColumnFamily(counterFamily);
			}
		}

        private static void CreateColumnFamilyWithUUIDOperator()
        {
            using (var db = new CassandraContext(keyspace: KeyspaceName, server: Server))
            {

                db.ExecuteNonQuery("CREATE TABLE TestCF (KEY text PRIMARY KEY) WITH comparator=uuid AND default_validation=text;");

                Guid columnName1 = GuidGenerator.GenerateTimeBasedGuid(DateTime.Now);

                Guid columnName2 = GuidGenerator.GenerateTimeBasedGuid(DateTime.Now);

                db.ExecuteNonQuery(string.Format("INSERT INTO TestCF (KEY, {0}, {1}) VALUES ('Key1', 'Value1', 'Value2')", columnName1, columnName2));

                List<ICqlRow> rows = db.ExecuteQuery("SELECT * FROM TestCF WHERE KEY = 'Key1'").ToList();

                Guid value = rows[0].Columns[1].ColumnName.GetValue<Guid>();

                ConsoleHeader("Returned data from db");
                ConsoleHeader(value.ToString());
            }
        }

        private static void TombstoneTest()
        {
            using (var db = new CassandraContext(keyspace: KeyspaceName, server: Server))
            {

                db.ExecuteNonQuery("CREATE TABLE OfferReservation (KEY int PRIMARY KEY) WITH comparator = text AND default_validation = float");
                db.ExecuteNonQuery("INSERT INTO OfferReservation (KEY, '25:100') VALUES (5, 0.25)");
                db.ExecuteNonQuery("INSERT INTO OfferReservation (KEY, '25:101') VALUES (5, 0.25)");
                db.ExecuteNonQuery("DELETE '25:100' FROM OfferReservation WHERE KEY = 5");

                List<ICqlRow> rows = db.ExecuteQuery("SELECT '25:100' FROM OfferReservation WHERE KEY = 5").ToList();

            }
        }

        public CassandraDatabaseSetup(bool volitile = false)
        {
            var keyspaceName = "Testing";
            var server = new Server("localhost");

            DB = new CassandraContext(keyspaceName, server);
            DB.ThrowErrors = true;

            var exists = DB.KeyspaceExists(keyspaceName);
            Users = new[] {
                new User { Id = 1, Name = "Darren Gemmell", Email = "[email protected]", Age = 32 },
                new User { Id = 2, Name = "Fernando Laubscher", Email = "[email protected]", Age = 23 },
                new User { Id = 3, Name = "Cody Millhouse", Email = "[email protected]", Age = 56 },
                new User { Id = 4, Name = "Emilia Thibert", Email = "[email protected]", Age = 67 },
                new User { Id = 5, Name = "Allyson Schurr", Email = "[email protected]", Age = 21 }
            };

            Family = DB.GetColumnFamily<AsciiType>("Standard");
            SuperFamily = DB.GetColumnFamily<AsciiType, AsciiType>("Super");
            UserFamily = DB.GetColumnFamily("Users");

            if (exists && !volitile)
                return;

            using (var session = DB.OpenSession())
            {
                if (exists)
                    DB.DropKeyspace(keyspaceName);

                var keyspace = new CassandraKeyspace(new CassandraKeyspaceSchema { Name = keyspaceName, Strategy = CassandraKeyspaceSchema.ReplicaPlacementStrategySimple, ReplicationFactor = 1 }, DB);
                DB.Keyspace = keyspace;

                keyspace.TryCreateSelf();
                keyspace.TryCreateColumnFamily<AsciiType>("Standard");
                keyspace.TryCreateColumnFamily<AsciiType, AsciiType>("Super");
                keyspace.TryCreateColumnFamily<AsciiType>("StandardAsciiType");
                keyspace.TryCreateColumnFamily<BytesType>("StandardBytesType");
                keyspace.TryCreateColumnFamily<IntegerType>("StandardIntegerType");
                keyspace.TryCreateColumnFamily<LexicalUUIDType>("StandardLexicalUUIDType");
                keyspace.TryCreateColumnFamily<LongType>("StandardLongType");
                keyspace.TryCreateColumnFamily<TimeUUIDType>("StandardTimeUUIDType");
                keyspace.TryCreateColumnFamily<UTF8Type>("StandardUTF8Type");
                keyspace.TryCreateColumnFamily<UUIDType>("StandardUUIDType");
                keyspace.TryCreateColumnFamily<CompositeType<LongType, UTF8Type>>("StandardCompositeType");
                keyspace.TryCreateColumnFamily<DynamicCompositeType>("StandardDynamicCompositeType");

                Family = DB.GetColumnFamily<AsciiType>("Standard");
                SuperFamily = DB.GetColumnFamily<AsciiType, AsciiType>("Super");

                Family.InsertColumn(TestKey1, "Test1", Math.PI);
                Family.InsertColumn(TestKey1, "Test2", Math.PI);
                Family.InsertColumn(TestKey1, "Test3", Math.PI);

                SuperFamily.InsertColumn(TestKey1, TestSuperName, "Test1", Math.PI);
                SuperFamily.InsertColumn(TestKey1, TestSuperName, "Test2", Math.PI);
                SuperFamily.InsertColumn(TestKey1, TestSuperName, "Test3", Math.PI);

                Family.InsertColumn(TestKey2, "Test1", Math.PI);
                Family.InsertColumn(TestKey2, "Test2", Math.PI);
                Family.InsertColumn(TestKey2, "Test3", Math.PI);

                SuperFamily.InsertColumn(TestKey2, TestSuperName, "Test1", Math.PI);
                SuperFamily.InsertColumn(TestKey2, TestSuperName, "Test2", Math.PI);
                SuperFamily.InsertColumn(TestKey2, TestSuperName, "Test3", Math.PI);

                DB.ExecuteNonQuery(@"
            CREATE COLUMNFAMILY Users (
            KEY int PRIMARY KEY,
            Name ascii,
            Email ascii,
            Age int
            );");
                DB.ExecuteNonQuery(@"CREATE INDEX User_Age ON Users (Age);");
                DB.Keyspace.ClearCachedKeyspaceSchema();
                UserFamily = DB.GetColumnFamily("Users");

                foreach (var user in Users)
                {
                    dynamic record = UserFamily.CreateRecord(user.Id);
                    record.Name = user.Name;
                    record.Email = user.Email;
                    record.Age = user.Age;

                    DB.Attach(record);
                }
                DB.SaveChanges();
            }
        }

        public void ResetUsersFamily(CassandraContext db)
        {
            db.ExecuteNonQuery(@"
            TRUNCATE users
            ;
            ");

            foreach (var user in Users)
            {
                db.ExecuteNonQuery(string.Format(@"
            INSERT INTO users(id, name, email, age)
            VALUES ({0},{1},{2},{3})
            ;
            ", user.Id.ToString(), Encode(user.Name), Encode(user.Email), user.Age.ToString()));
            }
        }

        public void ResetDatabase()
        {
            using (var session = new CassandraSession(ConnectionBuilder))
            using (var db = new CassandraContext(session))
            {
                db.ThrowErrors = true;
                db.TryDropKeyspace(Keyspace);

                var keyspace = new CassandraKeyspace(new CassandraKeyspaceSchema
                {
                    Name = Keyspace
                }, db);
                db.Keyspace = keyspace;

                keyspace.TryCreateSelf();

                db.ExecuteNonQuery(@"
            CREATE TABLE users (
            id int ,
            name ascii,
            email ascii,
            age int,
            PRIMARY KEY(id, email)
            );");

                ResetUsersFamily(db);
            }
        }

        public void ResetDatabase()
        {
            using (var session = new CassandraSession(ConnectionBuilder))
            using (var db = new CassandraContext(session))
            {
                db.ThrowErrors = true;
                db.TryDropKeyspace(Keyspace);

                var keyspace = new CassandraKeyspace(new CassandraKeyspaceSchema
                {
                    Name = Keyspace
                }, db);
                db.Keyspace = keyspace;

                keyspace.TryCreateSelf();
                keyspace.TryCreateColumnFamily<AsciiType>("Standard");
                keyspace.TryCreateColumnFamily<AsciiType, AsciiType>("Super");
                keyspace.TryCreateColumnFamily<AsciiType>("StandardAsciiType");
                keyspace.TryCreateColumnFamily<BytesType>("StandardBytesType");
                keyspace.TryCreateColumnFamily<IntegerType>("StandardIntegerType");
                keyspace.TryCreateColumnFamily<LexicalUUIDType>("StandardLexicalUUIDType");
                keyspace.TryCreateColumnFamily<LongType>("StandardLongType");
                keyspace.TryCreateColumnFamily<TimeUUIDType>("StandardTimeUUIDType");
                keyspace.TryCreateColumnFamily<UTF8Type>("StandardUTF8Type");
                keyspace.TryCreateColumnFamily<UUIDType>("StandardUUIDType");
                keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema
                {
                    FamilyName = "StandardCompositeType",
                    ColumnNameType = CassandraType.CompositeType(new[] { CassandraType.AsciiType, CassandraType.DoubleType })
                });
                keyspace.TryCreateColumnFamily(new CassandraColumnFamilySchema
                {
                    FamilyName = "StandardDynamicCompositeType",
                    ColumnNameType = CassandraType.DynamicCompositeType(new Dictionary<char, CassandraType> { { 'a', CassandraType.AsciiType }, { 'd', CassandraType.DoubleType } })
                });

                db.ExecuteNonQuery(string.Format(@"
            CREATE TABLE {0} (
            id int ,
            name ascii,
            email ascii,
            age int,
            PRIMARY KEY(id)
            );", toLowerIf(toLower, "Users")));

                //db.ExecuteNonQuery(@"CREATE INDEX User_Age ON users (Age);");
                db.Keyspace.ClearCachedKeyspaceSchema();

                var family = db.GetColumnFamily<AsciiType>("Standard");
                var superFamily = db.GetColumnFamily<AsciiType, AsciiType>("Super");
                var userFamily = db.GetColumnFamily(toLowerIf(toLower, "Users"));

                ResetFamily(family);
                ResetSuperFamily(superFamily);
                ResetUsersFamily(userFamily);
            }
        }

        public static CassandraJob GetJob(CassandraContext db, int hide_time_sec)
        {
            loop:
            dynamic job = null;
            for (int i = 0; i < 1000; i++)
            {
                var jobs = db.ExecuteQuery(SelectCQL_Main(1000)).ToArray();
                var mx = jobs.Length < 1000 ? jobs.Length - 1 : 1000;
                if (mx == -1)
                    break;
                job = jobs[rnd.Value.Next(mx)];
                if (job["hidden_till"] < ToUnixTime(DateTimeOffset.UtcNow))
                    break;
            }
            if (job != null)
            {
                var myID = Guid.NewGuid();
                var ret = new CassandraJob(job, myID);
                db.ExecuteNonQuery(HideRowCQL_Trans(ret.GetID(), ret.GetAckID()));
                var cnt = db.ExecuteQuery(SelectCQL_Trans(ret.GetID(), 1000)).ToArray();
                var counter = cnt.First()["count"];
                if (counter > 1)
                {
                    db.ExecuteNonQuery(DeleteRowCQL_Trans(ret.GetID()));
                    goto loop;
                }
                if (counter == 0)
                    goto loop;

                db.ExecuteNonQuery(HideRowCQL_Main2(ret.GetID(), hide_time_sec,ret.GetLogic(),ret.GetCommand()));

                return ret;
            }
            else
                return null;
        }

 public static void DeleteJob(CassandraContext db, CassandraJob job)
 {
     db.ExecuteNonQuery(DeleteRowCQL_Main((job as CassandraJob).GetID()));
        db.ExecuteNonQuery(DeleteRowCQL_Trans((job as CassandraJob).GetID()));
 }

 public static void AddJob(CassandraContext db, string logic, string command)
 {
     var id = Guid.NewGuid();
        db.ExecuteNonQuery(InsertRowCQL(id, logic, command));
 }