/// <summary>
/// Initialize the object.
/// </summary>
/// <param name="project">Set the project.</param>
public ProjectPlayback(Project project)
{
_project = project;
PlaybackIndex = MIN_INDEX;
IsLooping = false;
Name = project.ProjectName;
// Set the number of ensembles from the project.
GetNumberOfEnsembles();
}
/// <summary>
/// Query the tblOptions for the revision of the
/// project file.
/// </summary>
/// <param name="project">Project to check.</param>
/// <returns>Number of rows in the table.</returns>
public static string GetProjectVersion(Project project)
{
if (project != null)
{
string query = String.Format("SELECT {0} FROM {1} WHERE ID=1;", DbCommon.COL_CMD_REV, DbCommon.TBL_ENS_OPTIONS);
return Convert.ToString(DbCommon.RunQueryOnProjectDbObj(project, query));
}
else
{
return string.Empty;
}
}
/// <summary>
/// Open a database connection to the project.
/// Given the Project, get the name and directory
/// of the project. Then open a connection to the
/// database.
/// </summary>
/// <param name="project">Project that the database will be opened.</param>
/// <returns>Database connection to Project database.</returns>
public static SQLiteConnection OpenProjectDB(Project project)
{
try
{
// Create full path to the project database
//string projectFullPath = project.ProjectFolderPath + @"\" + project.ProjectName + ".db";
string projectFullPath = project.GetProjectFullPath();
string projectConnection = "Data Source=" + projectFullPath;
var conn = new SQLiteConnection(projectConnection);
conn.Open();
return conn;
}
catch (SQLiteException e)
{
log.Error("Error opening Project database: " + project.ProjectName, e);
return null;
}
catch (Exception e)
{
log.Error("Error opening Project database: " + project.ProjectName, e);
return null;
}
}
/// <summary>
/// Query to check if the table contains any rows. This will check
/// the table given in the given project for any rows. If at least
/// 1 row exist in the table, it will return false.
/// </summary>
/// <param name="cnn">Sqlite Database Connection.</param>
/// <param name="project">Project to check.</param>
/// <param name="table">Table to check.</param>
/// <returns>TRUE = Table is empty.</returns>
public static bool CheckIfTableIsEmpty(SQLiteConnection cnn, Project project, string table)
{
// Create a query to check if the table is empty
string query = string.Format("SELECT exists(SELECT 1 FROM {0});", table);
// Check if any rows exist in the table
if (RunQueryOnProjectDb(cnn, project, query) > 0)
{
return false;
}
// Table is empty
return true;
}
/// <summary>
/// Constructor that takes a project
///
/// Set the maximum file size.
/// </summary>
/// <param name="project">Project.</param>
/// <param name="maxFileSize">Maximum file size for the binary file.</param>
/// <param name="fileType">File type to determine filename and extension. Default is Ensemble.</param>
public AdcpBinaryWriter(Project project, long maxFileSize = DEFAULT_BINARY_FILE_SIZE, FileType fileType = FileType.Ensemble)
{
ProjectName = project.ProjectName;
ProjectFolderPath = project.ProjectFolderPath;
SerialNumber = project.SerialNumber.SerialNumberString;
MaxFileSize = maxFileSize;
_fileType = fileType;
_isProcessingFile = false;
// Write buffer
_writeBuffer = new ConcurrentQueue<byte[]>();
_writeBufferIndex = 0;
// Set the project.
//SelectedProject = selectedProject;
}
/// <summary>
/// Run a query that will return an Integer. Give the
/// project and the query string. The result will be returned.
/// If an error, 0 will be returned.
/// </summary>
/// <param name="cnn">Sqlite Database Connection.</param>
/// <param name="project">Project to query.</param>
/// <param name="query">Query string.</param>
/// <returns>Result of query, if error, it will return 0.</returns>
public static int RunQueryOnProjectDb(SQLiteConnection cnn, Project project, string query)
{
int result = 0;
try
{
// Ensure a connection can be made
if (cnn == null)
{
return -1;
}
// Create command
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
// Get Result
object resultValue = cmd.ExecuteScalar();
result = Convert.ToInt32(resultValue.ToString());
}
}
catch (SQLiteException e)
{
log.Error(string.Format("Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return 0;
}
catch (Exception e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return 0;
}
return result;
}
/// <summary>
/// Check if the ensemble is in the cache.
/// If it is not in the cache, then query
/// the database for the ensemble. Then
/// update the cache with the next
/// </summary>
/// <param name="project">Project to get the data.</param>
/// <param name="index">Row ID of the data.</param>
/// <returns>Ensemble read from the database or cache.</returns>
private DataSet.Ensemble ReadEnsemble(Project project, long index)
{
// Use a background worker to get
// the ensemble.
if (project != null)
{
// Query for ensemble data
DataSet.Ensemble ensemble = _adcpDbCodec.QueryForDataSet(_cnn, project, index);
// Get the NMEA data and add it to the ensemble based off project settings
// Disturbute the dataset to all subscribers
if (ensemble != null)
{
// Create the velocity vectors for the ensemble
DataSet.VelocityVectorHelper.CreateVelocityVector(ref ensemble);
// Create a clone so the ensemble in the
//cache is not modified
return ensemble.Clone();
}
}
return null;
}
/// <summary>
/// Get the number of ensembles in the given
/// project.
/// </summary>
/// <param name="project">Project to determine the number of ensembles.</param>
public int GetNumberOfEnsembles(Project project)
{
return AdcpDatabaseCodec.GetNumberOfEnsembles(project);
}
/// <summary>
/// Check if the AdcpConfiguration needs to be updated in the database.
/// If it needs to be updated, the query will be run against the database.
/// The flag will then be reset.
///
/// The flag is locked because of a multithreaded environment.
/// </summary>
/// <param name="cnn">Database connection.</param>
/// <param name="selectedProject">Selected Project to get the lateset AdcpConfiguration.</param>
private void CheckCommandsAndOptions(SQLiteConnection cnn, Project selectedProject)
{
// Check if the ADCP Commands needs to be updated
if (_isAdcpConfigurationNeedUpdate)
{
// Send the query to the database
UpdateAdcpConfiguration(cnn, selectedProject);
// Lock the flag and reset the value
lock (_lockIsAdcpConfigurationNeedUpdate)
{
_isAdcpConfigurationNeedUpdate = false;
}
}
}
/// <summary>
/// Check if the AdcpConfiguration needs to be updated in the database.
/// If it needs to be updated, the query will be run against the database.
/// The flag will then be reset.
///
/// The flag is locked because of a multithreaded environment.
/// </summary>
/// <param name="cnn">Database connection.</param>
/// <param name="selectedProject">Selected Project to get the lateset AdcpConfiguration.</param>
private void CheckAppConfiguration(SQLiteConnection cnn, Project selectedProject)
{
// Check if the ADCP Commands needs to be updated
if (_isAppConfigurationNeedUpdate)
{
// Send the query to the database
UpdateAppConfiguration(cnn, selectedProject);
// Lock the flag and reset the value
lock (_lockIsAppConfigurationNeedUpdate)
{
_isAppConfigurationNeedUpdate = false; // Reset flag
_appConfigurationJsonStr = ""; // Reset string
}
}
}
/// <summary>
/// Constructor
///
/// Create queue to hold incoming ensemble data.
/// Subscribe to receive the latest ensemble data.
/// Start the thread to write the latest ensemble to
/// the project database.
/// </summary>
public AdcpDatabaseWriter()
{
// Initialize data
_selectedProject = null;
//IsRecording = true;
// Create a queue to hold incoming data
_datasetQueue = new ConcurrentQueue<DataSet.Ensemble>();
// Commands and options flags and locks
_isAdcpConfigurationNeedUpdate = false;
_lockIsAdcpConfigurationNeedUpdate = new object();
_isAppConfigurationNeedUpdate = false;
_lockIsAppConfigurationNeedUpdate = new object();
_isProjectOptionsNeedUpdate = false;
_lockIsProjectOptionsNeedUpdate = new object();
// Initialize the thread
_continue = true;
_eventWaitData = new EventWaitHandle(false, EventResetMode.AutoReset);
_processDataThread = new Thread(ProcessDataThread);
_processDataThread.Name = "ADCP Database Writer";
_processDataThread.Start();
}
/// <summary>
/// Get a list of ensembles from the database. This will query for a list of
/// rows from the database starting from the index and getting the size given.
/// Then parse and add the data to the list.
/// </summary>
/// <param name="project">Project containing the ensemble.</param>
/// <param name="index">Row ID</param>
/// <param name="size">Number of ensembles to get from the database.</param>
/// <returns>List of dataset based off the Row ID given and size.</returns>
public Cache<long, DataSet.Ensemble> QueryForDataSet(Project project, long index, uint size)
{
Cache<long, DataSet.Ensemble> cache = new Cache<long, DataSet.Ensemble>(size);
// Query for the ensemble
string queryEns = String.Format("SELECT * FROM {0} WHERE ID>={1} LIMIT {2};", DbCommon.TBL_ENS_ENSEMBLE, index.ToString(), size.ToString());
DataTable data = DbCommon.GetDataTableFromProjectDb(project, queryEns);
foreach (DataRow row in data.Rows)
{
int id = 0;
try { id = Convert.ToInt32(row[DbCommon.COL_ENS_ID]); }
catch (Exception) { }
DataSet.Ensemble dataset = ParseDataTables(project, row);
cache.Add(id, dataset);
}
return cache;
}
/// <summary>
/// Retrieve the dataset based off the index and project. Limit it
/// to the first dataset found with the correct index. The index is
/// auto incremented so there should only be 1 ensemble per index.
/// </summary>
/// <param name="cnn">Sqlite Database Connection.</param>
/// <param name="project">Project containing the ensemble.</param>
/// <param name="index">Row ID</param>
/// <returns>Dataset based off the Row ID given.</returns>
public DataSet.Ensemble QueryForDataSet(SQLiteConnection cnn, Project project, long index)
{
// Query for the ensemble
string queryEns = String.Format("SELECT * FROM {0} WHERE ID={1} LIMIT 1;", DbCommon.TBL_ENS_ENSEMBLE, index.ToString());
DataTable data = DbCommon.GetDataTableFromProjectDb(cnn, project, queryEns);
if (data.Rows.Count > 0)
{
DataSet.Ensemble dataset = ParseDataTables(project, data.Rows[0]);
return dataset;
}
return null;
}
/// <summary>
/// Convert the data from database to dataset.
/// </summary>
/// <param name="project">Project containing the datasets</param>
/// <param name="ensembleDataRow">DataTable returned from a query.</param>
/// <returns>DataSet of ensemble.</returns>
public DataSet.Ensemble ParseDataTables(Project project, DataRow ensembleDataRow)
{
// Create an ensemble
DataSet.Ensemble ensemble = new DataSet.Ensemble();
try
{
try
{
// Ensemble
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_ENSEMBLE_DS))
{
ensemble.EnsembleData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.EnsembleDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_ENSEMBLE_DS]));
}
if (ensemble.EnsembleData != null)
{
ensemble.IsEnsembleAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Ensemble data from the database.", e); }
try {
// Amplitude
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_AMPLITUDE_DS))
{
ensemble.AmplitudeData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.AmplitudeDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_AMPLITUDE_DS]));
}
if (ensemble.AmplitudeData != null)
{
ensemble.IsAmplitudeAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Amplitude data from the database.", e); }
try {
// Correlation
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_CORRELATION_DS))
{
ensemble.CorrelationData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.CorrelationDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_CORRELATION_DS]));
}
if (ensemble.CorrelationData != null)
{
ensemble.IsCorrelationAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Correlation data from the database.", e); }
try {
// Ancillary
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_ANCILLARY_DS))
{
ensemble.AncillaryData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.AncillaryDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_ANCILLARY_DS]));
}
if (ensemble.AncillaryData != null)
{
ensemble.IsAncillaryAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Ancillary data from the database.", e); }
try {
// Beam Velocity
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_BEAMVELOCITY_DS))
{
ensemble.BeamVelocityData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.BeamVelocityDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_BEAMVELOCITY_DS]));
}
if (ensemble.BeamVelocityData != null)
{
ensemble.IsBeamVelocityAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Beam Velocity data from the database.", e); }
try {
// Instrument Velocity
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_INSTRUMENTVELOCITY_DS))
{
ensemble.InstrumentVelocityData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.InstrumentVelocityDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_INSTRUMENTVELOCITY_DS]));
}
if (ensemble.InstrumentVelocityData != null)
{
ensemble.IsInstrumentVelocityAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Instrument Velocity data from the database.", e); }
try
{
// Earth Velocity
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_EARTHVELOCITY_DS))
{
ensemble.EarthVelocityData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.EarthVelocityDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_EARTHVELOCITY_DS]));
}
if (ensemble.EarthVelocityData != null)
{
ensemble.IsEarthVelocityAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Earth Velocity data from the database.", e); }
try {
// Good Beam
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_GOODBEAM_DS))
{
ensemble.GoodBeamData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.GoodBeamDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_GOODBEAM_DS]));
}
if (ensemble.GoodBeamData != null)
{
ensemble.IsGoodBeamAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Good Beam data from the database.", e); }
try {
// Good Earth
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_GOODEARTH_DS))
{
ensemble.GoodEarthData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.GoodEarthDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_GOODEARTH_DS]));
}
if (ensemble.GoodEarthData != null)
{
ensemble.IsGoodEarthAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Good Earth data from the database.", e); }
try {
// Bottom Track
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_BOTTOMTRACK_DS))
{
ensemble.BottomTrackData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.BottomTrackDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_BOTTOMTRACK_DS]));
}
if (ensemble.BottomTrackData != null)
{
ensemble.IsBottomTrackAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Bottom Track data from the database.", e); }
try {
// NMEA
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_NMEA_DS))
{
ensemble.NmeaData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.NmeaDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_NMEA_DS]));
}
if (ensemble.NmeaData != null)
{
ensemble.IsNmeaAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the NMEA data from the database.", e); }
try {
// Earth Water Mass Velocity
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_EARTHWATERMASS_DS))
{
ensemble.EarthWaterMassData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.EarthWaterMassDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_EARTHWATERMASS_DS]));
}
if (ensemble.EarthWaterMassData != null)
{
ensemble.IsEarthWaterMassAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Earth Water Mass Velocity data from the database.", e); }
try {
// Instrument Water Mass Velocity
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_INSTRUMENTWATERMASS_DS))
{
ensemble.InstrumentWaterMassData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.InstrumentWaterMassDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_INSTRUMENTWATERMASS_DS]));
}
if (ensemble.InstrumentWaterMassData != null)
{
ensemble.IsInstrumentWaterMassAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Instrument Water Mass Velocity data from the database.", e); }
try {
// Profile Engineering
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_PROFILEENGINEERING_DS))
{
ensemble.ProfileEngineeringData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.ProfileEngineeringDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_PROFILEENGINEERING_DS]));
}
if (ensemble.ProfileEngineeringData != null)
{
ensemble.IsProfileEngineeringAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Profile Engineering data from the database.", e); }
try {
// Bottom Track Engineering
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_BOTTOMTRACKENGINEERING_DS))
{
ensemble.BottomTrackEngineeringData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.BottomTrackEngineeringDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_BOTTOMTRACKENGINEERING_DS]));
}
if (ensemble.BottomTrackEngineeringData != null)
{
ensemble.IsBottomTrackEngineeringAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Bottom Track Engineering data from the database.", e); }
try {
// System Setup
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_SYSTEMSETUP_DS))
{
ensemble.SystemSetupData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.SystemSetupDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_SYSTEMSETUP_DS]));
}
if (ensemble.SystemSetupData != null)
{
ensemble.IsSystemSetupAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the System Setup data from the database.", e); }
try {
// Range Tracking
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_RANGETRACKING_DS))
{
ensemble.RangeTrackingData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.RangeTrackingDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_RANGETRACKING_DS]));
}
if (ensemble.RangeTrackingData != null)
{
ensemble.IsRangeTrackingAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the Ranging Tracking data from the database.", e); }
try {
// ADCP GPS
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_ADCPGPS))
{
ensemble.AdcpGpsData = Convert.ToString(ensembleDataRow[DbCommon.COL_ADCPGPS]);
}
if (!string.IsNullOrEmpty(ensemble.AdcpGpsData))
{
ensemble.IsAdcpGpsDataAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the ADCP GPS data from the database.", e); }
try {
// GPS 1
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_GPS1))
{
ensemble.Gps1Data = Convert.ToString(ensembleDataRow[DbCommon.COL_GPS1]);
}
if (!string.IsNullOrEmpty(ensemble.Gps1Data))
{
ensemble.IsGps1DataAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the GPS 1 data from the database.", e); }
try {
// GPS 2
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_GPS2))
{
ensemble.Gps2Data = Convert.ToString(ensembleDataRow[DbCommon.COL_GPS2]);
}
if (!string.IsNullOrEmpty(ensemble.Gps2Data))
{
ensemble.IsGps2DataAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the GPS 2 data from the database.", e); }
try {
// NMEA 1
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_NMEA1))
{
ensemble.Nmea1Data = Convert.ToString(ensembleDataRow[DbCommon.COL_NMEA1]);
}
if (!string.IsNullOrEmpty(ensemble.Nmea1Data))
{
ensemble.IsNmea1DataAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the NMEA 1 data from the database.", e); }
try {
// NMEA 2
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_NMEA2))
{
ensemble.Nmea2Data = Convert.ToString(ensembleDataRow[DbCommon.COL_NMEA2]);
}
if (!string.IsNullOrEmpty(ensemble.Nmea2Data))
{
ensemble.IsNmea2DataAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the NMEA 2 data from the database.", e); }
try {
// DVL
if (ensembleDataRow.Table.Columns.Contains(DbCommon.COL_DVL_DS))
{
ensemble.DvlData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataSet.DvlDataSet>(Convert.ToString(ensembleDataRow[DbCommon.COL_DVL_DS]));
}
if (ensemble.DvlData != null)
{
ensemble.IsDvlDataAvail = true;
}
}
catch (Exception e) { log.Error("Error parsing the DVL data from the database.", e); }
}
catch (Exception e)
{
// If there was a error parsing
// Return what could be parsed.
log.Error("Error parsing the data from the database.", e);
return ensemble;
}
return ensemble;
}
/// <summary>
/// Return the first ensemble found. This will get the total number
/// of ensembles. It will then start to look for
/// first ensemble. The first ensemble found will be returned.
/// </summary>
/// <param name="project">Project to get the ensemble.</param>
/// <returns>First ensemble in the project.</returns>
public DataSet.Ensemble GetFirstEnsemble(Project project)
{
DataSet.Ensemble ensemble = null;
// Get the total number ensembles
// Then return the first ensemble found
int total = GetNumberOfEnsembles(project);
for (int x = 0; x < total; x++)
{
ensemble = GetEnsemble(project, x);
if (ensemble != null)
{
return ensemble;
}
}
return ensemble;
}
/// <summary>
/// Return the last ensemble found. This will get the total number
/// of ensembles. It will then start to look for
/// last ensemble. The last ensemble found will be returned.
/// </summary>
/// <param name="project">Project to get the ensemble.</param>
/// <returns>Last ensemble in the project.</returns>
public DataSet.Ensemble GetLastEnsemble(Project project)
{
DataSet.Ensemble ensemble = null;
// Get the total number ensembles
// Then return the last ensemble found
// This will move backwards until it finds a good ensemble
int total = GetNumberOfEnsembles(project);
for (int x = total; x > 0 ; x--)
{
ensemble = GetEnsemble(project, x);
if (ensemble != null)
{
return ensemble;
}
}
return ensemble;
}
/// <summary>
/// Check if the Project Options needs to be updated in the database.
/// If it needs to be updated, the query will be run against the database.
/// The flag will then be reset.
///
/// The flag is locked because of a multithreaded environment.
/// </summary>
/// <param name="cnn">Database connection.</param>
/// <param name="selectedProject">Selected Project to get the lateset Project options.</param>
private void CheckProjectOptions(SQLiteConnection cnn, Project selectedProject)
{
// Check if the ADCP Commands needs to be updated
if (_isProjectOptionsNeedUpdate)
{
// Send the query to the database
UpdateProjectOptions(cnn, selectedProject);
// Lock the flag and reset the value
lock (_lockIsProjectOptionsNeedUpdate)
{
_isProjectOptionsNeedUpdate = false; // Reset flag
_projectOptionsJsonStr = ""; // Reset string
}
}
}
/// <summary>
/// Open a connection to the SQLite database.
/// This will check if the givn project is valid and
/// is new. If the project has changed, then open a
/// connection. If the project is the same, then do
/// not open a new connection. It should already be
/// open.
/// </summary>
/// <param name="project">Project to open.</param>
private void OpenConnection(Project project)
{
if (project != null)
{
// Close the previous connection if it is open
CloseConnection();
// Open a new connection
_cnn = DbCommon.OpenProjectDB(project);
}
}
/// <summary>
/// Update the row with the latest ADCP Configuration.
/// </summary>
/// <param name="cnn">Connection to the database.</param>
/// <param name="project">Project to set configuration to.</param>
private void UpdateAdcpConfiguration(SQLiteConnection cnn, Project project)
{
string json = Newtonsoft.Json.JsonConvert.SerializeObject(project.Configuration); // Serialize object to JSON
string jsonCmd = String.Format("{0} = '{1}'", DbCommon.COL_CMD_ADCP_CONFIGURATION, json);
string query = String.Format("UPDATE {0} SET {1} WHERE ID=1;", DbCommon.TBL_ENS_OPTIONS, jsonCmd); // Create query string
try
{
using (DbCommand cmd = cnn.CreateCommand())
{
Debug.WriteLine(query);
cmd.CommandText = query; // Set query string
cmd.ExecuteNonQuery(); // Execute the query
}
}
catch (Exception e)
{
log.Error(string.Format("Error updating AdcpConfiguration in project: {0}", query), e);
}
}
/// <summary>
/// Initialize values
/// </summary>
public AdcpDatabaseReader()
{
// Setup the codec
_adcpDbCodec = new AdcpDatabaseCodec();
// Initialize values
_prevProject = null;
_cnn = null;
}
/// <summary>
/// Update the row with the latest Project Options.
/// </summary>
/// <param name="cnn">Connection to the database.</param>
/// <param name="project">Project to set options to.</param>
private void UpdateProjectOptions(SQLiteConnection cnn, Project project)
{
string jsonCmd = String.Format("{0} = '{1}'", DbCommon.COL_CMD_PROJECT_OPTIONS, _projectOptionsJsonStr); // Set the column name and JSON string
string query = String.Format("UPDATE {0} SET {1} WHERE ID=1;", DbCommon.TBL_ENS_OPTIONS, jsonCmd); // Create query string
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query; // Set query string
cmd.ExecuteNonQuery(); // Execute the query
}
}
/// <summary>
/// Validate the version of the Project will work with this version of the software.
/// If the version is correct, return true.
///
/// Revision B includes the Adcp commands and options.
/// Revision C changed the options table.
/// Revision D made all the columns JSON data.
///
/// </summary>
/// <param name="project">Project to check.</param>
/// <returns>TRUE = version is valid.</returns>
public static bool ValidateVersion(Project project)
{
if(project != null)
{
// Get the version
string version = AdcpDatabaseCodec.GetProjectVersion(project);
// Return the latest version of the project
if (version.Contains("D"))
{
return true;
}
}
return false;
}
/// <summary>
/// Get all the ensembles for the given project. This will
/// get ensemble from the database and store to a cache.
/// </summary>
/// <param name="project">Project to get the ensembles.</param>
/// <returns>Cache of all the ensembles.</returns>
public Cache<long, DataSet.Ensemble> GetAllEnsembles(Project project)
{
uint size = (uint)GetNumberOfEnsembles(project);
Cache<long, DataSet.Ensemble> cache = new Cache<long, DataSet.Ensemble>(size);
// Verify the project is good
if (project != null)
{
cache = _adcpDbCodec.QueryForDataSet(project, 0, size);
}
return cache;
}
/// <summary>
/// Check if the given column exist in the given table for the given project.
/// If the column does not exist, it will return false.
///
/// To check if the colun exist, it will run a query to select the columnn given.
/// If the column does not exist, an exception will be given. If an exception is
/// found, it will return false.
/// </summary>
/// <param name="cnn">Sqlite Database Connection.</param>
/// <param name="project">Project to check.</param>
/// <param name="column">Column to check.</param>
/// <param name="table">Table to check.</param>
/// <returns>TRUE = Column exist in the table.</returns>
public static bool CheckIfColumnExist(SQLiteConnection cnn, Project project, string column, string table)
{
// Create a query to check if the column exist in the table
// This will try to select the column, if it does not exist, an error will be thrown
string query = string.Format("SELECT {0} FROM {1};", column, table);
// Default is true
bool result = true;
try
{
// Open a connection to the database
//using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
//{
// Ensure a connection can be made
if (cnn == null)
{
return false;
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
// Run the query
cmd.ExecuteNonQuery();
}
// Add all the data
dbTrans.Commit();
}
// Close the connection to the database
//cnn.Close();
//}
}
catch (SQLiteException)
{
return false;
}
catch (Exception)
{
return false;
}
return result;
}
/// <summary>
/// Playback the given ensemble.
/// </summary>
/// <param name="project">Project to get the data.</param>
/// <param name="index">Index for the ensemble.</param>
/// <returns>Ensemble read from the database.</returns>
public DataSet.Ensemble GetEnsemble(Project project, long index)
{
// Verify the index is valid
// The index starts at 1 so anything less than 0 is bad
if (project != null && index > 0)
{
// Check if the cache needs to be cleared for a new project
if (_prevProject == null || project != _prevProject)
{
// Set the project
_prevProject = project;
// Open connection to the database
OpenConnection(project);
}
// Get the ensemble from the
// cache or database.
return ReadEnsemble(project, index);
}
// Return an empty ensemble
return null;
}
/// <summary>
/// Allows the programmer to run a query against the project database
/// and return a table with the result.
/// </summary>
/// <param name="cnn">Sqlite Database Connection.</param>
/// <param name="project">Project to query.</param>
/// <param name="query">The SQL Query to run</param>
/// <returns>A DataTable containing the result set.</returns>
public static DataTable GetDataTableFromProjectDb(SQLiteConnection cnn, Project project, string query)
{
DataTable dt = new DataTable();
try
{
// Open a connection to the database
//using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
//{
// Ensure a connection can be made
if (cnn == null)
{
return dt;
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
DbDataReader reader = cmd.ExecuteReader();
// Load the datatable with query result
dt.Load(reader);
// Close the connection
reader.Close();
//cnn.Close();
}
}
//}
}
catch (Exception e)
{
if (project != null)
{
log.Error(string.Format("Error populating datatable from {0} database. \n{1}", project.ProjectName, query), e);
}
else
{
log.Error(string.Format("Error populating datatable. \n{0}", query), e);
}
}
return dt;
}
/// <summary>
/// Get a fixed number of ensembles starting at index from the project.
/// This will open the database for the given project. It will then read
/// the ensemble starting at index.
///
/// The index starts with 1 in the database.
/// </summary>
/// <param name="project">Project to read data.</param>
/// <param name="index">Start location.</param>
/// <param name="size">Number of ensembles to read.</param>
/// <returns>Cache with the read ensembles.</returns>
public Cache<long, DataSet.Ensemble> GetEnsembles(Project project, long index, uint size)
{
Cache<long, DataSet.Ensemble> cache = new Cache<long, DataSet.Ensemble>(size);
// Verify the project is good
if (project != null)
{
cache = _adcpDbCodec.QueryForDataSet(project, index, size);
}
return cache;
}
/// <summary>
/// Run a query that will return an object. Give the
/// project and the query string. The result will be returned.
/// If an error, null will be returned.
/// </summary>
/// <param name="project">Project to query.</param>
/// <param name="query">Query string.</param>
/// <returns>Result of query, if error, it will return 0.</returns>
public static object RunQueryOnProjectDbObj(Project project, string query)
{
object result = 0;
try
{
// Open a connection to the database
using (SQLiteConnection cnn = DbCommon.OpenProjectDB(project))
{
// Ensure a connection can be made
if (cnn == null)
{
return -1;
}
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = query;
// Get Result
result = cmd.ExecuteScalar();
}
// Add all the data
dbTrans.Commit();
}
// Close the connection to the database
cnn.Close();
}
}
catch (SQLiteException e)
{
log.Error(string.Format("Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return 0;
}
catch (Exception e)
{
log.Error(string.Format("Unknown Error running query on database: {0} \n{1}", project.ProjectName, query), e);
return 0;
}
return result;
}
/// <summary>
/// Query the tblEnsemble for the number of rows it contains.
/// Return the value.
/// </summary>
/// <param name="project">Project to check.</param>
/// <returns>Number of rows in the table.</returns>
public static int GetNumberOfEnsembles(Project project)
{
if (project != null)
{
string query = String.Format("SELECT COUNT(*) FROM {0};", DbCommon.TBL_ENS_ENSEMBLE);
return DbCommon.RunQueryOnProjectDb(project, query);
}
else
{
return 0;
}
}
