c++ - 在 Poco SQLite 中的表之间加入

Question

尽管 SQLite 支持关系数据库，但我不确定它是否在 Poco 库中实现。来自 poco-1.6.0-all/Data/SQLite/testsuite/src/SQLiteTest.cpp 的单元测试似乎没有示例，我无法在任何地方找到明确的确认是可能的。

在他们来自 http://pocoproject.org/docs/00200-DataUserManual.html 的示例中名称后有一个简单的选择:

std::string aName;
std::string match("Peter");
ses << "SELECT name FROM names WHERE name=?", into(aName), use(match), now;

假设我想从另一个包含公司描述的表中查看 Peter 工作的公司，给定公司 ID:

// Untested
std::string aName;
std::string aCompany;
std::string match("Peter");
ses << "SELECT name, company_name FROM names, companies WHERE name=? AND company_id=2", 
    into(aName), into(aCompany), use(match), now;

我该怎么做？

Answer 1

这是一道SQL题；这是使用 POCO 的方法:

Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");

tmp << "DROP TABLE IF EXISTS Simpsons", now;
tmp << "DROP TABLE IF EXISTS Companies", now;
tmp << "CREATE TABLE IF NOT EXISTS Simpsons (LastName VARCHAR, FirstName VARCHAR)", now;
tmp << "CREATE TABLE IF NOT EXISTS Companies (CompanyName VARCHAR, LastName VARCHAR, FirstName VARCHAR)", now;

tmp << "INSERT INTO Simpsons VALUES(?, ?)", bind("Simpson"), bind("Bart"), now;
tmp << "INSERT INTO Simpsons VALUES(?, ?)", bind("Simpson"), bind("Homer"), now;

tmp << "INSERT INTO Companies VALUES(?, ?, ?)", bind("Microsoft"), bind("Simpson"), bind("Bart"), now;
tmp << "INSERT INTO Companies VALUES(?, ?, ?)", bind("Oracle"), bind("Simpson"), bind("Homer"), now;

std::string fname, lname, cname;
tmp << "SELECT P.FirstName, P.LastName, C.CompanyName "
        "FROM Simpsons P, Companies C "
        "WHERE P.FirstName = ? AND P.FirstName = C.FirstName", bind("Bart"), into(fname), into(lname), into(cname), now;

std::cout << lname << ',' << fname << " works for " << std::endl;

输出:

Simpson,Bart works for Microsoft