Kohana nested SELECT statment

Problem
You need to join two tables using a nested SELECT statement.  However, Kohana's Database module doesn't really allow this query:

mysql> select forum_categories.*, (
    ->     select count(forum_data.id)
    ->     from forum_data
    ->     where forum_data.category_id = forum_categories.id and
    ->     forum_data.parent_id = '0'
    -> ) as cat_count
    -> from forum_categories;
+----+----------------+------------------------+-----------+
| id | title          | description            | cat_count |
+----+----------------+------------------------+-----------+
|  1 | Category One   | category 1 description |         2 |
|  2 | Category Two   | category 2 description |         2 |
|  3 | Category Three | category 3 description |         0 |
|  4 | Category Four  | category 4 description |         0 |
|  5 | Category Five  | category 5 description |         1 |
|  6 | Category Six   | category 6 description |         0 |
+----+----------------+------------------------+-----------+
6 rows in set (0.01 sec)

Solution
Rewrite your query as a left outer join (or inner join, depending on your case):

mysql>  select forum_categories.*, ifnull(forum_data.total, 0) as total
    ->   from forum_categories
    ->   left join (
    ->       select category_id, count(forum_data.id) as total
    ->       from forum_data where parent_id = '0'
    ->       group by category_id
    ->   ) as forum_data
    ->   on forum_data.category_id = forum_categories.id;
+----+----------------+------------------------+-------+
| id | title          | description            | total |
+----+----------------+------------------------+-------+
|  1 | Category One   | category 1 description |     2 |
|  2 | Category Two   | category 2 description |     2 |
|  3 | Category Three | category 3 description |     0 |
|  4 | Category Four  | category 4 description |     0 |
|  5 | Category Five  | category 5 description |     1 |
|  6 | Category Six   | category 6 description |     0 |
+----+----------------+------------------------+-------+
6 rows in set (0.01 sec)

Translating to Kohana, we now have:

$subquery = DB::select('category_id', array('COUNT("forum_data.id")', 'total'))
 ->from('forum_data')
 ->where('parent_id', '=', '0')
 ->group_by('category_id');

$query = DB::select('forum_categories.*', array('IFNULL("forum_data.total", 0)', 'total'))
 ->from('forum_categories')
 ->join(array($subquery, 'forum_data'), 'LEFT')
 ->on('forum_data.category_id', '=', 'forum_categories.id')
 ->as_object()
 ->execute();

For more info, check out the Subqueries section of Kohana's Query Builder documentation.

Downgrading from PHP 5.3 to 5.2 on Debian Squeeze

Problem

You've installed PHP 5.3 on your Debian Squeeze system. Problem is, some web apps have trouble running on that version. You need to downgrade your system from 5.3 to 5.2.

Solution

Remove the PHP 5.3 packages from your system:

sudo aptitude purge `dpkg -l | grep php| awk '{print $2}' |tr "\n" " "`

Clean the cache just to be sure:

rm -f /var/cache/apt/archives/php5*

Use Karmiс for PHP packages:

echo -e "Package: php5\nPin: release v=karmic\nPin-Priority: 991\n" | sudo tee /etc/apt/preferences.d/php > /dev/null
apt-cache search php5-|grep php5-|awk '{print "Package:", $1,"\nPin: release v=karmic\nPin-Priority: 991\n"}'|sudo tee /etc/apt/preferences.d/php > /dev/null

Add Ubuntu Karmic to source list:

cd /etc/apt/sources.list.d
sudo wget -O karmic.list "http://pastebin.com/download.php?i=q9ya307g"

