/*--------------------------------------------------------------------------------*/
AString PostgresDatabase::GetColumnType(const AString& column) const
{
AString ctype = column.Word(1);
AString type;
if ((type = ConvertSimpleType(ctype)).Empty())
{
if (ctype == "references") type.printf("integer references %s", column.Word(2).str()); // reference to another table
else if (ctype == "references64") type.printf("bigint references %s", column.Word(2).str()); // reference to another table (with 64-bit id)
else type = ctype;
}
return type;
}
/*--------------------------------------------------------------------------------*/
bool PostgresDatabase::Open(const AString& host, const AString& username, const AString& password, const AString& database)
{
bool success = false;
if (!conn) {
AString str;
ClearResult();
connstr.Delete();
connstr.printf("postgresql://%s:%s@%s", username.str(), password.str(), host.str());
if (CheckConnection()) {
str = connstr;
if (database.Valid()) str.printf("/%s", database.ToLower().str());
success = (((conn = PQconnectdb(str.str())) != NULL) && (PQstatus(conn) == CONNECTION_OK));
if (success) {
if (database.Valid()) debug("Connected to database '%s' on %s\n", database.str(), host.str());
isopen = true;
}
else {
if (database.Valid()) debug("Failed to connect to database '%s' on %s: %s\n", database.str(), host.str(), GetErrorMessage().str());
else debug("Failed to connect server %s: %s\n", host.str(), GetErrorMessage().str());
Close();
}
}
else debug("No connection to server!\n");
}
return success;
}
/*--------------------------------------------------------------------------------*/
bool PostgresDatabase::CheckConnection(const AString& host)
{
AString connstr;
connstr.printf("postgresql://%s", host.str());
bool success = (PQping(connstr) == PQPING_OK);
debug("Checking connection to %s...%s\n", host.str(), success ? "success" : "**failure**");
return success;
}
/*--------------------------------------------------------------------------------*/
bool PostgresDatabase::PostgresQuery::Fetch(AString& results)
{
bool success = false;
if (res && nfields && (row < nrows)) {
uint_t i;
results.Delete();
for (i = 0; i < nfields; i++) {
if (i) results.printf(",");
const char *p = PQgetvalue(res, row, i);
switch (PQftype(res, i)) {
case TIMESTAMPOID: {
ADateTime dt;
dt.FromTimeStamp(p, true);
results += AString((uint64_t)dt);
//debug("%s->%llu->%s (%s)\n", p, (uint64)dt, dt.DateFormat("%Y-%M-%D %h:%m:%s.%S").str(), dt.UTCToLocal().DateFormat("%Y-%M-%D %h:%m:%s.%S").str());
break;
}
case TEXTOID:
case CHAROID:
case VARCHAROID:
results.printf("'%s'", AString(p).Escapify().str());
break;
default:
results.printf("%s", p);
break;
}
}
//debug("Row %u/%u: %s\n", row, nrows, results.str());
row++;
success = true;
}
return success;
}
bool SendFileToRecordingSlave(const AString& filename)
{
const ADVBConfig& config = ADVBConfig::Get();
AString cmd;
bool success;
//config.logit("'%s' -> '%s:%s'...", filename.str(), config.GetRecordingSlave().str(), filename.str());
cmd.printf("scp -p -C -P %u %s \"%s\" %s:\"%s\"", config.GetRecordingSlavePort(), config.GetSCPArgs().str(), filename.str(), config.GetRecordingSlave().str(), filename.str());
success = RunAndLogCommand(cmd);
//config.logit("'%s' -> '%s:%s' %s", filename.str(), config.GetRecordingSlave().str(), filename.str(), success ? "success" : "failed");
return success;
}
/*--------------------------------------------------------------------------------*/
bool PostgresDatabase::CreateTable(const AString& name, const AString& columns)
{
AString sql;
SQLQuery *query = NULL;
uint_t i, n = columns.CountColumns();
sql.printf("create table %s (", name.str());
for (i = 0; i < n; i++) {
AString column = columns.Column(i);
if (i > 0) sql.printf(", ");
sql.printf("%s %s", column.Word(0).str(), GetColumnType(column).str());
}
sql.printf(")");
if ((query = RunQuery(sql)) != NULL) {
bool success = query->GetResult();
delete query;
return success;
}
return false;
}
/*--------------------------------------------------------------------------------*/
AString PostgresDatabase::ConvertSimpleType(const AString& ctype) const
{
AString type;
if (ctype == "id") type = "integer not null primary key"; // primary key id
else if (ctype == "id64") type = "bigint not null primary key"; // primary key id (64-bit)
else if (ctype.Left(6) == "string") type.printf("varchar%s", ctype.Mid(6).SearchAndReplace("[", "(").SearchAndReplace("]", ")").str()); // string type / varchar
else if (ctype == "datetime") type = "timestamp";
else if (ctype == "float") type = "real";
else if (ctype == "double") type = "real";
else if (ctype == "short") type = "smallint";
else if (ctype == "int64") type = "bigint";
else if (ctype == "") type = "integer";
return type;
}
AString ADVBPatterns::GetPatternDefinitionsJSON()
{
AString str;
uint_t i, j, nfields;
const FIELD *fields = ADVBProg::GetFields(nfields);
str.printf("\"patterndefs\":");
str.printf("{\"fields\":[");
for (i = 0; i < nfields; i++) {
const FIELD& field = fields[i];
if (i) str.printf(",");
str.printf("{\"name\":\"%s\"", JSONFormat(field.name).str());
str.printf(",\"desc\":\"%s\"", JSONFormat(field.desc).str());
str.printf(",\"type\":%u", field.type);
str.printf(",\"assignable\":%s", field.assignable ? "true" : "false");
str.printf(",\"operators\":[");
bool flag = false;
for (j = 0; j < NUMBEROF(operators); j++) {
const OPERATOR& oper = operators[j];
if ((field.assignable == oper.assign) &&
(oper.fieldtypes & (1U << field.type))) {
if (flag) str.printf(",");
str.printf("%u", j);
flag = true;
}
}
str.printf("]}");
}
str.printf("]");
str.printf(",\"fieldnames\":{");
for (i = 0; i < nfields; i++) {
const FIELD& field = fields[i];
if (i) str.printf(",");
str.printf("\"%s\":%u", JSONFormat(field.name).str(), i);
}
str.printf("}");
str.printf(",\"operators\":[");
for (j = 0; j < NUMBEROF(operators); j++) {
const OPERATOR& oper = operators[j];
if (j) str.printf(",");
str.printf("{\"text\":\"%s\"", JSONFormat(oper.str).str());
str.printf(",\"desc\":\"%s\"", JSONFormat(oper.desc).str());
str.printf(",\"opcode\":%u", (uint_t)oper.opcode);
str.printf(",\"assign\":%s}", oper.assign ? "true" : "false");
}
str.printf("]");
str.printf(",\"orflags\":[");
for (j = 0; j < 2; j++) {
if (j) str.printf(",");
str.printf("{\"text\":\"%s\"", JSONFormat(j ? "or" : "and").str());
str.printf(",\"desc\":\"%s\"", JSONFormat(j ? "Or the next term" : "And the next term").str());
str.printf(",\"value\":%u}", j);
}
str.printf("]}");
return str;
}