[Cod-bugs] COD - setting up mysql database for a local COD copy

[Saulius Gražulis]

Dear Pavol,

thank you for you e-mail.

On 2018-01-27 00:59, Pavol Juhas wrote:
> I hope all is well, we met a while ago at a software demo
> session at the IUCr meeting in Montreal.
> 
> I am trying to setup a copy of the COD database on my
> iMac desktop with a local mysql server.  I have downloaded
> the CIF files from the cod-cifs-mysql.tgz tarball and then updated
> them with the rsync command as described at
> http://wiki.crystallography.net/howtoobtaincod.

Great!

Another way would be SVN checkout, it could make updates even easier :)

> The files seem to include a database dump in
> mysql/data.txt file, but I am a bit at loss how to
> use it to run a mysql server instance.  I was
> not able to find instructions at the COD wiki or
> within the COD file tree.

Well, I'm afraid we do not have instructions how to set up the SQL
database itself – given that there are some 5-6 popular platforms and
similar amount of mainstream SQL implementations, we have no chance to
cover all combinations. Most SQL engines are properly documented, so I
would refer you to the corresponding OS+SQL documentation.

I personally run Debian-based GNU/Linux systems (Ubuntu, Mint or Debian
proper) and MySQL or SQLite engines (https://dev.mysql.com/doc/,
.https://sqlite.org/docs.html). On these combinations, and from the
command line, getting COD into a database is easy:

For MySQL:

sh$ sudo apt-get install mysql-client mysql-server
# Go to the directory where you have unpacked cod-cifs-mysql.tgz:
sh$ cd mysql
sh$ mysql -u root -p
mysql> create database cod default character set utf8;
mysql> use cod;
mysql> SOURCE ./data.sql;
mysql> ALTER TABLE data DISABLE KEYS;
mysql> LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE data CHARACTER SET
utf8 FIELDS TERMINATED BY '\t';
mysql> ALTER TABLE data ENABLE KEYS;
mysql> quit

# Be patient, 'ENABLE KEYS' may take a while :)
# Instead of loading the MySQL database "by hand", you can use a
# pre-packed script from the mysql/ directory; instead of running
# commands in mysql> shell please run the following:
sh$ mysql -u root -p cod -e 'create database cod default character set utf8'
sh$ ./cod-load-mysql-dump.sh

# You can now query the COD:
sh$ mysql -u root -p cod -e 'select count(*) from data'

and so on. You may want to create use cod_reader at localhost without a
password with just SELECT privilege
(https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html) for
querying the database without a password, and possibly a
password-protected account for yourself for COD administration and
updates. Other COD tables can be loaded in a similar way.

For SQLite:

sh$ sudo apt-get install sqlite3 libsql-translator-perl
# Go to the directory where you have unpacked cod-cifs-mysql.tgz:

sqlt -f MySQL -t SQLite data.sql | sqlite3 cod.db
perl -pe 's/"/\\"/g; s/\\\n$/\\n/' data.txt > data.txt.sqlite
sqlite3 -separator "$(echo -e "\t")" cod.db '.import data.txt.sqlite data'

# The database which is created is in the file 'cod.db'.
# You can now query it:
sqlite3 cod.db 'select count(*) from data'

Please note that MySQL and SQLite3 have slightly different escaping
conventions, so the actual content of text fields in the 'cod.db' might
somewhat differ from their MySQL counterparts.

SQLite2 database "lives" in a file, can be copied and backed up using
regular filesystem tools, and is easier to set up safely (file access
permissions also regulate the database). MySQL, on the other hand, could
be faster and is accessible simultaneously from multiple computers. See
which tool suits better your needs.

I have put this text to our COD Wiki at
http://wiki.crystallography.net/creatingSQLdatabase/

Hope this helps.

Regards,
Saulius

-- 
Dr. Saulius Gražulis
Vilnius University Institute of Biotechnology, Saulėtekio al. 7
LT-10257 Vilnius, Lietuva (Lithuania)
fax: (+370-5)-2234367 / phone (office): (+370-5)-2234353
mobile: (+370-684)-49802, (+370-614)-36366