(Update, October 18, 2012: The sources list has been updated, because Ubuntu no longer supports Karmic. Please leave a comment if this still doesn't work.)

Update the package database:

sudo apt-get update

If the command above produces this error:

W: GPG error: http://security.ubuntu.com karmic-security Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 40976EAF437D05B5
W: GPG error: http://archive.ubuntu.com karmic Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 40976EAF437D05B5
W: GPG error: http://archive.ubuntu.com karmic-updates Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 40976EAF437D05B5

Then import the required keys and add them to your list of trusted keys:

gpg --keyserver hkp://subkeys.pgp.net --recv-keys 40976EAF437D05B5
gpg --export --armor 437D05B5 | sudo apt-key add -

gpg --keyserver hkp://subkeys.pgp.net --recv-keys 40976EAF437D05B5
gpg --export --armor 40976EAF437D05B5 | sudo apt-key add -

gpg --keyserver hkp://subkeys.pgp.net --recv-keys 40976EAF437D05B5
gpg --export --armor 40976EAF437D05B5 | sudo apt-key add -

Finally, install PHP 5.2:

sudo apt-get install -t karmic php5-cli php5-cgi libapache2-mod-php5

UPDATE: Using the Ubuntu sources to download old packages works on Debian. So far, it works on my system, but you may need to use the Debian sources just in case.

Apache: Restricting folders by IP addresses

1st Problem:
You are running an Apache server. You need to restrict access to certain folders by IP addresses.

Solution to 1st problem:
Use .htacess to deny all access to that folder, then allow certain hosts/IP addresses.

AuthName "simoncpu's dark secret"
AuthType Basic

<Limit GET POST>
order deny,allow
deny from all
allow from 10.0.0.
allow from .example.org
allow from this.is.an.example.invalid
</Limit>

2nd Problem:
Your Apache is behind a proxy such as nginx. All requests to your Apache server appear to originate from your proxy.

Solution to 2nd problem:
Install mod_rpaf and restart your web server. .htaccess will now correctly restrict access to your folders.  Refer to its web page for installation instructions.

HOWTO Let lighttpd listen to both IPv4 and IPv6 on *BSD.

Okay, I've managed to let lighttpd listen to both IPv4 and IPv6 requests without using IPv4-mapped addresses. According to an Internet draft, IPv4 mapped addresses are considered harmful (R.I.P., itojun :). This has already been turned off in *BSDs by default but not in Linux.

Anyway, I've found out that if sysctl net.inet6.ip6.v6only is set to 1, enabling IPv6 in lighttpd would cause it to listen to IPv6 only. To solve this, you need to use these settings:

server.use-ipv6 = "enable"
$SERVER["socket"]          == "0.0.0.0:80" {
}

If you need to use SSL, simply use:

server.use-ipv6 = "enable"
ssl.engine = "enable"
ssl.pemfile = "/var/etc/cert.pem"

$SERVER["socket"]          == "0.0.0.0:443" {
  ssl.engine = "enable"
  ssl.pemfile = "/var/etc/cert.pem"
}

Tada! It works:

root@soulfury:/# sockstat | grep lighttpd
root     lighttpd   23974 4  tcp6   *:443                 *:*
root     lighttpd   23974 5  tcp4   *:443                 *:*
root@soulfury:/# sysctl -a | grep v6only
net.inet6.ip6.v6only: 1

Agavi Validation

I'm trying to learn how Agavi's validation system works.

I wrote a writeup on my wiki.

Google Moderator part 2

before sending a new page request, the server checks the differences on what the client already has, and what its going to sent. the difference is then compressed and sent, browser on the client patches it. this should provide amazing improvement.

What kind of files? Generating a diff from file A and B, where A and B are totally unrelated files, results in a file which is more or less the same size as B. Therefore, this won't provide "amazing improvement".

Browsers already support caching and HTTP compression.

My idea is to make a giant web where the internet connection is splitted between the people connected so there is no connection wasted, think for example, when somebody with 10 mb/s turns off the pc nobody can use that speed, maybe you can make it :)

QoS.

Governments should aim to provide affordable broadband access for all citizens, in the same way they provide access to affordable water, electricity or shelter.

Government-run ISP? No thanks.

The government needs to subsidize the various brodband access methods out there. Things like Brodband over Power lines, and low orbit satellites. We've spent 7.4 trillion, one would think there is some money in there to fund this stuff.

Government subsidy? Do you know where that money comes from? From taxes, of course. Are you suggesting that people who don't use the Internet will subsidize people who use 1TB of bandwidth per month? Good thinking!

2 ways to make the web faster, 1st one is stopping illegal contents, spams & peer2peer streams, to access & saturate large % of ISPs bandwidth; the 2nd one is replacing the customer's outdated PSTN cables by optic fiber, but how will pay THE BILL ?

Use bandwidth effectively by removing the limits and allocating it to who use them the most,...instead of giving high bandwidth for a person who uses internet just to check an email, give it to someone who runs a webserver...

You want to censor the Internet? Die, communist, die!

Let's make the web faster: Google moderator

Okay, I'm sufficiently annoyed with the ideas posted in Google moderator. Most of the ideas posted there are not new, and they can be implemented with existing technology. The sad thing is, people vote them up.

most websites are templated on the serverside, and for most websites every page you view means about 20% of data transfer is the template. the template should only load once, and should be done in browsers.

You mean like XSL Transformations, which is already implemented in several Web browsers?

