CakePHP 3:临时SQL表的最佳实践 [英] CakePHP 3: Best Practice for Temporary SQL Tables
问题描述
亲爱的CakePHP 3开发人员，
Dear CakePHP 3 developers,
我想使用 SQL临时表
I'd like to use SQL's Temporary Tables in a CakePHP 3.4.13 project for a single run through a script. Going through Cake's documentation, there seems no direct way to tell CakePHP my desire. How would I best go about it, then?
我已经在 src/Model/Table/TempItemsTable.php 中准备了一个表:
I've prepared a Table in src/Model/Table/TempItemsTable.php:
namespace App\Model\Table;
use Cake\ORM\Table;
class TempItemsTable extends Table
{
public $fields = [
'id' => ['type' => 'integer'],
'con' => ['type' => 'string', 'length' => 255, 'null' => false],
'_constraints' => [
'primary' => ['type' => 'primary', 'columns' => ['id']]
]
];
public function initialize(array $config)
{
// $this->setTable(null);
}
}
使用$ fields告诉CakePHP所需的表模式的想法来自可能不相关的测试治具的文档.
The idea to use $fields to tell CakePHP the desired table schema comes from a possibly unrelated documentation for Test Fixtures.
但是如何告诉CakePHP不要在数据库中查找实际表?没有注释的行 $ this-> setTable(null); 是我的较差尝试，据说与
But how do I tell CakePHP not to look for an actual table in the database?
The uncommented line $this->setTable(null); was my poor attempt at that, which is supposedly similiar to the right way in earlier versions of CakePHP, but according to version 3.x documentation, setTable() doesn't accept null, while table() does, but it's deprecated as of 3.4 and also didn't change anything.
最后，当然，一旦我尝试通过 $ temp = TableRegistry :: get('TempItems'); :>
Finally, of course, I get this exception as soon as I try to access this "table" in a controller via $temp = TableRegistry::get('TempItems');:
SQLSTATE [42S02]:找不到基表或视图:1146表'mydatabase.temp_items'不存在
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mydatabase.temp_items' doesn't exist
帮助，我被困住了.:(
Help, I'm stuck. :(
推荐答案
不需要告诉它不查找表，实际上这与您想要做的相反您最终想要访问它.
There's no need to tell it to not look for the table, actually that's the opposite of what you want to do, given that you eventually want to access it.
基本上应该像往常一样配置表类，并且应该在应用程序导致对其进行访问之前创建临时数据库表.您可以手动编写原始表创建SQL，也可以从支持临时表的 \ Cake \ Database \ Schema \ TableSchema 实例生成它.
The table class should basically be configured as usual, and you should create the temporary database table before the application causes it to be accessed. You can either write the raw table creation SQL manually, or generate it from a \Cake\Database\Schema\TableSchema instance, which supports temporary tables.
您可以显式创建架构对象:
You can either explicitly create the schema object:
$schema = new \Cake\Database\Schema\TableSchema('temp_items');
$schema
->addColumn('id', ['type' => 'integer'])
->addColumn('con', ['type' => 'string', 'length' => 255, 'null' => false])
->addConstraint('primary', ['type' => 'primary', 'columns' => ['id']])
->setTemporary(true);
$TableObject->setSchema($schema);
或使用您的字段定义数组让表对象生成它:
or let the table object generate it, using your fields definition array:
$TableObject->setSchema($TableObject->fields);
$schema = $TableObject->getSchema()->setTemporary(true);
然后可以从架构对象生成表创建SQL并针对数据库运行它:
You can then generate the table creation SQL from the schema object and run it against the database:
$connection = $TableObject->getConnection();
$queries = $schema->createSql($connection);
$connection->transactional(
function (\Cake\Database\Connection $connection) use ($queries) {
foreach ($queries as $query) {
$stmt = $connection->execute($query);
$stmt->closeCursor();
}
}
);
$ queries 是创建表所需的SQL命令数组，类似以下内容:
$queries would be an array of SQL commands required to create the table, something along the lines of:
[
'CREATE TEMPORARY TABLE `temp_items` (
`id` INTEGER AUTO_INCREMENT,
`con` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
)'
]
请注意，如果不将架构分配给表对象，则可能会遇到缓存问题，因为在更改表定义并且不清除缓存时，缓存的架构将不再匹配.
Note that if you do not assign the schema to the table object, you could run into caching problems, as the cached schema wouldn't match anymore when you change the table definition and do not clear the cache.
另请参见
这篇关于CakePHP 3:临时SQL表的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
