Thread: [Gemstracker-svn] SF.net SVN: gemstracker:[1007] trunk/library (Page 8)

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

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.





Thread: [Gemstracker-svn] SF.net SVN: gemstracker:[1007] trunk/library (Page 8)

gemstracker-svn