On his blog today Sameer Borate has a new post looking at using Doctrine DBAL to make schemas rather than having to make them by hand each time (can be very useful for reloads with fixtures).

A tedious task during web development is that of database schema creation. A schema containing a few tables comprising of a small set of rows is quick, while that containing dozens of tables and large numbers of columns is a tedious process. I usually resort to a small php script with some regular expression tossed in to automatically create a schema from a text file definition. But that is a little buggy as I've to manually add the indexes and other small things. Now that Doctrine has released a DBAL library, this will provide a nice ability to automatically create sql schemas.

He introduces the DBAL abstraction layer and includes a basic script to create a schema for a MySQL database, manually adding the columns and setting up things like primary keys and foreign key constraints. He also includes the SQL statements that it will generate and execute on your Doctrine-based connection.

In this new post to his blog today Gonzalo Ayuso looks at how he's set up a system that uses PHP to synchronize their PostgreSQL databases' schemas when things changed.

We create source code at development server and push the changes to production. It's really easy to keep synchronized all our code. But with databases it's different. [...] It's a recurrent problem working with databases. We create database objects (tables, views, ..) in the development server and when our application is ready to go live we push the changes to production server. If we are smart developers we save all database scripts in a file and when we deploy them to production we execute the script.

He mentions tools like dbdeploy and phing to help make these migrations a bit more automatic. He needed something a little different though - a command-line script that would, based on an ini file, sync two or more databases. He's created the basic script that includes the actions to show the differences between the databases, a summary of the differences and an execution method to bring them into sync. He gives a basic example of how it would handle the sync between his example production and development databases.

Rob Allen has a new post to his blog today that looks at database migrations in Zend Framework applications. He introduces a component of his own - Akrabat_Db_Schema_Manager - to handle the migrations.

It is intended that any time you want to make a change to your database schema (add columns, tables, indexes, etc), then you create a new migration file. [...] The migration file contains a class that extends Akrabat_Db_Schema_AbstractChange and must contain two methods: up() and down(). It follows that up() is called when implementing the changes in this migration and down() is called to put the database back where it was if the change is backed out.

He includes an example of a migration file, one that defines the "up" method to create a table and the "down" to remove it. The Akrabat_Db_Schema_Manager is what your script would interface with by calling the "updateTo" method and change your database's structure. It can even hook into Zend_Tool to make it command-line friendly.

Chris Hartjes has posted the second part of his look at converting legacy applications over to a more structured CakePHP environment. In this new post he looks at working with the database schema.

Now you've decided to convert your legacy app over to CakePHP, you will run into the first serious obstacle: your database schema. To put it bluntly, if your schema does not already account for relationships between multiple tables you are screwed. Given that CakePHP is good at generating the queries you need to pull related records in for you, you NEED that schema to contain relationships.

He talks about the importance of relationships, creating his working models and some things to get well acquainted with - ow relationships work in CakePHP, how to use Containable behavior and some good SQL to back you up should you need it.

Francois Zaninotto submitted a tutorial he's written up about creating a YAML validation script with PHP.

As of today, there is no simple way to validate the syntax of a YAML file in PHP. But with two simple tricks, it takes only a few dozens of lines of code to build a robust validator capable of checking the syntax of any YAML file against a given schema.

He points out that Ruby has a tool for this (kwalify) but PHP doesn't. He creates his own with the help of the sfYaml component from the symfony framework, translating the YAML data into something PHP can parse more easily - XML. He passes this through an XSL parser and uses the DOM XML schemaValidate function to check it against the given schema.

Padraic Brady has an addendum he's posted to his "making a blogging application with the Zend Framework" series dealing with a few random issues from along the way.

The interesting thing about live publishing of a long tutorial series is that it's not flawless. In fact it's the opposite. [...] To cover all these I'll occasionally highlight the more important ones both in notes to new entries, or where they slip past me, in Addendum entries like this one.

There's four sections in this update - one dealing with the referencing of base URLs, another worrying about magic_quotes settings, an updated database schema for the project and the final about removing non-english characters in the title URLs.

The IBM developerWorks site has posted a tutorial (you'll need to log in) showing how to use native XML databases to speed up development time for your applications.

Native XML databases have grown in popularity along with XML, because data is stored as native XML, rather than through tables in a traditional database. Using a native XML database means that a change to the schema requires minimal changes to your code and no change to the database. PHP and Java developers can benefit greatly from using native XML databases

IT talks about how to connect to the database (in this case DB2), grab the XML data via a query and how to insert information back in via a web-based form. There's also an example showing how to make a "search" on the data and how to change the schema of the database on the fly as well.

Related to an issue from a previous post, Greg Beaver has posted a solution to his Relax NG schema problem in PHP.

Shortly after my last blog post, I got a very helpful email from Rob Richards, who offered to help me with my trials and tribulations. He took a quick look at the schema for package.xml, and a sample package.xml and was able to find two small tweaks to make parsing actually work (gasp). This changes everything. I am abandoning the creation of a relax NG schema in favor of the battle-tested xsd. The error messages for xsd validation are far clearer than the rng ones.

The code included shows how much simpler the error messages are with the XSD validation option versus the RNG ones.

Raphael Stolt has posted a new tutorial today talking about how to validate the content of XML files within a PHP 4 application.

Today I ran into a small problem, validating import XML data against a DTD(Document Type Definition), and discovered a neat way to solve this without PHP4's own means, as the PHP 4 version on the running system wasn't supporting the DOM XML Functions. Xmllint, a command line XML tool, parses provided XML files and can be used to verify that these files are well formed and are satisfying an associated DTD/XML Schema.

In his example he shows a simple implementation of the tool, grabbing the file's information and pushing it through a command line request to check the file.

Developer.com has posted a new tutorial by Jason Gilmore about the creation of a simple search engine based around a MySQL database and using PHP to perform the queries.

In this article I'll offer a simplified version of this search engine, introducing you to some compelling PHP and MySQL features along the way. You might adopt what you learn towards building your own search engine, or towards other applications.

He starts with the base of the application - the database schema (including loading data into it) - and works up from there. He opts to go with the fulltext search type as it can provide a huge performance boost over just using something like LIKE statements in your query. He also includes a bit of HTML for help make the output easier to use.