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
|
-- source include/have_ndb.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;
|
