Install mysql

Install mysql client only

- download the latest version of mysql community server: dev.mysql.com/downloads/mysql/ (msi installer)

- launch the installer and select custom install

-  don't select "MySQL Server" as feature to be installed. You may want to use the c api feature if you develop in C/C++.

- At the end of installation you will be asked if you want to configure the server. Do not tick "configure server now".

- add the mysql bin path to your system path so you can call the mysql client from the cmd window easily

Install mysql client and server

- download the latest version of mysql community server: dev.mysql.com/downloads/mysql/ (msi installer)

- launch the installer and select custom install

-  select "MySQL Server" as feature to be installed. You may want to use the c api feature if you develop in C/C++.

- At the end of installation you will be asked if you want to configure the server. Tick "configure server now".

- If you want your machine to be a mysql server tick "configure the mysql server now" before finishing installation. Select detailed configuration. Choose default options but don't forget to tick "add port exception" and "allow root access from remote".

Also tick "include bin directory in windows PATH" so you can launch the mysql client easily

Connect to the server

Once the mysql client is installed go to cmd and type: mysql -u root -h lifmetpc34.epfl.ch -p and type the password which is: 98765 to connect to lifmetpc34

If you installed mysql server and want to connect to localhost server just type: mysql -u root -p and then type your password

If you prefer a GUI to the cmd download dev.mysql.com/downloads/workbench/5.2.html

You are now connected to the mysql server! Type use adni; to enter the adni database.

Organisation of the adni database

niifilesonlifmetstor1: each entry corresponds to a file in lifmetstor1. Each entry has a fileID, fileName, filePath, LONIUID and SUID

adcncleanlistadnibaselinedata: contains the detailed information on a

 PID
 RID
 DX
 AGE
 PTGENDER
 FIELDSTRENGTH
 MRIDATE
 LONI_STUDY_UID
 LONIUID
 RANK
 MANUFACTURER
 MODEL
 COIL

collections: it is possible to create collections to characterize a group of data and be able to retrieve the corresponding files easily. A collection has an ID (a number), a name and description text.

filesincollections: this table makes the link between a fileID and a collection. Each entry has a fileID and a collectionID

Make a query

A query will enable you to retrieve information from the database using the SQL language. Here are basic examples. Mysql reference doc: dev.mysql.com/doc/

Ex: find the full path of a file knowing only the file name

select concat(absolutePathName,'/',fileBaseName) from niifilesonlifmetstor1 where fileBaseName='name_of_my_file_of_interest.nii';

Ex: find the files from the niifilesonlifmetstor1 table that are described in adcncleanlistadnibaselinedata as females

select niifilesonlifmetstor1.fileBaseName from niifilesonlifmetstor1 inner join adcncleanlistadnibaselinedata on niifilesonlifmetstor1.LONIUID=adcncleanlistadnibaselinedata.LONIUID and adcncleanlistadnibaselinedata.ptgender='female';