March 2010

Exporting a MySQL Database Schema as XML

Dumping a database schema is a an often required quick task. This script will read the schema from a MySQL database, and output it XML to describe it.


<?php
// Define a few constants
define("DB_SERVER", "localhost");
define("DB_USER", "root");
define("DB_PASS", "password");
define("DB_NAME", "example");

// Lets connect to the db
$dbhandle = mysql_connect(DB_SERVER, DB_USER, DB_PASS)
or die("Unable to connect to MySQL");

// Choose a database to work with
$selected = mysql_select_db(DB_NAME, $dbhandle)
or die("Could not select examples");
// Return all of the available tables
$result_tbl = mysql_query( "SHOW TABLES FROM ".DB_NAME, $dbhandle );

$tables = array();
while ($row = mysql_fetch_row($result_tbl)) {
$tables[] = $row[0];
}

$output = "<?xml version=\"1.0\" ?>\n";
$output .= "<schema>";

// Now iterate through each table and return the fields
foreach ( $tables as $table ) {
$output .= "<table name=\"$table\">";
$result_fld = mysql_query( "SHOW FIELDS FROM ".$table, $dbhandle );

while( $row1 = mysql_fetch_row($result_fld) ) {
$output .= "<field name=\"$row1[0]\" type=\"$row1[1]\"";
$output .= ($row1[3] == "PRI") ? " primary_key=\"yes\" />" : " />";
}

$output .= "</table>";
}

$output .= "</schema>";

// Notify the browser the type of file being dealt with
header("Content-type: text/xml");
// Display the XML to describe the schema
echo $output;

// Dont forget to close the connection
mysql_close($dbhandle);
?>

Remember, this is an example, and real world code should escape characters, follow best security practices etc. Proof of concept code here, – not a snippet to put live.

Exporting a MySQL Database Schema as XML Read More »

WordPress Custom RSS feeds

Wordpress Logo

Adding custom content to your RSS feed is actually a fairly simple process. You may wish to do this to add a copyright notice, a link to social media profile, twitter account, or perhaps even sell advertising.
Basic usage looks like this:

function rssModification ($contentToFilter) {
// Manipulate the content as required
return $contentToFilter;
}add_filter('filterName', 'rssModification ');

There are lots of filters available and obviously an extensive list here. In this example we are going to use the the_excerpt_rss and the_content_rss filters.

When the filter is executed the data to be modified is passed to the specified function. The content can then be altered and then returned.


function rssContent($content) {
$content = $content . '<a href="http://twitter.com/booleanvalue/">Follow me on Twitter!</a>';
return $content;
}
add_filter('the_excerpt_rss', 'rssContent');
add_filter('the_content_rss', 'rssContent');

WordPress Custom RSS feeds Read More »

Building scalable web applications

Scalability is a desirable property of a system, a network, or a process, which indicates its ability to either handle growing amounts of work in a graceful manner or to be readily enlarged. (Wikipedia)

In order to achieve good scalability an application has to be designed with scalability in mind. Every application requires its own level of scalability and performance, and this should ideally be addressed during functional and technical design specification.

During this design phase there are a number of things to consider, – especially performance. Higher performance generally means more requests per second and less intensive operations; good caching is vital (think Smarty, ZendPlatform, memcached etc)

Where possible try to build software that is ‘loosely coupled’, – that way if it is required in the future it will be possible to serve different components from different servers. Often application bottlenecks occur in a small part of the application so with loosely coupled software it is easier to relocate that component to dedicated hardware.

Infrastructure architecture must support the application architecture. PHP web server clusters require a solution for sharing sessions between servers. These servers can have their own local cache or they could use a shared caching mechanism. Building on a SOA architecture can help spread load across multiple servers.

Designing a database includes thinking about scalability and performance. While replication is a good way to spread read queries across multiple servers it doesn’t help when you have lots of writes. Data partitioning is a valuable consideration; for example each group of database servers can serve part of your database. This allows you to spread writes to different groups of servers, however obviously adds to the complexity of partitioning and fetching data.

During the development process ensure that you develop with a database that reflects the real thing. Try to populate your database with content before you start developing as a database with just a couple of rows behaves different to one with millions of rows.

Building scalable web applications Read More »