Changeset 1367


Ignore:
Timestamp:
Jan 12, 2012, 3:10:27 PM (6 years ago)
Author:
dkg
Message:

clean up db.sql for new installs

File:
1 edited

Legend:

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

    r1134 r1367  
    718718REVOKE ALL ON TABLE mail_log_id_seq FROM dkg;
    719719GRANT ALL ON TABLE mail_log_id_seq TO dkg;
    720 GRANT INSERT,UPDATE ON TABLE mail_log_id_seq TO "www-data";
     720GRANT SELECT,UPDATE ON TABLE mail_log_id_seq TO "www-data";
    721721
    722722
     
    846846  FROM workshop_category GROUP BY workshop_id;
    847847
    848 CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name, last_name, email, workshop_columns.*, organization.shortname AS school, organization.id = 31 as gates, organization.id = 9 as consortium from person LEFT JOIN attendance ON (person.id = person_id) LEFT JOIN workshop_columns USING (workshop_id) LEFT JOIN affiliation ON (person.id = affiliation.person_id) LEFT JOIN organization ON (organization_id = organization.id) ORDER BY last_name,first_name;
    849 
    850 
    851 CREATE VIEW pv AS select MAX(first) as first, MAX(mid) as mid, MAX(last) as last, email, MAX(school) as school,
    852  SUM(CASE WHEN lower(tag) LIKE '%math%' THEN 1 ELSE 0 END) > 0 AS math,
    853  SUM(CASE WHEN lower(tag) LIKE '%history%' THEN 1 ELSE 0 END) > 0 AS history,
    854  SUM(CASE WHEN lower(tag) LIKE '%science%' THEN 1 ELSE 0 END) > 0 AS science,
    855  SUM(CASE WHEN lower(tag) LIKE '%art%' THEN 1 ELSE 0 END) > 0 AS art,
    856  SUM(CASE WHEN lower(tag) LIKE '%english%' THEN 1 ELSE 0 END) > 0 AS english,
    857  SUM(CASE WHEN lower(tag) LIKE '%gates%' THEN 1 ELSE 0 END) > 0 AS gates,
    858  SUM(CASE WHEN lower(tag) LIKE '%empire%' THEN 1 ELSE 0 END) > 0 AS empire,
    859  SUM(CASE WHEN lower(tag) LIKE '%admin%' THEN 1 ELSE 0 END) > 0 AS admin,
    860  SUM(CASE WHEN lower(tag) LIKE '%amerhistgrant%' THEN 1 ELSE 0 END) > 0 AS amerhistgrant,
    861  SUM(CASE WHEN lower(tag) LIKE '%network%' THEN 1 ELSE 0 END) > 0 AS network
    862  from pp LEFT JOIN pt ON (pp.id = pp_id)  GROUP BY email;
     848-- CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name, last_name, email, workshop_columns.*, organization.title AS school, organization.id = 31 as gates, organization.id = 9 as consortium from person LEFT JOIN attendance ON (person.id = person_id) LEFT JOIN workshop_columns USING (workshop_id) LEFT JOIN affiliation ON (person.id = affiliation.person_id) LEFT JOIN organization ON (organization_id = organization.id) ORDER BY last_name,first_name;
     849
     850
     851-- CREATE VIEW pv AS select MAX(first) as first, MAX(mid) as mid, MAX(last) as last, email, MAX(school) as school,
     852-- SUM(CASE WHEN lower(tag) LIKE '%math%' THEN 1 ELSE 0 END) > 0 AS math,
     853-- SUM(CASE WHEN lower(tag) LIKE '%history%' THEN 1 ELSE 0 END) > 0 AS history,
     854-- SUM(CASE WHEN lower(tag) LIKE '%science%' THEN 1 ELSE 0 END) > 0 AS science,
     855-- SUM(CASE WHEN lower(tag) LIKE '%art%' THEN 1 ELSE 0 END) > 0 AS art,
     856-- SUM(CASE WHEN lower(tag) LIKE '%english%' THEN 1 ELSE 0 END) > 0 AS english,
     857-- SUM(CASE WHEN lower(tag) LIKE '%gates%' THEN 1 ELSE 0 END) > 0 AS gates,
     858-- SUM(CASE WHEN lower(tag) LIKE '%empire%' THEN 1 ELSE 0 END) > 0 AS empire,
     859-- SUM(CASE WHEN lower(tag) LIKE '%admin%' THEN 1 ELSE 0 END) > 0 AS admin,
     860-- SUM(CASE WHEN lower(tag) LIKE '%amerhistgrant%' THEN 1 ELSE 0 END) > 0 AS amerhistgrant,
     861-- SUM(CASE WHEN lower(tag) LIKE '%network%' THEN 1 ELSE 0 END) > 0 AS network
     862-- from pp LEFT JOIN pt ON (pp.id = pp_id)  GROUP BY email;
    863863
    864864CREATE TABLE event (
     
    977977-- is needed:
    978978
    979 DROP VIEW pommo_import;
     979DROP VIEW pommo_import IF EXISTS;
    980980
    981981CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name,
Note: See TracChangeset for help on using the changeset viewer.