Archive for the 'Development' Category

h1

Using Regular Expressions with SQLite

Wednesday, November 27th, 2013

SQLite is a great database tool that is available almost anywhere and is quick and easy to use.

I am currently using it for work I am doing for a client. I am writing a rules-based data validator in for a tab-delimited file using Perl on Windows. With SQLite, it’s simple to slurp the whole file into a database table and then query it multiple times to see whether columns conform to rules.

SQLite doesn’t quite come with a built-in REGEXP operator. I say not quite because it does alias REGEXP to a call to a function called regexp(re,str), however the function does not exist unless you provide it.

You can provide it by compiling a dynamic library extension (.so in Linux, .dll in Windows, .dylib on Mac), but my experience with that route was frustrating and bothersome. It’s only really necessary if you need to use SQLite with regular expressions from the commandline tool or a program outside your control.

Today I found out that if you are using SQLite in a program, you can provide the function in your own language!

Here is a simple example in PHP (it’s just as easy in Ruby or Perl or Python or whatever). Save this to dbtest.php and run it from the command prompt with ‘php dbtest.php’:

<?

if(! file_exists('test.db')){
        $db = new SQLite3('test.db');
        $db->exec("create table users (name varchar(10))");
        $db->exec("insert into users values ('Robert')");
        $db->exec("insert into users values ('Egbert')");
        $db->exec("insert into users values ('Dilbert')");
        $db->exec("insert into users values ('Ratbert')");
        $db->exec("insert into users values ('Dogbert')");
        $db->exec("insert into users values ('Umberto')");
        $db->close();
}

$db = new SQLite3('test.db');

// add the REGEXP magic to the db
function regexp($r,$s){return (preg_match("/$r/",$s)===1);}
$db->createFunction('regexp', 'regexp', 2);

// find names that start with a capital, two lowercase, then bert
$sql = "SELECT * FROM users WHERE name REGEXP '^[A-Z][a-z]{2}bert'";
$result = $db->query($sql);
while ($row = $result->fetchArray()) {
  echo $row['name'] . "\n";
}

/p?easy/ !

I would have thought it would be really slow, but I can run a REGEXP comparison against thousands of records and it’s plenty quick enough for my needs.

*Update*

I just added a CAPTURE function to SQLite that returns the first captured section of a string when you supply a regular expression with parentheses.

Here it is in Perl:

$dbh->func('capture',2,sub { my($regex,$string) = @_; my($capture) = $string =~ /$regex/; return $capture; }, 'create_function'); # get month from YYYY/MM/DD format where month or day could be one or two digits my $row = $dbh->selectrow_arrayref( "SELECT CAPTURE('\\d{4}/(\\d{1,2})/\\d{1,2}', date_created) FROM invoices" );

				
h1

iOS6 Safari Ajax issues

Friday, September 21st, 2012

Pete Forde pointed me to an interesting discussion of an iOS 6 Safari bug that affects Ajax calls.

I later came across a discussion of a different bug, also affecting Ajax calls.

I know it’s only day one for iOS6 and the odd bug is expected, however I find it jarring that with Apple’s strong advocacy of HTML5 and JavaScript to create interactive web apps, these issues could creep into Safari without someone considering the implications or finding the issue before release.

h1

The Two Trajectories of Device Convergence

Monday, April 12th, 2010

The flap over Apple’s recent iPhone OS announcements has caused me to step back from the fray and I think from a distance some clarity has emerged.

I, like much of the world, see current phone and tablet devices as an evolution of the general purpose computer getting smaller and more focused. We project on them the same expectations of openness and freedom that we’ve come to expect from computers and laptops. Standardized interchangeable chargers and cables, replaceable batteries, users and developers have administrative control over the OS, development and distribution is completely within user and developer control. Connectivity and expansion are standardized and open.

Apple sees the iPad and iPhone as an evolution of the electronic telecom or entertainment device getting bigger and more general-purpose. They project on them the same expectations of vendor lockin and control that they’ve come to expect from mobile devices, audio players, and video games. Proprietary chargers and cables, no user-serviceable parts or batteries, users have no control over the OS, development and distribution is tightly controlled by the vendor. Connectivity and expansion are proprietary and controlled.

As the two fronts of this battle advance on each other, Apple is firing their salvos from their highly fortified appliance battlements below, while other players are approaching this space from the open skies above, bringing open general purpose computing to the personal device space.

It remains to be seen whether this is a battle that will vanquish one side or the other, or whether the two approaches can carve out similar but separate markets, but it’s clear that they are different approaches and that understanding is key when choosing which side you want to follow (or for that matter, lead, which is a possibility on one side of the equation and not the other).

