odbtools/odbctest/mql/test/basic/1.2.basic_data.mql

delete from /oktest/basic/mxfield with version
-- { onerror(continue) noerrinfo() }
;

delete from /oktest/basic with version
-- { onerror(continue) noerrinfo() }
;

delete from /oktest/basic2 with version
-- { onerror(continue) noerrinfo() }
;

delete from /oktest/alert_status_0000 with version
-- { onerror(continue) noerrinfo() }
;

delete from /oktest with version
-- { onerror(continue) noerrinfo() }
;

insert into /matrix/system/oktest (name, version, policy) values ('hello', 1, 1);

insert into /matrix/system/oktest (name, version, policy) values ('hello', 2, 2);

insert into /matrix/system/oktest (name, version, policy) values ('hello', 3, 3);

update /matrix/system/oktest set version = 4, policy = 4 where name = 'hello';

update /matrix/system/oktest set version = 5, policy = 5 where name = 'hello';

insert into /oktest/basic (v_varchar, v_text, v_int, v_bigint, v_float, v_double, v_smalldouble, v_bool, v_blob) values ('*oktest*', 'hello word 2003 123-456-789 中华人民共和国是中国的唯一合法政府，台湾是中国一部分。  Mercury, Venus2，Earch%。Mars$Uranus CeresAndJupiterOrSaturn #Neptune Pluto_Charon"\""', 5, 1234567890123456789, 1.1, 1.1234567890123456, 1.123456, true, "hello"  );

insert into /oktest/basic (v_varchar, v_text, v_int, v_bigint, v_float, v_double, v_smalldouble, v_bool) values ('*oktest*', 'hello word 2003 123-456-789 中华人民共和国是中国的唯一合法政府，台湾是中国一部分。  Mercury, Venus2，Earch%。Mars$Uranus CeresAndJupiterOrSaturn #Neptune Pluto_Charon"\""', 1, 1234567890123456789, 1.1, 1.1234567890123456, 1.123456, true  );

insert into /oktest/basic2 (name) values ('hello');

insert into /oktest/basic (v_varchar, v_text, v_int, v_bigint, v_float, v_double, v_smalldouble, v_bool) values ('2', 'because in both case, Cassandra guarantees that these queries performance will be proportional to the amount of data returned. In particular, if no users are born in 1981, then the second query performance will not depend of the number of user profile stored in the database (not directly at least: due to secondary index implementation consideration, this query may still depend on the number of node in the cluster, which indirectly depends on the amount of data stored. Nevertheless, the number of nodes will always be multiple number of magnitude lower than the number of user profile stored). Of course, both query may return very large result set in practice, but the amount of data returned can always be controlled by adding a LIMIT.', 1, 1234567890123456789, 1.1, 1.1234567890123456, 1.123456,false  );

