void CompatibilityManager::migrateAccounts(const shared_ptr<IPortalDatabase> &database)
{
shared_ptr<IDbConnection> globalConnection = Engine::instance()->createSystemConnection();
DataTable result;
String sql = _S("select * from os_accounts");
database->execute(sql,result);
for(uint32 r=0;r<result.rows();r++)
{
String id = result.get(r,_S("id"));
shared_ptr<DataAccount> dataAccount(OS_NEW DataAccount());
dataAccount->read(result[r]);
dataAccount->description = _S("Imported from portal '") + database->getPortal()->getName();
String sql = String::format(_S("select count(*) from os_accounts where id='%S'").c_str(), id.c_str());
int nRecordExists = globalConnection->value_of(sql);
bool alreadyExists = (nRecordExists>0);
DbSqlValues values;
dataAccount->write(values);
if(alreadyExists)
globalConnection->executeStatement(globalConnection->prepare_update(DBTABLES::ACCOUNTS_TABLE, values, DBTABLES::ACCOUNTS::ID, Convert::toSQL(dataAccount->id)), values);
else
globalConnection->executeStatement(globalConnection->prepare_insert(DBTABLES::ACCOUNTS_TABLE, values), values);
}
}
DataItem IDbConnection::queryValue(const String &sql)
{
DataItem value;
DataTable result;
if(query(sql, result))
if(result.rows() > 0 && result.columns() > 0)
value = *result[0][0];
return value;
}
uint32 IDbConnection::value_of(const String &sql) // TOCLEAN, rimpiazzare con la queryValue
{
DataTable result;
if(query(sql, result) == false)
return false;
uint32 value = 0;
if(result.rows() > 0 && result.columns() > 0)
value = *result[0][0];
return value;
}
void CompatibilityManager::resigner(const shared_ptr<IPortalDatabase> &database, shared_ptr<IdeSession> session)
{
shared_ptr<ObjectsUser> user = session->getUser(database);
if(user != nullptr)
{
Buffer signature = user->signature;
if( (signature.getSize() == 1) && (signature.getAt(0) == 0) )
{
// Need re-sign.
DataTable result;
String sql = String::format(_S("select id,entity_author,author from os_entries where author='%S' union select id,entity_author,author from os_entries where entity_author='%S'").c_str(),user->id->toUTF16().c_str(),user->id->toUTF16().c_str());
database->execute(sql,result);
for(uint32 r=0;r<result.rows();r++)
{
String id = result.get(r,_S("id"));
String entityAuthor = result.get(r,_S("entity_author"));
String author = result.get(r,_S("author"));
shared_ptr<ObjectsIObject> object = database->getPortal()->getObject(database, id.to_ascii());
if(object != nullptr)
{
LanguageResult acceptable = object->acceptable(database);
if(acceptable.empty())
{
if( (entityAuthor == user->id->toUTF16()) && (author == user->id->toUTF16()) )
{
shared_ptr<ObjectsIRevisionable> revisionable = objects_revisionable_cast(object);
if(revisionable != nullptr)
{
bool done = revisionable->signEntity(session->getPrivateKey(), database->getPortal()->getPortalID());
OS_ASSERT(done);
}
}
if(author == user->id->toUTF16())
{
bool done = object->sign(session->getPrivateKey(), database->getPortal()->getPortalID());
OS_ASSERT(done);
}
LanguageResult result = object->store(database);
OS_ASSERT(result.empty());
}
}
}
}
}
}
shared_ptr<EntitiesEntities> EntitiesEntity::getChilds(const shared_ptr<IPortalDatabase> &database, const ObjectsTypes &types, const RangeUint32 &range, shared_ptr<DbSqlSelect> select) const
{
OS_ASSERT(select != nullptr);
OS_ASSERT(select->count == false);
OS_LOCK(m_cs);
String sql;
// Genera l'sql di caricamento dei figli
_getSql(database, types, range, false, select, sql);
String childsKey = getChildsKey(types);
shared_ptr<EntitiesEntities> childs = m_childs.get(childsKey);
// Controlla se i figli della tipologia specificata sono gi stati caricati in precedenza
if(childs != nullptr)
{
if(childs->getRange() == range && childs->getSql() == sql)
return childs; // Se il range in cui sono stati caricati i figli e l'sql di estrazione corrispondono non serve effettuare nuovamente il caricamento...
// Nota: se il range richiesto non coincide con quello attuale o l'sql differente bisogna ricaricare i figli
// Anche se il range attuale comprendesse quello richiesto bisogna comunque ricaricare i figli altrimenti dall'esterno,
// una volta richiesto un range, scorrendo i figli non si avrebbe la certezza che siano nel range richiesto (a meno di complicare i vari giri)
m_childs.remove(childsKey);
}
// Inizializza la lista dei figli prima di aprire il database (la connessione potrebbe fallire, ma la lista deve essere comunque creata)
childs.reset(OS_NEW EntitiesEntities(const_cast <EntitiesEntity *>(this)->get_this_ptr(), sql));
m_childs.push_back(childsKey, childs);
DataTable result;
database->execute(sql, result);
for(uint32 i = 0; i < result.rows(); i++)
{
String child_id = result.get(i, DBTABLES::ENTITY);
childs->push_back(child_id.to_ascii());
}
return childs;
}
bool IExtensionsExtension::isExtensionRegistered(shared_ptr<Portal> portal, shared_ptr<IPortalDatabase> db, Version &version) const
{
OS_ASSERT(portal != nullptr);
OS_EXCEPT_IF(db == nullptr, "Invalid database");
shared_ptr<DbSqlSelect> select(OS_NEW DbSqlSelect(DBTABLES::EXTENSIONS_TABLE));
select->fields.add(DbSqlField(DBTABLES::EXTENSIONS::VERSION));
select->where.add(DBTABLES::EXTENSIONS::ID, Convert::toSQL(getID().toUTF16()));
select->limit.setCount(1);
DataTable result;
if(db->execute(select, result) == false)
return false;
if(result.rows() == 0)
return false;
version.fromString(static_cast<String>(*result[0][0]).to_ascii());
return true;
}
bool P2pMachine::load(const String &id)
{
try
{
shared_ptr<IDbConnection> connection = Engine::instance()->createSystemConnection();
shared_ptr<DbSqlSelect> select(OS_NEW DbSqlSelect(_S("os_machines")));
select->where.add(_S("id"), Convert::toSQL(id));
select->limit.setCount(1);
DataTable result;
connection->query(select, result);
if(result.rows() == 1)
{
DataTableRow row = result[0];
DbValue<String> id;
id.read(row, _S("id"));
if(m_id.fromHex(id->to_ascii()) == false)
{
OS_ASSERTFALSE();
return false;
}
DbValue<Buffer> public_key;
public_key.read(row, _S("public_key"));
m_publicKey = public_key;
return true;
}
}
catch(std::exception &e)
{
OS_LOG_ERROR(e.what());
}
return false;
}
String Connection::preAnalyze(const String &sql)
{
static boost::recursive_mutex cs;
OS_LOCK(cs);
// Per vedere le sql nel charts:
// RealtimeStatsManager::signal(sql, 1, RealtimeStatsCounter::ctAbsolute, _S("status"));
// Da notare che vengono archiviate nella os_monitor solo le query analizzate dalla "explain".
// Una "insert", non usando indici, non viene catalogata, a meno che non abbia sotto-query.
if(sql.find(_S("explain query plan ")) != String::npos)
return _S("");
if(sql.find(_S("os_monitor")) != String::npos)
return _S("");
static shared_ptr<Connection> connectionAnalyze = nullptr;
if(connectionAnalyze == nullptr)
{
shared_ptr<Driver> driver = boost::dynamic_pointer_cast<Driver>(getDriver());
String path = driver->getDatabasePath(_S("monitor.db"));
if(FileSystem::instance()->exists(path) == false)
{
// Crea il database se non esiste
File file;
String msg = _S("Cannot create file '") + path + _S("'");
OS_EXCEPT_IF(file.open(path, File::ofWrite) == false, msg.to_ascii());
}
connectionAnalyze.reset(OS_NEW Connection(path, getDriver()->createOptions(), getDriver()));
int32 res = _tsqlite3_open(path.c_str(), &connectionAnalyze->m_connection);
OS_EXCEPT_IF(res != SQLITE_OK, sqlite_last_error(connectionAnalyze->m_connection, res));
// Creazione tabella
String sqlCreate = _S("CREATE TABLE IF NOT EXISTS os_monitor (filename text,sql text,thread integer,conn integer,trans integer, qorder integer,qfrom integer,detail text,indexUsed text)");
doExecute(connectionAnalyze->m_connection, sqlCreate);
}
// I valori dei campi li setto a ?, per poter fare delle distinct sui risultati
String keySql = sql;
keySql.trim();
keySql = regex_replace(keySql, RegexManager::instance()->getRegex(_S("'(.*?)'")), _S("<s>")); // Valori dei campi
keySql = regex_replace(keySql, RegexManager::instance()->getRegex(_S("[-+]?[0-9]*\\.?[0-9]+")), _S("<n>")); // Numeri
keySql = keySql.replace_all(_S("\n"),_S(""));
keySql = keySql.replace_all(_S("\r"),_S(""));
keySql = keySql.replace_all(_S("\t"),_S(""));
keySql = keySql.replace_all(_S("'"),_S("")); // Futile, per evitare errori nella insert nella os_monitor
for(;;) // Comprimo gli spazi
{
String x = keySql;
keySql = keySql.replace_all(_S(" "),_S(" "));
if(x == keySql)
break;
}
uint32 conn = uint32(reinterpret_cast<uint64>(this));
boost::any threadID = PlatformManager::instance()->getCurrentThreadID();
// Tenta un cast a uint32
uint32 *thread = boost::any_cast<uint32>(&threadID);
int32 trans = m_transactionId;
DataTable result;
doQuery(m_connection, _S("explain query plan ") + sql,result);
for(uint32 r=0;r<result.rows();r++)
{
int32 order = *result.get(r,_S("order"));
int32 from = *result.get(r,_S("from"));
String detail = *result.get(r,_S("detail"));
String indexUsed;
String::size_type posWithIndex = detail.find(_S("WITH INDEX"));
if(posWithIndex != String::npos)
{
indexUsed = detail.substr(posWithIndex+10);
indexUsed = indexUsed.replace_all(_S("ORDER BY"),String::EMPTY);
indexUsed.trim();
}
String sqlInsert = String::format(_S("insert into os_monitor values ('%S','%S',%u,%u,%u,%d,%d,'%S','%S')").c_str(), m_filename.c_str(), keySql.c_str(), conn, thread != nullptr ? *thread : 0, trans, order, from, detail.c_str(), indexUsed.c_str());
doExecute(connectionAnalyze->m_connection, sqlInsert);
}
String trace = String::format(_S("Sqlite monitor: sql='%S',conn=%u,thread=%u,trans=%u\n").c_str(), sql.c_str(), conn, thread != nullptr ? *thread : 0, trans);
//OS_TRACE(trace);
//RealtimeStatsManager::signal(_S("Sqlite ") + keySql.mid(0,6), 1, RealtimeStatsCounter::ctRelative, _S("items"));
return keySql;
}
void CompatibilityManager::renameIDS(const shared_ptr<IPortalDatabase> &database)
{
/*
if(id == 2) // Reputation
newID = database->getUserReputationID(
else if(id == 7) // Avatar
else if(id == 14) // Vote
else if(id == 17) // PollVote
}
*/
// Reputations
{
DataTable result;
String sql = _S("select id, author, reference from os_reputations");
database->execute(sql,result);
for(uint32 r=0;r<result.rows();r++)
{
String id = result.get(r,_S("id"));
String author = result.get(r,_S("author"));
String reference = result.get(r,_S("reference"));
ObjectID newID = database->getUserReputationID(author.to_ascii(), reference.to_ascii());
String sql = _S("update os_reputations set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
sql = _S("update os_entries set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
}
}
// Avatars
{
DataTable result;
String sql = _S("select id, author from os_avatars");
database->execute(sql,result);
for(uint32 r=0;r<result.rows();r++)
{
String id = result.get(r,_S("id"));
String author = result.get(r,_S("author"));
ObjectID newID = database->getUserAvatarID(author.to_ascii());
String sql = _S("update os_avatars set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
sql = _S("update os_entries set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
}
}
// Vote
{
DataTable result;
String sql = _S("select id, author, reference from os_votes");
database->execute(sql,result);
for(uint32 r=0;r<result.rows();r++)
{
String id = result.get(r,_S("id"));
String author = result.get(r,_S("author"));
String reference = result.get(r,_S("reference"));
ObjectID newID = database->getUserVoteID(author.to_ascii(), reference.to_ascii());
String sql = _S("update os_votes set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
sql = _S("update os_entries set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
}
}
// PollVote
{
DataTable result;
String sql = _S("select id, author, reference from os_polls_votes");
database->execute(sql,result);
for(uint32 r=0;r<result.rows();r++)
{
String id = result.get(r,_S("id"));
String author = result.get(r,_S("author"));
String reference = result.get(r,_S("reference"));
ObjectID newID = database->getUserPollVoteID(author.to_ascii(), reference.to_ascii());
String sql = _S("update os_polls_votes set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
sql = _S("update os_entries set id=") + Convert::toSQL(newID) + _S(" where id=") + Convert::toSQL(id);
database->execute(sql);
}
}
}
void IdeTableQuery::onPreRender()
{
ControlBase::onPreRender();
shared_ptr<XMLDocument> document = getDocument();
shared_ptr<XMLNode> node_root = document->create(_S("table_query"));
DataTable result;
if(getPage()->getDatabase()->execute(m_sql,result))
{
shared_ptr<XMLNode> node_header = node_root->addChild(_S("header"));
for(uint32 c = 0; c < result.columns(); c++)
{
String columnTitle = getColumnTitle(c);
if(columnTitle.empty())
columnTitle = result.getColumnName(c);
shared_ptr<XMLNode> node_column = node_header->addChild(_S("column"));
node_column->setAttributeString(_S("name"), columnTitle);
switch(getColumnType(c))
{
case IdeTableQuery::ctString:
{
node_column->setAttributeString(_S("type"), _S("string"));
} break;
case IdeTableQuery::ctScore:
{
node_column->setAttributeString(_S("type"), _S("score"));
} break;
case IdeTableQuery::ctEntityID:
{
node_column->setAttributeString(_S("type"), _S("entity-id"));
} break;
case IdeTableQuery::ctObjectID:
{
node_column->setAttributeString(_S("type"), _S("object-id"));
} break;
case IdeTableQuery::ctUserID:
{
node_column->setAttributeString(_S("type"), _S("user-id"));
} break;
case IdeTableQuery::ctShortDateTime:
{
node_column->setAttributeString(_S("type"), _S("short-datetime"));
} break;
case IdeTableQuery::ctLongDateTime:
{
node_column->setAttributeString(_S("type"), _S("long-datetime"));
} break;
}
}
shared_ptr<XMLNode> node_data = node_root->addChild(_S("data"));
for(uint32 r=0;r<result.rows();r++)
{
shared_ptr<XMLNode> node_data_row = node_data->addChild(_S("row"));
for(uint32 c=0;c<result.columns();c++)
{
shared_ptr<XMLNode> node_data_row_item = node_data_row->addChild(_S("item"));
String value = result.get(r,c);
switch(getColumnType(c))
{
case IdeTableQuery::ctScore:
{
node_data_row_item->setAttributeString(_S("value"), String::format(_S("%1.1f").c_str(), double(result.get(r,c))));
} break;
case IdeTableQuery::ctEntityID:
{
// Sbagliata
shared_ptr<XMLPortalExporter> exporter(OS_NEW XMLPortalExporter(node_data_row_item, getPage(), XMLPortalExporter::emLite));
shared_ptr<ObjectsIObject> object = getPage()->getObject(value.to_ascii());
if(object != nullptr)
{
object->exportXML(exporter);
}
} break;
case IdeTableQuery::ctObjectID:
{
shared_ptr<XMLPortalExporter> exporter(OS_NEW XMLPortalExporter(node_data_row_item, getPage(), XMLPortalExporter::emLite));
shared_ptr<ObjectsIObject> object = getPage()->getObject(value.to_ascii());
if(object != nullptr)
{
object->exportXML(exporter);
}
} break;
case IdeTableQuery::ctUserID:
{
shared_ptr<XMLPortalExporter> exporter(OS_NEW XMLPortalExporter(node_data_row_item, getPage(), XMLPortalExporter::emLite));
shared_ptr<ObjectsIObject> object = getPage()->getObject(value.to_ascii());
if(object != nullptr)
{
object->exportXML(exporter);
}
} break;
case IdeTableQuery::ctShortDateTime:
case IdeTableQuery::ctLongDateTime:
{
DateTime dt;
dt.fromString(value);
node_data_row_item->setAttributeDateTime(_S("value"), dt);
} break;
default:
{
node_data_row_item->setAttributeString(_S("value"), value);
} break;
}
}
}
}
}
void Forum::computeStatistics(shared_ptr<IPortalDatabase> db, shared_ptr<ObjectsIObject> object)
{
ComponentBase::computeStatistics(db, object);
shared_ptr<ObjectsSection> section = objects_section_cast(object);
shared_ptr<ForumStats> stats(OS_NEW ForumStats());
stats->reference = section->getEntityID().toUTF16();
stats->topics = 0;
stats->posts = 0;
uint32 directTopics=0;
uint32 directPosts=0;
// Calcolo le statistiche sui topics figli
{
String sql = String::format(_S("select count(*), sum(os_discussions_stats.posts) from os_discussions_stats, os_snapshot_objects where \
os_snapshot_objects.parent = '%S' \
and os_snapshot_objects.type in (%d,%d) \
and os_snapshot_objects.visible = 1 \
and os_discussions_stats.reference = os_snapshot_objects.entity \
and os_discussions_stats.parent = '%S'").c_str(),
section->getEntityID().toUTF16().c_str(),
portalObjectTypeText,
portalObjectTypePoll,
section->getEntityID().toUTF16().c_str()
);
DataTable table;
db->execute(sql, table);
directTopics = *table[0][0];
directPosts = *table[0][1];
}
uint32 indirectTopics=0;
uint32 indirectPosts=0;
// Calcolo le statistiche sui forums figli
{
String sql = String::format(String(_S("\
select sum(") + OS_FORUMS_FORUM_STATS_TABLE + _S(".topics), sum(") + OS_FORUMS_FORUM_STATS_TABLE + _S(".posts) \
from ") + OS_FORUMS_FORUM_STATS_TABLE + _S(",os_snapshot_objects,os_sections \
where \
os_snapshot_objects.entity = ") + OS_FORUMS_FORUM_STATS_TABLE + _S(".reference and \
os_snapshot_objects.current = os_sections.id and \
os_sections.component = '%S' and \
os_snapshot_objects.parent = '%S' and \
os_snapshot_objects.visible = 1")).c_str(),
getID().toUTF16().c_str(),
section->getEntityID().toUTF16().c_str());
DataTable table;
db->execute(sql, table);
indirectTopics = *table[0][0];
indirectPosts = *table[0][1];
}
stats->reference = section->getEntityID().toUTF16();
stats->topics = directTopics + indirectTopics;
stats->posts = directPosts + indirectPosts;
// Calcolo le statistiche riferite ai topic figli
{
String sql = String::format(_S("\
select os_discussions_stats.reference, os_discussions_stats.last_entry, os_discussions_stats.last_entry_date \
from \
os_snapshot_objects, \
os_discussions_stats \
where \
os_snapshot_objects.entity = os_discussions_stats.reference and \
os_snapshot_objects.parent = '%S' and \
os_snapshot_objects.type in (%d,%d) and \
os_snapshot_objects.visible = 1 \
order by os_discussions_stats.last_entry_date desc \
limit 1").c_str(),
section->getEntityID().toUTF16().c_str(),
portalObjectTypeText,
portalObjectTypePoll);
DataTable table;
db->execute(sql, table);
if(table.rows()>0)
{
stats->last_topic = *table[0][0];
stats->last_message = *table[0][1];
stats->last_message_date = *table[0][2];
}
}
// Calcolo le statistiche riferite ai forums figli
{
String sql = String::format(String(_S("\
select ") + OS_FORUMS_FORUM_STATS_TABLE + _S(".last_topic, ") + OS_FORUMS_FORUM_STATS_TABLE + _S(".last_message, ") + OS_FORUMS_FORUM_STATS_TABLE + _S(".last_message_date \
from \
os_snapshot_objects, \
") + OS_FORUMS_FORUM_STATS_TABLE + _S(", \
os_sections \
where \
os_snapshot_objects.entity = ") + OS_FORUMS_FORUM_STATS_TABLE + _S(".reference and \
os_snapshot_objects.current = os_sections.id and \
os_sections.component = '%S' and \
os_snapshot_objects.parent = '%S' and \
os_snapshot_objects.visible = 1 \
order by ") + OS_FORUMS_FORUM_STATS_TABLE + _S(".last_message_date desc \
limit 1")).c_str(),
getID().toUTF16().c_str(),
section->getEntityID().toUTF16().c_str());
DataTable table;
db->execute(sql, table);
if(table.rows()>0)
{
DateTime last_message_date = static_cast<String>(*table[0][2]);
if( (stats->last_message_date->isNull()) || (last_message_date > stats->last_message_date) )
{
stats->last_topic = *table[0][0];
stats->last_message = *table[0][1];
stats->last_message_date = *table[0][2];
}
}
}
db->updateRecord(stats);
}
void SearchResultsDatabase::executeQuery(shared_ptr<IPortalDatabase> database, shared_ptr<Portal> portal, shared_ptr<SearchQuery> query, const StringMap& objectsMap)
{
clear();
shared_ptr<DbSqlSelect> select(new DbSqlSelect(DBTABLES::SNAPSHOT_OBJECTS_TABLE));
select->fields.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::ENTITY, DBTABLES::SNAPSHOT_OBJECTS_TABLE));
// Se il primo step di un groupMode, ho bisogno di estrarre anche il padre.
if(query->getGroupMode() && (objectsMap.empty()))
select->fields.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::PARENT, DBTABLES::SNAPSHOT_OBJECTS_TABLE));
// Solo se non specificata gi una lista di oggetti.
// Le where si riferiscono agli oggetti singoli (ex. post), non al loro oggetto da visualizzare.
// Quindi non necessario specificarle nel secondo giro.
if(objectsMap.empty())
{
// current != ""
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::CURRENT, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(String::EMPTY), DbSqlCondition::cfDifferent | DbSqlCondition::cfAnd);
// Controlla se stata specificata una data di creazione minima
const DateTime &fromSubmitDate = query->convertSearchDateTime(query->getFromSubmitDateType(),query->getFromSubmitDateSpecific());
if(fromSubmitDate.isValid())
{
// submit_date >= fromSubmitDate
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::SUBMIT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(fromSubmitDate), DbSqlCondition::cfMajor | DbSqlCondition::cfEqual | DbSqlCondition::cfAnd);
}
// Controlla se stata specificata una data di creazione massima
const DateTime &toSubmitDate = query->convertSearchDateTime(query->getToSubmitDateType(),query->getToSubmitDateSpecific());
if(toSubmitDate.isValid())
{
// submit_date <= toSubmitDate
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::SUBMIT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(toSubmitDate), DbSqlCondition::cfMinor | DbSqlCondition::cfEqual | DbSqlCondition::cfAnd);
}
// Controlla se stata specificata una data di inserimento minima
const DateTime &fromInsertDate = query->convertSearchDateTime(query->getFromInsertDateType(),query->getFromInsertDateSpecific());
if(fromInsertDate.isValid())
{
// insert_date >= fromInsertDate
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::INSERT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(fromInsertDate), DbSqlCondition::cfMajor | DbSqlCondition::cfEqual | DbSqlCondition::cfAnd);
}
// Controlla se stata specificata una data di creazione massima
const DateTime &toInsertDate = query->convertSearchDateTime(query->getToInsertDateType(),query->getToInsertDateSpecific());
if(toInsertDate.isValid())
{
// insert_date <= toInsertDate
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::INSERT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(toInsertDate), DbSqlCondition::cfMinor | DbSqlCondition::cfEqual | DbSqlCondition::cfAnd);
}
// Non mostrare le entit virtuali. Dato che le entit virtuali hanno insertDate==null,
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::INSERT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(DateTime::EMPTY), DbSqlCondition::cfDifferent | DbSqlCondition::cfAnd);
// Se non deve mostrare gli oggetti cancellati forza la visibilit ad 1
if(query->getShowDeleted() == false)
// visible == 1
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::VISIBLE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), Convert::toSQL(true));
StringList types;
for(SearchQuery::Options::const_iterator i = query->getOptions().begin(); i != query->getOptions().end(); ++i)
{
shared_ptr<ISearchOptions> options = i->second;
if(options->getActive())
{
types.push_back(Convert::toString(options->getType()));
}
}
if(types.empty() == false)
// type in (...)
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::TYPE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), types);
// Ricerca in determinati padri
StringList parentsList;
query->computeParentsList(database, portal, parentsList);
// Ci metto gli apici...
for(StringList::iterator i = parentsList.begin(); i != parentsList.end(); ++i)
{
*i = _S("'") + String(*i) + _S("'");
}
if(!parentsList.empty())
{
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::PARENT, DBTABLES::SNAPSHOT_OBJECTS_TABLE), parentsList);
}
// 0.14 RC3
if(query->getText().substr(0,17) == _S("title-start-with:"))
{
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::TITLE), Convert::toSQL(query->getText().substr(17) + _S("%")), DbSqlCondition::cfLike | DbSqlCondition::cfAnd);
}
// 0.14 RC3
if(query->getText().substr(0,17) == _S("title-start-with:"))
{
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::TITLE), query->getText().substr(17) + _S("%"), DbSqlCondition::cfLike | DbSqlCondition::cfAnd);
}
// 0.12
if( (query->getByUser().empty() == false) && (query->getByUser() != _S("*")) )
{
String user = query->getByUser();
// MySelf test
if(user == _S("myself"))
{
// VERYURGENTRAZOR: Commentata perchè ho piallato il loggedUser come parametro... rivalutare la cosa
/*
if( (loggedUser != nullptr) && loggedUser->isLogged() && (loggedUser->getGuestMode() == false))
{
user = loggedUser->getUser()->id->toUTF16();
}
else
{
user == String::EMPTY;
}
*/
user == String::EMPTY;
}
else
{
// VERYURGENT, un test migliore per verificare se è un ID?
// VERYURGENT, l'sql potrebbe essere Sqlite-specifica.
// Fatto meglio, dovrei avere un'altro combo "localizza per" "nick/id"
// Inoltre qui ci sarebbe da supportare una ricerca "non exact-match" per farlo stile Invision.
if(user.length() != 48)
{
algorithms::to_upper(user);
String sql = String::format(_S("select rs.id from os_snapshot_users ts, os_users tu where ts.id=tu.id and upper(tu.name) = %S").c_str(), Convert::toSQL(user).c_str());
DataTable result;
if(database->execute(sql, result))
{
if(result.rows() > 0 && result.columns() > 0)
user = static_cast<String>(*result[0][0]);
}
}
}
// Come autore della primaria
select->tables.add(DbSqlTable(DBTABLES::ENTRIES_TABLE));
// Crea la join
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::ENTITY, DBTABLES::SNAPSHOT_OBJECTS_TABLE), DbSqlField(DBTABLES::ENTRIES::ID, DBTABLES::ENTRIES_TABLE));
select->where.add(DbSqlField(DBTABLES::ENTRIES::AUTHOR, DBTABLES::ENTRIES_TABLE), Convert::toSQL(user));
// Come editore dell'oggetto
// where DBTABLES::SNAPSHOT_OBJECTS::REFERENCE in (select id from os_entries where revision= getByUser();
}
}
else
{
// Se objectsList valorizzata, allora il secondo passo di una ricerca in groupMode, gli passo l'elenco oggetti.
StringList objectsList;
for(StringMap::const_iterator i = objectsMap.begin(); i != objectsMap.end(); ++i)
{
objectsList.push_back(_S("'") + i->first + _S("'"));
}
//select->where.add(String(DBTABLES::SNAPSHOT_OBJECTS_TABLE) + _S(".") + DBTABLES::SNAPSHOT_OBJECTS::REFERENCE, objectsList);
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::ENTITY, DBTABLES::SNAPSHOT_OBJECTS_TABLE), objectsList);
}
// Se il primo passaggio di una groupMode, l'ordinamento inutile.
// 0.12 RC5, tolto, con un limite nel primo giro di group-mode, devono essere ordinati.
//if( (!query->getGroupMode()) || (!objectsMap.empty()) )
{
DbSqlField::Order order = DbSqlField::foNone;
switch(query->getOrderDirection())
{
case searchOrderDirectionDescendent: order = DbSqlField::foDesc;
break;
case searchOrderDirectionAscendent: order = DbSqlField::foAsc;
break;
default: OS_ASSERTFALSE();
break;
}
SearchOrderMode orderMode = query->getOrderMode();
// 0.12 RC5, da testare
// Se è il primo passaggio di una group-mode, l'ordinamento "omLastObject" deve essere un "omSubmitDate".
if( (query->getGroupMode()) && (objectsMap.empty()) )
{
if(orderMode == searchOrderModeLastObject)
orderMode = searchOrderModeSubmitDate;
else if(orderMode == searchOrderModeVotesTotal)
orderMode = searchOrderModeSubmitDate;
else if(orderMode == searchOrderModeVotesAverage)
orderMode = searchOrderModeSubmitDate;
}
switch(orderMode)
{
case searchOrderModeRelevance: // Non c' la rilevanza nelle search non basate sul Lucene.
break;
// TOCLEAN_SNAPSHOT_SCORE
/* Per riprestinarlo, devo fare una join con la snapshot::users.
case searchOrderModeReputation: select->orderBy.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::SCORE, DBTABLES::SNAPSHOT_OBJECTS_TABLE));
select->orderBy.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::POSITION, DBTABLES::SNAPSHOT_OBJECTS_TABLE), order);
break;
*/
case searchOrderModeSubmitDate: select->orderBy.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::SUBMIT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), order);
break;
case searchOrderModeInsertDate: select->orderBy.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::INSERT_DATE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), order);
break;
case searchOrderModePosition: select->orderBy.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::POSITION, DBTABLES::SNAPSHOT_OBJECTS_TABLE), order);
break;
case searchOrderModeTitle: select->orderBy.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::TITLE, DBTABLES::SNAPSHOT_OBJECTS_TABLE), order);
break;
case searchOrderModeLastObject: {
select->tables.add(DbSqlTable(DBTABLES::DISCUSSIONS_STATS_TABLE));
// Crea la join
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::ENTITY, DBTABLES::SNAPSHOT_OBJECTS_TABLE), DbSqlField(DBTABLES::DISCUSSIONS_STATS::REFERENCE, DBTABLES::DISCUSSIONS_STATS_TABLE));
DbSqlField last_entry_date(DBTABLES::DISCUSSIONS_STATS::LAST_ENTRY_DATE, DBTABLES::DISCUSSIONS_STATS_TABLE);
last_entry_date.setOrder(order);
select->orderBy.fields.add(last_entry_date);
}
break;
case searchOrderModeVotesTotal: {
select->tables.add(DbSqlTable(DBTABLES::DISCUSSIONS_STATS_TABLE));
// Crea la join
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::ENTITY, DBTABLES::SNAPSHOT_OBJECTS_TABLE), DbSqlField(DBTABLES::DISCUSSIONS_STATS::REFERENCE, DBTABLES::DISCUSSIONS_STATS_TABLE));
DbSqlField last_entry_date(DBTABLES::DISCUSSIONS_STATS::VOTES, DBTABLES::DISCUSSIONS_STATS_TABLE);
last_entry_date.setOrder(order);
select->orderBy.fields.add(last_entry_date);
}
break;
case searchOrderModeVotesAverage: {
select->tables.add(DbSqlTable(DBTABLES::DISCUSSIONS_STATS_TABLE));
// Crea la join
select->where.add(DbSqlField(DBTABLES::SNAPSHOT_OBJECTS::ENTITY, DBTABLES::SNAPSHOT_OBJECTS_TABLE), DbSqlField(DBTABLES::DISCUSSIONS_STATS::REFERENCE, DBTABLES::DISCUSSIONS_STATS_TABLE));
DbSqlField last_entry_date(DBTABLES::DISCUSSIONS_STATS::VOTES_AVERAGE, DBTABLES::DISCUSSIONS_STATS_TABLE);
last_entry_date.setOrder(order);
select->orderBy.fields.add(last_entry_date);
}
break;
default: OS_EXCEPTION("Unknown query order mode");
break;
}
}
uint32 searchLimit = Options::instance()->getOption<uint32>(Options::portals_options::search_limit);
if(searchLimit != 0)
{
// 0.12 RC5 - Altrimenti, setto cmq un massimo di risultati, come limite massimo ragionevole.
select->limit.setCount(searchLimit);
}
if(query->hasMaxResults())
{
// 0.13 RC2
select->limit.setCount(query->getMaxResults());
}
// Fa prima una "count" per sapere quanti risultati totali, ad esempio per calcolare le pagine del pager.
// Poi, vengono estratti solo i record che servono alla pagina attuale (se non è il primo giro di un group-mode).
select->count = true;
DataTable result;
if(database->execute(select, result))
{
OS_ASSERT(result.hasRow(0));
m_totalResults = result.get(0, 0);
if(m_totalResults > 0) // In teoria non serve rieseguire la query se non ci sono risultati...
{
// Se è in groupMode, ed è il primo passaggio, controllo se è parziale.
if( (query->getGroupMode()) && (objectsMap.empty()) )
if( (searchLimit != 0) && (m_totalResults >= searchLimit) )
m_partial = true;
// Se non è in groupMode, o è il secondo passaggio, allora filtro per il pager.
if( (!query->getGroupMode()) || (!objectsMap.empty()) )
{
uint32 limit=0;
if( (query->hasLimit()) && (query->hasMaxResults()) )
limit = std::min<uint32>(query->getLimit(), query->getMaxResults());
else if(query->hasLimit())
limit = query->getLimit();
else if(query->hasMaxResults())
limit = query->getMaxResults();
if(limit != 0)
{
select->limit.setCount(limit);
select->limit.setOffset(query->getOffset());
}
}
else
{
if(searchLimit != 0)
{
// 0.12 RC5 - Altrimenti, setto cmq un massimo di risultati, come limite massimo ragionevole.
select->limit.setCount(searchLimit);
}
}
select->count = false;
result.clear();
if(database->execute(select, result))
{
for(uint32 r = 0; r < result.rows(); r++)
{
EntityID reference = static_cast<String>(result.get(r, DBTABLES::SNAPSHOT_OBJECTS::ENTITY)).to_ascii();
if(query->getGroupMode())
{
shared_ptr<EntitiesEntity> entity = database->getPortal()->getEntity(database, reference);
if( (entity != NULL) && (entity->getCurrent() != NULL) )
{
if(ObjectsSystem::instance()->getDescriptor(entity->getObjectType())->isGroupable())
reference = static_cast<String>(result.get(r, DBTABLES::SNAPSHOT_OBJECTS::PARENT)).to_ascii();
}
}
add(reference);
}
}
}
}
}