/// <summary>Will add a DatasetTableRow from db to the given Experiment</summary>
/// <param name="experiment">Experiment to have its GSMs property updated</param>
public static void AddDatasetTableRowToExperimentFromDb(Experiment experiment, SQLiteDataReader reader)
{
DatasetTableRow dtr = new DatasetTableRow(reader["id_ref"].ToString(), reader["identifier"].ToString(), reader["value"].ToString());
dtr.Id = Convert.ToInt32(reader["id"].ToString());
dtr.ExperimentId = reader["experiment_id"].ToString();
experiment.DatasetTable.Add(dtr);
}
/// <summary>Will add GSMs from db to the given Experiment</summary>
/// <param name="experiment">Experiment to have its GSMs property updated</param>
public static void AddExperimentGSMToExperimentFromDb(Experiment experiment, SQLiteDataReader reader)
{
ExperimentGSM gsm = new ExperimentGSM(reader["gsm_id"].ToString(), reader["gsm_value"].ToString());
gsm.Id = Convert.ToInt32(reader["id"].ToString());
gsm.ExperimentId = reader["experiment_id"].ToString();
experiment.GSMs.Add(gsm);
}
public ExperimentDetailsForm(Experiment experiment)
{
if (experiment == null) {
throw new ArgumentNullException();
}
InitializeComponent();
Experiment = experiment;
}
/// <summary>Default constructor</summary>
public ExperimentSelectionController(int seeds, double significance, string[] identifiers, Experiment[] experiments)
{
if (identifiers == null || identifiers.Count() == 0 || experiments == null || experiments.Count() <= 1) {
throw new ArgumentNullException("Invalid arguments, experiment selection aborted");
}
Seeds = seeds;
Significance = significance;
Identifiers = identifiers;
Experiments = experiments;
}
public void ReturnDeleteQueryForExperimentAndRelatedData()
{
Experiment e = new Experiment();
e.Dataset = "foobar";
string query = SqlFactory.CreateDeleteExperimentQueryFromObject(e);
bool deleteFromExperiments = query.Contains("DELETE FROM experiments WHERE dataset = 'foobar'");
bool deleteGSMsData = query.Contains("DELETE FROM gsms_data WHERE experiment_id = 'foobar'");
bool deleteFromDatasets = query.Contains("DELETE FROM datasets WHERE experiment_id = 'foobar'");
Assert.IsTrue(deleteFromExperiments && deleteGSMsData && deleteFromDatasets);
}
public static void UpdateExperimentDetails(GroupBox details, Experiment experiment)
{
foreach (Control control in details.Controls)
{
switch (control.AccessibleName)
{
case "txtExperimentNameOutput":
control.Text = experiment.DatabaseName;
break;
case "txtExperimentDescriptionOutput":
control.Text = experiment.DatasetDescription;
break;
case "txtTags":
control.Text = experiment.Tags;
break;
}
}
}
public static string CreateInsertExperimentQueryFromObject(Experiment e)
{
if (e == null) {
throw new ArgumentNullException("Invalid query argument");
}
if (string.IsNullOrEmpty(e.Dataset)) {
throw new ArgumentException("Experiment dataset is null or empty");
}
StringBuilder result = new StringBuilder();
result.Append("INSERT INTO experiments (database_used, database_name, database_web_link, ");
result.Append("database_institute, database_email, database_ref, dataset, dataset_title, ");
result.Append("dataset_description, dataset_type, dataset_pubmed_id, dataset_platform, ");
result.Append("dataset_platform_organism, dataset_platform_technology_type, ");
result.Append("dataset_feature_count, dataset_sample_organism, dataset_sample_type, ");
result.Append("dataset_reference_series, dataset_update_date) VALUES (");
result.Append("'"); result.Append(e.Database); result.Append("', ");
result.Append("'"); result.Append(e.DatabaseName); result.Append("', ");
result.Append("'"); result.Append(e.DatabaseWebLink); result.Append("', ");
result.Append("'"); result.Append(e.DatabaseInstitute); result.Append("', ");
result.Append("'"); result.Append(e.DatabaseEmail); result.Append("', ");
result.Append("'"); result.Append(e.DatabaseRef); result.Append("', ");
result.Append("'"); result.Append(e.Dataset); result.Append("', ");
result.Append("'"); result.Append(e.DatasetTitle); result.Append("', ");
result.Append("'"); result.Append(e.DatasetDescription); result.Append("', ");
result.Append("'"); result.Append(e.DatasetType); result.Append("', ");
result.Append("'"); result.Append(e.DatasetPubmedId); result.Append("', ");
result.Append("'"); result.Append(e.DatasetPlatform); result.Append("', ");
result.Append("'"); result.Append(e.DatasetPlatformOrganism); result.Append("', ");
result.Append("'"); result.Append(e.DatasetPlatformTechnologyType); result.Append("', ");
result.Append("'"); result.Append(e.DatasetFeatureCount); result.Append("', ");
result.Append("'"); result.Append(e.DatasetSampleOrganism); result.Append("', ");
result.Append("'"); result.Append(e.DatasetSampleType); result.Append("', ");
result.Append("'"); result.Append(e.DatasetReferenceSeries); result.Append("', ");
result.Append("'"); result.Append(e.DatasetUpdateDate); result.Append("'); ");
return result.ToString();
}
public static string CreateDeleteExperimentQueryFromObject(Experiment experiment)
{
if (experiment == null) {
throw new ArgumentNullException();
}
StringBuilder result = new StringBuilder();
result.Append("DELETE FROM experiments WHERE dataset = '");
result.Append(experiment.Dataset);
result.Append("';");
result.Append("DELETE FROM gsms_data WHERE experiment_id = '");
result.Append(experiment.Dataset);
result.Append("';");
result.Append("DELETE FROM datasets WHERE experiment_id = '");
result.Append(experiment.Dataset);
result.Append("';");
return result.ToString();
}
/// <summary>Will set all Experiment properties using data from database</summary>
/// <param name="experiment">Experiment to have its properties set</param>
/// <param name="reader">The database reader containing columns data</param>
public static void SetExperimentPropertiesFromDb(Experiment experiment, SQLiteDataReader reader)
{
experiment.Id = Convert.ToInt32(reader["id"].ToString());
experiment.Database = reader["database_used"].ToString();
experiment.DatabaseName = reader["database_name"].ToString();
experiment.DatabaseInstitute = reader["database_institute"].ToString();
experiment.DatabaseWebLink = reader["database_web_link"].ToString();
experiment.DatabaseEmail = reader["database_email"].ToString();
experiment.DatabaseRef = reader["database_ref"].ToString();
experiment.Dataset = reader["dataset"].ToString();
experiment.DatasetDescription = reader["dataset_description"].ToString();
experiment.DatasetFeatureCount = reader["dataset_feature_count"].ToString();
experiment.DatasetPlatform = reader["dataset_platform"].ToString();
experiment.DatasetPlatformOrganism = reader["dataset_platform_organism"].ToString();
experiment.DatasetPlatformTechnologyType = reader["dataset_platform_technology_type"].ToString();
experiment.DatasetPubmedId = reader["dataset_pubmed_id"].ToString();
experiment.DatasetReferenceSeries = reader["dataset_reference_series"].ToString();
experiment.DatasetSampleOrganism = reader["dataset_sample_organism"].ToString();
experiment.DatasetSampleType = reader["dataset_sample_type"].ToString();
experiment.DatasetTitle = reader["dataset_title"].ToString();
experiment.DatasetType = reader["dataset_type"].ToString();
experiment.DatasetUpdateDate = reader["dataset_update_date"].ToString();
experiment.Tags = reader["tags"].ToString();
}
public static string CreateInsertDatasetTableRowsQueryFromObject(Experiment e)
{
if (e == null) {
throw new ArgumentNullException("Invalid query argument");
}
StringBuilder result = new StringBuilder();
// optimisation: group all INSERTs into a single transaction
result.Append("BEGIN TRANSACTION; ");
foreach (DatasetTableRow dtr in e.DatasetTable)
{
result.Append("INSERT INTO datasets (experiment_id, id_ref, identifier, value) VALUES (");
result.Append("'"); result.Append(e.Dataset); result.Append("', ");
result.Append("'"); result.Append(dtr.IdRef); result.Append("', ");
result.Append("'"); result.Append(dtr.Identifier); result.Append("', ");
result.Append("'"); result.Append(dtr.Value); result.Append("'); ");
}
result.Append("COMMIT; ");
return result.ToString();
}
/// <summary>Get all experiments from database</summary>
/// <returns>Array of all Experiments</returns>
public static Experiment[] All()
{
List<Experiment> result = new List<Experiment>();
SQLiteConnection dbConnection = DatabaseManager.Instance.Connection;
dbConnection.Open();
try {
SQLiteCommand command = new SQLiteCommand(dbConnection);
command.CommandText = SqlFactory.CreateSelectAllExperimentsQuery();
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
Experiment experiment = new Experiment();
ExperimentHelper.SetExperimentPropertiesFromDb(experiment, reader);
result.Add(experiment);
}
}
}
foreach (Experiment experiment in result)
{
// get experiment GSMs
SetExperimentGSMs(experiment, command);
}
} finally {
dbConnection.Close();
}
return result.ToArray();
}
public void ThrowExceptionIfSelectedExperimentsNotGreaterThanOne()
{
Experiment[] experiments = new Experiment[] { new Experiment() };
string[] identifiers = new string[] { "foor", "bar" };
ExperimentSelectionController controller = new ExperimentSelectionController(0, 0.0, identifiers, experiments.ToArray());
}
public void ThrowExceptionIfNoIdentifiersAreSelected()
{
Experiment[] experiments = new Experiment[] { new Experiment(), new Experiment() };
string[] identifiers = new string[0];
ExperimentSelectionController controller = new ExperimentSelectionController(0, 0.0, identifiers, experiments.ToArray());
}
/// <summary>Selects, reads and sets Experiment GSMs for Experiment</summary>
/// <param name="result">Experiment to update</param>
/// <param name="command">Command to use for the select the query</param>
private static void SetExperimentGSMs(Experiment result, SQLiteCommand command)
{
command.CommandText = SqlFactory.CreateSelectGSMsForExperimentQuery(result.Dataset);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
result.GSMs.Clear();
while (reader.Read())
{
ExperimentGSMHelper.AddExperimentGSMToExperimentFromDb(result, reader);
}
}
}
}
/// <summary>Sets an Experiment property from given data</summary>
/// <remarks>Method will figure out which property exactly to update</remarks>
/// <param name="experiment">Experiment to be updated</param>
/// <param name="lineFromCsvFile">Data from CSV containing the value to be set</param>
public static void SetExperimentPropertyFromCsvLine(Experiment experiment, string lineFromCsvFile)
{
string[] data;
if (lineFromCsvFile.Contains("="))
{
data = lineFromCsvFile.Split('=');
data = Normalise(data);
switch (data[0])
{
case "^DATABASE":
experiment.Database = data[1];
break;
case "!Database_name":
experiment.DatabaseName = data[1];
break;
case "!Database_institute":
experiment.DatabaseInstitute = data[1];
break;
case "!Database_web_link":
experiment.DatabaseWebLink = data[1];
break;
case "!Database_email":
experiment.DatabaseEmail = data[1];
break;
case "!Database_ref":
experiment.DatabaseRef = data[1];
break;
case "^DATASET":
experiment.Dataset = RemoveCommas(data[1]);
break;
case "!dataset_title":
experiment.DatasetTitle = data[1];
break;
case "!dataset_description":
experiment.DatasetDescription = data[1];
break;
case "!dataset_type":
experiment.DatasetType = data[1];
break;
case "!dataset_pubmed_id":
experiment.DatasetPubmedId = data[1];
break;
case "!dataset_platform":
experiment.DatasetPlatform = data[1];
break;
case "!dataset_platform_organism":
experiment.DatasetPlatformOrganism = data[1];
break;
case "!dataset_platform_technology_type":
experiment.DatasetPlatformTechnologyType = data[1];
break;
case "!dataset_feature_count":
experiment.DatasetFeatureCount = data[1];
break;
case "!dataset_sample_organism":
experiment.DatasetSampleOrganism = data[1];
break;
case "!dataset_sample_type":
experiment.DatasetSampleType = data[1];
break;
case "!dataset_reference_series":
experiment.DatasetReferenceSeries = data[1];
break;
case "!dataset_update_date":
experiment.DatasetUpdateDate = data[1];
break;
}
if (data[0].StartsWith("#GSM"))
{
ExperimentGSM gsm = new ExperimentGSM(data[0], data[1]);
experiment.GSMs.Add(gsm);
}
}
else
{
lineFromCsvFile = FixInvalidDatasetTableIdentifiers(lineFromCsvFile);
data = lineFromCsvFile.Split(',');
if (data[0].EndsWith("_at"))
{
StringBuilder datasetValue = new StringBuilder();
for (int i = 2; i < data.Length; i++)
{
try
{
datasetValue.Append(data[i]);
datasetValue.Append(";");
}
catch (ArgumentOutOfRangeException)
{
MessageBox.Show("Dataset table rows cannot contain double quotes and should be comma-separated. Row ID_REF: " + data[0], "Dataset table invalid row", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
datasetValue.Remove(datasetValue.Length - 1, 1); // remove last ';' separator
DatasetTableRow datasetRow = new DatasetTableRow(data[0], data[1], datasetValue.ToString());
experiment.DatasetTable.Add(datasetRow);
}
}
}
/// <summary>Load experiment data from a CSV file into database</summary>
/// <param name="path">Path to CSV file containig the data</param>
public void LoadExperimentFromCsvFile(string path)
{
try
{
using (StreamReader reader = new StreamReader(path))
{
Experiment experiment = new Experiment();
string lineFromFile = null;
while ((lineFromFile = reader.ReadLine()) != null)
{
ExperimentHelper.SetExperimentPropertyFromCsvLine(experiment, lineFromFile);
}
experiment.Save(); // will save as new experiment
reader.Close();
}
}
catch (IOException e)
{
MessageBox.Show(e.Message);
}
}
public void CreateEmptyListsWhenInitialised()
{
Experiment ex = new Experiment();
Assert.IsNotNull(ex.GSMs);
Assert.IsNotNull(ex.DatasetTable);
}
internal static string CreateUpdateExperimentQueryFromObject(Experiment e)
{
if (e == null) {
throw new ArgumentNullException("Invalid query argument");
}
StringBuilder result = new StringBuilder();
result.Append("UPDATE experiments SET ");
result.Append("database_used = ");
result.Append("'"); result.Append(e.Database); result.Append("', ");
result.Append("database_name = ");
result.Append("'"); result.Append(e.DatabaseName); result.Append("', ");
result.Append("database_web_link = ");
result.Append("'"); result.Append(e.DatabaseWebLink); result.Append("', ");
result.Append("database_institute = ");
result.Append("'"); result.Append(e.DatabaseInstitute); result.Append("', ");
result.Append("database_email = ");
result.Append("'"); result.Append(e.DatabaseEmail); result.Append("', ");
result.Append("database_ref = ");
result.Append("'"); result.Append(e.DatabaseRef); result.Append("', ");
result.Append("dataset = ");
result.Append("'"); result.Append(e.Dataset); result.Append("', ");
result.Append("dataset_title = ");
result.Append("'"); result.Append(e.DatasetTitle); result.Append("', ");
result.Append("dataset_description = ");
result.Append("'"); result.Append(e.DatasetDescription); result.Append("', ");
result.Append("dataset_type = ");
result.Append("'"); result.Append(e.DatasetType); result.Append("', ");
result.Append("dataset_pubmed_id = ");
result.Append("'"); result.Append(e.DatasetPubmedId); result.Append("', ");
result.Append("dataset_platform = ");
result.Append("'"); result.Append(e.DatasetPlatform); result.Append("', ");
result.Append("dataset_platform_organism = ");
result.Append("'"); result.Append(e.DatasetPlatformOrganism); result.Append("', ");
result.Append("dataset_platform_technology_type = ");
result.Append("'"); result.Append(e.DatasetPlatformTechnologyType); result.Append("', ");
result.Append("dataset_feature_count = ");
result.Append("'"); result.Append(e.DatasetFeatureCount); result.Append("', ");
result.Append("dataset_sample_organism = ");
result.Append("'"); result.Append(e.DatasetSampleOrganism); result.Append("', ");
result.Append("dataset_sample_type = ");
result.Append("'"); result.Append(e.DatasetSampleType); result.Append("', ");
result.Append("dataset_reference_series = ");
result.Append("'"); result.Append(e.DatasetReferenceSeries); result.Append("', ");
result.Append("dataset_update_date = ");
result.Append("'"); result.Append(e.DatasetUpdateDate); result.Append("', ");
result.Append("tags = ");
result.Append("'"); result.Append(e.Tags); result.Append("' ");
result.Append("WHERE id = "); result.Append(e.Id);
return result.ToString();
}
/// <summary>Find an experiment in the database by given condition</summary>
/// <remarks>Conditions currently supported (see the SQL query) are: dataset, database_name and database_used</remarks>
/// <param name="condition">Condition to look for</param>
/// <returns>Experiment matching the condition or null if one was not found</returns>
public static Experiment FindBy(string condition)
{
Experiment result = null;
SQLiteConnection dbConnection = DatabaseManager.Instance.Connection;
dbConnection.Open();
try {
// get experiment metadata
SQLiteCommand command = new SQLiteCommand(dbConnection);
command.CommandText = SqlFactory.CreateSelectExperimentQuery(condition);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
result = new Experiment();
ExperimentHelper.SetExperimentPropertiesFromDb(result, reader);
}
}
// get experiment GSMs
SetExperimentGSMs(result, command);
} finally {
dbConnection.Close();
}
return result;
}
public static string CreateInsertGSMQueryFromObject(Experiment e)
{
if (e == null) {
throw new ArgumentNullException("Invalid query argument");
}
StringBuilder result = new StringBuilder();
foreach (ExperimentGSM gsm in e.GSMs)
{
result.Append("INSERT INTO gsms_data (experiment_id, gsm_id, gsm_value) VALUES (");
result.Append("'"); result.Append(e.Dataset); result.Append("', ");
result.Append("'"); result.Append(gsm.GSMId); result.Append("', ");
result.Append("'"); result.Append(gsm.Value); result.Append("'); ");
}
return result.ToString();
}
private static void CalculatePairwiseCorrelations(Experiment e, CorrelationTable ct)
{
for (int i = 0; i < e.DatasetTable.Count; i++)
{
DatasetTableRow rowA = e.DatasetTable[i];
for (int k = i + 1; k < e.DatasetTable.Count; k++)
{
DatasetTableRow rowB = e.DatasetTable[k];
string id_a = rowA.Identifier;
string id_b = rowB.Identifier;
if (!ct.CorrelationExist(id_a, id_b))
{
double[] valuesA = ExperimentHelper.MakeArrayFromStringValues(rowA.Value);
double[] valuesB = ExperimentHelper.MakeArrayFromStringValues(rowB.Value);
double correlation = Correlation.Pearson(valuesA, valuesB);
CorrelationResult cr = new CorrelationResult(id_a, id_b, correlation);
ct.Correlations.Add(cr);
}
}
}
}
/// <summary>Clears and populates a listview control with given data</summary>
/// <param name="lv">Listview control to be updated</param>
/// <param name="data">The data to be added to the listview</param>
public static void UpdateExperimentsListView(ListView lv, Experiment[] data)
{
lv.Items.Clear();
lv.BeginUpdate(); // avoid control flicker if large amount of data is going to be added
foreach (Experiment e in data)
{
ListViewItem lvi = new ListViewItem(e.DatabaseName);
lvi.SubItems.Add(e.DatabaseInstitute);
lvi.SubItems.Add(e.Dataset);
lvi.SubItems.Add(e.Tags);
lv.Items.Add(lvi);
}
lv.EndUpdate();
}
