Sometimes you may need to store complicated data with non-fixed fields/data types in table. There are different ways for that but if we use symfony where YML is so native and good known – why dont store data in table as YML-formed value.
It’s pretty straightforward with using of Spyc class and its methods YAMLLoad, YAMLDump to convert YAML from/into array but some things worth mentioning:
YML usually contains more information that may fit simple varchar so you may want to use “longvarchar” type for storing this kind of info and then I faced with the problem that in order to access this value from $data object you may need to use additionally getContents method, e.g. $data_object->getValue()->getContents().
The other thing – dont forget to decode your text before printing it with YAMLLoad by calling html_entity_decode method, so it finally looks like to dump YAML into PHP array:
Spyc::YAMLLoad(html_entity_decode($data_object->getValue()->getContents()))
and to store text field as YAML well-formed object use this:
Spyc::YAMLDump($this->getRequestParameter(‘some_value’))
The other non-ordinary thing is that you may want to prepare fixture YML files for batch processing, in this case the problem you may expect is how to prepare YAML file which contains text field with break lines. I found the solution in sfSimpleForumPlugin (see using content field in this sample):
post_12:
topic_id: topic_7
content: |
If you see no alert, it means that your output escaping is turned on
user_id: tarzanman
That’s it.
2 replies on “use YML for storing data in db”
I sometimes put a blob in a table. This can be a quick way to store hierarchical data, or other things that don’t fit into the SQL model.
I’ve often used PHP serialization or JSON for this purpose.
A big problem with using blobs like this is that you lose the intelligence of the database. At some point you might need to do a query on one of the fields hidden in the blob. You could do this in one line of SQL if this was in it’s own column, but instead you have to write a program and do a full table scan.
Another problem with blobs is evolvability. It’s a pain to evolve your schemas in SQL, but SQL provides you the tools to do it. You may be able to cope if your data structures are loosely coupled, but the evolvability problem for OO databases is tough and unsolved.
A less drastic model is to create a table that lets you attach key/value pairs to rows in a table. You can still do ad-hoc SQL queries. This doesn’t work well for complex structured data, but is a good compromise in many cases.
[…] use YML for storing data in db […]