Data Management Rework

[avkoehl]

• [x] list all tables and databases on datasci and digitalscholars: note any discrepencies
• [x] grant access to digitalscholars on datasci mysql
• [x] run some local tests from ds to see if the connection works
• [ ] write test queries to time the difference
• [ ] rewrite config.php
• [ ] make sure everything is working
• [ ] save backup of ds databases on google drive and then delete the databases
• [ ] from now on make updates only to the datasci databases

[avkoehl]

Tables on ds

quintessence_corpus (phase 1 only)

• Authors
• Keywords
• Languages
• Lemma
• Metadata
• Parts of Speech
• Raw
• Standardized
• Truncated Corpus
• XML

EEBO_Models (phase 1 only)

• all the authors_freq
• all the authors_neighbors
• all the decades_freq
• all the decades_neighbors
• timeseries
• top_tfidf
• topic_proportions
• topic_terms
• topic_terms_pca
• doc_topics
• EEBO_Metadata
• dtm_tfidf

[avkoehl]

Tables on datasci

quintessence (mixed)

• Authors (has phase 1 and 2)
• Keywords (has phase 1 and 2)
• Languages (has phase 1 and 2)
• Metadata (has phase 1 and 2)
• EEBO_Corpus (phase 1 only no qids)
• EEBO_Metadata (phase 1 only no qids)
• EEBO_XML (phase 1 only no qids)

quintessence_corpus (phase 1 and 2)

• Lemma
• PartsofSpeech
• Raw
• Standardized
• Truncated_Corpus

EEBO_Models

• same as ds except
• doc_topics (updated to new model with k=75)
• topic_terms (updated to new model with k=75)
• topic_terms_pca (updated to new model and has top authors etc)
• topic_proportions (updated to new model with k=75)
• missing EEBO_metadata
• missing dtm_tfidf

[avkoehl]

My conclusions, correct me if I am wrong with any of this @cnagda.

• Nothing is missing from datasci that is on digital scholars.
• In digital scholars EEBO_Models dtm_tfidf and EEBO_metadata are outdated and not used and should be deleted.
• datasci quintessence and quintessence_corpus should be merged into quintessence_corpus (we need to move Keywords, Authors, Languages, and Metadata from quintessence to quintessence_corpus)
• On datasci quintessence EEBO_Corpus, EEBO_Metadata and EEBO_XML are all outdated so once the other tables are moved we can just delete quintessence
• The topic model tables are updated on EEBO_Models on datasci; all that remains is to update the embeddings tables; timeseries table and tfidf table (which we will do later)

[cnagda]

@avkoehl The embeddings tables, timeseries table, topic model tables, tfidf table are updated on EEBO_Models on datasci (meaning they have phase1+2). Otherwise that all sounds right. Also just noticed I never made the topic_proportions table, is there code for that somewhere?

[avkoehl]

That's great news! Okay so all that remains to to update the topic_proportions. I will raise an issue #6

[avkoehl]

So as part of this issue I will merge quintessence and quintessence_corpus on datasci. And delete dtm_tfidf and EEBO_metadata tables from ds.

[avkoehl]

So as part of this issue I will merge quintessence and quintessence_corpus on datasci. And delete dtm_tfidf and EEBO_metadata tables from ds.

the two tables were deleted from digitalscholars EEBO_Models database.

moving Authors, Keywords, Languages, Metadata from quintessence to quintessence_corpus on datasci using this syntax alter table my_old_db.mytable rename my_new_db.mytable

alter table quintessence.Authors rename quintessence_corpus.Authors
alter table quintessence.Keywords rename quintessence_corpus.Keywords
alter table quintessence.Languages rename quintessence_corpus.Languages
alter table quintessence.Metadata rename quintessence_corpus.Metadata

Then had to drop schema quintessence. and manually deleted any files in /data/mysql/quintesssence

[avkoehl]

grant access to digitalscholars on datasci mysql

First got the ip address of digitalscholars. Then on datasci mysql from command line:

GRANT ALL ON quintessence_corpus.* TO digitalscholars@'169.237.102.141' IDENTIFIED BY "P@55word";
GRANT ALL ON EEBO_Models.* TO digitalscholars@'169.237.102.141' IDENTIFIED BY "P@55word";