Smoking toooooo much PHP

Storing tree data in Mysql databases, is relatively common, however, all the existing documentation about doing this, makes the whole process rather complex.

If you google it, you will probably find the quite definitive answer at mysql.com, describing the classic� parent_id method, and the left/right numbering process. Both of these methods involve rather complex SQL to fetch and update the tree.

In seeking a better solutions for a tree that was infrequently updated, but frequently queried, I thought I'd try seeing if I could write a few stored procedures to simplify the process.

Our basic database structure looks like this:

CREATE TABLE _TREE_ (
    id int(11) NOT NULL auto_increment,
    parent_id   int(11)  NOT NULL DEFAULT 0,
    seqid int(11)  NOT NULL DEFAULT 0,
    depth int(11)  NOT NULL DEFAULT 0,
    leaf int(1)  NOT NULL DEFAULT 0,
    name varchar(128) default '',
    fullpath TEXT default '',
    PRIMARY KEY  (`id`),
    INDEX qlookup( parent_id , seqid , depth)
);

our key components are

• id (the nodes id)
  
• parent_id (the nodes parent - pretty clasic)
• name (the textual name of the node)
• seqid - the generated order item for the whole tree
• depth - how deep the node is (usefull for indenting)
• leaf - is it a leaf node (eg. has no children) - usefull for icons

The trick of the stored procedures is to correctly generate the seqid, this consists of two parts, the top level wrapper and the recursive prodedure to update the table.

DROP PROCEDURE IF EXISTS _TREE__resequence;

DELIMITER $$
CREATE PROCEDURE _TREE__resequence(i_sep VARCHAR(4))   DETERMINISTIC
  BEGIN
        DECLARE v_p, v_d, v_s INT(11);
        DECLARE v_fp TEXT;
        SET v_fp = '';
        SET v_p =0;
        SET v_d =0;
        SET v_s =0;
        SET max_sp_recursion_depth=255;
        CALL _TREE__resequence_sub(v_p, v_d, v_fp, i_sep, v_s);
        
        
    END $$
DELIMITER ;

   



DROP PROCEDURE IF EXISTS _TREE__resequence_sub;

DELIMITER $$
CREATE PROCEDURE _TREE__resequence_sub(
    i_parent INT(11),
    i_depth INT(11),
    i_fullpath TEXT,
    i_sep VARCHAR(4),
    INOUT i_seqid INT(11)
    )   DETERMINISTIC
    BEGIN
        DECLARE v_nid, v_ex_seqid  INT(11);
        DECLARE v_name VARCHAR(128);
        DECLARE v_leaf INT(1);
        DECLARE v_fullpath TEXT;
        DECLARE done INT DEFAULT 0;

        DECLARE qry CURSOR FOR SELECT id, seqid, name  FROM _TREE_ 
              WHERE parent_id = i_parent ORDER BY seqid;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
        OPEN qry;
        
        REPEAT
            FETCH qry INTO v_nid, v_ex_seqid, v_name;
            
            IF NOT done THEN
                IF v_ex_seqid != i_seqid THEN 
                    UPDATE _TREE_ SET seqid = i_seqid, depth=i_depth WHERE id=v_nid;
                END IF;
                IF i_depth > 0 THEN 
                    SET v_fullpath = CONCAT(i_fullpath, i_sep, v_name);
                ELSE 
                    SET v_fullpath = v_name;
                END IF;
                SET v_leaf =0;
                SELECT COUNT(id) INTO v_leaf FROM _TREE_ where parent_id = v_nid;
                
                UPDATE _TREE_ SET 
                    fullpath = v_fullpath,
                    leaf = IF (v_leaf > 0, 0 , 1)
                    WHERE id=v_nid;
                
                SET i_seqid = i_seqid +1;
                #// do the children..
                CALL _TREE__resequence_sub(v_nid, i_depth+1, v_fullpath, i_sep, i_seqid);
            END IF;
        UNTIL done END REPEAT;
        
        CLOSE qry;
    
    END $$