P2P browser: Standard browsing is exclusively client/server. Imagine instead that every browser is a P2P app and reports webpage data it downloads to "tracking servers" so when someone else wants the same page they get it from several peers instead

You mean like The Coral Content Distribution Network?

Extend HTML mark-up with new elements. The code will be more complicated because of more elements, but much shorter. Same with CSS - extend number of properties to resolve different tasks by one style property. Reduce size of properties names for CSS

Extend HTML? Oh, so you mean like XHTML, or the Extensible Hypertext Markup Language?

Apache has modules like mod_perl, mod_php and other that allow you to extend the functionality of the browser. While Firefox 'plugins' do that for the frontend, why not make modules for the 'backend' of the browser that enable you to send PHP or Perl

Send what?

Come up with rules which can't be explained by different ways. Like padding in IE and FF - now to make page with same look in all popular browsers you should add more code and CSS which increase size of page and take more time...

You mean like XHTML Strict?

Developers need to comeup with a way to use the already existing information through browoser. Say if it is the same IP packets receiving from the past try to use it and construct the patterns. Avoid duplicate bits to receive. This helps in bandwidth

You mean like using caching settings in the HTTP header?

Many headers that are being sent as part of every HTTP req. and resp, though all HTTP headers are optional. Web servers have to rethink on file naming and their url references. Smaller data to be transported means faster transport.

What kind of rethinking? What kind of URL references? What kind of naming?

And save what, 8 bytes?

Why don't we have an alternative to HTML? I mean open source, anyone can commit changes to the source, and a group oversees which changes are actually put in place. HTML5 will not be released till 2022, and that's when you realize it's ridiculous.

What's ridiculous is that you think HTML is a program.

w3c should regulate the release of browsers. if a browser doesn't pass all the tests for a standard, it should be allowed to call itself a "browser" . because of browsers not following standards, we forcefully hv to implant hacks, and make web slower

Are you a communist?

Process Management in PHP

My PHP script downloads a series of binary patches and places them into a directory for further processing. It also creates lock files to prevent two or more instances of my script from being run.

The Problem

When the script receives a SIGINT (Ctrl+C) or SIGTERM signal from the OS, my script is prevented from cleaning up the temporary files, which usually occupies hundreds of MB of space. The lock files also become stale, so my script prevents itself from being run until the next reboot, when the /tmp directory is cleaned up.

The Solution

The solution is to catch the signals so that my script is given one last chance to perform cleanup:

function sig_handler($signo)
{
   switch ($signo) {
   case SIGTERM:
   case SIGQUIT:
   case SIGABRT:
   case SIGINT:
       echo "==> Software Update has been aborted.\n";
       echo "==> Performing cleanup...\n";
       cleanup();
       exit();

       break;
   default:
       // Unknown signal. Do something here...
       exit();
   }

}

pcntl_signal(SIGTERM, "sig_handler");
pcntl_signal(SIGQUIT, "sig_handler");
pcntl_signal(SIGABRT, "sig_handler");
pcntl_signal(SIGINT, "sig_handler");

tail -F in PHP

I'm currently writing a PHP script that pushes live log data to the front-end UI. I'm using a streaming Comet model such that my back-end would update the front-end's screen every time the log file is updated.

Unix has a nifty utility called 'tail' that can continuously monitor a file for updates and display them to standard output (which is the usually the monitor). By capturing tail's output via popen(), I was able to make a log file viewer with just a few lines:

exec("/usr/bin/killall tail");
// execute myapp in the background and redirect all of its output to logfile
exec("/usr/local/bin/myapp > /var/log/mylogfile.log 2>&1 &");

$handle = popen("/usr/bin/tail -F /var/log/mylogfile.log 2>&1", 'r');
while(1) {
   $buffer = fgets($handle);

   if (trim($buffer) == '-- ok') { // this string signifies end of execution
       pclose($handle);
       exec("/usr/bin/killall tail");
       exit();
   } else {
       echo trim($buffer);
       // if you're using Comet, push output to the stream here
   }

   ob_flush();
   flush();
}

Very cool...

PHP doesn't support dot characters in form names

I've written a PHP proxy script that communicates with an underlying application. I encountered a problem where the script is unable to proxy data that contains dot characters in the form name.

To illustrate:

<?php
   var_dump($_POST);
?>
<form action="test.php" method="post">
   <input name="this.is.a/variable" type="text" />
   <input type="submit" />
</form>

Output:

array(1) {
   ["this_is_a/variable"]=>
   string(0) ""
}

Notice the array key in the var_dump(); dots are converted into underscore.

Solution

