View previous topic :: View next topic |
Author |
Message |
brent_weaver Guru
Joined: 01 Jul 2004 Posts: 510 Location: Burlington, VT
|
Posted: Sat Dec 29, 2012 2:40 pm Post subject: Help newb MySQL user |
|
|
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 |
|
|
lxg Veteran
Joined: 12 Nov 2005 Posts: 1019 Location: Aachen, Germany
|
Posted: Sat Dec 29, 2012 3:29 pm Post subject: |
|
|
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 |
|
|
brent_weaver Guru
Joined: 01 Jul 2004 Posts: 510 Location: Burlington, VT
|
Posted: Sat Dec 29, 2012 4:06 pm Post subject: |
|
|
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 |
|
|
lxg Veteran
Joined: 12 Nov 2005 Posts: 1019 Location: Aachen, Germany
|
Posted: Sat Dec 29, 2012 11:55 pm Post subject: |
|
|
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 |
|
|
|
|
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
|
|