DELIMITER ;

This does the hard work of iterating through the tree, and updating the sequence number, depth, leaf field and filling in the fullpath field

Now our simple add node code just adds the node in the correct place, bumps the seqid along, so that you can then regenerate the tree.

DROP FUNCTION IF EXISTS _TREE__add_node;

DELIMITER $$
CREATE FUNCTION _TREE__add_node(
    i_parent INT(11),
    i_after INT(11),
    i_name VARCHAR(128)
    ) RETURNS INT(11) DETERMINISTIC
    BEGIN
        
        DECLARE v_depth INT(11);
        DECLARE v_seqid INT(11);
        DECLARE v_ret INT(11);
        DECLARE v_tmp INT(11);
         
        SET v_depth = 0;
        SET v_seqid = 0;
        SET v_ret = 0;
        SET v_tmp = 0;
         
        
        #// grap parent depth.
        SELECT depth +1, seqid + 1 INTO v_depth, v_seqid FROM _TREE_ WHERE  id= i_parent LIMIT 1;
        #// grab previous id..
        IF i_after > 0 THEN 
            SELECT seqid INTO v_tmp FROM _TREE_ WHERE id = i_after AND parent_id = i_parent LIMIT 1;
            IF v_tmp > -1 THEN 
                SET v_seqid = v_tmp+1;
            END IF;
        END IF;
            
        
        
        INSERT INTO _TREE_ SET 
            depth = v_depth , 
            seqid = v_seqid , 
            parent_id =  i_parent,
            name = i_name;
        
        
        SELECT LAST_INSERT_ID() INTO v_ret;
        
        #// fix the seqend id 
        
        
        UPDATE   _TREE_ SET seqid = seqid +1   WHERE seqid >= v_seqid  AND id != v_ret;
        
        RETURN v_ret;
        
    END $$
DELIMITER ;

You can now create a generic tree code by replacing my _TREE_ word with your table name:
eg.

#sed -e "s/_TREE_/yourtable/g" tree.js | mysql yourdb -f 

Once all that is done the SQL to create tree nodes is very simple:

 delete from nametree; alter table nametree AUTO_INCREMENT =1 ;
 
 select nametree_add_node(0,0, 'one at top'); 
 call nametree_resequence(':');

 select nametree_add_node(1,0, 'two on one'); 
 call nametree_resequence(':');

�select nametree_add_node(1,0, 'three on one (first)'); 
 call nametree_resequence(':');
�
 select nametree_add_node(1,2, 'four on one (last)'); 
 call nametree_resequence(':');
�
 select nametree_add_node(0,1, 'five on top (last)');
�call nametree_resequence(':');
�
 select nametree_add_node(0,0, 'six is first'); 
 call nametree_resequence(':');

 SELECT * FROM nametree ORDER BY seqid;

Gives you a nice little table

+----+-----------+-------+-------+------+----------------------+---------------------------------+
| id | parent_id | seqid | depth | leaf | name                 | fullpath                        |
+----+-----------+-------+-------+------+----------------------+---------------------------------+
|  6 |         0 |     0 |     0 |    1 | six is first         | six is first                    | 
|  1 |         0 |     1 |     0 |    0 | one at top           | one at top                      | 
|  3 |         1 |     2 |     1 |    1 | three on one (first) | one at top:three on one (first) | 
|  2 |         1 |     3 |     1 |    1 | two on one           | one at top:two on one           | 
|  4 |         1 |     4 |     1 |    1 | four on one (last)   | one at top:four on one (last)   | 
|  5 |         0 |     5 |     0 |    1 | five on top (last)   | five on top (last)              | 
+----+-----------+-------+-------+------+----------------------+---------------------------------+

Enjoy tree.js (I name my mysql stored procs as js, just so my editor works better with them)

