记录一下单条和批量写入到MongoDB和PostgreSQL方法

  •   
  • 3385
  • MySQL
  • 0
  • super_dodo
  • 2018/09/14

最近一段时间做数据迁移从MongoDB里面迁移到Pg里面,之前的一个方案是实现双写操作,在灰度阶段和一些特定场景下面的需要双写,避免数据的遗漏。记录一下的主要是因为记录一下在对于nosql的mongo的情况下,很多时候需要对类型进行强制的转换。不然会造成字段类型不一致,后面查询的时候,漏数据的情况。

<?php


class HistoryBuilder extends HistoryBuilder
{

    //单独写一条操作历史记录
    public function build($clientId, $userId, $type, $company_id, $diff)
    {
        
        $id = \RedisRecord::produceMaxId();
        //写入mongoDB
        $momgoModel = new \CompanyHistory();
        $momgoModel->_id = \MongoObject::int64Value($id);
        $momgoModel->client_id = \MongoObject::int64Value($clientId);
        $momgoModel->company_id = \MongoObject::int64Value($company_id);
        $momgoModel->type = \MongoObject::int64Value($type);
        $momgoModel->create_time = date('Y-m-d H:i:s');
        $momgoModel->update_user = \MongoObject::int64Value($userId);
        $momgoModel->diff = $diff;
        $momgoModel->save();

        //写入新表PG
        $pgModel = new \common\models\client\CompanyHistoryPg();
        $pgModel->client_id = $clientId;
        $pgModel->type = $type;
        $pgModel->company_id = $extraData['company_id'];
        $pgModel->create_time = date('Y-m-d H:i:s');
        $pgModel->update_time = date('Y-m-d H:i:s');
        $pgModel->update_user = $userId;
        $pgModel->diff = json_encode($diff);
        $pgModel->save())
  
    }

    /**
    ** 批量插入的操作--双写操作--对比MongoDB和Pg
    **/
    public function buildBatch($clientId, $userId, $type, $map)
    {
        $count = count($map);
        $maxId = \RedisRecord::produceMaxId($count);
        $id = $maxId - $count + 1;

        $mindoc = [];
        $dataList = [];

        $date = \MongoObject::dateValue(date('Y-m-d H:i:s'));

        foreach ($map as $companyId => $diff) {
            //组装MongoDB数据
            $doc[] = [
                '_id' => \MongoObject::int64Value($id),
                'company_id' => \MongoObject::int64Value($companyId),
                'client_id' => \MongoObject::int32Value($clientId),
                'type' => \MongoObject::int32Value($type),
                'diff' => $diff,
                'update_user' => \MongoObject::int32Value($userId),
                'create_time' => \MongoObject::dateValue(date('Y-m-d H:i:s')),
                'update_time' => \MongoObject::dateValue(date('Y-m-d H:i:s'))
            ];

            $dataList[] = [
                'client_id' => $clientId,
                'type' => $type,
                'company_id' => $companyId,
                'update_user' => $userId,
                'create_time' => "'".date('Y-m-d H:i:s')."'",
                'update_time' => "'".date('Y-m-d H:i:s')."'",
                'diff' => json_encode($diff)
            ];

            ++$id;
        }

        //写入MongoDB
        $coll = \CompanyHistory::getCollectionFromClientId($clientId);
        $coll->insertMany($doc, ['ordered' => false]);

        //写入PostgreSQL
        $this->batchInsert($clientId, $dataList);
    }



    public function batchInsert($clientId, array $dataList = [])
    {
        $model = new \common\models\client\CompanyHistoryPg();
        $table = $model::getModelTableName();       //插入的表
        $fieldList = ['client_id', 'type', 'company_id', 'update_user', 'create_time', 'update_time', 'diff'];      //插入的字段

        if(empty($dataList)) return ;
        if(!$clientId) return;

        $fieldStr = implode(',', $fieldList);   //字段
        $sql = "INSERT INTO {$table} ({$fieldStr}) VALUES ";

        foreach ($dataList as $row) {
            $rowStr = implode(',', $row);   //字段
            $sql .= "($rowStr),";
        }
        $sql = rtrim($sql, ',');

        $pgDb = \PgActiveRecord::getDbByClientId($clientId);
        $pgDb->createCommand($sql)->execute();
    }



}

每个人都知道天下没有不散的宴席,可还是信誓旦旦地承诺永远。永远到底有多远?多少人问过这句话。有人说,永远是明天;也有人说,永远是一辈子;还有人说,永远是永生永世。或许他们都说对了,也或许都说错了,又或许人间原本就没有什么是永远。你曾经千里迢迢来赶赴一场盟约,有一天也会骤然离去,再相逢已成隔世。