Does twophase=True limit to only two databases at the same time?

[Jinghui Niu]

I have three different DBs, one is person.db, another is journal.db, yet another is tag.db. In the documentation it reads:

Vertical partitioning places different kinds of objects, or different tables, across multiple databases:

engine1 = create_engine('postgresql://db1')

engine2 = create_engine('postgresql://db2')

Session = sessionmaker(twophase=True)

# bind User operations to engine 1, Account operations to engine 2

Session.configure(binds={User:engine1, Account:engine2})

session = Session()

I noticed that this example only deals with two DBs, and the parameter is called "twophase". I was wondering if there is any significance of "two" here? How can I fit my third DB in? Thanks.

[Jinghui Niu]

Oh by the way, I'm using SQLite as backend.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/tRlV984I_64/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

On 8/14/15 5:07 AM, Jinghui Niu wrote:

Oh by the way, I'm using SQLite as backend.

On Aug 14, 2015 2:04 AM, "Jinghui Niu" <niuji...@gmail.com> wrote:

I have three different DBs, one is person.db, another is journal.db, yet another is tag.db. In the documentation it reads:

Vertical partitioning places different kinds of objects, or different tables, across multiple databases:

engine1 = create_engine('postgresql://db1')

engine2 = create_engine('postgresql://db2')

Session = sessionmaker(twophase=True)

# bind User operations to engine 1, Account operations to engine 2

Session.configure(binds={User:engine1, Account:engine2})

session = Session()

I noticed that this example only deals with two DBs, and the parameter is called "twophase". I was wondering if there is any significance of "two" here? How can I fit my third DB in? Thanks.

"twophase" refers to enabling two-phase commit on supporting backends, which does not include SQLite.   See https://en.wikipedia.org/wiki/Two-phase_commit_protocol.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/tRlV984I_64/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Jonathan Vanasco]

twophase deals with the transaction commit protocol , and is unlreated to anything else in your example.  (http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.params.twophase)

You'd simply create an engine3 and bind whatever object classes to it.

FWIW.  Sqlite does not support two-phase commits.   Fore more info on two-phase commits - https://en.wikipedia.org/wiki/Two-phase_commit_protocol

[Jinghui Niu]

Thanks for all these helpful feedback. 

If I still want to use SQLite, and I still need to do vertical partition, what can I do? Am I out of luck?

[Jonathan Vanasco]

On Friday, August 14, 2015 at 5:16:48 PM UTC-4, Jinghui Niu wrote:

If I still want to use SQLite, and I still need to do vertical partition, what can I do? Am I out of luck?

You can, but not with a two-phase commit. 

Two-phase commit basically works like this:

- round 1, everyone locks-state and votes "COMMIT!" or "NOOOO!"

- round 2, if commit in round 1 was unanimous, it commits. otherwise everyone is told to roll back.

Since SQLlite doesn't support that, you'd need to write that logic in at the application level.

[Jinghui Niu]

Thank you very much Jonathan for your very intuitive analogy!

Basically I just want to put "people", "journal" and "tag" tables(each will potentially be very large) into different DBs, if I write that logic, how can I integrate it with SQLAlchemy? Could you give me a rough idea here? Or point some reference. I suppose such feature is relatively commonly needed among SQLite users isn't it?

[Jonathan Vanasco]

Well, this problem doesn't really have anything to do with SqlAlchemy -- you should probably ask people for advice on the Sqlite lists or Stack Overflow.

You can segment out your database into 3 files using the example above.  You will just run into an issue where -- because there isn't a two-phase-commit available in Sqlite, you will need to decide how to handle situations like (but not limited to):

- the first and second databases committed, but the third database raised an error (you need to undo in the application)

- the first and second databases committed, but your application was quit before the third database could commit (you need to undo from another application)

You will have to decide how to handle that at the application and database levels, and then SqlAlchemy can be used to implement that strategy. 

I just want to be clear -- your concern right now is on the best way to use Sqlite to solve your problem -- not use Sqlalchemy.  Once you figure that out, people here can be more helpful.

[Jinghui Niu]

Thanks Jonathan for pointing out the direction, it is very helpful to know where I can find more info.

[Jinghui Niu]

Just a thought, if I don't commit those three tables together in my application, can I just use 3 Session objects to commit them separately, without having to worry about this two phase issue? I want to go simple, not sure if I can handle this fancy stuff:)

[Jonathan Vanasco]

On Friday, August 14, 2015 at 8:26:09 PM UTC-4, Jinghui Niu wrote:

Just a thought, if I don't commit those three tables together in my application, can I just use 3 Session objects to commit them separately, without having to worry about this two phase issue? I want to go simple, not sure if I can handle this fancy stuff:)

You can commit them as part of the same session (via the example above) or different sessions -- but you'll still run into the same database integrity issues that two-phase transaction commits were designed to solve.