ndb_index.test
上传用户:romrleung
上传日期:2022-05-23
资源大小:18897k
文件大小:7k
- -- source include/have_ndb.inc
- -- source include/not_embedded.inc
- --disable_warnings
- drop table if exists t1;
- --enable_warnings
- CREATE TABLE t1 (
- PORT varchar(16) NOT NULL,
- ACCESSNODE varchar(16) NOT NULL,
- POP varchar(48) NOT NULL,
- ACCESSTYPE int unsigned NOT NULL,
- CUSTOMER_ID varchar(20) collate latin1_bin NOT NULL,
- PROVIDER varchar(16),
- TEXPIRE int unsigned,
- NUM_IP int unsigned,
- LEASED_NUM_IP int unsigned,
- LOCKED_IP int unsigned,
- STATIC_DNS int unsigned,
- SUSPENDED_SERVICE int unsigned,
- SUSPENDED_REASON int unsigned,
- BGP_COMMUNITY int unsigned,
- INDEX CUSTOMER_ID_INDEX(CUSTOMER_ID),
- INDEX FQPN_INDEX(POP,ACCESSNODE,PORT),
- PRIMARY KEY(POP,ACCESSNODE,PORT,ACCESSTYPE)
- ) engine=ndbcluster;
- INSERT INTO t1 VALUES ('port67', 'node78', 'pop98', 1, 'kllopmn', 'pr_43', 121212, 1, 2, 3, 8, NULL, NULL, NULL);
- INSERT INTO t1 VALUES ('port67', 'node78', 'pop99', 2, 'klkighh', 'pr_44', 121213, 3, 3, 6, 7, NULL, NULL, NULL);
- INSERT INTO t1 VALUES ('port79', 'node79', 'pop79', 2, 'kpongfaa', 'pr_44', 981213, 2, 4, 10, 11, 2, 99, 1278);
-
- # Test select using port
- select port, accessnode, pop, accesstype from t1 where port='port67' order by accesstype;
- select port, accessnode, pop, accesstype from t1 where port='foo';
- # Test select using accessnode
- select port, accessnode, pop, accesstype from t1 where accessnode='node78' order by accesstype;
- select port, accessnode, pop, accesstype from t1 where accessnode='foo';
- # Test select using pop
- select port, accessnode, pop, accesstype from t1 where pop='pop98';
- select port, accessnode, pop, accesstype from t1 where pop='pop98';
- select port, accessnode, pop, accesstype from t1 where pop='pop98';
- select port, accessnode, pop, accesstype from t1 where pop='pop98' order by accesstype;
- # The following two querys will not return any rows since
- # the index used for access is case sensitive
- # They are thus disabled for now
- #select port, accessnode, pop, accesstype from t1 where pop='POP98';
- #select port, accessnode, pop, accesstype from t1 where pop='POP98' order by accesstype;
- select port, accessnode, pop, accesstype from t1 where pop='foo';
- # Test select using accesstype
- select port, accessnode, pop, accesstype from t1 where accesstype=1;
- select port, accessnode, pop, accesstype from t1 where accesstype=2 order by port;
- select port, accessnode, pop, accesstype from t1 where accesstype=98 order by port;
- # Test select using customer_id
- # NOTE! customer_id has a INDEX (ordered index in NDB), it's case sensitive!
- select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn';
- select port, accessnode, pop, accesstype from t1 where customer_id='KLLOPMN';
- select port, accessnode, pop, accesstype from t1 where customer_id='kLLoPMn';
- select port, accessnode, pop, accesstype from t1 where customer_id='foo';
- # Test select using provider
- select port, accessnode, pop, accesstype from t1 where provider='pr_43';
- select port, accessnode, pop, accesstype from t1 where provider='foo';
- # Test select using texpire
- select port, accessnode from t1 where texpire=121212;
- select port, accessnode from t1 where texpire=2323;
- # Test select using num_ip
- select port, accessnode, pop, accesstype from t1 where num_ip=1;
- select port, accessnode, pop, accesstype from t1 where num_ip=89;
- # Test select using leased_num_ip
- select port, accessnode, pop, accesstype from t1 where leased_num_ip=2;
- select port, accessnode, pop, accesstype from t1 where leased_num_ip=89;
- # Test select using locked_ip
- select port, accessnode, pop, accesstype from t1 where locked_ip=3;
- select port, accessnode, pop, accesstype from t1 where locked_ip=89;
- # Test select using static_dns
- select port, accessnode, pop, accesstype from t1 where static_dns=8;
- select port, accessnode, pop, accesstype from t1 where static_dns=89;
- # Test select using suspended_service
- select port, accessnode, pop, accesstype from t1 where suspended_service=8;
- select port, accessnode, pop, accesstype from t1 where suspended_service=89;
- # Test select using suspended_reason
- select port, accessnode, pop, accesstype from t1 where suspended_reason=NULL;
- select port, accessnode, pop, accesstype from t1 where suspended_reason=89;
- select port, accessnode, pop, accesstype from t1 where suspended_reason=0;
- # Test select using bgp_community
- select port, accessnode, pop, accesstype from t1 where bgp_community=NULL;
- select port, accessnode, pop, accesstype from t1 where bgp_community=89;
- select port, accessnode, pop, accesstype from t1 where bgp_community=0;
- # Test select using full primary key
- select port, accessnode, pop, accesstype from t1 where port='port67' and accessnode='node78' and pop='pop98' and accesstype=1;
- select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78' and pop='pop98';
- select port, accessnode, pop, accesstype from t1 where pop='pop98' and port='port67' and accesstype=1 and accessnode='node78';
- select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo' and accesstype=99;
- # Test select using partial primary key
- select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accesstype=1;
- select port, accessnode, pop, accesstype from t1 where accesstype=1 and accessnode='node78' and pop='pop98';
- select port, accessnode, pop, accesstype from t1 where port='port67' and accesstype=1 and accessnode='node78';
- select port, accessnode from t1 where port='foo' and accessnode='foo' and pop='foo';
- # Test select using CUSTOMER_ID_INDEX
- select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn';
- select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=1;
- select port, accessnode, pop, accesstype from t1 where customer_id='kllopmn' and accesstype=2;
- select port, accessnode, pop, accesstype from t1 where accesstype=2 and customer_id='kllopmn';
- # Test select using FQPN_INDEX
- select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67';
- #select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' order by accesstype;
- #select port, accessnode, pop, accesstype from t1 where accessnode='node78' and port='port67' and pop='pop98' order by accesstype;
- #select port, accessnode, pop, accesstype from t1 where port='port67' and pop='pop98' and accessnode='node78' order by accesstype;
- select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='kllopmn';
- select port, accessnode, pop, accesstype from t1 where pop='pop98' and accessnode='node78' and port='port67' and customer_id='foo';
- drop table t1;
- # End of 4.1 tests