Re: [sqlalchemy] changing the order of columns in primary keys, including when generated by mixins

2016-09-22 Thread Mike Bayer 



On 09/22/2016 01:29 PM, Chris Withers wrote:

On 22/09/2016 14:55, Mike Bayer wrote:


On 09/22/2016 07:30 AM, Chris Withers wrote:

How do you control the order in which columns are added to a
multi-column primary key when using the declarative primary_key=True
syntax?
How about when one of those columns comes from a mixin?


without mixins, the columns are processed in the order that they were
created - they all have a global "created index" number that is
consulted outside of the order in which the objects appear on a
declarative class.


Right, so you'd just re-order the columns...


With mixins, that all gets screwed up.  In that case you'd use a
PrimaryKeyConstraint() object on the final mapped class and that will
express the ordering of the columns.


...which I guess I'd want to introspect and then form that final
PrimaryKeyConstraint. How would I do that in this mixin:

https://github.com/Mortar/mortar_mixins/blob/master/mortar_mixins/temporal.py


...while also removing the primary key generated by the primary_key=True
on the various columns?


you know I bet you could use an event hook to just catch that when the 
Table is first set up and just re-set the PrimaryKeyConstraint.   I 
thought this could be done with a simple after_parent_attach but that 
seems to occur in an inopportune place.   so we'll get the table when 
it's done totally:


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy import event, PrimaryKeyConstraint


Base = declarative_base()


@event.listens_for(Base, "instrument_class", propagate=True)
def instrument_class(mapper, class_):
mapper.local_table.append_constraint(
PrimaryKeyConstraint(
*sorted(list(mapper.local_table.primary_key),
key=lambda column: column.key))
)


class Z(object):
z = Column(Integer, primary_key=True)


class A(Z, Base):
__tablename__ = 'a'
x = Column(Integer, primary_key=True)
y = Column(Integer, primary_key=True)


from sqlalchemy.schema import CreateTable
print CreateTable(A.__table__)












cheers,

Chris



--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] changing the order of columns in primary keys, including when generated by mixins

2016-09-22 Thread Chris Withers 

On 22/09/2016 14:55, Mike Bayer wrote:


On 09/22/2016 07:30 AM, Chris Withers wrote:

How do you control the order in which columns are added to a
multi-column primary key when using the declarative primary_key=True
syntax?
How about when one of those columns comes from a mixin?


without mixins, the columns are processed in the order that they were
created - they all have a global "created index" number that is
consulted outside of the order in which the objects appear on a
declarative class.


Right, so you'd just re-order the columns...


With mixins, that all gets screwed up.  In that case you'd use a
PrimaryKeyConstraint() object on the final mapped class and that will
express the ordering of the columns.


...which I guess I'd want to introspect and then form that final 
PrimaryKeyConstraint. How would I do that in this mixin:


https://github.com/Mortar/mortar_mixins/blob/master/mortar_mixins/temporal.py

...while also removing the primary key generated by the primary_key=True 
on the various columns?


cheers,

Chris

--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] changing the order of columns in primary keys, including when generated by mixins

2016-09-22 Thread Mike Bayer 



On 09/22/2016 07:30 AM, Chris Withers wrote:

Hi All,

How do you control the order in which columns are added to a
multi-column primary key when using the declarative primary_key=True syntax?
How about when one of those columns comes from a mixin?


without mixins, the columns are processed in the order that they were 
created - they all have a global "created index" number that is 
consulted outside of the order in which the objects appear ona 
declarative class.


With mixins, that all gets screwed up.  In that case you'd use a 
PrimaryKeyConstraint() object on the final mapped class and that will 
express the ordering of the columns. There was a slight regression 
in this behavior in 1.1.0b1 but people complained quickly and it was 
fixed (details at 
http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#no-more-generation-of-an-implicit-key-for-composite-primary-key-w-auto-increment).


http://docs.sqlalchemy.org/en/latest/core/constraints.html?highlight=primarykeyconstraint#sqlalchemy.schema.PrimaryKeyConstraint






Context is in the mail below, any help gratefully received!

Chris


 Forwarded Message 
Subject:Re: [GENERAL] performance problems with bulk inserts/updates
on tsrange with gist-based exclude constrains
Date:   Mon, 19 Sep 2016 09:41:33 -0700
From:   Jeff Janes 
To: Chris Withers 
CC: pgsql-general 



On Fri, Sep 16, 2016 at 2:01 AM, Chris Withers > wrote:

Hi All,

I have quite a few tables that follow a pattern like this:

 Table "public.my_model"
  Column |   Type| Modifiers
+---+---
  period | tsrange   | not null
  key| character varying | not null
  value  | integer   |
Indexes:
 "my_model_pkey" PRIMARY KEY, btree (period, key)
 "my_model_period_key_excl" EXCLUDE USING gist (period WITH &&,
key WITH =)
Check constraints:
 "my_model_period_check" CHECK (period <> 'empty'::tsrange)


Try swapping the order of the columns in the exclude constraint.  You
want the more selective criterion to appear first in the
index/constraint.  Presumably "key with =" is the most selective,
especially if many of your periods are unbounded.

Cheers,

Jeff

--
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+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] changing the order of columns in primary keys, including when generated by mixins

2016-09-22 Thread Chris Withers 

Hi All,

How do you control the order in which columns are added to a 
multi-column primary key when using the declarative primary_key=True syntax?

How about when one of those columns comes from a mixin?

Context is in the mail below, any help gratefully received!

Chris


 Forwarded Message 
Subject: 	Re: [GENERAL] performance problems with bulk inserts/updates 
on tsrange with gist-based exclude constrains

Date:   Mon, 19 Sep 2016 09:41:33 -0700
From:   Jeff Janes 
To: Chris Withers 
CC: pgsql-general 



On Fri, Sep 16, 2016 at 2:01 AM, Chris Withers > wrote:


   Hi All,

   I have quite a few tables that follow a pattern like this:

 Table "public.my_model"
  Column |   Type| Modifiers
   +---+---
  period | tsrange   | not null
  key| character varying | not null
  value  | integer   |
   Indexes:
 "my_model_pkey" PRIMARY KEY, btree (period, key)
 "my_model_period_key_excl" EXCLUDE USING gist (period WITH &&,
   key WITH =)
   Check constraints:
 "my_model_period_check" CHECK (period <> 'empty'::tsrange)


Try swapping the order of the columns in the exclude constraint.  You 
want the more selective criterion to appear first in the 
index/constraint.  Presumably "key with =" is the most selective, 
especially if many of your periods are unbounded.


Cheers,

Jeff

--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.