composite index using declarative
Daniel Robbins
How does one define a composite index using declarative?
Quick example? Couldn't find any examples in the docs or online.
In my particular case, "id" will be a primary key, and I'd like a
composite UniqueConstraint on "group_id" and "name", and a composite
index on "group_id" (pos 1) and "name" (pos 2).
Thanks and Regards,
-Daniel
Michael Bayer
we have some index examples at
http://www.sqlalchemy.org/docs/metadata.html#indexes . the Index is not
related to declarative and is specified separately.
>
> -Daniel
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
Daniel Robbins
> we have some index examples at
> http://www.sqlalchemy.org/docs/metadata.html#indexes . the Index is not
> related to declarative and is specified separately.
Any plans to improve this? I would have thought that Indexes would be defined under __table_args__ along with constraints, which are often related.
Since Index creation is such a common need, and the current means of creating one is counter-intuitive, do you think you could add a sample Index code snippet to the declarative documentation?
-Daniel
Michael Bayer
> On Mar 15, 2010, at 4:58 PM, Michael Bayer wrote:
>> we have some index examples at
>> http://www.sqlalchemy.org/docs/metadata.html#indexes . the Index is not
>> related to declarative and is specified separately.
>
> Any plans to improve this? I would have thought that Indexes would be
> defined under __table_args__ along with constraints, which are often
> related.
the word "improve" here makes me smile. Its the way it is intentionally
to look the way table DDL does. We could make the Index construct
capable of accepting non-table bound columns in its constructor which
would allow you to stick it within your declarative class before the Table
is generated...but we're literally talking about allowing a single indent.
>
> Since Index creation is such a common need, and the current means of
> creating one is counter-intuitive, do you think you could add a sample
> Index code snippet to the declarative documentation?
we definitely should because I am amazed at how often this question gets
asked. But the more puzzling thing is that it means the central tenet of
declarative isn't getting across to people, which is that nothing changes
at all when using declarative, its strictly a syntactical shortcut -
everything you do when not using declarative is still 100% doable, in
exactly the same way. I'm not sure why I see so many questions from
others of the form "but I'm using declarative!"
Daniel Robbins
>
> we definitely should because I am amazed at how often this question gets
> asked. But the more puzzling thing is that it means the central tenet of
> declarative isn't getting across to people, which is that nothing changes
> at all when using declarative, its strictly a syntactical shortcut -
> everything you do when not using declarative is still 100% doable, in
> exactly the same way. I'm not sure why I see so many questions from
> others of the form "but I'm using declarative!"
My advice to you is to embrace the feedback and use it to make the
project better. People want to *start* with declarative, whereas you
as the architect started with the core parts of the framework and
recently added declarative. So you know all the underpinnings, but
most users don't, and they want to remain on as high a level
(declarative) as possible without having to get sidetracked by being
forced to master the lower-level parts in order to simply create an
index for their declarative tables. Make sense? How to tie the two
together is not always documented clearly. Think from the new user's
perspective and try to accommodate them via the docs.
I'm including a bunch of my sample code below, which you are welcome
to use in the docs or SQLAlchemy itself. This stuff deals with table
creation - you also need more examples for queries, but I don't have
enough useful examples stored up for those yet.
Here's one thing that was tricky to figure out - a self-referencing
table using declarative. Tricky because of the "remote_side" reference
using a string:
# The following code implements a self-referencing, heirarchical
table, and is tricky code
# to figure out for SQLAlchemy. You can append children to .children
or choose to create the
# child first and set its parent. Commit one and the parent/children
should be committed
# too.
class Location(Base):
__tablename__ = 'location'
parent_id = Column(Integer, ForeignKey('location.id'))
parent = relation('Location', backref=backref('children'),
remote_side='location.c.id')
name = UniqueString(25)
desc = Column(String(80))
Below, there are some examples of "bridging the divide" between
non-declarative and declarative use, to show people how to do it. It's
much easier to understand how to do this when you can actually see
code that does it. A lot of the examples in the docs are somewhat
trivial and don't really show you how the pieces fit together, such as
this example below, which shows how to reference __table__:
class Endpoint(Base):
__tablename__ = 'endp'
__table_args__ = [
UniqueConstraint( 'samplegroup_id', 'name' ),
]
samplegroup_id, samplegroup = ManyToOne(SampleGroup, nullable=False)
name = Column(String(80), nullable=False)
os_id, os = ManyToOne(EndpointOSType, nullable=False)
Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
Endpoint.__table__.c.name)
Here is another much-needed example:
# this table has a "peer_id" which can reference another
HostInterface. myhostinterface.peer will reference this peer, or
# None if no peer is set in peer_id. There is a backref to from the
peer back to myhostinferface using the .peered backref.
#
class HostInterface(Base):
__tablename__ = 'host_i'
name = Column(String(20), nullable=False)
host_id, host = ManyToOne(Host, nullable=False )
hostmac_id, hostmac = ManyToOne(HostMAC, nullable=False)
peer_id = Column(Integer, ForeignKey('host_i.id'), index=True)
peer = relation('HostInterface', backref=backref('peered'),
remote_side='host_i.c.id')
ip_id, ip = ManyToOne(IP)
class Host(Base):
__tablename__ = 'host'
owner_id, owner = ManyToOne(User, nullable=False, index=True)
type = Column(String(1), nullable=False)
hostid = UniqueString()
#Our host can have many HostInterfaces. By default, SQLAlchemy would
allow us to reference them as a list, such as:
# for int in myhost.interfaces:
# print int
# But it would be nice to access them as a dictionary, so we could
grab a particular interface by typing:
# myhost.interfaces["eth0"]. Here's how we create an "interfaces"
reference in dictionary mode. This must be done
# outside of the class after both tables have been defined:
Host.interfaces = relation("HostInterface",
collection_class=column_mapped_collection(HostInterface.name))
I am working on a pretty simple DB project, with only about 12 tables,
but here is my supporting/helper code, which is an order of magnitude
more complex than the samples in the docs, but got declarative to the
point where 1) I could actually use it with Oracle by adding
auto-sequences to the declarative model (a BIG hurdle for new users
who just want to dive in and are using it with a db that doesn't have
auto-increment sequences) and 2) where I could significantly reduce
duplicated code, which is generally one of the benefits of using a
class heirarchy.
It took me quite a bit of time to piece this all together, where more
complex examples in the docs would have helped me along:
# Easy unique string creation:
def UniqueString(length=80,index=True):
return Column(String(length), unique=True, index=index, nullable=False)
# Easy ManyToOne relationship - returns a column plus a relation - see
example below:
def ManyToOne(myclass,nullable=True, backref=None, index=False):
return Column(Integer, ForeignKey(myclass.__tablename__ +
".id"), nullable=nullable, index=index), relation(myclass,
backref=backref)
# master primary key generation function - will generate a unique
numbered sequence, or if a "seqprefix" is provided
# will use seqprefix as the prefix for the sequence name.
seqnum=0
def PrimaryKey(seqprefix=None):
global seqnum
if not seqprefix:
seqnum += 1
seqname = "id_seq_%s" % seqnum
else:
seqname = "%s_id_seq" % seqprefix
return Column(Integer, Sequence(seqname, optional=True),
primary_key=True)
# This creates a common base class that has an integer primary key
(with a sequence, if necessary for your particular database
# (ie. Oracle). It handles the situation where you may be using single
table inheritance, probably not 100% but it works for my
# purposes. This is pretty useful for most beginning db projects:
class ClassDefaults(DeclarativeMeta):
def __init__(cls,classname, bases, dict_):
if not ( dict_.has_key('__mapper_args__') and
dict_['__mapper_args__'].has_key('polymorphic_identity') ):
# Only add the key if we are not creating a
polymorphic SQLAlchemy object, because SQLAlchemy
# does not want a separate 'id' key added in that case.
# seqprefix can be None
seqprefix = getattr(cls,'__tablename__',None)
dict_['id'] = PrimaryKey(seqprefix=seqprefix)
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
# Now, use the following "Base" class as the parent of your
declarative tables, and you will automatically get an integer primary
# key "id" added for you:
Base = declarative_base(metaclass=ClassDefaults)
Regards,
Daniel
Michael Bayer
> On Tue, Mar 16, 2010 at 8:08 AM, Michael Bayer <mik...@zzzcomputing.com>
> wrote:
>>
>> we definitely should because I am amazed at how often this question gets
>> asked. � But the more puzzling thing is that it means the central tenet
>> of
>> declarative isn't getting across to people, which is that nothing
>> changes
>> at all when using declarative, its strictly a syntactical shortcut -
>> everything you do when not using declarative is still 100% doable, in
>> exactly the same way. �I'm not sure why I see so many questions from
>> others of the form "but I'm using declarative!"
>
> My advice to you is to embrace the feedback and use it to make the
> project better.
I'm sure if you review our mailing list history for the past five years
you'll be pleased to know that the virtually all of the progress made on
the SQLAlchemy project post version 0.1 has been driven completely by
end-user need, but at the same time not turning the project into a
PHP-soup of flags and switches, distilling what end users need into an
ever expanding but coherent system that attempts to be as consistent as
possible. Rest assured that if we didn't do a pretty good job of
balancing these sides we wouldn't still be a prominent project today.
> People want to *start* with declarative, whereas you
> as the architect started with the core parts of the framework and
> recently added declarative. So you know all the underpinnings, but
> most users don't, and they want to remain on as high a level
> (declarative) as possible without having to get sidetracked by being
> forced to master the lower-level parts in order to simply create an
> index for their declarative tables. Make sense? How to tie the two
> together is not always documented clearly. Think from the new user's
> perspective and try to accommodate them via the docs.
I appreciate the rant but we're only talking about adding a distinct
section to the declarative documentation regarding indexes, which is
already accepted, and specific examples regarding remote_side and such are
non-controversial as well and we shall review the cases you present.
Declarative doesn't *require* that arguments are passed as strings,
either, this is optional. There's some discussion of some common gotchas
here on the FAQ at
http://www.sqlalchemy.org/trac/wiki/FAQ#ImusingDeclarativeandsettingprimaryjoinsecondaryjoinusinganand_oror_andIamgettinganerrormessageaboutforeignkeys
, but we can integrate this into the declarative docs too.
It doesn't make much sense for the declarative documentation to completely
duplicate the entire mapping/relation/metadata sections of the
documentation, however. It's still expected that users who want to deal
with Index, Sequence, etc. constructs will have read these sections.
Better hyperlinking, a constant work in progress (and very open to outside
contribution via patches, we transitioned to Sphinx largely for this
reason) also makes the docs easier to follow.
>
> Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
> Endpoint.__table__.c.name)
Index to my knowledge does accept declarative columns these days. You can
pass in Endpoint.samplegroup_id and Endpoint.name directly. The above
workaround was for a bug.
> # But it would be nice to access them as a dictionary, so we could
> grab a particular interface by typing:
> # myhost.interfaces["eth0"]. Here's how we create an "interfaces"
> reference in dictionary mode. This must be done
> # outside of the class after both tables have been defined:
>
> Host.interfaces = relation("HostInterface",
> collection_class=column_mapped_collection(HostInterface.name))
I'm not aware of this requirement. you should be able to create the
"interfaces" relation inside of Host, using a string to define
"collection_class" or otherwise use attribute_mapped_collection("name").
If this is not possible, then its a bug.
> I am working on a pretty simple DB project, with only about 12 tables,
> but here is my supporting/helper code, which is an order of magnitude
> more complex than the samples in the docs, but got declarative to the
> point where 1) I could actually use it with Oracle by adding
> auto-sequences to the declarative model (a BIG hurdle for new users
> who just want to dive in and are using it with a db that doesn't have
> auto-increment sequences)
I wouldn't consider Oracle to be a good candidate for "diving in". But we
do have a prominent sidebox in the ORM tutorial now, describing the need
for the Sequence construct when using Firebird or Oracle, which you can
see at
http://www.sqlalchemy.org/docs/ormtutorial.html#define-and-create-a-table
.
> and 2) where I could significantly reduce
> duplicated code, which is generally one of the benefits of using a
> class heirarchy.
>
> It took me quite a bit of time to piece this all together, where more
> complex examples in the docs would have helped me along:
I'd note that the metaclass stuff you've done has been worked into a
simple built in feature, using our new "mixin" capability, which is
described at:
http://www.sqlalchemy.org/docs/reference/ext/declarative.html#mix-in-classes
This feature was contributed by Chris Withers who showed up to our sprints
to help, and we gave him all the access he needed to get it done.
>
> # Easy unique string creation:
>
> def UniqueString(length=80,index=True):
> return Column(String(length), unique=True, index=index,
> nullable=False)
>
> # Easy ManyToOne relationship - returns a column plus a relation - see
> example below:
>
> def ManyToOne(myclass,nullable=True, backref=None, index=False):
> return Column(Integer, ForeignKey(myclass.__tablename__ +
> ".id"), nullable=nullable, index=index), relation(myclass,
> backref=backref)
We mention prominently in the intro and on the wiki that there's a place
for recipes like these, which is the UsageRecipes section:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes
Recipes on that section that are of extreme general usefulness eventually
become built in features. In particular the ManyToOne etc. idea I've
considered, the only issue is how to present without adding to its being
overwhelming.
- mike
Daniel Robbins
<mik...@zzzcomputing.com> wrote:
> Daniel Robbins wrote:
>
>> People want to *start* with declarative, whereas you
>> as the architect started with the core parts of the framework and
>> recently added declarative. So you know all the underpinnings, but
>> most users don't, and they want to remain on as high a level
>> (declarative) as possible without having to get sidetracked by being
>> forced to master the lower-level parts in order to simply create an
>> index for their declarative tables. Make sense? How to tie the two
>> together is not always documented clearly. Think from the new user's
>> perspective and try to accommodate them via the docs.
>
> I appreciate the rant but we're only talking about adding a distinct
> section to the declarative documentation regarding indexes, which is
> already accepted, and specific examples regarding remote_side and such are
> non-controversial as well and we shall review the cases you present.
I'm not ranting, I'm offering constructive, critical feedback for your
excellent project.
> It doesn't make much sense for the declarative documentation to completely
> duplicate the entire mapping/relation/metadata sections of the
> documentation, however.
Complete duplication is not required. However. I've written a lot of
technical documentation, and I've found that some repetition, and
building upon ideas introduced in other sections, does make technical
documentation much easier to use.
>> Index('endp_grp_ix', Endpoint.__table__.c.samplegroup_id,
>> Endpoint.__table__.c.name)
>
> Index to my knowledge does accept declarative columns these days. You can
> pass in Endpoint.samplegroup_id and Endpoint.name directly. The above
> workaround was for a bug.
Then this should be documented in the declarative section, with an example.
>> Host.interfaces = relation("HostInterface",
>> collection_class=column_mapped_collection(HostInterface.name))
>
> I'm not aware of this requirement. you should be able to create the
> "interfaces" relation inside of Host, using a string to define
> "collection_class" or otherwise use attribute_mapped_collection("name").
> If this is not possible, then its a bug.
Looks like a bug in 0.6_beta1 then:
Traceback (most recent call last):
File "base.py", line 525, in <module>
class HostInterface(Base):
File "base.py", line 533, in HostInterface
interfaces = relation('HostInterface',
collection_class=column_mapped_collection('HostInterface.name'))
File "/usr/lib64/python2.6/site-packages/sqlalchemy/orm/collections.py",
line 132, in column_mapped_collection
cols = [expression._no_literals(q) for q in util.to_list(mapping_spec)]
File "/usr/lib64/python2.6/site-packages/sqlalchemy/sql/expression.py",
line 980, in _no_literals
"to indicate a SQL expression literal, or 'literal()' to indicate
a bound value." % element)
sqlalchemy.exc.ArgumentError: Ambiguous literal: 'HostInterface.name'.
Use the 'text()' function to indicate a SQL expression literal, or
'literal()' to indicate a bound value.
Regardless, documentation in the declarative section about how to do
this cleanly would be appreciated.
> I wouldn't consider Oracle to be a good candidate for "diving in". But we
> do have a prominent sidebox in the ORM tutorial now, describing the need
> for the Sequence construct when using Firebird or Oracle, which you can
> see at
> http://www.sqlalchemy.org/docs/ormtutorial.html#define-and-create-a-table
That is a great addition.
> I'd note that the metaclass stuff you've done has been worked into a
> simple built in feature, using our new "mixin" capability, which is
> described at:
I think Mix-Ins are a great idea and very elegant, and I'm very
thankful they were added by Chris, but again in this case, more
complex examples would be extremely helpful.
If you look closely at what I am trying to do -- naming the primary
key sequence based on the name of the table -- Mix-Ins provide no
obvious mechanism to do this, since I need to create the primary key
and sequence "on the fly" after the table name is provided. With the
primary key in the base class and the table name in the sub-class,
this becomes problematic.
If you know how to implement the code I provided using a Mix-in, then
I'd love to see an example on this mailing list, or even better, in
the docs :)
Regarding Oracle, if someone only has Oracle to work with, or wants to
create an Oracle-based solution, then they need to figure out how to
do sophisticated things like this in order to get meaningful work done
with SQLAlchemy and feel comfortable with the framework. I don't think
the extra code or design philosophy of SQLAlchemy is an issue, it's
the time required to harvest the necessary info from the docs and
compile it into a working skeleton. Once the skeleton has been
assembled, SQLAlchemy is a joy to use.
> We mention prominently in the intro and on the wiki that there's a place
> for recipes like these, which is the UsageRecipes section:
>
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes
>
> Recipes on that section that are of extreme general usefulness eventually
> become built in features. In particular the ManyToOne etc. idea I've
> considered, the only issue is how to present without adding to its being
> overwhelming.
OK, I can add a bunch of my code there. I wouldn't suggest that
ManyToOne be built-in to SQLAlchemy, as long as the sample code is
readily available (note: a good place to present it would be in a
declarative tutorial, to give people some customizable building blocks
to make declarative more pleasant in real-world use.)
I'm happy having my own ManyToOne as boilerplate at the top of my
SQLAlchemy code that works just the way I want it to (and can be
tweaked to work differently if I need it.) I agree with the design
philosophy of SQLAlchemy and am really only asking for beefier
examples in the docs :)
Thanks for your prompt reply and feedback.
Best Regards,
Daniel