== Schritte zur Umstellung der SQL-Datenbank ==

   * Erzeuge neues Feld key und web_object_created
   {{{
     alter table personal_www add column key text;
     alter table personal_www add column web_object_created text;
     alter table personal_www add column "group" text;
alter table personal_www add column "_month_arrival" text;
alter table personal_www add column "_month_departure" text;

   }}}

   * Belege das Key Feld
   {{{
    update personal_www set key = trim(both ' ' from vorname)||'_'||trim(both ' ' from name)
   }}}

   * Umbenennen
   {{{
    alter table personal_www rename vorname to first_name;
    alter table personal_www rename name to last_name;
    alter table personal_www rename position to status;
    alter table personal_www rename abteilung to department;
    alter table personal_www rename heimat_inst to home_inst;
    alter table personal_www rename stay_at_mpiwg to date_stay_at_mpiwg;
   }}}

  * Schalte "nicht null" Eigenschaft von id ab, id darf dazu nicht mehr primär key sein.

  * lasse cleanDBUp.py über die Datenbank laufen, um doppelte zu löschen.


= Hilfsdatenbanken - current_work =

{{{
alter table current_work add column key_main text;
update current_work set key_main = (select personal_www.key from personal_www where personal_www.id=current_work.id_main);
alter table research_interest add column key_main text;
update research_interest set key_main = (select personal_www.key from personal_www where personal_www.id=research_interest.id_main);
alter table publications add column key_main text;
update publications set key_main = (select personal_www.key from personal_www where personal_www.id=publications.id_main);
alter table talks add column key_main text;
update talks set key_main = (select personal_www.key from personal_www where personal_www.id=talks.id_main);
alter table teaching add column key_main text;
update teaching set key_main = (select personal_www.key from personal_www where personal_www.id=teaching.id_main);
}}}

= Neue Views =

Liste mit isScholar:

{{{
CREATE OR REPLACE VIEW personal_www_list AS 
 SELECT *,
  CASE
    WHEN personal_www."group" = 'Wissenschaftler'::text 
      OR personal_www."group" = 'wissenschaftliche Gäste'::text 
      OR personal_www."group" = 'Stipendiaten'::text 
    THEN 'yes'::text
    ELSE 'no'::text
  END AS is_scholar
 FROM personal_www;
}}}

Liste mit current_work:

{{{
CREATE OR REPLACE VIEW personal_www_list AS 
 SELECT *, 
        CASE
            WHEN personal_www."group" = 'Wissenschaftler'::text OR personal_www."group" = 'wissenschaftliche Gäste'::text OR personal_www."group" = 'Stipendiaten'::text THEN 'yes'::text
            ELSE 'no'::text
        END AS is_scholar, ( SELECT current_work.current
           FROM current_work
          WHERE current_work.key_main = personal_www."key" AND current_work.publish = 'yes'::text
          ORDER BY current_work.priority
         LIMIT 1) AS current_work
   FROM personal_www;
}}}