How to update the CCDB Hydrographic Database in MySQL
In addition to ERDDAP, SIO-CalCOFI has a smaller, queryable, online, hydrographic database. This query form includes bottle-related data only and resides at sio-calcofi.ucsd.edu/db/login.php. These instructions were composed after multiple attempts to update the large mySQL database table with new data.
- Create a new CCDB data table containing only newer final data (last update included 201301). The CCDB queriable online database is a distillation of the cast & bottle tables of the CalCOFI Hydrographic Database (webpage). Refer to the query form or CCDB table in the complete database for data columns. In the MSAccess hydrographic database there is a make-table query, !MT-CCDB. In Design View, edit the Cruise criteria to extract the latest data only. The query will create a new table called CCDB so rename this table CCDB???? where ???? are the start & end years eg 4956 for 1949-1956 (or any unique label). Add the month if necessary ie CCDB1304, for a single cruise or yearmonth, CCDB 13041311, for multiple cruises. This is the table you will be using next.
- Create a .sql dump MySQL file of this CCDB new table. Using MSAccess to MySQL (free Windows utility by Bullzip), connect to the MSAccess hydrographic database. Select the new CCDB???? table then 'sql dump' it to CCDB????.sql. The sql dump file cannot be larger than 100mb. If it is larger then split it up using the make-table (!MT-CCDB) query multiple times with multiple Cruise criteria. For the entire database, I used: 194901-195612, 195701-196912, 197001-198912, & 199001-present. Each file was ~200,000 records & 60mb.
- Login into phpMyAdmin on sio-calcofi.ucsd.edu. PHPMyAdmin is used (http://sio-calcofi.ucsd.edu/phpmyadmin/) to manage the online database. Select the CCDB database then Import. Using Choose File, select the CCDB????.sql file generated in step 2 then click GO. This should import the new data into a new table - it is important it be a new table (something other than CCDB) otherwise the original data table will be replaced.
Copy the new table into CCDB (main data table). Select the new table in ccdb then select Operations from the phpMyAdmin tabs. Using Copy table to (database.table); change the CCDB???
in the right box to CCDB (both boxes read ccdb & CCDB). Select Data only then click GO - I've also clicked Add AUTO_INCREMENT value but that does not seem to do anything since the primary index of CCDB is Btl_Cnt.
- If all went well, that's it. The new row count will appear when you select CCDB. Currently, with 201301 data added, the row count is 835,407.