Dynamic Components in ActiveRecord
Dan Bunea
One of our requirements in a new app we're writing is to allow runtime add/remove of columns to some tables in our database. To be able to still use AR, we thought we could use NH 1.2 dynamic components ( http://www.hibernate.org/hib_docs/nhibernate/1.2/reference/en/html/components.html#components-dynamic). Doesn anyone know whether this is available in AR on Castle RC3, or will we have to contribute to it?
Thanks,
--
Dan Bunea
http://danbunea.blogspot.com
Ayende Rahien
Hamilton Verissimo
On 10/16/07, Dan Bunea <dan....@gmail.com> wrote:
--
Cheers,
hamilton verissimo
ham...@castlestronghold.com
http://www.castlestronghold.com/
josh robb
>
> Let me guess: SaaS?
Welcome to my world.
:(
Wait until they want reports on these new fields is all I have to say
(and take a look at CouchDB).
j.
Hamilton Verissimo
more columns) or everything is on an Entity table (kinda like jira
does)?
Ayende Rahien
CRM, ERP, Bug Tracking, etc.
I would not go with dynamic components, actually, but rather sub classing at the customer site, maybe aided by a wizard
Jonathon Rossi
In a project I have been working on, a specific entity has dynamic fields. I implemented fields as separate entities. The complexity comes when you think about what type of fields are needed. I had to create one for text, many-to-one (with another value entity), user, and various others specific ones to store links to other entities in the model. My model became even more complex when I implemented revisions on all those fields. All the data for these fields are stored in separate tables, I didn't want to make changes to the DB schema for the dynamic columns so heaps of separate entities and heaps of joins is the way it had to be made.
If there is a nicer way of doing this or even a nicer way of being able to deal with all these classes in my calling code I'd love to hear what others have to say.
What approach are you using? Root entities that allow extensions (ie
more columns) or everything is on an Entity table (kinda like jira
does)?
On 10/16/07, josh robb < josh...@fastmail.fm> wrote:
>
> On 10/16/07, Hamilton Verissimo <ham...@castlestronghold.com> wrote:
> >
> > Let me guess: SaaS?
>
> Welcome to my world.
>
> :(
>
> Wait until they want reports on these new fields is all I have to say
> (and take a look at CouchDB).
>
> j.
>
> >
>
--
Cheers,
hamilton verissimo
ham...@castlestronghold.com
http://www.castlestronghold.com/
--
Jonathon Rossi
josh robb
> What approach are you using? Root entities that allow extensions (ie
> more columns) or everything is on an Entity table (kinda like jira
> does)?
I don't know what Jira does. (Looks what jira does:
http://confluence.atlassian.com/display/JIRA/Database+Schema)
Ok - yep - like JIRA - I'd call it meta modeling. At the moment we've
managed to keep the extensibility to one entity - but it's really
necessary for 3. (and all of them have multiple many-to-many relations
- which also might need to be extensible).
Sigh - anyway.. I'm personally of the opinion that I'm fighting the
relational model with this stuff. A semi-structured store seems to be
the way forward (in the long term).
j.
Ayende Rahien
Create additional tables for the new data, and that would make it much easier, no?
Dan Bunea
They do want reporting on it, but they write their own reports, and most of the time they don't.
There is a second oprion: Lucene, but they might not like it because of the reporting tools.
Thanks,
Dan
What approach are you using? Root entities that allow extensions (ie
more columns) or everything is on an Entity table (kinda like jira
does)?
On 10/16/07, josh robb < josh...@fastmail.fm> wrote:
>
> On 10/16/07, Hamilton Verissimo <ham...@castlestronghold.com> wrote:
> >
> > Let me guess: SaaS?
>
> Welcome to my world.
>
> :(
>
> Wait until they want reports on these new fields is all I have to say
> (and take a look at CouchDB).
>
> j.
>
> >
>
--
Cheers,
hamilton verissimo
ham...@castlestronghold.com
http://www.castlestronghold.com/
Ayende Rahien
You are talking about using that as a data store instead of for searching?
Jonathon Rossi
However, that doesn't work so easily when users of the app should be able to change the fields. Straight off you'd need dbo permissions on the DB.
Jonathon Rossi
Ayende Rahien
If we are talking about something like ERP, then by the nature of the beast, you are going to need admin access to make those modifications.
If we are talking about defining custom fields for each project in a bug tracking system, this is another matter, probably best solved by putting those outside the DB.
I am very interested with the lucene idea. Mostly because it is so elegant and so easy to implement, and it makes searches very easy
josh robb
> You are only fighting it because you are trying to avoid using tables the
> way they are meant to.
Nope - this is genuinely semi-structured data (in my thinking). I'm
fighting the model because otherwise I'd get to the point where I
neede a seperate table for each instance of an entity. (not to mention
variations for each customer 20+ and growing rapidly).
> Create additional tables for the new data, and that would make it much
> easier, no?
We actually have an application in house which works in the same
domain (delphi + MSSQL 6.5) which does in fact create a seperate table
for each entity.
Trust me - it's not easier.
j.
josh robb
> change the fields. Straight off you'd need dbo permissions on the DB.
In my scenario - this is exactly part of the problem. It's user
extensible - not customer extensible.
It gets worse because until now we've been able to get away with not
providing much filtering on dynamic fields - now we're in a situation
where we need to provide filtering on entities based on dynamically
configured attributes. (Think Ayende's policy screen but much worse).
j.
josh robb
Users within the system - who have limited access to some subset of
entities in the system and the ability to customise those entities.
> If we are talking about something like ERP, then by the nature of the beast,
> you are going to need admin access to make those modifications.
nope
> If we are talking about defining custom fields for each project in a bug
> tracking system, this is another matter, probably best solved by putting
> those outside the DB.
Exactly - different fields for each bug. :(
> I am very interested with the lucene idea. Mostly because it is so elegant
> and so easy to implement, and it makes searches very easy
Yep - except that Lucene wasn't designed for this - which is why I'm
personally so excited about CouchDB - it's pretty similar actually -
except it was designed from the beginning as a persistent data store,
with ACID guarantees etc.
j.
josh robb
> What approach are you using? Root entities that allow extensions (ie
> more columns) or everything is on an Entity table (kinda like jira
> does)?
Have you looked at this OfBix Entity Engine thing?
Dan Bunea
On the other hand, the client can go into the administration UI (WPF) and add custom fields to the options. He can later change, delete but only the custom ones. It's not an ERP, it's, despite how incredible it might seem, a 3D application: http://www.visualretailing.com/
Thanks for your answers,
Dan
PS offtopic: MVP/MVP on WPF anyone?
Ayende Rahien
I am spending the the last two weeks working on search screens, and the last one search screen that I did took roughly two - three man months to complete.
I estimate that I would about that amount of effort.
I see what you mean now, you need extra fields per entity instance, not per entity type.
That being the case, I would strongly recommend on going with the lucene approach, that is incredibly easy to get starting, and searching in built in.
josh robb
> I am spending the the last two weeks working on search screens, and the last
> one search screen that I did took roughly two - three man months to
> complete.
> I estimate that I would about that amount of effort.
Yeah - I feel you! The good news is that I get to specify the feature
- so it might be possible to find an 80/20 solution.
> I see what you mean now, you need extra fields per entity instance, not per
> entity type.
> That being the case, I would strongly recommend on going with the lucene
> approach, that is incredibly easy to get starting, and searching in built
> in.
I will definitely investigate - the main thing that worries me about
this is reporting tools. But I might be able to work around that.
Ayende Rahien
If you think about your entities as data, it makes a lot more sense.
Ayende Rahien
Ayende Rahien
Ayende Rahien
http://www.ayende.com/Blog/archive/2007/10/16/Lucene-as-a-data-repository.aspx
josh robb
> Lucene was designed for searching, yes, also as a document repository.
> If you think about your entities as data, it makes a lot more sense.
(not sure if I should take this off list - or somewhere more appropriate)
The problem is that a key underlying assumption of lucene is that it
is possible (although maybe expensive) to reindex all the documents
you feed it. It doesn't make any consistency guarantees in the face of
failure.
CouchDB is basically the same model as Lucene (documents+attributes -
with a blob type) - except it's major design purpose is as a
persistent document storage engine.
I'd need to have some serious reassurance from Doug Cutting that this
was a good idea before I went ahead and did this as it really seems
outside of the designed for use cases. (Like using .mdb/.xls's for
serving asp classic websites ;)
j.
Ayende Rahien
I can see your point, but I think that this is already used in mission critical scenarios right now.
I did some searches for transaction semantics for Lucene, and the results seems to point toward the DB store as the way to go there.
It has not been ported yet (that I know of).
I would be interested in receiving an authoritative answer for this.
Phil H
At the database level (not the NH/AR level etc) I have come across a
number of ways of doing this.
I am not promoting any of these - just giving you some options to
consider.
1. Entity-Attribute-Value (EAV) See http://en.wikipedia.org/wiki/Entity-Attribute-Value_model
In this model the data is pivoted so that columns become rows. If
you are using SQL Server you could use a sql_variant column to store
the value rather than have a different column for each type or store
all types as a varchar. As I see it the main downside to this approach
is the increased complexity of querying.
2. XML. (Tokenized field) Store additional user defined data in an XML
column (SQL Server 2005 or later)
3. Predefined columns. Create a number of generic columns (eg,
varchar1, varchar2, ...) which can be mapped to custom properties as
they are added. This is how MS Sharepoint and TFS seem to work.
Actually, this might not be appropriate for your situation since you
want to be able to add custom fields per item, not per type. FWIW I
notice that SQL Server 2008 has support for "sparse columns" which
allows you to define columns which use up no space if they are not
used.
Regards
Phil
On Oct 16, 11:59 pm, "Jonathon Rossi" <j...@jonorossi.com> wrote:
> I was wondering how other people did this too.
>
> In a project I have been working on, a specific entity has dynamic fields. I
> implemented fields as separate entities. The complexity comes when you think
> about what type of fields are needed. I had to create one for text,
> many-to-one (with another value entity), user, and various others specific
> ones to store links to other entities in the model. My model became even
> more complex when I implemented revisions on all those fields. All the data
> for these fields are stored in separate tables, I didn't want to make
> changes to the DB schema for the dynamic columns so heaps of separate
> entities and heaps of joins is the way it had to be made.
>
> If there is a nicer way of doing this or even a nicer way of being able to
> deal with all these classes in my calling code I'd love to hear what others
> have to say.
>
> On 10/16/07, Hamilton Verissimo <hamm...@castlestronghold.com> wrote:
>
>
>
>
>
> > What approach are you using? Root entities that allow extensions (ie
> > more columns) or everything is on an Entity table (kinda like jira
> > does)?
>
> > On 10/16/07, josh robb <josh_r...@fastmail.fm> wrote:
>
> > > On 10/16/07, Hamilton Verissimo <hamm...@castlestronghold.com> wrote:
>
> > > > Let me guess: SaaS?
>
> > > Welcome to my world.
>
> > > :(
>
> > > Wait until they want reports on these new fields is all I have to say
> > > (and take a look at CouchDB).
>
> > > j.
>
> > --
> > Cheers,
> > hamilton verissimo
> > hamm...@castlestronghold.com
Ayende Rahien
josh robb
On 10/17/07, Phil H <phas...@gmail.com> wrote:
> 2. XML. (Tokenized field) Store additional user defined data in an XML
> column (SQL Server 2005 or later)
I'm using this ATM for one (very small) aspect of my solution. I'm
really pleased with how it's working. I'm actually just using the XML
serializer to de/re-hydrate things into/outof the xml column. It's
pretty sweet.
j.
Colin Ramsay
josh robb
> You're doing this manually? It would be good if there were a way to
> "somehow" let ActiveRecord know that a property on a model should be
> serialised into a particular column.
For the moment - I'm happy having control over the serialization - but
I can see that this would be useful.
Colin Ramsay
josh robb
> This is slightly off topic (and probably exposing my lack of knowledge), but
> in your circumstance, what advantage do you get storing this data in a
> proper XML column as opposed to just a string? If you're just serialising in
> and out of it?
It just means that I can query it if I ever decide I want to. (I know
- YAGNI.... )
Ayende Rahien
Ayende Rahien
Expression.XPath() ?? //doesn't exist now, trivial to do