Menu
▾
▴
[Gemstracker-svn] SF.net SVN: gemstracker:[1007] trunk/library
|
From: <gem...@li...> - 2012-11-08 16:10:59
|
Revision: 1007
http://gemstracker.svn.sourceforge.net/gemstracker/?rev=1007&view=rev
Author: matijsdejong
Date: 2012-11-08 16:10:47 +0000 (Thu, 08 Nov 2012)
Log Message:
-----------
Started caching in DbLookup.php
DatabaseModelAbstract: switched from many OR to single IN() and allowed column expressions to be searched on fieldlist instead
Many indeces for speeding up the database
Modified Paths:
--------------
trunk/library/classes/Gems/Default/RespondentAction.php
trunk/library/classes/Gems/Model.php
trunk/library/classes/Gems/Util/DbLookup.php
trunk/library/classes/MUtil/Model/DatabaseModelAbstract.php
trunk/library/configs/db/patches.sql
trunk/library/configs/db/tables/gems__respondent2org.50.sql
trunk/library/configs/db/tables/gems__respondent2track.40.sql
trunk/library/configs/db/tables/gems__surveys.30.sql
trunk/library/configs/db/tables/gems__tokens.200.sql
trunk/library/configs/db/tables/gems__tracks.30.sql
Modified: trunk/library/classes/Gems/Default/RespondentAction.php
===================================================================
--- trunk/library/classes/Gems/Default/RespondentAction.php 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/classes/Gems/Default/RespondentAction.php 2012-11-08 16:10:47 UTC (rev 1007)
@@ -483,7 +483,7 @@
$this->openedRespondent($data['gr2o_patient_nr'], $data['gr2o_id_organization'], $data['grs_id_user']);
// Check for completed tokens
- if ($this->loader->getTracker()->processCompletedTokens($data['grs_id_user'], $this->session->user_id)) {
+ if ($this->loader->getTracker()->processCompletedTokens($data['grs_id_user'], $this->session->user_id, $data['gr2o_id_organization'])) {
//As data might have changed due to token events... reload
$data = $model->applyRequest($this->getRequest(), true)->loadFirst();
}
Modified: trunk/library/classes/Gems/Model.php
===================================================================
--- trunk/library/classes/Gems/Model.php 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/classes/Gems/Model.php 2012-11-08 16:10:47 UTC (rev 1007)
@@ -222,7 +222,8 @@
$model->setIfExists('grs_last_name', 'label', $this->translate->_('Last name'));
}
$model->set('name', 'label', $this->translate->_('Name'),
- 'column_expression', "CONCAT(COALESCE(CONCAT(grs_last_name, ', '), '-, '), COALESCE(CONCAT(grs_first_name, ' '), ''), COALESCE(grs_surname_prefix, ''))");
+ 'column_expression', "CONCAT(COALESCE(CONCAT(grs_last_name, ', '), '-, '), COALESCE(CONCAT(grs_first_name, ' '), ''), COALESCE(grs_surname_prefix, ''))",
+ 'fieldlist', array('grs_last_name', 'grs_first_name', 'grs_surname_prefix'));
$model->setIfExists('grs_address_1', 'label', $this->translate->_('Street'));
$model->setIfExists('grs_zipcode', 'label', $this->translate->_('Zipcode'));
Modified: trunk/library/classes/Gems/Util/DbLookup.php
===================================================================
--- trunk/library/classes/Gems/Util/DbLookup.php 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/classes/Gems/Util/DbLookup.php 2012-11-08 16:10:47 UTC (rev 1007)
@@ -53,6 +53,12 @@
/**
*
+ * @var Zend_Cache_Core
+ */
+ protected $cache;
+
+ /**
+ *
* @var Zend_Db_Adapter_Abstract
*/
protected $db;
@@ -376,19 +382,29 @@
/**
* Return key/value pairs of all staff members, currently active or not
*
- * @staticvar array $data
* @return array
*/
public function getStaff()
{
- static $data;
+ $cacheId = __CLASS__ . '_' . __FUNCTION__;
- if (! $data) {
- $data = $this->db->fetchPairs("SELECT gsf_id_user, CONCAT(COALESCE(gsf_last_name, '-'), ', ', COALESCE(gsf_first_name, ''), COALESCE(CONCAT(' ', gsf_surname_prefix), ''))
- FROM gems__staff ORDER BY gsf_last_name, gsf_first_name, gsf_surname_prefix");
+ if ($results = $this->cache->load($cacheId)) {
+ return $results;
}
- return $data;
+ $select = "SELECT gsf_id_user,
+ CONCAT(
+ COALESCE(gsf_last_name, '-'),
+ ', ',
+ COALESCE(gsf_first_name, ''),
+ COALESCE(CONCAT(' ', gsf_surname_prefix), '')
+ )
+ FROM gems__staff
+ ORDER BY gsf_last_name, gsf_first_name, gsf_surname_prefix";
+
+ $results = $this->db->fetchPairs($select);
+ $this->cache->save($results, $cacheId, array('staff'));
+ return $results;
}
public function getStaffGroups()
Modified: trunk/library/classes/MUtil/Model/DatabaseModelAbstract.php
===================================================================
--- trunk/library/classes/MUtil/Model/DatabaseModelAbstract.php 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/classes/MUtil/Model/DatabaseModelAbstract.php 2012-11-08 16:10:47 UTC (rev 1007)
@@ -722,12 +722,19 @@
// MUtil_Echo::track($key, $value, $filter, stripos($value, $filter));
if (stripos($value, $filter) !== false) {
if (null === $key) {
- $wheres[] = $sqlField . ' IS NULL';
+ $wheres[1] = $sqlField . ' IS NULL';
} else {
- $wheres[] = $sqlField . ' = ' . $adapter->quote($key);
+ $wheres[0][] = $adapter->quote($key);
}
}
}
+ if (isset($wheres[0])) {
+ if (count($wheres[0]) == 1) {
+ $wheres[0] = $sqlField . ' = ' . $wheres[0][0];
+ } else {
+ $wheres[0] = $sqlField . ' IN (' . implode(', ', $wheres[0]) . ')';
+ }
+ }
return $wheres;
}
@@ -761,7 +768,13 @@
foreach ($this->getItemsUsed() as $name) {
if ($this->get($name, 'label')) {
if ($expression = $this->get($name, 'column_expression')) {
- $fields[$name] = $expression;
+ if ($fieldList = $this->get($name, 'fieldlist')) {
+ foreach ((array) $fieldList as $field) {
+ $fields[$field] = $adapter->quoteIdentifier($field);
+ }
+ } else {
+ $fields[$name] = $expression;
+ }
} else {
$fields[$name] = $adapter->quoteIdentifier($name);
}
Modified: trunk/library/configs/db/patches.sql
===================================================================
--- trunk/library/configs/db/patches.sql 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/configs/db/patches.sql 2012-11-08 16:10:47 UTC (rev 1007)
@@ -441,3 +441,22 @@
ALTER TABLE `gems__tracks` CHANGE gtr_completed_event gtr_completed_event varchar(128) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' null;
-- GEMS VERSION: 50
+-- PATCH: Speedup respondent screen
+ALTER TABLE gems__respondent2org
+ ADD INDEX (gr2o_id_organization),
+ ADD INDEX (gr2o_opened_by),
+ ADD INDEX (gr2o_changed_by);
+
+ALTER TABLE gems__respondent2track
+ ADD INDEX (gr2t_id_track),
+ ADD INDEX (gr2t_id_user),
+ ADD INDEX (gr2t_id_organization),
+ ADD INDEX (gr2t_start_date);
+
+ALTER TABLE `gems__tokens` ADD INDEX (gto_id_organization);
+ALTER TABLE `gems__tokens` ADD INDEX (gto_id_respondent);
+
+ALTER TABLE gems__surveys ADD INDEX (gsu_surveyor_active);
+
+ALTER TABLE gems__tracks ADD INDEX (gtr_track_type), ADD INDEX (gtr_track_class);
+
Modified: trunk/library/configs/db/tables/gems__respondent2org.50.sql
===================================================================
--- trunk/library/configs/db/tables/gems__respondent2org.50.sql 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/configs/db/tables/gems__respondent2org.50.sql 2012-11-08 16:10:47 UTC (rev 1007)
@@ -27,8 +27,11 @@
PRIMARY KEY (gr2o_patient_nr, gr2o_id_organization),
UNIQUE KEY (gr2o_id_user, gr2o_id_organization),
+ INDEX (gr2o_id_organization),
INDEX (gr2o_opened),
- INDEX (gr2o_reception_code)
+ INDEX (gr2o_reception_code)
+ INDEX (gr2o_opened_by),
+ INDEX (gr2o_changed_by)
)
ENGINE=InnoDB
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
Modified: trunk/library/configs/db/tables/gems__respondent2track.40.sql
===================================================================
--- trunk/library/configs/db/tables/gems__respondent2track.40.sql 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/configs/db/tables/gems__respondent2track.40.sql 2012-11-08 16:10:47 UTC (rev 1007)
@@ -25,7 +25,11 @@
gr2t_created timestamp not null,
gr2t_created_by bigint unsigned not null,
- PRIMARY KEY (gr2t_id_respondent_track)
+ PRIMARY KEY (gr2t_id_respondent_track),
+ INDEX (gr2t_id_track),
+ INDEX (gr2t_id_user),
+ INDEX (gr2t_start_date),
+ INDEX (gr2t_id_organization)
)
ENGINE=InnoDB
auto_increment = 100000
Modified: trunk/library/configs/db/tables/gems__surveys.30.sql
===================================================================
--- trunk/library/configs/db/tables/gems__surveys.30.sql 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/configs/db/tables/gems__surveys.30.sql 2012-11-08 16:10:47 UTC (rev 1007)
@@ -47,6 +47,7 @@
PRIMARY KEY(gsu_id_survey),
INDEX (gsu_active),
+ INDEX (gsu_surveyor_active),
INDEX (gsu_code)
)
ENGINE=InnoDB
Modified: trunk/library/configs/db/tables/gems__tokens.200.sql
===================================================================
--- trunk/library/configs/db/tables/gems__tokens.200.sql 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/configs/db/tables/gems__tokens.200.sql 2012-11-08 16:10:47 UTC (rev 1007)
@@ -50,6 +50,8 @@
gto_created_by bigint unsigned not null,
PRIMARY KEY (gto_id_token),
+ INDEX (gto_id_organization);
+ INDEX (gto_id_respondent);
INDEX (gto_id_survey),
INDEX (gto_id_track),
INDEX (gto_id_round),
Modified: trunk/library/configs/db/tables/gems__tracks.30.sql
===================================================================
--- trunk/library/configs/db/tables/gems__tracks.30.sql 2012-11-08 13:25:32 UTC (rev 1006)
+++ trunk/library/configs/db/tables/gems__tracks.30.sql 2012-11-08 16:10:47 UTC (rev 1007)
@@ -30,7 +30,9 @@
PRIMARY KEY (gtr_id_track),
INDEX (gtr_track_name),
- INDEX (gtr_active)
+ INDEX (gtr_active),
+ INDEX (gtr_track_type),
+ INDEX (gtr_track_class)
)
ENGINE=InnoDB
auto_increment = 7000
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
