mysql | DB table import strategy

Question

I'm writing the DB import function. My language is php. My data is in JSON format.

My code below:

public function import($data = '') {

    if (!isset($data)) {
        return array(
            'error' => "Error: no data"
        );  
    }

    $data_arr = json_decode($data, true);

    if (is_array($data_arr) && sizeof($data_arr)) {

        // Truncate DB table

        $sql = 'TRUNCATE `ms_data`';
        $this->db->query($sql)->execute();

        // Import data
        $sql =  'INSERT INTO `ms_data` (
                `id`, 
                `name`, 
                `parent`, 
                `ordering`
                ) VALUES (
                    :id,
                    :name, 
                    :parent,  
                    :ordering
                )';

        foreach($data_arr as $d) {

            $this->db->query($sql)
                ->bind(":id",        $d['id'])
                ->bind(":name",      trim($d['name']))
                ->bind(":ordering", $d['ordering'])
                ->execute();
        }

        return array(
            'status' => 1,
            'message' => 'Data has been imported'
        );                  

    } else {

        return array(
            'error' => "Input is not array"
        );  
    }       
}

This is working code but: maybe you know any code problems above I don't know now or any improvement suggestions?

Thanks!

Answer 1

If you have a lot of data you should do the following:

1) Create a new table that will be used to load the data: CREATE TABLE new_data LIKE ms_data
2) Populate data as you see fit: i.e. INSERT INTO new_data VALUES (?,?,?,?)
3) Replace the old table with new table: RENAME ms_data TO old_ms_data, new_data TO ms_data. This operation is atomic so it should be invisible to other users.
4) Clean up, remove the old table: DROP TABLE old_ms_data

CREATE TABLE, DROP TABLE and RENAME TABLE all cause implicit commit so you can't do this as a single transaction but due to nature of RENAME TABLE replacement of the data in 'ms_data' this is still atomic.

Be advised that two load processes cannot be executed at the same time if you use static name (e.g. 'new_data') for the temporary table.

Answer 2

There is one problem that I see:

If you load a lot of informations using this function, you will take a long while to end.

You can load all your data using a single query, with a code like this:

<?php
public function import($data = '') {

   if (!isset($data)) {
      return array(
         'error' => "Error: no data"
      );    
   }

   $data_arr = json_decode($data, true);

   if (is_array($data_arr) && sizeof($data_arr)) {

         // Truncate DB table

         $sql   = 'TRUNCATE `ms_data`';
         $this->db->query($sql)->execute();

         $itemCount = count($data_arr);

         // Import data
         $sql       = 'INSERT INTO `ms_data` (
                     `id`, 
                     `name`, 
                     `parent`, 
                     `ordering`
                     )  VALUES ';

        for($i=1; $i <= $itemCount; $i++)
        {
            // Last item should not put comma after values. 
            if ($i == $itemCount)
            {
                $sql = $sql . "(?,?,?,?)"
            }
            else
            {
                $sql = $sql . "(?,?,?,?),"
            }
        }

        $stmt = $this->db->prepare($sql);

        $i = 1;
        foreach($data_arr as $d) {


             $stmt->bindParam($i++, $d['id'])
             ->bindParam($i++, trim($d['name']))
             ->bindParam($i++, $d['ordering']);
         }

         $stmt->execute();

         return array(
             'status' => 1,
             'message' => 'Data has been imported'
         );                 

    } else {

         return array(
            'error' => "Input is not array"
         ); 
    }
}       
?>

Using this way, you will insert all your rows in a single query, this reduces execution time a lot, and will be a atomic operation.