optimise all tables in all mysql dbs on your server

A quick script to do so:

<?php
mysql_connect('localhost','root','YOURPASSWORD');
$alldbs=mysql_query("SHOW DATABASES");
while ($dbs = mysql_fetch_assoc($alldbs)) {
	foreach($dbs as $db_name){
		if($db_name=='information_schema' || $db_name=='mysql')continue;
		mysql_select_db($db_name);
		echo '<strong>database: '.$db_name.'</strong><br />';
		$alltables = mysql_query("SHOW TABLES");
		while ($table = mysql_fetch_assoc($alltables)) {
			foreach ($table as $db => $tablename) {
				echo 'optimising '.$tablename.'<br />';
				mysql_query("OPTIMIZE TABLE `".$tablename."`") or die(mysql_error());
			}
		}
	}
}

Based on this original script for optimising all tables in a specific database.

hosting multiple sites from the same CMS engine

I posted about webwork.ie‘s free website engine a few days ago. In the comments, Mickster asked how he would go about doing it himself.

I haven’t studied how other engines do it, but here’s how I do it.

First off, some benefits to sharing the CMS across separate sites:

  • Reduced resource usage. Because a single PHP engine is used, it is easier to optimise using RAM-disks, PHP caching, and other optimisations.
  • Easier upgrades – upgrade one system, upgrade them all!
  • Easier bug-fixing. One of the banes of my life is discovering a bug in a CMS that you know exists in at least ten other instances of that CMS. Now, you fix the bug in one place, and it’s fixed everywhere!

Convinced yet? Of course you are. Here’s how you do it.

First, separate all site-specific files from your engine files. To do this, you need to strictly keep your uploaded files and site-specific CSS in separate from your executable PHP. After doing this step, you should be able to clearly point out directories in your CMS and say “those are the engine’s executables and other resources, these are the design files and other uploadables which are site-specific.”

Second, all site-specific resources should be served through a script. The reason for this is that we are going to be moving the files away from the perceived directory that they’re in. Instead of, say, a web root of /home/webme/public_html/ with an image located at /home/webme/public_html/i/image.jpg, you might have the image located at /home/webme/sites/webme.eu/i/image.jpg, and another image from a different site located at /home/webme/sites/an-other-site.com/i/image.jpg. The choice of which image to show when /i/image.jpg is referenced depends then on the domain-name the browser requested the file from.

Then you need to over-ride the config at the time of its request. In the WebME CMS, the config file is located at /.private/config.php. What we want is to replace that with a “proxy” config file which, when included, checks the domain name and includes the real config file.

So how does the proxy config know where the real one is located? the way I handle this is to have a directory named after the site, located in /home/webme/sites/$NAME. If, for example, blah.webme.eu or www.blah.webme.eu is requested, you strip off any leading www. strings and trailing webme.eu strings with a regexp, and check to see if the config (/home/webme/sites/blah/config.php) exists. If so, include it.

To handle aliases, for example, if you wanted www.blah.com to load the blah.webme.eu site, you create a directory /home/webme/sites.aliases/$NAME, where $NAME (blah.com) is a symbolic link pointing to the correct directory.

It’s not a difficult trick, but it works.

Here’s my proxy config file:

Here is a copy of the proxy file that I use in webme.eu’s multi-user engine.

<?php
$host=preg_replace('/^www\.|\.webme.eu$/','',strtolower($_SERVER['HTTP_HOST']));
$cfile='/home/webme/sites/'.$host.'/config.php';
if(!file_exists($cfile)){
        $cfile='/home/webme/sites.aliases/'.$host.'/config.php';
        if(!file_exists($cfile)){
                header('Location: http://webme.eu/');
                exit;
        }
}
require $cfile;
define('CONFIG_FILE',$cfile);

webme-mu: create your own website

I’ve just finished the first version of the site creator. You can create your own website using this wizard.

This involved a bit of work on my part – I needed to separate personal files (images and other downloadables) from internal files (scripts and other resources), and ensure that personal files could be read from anywhere on the server, not necessarily from a web-accessible directory.

