25 Jan

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.

18 Jan

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.

04 Oct

KFM work restarted, translation plans

After a long break, I’m back working on KFM. I’ve done an average of maybe 2 hours every day this week, cleaning up code and preparing for future plans.

What I’m working on at the moment is a rewrite of the translations code. There are a few things wrong with the way that it works at the moment:

  • JavaScript and PHP language strings are currently separate – en.php and en.js for example.
  • Whenever KFM is loaded, the entire JS language file is loaded, even if very little of it will actually be used.
  • There is no easy way apart from hacking KFM to add translations for user-contributed plugins.

The solution we (Benjamin and myself) have come up with is to use a database solution, with an import facility for plugins. The idea is a complete rewrite of how we do it.

Currently, translations are recorded in a JS object something like this:

kfm.lang={
  Errors                  : "Ошибки",
  LastModified            : "последний раз изменен"
};

The above is easy to use in KFM, in that the strings can be accessed using normal object notation, kfm.lang.Errors for example. However, it should be easier.

What we will be doing is to convert all language files so they are written using the English string as the key instead of a code such as “LastModified“. And, using the common nomenclature of other languages, the translation will be done such as: alert(__("What do you want to rename %1 to?","default",filename));, where the second parameter is the language context (are we talking about files, people, dogs, etc?).

To help improve the speed at which KFM opens, only the most popular strings will be preloaded. If an unknown string is requested, it will be loaded through AJAX.

How that will work is that all translations will be asynchronous. When you request a translation, a span element will be returned containing the translation. If the translation is already known or cached by the client, then the element will be pre-filled. Otherwise, an AJAX request will be fired off, and when it returns, the element will be populated.

The popularity of the strings will be determined by the number of requests for it.

On the server side, when a request comes in, KFM will search its database looking for the string. If it is found, it is returned (and its ‘requests’ field incremented). If it is not found, then all installed plugins will be searched for language files, which will be imported into the database then returned. If the string is not found anywhere, then the plain English is recorded in the database, and a note is added that the translation was not found.

I’m considering whether or not to add in a bit which periodically reports back to the main KFM server a list of missing translations. Also, a periodic report of popular strings and even of unused strings, allowing us to prune the files for future releases.

If you want to try out the work, then download a copy of the trunk via SVN: svn checkout http://kfm.googlecode.com/svn/trunk/trunk/ kfm

03 Oct

case-insensitive authentication in postfix-mysql

Just a quick note. If you find that your customers can log into their postfix account ok, but can’t read their email, check the directory their email is in:

[root@postfix /]# cd /home/vmail/domainname.ie/
[root@postfix domainname.ie]# ls -l
drwx------  5 postfix postfix 4096 Sep  4 11:52 joe.smith
drwx------  9 postfix postfix 4096 Sep  9 09:30 jane.smyth
drwx------  9 postfix postfix 4096 Sep  9 21:09 Jane.Smyth

The problem appears to be that postfix authenticates using MySQL, which is case-insensitive by default, then creates a default email skeleton directory named after the login username if it doesn’t find one already.

You can see in the above example that our user has logged in using uppercase letters in the username, MySQL verified the user had a right to be there, then postfix created the user’s directory using the login credentials, even though a lower-case version of the directory name already existed…

Obviously, this incorrect account will not receive email – email will be sent to the correct one.

There are a few solutions:

  • you can remove the Jane.Smyth directory and tell the user to change their authentication to use a lowercase username.
  • you can remove the Jane.Smyth directory then create a symbolic link from jane.smyth to Jane.Smyth.
  • change your MySQL installation to use a case-sensitive collation.

Personally, i go for the first solution.

07 Jun

book review: Learning PHP Data Objects

Overview: Learning PHP Data Objects, by Dennis Popel, is an introduction to PDO, which walks through the building of a believable test example – a library manager for your home library. Each chapter introduces a new facet of PDO and shows how to rewrite the appropriate parts of the application to slot the new ideas in. Very clear and easy to read. Non-PDO subjects are appropriately kept to the appendices.

I really couldn’t find very much about this book that I didn’t like. Ignoring the appendices, the book is 154 pages purely devoted to teaching PDO through examples, including error handling, working with BLOBs, even the creation of the M in MVC (Models).

I mentioned MVC there. One of my gripes with most tutorials of MVC is that they introduce the concept simply, then provide pages and pages of code with the end product which is “hello world”. Why I should go to all that trouble instead of simply writing <php echo 'hello world'; ?> to the screen usually escapes me. Dennis, however, concentrates solely on the Model and shows exactly why it’s a great idea. I think some more separation of concerns would have been better (don’t mix Author and Book SQL in the same object, for example), but the ideas were all good.

I think that if Dennis was going to show how the Model works, he should also have gone a little further and showed an example of an Active Record pattern as well. But I guess the point of showing MVC was more to show /an/ example of abstraction of the DB code, and that was sufficient.

The book covers a Library manager application all the way through from conception to implementation, demonstrating at all points that the code works with SQLite and MySQL (and by implication, all other DBMS’s) with a change of only the connection string.

