Ubuntu on an external hard drive - It's a megablast!

Wow, it's just such a thrill to have ubuntu sitting here on a portable drive!!

I just hooked my external drive to the IBM Thinkpad laptop I use for my job.

True, for some reason, I had to install the drive first under Windows XP (I'd expected the BIOS to pick it up right away, but it didnt), but after that, I could boot Ubuntu right away, just like I did on my desktop upstairs.

(Some people might regard it as a bit of a makeshift solution, but I like having the internal hard drive as the default boot device. During startup, I just hit whatever function key BIOS happens to accept to pop up a list of boot devices - F8 on my desktop, F12 on my laptop - and pick my external hard-drive if I want to boot Ubuntu. It makes sense, I really cannot be sure the external drive is hooked up, whereas It's safe to assume the interal hard drive's still there ;-).

So, Ubuntu has just awoken in this strange, new, compact body. From there, I used the graphical "networking" tool to set up my wireless adapter (built-in in my thinkpad, my desktop uses and old fashioned umbilical chord) and lo-and-behold: here I am, sitting in the living downstairs, with my laptop posting this.

I admit, I did reboot once after tinkering with the wireless adapter configuration. It just would not work at once, and the rebooting reflex is kinda engraved in my backbone, being a Windows user since the 3.11 version.

Thanks again Dave (a.k.a. DaBruGo), I really, really am so much obliged to you for posting this solution of yours!! I'll just repeat it here: those that want to have a dual boot solution for ubuntu and they want ubuntu to sit on an external drive, look no further! This thread in the ubuntu forums contains indispensible valuable information. Right, people are even posting replies that tell you how to do this with an USB flash memory stick, imagine that!

Linux for Human Beings - AND Roland too!

I've been busy exploring open source software for some years now.

I think it was Erik, a friend of mine, that first told me and explained what open source software was about. Then, he was right in the middle of developing the backup tool Abakt and we then discussed XML as a format for storing configuration data. He used (and still uses, I think) an open source SAX parser, I can't remember which one exactly.

Anyway, he introduced me to MySQL, OpenOffice, Mozilla - all these different kinds of products. My initial reaction was one of disbelief: How in the world could this software be free of charge? Put another way, how can it be free of charge, and be of good quality too? What's the catch?

I then briefly explored MySQL (Still a 4.0 I think?) and at the time, I thought I knew what the catch was: MySQL was not that good. At least, not compared to what I was used too (Oracle, MSSQL, Interbase even). It didn't have a lot of things that I used (and still use) a lot. Declarative Check Constraints, Views, Stored Procedures. Besides, it had all these strange features such as really eerie NULL handling. On top of that, you had to configure all that InnoDB stuff and go to all kinds of trouble to enable transactions and foreing keys.

Mozilla was sort of the same story. At the time, I didn't have built-in XSLT support (like IE does), and although it claimed it implemented only standard DOM to perform DHTML, I really was unimpressed as the interface IE offered seemed so much richer and easier to me.

Of course, I just consumed the products, briefly glancing over the features. And my perpective was quite narrow towards open source software, almost exlusively focussed on the costs, and not on the inherent quality or the community process. Some of the things I really did dig then was the Java programming language. I thought that was open source too, because it was free - of cost that is. Also, I had a tendency to compare all this stuff to the equivalents I was familiar with - being mostly Microsoft products, or commercially available software running on the Windows platform.

Gradually, my attitude changed. A product that definitely made a difference in this respect was PHP. At the time, I was using three different technologies to develop web applications: Oracle Mod_plsql, Java servlet pages and Microsoft Active Server Pages. To me, the problem with all these technologies was their lack of portability. I want to develop applications and let my customers decide what underlying infrastructure (Web server, database server, and of course: Operating System) is best for them. With these three, this is an utopy.

I stumbled into PHP again via Erik. Also, I noticed that funny php extension on all these webpages I was browsing, and I made a note I had to check it out sometime, ifnot to know why not to use it. Finally I downloaded it, and read the installation notes. Then, I set up in both Apache and Microsoft Internet Information Server - both up and running within 10 minutes. This was amazing! This was a product that I legally did not pay for, it had clear documentation AND it worked exactly as described on two very different webservers (at least, marketing-wise). Better still than not paying for it, I could even use PHP to build applications on a commercial basis!

(Actually, I was using Apache quite alot then too, but listen up, hear this and try not to laugh at me: I did't know Apache was open source! See, I was familiar with what was (and still is) called "the Oracle HTTP Server _{powered by apache}" wich is about 98% apache that's shipped with the oracle database as of version 8 (among that 2% is that mod_plsql I've blogged about here an there). Mind you, they havent even succeeded in using a Apache 2 based product, it's still Apache 1.3 there. tsk..).

From then on, I looked at open source software from a different point of view. I started using MySQL. The 4.1 is of course objectively a big improvement over the 4.0 version I met initially, but of course, my change of attitude has made all the difference. And my confidence in open source software has got another boost by getting involved (in my own modest way) in the MySQL forums, and in writing the odd blog or article.

However, all this time, I have been glued to the Windows operating system. I've been wanting to convert there too, but there's something about the nature of an OS that doesn't make me change. I mean, I don't like particularly configuring stuff, making my computer and OS see my hardware, solving IRQ conflicts, man you name it.

But now, something has come to my attention that might change all that. It's the ubuntu Linux distribution. Ubuntu advertises itself as "Linux for human beings". They do not explicitly say to what kind of a user base the other Linuxes are targeted, but for now, I'll gladly assume the mean "human beings that do like to configure, make the OS see my hardware and solving IRQ conflicts".

People might be wondering why I'm picking it up so late, but the result's what matters to me. (In case anyone cares, I picked it up through Morgan Tocker's blog wich I picked up through PlanetMySQL: thanks Morgan, thanks PlanetMySQL!).

Actually, I'm writing this blog from my freshly installed Ubuntu. It ships with Mozilla Firefox. I'm really impressed with how it looks, and how fast it works!

I'm still runnin it dual boot with my Windows XP OS, and I'll probably hang on to that too (actually, I haven't got much of a choice there as I need it to serve most of my customers). I had some installation troubles of course: I wanted to run it from an external hard drive, in part because I was a bit afraid the installation might corrupt my windows disk.


Anyway, power to the community, here as well: on the ubuntu forums, it took some 10 hours for me to get the issue solved....using a pointer to an existing thread (that I would've found if I had searched better). So, thanks for the pointer aysiu. And of course, many thanks, and a big thumbs up for my hero of the day: DaBruGo, who has so kindly explained to us all how to set ubuntu up using an external hard drive.

No helpdesk can beat this kind of support - it's just amazing.

Reporting for the Masses: Eclipse/BIRT, Apache Tomcat and MySQL

I got it working...

Eclipse

About two monts or so ago, my attention was caught by that remarkable open source product, Eclipse. (Maybe a lot of people are saying "..duh!.." out loud right now, but hey, I can't hear you)

I wrote the MySQL connector/J example accompanying the article in the Call Level Interface section of Andrew Gilfrin's site www.mysqldevelopment.com, using eclipse, very much to my satisfaction.

When I started to write the example, I was using sun's netbeans, but it was simply too slow. Especially as my source grew bigger (I'm talking only several hundreds of lines here, shouldn't be a big deal) the editor was just lagging and freezing up to a level that simply wasn't acceptable to me. Clearly, I needed something else and eclipse happened to be the first thing I thought of using instead.

BIRT

Browsing through some of the other, non-core, eclipse projects I noticed the BIRT project. And, quite shortly after becoming aware of BIRT's existence, an article was published on the MySQL website: Using BIRT To Report On Bugzilla in MySQL. I knew then that I had to check it out for sure.

For those that never heard about it, BIRT stands for: "Business Intelligence and Reporting Tools". BIRT lets you extract and present data stored in various types of data sources (such as relational databases, but also comma seperated files, and some others). Such a presentation, or report, can be a powerful tool for the upper and middle management to analyze, control and plan their business. (You can take a look at a DEMO flash movie here)

Of course, reports can have a much more modest purposes too. For example, an order entry system could be required to print out an entire order including order lines so it can be printed out and sent to the client as an invoice.

Now, I have worked with some closed source Business Intelligence and Reporting Tools, and I think it's fair to say that at this moment, BIRT is best characterized as a reporting tool rather than a Business Intelligence tool.

True Business Intelligence tools offer all kinds of instruments to analyze data, quite often in a dynamic, flexible, ad hoc manner. At this moment, BIRT is not such a tool. Those that are wondering what tools qualify as true Business Intelligence tools: I'm thinking of OLAP and Datamining tools such Cognos Powerplay, Oracle Discoverer, Microsoft Analysis Services.

BIRT let's you define sets of data (for example, from a SQL query) and then render that data into a mainly static layout. There is quite some choice of course: Data maybe rendered as simply plaintext, or in a complex tabular grouped format or even as a colourful graph or diagram. It should be possible to add some user interaction too, because you can put hyperlinks and javascript into the report (haven't tried that yet though).

There is also limited support for some analytics. The data drawn from the query can be grouped in various levels. Aggregate functions can be independantly applied to these groupings, showing both detail rows as well as the aggregates (think of a grand total for all ordered items on the invoice).

Actually, I think BIRT bears quite some resemblance to Microsoft Reporting Services. This is especially true when designing Reports. The Eclipse IDE, in which you design BIRT reports, matches Microsoft Visual Studio (which is used to create MS Reporting Services Reports). Both reporting platforms are extensible. Of course, BIRT is so by definition, as it is open source; but the methods for extending Reporting Services are documented and the specification is available. The language in which reports are defined is an XML format for both platforms .

(BTW - the XML-format this is something I like very much in both these products, It offers one all kinds of opportunities to manipulate report sources. For example, because this is all XML, it shouldn't be too hard to translate reports sources from one platform to the other using simple XSLT transformations. Another thing you could do is build your own implementation, generating source code in say, PHP. Again, all you'd need are XSLT transformations).

Of course, there are also some differences between these products. Here are some obvious ones which do not directly impact the functionality of the report:

• BIRT is open source under the Eclipse Public License - Reporting Services is closed proprietary source software, subject to its own product license


• BIRT is based on java technology - Reporting Services is built on .NET technology


• BIRT has jdbc database connectivity - Reporting Services has ODBC and ADO .NET connectivity


• Syntactically, BIRT expressions are Javascript expression - Reporting Services expression are Visual Basic .NET expressions


• BIRT does *NOT* have crosstabs as of yet - Reporting Services has of course


• BIRT datasources are defined on the report level - Reporting Services datasources are defined on the Report Server and/or Project level (wich I think is much more convenient than in BIRT)


• A lot of output formats are available for Reporting Services - BIRT can only output HTML or PDF (I'm really missing an XML output format)

There are significant differences in Architecture:

• Although you can use Microsoft Reporting services on various databases (ODBC, ADO) you really need a Microsoft SQL Server instance, always! The report data is processed and cached in a SQL Server database, no matter the actual data source. The actual report is run from the cached data. BIRT is dependant only upon a runtime library. No datastore is required to cache data.


• Reporting Services is integrated into Microsoft SQL Server and Microsoft Internet Information Services (webserver). So, there, deployment is really easy. BIRT is just a reporting tool. You'll have to hook it up to a Application Server yourself - one capable of running java servlets. Deployment is then done manually, copying the source files to the application server

Tomcat

Of course, for it to be a serious solution, BIRT really needs to be hooked up to an application server. You can't expect your users to open eclipse to preview their report.

Luckily, the BIRT manual explains exactly how you can run your reports from your Apache Tomcat application server. Some time ago, I installed one of those too, but I never got round to playing around with it. This was my first chance to do that too.

I expected it to be big deal - it's not. In a minimal setup, you need to create a BIRT directory under tomcat's webapps directory and copy one of BIRT's components, the report viewer to that directory. It's a walk in the park, I had it running withing minutes, having read the relevant chapter in the BIRT manual.

MySQL - Sakila

I used the sakila sample database (download here) to do some reporting.

I devised two little queries that I think would be useful had I started a career as a DVD rental shop owner: Rentals per store through time, and Rentals per category.

Here's Rentals per store:

select     DATE_FORMAT(last_day(r.rent_date),'%Y') as rent_year
,          DATE_FORMAT(last_day(r.rent_date),'%c') as rent_month_number
,          DATE_FORMAT(last_day(r.rent_date),'%b') as rent_month_abbreviation
,          i.store_id                              as store
,          count(*)                                as count_rentals
from       rental r
inner join inventory i
on         r.inventory_id = i.inventory_id
group  by  rent_year
,          rent_month_number
,          store

I rendered it as bar graph:




Here's Rentals per category:

select     c.name               category
,          count(*)
from       rental    r
inner join inventory i
on         r.inventory_id = i.inventory_id 
inner join film      f
on         i.film_id      = f.film_id
left join  category  c
on         f.category_id  = c.category_id
group by   category

I rendered it as a Piechart:




Quite interesting results, actually. I'm not experienced in DVD rentals, but this does look like quite a homogenous set of data to me. Anyway, I just heard Mike Hillyer is going to discuss the sakila database at the MySQL user conference in april 2006. We'll probably hear exactly how he generated this set.

I just want to add that it took me just a couple of hours to explore BIRT and the underlying XML format (dont worry, designing is all GUI) enough to come up with these. Apart from these graphs, my first report also includes a table of rentals per store with subtotals for several grouping levels. I skipped the tutorials, I just watched the Demo Flash movie (link in the top). My conclusion is that eclipse/BIRT is intuitive enough to build serious reports.

Also, I'm impressed with the performance running the reports. I'm running eclipse/birt, mysql 5.0.17 and Tomcat all on my 1700 Mhz/ 1G Ram Thinkpad, and the graphs appear almost instantly (Yup, disabled my browser cache - it really is fast). We are looking at 16088 rows of rental data - I'm impressed this speed is possible even without the data cache such a used by Microsft Reporting services.

Final thoughts

You can put together a Reporting Platform based exclusively on open source products. It's quite easy to set it all up. BIRT may not be as advanced as some comercial products, but because no financial investments are involved purchasing software, theres no risk in trying. In fact, I think that in a lot of cases, the functionality provided by BIRT is quite sufficient to serve most reporting requirements.

Although BIRT may lack some more advanced features such as crosstabs, most of these will probably be added within an overseeable amount of time. Take a look at the BIRT project plan to see what is going on. I'm quite convinced we won't have to wait too long before we see a crosstab inside BIRT

'Doing' MS Access

I've just come home visiting one of my Inter Access co-workers.

Barbara is a very capable Oracle Developer, Information Analyst and BI Consultant. She enjoys the craftmanship so much that she still finds time to help out friends managing their little IT solutions.

For example, one of her friends owns a sports clothing store. She built them an order entry system in that popular little Database by Microsoft known as Access.

As you might expect, the system has grown over time, and has had too little administrative attention. Now, it's about 30Mb of data. This may not seem much, until you try to run a query. Size really is a relative measure.

What's worse, the number of users has grown too. Again, it may not seem much, but five users that are simultaneously working with one Access database is really about the limit. At least, it seems to be the case here.

She decided that it might be a good idea to try porting the application's backend to MySQL, and she asked me if I had any experience in porting such an application. 'Yeah..' I said, lying through my teeth of course, '..sure I have. I do that stuff all the time.'. 'Lyer..', she said, '..but do you think you can give me a hand?'.

Of course, I said yes. I really never did this, although I was aware of the builtin functionalities of the migration toolkit to do the legwork. And I'm always eager to goahead and try these kinds of things, especially since this is sort of a real, mission-critical application. (Just because we're doing this in our spare time, and that this concerns only a small sportsclothes shop does not change the fact that this application is mission-critical and that the migration should be planned and executed carefully).

Luckily, the application was already built in two separate chunks. The front end is an Access application: It contains some stored Queries ('Views'), some Forms and some VB code. The backend is also an access database. This one is worth 35 Mb of base tables. The front end communicates with the backend through a so-called 'linked table'. (For the MySQL techies: this offers the same functionality as the FEDERATED storage engine in MySQL)

In our first endeavours, we used the MySQL Migration Toolkit (download: here) to reverse engineer the database schema and to load the data into MySQL. This really went remarkebly well. We used default options in most of the cases, and I can only conclude that this is a very good way to import an access database into MySQL.

Then, we used the MySQL ODBC driver to link the existing Ms Access front end to the MySQL backend database. The immediate first impression regarding this solution is that query performance is much better than in the old situation, so that's a thumbs up for MySQL. And we didn't even do any tuning sofar.

A bit of a bummer is that we did encounter problems inserting data into tables with socalled AutoNumber columns (such a column is very much like an auto_increment in mysql).
In the fist place, the migration toolkit did NOT automatically map those to mysql auto_increment columns. This was fixed soon enough though. We just added the auto_increment attribute ourselves.

A more serious problem was that Access did not synchronize well upon insertion of a new row. The row would be put into the database, but immediately after insertion, the access front end would show the row as #deleted#. Refreshing the view on the data from inside Access would show the row, proving the insert did indeed take place, and proving access is capable of retrieving the newly inserted row.

We did some searching on the MySQL Access Migration forum but alas, to no avail. Lucky for us, a bit of browsing around brought us to the -unbeatable - reference manual. The "deleted" problem is described there exactly, and a workaround is ginven there too.

(By the way, the manual has an entire section devoted to using the MySQL ODBC driver in combination with Microsoft Access)

We had to change just two things. First, we had to enable the Return matching rows option in the MyODBC configuration dialog. This is available from the ODBC Driver manager. Then, we had to add a timestamp column to the tables:

    alter table table1
    add dummy timstamp
    default current_timestamp
    on update current_timestamp

...and that fixed the problem.

A point worth mentioning is that it really doesn't matter how you call that column. You don't even need to use it in your application. It just needs to be there, that's all.

Unfortunately, we are both at a loss as to explain why (and how) this works. We even did some ODBC tracing to see what happened. We couldn't find out how it works, but we could see something else that's interesting. After the INSERT, Access performs a SELECT, presumably to synchronize it's view on the data. Judging by the trace, that SELECT statement does NOT use the timestamp column in the WHEREclause.

Now, there still have to be some tests, but at least, we're very confident that this port to MySQL will be quite successful and without siginificant effort (thanks to the Migration toolkit)

Barbara promised she'd let me know if there's any progress or any troubles, so I will report them back in this blog, '...for the world to knwow...'

Some MySQL stuff I have been doing during my Holidays

During my holiday, I alo spent some time with MySQL.

I've been working on an article series for mysqldevelopment.com. This time, Andrew, Markus and yours truly have teamed up to cover (or at least, introduce) the interfaces you use to access MySQL from the (end-user) application. So far, we've come up with an introduction and an article describing Connector/J, the MySQL JDBC interface.

Given the amount of available API's/Connectors, it seems we've got our work cut out for us. But hey, dear reader, if you feel you want to contribute, don't hesitate and drop us a line.

I've been working on MySQL UDF's too. What a great and powerful feature! As I described in a previous entry (This is too wicked), I'm building a set of UDF's to port between Oracle and MySQL (not neccessarily in that direction)

To me, the most amazing thing sofar has been the discovery that MySQL already implements so much Oracle specific functions. I did notice a few before, but It really became clear to me once I started making an alphabetical list of functions (still busy with it). Or rather, since I started mapping Oracle's alphabetical list of functions from the Oracle SQL Reference to the MySQL functions.

I sure did learn a lot since I wrote my first UDF (Using an UDF to Raise Errors from inside MySQL Procedures and/or Triggers). I finally got rid of that pesky #include "winsock.h", and I developed a couple of macros, typedefs and such that make up a nice little reusable toolkit for creating UDF's (well, at least: I think so). I'll think I'll describe it in the blog in the near future.

Having a bit of a Holiday

I've been enjoying a bit of a holiday lately. Nothing much, just two weeks off from my regular job.

Frankly, not having to go to work, celebrate "Sinterklaas" with my family, reading a novel (Sinterklaas gave me the Dutch translation of Michel Houellebecq's last novel, "La possibilité d’une île", Dank U Sinterklaasje! A certain iPOD Nano device also arrived on the 5th of december - what timing, I love it!) and making a walk on the beach (Yup! we saw a seal, a wild one that is, in Katwijk aan Zee. It looked as if it was making an attempt to swim into the Rhine River, in fact it was at the foot of the dunes, well on the shore).

This is TOO Wicked!

Wow, this really is too cool to believe!

I'm one of the lucky three grand prize winners in the You Make MySQL 5.0 Rock! contest. I bet that sounds cheesy, but I really never imagined I would be one of those lucky three. It goes without saying I'm very very pleased indeed! Brilliant!

I just want to say: Thank You! to the MySQL team, it's a great honour. And hey, Arjen, I didn't mind sucking up at all ;-), as a matter of fact, I can't wait until the next contest.

Well, now that I'm at it, I want to speak up in favour of a couple of MySQL community personalities that I respect and think highly of. To a large extent, my motivation to occupy myself with MySQL has to do with being inspired reading their posts on various MySQL forums, their blogs and in an isolated case, their book. So, here we go:

Andrew Gilfrin

Andrew barely needs introduction: without doubt, one of the people most present on various of the MySQL forums, and of course, the man behind www.mysqldevelopment.com. But hey, this guy is so busy, isn't there anyone out there that can help out, and contribute to the site? Come take a look and help to make it even better still!

Markus Popp

The brains behind that wonderful and unique initiative, www.db4free.net, making open source database product accessible to literally everyone (with a connection and an email adress, that is). Congrats to you, dude!

Jay Pipes

Apart from being a very helpful and knowledgable MySQL forum member, Jay's also one of the Authors of Pro MySQL. I think that book's just great stuff, taking the principle perspective of MySQL as a professional database product.

Felix Geerinckx

I've seldom seen a forum member that was so accurate and quick in answering forum questions. And that's exactly what Felix does. I bet he's the modest type too, because unlike myself, you can never catch him plugging his own pages into the forum. Felix, where's your blog?

Beat Vontobel

Well, who could've missed that, a MySQL Stored Procedure debugger. Amazing! Congratulations Beat! The day will come that somebody will aggregate your blogs in a Internet Explorer friendly page ;D)

Giuseppe Maxia

Finally, someone senior enough has taken it upon himself to create some structure and organize the various MySQL general purpose routines that are scattered around. So, come on, check out the MySQL General Purpose Stored Routines Library and post your comments and contributions.

Arjen Lentz

Maybe I'm not the best judge, but it is my impression that Arjen is one of the major reasons why this community is thriving, and literally alive and kicking. For one, the PlanetMySQL initiative is really great. Just one page to see all those interesting items and blogs. This is daily digest for me

So there you have it. You may think it's corny, or you may think it's a matter of blunt, bad taste advertising...so be it. I benefit a lot from these people, and to me, it just seems the proper thing to publicly pay them tribute. Props to all of you!

Meanwhile, work has to be done too.

For one, Markus and Andrew and myself have been, and still are, quite busy writing an series of articles on connecting to MySQL from an application programming language for mysqldevelopment.com. You can expect to see those soon over there.

Second, I'm getting kinda hooked writing UDF's and delving into the associated materials. The C language (after about 8 years, it's coming all back to me), some of the MySQL source (up till now, only the headers). I took it upon me to write a library to implement most of the Oracle functions. Why? Because I might need it for a migration, and because it's fun!
I decided to make a plan to do it too, although I can't dislose too much about that right now. Let me just say I'm implementing them in alphabetical order (about to do DECODE() now), until there are no more entries on the list ;-).

Bye and greetings from one happy Roland.