1 /* Copyright (C) 2000-2015 Lavtech.com corp. All rights reserved.
2
3 This program is free software; you can redistribute it and/or modify
4 it under the terms of the GNU General Public License as published by
5 the Free Software Foundation; either version 2 of the License, or
6 (at your option) any later version.
7
8 This program is distributed in the hope that it will be useful,
9 but WITHOUT ANY WARRANTY; without even the implied warranty of
10 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 GNU General Public License for more details.
12
13 You should have received a copy of the GNU General Public License
14 along with this program; if not, write to the Free Software
15 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
16 */
17
18 #include "udm_config.h"
19 #if (HAVE_PGSQL)
20 #include <stdio.h>
21 #include <stdlib.h>
22 #include <string.h>
23 #include <sys/types.h>
24 #ifdef HAVE_UNISTD_H
25 #include <unistd.h>
26 #endif
27
28 #include "udm_common.h"
29 #include "udm_sqldbms.h"
30 #include "udm_utils.h"
31 #include "udm_vars.h"
32
33 #include "udm_xmalloc.h"
34 #ifdef WIN32
35 #include <process.h>
36 #endif
37
38 #if (HAVE_PGSQL)
39 #include <libpq-fe.h>
40 #endif
41
42 /*
43 TODO: auto-detect from Pg client version
44 7.3 does not have prepared statements
45 7.4 does have, but incomplete.
46 Check which version have PQprepare
47 */
48
49
50 #ifdef PG_DIAG_INTERNAL_POSITION
51 #define HAVE_PGSQL_PS 1
52 #define HAVE_PGSQL_VR 1
53 #endif
54
55 #define UDM_PG_MAX_PARAM UDM_SQL_MAX_BIND_PARAM
56
57 #ifdef HAVE_PGSQL_PS
58 typedef struct udm_pgsql_stmt_st
59 {
60 PGresult *stmt;
61 int nParams;
62 Oid *paramTypes[UDM_PG_MAX_PARAM];
63 const char *paramValues[UDM_PG_MAX_PARAM];
64 int paramLengths[UDM_PG_MAX_PARAM];
65 int paramFormats[UDM_PG_MAX_PARAM];
66 char small[32][8];
67 } UDM_PGSQL_STMT;
68 #endif
69
70
71 typedef struct udm_pgsql_conn_st
72 {
73 PGconn *pgsql;
74 #ifdef HAVE_PGSQL_PS
75 UDM_PGSQL_STMT stmt;
76 #endif /* HAVE_PGSQL_PS */
77 } UDM_PGSQL;
78
79
80 static inline UDM_PGSQL *
UdmPG(UDM_SQL * db)81 UdmPG(UDM_SQL *db)
82 {
83 return (UDM_PGSQL*) db->specific;
84 }
85
86 static inline PGconn *
UdmDBPgConn(UDM_SQL * db)87 UdmDBPgConn(UDM_SQL *db)
88 {
89 return (PGconn*) ((UDM_PGSQL*) db->specific)->pgsql;
90 }
91
92
93 static void
UdmSetErrorCode(UDM_SQL * db,int errcode)94 UdmSetErrorCode(UDM_SQL *db, int errcode)
95 {
96 db->errcode= errcode;
97 }
98
99
100 static udm_rc_t
UdmPgSQLConnect(UDM_SQL * db)101 UdmPgSQLConnect(UDM_SQL *db)
102 {
103 char port[8];
104 const char* DBUser= UdmVarListFindStr(&db->Vars, "DBUser", NULL);
105 const char* DBPass= UdmVarListFindStr(&db->Vars, "DBPass", NULL);
106 const char* DBHost= UdmVarListFindStr(&db->Vars, "DBHost", NULL);
107 const char* DBName= UdmVarListFindStr(&db->Vars, "DBName", NULL);
108 const char* DBSock= UdmVarListFindStr(&db->Vars, "socket", NULL);
109 int DBPort= UdmVarListFindInt(&db->Vars, "DBPort", 0);
110 const char* setnames= UdmVarListFindStr(&db->Vars,"setnames", "");
111 UDM_PGSQL *pgdb= (UDM_PGSQL*) UdmMalloc(sizeof(UDM_PGSQL));
112
113 if (!(db->specific= pgdb))
114 {
115 UdmSetErrorCode(db, 1);
116 sprintf(db->errstr, "Can't alloc mydb. Not enough memory?");
117 return UDM_ERROR;
118 }
119 bzero((void*) pgdb, sizeof(UDM_PGSQL));
120
121 /*
122 If no host given then check if an alternative
123 Unix domain socket is specified.
124 */
125 DBHost= (DBHost && DBHost[0]) ? DBHost : DBSock;
126
127 sprintf(port,"%d",DBPort);
128 pgdb->pgsql= PQsetdbLogin(DBHost, DBPort ? port : 0, 0, 0,
129 DBName, DBUser, DBPass);
130 if (PQstatus(pgdb->pgsql) == CONNECTION_BAD)
131 {
132 UdmSetErrorCode(db, 1);
133 sprintf(db->errstr, "PSstatus: %s", PQerrorMessage(pgdb->pgsql));
134 return UDM_ERROR;
135 }
136 db->connected= UDM_TRUE;
137
138
139 /*
140 Extract server version.
141 Protocol version is also available:
142 extern int PQprotocolVersion(const PGconn *conn);
143 */
144
145 #ifdef HAVE_PGSQL_VR
146 db->version= PQserverVersion(pgdb->pgsql);
147 /*
148 DROP TABLE IF EXISTS t1; -- starting from Pg-8.2 (checked with 8.2.4)
149 */
150 if (db->version >= 80204)
151 {
152 if (UdmVarListFindBool(&db->Vars, "DropIfExists", UDM_TRUE))
153 db->flags|= UDM_SQL_HAVE_DROP_IF_EXISTS;
154 }
155 #endif
156
157 if (setnames[0])
158 {
159 UDM_CHARSET *cs= UdmGetCharSet(setnames);
160 const char *csname= cs && cs->pgsql_name ? cs->pgsql_name : "SQL_ASCII";
161 if (PQsetClientEncoding(pgdb->pgsql, csname))
162 {
163 UdmSetErrorCode(db, 1);
164 sprintf(db->errstr, "PQsetClientEncoding: %s", PQerrorMessage(pgdb->pgsql));
165 return UDM_ERROR;
166 }
167 }
168 return UDM_OK;
169 }
170
171
172 #define udm_oct2int(ch) ((ch) - '0')
173 #define udm_isoct(ch) ((ch) >= '0' && (ch) <= '7')
174
175 static size_t
PgUnescape(char * dst,const char * src,size_t length)176 PgUnescape(char *dst, const char *src, size_t length)
177 {
178 char *dst0= dst;
179 const char *srcend= src + length;
180
181 /* Postgres 9.0 hex format */
182 if (length > 1 && src[0] == '\\' && src[1] == 'x')
183 return UdmHexDecode(dst, src + 2, length - 2);
184
185 /* Traditional Postgres escape format */
186 for ( ; src < srcend; dst++, src++)
187 {
188 if (*src == '\\' && src + 4 <= srcend &&
189 udm_isoct(src[1]) && udm_isoct(src[2]) && udm_isoct(src[3]))
190 {
191 *dst= udm_oct2int(src[1]) * 64 +
192 udm_oct2int(src[2]) * 8 +
193 udm_oct2int(src[3]);
194 src+= 3;
195 }
196 else if (*src == '\\' && src[1] == '\\')
197 {
198 *dst= '\\';
199 src++;
200 }
201 else
202 *dst= *src;
203 }
204 return dst - dst0;
205 }
206
207
208
209 static udm_rc_t
UdmPgSQLResultUnescape(UDM_SQLRES * res)210 UdmPgSQLResultUnescape(UDM_SQLRES *res)
211 {
212 size_t row;
213 PGresult *pgsqlres= (PGresult*) res->specific;
214 res->Items= (UDM_STR*)UdmMalloc(res->nRows * res->nCols * sizeof(UDM_STR));
215 for (row= 0; row < res->nRows; row++)
216 {
217 size_t col, offs= res->nCols * row;
218 for (col= 0; col < res->nCols; col++)
219 {
220 UDM_STR *I= &res->Items[offs + col];
221 const char *ptr= PQgetvalue(pgsqlres, (int) row, (int) col);
222 size_t len= PQgetlength(pgsqlres, (int) row, (int )col);
223 I->str= (char*) UdmMalloc(len + 1);
224 if (PQftype(pgsqlres, (int) col) == 17)
225 {
226 I->length= PgUnescape(I->str, ptr, len);
227 I->str[I->length]= '\0';
228 }
229 else
230 {
231 memcpy(I->str, ptr, len);
232 I->str[len]= '\0';
233 I->length= len;
234 }
235 }
236 }
237 return UDM_OK;
238 }
239
240
241 static udm_rc_t
UdmPgSQLProcessResult(UDM_SQL * db,PGresult * PGres,UDM_SQLRES * res,const char * caller,int unescape,int clear)242 UdmPgSQLProcessResult(UDM_SQL *db, PGresult *PGres, UDM_SQLRES *res,
243 const char* caller,
244 int unescape, int clear)
245 {
246 int have_bytea= 0;
247 PGconn *pgsql= UdmDBPgConn(db);
248 size_t i;
249
250
251 if (PQresultStatus(PGres)==PGRES_COMMAND_OK)
252 {
253 /* Free non-SELECT query */
254 if (clear)
255 PQclear(PGres);
256 return UDM_OK;
257 }
258
259
260 if (PQresultStatus(PGres) != PGRES_TUPLES_OK)
261 {
262 #ifdef PG_DIAG_SQLSTATE
263 {
264 char *sqlstate= PQresultErrorField(PGres, PG_DIAG_SQLSTATE);
265 if (sqlstate && !strcmp(sqlstate, "23505"))
266 {
267 PQclear(PGres);
268 return UDM_OK;
269 }
270 }
271 #endif
272
273 PQclear(PGres);
274 sprintf(db->errstr, "%s: %s", caller, PQerrorMessage(pgsql));
275 if (strstr(db->errstr,"uplicate") ||
276 strstr(db->errstr, "duplicada") ||
277 strstr(db->errstr, "���������") ||
278 strstr(db->errstr, "повторный") ||
279 strcasestr(db->errstr,"Duplizierter"))
280 {
281 return UDM_OK;
282 }
283 else
284 {
285 UdmSetErrorCode(db, 1);
286 return UDM_ERROR;
287 }
288 }
289
290 if (!res)
291 {
292 /*
293 Don't allow to call UdmPgSQLQuery
294 returning data with NULL res pointer
295 */
296 sprintf(db->errstr,
297 "UdmPgSQLQuery executed with res=NULL returned result %d, %s",
298 PQresultStatus(PGres),PQerrorMessage(pgsql));
299 UdmSetErrorCode(db, 1);
300 return UDM_ERROR;
301 }
302
303 res->specific= (void*) PGres;
304 res->nCols=(size_t)PQnfields(PGres);
305 res->nRows=(size_t)PQntuples(PGres);
306 res->Fields=(UDM_SQLFIELD*)UdmMalloc(res->nCols*sizeof(UDM_SQLFIELD));
307 for (i=0;i<res->nCols;i++)
308 {
309 res->Fields[i].sqlname = (char*)UdmStrdup(PQfname(PGres,(int)i));
310 if (PQftype(PGres, (int) i) == 17)
311 have_bytea++;
312 }
313
314 if (have_bytea && unescape)
315 UdmPgSQLResultUnescape(res);
316
317 return UDM_OK;
318 }
319
320
321 static udm_rc_t
UdmPgSQLQueryInternal(UDM_SQL * db,UDM_SQLRES * res,const char * q,int unescape,int clear)322 UdmPgSQLQueryInternal(UDM_SQL *db, UDM_SQLRES *res,
323 const char *q, int unescape, int clear)
324 {
325 PGresult *PGres;
326
327 if (res)
328 {
329 bzero((void*) res, sizeof(UDM_SQLRES));
330 res->db= db;
331 }
332
333 UdmSetErrorCode(db, 0);
334 if (!db->connected)
335 {
336 UdmPgSQLConnect(db);
337 if (db->errcode)
338 return UDM_ERROR;
339 }
340
341 if (!(PGres= PQexec(UdmDBPgConn(db), q)))
342 {
343 sprintf(db->errstr, "PQexec: %s", PQerrorMessage(UdmDBPgConn(db)));
344 UdmSetErrorCode(db, 1);
345 return UDM_ERROR;
346 }
347
348 return UdmPgSQLProcessResult(db, PGres, res, "PQexec", unescape, clear);
349 }
350
351
352 static udm_rc_t
UdmPgSQLQuery(UDM_SQL * db,UDM_SQLRES * res,const char * q)353 UdmPgSQLQuery(UDM_SQL *db, UDM_SQLRES *res, const char *q)
354 {
355 return UdmPgSQLQueryInternal(db, res, q, 1, 1);
356 }
357
358
359 static udm_rc_t
UdmPgSQLExecDirect(UDM_SQL * db,UDM_SQLRES * res,const char * q)360 UdmPgSQLExecDirect(UDM_SQL *db, UDM_SQLRES *res, const char *q)
361 {
362 return UdmPgSQLQueryInternal(db, res, q, 0, 1);
363 }
364
365
366 #if HAVE_PGSQL_PS
367
368 static void
UdmPgSQLPrintStmtError(UDM_SQL * db,const char * func)369 UdmPgSQLPrintStmtError(UDM_SQL *db, const char *func)
370 {
371 udm_snprintf(db->errstr, sizeof(db->errstr), "%s failed: %s",
372 func, PQerrorMessage(UdmDBPgConn(db)));
373 }
374
375
376 static udm_rc_t
UdmPgSQLPrepare(UDM_SQL * db,const char * query)377 UdmPgSQLPrepare(UDM_SQL *db, const char *query)
378 {
379 UDM_PGSQL *pgdb= UdmPG(db);
380 UDM_PGSQL_STMT *stmt= &pgdb->stmt;
381 PGconn *pgsql= pgdb->pgsql;
382 UdmSetErrorCode(db, 0);
383 db->errstr[0]= '\0';
384
385 stmt->nParams= 0;
386 if (!(stmt->stmt= PQprepare(pgsql,
387 "" /* "stmtName" */,
388 query,
389 0 /*int nParams*/,
390 NULL /*const Oid *paramTypes*/)))
391 {
392 UdmSetErrorCode(db, 1);
393 UdmPgSQLPrintStmtError(db, "PQprepare()");
394 return UDM_ERROR;
395 }
396
397 return UdmPgSQLProcessResult(db, stmt->stmt, NULL, "PQprepare", 0, 0);
398 }
399
400
401 static udm_rc_t
UdmPgSQLBind(UDM_SQL * db,int pos,const void * data,int size,udm_sqltype_t type)402 UdmPgSQLBind(UDM_SQL *db, int pos, const void *data, int size, udm_sqltype_t type)
403 {
404 UDM_PGSQL *pgdb= UdmPG(db);
405 UDM_PGSQL_STMT *stmt= &pgdb->stmt;
406 if (stmt->nParams < pos)
407 stmt->nParams= pos;
408 pos--;
409 stmt->paramValues[pos]= (const char*) data;
410 stmt->paramLengths[pos]= (int) size;
411 stmt->paramFormats[pos]= 1;
412 #ifndef WORDS_BIGENDIAN
413 if (type == UDM_SQLTYPE_INT32)
414 {
415 char *tmp= stmt->small[pos];
416 const char *src= (const char*) data;
417 /* Postgres uses big endian values in the protocol */
418 tmp[0]= src[3];
419 tmp[1]= src[2];
420 tmp[2]= src[1];
421 tmp[3]= src[0];
422 stmt->paramValues[pos]= tmp;
423 }
424 #endif
425 return UDM_OK;
426 }
427
428
429 static udm_rc_t
UdmPgSQLStmtFree(UDM_SQL * db)430 UdmPgSQLStmtFree(UDM_SQL *db)
431 {
432 UDM_PGSQL *pgdb= UdmPG(db);
433 PQclear(pgdb->stmt.stmt);
434 return UDM_OK;
435 }
436
437
438 static udm_rc_t
UdmPgSQLExec(UDM_SQL * db)439 UdmPgSQLExec(UDM_SQL *db)
440 {
441 UDM_PGSQL *pgdb= UdmPG(db);
442 UDM_PGSQL_STMT *stmt= &pgdb->stmt;
443 PGresult *PGres;
444
445 PGres= PQexecPrepared(pgdb->pgsql,
446 "", /*"stmtName"*/
447 stmt->nParams,
448 stmt->paramValues,
449 stmt->paramLengths,
450 stmt->paramFormats,
451 0 /*int resultFormat*/);
452
453 return UdmPgSQLProcessResult(db, PGres,
454 /*UDM_SQLRES*/ NULL,
455 "PQexecPrepared",
456 /*unescape */ 1,
457 /* clear */ 1);
458 }
459 #endif /* HAVE_PGSQL_PS */
460
461
462 static udm_rc_t
UdmPgSQLClose(UDM_SQL * db)463 UdmPgSQLClose(UDM_SQL *db)
464 {
465 UDM_PGSQL *pgdb= UdmPG(db);
466 PQfinish(pgdb->pgsql);
467 UDM_FREE(db->specific);
468 db->connected= UDM_FALSE;
469 db->specific= NULL;
470 return UDM_OK;
471 }
472
473
474 static udm_rc_t
UdmPgSQLBegin(UDM_SQL * db)475 UdmPgSQLBegin(UDM_SQL *db)
476 {
477 return UdmPgSQLQuery(db,NULL,"BEGIN WORK");
478 }
479
480
481 static udm_rc_t
UdmPgSQLCommit(UDM_SQL * db)482 UdmPgSQLCommit(UDM_SQL *db)
483 {
484 return UdmPgSQLQuery(db,NULL,"END WORK");
485 }
486
487
488 static udm_rc_t
UdmPgSQLFetchRow(UDM_SQL * db,UDM_SQLRES * res,UDM_STR * buf)489 UdmPgSQLFetchRow(UDM_SQL *db, UDM_SQLRES *res, UDM_STR *buf)
490 {
491 size_t j;
492 PGresult *pgsqlres= (PGresult*) res->specific;
493
494 if (res->curRow >= res->nRows)
495 return UDM_ERROR;
496
497 for (j = 0; j < res->nCols; j++)
498 {
499 buf[j].str= PQgetvalue(pgsqlres,(int)res->curRow,(int)(j));
500 buf[j].length= PQgetlength(pgsqlres, res->curRow, j);
501 if (PQftype(pgsqlres, (int) j) == 17)
502 {
503 buf[j].length= PgUnescape(buf[j].str, buf[j].str, buf[j].length);
504 buf[j].str[buf[j].length]= '\0';
505 }
506 }
507 res->curRow++;
508 return(UDM_OK);
509 }
510
511
512 static udm_rc_t
UdmPgSQLRenameTable(UDM_SQL * db,const char * from,const char * to)513 UdmPgSQLRenameTable(UDM_SQL *db, const char *from, const char *to)
514 {
515 char buf[256];
516 udm_snprintf(buf, sizeof(buf), "ALTER TABLE %s RENAME TO %s", from, to);
517 return UdmSQLExecDirect(db, NULL, buf);
518 }
519
520
521 static udm_rc_t
UdmPgSQLCopyStructure(UDM_SQL * db,const char * from,const char * to)522 UdmPgSQLCopyStructure(UDM_SQL *db, const char *from, const char *to)
523 {
524 char buf[256];
525 /*
526 Does not copy indexes by default.
527 Statring with 8.3 also this syntax is supported:
528 CREATE TABLE t2 (LIKE t1 INCLUDING INDEXES); -- copy indexes
529 */
530 udm_snprintf(buf, sizeof(buf), "CREATE TABLE %s (LIKE %s)", to, from);
531 return UdmSQLExecDirect(db, NULL, buf);
532 }
533
534
535 const UDM_SQLDB_HANDLER udm_sqldb_pgsql_handler =
536 {
537 UdmSQLEscStrGeneric,
538 UdmPgSQLQuery,
539 UdmPgSQLConnect,
540 UdmPgSQLClose,
541 UdmPgSQLBegin,
542 UdmPgSQLCommit,
543 #if HAVE_PGSQL_PS
544 UdmPgSQLPrepare,
545 UdmPgSQLBind,
546 UdmPgSQLExec,
547 UdmPgSQLStmtFree,
548 #else
549 UdmSQLPrepareGeneric,
550 UdmSQLBindGeneric,
551 UdmSQLExecGeneric,
552 UdmSQLStmtFreeGeneric,
553 #endif
554 UdmPgSQLFetchRow,
555 UdmSQLStoreResultSimple,
556 UdmSQLFreeResultSimple,
557 UdmPgSQLExecDirect,
558 UdmPgSQLRenameTable,
559 UdmPgSQLCopyStructure,
560 UdmSQLLockOrBeginGeneric,
561 UdmSQLUnlockOrCommitGeneric,
562 };
563
564 #endif
565