ADASS Web Database How-to

How-to get your ADASS participants and program info into the ADASSWEB Database

The ADASSWEB database was last updated in 2005. The early metadata were obtained from parsing historic online participant lists and programs. In recent years, many ADASS sponsors have maintained their own registration database. You can help us keep this centralized database up to date by providing us with a text file in MySQL LOAD format (see www.mysql.com) of your participant and program information. If you dump selected fields in the order of the corresponding fields in the adass database and send us a CSV file or text file in MySQL LOAD format we will load it. Before you send us anything, please read the description of adass database tables.

ADASS 2004 Example

Populating adass.participant table

Step 1: Obtain list of participants from the 2004 register database:

    use register;
    SELECT UPPER(last_name), UPPER(first_name), UPPER(second_name), UPPER(REPLACE(institution,'\r\n',""))
    INTO OUTFILE "/tmp/part_adass14.txt"
    FIELDS TERMINATED BY '\t' 
    FROM adass_participants;

We created a small Perl script to parse /tmp/part_adass14.txt and put into MySQL load format.

    % perl parse_part_adass14.pl < /tmp/part_adass14.txt >part_adass2004.txt

    % mysql --local-infile --user=root --password=xxxx adass
    mysql> LOAD DATA LOCAL INFILE "./part_adass2004.txt" INTO TABLE adass.participant;

Populating the program table

Step 1: Obtain the abstract information from the 2004 register database;

    use register;
    SELECT UPPER(b.presentation_id), UPPER(REPLACE(b.presentation_title,'\r\n',"")), UPPER(a.last_name), UPPER(a.first_name), UPPER(a.second_name), UPPER(b.type)
    INTO OUTFILE "/tmp/abstracts_adass14.txt"
    FIELDS TERMINATED BY '\t' 
    FROM adass_participants a, adass_abstracts b
    WHERE a.participant_id = b.participant_id;

Step 2: Parse output and put in MySQL load format:

We created a small Perl script to parse '/tmp/abstracts_adass14.txt' and put into MySQL load format.

     % perl parse_abstracts_adass14.pl < /tmp/abstracts_adass14.txt >program_adass2004.txt

Step 3: load it into the adass.program table:

    USE adass
    LOAD DATA LOCAL INFILE "./program_adass2004.txt"
    INTO TABLE adass.program;

Populating the 'adass.invited' table

All of the fields in the invited table can be found in the 2004 register.adass_participants table with the exeception of special_topic. I believe this will be the case from most ADASS registration databases. So there is much redundancy in keeping the invited table. I suggest that the invited table be dropped.

Send comments, suggestions or questions to ADASS Web Database Administrator: Erik Timmermann Last Updated: Tue Mar 17 13:24:07 MST 2009	ADASS DB