Possible problems are explained clearly and solutions are provided. For example, Dennis explains why, after you compile the query select * from books, PDO (and indeed the database itself) does not know how many rows it will return. A solution, in the form of a very smart getRowCount() function shows a query-agnostic method for counting results of an arbitrary line of SQL.

Other areas that are covered in the book include error-handling, prepared statements and transaction-handling.

PDO can handle Prepared Statements even if the underlying DBMS cannot handle it, so it is possible to write your code in a cross-platform way. Examples of why you should use this are provided. One of the examples shows an efficient way to handle insertion or updating of a table using the same parameters for both cases, with the row-handling function deciding whether to use update or insert based on whether an ID was provided.

I feel the Transactions section could have been expanded a bit further. It is not explained how PDO handles this for DBMS’s that don’t internally support transactions, and I wouldn’t like to assume that they work all the time, only to find after deleting critical data that it’s not supported.

Overall, I enjoyed reading this book. Dennis is a good writer and I think he explained his thoughts very clearly.

On an aside, my four-year-old son Jareth loves Packt Publishing‘s books. Sometimes when I go to read another chapter, I need to covertly steal the book I’m reading back from him. For a while, he made it a bed-time ritual to grab all the Packt books he could find around and bring them up with him to read in bed. I think he loved the screen-shots and the frequent code samples. He’s high-functioning autistic and likes literary constructs, and programming books are perfect for him in that regard. Thanks Packt, you’ve made my son (and therefore me) happy.

26 Apr

review: Mastering phpMyAdmin 2.11 for Effective MySQL Management

Packt publishing emailed me a week ago and asked me if I was interested in writing a review of a book on phpMyAdmin.

In short, my review is this: overall, the book detailed everything I can think of (and more) about phpMyAdmin – I learned about some features in phpMyAdmin that I was not already aware of. I would ask for less screenshots, more how-tos and less “we/our” speech for the next revision, but if there are any questions I have about phpMyAdmin, the answers are in this book.

I use phpMyAdmin only occasionally, when I can’t get direct access to the database in question (I’m a console jockey – there’s nothing like the thrill of tappity-tapping away, knowing that you’re doing Important Stuff and …well, yeah – geek).

I’ve used phpMyAdmin since forever, so was familiar with its common uses – importing/exporting databases, running simple queries, etc. I was interested to see if there was anything that I might have missed. And who better to show me than one of the developers, Marc Delisle.

The table of contents was large, spread over 12 pages. I feel it would have been easier to read if there was one page detailing just h1 listings, followed by 2-3 pages of h2 listings. The h3 and h4 listings could have been shown at the beginning of the concerned chapters.

Disregarding that, scanning the contents showed some features that I was not aware of. Did you know that phpMyAdmin has a built-in relations manager? Even if the engine used is MyISAM, which does not manage referential integrity, phpMyAdmin will protect you and manage it for you if queries are run through it.

Screenshots are provided for everything. This makes some things much easier to understand. Sometimes it made interesting features stand out even more. Did you know that phpMyAdmin can generate PDFs of the relational schema of a database? Some screenshots in chapter 15 show examples of this feature.

Marc obviously knows his stuff. As a major contributor to phpMyAdmin since the early days, he is an ideal person to talk us through configuration details and the various tools that phpMyAdmin offers.

There are, however, a number of things which detract from the readability of the book.

This is the first edition for this title, so the following are probably “teething” problems for the author. However, I think they should be taken care of for the second edition, to make the book easier to read. Although, the second edition would most likely have a different title (why is the version number in the title?)

The book is written from a 1st-person-plural point of view – now we do this, now we do that, our table, our server. This distracted and annoyed me while reading the book.

It is too detailed in places, at the expense of readability. For example, chapter one goes through a blow-by-blow account of how phpMyAdmin came to be, with version numbers and even dates! I feel this should have been left to an appendix. The first chapter of any book should introduce a reader gently to the subject; not pummel the reader with historical facts.

The author sometimes forgets that the reader does not know the subject and its community’s habits. As an example, on page 28 the configuration value “PmaAbsoluteUri” is described, but afterwards, I was not sure a) what “Pma” is, or b) what the variable was supposed to do (the book described how it should be configured, but not what it was supposed to do). It isn’t until page 33 that the book pointed out that “pma” is an acronym for “phpMyAdmin” used by the community. I don’t feel stupid for not getting this immediately, as there is an awkward mixture of letter-cases going on there. By rights, “Pma” should stand for “Phpmyadmin”, not “phpMyAdmin”.

The sheer number of screenshots was a bit confusing. I feel that screenshots should only be used where the subject is not clear from the text. Two images on every page (almost) has the effect of breaking your concentration when you’re trying to read.

