Database-conversion.md

@@ -1526,6 +1526,105 @@ And cdli_db_old.cataloguesnew.translation_source != ""
 ```
 
 
+Adding translation credits to update events as authors
+
+```sql
+create view translation_source as 
+select 
+`cdli_db_old`.`cataloguesnew`.`translation_source` AS 
+`author`,
+`cdli_db_old`.`cataloguesnew`.`id_text` AS 
+`id`,
+`cdli_db_tmp`.`translation_authors`.`author_id` AS author_id
+from 
+(`cdli_db_old`.`cataloguesnew` join `cdli_db_tmp`.`translation_authors` on(`cdli_db_tmp`.`translation_authors`.`author` = `cdli_db_old`.`cataloguesnew`.`translation_source`))
+where
+cdli_db_tmp.translation_authors.author_id is not null
+AND
+cdli_db_tmp.translation_authors.author_id != 0;
+create view translation_source2 as 
+
+select 
+`cdli_db_old`.`cataloguesnew`.`translation_source` AS 
+`author`,
+`cdli_db_old`.`cataloguesnew`.`id_text` AS 
+`id`,
+`cdli_db_tmp`.`translation_authors`.`author_id` AS author_id
+from 
+(`cdli_db_old`.`cataloguesnew` join `cdli_db_tmp`.`translation_authors` on(`cdli_db_tmp`.`translation_authors`.`author` = `cdli_db_old`.`cataloguesnew`.`translation_source`))
+where
+cdli_db_tmp.translation_authors.author_id_2 is not null
+AND
+cdli_db_tmp.translation_authors.author_id_2 != 0;
+
+create view translation_source3 as 
+
+select 
+`cdli_db_old`.`cataloguesnew`.`translation_source` AS 
+`author`,
+`cdli_db_old`.`cataloguesnew`.`id_text` AS 
+`id`,
+`cdli_db_tmp`.`translation_authors`.`author_id` AS author_id
+from 
+(`cdli_db_old`.`cataloguesnew` join `cdli_db_tmp`.`translation_authors` on(`cdli_db_tmp`.`translation_authors`.`author` = `cdli_db_old`.`cataloguesnew`.`translation_source`))
+where
+cdli_db_tmp.translation_authors.author_id_3 is not null
+AND
+cdli_db_tmp.translation_authors.author_id_3 != 0;
+
+create view translation_source4 as 
+
+select 
+`cdli_db_old`.`cataloguesnew`.`translation_source` AS 
+`author`,
+`cdli_db_old`.`cataloguesnew`.`id_text` AS 
+`id`,
+`cdli_db_tmp`.`translation_authors`.`author_id` AS author_id
+from 
+(`cdli_db_old`.`cataloguesnew` join `cdli_db_tmp`.`translation_authors` on(`cdli_db_tmp`.`translation_authors`.`author` = `cdli_db_old`.`cataloguesnew`.`translation_source`))
+where
+cdli_db_tmp.translation_authors.author_id_4 is not null
+AND
+cdli_db_tmp.translation_authors.author_id_4 != 0;
+
+INSERT INTO cdli_db.authors_update_events 
+(`update_event_id`, `author_id`)
+Select cdli_db.update_events.id, cdli_db_tmp.translation_source.author_id
+FROM cdli_db.update_events
+JOIN cdli_db.artifacts_updates ON cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id 
+JOIN cdli_db_tmp.translation_source on cdli_db_tmp.translation_source.id = cdli_db.artifacts_updates.artifact_id
+WHERE cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id;
+
+INSERT INTO cdli_db.authors_update_events 
+(`update_event_id`, `author_id`)
+Select cdli_db.update_events.id, cdli_db_tmp.translation_source2.author_id
+FROM cdli_db.update_events
+JOIN cdli_db.artifacts_updates ON cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id 
+JOIN cdli_db_tmp.translation_source2 on cdli_db_tmp.translation_source2.id = cdli_db.artifacts_updates.artifact_id
+WHERE cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id;
+
+INSERT INTO cdli_db.authors_update_events 
+(`update_event_id`, `author_id`)
+Select cdli_db.update_events.id, cdli_db_tmp.translation_source3.author_id
+FROM cdli_db.update_events
+JOIN cdli_db.artifacts_updates ON cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id 
+JOIN cdli_db_tmp.translation_source3 on cdli_db_tmp.translation_source3.id = cdli_db.artifacts_updates.artifact_id
+WHERE cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id;
+
+INSERT INTO cdli_db.authors_update_events 
+(`update_event_id`, `author_id`)
+Select cdli_db.update_events.id, cdli_db_tmp.translation_source4.author_id
+FROM cdli_db.update_events
+JOIN cdli_db.artifacts_updates ON cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id 
+JOIN cdli_db_tmp.translation_source4 on cdli_db_tmp.translation_source4.id = cdli_db.artifacts_updates.artifact_id
+WHERE cdli_db.update_events.id = cdli_db.artifacts_updates.update_events_id;
+```
+
+
+
+
+
+
 
 adding composites to artifacts_updates