Summary: | ASTERISK-02115: [new app] [post-1.0] app_sql_mysql | ||
Reporter: | constfilin (constfilin) | Labels: | |
Date Opened: | 2004-07-26 19:17:32 | Date Closed: | 2004-09-25 02:01:42 |
Priority: | Major | Regression? | No |
Status: | Closed/Complete | Components: | Applications/General |
Versions: | Frequency of Occurrence | ||
Related Issues: | |||
Environment: | Attachments: | ( 0) app_sql_mysql.c ( 1) app_sql_mysql.c ( 2) app_sql_mysql.c ( 3) apps_Makefile | |
Description: | I coded a new Asterisk application called MYSQL which is similar to application PGSQL but works with MYSQL. The syntax of the application is: ------------------- *CLI> show application Mysql -= Info about application 'MYSQL' =- [Synopsis]: Do several mySQLy things [Description]: MYSQL(): Do several mySQLy things Syntax: MYSQL(Connect connid dhhost dbuser dbpass dbname) Connects to a database. Arguments contain standard MySQL parameters passed to function mysql_real_connect. Connection identifer returned in ${var} MYSQL(Query resultid ${connid} query-string) Executes standard MySQL query contained in query-string using established connection identified by ${connection_identifier}. Result of query is is stored in ${var}. MYSQL(Fetch fetchid ${resultid} var1 var2 ... varn) Fetches a single row from a result set contained in ${result_identifier}. Assigns returned fields to ${var1} ... ${varn}. ${statusvar} is set TRUE if additional rows exist in reseult set. MYSQL(Clear ${resultid}) Frees memory and datastructures associated with result set. MYSQL(Disconnect ${connid}) Disconnects from named connection to MySQL. *CLI> ------------------- I am attaching the source file and the new applicatins Makefile for the change. My disclaimer is on file with Digium. | ||
Comments: | By: Olle Johansson (oej) 2004-07-28 13:52:39 Thank you for contributing! Please e-mail the -dev list and see if we can get other users to comment and test this addition. /Olle By: Olle Johansson (oej) 2004-08-08 15:47:34 What's the situation with MySQL? Does this go into addons or cvs head? By: twisted (twisted) 2004-08-12 19:00:40 Please respond, as no response has been recieved, nor comments received on this, We will be closing soon...... By: constfilin (constfilin) 2004-08-12 19:05:46 I posted to -dev mailing list and the comment I've got is that we need to come up with the generic solution for accessing databases instead of writing a separate app for each DB. I suggested to use ODBC but was told that ODBC is slow. Meanwhile I have been using MYSQL all along in my dialplans. In fact the original MYSQL source was of good quality and I didn't have to make any additional patches. Also, I got a message from another person saying that MYSQL works for him to. I think many people will benefit if this gets to CVS. By: twisted (twisted) 2004-08-12 19:28:12 I have to agree with the benefits here, but I have a couple of questions: 1) since we're linking to MySQL, would this belong in the -addons or could it be dropped into head? I'm not sure of the MySQL licensing methods. 2) There is some truth to the statement that we need a unified method of db access, rather than creating new apps for each one; how difficult would it be to create a framework for db access that we could just 'slap in' access modules? By: constfilin (constfilin) 2004-08-13 12:41:34 1. If I were to choose, I'd put it in the head. Rationale: there is mysql sip friends implementations that is already in the head (chan_sip.c). So adding mysql into the head will not create any new licensing problems. But I'd be happy to see it in add-ons as well. 2. I'd estimate it to be 2-4 days including the initial modules for PostgreSQL and MYSQL. However, I am unlikely to find this time unless this is necessary for the business my company is in. By: twisted (twisted) 2004-08-13 14:24:54 I would suggest we make a crack at some sort of external data storage/retrieval framework, that we could just plug in modules for , and based on the calls in the dialplan logic, we determine what type of db we're connecting to, assuming we have that module installed.. something like... res_database, and called from the cli/extensions.conf similar to the method you are using above, except we could use options to specify the db type - Database([action][|query string[|dbtype]]) could prove useful, and being 'pluggable' theoretically our access methods are unlimited... By: constfilin (constfilin) 2004-08-13 16:23:15 1. just when I said that I didn't have to need any patches to original source, I ran into a bug :(. The problem was in scenario when SQL query does not return any rows (e.g. INSERT or UPDATE). Now MYSQL() now handles these scenarios correctly. Moreover, for INSERTs, UPDATEs and DELETEs there is no need to call MYSQL(Clear ...) after MYSQL(Query). I am attaching new app_sql_mysql.c for completeness. If anyone will need MYSQL then (s)he can pick it up here. 2. Yes, something like "Database([action][|query string[|dbtype]])" would be good. Another improvement must be done for caching SQL connection handles. Right now a new SQL connection opens and closes on each channel. this is ineffective. 3. Until we are done with "Database", we can put MYSQL into -addons. But - eventually - it is your call. Thanks! -c By: constfilin (constfilin) 2004-08-16 14:35:17 Yet another improvement to MYSQL application. Now MYSQL uses mutexes to avoid 2 threads changing its static variables at the same time. By: Brian West (bkw918) 2004-08-22 22:57:56 this will have to go in addons I suspect. By: Mark Spencer (markster) 2004-09-17 09:24:44 Added to asterisk-addons, thanks! |