Changeset 968


Ignore:
Timestamp:
Jan 30, 2008, 3:48:56 PM (11 years ago)
Author:
Daniel Kahn Gillmor
Message:

jpdd: clean up pommo import to auto-generate the correct format of output. This puts JPDD close to being able to import directly into pommo.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • trunk/jpdd/sql/db.sql

    r951 r968  
    958958
    959959
    960 CREATE OR REPLACE VIEW person_categories AS SELECT person_id,
     960CREATE OR REPLACE VIEW person_categories AS SELECT person_id, event_id,
    961961 COUNT(category_id = 9 OR NULL) > 0 AS math,
    962962 COUNT(category_id = 15 OR NULL) > 0 AS art,
     
    964964 COUNT(category_id = 12 OR NULL) > 0 AS history,
    965965 COUNT(category_id = 25 OR NULL) > 0 AS english
    966   FROM person_event_category GROUP BY person_id;
    967 
     966  FROM person_event_category GROUP BY person_id, event_id;
     967
     968CREATE OR REPLACE VIEW pommo_person_categories AS SELECT person_id, event_id,
     969 CASE WHEN math THEN '"on"' ELSE '' END AS math,
     970 CASE WHEN art THEN '"on"' ELSE '' END AS art,
     971 CASE WHEN science THEN '"on"' ELSE '' END AS science,
     972 CASE WHEN history THEN '"on"' ELSE '' END AS history,
     973 CASE WHEN english THEN '"on"' ELSE '' END AS english
     974FROM person_categories;
    968975
    969976-- we're redoing the pommo import with our new conception of what data
     
    973980
    974981CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name,
    975 last_name, email, person_categories.*, organization.title AS
     982last_name, email, person_event.person_id,
     983pommo_person_categories.math,
     984pommo_person_categories.art,
     985pommo_person_categories.science,
     986pommo_person_categories.history,
     987pommo_person_categories.english,
     988organization.title AS
    976989school, event_id FROM
    977990
    978  person JOIN person_categories ON (person.id = person_id) JOIN (SELECT
     991 person JOIN pommo_person_categories ON (person.id = person_id) JOIN (SELECT
    979992DISTINCT person_id, event_id FROM person_event_category) AS
    980 person_event USING (person_id) LEFT JOIN affiliation ON (person.id =
     993person_event USING (person_id, event_id) LEFT JOIN affiliation ON (person.id =
    981994affiliation.person_id) LEFT JOIN organization ON (organization_id =
    982 organization.id) ORDER BY last_name,first_name;
     995organization.id) ORDER BY lower(last_name),lower(first_name);
    983996
    984997-- -- here's the actual export:
     
    9911004-- jpdd=>
    9921005
    993 -- and then i needed to swap out ,"on", and ,, for ,t, and ,f,
    994 
    9951006-- e-mail addresses will be treated case-insensitively: this is
    9961007-- against the RFC, but it is a minor inconvenience for a tiny subset
Note: See TracChangeset for help on using the changeset viewer.