Summary:	ASTERISK-02974: [patch] ODBC Realtime extensions switch requires MySQL
Reporter:	Terry Wilson (twilson)	Labels:
Date Opened:	2004-12-13 00:33:34.000-0600	Date Closed:	2008-01-15 15:17:31.000-0600
Priority:	Minor	Regression?	No
Status:	Closed/Complete	Components:	Core/General
Versions:		Frequency of Occurrence
Related Issues:
Environment:		Attachments:	( 0) pbx_realtime.c.patch.txt
Description:	Using realtime extensions lookups only works for mysql (and possibly msql), but not postgresql.  The cause is the non-standard RLIKE operator which is used--which PostgreSQL doesn't support.  Since we have a res_odbc and res_mysql, I assume that we are aiming for multi-db capability under ODBC.  :-) ****** STEPS TO REPRODUCE ****** The SQL3 (SQL99) standard is the first to address regular expression searches.  It specifies that the proper operator should be "SIMILAR TO", which postgres supports (but MySQL doesn't).  Postgres also has the non-standard '~' operator, which MySQL doesn't support.  I believe, that at least for the query we are doing, the RLIKE and '~' operators should be equivalent. ****** ADDITIONAL INFORMATION ****** This patch converts the RLIKE '_.*' query to a LIKE '\_%' query which should be equivalent for our purposes.  (You have to escape the backslash because in a LIKE it means match a single character).  Of course, I am assuming that the original query was to match any extension beginning with an '_' and followed by anything.  I have been unable to test this patch yet because I am out of the office.  At the very least, I thought I'd make everyone aware of the issue.
Comments:	By: Terry Wilson (twilson) 2004-12-13 00:47:30.000-0600 always forget... disclaimer on file. By: Terry Wilson (twilson) 2004-12-13 11:39:49.000-0600 New version of patch correctly changes '\_%' to '\\_%'.  Tested and it works on both MySQL and Postgres.  Although, with postgres I get a warning: res_config_odbc.c:144 realtime_odbc: SQL Get Data error! [SELECT * FROM extensions WHERE exten LIKE ? AND context = ? AND priority = ?] even though it works. By: Mark Spencer (markster) 2004-12-13 14:42:39.000-0600 To confirm, you did test with mysql *and* using patterns?  Any chance of trying to figure out more about why the postgres gives you that weird error?  Since it doesn't work with postgres right now at all, i'll obviously apply the patch without a solution for that problem but if we can figure it out it would be better. By: Terry Wilson (twilson) 2004-12-13 15:14:13.000-0600 I did test using patterns (ala _123123XXXX) and it worked for both mysql and postgres.  I don't get the error with postgres if I follow my exten,1,NoOp(Works) with an exten,2,Hangup.  So it has something to do with not getting any info back about an h (or t?), exten. Also, one of the only problems I really am having with realtime extensions is that if you want to dynamically create something that requires a new context (a new autoattendant menu?), it looks like you need to do a reload because you have nowhere to include the switch statement.  What do you think about an option in the [general] section for autoswitch => switchtype@blah/blah to automatically attempt a switch if it is sent to what appears to be an invalid extension/context, before hitting the actual invalid(i) extension?  This would make realtime more realtime.  If this functionality is already covered some other way and I missed it, kindly disregard the question.  :-) By: Terry Wilson (twilson) 2004-12-13 16:07:09.000-0600 The problem is probably the two queries: SELECT * FROM extensions WHERE exten LIKE '_%' AND context = 'inbound' AND priority = '1' SELECT * FROM extensions WHERE exten = '' AND context = 'inbound' AND priority = '1' query.   One returns pretty much all extensions with a priority of 1 -- Similar to a regular expression '.+'  The other obviously is searching for an empty extension.  These queries are executed even without my patch.  It almost seems like an off by one bug in that these queries are being sent out w/o an extension (I think the first query is setup based on the MODE_MATCHMORE case in pbx_realtime.c at line 104) and doesn't seem to be necessary to actually looking the data up out of the table.  Unfortunately, most if this is conjecture on my part because I just don't understand the ODBC code yet--I'm still trying to wrap my head anround it all).  But it seems obvious that executing these particular queries is wrong, since knowing all extensions that have a priority of one in a particular context seems kind of pointless...  Any ideas? edited on: 12-13-04 16:48 edited on: 12-13-04 17:36 By: Mark Spencer (markster) 2004-12-20 20:43:35.000-0600 Fixed in CVS, thanks! By: Digium Subversion (svnbot) 2008-01-15 15:17:31.000-0600 Repository: asterisk Revision: 4510 U   trunk/pbx/pbx_realtime.c ------------------------------------------------------------------------ r4510 | markster | 2008-01-15 15:17:31 -0600 (Tue, 15 Jan 2008) | 2 lines Fix Realtime to work with MySQL and Postgres both (bug ASTERISK-2974) ------------------------------------------------------------------------ http://svn.digium.com/view/asterisk?view=rev&revision=4510