/**
* @brief server querying function, which querys the server and get a
* list of boards from the server. Then we iterate over the list and
* print out the clients and lines attached to the boards.
* @param snm server name to query.
* @param prgn program number for the server.
* @param clp client pointer, which indicates we need to create a new
* client to the server before doing the querying thing.
* @return number of boards on the given server with progn.
*/
int queryServer(char * snm, int prgn, CLIENT * clp)
{
int dummy = 0;
int ccnt = 0, bcnt = 0 ;
BBoardp b, * bp = query_1(&dummy, clp);
if (!bp) {
printf("Server has no boards.\n");
return 0;
}
// no boards returned from the server.
fprintf(stdout, "\nQuery Results: \n");
for (b = *bp; b; b = b->next) {
printf("Board %s on server %s prognum %x has\n",
b->clients->clientdata.boardnm, snm, prgn);
// print clients information for the current board.
struct BClient * clnt = b->clients;
while(clnt) {
printf("\tclient on server %s displayed at %s with prognum %x\n",
clnt->clientdata.machinenm, clnt->clientdata.xdisplaynm,
clnt->clientdata.nprogram);
ccnt++;
clnt = clnt -> next;
}
// print the lines in this board.
struct ALine * lnp = b -> lines;
while(lnp) {
fprintf(stdout, "Line: (%d, %d) to (%d, %d), color 0x%x.\n",
lnp->ln.x1, lnp->ln.y1, lnp->ln.x2, lnp->ln.y2, lnp->ln.color);
lnp = lnp -> next;
}
bcnt++;
} // for, loop around the board list.
// print results.
fprintf(stdout, "\nTotal Boards: %d, total clients %d.\n\n", bcnt, ccnt);
return bcnt;
}
QVector<SpiceInfoData> SpiceInfo::querySpice(const QString &name)
{
QVector<SpiceInfoData> vecSpiceInfo;
QSqlDatabase db;
if(!createConnection(db))
{
// 连接使用完后需要释放回数据库连接池
ConnectionPool::closeConnection(db);
return vecSpiceInfo;
}
QSqlQuery query(db);
//spice_information
query.prepare("SELECT id, englishName, chineseName, typeId, property, sense, extract, origin, purpose, imagePath, FEMA, FDA, COE, GB, density, refractive, solubility FROM spice_information "
"WHERE chineseName like :chineseName OR englishName like :englishName");
query.bindValue(":chineseName", "%" + name + "%");
query.bindValue(":englishName", "%" + name + "%");
if(!query.exec())
{
qDebug() << __FILE__ << __LINE__ << query.lastError().text();
}
while(query.next())
{
SpiceInfoData spiceInfo;
spiceInfo.id = query.value(0).toInt();
spiceInfo.name.EnList = query.value(1).toString().split(';');
spiceInfo.name.CnList = query.value(2).toString().split(';');
spiceInfo.type = query.value(3).toInt();
spiceInfo.property = query.value(4).toString();
spiceInfo.sense = query.value(5).toString();
spiceInfo.extract = query.value(6).toString();
spiceInfo.origin = query.value(7).toString();
spiceInfo.purpose = query.value(8).toString();
spiceInfo.imagePath = query.value(9).toString();
spiceInfo.management.FEMA = query.value(10).toString();
spiceInfo.management.FDA = query.value(11).toString();
spiceInfo.management.COE = query.value(12).toString();
spiceInfo.management.GB = query.value(13).toString();
spiceInfo.physics.density = query.value(14).toString();
spiceInfo.physics.refractive = query.value(15).toString();
spiceInfo.physics.solubility = query.value(16).toString();
//spice_content
QSqlQuery query_content(db);
query_content.prepare("SELECT retentionTime, absoluteValue, relativeValue, contentId FROM spice_content "
"WHERE spiceId=:id");
query_content.bindValue(":id", spiceInfo.id);
if(!query_content.exec())
{
qDebug() << __FILE__ << __LINE__ << query_content.lastError().text();
}
SpiceContent content;
while(query_content.next())
{
content.retentionTime = query_content.value(0).toDouble();
content.absoluteContent = query_content.value(1).toDouble();
content.relativeContent = query_content.value(2).toDouble();
int contentId = query_content.value(3).toInt();
QSqlQuery query_1(db);
query_1.prepare("SELECT chineseName, englishName FROM content WHERE id=:id");
query_1.bindValue(":id",contentId);
if(query_1.exec())
{
if(query_1.next())
{
content.chineseName = query_1.value(0).toString();
content.englishName = query_1.value(1).toString();
spiceInfo.vecContent.push_back(content);
}
}
else
{
qDebug() << __FILE__ << __LINE__ << query_1.lastError().text();
}
}
vecSpiceInfo.push_back(spiceInfo);
}
// 连接使用完后需要释放回数据库连接池
ConnectionPool::closeConnection(db);
return vecSpiceInfo;
}
TEST_F(InsertSQLTests, InsertIntoSelectColumn) {
auto &txn_manager = concurrency::TransactionManagerFactory::GetInstance();
auto txn = txn_manager.BeginTransaction();
catalog::Catalog::GetInstance()->CreateDatabase(txn, DEFAULT_DB_NAME);
txn_manager.CommitTransaction(txn);
CreateAndLoadTable6();
CreateAndLoadTable7();
std::vector<ResultValue> result;
std::vector<FieldInfo> tuple_descriptor;
std::string error_message;
int rows_changed;
std::unique_ptr<optimizer::AbstractOptimizer> optimizer(
new optimizer::Optimizer());
// TEST CASE 1
std::string query_1("INSERT INTO test6 SELECT b,a,c FROM test7;");
txn = txn_manager.BeginTransaction();
auto plan =
TestingSQLUtil::GeneratePlanWithOptimizer(optimizer, query_1, txn);
txn_manager.CommitTransaction(txn);
EXPECT_EQ(plan->GetPlanNodeType(), PlanNodeType::INSERT);
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(optimizer, query_1, result,
tuple_descriptor, rows_changed,
error_message);
EXPECT_EQ(4, rows_changed);
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(
optimizer, "SELECT * FROM test6 WHERE a=8", result, tuple_descriptor,
rows_changed, error_message);
EXPECT_EQ(3, result.size());
EXPECT_EQ("8", TestingSQLUtil::GetResultValueAsString(result, 0));
EXPECT_EQ("55", TestingSQLUtil::GetResultValueAsString(result, 1));
EXPECT_EQ("999", TestingSQLUtil::GetResultValueAsString(result, 2));
// TEST CASE 2
std::string query_2("INSERT INTO test7 SELECT * FROM test6 WHERE a=1;");
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(optimizer, query_2, result,
tuple_descriptor, rows_changed,
error_message);
EXPECT_EQ(1, rows_changed);
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(
optimizer, "SELECT * FROM test7 WHERE a=1", result, tuple_descriptor,
rows_changed, error_message);
EXPECT_EQ(3, result.size());
EXPECT_EQ("1", TestingSQLUtil::GetResultValueAsString(result, 0));
EXPECT_EQ("22", TestingSQLUtil::GetResultValueAsString(result, 1));
EXPECT_EQ("333", TestingSQLUtil::GetResultValueAsString(result, 2));
// TEST CASE 3
std::string query_3("INSERT INTO test7 SELECT b,a,c FROM test6 WHERE a=2;");
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(optimizer, query_3, result,
tuple_descriptor, rows_changed,
error_message);
EXPECT_EQ(1, rows_changed);
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(
optimizer, "SELECT * FROM test7 WHERE a=11", result, tuple_descriptor,
rows_changed, error_message);
EXPECT_EQ(3, result.size());
EXPECT_EQ("11", TestingSQLUtil::GetResultValueAsString(result, 0));
EXPECT_EQ("2", TestingSQLUtil::GetResultValueAsString(result, 1));
EXPECT_EQ("0", TestingSQLUtil::GetResultValueAsString(result, 2));
// free the database just created
txn = txn_manager.BeginTransaction();
catalog::Catalog::GetInstance()->DropDatabaseWithName(txn, DEFAULT_DB_NAME);
txn_manager.CommitTransaction(txn);
}
TEST_F(InsertSQLTests, InsertIntoSelectSimpleAllType) {
auto &txn_manager = concurrency::TransactionManagerFactory::GetInstance();
auto txn = txn_manager.BeginTransaction();
catalog::Catalog::GetInstance()->CreateDatabase(txn, DEFAULT_DB_NAME);
txn_manager.CommitTransaction(txn);
CreateAndLoadTable4();
CreateAndLoadTable5();
std::vector<ResultValue> result;
std::vector<FieldInfo> tuple_descriptor;
std::string error_message;
int rows_changed;
std::unique_ptr<optimizer::AbstractOptimizer> optimizer(
new optimizer::Optimizer());
// TEST CASE 1
std::string query_1("INSERT INTO test5 SELECT * FROM test4;");
txn = txn_manager.BeginTransaction();
auto plan =
TestingSQLUtil::GeneratePlanWithOptimizer(optimizer, query_1, txn);
txn_manager.CommitTransaction(txn);
EXPECT_EQ(plan->GetPlanNodeType(), PlanNodeType::INSERT);
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(optimizer, query_1, result,
tuple_descriptor, rows_changed,
error_message);
EXPECT_EQ(2, rows_changed);
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(
optimizer, "SELECT * FROM test4 WHERE a=1", result, tuple_descriptor,
rows_changed, error_message);
EXPECT_EQ(11, result.size());
EXPECT_EQ("1", TestingSQLUtil::GetResultValueAsString(result, 0));
EXPECT_EQ("2", TestingSQLUtil::GetResultValueAsString(result, 1));
EXPECT_EQ("3", TestingSQLUtil::GetResultValueAsString(result, 2));
EXPECT_EQ("4", TestingSQLUtil::GetResultValueAsString(result, 3));
EXPECT_EQ("5.1", TestingSQLUtil::GetResultValueAsString(result, 4));
EXPECT_EQ("6.1", TestingSQLUtil::GetResultValueAsString(result, 5));
EXPECT_EQ("2017-10-10 00:00:00.000000+00",
TestingSQLUtil::GetResultValueAsString(result, 6));
EXPECT_EQ("A", TestingSQLUtil::GetResultValueAsString(result, 7));
EXPECT_EQ("a", TestingSQLUtil::GetResultValueAsString(result, 8));
EXPECT_EQ('1', TestingSQLUtil::GetResultValueAsString(result, 9).at(0));
EXPECT_EQ("true", TestingSQLUtil::GetResultValueAsString(result, 10));
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(
optimizer, "SELECT * FROM test5 WHERE a=1", result, tuple_descriptor,
rows_changed, error_message);
EXPECT_EQ(11, result.size());
EXPECT_EQ("1", TestingSQLUtil::GetResultValueAsString(result, 0));
EXPECT_EQ("2", TestingSQLUtil::GetResultValueAsString(result, 1));
EXPECT_EQ("3", TestingSQLUtil::GetResultValueAsString(result, 2));
EXPECT_EQ("4", TestingSQLUtil::GetResultValueAsString(result, 3));
EXPECT_EQ("5.1", TestingSQLUtil::GetResultValueAsString(result, 4));
EXPECT_EQ("6.1", TestingSQLUtil::GetResultValueAsString(result, 5));
EXPECT_EQ("2017-10-10 00:00:00.000000+00",
TestingSQLUtil::GetResultValueAsString(result, 6));
EXPECT_EQ("A", TestingSQLUtil::GetResultValueAsString(result, 7));
EXPECT_EQ("a", TestingSQLUtil::GetResultValueAsString(result, 8));
EXPECT_EQ('1', TestingSQLUtil::GetResultValueAsString(result, 9).at(0));
EXPECT_EQ("true", TestingSQLUtil::GetResultValueAsString(result, 10));
TestingSQLUtil::ExecuteSQLQueryWithOptimizer(
optimizer, "SELECT * FROM test5 WHERE a=11", result, tuple_descriptor,
rows_changed, error_message);
EXPECT_EQ(11, result.size());
EXPECT_EQ("11", TestingSQLUtil::GetResultValueAsString(result, 0));
EXPECT_EQ("12", TestingSQLUtil::GetResultValueAsString(result, 1));
EXPECT_EQ("13", TestingSQLUtil::GetResultValueAsString(result, 2));
EXPECT_EQ("14", TestingSQLUtil::GetResultValueAsString(result, 3));
EXPECT_EQ("15.1", TestingSQLUtil::GetResultValueAsString(result, 4));
EXPECT_EQ("16.1", TestingSQLUtil::GetResultValueAsString(result, 5));
EXPECT_EQ("2017-10-11 00:00:00.000000+00",
TestingSQLUtil::GetResultValueAsString(result, 6));
EXPECT_EQ("B", TestingSQLUtil::GetResultValueAsString(result, 7));
EXPECT_EQ("b", TestingSQLUtil::GetResultValueAsString(result, 8));
EXPECT_EQ('2', TestingSQLUtil::GetResultValueAsString(result, 9).at(0));
EXPECT_EQ("false", TestingSQLUtil::GetResultValueAsString(result, 10));
// free the database just created
txn = txn_manager.BeginTransaction();
catalog::Catalog::GetInstance()->DropDatabaseWithName(txn, DEFAULT_DB_NAME);
txn_manager.CommitTransaction(txn);
}
int main(int argc, char** argv) {
if ( argc < 2 ) {
std::cerr << "Arg is missing" << std::endl;
usage();
return EXIT_FAILURE;
}
/*
* Dealing with settings
*/
m_settings settings;
if ( argc > 2 ) {
int c;
while ((c = getopt (argc, argv, "h:u:p:d:s:f:")) != -1 ) {
switch (c) {
case 'h':
settings.insert("hostname", optarg);
break;
case 'u':
settings.insert("username", optarg);
break;
case 'p':
settings.insert("password", optarg);
break;
case 'd':
settings.insert("driver", optarg);
break;
case 's':
settings.insert("database", optarg);
break;
case 'f':
settings.insert("import_type", optarg);
break;
case '?':
return EXIT_FAILURE;
}
}
} else {
QCoreApplication::setOrganizationName("nsrl_toolkit");
QCoreApplication::setApplicationName("rds_bench");
}
if ( check_settings(settings) == false )
return EXIT_FAILURE;
QSqlDatabase db;
qint64 rows = 0;
qint64 total_time = 0;
// Create the db object
if ( init_db(db, settings) == false )
return EXIT_FAILURE;
QSqlQuery query_1(db);
QSqlQuery query_2(db);
db.transaction();
if ( db.driverName().compare("PGSQL") == 0 )
start_pgsql(rows, total_time, db, query_1, query_2);
if ( db.driverName().compare("MYSQL") == 0 )
start_mysql(rows, total_time, db, query_1, query_2);
db.rollback();
/*
* Ending
*/
if ( rows > 0 ) {
std::cout << "Elapsed time: " << total_time << " milliseconds (" << (float)total_time / 1000.0 << " seconds)" << std::endl;
std::cout << "Processed lines: " << rows << std::endl;
std::cout << "Speed: " << (float)rows / ((float)total_time / 1000.0 ) << " rows / second" << std::endl;
} else {
std::cout << "No line returned" << std::endl;
}
// Close and destroy the db object
db.close();
QSqlDatabase::removeDatabase(settings.value("driver"));
return EXIT_SUCCESS;
}
