[Home]

Summary:ASTERISK-05489: [patch] [post 1.2] Postgresql Realtime Driver
Reporter:Manuel Guesdon (mguesdon)Labels:
Date Opened:2005-11-07 04:59:43.000-0600Date Closed:2008-01-15 17:21:40.000-0600
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Core/General
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) res_config_pgsql.c
Description:Here is a Postgresql realtime driver. It is based on mysql one.
Makefile should add libpq library (-lpq) and include directory (-I/usr/include/pgsql/ on my config).

Here is a config samplefile:
;
; Sample configuration for res_config_pgsql.c
;
; The value of dbhost may be either a hostname or an IP address.
; If dbhost is commented out or the string "localhost", a connection
; to the local host is assumed and dbsock is used instead of TCP/IP
; to connect to the server.
;
[general]
dbhost=TheDBHost
dbport=5432
dbname=TheDBName
dbuser=TheDBUser
dbpass=TheDBUserPassword
dbsock=


It has been tested for sipusers.
UPDATE hasn't been tested
Comments:By: Olle Johansson (oej) 2005-11-07 06:10:25.000-0600

Do you have a disclaimer on file?
By: Manuel Guesdon (mguesdon) 2005-11-07 07:00:31.000-0600

I'mn not sure I understand the question :-)
I've just faxed http://www.digium.com/disclaimer.txt to Digium
By: Russell Bryant (russell) 2005-11-07 09:11:49.000-0600

Someone else has been working on this in ASTERISK-5119.  Perhaps you guys can work together on this?
By: Manuel Guesdon (mguesdon) 2005-11-11 13:20:12.000-0600

I've contacted 'pressureman' but he's no more working on asterisk and he hasn't   signed the disclaimer so I presume his code won't be integrated into asterisk so it's probably better to integrate mine, isn't it ?

Manuel
By: Clod Patry (junky) 2006-01-03 20:09:50.000-0600

can we go somewhere with this interesting patch (or maybe ASTERISK-5119) ?
Since these patches are here for some months now.
By: Olle Johansson (oej) 2006-02-09 09:15:59.000-0600

We need test reports. Does it support UPDATEs now? It is required for SIP.
By: Olle Johansson (oej) 2006-03-03 05:50:22.000-0600

I still need an answer on the UPDATE question...
By: Manuel Guesdon (mguesdon) 2006-03-08 11:53:04.000-0600

There's an update function (.update_func = update_pgsql). As far as I remember it was working. Did you tried ?
By: Olle Johansson (oej) 2006-03-08 13:50:38.000-0600

Please upload a sample configuration file. Thanks.
By: Olle Johansson (oej) 2006-03-08 13:53:22.000-0600

Found the config above.

We will not charge you extra if you add comments in your source code. It make the driver more maintainable both for you and for others. :-)
By: Olle Johansson (oej) 2006-03-08 13:59:26.000-0600

Added to "test-this-branch"
By: Michael J. Maravillo (maravillo) 2006-03-13 23:36:09.000-0600

Just tried out the driver and so far it works with sip and extensions... I'm getting a warning though where it tries to query the extension table with the ip address of the asterisk server.  I've only tried odbc with pgsql before so I'm not sure if the same query happens with mysql.  Great work guys!  Will try it later with voicemail and queues also...

extconfig.conf
--------------
sipusers => pgsql,asterisk,account
sippeers => pgsql,asterisk,account
extensions => pgsql,asterisk,extension

Mar 14 13:24:35 DEBUG[6887]: res_config_pgsql.c:739 pgsql_reconnect: Postgresql RealTime: Everything is fine.
Mar 14 13:24:35 DEBUG[6887]: res_config_pgsql.c:129 realtime_pgsql: 1Postgresql RealTime: Result=0x9530e50 Query: SELECT * FROM extension WHERE exten LIKE 'xxx.xxx.xxx.xxx%' AND context = 'default' AND priority = '1'
Mar 14 13:24:35 WARNING[6887]: res_config_pgsql.c:170 realtime_pgsql: Postgresql RealTime: Could not find any rows in table extension.
Mar 14 13:24:35 DEBUG[6887]: res_config_pgsql.c:739 pgsql_reconnect: Postgresql RealTime: Everything is fine.
By: Olle Johansson (oej) 2006-03-14 05:16:52.000-0600

Finally installed PostgreSQL in my Fedora core 4 box with "yum install postgresql-devel". Compilation totally fails, lacking include files.

I dont know if there's a version problem or something else...
By: Michael J. Maravillo (maravillo) 2006-03-14 05:29:52.000-0600

On CentOS 4, I had to change the line in res_config_pgsql.c that includes postgresql's libpq-fe.h to:

#include <libpq-fe.h>
By: Olle Johansson (oej) 2006-03-14 05:44:12.000-0600

Fixed the version in test-this-branch. Please check that version. Get a lot of compiler warnings about unused variables, that needs to be cleaned out. Thanks.

http://svn.digium.com/view/asterisk/team/oej/test-this-branch/res/res_config_pgsql.c?view=markup
By: Olle Johansson (oej) 2006-03-14 05:46:33.000-0600

maravillo: Thanks!
By: Christian Benke (christianbee) 2006-03-14 06:19:47.000-0600

