|
|
1.32 misha 1: /** @file
2: Parser PgSQL driver.
3:
4: Copyright(c) 2001, 2003 ArtLebedev Group (http://www.artlebedev.com)
5:
6: Author: Alexandr Petrosian <paf@design.ru> (http://paf.design.ru)
7:
8: 2007.10.25 using PgSQL 8.1.5
9: */
10: static const char *RCSId="$Id: parser3pgsql.C,v 1.30 2008-06-26 15:49:40 misha Exp $";
11:
12: #include "config_includes.h"
13:
14: #include "pa_sql_driver.h"
15:
16: #include <libpq-fe.h>
17: #include <libpq/libpq-fs.h>
18:
19: // from catalog/pg_type.h
20: #define BOOLOID 16
21: #define INT8OID 20
22: #define INT2OID 21
23: #define INT4OID 23
24: #define OIDOID 26
25: #define FLOAT4OID 700
26: #define FLOAT8OID 701
27: #define DATEOID 1082
28: #define TIMEOID 1083
29: #define TIMESTAMPOID 1114
30: #define TIMESTAMPTZOID 1184
31: #define TIMETZOID 1266
32: #define NUMERICOID 1700
33:
34: // LO_BUFSIZE from interfaces\libpq\fe-lobj.c = 8192 (0x2000)
35: // actually writing chunks of that size failed, reduced it twice
36: #define LO_BUFSIZE 0x1000
37:
38:
39: #include "ltdl.h"
40:
41: #define MAX_COLS 500
42:
43: #define MAX_STRING 0x400
44: #define MAX_NUMBER 20
45:
46: #if _MSC_VER
47: # define snprintf _snprintf
48: # define strcasecmp _stricmp
49: #endif
50:
51: #ifndef max
52: inline int max(int a,int b){ return a>b?a:b; }
53: inline int min(int a,int b){ return a<b?a:b; }
54: #endif
55:
56: static char *lsplit(char *string, char delim){
57: if(string){
58: if(char *v=strchr(string, delim)){
59: *v=0;
60: return v+1;
61: }
62: }
63: return 0;
64: }
65:
66: static char *lsplit(char **string_ref, char delim){
67: char *result=*string_ref;
68: char *next=lsplit(*string_ref, delim);
69: *string_ref=next;
70: return result;
71: }
72:
73: static char* rsplit(char* string, char delim){
74: if(string){
75: if(char* v=strrchr(string, delim)){
76: *v=0;
77: return v+1;
78: }
79: }
80: return NULL;
81: }
82:
83: static void toupper_str(char *out, const char *in, size_t size){
84: while(size--)
85: *out++=(char)toupper(*in++);
86: }
87:
88: struct Connection {
89: SQL_Driver_services* services;
90:
91: PGconn *conn;
92: const char* client_charset;
93: bool autocommit;
94: bool without_default_transactions;
95: };
96:
97: /**
98: PgSQL server driver
99: */
100: class PgSQL_Driver : public SQL_Driver {
101: public:
102:
103: PgSQL_Driver() : SQL_Driver() {
104: }
105:
106: /// get api version
107: int api_version(){ return SQL_DRIVER_API_VERSION; }
108:
109: /// initialize driver by loading sql dynamic link library
110: const char *initialize(char *dlopen_file_spec){
111: return dlopen_file_spec?
112: dlink(dlopen_file_spec):"client library column is empty";
113: }
114:
115: #define throwPQerror connection.services->_throw(PQerrorMessage(connection.conn))
116: #define PQclear_throw(msg) { \
117: PQclear(res); \
118: connection.services->_throw(msg); \
119: }
120: #define PQclear_throwPQerror PQclear_throw(PQerrorMessage(connection.conn))
121:
122: /** connect
123: @param url
124: format: @b user:pass@host[:port]|[local]/database?
125: ClientCharset=charset& // transcode by parser
126: charset=value& // transcode by server with 'SET CLIENT_ENCODING=value'
127: datestyle=value& // 'SET DATESTYLE=value' available values are: ISO|SQL|Postgres|European|US|German [default=ISO]
128: autocommit=1& // each transaction is commited automatically (default)
1.33 ! misha 129: WithoutDefaultTransaction=0 // 1 -- disable any BEGIN TRAN/COMMIT/ROLLBACK [can NOT be used with autocommit option]
1.32 misha 130: */
131: void connect(
132: char* url,
133: SQL_Driver_services& services,
134: void** connection_ref ///< output: Connection*
135: ){
136: char* user=url;
137: char* host=rsplit(user, '@');
138: char* db=lsplit(host, '/');
139: char* pwd=lsplit(user, ':');
140: char* port=lsplit(host, ':');
141:
142: char *options=lsplit(db, '?');
143:
144: char* charset=0;
145: char* datestyle=0;
146:
147: Connection& connection=*(Connection *)services.malloc(sizeof(Connection));
148: *connection_ref=&connection;
149: connection.services=&services;
150: connection.client_charset=0;
151: connection.autocommit=true;
152: connection.without_default_transactions=false;
153:
154: connection.conn=PQsetdbLogin(
155: (host&&strcasecmp(host, "local")==0)?NULL/* local Unix domain socket */:host, port,
156: NULL, NULL, db, user, pwd);
157:
158: if(!connection.conn)
159: services._throw("PQsetdbLogin failed");
160:
161: if(PQstatus(connection.conn)!=CONNECTION_OK)
162: throwPQerror;
163:
164: while(options){
165: if(char *key=lsplit(&options, '&')){
166: if(*key){
167: if(char *value=lsplit(key, '=')){
168: if(strcmp(key, "ClientCharset")==0){
169: toupper_str(value, value, strlen(value));
170: connection.client_charset=value;
171: } else if(strcasecmp(key, "charset")==0){
172: charset=value;
173: } else if(strcasecmp(key, "datestyle")==0){
174: datestyle=value;
175: } else if(strcasecmp(key, "autocommit")==0){
176: if(connection.without_default_transactions)
177: services._throw("options WithoutDefaultTransaction and autocommit can't be used together");
178: if(atoi(value)==0)
179: connection.autocommit=false;
180: } else if(strcmp(key, "WithoutDefaultTransaction")==0){
181: if(!connection.autocommit)
182: services._throw("options WithoutDefaultTransaction and autocommit can't be used together");
183: if(atoi(value)==1){
184: connection.without_default_transactions=true;
185: connection.autocommit=false;
186: }
187: } else
188: services._throw("unknown connect option" /*key*/);
189: } else
190: services._throw("connect option without =value" /*key*/);
191: }
192: }
193: }
194:
195: if(charset){
196: char statement[MAX_STRING]="SET CLIENT_ENCODING=";
197: strncat(statement, charset, MAX_STRING);
198:
199: _execute_cmd(connection, statement);
200: }
201:
202: if(datestyle){
203: char statement[MAX_STRING]="SET DATESTYLE=";
204: strncat(statement, datestyle, MAX_STRING);
205:
206: _execute_cmd(connection, statement);
207: }
208:
1.33 ! misha 209: _transaction_begin(connection);
1.32 misha 210: }
211:
212: void disconnect(void *aconnection){
213: Connection& connection=*static_cast<Connection*>(aconnection);
214: PQfinish(connection.conn);
215: connection.conn=0;
216: }
217:
218: void commit(void *aconnection){
219: Connection& connection=*static_cast<Connection*>(aconnection);
1.33 ! misha 220: _transaction_commit(connection);
! 221: _transaction_begin(connection);
1.32 misha 222: }
223:
224: void rollback(void *aconnection){
225: Connection& connection=*static_cast<Connection*>(aconnection);
1.33 ! misha 226: _transaction_rollback(connection);
! 227: _transaction_begin(connection);
1.32 misha 228: }
229:
230: bool ping(void *aconnection) {
231: Connection& connection=*static_cast<Connection*>(aconnection);
232: return PQstatus(connection.conn)==CONNECTION_OK;
233: }
234:
235: const char* quote(void *aconnection, const char *from, unsigned int length){
236: Connection& connection=*static_cast<Connection*>(aconnection);
237:
238: char *result=(char*)connection.services->malloc_atomic(length*2+1);
239: int err=0;
240: PQescapeStringConn(connection.conn, result, from, length, &err);
241: return result;
242: }
243:
244: void query(void *aconnection,
245: const char *astatement,
246: size_t placeholders_count, Placeholder* placeholders,
247: unsigned long offset, unsigned long limit,
248: SQL_Driver_query_event_handlers& handlers
249: ){
250: Connection& connection=*static_cast<Connection*>(aconnection);
251: SQL_Driver_services& services=*connection.services;
252: PGconn *conn=connection.conn;
253:
254: const char* client_charset=connection.client_charset;
255: const char* request_charset=services.request_charset();
256: bool transcode_needed=client_charset && strcmp(client_charset, request_charset)!=0;
257:
258: const char** paramValues;
259: if(placeholders_count>0){
260: int binds_size=sizeof(char)*placeholders_count;
261: paramValues = static_cast<const char**>(services.malloc_atomic(binds_size));
262: _bind_parameters(placeholders_count, placeholders, paramValues, connection, transcode_needed);
263: }
264:
265: if(transcode_needed){
266: // transcode query from $request:charset to ?ClientCharset
267: size_t length=strlen(astatement);
268: services.transcode(astatement, length,
269: astatement, length,
270: request_charset,
271: client_charset);
272: }
273:
274: const char *statement=_preprocess_statement(connection, astatement, offset, limit);
275: // error after prepare?
276:
277: PGresult *res;
278: if(placeholders_count>0){
279: res=PQexecParams(conn, statement, placeholders_count, NULL, paramValues, NULL, NULL, 0);
280: } else {
281: res=PQexec(conn, statement);
282: }
283: if(!res)
284: throwPQerror;
285:
1.33 ! misha 286: bool failed=false;
! 287: SQL_Error sql_error;
! 288:
1.32 misha 289: switch(PQresultStatus(res)) {
290: case PGRES_EMPTY_QUERY:
291: PQclear_throw("no query");
292: break;
293: case PGRES_COMMAND_OK: // empty result: insert|delete|update|...
1.33 ! misha 294: goto cleanup;
! 295: break;
1.32 misha 296: case PGRES_TUPLES_OK:
297: break;
298: default:
299: PQclear_throwPQerror;
300: break;
301: }
302:
303: #define CHECK(afailed) \
304: if(afailed) { \
305: failed=true; \
306: goto cleanup; \
307: }
308:
1.33 ! misha 309: int column_count=PQnfields(res);
! 310: if(!column_count)
! 311: PQclear_throw("result contains no columns");
! 312:
1.32 misha 313: if(column_count>MAX_COLS)
314: column_count=MAX_COLS;
315:
316: unsigned int column_types[MAX_COLS];
317: bool transcode_column[MAX_COLS];
318:
319: for(int i=0; i<column_count; i++){
320: column_types[i]=PQftype(res, i);
321: switch(column_types[i]){
322: case BOOLOID:
323: case INT8OID:
324: case INT2OID:
325: case INT4OID:
326: case FLOAT4OID:
327: case FLOAT8OID:
328: case DATEOID:
329: case TIMEOID:
330: case TIMESTAMPOID:
331: case TIMESTAMPTZOID:
332: case TIMETZOID:
333: case NUMERICOID:
334: transcode_column[i]=false;
335: break;
336: default:
337: transcode_column[i]=transcode_needed;
338: break;
339: }
1.33 ! misha 340: char *name=PQfname(res, i);
! 341: size_t length=strlen(name);
1.32 misha 342: char* strm=(char*)services.malloc(length+1);
343: memcpy(strm, name, length+1);
344: const char* str=strm;
345:
1.33 ! misha 346: if(transcode_needed) // transcode column name from ?ClientCharset to $request:charset
1.32 misha 347: services.transcode(str, length,
348: str, length,
349: client_charset,
350: request_charset);
351:
352: CHECK(handlers.add_column(sql_error, str, length));
353: }
354:
355: CHECK(handlers.before_rows(sql_error));
356:
357: if(unsigned long row_count=(unsigned long)PQntuples(res))
358: for(unsigned long r=0; r<row_count; r++) {
359: CHECK(handlers.add_row(sql_error));
360: for(int i=0; i<column_count; i++){
361: const char *cell=PQgetvalue(res, r, i);
362: size_t length;
363: const char* str;
364:
365: switch(column_types[i]){
366: case OIDOID:
367: {
368: char *error_pos=0;
369: Oid oid=cell?atoi(cell):0;
370: int fd=lo_open(conn, oid, INV_READ);
371: if(fd>=0){
372: // seek to end
373: if(lo_lseek(conn, fd, 0, SEEK_END)<0)
374: PQclear_throwPQerror;
375: // get length
376: int size_tell=lo_tell(conn, fd);
377: if(size_tell<0)
378: PQclear_throwPQerror;
379: // seek to begin
380: if(lo_lseek(conn, fd, 0, SEEK_SET)<0)
381: PQclear_throwPQerror;
382: length=(size_t)size_tell;
383: if(length){
384: // read
385: char* strm=(char*)services.malloc(length+1);
386: if(!lo_read_ex(conn, fd, strm, size_tell))
387: PQclear_throw("lo_read can not read all bytes of object");
388: strm[length]=0;
389: str=strm;
390: } else
391: str=0;
392: if(lo_close(conn, fd)<0)
393: PQclear_throwPQerror;
394: } else
395: PQclear_throwPQerror;
396: }
397: default:
398: // normal column, read it normally
399: length=(size_t)PQgetlength(res, r, i);
400: if(length){
401: char* strm=(char*)services.malloc(length+1);
402: memcpy(strm, cell, length+1);
403: str=strm;
404: } else
405: str=0;
406: }
407:
408: if(str && length && transcode_column[i]){
409: // transcode cell value from ?ClientCharset to $request:charset
410: services.transcode(str, length,
411: str, length,
412: client_charset,
413: request_charset);
414: }
415:
416: CHECK(handlers.add_row_cell(sql_error, str, length));
417: }
418: }
419: cleanup:
420: PQclear(res);
421: if(failed)
422: services._throw(sql_error);
423:
424: if(connection.autocommit)
425: commit(aconnection);
426: }
427:
428: private:
429: void _bind_parameters(
430: size_t placeholders_count,
431: Placeholder* placeholders,
432: const char** paramValues,
433: Connection& connection,
434: bool transcode_needed
435: ){
436: for(size_t i=0; i<placeholders_count; i++){
437: Placeholder& ph=placeholders[i];
438: if(transcode_needed){
439: size_t name_length;
440: size_t value_length;
441: connection.services->transcode(ph.name, strlen(ph.name),
442: ph.name, name_length,
443: connection.services->request_charset(),
444: connection.client_charset);
445:
446: if(ph.value) {
447: connection.services->transcode(ph.value, strlen(ph.value),
448: ph.value, value_length,
449: connection.services->request_charset(),
450: connection.client_charset);
451: }
452: }
453: int name_numner=atoi(ph.name);
454: if(name_numner <= 0 || name_numner > placeholders_count)
455: connection.services->_throw("bad bind parameter key");
456:
457: paramValues[name_numner-1]=ph.value;
458: }
459: }
460:
461:
1.33 ! misha 462: void _transaction_begin(Connection& connection){
! 463: _execute_transactions_cmd(connection, "BEGIN");
! 464: }
! 465:
! 466: void _transaction_commit(Connection& connection){
! 467: _execute_transactions_cmd(connection, "COMMIT");
! 468: }
! 469:
! 470: void _transaction_rollback(Connection& connection){
! 471: _execute_transactions_cmd(connection, "ROLLBACK");
! 472: }
! 473:
! 474: void _execute_transactions_cmd(const Connection& connection, const char *query){
! 475: if(!connection.without_default_transactions) // with option ?WithoutDefaultTransaction=1 user must execute BEGIN/COMMIT/ROLLBACK by himself
! 476: _execute_cmd(connection, query);
! 477: }
! 478:
! 479: // executes a query and throw away the result.
1.32 misha 480: void _execute_cmd(const Connection& connection, const char *query){
481: if(PGresult *res=PQexec(connection.conn, query))
1.33 ! misha 482: PQclear(res); // throw away the result [don't need but must call]
1.32 misha 483: else
484: throwPQerror;
485: }
486:
487: const char *_preprocess_statement(
488: Connection& connection,
489: const char *astatement,
490: unsigned long offset,
491: unsigned long limit
492: ){
493: PGconn *conn=connection.conn;
494:
495: size_t statement_size=strlen(astatement);
496:
497: char *result=(char *)connection.services->malloc(statement_size
498: +MAX_NUMBER*2+15 // limit # offset #
499: +MAX_STRING // in case of short 'strings'
500: +1);
501: // offset & limit -> suffixes
502: const char *o;
503: if(offset || limit!=SQL_NO_LIMIT){
504: char *cur=result;
505: memcpy(cur, astatement, statement_size); cur+=statement_size;
506: if(limit!=SQL_NO_LIMIT)
507: cur+=snprintf(cur, 7+MAX_NUMBER, " limit %u", limit);
508: if(offset)
509: cur+=snprintf(cur, 8+MAX_NUMBER, " offset %u", offset);
510: o=result;
511: } else
512: o=astatement;
513:
514: // /**xxx**/'literal' -> oid
515: char *n=result;
516: while(*o) {
517: if(
518: o[0]=='/' &&
519: o[1]=='*' &&
520: o[2]=='*') { // name start
521: const char* saved_o=o;
522: o+=3;
523: while(*o)
524: if(
525: o[0]=='*' &&
526: o[1]=='*' &&
527: o[2]=='/' &&
528: o[3]=='\'') { // name end
529: saved_o=0; // found, marking that
530: o+=4;
531: Oid oid=lo_creat(conn, INV_READ|INV_WRITE);
532: if(oid==InvalidOid)
533: throwPQerror;
534: int fd=lo_open(conn, oid, INV_WRITE);
535: if(fd>=0) {
536: const char *start=o;
537: bool escaped=false;
538: while(*o && !(o[0]=='\'' && o[1]!='\'' && !escaped)) {
539: escaped=*o=='\\' || (o[0]=='\'' && o[1]=='\'');
540: if(escaped) {
541: // write pending, skip "\" or "'"
542: if(!lo_write_ex(conn, fd, start, o-start))
543: connection.services->_throw("lo_write could not write all bytes of object (1)");
544: start=++o;
545: } else
546: o++;
547: }
548: if(!lo_write_ex(conn, fd, start, o-start))
549: connection.services->_throw("lo_write can not write all bytes of object (2)");
550: if(lo_close(conn, fd)<0)
551: throwPQerror;
552: } else
553: throwPQerror;
554: if(*o)
555: o++; // skip "'"
556:
557: n+=snprintf(n, MAX_NUMBER, "%u", oid);
558: break;
559: } else
560: o++; // /**skip**/'xxx'
561: if(saved_o) {
562: o=saved_o;
563: *n++=*o++;
564: }
565: } else
566: *n++=*o++;
567: }
568: *n=0;
569:
570: return result;
571: }
572:
573: private: // lo_read/write exchancements
574:
575: bool lo_read_ex(PGconn *conn, int fd, const/*paf*/ char *buf, size_t len) {
576: return lo_rw_method (conn, fd, buf, len, lo_read);
577: }
578:
579: bool lo_write_ex(PGconn *conn, int fd, const/*paf*/ char *buf, size_t len) {
580: return lo_rw_method (conn, fd, buf, len, lo_write);
581: }
582:
583: bool lo_rw_method(PGconn *conn, int fd, const/*paf*/ char *buf, size_t len, int (*lo_func)(PGconn *conn, int fd, const/*paf*/ char *buf, size_t len)) {
584: int size_op;
585: while(len && (size_op=lo_func(conn, fd, buf, min(LO_BUFSIZE, len)))>0) {
586: buf+=size_op;
587: len-=size_op;
588: }
589: return len==0;
590: }
591:
592: private: // conn client library funcs
593:
594: typedef PGconn* (*t_PQsetdbLogin)(
595: const char *pghost,
596: const char *pgport,
597: const char *pgoptions,
598: const char *pgtty,
599: const char *dbName,
600: const char *login,
601: const char *pwd); t_PQsetdbLogin PQsetdbLogin;
602: typedef void (*t_PQfinish)(PGconn *conn); t_PQfinish PQfinish;
603: typedef char *(*t_PQerrorMessage)(const PGconn* conn); t_PQerrorMessage PQerrorMessage;
604: typedef ConnStatusType (*t_PQstatus)(const PGconn *conn); t_PQstatus PQstatus;
605: typedef PGresult *(*t_PQexec)(PGconn *conn,
606: const char *query); t_PQexec PQexec;
607: typedef PGresult *(*t_PQexecParams)(
608: PGconn *conn,
609: const char *query,
610: int nParams,
611: const Oid *paramTypes,
612: const char * const *paramValues,
613: const int *paramLengths,
614: const int *paramFormats,
615: int resultFormat); t_PQexecParams PQexecParams;
616:
617: typedef ExecStatusType (*t_PQresultStatus)(const PGresult *res); t_PQresultStatus PQresultStatus;
618: typedef int (*t_PQgetlength)(const PGresult *res,
619: int tup_num,
620: int field_num); t_PQgetlength PQgetlength;
621: typedef char* (*t_PQgetvalue)(const PGresult *res,
622: int tup_num,
623: int field_num); t_PQgetvalue PQgetvalue;
624: typedef int (*t_PQntuples)(const PGresult *res); t_PQntuples PQntuples;
625: typedef char *(*t_PQfname)(const PGresult *res,
626: int field_index); t_PQfname PQfname;
627: typedef int (*t_PQnfields)(const PGresult *res); t_PQnfields PQnfields;
628: typedef void (*t_PQclear)(PGresult *res); t_PQclear PQclear;
629:
630: typedef Oid (*t_PQftype)(const PGresult *res, int field_num); t_PQftype PQftype;
631:
632: typedef size_t (*t_PQescapeStringConn)(PGconn *conn,
633: char *to, const char *from, size_t length,
634: int *error); t_PQescapeStringConn PQescapeStringConn;
635:
636: typedef int (*t_lo_open)(PGconn *conn, Oid lobjId, int mode); t_lo_open lo_open;
637: typedef int (*t_lo_close)(PGconn *conn, int fd); t_lo_close lo_close;
638: typedef int (*t_lo_read)(PGconn *conn, int fd, const/*paf*/ char *buf, size_t len); t_lo_read lo_read;
639: typedef int (*t_lo_write)(PGconn *conn, int fd, const/*paf*/ char *buf, size_t len); t_lo_write lo_write;
640: typedef int (*t_lo_lseek)(PGconn *conn, int fd, int offset, int whence); t_lo_lseek lo_lseek;
641: typedef Oid (*t_lo_creat)(PGconn *conn, int mode); t_lo_creat lo_creat;
642: typedef int (*t_lo_tell)(PGconn *conn, int fd); t_lo_tell lo_tell;
643: typedef int (*t_lo_unlink)(PGconn *conn, Oid lobjId); t_lo_unlink lo_unlink;
644: typedef Oid (*t_lo_import)(PGconn *conn, const char *filename); t_lo_import lo_import;
645: typedef int (*t_lo_export)(PGconn *conn, Oid lobjId, const char *filename); t_lo_export lo_export;
646:
647: private: // conn client library funcs linking
648:
649: const char *dlink(const char *dlopen_file_spec) {
650: if(lt_dlinit())
651: return lt_dlerror();
652: lt_dlhandle handle=lt_dlopen(dlopen_file_spec);
653: if(!handle)
654: return "can not open the dynamic link module";
655:
656: #define DSLINK(name, action) \
657: name=(t_##name)lt_dlsym(handle, #name); \
658: if(!name) \
659: action;
660:
661: #define DLINK(name) DSLINK(name, return "function " #name " was not found")
662:
663: DLINK(PQsetdbLogin);
664: DLINK(PQerrorMessage);
665: DLINK(PQstatus);
666: DLINK(PQfinish);
667: DLINK(PQgetvalue);
668: DLINK(PQgetlength);
669: DLINK(PQntuples);
670: DLINK(PQfname);
671: DLINK(PQnfields);
672: DLINK(PQclear);
673: DLINK(PQresultStatus);
674: DLINK(PQexec);
675: DLINK(PQexecParams);
676: DLINK(PQftype);
677: DLINK(PQescapeStringConn);
678: DLINK(lo_open); DLINK(lo_close);
679: DLINK(lo_read); DLINK(lo_write);
680: DLINK(lo_lseek); DLINK(lo_creat);
681: DLINK(lo_tell); DLINK(lo_unlink);
682: DLINK(lo_import); DLINK(lo_export);
683:
684: return 0;
685: }
686: };
687:
688: extern "C" SQL_Driver *SQL_DRIVER_CREATE() {
689: return new PgSQL_Driver();
690: }