database url into instance_key

[antonio_antuan]

Hi Mike.
I have a question in case of that discussion about multi-bound sessions and supplemental keys: https://groups.google.com/forum/#!topic/sqlalchemy/URbnrqZl-O0.

If a `Session` is multi-bound, instances can be added or presented on several databases. So, there should be some kind of `db_url` parameter for methods `add`, `add_all`, `merge` and `bulk_*` operations. `delete`, `expire` and `expunge` are not in list because I think, that such methods manages objects presented in particular database.

At first I've added `db_url` property to `InstanceState`, looks like that:

@property
def db_url(self):
    """
    Returns state db_url, if such presented in state instance key.
    """
    if self._db_url is None and len(self.key or ()) == 3:
        self._db_url = self.key[-1]
    return self._db_url

@db_url.setter
def db_url(self, value):
    """
    Sets state db_url. Changes instance key.
    """
    if self.key is not None and value is not None:
        self.key = self.key[0], self.key[1], value
    self._db_url = value

@db_url.deleter
def db_url(self):
    """
    Clears db_url. Changes instance key.
    """
    self.key = self.key[0], self.key[1]
    self._db_url = None


Then I see two ways how to add new parameter for public methods:
1. Add new parameter to all of that methods for `Session`. `db_url` property described above can help to manage with this. Think that it is the easiest way, but also I think that you can be disagree with that method.
2. Allocate all code, that can interact with `db_url` (connections creation, states keys creating and updating and so on) and manage with them ONLY in overridden public methods in `MultiSession` class.

The first approach leads to change `Session`'s methods signature, but code looks more clearly and its inner-changes not so numerous.
The second changes more of internal code, new private methods will be added, but the public methods' parameters won't change.

Which one way is preferable?

Thanks.
Anton.

[Mike Bayer]

I'll reiterate that an explicit URL or method that refers to the term
"url" would never directly be present within anything in the ORM,
there would only be a token of some kind that can be used to link up
to a series of Engine objects.

The enhancement at
https://bitbucket.org/zzzeek/sqlalchemy/issues/4137/sharding-token-extension-in-identity-key
covers this. InstanceState objects when they represent persistent
database objects have an identity key, and there would be an
additional token in the identity key that can be used by a custom
Session class in any way desired, including to associate the object
with a particular Engine. You'd look at instancestate.key[2] to get
this token.

#4137 is not that hard to implement but needs a few reasonable design
decisions to be made. A proof of concept is at
https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/613/ . note the
changes to test_horizontal_shard where any instance retrieved from the
database should have inspect(instance).key[2] with the shard
identifier. If you can work with this that would be great.





>
> Thanks.
> Anton.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-devel" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-dev...@googlegroups.com.
> To post to this group, send email to sqlalche...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy-devel.
> For more options, visit https://groups.google.com/d/optout.

[antonio_antuan]

Oh, missed mention about `never URL, only token`...

My purpose is to make session really multi-bound. I want to help to make this not just because of my company's project.

Thought that it would be great if anyone can manage several databases within one session. Looks like you don't think so.

Anyway maybe it will be interesting for you: https://github.com/aCLr/sqlalchemy/commit/bb7a876ba75bd3eec24a263e5dbaf1a2833d25be
Currently works great with all existed tests passed. Going to implement tests for MultiBoundSession methods: add, add_all, merge, delete, bulk_*, get, query.


Thanks for your help and advices again.


четверг, 14 декабря 2017 г., 18:26:15 UTC+3 пользователь Mike Bayer написал:

[Mike Bayer]

On Thu, Dec 14, 2017 at 11:00 AM, antonio_antuan <a.ch...@gmail.com> wrote:
> Oh, missed mention about `never URL, only token`...
>
> My purpose is to make session really multi-bound. I want to help to make
> this not just because of my company's project.
>
> Thought that it would be great if anyone can manage several databases within
> one session. Looks like you don't think so.

im sorry...did you look at the enhancement patch I just worked up for
this case?

[Mike Bayer]

On Thu, Dec 14, 2017 at 11:32 AM, Mike Bayer <mik...@zzzcomputing.com> wrote:
> On Thu, Dec 14, 2017 at 11:00 AM, antonio_antuan <a.ch...@gmail.com> wrote:
>> Oh, missed mention about `never URL, only token`...
>>
>> My purpose is to make session really multi-bound. I want to help to make
>> this not just because of my company's project.
>>
>> Thought that it would be great if anyone can manage several databases within
>> one session. Looks like you don't think so.
>
> im sorry...did you look at the enhancement patch I just worked up for
> this case?
>
>

if you look at the latest
https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/613/, the token
is now part of persistence as well as querying. InstanceState now
has an "identity_token" attribute that is populated directly during
persistence and querying as well as being part of the identity key.

you should be able to take your existing code and change "db_url" to
"identity_token" and it's all there.

[antonio_antuan]

Figured it out. There is another approach, as you said, token instead of bind url.

In my code identity_token can be passed to `bulk_*`, `add` and `merge` operations. In your patch there is no way to do it.
Only one way: if instance exists, call `_choose_shard_and_assign`.
If another `get_bind` implementation used, how token can be passed to get_bind?
Something like that:

class MultiBoundSession(Session):
    def get_bind(self, mapper=None, clause=None, bind_id=None, **kwargs):
        original_bind = None
        try:
            original_bind = super(MultiBoundSession, self).get_bind(mapper, clause)
        except UnboundExecutionError:
            pass
        if bind_id is None:
            bind_id = self.bind_id_getter(mapper, clause, **kwargs)
        bind_for_shard = self.__binds[bind_id]
        if original_bind is not None and original_bind.url == bind_for_shard.url:
            return original_bind
        else:
            return bind_for_shard

    def __init__(self, binds=None, query_cls=MultiBoundQuery, bind_id_getter=None, **kwargs):
        super(MultiBoundSession, self).__init__(query_cls=query_cls, **kwargs)
        self.__binds = {}
        self.bind_id_getter = bind_id_getter
        if binds is not None:
            for k, v in binds.items():
                self.__binds[k] = v

As I said, I found only one way: add new parameter to `add`, `merge` and `bulk_*` methods, identity_token, as you named it.
Is there another ways to achieve that?


четверг, 14 декабря 2017 г., 20:23:02 UTC+3 пользователь Mike Bayer написал:

[Mike Bayer]

On Mon, Dec 18, 2017 at 9:56 AM, antonio_antuan <a.ch...@gmail.com> wrote:
> Figured it out. There is another approach, as you said, token instead of
> bind url.
>
> In my code identity_token can be passed to `bulk_*`, `add` and `merge`
> operations. In your patch there is no way to do it.

right so here's how that would work, easy API function to add this later:


myobject = MyObject()

inspect(myobject).identity_token = <your token>


now that object is linked to that identity, when it goes to get
persisted the value will be used as part of the identity key and all
of that.

> Only one way: if instance exists, call `_choose_shard_and_assign`.
> If another `get_bind` implementation used, how token can be passed to
> get_bind?

get_bind receives the instance, look at line 163 at
https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/613/4/lib/sqlalchemy/ext/horizontal_shard.py