Category Archives: mysql

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.

04 Apr

points in an area in mysql

3am, daughter wide awake, not allowed to sleep – what’s a guy to do? Let’s do an experiment.

Let’s say we want to efficiently select all points in an area from a database. This has real-world applications – I’ll be using it in a geographical project very soon.

First, create a simple table in MySQL. I’ve created mine in a database called ‘geodb’.

CREATE TABLE points(x INT, y INT, INDEX(x), INDEX(y));

Note that x and y are both indexed.

Then, seed that table using some PHP (I had to up the max_execution_time on my laptop to 300 for this).

<?php
mysql_connect('localhost','username','password');
mysql_select_db('geodb');
for($i=0;$i<10000000;++$i){
  $x=rand(-1000000,1000000);
  $y=rand(-1000000,1000000);
  mysql_query("INSERT INTO points (x,y) VALUES ($x,$y)");
}

Ok. For the rest of the experiment, we’ll be trying various ways to extract the number of points within a radius of 100000 from (0,0). The goal is to have the lowest working time. Each method should return the exact same result.

First, from the console, do a straight select statement.

time echo "SELECT COUNT(x) FROM points WHERE SQRT(x*x+y*y)<100000" | mysql -uusername -ppassword geodb

Returned result 7993 in .414 seconds. Wow – pretty quick already… but, my daughter is still awake, so let’s continue.

We can improve this by avoiding the math on points that we are certain can not be in the area. For example, in a radius of 100000 from (0,0), and points with x<-100000, x>100000, y<-100000, y>100000 can definitely not be in the circle.

time echo "SELECT COUNT(x) FROM (SELECT x,y FROM points WHERE x>-100000 AND x<100000 AND y>-100000 AND y<100000) AS sub1 WHERE SQRT(x*x+y*y)<100000" | mysql -uusername -ppassword geodb

.326 seconds. Better. However, there are two calculations being performed on each value. Let’s reduce that.

time echo "SELECT COUNT(x) FROM (SELECT x,y FROM points WHERE ABS(x)<=100000 AND ABS(y)<=100000) AS sub1 WHERE SQRT(x*x+y*y)<100000" | mysql -uusername -ppassword geodb

.297 – more than 25% fster than the original.

It should be possible to reduce that further. For example, we can be certain that x,y values which are both less than (100000*Cos(Pi/4)) are contained inside the circle, so that’s another < comparison, reducing the number of maths operations. I’d test that one as well, but my daughter is finally asleep in my left arm as type.

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.

05 Aug

KFM 1.0 released

Today, I released KFM 1.0, a web-based file manager powered by php/ajax with mysql/postgresql/sqlite.

This is an important release for me, as it marks the completion of the original feature-set that I had in mind when I first started the project – match and exceed FCKeditor‘s default file manager, and provide extras such as multiple file upload and tags.

I was considering giving this a rest for a while before starting on version 2, which will have a lot more “desktop” tricks in it – different view modes, live streaming of videos, vector graphics, plugins – but I feel energised right now, so may just get to work on it.

ohloh considers this project to be worth about $889,277, and you get it for free, so please download it, try it, and comment about it.

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.

14 Dec

kfm 0.7 in beta

No versioned release zip yet, but I just finished the last of the features scheduled for this release. You can download via SVN using the details mentioned on the KFM site.

I’ll be announcing the string-freeze to the translators later today. We have one more language this time, Bulgarian. The official release will be in one week’s time. I need to give the translators time to do their work, and also, will spend that time looking through the code for bits that I can make more efficient.

New features for 0.7:

  • you can now upload a zipped archive of a few files, and extract the archive. this allows you to upload a load of files at the same time.
  • there were a lot of problems with SQLite in version 0.6. to help alleviate this problem, KFM now supports MySQL, Postgres and SQLite, using the MDB2 Pear library.
  • instead of returning links which point directly to the requested images/files, we now return a link which retrieves the requested file via KFM. this allows your file repository to be held outside the web root, and will allow file authentication and other tricks (logging, uri-based thumbs) in the future.
  • long directory names are now truncated similar to long file names.
  • “file upload”, and “copy from Internet” now use the same form.
  • lots of speed issues have been fixed.

enjoy. The main release will be next Tuesday. I’ll write up a quick article then detailing what features I think will be in 0.8, 0.9, and on up to 1.0.

If there are any problems using this beta, please mention them using the KFM forum.

In related news, Webworks, my great and glorious company will be using KFM in a very large project next year, which will mean a lot of work will be put into it. I am still committed to providing the improvements to the great unwashed, so you’ll all benefit from our hard work.

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.