well ... i was trying to be a bit lazy on describing my models, because
i'm interested in querying python classes that were not mapped to any
table, but to a lot of tables.
my model works, it doesn't have all the flaws pointed here. i just
thought it would be more clear to see. but no problem. let's just forget
about schematics and go to a simple class where i can save to the
database any attribute that doesn't start with a underscore -- a pretty
common pattern between pythonistas :)
so, i have two tables that defines a structure (entity and attribute),
and some other tables that contains their values:
* instance, to tell me what entity i'm using;
* types * values, to tell me what i have persisted, from what instance
it is, the type of the attribute and, from there, any validation i
may find necessary.
now we can start again? :)
what i would like to do is get this persisted "pseudo-model" and add
mappings so i can query on using the sqlalchemy orm api, even better if
i would also be able to insert, update, delete ...
my best regards,
richard.
On 12/05/2013 07:44 PM, Michael Bayer wrote:
Overall the different pieces aren’t making sense entirely.
We have the notion of a “schema”, stored in the database - that is,
Entity/Attribute. Those tables are fixed per type. It can tell me
for example that there’s a “Person” type with two attributes
associated, “name” and “website”.
So what happens when I do this:
class Person(Model):
name = StringType(required=True)
website = URLType()
is there a metaclass that’s writing to the database at that point the
Entity/Attribute rows for that type? It’s not clear when I say
Person.website, am I just going by the fact that we have
Person.website in the Python model, and if so what am I getting with
the Attribute or even the Value table? There could just be a table
called “url_values” and I join from Instance to that. The schema
seems to be stated twice here in two very different ways.
Also not clear what the purpose of Instance.valid_attributes are, this
seems redundant vs. Entity already referring to Attribute.
On Dec 5, 2013, at 11:48 AM, Richard Gerd Kuesters
<rich...@humantech.com.br <mailto:rich...@humantech.com.br>> wrote:
the vertical mapper example was kind of a base for me to develop my
"eav" system. i have added also a table that defines the structure of
the entity, since then I use schematics (and its json schema import -
export utility). it's very handy.
i think that a pseudo-code can explain a little better, those ones in
sa (i'll just write simplified):
*class Entity(Base):**
** id = Column(int, pk)**
** name = Column(str, unique)**
**
**
**class **Attribute(Base):**
** id = Column(int, pk)**
** name = Column(str)**
** discriminator = Column(enum) # bool, string, integer, blob, etc**
** entity_id = Column(fk(Entity.id)**)**
**
** entity = relationship(Entity)**
**
** tbl_args = uniqueconstraint(name, entity_id)*
basically, with those classes i can define how my eav objects are (of
course, they're much more complete, but for now it can give the
idea). so, when I have this schematics model:
*class Person(Model):**
** name = StringType(required=True)**
** website = URLType()*
it will be interpreted as:
*person_entity = Entity()**
**person_entity.name('person')**
**
**session.add(person_entity)**
**session.commit()**
**
**name_attr = Attribute()**
**name_attr.name = 'name'**
**name_attr.discriminator = TypeDiscriminator.STRING # i used here a
slightly modified code from decl_enum, a post you wrote in your blog**
**name_attr.entity. = person_entity**
**
**session.add(name_attr)**
**session.commit()**
**
**website_attr = Attribute()**
**...*
i think this can start to illustrate better what the "Person model"
really is. now, let's go to the values (in poor code again, lol):
*class**Instance**(Base):**
** id = Column(int, pk)**
** entity_id = Column(fk(Entity.id))**
**
** entity = relationship(Entity)**
** valid_attributes = relationship(Attribute**,
primaryjoin=entity_id==Attribute.entity_id)**
**
**
**class Value(Base):**
** id = Column(int, pk)**
** attribute_id = Column(fk(Attribute.id))**
** discriminator = Column(enum)**
**
** __mapper_args__ = dict(polymorphic_on=discriminator)**
**
**
**class StringValue(Value):**
** id = Column(fk(Value.id))**
** value = Column(string)**
**
** __mapper_args__ =
dict(polymorphic_identity=**TypeDiscriminator.STRING)**
**
**
**class BoolValue(Value):**
** ...*
then, with a dozen of ifs and elses, I can translate the values given
to a Person instance from schematics directly to the database.
so, if i want to find a string value (foo), from the Person model,
with an attribute named "name", my query would be something like (the
mess below):
*res = session.query([Entity, Attribute, Instance, StringValue])
.join(Attribute, Attribute.entity_id == Entity.id)
.join(Instance, Instance.entity_id == Entity.id)
.join(StringValue, [StringValue.id == Value.id,
Value.attribute_id == Attribute.id])
.filter(Entity.name == 'person', Attribute.name == "name",
Attribute.discriminator == TypeDiscriminator.STRING,
StringValue.value == 'foo')**
.all()*
ok. this query seems crappy and propably won't work if i run it now
(i'm writing this on the fly, lol), but it can give you a better idea
of my goal :)
now, instead of making all these queries (i don't have a problem
writing them because i'm writing the system), i would like to create
something "easier" to develop (since this project is the first python
project some of them will work on), so imagine using sqlalchemy at
this level :)
thanks a lot for your help.
my best regards,
richard.
On 12/05/2013 01:52 PM, Michael Bayer wrote:
On Dec 5, 2013, at 6:16 AM, Richard Gerd Kuesters
<rich...@humantech.com.br <mailto:rich...@humantech.com.br>> wrote:
ok, let's see if I can explain the scenario better :D
first, i'm using schematics <http://schematics.readthedocs.org/> to
define "arbitrary" models for message exchange between processes,
ui, events and so on. based on a json schema (that the schematics
model generates), i'm able to store data on the database using a
relatively sparse system of tables that compose my eav system --
not a good system (eav), but sometimes necessary (and i have bad
experiences with nosql databases).
so, given a composed model with data, i can populate the database
and retrieve data from it (i'm using postgres btw). so far so good.
now, i would like to "query" them :) let's suppose i have the
following (simple) model (from schematics example):
*
* *class Person(Model):**
** name = StringType(required=True)**
** website = URLType()*
with that, i know that StringType should go to StringDataTable,
URLType to StringDataTable, Boolean to BooleanTable, and so on.
using some sql filtering, i can retrieve all values that represents
the field "name" from the model "Person", but that's quite not
viable if I want my devs to work with eav when needed instead of
going nuts because someone created a hundred tables to store just
lines of data on each one.
so, if could extend "Model" or create a extension that maps my
class and then can be used in the "sqlalchemy way" of querying, it
would be awesome. let's say:
*
**session.query(Person).filter(Person.website == None).all()*
i know it'll give me some hard work to do it, but I'm willing to
give it a try :)
well we have an approach for this demonstrated in the “vertical
attribute mapping” example
(http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.vertical)
that uses any(), which produces an “EXISTS”.
though what I’m not sure about here is that it sounds like there
isn’t actually a “person” table. what links the “name” and
“website” tables together then with Person for a particular row?
e.g., what SQL does your query() need to render?
my best regards,
richard.
On 12/04/2013 05:51 PM, Michael Bayer wrote:
you’d need to show me a minimal case of the composed model in
question and what kind of operation you want to do.
you basically have to write your own comparators and such that
produce the correct SQL. it tends to not be that easy of a task.
On Dec 4, 2013, at 2:07 PM, Richard Gerd Kuesters
<rich...@humantech.com.br <mailto:rich...@humantech.com.br>> wrote:
thanks Mike!
it is good to know this :) can you give me a hint from where do i
to start? :)
best regards,
richard.
On 12/04/2013 04:38 PM, Michael Bayer wrote:
sure it can. you’d just need to be defining accessors and
methods for all the things it needs to do in each case - these
accessors would use the components to produce the appropriate
SQL constructs as needed.
On Dec 4, 2013, at 1:23 PM, Richard Gerd Kuesters
<rich...@humantech.com.br <mailto:rich...@humantech.com.br>> wrote:
hi all!
i was wondering if there is a way to create "fake" models, in
which i can query the way I want and map methods the way i want.
let's say i have a common object that is made in pieces to the
database (similar to the eav pattern), stored in multiple
tables. now, backwards, i have to make a whole mess of code to
bring the object with data again, but it works fine, so far.
what I really wanted was to use this class like a mapped model,
using session.query, filters and stuff, abstracting its keys to
other attributes and conditionals for the *real* table structure.
the problem is: can it be made?
thanks in advance,
richard.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to
sqlalchemy@googlegroups.com <mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
