1 // Copyright (c) 1999-2018 David Muse
2 // See the file COPYING for more information.
3
4 #include <rudiments/charstring.h>
5 #include <rudiments/process.h>
6 #include <rudiments/datetime.h>
7 #include <sqlrelay/sqlrclient.h>
8 #include <string.h>
9 #include <stdlib.h>
10 #include <stdio.h>
11
12 sqlrconnection *con;
13 sqlrcursor *cur;
14 sqlrconnection *secondcon;
15 sqlrcursor *secondcur;
16
checkSuccess(const char * value,const char * success)17 void checkSuccess(const char *value, const char *success) {
18
19 if (!success) {
20 if (!value) {
21 printf("success ");
22 return;
23 } else {
24 printf("%s!=%s\n",value,success);
25 printf("failure ");
26 delete cur;
27 delete con;
28 process::exit(1);
29 }
30 }
31
32 if (!charstring::compare(value,success)) {
33 printf("success ");
34 } else {
35 printf("%s!=%s\n",value,success);
36 printf("failure ");
37 delete cur;
38 delete con;
39 process::exit(1);
40 }
41 }
42
checkSuccess(const char * value,const char * success,size_t length)43 void checkSuccess(const char *value, const char *success, size_t length) {
44
45 if (!success) {
46 if (!value) {
47 printf("success ");
48 return;
49 } else {
50 printf("%s!=%s\n",value,success);
51 printf("failure ");
52 delete cur;
53 delete con;
54 process::exit(1);
55 }
56 }
57
58 if (!strncmp(value,success,length)) {
59 printf("success ");
60 } else {
61 printf("%s!=%s\n",value,success);
62 printf("failure ");
63 delete cur;
64 delete con;
65 process::exit(1);
66 }
67 }
68
checkSuccess(int value,int success)69 void checkSuccess(int value, int success) {
70
71 if (value==success) {
72 printf("success ");
73 } else {
74 printf("%d!=%d\n",value,success);
75 printf("failure ");
76 delete cur;
77 delete con;
78 process::exit(1);
79 }
80 }
81
checkSuccess(double value,double success)82 void checkSuccess(double value, double success) {
83
84 if (value==success) {
85 printf("success ");
86 } else {
87 printf("%f!=%f\n",value,success);
88 printf("failure ");
89 delete cur;
90 delete con;
91 process::exit(1);
92 }
93 }
94
main(int argc,char ** argv)95 int main(int argc, char **argv) {
96
97 // instantiation
98 con=new sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
99 "test","test",0,1);
100 cur=new sqlrcursor(con);
101
102 con->setClientInfo("extensionstest");
103
104
105 printf("IGNORE SELECT DATABASE:\n");
106 const char *originaldb=con->getCurrentDatabase();
107 checkSuccess((originaldb!=NULL),true);
108 checkSuccess(con->selectDatabase("nonexistentdb"),true);
109 checkSuccess(con->getCurrentDatabase(),originaldb);
110 printf("\n\n");
111
112
113 printf("TRANSLATE BIND VARIABLES:\n");
114 cur->prepareQuery("select :1 from dual where 'hel''lo'='hel''lo' and 1=:2 and 2=:3");
115 cur->validateBinds();
116 cur->inputBind("1","hello");
117 cur->inputBind("2",1);
118 cur->inputBind("3",2);
119 checkSuccess(cur->validBind("1"),true);
120 checkSuccess(cur->validBind("2"),true);
121 checkSuccess(cur->validBind("3"),true);
122 checkSuccess(cur->validBind("4"),false);
123 checkSuccess(cur->countBindVariables(),3);
124 checkSuccess(cur->executeQuery(),1);
125 checkSuccess(cur->getField(0,(uint32_t)0),"hello");
126 cur->clearBinds();
127 printf("\n");
128
129 cur->prepareQuery("select @1 from dual where 'hel''lo'='hel''lo' and 1=@2 and 2=@3");
130 cur->validateBinds();
131 cur->inputBind("1","hello");
132 cur->inputBind("2",1);
133 cur->inputBind("3",2);
134 checkSuccess(cur->validBind("1"),true);
135 checkSuccess(cur->validBind("2"),true);
136 checkSuccess(cur->validBind("3"),true);
137 checkSuccess(cur->validBind("4"),false);
138 checkSuccess(cur->countBindVariables(),3);
139 checkSuccess(cur->executeQuery(),1);
140 checkSuccess(cur->getField(0,(uint32_t)0),"hello");
141 cur->clearBinds();
142 printf("\n");
143
144 cur->prepareQuery("select $1 from dual where 'hel''lo'='hel''lo' and 1=$2 and 2=$3");
145 cur->validateBinds();
146 cur->inputBind("1","hello");
147 cur->inputBind("2",1);
148 cur->inputBind("3",2);
149 checkSuccess(cur->validBind("1"),true);
150 checkSuccess(cur->validBind("2"),true);
151 checkSuccess(cur->validBind("3"),true);
152 checkSuccess(cur->validBind("4"),false);
153 checkSuccess(cur->countBindVariables(),3);
154 checkSuccess(cur->executeQuery(),1);
155 checkSuccess(cur->getField(0,(uint32_t)0),"hello");
156 cur->clearBinds();
157 printf("\n");
158
159 cur->prepareQuery("select ? from dual where 'hel''lo'='hel''lo' and 1=? and 2=?");
160 cur->validateBinds();
161 cur->inputBind("1","hello");
162 cur->inputBind("2",1);
163 cur->inputBind("3",2);
164 checkSuccess(cur->validBind("1"),true);
165 checkSuccess(cur->validBind("2"),true);
166 checkSuccess(cur->validBind("3"),true);
167 checkSuccess(cur->validBind("4"),false);
168 checkSuccess(cur->countBindVariables(),3);
169 checkSuccess(cur->executeQuery(),1);
170 checkSuccess(cur->getField(0,(uint32_t)0),"hello");
171 cur->clearBinds();
172 printf("\n\n");
173
174
175 printf("FAKE INPUT BIND VARIABLES:\n");
176 cur->prepareQuery("select '',1,'',:hello,'''','\\'' from dual where 1=:one");
177 cur->inputBind("hello","hello");
178 cur->inputBind("one","1");
179 cur->inputBind("nonexistentvar","nonexistentval");
180 checkSuccess(cur->executeQuery(),1);
181 checkSuccess(cur->getField(0,(uint32_t)0),"");
182 checkSuccess(cur->getField(0,(uint32_t)1),"1");
183 checkSuccess(cur->getField(0,(uint32_t)2),"");
184 checkSuccess(cur->getField(0,(uint32_t)3),"hello");
185 checkSuccess(cur->getField(0,(uint32_t)4),"'");
186 checkSuccess(cur->getField(0,(uint32_t)5),"'");
187 printf("\n\n");
188
189
190 printf("ISOLATION LEVELS: \n");
191
192 // set autocommit off
193 checkSuccess(con->autoCommitOff(),1);
194
195 // create a table
196 cur->sendQuery("drop table testtable");
197 checkSuccess(cur->sendQuery("create table testtable (col1 int)"),1);
198
199 // open a second connection and set autocommit off there too
200 secondcon=new sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
201 "test","test",0,1);
202 secondcur=new sqlrcursor(secondcon);
203 checkSuccess(secondcon->autoCommitOff(),1);
204
205 // change the isolation level
206 checkSuccess(secondcur->sendQuery("alter session set isolation_level=serializable"),1);
207 printf("\n");
208
209 // in the second connection, select from the table, it should be empty
210 checkSuccess(secondcur->sendQuery("select * from testtable"),1);
211 checkSuccess(secondcur->rowCount(),0);
212
213 // in the first connection, insert a row into the table
214 checkSuccess(cur->sendQuery("insert into testtable values (1)"),1);
215
216 // in the second connection, select again, it should still be empty
217 checkSuccess(secondcur->sendQuery("select * from testtable"),1);
218 checkSuccess(secondcur->rowCount(),0);
219
220 // in the first connecton, commit
221 checkSuccess(con->commit(),1);
222 printf("\n");
223
224 // in the second connection, select again, it should STILL be empty
225 checkSuccess(secondcur->sendQuery("select * from testtable"),1);
226 checkSuccess(secondcur->rowCount(),0);
227
228 // end the second connections sesssion and select again,
229 // finally it should see the row
230 secondcon->endSession();
231 checkSuccess(secondcur->sendQuery("select * from testtable"),1);
232 checkSuccess(secondcur->rowCount(),1);
233
234 // clean up
235 delete secondcur;
236 delete secondcon;
237 delete cur;
238 delete con;
239 con=new sqlrconnection("sqlrelay",9000,"/tmp/test.socket",
240 "test","test",0,1);
241 cur=new sqlrcursor(con);
242 checkSuccess(cur->sendQuery("drop table testtable"),1);
243 con->setClientInfo("extensionstest");
244 printf("\n\n");
245
246
247 printf("SQLRCMD CSTAT: \n");
248 checkSuccess(cur->sendQuery("sqlrcmd cstat"),1);
249 checkSuccess(cur->colCount(),9);
250 printf("\n");
251
252 checkSuccess(cur->getColumnName((uint32_t)0),"INDEX");
253 checkSuccess(cur->getColumnName(1),"MINE");
254 checkSuccess(cur->getColumnName(2),"PROCESSID");
255 checkSuccess(cur->getColumnName(3),"CONNECT");
256 checkSuccess(cur->getColumnName(4),"STATE");
257 checkSuccess(cur->getColumnName(5),"STATE_TIME");
258 checkSuccess(cur->getColumnName(6),"CLIENT_ADDR");
259 checkSuccess(cur->getColumnName(7),"CLIENT_INFO");
260 checkSuccess(cur->getColumnName(8),"SQL_TEXT");
261 printf("\n");
262
263 checkSuccess(cur->getColumnType((uint32_t)0),"NUMBER");
264 checkSuccess(cur->getColumnType(1),"VARCHAR2");
265 checkSuccess(cur->getColumnType(2),"NUMBER");
266 checkSuccess(cur->getColumnType(3),"NUMBER");
267 checkSuccess(cur->getColumnType(4),"VARCHAR2");
268 checkSuccess(cur->getColumnType(5),"NUMBER");
269 checkSuccess(cur->getColumnType(6),"VARCHAR2");
270 checkSuccess(cur->getColumnType(7),"VARCHAR2");
271 checkSuccess(cur->getColumnType(8),"VARCHAR2");
272 printf("\n");
273
274 checkSuccess(cur->getColumnLength((uint32_t)0),10);
275 checkSuccess(cur->getColumnLength(1),1);
276 checkSuccess(cur->getColumnLength(2),10);
277 checkSuccess(cur->getColumnLength(3),12);
278 checkSuccess(cur->getColumnLength(4),25);
279 checkSuccess(cur->getColumnLength(5),12);
280 checkSuccess(cur->getColumnLength(6),24);
281 checkSuccess(cur->getColumnLength(7),511);
282 checkSuccess(cur->getColumnLength(8),511);
283 printf("\n");
284
285 checkSuccess(cur->getColumnPrecision((uint32_t)0),10);
286 checkSuccess(cur->getColumnPrecision(1),0);
287 checkSuccess(cur->getColumnPrecision(2),10);
288 checkSuccess(cur->getColumnPrecision(3),12);
289 checkSuccess(cur->getColumnPrecision(4),0);
290 checkSuccess(cur->getColumnPrecision(5),12);
291 checkSuccess(cur->getColumnPrecision(6),0);
292 checkSuccess(cur->getColumnPrecision(7),0);
293 checkSuccess(cur->getColumnPrecision(8),0);
294 printf("\n");
295
296 checkSuccess(cur->getColumnScale((uint32_t)0),0);
297 checkSuccess(cur->getColumnScale(1),0);
298 checkSuccess(cur->getColumnScale(2),0);
299 checkSuccess(cur->getColumnScale(3),0);
300 checkSuccess(cur->getColumnScale(4),0);
301 checkSuccess(cur->getColumnScale(5),2);
302 checkSuccess(cur->getColumnScale(6),0);
303 checkSuccess(cur->getColumnScale(7),0);
304 checkSuccess(cur->getColumnScale(8),0);
305 printf("\n");
306
307 checkSuccess(cur->getColumnIsNullable((uint32_t)0),0);
308 checkSuccess(cur->getColumnIsNullable(1),0);
309 checkSuccess(cur->getColumnIsNullable(2),0);
310 checkSuccess(cur->getColumnIsNullable(3),0);
311 checkSuccess(cur->getColumnIsNullable(4),0);
312 checkSuccess(cur->getColumnIsNullable(5),0);
313 checkSuccess(cur->getColumnIsNullable(6),0);
314 checkSuccess(cur->getColumnIsNullable(7),1);
315 checkSuccess(cur->getColumnIsNullable(8),1);
316 printf("\n");
317
318 checkSuccess(cur->getField(0,(uint32_t)0),"0");
319 checkSuccess(cur->getField(0,(uint32_t)1),"*");
320 checkSuccess(cur->getField(0,(uint32_t)4),"RETURN_RESULT_SET");
321 // 127.0.0.1 on Windows
322 //checkSuccess(cur->getField(0,(uint32_t)6),"UNIX");
323 checkSuccess(cur->getField(0,(uint32_t)7),"extensionstest");
324 checkSuccess(cur->getField(0,(uint32_t)8),"sqlrcmd cstat");
325 printf("\n\n");
326
327
328 printf("SQLRCMD GSTAT: \n");
329 checkSuccess(cur->sendQuery("sqlrcmd gstat"),1);
330
331 checkSuccess(cur->colCount(),2);
332
333 checkSuccess(cur->getColumnName((uint32_t)0),"KEY");
334 checkSuccess(cur->getColumnName(1),"VALUE");
335
336 checkSuccess(cur->getColumnType((uint32_t)0),"VARCHAR2");
337 checkSuccess(cur->getColumnType(1),"VARCHAR2");
338
339 checkSuccess(cur->getColumnLength((uint32_t)0),40);
340 checkSuccess(cur->getColumnLength(1),40);
341
342 checkSuccess(cur->getField(0,(uint32_t)0),"start");
343 checkSuccess(cur->getField(1,(uint32_t)0),"uptime");
344 checkSuccess(cur->getField(2,(uint32_t)0),"now");
345 checkSuccess(cur->getField(3,(uint32_t)0),"access_count");
346 checkSuccess(cur->getField(4,(uint32_t)0),"query_total");
347 checkSuccess(cur->getField(5,(uint32_t)0),"qpm");
348 checkSuccess(cur->getField(6,(uint32_t)0),"qpm_1");
349 checkSuccess(cur->getField(7,(uint32_t)0),"qpm_5");
350 checkSuccess(cur->getField(8,(uint32_t)0),"qpm_15");
351 checkSuccess(cur->getField(9,(uint32_t)0),"select_1");
352 checkSuccess(cur->getField(10,(uint32_t)0),"select_5");
353 checkSuccess(cur->getField(11,(uint32_t)0),"select_15");
354 checkSuccess(cur->getField(12,(uint32_t)0),"insert_1");
355 checkSuccess(cur->getField(13,(uint32_t)0),"insert_5");
356 checkSuccess(cur->getField(14,(uint32_t)0),"insert_15");
357 checkSuccess(cur->getField(15,(uint32_t)0),"update_1");
358 checkSuccess(cur->getField(16,(uint32_t)0),"update_5");
359 checkSuccess(cur->getField(17,(uint32_t)0),"update_15");
360 checkSuccess(cur->getField(18,(uint32_t)0),"delete_1");
361 checkSuccess(cur->getField(19,(uint32_t)0),"delete_5");
362 checkSuccess(cur->getField(20,(uint32_t)0),"delete_15");
363 checkSuccess(cur->getField(21,(uint32_t)0),"etc_1");
364 checkSuccess(cur->getField(22,(uint32_t)0),"etc_5");
365 checkSuccess(cur->getField(23,(uint32_t)0),"etc_15");
366 checkSuccess(cur->getField(24,(uint32_t)0),"sqlrcmd_1");
367 checkSuccess(cur->getField(25,(uint32_t)0),"sqlrcmd_5");
368 checkSuccess(cur->getField(26,(uint32_t)0),"sqlrcmd_15");
369 checkSuccess(cur->getField(27,(uint32_t)0),"max_listener");
370 checkSuccess(cur->getField(28,(uint32_t)0),"max_listener_error");
371 checkSuccess(cur->getField(29,(uint32_t)0),"busy_listener");
372 checkSuccess(cur->getField(30,(uint32_t)0),"peak_listener");
373 checkSuccess(cur->getField(31,(uint32_t)0),"connection");
374 checkSuccess(cur->getField(32,(uint32_t)0),"session");
375 checkSuccess(cur->getField(33,(uint32_t)0),"peak_session");
376 checkSuccess(cur->getField(34,(uint32_t)0),"peak_session_1min");
377 checkSuccess(cur->getField(35,(uint32_t)0),"peak_session_1min_time");
378 printf("\n\n");
379
380
381 printf("SESSION QUERIES: Date Format\n");
382 checkSuccess(cur->sendQuery("select sysdate from dual"),1);
383 datetime dt;
384 dt.getSystemDateAndTime();
385 const char *field=cur->getField(0,(uint32_t)0);
386 char *day=charstring::subString(field,0,1);
387 char *month=charstring::subString(field,3,4);
388 char *year=charstring::subString(field,6,9);
389 char *hour=charstring::subString(field,11,12);
390 char *minute=charstring::subString(field,14,15);
391 checkSuccess((int)charstring::toInteger(day),(int)dt.getDayOfMonth());
392 checkSuccess((int)charstring::toInteger(month),(int)dt.getMonth());
393 checkSuccess((int)charstring::toInteger(year),(int)dt.getYear());
394 checkSuccess((int)charstring::toInteger(hour),(int)dt.getHour());
395 int dbmin=(int)charstring::toInteger(minute);
396 int min=(int)dt.getMinutes();
397 bool success=((dbmin==min) || (dbmin==min-1) || (dbmin-1==min));
398 checkSuccess(success,1);
399 delete[] year;
400 delete[] day;
401 delete[] month;
402 delete[] hour;
403 delete[] minute;
404 printf("\n\n");
405
406
407 printf("FILTERS:\n");
408 checkSuccess(cur->sendQuery("select * from badstring"),0);
409 checkSuccess(cur->errorMessage(),"badstring encountered");
410 checkSuccess(cur->sendQuery("select * from badregex"),0);
411 checkSuccess(cur->errorMessage(),"badregex encountered");
412 checkSuccess(cur->errorNumber(),100);
413 checkSuccess(cur->sendQuery("select * from badpattern"),0);
414 printf("\n\n");
415
416 delete cur;
417 delete con;
418
419 return 0;
420 }
421