Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Help newb MySQL user
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Other Things Gentoo
View previous topic :: View next topic  
Author Message
brent_weaver
Guru
Guru


Joined: 01 Jul 2004
Posts: 505
Location: Burlington, VT

PostPosted: Sat Dec 29, 2012 2:40 pm    Post subject: Help newb MySQL user Reply with quote

Hey there! I am totally new to MySQL and am looking to store file sizes in the database. So like I would have a file /db/live/CACHE.DAT and I track it's growth and would like to store this in a database. There will be multiple servers I will be storing in this mysql db. I am a TOTAL newbie at and have no idea where to start. This really a design question on the most efficient way to store this data.

Any advice is welcome. Thanks!
_________________
Brent Weaver
Back to top
View user's profile Send private message
lxg
Veteran
Veteran


Joined: 12 Nov 2005
Posts: 1019
Location: Aachen, Germany

PostPosted: Sat Dec 29, 2012 3:29 pm    Post subject: Reply with quote

Why do you need a relational database for this? If you just want to create a list of entries, create a cronjob which runs stat -c%s /db/live/CACHE.DAT >> filesize.log on a regular basis.

If you really want to use MySQL, you'll need a client application or script which retrieves the file size, connects to the DB and inserts a new file size entry. This can be done in almost any programming language … Bash, PHP, Python, Java, C – you name it.

Could you elaborate a bit on your use case, especially what you want to do with the sizes list later?
_________________
lxg.de – codebits and tech talk
Back to top
View user's profile Send private message
brent_weaver
Guru
Guru


Joined: 01 Jul 2004
Posts: 505
Location: Burlington, VT

PostPosted: Sat Dec 29, 2012 4:06 pm    Post subject: Reply with quote

lxg - Thank you for your valuable time!

Basically I want to use MySQL so we can query the db for date ranges to see the growth pattern. I am currently storing this data is a flat text file but I would like to make it more robust in functionality.

Hope this is helpful!
_________________
Brent Weaver
Back to top
View user's profile Send private message
lxg
Veteran
Veteran


Joined: 12 Nov 2005
Posts: 1019
Location: Aachen, Germany

PostPosted: Sat Dec 29, 2012 11:55 pm    Post subject: Reply with quote

I see.

I think a schema with one table is currently enough. The table will represent an entity, which you can think of as an object in OOP. If you need different types of objects that have a certain relation towards each other, I suggest you read about entity relations and schema normalization in general.

Without knowing to much about what you're doing, I would guess that one record (or object or entity, we can use them synonymously for now) in your table would have the following properties:
- a unique ID to reference it,
- a timestamp
- the file size

This gives us the following schema for the table:

Code:

CREATE TABLE IF NOT EXISTS `size` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `size` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;


I expect that you know the basics about CRUD (create, read, update, delete); if not, you should get a book on SQL basics or read (My)SQL docs online.

The above schema is set up in a way that you only need to provide the size in the INSERT statement, id and created are set automatically.

If you care about performance, and at some point you will, you should think about indexing. For example, if you're often performing SQL queries which search certain records by the date, you should index the created column.

For programmatically querying the database, you may (as already mentioned) pick the programming language of your preference and write up a little client.
_________________
lxg.de – codebits and tech talk
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Other Things Gentoo All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum