1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
-- 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
|