Then, I needed to write the site generator to write config files automatically.

Then, WebME needed to be hacked to load the correct config file, based on the domain name requested.

This has worked. I now have two sites running on that server (http://verens.webme.eu/ and http://webme.eu/), both of which use the same engine instance. The only things that are separate are their databases and their personal files.

The best thing about this was that it was possible to do this without compromising the structure of the downloadable version of WebME. The downloadable version and the multi-user version use exactly the same source code. The only difference is in the config file. In the case of the multi-user version, when the config file is loaded, it acts as a proxy, loading the correct site-specific config file.

After all that work, I need a drink! Please, give the site a chance, and report any problems you come across.

running Thief in Wine

thief1

WOOT! Finally got Thief: The Dark Project running in Linux

One of the reasons I finally removed my Windows partition was that it had become absolutely useless to me.

The only reasons I’ve used Windows at all in the last few years are to play games or to test something in IE. Absolutely nothing else. And these days, most of the games I like to play simply do not work on Windows anymore. Vista, for example, refused to play Thief 2, and I haven’t been able to play Thief 1 since XP. For IE, I have another machine in the office in work, so there’s absolutely no reason for me to run Windows.

So anyway, I removed the partition with the reasoning that if Windows can’t play the games, and Wine can, then Wine is better. Ironic, really.

Thief is one of those game series that is just so brilliant that you need to buy it a few times, because you keep lending your copy to someone else and forgetting exactly who (and they like it so much they simply “forget” to give it back). Dungeon Keeper is another brilliant series like that.

Thief finally works in Wine 1.1.12, which was freshly released via Yum on the Fedora network just today. This is great news! The game did not work in Wine 1.1.11, when I tested last week. This week it’s perfect.

I fired up my previously failing installation, and after a few changes, was able to get it running.

Christos, in the bug tracker, says he re-installed the game and codecs to get it working. I didn’t have to reinstall anything. The only changes I had to make are:

  • use winecfg to change the emulation to Win98, and the graphics to use a virtual desktop. My laptop is widescreen, and Thief starts in 640×480 mode – for some stupid reason, x.org chops off the bottom of the screen when you request 640×480, and makes it 640×400. This makes Thief unplayable, so you need to use a virtual desktop.
  • write a small script to run the game in single-CPU mode – I created a file bin/thief containing this:
    #!/bin/sh
    
    cd ~/.wine/drive_c/Games/Thief/
    schedtool -a 0x2 -e wine THIEF.EXE

That was it – when I run thief from the console I get to play the game in full glory. It’s even better than the first time because this time my machine vastly beats the minimum requirements!

aw man…. 7 things

I thought I’d escaped this one. Everyone in the PHP world appears to have been “tagged” by now. Ken passed the buck to me, though, so I guess I’d better play.

Hmm… 7 things that people don’t already know about me? Hard task…

  1. I used to be into piercings, and if the piercing was common, then I wasn’t into it. Mostly, I was into surface piercings, and had a number of them down my sides and arms. I had planned on building a platform for a watch which would then be “embedded” in the arm, but never got around to it.
  2. I used to do “side-show” tricks, and have performed them on stage. This includes tricks such as “skewer through face” (horizontal – never got around to learning vertical), “human blockhead”, “face in glass”, eating glass, fire-eating, fire-blowing, and other weird stuff. Each of these is surprisingly safe when you know the trick.
  3. My first computer was a Commodore Vic 20 which I got when I was possibly 7 or 8. The highlight of that machine was writing (in BASIC) an animation of a bird which flew across the screen and “pooped” on a tank. I didn’t really consider that as “programming”, though – just playing. That machine was followed a year or two later with a Spectrum 48k+, which is where I really started – with 3d wire-frame graphics and text adventures. My first “real” computer was an Amstrad PPC640, where I learned QBASIC. My first CGI experience was writing applications (forums, etc) in C. That was followed by Perl, and finally I started writing php in about 1999 or so. Now I’m secretary of the Irish PHP User’s Group. Yay me! Oh, and Go Linux! I’ve been using Linux as my main OS since the 20th century as well.
  4. I can play a number of instruments. I played violin (rather, fiddle) for my school in a number of Feis Ceoil events and still have an electric violin that I pick up every now and then. I played Bagpipes in two separate pipe bands in Dublin – anecdote: in school, a music teacher was evaluating our playing of the tin whistle, and when he came to me, he saw immediately from the way I was holding it that I had training in a “pipe” instrument (uillean pipes and bag pipes are fingered similarly). I’ve played guitar in a few bands and like to get on stage at the Monaghan Blues Jam whenever I can (last compliment I got at one of them was “I like your playing – it’s /clean/ – very 70s”). I am looking for a classical guitar teacher at the moment. I also play keyboards, and have written music before (example).
  5. I’ve been vegetarian for more than half my life. This is not because of any hippy feelings towards animals. The two main reasons are 1) it’s disgusting to put dead animals in your mouth, and 2) it’s illogical, inefficient and cruel to kill animals for your food when you really don’t need to. I have no problem with people eating meat as long as they have no problems with me (and my family) not eating meat.
  6. I believe that the universe doesn’t really exist, but is a sort of shared illusion (look, it’s complex, ok!). Basically, it works like this – if the equation 2x-2x=0 is true, then you might consider that ‘x’ is a “real” object even though the sum of the equation is ‘0’ (ie; from nothing, comes something). This maps onto reality if you swap ‘x’ with ‘particle’, and ‘-x’ with ‘anti-particle’. I am always coming across situations where physicists discover this is true. Hawking radiation, for example, is an example of energy/matter spontaneously bursting into existence,, and is an exact analogy because you end up with ‘x’ (the radiation coming out of the black hole) and ‘-x’ (the radiation going into the black hole). Oh, and if you’re thinking it’s mad to think that ‘x’ could be real… in a computer game, all characters are “real” to each other, but not to an outsider looking in. In our case, we are those characters, and in an equation, each element is one of those characters. In the context of the equation/universe, all parts are real relative to all other parts, even if from the outside, they’re not.
  7. I used to do contact juggling. In fact, I founded the biggest contact juggling website. My son, Jareth, is named after David Bowie’s character from The Labyrinth where the goblin king (with the help of Michael Moschen) does some contact juggling (called “crystal ball manipulation” in the credits). I even wrote a book on the subject.
  8. Everyone in the world appears to have been tagged for this already, so I won’t bother tagging anyone.
    edit: suppose I’d better…

    1. Conor Mac Aoidh – local chap. Bright and enthusiastic. I feel that he’ll be producing sterling work soon and will be a credit to the Irish PHP community.
    2. St̩phane Lambert Рtireless compiler of PHP Whitebook trivia, and host of a damned good neo-classical metal radio show.

    I can’t think of any other PHP developers that I know that haven’t already been tagged. Really! Now go away and read something else!

    Rules

    • Link your original tagger(s), and list these rules on your blog.
    • Share seven facts about yourself in the post – some random, some weird.
    • Tag seven people at the end of your post by leaving their names and the links to their blogs.
    • Let them know they’ve been tagged by leaving a comment on their blogs and/or Twitter

