Is it inappropriate to use a BLOB in this way?

[Chris D]

I'm working on something that requires a reasonably small amount of data to be accessed from the xf_user table.

Currently I am doing this via a blob which is storing basically some access tokens, and some user settings related to my add-on.

What are the good and bad points of using a single blob in this way?

It seems to be working quite well for me at the moment but I don't want to do something that's completely out of the ordinary.

 

[Mr. Goodie2Shoes]

using blob is ordinary
XenForo uses blob to store options, user authentication and other 'small' fields, so I don't think that's an issue here.

 

[Chris D]

I know they're used a lot in XenForo generally.

I was just worried.

For me, it'll be an array like this:

$blob = array(
	'twitter' => array(
		'oauth_token' => 'token_string',
		'oauth_secret' => 'secret_string',
		'twitter_option_1' => true,
		'twitter_option_2' => 'value'
	),
	'facebook => array(
		'token' => 'token_string',
		'secret' => 'secret_string'
	)
);

Which will then be serialized and unserialized when required.

The alternative, of course, is to have a new column for each item. I just don't know what's best.

 

[Mr. Goodie2Shoes]

Different rows will actually use more resources
If you are worried, change it to mediumblob or text

 

[digitalpoint]

A blob isn't necessary, but it will work just fine... Blob stores things binarily intact. If the data you are storing is always text, it's fine to use a blob or text field. As far as adding multiple columns, if it's nothing you are ever directly searching/sorting by, it's not needed.

Side note: json_encode/decode takes less storage space and executes about twice as fast as serialize.

 

[Chris D]

A blob isn't necessary, but it will work just fine... Blob stores things binarily intact. If the data you are storing is always text, it's fine to use a blob or text field. As far as adding multiple columns, if it's nothing you are ever directly searching/sorting by, it's not needed.

Side note: json_encode/decode takes less storage space and executes about twice as fast as serialize.

Thank you. Great advice.

On the side note - why didn't KAM use json_encode/decode over serialize/unserialize?

 

[digitalpoint]

Beats me... Maybe if they were rewriting it they would. It's also one of those things that people just got used to (json functions weren't added to PHP until 5.2.0... Serialize was always there).

 

[Despair]

On the side note - why didn't KAM use json_encode/decode over serialize/unserialize?

They did at least move over one thing to json way back: http://xenforo.com/community/threads/xenforo-1-0-0.13280/#post-173592

 

[Mr. Goodie2Shoes]

Side note: json_encode/decode takes less storage space and executes about twice as fast as serialize.

There are pros and cons for both methods... here's something: http://stackoverflow.com/questions/...-to-store-php-arrays-json-encode-vs-serialize