Trent’s Blog

Well I received an email about the time I noticed the Blog Rush widget was no longer displaying on my website. It appears they have discontinued Blog Rush, so I have removed it from my blog. Fortunately for me I don’t believe it had much impact, though I have been wrong before. I wish the best of luck to the Blog Rush team in the future, and Thanks for your work!

This is part two of my quest to Incorporating Regex into ORM’s and Database Abstractions. My first steps to exploring the option to implement uniform regex support into my own database orm/abstraction is to compare the syntax of the more popular database systems. To do so I will build an sql query where possible to search the body of page contents for ‘bla bla bla..’ in a test database table.

MySQL


select * from pages where body REGEXP '(bla(\s)?)+'


Postgres


select * from pages where body ~ '(bla(\s)?)+'


SQL Server

SQL Server doesn’t appear to natively support regex without the help of CLR.


select * from pages where dbo.RegExMatch( body,'(bla(\s)?)+')


SQLite

SQLite similarly does not natively support Regular Expressions, however SQLite can be extended as well to do so:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named “regexp” is added at run-time, that function will be called in order to implement the REGEXP operator.

So SQLite recognizes REGEXP, but it is not implemented. It states that if we implement regexp() it can use it. So here might be the way to implement REGEXP using PHP. Implementing this should result in a syntax similar to MySQL’s syntax.

DB2

DB2 falls into the same category as SQL Server and SQLite by needing to create a function to implement Regular Expressions. Implementing this function can be done in C and appears to support pretty standard RegEx syntax.

Oracle

Oracle Database does reportedly support Regular Expressions. While I do not have an Oracle database to test on, it is reported to work like the following:


SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]’)


Informix

Informix seems to support regular expressions, however it does require that you have regexp1.0 installed to use them. Using this feature consists of calling a function rather than using an operator.

Sybase

I could not find any definite resources to indicate whether Sybase supports regular expressions or not. Perhaps if someone is familiar with this database they could shed some light on this.

After examining the different database engines a little more closely and their capabilities of handling Regular Expressions, I have found that support is surprisingly not very good from proprietary databases, however there are ways to implement it in most systems. However MySQL, Postgres, and Oracle(the exception to the proprietary databases) appear to support regex straight out of the box. This is a little discouraging, however if you could supply the necessary tools, libraries, functions, or procedures to enable regular expressions it would still be possible.

Almost on a daily basis I come across using regular expressions rather convenient. I first toyed with regular expressions in my Compiler Design classes during my undergrad studies, and little did I know they can be used for everyday things like submitting forms. I’ve even toyed with them to create my own json parser using an SQL like syntax. I think they’re a great way to perform strict validation and searching among other things. Searching. There is something I need to work on. Not just strings, but database searches for account numbers, phone numbers, etc. Not just ‘%some string%’. Regular Expressions within SQL is something I have blogged about before. I’ve come to find that Regular Expression support is far from constant and in some cases not even supported(SQLite).

Incase you’re curious what I’m talking about, some database engines allow you to write something like the following:


SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]’)


Sure, this process probably isn’t very efficient, but it can be very powerful.

More and more frameworks are moving towards ORM’s. You no longer have to write database specific code as the database layer will handle the specifics for you, but none that I am aware of will handle anything of this complexity and for obvious reason. Lets step back to a simpler solution that many of us deal with on a daily basis:


$("input[name$='letter']“)


jQuery somewhat creates it’s own language. It’s not xpath, its somewhat CSS selectors(ok, mostly css selectors), but there’s much more added to it’s own engine. Perhaps this approach could be a simple solution to the problem. Support a simplified subset or maybe my own mini language, and use the driver specific code to handle generating the database specific Regular Expressions. Here’s what such example may resemble in pseudo code:


$db->select('mytable')->where_regex("acct", "^\d{4}\-\d{8}$");


This code might search mytable for the acct that matches a pattern like “1234-12345678″. This isn’t terribly complex, however will suffice for the majority of everyday needs. Only supporting this level of complexity is more within the reach creating such if possible. This is my first post among others to come as this is mostly a brainstorm at this point. I openly welcome any pointers, advice, resources, or any other comments!

Firefox has released an alpha concept(Fennec) for mobile devices. It looks like it has a lot of potential, however it does look a little slow/buggy from the window in some instances. I do like the features it offers for a mobile browser:

• Full screen rendering
• Tabs
• Awesome bar type suggest(not sure on the exact name..)
• Bookmarks and easy navigational buttons
• Desktop emulators for Windows, OSX, and Linux!

Fennec Alpha Walkthrough from Madhava Enros on Vimeo.

The final days are approaching us when Ubuntu 8.10 is released into the wild. The question still remains: Will Ubuntu Deliver? You know what I’m talking about..

It seems strange but if you watch closely maybe major pieces are coming together before our very eyes. Linux is a big puzzle and the pieces are coming together. First we have Ubuntu Intrepid Ibex releasing this month. Gnome 2.24 has been released with big improvements in instant messaging and video/audio conferencing. Gnome is also bringing to the table a mobile platform, tabbed file browing, and improved screen resolution controls(which I think have been long overdue).

Many popular applications well known through the linux world are producing quality updates like Gimp, and are becoming increasingly comparable to high end software.

And the biggest reason I can see linux evolving and growing is that fact that I’m positive I wasn’t the only person to get another new comer to try linux yesterday!

Gimp 2.6 is here! We all can’t afford Photoshop and some of us love linux. Gimp has been there for us. Some of the changes include interface improvements including removing/merging toolbars, using more Utility windows which cleans up the task bar, and many improvements to the tools, brushes and under the hood. As you can see in the image below the only windows showing on the task bar are the actual image editing windows, no utility windows.