Database-conversion.md

@@ -1032,60 +1032,52 @@ SET
 
 # 3 Bibliography Migration
 
-Can't find all notes but this is part of it:  
-```sql
-UPDATE publications t1, publications t2
-SET t1.bibtexkey = concat(year,t2.id)
-WHERE t1.id = t2.id AND t1.bibtexkey is null
+Update mapping for primary authors
 
+`UPDATE cdli_db_tmp.primary_pub_authors join cdli_db.authors ON cdli_db_tmp.primary_pub_authors.`author` = cdli_db.authors.author SET cdli_db_tmp.primary_pub_authors.author_id = cdli_db.authors.id WHERE cdli_db_tmp.primary_pub_authors.`author` = cdli_db.authors.author`
 
-UPDATE authors t1, authors t2
-SET t1.last = SUBSTRING_INDEX(t2.author,",",1)
-WHERE t1.id = t2.id AND t1.last =""
+add primary publications
 
+```
 INSERT INTO cdli_db.publications (year, designation)
 SELECT distinct publication_date, primary_publication FROM cdli_db_old.cataloguesnew;
+```
+
+Remove designations which are not publications
 
+```
 Delete from publications where designation like "%CDLI%";
 Delete from publications where designation like "%unpublished%";
+```
 
+Fill the contribution fields 
+```
 update cdli_db.publications Set accepted_by = 820;
 update cdli_db.publications Set accepted = 1;
 ```
 
-```sql
+
+Add publications to artifacts_publications 
+
+```
 INSERT INTO cdli_db.artifacts_publications (artifact_id, publication_id, publication_comments)
 Select cdli_db_old.cataloguesnew.id_text, cdli_db.publications.id, cdli_db_old.cataloguesnew.author_remarks
 from cdli_db_old.cataloguesnew
 join cdli_db.publications ON cdli_db_old.cataloguesnew.primary_publication = cdli_db.publications.designation
 where cdli_db_old.cataloguesnew.primary_publication = cdli_db.publications.designation;
-
-
-  UPDATE `artifacts_publications` SET `publication_type`="primary" WHERE `publication_type` IS NULL;
-
-UPDATE `primary_pub_authors`
-join cdli_db.authors ON  `primary_pub_authors`.`author` = cdli_db.authors.author
-SET primary_pub_authors.author_id = cdli_db.authors.id
-WHERE `primary_pub_authors`.`author` = cdli_db.authors.author
 ```
 
-### Set authors for primary publications
-
-```sql
-
-CREATE VIEW cdli_db_tmp.primary_pubs AS
-SELECT cdli_db_tmp.primary_pub_authors.author, cdli_db_tmp.primary_pub_authors.author_id, cdli_db_tmp.primary_pub_authors.author_id_2, cdli_db_tmp.primary_pub_authors.author_id_3, cdli_db_old.cataloguesnew.primary_publication 
-FROM cdli_db_old.cataloguesnew
-join  cdli_db_tmp.primary_pub_authors on cdli_db_tmp.primary_pub_authors.author = cdli_db_old.cataloguesnew.author
-Where cdli_db_old.cataloguesnew.author != 'CDLI' or cdli_db_old.cataloguesnew.author != 'nn'
+Update publication type before inserting other types
 
+`UPDATE `artifacts_publications` SET `publication_type`="primary" WHERE `publication_type` IS NULL;`
 
-Put back fk keys
+add authors to primary publications 
 
+```
 INSERT INTO cdli_db.authors_publications (author_id, publication_id)
 SELECT  cdli_db_tmp.primary_pubs.author_id, cdli_db.publications.id
 FROM cdli_db.publications
-Join cdli_db_tmp.primary_pubs on cdli_db_tmp.primary_pubs.primary_publication = cdli_db.publications.designation
+Join cdli_db_tmp.primary_pubs on cdli_db_tmp.primary_pubs.primary_publication = cdli_db.publications.designation;
 
 INSERT INTO cdli_db.authors_publications (author_id, publication_id)
 SELECT  cdli_db_tmp.primary_pubs.author_id_2, cdli_db.publications.id
@@ -1098,22 +1090,205 @@ FROM cdli_db.publications
 Join cdli_db_tmp.primary_pubs on cdli_db_tmp.primary_pubs.primary_publication = cdli_db.publications.designation where cdli_db_tmp.primary_pubs.author_id_3 is not null;
 ```
 
-```sql
 