had to change
#include <pgsql/libpq-fe.h>
#include <errmsg.h>

to

#include <postgresql/libpq-fe.h >
#include <mysql/errmsg.h>

on gentoo linux 2005.1.

after that it compiled fine, but asterisk crashed with:

Asterisk 1.2.5, Copyright (C) 1999 - 2006 Digium, Inc. and others.
Created by Mark Spencer <markster@digium.com>
Asterisk comes with ABSOLUTELY NO WARRANTY; type 'show warranty' for details.
This is free software, with components licensed under the GNU General Public
License version 2 and other licenses; you are welcome to redistribute it under
certain conditions. Type 'show license' for details.
=========================================================================
[ Booting...Mar 14 10:10:26 NOTICE[4689]: cdr.c:1187 do_reload: CDR simple logging enabled.
.Mar 14 10:10:26 WARNING[4689]: res_musiconhold.c:998 load_moh_classes: The old musiconhold.conf syntax has been deprecated!  Please refer to the sample
configuration for information on the new syntax.
.Warning, flexibel rate not heavily tested!
.Mar 14 10:10:26 NOTICE[4689]: res_odbc.c:294 load_odbc_config: registered database handle 'test_db' dsn->[test_db]
Mar 14 10:10:26 NOTICE[4689]: res_odbc.c:554 odbc_obj_connect: Connecting test_db
Warning, flexibel rate not heavily tested!
Mar 14 10:10:26 WARNING[4689]: res_odbc.c:565 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=0 [unixODBC][Driver Manager]Data source name not found,
and no default driver specified
Mar 14 10:10:26 NOTICE[4689]: res_odbc.c:599 load_module: res_odbc loaded.
...Junk at the beginning 49443303
.Warning, flexibel rate not heavily tested!
..Mar 14 10:10:26 NOTICE[4689]: config.c:863 ast_config_engine_register: Registered Config Engine odbc
..Mar 14 10:10:26 ERROR[4689]: res_config_mysql.c:615 mysql_reconnect: MySQL RealTime: Failed to connect database server  on . Check debug for more info.
Mar 14 10:10:26 WARNING[4689]: res_config_mysql.c:450 load_module: MySQL RealTime: Couldn't establish connection. Check debug.
Mar 14 10:10:26 NOTICE[4689]: config.c:863 ast_config_engine_register: Registered Config Engine mysql
.Mar 14 10:10:26 WARNING[4689]: res_config_pgsql.c:609 parse_config: Postgresql RealTime: No database user found, using 'asterisk' as default.
Mar 14 10:10:26 WARNING[4689]: res_config_pgsql.c:616 parse_config: Postgresql RealTime: No database password found, using 'asterisk' as default.
Mar 14 10:10:26 WARNING[4689]: res_config_pgsql.c:623 parse_config: Postgresql RealTime: No database host found, using localhost via socket.
Mar 14 10:10:26 WARNING[4689]: res_config_pgsql.c:630 parse_config: Postgresql RealTime: No database name found, using 'asterisk' as default.
Mar 14 10:10:26 WARNING[4689]: res_config_pgsql.c:637 parse_config: Postgresql RealTime: No database port found, using 5432 as default.
Mar 14 10:10:26 WARNING[4689]: res_config_pgsql.c:644 parse_config: Postgresql RealTime: No database socket found, using '/tmp/pgsql.sock' as default.
asterisk: symbol lookup error: /usr/lib/asterisk/modules/res_config_pgsql.so: undefined symbol: PQconnectdb
mgw2 asterisk-1.2.5 # Ouch ... error while writing audio data: : Broken pipe
Ouch ... error while writing audio data: : Broken pipe
Ouch ... error while writing audio data: : Broken pipe
By: Olle Johansson (oej) 2006-03-14 07:28:26.000-0600

christianbee: Try updating to the latest version from test-this-branch. Thanks.
By: Olle Johansson (oej) 2006-03-14 14:36:37.000-0600

Created separate branch for this driver "team/oej/res_config_pgsql"
By: Christian Benke (christianbee) 2006-03-17 13:10:18.000-0600

compiles fine for gentoo now, also seems to run stable.
cmd's voicemailmain and voicemail work.
features password, email and saycid are working too(values are read properly from the table). have not tried anything else(and i only need realtime voicemail). please tell me if theres anything special i can do to test...

thanks!!!
kind regards
christian

p.s.:when is moh-realtime finished? ;-)
By: Olle Johansson (oej) 2006-04-05 11:16:39

mguesdon: Please join discussion on the asterisk-dev mailing list
By: Olle Johansson (oej) 2006-04-05 12:46:58

Committed to svn trunk, rev 17628.

Thanks for your contribution to Asterisk!
By: Digium Subversion (svnbot) 2008-01-15 17:21:40.000-0600

Repository: asterisk
Revision: 12470

U   team/oej/test-this-branch/README.test-this-branch
A   team/oej/test-this-branch/configs/res_pgsql.conf
U   team/oej/test-this-branch/res/Makefile
A   team/oej/test-this-branch/res/res_config_pgsql.c

------------------------------------------------------------------------
r12470 | oej | 2008-01-15 17:21:39 -0600 (Tue, 15 Jan 2008) | 2 lines

Adding ASTERISK-5489 - PostgreSQL realtime driver

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=12470