int rlBussignalDatabase::writeDatabaseString(const char *item)
{
int ret;
char sqlbuf[rl_PRINTF_LENGTH];
// Try an UPDATE
ret = snprintf(sqlbuf,sizeof(sqlbuf)-1,"UPDATE %s SET datatype='%s', datavalue='%s' WHERE name='%s'",tableName,typebuf,buf,item);
if(ret < 0)
{
printf("sqlbuf too small in writeDatabaseString\n");
return -1;
}
ret = myquery(sqlbuf);
if(ret > 0) return 0; // success
// Try an INSERT
ret = snprintf(sqlbuf,sizeof(sqlbuf)-1,"INSERT INTO %s VALUES ('%s','%s','%s')",tableName,item,typebuf,buf);
if(ret < 0)
{
printf("sqlbuf too small in writeDatabaseString\n");
return -1;
}
ret = myquery(sqlbuf);
if(ret >= 0) return 0; // success
return -1; // failure
}
static void verify_col_data(const char *table, const char *col,
const char *exp_data)
{
static char query[MAX_TEST_QUERY_LENGTH];
MYSQL_RES *result;
MYSQL_ROW row;
int rc, field= 1;
if (table && col)
{
strxmov(query, "SELECT ", col, " FROM ", table, " LIMIT 1", NullS);
if (!opt_silent)
fprintf(stdout, "\n %s", query);
rc= mysql_query(mysql, query);
myquery(rc);
field= 0;
}
result= mysql_use_result(mysql);
mytest(result);
if (!(row= mysql_fetch_row(result)) || !row[field])
{
fprintf(stdout, "\n *** ERROR: FAILED TO GET THE RESULT ***");
exit(1);
}
if (strcmp(row[field], exp_data))
{
fprintf(stdout, "\n obtained: `%s` (expected: `%s`)",
row[field], exp_data);
DIE_UNLESS(FALSE);
}
mysql_free_result(result);
}
int SafetBinaryRepo::deleteRecords(const QString &nametable, const QString &whereclause) {
if (!_binaryDb.isOpen()) {
SYW << tr("Repositorio Binario (deleteRecords): la base de datos no est� abierta");
return 0;
}
QString strsql;
QSqlQuery myquery(_binaryDb);
bool executed;
strsql = _templatedeleteone.arg(nametable).arg(whereclause);
executed = myquery.exec(strsql);
if ( !executed ) {
SYW << tr("...Repositorio Binario (deleteRecords): No se puede ejecutar la sentencia: \"%1\"")
.arg(strsql);
return 0;
}
SYD << tr(" Repositorio Binario(deleteRecords. Ejecutada la sentencia:|%1|")
.arg(strsql);
SYD << tr("....SafetBinaryRepo::deleteRecords...ok!");
return 1;
}
void fill_tables(const char **query_list, unsigned query_count)
{
int rc;
const char **query;
DBUG_ENTER("fill_tables");
for (query= query_list; query < query_list + query_count;
++query)
{
rc= mysql_query(mysql, *query);
myquery(rc);
}
DBUG_VOID_RETURN;
}
static my_bool check_have_innodb(MYSQL *conn)
{
MYSQL_RES *res;
MYSQL_ROW row;
int rc;
my_bool result;
rc= mysql_query(conn, "show variables like 'have_innodb'");
myquery(rc);
res= mysql_use_result(conn);
DIE_UNLESS(res);
row= mysql_fetch_row(res);
DIE_UNLESS(row);
result= strcmp(row[1], "YES") == 0;
mysql_free_result(res);
return result;
}
static void execute_prepare_query(const char *query, ulonglong exp_count)
{
MYSQL_STMT *stmt;
ulonglong affected_rows;
int rc;
stmt= mysql_simple_prepare(mysql, query);
check_stmt(stmt);
rc= mysql_stmt_execute(stmt);
myquery(rc);
affected_rows= mysql_stmt_affected_rows(stmt);
if (!opt_silent)
fprintf(stdout, "\n total affected rows: `%ld` (expected: `%ld`)",
(long) affected_rows, (long) exp_count);
DIE_UNLESS(affected_rows == exp_count);
mysql_stmt_close(stmt);
}
QString SafetBinaryRepo::search(const QString &nametable, const QString &key) {
QString result;
if (!_binaryDb.isOpen()) {
SYE << tr("Repositorio Binario (search): la base de datos no est� abierta");
return result;
}
QString strsql;
QSqlQuery myquery(_binaryDb);
if (key.isEmpty()) {
strsql = _templatesearchall.arg(nametable);
}
else {
strsql = _templatesearch.arg(nametable).arg(key);
}
bool executed = myquery.exec(strsql);
if ( !executed ) {
SYW << tr("...Repositorio Binario (search):"
"No se puede ejecutar la sentencia: \"%1\"")
.arg(strsql);
//_binaryDb.close();
return result;
}
QSqlRecord rec = myquery.record();
while(myquery.next() ) {
for (int i= 0; i< rec.count(); i++){
result += myquery.value(i).toString().trimmed();
result += ",";
}
result.chop(1);
result += "\n";
}
result.chop(1);
return result;
}
static my_bool check_have_innodb(MYSQL *conn)
{
MYSQL_RES *res;
MYSQL_ROW row;
int rc;
my_bool result;
rc= mysql_query(conn,
"SELECT (support = 'YES' or support = 'DEFAULT' or support = 'ENABLED') "
"AS `TRUE` FROM information_schema.engines WHERE engine = 'innodb'");
myquery(rc);
res= mysql_use_result(conn);
DIE_UNLESS(res);
row= mysql_fetch_row(res);
DIE_UNLESS(row);
result= strcmp(row[1], "1") == 0;
mysql_free_result(res);
return result;
}
void SafetBinaryRepo::append(const QString &nametable, const QString &fields, const QString& values)
{
if (!_binaryDb.isOpen()) {
SYW << tr("Repositorio Binario (append): la base de datos no est� abierta");
return;
}
QString strsql;
QSqlQuery myquery(_binaryDb);
strsql = _templateinsert.arg(nametable).arg(fields).arg(values);
bool executed = myquery.exec(strsql);
if ( !executed ) {
SYW << tr("...Repositorio Binario (append): No se puede ejecutar la sentencia: \"%1\"")
.arg(strsql);
//_binaryDb.close();
return;
}
SYD << tr("...SafetBinaryRepo::append..ok!");
}
bool SafetBinaryRepo::createTable(const QString &nametable, const QString &fields) {
if (!_binaryDb.isOpen()) {
SYE << tr("Repositorio Binario (createTable): la base de datos no est� abierta");
return false;
}
QString strsql;
QSqlQuery myquery(_binaryDb);
bool executed;
strsql = _templatecreate.arg(nametable).arg(fields);
executed = myquery.exec(strsql);
if ( !executed ) {
SYE << tr("...Repositorio Binario (createTable): No se puede ejecutar la sentencia: \"%1\"")
.arg(strsql);
//_binaryDb.close();
return false;
}
SYD << tr("....SafetBinaryRepo::createTable...ok!");
}
/**
Connect to the server with options given by arguments to this application,
stored in global variables opt_host, opt_user, opt_password, opt_db,
opt_port and opt_unix_socket.
@param flag[in] client_flag passed on to mysql_real_connect
@param protocol[in] MYSQL_PROTOCOL_* to use for this connection
@param auto_reconnect[in] set to 1 for auto reconnect
@return pointer to initialized and connected MYSQL object
*/
static MYSQL* client_connect(ulong flag, uint protocol, my_bool auto_reconnect)
{
MYSQL* mysql;
int rc;
static char query[MAX_TEST_QUERY_LENGTH];
myheader_r("client_connect");
if (!opt_silent)
fprintf(stdout, "\n Establishing a connection to '%s' ...",
opt_host ? opt_host : "");
if (!(mysql= mysql_client_init(NULL)))
{
opt_silent= 0;
myerror("mysql_client_init() failed");
exit(1);
}
/* enable local infile, in non-binary builds often disabled by default */
mysql_options(mysql, MYSQL_OPT_LOCAL_INFILE, 0);
mysql_options(mysql, MYSQL_OPT_PROTOCOL, &protocol);
if (opt_plugin_dir && *opt_plugin_dir)
mysql_options(mysql, MYSQL_PLUGIN_DIR, opt_plugin_dir);
if (opt_default_auth && *opt_default_auth)
mysql_options(mysql, MYSQL_DEFAULT_AUTH, opt_default_auth);
if (!(mysql_real_connect(mysql, opt_host, opt_user,
opt_password, opt_db ? opt_db:"test", opt_port,
opt_unix_socket, flag)))
{
opt_silent= 0;
myerror("connection failed");
mysql_close(mysql);
fprintf(stdout, "\n Check the connection options using --help or -?\n");
exit(1);
}
mysql->reconnect= auto_reconnect;
if (!opt_silent)
fprintf(stdout, "OK");
/* set AUTOCOMMIT to ON*/
mysql_autocommit(mysql, TRUE);
if (!opt_silent)
{
fprintf(stdout, "\nConnected to MySQL server version: %s (%lu)\n",
mysql_get_server_info(mysql),
(ulong) mysql_get_server_version(mysql));
fprintf(stdout, "\n Creating a test database '%s' ...", current_db);
}
strxmov(query, "CREATE DATABASE IF NOT EXISTS ", current_db, NullS);
rc= mysql_query(mysql, query);
myquery(rc);
strxmov(query, "USE ", current_db, NullS);
rc= mysql_query(mysql, query);
myquery(rc);
have_innodb= check_have_innodb(mysql);
if (!opt_silent)
fprintf(stdout, "OK");
return mysql;
}
QString SafetBinaryRepo::calculateAverage(const QString& nametable, const QString& key,
int fromdate,
int todate) {
QString result;
SYD << tr("...SafetBinaryRepo::calculateAverage...fromdate:|%1|...todate:|%2|")
.arg(fromdate)
.arg(todate);
if (!_binaryDb.isOpen()) {
SYE << tr("Repositorio Binario (search): la base de datos no est� abierta");
return result;
}
QString strsql;
QSqlQuery myquery(_binaryDb);
if (fromdate == -1 && todate == -1) {
strsql = QString("select secondswait from %1 where wftask='%2';").arg(nametable).arg(key);
}
else if ( fromdate != -1 && todate == -1) {
strsql = QString("select secondswait from %1 where wftask='%2' and timecurr>=%3;")
.arg(nametable)
.arg(key)
.arg(fromdate);
}
else if ( fromdate == -1 && todate != -1) {
strsql = QString("select secondswait from %1 where wftask='%2' and timecurr<=%3;")
.arg(nametable)
.arg(key)
.arg(todate);
}
else {
strsql = QString("select secondswait from %1 where wftask='%2' and timecurr>=%3"
" and timecurr<=%4;")
.arg(nametable)
.arg(key)
.arg(fromdate)
.arg(todate);
}
SYD << tr(".......SafetBinaryRepo::calculateAverage....strsql:|%1|")
.arg(strsql);
bool executed = myquery.exec(strsql);
if ( !executed ) {
SYW << tr("...Repositorio Binario (search):"
"No se puede ejecutar la sentencia: \"%1\"")
.arg(strsql);
//_binaryDb.close();
return result;
}
int avg = 0, avcount = 0;
bool ok;
while(myquery.next() ) {
avg += myquery.value(0).toInt(&ok);
avcount++;
}
if (avcount == 0 ) {
return QString();
}
avg = avg / avcount;
SYD << tr(".......SafetBinaryRepo::calculateAverage....avg (2):|%1|")
.arg(avg);
int tnow = QDateTime::currentDateTime().toTime_t();
int tavg = tnow-avg;
QDateTime tdavg = QDateTime::fromTime_t(tavg);
QDateTime tdnow = QDateTime::fromTime_t(tnow);
int days = 0;
result = SafetWorkflow::humanizeDate(days,tdavg.toString("dd/MM/yyyy hh:mmap")
,"dd/MM/yyyy hh:mmap",tdnow);
return result;
}
QPair<QString,QString> SafetBinaryRepo::dataJSON(const QString &nametable,
const QString& filter,
const QStringList& todatefields,
const QStringList& tospandatefields
) {
QPair<QString,QString> result;
result.first = "safetlist =[\n";
result.second = "safetcolumns = [\n";
if (!_binaryDb.isOpen()) {
SYE << tr("Repositorio Binario (search): la base de datos no est� abierta");
return result;
}
QString strsql;
QSqlQuery myquery(_binaryDb);
if (filter.isEmpty()) {
strsql = _templatesearchall.arg(nametable);
}
else {
strsql = _templatesearch.arg(nametable).arg(filter);
}
bool executed = myquery.exec(strsql);
if ( !executed ) {
SYW << tr("...Repositorio Binario (JSON):"
"No se puede ejecutar la sentencia: \"%1\"")
.arg(strsql);
//_binaryDb.close();
return result;
}
QSqlRecord rec = myquery.record();
QDateTime mynow = QDateTime::currentDateTime();
while(myquery.next() ) {
result.first += "\n{";
for (int i= 0; i< rec.count(); i++){
QString fieldname = rec.fieldName(i).trimmed();
QString value = myquery.value(i).toString().trimmed();
if (todatefields.contains(fieldname)) {
bool ok;
uint seconds = value.toUInt(&ok);
value = QDateTime::fromTime_t(seconds).toString("dd/MM/yyyy hh:mmap");
}
else if (tospandatefields.contains(fieldname) ) {
bool ok;
int days = 0;
int seconds = value.toUInt(&ok);
QString datestr = QDateTime::fromTime_t(mynow.toTime_t()-seconds).toString("dd/MM/yyyy hh:mmap");
value = SafetWorkflow::humanizeDate(days,datestr,"dd/MM/yyyy hh:mmap",
mynow, SafetWorkflow::WaitTime);
}
result.first += QString("%1:\"%2\",")
.arg(fieldname)
.arg(value);
}
result.first.chop(1);
result.first += "},\n";
}
result.first.chop(2);
result.first += "\n];\n";
// Nombre de campos
for (int i= 0; i< rec.count(); i++){
result.second += QString(
"{ key:\"%1\",label:\"%1\","
"width:%2,resizeable:true,sortable:true},\n")
.arg(rec.fieldName(i).trimmed())
.arg(SafetYAWL::getScriptLen(rec.field(i)));
}
result.second.chop(2);
result.second += "\n];\n";
return result;
}
