PublicPlaceTableSync::SearchResult PublicPlaceTableSync::Search(
Env& env,
optional<RegistryKeyType> cityId,
optional<string> name,
int first /*= 0*/,
optional<std::size_t> number /*= 0*/,
LinkLevel linkLevel
){
SelectQuery<PublicPlaceTableSync> query;
if(cityId)
{
query.addWhereField(COL_CITYID, *cityId);
}
if(name)
{
query.addWhereField(COL_NAME, *name, ComposedExpression::OP_LIKE);
}
if (number)
query.setNumber(*number + 1);
if (first > 0)
query.setFirst(first);
return LoadFromQuery(query, env, linkLevel);
}
InterSYNTHESESlaveTableSync::SearchResult InterSYNTHESESlaveTableSync::Search(
Env& env,
std::string name
, int first /*= 0*/,
boost::optional<std::size_t> number
, bool orderByName
, bool raisingOrder,
LinkLevel linkLevel
){
SelectQuery<InterSYNTHESESlaveTableSync> query;
if (!name.empty())
{
query.addWhereField(SimpleObjectFieldDefinition<Name>::FIELD.name, name, ComposedExpression::OP_LIKE);
}
if (orderByName)
{
query.addOrderField(SimpleObjectFieldDefinition<Name>::FIELD.name, raisingOrder);
}
if (number)
{
query.setNumber(*number + 1);
}
if (first > 0)
{
query.setFirst(first);
}
return LoadFromQuery(query, env, linkLevel);
}
MapSourceTableSync::SearchResult MapSourceTableSync::Search(
util::Env& env,
optional<string> name,
size_t first /*= 0*/,
optional<size_t> number /*= boost::optional<std::size_t>()*/,
bool orderByName,
bool raisingOrder,
util::LinkLevel linkLevel
){
SelectQuery<MapSourceTableSync> query;
if(name)
{
query.addWhereField(COL_NAME, *name, ComposedExpression::OP_LIKE);
}
if(orderByName)
{
query.addOrderField(COL_NAME, raisingOrder);
}
if (number)
{
query.setNumber(*number + 1);
}
if (first > 0)
{
query.setFirst(first);
}
return LoadFromQuery(query, env, linkLevel);
}
SelectQuery selectObjectQuery(const std::vector<FieldType>& fdatas,
const Expr& e) {
SelectQuery sel;
Split tables;
std::set<LITESQL_String> tableSet;
for (size_t i = 0; i < fdatas.size(); i++)
if (tableSet.find(fdatas[i].table()) == tableSet.end()) {
tables.push_back(fdatas[i].table());
tableSet.insert(fdatas[i].table());
}
Split tableFilters;
tableFilters.resize(tables.size()-1);
for (size_t i = 1; i < tables.size(); i++)
tableFilters[i-1] = tables[i-1] + LITESQL_L(".id_ = ") + tables[i] + LITESQL_L(".id_");
tableSet.clear();
for (size_t i = 0; i < tables.size(); i++) {
sel.source(tables[i]);
tableSet.insert(tables[i]);
}
if (tables.size() > 1)
sel.where((e && RawExpr(tableFilters.join(LITESQL_L(" AND ")))).asString());
else
sel.where(e.asString());
for (size_t i = 0; i < fdatas.size(); i++)
sel.result(fdatas[i].table() + LITESQL_L(".") + fdatas[i].name());
return sel;
}
DriverAllocationTemplateTableSync::SearchResult DriverAllocationTemplateTableSync::Search(
util::Env& env,
boost::gregorian::date date /*= boost::gregorian::date(not_a_date_time)*/,
size_t first /*= 0*/,
boost::optional<std::size_t> number /*= boost::optional<std::size_t>()*/,
bool orderByDate /*= true*/,
bool raisingOrder /*= true*/,
util::LinkLevel linkLevel /*= util::UP_LINKS_LOAD_LEVEL */
) {
SelectQuery<DriverAllocationTemplateTableSync> query;
if (!date.is_not_a_date())
{
query.addWhereField(SimpleObjectFieldDefinition<Date>::FIELD.name, to_iso_extended_string(date));
}
if (orderByDate)
{
query.addOrderField(SimpleObjectFieldDefinition<Date>::FIELD.name, raisingOrder);
}
if (number)
{
query.setNumber(*number + 1);
}
if (first > 0)
{
query.setFirst(first);
}
return LoadFromQuery(query, env, linkLevel);
}
template <> litesql::DataSource<pomotuxdatabase::ActivityInventorySheet> ActivityInAIS::get(const litesql::Database& db, const litesql::Expr& expr, const litesql::Expr& srcExpr)
{
SelectQuery sel;
sel.source(table__);
sel.result(ActivityInventorySheet.fullName());
sel.where(srcExpr);
return DataSource<pomotuxdatabase::ActivityInventorySheet>(db, pomotuxdatabase::ActivityInventorySheet::Id.in(sel) && expr);
}
litesql::DataSource<ActivityInTTS::Row> ActivityInTTS::getRows(const litesql::Database& db, const litesql::Expr& expr)
{
SelectQuery sel;
sel.result(Activity.fullName());
sel.result(TodoTodaySheet.fullName());
sel.source(table__);
sel.where(expr);
return DataSource<ActivityInTTS::Row>(db, sel);
}
void Statistics::loadTypes() {
using namespace Database;
SelectQuery query;
query.addColumn("statistics_types", "stat_type_id");
query.addColumn("statistics_types", "stat_type_name");
query.addServerTable("statistics_types");
auto result = query.execute();
for (const auto &row : result) {
types.emplace(row["stat_type_name"].as<std::string>(), row["stat_type_id"].as<int>());
}
}
void Statistics::load() {
using namespace Database;
SelectQuery query;
query.addColumn("statistics", "stat_type");
query.addColumn("statistics", "stat_players");
query.addColumn("statistics", "stat_bin");
query.addColumn("statistics", "stat_count");
query.addEqualCondition<int>("statistics", "stat_version", versionId);
query.addServerTable("statistics");
auto result = query.execute();
for (const auto &row : result) {
int type = row["stat_type"].as<int>();
int players_online = row["stat_players"].as<int>();
int bin = row["stat_bin"].as<int>();
int count = row["stat_count"].as<int>();
while (statisticsDB[type].size() <= static_cast<size_t>(players_online)) {
statisticsDB[type].emplace_back();
}
statisticsDB[type][players_online][bin] = count;
}
}
MailingListSubscriptionTableSync::SearchResult MailingListSubscriptionTableSync::Search(
Env& env,
int first /*= 0*/,
optional<size_t> number,
bool raisingOrder,
LinkLevel linkLevel
) {
SelectQuery<MailingListSubscriptionTableSync> query;
if (number)
{
query.setNumber(*number + 1);
}
if (first > 0)
{
query.setFirst(first);
}
return LoadFromQuery(query, env, linkLevel);
}
RoadChunkTableSync::SearchResult RoadChunkTableSync::Search(
Env& env,
optional<RegistryKeyType> roadId,
int first /*= 0*/,
boost::optional<std::size_t> number /*= 0*/,
LinkLevel linkLevel
){
SelectQuery<RoadChunkTableSync> query;
if(roadId)
{
query.addWhereField(COL_ROADID, *roadId);
}
if (number)
query.setNumber(*number + 1);
if (first > 0)
query.setFirst(first);
return LoadFromQuery(query, env, linkLevel);
}
TimetableRowTableSync::SearchResult TimetableRowTableSync::Search(
Env& env,
optional<RegistryKeyType> timetableId
, bool orderByTimetable
, bool raisingOrder
, int first
, boost::optional<std::size_t> number,
LinkLevel linkLevel
){
SelectQuery<TimetableRowTableSync> query;
// Selection
if (timetableId)
{
query.addWhereField(COL_TIMETABLE_ID, *timetableId);
}
// Ordering
if (orderByTimetable)
{
query.addOrderField(COL_TIMETABLE_ID, raisingOrder);
query.addOrderField(COL_RANK, raisingOrder);
}
// Size
if (number)
query.setNumber(*number + 1);
if (first > 0)
query.setFirst(first);
return LoadFromQuery(query, env, linkLevel);
}
TimetableRowGroupItemTableSync::SearchResult TimetableRowGroupItemTableSync::Search(
util::Env& env,
boost::optional<util::RegistryKeyType> timetableRowGroupId,
boost::optional<size_t> rank,
int first,
boost::optional<std::size_t> number,
bool orderByRank,
bool raisingOrder,
util::LinkLevel linkLevel
){
SelectQuery<TimetableRowGroupItemTableSync> query;
if (timetableRowGroupId)
{
query.addWhereField(SimpleObjectFieldDefinition<TimetableRowGroup>::FIELD.name, *timetableRowGroupId);
}
if(rank)
{
query.addWhereField(SimpleObjectFieldDefinition<Rank>::FIELD.name, *rank);
}
if(orderByRank)
{
query.addOrderField(SimpleObjectFieldDefinition<Rank>::FIELD.name, raisingOrder);
}
if (number)
{
query.setNumber(*number + 1);
if (first > 0)
query.setFirst(first);
}
return LoadFromQuery(query, env, linkLevel);
}
PermanentThreadTableSync::SearchResult PermanentThreadTableSync::Search(
Env& env,
int first /*= 0*/
, optional<size_t> number /*= 0*/
, bool orderByName
, bool raisingOrder,
LinkLevel linkLevel
){
SelectQuery<PermanentThreadTableSync> query;
if (orderByName)
{
query.addOrderField(Name::FIELD.name, raisingOrder);
}
if (number)
{
query.setNumber(*number + 1);
}
if (first > 0)
{
query.setFirst(first);
}
return LoadFromQuery(query, env, linkLevel);
}
std::string MySQLValueRenderer::render(const SelectQuery& x) {
// TODO: Actually, the semantics here may have to be slightly different, because
// a sub-SELECT is allowed to do different things from a toplevel select.
MySQLQueryRenderer renderer{conn, symbolic_relation_resolver};
return wayward::format("({0})", x.to_sql(renderer));
}
/** modifies SelectQuery to order result set
\param f field to order by
\param asc ascending order
\return *this, methods can be chained */
DataSource& orderBy(FieldType f, bool asc=true) {
sel.orderBy(f.fullName(), asc);
return *this;
}
/** modifies SelectQuery to order result set by external table
\param id foreign key field used to join table with query
\param f field to order by
\param asc ascending order
\return *this, methods can be chained */
DataSource& orderByRelation(FieldType id, FieldType f, bool asc=true) {
sel.source(id.table());
sel.where(id == T::Id);
sel.orderBy(f.fullName(), asc);
return *this;
}
In::In(const FieldType & fld, const SelectQuery& s)
: Oper(fld, LITESQL_L("in"), LITESQL_L("(") + s.asString() + LITESQL_L(")")) {}
