[activeholdingco]

I tried to figure out where to request adding an event but I am not sure where.

Can we get a built in way to cache the db query for pages, partials, and templates. Im trying to avoid this:

select id, url from pages 0.0363 s

SELECT (if(pages.protocol='any', 'Any', if(pages.protocol='https', 'HTTPS only', 'HTTP only'))) as protocol_name, pages.* FROM pages WHERE (pages.id='87') LIMIT 0, 1 0.0389 s

select id, name, html_code, file_name from partials 0.548 s

Once I go live I will rarely be changing the pages, templates, and partials so it would be great to get an event or a built in way to cache this query permanently. If I want to clear the cache I can always do that with the built in methods.

Since it is an active record maybe there is a way to add an event to the active record itself when it runs the query so I can expand this to cache other things such as the maintenance configuration and customer groups.

Any ideas on where and how we can accomplish this?

[Aleksey]

Hi!

Have you considered (or tried) MySQL query caching (http://dev.mysql.com...uery-cache.html)? I have not tried it, but it looks like it can speed up database queries.

The idea with having an event in ActiveRecord is good as well, but it will require custom development from your side. And I just have no time for digging into this topic right away.

Thanks!

[Eric]

We talked about this in chat. We figure extending core:onBeforeDatabaseQuery to allow returning the data array, and skipping the database query, would be acceptable. However, that event could be useful for overloading the SQL query as well. So, we may want to use a dual purpose multi-dimensional array. We will discuss this internally and let you know.

[activeholdingco]

Eric, on 05 May 2011 - 04:35 PM, said:

We talked about this in chat. We figure extending core:onBeforeDatabaseQuery to allow returning the data array, and skipping the database query, would be acceptable. However, that event could be useful for overloading the SQL query as well. So, we may want to use a dual purpose multi-dimensional array. We will discuss this internally and let you know.

I will wait to hear.

The way I see it is something like this:

		public function execute($sql) 
		{
			if (Phpr::$traceLog)
				Phpr::$traceLog->write($sql, 'SQL');

			if (Phpr::$config && Phpr::$config->get('ENABLE_DEVELOPER_TOOLS') && Backend::$events) {
				$db_event = Backend::$events->fireEvent('core:onBeforeDatabaseQuery', $sql);
				if( $db_event['sql'] ) {
					$sql = $db_event['sql'];
				}
				if( $db_event['result'] ) {
					return $db_event['result'];
				}
			}

			$result = $this->driver()->execute($sql);

			...
		}

[activeholdingco]

After some trying of my own. It turns out this wont work using the existing events.

This may work still to override the $sql statment, but it will not work with the $result. Since the $result is a mysql resource and not an actual dataset it is useless. So disregard all of the above.

I have accomplished this when some new events below. Ive only test this with the module I am building. In phproad/modules/db/classes/db_sqlbase.php:

		/**
		 * Fetches all SQL result rows as a sequential array.
		 *
		 * @param string $sql An SQL SELECT statement.
		 * @param array $bind Data to bind into SELECT placeholders.
		 * @return array
		 */
		public function fetchAll($sql, $bind = null)
		{
            if (Backend::$events) {
                $fetch_event = Backend::$events->fireEvent('core:onBeforeDatabaseFetch', $sql);
                if( isset($fetch_event[0]['result']) ) {
                    return $fetch_event[0]['result'];
                }
            }

			$result = $this->query($this->prepare($sql, $bind));
			$fetch = $this->_fetchAll($result);

            if (Backend::$events)
                Backend::$events->fireEvent('core:onAfterDatabaseFetch', $sql, $fetch);

            return $fetch;
		}

		/**
		 * Fetches the first column of all SQL result rows as an array.
		 *
		 * The first column in each row is used as the array key.
		 *
		 * @param string $sql An SQL SELECT statement.
		 * @param array $bind Data to bind into SELECT placeholders.
		 * @return array
		 */
		public function fetchCol($sql, $bind = null) 
		{
            if (Backend::$events) {
                $fetch_event = Backend::$events->fireEvent('core:onBeforeDatabaseFetch', $sql);
                if( isset($fetch_event[0]['result']) ) {
                    return $fetch_event[0]['result'];
                }
            }

			$result = $this->query($this->prepare($sql, $bind));
			$fetch = $this->_fetchAll($result, 0);

            if (Backend::$events)
                Backend::$events->fireEvent('core:onAfterDatabaseFetch', $sql, $fetch);

            return $fetch;
		}

		/**
		 * Fetches the first column of the first row of the SQL result.
		 *
		 * @param string $sql An SQL SELECT statement.
		 * @param array $bind Data to bind into SELECT placeholders.
		 * @return string
		 */
		public function fetchOne($sql, $bind = null) 
		{
            if (Backend::$events) {
                $fetch_event = Backend::$events->fireEvent('core:onBeforeDatabaseFetch', $sql);
                if( isset($fetch_event[0]['result']) ) {
                    return $fetch_event[0]['result'];
                }
            }

			$result = $this->query($this->prepare($sql, $bind));
			$fetch = $this->driver()->fetch($result, 0);

            if (Backend::$events)
                Backend::$events->fireEvent('core:onAfterDatabaseFetch', $sql, $fetch);

            return $fetch;
		}
		
		/**
		 * Fetches the first row of the SQL result.
		 *
		 * @param string $sql An SQL SELECT statement.
		 * @param array $bind Data to bind into SELECT placeholders.
		 * @return array
		 */
		public function fetchRow($sql, $bind = null)
		{
            if (Backend::$events) {
                $fetch_event = Backend::$events->fireEvent('core:onBeforeDatabaseFetch', $sql);
                if( isset($fetch_event[0]['result']) ) {
                    return $fetch_event[0]['result'];
                }
            }

			$result = $this->query($this->prepare($sql, $bind));
            $fetch = $this->driver()->fetch($result);

            if (Backend::$events)
                Backend::$events->fireEvent('core:onAfterDatabaseFetch', $sql, $fetch);

            return $fetch;
		}

Im still working on it but as you can see we do it on the fetch requests so that we have the $sql query still and can pass in a dataset. This effectively allows me to return a custom dataset for a query.

[Aleksey]

Hi!

That looks good. I added the core:onBeforeDatabaseFetch and core:onAfterDatabaseFetch events to the roadmap.

Thank you

[Eric]

Implemented. Refer to the tracker: http://forum.lemonst...rdatabasefetch/