How to create all tables in one transaction?

[Rofl113]

I tried to pass line by line to the transaction object, but nothing happens at all.
Here is an example of creation (I use SQLite3):

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "tokens" (
	"id"		INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"token"		TEXT NOT NULL UNIQUE,
	"idUser"	INTEGER NOT NULL,
	FOREIGN KEY("idUser")	REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"		INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"login"		TEXT NOT NULL UNIQUE,
	"password"	TEXT NOT NULL,
	"name"		TEXT NOT NULL
);
COMMIT;

Here is the code to read the file:

void initTables(const std::string& path) const
{
	std::ifstream streamFile (path);
	std::stringstream buffer;
	buffer << streamFile.rdbuf();
	auto transaction = m_dbBe->newTransaction();
	std::stringstream ss (buffer);
	std::string line;
	while (std::getline(ss, line, ';'))
	{
		line += ";";
		*transaction << line;
	}
}

But nothing happens and there are no errors. Am I using transactions incorrectly?

[an-tao]

you don't need to run the 'begin' and the 'commit' command when using transaction objects of drogon orm.

[Rofl113]

I tried without them, but nothing happened. Do I need to add a transaction execution wait?

[Rofl113]

I made a project to test and this is what I got:

// Drogon lib
#include <drogon/orm/DbClient.h>

int main()
{
	// INFO: crt Client
	auto clientDb = drogon::orm::DbClient::newSqlite3Client("filename=./test.db", 1);
	// INFO: crt Tables
	{
		const std::string cmd = R"CMD_SQL(
CREATE TABLE IF NOT EXISTS "tokens" (
	"id"		INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"token"		TEXT NOT NULL UNIQUE,
	"idUser"	INTEGER NOT NULL,
	FOREIGN KEY("idUser")	REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"		INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"login"		TEXT NOT NULL UNIQUE,
	"password"	TEXT NOT NULL,
	"name"		TEXT NOT NULL
);
)CMD_SQL";
		auto transaction = clientDb->newTransaction();
		std::stringstream ss (cmd);
		std::string line;
		while (std::getline(ss, line, ';'))
		{
			line += ";";
			*transaction << line;
		}
	}
	// INFO: check Tables
	clientDb->execSqlSync("SELECT * FROM \"tokens\";");
	clientDb->execSqlSync("SELECT * FROM \"users\";");
	// FINISH
	return EXIT_SUCCESS;
}

Application Output:

terminate called after throwing an instance of 'drogon::orm::SqlError'
  what():  no such table: tokens

[Rofl113]

Figured it out. I did SQL queries incorrectly - an empty query was added at the end. I noticed this when I changed the transaction to just calling execSqlSync. It turns out that the transaction does not cause an exception, but is simply silent? How normal is this?

[Rofl113]

Here is a complete example with getting an exception for each request. Is it possible to get an exception by the general method? After all, the transaction is stopped or rolled back when at least one error (exception) occurs, therefore it would be logical to somehow get the exception of the entire transaction and not every request. Is it possible?

#include <iostream>
// Drogon lib
#include <drogon/orm/DbClient.h>

void trim(std::string &s)
{
	s.erase(s.begin(), std::find_if(s.begin(), s.end(), [](unsigned char ch)
	{
		return !std::isspace(ch);
	}));
	s.erase(std::find_if(s.rbegin(), s.rend(), [](unsigned char ch)
	{
		return !std::isspace(ch);
	}).base(), s.end());
}

int main()
{
	std::remove("./test.db");
	// INFO: crt Client
	auto clientDbBe = drogon::orm::DbClient::newSqlite3Client("filename=./test.db", 1);
	// INFO: crt Tables
	{
		const std::string cmd = R"CMD_SQL(
CREATE TABLE IF NOT EXISTS "tokens" (
	"id"		INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"token"		TEXT NOT NULL UNIQUE,
	"idUser"	INTEGER NOT NULL,
	FOREIGN KEY("idUser")	REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE TABLE IF NOT EXISTS "users" (
	"id"		INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	"login"		TEXT NOT NULL UNIQUE,
	"password"	TEXT NOT NULL,
	"name"		TEXT NOT NULL
);
)CMD_SQL";
		auto transaction = clientDbBe->newTransaction();
		std::stringstream ss (cmd);
		std::string line;
		while (std::getline(ss, line, ';'))
		{
			trim(line);
			if (line.empty())
			{
				continue;
			}
			line += ";";
			*transaction << line
						 >> [](const drogon::orm::DrogonDbException &e)
							{
								std::cerr << "err:" << e.base().what() << std::endl;
								throw e;
							};
		}
	}
	// INFO: check Tables
	clientDbBe->execSqlSync("SELECT * FROM \"tokens\";");
	clientDbBe->execSqlSync("SELECT * FROM \"users\";");
	// FINISH
	return EXIT_SUCCESS;
}