I accessed the raw input stream directly instead of using $_POST:

<?php
   $data = file_get_contents("php://input");
   $response = proxy_blah_blah($data);
?>

PHP's New Namespace Separator

I don't like PHP's new namespace separator. I prefer '::' to '\'.

Aaaaargh... why did they choose the escape character?

Slow lighttpd using IPv6

I've been wondering why lighttpd seems to be so slow when IPv6 is enabled. I discovered that I had to bump the minimum and maximum number of FastCGI process (min-procs/max-procs) by twice as much because it appears that lighttpd divides the available PHP processes between IPv4 and IPv6. I dunno why it does that.

Unix/Linux Tip: Syntax check your PHP files

If you wish to syntax check (lint) all the PHP files in your current directory, simply do:
    for i in `ls *.php`; do
        php -l $i
    done

OpenLaszlo IDEforLaszlo Eclipse plugin

Syte GMBH has provided an update for IDEforLaszlo plugin to support OpenLaszlo 4.0.10 and OpenLaszlo 3.4. IDEforLaszlo has been stale for quite some time now, and I'm glad that someone has offered their time and effort for this project. I'll be checking it out later.

IDEforLaszlo can be found at http://www.syte.ch/en/laszlo.xml.

Permission denied to call method XMLHttpRequest.open

Problem
When deploying DHTML OpenLaszlo applications in SOLO mode, attempts to fetch datasets results in the following error:

ERROR: uncaught exception: Permission denied to call method XMLHttpRequest.openLFCdhtml-debug.js (line 1421)
uncaught exception: Permission denied to call method XMLHttpRequest.open

Solution
As a security measure, most browsers restrict XMLHttpRequest such that they don't accept XML data if the HTTP response headers are not properly set. To fix this, simply change Content-Type to:
Content-Type: application/xml; charset=UTF-8

__LZgetNodes: p is null in Datapath

I'm creating an IPv4/IPv6 OpenLaszlo widget that can bind to a datapath. I spent hours trying to find out why this.datapath.getNodeText() doesn't work inside <handler name="oninit">, although it works fine inside <method>. I kept getting an error of "p is null in Datapath."

For developers experiencing a similar problem, you might want to check out Laszlonia's entry on his blog. It turns out that I simply had to use <handler name="ondata">.

map methods execute*() methods in Agavi

Agavi is architecturally beautiful. I can really appreciate the way they designed this thing. One fundamental problem with this framework, however, is that it lacks good documentation. You need to read the code, or ask others who already did, for you to answer some very simple questions.

In my case, I was looking for a way to change the HTTP verb mappings with AgaviWebRequest methods.

Agavi maps executeCreate() with HTTP PUT and executeWrite() with HTTP POST. If I use these functions in the context of REST architecture, it makes more sense if they are mapped the other way around. Although I understand that there is no single way to implement REST, there seems to be a general agreement that update operations should be mapped to HTTP PUT while create operations should be mapped to HTTP POST.

Fortunately, Agavi provides a simple (undocumented) way to change the mappings. In config/factories.xml, simply add:

              <request class="AgaviWebRequest">
                  <parameter name="method_names">
                      <parameter name="POST">create<parameter>
                      <parameter name="GET">read<parameter>
                      <parameter name="PUT">write<parameter>
                      <parameter name="DELETE">delete<parameter>
                  <parameter>
              <request>

Thanks to the crazy guy, MikeSeth. =)

confused on implementing REST using HTTP PUT

I'm implementing a REST web service using PHP5 right now. I'm a bit confused on how to implement a write operation using HTTP PUT verb.

In PHP, data that are sent via HTTP POST are in the following format:

variable1=data&variable2=data

The data is immediately available via $_POST. Data that is sent via HTTP PUT is uploaded as a file to the server and must be parsed before reading. There's nothing wrong with it per se, except that writing data is inconsistent. There's no problem requiring my REST clients to upload a file using HTTP PUT, except that it's inconsistent.

So far, I used curl for testing my web app. I haven't tried sending data via <form> using a web browser, and I don't know whether <form method="put"> works. If it does, then the browser would probably send the data similar to POST's format, with only a different header (UPDATE - 2008/03/11: yepp, it indeed works that way). Blah, possibilities, possibilities.

I'm using Agavi framework, by the way. It's a kick-ass lightweight framework. Agavi is beautiful, but the documentation is sparse. Documentation effort is underway, and I hope to see more of it in the coming weeks. Without it, Agavi is considered "fringe" at the moment (I like fringe software; I even use fringe operating systems such as *BSD hehe).