CODEWITHSUNDEEP
phpmysqllaraveleloquentlaravel-migrations
Nathan F.
Nathan F.

Reputation: 3469

Laravel move data from one table to another and drop the column that the data came from?

I'm trying to do the following process in a Laravel migration:

Create a new table called client_log_partitions. (DONE)

$table->create();

$table->bigIncrements('id')->unique();
$table->bigInteger('client_log_id');
$table->mediumText('partition_data');

$table->timestamps();

I know I may be able to achieve this using $table->postExecute(), I just don't know what I might use to do so.

Once the data is moved to the new table, I then need to drop the log_data column from client_logs.

Normally, I would do this with a PHP script or something similar. But unfortunately I'm operating under circumstances where I'm unable to.

My question is, Is this doable using Laravel Migrations, and if so, how?

EDIT:

Although I'm unsure since i only whipped this up on the fly and haven't tested it, this is what I imagine the SQL for achieving this would look something like:

DROP PROCEDURE IF EXISTS PROCESS_LOG_DATA;
DROP PROCEDURE IF EXISTS PROCESS_LOG_ENTRIES;
DELIMITER ;;

## Procedure for processing a specific log entry
## and moving its data to the new table.
CREATE PROCEDURE PROCESS_LOG_DATA(log_id bigint, partition_size int)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SELECT LENGTH(log_data)/partition_size FROM client_logs where id=log_id INTO n;
    SET i=0;
    WHILE i<n DO

      # Move the characters to the new table.
      INSERT INTO client_log_partitions 
          (client_log_id, partition_data)
      SELECT (id, LEFT(log_data, partition_size))
      FROM client_logs 
      WHERE id=log_id

      # Shift the characters off of the log_data
      UPDATE client_logs
      SET log_data = SUBSTR(
          log_data,
          partition_size,
          LENGTH(log_data) - partition_size
      ) where id=log_id;

      # Update the number of data partitions we've processed for this log entry
      SET i = i + 1;
    END WHILE;
End;
;;


## Procedure for processing all log entries
## and passing each one to the PROCESS_LOG_DATA procedure.
CREATE PROCEDURE PROCESS_LOG_ENTRIES(partition_size int)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SELECT COUNT(*) FROM client_logs INTO n;
    SET i=0;
    WHILE i<n DO
        PROCESS_LOG_DATA(i, partition_size)
    END WHILE;
End;
;;

DELIMIETER ;

## Process the log entries.
CALL PROCESS_LOG_ENTRIES(250000);

Upvotes: 4

Views: 13683

Answers (2)

Royal_MGH
Royal_MGH

Reputation: 784

Change your new table migration up method to :

public function up()
{
    //Create the table
    Schema::create('your_table_name', function (Blueprint $table) {
        $table->bigIncrements('id')->unique();
        $table->bigInteger('client_log_id');
        $table->mediumText('partition_data');

        $table->timestamps();
    });

    //Copy column from last table to new table
    foreach(MyOldModel::all() as $item)
    {
        //now you can save old data into new table old data : $item -> log_data
        //other operation you want
        MyNewModel::create(array('partition_data' => $item -> log_data));//you can save other columns with adding array values
    }

    //Drop old table column
    Schema::table('client_logs', function (Blueprint $table) {
        $table->dropColumn('log_data');
    });
}

I think in this way also migrate:rollback command should be work ( for undo your changes ) !

Upvotes: 4

CommandZ
CommandZ

Reputation: 3611

All you should need to do is run your code to move the information and re-associate the ids before the drop column call. This is because everything in the up function is ran sequentially, from top to bottom, and not asynchronously. https://laravel.com/docs/5.8/migrations

You could use Eloquent and PHP to move the information and alter it. Doing so would probably be easier than writing a query. This is how companies that I've worked at moved data and then altered tables. Sometimes, because of SoX compliance stupidity, we had to move data using SQL scripts but you shouldn't have to worry about that it sounds like. 

class DropClientLogsLogDataColumn extends Migration
{

    public function up()
    {
        // move data
        $this->moveData()

        Schema::table('client_logs', function (Blueprint $table) {
          // drop the log_data column
          $table->dropColumn('log_data');
        });
    }

    private function moveData()
    {
      // do whatever you need to do here 
      // more than likely you'll use eloquent or query builder
      // this is where you'd associate the id's
    }
}

Upvotes: 2

Related Questions