Nowy silnik komunikacji z bazą danych

System YetiForce od wersji 3.3 ma wbudowany nowy silnik komunikacji z bazą danych, który w kolejnych wersjach systemu jest rozwijany i udoskonalany.
Używamy prefixów w nazwach tabel np. u_#__crmentity_label >> u_yf_crmentity_label. Domyślny prefix to "yf_"
  • a_yf_encryption = 'admin' (część administratora systemu)
  • s_yf_multireference = 'admin' (część konfiguracji systemu)
  • l_yf_sqltime  = 'log' (część zawierająca tylko logi systemu )
  • o_yf_csrf =  'log' (część systemu zawierająca komunikaty typu OWASP)
  • w_yf_servers  = 'portal' (nazwa ulegnie zmianie, część dla aplikacji webserwisowych wliczając portal klienta)
Więcej przykładów: https://github.com/yiisoft/yii2/tree/master/tests/framework/db

Podstawy budowy zapytań

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

Typy danych

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

Połączenie do bazy danych, bez parametru baza podstawowa

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

Pobieranie danych

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]]);

Skróty

$query->all();
 
$query->createCommand()->queryOne() => $query->one() // pobranie pierwszego wiersza
$id = (new \App\Db\Query())->from($tableName)->scalar(); // pobranie wartości z pierwszego wiersza pierwsza kolumna
 
Pełna wersja:
$dataReader = $query->createCommand()->query();
$values = [];
while ($shownerid = $dataReader->readColumn(0)) {
 $values[] = $shownerid;
}
Skrócona wersja:
$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')

Z użyciem 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();

Z użyciem 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();

Z użyciem 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) {
 
}

Grupowanie wyników

$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
 )
)

Dodawanie danych

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();

Aktualizacja danych

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)

 Sprawdzanie generowanego zapytania

$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()

Używanie warunków w zapytaniach

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();

Dodawanie wielu danych

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();

Zagnieżdżone zapytania

$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 - maksymalna wartość

$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')])

Pobranie ID dodane wpisu

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

Czy istnieje wpis w bazie danych

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();

Rozbudowane zapytanie z uprawnieniami i podzapytaniem

$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"

Indeksowanie wyniików

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) ""
 }
}
  • czwartek, 10 sierpień 2017