MySql: The power of Views (or Better late than never)

I sometimes get the sour feeling of “there’s something fundamentally wrong with this! It works, but nevertheless…” It happens in all aspects of life, including programming. Some of (not always!) the times this feeling causes curiosity and energy to strive for a new perspective, new knowledge, new ways of facing the problem. Aspects of inutition and creativity, I guess…

In my current project there’s an awful lot of SQL going on. Lots of connected tables, nestled selects etc. In the preparation for the next level of full text searchability, I stumbled upon the concept of Views in MySql. I can’t believe that I didn’t know this beauty before!

In short, a View is a virtual database table where the content is a result of a underlying sql statement. It can be accessed by normal queries just like any table – a view can build its content depending on tables as well as other views.

For a good introduction of MySql Views, check this Techtopia blogpost out.

Creating a View in PhpMyAdmin is a breeze, there’s a Create View link in the right bottom of any table view:

I haven’t found any button to retrieving the underlaying View sql though. However, it can be done by using a normal “SHOW CREATE VIEW tablename” sql statement.

The concept of views will ease my further development a lot! I’m glad I did listen to that feeling…

Red5 on Ubuntu 9.04 – Thanks to BigBlueButton!

Found the BigBlueButton project (http://bigbluebutton.org/) the other day – very interesting!

Not at least that they have solved the tricky red5 installation very in a way that hopefully will find it’s way back to red5.org very soon… Their full installation uses nginx web server (http://nginx.net) instead of Apache, but here’s a way to pick the red5 raisin out of the big blue cookie:

1. wget http://archive.bigbluebutton.org/bigbluebutton.asc
2. sudo apt-key add bigbluebutton.asc
3. echo “deb http://archive.bigbluebutton.org/ bigbluebutton main” | sudo tee /etc/apt/sources.list.d/bigbluebutton.list
4. sudo apt-get update
5. sudo apt-get install red5

Thank you, Fred and others at BigBlueButton!
Now, let’s find out if this works on a Debian Lenny too…

The quest for client-side search query language

…just led me to CQL (Contextual Query Language). Thanks StackOverflow and njk for showing the way!

(Please note that since version 1.2, “Contextual Query Language” is changed from former “Common Query Language”)

This lets the user write advanced searches using logical operators, indexes, even proximities… So, let’s create a search for all the cql-to-php-to-sql libraries laying around waiting for to be implemented…

Imagemagick: Pdf to bitmap density quirk

When converting bitmapcontained pdfs to bitmaps using the imagemagick convert method, beware the parameter order! The density parameter sets the resolution of the bitmap conversion before resizing – therefore, the higher density value, the better resulting quality.
So, the followning should work, but doesnt:

convert source.pdf -resize 630×891 -density 300 result%04d.png

By some reason the -density parameter has to be set before the source file name. This works:

convert -resize 630×891 -density 300 source.pdf result%04d.png

By the way, the “%04d” tag in the resulting file name gives an autonumbering with leading zeros, in this case 4 digits in total. Thus:

page0000.png
page0001.png
page0002.png

etc.

Kohana 2.x code completion in Eclipse PDT

Things are about to change with Kohana 3 just released…

Meanwhile, the Eclipse PDT editor can’t really cope with Kohanas class namings and folder structure. But of course there is a solution: Peter Bowyer’s zend_autocomplete (works in all Eclipse PDT implementations including my boosted FlexBuilder 3, not just Zend Studio!). Put it in your controller directory, run it – and it creates a file in the cache containing all Kohana system classes as well as your own application classes! This is what Eclipse needs to give you well-working code completion!

Thank you, Peter!

Kohana, Formo and many-to-many relations (“habtm”)

Just got the solution for how to handle many-to-many relations in Formo. Thank you, Ben!

Three tables, groups (id, name), users (id, name) and the pivot table groups_users(group_id, user_id).

class User_Model extends ORM {
protected $has_and_belongs_to_many = array('groups');
}
class Group_Model extends ORM {
protected $has_and_belongs_to_many = array('users');
}

And here’s how to in the controller handle display of an user form, complete with checkbuttons for toggling connections to groups:

public function user($id = false) {
$user = new User_Model($id);
$form = Formo::factory()
->orm($user)
->habtm('user', 'group')
->add('Submit');
if ($form->validate()) $form->save();
echo $form;
}

This gives the following form. The magic lies in the habtm plugin (has to be active either by Formo config or by Formo::factory()->plugin(‘habtm’). Not one single sql line, not one line of deadly tedious form populating and deriving…

Ok, not pretty, but that’s another story.

Soo proud. Just struck me what “habtm” stands for.
Na na, won’t tell ya!

Kohana PHP with ORM and Formo – db made easy!

After some days prototyping with Kohana, I start to realize the strength and beauty!

One of the every-day needs in my world is to coordinate different database tables to get things to work. Most of the time this is done by hand, using PhpMyAdmin. For basic stuff, this works fine, but very often there’s the bad feeling of should-have-a-real-admin-interface-for-this…

No bad feelings no more! The combination of Kohana’s Object Relational Mapping library (ORM) and the Formo module makes it super-easy to create a table-admin-interface, including related tables. No SQL at all, no defining form layouts at all! (Ok, there can be a need for some validation information, some styling etc.)

Here’s an example, based on two tables, schools and students:

schools: <- Note: Table name in plural
id:int autoincrement
name:varchar

students:  <- Note: Table name in plural
id:int autoincrement
school_id:int   <- Note! Referred table as singular + "_id"
name:varchar

Two models for those tables:

// models/student.php
class Student_Model extends ORM {
protected $belongs_to = array("school");
// Defines the connection to school - "each student belonst to a school"
}
// models/school.php
class School_Model extends ORM {
protected $has_many = array("students");
// Defines the connection to students - "each school has many students"
}

So for the controller. Here’s the real beauty: In the listAll action below the schools are fetched, and ORM handles automatically the relationship and fetches all connected students as well!

// controllers/controllername.php
public function listAll() {
$schoolModel = new School_Model();
$schools = $schoolModel->find_all();
foreach ($schools as $school) {
echo "<br/><b>$school->name - ".html::anchor("controllername/school/{$school->id}", "edit")."</b>";
foreach ($school->students as $student) {
echo "<br/>- $student->name - ".html::anchor("controllername/student/{$student->id}", "edit");
}
}
}

The actions below handle both creation and editing of posts. The super-handy Formo library maps out a complete form – including dropselect for selecting a school for the students! Kind of magic!

// controllers/ormtest.php
public function school($id = false) {
$school = new School_Model($id);
$form = Formo::factory()->orm($school)->add("Submit");
echo $form;
if ($form->validate()) {
$form->save();
}
}

public function student($id = false) {
$student = new Student_Model($id);
$form = Formo::factory()
  		->orm($student)
  		->add("Submit");
if ($form->validate()) $form->save();
echo $form;
}

The only “tricky” part is that you have to get the table names and references to them right according to the ORM naming conventions. Here’s a short summary:

• Table names should be plural
  (“schools”, “students” – this makes sense because the table is a list with many items)
• Referring field names should be singular plus “_id”
  (“school_id” – logically because a single student belongs to one single school)
• Model names should be singular.
  (The model file for a student is named “student.php”, and the class is named “class Student_Model extends ORM”. A model most of the time represent one single item: one student or one school. The exception is when we want a listing of all items in the table. Have a look at the listAll code above.)

The models need to include information about the relations.

• The student belongs to one of many schools, therefore we use
  protected $belongs_to = array(’schools’);
  with plural in “schools”
• The school has one or many students, thus
  protected $has_many = array(’students’);

Note that this relates to a one-to-many relationship.

Happy crudding with Kohana

Another superb Kohana solution: CRUD Scaffold (projects.nathanbentley.com/projects/show/crudscaffold) by Nathan Bentley. Just install, write some controller and model lines, and you have a nice JQuery-driven table editor – just the way it should be!:

Thank you, Nathan!

Now, there’s just left to sort out how to handle related tables…

Php: Getting it together with Kohana?

Somewhere deep inside you carry a vision, a longing, a vague picture of “getting it all together”… At least I do – in every aspect of life..!

Since I started php coding some years ago, I’ve always had a bad taste in my mouth. It’s so ugly, so diversitive, so sprawling, so poly-cultural (in a bad sense), so non-coherent… This feeling has been accentuated since I started using Flex and Actionscript 3 with its java-like package concept combined with the superb Eclipse based editor… At last an environment that helped me to good and professional-like thinking, structure and habits!

Since some months, I’ve been able to get my php-solutions together in a low-level kind-of-framework-like structure, where I (at last!) quickly and easily can reach what I need: GD-processing, File handling stuff, SQL processing, debugging, ZendAmf stuff etc…

Connecting to my initial philosophies, I would like to weave this basic every-day utility stuff into something that has full limit-less production capabilities, including crudding, cms-ing, caching etc… And maybe I’ve found what I’ve been looking for – Kohana (konanaphp.com)! With its CodeIgniter roots, it’s the far most elegant and simple php framework that I’ve been able to understand this far..!

It’s layered concistent structure of system-modules-application makes it so quick and easy! Just one example: Yesterday, I downloaded and tried the s7n cms system (http://code.google.com/p/s7ncms/) created in Kohana by Eduard Baun. The complete cms core as one module! Extensions of the cms also as modules! The fundamental Kohana layout virtually untouched! Some months ago I investigated some Zend cms solutions, and they were a complicated mess compared to this one…

S7N admin interface, created with Kohana PHP

And it looks good too…

AS3 BitmapData AMF solution using IExternalizable

Using bitmaps in an AS3 remoting environment isn’t that easy. If I’m getting it right, the problem is that the BitmapData object needs constructor parameters, wich isn’t combinable with the handy [RemoteClass(alias= ] method of invoking the objects when streaming data into a ByteArray.

A walkaround is to wrap the BitmapData into a custom class implementing the IExternalizable interface. This interface forces the writing and reading of data through methods where the serialization can be taken care of.
In the example below (a renamed copy of this class, thank you Greg! – unfortunately I couldn’t find your lastname…), the bitmap data storage is done through encoding/decoding as png data. Loading is then performed via a Loader object.

package
{
    import flash.display.Bitmap;
    import flash.display.BitmapData;
    import flash.display.Loader;
    import flash.events.Event;
    import flash.utils.ByteArray;
    import flash.utils.IDataInput;
    import flash.utils.IDataOutput;
    import flash.utils.IExternalizable;
    import mx.graphics.codec.PNGEncoder;

    [RemoteClass(name="ExtBitmap")]
    public class ExtBitmap implements IExternalizable
    {
        public var imageData:BitmapData;

        protected var loader:Loader;

        public function ExtBitmap(){
            loader = new Loader();
            loader.contentLoaderInfo.addEventListener(
                   Event.COMPLETE, handleBytesLoaded);
        }

        public function handleBytesLoaded(event:Event):void{
            trace('handleBytesLoaded');
            imageData = Bitmap(loader.content).bitmapData;
        }

        public function writeExternal(out:IDataOutput):void{
            trace('writeExternal');
            var encoder:PNGEncoder = new PNGEncoder();
            var bytes:ByteArray = encoder.encode(imageData);
            bytes.position = 0; // may not be necessary
            out.writeDouble(bytes.length);
            out.writeBytes(bytes);
        }

        public function readExternal(input:IDataInput):void{
            trace('readExternal');
            var length:Number = input.readDouble();
            var pngData:ByteArray = new ByteArray();
            input.readBytes(pngData,0,length);
            loader.loadBytes(pngData);
        }
    }
}

Let’s hope that this kind of wierdness can be forgotten in AS4…