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