h1

Apple pulls the Van Pelt maneuver on developers

Thursday, April 8th, 2010

Daring Fireball reports that Apple has changed their iPhone Developer Agreement to exclude applications that aren’t written with purely native C/C++ or Objective-C code or HTML/Javascript/CSS and webkit.

If it proves to be true, this move can only sound the death knell for a number of projects:

and possibly

I’ve personally put a few weeks of effort into a Rhomobile project that is now a big pile of wasted money unless Apple changes this requirement.

I can only imagine how completely gobsmacked the developers and funders of these projects will be to know that Apple has decided to take away their football. We’re talking literally millions of dollars of value suddenly disappearing on Apple’s whim.

I will be watching this issue with interest. These projects were only just starting to make iPhone/iPad development interesting by providing higher levels of programming abstraction.

h1

How to move your home phone to VOIP

Wednesday, September 30th, 2009

I’ve been a land-line hold-out for a long time. I’ve resisted the marketing campaigns of my phone provider (Bell Canada), my ISP (Rogers), and my long distance provider (Primus), and have continued to pay $40 per month for a wired land line with voicemail, forwarding, call waiting, and unlimited local calls, and an additional (average) $35 per month for long distance services to the UK at about 6 cents per minute, a great rate considering I paid at least $1.00 per minute when I first brought my English wife to Canada 23 years ago.

This month I finally changed over to Voice-Over-IP. I chose to go with Unlimitel, a Canadian voip provider with whom I was able to keep my existing phone number.

Unlimitel charges $3.50 per month for a DID – a Direct Inward Dialing number. That means a specific phone number is assigned to your provider who then provides a system for you to connect to it to receive and send calls. You connect to the provider over the internet.

You can connect to most providers via SIP (Session Initiation Protocol) or IAX (Inter-Asterisk eXchange). Generally, SIP is used by software or equipment that acts as a single end point, such as a VOIP handset or a soft-phone such as the free X-Lite software, whereas IAX is used by PBX software such as Asterisk or FreeSwitch, which will act as a switchboard to manage multiple extensions, Interactive Voice Response menus, voicemail, etc.

I decided to connect to Unlimitel via IAX, so I can run a simple PBX at home and play with some of the features. I chose to run SwitchVox Free Edition from Digium, because it’s a free, full-featured yet turnkey solution. I’m running it on a used Dell Optiplex box I got for $89 at Factory Direct.

In order to connect my existing home analog phones to the SwitchVox software PBX, I bought a Linksys PAP2T 2-line analog telephone adapter for $51 from Canada Computers.

After ordering the DID from Unlimitel by asking them to transfer my existing line to them, I received the login info from them and entered it into my SwitchVox software. When the line was transferred over, I disconnected my home phone wiring from the incoming wires, and plugged Line 1 of my Linksys PAP2T into one of my home jacks to connect it to all the rest of the phones in my house. I configured it as Extension 100 and told SwitchVox to route all incoming calls to that extension.

I have also set up other extensions for myself and my family members, so we can connect via our computers to make and receive phone calls. I have not yet routed incoming calls to IVR to give directory access by last name etc, but I can do that if I like.

I actually have another DID in addition to my original home number so I can play with things. I can route incoming and outgoing calls based on rules that I define. I’m even considering buying a cheap UK DID so my wife’s family can call us on a local number and avoid long distance charges.

I have 5 channels, meaning I can have up to 5 incoming/outgoing calls at any one time, including callers to a conference bridge on my PBX. Local calls are now charged at 1.1 cents per minute. UK calls are 3 cents per minute. I anticipate my total monthly charge to be less than $20, down from $75 before.

Unlimitel provides Basic 911 service, which means that I will be able to dial 911, but they will not automatically get my location (as they would with the usual E-911 service) – I will have to provide it when they answer. I have mitigated this somewhat by making a rule that says that whenever 911 is dialed from the extensions that are connected to analog handsets in my house, the Caller ID that is transmitted is my Postal Code and street number and name.

To summarize, I now pay about a quarter what I was paying before, I have up to 15 extensions, 5 channels, voicemail, call waiting, customizable Caller ID based on outbound call rules, detailed call logging and daily and monthly billing summaries. My intial hardware outlay was about the same as 1.5 months of my original bill and it runs on my existing broadband connection.

What the heck is not to like?

Tell me your story. How much have you saved, how much flexibility have you gained, what lessons have you learned?

h1