Changeset 968 for trunk/jpdd/sql/db.sql

Timestamp:

Jan 30, 2008 3:48:56 PM (5 years ago)

Author:

dkg

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:

• trunk/jpdd/sql/db.sql (modified) (4 diffs)

trunk/jpdd/sql/db.sql

@@ -958 +958 @@
 
 
-CREATE OR REPLACE VIEW person_categories AS SELECT person_id,
+CREATE OR REPLACE VIEW person_categories AS SELECT person_id, event_id, 
  COUNT(category_id = 9 OR NULL) > 0 AS math,
  COUNT(category_id = 15 OR NULL) > 0 AS art,
@@ -964 +964 @@
  COUNT(category_id = 12 OR NULL) > 0 AS history,
  COUNT(category_id = 25 OR NULL) > 0 AS english
-  FROM person_event_category GROUP BY person_id;
-
+  FROM person_event_category GROUP BY person_id, event_id;
+
+CREATE OR REPLACE VIEW pommo_person_categories AS SELECT person_id, event_id,
+ CASE WHEN math THEN '"on"' ELSE '' END AS math,
+ CASE WHEN art THEN '"on"' ELSE '' END AS art,
+ CASE WHEN science THEN '"on"' ELSE '' END AS science,
+ CASE WHEN history THEN '"on"' ELSE '' END AS history,
+ CASE WHEN english THEN '"on"' ELSE '' END AS english 
+FROM person_categories;
 
 -- we're redoing the pommo import with our new conception of what data
@@ -973 +980 @@
 
 CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name,
-last_name, email, person_categories.*, organization.title AS
+last_name, email, person_event.person_id,
+pommo_person_categories.math,
+pommo_person_categories.art,
+pommo_person_categories.science,
+pommo_person_categories.history,
+pommo_person_categories.english,
+organization.title AS
 school, event_id FROM
 
- person JOIN person_categories ON (person.id = person_id) JOIN (SELECT
+ person JOIN pommo_person_categories ON (person.id = person_id) JOIN (SELECT
 DISTINCT person_id, event_id FROM person_event_category) AS
-person_event USING (person_id) LEFT JOIN affiliation ON (person.id =
+person_event USING (person_id, event_id) LEFT JOIN affiliation ON (person.id =
 affiliation.person_id) LEFT JOIN organization ON (organization_id =
-organization.id) ORDER BY last_name,first_name;
+organization.id) ORDER BY lower(last_name),lower(first_name);
 
 -- -- here's the actual export:
@@ -991 +1004 @@
 -- jpdd=>
 
--- and then i needed to swap out ,"on", and ,, for ,t, and ,f,
-
 -- e-mail addresses will be treated case-insensitively: this is
 -- against the RFC, but it is a minor inconvenience for a tiny subset