大量查询，如果模板存储在数据库中，或者从缓存中删除时出错 [英] Large number of queries if templates stored in database, or error on delete from cache

问题描述

在数据库中存储一些模板，如 this answer 数据库查询的数量大大增加.例如，分析器显示页面上的 20 个查询中有 12 个与搜索模板相关，但没有一个存储在数据库中.我的印象是，只有在文件结构中没有可用的模板时才会进行搜索.有没有办法只在需要的模板不在文件结构中时才查询数据库?

With storing a few templates in a database as found in this answer there is a great increase in the number of database queries. For example, profiler shows 12 of 20 queries on a page were related to searching for templates although none was stored in the database. I was under the impression that searches were done only if a template was not available within the file structure. Is there a method to query the database only if the required template is not in the file structure?

实验表明，注释掉 vol.volbundle.twig_database_loader 服务的 tags 部分可以消除不必要的查询，但仍然可以在数据库中找到模板.没有 tags 块会发生以下错误

Experiments have shown that commenting out the tags section of the vol.volbundle.twig_database_loader service eliminates the unnecessary queries but can still find a template in the database. Without the tags block the following error occurs

InvalidArgumentException:模板名称new_opp"无效(格式是bundle:section:template.format.engine"

InvalidArgumentException: Template name "new_opp" is not valid (format is "bundle:section:template.format.engine"

当模板被编辑、持久化并尝试使用以下代码从缓存中删除其前身时.[在 tags 块存在的情况下，缓存的模板将被删除！] 这似乎是因为 tags 块允许在缓存删除期间找到数据库加载器.

when a template is edited, persisted and an attempt is made to delete its predecessor from cache with the following code. [With the tags block present, the cached template is deleted!] This appears to be the case because the tags block allows the database loader to be found during cache delete.

    $fileCache = $this->container->get('twig')->getCacheFilename($name);
    if (is_file($fileCache)) {
        @unlink($fileCache);
    } 

那么奇怪的是，当块不存在时，控制器使用数据库加载器.

So it is odd, then, that the database loader is used by the controller when the block is not present.

vol.volbundle.twig_database_loader:
    class: VolVolBundleToolsTwigDatabaseLoader
    arguments: [@doctrine.orm.entity_manager]
    tags:
        - { name: twig.loader }

TwigDatabaseLoader

namespace VolVolBundleTools;
use Twig_Error_Loader;
/**
 * Description of DatabaseTwigLoader
 *
 */
class TwigDatabaseLoader implements Twig_LoaderInterface
{

    private $entityManager;

    public function __construct($entityManager)
    {
        $this->entityManager = $entityManager;
    }

    public function getSource($name)
    {
        if (false === $source = $this->getValue('source', $name)) {
            throw new Twig_Error_Loader(sprintf('Template "%s" does not exist.', $name));
        }

        return $source;
    }

    public function isFresh($name, $time)
    {
        if (false === $lastModified = $this->getValue('last_modified', $name)) {
            return false;
        }

        return $lastModified <= $time;
    }

    public function getCacheKey($name)
    {
// check if exists
        return 'db:' . $name;
    }
    protected function getValue($column, $name)
    {
        $conn = $this->entityManager->getConnection();
        $sth = $conn->prepare('SELECT '.$column.' FROM template WHERE name = :name');
        $sth->execute(array(':name' => (string) $name));

        return $sth->fetchColumn();
    }
}

编译器

namespace VolVolBundleDependencyInjectionCompiler;

use SymfonyComponentDependencyInjectionReference;
use SymfonyComponentDependencyInjectionContainerBuilder;
use SymfonyComponentDependencyInjectionCompilerCompilerPassInterface;

/**
 * Description of TwigDatabaseLoaderPass
 *
 */
class TwigDatabaseLoaderPass implements CompilerPassInterface
{

    public function process(ContainerBuilder $container)
    {
        $definition = $container->getDefinition('twig');
        $definition->addMethodCall('setLoader', array(new Reference('vol.volbundle.twig_database_loader')));
    }
}

推荐答案

原来大量查询只发生在dev模式！这是通过在 MySQL 中启用查询日志来确定的.执行模板的编辑然后渲染它然后查看日志表明只有存储的模板调用了模板查询.其他模板均未出现在查询日志中.结案！

It turns out that the large number of queries only occurs in dev mode! This was determined by enabling the query log in MySQL. Exercising the editing of a template and then rendering it then reviewing the log showed that only the stored template invoked a template query. None of the other templates appeared in the query log. Case closed!

这篇关于大量查询，如果模板存储在数据库中，或者从缓存中删除时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！