int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> datasetArg("t","dataset","Dataset name",true,"","string");
TCLAP::ValueArg<std::string> shapeArg("S","shapefile","Shape file",true,"","string");
TCLAP::ValueArg<std::string> datasetDescArg("s","datasetDescription","Dataset description",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Passwork. Default no password",false,"","string");
TCLAP::ValueArg<std::string> unitArg("U","units","New unit for the dataset",false,"Not set","string");
TCLAP::ValueArg<std::string> metaArg("m","metadata","File containing metadata",false,"None","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
TCLAP::SwitchArg overwriteSwitch("o","overwrite","Overwrite the previous dataset if present", cmd, false);
TCLAP::SwitchArg rasterSwitch("n","notrasterize","Not rasterize the shape (false by default)", cmd, false);
cmd.add(databaseArg);
cmd.add(datasetArg);
cmd.add(shapeArg);
cmd.add(datasetDescArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(unitArg);
cmd.add(metaArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString tableName = QString::fromUtf8(datasetArg.getValue().c_str());
QString shapeFile = QString::fromUtf8(shapeArg.getValue().c_str());
QString tableDesc = QString::fromUtf8(datasetDescArg.getValue().c_str());
QString unit = QString::fromUtf8(unitArg.getValue().c_str());
QString meta = QString::fromUtf8(metaArg.getValue().c_str());
bool replace = overwriteSwitch.getValue();
bool rasterize = !rasterSwitch.getValue();
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
QTime procTime;
procTime.start();
QString sql;
QSqlQuery qry(mydb);
if (replace)
{
sql = "DROP TABLE IF EXISTS " + tableName;
qry.exec(sql);
sql = "DELETE FROM datasetinfo WHERE dataset_id = '" + tableName + "'";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot remove previous dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
//We need to drop the _raster of the table
sql = "DROP TABLE IF EXISTS " + tableName + "_raster";
qry.exec(sql);
}
// This process insert the shapeFile into mysql using geometries
// This process if based on Jeff Lounsbury's shape to mysql program
insertShape insShape;
insShape.setTableName(tableName);
insShape.setShapeFile(shapeFile);
insShape.loadShape();
QStringList sqls;
sqls.append(insShape.getSQLs());
for (int pos = 0; pos <= sqls.count()-1;pos++)
{
sql = sqls[pos].replace("\n","");
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot remove previous dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
sql = "ALTER TABLE " + tableName + " ADD COLUMN num_cells INT(11) default 0";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot add numcells to dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
sql = "CREATE TABLE " + tableName + "_raster (";
sql = sql + "geokey VARCHAR(14) NOT NULL ,";
sql = sql + "xpos DECIMAL(7) NULL ,";
sql = sql + "ypos DECIMAL(7) NULL ,";
sql = sql + "shapeid INT(11) ,";
sql = sql + "PRIMARY KEY (geokey)) ENGINE = MyISAM";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot create dataset. It might already exists"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
//Get the cellSize of the database
double dbCellSize;
dbCellSize = 0.0;
sql = "SELECT cellSize FROM gbtconfig";
if (qry.exec(sql))
{
if (qry.first())
dbCellSize = qry.value(0).toDouble();
}
//Rasterize the shapefile
sql = "ALTER TABLE " + tableName + "_raster DISABLE KEYS";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot disable keys"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
Rasterizer rasterizeShape;
if (rasterize)
{
gbtLog(QObject::tr("Rasterizing shape"));
//Then we need to rasterize the shapes.
rasterizeShape.loadShapeFile(shapeFile,dbCellSize,-9999,tableName);
rasterizeShape.setTableName(tableName + "_raster");
uploadCSV uploadData;
uploadData.setTableName(tableName + "_raster");
uploadData.setPath(path);
uploadData.setDataBase(dbName);
uploadData.setRemote(remote);
uploadData.setHost(host);
uploadData.setPort(port.toInt());
uploadData.setUser(userName);
uploadData.setPassword(password);
rasterizeShape.start();
uploadData.start();
rasterizeShape.wait();
uploadData.wait();
}
sql = "ALTER TABLE " + tableName + "_raster ENABLE KEYS";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot enable keys"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
//Update the number of cells
if (rasterize)
{
sql = "UPDATE " + tableName + " TA SET num_cells = (SELECT count(geokey) FROM ";
sql = sql + tableName + "_raster TB WHERE TA.shapeid = TB.shapeid);";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot update number of cells"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
//Insert the dataset into the dataset information table
if (rasterize)
{
sql = "INSERT INTO datasetinfo (dataset_id,dataset_desc,dataset_unit,dataset_type,ncols,nrows,xllcenter,yllcenter,shapefiletype)";
sql = sql + " VALUES ('" + tableName +"',";
sql = sql + "'" + tableDesc +"',";
sql = sql + "'" + unit +"',2,";
sql = sql + QString::number(rasterizeShape.getNCols()) + ",";
sql = sql + QString::number(rasterizeShape.getNRows()) + ",";
sql = sql + QString::number(rasterizeShape.getXllCenter(),'f',11) + ",";
sql = sql + QString::number(rasterizeShape.getYllCenter(),'f',11) + ",";
sql = sql + QString::number(rasterizeShape.getShapeType()) + ")";
}
else
{
sql = "INSERT INTO datasetinfo (dataset_id,dataset_desc,dataset_unit,dataset_type,shapefiletype)";
sql = sql + " VALUES ('" + tableName +"',";
sql = sql + "'" + tableDesc +"',";
sql = sql + "'" + unit +"',2,";
sql = sql + QString::number(insShape.getShapeType()) + ")";
}
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot insert dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
if (meta != "None")
{
if (QFile::exists(meta))
{
QFile metaFile(meta);
if(metaFile.open(QIODevice::ReadOnly))
{
QByteArray ba;
ba = metaFile.readAll();
metaFile.close();
qry.prepare("UPDATE datasetinfo SET dataset_metadata = :metafile WHERE dataset_id = '" + tableName + "'");
qry.bindValue(":metafile", ba);
if (!qry.exec())
{
gbtLog(QObject::tr("Cannot insert the metadata."));
gbtLog(qry.lastError().databaseText());
}
}
else
{
gbtLog(QObject::tr("Cannot open the metadata file."));
gbtLog(qry.lastError().databaseText());
}
}
else
{
gbtLog(QObject::tr("The metadata file does not exits."));
gbtLog(qry.lastError().databaseText());
}
}
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
printf("\n");
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> calculationArg("c","calculation","Calculation to perform. For example: 'sum(DatasetA),sum(DatasetB)' ",true,"","string");
TCLAP::ValueArg<std::string> shapeArg("s","shapefile","Shapefile to use",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Passwork. Default no password",false,"","string");
TCLAP::ValueArg<std::string> descArg("S","descriptions","Descriptions for the calculations separated by coma. Default value is the calculation string",false,"","string");
TCLAP::ValueArg<std::string> fieldArg("f","fields","Field of shapefile to include in result separared by coma. Default value all",false,"all","string");
TCLAP::ValueArg<std::string> whereArg("w","where","WHERE statement for shapefile",false,"","string");
TCLAP::ValueArg<std::string> groupArg("g","group","GROUP fields statements for shapefile",false,"","string");
TCLAP::ValueArg<std::string> ouputArg("t","outputType","Output type: (h)uman readable or (c)omputer readable",false,"h","string");
TCLAP::ValueArg<std::string> outputFileArg("o","outputFile","If output type is computer, output xml file. Default ./executesql_out.xml",false,"./executesql_out.xml","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
cmd.add(databaseArg);
cmd.add(calculationArg);
cmd.add(ouputArg);
cmd.add(shapeArg);
cmd.add(whereArg);
cmd.add(groupArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(descArg);
cmd.add(fieldArg);
cmd.add(outputFileArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString calculation = QString::fromUtf8(calculationArg.getValue().c_str());
QString format = QString::fromUtf8(ouputArg.getValue().c_str());
QString description = QString::fromUtf8(descArg.getValue().c_str());
QString shape = QString::fromUtf8(shapeArg.getValue().c_str());
QString cmdWhere = QString::fromUtf8(whereArg.getValue().c_str());
QString cmdgroup = QString::fromUtf8(groupArg.getValue().c_str());
QString strfields = QString::fromUtf8(fieldArg.getValue().c_str());
QString outfile = QString::fromUtf8(outputFileArg.getValue().c_str());
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
QTime procTime;
procTime.start();
QString sql;
QSqlQuery qry(mydb);
if (!shapeFileExists(shape,mydb))
{
gbtLog(QObject::tr("The shapefile does not exists"));
mydb.close();
con.closeConnection();
return 1;
}
QStringList sfields;
sfields = getFields(strfields,shape,mydb);
if (constructSQL(calculation,sql,mydb,shape,sfields,cmdWhere,cmdgroup,description))
{
gbtLog(QObject::tr("Error in calculation."));
mydb.close();
con.closeConnection();
return 1;
}
//gbtLog(sql);
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Error in calculation."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
if (format == "h")
printResult(qry);
else
genXML(outfile,qry);
//---------------Print or save result-------------------------------
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
if (format == "h")
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
int main(int argc, char *argv[])
{
QString title;
title = title + "****************************************************************** \n";
title = title + " * MySQLtoFile 1.0 * \n";
title = title + " * This tool exports a table in a MySQL schema to various file * \n";
title = title + " * formats like STATA, SPSS, CSV (tab delimited), JSON and XML * \n";
title = title + " * This tool is part of CSPro Tools (c) ILRI, 2013 * \n";
title = title + " ****************************************************************** \n";
TCLAP::CmdLine cmd(title.toAscii().data(), ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> hostArg("H","host","MySQL host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","MySQL port. Default 3306.",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User to connect to MySQL",true,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Password to connect to MySQL",true,"","string");
TCLAP::ValueArg<std::string> schemaArg("s","schema","Schema in MySQL",true,"","string");
TCLAP::ValueArg<std::string> auditArg("a","audit","Target directory for the audit files. Default ./audit (created if not exists)",false,"./audit","string");
TCLAP::SwitchArg createSwitch("c","create","Create META dictionary tables", cmd, false);
TCLAP::SwitchArg loadSwitch("l","load","Load META dictionary tables", cmd, false);
TCLAP::SwitchArg deleteSwitch("d","delete","Delete previous data before load", cmd, false);
TCLAP::SwitchArg viewSwitch("v","views","Include views in dictionary tables", cmd, false);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(schemaArg);
cmd.add(auditArg);
cmd.parse( argc, argv );
//Getting the variables from the command
bool createMETA;
bool loadMETA;
bool deldata;
bool includeViews;
createMETA = createSwitch.getValue();
loadMETA = loadSwitch.getValue();
deldata = deleteSwitch.getValue();
includeViews = viewSwitch.getValue();
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString user = QString::fromUtf8(userArg.getValue().c_str());
QString pass = QString::fromUtf8(passArg.getValue().c_str());
QString schema = QString::fromUtf8(schemaArg.getValue().c_str());
QString auditPath = QString::fromUtf8(auditArg.getValue().c_str());
//Dictionary tables
ignoreTables << "dict_grpinfo";
ignoreTables << "dict_tblinfo";
ignoreTables << "dict_clminfo";
ignoreTables << "dict_relinfo";
ignoreTables << "dict_childinfo";
ignoreTables << "audit_log";
//Authorisation tables Not used since 2015-03-19. Backward compatibility
ignoreTables << "auth_group";
ignoreTables << "auth_group_permissions";
ignoreTables << "auth_permission";
ignoreTables << "auth_user";
ignoreTables << "auth_user_groups";
ignoreTables << "auth_user_user_permissions";
ignoreTables << "user_log";
//Django tables Not used since 2014. Backward compatibility
ignoreTables << "auth_message";
ignoreTables << "django_admin_log";
ignoreTables << "django_content_type";
ignoreTables << "django_session";
ignoreTables << "django_site";
//I18N tables
ignoreTables << "dict_iso639";
ignoreTables << "dict_lkpiso639";
ignoreTables << "dict_dctiso639";
{
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName(host);
db.setPort(port.toInt());
db.setDatabaseName(schema);
db.setUserName(user);
db.setPassword(pass);
bool METACreated;
METACreated = false;
bool METALoaded;
METALoaded = false;
bool deletePrevious;
deletePrevious = false;
QString data;
if (db.open())
{
getTableOrder(db);
if (createMETA)
{
log("Are you sure that you want to create META's tables? This will delete any previous META information INCLUDING the audit. (Y/N): ",false);
QTextStream createIn(stdin);
data = createIn.readLine();
if (data.toUpper().simplified() == "Y")
{
if (createMETATables(db) == 0)
METACreated = true;
else
return 1;
}
}
if (loadMETA)
{
if (deldata && !createMETA)
{
log("Are you sure that you want to delete any previous META's data? This WILL NOT delete the audit. (Y/N): ",false);
QTextStream deleteIn(stdin);
data = deleteIn.readLine();
if (data.toUpper().simplified() == "Y")
{
deletePrevious = true;
}
}
if (loadMETATables(db,deletePrevious,includeViews) == 0)
METALoaded = true;
else
return 1;
}
createMETAAudit(db,auditPath);
if (METACreated && !METALoaded)
{
log("META was created but not information was loaded. Use initmeta -l to load METAS's information");
}
}
else
{
log("Cannot connect to database");
log(db.lastError().databaseText());
return 1;
}
}
return 0;
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> datasetArg("t","dataset","Dataset name",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Password. Default no password",false,"","string");
TCLAP::ValueArg<std::string> descriptionArg("s","description","New description for the dataset",false,"","string");
TCLAP::ValueArg<std::string> unitArg("U","units","New unit for the dataset",false,"","string");
TCLAP::ValueArg<std::string> metaArg("m","metadata","File containing metadata",false,"None","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
cmd.add(databaseArg);
cmd.add(datasetArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(descriptionArg);
cmd.add(unitArg);
cmd.add(metaArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString tableName = QString::fromUtf8(datasetArg.getValue().c_str());
QString description = QString::fromUtf8(descriptionArg.getValue().c_str());
QString unit = QString::fromUtf8(unitArg.getValue().c_str());
QString meta = QString::fromUtf8(metaArg.getValue().c_str());
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
QTime procTime;
procTime.start();
QString sql;
QSqlQuery qry(mydb);
sql = "SELECT dataset_desc,dataset_unit FROM datasetinfo WHERE dataset_id = '" + tableName + "'";
if (qry.exec(sql))
{
if (qry.first())
{
if (description.isEmpty())
description = qry.value(0).toString();
if (unit.isEmpty())
unit = qry.value(1).toString();
}
else
{
gbtLog(QObject::tr("Dataset does not exists"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
else
{
gbtLog(QObject::tr("Cannot read dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
sql = "UPDATE datasetinfo SET dataset_desc = '" + description + "', ";
sql = sql + "dataset_unit = '" + unit + "'";
sql = sql + " WHERE dataset_id = '" + tableName + "'";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot update dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
if (meta != "None")
{
if (QFile::exists(meta))
{
QFile metaFile(meta);
if(metaFile.open(QIODevice::ReadOnly))
{
QByteArray ba;
ba = metaFile.readAll();
metaFile.close();
qry.prepare("UPDATE datasetinfo SET dataset_metadata = :metafile WHERE dataset_id = '" + tableName + "'");
qry.bindValue(":metafile", ba);
if (!qry.exec())
{
gbtLog(QObject::tr("Cannot insert the metadata."));
gbtLog(qry.lastError().databaseText());
}
qry.clear();
}
else
{
gbtLog(QObject::tr("Cannot open the metadata file."));
gbtLog(qry.lastError().databaseText());
}
}
else
{
gbtLog(QObject::tr("The metadata file does not exits."));
gbtLog(qry.lastError().databaseText());
}
}
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> datasetArg("t","datasets","Datasets to combine. For example: 'dataset1,dataset2,dataset3,....'",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Passwork. Default no password",false,"","string");
TCLAP::ValueArg<std::string> extentArg("e","extent","Extent: '(upperLeft degrees lat,log) (lowerRight degrees lat,log)'",false,"","string");
TCLAP::ValueArg<std::string> shpConstraintArg("S","constraintbyshapes","Constraint classification using shapes: ShapeDataSet:shapeID,ShapeID,....",false,"","string");
TCLAP::ValueArg<std::string> functionArg("f","combfunction","Combination function sum or (mul)tiplication Default sum",false,"sum","string");
TCLAP::ValueArg<std::string> targetDatasetArg("T","targetDataset","Target dataset if stored. Default combinationoutput",false,"combinationoutput","string");
TCLAP::ValueArg<std::string> targetDatasetDescArg("s","targetDatasetDesc","Description of target dataset if stored. Default: Result of combination ...",false,"Result of combination ...","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
TCLAP::SwitchArg storeSwitch("o","store","Store the combination as a dataset", cmd, false);
TCLAP::SwitchArg overWriteSwitch("O","overwrite","Overwrite dataset if exists", cmd, false);
cmd.add(databaseArg);
cmd.add(datasetArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(extentArg);
cmd.add(shpConstraintArg);
cmd.add(functionArg);
cmd.add(targetDatasetArg);
cmd.add(targetDatasetDescArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
bool store = storeSwitch.getValue();
bool overwrite = overWriteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString tableName = QString::fromUtf8(datasetArg.getValue().c_str());
QString extent = QString::fromUtf8(extentArg.getValue().c_str());
QString shapes = QString::fromUtf8(shpConstraintArg.getValue().c_str());
QString function = QString::fromUtf8(functionArg.getValue().c_str());
QString targetDataset = QString::fromUtf8(targetDatasetArg.getValue().c_str());
QString targetDatasetDesc = QString::fromUtf8(targetDatasetDescArg.getValue().c_str());
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
if (function.isEmpty())
function = "sum";
if (function != "sum" &&
function != "mul")
{
gbtLog(QObject::tr("Invalid function."));
return 1;
}
if (!tableName.contains(","))
{
gbtLog(QObject::tr("You need to indicate two or more classified datasets"));
mydb.close();
con.closeConnection();
return 1;
}
QStringList dataSets;
getDataSetList(tableName,dataSets);
if (dataSets.count() < 2)
{
gbtLog(QObject::tr("You need to indicate two or more classified datasets"));
mydb.close();
con.closeConnection();
return 1;
}
if (dataSets.count() > 20 && function == "sum")
{
gbtLog(QObject::tr("You cannot classify more than 20 datasets using sum as function"));
mydb.close();
con.closeConnection();
return 1;
}
QStringList letters;
letters.append("A");
letters.append("B");
letters.append("C");
letters.append("D");
letters.append("E");
letters.append("F");
letters.append("G");
letters.append("H");
letters.append("I");
letters.append("J");
letters.append("K");
letters.append("L");
letters.append("M");
letters.append("N");
letters.append("O");
letters.append("P");
letters.append("Q");
letters.append("R");
letters.append("S");
letters.append("T");
QTime procTime;
procTime.start();
QString sqlSelect;
QSqlQuery qry(mydb);
sqlSelect = "SELECT T" + letters[0] + ".geokey,";
sqlSelect = sqlSelect + "T" + letters[0] + ".xpos,";
sqlSelect = sqlSelect + "T" + letters[0] + ".ypos,";
sqlSelect = sqlSelect + "(";
int pos;
for (pos = 0; pos <= dataSets.count()-1;pos++)
{
if (function == "sum")
sqlSelect = sqlSelect + "T" + letters[pos] + ".classCode + ";
else
sqlSelect = sqlSelect + "T" + letters[pos] + ".classCode * ";
}
sqlSelect = sqlSelect.left(sqlSelect.length()-3) + ") as comCode";
QString sqlFrom;
sqlFrom = " FROM ";
for (pos = 0; pos <= dataSets.count()-1;pos++)
{
sqlFrom = sqlFrom + dataSets[pos] + " T" + letters[pos] + ",";
}
sqlFrom = sqlFrom.left(sqlFrom.length()-1);
QString extentWhere;
if (!extent.isEmpty())
extentWhere = getWhereClauseFromExtent(extent,mydb,dataSets[0]);
QString shapeFromSQL;
QString shapeWhereSQL;
QString shapeClause;
if (!shapes.isEmpty())
{
shapeClause = getShapeClause(shapes,mydb);
if (!shapeClause.isEmpty())
{
QString sqlcreate;
sqlcreate = "CREATE TEMPORARY TABLE tmpshapes (";
sqlcreate = sqlcreate + "geokey VARCHAR(14) NOT NULL ,";
sqlcreate = sqlcreate + "PRIMARY KEY (geokey))";
sqlcreate = sqlcreate + " ENGINE = MyISAM";
if (qry.exec(sqlcreate))
{
QString extentClause;
extentClause = extentWhere;
extentClause.replace("TA.","");
if (!extentClause.isEmpty())
sqlcreate = "INSERT INTO tmpshapes " + shapeClause + " AND " + extentClause;
else
sqlcreate = "INSERT INTO tmpshapes " + shapeClause;
gbtLog(QObject::tr("Preselecting shapes"));
if (qry.exec(sqlcreate))
{
shapeFromSQL = ", tmpshapes TK";
shapeWhereSQL = " TA.geokey = TK.geokey";
}
else
{
gbtLog(QObject::tr("Cannot insert temporary shapes."));
gbtLog(qry.lastError().databaseText());
}
}
else
{
gbtLog(QObject::tr("Cannot shapes temporary table."));
gbtLog(qry.lastError().databaseText());
}
}
}
sqlFrom = sqlFrom + shapeFromSQL;
//Link the tables
QString sqlWhere;
sqlWhere = " WHERE ";
for (pos = 1; pos <= dataSets.count()-1;pos++)
{
sqlWhere = sqlWhere + "T" + letters[pos-1] + ".geokey = ";\
sqlWhere = sqlWhere + "T" + letters[pos] + ".geokey AND ";
}
if (!extentWhere.isEmpty())
sqlWhere = sqlWhere + extentWhere + " AND ";
if (!shapeWhereSQL.isEmpty())
sqlWhere = sqlWhere + shapeWhereSQL + " AND ";
////Append the not null
//for (pos = 0; pos <= dataSets.count()-1;pos++)
// {
// sqlWhere = sqlWhere + "T" + letters[pos] + ".classCode IS NOT NULL AND ";
// }
sqlWhere = sqlWhere.left(sqlWhere.length()-5);
QString sql;
sql = sqlSelect + sqlFrom + sqlWhere;
sql = "INSERT INTO combdataset (geokey,xpos,ypos,comCode) " + sql;
gbtLog("Deleting previous combination");
QString sql2;
sql2 = "DELETE FROM combdataset";
if (qry.exec(sql2))
{
sql2 = "ALTER TABLE combdataset DISABLE KEYS";
if (qry.exec(sql2))
{
bool error;
QString dberror;
gbtLog("Combining datasets. Please wait....");
if (qry.exec(sql))
{
error = false;
sql2 = "ALTER TABLE combdataset ENABLE KEYS";
if (!qry.exec(sql2))
{
gbtLog("Unable to enable keys in combination table");
mydb.close();
con.closeConnection();
return 1;
}
}
else
{
dberror = qry.lastError().databaseText();
error = true;
}
if (error)
{
sql2 = "ALTER TABLE combdataset ENABLE KEYS";
if (!qry.exec(sql2))
{
gbtLog("Unable to enable keys in combination table");
mydb.close();
con.closeConnection();
return 1;
}
gbtLog("Unable to combine datasets");
gbtLog(dberror);
mydb.close();
con.closeConnection();
return 1;
}
}
else
{
gbtLog("Unable to disable keys in combination table");
mydb.close();
con.closeConnection();
return 1;
}
}
else
{
gbtLog("Unable to delete preivous combinations");
mydb.close();
con.closeConnection();
return 1;
}
if (store)
{
gbtLog("Storing combination as a dataset");
if (overwrite)
{
sql = "DROP TABLE IF EXISTS " + targetDataset;
qry.exec(sql);
sql = "DELETE FROM datasetinfo WHERE dataset_id = '" + targetDataset + "'";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot remove previous dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
sql = "CREATE TABLE " + targetDataset + " (";
sql = sql + "geokey VARCHAR(14) NOT NULL ,";
sql = sql + "xpos DECIMAL(7) NULL ,";
sql = sql + "ypos DECIMAL(7) NULL ,";
sql = sql + "cellvalue DECIMAL(14,5) NULL DEFAULT -9999 ,";
sql = sql + "classCode BIGINT NULL DEFAULT NULL ,";
//sql = sql + "classColour VARCHAR(9) NULL DEFAULT NULL ,"; //,
sql = sql + "PRIMARY KEY (geokey) ,";
sql = sql + "INDEX " + targetDataset + "_IDXVALUE (cellvalue ASC)) ENGINE = MyISAM";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot create dataset. It might already exists"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
sql = "ALTER TABLE " + targetDataset + " DISABLE KEYS";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot disable keys"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
sql = "INSERT INTO " + targetDataset + " (geokey,xpos,ypos,cellvalue) SELECT geokey,xpos,ypos,comCode FROM combdataset";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Unable to store combination"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
sql = "ALTER TABLE " + targetDataset + " ENABLE KEYS";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot enable keys"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
int ncols;
int nrows;
double xllCenter;
double yllCenter;
double dbCellSize;
int bottom;
int left;
if (!extent.isEmpty())
{
if (getGridValuesFromExtent(extent,mydb,dbCellSize,xllCenter,yllCenter,ncols,nrows,bottom,left))
{
gbtLog(QObject::tr("Error in extent"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
else
{
if (!shapes.isEmpty())
{
if (calcBoundFromShapes(shapes,mydb,dbCellSize,xllCenter,yllCenter,ncols,nrows,bottom,left))
{
gbtLog(QObject::tr("Error in constraining shapes"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
else
{
if (calcExtendFromGrid(firstGrid,mydb,dbCellSize,xllCenter,yllCenter,ncols,nrows,bottom,left))
{
gbtLog(QObject::tr("Error in grid extent"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
}
sql = "select count(distinct xpos),count(distinct ypos) from " + targetDataset;
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot read dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
qry.first();
ncols = qry.value(0).toInt();
nrows = qry.value(1).toInt();
sql = "select min(xpos),max(ypos) from " + targetDataset;
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot read dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
qry.first();
int xpos;
int ypos;
xpos = qry.value(0).toInt() -1;
ypos = qry.value(1).toInt() -1;
sql = "INSERT INTO datasetinfo (dataset_id,dataset_desc,dataset_type,ncols,nrows,xllcenter,yllcenter)";
sql = sql + " VALUES ('" + targetDataset +"',";
sql = sql + "'" + targetDatasetDesc +"',1,";
sql = sql + QString::number(ncols) + ",";
sql = sql + QString::number(nrows) + ",";
sql = sql + QString::number(((xpos * dbCellSize) - 180),'f',15) + ",";
sql = sql + QString::number((90 - (ypos * dbCellSize)),'f',15) + ")"; // + 0.0200 so its reduce
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot insert dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> datasetArg("t","dataset","Dataset name",true,"","string");
TCLAP::ValueArg<std::string> classArg("c","classdefinition","Class definition: 'ClassNumber:valueFrom ValueTo,ClassNumber:valueFrom ValueTo,...'",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Passwork. Default no password",false,"","string");
TCLAP::ValueArg<std::string> extentArg("e","extent","Extent: '(upperLeft degrees lat,log) (lowerRight degrees lat,log)'",false,"","string");
TCLAP::ValueArg<std::string> shpConstraintArg("S","constraintbyshapes","Constraint classification using shapes: ShapeDataSet:shapeID,ShapeID,....",false,"","string");
TCLAP::ValueArg<std::string> defValArg("v","defaultvalue","Default value 0",false,"0","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
cmd.add(databaseArg);
cmd.add(datasetArg);
cmd.add(classArg);
cmd.add(extentArg);
cmd.add(shpConstraintArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(defValArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString tableName = QString::fromUtf8(datasetArg.getValue().c_str());
QString classDef = QString::fromUtf8(classArg.getValue().c_str());
QString extent = QString::fromUtf8(extentArg.getValue().c_str());
QString shapes = QString::fromUtf8(shpConstraintArg.getValue().c_str());
QString defvalue = QString::fromUtf8(defValArg.getValue().c_str());
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
QTime procTime;
procTime.start();
//We need to check if dataset is a grid
getClasses(classDef);
if (classes.count() ==0 )
{
gbtLog(QObject::tr("There are no classes!"));
mydb.close();
con.closeConnection();
return 1;
}
QString sql;
QString headsql;
headsql = "UPDATE " + tableName + " TA";
QString WhereClause;
sql = " SET TA.classCode = CASE";
for (int t=0; t <= classes.count()-1;t++)
{
sql = sql + " WHEN (TA.cellvalue >= " + QString::number(classes[t].from,'f',5);
sql = sql + " AND TA.cellvalue <= " + QString::number(classes[t].to,'f',5);
sql = sql + ") THEN " + QString::number(classes[t].classNumber);
}
sql = sql + " ELSE " + defvalue + " END";
if (!extent.isEmpty())
{
WhereClause = getWhereClauseFromExtent(extent,mydb,tableName);
if (!WhereClause.isEmpty())
sql = sql + " WHERE " + WhereClause;
}
QSqlQuery qry(mydb);
if (!shapes.isEmpty())
{
QString shapeClause;
shapeClause = getShapeClause(shapes,mydb);
if (!shapeClause.isEmpty())
{
//
QString sqlcreate;
sqlcreate = "CREATE TEMPORARY TABLE tmpshapes (";
sqlcreate = sqlcreate + "geokey VARCHAR(14) NOT NULL ,";
sqlcreate = sqlcreate + "PRIMARY KEY (geokey))";
sqlcreate = sqlcreate + " ENGINE = MyISAM";
if (qry.exec(sqlcreate))
{
QString extentClause;
extentClause = WhereClause;
extentClause.replace("TA.","");
if (!extentClause.isEmpty())
sqlcreate = "INSERT INTO tmpshapes " + shapeClause + " AND " + extentClause;
else
sqlcreate = "INSERT INTO tmpshapes " + shapeClause;
gbtLog(QObject::tr("Preselecting shapes"));
if (qry.exec(sqlcreate))
{
headsql = headsql + ", tmpshapes TB";
if (sql.contains("WHERE"))
sql = sql + " AND TA.geokey = TB.geokey";
else
sql = sql + " WHERE TA.geokey = TB.geokey";
}
else
{
gbtLog(QObject::tr("Cannot insert temporary shapes."));
gbtLog(qry.lastError().databaseText());
}
}
else
{
gbtLog(QObject::tr("Cannot shapes temporary table."));
gbtLog(qry.lastError().databaseText());
}
}
}
gbtLog(QObject::tr("Classifying...Please wait"));
sql = headsql + sql;
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot classify dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> datasetArg("t","dataset","Dataset name",true,"","string");
TCLAP::ValueArg<std::string> outputArg("o","output","Output file",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Passwork. Default no password",false,"","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
cmd.add(databaseArg);
cmd.add(datasetArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(outputArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString tableName = QString::fromUtf8(datasetArg.getValue().c_str());
QString outputFile = QString::fromUtf8(outputArg.getValue().c_str());
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
QTime procTime;
procTime.start();
QString sql;
QSqlQuery qry(mydb);
sql = "SELECT dataset_metadata FROM datasetinfo WHERE dataset_id = '" + tableName + "'";
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot select metadata."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
else
{
if (qry.first())
{
QByteArray ba1 = qry.value(0).toByteArray();
QFile file(outputFile);
if (!file.open(QIODevice::WriteOnly | QIODevice::Text))
{
gbtLog(QObject::tr("Cannot create output file"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
file.write(ba1);
file.close();
}
else
{
gbtLog(QObject::tr("Dataset does not exits"));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
}
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
//Command line arguments
TCLAP::CmdLine cmd("GOBLET (c) 2012, International Livestock Research Institute (ILRI) \n Developed by Carlos Quiros ([email protected])", ' ', "1.0 (Beta 1)");
//Required arguments
TCLAP::ValueArg<std::string> databaseArg("d","database","Database name",true,"","string");
TCLAP::ValueArg<std::string> calculationArg("c","calculation","Calculation to perform. For example: 'sum(DatasetA),sum(DatasetB)' ",true,"","string");
TCLAP::ValueArg<std::string> ouputArg("o","output","Output type: (h)uman readable or (c)omputer readable",true,"","string");
//Non required arguments
TCLAP::ValueArg<std::string> pathArg("a","path","Path to database. Default .",false,".","string");
TCLAP::ValueArg<std::string> hostArg("H","host","Connect to host. Default localhost",false,"localhost","string");
TCLAP::ValueArg<std::string> portArg("P","port","Port number to use. Default 3306",false,"3306","string");
TCLAP::ValueArg<std::string> userArg("u","user","User. Default empty",false,"","string");
TCLAP::ValueArg<std::string> passArg("p","password","Passwork. Default no password",false,"","string");
TCLAP::ValueArg<std::string> descArg("s","descriptions","Descriptions for the calculations separated by coma. Default value is the calculation string",false,"","string");
//Switches
TCLAP::SwitchArg remoteSwitch("r","remote","Connect to remote host", cmd, false);
cmd.add(databaseArg);
cmd.add(calculationArg);
cmd.add(ouputArg);
cmd.add(pathArg);
cmd.add(hostArg);
cmd.add(portArg);
cmd.add(userArg);
cmd.add(passArg);
cmd.add(descArg);
//Parsing the command lines
cmd.parse( argc, argv );
//Getting the variables from the command
bool remote = remoteSwitch.getValue();
QString path = QString::fromUtf8(pathArg.getValue().c_str());
QString dbName = QString::fromUtf8(databaseArg.getValue().c_str());
QString host = QString::fromUtf8(hostArg.getValue().c_str());
QString port = QString::fromUtf8(portArg.getValue().c_str());
QString userName = QString::fromUtf8(userArg.getValue().c_str());
QString password = QString::fromUtf8(passArg.getValue().c_str());
QString calculation = QString::fromUtf8(calculationArg.getValue().c_str());
QString format = QString::fromUtf8(ouputArg.getValue().c_str());
QString description = QString::fromUtf8(descArg.getValue().c_str());
myDBConn con;
QSqlDatabase mydb;
if (!remote)
{
QDir dir;
dir.setPath(path);
if (con.connectToDB(dir.absolutePath()) == 1)
{
if (!dir.cd(dbName))
{
gbtLog(QObject::tr("The database does not exists"));
con.closeConnection();
return 1;
}
mydb = QSqlDatabase::addDatabase(con.getDriver(),"connection1");
}
}
else
{
mydb = QSqlDatabase::addDatabase("QMYSQL","connection1");
mydb.setHostName(host);
mydb.setPort(port.toInt());
if (!userName.isEmpty())
mydb.setUserName(userName);
if (!password.isEmpty())
mydb.setPassword(password);
}
mydb.setDatabaseName(dbName);
if (!mydb.open())
{
gbtLog(QObject::tr("Cannot open database"));
con.closeConnection();
return 1;
}
else
{
QTime procTime;
procTime.start();
QString sql;
QSqlQuery qry(mydb);
QList <TdatasetInfo> datasets;
if (constructSQL(calculation,sql,mydb,datasets))
{
gbtLog(QObject::tr("Error in calculation."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
QDomDocument doc;
QDomElement root;
doc = QDomDocument("GOBLETXML");
root = doc.createElement("CalcXML");
root.setAttribute("version", "1.0");
doc.appendChild(root);
QDomElement varName;
QDomText varValue;
QList<TfieldDef> fields;
int ncols;
ncols = 0;
if (!qry.exec(sql))
{
gbtLog(QObject::tr("Cannot reset dataset."));
gbtLog(qry.lastError().databaseText());
mydb.close();
con.closeConnection();
return 1;
}
int nfields;
nfields = qry.record().count();
QString nfield;
int pos;
QStringList descriptions;
descriptions = getDescriptions(description);
if (descriptions.count() != nfields)
{
descriptions.clear();
descriptions.append("Class code");
for (pos = 1; pos <= nfields-1; pos++)
{
descriptions.append(qry.record().field(pos).name());
}
}
for (pos = 0; pos <= nfields-1;pos++)
{
ncols++;
nfield = descriptions[pos];
nfield.replace("T" + datasets[pos].code + ".",datasets[pos].name + ".");
nfield.replace(".cellvalue","");
TfieldDef field;
field.fieldName = nfield;
field.fieldDesc = nfield; //Change for description
if (pos == 0)
{
field.fieldType = "CHAR";
}
else
field.fieldType = "DEC";
fields.append(field);
}
QDomElement shapevars;
shapevars = doc.createElement("Values");
root.appendChild(shapevars);
for (pos = 0; pos <= fields.count()-1;pos++)
{
varName = doc.createElement("Field");
shapevars.appendChild(varName);
varValue = doc.createTextNode(fields[pos].fieldDesc);
varName.appendChild(varValue);
}
int nrows;
nrows = 0;
while (qry.next())
{
nrows++;
}
nrows++;
QVector<QVector<QString> > grid;
grid.resize(nrows);
int r;
for(r=0; r<nrows; r++)
{
grid[r].resize(ncols);
}
for (pos = 0; pos <= fields.count()-1;pos++)
grid[0][pos] = fields[pos].fieldDesc;
r = 1;
qry.first();
QString value;
while (qry.isValid())
{
for (pos = 0; pos <= fields.count()-1;pos++)
{
if (fields[pos].fieldType == "DEC")
value = QString::number(qry.value(pos).toDouble(),'f',3);
else
value = qry.value(pos).toString();
grid[r][pos] = value;
}
r++;
qry.next();
}
if (format == "h")
{
QVector< int> colSizes;
colSizes.resize(ncols);
for (pos = 0; pos <= ncols-1;pos++)
colSizes[pos] = 0;
//Get the maximum size of each column
for (pos = 0; pos <= ncols-1;pos++)
{
for(r=0; r<nrows; r++)
{
if (grid[r][pos].length() +2 > colSizes[pos])
{
colSizes[pos] = grid[r][pos].length() +2;
}
}
}
//Print the table
printf("\n");
//Print top line
for (pos=0;pos<= ncols-1;pos++)
{
printf("+");
printf(fixLine("-",colSizes[pos]).toLocal8Bit().data());
}
printf("+");
printf("\n");
//Print the columns headings
for (pos=0;pos<= ncols-1;pos++)
{
printf("+");
printf(fixString(" " + grid[0][pos] + " ",colSizes[pos]).toLocal8Bit().data());
}
printf("+");
printf("\n");
//Print separation
for (pos=0;pos<= ncols-1;pos++)
{
printf("+");
printf(fixLine("-",colSizes[pos]).toLocal8Bit().data());
}
printf("+");
printf("\n");
//Print the values
for (r=1;r<=nrows-1;r++)
{
printf("|");
for (pos=0;pos<= ncols-1;pos++)
{
printf(fixString(" " + grid[r][pos] + " ",colSizes[pos]).toLocal8Bit().data());
printf("|");
}
printf("\n");
}
//Print the end
//Print separation
for (pos=0;pos<= ncols-1;pos++)
{
printf("+");
printf(fixLine("-",colSizes[pos]).toLocal8Bit().data());
}
printf("+");
printf("\n");
}
else
{
QDomElement shapeData;
shapeData = doc.createElement("CalcData");
root.appendChild(shapeData);
QDomElement rowData;
QDomElement fieldData;
QDomText fieldValue;
for (r=1;r<=nrows-1;r++)
{
rowData = doc.createElement("Row");
shapeData.appendChild(rowData);
for (pos=0;pos<= ncols-1;pos++)
{
fieldData = doc.createElement("Field");
fieldData.setAttribute("Name",fields[pos].fieldDesc);
rowData.appendChild(fieldData);
fieldValue = doc.createTextNode(grid[r][pos]);
fieldData.appendChild(fieldValue);
}
}
QTextStream out(stdout);
out.setCodec("UTF-8");
doc.save(out,1,QDomNode::EncodingFromTextStream);
}
int Hours;
int Minutes;
int Seconds;
int Milliseconds;
Milliseconds = procTime.elapsed();
Hours = Milliseconds / (1000*60*60);
Minutes = (Milliseconds % (1000*60*60)) / (1000*60);
Seconds = ((Milliseconds % (1000*60*60)) % (1000*60)) / 1000;
if (format == "h")
gbtLog("Finished in " + QString::number(Hours) + " Hours," + QString::number(Minutes) + " Minutes and " + QString::number(Seconds) + " Seconds.");
mydb.close();
con.closeConnection();
return 0;
}
return 0;
}
