Datenbanken für Webseite

• personal_www Haupttabelle
• additionallink "Additional Link", key: key_main
• pubmanbiblio "Publications" (pubman-id -> person-id), key: key_main
• talks "Talks" key:key_main
• teaching "Teaching" key: key_main
• pubmanbiblio_projects (pubman-id -> project-id)
• projects_members (project -> person-id), key: member_key

• agenda Institutskalender
• bibliography obsolet
• crossreferenz_instbibl_pubman (nur für Konvertierung)
• current_work (altes Feld "Current Work"), key: key_main
• institutsbiblio (obsolet)
• keys (person-id -> Cone-id), key: key_main
• publications obsolet
• research_interest (altes Feld "Research Interest") key: key_main
• www_berlinkalender (alter "Berlinkalender")
• www_preprints obsolet (derzeit direkt vom Filemaker-Server)

Updating to new keys

update personal_www set id = null
-- <import ids from xml in Zope>
-- <use list of additional ids for very old keys>
create temp table newids (newid text, oldkey text);
COPY newids FROM '/tmp/additional-ids.txt' delimiter ',';
update personal_www set id = new.newid from newids "new" where key = new.oldkey;

select key, id from personal_www where id is null or key is null

update personal_www set key = id, id = key;

select pw.key, pw.id, al.link from personal_www "pw", additionallink "al" where pw.key = al.key_main

update additionallink "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

update pubmanbiblio "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

update talks "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

update teaching "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

update keys "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

update projects_members "tbl" set member_key = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.member_key) and pw.key is not null;

update current_work "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

update research_interest "tbl" set key_main = pw.key 
 from personal_www "pw" 
 where lower(pw.id) = lower(tbl.key_main) and pw.key is not null;

Adding coneids for all members with keys

insert into keys 
  (select key, concat('http://pubman.mpiwg-berlin.mpg.de/cone/persons/resource/', key) 
     from keys right outer join personal_www on key_main = key where key_main is null)

---

Der Anhang zeigt ein altes Schema