insert into /oktest/basic (v_varchar, v_text, v_int, v_bigint, v_float, v_double, v_smalldouble, v_bool) values ('3', '{
			  "id": "omdb-query-query",
			  "bid": "omdb-query-query",
			  "type": "json-update",
			  "data": {
				"message": [
				{
				  "graph": {
					"nodes": [
					{
					  "_icon": "app",
					  "class": "/matrix/entity/app",
					  "id": "app:demoapp3",
					  "name": "app:demoapp3",
					  "status": 5
					},
					{
					  "class": "/matrix/entity/pod",
					  "id": "pod:web-demo-f6dsp"
					},
					{
					  "_icon": "mysql",
					  "class": "/matrix/entity/mysql",
					  "id": "mysql:demodb",
					  "name": "mysql:demodb",
					  "status": 0
					},
					{
					  "_icon": "biz",
					  "class": "/matrix/entity/biz",
					  "id": "biz:开发测试",
					  "name": "biz:开发测试",
					  "status": 4
					},
					{
					  "_icon": "linux",
					  "class": "/matrix/entity/linux",
					  "id": "linux:node2",
					  "name": "linux:node2",
					  "status": 0
					},
					{
					  "_icon": "mysql",
					  "class": "/matrix/entity/mysql",
					  "id": "mysql:appdb",
					  "name": "mysql:appdb",
					  "status": 0
					},
					{
					  "_icon": "app",
					  "class": "/matrix/entity/app",
					  "id": "app:demoapp2",
					  "name": "app:demoapp2",
					  "status": 0
					},
					{
					  "_icon": "app",
					  "class": "/matrix/entity/app",
					  "id": "app:tomcat-app3",
					  "name": "app:tomcat-app3",
					  "status": 5
					},
					{
					  "_icon": "linux",
					  "class": "/matrix/entity/linux",
					  "id": "linux:node1",
					  "name": "linux:node1",
					  "status": 0
					},
					{
					  "_icon": "pod",
					  "class": "/matrix/entity/pod",
					  "id": "pod:tomcat-pod1",
					  "name": "pod:tomcat-pod1",
					  "status": 4
					},
					{
					  "_icon": "linux",
					  "class": "/matrix/entity/linux",
					  "id": "linux:node4",
					  "name": "linux:node4",
					  "status": 0
					},
					{
					  "_icon": "app",
					  "class": "/matrix/entity/app",
					  "id": "app:demoapp1",
					  "name": "app:demoapp1",
					  "status": 0
					},
					{
					  "_icon": "pod",
					  "class": "/matrix/entity/pod",
					  "id": "pod:mysql-demo-pod1",
					  "name": "pod:mysql-demo-pod1",
					  "status": 4
					},
					{
					  "_icon": "pod",
					  "class": "/matrix/entity/pod",
					  "id": "pod:tomcat-pod2",
					  "name": "pod:tomcat-pod2",
					  "status": 4
					},
					{
					  "class": "/matrix/entity/pod",
					  "id": "pod:tomcat-pod3"
					},
					{
					  "_icon": "app",
					  "class": "/matrix/entity/app",
					  "id": "app:tomcat-app1",
					  "name": "app:tomcat-app1",
					  "status": 0
					},
					{
					  "_icon": "app",
					  "class": "/matrix/entity/app",
					  "id": "app:tomcat-app2",
					  "name": "app:tomcat-app2",
					  "status": 0
					},
					{
					  "class": "/matrix/entity/pod",
					  "id": "pod:web-demo-j6qxj"
					},
					{
					  "class": "/matrix/entity/pod",
					  "id": "pod:web-demo-9rfxj"
					},
					{
					  "_icon": "biz",
					  "class": "/matrix/entity/biz",
					  "id": "biz:数字国网",
					  "name": "biz:数字国网",
					  "status": 4
					}
					],
					"edges": [
					{
					  "attrs": {},
					  "class": "connect",
					  "id": "app:demoapp1-mysql:demodb",
					  "source": "app:demoapp1",
					  "target": "mysql:demodb"
					},
					{
					  "attrs": {},
					  "class": "contain",
					  "id": "biz:开发测试-app:demoapp2",
					  "source": "biz:开发测试",
					  "target": "app:demoapp2"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "mysql:appdb-linux:node4",
					  "source": "mysql:appdb",
					  "target": "linux:node4"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "mysql:demodb-pod:mysql",
					  "source": "mysql:demodb",
					  "target": "pod:mysql"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "app:tomcat-app2",
					  "source": "app:tomcat",
					  "target": "app2"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "pod:tomcat-pod2",
					  "source": "pod:tomcat",
					  "target": "pod2"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "app:demoapp1-pod:web",
					  "source": "app:demoapp1",
					  "target": "pod:web"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "pod:mysql-demo",
					  "source": "pod:mysql",
					  "target": "demo"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "app:demoapp2-pod:web",
					  "source": "app:demoapp2",
					  "target": "pod:web"
					},
					{
					  "attrs": {},
					  "class": "connect",
					  "id": "app:demoapp3-mysql:demodb",
					  "source": "app:demoapp3",
					  "target": "mysql:demodb"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "app:demoapp3-pod:web",
					  "source": "app:demoapp3",
					  "target": "pod:web"
					},
					{
					  "attrs": {
						"floatp": 3.4,
						"intp": 1
					  },
					  "class": "contain",
					  "id": "biz:数字国网-app:tomcat",
					  "source": "biz:数字国网",
					  "target": "app:tomcat"
					},
					{
					  "attrs": {},
					  "class": "connect",
					  "id": "app:tomcat-app3",
					  "source": "app:tomcat",
					  "target": "app3"
					},
					{
					  "attrs": {},
					  "class": "contain",
					  "id": "biz:开发测试-app:demoapp3",
					  "source": "biz:开发测试",
					  "target": "app:demoapp3"
					},
					{
					  "attrs": {},
					  "class": "contain",
					  "id": "biz:开发测试-app:demoapp1",
					  "source": "biz:开发测试",
					  "target": "app:demoapp1"
					},
					{
					  "attrs": {},
					  "class": "runon",
					  "id": "pod:tomcat-pod1",
					  "source": "pod:tomcat",
					  "target": "pod1"
					},
					{
					  "attrs": {},
					  "class": "connect",
					  "id": "app:tomcat-app1",
					  "source": "app:tomcat",
					  "target": "app1"
					},
					{
					  "attrs": {},
					  "class": "connect",
					  "id": "app:demoapp2-mysql:demodb",
					  "source": "app:demoapp2",
					  "target": "mysql:demodb"
					}
					],
					"paths": null,
					"diff": null,
					"pathtags": null
				  }
				}
				],
				"meta": {
				"type": "graph"
				},
				"status": "ok"
			  }
			}', 1, 1234567890123456789, 1.1, 1.1234567890123456, 1.123456, true  );

