Tuesday, November 20, 2012

Auto-Constructing DDL for a MySQL federated table from and existing table

For anyone looking for a tool to automatically generate a DDL statement for a federated table from the DDL statement of an existing table, here is the PHP script (note: this was only tested with MySQL 5.5)

Please see http://forums.mysql.com/read.php?10,512901,513020#msg-513020

 <?php  
 parse_str(implode('&', array_slice($argv, 1)), $_GET);  
 $mysqli = new mysqli('target_host', 'login_id', 'password', 'db_name', '3306' );  
 if(!$mysqli) die("Connection error. Aborting\n");  
 // this is the name of the server used in the CREATE SERVER statement  
 $fedlink_server = $_GET['fedlink_server'] = "my_preconfigured_fedlink";  
 $source_table = $_GET['source_table']   ;//= "accounts";  
 $target_table = $_GET['target_table']   ;//= "fedlinked_accounts";  
 if ((strlen($source_table) ==0) OR (strlen($target_table) ==0))  
   die("Required values: source_table=?, target_table=?\n\n");  
 echo "source_table=$source_table\n";  
 echo "target_table=$target_table\n";  
 $sql = "SHOW CREATE TABLE `$source_table`;";  
 $mysqli_result = $mysqli->query($sql);  
 if (!$mysqli_result) die("The query failed to return results\n");  
 $row = $mysqli_result->fetch_all();  
 $ddl = $row[0][1];  
 $ddl = str_replace("''", "'", $ddl);  
 $ddl_arr = preg_split("/\n/", $ddl);  
 $ddl_end = $ddl_arr[sizeof($ddl_arr) -1];  
 $ddl_arr[0] = "CREATE TABLE `$target_table` (";  
 $charset= (substr_count($ddl_end, "DEFAULT CHARSET=utf8") == 1)  
     ? $charset = " CHARSET=utf8"  
     : "";  
 $ddl_arr[sizeof($ddl_arr) -1]  
   = ") ENGINE=FEDERATED $charset CONNECTION='$fedlink_server/$source_table';";  
 echo join("\n", $ddl_arr);  
 ?>  
Simply execute the script as follows (for example):

php -f the-script-in-a-file.php source_table=accounts target_table=linkedtable_account

NOTE: This script was written to suite MY needs and won't necessarily work for every situation.

Sunday, October 14, 2012

The Rise of Postgres

I have been advocating PostgreSQL close to a decade now with little success. People just didn't seem to get it. It seems that it's finally catching on now. Various apps I use are now being deployed with pro-PostgresSQL. Even ones which in the past were pro-MySQL (Atlassian Confluence for example). The tide is finally changing. Read the full article here, and another one here.

All I can say on this is that it's nice to be ahead of the curve, even when everyone thinks you're nuts - Unfortunately that doesn't help you get hired, in a place like Las Vegas.

Wednesday, September 26, 2012

TIP: How to save on web page hosting fees

As many of you know web hosting companies charge a fee for hosting websites. Most small businesses just need an online-brochure for their website. Nothing fancy, just a simple template to put images and text into. Such solutions usually cost about $5 per month. If a simple online-brochure is all you need your website to be then there's really no reason to spend that money. Did you know that blogger.com can host your site at your own "www" URL and you wont have to pay for the hosting?
All you need to do after purchasing your domain, is:
  1. create a blogger.com account for your business
  2. go to the settings for your blogger.com account
  3. click "Add a custom domain", and follow the instructions

Probably the most complicated part for most people, is that you will need to update your DNS settings in your domain so that the web page requests to your "www" site point to your blogger site. Don't worry about adds. You don't have to publish Google AddSense adds on your SMB site. I would imagine that many SMBs that use GoDaddy for hosting, might be looking to have their site hosted elsewhere, after GoDaddy's service outage on Sept 10th.  

Asterisk Dictate and the old Hangup Issue

I implemented a custom phone based dictation solution using Asterisk PBX and the Dictate app and noticed that anything in the dictation dial-plan after the Dictate command, was never executed *if* the call was dropped or hung-up by the caller. When researching the issue, I found various other references to people having this same problem:

  • http://www.voip-info.org/wiki/index.php?page_id=2595&comments_page=1 (My solution could have saved someone $40K apparently!)
  •  http://forums.digium.com/viewtopic.php?t=7151

