MIOMAP HOME                                                            BACK TO FGDC METADATA

 

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:

 

Refer

Locality

AgeDeposit

FaunalComment

Absolute

Synonymy

 

The following changes should be made to the tables. Revised tables should be placed in the ³Paradox Export Ready² folder.

 

Refer

 

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.

-Perform query.

 

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.

 

MachineNumber

AnalysisUnit 

SiteName     

AltName       

StateProv    

County       

SiteNum      

CodedBy      

DateCoded    

CodingUpdate 

Comment      

Prec        

Quad         

Quad_Size    

Township     

Latitude     

Longitude    

Altitude    

Repos        

LocBib

LatDec

LongDec       

RecMeth      

DepSyst      

DepEnv       

Facies       

Mixing      

Member       

Formation    

ArchPeriod   

ArchEpoch    

ArchAge      

ArchLMA      

ArchBib      

Period       

Epoch        

Age     

LMA    

Magnetochron 

MinAge       

MinAgeMeth   

MaxAge       

MaxAgeMeth   

Agent   

    

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

-Perform query.

-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².

 

FaunalComment

 

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.²

 

 

Absolute

 

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.

 

Synonymy

 

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Š.