Re: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
Re: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
- Subject: Re: Model/DB synchronization (was: Migrations (was: ERXSQLHelper))
- From: Samuel Pelletier <email@hidden>
- Date: Thu, 15 Jan 2015 08:49:52 -0500
Hi OC,
I had to deal with similar situation in the past for online product catalog and I used these two way of storing the data successfully :
1- Store additional attributes in a serialized map structure (json, serialized php array, serialized java objects or property list).
This allows to store anything that can be serialized and fit inside the blob or big text column. The price to pay is in search, you cannot search with the database or use index. This structure is the one to use when the data structure is completely custom from item to item. At this time, I would use a document based database like mongoDB with use this type of storage natively. With WO, you can use a document database for the dynamic documents and a relational database for the transactional datas. I would not recommend using a document database for transactional operations, they are not designed for that.
2- Store the additional attributes in an dedicated entity with a commun dictionary of attribute definitions. The attribute definition define the attribute display name, data type, unit of measure (if required). You may have a single list of definition or a list per item category for example. When the user want to add an attribute to an item, he select the attribute from a list and add it to the item. The form is dynamicaly constructed with correct display and data validation based on the attribute definition. Attribute values are stored in a small entity linked to an item and an attribute description. You can index the value columns and do fast search in the database. By adding some utility method to your Item entity, you can have a dictionary like behaviour for your additional attribute values.
Entities required:
AttributeDefinition <-->> ItemValue <<--> Item
----------------------- ---------------- -------------
id id id
name itemID name
type attributeDefinitionID ...
... intValue
decimalValue
textValue
For optimal queries, you will need to use the ERXExistsQualifier presented last year. I implemented this structure one time in WO to read data managed by a PHP app but few times in PHP for product catalog and dynamic forms like http://vizu.ca
Regards,
Samuel
Sample of utility methods for a dynamic form implementation (code is a unique never modified, even when a new version of the form is created identifier for a question):
public ClientQuestionReponse reponseForQuestionCode(String code) {
EOQualifier qualifier = ClientQuestionReponse.QUESTION.dot(Question.CODE).eq(code);
NSArray<ClientQuestionReponse> reponses = reponses(qualifier);
if (reponses.count() == 0) {
return null;
}
return reponses.objectAtIndex(0);
}
public String textReponseForQuestionCode(String code) {
ClientQuestionReponse reponse = reponseForQuestionCode(code);
if (reponse == null) {
return null;
}
return reponse.texte();
}
public BigDecimal nombreReponseForQuestionCode(String code) {
ClientQuestionReponse reponse = reponseForQuestionCode(code);
if (reponse == null) {
return null;
}
return reponse.nombre();
}
public NSTimestamp dateReponseForQuestionCode(String code) {
ClientQuestionReponse reponse = reponseForQuestionCode(code);
if (reponse == null) {
return null;
}
return reponse.date();
}
> Le 2015-01-13 à 18:55, Ramsey Gurley <email@hidden> a écrit :
>
>
> On Jan 13, 2015, at 8:26 AM, OC <email@hidden> wrote:
>>
>> Well the gist is that some of my EOs have (along with normal ones) a set of user-defined attributes: the user can open an appripriate editor and set up something like "my auction will, along with the attributes which the application itself defines, also three VARCHAR attributes named 'foo', 'bar', and 'bax'; they will have four DECIMAL attributes named so-and-so, etc etc.". These things are stored in the database, and the application then allows to use the dynamic attributes the very same way one can use static ones (i.e., display them in forms/tables, edit their contents, filter by them, etc.)
>>
>> Originally, I have solved this through a BLOB, which contains a serialized NSDictionary, which contains all the dynamic attributes by-name.
>>
>> That works reasonably well, but we bumped into grave efficiency problem filtering such tables: to fetch only items whose attribute 'foo' matches 'John*', we can't do SQL SELECT; instead we have to fetch the whole table, decode all the BLOBs, and filter by the results. That proved a show-stopper.
>>
>> Thus, now I have to implement all those dynamic attributes as table columns, so that I can (e.g.) use fetch qualifiers for them.
>>
>> Thanks and all the best,
>> OC
>
> So the plan is to have users executing SQL DDLs on the live production database?
>
> You may want to consider something like Postgresql with JSON datatypes
>
> http://www.pgcon.org/2014/schedule/attachments/328_9.4json.pdf
>
> Or if you just love (are stuck with) Frontbase, running an instance of CouchDB and passing data and queries to it via http. Either option seems much safer to me than having end users running alter table statements on the production database.
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
>
> This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden