Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

SitePoint PHP Blog:
Quick Tip: Testing Symfony Apps with a Disposable Database
Jul 14, 2016 @ 11:12:16

On the SitePoint PHP blog author Andrew Carter has shared a "quick tip" about using a disposable database in the testing of your Symfony-based applications.

Testing code that interacts with a database can be a massive pain. Some developers mock database abstractions, and thus do not test the actual query. Others create a test database for the development environment, but this can also be a pain when it comes to continuous integration and maintaining the state of this database.

In-memory databases are an alternative to these options. As they exist only in the memory of the application, they are truly disposable and great for testing. Thankfully, these are very easy to set up with Symfony applications that use Doctrine.

He talks first about the built-in functionality Symfony has to use different configuration files for different environments. This allows for easier testing in a more isolated setup than replicating development. He then shows how to use Doctrine with a SQLite in-memory database with a simple update to the config_test YAML configuration file. He also includes the code for a DatabasePrimer class that gets the Doctrine entity manager and executes the the schema tool to set up the schema in the database and "prime" it with any fixtures you might need.

tagged: tutorial symfony sqlite database inmemory testing schema fixture

Link: https://www.sitepoint.com/quick-tip-testing-symfony-apps-with-a-disposable-database/

Paul Jones:
SQL Schema Naming Conventions
Nov 04, 2015 @ 12:15:59

Paul Jones has a post to his site looking at SQL schema naming conventions and some of his own thoughts on the matter. There's a lot of different camps of thought around naming, much less database ones, and he makes a few suggestions learned from his experience over time.

Several weeks ago I asked on Twitter for SQL schema naming conventions from DBA professionals. (I’m always interested in the generally-accepted practices of related professions; when I can, I try to make my work as compatible with theirs as possible.)

I got back only a handful of responses, representing MySQL, PostgreSQL, and DB2 administrators, really not enough for a statistically useful sample. Even so, I’m going to present their anonymized responses here, because they led me to work I had not previously considered at length.

He asked about things like singular vs plural names, primary key choices and naming of association tables. The uses the rest of the post sharing the responses he got from his questions with a good range of responses representing both sides of each question. He wraps up the post looking at what these answers mean to the average developer and the answers that Joe Celko and Simon Holywell have to say on the matter.

tagged: sql schema naming convention feedback table primarykey association feedback

Link: http://paul-m-jones.com/archives/6188

Matthew Turland:
Customizing Codeception Database Cleanup
May 12, 2014 @ 11:15:24

If you're a Codeception user, you'll find Matthew Turland's latest post interesting. In it he shares a way to customize database cleanup between the tests. Codeception handles it a bit differently that how PHPUnit's Db module does.

Recently, I was looking into ways to speed up the runtime of the test suite at Blopboard. We use the Codeception framework to write functional tests for our REST API, part of which entails putting the database into a known state using Codeception’s Db module. The behavior of this module is similar to that of the PHPUnit Database extension with one exception: where PHPUnit only truncates tables and leaves their schemas intact, Codeception removes the database structure and expects the SQL dump it uses to recreate it between tests. I must admit to not understanding this design decision of Codeception, nor attempts to clarify it.

He admits that his solution is "a bit hacky" but it does work to truncate the table rather than drop the entire schema and wait for a rebuild. His "DbHelper" class is used in place of the Db module. He traced through the execution path of the Db module and found a "hook" where he could override the "cleanup" method to prevent the schema drop and replace it with a truncate. He also includes code for a suggested addition to Codception that would handle the same thing in a more integrated way.

tagged: customize database cleanup codeception tutorial schema truncate phpunit

Link: http://matthewturland.com/2014/05/09/customizing-codeception-database-cleanup

Chris Jones:
Getting Started with PHP Zend Framework 2 for Oracle DB
May 15, 2013 @ 10:55:41

In his latest post to his site Chris Jones shows you how to update the Zend Framework 2 tutorial app (quickstart) to make it work with an Oracle database instead.

This post shows the changes to the ZF2 tutorial application to allow it to run with Oracle Database 11gR2. [...] The instructions for creating the sample ZF2 application are here. Follow those steps as written, making the substitutions shown [in the rest of the post].

The full schema definition is included in the post, complete with the same sample data as the tutorial. He includes the updates you'll need to make to the database configuration for the OCI8 connection and changes to the code to accommodate the Oracle data format (mostly uppercasing everything).

tagged: zendframework2 tutorial oracle database schema code

Link: https://blogs.oracle.com/opal/entry/getting_started_with_php_zend

Danne Lundqvist's Blog:
Getting to grips with an existing XML structure
Apr 25, 2012 @ 10:44:43

Danne Lundqvist has a new post where he shares a bit of code he's written to "come to grips" with an existing XML structure.

Very often I find myself writing input filters for large XML files using PHP. Common enough task; and PHP offer a great variety of tools to do this effectively depending on the situation. Unfortunately, almost as common is the lack of documentation for the aforementioned XML files. [...] I have looked around for a simple tool but I didn’t really find a tool that gave me the quick and dirty overview I wanted. A year or so ago I finally wrote a small PHP class to analyze large XML files.

He includes an example XML file, the HTML output of the parsing and a sample of how to use the class to parse and output the XML structure, complete with some CSS.

tagged: xml structure schema parse output html csss


Sameer Borate' Blog:
Creating SQL schemas with Doctrine DBAL
Dec 22, 2010 @ 14:25:53

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.

tagged: sql schema doctrine generate dbal mysql


Gonzalo Ayuso's Blog:
Keep our PostgreSQL databases synchronized with PHP. Database version control
Dec 13, 2010 @ 09:26:49

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.

tagged: synchronize database schema version control


Rob Allen's Blog:
Akrabat_Db_Schema_Manager: Zend Framework database migrations
Mar 29, 2010 @ 10:16:41

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.

tagged: zendframework database migration akrabat schema


Chris Hartjes' Blog:
Converting Legacy Apps to CakePHP, Part 2
Dec 09, 2008 @ 10:26:09

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.

tagged: cakephp legacy application convert model database schema


Francois Zaninotto's Blog:
Validating a YAML file against a schema in PHP
Sep 24, 2008 @ 12:58:35

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.

tagged: yaml validate tutorial schema xsl domxml sfyaml symfony framework