Category: MySQL

The MySQL RDBMS

Development

New Project: SPDO

I have just posted the (embarrassingly empty) page for my new pet project: SPDO (Sjan’s PDO) – in two flavors: PHP and Python.

There are only about a thousand PDOs out there, and perhaps a dozen or so of them are really functional and (in a few cases) very polished pieces of work. So why am I messing with writing my own? A couple of reasons:

  1. I like to have coding signatures that are consistent, whether I am working with PostgreSQL, MySQL or (heaven help us all) SQLite.
  2. I like to have coding signatures that are (reasonably) consistent across different languages – in this case Python and PHP.
  3. I wanted to take advantage of Prepared Statements where I could, even though the PHP implementations of those are pretty weak (especially in the case of MySQL).

Currently implemented in

  • Python:
    • PostgreSQL (with prepared statements)
    • SQLite (no prepared statements).
  • PHP
    • PostgreSQL (with prepared statements)
    • MySQL (with prepared statements)
    • SQLite (no prepared statements)

Here’s an example of how they work (in most simplistic terms):

#in Python
from pyDB import *
db = pyDB('mysite')
newid = db.insert('INSERT INTO test (name, value) VALUES (?,?)',['foo','bar'])
update_count = db.update('UPDATE test SET value=? WHERE id=?',['baz',newid])
results = db.select('SELECT * FROM test')
for row in results:
    for i in row:
        print "\t", i,"\t", row[i]
delete_count = db.delete('DELETE FROM test WHERE id=?',[newid])
//in PHP
require_once('phpdb.php');
$db = new phpDB('test');
$newid = $db->insert('INSERT INTO test (name, value) VALUES (?,?)',array('foo','bar'));
$update_count = $db->update('UPDATE test SET value=? WHERE id=?',array('baz',newid));
$results = db->select('SELECT * FROM test');
foreach($results as $row)
{
    foreach ($row as $key=>$val)
    {
        print "\t$key\t$val";
    }
}
$delete_count = $db->delete('DELETE FROM test WHERE id=?',array($newid));

The page with links to the code is in the list up top, and everything is MIT license. Enjoy.

Database

Drizzle – Lightweight DB Based on MySQL

Brain Acker, director of architecture for MySQL has opened the door to Drizzle, a light-weight, high-concurrency database server based on MySQL, targeted at web applications.

The architectural ideas as described in the FAQ:

A micro-kernel that we then extend to add what we need (all additions come through interfaces that can be compiled/loaded in as needed). The target for the project is web infrastructure backend and cloud components.

The FAQ goes on explain the differences between Drizzle and MySQL which include:

No modes, views, triggers, prepared statements, stored procedures, query cache, data conversion inserts, ACL. Fewer data types.  Less engines, less code. Assume the primary engine is transactional.

Michael Widenius, founder and original MySQL developer, explains more about Drizzle in this blog post, including the most interesting piece (to me) – that Drizzle will always contain the most up-to-date InnoDB code, meaning you don’t need to wait around for MySQL 6 or download the plugins from Oracle each year to get the latest and greatest.

While Drizzle is still in development you can check out the code and try it out. Or maybe even get involved and help out. More information on how to do both is available on the wiki.

Apache

Apache 2.2.6, PHP 5.2.4 and MySQL 5.0.45 on OS X

I got tired of looking for a way to replace the Apache/PHP that Apple packages with OS X (without breaking anything else in the process) so I decided to install Apache 2.2 and PHP5 in their own location to avoid stepping on the Apple package toes.

Since I do a great deal of development again MySQL I needed to install that as well, and figured that I would probably need the GD functionality as well so I grabbed libjpeg and libpng to make those work as well. This is the step-by-step.

(Props to James Pelow and his article from last year, from which I borrowed the configure command lines and configuration modifications, as well as the idea of installing the whole mess in /apache2.)

Download the latest MySQL (I used the package version) from MySQL.

Installation is straightforward following the same methods as any other Mac installer.

Download and install libjpeg and libpng – from Ethan Tira-Thompson (this is also in a Mac installer which contains both libraries in one installer).

Download the latest Apache httpd server (Unix source) from Apache

in the terminal:

tar -xzvf httpd-2.2.6.tar.gz && cd httpd-2.2.6

./configure

--prefix=/apache2

--enable-module=most

--enable-shared=max

make

sudo make install

sudo mkdir /apache2/php

Download the latest PHP from PHP

tar -xzvf php-5.2.4.tar.gz && cd php-5.2.4

./configure

--prefix=/apache2/php

--with-zlib

--with-xml

--with-ldap=/usr

--enable-cli

--with-zlib-dir=/usr

--enable-exif

--enable-ftp

--enable-mbstring

--enable-mbregex

--enable-dbx

--enable-sockets

--with-iodbc=/usr

--with-curl=/usr

--with-mysql=/usr/local/mysql

--with-gd

--with-jpeg-dir=/usr/local

--with-png-dir=/usr/local
--with-apxs2=/apache2/bin/apxsmake

sudo make install

sudo cp php.ini-dist /apache2/php/lib/php.ini

Now to make your Apache2.2 a little more ‘Mac’ – you can point it at the Mac web shared files folder, change the user and group and change the location for user files to match the Mac folder system.

Edit httpd.conf (I use nano, you can use any flat text editor like nano, pico, vi, emacs or even BBedit)

sudo nano -w /apache2/conf/httpd.conf

The changes to httpd.conf I made:
I changed

User daemon
Group daemon

to

User www
Group www

and

DocumentRoot "/apache2/htdocs"

to

DocumentRoot "/Library/WebServer/Documents"

and

<Directory "/apache2/htdocs">

to

<Directory "/Library/WebServer/Documents">

and added

AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

DirectoryIndex index.html index.php

Edit httpd-userdir.conf

sudo nano -w /apache2/conf/extra/httpd-userdir.conf

The changes to httpd-userdir.conf I made:
I changed

UserDir public_html

to

UserDir Sites

To start and stop the server:
MySQL comes with a Preference Pane that allows you to start and stop it there. To start and stop Apache you need to first make sure that the default Apache shipped with OS X is stopped.

sudo /apache2/bin/apachectl start
sudo /apache2/bin/apachectl stop

I only ran into one issue, when trying to start the server I ran against the following error message (and no running server, of course):

httpd: Syntax error on line 53 of /apache2/conf/httpd.conf:
Cannot load /apache2/modules/libphp5.so into server:
Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient.15.dylib
Referenced from: /apache2/modules/libphp5.son  Reason: image not found

To fix this I did the following:

cd /usr/local/mysql/lib
sudo mkdir /usr/local/mysql/lib/mysql

for i in `ls ./l*`; do sudo ln -sf /usr/local/mysql/lib/$i /usr/local/mysql/lib/mysql/$i; done

This creates soft links in the directory that libphp5.so is looking for the MySQL libraries.

Then it started right up! Wheee! (I did a quick test by dropping PhpMyAdmin into the /Library/WebServer/Documents folder and browsed to it – the whole Apache/PHP/MySQL is working correctly)

Technorati Tags: , , ,

Read More

Best Practices

A simple intro to database normalization

I found a very clear, well-written introductory example to database normalization on devshed. Although it is in the MySQL portion of the site, it applies equally well across the board to other RDBMSs.

To get more details on normalization, the normal forms, and general good database development in general, check out Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design, Second Edition by Michael J. Hernandez. Without a doubt the most useful db development book I’ve ever laid my hands on.