Poking around in the database

https://ieadb.uoregon.edu/phpmyadmin/
Username: rmitchel
Password: 3594beeskeR
Database names map as follows:
iea1 – Live
iea2 – Temporary
ieabackup – Backup
ieasys – System tables
information_schema – Ignore
rmitchel_db – Drupal site

Basic process for uploading from Excel to IEA server at http://ieadb.uoregon.edu/

Go to IEA database server at: http://ieadb.uoregon.edu/
Go to Databases tab and click Live –> Backup to ensure there is a working copy of the database from which to resurrect if the upload doesn’t work
Save all necessary tabs from MEAD108.xls and MEAD108-membership.xls as UNICODE files.
Upload all UNICODE files from Step 2 to appropriate ieadb folder
Upload all agreement text files as UNICODE to appropriate ieadb folder
Upload all performance indicator (pi) files as UNICODE to appropriate ieadb folder
Go to Import tab and press go — Takes about 2 hours to complete!
Go to Import Treaty Text tab and press go — Takes about 1 hour to complete
Go to Import PI (performance indicator) tab and press go. Takes about 2 hours to run.. An Email will be sent when complete!
Go to Full Treaty Status tab and press go. Takes about 2 hours to run.. An Email will be sent when complete!
Go to Update PI tab and press go. This takes about 1 hour to run.
Go to Databases tab and click Temporary –> Live to have the newly created database go Live
Confirm that it works — check various links to make sure
Don’t forget, at the end, to do the following:
- Generate the full dataset file by clicking: http://iea.uoregon.edu/page.php?query=download_IEA_dataset
- After downloading, make sure to upload it via FTP to: full_database directory: http://iea.uoregon.edu/full_database/ as IEA_Dataset.xlsx
- Generate all the static versions of the treaty text files by going via PUTTY and running php make_hard_html_files.php — This will use the Treaty Text table to generate an indexed list of all the treaty files and save them in the iea.uoregon.edu/texts folder where Google can access them. You can then confirm it worked at: http://iea.uoregon.edu/text_index.htm

Import PI Documentation

Files: data/pi/
All files stored as utf16 from excel in their respective folders. You have to run the import twice for new files to convert the files first and then to import them.

Country Lookup: The preferred country is pulled from countries table in the iea2 database. The table is generated when the first import tab is run.

Meta Data:
Folder – data/pi/meta/
File structure – The first column contains meta field names and the second column contains values.
Data – Meta is store in pi_meta table in the iea2 database which is the temp database. The data is stored as filename, field_name, value. If a meta file for the below files is not provided you will see a warning in the log and in the meta file db there will also be a record.

Wide Files:
FORMAT: source — indicator — double — 1980 — 1981 — 1982 — …..
Folder – data/pi/wide/
File structure
— Columns to the LEFT of the country column are field names that will be concatenated to form the indicator. There can only be 8 columns of field names — after the eighth column the field names will be ignored. All spaces ( ), underscores (_), single quotes (‘) will be converted to hyphens (-) and concatenated with the filename e.g. Fish_Pacific,-Ant.
— The column containing country names MUST have datatype in the topmost cell: either double or text (else the file will be skipped during import).
— Country names in cells cannot be blank — if they are, those rows will be skipped.
— Columns to the RIGHT of the country column are YEARS as column labels with values in that column. Year column labels have to be between 1000 and 3000.

Long Files:
FORMAT: source — double — year — var1 — var2 — var3 — …..
Folder – data/pi/long/
File structure
— The column containing country names MUST have datatype in the topmost cell: either double or text (else the file will be skipped during import).
— Country names in cells cannot be blank — if they are, those rows will be skipped.
— The column after the country is years. Years have to be between 1000 and 3000 — if they are not, those rows will be skipped.
— Rest of column names (first row) will have INDICATORS as column labels with values in that column.

EAV Files:
FORMAT: source — indicator — double — year — data
Folder – data/pi/eav/
File structure
— First several columns contain indicator labels.
— The column containing country names MUST have datatype in the topmost cell: either double or text (else the file will be skipped during import).
— Country names in cells cannot be blank — if they are, those rows will be skipped.
— Third column will have year. Years have to be between 1000 and 3000 — if they are not, those rows will be skipped.
— Fourth column contains values.

Data:
Wide, Long, and EAV files are stored in pi_eav table in the iea2 database. The data is stored as indicator, country, year, value, datatype. The field names are stored in the pi_fields tables in the iea2 database. The data is stored as indicator, meta_file, indicator1, indicator2, …, indicator8.