[odb-users] About using odb in same program to connect to mysql/sqlite/pgsql at same time

[Scott Zhang]

Yes.

For pagination, I don't think the difference is so large.

I have been working with SqlServer 2005 - 2008 since 2005. use row_number()
function previous is the only way to do it, and it works fine for moderate
data size. For now, sql2linq support directly pagination which I don't know
how is that implemented.
for non linq app, I think row_num should work.
http://www.witheringtree.com/2012/02/overloading-codeigniter-db-driver/


Mysql/sqlite/pgsql should support limit.

Oracle have something like this
$newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql)
inner_query WHERE rownum < $limit)";

                if ($offset != 0)
                {
                        $newsql .= " WHERE rnum >= $offset";
                }




I am aware we can insert any sql into "query" to solve most of questions.
Just it make orm looks weird. That's why I suggest order by and pagination
integration. All orm like softwares provide these function instead of
inject sql.


On Thu, Sep 13, 2012 at 8:40 PM, Boris Kolpackov <boris at codesynthesis.com>wrote:

> Hi Scott,
>
> Scott Zhang <macromarship at gmail.com> writes:
>
> >     Well. odb in my view is a really complex and heavy used template
> > design.
>
> Actually, while ODB does use templates, most of it is what I would call
> "traditional" template usage. Things like traits, class templates, etc.
> In particular, there is very little template metaprogramming, mostly to
> keep things portable and compilation times sane.
>
> In fact, some people suggest that we don't use templates enough! In
> their optinion, we should get rid of the ODB compiler and re-implement
> all this functionality using metaprogramming (and preprocessor). Go
> figure.
>
>
> > And from the code flow. Looks I only need to add a Take(int num_),
> > Offset(int offset_), (maybe OrderBy(string, enum orderby?)  and more ),
> in
> > the "query" class then we can support "pagination". Because for the
> > query to run: eg:
> >   result r (db->query<person> (query::age > 30));
> > although the first sight to convert "query::age>30" or even more complex
> > expression into a where clause looks mysterious. But after reading code,
> > query::age itself is a query_column, which can parse ">30" and return a
> > query.
>
> Yes, you seem to have figured it out, even with all these pesky templates.
>
>
> > Then by the override operators, query can parse the left expressions
> > itself. Then the place for the Offset, Take(orderby, groupby ....) could
> be
> > after all where clause processed. We just need to call on query object
> like
> >
> > result r(db->query<person>(
> > (query::aget>30).orderby(query::aget::column(),
> DESC).offset(10).take(10)))
>
> Well, this would require (custom) modifications to ODB. You could also
> achieve pretty much the same but with a slightly different syntax without
> any ODB modifications. For example:
>
> template <typename T, odb::mysql::database_type_id ID>
> odb::mysql::query
> order_by (const odb::mysql::query_column<T, ID>& c)
> {
>   return "ORDER BY" + c;
> }
>
> odb::mysql::query
> limit (std::size_t n)
> {
>   return "LIMIT" + odb::mysql::query::_val (n);
> }
>
> odb::mysql::query
> offset (std::size_t n)
> {
>   return "OFFSET" + odb::mysql::query::_val (n);
> }
>
> And then:
>
> db->query<person> ((query::age > 30) +
>                    order_by (query::name) +
>                    offset (10) +
>                    limit (20));
>
> >     And another question, from the code, looks odb::query linked with its
> > mysql implementation  so the query it returned will be a mysql query. But
> > if we link with both mysql and oracle at same time, eg for an application
> > need to use mysql and oracle at same time. Can odb::query work correctly?
>
> No, this is not yet properly supported by ODB, though it is high up on our
> TODO list. For more information, see this earlier post:
>
> http://www.codesynthesis.com/pipermail/odb-users/2012-August/000720.html
>
> Boris
>