我可以干run / sandbox sql命令吗？ [英] Can I dry run/sandbox sql commands?

问题描述

我试图建立一个小rails应用程序，以游戏格式向初学者教授sql。它的一部分是命令行，其中使用尝试解决与sql的问题。在后端我想对测试数据库运行他们的sql命令，并检查结果，以查看他们的命令是否正确。

我当前的想法是为课程的每个阶段创建sqlite数据库，然后为每个用户在达到该阶段时创建该数据库的副本。

我认为这将工作，但我关心的效率。

我的问题是，是否有一种有效的方式让用户运行SQL命令（包括drop，alter等），获取结果，但实际上没有做任何更改到db本身，本质上是任意sql命令的干运行。

解决方案

在Postgres中，你可以用 ：

$ b <$> BEGIN;

UPDATE foo ...：
INSERT bar ...;
SELECT baz FROM ...;
CREATE TABLE abc ...; - 甚至可以用于DDL语句
DROP TABLE def ...;
ALTER TABLE ghi ...：

ROLLBACK; - ！


手册中的更多内容： BEGIN ROLLBACK

注意一些事情不能回滚，虽然。对于实例序列不回滚。或者一些特殊命令，如 dblink 调用。

有些命令不能在与他人的事务中运行。像 CREATE DATABASE 或 VACUUM 。

此外，可能会有并发负载的副作用，例如死锁。不太可能，但。您可以根据需要设置事务隔离级别，以排除任何一方效果（以性能为代价）。

我不会这样做与敏感的数据。意外事故的风险太大。让用户执行任意代码是一种难以控制的风险。但对于培训环境，这应该是足够好了。

使用模板数据库备份。如果事情出错了，这是恢复基本状态的最快的方法。示例（查看最后一章）：

截断postgres数据库中的所有表

这也可以用作强力替代：提供一个原始的新每个学员的数据库。

I am trying to build a small rails app to teach sql to beginners in a game format. Part of it is a command line where the use tries to solve problems with sql. On the backend I’d like to run their sql command against a test database, and check the result to see if their command was correct.

My current idea is to create sqlite databases for each stage of the lesson, and then create a copy of that database for each user when they reach that stage.

I think that will work but I am concerned about efficiency.

My question is whether there is an efficient way to have users run SQL commands (including drop, alter, etc.), get the result, but not actually make any changes to the db itself, in essence a dry run of arbitrary sql commands. I am familiar with sqlite and postgres but am open to other databases.

解决方案

In Postgres, you can do much with transactions that are rolled back at the end:

BEGIN;

UPDATE foo ...:
INSERT bar ...;
SELECT baz FROM ...;
CREATE TABLE abc...;   -- even works for DDL statements
DROP   TABLE def...;
ALTER  TABLE ghi ...:

ROLLBACK;   -- !

More in the manual: BEGIN ROLLBACK

Be aware that some things cannot be rolled back, though. For instances sequences don't roll back. Or some special commands like dblink calls.

And some commands cannot be run in a transaction with others. Like CREATE DATABASE or VACUUM.

Also, there may be side effects with concurrent load, like deadlocks. Unlikely, though. You can set the transaction isolation level to your requirements to rule out any side effects (at some cost to performance).

I would not do this with sensible data. The risk of committing by accident is too great. And letting users execute arbitrary code is a risk that hardly containable. But for a training environment, that should be good enough.

Back it up with a template database. If something should go wrong, that's the fastest way to restore a basic state. Example (look at the last chapter):
Truncating all tables in a postgres database

This can also be used as brute force alternative: to provide a pristine new database for each trainee.

这篇关于我可以干run / sandbox sql命令吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！