Best way to store ONE field and one record in DB and is it worth doing so?

Yii 1.1 General Discussion
1

Hi there,

My current application whole configuration set is at final step converted into one long string (serialized - and unserialized upon reading) and currently I’m storing it into file. I would like to ask, if someone can propose the fastest, shortest or best pratice way to store this in DB, since I never before faced problem (if it is problem) of creating table with only one field for storing only one record. What would be the fastest way to do so? Write a model for such table? Or use direct SQL for retreving / updating configuration.

I would also ask, what do you think about the idea of storing such thing in a file? Is it save? Is it worth to change to DB?

Pros:

  • faster?

  • DB idepended - app can still work (or at least startup) if DB connection is down, won’t start at all, if configuration is stored in DB.

Cons:

  • can be accidentialy overwritten with sending all files through FTP (this is also true for SQLite, but I’m planning to use MySQL if I decide to move to DB storage),

  • can be overwritten, if two processes request access to this file in exactly the same time.

The last thing concerns me the most. Long time ago, when I have my very basic page written in very basic PHP, I stored my counter value in a file and I was constatly facing problem of its being zeroed. Someone told me to move to DB, which queries all access request into quene and won’t allow such situation. I abandoned whole project then (moved to WordPress-powered webpage) therefore I still hasn’t solve problem with table with one field and one records.

Thanks in advance for any answer or advices here.

Trejder

2

I would say, fastest way is:





function saveToFile($data)

{

    file_put_contents('config.php', 'return ' . var_export($data, true));

}




$config = include 'config.php';
3

Hi, mbi,

Thanks for your reply, but I’m getting feeling that I either not read my post or you misunderstood it! :]

I do have writing configuration to file already implemented! I’m looking for fast way to store (and retrieve) the very same, serialised, configuration string in MySQL DB.

Cheers,

Trejder

4

So, what I do is to use redis as cache and set it to cache, which is the same than than db but faster

File has the speed problem and also like you mentioned concurrent write

5

I always created a table config with some fields that are the configuration parameters.

Is a table with one row and many fields, but I don’t think is a bad pattern.

What I don’t like is to save as string a serialized php array in db, I’d rather use many field for each param.

6

Well… your idea is worth exploring as replacement for a standard cache component, but I don’t see, how could I use it in my situation? Configuration file (just about twenty params serialized into one, long string) is just to small and changes to often, to use it against any caching system. Beside… look… I’m asking do I have to create model, because this seems to be a to much work for me (I’m lazy! :]) and you’re proposing a solution that reuqires setting up the whole cache server etc.! :] :] :]

Why did you wrote “File has the speed problem and also like you mentioned concurrent write”? I agree, that concurrent writing can be a problem, but speed. Either I’m missing you or you are saying that file access (configuration storing in a file) is slower than in DB? Wow, I personally never found any DB system that would be faster than direct file write.

7

I do exactly, what you don’t like! :] Because this is just simple and fast. What wrong do you see in serialization / deserialization? There is a much more work with setting up DB and creating model or writing SQLs for serving in solution you wrote. I can agree that in lager projects, this is the only reasonable way and I’m going to implement it in a big project I’m working currently. But for a small website, where you store like five colors and seven labels, do you really think, it is required and worth to use a DB?

8

To setup the redis system takes like 15min tops, and then just let it running forever ( I dont even remember how to setup it again … It is running in the backgrond for a long time, but I remember I had no problem and was fast )

Also you can benefit a lot in the rest of your application

The serialized array is what I do for small projects, and save to db / cache, I think I have a class for that here if interests you, very simple, with two methods, load and save, static and that can be used to retrieve singular params(__get and __set) no model need, use Yii::app()->getDb()->createCommand($sql)->query();

I use a separeted table for that, but you could use a field in an existent table

It depends the way you use, can be much slower that db, but for your case speed wont be a problem

9

Thank you very much, but I’ve already developed my own one. I was only asking, if switching file medium to a db medium can lead into any problems and if there is any performance issue / sql magic, when writing to a table containing one column and one record - i.e. is it worth doing this via model or pure SQL approach is better? You answered most of my questions and for the last one I think that since this is only one column and one record, pure SQL will be much much faster than model.