Re: [sqlalchemy] EAV Optimizations
I believe it was for some work you did for Akiban. If I remember correctly (ha!) I believe you detailed with some slides an updated join strategy that they are using (or that you hacked) into a custom version of SA used by them. I noted the demo as I am planning on using EAV for a personal project and was wondering if that updated EAV join strategy code would make it into the released version of SA. From your slides I think you showed that the number of joins on the EAV tables with the updated strategy was significantly reduced. Sorry I can't remember more. As a side note EAV is not a panacea but I have used it to great effect in the past judiciously. In the application in question we had millions of digital assets in effectively a proprietary photo library used by Sony. All of the data for the application except the binary asset was stored in the DB (Oracle in this case). The requirement for the project was that the users could also annotate their assets with security descriptors, groups, and any other typed metadata they wished, including links to other assets to create packages of assets. The metadata key/value count was going to be in the billions of facts. As such we made the decision to make the entire of the metadata schema EAV including internal metadata (security, stock keys such as create date, name, path, etc). By putting all of the metadata in EAV we were able to also employ such niceties as indexes for speed and table level constraints (as well as triggers for certain keys and other business logic in PL/SQL). The EAV method though did add some complexity to the application queries which we abstracted away by using some Ruby ORM libs that could work natively with EAV schemas hiding most of the complexity of the schema implementation from the application engineers. Over all the project was a big success and I believe is still in use with many billions of records, all without requiring the use of a secondary key/value document store with differing query semantics and iffy indexing and constraint enforcement. I wouldn't use EAVs for everything, not by a long shot, but properly implemented and indexed they work great in my practical experience. Mike On Monday, May 13, 2013 5:26:01 PM UTC-7, Michael Bayer wrote: what video is that ?I don't use EAV's too often. If you can point me to something I can identify what it was. On May 13, 2013, at 8:22 PM, Lycovian mfwi...@gmail.com javascript: 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. 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+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . 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
oh, yeah I don't have that code, that was Ori talking about a particular client integration they had, where they went bananas with EAV. The point there was to show how Akiban does a much better job querying structures like that, but I had no involvement in that gig. The EAV stuff I have, you can look in examples/vertical/ for a few old ones, then there's the http://www.sqlalchemy.org/trac/wiki/UsageRecipes/VersionedMap recipe which is more or less like the one I use in my current work, that's about it. As for use case, I'm all in favor of judicious use of almost anything. I don't respond well to the never do XYZ! thing, I feel like there should be a, * unless you have more than 10 years of experience clause (see http://norvig.com/21-days.html for background). I use, to a greater or lesser extent, all the baddies: global variables, mixins, multiple inheritance, EAVs, explicit locking, ORM queries in my controllers, all of it, if it's been called bad I've probably got it in production in a very judicious and pragmatic way, without problems. On May 16, 2013, at 3:48 PM, Lycovian mfwil...@gmail.com wrote: I believe it was for some work you did for Akiban. If I remember correctly (ha!) I believe you detailed with some slides an updated join strategy that they are using (or that you hacked) into a custom version of SA used by them. I noted the demo as I am planning on using EAV for a personal project and was wondering if that updated EAV join strategy code would make it into the released version of SA. From your slides I think you showed that the number of joins on the EAV tables with the updated strategy was significantly reduced. Sorry I can't remember more. As a side note EAV is not a panacea but I have used it to great effect in the past judiciously. In the application in question we had millions of digital assets in effectively a proprietary photo library used by Sony. All of the data for the application except the binary asset was stored in the DB (Oracle in this case). The requirement for the project was that the users could also annotate their assets with security descriptors, groups, and any other typed metadata they wished, including links to other assets to create packages of assets. The metadata key/value count was going to be in the billions of facts. As such we made the decision to make the entire of the metadata schema EAV including internal metadata (security, stock keys such as create date, name, path, etc). By putting all of the metadata in EAV we were able to also employ such niceties as indexes for speed and table level constraints (as well as triggers for certain keys and other business logic in PL/SQL). The EAV method though did add some complexity to the application queries which we abstracted away by using some Ruby ORM libs that could work natively with EAV schemas hiding most of the complexity of the schema implementation from the application engineers. Over all the project was a big success and I believe is still in use with many billions of records, all without requiring the use of a secondary key/value document store with differing query semantics and iffy indexing and constraint enforcement. I wouldn't use EAVs for everything, not by a long shot, but properly implemented and indexed they work great in my practical experience. Mike On Monday, May 13, 2013 5:26:01 PM UTC-7, Michael Bayer wrote: what video is that ?I don't use EAV's too often. If you can point me to something I can identify what it was. On May 13, 2013, at 8:22 PM, Lycovian mfwi...@gmail.com 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. 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+...@googlegroups.com. To post to this group, send email to sqlal...@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving
Re: [sqlalchemy] EAV Optimizations
On 05/14/2013 16:58, Michael Bayer 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. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. 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
On May 16, 2013, at 4:07 PM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:58, Michael Bayer 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. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. I agree, I've seen lots of projects where they base it all on EAV, and it's a terrible idea. But I was lucky to get my everything-is-an-EAV thing done with by about 1998. 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. -- 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 16 May 2013, at 21:21, Michael Bayer mike...@zzzcomputing.com wrote: On May 16, 2013, at 4:07 PM, Julien Cigar jci...@ulb.ac.be wrote: On 05/14/2013 16:58, Michael Bayer 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. Yes it may be acceptable to store configurational data (although I would probably use HSTORE under PostgreSQL). But a large EAV based project sounds terribly bad to me. I agree, I've seen lots of projects where they base it all on EAV, and it's a terrible idea. But I was lucky to get my everything-is-an-EAV thing done with by about 1998. I have a project at the moment where I'm storing manufacturing test results. Each test is basically a big set of key/value pairs. The set of keys changes over time (as new tests are written), and one of the requirements for the database is that users be able to efficiently query specific values (for example to view trends over time). My first attempt (written in 2006, using SQLAlchemy 0.3, and still running!) had a table for each test type, with columns for each measurement. The schema was tightly coupled to the test results, and every time a test was altered or added, I would have to change the code and alter the database to match. This is painful enough that I don't do it very often, so there are an increasing number of measurements which aren't stored in the database. I've recently revisited the system, and had a go at using an EAV approach. So far I think I am approaching 100,000,000 values. I thought this might be a bit much for a single table, so I've split them across a number of tables, based on a hash of the key name. I'm not sure yet if that was a good idea, but it means I've got 256 tables with between 0 and 4,000,000 rows in. The tables have 3 columns, 1 for the actual value, one is a foreign key to say which test the value belongs to, and the other is a foreign key to the table describing the key. Currently there are about 32000 distinct keys. I don't think this would be a good match for HSTORE - I assume that retrieving values from HSTORE won't be as efficient as retrieving them from their own rows. The new system is definitely much more flexible than the old, and there is a lot less code to maintain. I'm not sure about the performance yet - some of the queries feel slower than I think they should, but I haven't looked into it properly yet. Simon -- 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 Thu, May 16, 2013 at 7:16 PM, Simon King si...@simonking.org.uk wrote: I don't think this would be a good match for HSTORE - I assume that retrieving values from HSTORE won't be as efficient as retrieving them from their own rows. It depends on the number of attributes per test. It won't ever be as efficient the access itself, but it might compensate the overhead having one row per attribute. Furthermore, you can create functional indexes to index over a particularly interesting attribute, and with partial indexes you can even filter, which means, it could be pretty fast if there's no need to run arbitrary queries. I don't think trends over time would be a problem for an HSTORE-based schema, since you'd already pay the cost of sequential scan anyway. This is all assuming there's a limited and rather small number of attributes per test (dozens?). Otherwise EAV is probably better. -- 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 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.
[sqlalchemy] EAV Optimizations
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. 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.
Re: [sqlalchemy] EAV Optimizations
what video is that ?I don't use EAV's too often. If you can point me to something I can identify what it was. On May 13, 2013, at 8:22 PM, Lycovian mfwil...@gmail.com 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. 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. -- 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.
