APPENDIX H - MySQL quick guide
This section wouldn't to be a MySQL guide replacement, but a summary of the main SQL commands and configuration options that can be useful to prepare databases for VEGA and VEGA ZZ.
To download and install MySQL, visit the Web site: http://www.mysql.com
If you intend to put to the databases large molecules as protein models, you must consider to increase the max_allowed_packet variable that defines the size of transfer packed. If the data sent to the server is larger than the value of this variable, the stored information will be truncated. For this reason, it will impossible to extract the big molecules from the database. To avoid this problem, you must increase the max_allowed_packet in permanent way changing the MySQL configuration file. More in detail, you can add the following line:
max_allowed_packet=16777216
before [mysqld] section to enlarge the packet size to 16 Mb. For Windows operating systems, the configuration file is placed in the MySQL installation directory (usually C:\Program Files\MySQL\MySQL Server X.Y) and is named my.ini. For Unix operating systems and MacOS, it's placed in the /etc or /etc/mysql directory and is named my.cnf. Please remember to restart MySQL when you change its configuration file.
VEGA and VEGA ZZ uses the standard ODBC interface to communicate with MySQL server and, for this reason, you must install the MySQL ODBC driver (MySQL Connectors) and eventually the ODBC layer (unixODBC). Windows doesn't need unixODBC, because the layer is integrated in the operating system.
To connect to a remote database, you must create an ODBC file data source (.dsn file) which generic content can be:
[ODBC] DRIVER=MySQL ODBC 5.1 Driver UID=user_name PWD=password DFLT_BIGINT_BIND_STR=1 PORT=3306 DATABASE=database_name SERVER=server_address
in which:
Field | Description |
DRIVER |
ODBC driver. You must keep the string as shown in the example if you want to connect to MySQL by the 5.1 version of the driver. |
UID |
User name. It must be the same created in MySQL and must have the rights to access to the database to which you want to connect. For more details, look the next section. |
PWD | User password. |
PORT |
This is number of the TCP/IP communication port. If you don't change in the server configuration, keep the default value. |
DATABASE | Database name. Look the next section to retrieve the list of the databases or to create a new one. |
SERVER | IP or DNS name of the server. |
If you want to open the data sources in easy way by the context menu of the Database explorer window, put the file in the ...\VEGA ZZ\Data\Databases directory, keeping the .dsn extension.
Use the MySQL command line client to enter the SQL commands.
Creation of a new remote user:
CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';Example:
CREATE USER 'lab'@'%' IDENTIFIED BY 'my_pass';The creation of a new user could be required to limit the access to specific databases.
Deletion of a remote user:
DROP USER 'user_name'@'%';Example:
DROP USER 'lab'@'%';
Creation of a new database:
CREATE DATABASE database_name;Example:
CREATE DATABASE my_new_database;Remember that you don't need to create the tables, because VEGA makes it for you.
Deletion of a database:
DROP DATABASE database_name;Example:
DROP DATABASE my_new_database;Take care: with this command you loose all data contained in the database.
List of the databases:
SHOW DATABASES;Examples:
SHOW DATABASES; SHOW DATABASES LIKE 'my_%';As in the last example, this SQL command allows the pattern matching.
Set the default database:
USE database_name;Example:
USE my_new_database; SELECT * FROM molecules;This command sets the default database for the next commands.
Access privileges to a database:
GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'%' WITH GRANT OPTION;Example:
GRANT ALL PRIVILEGES ON my_new_database.* TO 'lab'@'%' WITH GRANT OPTION;Remember to set the access privileges every time that you create a new database.
List of the tables of the current database:
SHOW TABLES;Example:
SHOW TABLES;
Add a new column:
ALTER TABLE table ADD COLUMN new_column data_type [AFTER column_name];Example:
ALTER TABLE molecules ADD COLUMN Activity REAL AFTER Volume;Add the Activity column to molecules table after Volume column as REAL data type. The most common data types are: CHAR(n) (fixed length character string with the n size), DATE (date), INT (integer number), REAL or FLOAT (real number), VARCHAR(n) (variable length character string with the n max. size), TIME (time).
Deletion of a column:
ALTER TABLE table DROP column_name;Example:
ALTER TABLE molecules DROP Activity;Take care to not remove columns made by VEGA, otherwise, when you add new molecules to the database, an error message will be showed.
List of the columns:
SHOW COLUMNS FROM table;Example:
SHOW COLUMNS FROM molecules;
Show system variables:
SHOW [GLOBAL] VARIABLES;Example:
SHOW VARIABLES;