serving files through a script

One thing I need to do while building the multi-user version of webme is to convert it so file references such as /f/photos/an_image.jpg get transparently converted so they serve correctly, even though the actual file may be located somewhere entirely else.

While writing the code for that, I started with a much simpler idea – make sure that files such as /f/photos/an_image.jpg are served through a script, instead of by the web server. This allows you to do stuff such as:

  • User-based permissions on a per-file basis.
  • Load the file from outside the web-root. This has the added advantage that your users can upload potentially dangerous files such as PHP scripts, but the scripts will not be run because they are not accessible directly from a URL.
  • Log in a database that the file was downloaded.

So, there are two steps involved here – first you need to internally rewrite the URL from /f/photos/an_image.jpg to something like /common/get_file.php?filename=photos/an_image.jpg, and the second step is to write the /common/get_file.php script (well, in your case, it involves copy/paste. In mine, it involved writing it 😉 ).

In the root .htaccess file, add this:

RewriteEngine on
RewriteRule ^f/(.*)$ /common/get_file.php?filename=$1 [QSA,L]

And the get_file.php script is this:

<?php

require '../common.php';

if(!isset($_REQUEST['filename']))exit;
$file=BASEDIR.'f/'.$_REQUEST['filename'];
if(strpos($file,'..')!==false || strpos($file,'/.')!==false)exit;

