21 Apr

Laravel 4 and database seeding

I’ve been playing around with Laravel 4 for a few days now and have been very impressed. Once I figured outhow to install it, so far the learning curve from Codeigniter seems to be small. Right now I’m taking an existing project written with Codeigniter and creating a Laravel version for it. I got the configurations set up, now to move on to the database. The migration feature is great. I’ve been able to create my database and rollback with ease. The next step is to take data from an existing database and import it over. I could dump it into an SQL file and be done with it. But the source project is live and data changes. I’d like to use migration to get the latest data off the source.

The solution is to use Laravel’s seeding. Migration and seeding work with each other. Unfortunately, the sample code and demo I’ve seen so far uses arrays to populate (seed) the database. It’s a good sign though because all I have to do is query the existing database and populate an array with the resultset. The problem is how and what the syntax is. After a few hours of researching online and testing, I’ve got the solution.

Let’s say your source database is MySQL and you have a client table with client_id and client_name columns. On your Laravel project you have a clients table with id, client_name, created_at, and updated_at columns. Add a new connection to your /app/config/database.php. If you have multiple MySQL databases, it’s okay, just append 2… so you have a mysql and mysql2 connection. So your mysql array connection holds your Laravel database and mysql2 holds your database connection information where you’ll be getting data from.

Next edit the /app/database/seeds/DatabaseSeeder.php file. Here’s what I have.

<?php
class DatabaseSeeder extends Seeder {

	/**
	 * Run the database seeds.
	 *
	 * @return void
	 */
	public function run()
	{
		Eloquent::unguard();

		$this->call('ClientTableSeeder');
	}

}

class ClientTableSeeder extends Seeder
{

	public function run()
	{
                // truncate the table before inserting imported data
		DB::table('clients')->truncate();

                // use mysql2 connection then query the database, then put resultset in array
		$clients = DB::connection('mysql2')->select('SELECT `client_id`, `client_name` FROM `client`');
		$now = date('Y-m-d H:i:s');

                // loop through the resultset and insert into laravel database
		foreach ($clients as $client)
		{
			Client::create([
				'id'			=> $client->client_id,
				'client_name' 	=> $client->client_name,
			    'created_at' 	=> $now,
			    'updated_at' 	=> $now
			]);
			
		}
	}
}
14 May

Allowing network connections to MySQL in OS X Leopard Server

For the past couple of days I’ve been trying to set up one of our XServe running Leopard Server at work for web hosting. I was able to configure the Web, AFP, and SMB properly. The problem I was having was configuring MySQL. It was easy to enable but trying to connect to it from other computers in the same domain was the problem. There’s a checkbox with “Allow network connections” in the Server Admin section of MySQL. So I checked it, save, and restarted it. I still wasn’t able to connect. I checked for firewall settings. I pinged the host and got a response. I couldn’t figure out what was wrong. So I googled it.

The common answer I found was to edit the /etc/mysql/my.cnf file. Well it’s not there but I did find a /etc/my.cnf file. So I tried editing that file and restarting. It didn’t work.

Then I remembered that the php.ini file that Leopard Server is using isn’t in the default location /etc/php.ini, but it ran the /private/etc/php.ini. So I looked in there and found another my.cnf file. I edited the file by adding # in front of skip-networking entry. I restarted it and it finally worked.

So here’s what you need to do:

  • Go into Server Admin and check the Allow network connections and save
  • Comment out the skip-networking entry from /private/etc/my.cnf
  • Add the IP address of the computer you will be accessing MySQL remotely
  • Give it the privileges it needs
  • Restart MySQL

That should allow you to connect to MySQL running on Leopard Server remotely.

04 Feb

MySQL backup and restore databases with views

We recently installed OS X Leopard Server at work. I had to take MySQL databases from our old Tiger Server and restore them onto the new XServes. Both servers are running MySQL Server version 5.0.45 but our Tiger server is running MySQL Client version 5.0.22 while our Leopard servers are running MySQL Client version 5.1.18. According to MySQL’s documentation (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html):

Prior to release 5.0.48, this option did not create valid SQL if the database dump contained views. The recreation of views requires the creation and removal of temporary tables and this option suppressed the removal of those temporary tables. As a workaround, use –compress with the –add-drop-table option and then manually adjust the dump file.

I tried the –compress option but it didn’t work. I still had to manually adjust the SQL file. The I tried the GUI tool from MySQL, http://dev.mysql.com/downloads/gui-tools/5.0.html (MySQL Administrator).

mysql admin

Once you create the backup, you can restore it using the same application on the newer server. It will even include the views. Once you start using the newer client, you shouldn’t have a problem restoring databases with views from backups made with the mysqldump command.