After a successful asterisk installation I was asked to also add call recording so that calls could be monitored and reviewed.To allow easy browsing of recorded calls it’s also handy to setup CDR to store call records in a database and since MixMonitor() by records calls named after the UNIQUEID of the call it’s important this ID is recorded as well.

Unfortunately after some simple testing it seems that cdr_mysql in the asterisk-mysql package has not been compiled to log this important ID.Fortunately we can fix this ourselves using the Ubuntu packaging tools. Firstly we need to install the packages needed to edit the package:-

sudo apt-get install devscripts debhelper fakeroot pbuilder dpatch

Next we should download the source package:-

apt-get source asterisk-mysql

Now we can set about editing the package, a quick look in debian/patches tells us that this package uses dpatch to maintain its patches – so we should too:-

cd asterisk-addons-1.4.5/
dpatch-edit-patch loguniqueid

This will drop us into a sub-shell where we can make our code changes for dpatch to record for us in a patch file. All we need to do is edit the file cdr_addon_mysql.c and at around line 52 near the #define DATE_FORMAT line we should add:-

#define MYSQL_LOGUNIQUEID 1

Save the file and quit out of the sub-shell created by dpatch, dpatch will now create our patch file under debian/patches/. All that’s left for us to do is edit debian/patches/00list and insert our new patch file into the list of patch files, I put mine first so the 00list file now looks like this:-

loguniqueid.dpatch
nomarch.dpatch
include_asterisk

With that done we should bump the changelog to reflect our changes:-

dch -i

Add a description after the top blank * indent like “Added logging of uniqueid”, also, it’s advisable to append ~uniqueid1 (or anything you like really, the ~ is important) so that our package doesn’t conflict with any new package versions Ubuntu might release in future (Make sure that the Ubuntu codename still says “hardy” and not “intrepid” or indeed “jaunty” too). Save the changelog.

We are now ready to build our new package:-

debuild -S -uc -us

If all went well our new dsc file has been created in the parent directory, so all that remains is to get pbuilder building the package. If you’ve never used pbuilder before you need to run:-

sudo pbuilder create

If you have run pbuilder before you should check everything is up to date by running:-

sudo pbuilder update

With pbuilder up to date, let’s build our new package:-

sudo pbuilder build ../asterisk-addons_1.4.5~uniqueid1.dsc

After a bit of compiling if all was successful pbuilder will report it has successfully built the package, so let’s install it:-

sudo dpkg -i /var/cache/pbuilder/result/asterisk-mysql*.deb

Once the package is installed all that remains is to restart asterisk to pickup the new module:-

sudo asterisk -rx 'restart when convenient'

Now test your cdr_mysql logging again, you should find the uniqueid is being logged.

Linux asterisk, cdr, hardy, mysql, ubuntu, uniqueid

I’ve been using PDO as my database library and it works reasonably well (as long as you remember it’s not a full blown database abstraction library), however recently I needed to use nested transactions to ensure that the database remains consistent while doing a series of SQL statements.

Unfortunately PDO does not support nested transactions although PostgreSQL and MySQL do. I decided to extend the PDO class to support nested transactions while also using PDO to keep track of the first transaction. I came up with the following class (released under the GNU General Public License, Version 3):-

class MyPDO extends PDO {
    // Database drivers that support SAVEPOINTs.
    protected static $savepointTransactions = array("pgsql", "mysql");

    // The current transaction level.
    protected $transLevel = 0;

    protected function nestable() {
        return in_array($this->getAttribute(PDO::ATTR_DRIVER_NAME),
                        self::$savepointTransactions);
    }

    public function beginTransaction() {
        if(!$this->nestable() || $this->transLevel == 0) {
            parent::beginTransaction();
        } else {
            $this->exec("SAVEPOINT LEVEL{$this->transLevel}");
        }

        $this->transLevel++;
    }

    public function commit() {
        $this->transLevel--;

        if(!$this->nestable() || $this->transLevel == 0) {
            parent::commit();
        } else {
            $this->exec("RELEASE SAVEPOINT LEVEL{$this->transLevel}");
        }
    }

    public function rollBack() {
        $this->transLevel--;

        if(!$this->nestable() || $this->transLevel == 0) {
            parent::rollBack();
        } else {
            $this->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->transLevel}");
        }
    }
}

This code will only attempt to use the SAVEPOINT code if you’re using a database driver that supports it (it should probably version check the database server) this then means that in your code you can do things like:-

$pdo = new MyPDO(DB_DSN, DB_USER, DB_PASS);
$pdo->beginTransaction();
try {
    $pdo->exec(...);
    $pdo->exec(...);

    $pdo->beginTransaction();
    try {
        $pdo->exec(...);
        $pdo->exec(...);
        $pdo->exec(...);
        $pdo->commit();
    } catch(PDOException $e) {
        // If this statement fails, rollback...
        // NOTE: This will only rollback statements made in the
        //       inner try { block and not the outer one.
        $pdo->rollBack();
    }

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
}

NB: I’ve tweaked the code slightly when transferring it to my blog and I haven’t tested it, so there could be some minor errors – please leave comments if you spot any. Thanks!

Coding mysql, pdo, php, postgresql, transactions