Andrew Channels Dexter Pinion

Wherein I write some stuff that you may like to read. Or not, its up to you really.

June 30, 2003

PHP News

So, I've been playing with PHP at home, and I noticed today that the PHP 5 beta has been announced.

Looking over the list of changes its remarkable how Java-like the new features are. One of the attractions of PHP is its simplicity, does it really need private and protected members, never mind interfaces? Is this the reason why you can now give class type hints in what is supposed to be a dynamically bound language?

Posted by Andy Todd at 01:22 PM | Comments (2)

June 27, 2003

So True

I don't normally do the link-quote blog thing here, but I was perusing the Guardian's online section at lunchtime and Jack Schofield's piece entitled IT matters leapt out at me. In it Jack hits the nail on the head.

In particular I was nodding quite vigourously where he says that the most common IT fallacy is that "... you can buy a solution, rather than a tool that can help you create a solution." Something that is so, so true, especially in the ERP market, to name but one.

Posted by Andy Todd at 01:23 PM | Comments (1)

Table Modifications in DB2

The schema changes keep piling up in my inbox and with each one I discover that I am unable to do something that would be quite trivial in another database. This week I have uncovered;

The second point I will concede, although its interesting that this is possible in databases which haven't always enforced referential integrity (I'm looking at you Oracle and MySQL).

The first point is nonsense though. Why stop me changing a column to make its definition less restrictive? There is no technical reason for this unlike, say, the difficulties inherent in changing a column's data type. The only conclusion I can draw is that in DB2 the guiding principle is that you can't change columns - full stop.

Its their database, so I can't really argue. What I do know is that its making my life a little tricky. Its hard enough being agile and writing migration scripts for a data model. Its doubly hard when you can't modify anything in place.

Which leads me to an observation, is it just database vendors who make their manuals suitably vague? They are always full of "Product x will do a,b and c" but they never say what they won't do.

I suspect it has something to do with product liability - you know, if we don't mention it they can't sue us for not providing it - but I can't prove it. All I do know is that its as frustrating as hell sometimes when you are searching for a way to implement a feature only to find out that it doesn't exist.

Posted by Andy Todd at 01:09 PM | Comments (1)

June 17, 2003

Iterators and Databases

Inspired by David Mertz' latest Charming Python article I've been playing with generators.

An area where they look very applicable to me is in fetching large result sets from a database. Until now, I've always executed the query and then performed a for row in cursor.fetchall() to get and then process all of the results.

This practically means that all of the rows are read into memory before they are processed. If your query is a large one this can use an awful lot of memory.

So, until all of the Python database modules are updated to use generators here is a simple recipe to use;

def genResults(db, sql):
   while row:
      yield row

So in your code you can just include for x in genResults(db, "SELECT * FROM table") and carry on as normal.

Beware though, those database modules which only allow one cursor per connection (in which case pass in a cursor not a connection) but otherwise a thing of beauty.

Posted by Andy Todd at 01:14 PM | Comments (5)

June 11, 2003

Changing Tables in DB2

Further investigation reveals that you can't change the name or data type of columns within DB2 either. With the exception of increasing the length of VARCHAR columns that is.

Which means that when you want to change a column definition you have to;

  1. Create a temporary table
  2. Copy the data from your table to the temporary table
  3. Drop and re-create the table (including any foreign keys that point to it)
  4. Copy the data back from the temporary table
  5. Drop the temporary table

So far this week my project team have generated thirty four changes to the data model - well, we are very early in the development cycle - and I haven't looked in my in box yet this morning.

I was planning on producing weekly deltas allowing the team to upgrade their local copies of the database. At this rate I don't think its possible to do that, as it will take me longer than a week to produce a script to perform all of the changes.

As the model matures it should be possible to do it but until then I will just have to produce whole sets of DDL scripts and get people to drop and re-create their databases on a regular basis.

Posted by Andy Todd at 09:51 AM | Comments (0)

June 09, 2003

Dropping Columns in DB2

I don't want to believe it, but according to this post at dBforums you can't drop a column from a table on DB2 UDB (the version which runs on Windows, AIX and Linux).

My reading of the reference manual seems to confirm this as well.

I fear I am coming across as a bit of a whinger, put it down to Monday-itis. But come on, we need this, especially on projects where we strive to be agile.

Posted by Andy Todd at 05:42 PM | Comments (6)

Oracle v SQL Server, Part 9

Blimey. There is, as far as I can deduce, no way of easily converting a number into a date in SQL Server. Oracle has to_date but the cupboard is bare in Microsoft's finest (and I presume in Sybase as well).

For the record, my colleague had a numerical representation of a date in an integer column (e.g. 20010410 for the 10th of April, 2001) but wanted to perform date comparisons on it. Thanks to the power of the interweb we came to a solution via Experts Exchange. Our solution was slightly different and was along the lines of;

SELECT convert(datetime,
       substring(cast(IntDateEntered as char(10)),7,2) + '/' +
       substring(cast(IntDateEntered as char(10)),5,2) + '/' +
       left(cast(IntDateEntered as char(10)),4)
       ,103) DtDateEntered
FROM   dbo.TestTable

Posted by Andy Todd at 02:09 PM | Comments (6)

June 04, 2003

Playing Vim Catchup

A useful tip that I didn't know until today is how to discover what the current value of various options
are. According to the manual you just type;

:set {option}?

Where {option} is the name of your option. So, to find out the current folding method we use;

:set foldmethod?

Posted by Andy Todd at 02:31 PM | Comments (3)

Python Folding in VIM

I've been playing with the folding for python plugin in Vim again. I'm now officially a fan.

I initially installed the plugin when it was first released but for some reason didn't get on with it. Its probably because all of my program files where quite small and easy to view on screen.

Well, now I've got a couple of monsters and whilst the taglist is useful, in combination with folding my copy of Vim is now practically an IDE.

I took a while figuring out how to switch folding on or off though. Its not obvious from the manual, and this method may not be the best so feel free to correct me in the comments, but with the Python fold script installed folding is by default on.

To switch it off I use;

:set foldmethod=syntax.

Then to switch folding back on I use;

:set foldmethod=expr


Posted by Andy Todd at 02:21 PM | Comments (4)