The most typical reason for adding other dial-plan entries after the Dictate call, is to apply post processing of the .raw files after the dictation call is complete. Note that ending the dictation in the normal manner, by using #,# would exit the Dictate app gracefully and therefore dial-plan entries after the Dictate call, would be executed.

The Asterisk Dictate application has a bug [IMHO] which causes the Dictate application to continue after a caller has hung up the call or the call was dropped. The attached file shows debug information where one can see that Asterisk recieves "SIP BYE" and proceeds to issue the "owner hangup", but immediately after that the log shows a warning: "WARNING[12065] file.c: Failed to write frame", followed by the Dictate app playing another sound file prompting to enter a new file name: "Playing 'dictate/enter_filename.ulaw'".
So it's evident that the Dictate app knows that the dictation has stopped because it prompt for a new file name for a new dictation, however, it does not detect from Asterisk that the call ended, which is why it prompts for a new dictation file-name.

Log output:

 <!-- the call hang-up is detected by Asterisk here: -->  
 [2012-09-26 06:40:07] DEBUG[11441] chan_sip.c: **** Received BYE (8) - Command in SIP BYE  
 [2012-09-26 06:40:07] DEBUG[11441] netsock2.c: Splitting '192.168.1.106:62724' into...  
 [2012-09-26 06:40:07] DEBUG[11441] netsock2.c: ...host '192.168.1.106' and port '62724'.  
 [2012-09-26 06:40:07] DEBUG[11441] chan_sip.c: Setting SIP_ALREADYGONE on dialog B3L2U8Zl2YEmju4BEo0jwFk8nBTlKiwk  
 [2012-09-26 06:40:07] DEBUG[11441] res_rtp_asterisk.c: Setting RTCP address on RTP instance '0xb7704b18'  
 [2012-09-26 06:40:07] DEBUG[11441] chan_sip.c: Session timer stopped: 777 - B3L2U8Zl2YEmju4BEo0jwFk8nBTlKiwk  
 [2012-09-26 06:40:07] DEBUG[11441] chan_sip.c: Received bye, issuing owner hangup  
 [2012-09-26 06:40:07] DEBUG[11441] chan_sip.c: Trying to put 'SIP/2.0 200' onto UDP socket destined for 192.168.100.2:62724  
 [2012-09-26 06:40:07] DEBUG[11452] manager.c: Examining event:  
 <!-- Dictate (I assume) tries to create a file handle for a new dictation here - even though the call has ended: -->  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Set channel SIP/999-00000004 to write format ulaw  
 [2012-09-26 06:40:07] WARNING[12065] file.c: Failed to write frame  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Scheduling timer at (0 requested / 0 actual) timer ticks per second  
 <!-- Dictate prompts for a new dictation filename to write data to here - even though the call has ended: -->  
 [2012-09-26 06:40:07] VERBOSE[12065] file.c: -- <SIP/999-00000004> Playing 'dictate/enter_filename.ulaw' (language 'en')  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Set channel SIP/999-00000004 to read format ulaw  
 [2012-09-26 06:40:07] DEBUG[12065] pbx.c: Extension _., priority 11 returned normally even though call was hung up  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Soft-Hanging up channel 'SIP/999-00000004'  
 <!-- Notice that Asterisk does answer on the hangup extension for the Dictate app after the call ends, -->  
 <!-- but I have noticed that adding dial-plan directives there (for post-processing) won't work either -->  
 [2012-09-26 06:40:07] DEBUG[12065] pbx.c: Launching 'Answer'  
 [2012-09-26 06:40:07] VERBOSE[12065] pbx.c: -- Executing [h@app-dictate-record-custom:1] Answer("SIP/999-00000004", "") in new stack  
 [2012-09-26 06:40:07] DEBUG[12065] pbx.c: Spawn extension (app-dictate-record-custom,h,1) exited non-zero on 'SIP/999-00000004'  
 [2012-09-26 06:40:07] VERBOSE[12065] pbx.c: == Spawn extension (app-dictate-record-custom, h, 1) exited non-zero on 'SIP/999-00000004'  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Scheduling timer at (0 requested / 0 actual) timer ticks per second  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Scheduling timer at (0 requested / 0 actual) timer ticks per second  
 [2012-09-26 06:40:07] DEBUG[12065] channel.c: Hanging up channel 'SIP/999-00000004'  
 [2012-09-26 06:40:07] DEBUG[12065] chan_sip.c: Hangup call SIP/999-00000004, SIP callid B3L2U8Zl2YEmju4BEo0jwFk8nBTlKiwk  
 [2012-09-26 06:40:07] DEBUG[12065] chan_sip.c: Updating call counter for incoming call  

Since the CDR is always correctly updated after the caller hangs up, I simply updated my own post-processing script to check the CDR records for dictations that are completed. Problem solved.

Thursday, September 20, 2012

Keep Unix password in sync with Atlassian Crucible password

I developed a little app stack using Atlassian Crucible and Postfix+Dovecot for mail. I'm not using LDAP or any other SSO solution since that would be an over-kill solution for the few users that will be using my app stack. To keep the users passwords in sync from unix (for mail access) with Crucible, I simply have to have a utility to keep the passwords in sync. (BTW, Usermin would also be over-kill)

Since the users are already used to doing everything via a web GUI, I decided to keep the utility in a web page and use PHP for scripting the solution on the back-end.

There are 3 very simple parts to this solution. Firstly, we have the PHP script function update the user's Confluence login via the SOAP service provided in Confluence as follows:


 function change_confluence_password($user, $password, $new_password) {  
   // login to the Confluence SOAP service  
   $soapClient = new SoapClient("http://your-server.local/rpc/soap-axis/confluenceservice-v2?wsdl");  
   try {  
     $token = $soapClient->__soapCall("login", array('in0'=>$user,'in1'=>$password));  
     if (is_soap_fault($token)) { echo $token->faultstring; exit; }  
   } catch (SoapFault $e) {  
     echo $e->getMessage();  
     return 0;  
   }  
   // change the password:  
   // boolean changeMyPassword(String token, String oldPass, String newPass) - changes the current user's password  
   $params = array('in0'=>$token,'in1'=>$password,'in2'=>$new_password);  
   try {  
     $result = $soapClient->__soapCall("changeMyPassword", $params);  
   } catch (SoapFault $e) {  
     echo $e->getMessage();  
     return 0;  
   }  
   return $result;  
 }  


Next we have a PHP function to request a password change from my "rapchan" script. What is rapchan? rapchan is simply a little mechanism to Request A Password Change (rapchan). The solutions I have seen on the web for making a change to a password via PHP were not 100% satisfactory to me so I created a mechanism whereby a PHP script can request a password change and a cron job run as root can fulfill the request. Having the password change mechanism in the same PHP script as the request seems like a bad idea to me.

So how is the rapchan paradigm different? My solution involves having a script (PHP in this case) drop a special file in a special folder, which represents a request to have a password changed. For security purposes that folder may not be readable to anyone except root otherwise requested password changes would be leaked. Furthermore, rapchan does not allow password change requests for root. No-one one the sudo group can have their password changed via rapchan either, unless so configured (user beware).

How does it work? All the script (which requests a password change) needs to do, is drop a file into a certain folder. The filename must be formatted as [username][secret_file_suffix]. The "username" will be used to determine for what user we are requesting a password change. The contents of the file must contain a string on the first line which is what the new password should be.

Some security considerations: The secret suffix is a small security measure. It requires that the calling script add the required suffix at the end of the file in order for the request to be honored. The more important security consideration is that the folder where the file is dropped into, belongs to the "rapchan" group; the requesting application must be in the "rapchan" group and the folder permissions must be 720. That way only applications executed as users in the rapchan group are allowed to actually request password changes by dropping requests in to that folder. The script which contains the configurations settings and executes the actual password change, must be owned by the root user. The last security measure for a PHP script, would be to use Zend Optimizer for some obfuscation of the PHP script so that the secret suffix is harder to divulge.

