[wp-hackers] How do you store multiple plugin options

[Potkanski, Jason]

On 3/24/10 1:42 PM, "Otto" <otto at ottodestruct.com> wrote:

> On Wed, Mar 24, 2010 at 12:56 PM, Potkanski, Jason
> <jpotkanski at tribune.com> wrote:
>> Storing database variables in an array should be considered a bad practice.
>> Doing this is as an convenient  hack that shortens plugin design but has
>> significant consequences.
>
> Many people disagree with you on this. I'm one of those people.
>
> Serializing your options is not a "hack". It's the preferred method. Why?
> - Speed: Pulling one row out of the database is faster than pulling
> dozens of them
> - Simplicity: Having my options all together makes them easier to
> reference instead of having dozens of get_options calls.
> - Ease of design: Making settings pages which all use similar options
> is easier this way. No need for dozens of "register_setting" calls,
> dozens of slow get_options calls hitting the memory cache or the
> database, reduced numbers of SQL queries, etc.

I'll sum this as this method gives plugin developers:
Fast Development Speed
Lower Network Traffic (via smaller DB call)
versus
More Memory Use (unneeded options potentially being loaded)
Increased Server CPU time (calls to serialize, deserialize and the preg_match to autodetect)
Database Denormalized. (RDBMS nightmare)



>> Serialized data in the blog options table denormalizes the database.
>> ReCaptcha uses one blog option setting called reCaptcha. If I want to update
>> the keys across all my blogs in a wordpress MU install, I can not do it via
>> SQL alone, I have to write a php plugin to tinker with the values. (Yes, I
>> know reCaptcha has MU installation option and configuration file). Where I
>> could have help from a DBA to fix something, I now need a developer.
>
> MySQL has search and replace capabilities which work very nicely,
> actually, and they can search and replace inside serialized data just
> as easily as they can in non-serialized data. You don't need a
> developer to adjust things. Although it does strike me as rather
> trivial to write php code which basically says "include wp-load.php;
> $o = get_option('whatever'); $o['whatever'] = new value;
> update_option('whatever',$o);"

I really want to see this MySQL easy regex search and replace inside serialized data. Easy and regex never go hand in hand.

You didn't address the database denormalization issue. That's a severe one. If plugin developers are not going to normalize their data, why use the blog options table at all? Use a flat file.

>> Denormalization also means I couldn't have any systems integration or
>> monitoring on database values without custom plugins.
>
> They're stored as strings. What's so hard about monitoring strings?

Lets say I want to write a Nagios monitor of a certain DB value of a plugin. In this case I want to monitor the roles of a blog (stored as serialized data) for security reasons. I couldn't just alter a Nagios standard function that can make a simple select call, I would have to write something to pull the data , deserialize it and make sure it is all correct.

>> Other programming languages such as Java or Ruby don't have the same
>> serialization functions PHP does, so any other development work that may need
>> access to Wordpress data is restricted to PHP.
>
> Java has tons and tons of serialization stuff, actually. I suppose
> you've never seen "implements Serializable"?
>
> Admittedly, the syntax is not the same between the two, in which case
> you could either implement a parser (google will find one for you
> quickly), or you could switch the code to use a common interchange
> format. Probably the easiest way would be to use JSON. Or perhaps XML,
> if you swing that way.

I've written stuff in java that implements serializable. You use this technique with Tomcat to make all server variables of an application stay persistent when the server is restarted. Or when you have multiple tomcat instances and want to share session data properly. You don't use it to dump java objects to the DB.

>>
>> Correct practice should be to avoid serialized arrays where possible and use
>> proper prefixing in blog options to avoid collisions with other plugins.
>
> I disagree. Correct practice would be to store your options in a
> single array as much as possible and to let WordPress handle the
> serialization for you as necessary. Only separate options out when you
> have a different load case for them (such as options you only need in
> specific places and not every time, large options, etc).

I think we can have the best of both worlds. From the easy plugin design perspective, if you just throw an array at set_option or plugin option, it should be able to handle storing those options automatically. From my database normalization perspective, the set_option functions probably shouldn't use the serialize/deserialize methods if it can be avoided.

There are some further replies on this thread that will be interesting.

--

Jason Potkanski
Tribune Technology