-insert into cdli_db.publications (id, designation, bibtexkey) 
-VALUES (  (SELECT cdli_db_tmp.pub_history.id), (SELECT cdli_db_tmp.pub_history.pub), (SELECT cdli_db_tmp.pub_history.id
-         cdli_db_tmp.pub_history));
+### publication history, citation and collation 
+
+if no ";"(344 000 entries, 17 800 left)
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select id_text, cdli_db_old.cataloguesnew.publication_history from cdli_db_old.cataloguesnew
+where cdli_db_old.cataloguesnew.publication_history not like "%;%";
+```
+
+
+before first ";"
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select id_text,SUBSTRING_INDEX(cdli_db_old.cataloguesnew.publication_history, ";", 1) from cdli_db_old.cataloguesnew
+where cdli_db_old.cataloguesnew.publication_history like "%;%";
+```
+
+create a table to work the rest 
+`INSERT INTO pub_history_full (artifact_id, pub) Select id, publication_history from cdli_db_old.cataloguesnew;`
+
+remove entries with only one pub
+
+`delete FROM pub_history_full where pub not like "%;%";`
+
+update to remove first pub from all entries
+
+`UPDATE pub_history_full SET pub= SUBSTR(REPLACE(pub,SUBSTRING_INDEX(pub,';',1),''),2);`
+ 
+add all the new single entries
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id, pub from pub_history_full
+where pub_history_full.pub not like "%;%";
+```
 
+remove the single entries
 
-  publication_history
+`delete FROM pub_history_full where pub not like "%;%";`
 
-  insert each value into publications table 
-  SELECT LAST_INSERT_ID(), cdli_db_old.cataloguesmew.text_id,  and "history" in artifacts_publications
 
-  insert into cdli_db.publications (id, designation, bibtexkey) 
-SELECT cdli_db_tmp.pub_history.id, cdli_db_tmp.pub_history.pub ,SELECT cdli_db_tmp.pub_history.id from from cdli_db_tmp.pub_history
+insert the first
 
 ```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id,SUBSTRING_INDEX(pub_history_full.pub, ";", 1) from pub_history_full
+where pub_history_full.pub like "%;%";
+```
+
+update to remove first pub from all entries
+
+`UPDATE pub_history_full SET pub= SUBSTR(REPLACE(pub,SUBSTRING_INDEX(pub,';',1),''),2);`
+
+
+add all the new single entries
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id, pub from pub_history_full
+where pub_history_full.pub not like "%;%";
+```
+
+remove the single entries
+
+`delete FROM pub_history_full where pub not like "%;%";`
+
+
+insert the first
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id,SUBSTRING_INDEX(pub_history_full.pub, ";", 1) from pub_history_full
+where pub_history_full.pub like "%;%";
+```
+
+update to remove first pub from all entries
+
+`UPDATE pub_history_full SET pub= SUBSTR(REPLACE(pub,SUBSTRING_INDEX(pub,';',1),''),2);`
+
+add all the new single entries
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id, pub from pub_history_full
+where pub_history_full.pub not like "%;%";
+```
+
+remove the single entries
+
+`delete FROM pub_history_full where pub not like "%;%";`
+
+again a dozen times.
+
+set type for all entries to secondary 
+
+```
+update cdli_db_tmp.pub_history
+set type = "secondary";
+```
+fill full to work citations
+```
+INSERT INTO pub_history_full (artifact_id, pub) Select id, citation from cdli_db_old.cataloguesnew;
+delete from pub_history where pub = "";
+
+delete from pub_history_full where pub = "";
+```
+
+add all the new single entries
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id, pub from pub_history_full
+where pub_history_full.pub not like "%;%";
+```
+
+remove the single entries
+
+`delete FROM pub_history_full where pub not like "%;%";`
+
+
+insert the first
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id,SUBSTRING_INDEX(pub_history_full.pub, ";", 1) from pub_history_full
+where pub_history_full.pub like "%;%";
+```
+
+update to remove first pub from all entries
+
+`UPDATE pub_history_full SET pub= SUBSTR(REPLACE(pub,SUBSTRING_INDEX(pub,';',1),''),2);`
+
+as many times as needed
+
+set type for all null entries to citation
+
+`update pub_history set type = "citation" where cdli_db_tmp.pub_history.type is null`
+
+
+fill full to work collations
+```
+INSERT INTO pub_history_full (artifact_id, pub) Select id, published_collation from cdli_db_old.cataloguesnew;
+
+delete from pub_history_full where pub = "";
+```
+
+fill pub_history with data ( roll through as many times as needed)
+
+```
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id, pub from pub_history_full
+where pub_history_full.pub not like "%;%";
+delete FROM pub_history_full where pub not like "%;%";
+insert into cdli_db_tmp.pub_history
+(artifact_id, pub)
+select artifact_id,SUBSTRING_INDEX(pub_history_full.pub, ";", 1) from pub_history_full
+where pub_history_full.pub like "%;%";
+UPDATE pub_history_full SET pub= SUBSTR(REPLACE(pub,SUBSTRING_INDEX(pub,';',1),''),2);
+```
+
+set type for all null entries to collation
+
+`update pub_history set type = "collation" where cdli_db_tmp.pub_history.type is null`
+
+fill the publications table with pub data
+`ALTER TABLE `pub_history` ADD `pub_id` INT NOT NULL AFTER `type`;`
+
+```
+insert into cdli_db.publications (designation)
+select distinct pub from cdli_db_tmp.pub_history
+update cdli_db_tmp.pub_history
+join cdli_db.publications on cdli_db_tmp.pub_history.pub = cdli_db.publications.designation
+set pub_id = cdli_db.publications.id where
+cdli_db_tmp.pub_history.pub = cdli_db.publications.designation;
+```
+
+fill the atifacts_publications table with pub data
+
+```
+insert into cdli_db.artifacts_publications (artifact_id, publication_id, publication_type)
+select artifact_id, publication_id, type from cdli_db_tmp.pub_history
+
+UPDATE `publications` SET designation = bibtexkey WHERE designation is null;
+```
+
+
 
 # 4 Artifacts updates / update events & cat credits
 check for missing new fields before running the query