if(!file_exists($file) || !is_file($file))exit;

$force_download=isset($_REQUEST['force_download']);

header('Content-Description: File Transfer');
if($force_download){
  header('Content-Type: application/octet-stream');
  header('Content-Disposition: attachment; filename='.basename($file));
}
else{
  header('Content-Type: '.get_mimetype(preg_replace('/.*\./','',$file)));
}
header('Content-Transfer-Encoding: binary');
if($force_download){
  header('Expires: 0');
  header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
  header('Pragma: public');
}
else{
  header('Cache-Control: max-age = 2592000');
  header('Expires-Active: On');
  header('Expires: Fri, 1 Jan 2500 01:01:01 GMT');
  header('Pragma:');
}
header('Content-Length: ' . filesize($file));
ob_clean();
flush();
readfile($file);

function get_mimetype($f) {
    $mimetypes = array('ez'=>'application/andrew-inset', 'hqx'=>'application/mac-binhex40', 'cpt'=>'application/mac-compactpro', 'doc'=>'application/msword', 'bin'=>'application/octet-stream', 'dms'=>'application/octet-stream', 'lha'=>'application/octet-stream', 'lzh'=>'application/octet-stream', 'exe'=>'application/octet-stream', 'class'=>'application/octet-stream', 'so'=>'application/octet-stream', 'dll'=>'application/octet-stream', 'oda'=>'application/oda', 'pdf'=>'application/pdf', 'ai'=>'application/postscript', 'eps'=>'application/postscript', 'ps'=>'application/postscript', 'smi'=>'application/smil', 'smil'=>'application/smil', 'mif'=>'application/vnd.mif', 'xls'=>'application/vnd.ms-excel', 'ppt'=>'application/vnd.ms-powerpoint', 'wbxml'=>'application/vnd.wap.wbxml', 'wmlc'=>'application/vnd.wap.wmlc', 'wmlsc'=>'application/vnd.wap.wmlscriptc', 'bcpio'=>'application/x-bcpio', 'vcd'=>'application/x-cdlink', 'pgn'=>'application/x-chess-pgn', 'cpio'=>'application/x-cpio', 'csh'=>'application/x-csh', 'dcr'=>'application/x-director', 'dir'=>'application/x-director', 'dxr'=>'application/x-director', 'dvi'=>'application/x-dvi', 'spl'=>'application/x-futuresplash', 'gtar'=>'application/x-gtar', 'hdf'=>'application/x-hdf', 'js'=>'application/x-javascript', 'skp'=>'application/x-koan', 'skd'=>'application/x-koan', 'skt'=>'application/x-koan', 'skm'=>'application/x-koan', 'latex'=>'application/x-latex', 'nc'=>'application/x-netcdf', 'cdf'=>'application/x-netcdf', 'sh'=>'application/x-sh', 'shar'=>'application/x-shar', 'swf'=>'application/x-shockwave-flash', 'sit'=>'application/x-stuffit', 'sv4cpio'=>'application/x-sv4cpio', 'sv4crc'=>'application/x-sv4crc', 'tar'=>'application/x-tar', 'tcl'=>'application/x-tcl', 'tex'=>'application/x-tex', 'texinfo'=>'application/x-texinfo', 'texi'=>'application/x-texinfo', 't'=>'application/x-troff', 'tr'=>'application/x-troff', 'roff'=>'application/x-troff', 'man'=>'application/x-troff-man', 'me'=>'application/x-troff-me', 'ms'=>'application/x-troff-ms', 'ustar'=>'application/x-ustar', 'src'=>'application/x-wais-source', 'xhtml'=>'application/xhtml+xml', 'xht'=>'application/xhtml+xml', 'zip'=>'application/zip', 'au'=>'audio/basic', 'snd'=>'audio/basic', 'mid'=>'audio/midi', 'midi'=>'audio/midi', 'kar'=>'audio/midi', 'mpga'=>'audio/mpeg', 'mp2'=>'audio/mpeg', 'mp3'=>'audio/mpeg', 'aif'=>'audio/x-aiff', 'aiff'=>'audio/x-aiff', 'aifc'=>'audio/x-aiff', 'm3u'=>'audio/x-mpegurl', 'ram'=>'audio/x-pn-realaudio', 'rm'=>'audio/x-pn-realaudio', 'rpm'=>'audio/x-pn-realaudio-plugin', 'ra'=>'audio/x-realaudio', 'wav'=>'audio/x-wav', 'pdb'=>'chemical/x-pdb', 'xyz'=>'chemical/x-xyz', 'bmp'=>'image/bmp', 'gif'=>'image/gif', 'ief'=>'image/ief', 'jpeg'=>'image/jpeg', 'jpg'=>'image/jpeg', 'jpe'=>'image/jpeg', 'png'=>'image/png', 'tiff'=>'image/tiff', 'tif'=>'image/tiff', 'djvu'=>'image/vnd.djvu', 'djv'=>'image/vnd.djvu', 'wbmp'=>'image/vnd.wap.wbmp', 'ras'=>'image/x-cmu-raster', 'pnm'=>'image/x-portable-anymap', 'pbm'=>'image/x-portable-bitmap', 'pgm'=>'image/x-portable-graymap', 'ppm'=>'image/x-portable-pixmap', 'rgb'=>'image/x-rgb', 'xbm'=>'image/x-xbitmap', 'xpm'=>'image/x-xpixmap', 'xwd'=>'image/x-xwindowdump', 'igs'=>'model/iges', 'iges'=>'model/iges', 'msh'=>'model/mesh', 'mesh'=>'model/mesh', 'silo'=>'model/mesh', 'wrl'=>'model/vrml', 'vrml'=>'model/vrml', 'css'=>'text/css', 'html'=>'text/html', 'htm'=>'text/html', 'asc'=>'text/plain', 'txt'=>'text/plain', 'rtx'=>'text/richtext', 'rtf'=>'text/rtf', 'sgml'=>'text/sgml', 'sgm'=>'text/sgml', 'tsv'=>'text/tab-separated-values', 'wml'=>'text/vnd.wap.wml', 'wmls'=>'text/vnd.wap.wmlscript', 'etx'=>'text/x-setext', 'xsl'=>'text/xml', 'xml'=>'text/xml', 'mpeg'=>'video/mpeg', 'mpg'=>'video/mpeg', 'mpe'=>'video/mpeg', 'qt'=>'video/quicktime', 'mov'=>'video/quicktime', 'mxu'=>'video/vnd.mpegurl', 'avi'=>'video/x-msvideo', 'movie'=>'video/x-sgi-movie', 'ice'=>'x-conference/x-cooltalk');
    $extension = preg_replace('/.*\./', '', $f);
    if (isset($mimetypes[$extension]))return $mimetypes[$extension];
    return 'unknown/mimetype';
}

