Save non-searchable unstructured data/settings in DB

[dhoomm]

Many times we need to save user editable options in DB. We would have to create a table to save a single record. Instead, this is what i use at the moment.

DB:

CREATE TABLE options (
	name VARCHAR(20) NOT NULL,
	value TEXT NOT NULL,
	PRIMARY KEY (name)
)

Create a base class.

class OptionRecord extends \yii\base\Model
{
    public static function optionName()
    {
        return false;
    }
    public static function tableName()
    {
        return 'options';
    }
    public static function find()
    {
        $model = new static();
        $value = (new Query())->select(['value'])->from(static::tableName())->where(['name' => static::optionName()])->scalar();
        if ($value) {
            $values = unserialize($value);
            foreach ($model->getAttributes() as $key => $value) {
                if (isset($values[$key])) {
                    $model->$key = $values[$key];
                }
            }
        }
        return $model;
    }
    public function save($runValidation = true, $attributeNames = null)
    {
        if ($runValidation && !$this->validate($attributeNames)) {
            return false;
        }
        $sql = 'INSERT INTO `' . static::tableName() . '` (`name`, `value`) VALUES (:name,:value) ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);';
        return \Yii::$app->getDb()->createCommand($sql, [':name' => static::optionName(), ':value' => serialize($this->getAttributes())])->execute();
    }
}

Useage: Create a new Class, e.g.

class SmsSetup extends OptionRecord
{
    public $api;
    public $sender;
    public function rules()
    {
        return [
          [['api', 'sender'], 'required'],
          [['api', 'sender'], 'string', 'max' => 10]
        ];
    }
    public static function optionName()
    {
        return 'sms_setup';
    }
}

Now this can be used in controller as:

    public function actionUpdate()
    {
      $model = SmsSetup::find();
       if ($model->load(Yii::$app->request->post()) && $model->save()) 
       {
         //...
       }
    }
    public function actionDoSomething()
    {
        $settings = SmsSetup::find();
        \Yii::$app->smsService->send($settings->api, $settings->sender, 'SMS Content');
      //...
    }

Of course this won't work for wordpress type requirement where multiple options need to be loaded on each request, enable autoload, etc. Expecting suggestions...

[samdark]

Recently almost all databases started to support JSON including searching. Should be better than serialize, unserialize. Another way is normalized version of it called EAV.

[dhoomm]

Thanks for the info about JSON support even in relational databases. My vendor has old version of MySql DB, will use this for now...

[thiagotalma]

@samdark, this is certainly a good recipe. A lot of system runs on old databases but they are still in full development.

Therefore, you should accept the colleague's suggestion with praise. Be careful not to prune the suggestions based solely on your personal opinion. The world is much more complex than that 😉

[samdark]

I'm not declining it. Just mentioned that there could be better ways doing that.

[samdark]

https://github.com/yii2tech/ar-dynattribute