Details of how to convert the Paradox Tables to mySQL files are as follows. You can download an excel spreadsheet (Miomapconvert.xls) that traces all of the changes in table names, field names, field types, and other changes from Paradox tables to mySQL tables. Each sheet refers to a mySQL table.]
These instructions are tailored to transferring the original MIOMAP Paradox files from the Barnosky lab into mySQL files on the BNHM server. They are provided as a guide to how the process works, should any users have similar Paradox files (e.g. a copy of the FAUNMAP database) that need conversion to mySQL.
This document was authored by Brian P. Kraatz.
1. Checking and altering the structure of the Paradox tables before exporting to text files.
Paradox tables used for data entry (i.e. Marc¹s tables) should be obtained. These should be placed in ³Marc¹s Paradox Data² folder. The following six tables should exist:
The following changes should be made to the tables. Revised tables should be placed in the ³Paradox Export Ready² folder.
Two things need to occur with this table. Only fields Bibnum, Author, and Year are needed for the mySQL table. Machine Number and Reference Number can be discarded. Also, there are repetitions of records in the paradox table (i.e. repetitions of Bibnums), but only one of each Bibnum can be imported into mySQL, duplicates must be removed. One query will do all of this.
-Create a new query in Paradox for the Refer table.
-Check only the fields Bibnum, Author, and year.
This will return a new answer table that only has the three fields that were selected and in which duplicate Bibnums have been removed. Save this table to the ³Paradox Export Ready² folder as ³Refer.² Note that this must be done via an export and not a ³Save As² command. Be sure to export it as a Paradox table.
Locality and AgeDeposit
These two Paradox tables must be joined, as well as one new field added to the subsequent table. Below is a list of all the fields that should be present in the mySQL table Locality, with Paradox fields from Locality bolded and fields from AgeDeposit italicized. LocSeq will be a new field.
LocSeq new field
The paradox tables should be combined by conducting a query on the two tables (Locality and AgeDeposit) in paradox.
-Create a new Paradox query
-Select all fields in Locality, and all fields except for Machine Number and Analysis Unit in AgeDeposity.
-Place a join on Machine Number between both tables, and place another join between Analysis Unit between both tables
-Export Answer table as Locality in the ³Paradox Export Ready² folder. Be sure to export it as a Paradox table.
Next check to be sure that the field order in the new Locality table is the same as listed above. Field order can be changed in Paradox through the Restructure Table window. Also add a field named LocSeq at the end of the Paradox table with a default value of ³0².
Just add two fields to the end of the Paradox table, FaunalSeq and LocSeq, in that order. Set the default value as ³0². Be sure the fields are in the correct mySQL order, and export to ³Paradox Export Ready² folder as Faunal. Also be sure to save the table as ³Faunal² and not ³FaunalComment.²
Just add two fields to the end of the Paradox table, AbSeq and LocSeq, in that order. Set the default value as ³0². Be sure the fields are in the correct mySQL order, and export to ³Paradox Export Ready² folder as Faunal.
Just add two fields to the end of the Paradox table, ComSeq and FaunalSeq, in that order. Set the default value as ³0². Be sure the fields are in the correct mySQL order, and export to ³Paradox Export Ready² folder as Faunal. This new table should be called ³Comment² as that is what it will be called in mySQL.
2. Exporting data to text files from Paradox
All export ready Paradox tables should now be in the ³Paradox Export Ready² folder.
All tables will be exported through paradox with the following attributes:
-Fields Separated By |¹
-Fields Delimited By Nothing¹
-Fields Delimited Allfields¹
-Character set: ANSI¹
Export all paradox tables as .txt files; name them the same as the Paradox table name (include .txt in filename when naming) and export to ³Exported Text² folder.
Next, all the blank values in the text files need to be changed to ³NULL² so that they are properly read when uploaded into mySQL. If blank values are left blank, mySQL will change some of these to 0.¹
To do this in Text Pad use the replace function. Replace all ||¹ with |\N|¹.
IMPORTANT NOTE: If two blanks are next to each other, the above replace function in Text Pad will on change the first ||¹ with |\N|¹. Just repeat the replace function until Text Pad can not find and more literal ||¹
Sometimes an extra character is placed after the last record in the .txt file. Delete if present.
Ready for upload.
3. Uploading data into from text files
First, upload the new text files into the miopmap_web folder on the BNHM server, the old ones should be replaced.
If one table is updated, all other tables should also be updated because several fields (e.g. LocSeq, FaunalSeq) are auto-incremented by mySQL as data are brought in from text files. That is, the primary keys are auto-incremented. These fields are eventually placed in other tables as foreign keys (see below, inserting foreign key sequence numbers), so be sure to clear data from all tables and then reload data for all tables to insure foreign keys match the corresponding values in related records.
Drop all data from tables in mySQL with the following syntax:
Delete from <tablename>
Once data is cleared from existing mySQL tables, reset the auto-increment count by using the following syntax:
alter table <tablename> auto_increment = 1
Import data using the following syntax:
Load data infile /export/home/miomap_web/<tablename>.txt¹ into table <tablename> fields terminated by |¹
4. Insert Foreign Key Sequence Numbers
Once all the data is loaded, values for various foreign keys must be inserted into some tables.
First, LocSeq must be inserted into Faunal from Locality. The following PHP scripts should do this, but do not keep these scripts in the web folder so someone else can run them at another time.
Inserting LocSeq into Faunal:
InsertLocSeqFaunal.php (found in the Insert Sequence Numbers PHP¹ folder)
This will take a while, be patient. Even like a half an hour. Go get coffee.
Inserting FaunalSeq into Comment:
Ditto, but not as long.
Inserting LocSeq into Absolute
This one takes a while.