#ConcreteCMS Migrate legacy database connection to doctrine

readme.md

Migrate legacy database connection to doctrine

Why we should migrate legacy codes?

Support read vs. read/write database connections #9126

Insert Query

$db->prepare($query)

$db = Loader::db();
$v = [$this->getBlockName(), $bDate, $bDate, $this->getBlockFilename(), $this->getBlockTypeID(), $this->getBlockUserID()];
$q = 'insert into Blocks (bName, bDateAdded, bDateModified, bFilename, btID, uID) values (?, ?, ?, ?, ?, ?)';
$r = $db->prepare($q);
$res = $db->execute($r, $v);
$newBID = $db->Insert_ID(); // this is the latest inserted block ID

↓

$app = Facade::getFacadeApplication();
$connection = $app->make(Connection::class);
$connection->insert('Blocks', [
    'bName' => $this->getBlockName(),
    'bDateAdded' => $bDate,
    'bDateModified' => $bDate,
    'bFilename' => $this->getBlockFilename(),
    'btID' => $this->getBlockTypeID(),
    'uID' => $this->getBlockUserID()
]);
$newBID = $connection->lastInsertId(); // this is the latest inserted block ID

Ref: concretecms/concretecms#10101

Migrate from $db->Execute()

$db->Execute('insert into WorkflowRequestObjects (wrObject) values (?)', array($wrObject));
$this->wrID = $db->Insert_ID();

↓

$db->insert('WorkflowRequestObjects', ['wrObject' => $wrObject]);
$this->wrID = $db->lastInsertId();

Ref: concretecms/concretecms#11819

Select Query

$db->GetRow($query)

$row = $db->GetRow(
    'select cID, cvID, arHandle, cbDisplayOrder, ptComposerFormLayoutSetControlID from PageTypeComposerOutputBlocks where cID = ? and cvID = ? and bID = ? and arHandle = ?',
    [$ocID, $ovID, $this->getBlockID(), $this->getAreaHandle()]
);

↓

$row = $connection->createQueryBuilder()
    ->select('cID', 'cvID', 'arHandle', 'cbDisplayOrder', 'ptComposerFormLayoutSetControlID')
    ->from('PageTypeComposerOutputBlocks')
    ->where('cID = :cID')
    ->andWhere('cvID = :cvID')
    ->andWhere('bID = :bID')
    ->andWhere('arHandle = :arHandle')
    ->setParameter('cID', $ocID)
    ->setParameter('cvID', $ovID)
    ->setParameter('bID', $this->getBlockID())
    ->setParameter('arHandle', $this->getAreaHandle())
    ->execute()->fetchAssociative();

while ($row = $r->fetch()) {

$q = "select paID, pkID from BlockPermissionAssignments where cID = '$ocID' and bID = ? and cvID = ?";
$r = $db->query($q, [$this->getBlockID(), $ovID]);
if ($r) {
    while ($row = $r->fetch()) {
        // $row['paID']
    }
}

↓

$r = $connection->createQueryBuilder()
    ->select('paID', 'pkID')
    ->from('BlockPermissionAssignments')
    ->where('cID = :cID')
    ->andWhere('bID = :bID')
    ->andWhere('cvID = :cvID')
    ->setParameter('cID', $ocID)
    ->setParameter('bID', $this->getBlockID())
    ->setParameter('cvID', $ovID)
    ->execute();
if ($r) {
    while ($row = $r->fetchAssociative()) {
        // $row['pkID']
    }
}

$db->GetOne()

$num = $db->GetOne(
    'select count(wpID) as total from WorkflowProgress where wpID <> ? and wrID = ? and wpIsCompleted = 0',
    array(
        $wp->getWorkflowProgressID(),
        $this->getWorkflowRequestID(),
    )
);

↓

$num = $db->fetchOne(
    'select count(wpID) as total from WorkflowProgress where wpID <> ? and wrID = ? and wpIsCompleted = 0',
    [
        $wp->getWorkflowProgressID(),
        $this->getWorkflowRequestID(),
    ]
);

Update Query

$db->Replace('Table')

$db->Replace(
    'BlockPermissionAssignments',
    [
        'cID' => $ncID,
        'cvID' => $nvID,
        'bID' => $newBID,
        'paID' => $row['paID'],
        'pkID' => $row['pkID'],
    ],
    [
        'cID',
        'cvID',
        'bID',
        'paID',
        'pkID',
    ],
    true
);

↓

$assignment = $connection->createQueryBuilder()
    ->select('cID', 'cvID', 'bID', 'pkID', 'paID')
    ->from('BlockPermissionAssignments')
    ->where('cID = :cID')
    ->andWhere('cvID = :cvID')
    ->andWhere('bID = :bID')
    ->andWhere('pkID = :pkID')
    ->andWhere('paID = :paID')
    ->setParameter('cID', $ncID)
    ->setParameter('cvID', $nvID)
    ->setParameter('bID', $newBID)
    ->setParameter('pkID', $row['pkID'])
    ->setParameter('paID', $row['paID'])
    ->execute()->fetchOne();
if ($assignment === false) {
    $connection->insert('BlockPermissionAssignments', [
        'cID' => $ncID,
        'cvID' => $nvID,
        'bID' => $newBID,
        'pkID' => $row['pkID'],
        'paID' => $row['paID']
    ]);
}

$db->Execute()

$db->Execute('update WorkflowRequestObjects set wrObject = ? where wrID = ?', array($wrObject, $this->wrID));

↓

$db->update('WorkflowRequestObjects', ['wrObject' => $wrObject], ['wrID' => $this->wrID]);

Delete Query

$db->Execute()

$db->Execute('delete from WorkflowRequestObjects where wrID = ?', array($this->wrID));

↓

$db->delete('WorkflowRequestObjects', ['wrID' => $this->wrID]);