The only bit you might want to change above is to remove the require '../common.php';, and replace BASEDIR with your own DOCUMENT_ROOT address (with a trailing ‘/’).

As an added bonus, if you add a ?force_download to the address of the file you want to download, then it will be forced into a download, instead of being handled by the browser. For example, /f/photos/an_image.jpg?force_download.

Right. That’s enough explanation. I’ve got work to do.

webme.eu, WebMEµ

Morning all!

The WebME CMS has a new home, at webme.eu. You can download the CMS from the Downloads page there.

Tonight, I should have the first version of the site-builder ready. I’m calling it WebMEµ (webme-mu; web management engine – multi-user), inspired by Donncha’s http://mu.wordpress.org/ project. This will allow a load of separate CMSes to run from the same engine. Donncha – I love the feature list – #5 is a corker! (Ambiguity about how to pronounce its name).

webme: themes, package and new site

screen2-website-front-page

OK – finally, there is something that you can download as a package, with a theme an’ all!

You can get the package from the WebME downloads page.

See what I did there? I linked to another site – that site is the new home of Webworks WebME, until we come up with a better domain name. Note that the webme.verens.com website is itself running with a copy of the CMS. Eating my own dog food, dontchaknow!

I’ve added the ability to choose which theme to use for the site. You can do this very simply – download a new theme (or create one yourself) from the downloads page, and unzip it in /ww.skins/. Then in the admin area, go to users and admins > site options and click the theme you want to use. Couldn’t be simpler!

