Convert SQL Server tables to MySQL using ZF2 and Doctrine2

Viewed 378

I've developed an application for one of my client. He already have one. So I need to convert his actual database (SQL Server), to the new one (MySQL).

Some tables of SQL Server has over then 10.000.000 records. When I initiate start developing this converter, I've started with some tables with a few records, so I find all records and save to my new MySQL database. I'll show you some code for better understanding (this is just an example)

<?php

namespace Converter\Model;

class PostConverter extends AbstractConverter 
{

    public function convert() 
    {
        // this is the default connection, it is a mysql database (new application)
        $em = $this->getEntityManager();
        // this return an alternative connection to the sqlserver database (actual application)
        $emAlternative = $this->getEntityManagerAlternative();

        // instance of Converter\Repository\Post
        $repository = $emAlternative->getRepository('Converter\Entity\Post');

        $posts = $repository->findAll();

        foreach ($posts as $post)
            $post = new Post();
            $post->setTitle($object->getTitle());
            $em->persist($post);
        }  

        $em->flush();
    }
}

Now let's suppose that Post table has over then 10.000.000 records. I can't just find all and iterate over it. I'll get out of RAM. So I did something like this.

Repository class:

<?php

namespace Converter\Repository;

class Posts extends \Doctrine\ORM\EntityRepository
{

    public function findPosts($limit, $offset)
    {
        $qb = $this->createQueryBuilder('Post');

        $qb->setMaxResults($limit);
        $qb->setFirstResult($offset);

        return $qb->getQuery->getResult();
    }
}

Here I find only a few posts at time, in the while loop. But it's kinda slow. I couldn't find a better solution to improve the performance

<?php

namespace Converter\Model;

class PostConverter extends AbstractConverter 
{

    public function convert() 
    {
        $em = $this->getEntityManager();
        $emAlternative = $this->getEntityManagerAlternative();

        $repository = $emAlternative->getRepository('Converter\Entity\Post');

        $limit = 1000;

        while ($object = $repository->findPosts($limit, $offset) {
            $post = new Post();
            $post->setTitle($object->getTitle());
            $em->persist($post);

            $offset += $limit;
        }  

        $em->flush();
    }
}

I had never done something like this before. Maybe I'm going to a wrong way. I'll really appreciate if some of you could tell me the right one, so I can move on in this.

Thank you all


EDIT

I can't just dump one to another. What I posted here is just an example, in the conversion I have to handle almost all data before insert in the new database. His actual application was developed in 2005. The database is not even normalized

4 Answers
Related