The book exclusively deals with how to use phpMyAdmin, but I think there should have been more about how to deal with MySQL itself, using phpMyAdmin. For example, chapter 17 details phpMyAdmin’s coverage of character sets and collations. As a person who knows the pain of upgrading a database from MySQL 4 to MySQL 5 while switching character sets, I was looking forward to seeing how this case is handled (which I am sure is pretty common in one form or another). However, the chapter describes the problems with character sets and does not offer any solutions to those problems.

Overall, the book was detailed, and I learned a lot of new stuff from it. I’d recommend it for administrators that are not comfortable with CLI MySQL and want a reference for the best web-based MySQL administration tool out there.

22 Aug

online schema designer

check this out! – pointed out to me by the ever-zestful Liam Robertson.

Although this is called “SQL Designer”, it looks like its purpose is actually just for the design of database schemas.

Still – an impressive script!

Exports SQL in a number of formats – MySQL, MSSQL, PostGres, etc.

09 Feb

KFM 0.8

function getKfmFile(version){
window.SetUrl=function(value){
value=value.replace(/[a-z]*:\/\/[^\/]*/,”);
document.getElementById(‘filename’).value=value;
}
var lang=document.getElementById(‘lang’).value
window.open(‘http://kfm.verens.com/demo/0.8/?lang=’+lang,’kfm’,’modal,width=700,height=500′);
}

Demo English
Bulgarian
Danish
German

Spanish
Finnish
French
Irish
Italian
Dutch
Romanian
Russian

Browse…

download it at the KFM website

New Features

  • New language: Romanian (thanks to Andrei Suscov)
  • Return thumbnails to the opener (35, 142)
  • Tag engine (12, 13, 143), allowing files to be categorised, and searched by category.
  • Client-side syntax highlighted text editing (125, 126) using CodePress.
  • Rename multiple files (103). This allows you to select a group of files, and rename them all to a common format.

Improvements

  • Multi-line captions (89)
  • ImageMagick used where possible (152, 111), allowing huge images to be edited without PHP running out of memory.
  • KFM will return either a normal URL (155), or a URL which allows the get.php script to be used to retrieve the file. The advantage to using get.php is that in the future, we will be able to apply authentication, so only authorised users can read a file. This also allows thumbnails to be built on-the-fly.
  • The database tables can use a specified prefix now (129).
  • Double-click can be configured to either send the file back to the opener (FCKeditor, your CMS, etc), or to open the file directly (120).
  • config.php split into two files, allowing easier upgrades (146).
  • when moving through files with the arrow keys, the selected file will always be visible (110).
  • KFM can now be used to manage multiple sites through one instance (154).
  • A metrics logger has been added (134). This will allow us to know what versions of KFM are in use, and whether it is safe to deprecate certain features if need be.
  • Comments can now be extracted from .gif files to be used as captions (88).
  • Documentation written on how to transfer a KFM archive from one machine to another (161).
  • Many bug fixes (159, 97, 156, 149, 68, 157, 147, 148, 144, 102, 145, 118, 140, 141, 98, 139, 135, 150, 151, 165, 167, 164, 162, 160, 113, 163).
  • Some ideas were rejected (112, 21, 131, 19, 18).

As usual, this release has been helped along by the many testers in the forum, testers who have contacted me by email, and all of the translators.

Very large thanks to Benjamin Ter Kuile, who has been very actively hacking away at the KFM code and produced such gems as the CodePress integration.

22 Dec

KFM 0.7

demo, download (828k .tbz2, 1.1M .zip)

New Features

  • New Languages
    • Bulgarian, thanks to Tondy (tondy.com)
  • Unzip zipped files (84). This allows users to zip up multiple files offline, upload them as one file, and unzip once they are uploaded.
  • Multiple Databases (127, 122). We now support PostGreSQL, MySQL and SQLite.

Improvements

  • Files may be located anywhere on the system at all. They do not need to be within a web-readable area (33)
  • bugfixes (117, 100)
  • Long directory names are now truncated, using the same method as long filenames (80)
  • Directories with many files are now displayed quicker (106)
  • Download From Url has been combined with File Upload (108)
  • KFM has been tested and is known to work on PHP4.3+ and PHP5.1+

As usual, this release has been helped along by the many testers in the forum, testers who have contacted me by email, and all of the translators.

Development for version 0.7 was sponsored by the infinitely glorious web development company, Webworks.ie. We’re really quite good.

15 Nov

mysql modes

I just found out about MySQL “SQL modes”. This allows you to now turn off some of the features that make MySQL so easy to use.

Why would you do that? For the same reason that in Perl, you should use strict, and in PHP, turn off all global variables and GPC variables and turn on all warnings.

In other words, you should lock down your MySQL to not allow any sloppy work. This will train you to write correct code in the future.

What I will be doing is to lock down my home machine to the strictest possible setting, then work on getting my company‘s CMS working with it. Once that’s done, I’ll gradually lock down our development server (can’t just do it immediately, as there are always at least 15 sites in constant development, not all of which I’ve fixed up yet).

What I will /not/ be doing is locking down our production server, for at least a few years! With hundreds of sites of different CMS design, it is not predictable what will work smoothly and what will explode.