New database communication engine

Starting from version 3.3, YetiForce CRM has a new database communication engine built-in, which we continuously develop and improve together with each new release.

This allows YetiForce to communicate with multiple database engines (DBMS) that are compliant with PDO, such as MariaDB, MySQL, PostgreSQL, MSSQL, SQLite, Oracle, and Cubrid.

It does not yet have all the mechanisms rewritten to be able to fully install the system on another database engine.

Table names

We use prefixes in table name, for example u_#__crmentity_label >> u_yf_crmentity_label. The default prefix is "yf_"

a_yf_encryption = 'admin' (administrator's part)
s_yf_multireference = 'admin' (configuration's part)
l_yf_sqltime = 'log' (this part only contains logs)
o_yf_csrf = 'log' (this part only contains OWASP messages)
w_yf_servers = 'portal' (temporary name; this part is only for webservice applications including Portal)

More examples: https://github.com/yiisoft/yii2/tree/master/tests/framework/db

Query building basics

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html http://www.yiiframework.com/doc-2.0/yii-db-queryinterface.html#where()-detail

Data types

MySQL

https://github.com/YetiForceCompany/YetiForceCRM/blob/f086756f877f0e5594db115075b292f44de92350/vendor/yii/db/mysql/Schema.php#L25

https://github.com/YetiForceCompany/YetiForceCRM/blob/f086756f877f0e5594db115075b292f44de92350/vendor/yii/db/mysql/QueryBuilder.php#L25

PgSQL

https://github.com/YetiForceCompany/YetiForceCRM/blob/f086756f877f0e5594db115075b292f44de92350/vendor/yii/db/pgsql/Schema.php#L35 https://github.com/YetiForceCompany/YetiForceCRM/blob/f086756f877f0e5594db115075b292f44de92350/vendor/yii/db/pgsql/QueryBuilder.php#L53

Database connection without primary database parameter

$db = \App\Db::getInstance();
$db = \App\Db::getInstance('base');
$db = \App\Db::getInstance('log');
$db = \App\Db::getInstance('admin');

Downloading data

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html

$query = (new \App\Db\Query())
->select(['id', 'user_name'])
->from('vtiger_users')
->where(['status' => 'Active'])
->limit(10);
 
->andWhere(['between', 'holidaydate', $date[0], $date[1]]);

Shortcuts

$query->all();
 
$query->createCommand()->queryOne() => $query->one() // get first line
$id = (new \App\Db\Query())->from($tableName)->scalar(); // get value from first line first column 
 
Full version:
$dataReader = $query->createCommand()->query();
$values = [];
while ($shownerid = $dataReader->readColumn(0)) {
 $values[] = $shownerid;
}
Short version:
$values = $query->column();

JOIN

->innerJoin('vtiger_tab', 'vtiger_relatedlists.related_tabid = vtiger_tab.tabid')
->leftJoin('u_yf_announcement_mark', 'u_yf_announcement.announcementid = u_yf_announcement_mark.crmid')

Using createCommand

http://www.yiiframework.com/doc-2.0/yii-db-query.html

$query = (new App\Db\Query())->from('u_#__crmentity_label');
$dataReader = $query->createCommand()->queryOne();

Using DataReader

http://www.yiiframework.com/doc-2.0/yii-db-datareader.html

$query = (new \App\Db\Query())->from('u_#__crmentity_label');
 
$dataReader = $query->createCommand()->query();
$dataReader = $query->createCommand($db)->query();
 
while ($row = $dataReader->read()) {
 
}
while ($single = $dataReader->readColumn(0)) {
 
}
while ($row = $dataReader->readObject()) {
 
}
$rows = $dataReader->readAll();
$noRows = $dataReader->count();
$noColumn = $dataReader->getColumnCount();

Using Query

http://www.yiiframework.com/doc-2.0/yii-db-query.html

$query = (new App\Db\Query())->from('u_yf_crmentity_label');
//$query->batch($batchSize = 100, $db = null)
foreach ($query->batch() as $rows) {
 
}

Group users

$query = new \App\Db\Query();
$query->select(['crmid', 'setype'])->from('vtiger_crmentity');
$data = $query->createCommand()->queryAllByGroup(0);
 
Array
(
 [113] => Accounts
 [115] => Contacts
 [114] => Leads
 [116] => Project
)
 
$query = new \App\Db\Query();
$query->select(['crmid', 'setype', 'createdtime'])->from('vtiger_crmentity');
$data = $query->createCommand()->queryAllByGroup(1);
 
Array
(
 [113] => Array
 (
 [setype] => Accounts
 [createdtime] => 2017-08-01 09:43:15
 )
 [114] => Array
 (
 [setype] => Leads
 [createdtime] => 2017-08-01 09:43:21
 )
 [115] => Array
 (
 [setype] => Contacts
 [createdtime] => 2017-08-01 09:43:26
 )
 [116] => Array
 (
 [setype] => Project
 [createdtime] => 2017-08-01 09:43:34
 )
)
 
$query = new \App\Db\Query();
$query->select(['crmid', 'setype'])->from('vtiger_crmentity');
$data = $query->createCommand()->queryAllByGroup(2);
 
Array
(
 [113] => Array
 (
 [0] => Accounts
 )
 [115] => Array
 (
 [0] => Contacts
 )
 [114] => Array
 (
 [0] => Leads
 )
 [116] => Array
 (
 [0] => Project
 )
)

Add data

http://www.yiiframework.com/doc-2.0/yii-db-command.html#insert()-detail

\App\Db::getInstance()->createCommand()
 ->insert('a_#__inventory_limits', [
 'name' => 'xxxx',
 'status' => 2,
 'value' => 'rrrr'
 ])->execute();

Update data

http://www.yiiframework.com/doc-2.0/yii-db-command.html#update()-detail

\App\Db::getInstance()->createCommand()
 ->update('a_#__inventory_limits', [
 'name' => 'rrrrr',
 ], 'id=:id', [':id' => 1])
 ->execute();
// UPDATE `a_#__inventory_limits` SET `name`=:qp1 WHERE id=:id
 
\App\Db::getInstance()->createCommand()
 ->update('a_#__inventory_limits', [
 'name' => 'uuuu',
 ], ['id' => 1, 'status' => 2])
 ->execute();
// UPDATE `a_#__inventory_limits` SET `name`=:qp0 WHERE (`id`=:qp1) AND (`status`=:qp2)
 
\App\Db::getInstance()->createCommand()
 ->update('a_#__inventory_limits', [
 'name' => 'eeee',
 ], ['status' => [1, 2, 3]])
 ->execute();
// UPDATE `a_#__inventory_limits` SET `name`=:qp0 WHERE `status` IN (:qp1, :qp2, :qp3)
 
\App\Db::getInstance()->createCommand()
 ->update('a_#__inventory_limits', [
 'name' => 'bbbb',
 ], ['or', ['status' => 2], ['id' => 1]])
 ->execute();
// UPDATE `a_#__inventory_limits` SET `name`=:qp0 WHERE (`status`=:qp1) OR (`id`=:qp2)

Check generated query

$query = (new \App\Db\Query())
 ->select('last_reviewed_users as u, id, changedon')
 ->from('vtiger_modtracker_basic')
 ->where(['crmid' => 1333])
 ->andWhere(['<>', 'status', 3])
 ->orderBy(['changedon' => SORT_DESC, 'id' => SORT_DESC]);
list ($sql, $params) = \App\Db::getInstance()->getQueryBuilder()->build($query);

$query->createCommand()->getRawSql()

Using conditions in queries

http://www.yiiframework.com/doc-2.0/yii-db-query.html#where()-detail

//Stare zapytanie:
$db->delete('vtiger_picklist_dependency', '`tabid` = ? AND (sourcefield=? OR targetfield=?)', [$tabId, $columnName, $columnName]);
 
//Nowe zapytanie:
 $db->createCommand()->delete('vtiger_picklist_dependency', ['and', "tabid = $tabId", ['or', "sourcefield = '$columnname'", "targetfield = '$columnname'" ]])->execute();
 
//Stare zapytanie:
$updateQuery = 'UPDATE vtiger_field SET sequence = sequence-1 WHERE sequence > ? &amp;&amp; sequence <= ? &amp;&amp; block = ?';
$params = array($olderSequence, $newSequence, $olderBlockId);
$db->pquery($updateQuery, $params);
 
//Nowe zapytanie:
$db->createCommand()->update('vtiger_field', ['sequence' => new \yii\db\Expression('sequence - 1')], ['and', 'sequence > :olderSequence', 'sequence <= :newSequence', 'block = :olderBlockId'],
 [':olderSequence' => $olderSequence, ':newSequence' => $newSequence, ':olderBlockId' => $olderBlockId])->execute();

Adding many data

http://www.yiiframework.com/doc-2.0/yii-db-querybuilder.html#batchInsert()-detail

$db->createCommand()
 ->batchInsert('tableName', ['id', 'title', 'created_at'], [
 [1, 'title1', '2015-04-10'],
 [2, 'title2', '2015-04-11'],
 [3, 'title3', '2015-04-12'],
 ])->execute();
 
 
$db->createCommand()
 ->batchInsert('tbl_user', ['name', 'status'], [
 ['Bala', 1],
 ['Akilan', 0],
 ['Babu', 1],
 ])
 ->execute();

Nested queries

$subQuery = (new \App\Db\Query())->select('tabid')->from('vtiger_field')->where(['uitype' => 4])->distinct('tabid');
 $dataReader = (new \App\Db\Query())->select(['tabid', 'name'])
 ->from('vtiger_tab')
 ->where(['isentitytype' => 1, 'presence' => 0, 'tabid' => $subQuery])
 ->createCommand()->query();

MAX - max value

$maxSequence = (new \App\Db\Query())
 ->from('vtiger_cron_task')
 ->max('sequence');

Expression

->select(['c' => new \yii\db\Expression("CASE WHEN 1>0 THEN 'true' ELSE 'false' END")])
 
$db->createCommand()->update('vtiger_field', ['sequence' => new \yii\db\Expression('sequence + 1')])

Get the last insert ID

$db = \App\Db::getInstance();
$db->createCommand()->insert('a_#__bruteforce_blocked', [
 'ip' => 'ip',
 'attempts' => 1,
 'blocked' => 0,
 ])->execute();
$db->getLastInsertID();

Does query result contain any row of data?

http://www.yiiframework.com/doc-2.0/yii-db-query.html#exists()-detail

$isExists = (new \App\Db\Query())->from('yetiforce_mobile_keys')
 ->where(['user' => $userId, 'service' => 'pushcall'])
 ->exists();

Queries with permissions and subqueries

$query = (new \App\Db\Query())
 ->select(['vtiger_activity.*', 'linkmod' => 'relcrm.setype', 'processmod' => 'procrm.setype', 'subprocessmod' => 'subprocrm.setype'])
 ->from('vtiger_activity')
 ->innerJoin('vtiger_crmentity', 'vtiger_activity.activityid = vtiger_crmentity.crmid')
 ->innerJoin('vtiger_activitycf', 'vtiger_activity.activityid = vtiger_activitycf.activityid')
 ->innerJoin('vtiger_crmentity relcrm', 'vtiger_activity.link = relcrm.crmid')
 ->innerJoin('vtiger_crmentity procrm', 'vtiger_activity.process = procrm.crmid')
 ->innerJoin('vtiger_crmentity subprocrm', 'vtiger_activity.subprocess = subprocrm.crmid')
 ->where(['vtiger_activity.deleted' => 0]);
\App\PrivilegeQuery::getConditions($query, 'Calendar');
$query->andWhere(
 ['or',
 [
 'and',
 ['>=', new \yii\db\Expression("CONCAT(date_start, ' ', time_start)"), $dbStartDateTime],
 ['<=', new \yii\db\Expression("CONCAT(date_start, ' ', time_start)"), $dbEndDateTime]
 ],
 [
 'and',
 ['>=', new \yii\db\Expression("CONCAT(due_date, ' ', time_end)"), $dbStartDateTime],
 ['<=', new \yii\db\Expression("CONCAT(date_start, ' ', time_start)"), $dbEndDateTime]
 ],
 [
 'and',
 ['<', 'date_start', $dbStartDate],
 ['>', 'due_date', $dbEndDate]
 ]
]);
$query->andWhere(['vtiger_activity.activitytype' => $this->get('types')]);
$conditions = [];
$subQuery = (new \App\Db\Query())->select('crmid')->from('u_#__crmentity_showners')->where(['userid' => $currentUser->getId()]);
$conditions[] = ['vtiger_crmentity.crmid' => $subQuery];
$conditions[] = ['vtiger_crmentity.smownerid' => $users];
$query->andWhere(array_merge(['or'], $conditions));
$query->orderBy('vtiger_activity.date_start,vtiger_activity.time_start');

SQL:

SELECT "vtiger_activity".*, "relcrm"."setype" AS "linkmod", "procrm"."setype" AS "processmod", "subprocrm"."setype" AS "subprocessmod" 
FROM "vtiger_activity" 
JOIN "vtiger_crmentity" ON vtiger_activity.activityid = vtiger_crmentity.crmid 
INNER JOIN "vtiger_activitycf" ON vtiger_activity.activityid = vtiger_activitycf.activityid INNER JOIN "vtiger_crmentity" "relcrm" ON vtiger_activity.link = relcrm.crmid 
INNER JOIN "vtiger_crmentity" "procrm" ON vtiger_activity.process = procrm.crmid 
INNER JOIN "vtiger_crmentity" "subprocrm" ON vtiger_activity.subprocess = subprocrm.crmid 
WHERE (
 (
 (
 ("vtiger_activity"."deleted"=:qp0) 
 AND (
 ("vtiger_crmentity"."smownerid"=:qp1) 
 OR ("vtiger_crmentity"."smownerid" IN (SELECT "userid" FROM "vtiger_user2role" INNER JOIN "vtiger_users" ON vtiger_user2role.userid = vtiger_users.id INNER JOIN "vtiger_role" ON vtiger_user2role.roleid = vtiger_role.roleid WHERE "vtiger_role"."parentrole" LIKE :qp2)) 
 OR ("vtiger_crmentity"."crmid" IN (SELECT DISTINCT "crmid" FROM "u_yf_crmentity_showners" WHERE "userid"=:qp3))
 )
 ) 
 AND (
 ((CONCAT(date_start, ' ', time_start) >= :qp4) AND (CONCAT(date_start, ' ', time_start) <= :qp5)) 
 OR ((CONCAT(due_date, ' ', time_end) >= :qp6) AND (CONCAT(date_start, ' ', time_start) <= :qp7)) OR (("date_start" < :qp8) AND ("due_date" > :qp9))
 )
 )
 AND ("vtiger_activity"."activitytype" IN (:qp10, :qp11))
) 
AND (("vtiger_crmentity"."crmid" IN (SELECT "crmid" FROM "u_#__crmentity_showners" WHERE "userid"=:qp12)) OR ("vtiger_crmentity"."smownerid"=:qp13)) 
ORDER BY "vtiger_activity"."date_start", "vtiger_activity"."time_start"

Indexing query results

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#indexing-query-results

$query = (new \App\Db\Query())
 ->from('vtiger_contactsubdetails')
 ->indexBy('contactsubscriptionid')
 ->all();
 
array(1) {
 [113]=>
 array(5) {
 ["contactsubscriptionid"]=>
 int(113)
 ["birthday"]=>
 string(10) "2016-09-21"
 ["laststayintouchrequest"]=>
 int(0)
 ["laststayintouchsavedate"]=>
 int(0)
 ["leadsource"]=>
 string(0) ""
 }
}

Friday, 14 February 2020

Posted in: Engine
Tags: crm, database, engine, MariaDB, MSSQL, MySQL, PostgreSQL, YetiForce

New database communication engine

Related documentation

Table names

Query building basics

Data types

Database connection without primary database parameter

Downloading data

Shortcuts

JOIN

Using createCommand

http://www.yiiframework.com/doc-2.0/yii-db-query.html

Using DataReader

Using Query

Group users

Add data

Update data

Check generated query

Using conditions in queries

Adding many data

Nested queries

MAX - max value

Expression

Get the last insert ID

Does query result contain any row of data?

Queries with permissions and subqueries

Indexing query results

Related Links