Setting up an SQL database

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 one would refer to the corresponding OS+SQL documentation.

On COD servers we 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:

MySQL

The following commands should set up a working SQL database with the COD data table:

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@localhost without a password with just a 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.

SQLite

The commands below are for the SQLite3 database:

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.

SQLite3 database is stored in a file, thus it 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.