Integrate SQL database into backend

[wasimsandhu]

The hope is to use @sarah-biohub's script for QC processing to write QC results for any data file in a particular directory to a local SQL database, which will then upload the relevant tables to Google Drive.

In total, the SQLite database will house the following:

• Settings: instruments, notifications, internal standards
• Users: what users are allowed access to the Google Drive database
• Instrument information: each instrument, active runs, past runs, last sample for each run
• Instrument sequence
• Sample metadata
• QC results for each sample in each study

[wasimsandhu]

Here is Sarah's current database schema:

[wasimsandhu]

Preliminary design for the database can be found here.

Sarah stores QC results in a string. I couldn't think of a better way to store them so that's what I'll do too. Example:

[[‘1_Methionine_d8’, 2, 40061.5703125, ‘7.5023: 0.0303’, ‘156.0938: 0.0002’], [‘1_Creatinine_d3’, 2, 809211.4375, ‘4.9518: 0.0328’, ‘115.0705: 0.0’], [‘1_CUDA’, 2, 8629338125, ‘1.1431: 0.0391’, ‘339.2654: 0.0001’], [‘1_Glutamine_d5’, 2, 3827490.75, ‘8.6456: 0.0056’, ‘150.0932: 0.0001’], [‘1_Glutamic Acid_d3’, 2, 2652428.25, ‘8.8038: 0.0062’……. ]].

[wasimsandhu]

CREATE database and begin connecting to frontend: 66a5f6c

[wasimsandhu]

INSERT new instrument runs into database: https://github.com/czbiohub/MS-AutoQC/commit/5182457a81389766a09e2c9272d696a7c2c00edb

[wasimsandhu]

INSERT sample rows and UPDATE md5_checksum for run monitoring: https://github.com/czbiohub/MS-AutoQC/commit/50644938ed8eedc563cd8bf3a97fc5dda6d10f4e

[wasimsandhu]

UPDATE sample rows with QC processing results: https://github.com/czbiohub/MS-AutoQC/commit/166ac0ca406f25cddf1069cd4e1cc886dff03914

[wasimsandhu]

INSERT chromatography methods and UPDATE internal standards with MSP upload: https://github.com/czbiohub/MS-AutoQC/commit/43c0ce4ca57ec2856d18fe38d8a9747547283789

[wasimsandhu]

INSERT, DELETE new MS-DIAL configurations and UPDATE parameters in configurations: https://github.com/czbiohub/MS-AutoQC/commit/9f85c49c71ee6c7e697d551d6c0a31f43195752f

[wasimsandhu]

UPDATE internal standards with CSV upload: https://github.com/czbiohub/MS-AutoQC/commit/d03857536a5fb7c132ab7b1bb16d9dfe50711f2f

[wasimsandhu]

INSERT / DELETE biological standards, DELETE chromatography methods: https://github.com/czbiohub/MS-AutoQC/commit/8908193a0934a2634b4620d3f740f831659b5c9b

[wasimsandhu]

INSERT targeted features with MSP upload, UPDATE biological standards: https://github.com/czbiohub/MS-AutoQC/commit/fd781fef48bcdb0cfef2843b93ed998e0525fa50

[wasimsandhu]

INSERT, DELETE new QC configurations and UPDATE parameters in QC configurations: https://github.com/czbiohub/MS-AutoQC/commit/59ee31c40ee07996f5458dd8333d18cea772d987

[wasimsandhu]

UPDATE biological standard rows with QC results: https://github.com/czbiohub/MS-AutoQC/commit/3b7b443ce30f97422a16916dd4b73880fba76efc

[wasimsandhu]