Friday, 25 June 2010

this week was spent implementing charts for the query results. As mentioned in my latest blog post I implemented rendering charts for the query results in two formats.

The branch that I am working on is available here. Unfortunately charts are broken there, even they work on my local web server. I have yet to find what is wrong.

This week was a very short week for us lithuanians. On Thursday and Friday we have celebrated mid-summer which is a national holiday here. I will be taking the next week off because I will attend at social experiment.

I will be back to business the week after. Then I will be getting ready for JavaScript chart implementation. Can't wait! See you then!

Tuesday, 22 June 2010

while the code for generating charts for the status page and the profiling page was more or less trivial, code for the query results will be more complicated. This is because of the dynamic data structure that query results can have. You can get any number of rows and columns as a result. Also the data in the result is composite - there are strings, numbers, dates, etc.. Not everything is suitable for one chart or another.

I decided to introduce two (for now) query result formats that must be followed when you want to put your data to the charts. If this format is followed code will recognise that and will render a chart which suits best.

For now I will be talking about two types of charts: simple bar charts and stacked bar charts. For the simple bar chart we need to provide X and Y axis value pairs, something in a lines of this:

Month

Sum

2009-11

225

2009-12

1157

2010-01

569

2010-02

282

You could imagine now that the chart for this table would look like this:

Now this is easy. What if we know that the total amount on every month is composed of different parts and we would like to see them in the chart as well. For this we will have to add another column which specifies which component the data belongs to. This breaks the monolithic bars into the bars composed of series of data.

Month

Sum

Operator

2009-11

129

Bite

2009-11

40

Omnitel

2009-11

56

Tele2

2009-12

176

Bite

2009-12

707

Omnitel

2009-12

274

Tele2

2010-01

422

Bite

2010-01

108

Omnitel

2010-01

39

Tele2

2010-02

110

Bite

2010-02

131

Omnitel

2010-02

41

Tele2

For this data generated chart would look like this:

Note that for both charts labels for X and Y axis were taken from the column names. It is nice to have such details taken from the query result. However I would also imagine that some users would prefer to set details like these in the chart generation page. I will be adding some options there which will have default values but will give the ability to change them to whatever you like.

So If you would like to put your data to these gorgeous charts generated by pChart just write a MySQL query which puts your data to the formats described above and you are done. Any other chart types which are suitable for query results?

Sunday, 20 June 2010

I have thought for some time that it would be quite cool to have the internet anywhere you go using the GPRS network. All of us carry some mobile phone device. Not all of us know that almost every mobile phone can be connected to the PC and be used as a modem for GSM or even GPRS data transfer. Even better, mobile phone can be connected to the PC using Bluetooth - no unnecessary cables! When I saw that my carrier (Tele2) allows me to use 1 GB for 1 LT (~3.5 EUR) every month I decided to figure out how to put everything together.

The first thing I did was download mobile internet settings from my carriers web page and set the new connection as a default one in my mobile phone. Then I have found quite a good tutorial in the carriers home page. It basically deals with how to set up the dial-up connection on Windows XP using GPRS through a mobile phone. You can find it here (mirror). This is in lithuanian but has many pictures so it is quite easy to understand the general idea.

However there was a catch. I did follow every step from that document but with both my mobile phones I got the Error 31 when trying to connect. As I have discovered later this was due to not set GPRS APN setting in my mobile modem. To set it you will have to connect to your mobile phone modem using some terminal program. I have used puTTY.

First of all you have to know the COM port that your phone modem has been assigned to. You can do this by clicking twice on newly created dial-up conenction and then Properties.

The port that I want to use happens to be COM27. So fire up puTTY, select Serial as connection type, type in COM27 as serial line and click Open. Black screen will show up. To test the connection type in AT and press enter. If OK shows up you are good to go. Note that some mobile phones require a confirmation when someone is trying to access their modem through Bluetooth or USB.

The mighty command (I found it here) that you have to enter is: AT+CGDCONT=1,"IP","<YOUR GPRS APN>". I do not really know why does this do the trick, but it does. (: If you did everything right OK will show up. You can check if this information is saved by typing in AT+CGDCONT?. I have noticed that this information stays saved even between mobile reboots. So it seems that you only have to do this once.

Now close the puTTY window and give a few seconds for a mobile modem to close the connection. Then try to initiate dial-up connection from your PC and emerge into to the meadows of not so fast internet.

Now whenever I have my laptop in the wild with me and I need connectivity for one reason or another I just activate Bluetooth on my phone and that's it. I have tested this with two not so new and shiny mobile phones - Motorola E398 and E2. Both have worked but the connectivity was not very stable. There was about 40% of dropped packets when I was pinging to google.lt. Nevertheless it is better than nothing. (:

Saturday, 19 June 2010

there has been quite a lot of activity happening this week. First of all after some discussion I have scrapped the flash implementation of the charts. We have decided that the same chart functionality can be achieved using JS. And since JavaScript is still needed to load the data to the flash charts it does not really make much sense to have flash and JS.

This week I have implemented static image charts using pChart library. Before that I thought of implementing image charts by myself. However after stumbling upon (tm) pChart I have changed my mind. For now charts are working at the status and profiling pages. I have some code to generate charts from the query results, but I will push that and write a blog post completely on that a little bit later. These are still more or less prototypes. I do feel that it is better to try different approaches and be not afraid when deciding to go or not to go in some particular way.

Working with pChart library is quite nice. Everything is in one file hence the very easy installation. Charts look sleek and clean. The only drawback is the chart generation type. It feels that it takes extra one or two seconds to generate a chart.

During the next week I will be working on the charts for the query results. This is the most tricky part. Data is quite dynamic there and generally can be anything, thus I have to make sure to not get the user lost. More on that later...

Monday, 14 June 2010

I am finally done with travelling and all the progress that I made last week is the progress made today. As promised I implemented the Pie chart using OFC for the query statistics.

I have put the flash file which is needed for OFC into the js/ directory. The name of the directory does not say that this is right. I believe that it should be placed into flash/ directory, but since there is no such yet (it seems that there was no Flash in PMA until me) I will temporarily keep it there.

To embed SWF file into the page I did not use SWFObject script but rather plain and simple HTML tag. I remember when some time ago this did not work correctly throughout all the browsers, but today I have tested this on every major browser using the latest version and I didn't notice any errors.

For this week I will add Pie chart to the query profiling statistics and will start working on other types of charts using OFC.

The colours and layout are not yet final. I still have to find the best place to put javascript enabled and plain image charts.