How do I set this up?
1) Create a group named rapchan
2) Create a folder owned by root:rapchan and set the permissions to 720
3) Drop the following script code into a folder, which you script via cron to be executed as often as you need:
 #!/bin/bash  
 ############# CONFIGURATION ##############  
 QFOLDER="/etc/rapchan/queue"  
 OKFORSUDO="no"  
 SECRETSUFFIX=".rap"  
 ############## EXECUTION #################  
 # get the permissions on the folder  
 permissions_on_queue_folder="$(stat --format=%a $QFOLDER)"  
 # check the queue for files to process  
 for QFILE in $QFOLDER/*  
 do  
  # make sure the folder has the proper permissions  
  if [ $permissions_on_queue_folder != 720 ];  
  then  
   # log the problem if it does not  
   logger "rapchan error: skipping $QFILE. Queue folder permissions not 720"  
  else  
   # make sure the file has the SECRETSUFFIX  
   if [[ $QFILE == *$SECRETSUFFIX ]]; then  
    # get the username from the filepath  
    USER=${QFILE%$SECRETSUFFIX}  
    USER=${USER#$QFOLDER/}  
    # make sure there is a matching USER for the file, but not the root user  
    if [ -n "$(getent passwd $USER)" ] && [ $USER != "root" ]; then  
     # don't change passwords for sudo users unless allowed in config (above)  
     if [ $OKFORSUDO == "no" ] && [[ "$(id $USER)" == *"(sudo)"* ]]; then  
      logger "rapchan error: skipping $USER. User is in sudo group"  
     else  
      # change the password  
      PASS="$(head -n 1 $QFILE | sed 's/ *$//g' | sed 's/^ *//g')"  
      echo $USER:$PASS | /usr/sbin/chpasswd  
     fi  
    fi  
   fi  
  fi  
  # remove the file from the queue  
  rm -f $QFILE  
 done  

Finally just make sure that what ever user your PHP script will be running under, is in the rapchan group and have your PHP script write the requested password change into the rapchan queue:
 function rapchan($user, $new_password) {  
   file_put_contents("/etc/rapchan/queue/$user.rap", $new_password);  
 }  


There are some other obvious things you need to take care of such as making sure that you dont change the unix password unless the password change call to the Confluence web service was successful, but that level of detail is not covered here.

HTH!

Sunday, September 16, 2012

OS X Mountain Lion Webserver SSL nightmare

I'm having an issue with OS X Webserver that I'm trying to figure out with Apple. Apparently many other people have also been having SSL issues when using Server.app, since before Mountain Lion.

Read more: https://discussions.apple.com/thread/4299762

Wednesday, August 22, 2012

Setting up a dial-plan for dictations in Asterisk

Setting up a dictation system in FreePBX is a snap ... IF you're familiar with Asterisk and FreePBX.
I have spent 3 days toying around with it and finally setup an Asterisk based system with no prior experience.

First though, let me give out due kudos to the guys over at FreePBX.org for putting out a great product. Also check out their SipStation.com site for getting SIP trunking service. Since the creators of FreePBX are closely affiliated with SipStation.com it's no wonder that setting up your SipStation.com SIP account details into your FreePBX server is drop-dead-easy. Thank you guys!

So why was setting up an Asterisk dictation system so time consuming?

  • Well for one thing, there is a lot of documentation to go through if you are a complete noob like me. I am a firm believer that one should read the documentation and ask questions if you're still stuck. The best book I have found to date for Asterisk is Practical Asterisk 1.4 and 1.6
  • One of the opening quotes of that book states in part "...how much fun tinkering could be" which I whole-heartedly agree with. If you're noob like am, you're going to want to play with your FreePBX system for a few days get a feel of all it can do.
  • For questions that you may post on various forums, dont always expect an accurate; fast or courteous reply, but dont let that discourage you from posting questions either.
  • There are a lot of loose ends that one must string together in FreePBX to accomplish a complete dictation workflow. If you're new to the system, you're just going to have to get familiar with all the features and figure out what works best for you scenario. The reason there are so many loose ends is because it should be that way. There is no one-size-fits-all configuration for anything in Asterisk.
  • Also, I have noticed that when one asks for assistance on the forums with regard to using the Dictate feature, everyone seems to asume that the users are part of the local network. If you are setting up a Asterisk dictation system where the users are calling in from a PSTN then you need to make that clear when you post your questions. It does make a difference when the users of your dictation system are not part of the local phone system.

That being said, my current solution simply involves having calls from an inbound route go to an IVR which I use as a "main menu" for the inbound calls and some other routing, until the user simply dials *34 to begin the dictation app. I would prefer if the dictation app started automatically, but I haven't quite figured that out yet.

