WP7CoLinSQLite

= Building the Catalogue of Life in SQLite =

Some notes for creating and using the Catalogue of Life’s database.

From experience it is much easier if you create a temporary folder to build the database rather than try and build it in situ.

Unzip 2011AC_24October.zip (file extracted from Catalogue of Life CD) into a new folder, C:\ViBRANT\CatalogueOfLife.

To avoid the ‘fun’ attempting to create an SQLite version of the Catalogue of Life in this folder owing to numerous path issues, create a temporary folder C:\tables.

Into C:\tables, copy: - all 34 .txt files in C:\ViBRANT\CatalogueOfLife\tables - the script file SQLite_import.sql that is in C:\ViBRANT\CatalogueOfLife\tables\scripts - a copy of sqlite3.exe (download from http://www.sqlite.org/)

IMPORTANT You must edit source_database.txt, line 17 and remove the extra line feeds. I simply deleted them and the associated ‘\’ running the lines into one. You will see that each line begins with an incrementing number, so it is easy to work out when to stop because you are back to a valid line.

Then open a command prompt in C:\tables and enter this command:

sqlite3 icol2011ac24Oct.sqlite

to create the database and open an sqlite3 session using it. Note, the database name is the one suggested by Catalogue of Life. The name will change as the version of the data changes. You do not have to use the Catalogue of Life's suggested name.

Then enter this command to load the tables:

.read tables/SQLite_import.sql

followed by .exit and exit to leave sqlite3 and close the command window.

Then move the database to a suitable folder such as C:\ViBRANT\CatalogueOfLife and delete the temporary folder C:\tables.

To explore the database install the Firefox add-on SQLite Manager.