Also, I’ve added in some search functionality. If the site detects a search GET request, and no page name is selected, then a search results page is shown. If there is no existing search results page already then one is generated automatically and added to the database (and hidden from navigation).

I’ve been through the installation process a few times now, and I think I’ve ironed out the obvious problems. Please give this a try. I’m going to take a break, fix a Linux problem for a friend, then work on the new multi-user version of WebME that I mentioned yesterday.

webme: admins, frontend editing, KFM, and comments

I’ve done a bit more updating of the WebME code.

I realised I’d forgotten to add some code to create the first admin. That’s fixed now – just go to the admin area. If there are no existing admins, then the first attempt to login will create a valid user account.

You can insert images and stuff now through the FCKeditor in the Pages section. The file manager used is KFM.

When you create/edit a page, see the Advanced tab, where there are a number of options. You can set a page to be hidden from the navigation menu, you can set any page to be the front page, and you can also set a page to allow public comments.

The public comments thing is interesting, I think – I wrote it using a combination of AJAX and some verification tricks. On all the sites that it exists, I have not seen a single spam comment (but now that the challenge is out there…).

If you have logged in as someone who has Admin rights, then you can edit pages directly from the front end just by right-clicking on the page. Choose “Edit Page” and you’ll be able to write directly into the page. Right-click outside the editable area when you’re done, and choose “Save Changes and Reload Page”.

As usual, you can grab the code here: Webworks WebME SVN checkout. I think we’re just about at the point where a proper package will be useful. That will hopefully be provided tomorrow, depending on time.

And now for some news – I’ve been discussing making this into a free service with John (webworks‘ head honcho), and we’re going to go ahead with it. The idea is that you will create your own website on our hosting platform, get some basic services free, and will pay for extras if you want them (domain names, SMS services, etc), but there will be /no/ pressure – if you just want a simple website with an easy-to-use CMS, then that will be available for free. The great thing about this is that the system will be upgraded very often, with new services coming online as they’re finished, and you, as users, will have input into what gets worked on.

webme frontend working

I spent most of the day reconciling the fork that yesterday’s work created, then started work on the front end.

The original version of WebME uses a home-grown templating system, but Smarty is better than my version and is better maintained, and has more users, so today’s work was about trying to convert WebME to start using Smarty instead of my templating solution.

The biggest work was in trying to abstract away all the code that handles anything to do with templates. While that work is not yet complete, it’s at a point where it will at least display something on the screen now.

As Conor pointed out yesterday, some Pear libraries are required to get this all working. I’ve still to add a verification script to the installer, but for now, those requirements are: mdb2, mdb2_driver_mysql, net_useragent_detect. On a Fedora system, the following RPMs should also be installed: mysql-server pear php glibc-common glibc php-gd php-pear php-pdo php-mysql ImageMagick. The minimum PHP version should be 5.2.3 (for json_encode support and other modern niceties)

So yeah. At the moment, there is a default template in place. This template shows a very simple style.

As of now, webme is an actual working CMS. you can create pages, and those pages will be shown on the front end. Obviously, it is not very polished yet, but that will come over the next few weeks as I bring it closer to completion.

The next article should probably be on how you can create your own templates. As always, you can download the application here. A packaged version will be available soon, once it looks a bit more polished and I’ve cleaned up the admin area to not have areas which are not actually yet available for playing with.