After months of being left on the side burner, I just got round to fixing Digital TV with the ASUS My Cinema and MythTV.

Having finally got the correct source code from ASUS a few months ago, I've gone through it and made it thread safe (get rid of the global locks). So you can use multiple sticks on a single PC. and got rid of most of the shutdown kernel panics.

The final fixes where related to the really bad locking code, that basically was failing too quickly and constantly changing the GI settings. The kludge I've done is to remove the flipping (as all of the HK channels are on GI_945). and this card only really works in HK & China.

This resolve the last issue of waiting for a lock to a signal taking over a minute to milliseconds. - Finally making the card usable in MythTv (as it doesnt really like really slow locking cards..)

Anyway, code is in my repo - I need to start thinking about syncing with v4l-dvb guys and finding out how to rename the files so they are not just overwriting the existing dib3000mc driver.

For your pleasure

ASUS My Cinema U3100 Mini DMB-TH Linux Driver for v4l-dvb

If you need frequency details have a look at my last post about the ASUS stick.

Well, after a busy few months, things have gone quiet again. Hopefully it's a short term thing, but it has given me a bit of time to do get back to the less profitable things in life (like blogging).

PEAR releases

Yes, after a quite a few emails bugging me to release updates to DataObjects and Template Flexy, I finally got round to getting them out the door� (and even fixing a few bugs after the got out). I've also made an effort to get Services_JSON onto the pear release system, as I use it quite a bit, and it's been sitting in as the proposal for Services_JSON for well over a year.

Facebook

Yes, that other great waster of time, I've finally set up an account for me - "AK BK Consulting" - After my first effort of using facebook fell apart, due to the mess it made of mixing family stuff and professional stuff, (I use my wife's account for my family stuff). I set up that one so I can join up with PHP developers anywhere and IT people in Hong Kong. So feel free to add me as a friend (as I dont have many ;) - this blog should be� syndicated into my page...

ExtJS / Roo builder for HTML_FlexyFramework

I was messing around this week writing a builder for HTML_FlexyFramework, my little lightweight page loader, that integrates quite nicely with Dataobjects, Template Flexy and pear in general. Part of the incentive for this was seeing a little project that a potential client had developed, in some windows application that generated an how site starting with the database schema.

The idea� was quite nice, and the interface of the builder was quite friendly. But the downside was that the resulting code was just unusable jibberish. So rather than work out how to add features to it, I wondered if using DataObject's Generator as a core, I could generate a whole ExtJS/RooJS interface from the database, and then edit that to quickly get a frontend up and running.

The code's sitting in my akpear repo RooJS_DB_DataObject (it actually writes ExtJS1.1 code) and does the basic tasks of setting up a grid for each table, a simple form to edit each table, along with some limited joins based on the links.ini

If you want to try it out, it runs like this:

php  RooJS_DB_DataObject/cli.php 'mysql://dbuser:dbpass@dbhost/dbname'

Nice little proof of concept.. It's got some idea how to 'update' it's generated code, but I've disabled that at present. It should however give you a quick way to jumpstart an ExtJS application.

As my thumbs are twiddling waiting to see if projects start coming in next year, I thought I'd do a roundup of this years projects (open and closed). I was pondering if everyone else was seeing the effects of the recession, so I'd be interested to hear if you've also seen work slacking off.

Let's hope next year we see some more life from�the Sales and Marketing, as my current reliance on word of mouth, may not keep feeding my mouth at this rate.

Anyway Merry Christmas and Happy new year to all.

Read on for my Year of Projects

View Extended Entry

These are my notes/ comments on getting it to work (partly), check the blog again every month or so to see if I ever get the thing working fully..

