17#ifdef ORBIT_ENABLE_POSTGRES
28using Params = std::vector<std::optional<std::string>>;
39 if (s.empty())
return false;
40 bool part_start =
true;
43 if (part_start)
return false;
47 bool alpha = (c >=
'a' && c <=
'z') || (c >=
'A' && c <=
'Z') || c ==
'_';
48 bool digit = (c >=
'0' && c <=
'9');
49 if (!(alpha || (digit && !part_start)))
return false;
56 if (!
is_identifier(s))
throw std::invalid_argument(
"Invalid SQL identifier: " + s);
64 static const char*
const words[] = {
65 "all",
"and",
"any",
"as",
"asc",
"between",
"both",
"by",
"case",
"check",
"column",
"constraint",
66 "create",
"cross",
"current_date",
"current_time",
"current_timestamp",
"current_user",
"default",
67 "delete",
"desc",
"distinct",
"do",
"else",
"end",
"except",
"exists",
"false",
"fetch",
"for",
68 "foreign",
"from",
"full",
"grant",
"group",
"having",
"in",
"index",
"inner",
"insert",
"intersect",
69 "into",
"is",
"join",
"key",
"leading",
"left",
"like",
"limit",
"natural",
"not",
"null",
"offset",
70 "on",
"only",
"or",
"order",
"outer",
"primary",
"references",
"returning",
"right",
"select",
71 "session_user",
"set",
"some",
"table",
"then",
"to",
"trailing",
"true",
"union",
"unique",
"update",
72 "user",
"using",
"values",
"when",
"where",
"window",
"with"};
74 for (
char& c : lower) c =
static_cast<char>(c >=
'A' && c <=
'Z' ? c -
'A' +
'a' : c);
75 for (
const char* w : words) {
76 if (lower == w)
return true;
91inline std::string
ident(
const std::string& name) {
96 size_t dot = name.find(
'.', start);
97 std::string part = name.substr(start, dot == std::string::npos ? std::string::npos : dot - start);
100 if (dot == std::string::npos)
break;
109 for (
char& c : sql) {
116 static const char*
const allowed[] = {
"=",
"!=",
"<>",
"<",
"<=",
">",
">=",
"LIKE",
"NOT LIKE",
"ILIKE",
"NOT ILIKE"};
117 for (
const char* a : allowed) {
118 if (op == a)
return op;
120 throw std::invalid_argument(
"Unsupported SQL comparison operator: " + op);
125 if constexpr (std::is_same_v<T, bool>) {
126 return std::string(val ?
"1" :
"0");
127 }
else if constexpr (std::is_integral_v<T>) {
128 return std::to_string(val);
129 }
else if constexpr (std::is_floating_point_v<T>) {
130 std::ostringstream os;
131 os.precision(std::numeric_limits<T>::max_digits10);
135 return std::string(val);
140 if (v.
is_null())
return std::nullopt;
142 if (v.
is_boolean())
return std::string(v.
get<
bool>() ?
"1" :
"0");
149 out.reserve(sql.size() + 8);
152 if (c ==
'?') out +=
"$" + std::to_string(++n);
153 else out.push_back(c);
159template <
typename Escape>
168 if (i >= params.size())
throw std::logic_error(
"Fewer parameters than placeholders");
169 const auto& p = params[i++];
170 out += p ?
"'" + escape(*p) +
"'" : std::string(
"NULL");
186 combined.insert(combined.end(), other.
params.begin(), other.
params.end());
187 return Expr{
"(" +
sql +
" AND " + other.
sql +
")", std::move(combined)};
192 combined.insert(combined.end(), other.
params.begin(), other.
params.end());
193 return Expr{
"(" +
sql +
" OR " + other.
sql +
")", std::move(combined)};
205 explicit Col(std::string name) :
name_(detail::ident(name)) {}
207 template <
typename T>
210 template <
typename T>
213 template <
typename T>
216 template <
typename T>
219 template <
typename T>
222 template <
typename T>
228 template <
typename T>
242template <
typename DBClient>
244 return requires(std::shared_ptr<DBClient> c) { c->escape(std::string{}); };
247template <
typename DBClient>
249 constexpr bool mysql = is_mysql_client<DBClient>();
251 if constexpr (mysql) {
252 return client->query_async(
detail::inline_literals(rendered, params, [&](
const std::string& v) {
return client->escape(v); }));
254#ifdef ORBIT_ENABLE_POSTGRES
257 static_assert(!std::is_same_v<DBClient, DBClient>,
258 "this ORM client needs PostgreSQL support, which this build of Orbit leaves out (ORBIT_ENABLE_POSTGRES=OFF)");
281 using std::runtime_error::runtime_error;
299 switch (proto.
type()) {
300 case nlohmann::json::value_t::string:
302 case nlohmann::json::value_t::boolean:
303 if (text ==
"t" || text ==
"true" || text ==
"TRUE" || text ==
"1")
return true;
304 if (text ==
"f" || text ==
"false" || text ==
"FALSE" || text ==
"0")
return false;
306 case nlohmann::json::value_t::number_integer: {
308 auto [end, ec] = std::from_chars(text.data(), text.data() + text.size(), v);
309 if (ec == std::errc() && end == text.data() + text.size())
return v;
312 case nlohmann::json::value_t::number_unsigned: {
313 unsigned long long v = 0;
314 auto [end, ec] = std::from_chars(text.data(), text.data() + text.size(), v);
315 if (ec == std::errc() && end == text.data() + text.size())
return v;
318 case nlohmann::json::value_t::number_float: {
320 double v = std::strtod(text.c_str(), &end);
321 if (!text.empty() && end == text.c_str() + text.size())
return v;
327 if (!parsed.is_discarded())
return parsed;
348template <
typename ModelType>
350 std::vector<ModelType> out;
351 out.reserve(rs.
size());
353 if constexpr (std::is_default_constructible_v<ModelType>) shape = ModelType{};
354 for (
const auto& row : rs.
rows()) {
357 for (
const auto& [key, proto] : shape.
items()) {
358 auto it = j.
find(key);
361 }
else if (!it->is_null()) {
362 auto text = row.
get(key);
368 out.push_back(j.
get<ModelType>());
378template <
typename DBClient,
typename ModelType>
389 return detail::rows_to_models<ModelType>(rs);
393template <
typename DBClient,
typename ModelType>
408template <
typename DBClient>
419 if (rs.
size() == 0)
return 0;
420 std::string text = rs[0].get(0).value_or(
"0");
422 std::from_chars(text.data(), text.data() + text.size(), n);
428template <
typename DBClient,
typename ModelType>
439 auto rows = detail::rows_to_models<ModelType>(rs);
440 if (rows.empty())
throw DatabaseError(
"INSERT ... RETURNING returned no row");
441 return std::move(rows.front());
452template <
typename DBClient,
typename ModelType>
528 if (!j.
is_object())
throw std::invalid_argument(
"a model must serialise to a JSON object");
531 if (j.
empty())
throw std::invalid_argument(
"insert needs at least one column besides an unset primary key");
536 for (
auto& el : j.
items()) {
537 if (!first) { cols +=
", "; vals +=
", "; }
543 st.
sql =
"INSERT INTO " +
table_ +
" (" + cols +
") VALUES (" + vals +
")";
544 if (returning) st.
sql +=
" RETURNING *";
553 st.sql +=
" ORDER BY ";
554 for (
size_t i = 0; i <
orders_.size(); ++i) st.sql += (i ?
", " :
"") +
orders_[i];
558 st.sql +=
" LIMIT " + std::to_string(*
limit_);
559 }
else if (
offset_ && is_mysql_client<DBClient>()) {
560 st.sql +=
" LIMIT 18446744073709551615";
582 throw std::invalid_argument(
"update needs a JSON object with at least one column");
587 for (
auto& el : changes.
items()) {
676 std::shared_ptr<DBClient>
db_;
688 for (
size_t i = 0; i <
wheres_.size(); ++i) {
689 if (i > 0) st.
sql +=
" AND ";
697 throw std::logic_error(std::string(what) +
" without a where() condition; call all() to affect every row");
a class to store JSON values
Definition json.hpp:19401
iterator end() noexcept
returns an iterator to one past the last element
Definition json.hpp:22095
iteration_proxy< iterator > items() noexcept
helper to access iterator member functions in range-based for
Definition json.hpp:22185
constexpr bool is_string() const noexcept
return whether value is a string
Definition json.hpp:20673
static basic_json parse(InputType &&i, const parser_callback_t cb=nullptr, const bool allow_exceptions=true, const bool ignore_comments=false)
deserialize from a compatible input
Definition json.hpp:23317
constexpr bool is_number_integer() const noexcept
return whether value is an integer number
Definition json.hpp:20638
constexpr bool is_boolean() const noexcept
return whether value is a boolean
Definition json.hpp:20624
IteratorType erase(IteratorType pos)
remove element given an iterator
Definition json.hpp:21731
constexpr bool is_object() const noexcept
return whether value is an object
Definition json.hpp:20659
bool empty() const noexcept
checks whether the container is empty.
Definition json.hpp:22208
constexpr value_t type() const noexcept
return the type of the JSON value (explicit)
Definition json.hpp:20596
constexpr bool is_number_unsigned() const noexcept
return whether value is an unsigned integer number
Definition json.hpp:20645
string_t dump(const int indent=-1, const char indent_char=' ', const bool ensure_ascii=false, const error_handler_t error_handler=error_handler_t::strict) const
serialization
Definition json.hpp:20574
constexpr bool is_null() const noexcept
return whether value is null
Definition json.hpp:20617
auto get() const noexcept(noexcept(std::declval< const basic_json_t & >().template get_impl< ValueType >(detail::priority_tag< 4 > {}))) -> decltype(std::declval< const basic_json_t & >().template get_impl< ValueType >(detail::priority_tag< 4 > {}))
get a (pointer) value (explicit)
Definition json.hpp:21046
static basic_json array(initializer_list_t init={})
explicitly create an array from an initializer list
Definition json.hpp:20307
static basic_json object(initializer_list_t init={})
explicitly create an object from an initializer list
Definition json.hpp:20315
iterator find(const typename object_t::key_type &key)
find an element in a JSON object
Definition json.hpp:21953
general exception of the basic_json class
Definition json.hpp:4372
const char * what() const noexcept override
returns the explanatory string
Definition json.hpp:4375
Represents a generic, unified result set from a database query.
Definition ResultSet.hpp:197
const std::string & error() const
Definition ResultSet.hpp:213
const std::vector< Row > & rows() const
Definition ResultSet.hpp:215
uint64_t affected_rows() const
Definition ResultSet.hpp:218
bool ok() const
False if the query failed; error() then says why.
Definition ResultSet.hpp:212
size_t size() const
Definition ResultSet.hpp:216
A database column, allowing C++ operators to build parameterized SQL conditions.
Definition QueryBuilder.hpp:203
Expr operator<(const T &val) const
Definition QueryBuilder.hpp:217
Expr make(const char *op, const T &val) const
Definition QueryBuilder.hpp:229
Expr operator<=(const T &val) const
Definition QueryBuilder.hpp:223
Expr operator>=(const T &val) const
Definition QueryBuilder.hpp:220
Expr operator>(const T &val) const
Definition QueryBuilder.hpp:214
Col(std::string name)
Definition QueryBuilder.hpp:205
Expr operator!=(const T &val) const
Definition QueryBuilder.hpp:211
Expr operator==(const T &val) const
Definition QueryBuilder.hpp:208
std::string name_
Definition QueryBuilder.hpp:226
A query run by the ORM failed, or its rows did not fit the model.
Definition QueryBuilder.hpp:279
A lightweight, JSON-backed ORM Query Builder with bound parameters.
Definition QueryBuilder.hpp:453
QueryBuilder & where(const Expr &expr)
Definition QueryBuilder.hpp:474
bool all_rows_
Definition QueryBuilder.hpp:682
std::vector< std::string > orders_
Definition QueryBuilder.hpp:679
Statement select_statement() const
The SELECT that get_async() runs (placeholders as ?).
Definition QueryBuilder.hpp:549
QueryInsertAwaiter< DBClient, ModelType > insert_async(const ModelType &model)
Executes an INSERT query for a model, binding every value.
Definition QueryBuilder.hpp:653
QueryGetAwaiter< DBClient, ModelType > get_async()
Executes a SELECT query asynchronously and maps results to a vector of ModelType.
Definition QueryBuilder.hpp:608
std::vector< Expr > wheres_
Definition QueryBuilder.hpp:678
QueryBuilder & offset(uint64_t n)
Skips the first n rows (use with order_by() for stable pages).
Definition QueryBuilder.hpp:493
QueryBuilder & order_by(const std::string &field, Order direction=Order::Asc)
Definition QueryBuilder.hpp:481
QueryInsertAwaiter< DBClient, ModelType > remove_async()
Deletes the matching rows; resumes with the number affected.
Definition QueryBuilder.hpp:639
QueryBuilder & limit(uint64_t n)
Returns at most n rows.
Definition QueryBuilder.hpp:487
Statement count_statement() const
The SELECT COUNT(*) that count_async() runs.
Definition QueryBuilder.hpp:567
void append_where(Statement &st) const
Definition QueryBuilder.hpp:685
std::string table_
Definition QueryBuilder.hpp:677
QueryBuilder & primary_key(const std::string &column)
The model's primary key column (default "id").
Definition QueryBuilder.hpp:514
std::optional< uint64_t > offset_
Definition QueryBuilder.hpp:681
QueryBuilder & all()
Definition QueryBuilder.hpp:501
std::string primary_key_
Definition QueryBuilder.hpp:683
std::optional< uint64_t > limit_
Definition QueryBuilder.hpp:680
Statement insert_statement(const ModelType &model, bool returning=false) const
The INSERT that insert_async() runs (create_async() adds RETURNING *). An unset primary key is left o...
Definition QueryBuilder.hpp:526
QueryInsertAwaiter< DBClient, ModelType > update_async(const ModelType &model)
Definition QueryBuilder.hpp:632
QueryCreateAwaiter< DBClient, ModelType > create_async(const ModelType &model)
Inserts model and resumes with the row as stored, including the generated primary key and column defa...
Definition QueryBuilder.hpp:670
Statement delete_statement() const
Definition QueryBuilder.hpp:598
Statement update_statement(const nlohmann::json &changes) const
The UPDATE that update_async() runs.
Definition QueryBuilder.hpp:580
std::shared_ptr< DBClient > db_
Definition QueryBuilder.hpp:676
QueryCountAwaiter< DBClient > count_async()
Counts the matching rows.
Definition QueryBuilder.hpp:614
static Statement finish(Statement st)
Definition QueryBuilder.hpp:701
QueryBuilder(std::shared_ptr< DBClient > db, const std::string &table)
Definition QueryBuilder.hpp:456
QueryBuilder & where(const std::string &field, const std::string &op, const std::string &val)
Adds a WHERE condition. val is bound as a parameter.
Definition QueryBuilder.hpp:464
void require_condition(const char *what) const
Definition QueryBuilder.hpp:695
QueryInsertAwaiter< DBClient, ModelType > update_async(const nlohmann::json &changes)
Updates the matching rows; resumes with the number affected.
Definition QueryBuilder.hpp:625
QueryAwaiter query_async(std::shared_ptr< PostgresClient > client, const std::string &sql)
Creates an awaiter for executing a query on a PostgresClient.
Definition PostgresCoro.hpp:75
constexpr char kQuoteMark
Definition QueryBuilder.hpp:84
std::vector< ModelType > rows_to_models(const database::ResultSet &rs)
Maps result rows to models, converting each column by the type of the model's field rather than by gu...
Definition QueryBuilder.hpp:349
std::string inline_literals(const std::string &sql, const Params ¶ms, Escape &&escape)
Replaces ? placeholders with escaped, quoted literals (for clients without parameter binding).
Definition QueryBuilder.hpp:160
bool is_identifier(std::string_view s)
True for a plain SQL identifier (name) or a qualified one (table.name): ASCII letters,...
Definition QueryBuilder.hpp:38
void throw_if_failed(const database::ResultSet &rs)
Definition QueryBuilder.hpp:286
std::optional< std::string > to_param(const T &val)
Definition QueryBuilder.hpp:124
std::string number_placeholders(const std::string &sql)
Rewrites ? placeholders as PostgreSQL's $1, $2, ...
Definition QueryBuilder.hpp:147
const std::string & checked_operator(const std::string &op)
Definition QueryBuilder.hpp:115
std::optional< std::string > json_to_param(const nlohmann::json &v)
Definition QueryBuilder.hpp:139
const std::string & checked_identifier(const std::string &s)
Definition QueryBuilder.hpp:55
bool is_unset_key(const nlohmann::json &v)
A key that the database should generate: null, 0, or an empty string.
Definition QueryBuilder.hpp:291
bool is_reserved_word(std::string_view s)
Definition QueryBuilder.hpp:63
std::string render_identifiers(std::string sql, bool mysql)
Replaces quote marks with " (PostgreSQL, standard SQL) or a backtick (MySQL).
Definition QueryBuilder.hpp:108
std::string ident(const std::string &name)
Validates an identifier (name or table.name) and marks the parts that are reserved words for quoting.
Definition QueryBuilder.hpp:91
nlohmann::json typed_value(const std::string &text, const nlohmann::json &proto)
Converts a column's text to the JSON type of the model field proto.
Definition QueryBuilder.hpp:298
Definition legacy_namespaces.hpp:23
std::vector< std::optional< std::string > > Params
Values bound to the ? placeholders of an expression, in order. std::nullopt binds SQL NULL.
Definition QueryBuilder.hpp:28
constexpr bool is_mysql_client()
Runs sql (with ? placeholders) on either client type.
Definition QueryBuilder.hpp:243
Order
Sort direction for QueryBuilder::order_by().
Definition QueryBuilder.hpp:270
auto do_query_async(std::shared_ptr< DBClient > client, const std::string &sql, const Params ¶ms)
Definition QueryBuilder.hpp:248
A SQL condition with ? placeholders and the values bound to them.
Definition QueryBuilder.hpp:180
Expr operator||(const Expr &other) const
Definition QueryBuilder.hpp:190
Params params
Definition QueryBuilder.hpp:182
std::string sql
Definition QueryBuilder.hpp:181
Expr operator&&(const Expr &other) const
Definition QueryBuilder.hpp:184
Definition QueryBuilder.hpp:409
bool await_ready() const
Definition QueryBuilder.hpp:413
NativeAwaiter native_awaiter
Definition QueryBuilder.hpp:411
uint64_t await_resume()
Definition QueryBuilder.hpp:416
decltype(do_query_async(std::declval< std::shared_ptr< DBClient > >(), std::declval< std::string >(), std::declval< Params >())) NativeAwaiter
Definition QueryBuilder.hpp:410
void await_suspend(std::coroutine_handle<> h)
Definition QueryBuilder.hpp:414
Resumes with the row an INSERT ... RETURNING * stored.
Definition QueryBuilder.hpp:429
void await_suspend(std::coroutine_handle<> h)
Definition QueryBuilder.hpp:434
decltype(do_query_async(std::declval< std::shared_ptr< DBClient > >(), std::declval< std::string >(), std::declval< Params >())) NativeAwaiter
Definition QueryBuilder.hpp:430
NativeAwaiter native_awaiter
Definition QueryBuilder.hpp:431
bool await_ready() const
Definition QueryBuilder.hpp:433
ModelType await_resume()
Definition QueryBuilder.hpp:436
Definition QueryBuilder.hpp:379
bool await_ready() const
Definition QueryBuilder.hpp:383
void await_suspend(std::coroutine_handle<> h)
Definition QueryBuilder.hpp:384
decltype(do_query_async(std::declval< std::shared_ptr< DBClient > >(), std::declval< std::string >(), std::declval< Params >())) NativeAwaiter
Definition QueryBuilder.hpp:380
NativeAwaiter native_awaiter
Definition QueryBuilder.hpp:381
std::vector< ModelType > await_resume()
Definition QueryBuilder.hpp:386
Definition QueryBuilder.hpp:394
NativeAwaiter native_awaiter
Definition QueryBuilder.hpp:396
bool await_ready() const
Definition QueryBuilder.hpp:398
decltype(do_query_async(std::declval< std::shared_ptr< DBClient > >(), std::declval< std::string >(), std::declval< Params >())) NativeAwaiter
Definition QueryBuilder.hpp:395
void await_suspend(std::coroutine_handle<> h)
Definition QueryBuilder.hpp:399
uint64_t await_resume()
Definition QueryBuilder.hpp:401
A statement with ? placeholders and the values bound to them.
Definition QueryBuilder.hpp:264
std::string sql
Definition QueryBuilder.hpp:265
Params params
Definition QueryBuilder.hpp:266