The next tricky part is in the configuration of the [app-dictate-record] dialplan. Using my routing model above, the ${AMPUSER} variable is null and hence the default extension in that dialplan cannot work as provided by the FreePBX distro. The intention of how they have it coded is to have the dictation go into a folder which is named after the users extension. Hence, if the user from extension 101 uses the dictation feature, their dictations would go into folder 101. In my case, since the callers from my system are inbound over the PSTN, they are not authenticated as local users, hence ${AMPUSER} is null and all the dictations get dumped into the same folder. Also, the Dictate app asks the users to enter a file name, which is a really really bad idea for my solution, because if a user uses a filename they have used before, Dictate will over-write the existing one - YIKES!
To solve all these issues, I over-write the existing dictation dialplan with this one:

[app-dictate-record]
                                                                     
exten => *34,1,Answer
exten => *34,n,Macro(user-callerid,)
exten => *34,n,Noop(CallerID is ${AMPUSER})
exten => *34,n,Set(DICTENABLED=${DB(AMPUSER/${AMPUSER}/dictate/enabled)})
exten => *34,n,Set(CIDPRE=${CALLERID(name)})
exten => *34,n,Set(CIDPRE=${CIDPRE:0:2})
exten => *34,n,GotoIf($[$["x${DICTENABLED}"="x"]|$["x${DICTENABLED}"="xdisabled"]]?nodict:dictok)
exten => *34,n(nodict),Playback(feature-not-avail-line)
exten => *34,n,Hangup
exten => *34,n(dictok),Dictate(/var/lib/asterisk/sounds/dictate/${CIDPRE},${STRFTIME(${EPOCH},,%Y%m%d-%H:%M:%S)})
exten => *34,n,Macro(hangupcall,) 

I use the CIDPRE variable to capture the "CID Prefix" configured elsewhere in my routing model, and that corresponds with a specific user as selected through the IVR and I use an auto-generated date-time file name so that each filename will be unique. One of my main goals is to do as much of the configuring through the FreePBX GUI as possible. The code snippet above is the only thing I had to customize under the hood.

Done, now we're ready to begin testing!

[Update]
This is what I eventually came up with:
 ;tmsoa custom 1.2  
 [app-dictate-record-tmsoa]  
 exten => _.,1,Answer  
 ;exten => _.,n,SayDigits(1.2)  
 exten => _.,n,Macro(user-callerid,)  
 exten => _.,n,Set(CIDPRE=${CALLERID(name)})          ;capture the CID info  
 exten => _.,n,Set(CIDPRE=${CIDPRE:0:3})          ;get the first 3 characters of the CID prefix  
 ;set a file name that matches the date + time and the and then the paths  
 exten => _.,n,Set(DICTFILENAME=${STRFTIME(${EPOCH},,%y%m%d-%H.%M.%S)})  
 exten => _.,n,Set(DICTFILEPATH=/var/spool/asterisk/dictate)  
 exten => _.,n,Set(DICTFILEPATHRAW=${DICTFILEPATH}/raw/${CIDPRE})  
 exten => _.,n,Set(DICTFILEPATHWAV=${DICTFILEPATH}/wav/${CIDPRE})  
 ;make sure the audio file directories exist  
 exten => _.,n,System(mkdir -p ${DICTFILEPATHRAW})  
 exten => _.,n,System(mkdir -p ${DICTFILEPATHWAV})  
 exten => _.,n,System(mkdir -p ${DICTFILEPATHRAW}/.archive)  
 ;authenticate with VM pwd in MB matching CID prefix  
 exten => _.,n,VMAuthenticate(${CIDPRE}@default)  
 ;record dictation to folder matching CID prefix with the file name in date-time format  
 exten => _.,n,Dictate(${DICTFILEPATHRAW},${DICTFILENAME})  
 ;** DO NOT ADD ANYTHING AFTER THIS DIAL-PLAN SINCE IT  
 ;** WILL JUST BE IGNORED IF THE CALL IS DROPPED/HUNGUP  

Note:
I use the "SayDigits(1.2)" on development hosts to indicate the version number for dial plans I am working on. This gets commented out befor deployment.