After leaving the Digital TV sticks lying around for a few months, I finally got back to looking at the issues with drivers. ASUS have been absolutely useless, and have ceased responding to all support requests. The Source code supplied on the site, and emailed to me directly appears to be the completely the wrong source code (based on comparisons using strings on the binary driver,� I'll have more confirmation of this when I review the dmesg outputs as described below..)

Having given up on the source code, I decided just to see if I could get them to work in any way. We had a go with the Windows drivers on a PC at work, but did not really have much success (as I found out later, probably due to signal issues with the little antenna). But it did give me the idea of using virtual machines and running the EEEPC development kit. So here's the process so far just to test the cards. - details in the extended bit..

View Extended Entry

For this weeks hack, I've been looking at Lightning, the calendaring extension to thunderbird.

What brought this on was we where discussing a bug tracker for the Anti-spam / Mail management software, and none of the software that i've seen for this either is elegantly written, easy to set up or simple to use.

To me the 'ideal' way to handle this, having played around with Lightning, is to have a calender provider (that's shared) and have all the key people able to drag a 'requirement email' into the TODO task list, or similar. Where people can submit bug's by signing up to a calendar/todo list and just add it as a task.

Obviously not suited to all types of projects..., but I think you get the jist that using lightning to edit these bugs would be pretty compelling due to it's integration with your primary mailer.

State of the current providers.

So to see if this could work, I had a look at the current list of network providers available. ICAL, CalDav, Sun's Protocol, and there's a google provider.

On the face of things it looks like that's quite a choice, but if you look closely at all of these, one thing stands out like a sore thumb. The backend server to deal with all these protocols is an absolute nightmare to create, or modify to suit other types of applications (think of a shipping managment application that we could integrate order delivery information with the calendar, so you could add notes about the delivery etc. via the calendar item.).

Part of the core problem with the existing protocols can be summed up with one dirty word "XML"! - CalDav and google being the worst of this. If you look at the problem today and tried to solve it, the first thing that would come to mind would be JSON/REST. It's the perfect set of protocols to ensure that backend server would be trivial to write, and the front end would not be such a mess. (especially as Lightning's written mostly in Javascript anyway).

Hacking a provider

There are really only two examples I could find of calendar providers for lightning. Google is one, and there is also an extension called hcalendar - that pulls calendar's in from web pages (as far as I could see). The second of these two proved to be the goldmine for creating a JSON calendar provider extension.

hcalendar provides most of the code needed to implement any kind of provider, it only implements the getItems() method. However it has stub's for all the others.

You can browse through the code here, calJSONCalendar.js, I did run into a few issues with Async requests though, as It looks like you need to implement a request object (as per caldav and google calendar), otherwise you run into problems with transactions on updating. So as a simple workaround, the extension uses sync requests for updates and adding.

How it works

getItems() - get a list of calender or todo items.

is a GET request, with params
- aCount = max number of resulsts
- aRangeStart = date start ISO format
- aRangeEnd = date end ISO format
- caltype = calendar data type (C = calendar, T= todo)
- filter (not yet)
eg.
http://your_calendar?caltype=C&aCount=0&aRangeStart=2008-01-01....

this returns a json array something like this.

[
   {
	id: 1,                      // your data base id (ignored)
	uid: "XXXXXXXXXXXXXXXXX",
        dtstart: "2008-10-10 12:12:12",
        dtend: "2008-10-10 12:12:12",
	caltype : 'C',
	ical : "  <<< Calender data in ical format >>" 
	
	// all the other data is optional (if you do not have a ical record, it can build 
        // most of the event data from properties

	privacy: "default";
	created: "2008.....",
	last_modified: "2008....."
   }, 
   ......
]

modifyItem() / addItem()

is a POST request, with the params pretty much the same as the json data above (with the ical record in the ical POST entry) - the resulting entry is returned by the call in the same format as above. At present ical is the main thing that is posted, however I think it needs to send quite a bit of the generic information.

deleteItem()

is a POST request, with the property _delete_={uid},
(currently we ignore the response) - it should probably be
{ success: true|false, msgid : 1, msg: "A string error" }

the body of this message contains a example server implementation for the extension. - you can download the extension here: jsoncalendar-0.2.xpi, (REQUIRES LIGHTNING 0.9!!) or have a look at my svn repo JsonCalendarProvider for how to develop test it.

Read on for the SERVER BACKEND CODE...

View Extended Entry

Remember the good old days where phpnuke was the worst code since sliced cheese, and thousands of idiot's had installed in on their server waiting to be hacked into..

Well, It's a bit like how I felt this week trying to solve my svg graphing issues..

A bit of background, In the last week I just pushed out the latest version of our Anti-spam / Mail control application, the big new feature was a beta version of the statistic's� and analysis section. Where we showed nice little graphs about how much work your staff where doing and if they had really been sending jokes all day long or just reading facebook spam

Open Flash Charts

While the database side , the design is quite interesting, I did not really spend to much time thinking about the frontend - as I like to keep that flexible (read throw away). So for the first beta I decided to use Open Flash Charts. The current version is extremely well designed, in terms of the chart generation consists of delivering a JSON file to a static single Flash file. The JSON file sets the graph type, and date etc..

It's very quick to set up and work with, and the graphs are quite pretty, and a cutely interactive. And for our first beta it's just about OK.

However for anything other than a beta,� there are rather serious problems with it.
a) It's dog ass slow, to load, render on anything but a brand new overspec'd PC
b) The flash file is quite large, and yet another overhead to a already large application.
c) Having multiple Flash graphs on a single page, just makes the above two problems worse.
d) It depends on closed source tools (Actionscript compilers etc.) - Never a good idea.
e) Printing of Flash in firefox is broken (at least on linux)
or basically all summed up... Flash sucks

