1 /*
2 * PostgreSQL Database Driver for Kamailio
3 *
4 * Portions Copyright (C) 2001-2003 FhG FOKUS
5 * Copyright (C) 2003 August.Net Services, LLC
6 * Portions Copyright (C) 2005-2008 iptelorg GmbH
7 *
8 * This file is part of Kamailio, a free SIP server.
9 *
10 * Kamailio is free software; you can redistribute it and/or modify it under the
11 * terms of the GNU General Public License as published by the Free Software
12 * Foundation; either version 2 of the License, or (at your option) any later
13 * version
14 *
15 * Kamailio is distributed in the hope that it will be useful, but WITHOUT ANY
16 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
17 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
18 * details.
19 *
20 * You should have received a copy of the GNU General Public License along
21 * with this program; if not, write to the Free Software Foundation, Inc., 59
22 * Temple Place, Suite 330, Boston, MA 02111-1307 USA
23 */
24
25 /** \addtogroup postgres
26 * @{
27 */
28
29 /** \file
30 * Functions related to connections to PostgreSQL servers.
31 */
32
33 #include "pg_con.h"
34 #include "pg_uri.h"
35 #include "pg_sql.h"
36 #include "pg_mod.h"
37
38 #include "../../core/mem/mem.h"
39 #include "../../core/dprint.h"
40 #include "../../core/ut.h"
41
42 #include <stdlib.h>
43 #include <string.h>
44 #include <netinet/in.h>
45 #include <netinet/tcp.h>
46 #include <time.h>
47
48
49 /* Override the default notice processor to output the messages
50 * using SER's output subsystem.
51 */
notice_processor(void * arg,const char * message)52 static void notice_processor(void *arg, const char *message)
53 {
54 LOG(L_NOTICE, "postgres: %s\n", message);
55 }
56
57
58 /** Determine the format of timestamps used by the server.
59 * A PostgresSQL server can be configured to store timestamps either as 8-byte
60 * integers or floating point numbers with double precision. This functions
61 * sends a simple SQL query to the server and tries to determine the format of
62 * timestamps from the reply. This function is executed once after connecting
63 * to a PostgreSQL server and the result of the detection is then stored in
64 * form of a flag in pg_con connection structure.
65 * @param con A PostgreSQL connection handle
66 * @retval 0 If the server stores timestamps as floating point numbers.
67 * @retval 1 If the server stores timestamps as 8-byte integers.
68 * @retval A negative number on error.
69 */
timestamp_format(PGconn * con)70 static int timestamp_format(PGconn *con)
71 {
72 unsigned long long offset;
73 PGresult *res = 0;
74 char *val;
75 str sql;
76
77 if(build_timestamp_format_sql(&sql) != 0) {
78 ERR("postgres: Error while building SQL query to obtain timestamp "
79 "format\n");
80 return -1;
81 }
82 res = PQexecParams(con, sql.s, 0, 0, 0, 0, 0, 1);
83 pkg_free(sql.s);
84
85 if(PQfformat(res, 0) != 1) {
86 ERR("postgres: Binary format expected but server sent text\n");
87 goto error;
88 }
89
90 if(PQntuples(res) != 1) {
91 ERR("postgres: Only one column expected, %d received\n",
92 PQntuples(res));
93 goto error;
94 }
95
96 if(PQnfields(res) != 1) {
97 ERR("postgres: Only one row expected, %d received\n", PQnfields(res));
98 goto error;
99 }
100
101 val = PQgetvalue(res, 0, 0);
102 offset = ((unsigned long long)ntohl(((unsigned int *)val)[0]) << 32)
103 + ntohl(((unsigned int *)val)[1]);
104
105 PQclear(res);
106
107 /* Server using int8 timestamps would return 1000000, because it stores
108 * timestamps in microsecond resolution across the whole range. Server
109 * using double timestamps would return 1 (encoded as double) here because
110 * subsection fraction is stored as fractional part in the IEEE
111 * representation. 1 stored as double would result in 4607182418800017408
112 * when the memory location occupied by the variable is read as unsigned
113 * long long.
114 */
115 if(offset == 1000000) {
116 DBG("postgres: Server uses int8 format for timestamps.\n");
117 return 1;
118 } else {
119 DBG("postgres: Server uses double format for timestamps.\n");
120 return 0;
121 }
122
123 error:
124 PQclear(res);
125 return -1;
126 }
127
128
129 /** Retrieves a list of all supported field types from the server.
130 * This function retrieves a list of all supported field types and their Oids
131 * from system catalogs of the server. The list is then stored in pg_con
132 * connection structure and it is used to map field type names, such as int2,
133 * int4, float4, etc. to Oids. Every PostgreSQL server can map field types to
134 * different Oids so we need to store the mapping array in the connection
135 * structure.
136 * @param con A structure representing connection to PostgreSQL server.
137 * @retval 0 If executed successfully.
138 * @retval A negative number on error.
139 */
get_oids(db_con_t * con)140 static int get_oids(db_con_t *con)
141 {
142 struct pg_con *pcon;
143 PGresult *res = NULL;
144 str sql;
145
146 pcon = DB_GET_PAYLOAD(con);
147 if(build_select_oid_sql(&sql) < 0)
148 goto error;
149 res = PQexec(pcon->con, sql.s);
150 pkg_free(sql.s);
151 if(res == NULL || PQresultStatus(res) != PGRES_TUPLES_OK)
152 goto error;
153 pcon->oid = pg_new_oid_table(res);
154 PQclear(res);
155 if(pcon->oid == NULL)
156 goto error;
157 return 0;
158
159 error:
160 if(res)
161 PQclear(res);
162 return -1;
163 }
164
165
166 /** Free all memory allocated for a pg_con structure.
167 * This function function frees all memory that is in use by
168 * a pg_con structure.
169 * @param con A generic db_con connection structure.
170 * @param payload PostgreSQL specific payload to be freed.
171 */
pg_con_free(db_con_t * con,struct pg_con * payload)172 static void pg_con_free(db_con_t *con, struct pg_con *payload)
173 {
174 if(!payload)
175 return;
176
177 /* Delete the structure only if there are no more references
178 * to it in the connection pool
179 */
180 if(db_pool_remove((db_pool_entry_t *)payload) == 0)
181 return;
182
183 db_pool_entry_free(&payload->gen);
184 pg_destroy_oid_table(payload->oid);
185 if(payload->con)
186 PQfinish(payload->con);
187 pkg_free(payload);
188 }
189
190
pg_con(db_con_t * con)191 int pg_con(db_con_t *con)
192 {
193 struct pg_con *pcon;
194
195 /* First try to lookup the connection in the connection pool and
196 * re-use it if a match is found
197 */
198 pcon = (struct pg_con *)db_pool_get(con->uri);
199 if(pcon) {
200 DBG("postgres: Connection to %.*s:%.*s found in connection pool\n",
201 con->uri->scheme.len, ZSW(con->uri->scheme.s),
202 con->uri->body.len, ZSW(con->uri->body.s));
203 goto found;
204 }
205
206 pcon = (struct pg_con *)pkg_malloc(sizeof(struct pg_con));
207 if(!pcon) {
208 PKG_MEM_ERROR;
209 goto error;
210 }
211 memset(pcon, '\0', sizeof(struct pg_con));
212 if(db_pool_entry_init(&pcon->gen, pg_con_free, con->uri) < 0)
213 goto error;
214
215 DBG("postgres: Preparing new connection to: %.*s:%.*s\n",
216 con->uri->scheme.len, ZSW(con->uri->scheme.s), con->uri->body.len,
217 ZSW(con->uri->body.s));
218
219 /* Put the newly created postgres connection into the pool */
220 db_pool_put((struct db_pool_entry *)pcon);
221 DBG("postgres: Connection stored in connection pool\n");
222
223 found:
224 /* Attach driver payload to the db_con structure and set connect and
225 * disconnect functions
226 */
227 DB_SET_PAYLOAD(con, pcon);
228 con->connect = pg_con_connect;
229 con->disconnect = pg_con_disconnect;
230 return 0;
231
232 error:
233 if(pcon) {
234 db_pool_entry_free(&pcon->gen);
235 pkg_free(pcon);
236 }
237 return -1;
238 }
239
240
pg_con_connect(db_con_t * con)241 int pg_con_connect(db_con_t *con)
242 {
243 struct pg_con *pcon;
244 struct pg_uri *puri;
245 char *port_str;
246 int ret, i = 0;
247 const char *keywords[10], *values[10];
248 char to[16];
249
250 pcon = DB_GET_PAYLOAD(con);
251 puri = DB_GET_PAYLOAD(con->uri);
252
253 /* Do not reconnect already connected connections */
254 if(pcon->flags & PG_CONNECTED)
255 return 0;
256
257 DBG("postgres: Connecting to %.*s:%.*s\n", con->uri->scheme.len,
258 ZSW(con->uri->scheme.s), con->uri->body.len, ZSW(con->uri->body.s));
259
260 if(puri->port > 0) {
261 port_str = int2str(puri->port, 0);
262 keywords[i] = "port";
263 values[i++] = port_str;
264 } else {
265 port_str = NULL;
266 }
267
268 if(pcon->con) {
269 PQfinish(pcon->con);
270 pcon->con = NULL;
271 }
272
273 keywords[i] = "host";
274 values[i++] = puri->host;
275 keywords[i] = "dbname";
276 values[i++] = puri->database;
277 keywords[i] = "user";
278 values[i++] = puri->username;
279 keywords[i] = "password";
280 values[i++] = puri->password;
281 if(pg_timeout > 0) {
282 snprintf(to, sizeof(to) - 1, "%d", pg_timeout + 3);
283 keywords[i] = "connect_timeout";
284 values[i++] = to;
285 }
286
287 keywords[i] = values[i] = NULL;
288
289 pcon->con = PQconnectdbParams(keywords, values, 1);
290
291 if(pcon->con == NULL) {
292 ERR("postgres: PQconnectdbParams ran out of memory\n");
293 goto error;
294 }
295
296 if(PQstatus(pcon->con) != CONNECTION_OK) {
297 ERR("postgres: %s\n", PQerrorMessage(pcon->con));
298 goto error;
299 }
300
301 /* Override default notice processor */
302 PQsetNoticeProcessor(pcon->con, notice_processor, 0);
303
304 #ifdef HAVE_PGSERVERVERSION
305 DBG("postgres: Connected. Protocol version=%d, Server version=%d\n",
306 PQprotocolVersion(pcon->con), PQserverVersion(pcon->con));
307 #else
308 DBG("postgres: Connected. Protocol version=%d, Server version=%d\n",
309 PQprotocolVersion(pcon->con), 0);
310 #endif
311
312 #if defined(SO_KEEPALIVE) && defined(TCP_KEEPIDLE)
313 if(pg_keepalive) {
314 i = 1;
315 if(setsockopt(
316 PQsocket(pcon->con), SOL_SOCKET, SO_KEEPALIVE, &i, sizeof(i))
317 < 0) {
318 LM_WARN("failed to set socket option keepalive\n");
319 }
320 if(setsockopt(PQsocket(pcon->con), IPPROTO_TCP, TCP_KEEPIDLE,
321 &pg_keepalive, sizeof(pg_keepalive))
322 < 0) {
323 LM_WARN("failed to set socket option keepidle\n");
324 }
325 }
326 #endif
327
328 ret = timestamp_format(pcon->con);
329 if(ret == 1 || ret == -1) {
330 /* Assume INT8 representation if detection fails */
331 pcon->flags |= PG_INT8_TIMESTAMP;
332 } else {
333 pcon->flags &= ~PG_INT8_TIMESTAMP;
334 }
335
336 if(get_oids(con) < 0)
337 goto error;
338
339 pcon->flags |= PG_CONNECTED;
340 return 0;
341
342 error:
343 if(pcon->con)
344 PQfinish(pcon->con);
345 pcon->con = NULL;
346 return -1;
347 }
348
349
pg_con_disconnect(db_con_t * con)350 void pg_con_disconnect(db_con_t *con)
351 {
352 struct pg_con *pcon;
353
354 pcon = DB_GET_PAYLOAD(con);
355 if((pcon->flags & PG_CONNECTED) == 0)
356 return;
357
358 DBG("postgres: Disconnecting from %.*s:%.*s\n", con->uri->scheme.len,
359 ZSW(con->uri->scheme.s), con->uri->body.len, ZSW(con->uri->body.s));
360
361 PQfinish(pcon->con);
362 pcon->con = NULL;
363 pcon->flags &= ~PG_CONNECTED;
364 pcon->flags &= ~PG_INT8_TIMESTAMP;
365 }
366
367 /** @} */
368