-- 因 lucene 索引问题，新插入数据可能无法按索引条件立即查出，需增加 refresh 标记，或等待一定时间
-- 如果 refresh 或等待一定时间后，仍然无法按索引条件查出，需要重启 cassandra
-- { sleep(5s) }
;

select count(*) from /oktest/basic refresh
-- { output() count(1) equal(0,count,3)  }
;

select * from /oktest/basic
-- { output() count(3) matchcount(v_varchar, "*oktest*", v_text, "hello word 2003 123-456-789 中华人民共和国是中国的唯一合法政府，台湾是中国一部分。  Mercury\u002c Venus2，Earch%。Mars$Uranus CeresAndJupiterOrSaturn #Neptune Pluto_Charon\"\\\"\"", 1) }
;

select * from /oktest/basic where v_varchar=='*oktest*'
-- { output() count(1) }
;

select * from / where v_varchar=='*oktest*'
-- { output() count(1) }
;

select * from /oktest/ where v_varchar=='*oktest*'
-- { output() count(1) }
;

select * from /oktest/basic where v_int = 1
-- { output() count(3) }
;

select * from /oktest/basic where v_int > 0
-- { output() count(3) }
;

select * from /oktest/basic where v_int < 2
-- { output() count(3) }
;

select * from /oktest/basic where v_int >= 1
-- { output() count(3) }
;

select * from /oktest/basic where v_int <= 1
-- { output() count(3) }
;

select * from /oktest/basic where v_int >= 1.1
-- { output() count(0) }
;

select * from /oktest/basic where v_int > 1
-- { output() count(0) }
;

select * from /oktest/basic where v_bool = true
-- { output() count(2) }
;

select * from /oktest/basic where v_bool = false
-- { output() count(1) }
;

select * from /oktest/basic where v_float >= 1.1
-- { output() count(3) }
;

select * from /oktest/basic where v_float > 1
-- { output() count(3) }
;

select * from /oktest/basic where v_float = 1.1
-- { output() count(3) }
;

select * from /oktest/basic where v_float < 2
-- { output() count(3) }
;

select * from /oktest/basic where v_float <= 1.1
-- { output() count(3) }
;

select * from /oktest/basic where v_double >= 1.1234567890123456
-- { output() count(3) }
;

select * from /oktest/basic where v_smalldouble >= 1.123456
-- { output() count(3) }
;

select * from /oktest/basic where v_double > 1
-- { output() count(3) }
;

select * from /oktest/basic where v_double = 1.1234567890123456
-- { output() count(3) }
;

select * from /oktest/basic where v_double < 2
-- { output() count(3) }
;

select * from /oktest/basic where v_double <= 1.1234567890123456
-- { output() count(3) }
;

select * from /oktest/basic where v_varchar not in('2', '3')
-- { output() count(1) }
;

select * from /oktest/basic where v_text=^'hell'
-- { output() count(1) }
;

select *from /oktest/basic where v_text='*20*'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='*123-456*'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='*中*'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='*magni*'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='hello'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='2003'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='123-456-789'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='中国'
-- { output() count(1) }
;

select * from /oktest/basic where v_text='magnitude'
-- { output() count(1) }
;

insert into /oktest/basic (v_varchar, v_text, v_int, v_bigint, v_float, v_double, v_smalldouble, v_bool) values ('2', 'hhhhhh', 1, 1534567890123456789, 1.3, 2.1234567890123456, 1.223456, true  )
;

select count(*) from /oktest/basic group by v_varchar
-- { output() count(3) }
;

select sum(v_float), min(v_int), max(v_double) , avg(v_int) as avgint, v_varchar from /oktest/basic group by v_varchar
-- { output() count(3) }
;

-- { output() count(1) }
select * from /system/field where name='oktest_basic:target' and namespace='oktest'
;