Support for find_by_sql for SQL backends

[spiegela]

There are a couple of SQL statement types that aren't supported by sumo_db, but are required for my project:

• SELECT...JOIN for many-to-many relationships
• SELECT...WHERE field IN ... for a collection of many-to-many relationships

Rather than creating a bunch more SQL generation, which would take more planning, I thought we could implement find_by_sql for just the SQL backends. Other backends would implement the find_by_sql/2 callback, but would running raise a not_implemented error.

Thoughts?

[elbrujohalcon]

I understand your issue, but that's something that we explicitly avoided from day 0.
We aim at sumo_db being as generic as possible and we intentionally kept backends as replaceable as possible (i.e. as long as you're using sumo backends you should always be able to just change one for another in your configuration and keep the rest of your code as is).
What you can do, which would be awesome, is to create a repo like sumo_db-sql-extras where you can implement an Erlang library that depends on sumo_db and provides a sumo_extended_mysql_backend with things like find_by_sql and maybe others.
If you or other devs want to use that backend later, being fully aware that they will no longer be able to replace that backend by a generic one provided by sumo, you can just include that library instead of or together with sumo as a dependency in your apps.
I would love to see repos like sumo_db-sql-extras, sumo_db-nosql-extras, etc... And we will be glad to provide links to them in our README.md file.

[elbrujohalcon]

Also, I'm not sure if you're aware, but extending an existing repo/backend/etc. is very very easy if you use mixin:

-module(my_store_mysql).
-author('[email protected]').

-behaviour(sumo_store).

-include_lib("mixer/include/mixer.hrl").
-mixin([
        {sumo_store_mysql,
          [ init/1
          , create_schema/2
          , persist/2
          , delete/3
          , delete_by/3
          , delete_all/2
          , find_all/2
          , find_all/5
          , find_by/3
          , find_by/5
          ]}
       ]).
-export([find_by_sql/4]).

find_by_sql(SqlQuery, Values, DocName, State) ->
  StatementName =
    sumo_store_mysql:prepare(
      DocName, list_to_atom(SqlQuery),
      fun() -> SqlQuery end),

  sumo_store_mysql:get_docs(DocName, StatementName, Values, State).

NOTE: I just wrote that in this comment, it might not compile... but it will be close enough

[spiegela]

@elbrujohalcon Sounds good. I'll get started on sumo_db-sql-extras. I wasn't aware of mixer, so thanks for that.

[spiegela]

@elbrujohalcon FYI, I've created sumo_db-sql-extras. It only supports find_by_sql for MySQL at this point, but it's a start.

[elbrujohalcon]

@spiegela I know… I'm watching it already :)