Rforge

The most flexible way to get data into R is from (semi-)colon separated files. So I am having my vast and growing collection of .csv and .txt research data extracted from databases, spreadsheets and other statistical software.

I decided to start collecting them into a MySQL database.

My first encounter with MySQL documented on the fly, following the official tutorial:

Installation

The MySQL database on my machine was installed together with an apache web server and PHP (the so calles LAMP = Linux + Apache + MySQL + PHP) with the tasksel command:
sudo tasksel install lamp-server

In order to login to the database open a shell and
sudo mysql --password

then enter the root-password and you are presented with the database command line
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.1.37-1ubuntu5 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

A first overview of what is there gives
SHOW DATABASES;
at the command line. In my case it gives
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

Basic commands

SQL commands are printed in capitals for readability though it is not necessary to use capitals (“use” works as well as “USE”)

• USE mysql; connects to the database ‘mysql’ and
• SELECT DATABASE(); shows which database is used right now (in case you forgot)
• SHOW TABLES; lists all tables in the connected database
• DESCRIBE sometable; shows the structure of “sometable”
• SELECT * FROM sometable; prints the table
• CREATE DATABASE MyNewDatabase; creates a database calles MyNewDatabase – case sensitive
• QUIT ends the MySQL session
• mysql -h hostname -u username -p MyNewDatabase connects a specific user directly to a specific database without the need to use the otherwise obligatory use command.

Easy login

Now that I created a new database I would like to avoid the fuzz while connecting to it. Up til now I have to use the root password to connect to the database.

The solution is to grant all user privileges to my user account without setting a password:
GRANT ALL ON MyNewDatabase.* TO 'rforge'@'localhost'; grants all rights on the new database MyNewDatabase to user rforge

In order to access the MyNewDatabase the only statement needed is:
mysql MyNewDatabase

Loading data

In order to fill MyNewDatabase with content, MySQL demands two not very straightforward steps:

1. create an empty table with a list of all variables and their specific variable type (like date, numeric, character)
   CREATE TABLE MyNewTable (SomeDate DATE, SomeString VARCHAR(20), SomeNumber DOUBLE);
2. load data from a tab-separated textfile into the empty table
   LOAD DATA LOCAL INFILE 'MyPathTo/Textfile.txt' INTO TABLE MyNewTable