PlotKit

So after beta 1 was released to testing, I started the hunt for an alternative. Since the 'correct' method to do graphs on the web is really SVG, and as I like to shift all the pointless processing to the end users PC.� JavaScript to convert the data into a chart is really the ideal solution.

Pretty high up on google's list is PlotKit, which has some really nice demo's on the site, It depends on two other pieces of code, excanvas (as nice self contained javascript file that handles SVG compatibility issues with IE/FF/Safari etc.)� and MochiKit.

MochiKit

Since MochiKit is none too small, and would add to my javascript overhead, I thought I'd check out what it was, and see if it was really needed. What I saw, was more "nightmare on nuke street".

The front page of the web site claims quite large "MochiKit makes JavaScript suck less", That should be paraphrased, "only if you are a Python nut-head" - otherwise it make your Javascript code into complete jibberish. __self__, __new__, __screwed_up_language__ everywhere....

<big rant> The dumb f*** who wrote it decided that Javascript was not Python, so they should try and make it's syntax as close to python as possible. Ignoring the fact that Javascript syntax just solves� problems differently, it does not need fixing or even breaking to be as badly designed as python.. </big rant>

Anyway after digging through the code to plotkit, and mochikit, I came to the conclusion that they had developed a piece of code that was completely unmaintainable or extend-able. So was forced to keep looking for an alternative.

Plotr

Quite a way down google's list (search svg javascript graph) I finally found Plotr, Basically someone else had obviously looked at PlotKit, and decided that Mochikit was just so ugly, that removing it from the application was the only sane thing left to do.

What they chose rather than MochiKit, was Prototype, which in general is considerably better than MochiKit. And basically removed all the Mochikit dependancies and replaced them with Prototype. This made the code quite a bit simple to read and understand, (they also added comments), although I'm not to keen on this type of commenting, as I find it add's noise, and makes the code less readable.

xxxx : function (/** some_type **/ xxxxx, /** another_type **/) {

Anyway, otherwise Plotr looks clean and simple.

** It looks like the author of Plotr as gone on to start Flotr - another graphing library.. - did not notice that until I'd commited to working with Plotr...

Prototype

What I wanted to do however was reduce the size of the Prototype dependancy. So I started having a look at Prototype's internals.

Splattering the namespace

Unlike Ext, and Roo, Prototype's core creates quite a few global properties, $, $R, $$, JSONSomething and Element which by the looks of things are totally unnecessary, and make it difficult to work with other frameworks (or potentially other code), This is downright bad design, and could easily be rectified by putting almost all of those under the Prototype.* object.

Along with this, there is quite excessive use of adding to Global Prototypes, String.prototype gains about 20 extra methods, some quite useful, or wrappers for compatibility, other seem more like features for features sake, and should really be moved to a Prototype.String object or just removed.

What make me wonder the most was the way that prototype adds properties to HTML and DOM objects, $("xxxx") - get's does a getElementById( "xxxx") and returns the DOM/HTML object overlaid with all of the properties from the Element object.� In Ext/Roo's 'Roo.get("xxx")' method� create's something like the 'Element' object , Roo.Element, it puts the original DOM object in the return value ".dom" property. Hence making documentation clearer and code considerably more managable. Not so much of WTF did that method come from? when reading code.

Anyway - I managed to give Prototype a bit of a diet, so it's� not so much of a Namespace/bandwidth hog. You can download the lightwight code here:
http://www.akbkhome.com/svn/javascript/plotr
Note the full bundle (protolite, excanvas + plotr comes in at 54K or ~ 15K if you are serving javascript with the mod_gz library...

Just to prove it still works
http://devel.akbkhome.com/plotr/testplot.html

See here for the original code
http://code.google.com/p/plotr/source/browse/

Just a small update on roojs1 - I've added in-line comments to the RooJs1 documentation - so if you think a method or class is not well explained, add your comment, example code, or bug note right on the documentation. - Something I think makes a huge difference to the usability of code...

Other small changes are also beginning to be added - including the fields property of a Roo.toolbar, and the new Roo.form.Hidden element, to make creating forms with hidden variables simpler.

Anyway, rumors are swirling around that there is a recommendation to avoid ExtJS among certain linux distributors due to uncertainties and general lack of confidence in the license, it's continual changing and general poor behavior by Jack in not understanding the implications of the bait and switch changes.

It also appears that Jack's Legal team are working harder than his coding team again, sending out threatening letters to people involved in various ExtJs2 derivative works. (which is the key reason I'm focusing on v1 - clear, definitive separation and licenses that do not allow for such shenanigans)

While ExtJS2 offered some interesting new features. The significant change to the object rendering model, from my experience has been broken on every release prior to the GPL one, (and since I never tested it after that release, I suspect it's still broken).

Other changes relating to a clearer heirachy of object model look like they should be a simple addition to ExtJS1, along with better support for non DOM element dependent constructors. Hence I think building on ExtJS1 appears to be a better long term move, and sounds like a few more are beginning to see that wisdom.

View Extended Entry

After a little wait (~ 3 weeks), having asked ASUS to provide me with the source code for the drivers to the DMB-TH Digital TV receiver, I finally got two tarballs of the source code. One step closer to an open source friendly solution to watch Digital TV in China and Hong Kong.

It took a little persuasion to convince the technical support team at ASUS that I was entitled to the source as it was based on GPL code, but once they understood, and had asked their supervisor/ R&D department about it. It was just a matter of waiting for the R&D dept to get time to do it.

I was very impressed with ASUS in general - their tech response times for product support where pretty amazing (usually answering within a few hours). If you have the option of buying ASUS, I can highly recommend their email tech support. Friendly, positive, and responsive.

The driver modifications are all to drivers/media/dvb/*, and consist mostly of 2 new files dealing with the demodulator, and a rather unfortunate hack at db3000mc.c - Basically replacing the core of that file with the routines for the Legend chip. Rather than creating a new file (which looks like it would have been a far better idea).

Anyway, I'm going to try building these changes against a recent kernel, then see if I can sort out the db3000mc mess. If anyone want's to help out, drop me an email and I can send you the files.