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