Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
EAVs have a definite place and with judicious use they are extremely useful. On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 16:09, Michael Bayer wrote: EAVs have a definite place and with judicious use they are extremely useful. just curious: could you give an example where EAV is useful ? On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
When you are storing data with key/values, where the set of keys is part of the data. Storing configurational data is the main use case.HSTORE not an option because it is postgresql-specific. On May 14, 2013, at 10:49 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:09, Michael Bayer wrote: EAVs have a definite place and with judicious use they are extremely useful. just curious: could you give an example where EAV is useful ? On May 14, 2013, at 4:37 AM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 02:22, Lycovian wrote: A few months ago I watched a video conference where Mike demo'd some optimizations for SQLAlchemy when using EAV schemas. Does anyone know if these optimizations will make it into the product that we have access to? I'm about to start a large EAV based project with SA and I was curious. please don't ... EAV is evil and has tons of disadvantages. I don't know about your database, but in PostgreSQL you have HSTORE and JSON which could often be used to replace EAV Mike -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. -- 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?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
storing pickles in the database is an awful idea since your data is now specific not just to Python but to the current shape of your object model at a point in time. Storing JSON is not acceptable for the case where you need to audit and version each user that has made changes to particular keys, as well as a history of those changes. On May 14, 2013, at 11:18 AM, Claudio Freire klaussfre...@gmail.com wrote: On Tue, May 14, 2013 at 11:58 AM, Michael Bayer mike...@zzzcomputing.com wrote: When you are storing data with key/values, where the set of keys is part of the data. Storing configurational data is the main use case.HSTORE not an option because it is postgresql-specific. Configuration data can be json'd or pickled. You'd only use EAV if the set of keys or attributes is big enough that you can't json or pickle. -- 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?hl=en. 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
On Tue, May 14, 2013 at 12:38 PM, Michael Bayer mike...@zzzcomputing.com wrote: storing pickles in the database is an awful idea since your data is now specific not just to Python but to the current shape of your object model at a point in time. Storing JSON is not acceptable for the case where you need to audit and version each user that has made changes to particular keys, as well as a history of those changes. You could certainly add audit information to the json. Point in case: json is functionally equivalent to hstore, so if you were considering hstore, you can also use json. I agree pickle has its issues, not the least of which is security, but it's not so specific to Python[0] as it seems, nor is it any more dependent of object's structure as your EAV is dependent on attribute semantics. The issues with pickle aren't of that sort, but more of security and inconvenience (try reading a pickle from a command line database client and you'll want to shoot yourself). [0] http://irmen.home.xs4all.nl/pyrolite/ -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] EAV Optimizations
On May 14, 2013, at 12:05 PM, Claudio Freire klaussfre...@gmail.com wrote:
On Tue, May 14, 2013 at 12:38 PM, Michael Bayer
mike...@zzzcomputing.com wrote:
storing pickles in the database is an awful idea since your data is now
specific not just to Python but to the current shape of your object model at
a point in time. Storing JSON is not acceptable for the case where you
need to audit and version each user that has made changes to particular
keys, as well as a history of those changes.
You could certainly add audit information to the json. Point in case:
json is functionally equivalent to hstore, so if you were considering
hstore, you can also use json.
we use JSON where it is expedient, in our case we have UX where users click on
specific keys, want to see the version history of that key and how it is used
across versions, so it's appropriate that keys are actual entities.It's a
lot less code than working with diffs and is directly queryable with SQL.
I agree pickle has its issues, not the least of which is security, but
it's not so specific to Python[0] as it seems, nor is it any more
dependent of object's structure as your EAV is dependent on attribute
semantics.
It's not like I have today to argue about this, but I think most people will
agree the pickle format is a lot less portable than JSON:
d = {key1: value1, key2: value2}
import pickle
import json
pickle.dumps(d)
(dp0\nS'key2'\np1\nS'value2'\np2\nsS'key1'\np3\nS'value1'\np4\ns.
json.dumps(d)
'{key2: value2, key1: value1}'
The issues with pickle aren't of that sort, but more of
security and inconvenience (try reading a pickle from a command line
database client and you'll want to shoot yourself).
[0] http://irmen.home.xs4all.nl/pyrolite/
--
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?hl=en.
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Casting an overlap filter as an array
Thanks for the reply. Your example made me realize my model did not also use VARCHAR. Changing the model column type to match the casting type made this work. On Friday, May 10, 2013 3:57:36 PM UTC-4, Audrius Kažukauskas wrote: On Mon, 2013-05-06 at 11:04:57 -0700, Glenn Yonemitsu wrote: In Postgresql I have a CMS entry model with a tag column varchar(20)[]. I want to do a query so a row with any of the tags will be returned. I know overlap is the method to use but I can't get the casting done correctly. Right now I am trying (and a lot of searching revealed similar solutions): query.filter(ContentEntry.tag.overlap(cast(tags, ARRAY(VARCHAR(20) But I am getting the error: AttributeError: 'SQLCompiler' object has no attribute 'visit_array'. Any variation involving ARRAY() or array() says there is no attribute visit_ARRAY or visit_array. The following hand crafted SQL works, so for now I'm trying to at least get to this point (and as I understand, overlap will use the operator): SELECT content_entry.title, content_entry.tag FROM content_entry WHERE content_entry.tag cast(array['foo', 'bar'] as varchar(20)[]); How can I get this casted correctly? This is strange, casting to varchar(20)[] works here without problems. Given the following model: class Test(Base): __tablename__ = 'test_arr' id = Column(Integer, primary_key=True) data = Column(ARRAY(VARCHAR(20))) The query session.query(Test).\ filter(Test.data.overlap(cast(['foo'], ARRAY(VARCHAR(20) is compiled to SELECT test_arr.id AS test_arr_id, test_arr.data AS test_arr_data FROM test_arr WHERE test_arr.data CAST(%(param_1)s AS VARCHAR(20)[]) I'm using PostgreSQL 9.2.4, psycopg2 2.5 and SQLAlchemy 0.8.1, though somehow I doubt it has anything to do with different version numbers of any of those. On a side note, if I were you, I wouldn't bother with varchar(20)[] and use text[] instead. Internally varchar and text are stored the same way, the only difference is the former sets the length constraint if it is provided. -- Audrius Kažukauskas http://neutrino.lt/ -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
